import pandas as pd
import uuid
import datetime
import math
import bcrypt

# Konfigurasi bcrypt untuk password default
password = "Koperasi2026"
# Gunakan salt yang sama untuk mempercepat proses (hanya untuk seed awal)
salt = bcrypt.gensalt()
hashed_password = bcrypt.hashpw(password.encode('utf-8'), salt).decode('utf-8')

file_path = '../doc/KKCT JULI 2026-2.xlsx'
sheet_name = 'JULI 26'

def clean_val(val):
    if pd.isna(val) or str(val).strip() == '':
        return 0
    try:
        return float(val)
    except:
        return 0

def clean_str(val):
    if pd.isna(val):
        return ''
    return str(val).strip().replace("'", "''")

def parse_ke_dari(val):
    val = clean_str(val)
    if not val or '/' not in val:
        return 0, 0
    parts = val.split('/')
    try:
        x = int(parts[0])
        y = int(parts[1])
        return x, y
    except:
        return 0, 0

print("Membaca file Excel...")
df = pd.read_excel(file_path, sheet_name=sheet_name, header=None)

sql_statements = [
    "BEGIN;",
    "",
    "-- 0. BUAT PRODUK PINJAMAN BARU",
    "INSERT INTO loan_products (code, name, description, max_amount, max_tenor_months, interest_rate, calculation_type) VALUES",
    "('KKCT-01', 'Pinjaman Uang KKCT', 'Pinjaman uang tunai KKCT', 100000000, 120, 0.01, 'flat'),",
    "('ELK-01', 'Pinjaman Elektronik', 'Pinjaman untuk barang elektronik', 50000000, 60, 0.01, 'flat'),",
    "('BNK-01', 'Pinjaman Bank', 'Pinjaman Bank BNI', 200000000, 120, 0.01, 'flat')",
    "ON CONFLICT (code) DO NOTHING;",
    ""
]

print("Mulai parsing baris per baris...")
# Baris data dimulai dari index 5
for i in range(5, len(df)):
    row = df.iloc[i].tolist()
    no = clean_str(row[0])
    
    # Berhenti jika 'NO' kosong dan 'NAMA' juga kosong
    if not no and not clean_str(row[1]):
        continue
        
    # Lewati baris subtotal/grandtotal jika ada
    if "TOTAL" in clean_str(row[1]).upper():
        continue
        
    nama = clean_str(row[1])
    no_koperasi = clean_str(row[2])
    
    if not nama:
        continue
        
    if not no_koperasi:
        # Generate dummy CIF if not exists
        no_koperasi = f"CIF-{i}"

    user_id = str(uuid.uuid4())
    member_id = str(uuid.uuid4())
    email = f"member{i}@koperasi.id"
    
    # 1. USERS & MEMBERS
    sql_statements.append(f"-- Member: {nama}")
    sql_statements.append(f"INSERT INTO users (id, email, password_hash, role, full_name, is_active) VALUES ('{user_id}', '{email}', '{hashed_password}', 'member', '{nama}', true);")
    sql_statements.append(f"INSERT INTO members (id, user_id, cif, member_number, full_name, nik, status, join_date) VALUES ('{member_id}', '{user_id}', '{no_koperasi}', '{no_koperasi}', '{nama}', 'NIK-{no_koperasi}', 'active', CURRENT_DATE);")
    
    # 2. SIMPANAN (Pokok, Wajib, Sukarela)
    pokok = clean_val(row[3])
    wajib = clean_val(row[4])
    sukarela = clean_val(row[5])
    
    savings = [
        ('pokok', pokok, 1), # Asumsi product_id 1 = Pokok
        ('wajib', wajib, 2), # Asumsi product_id 2 = Wajib
        ('sukarela', sukarela, 3) # Asumsi product_id 3 = Sukarela
    ]
    
    for sav_type, amount, prod_id in savings:
        account_id = str(uuid.uuid4())
        acc_num = f"SAV-{prod_id}-{no_koperasi}"
        # Buat account dengan saldo akhir langsung diset
        sql_statements.append(f"INSERT INTO savings_accounts (id, account_number, member_id, product_id, balance, is_active) VALUES ('{account_id}', '{acc_num}', '{member_id}', {prod_id}, {amount}, true);")
        
        # Tambahkan transaction history sebagai Saldo Awal jika amount > 0
        if amount > 0:
            trx_id = str(uuid.uuid4())
            trx_code = f"TRX-{sav_type.upper()}-{no_koperasi}"
            sql_statements.append(f"INSERT INTO savings_transactions (id, transaction_code, account_id, member_id, type, amount, balance_before, balance_after, description) VALUES ('{trx_id}', '{trx_code}', '{account_id}', '{member_id}', 'deposit', {amount}, 0, {amount}, 'Saldo Awal Migrasi');")

    # 3. PINJAMAN
    # Helper to process loan
    def process_loan(angsuran, ke_dari, jasa, prod_code):
        angsuran_val = clean_val(angsuran)
        jasa_val = clean_val(jasa)
        x, y = parse_ke_dari(ke_dari)
        
        # Hanya proses jika ada tenor (y > 0) dan angsuran > 0
        if y > 0 and angsuran_val > 0:
            plafon = angsuran_val * y
            # Sisa angsuran bulan ini (X) masih perlu dibayar, jadi yang sudah lunas adalah (X-1)
            # Tapi tunggu, kolom excel adalah angsuran bulan Juli (misal 8/10),
            # Artinya sisa hutang SEBELUM Juli adalah (Y - (X-1)) * Angsuran.
            # Agar simpel, kita set outstanding = plafon, lalu insert jadwal 1 to (X-1) as 'paid', dan X to Y as 'unpaid'.
            
            loan_id = str(uuid.uuid4())
            app_no = f"LOAN-{no_koperasi}-{str(uuid.uuid4())[:8]}"
            
            sql_statements.append(f"INSERT INTO loan_applications (id, application_number, member_id, product_id, amount_requested, tenor_months, purpose, status, amount_approved, amount_disbursed, interest_rate, calculation_type) VALUES ('{loan_id}', '{app_no}', '{member_id}', (SELECT id FROM loan_products WHERE code = '{prod_code}'), {plafon}, {y}, 'Migrasi Data', 'disbursed', {plafon}, {plafon}, 0.01, 'flat');")
            
            # Generate schedules
            due_date = datetime.date.today()
            for m in range(1, y + 1):
                sch_id = str(uuid.uuid4())
                status = "'paid'" if m < x else "'unpaid'"
                paid_amt = angsuran_val if m < x else 0
                paid_at = "CURRENT_TIMESTAMP" if m < x else "NULL"
                
                # Tanggal jatuh tempo diset per bulan (sederhana saja, tambah 30 hari * (m - x))
                delta_days = (m - x) * 30
                sch_date = due_date + datetime.timedelta(days=delta_days)
                
                sql_statements.append(f"INSERT INTO loan_schedules (id, loan_id, installment_number, due_date, principal_amount, interest_amount, total_payment, paid_amount, paid_at, status) VALUES ('{sch_id}', '{loan_id}', {m}, '{sch_date.strftime('%Y-%m-%d')}', {angsuran_val}, {jasa_val}, {angsuran_val + jasa_val}, {paid_amt}, {paid_at}, {status});")

    # Pinjaman KKCT 1, 2, 3
    process_loan(row[6], row[7], row[8], 'KKCT-01')
    process_loan(row[9], row[10], row[11], 'KKCT-01')
    process_loan(row[12], row[13], row[14], 'KKCT-01')
    
    # Pinjaman Elektronik 1, 2
    process_loan(row[15], row[16], row[17], 'ELK-01')
    process_loan(row[18], row[19], row[20], 'ELK-01')
    
    # Pinjaman Bank
    process_loan(row[21], row[22], row[23], 'BNK-01')
    
    # 4. BELANJA TOKO (WasSerba Paylater)
    toko = clean_val(row[24])
    if toko > 0:
        order_id = str(uuid.uuid4())
        order_no = f"ORD-{no_koperasi}-{str(uuid.uuid4())[:8]}"
        sql_statements.append(f"INSERT INTO orders (id, order_number, member_id, total_amount, payment_method, status, notes) VALUES ('{order_id}', '{order_no}', '{member_id}', {toko}, 'paylater', 'completed', 'Migrasi tagihan WasSerba');")

    sql_statements.append("")

sql_statements.append("COMMIT;")

with open('import_data.sql', 'w') as f:
    f.write('\n'.join(sql_statements))

print("Berhasil generate import_data.sql")