import pandas as pd
from sqlalchemy import create_engine
import datetime
import os

# Setup DB connection
engine = create_engine('postgresql://postgres:postgres@localhost:5432/Koperasi_db')

# 1. Fetch Members
members = pd.read_sql("SELECT id, cif, full_name FROM members ORDER BY CAST(REGEXP_REPLACE(cif, '[^0-9]', '', 'g') AS INTEGER) ASC", engine)

# 2. Fetch Savings
savings_raw = pd.read_sql("""
    SELECT sa.member_id, sp.type, sa.balance 
    FROM savings_accounts sa
    JOIN savings_products sp ON sa.product_id = sp.id
""", engine)

# Pivot savings if not empty
if not savings_raw.empty:
    savings = savings_raw.pivot(index='member_id', columns='type', values='balance').reset_index()
    savings = savings.fillna(0)
else:
    savings = pd.DataFrame(columns=['member_id', 'pokok', 'wajib', 'sukarela'])

# 3. Fetch Loans
loans_raw = pd.read_sql("""
    SELECT 
        la.member_id, 
        lp.code as product_code,
        la.id as loan_id,
        la.tenor_months,
        la.amount_approved,
        (la.amount_approved / la.tenor_months) as angsuran,
        -- get number of paid installments to calculate 'Ke-X'
        (SELECT COUNT(*) FROM loan_schedules ls WHERE ls.loan_id = la.id AND ls.status = 'paid') as paid_count,
        (SELECT SUM(interest_amount) FROM loan_schedules ls WHERE ls.loan_id = la.id AND ls.installment_number = 1) as jasa
    FROM loan_applications la
    JOIN loan_products lp ON la.product_id = lp.id
""", engine)

# 4. Fetch Toko (orders)
toko_raw = pd.read_sql("""
    SELECT member_id, SUM(total_amount) as toko_total
    FROM orders
    WHERE payment_method = 'paylater' AND status = 'completed'
    GROUP BY member_id
""", engine)

# Merge everything
report_data = []

for index, member in members.iterrows():
    member_id = member['id']
    row = {
        'NO': index + 1,
        'NAMA': member['full_name'],
        'No urut': member['cif']
    }
    
    # Savings
    sav = savings[savings['member_id'] == member_id]
    if not sav.empty:
        row['SIMP POKOK'] = float(sav['pokok'].values[0]) if 'pokok' in sav.columns else 0
        row['SIMP WAJIB'] = float(sav['wajib'].values[0]) if 'wajib' in sav.columns else 0
        row['SIMP SUKARELA'] = float(sav['sukarela'].values[0]) if 'sukarela' in sav.columns else 0
    else:
        row['SIMP POKOK'] = 0
        row['SIMP WAJIB'] = 0
        row['SIMP SUKARELA'] = 0

    # Loans
    member_loans = loans_raw[loans_raw['member_id'] == member_id]
    
    # KKCT
    kkct_loans = member_loans[member_loans['product_code'] == 'KKCT-01']
    for i in range(3):
        prefix = f'KKCT_{i+1}_'
        if i < len(kkct_loans):
            loan = kkct_loans.iloc[i]
            x = loan['paid_count'] + 1
            y = loan['tenor_months']
            row[prefix + 'ANGS'] = float(loan['angsuran'])
            row[prefix + 'KE-DR'] = f"{int(x)}/{int(y)}"
            row[prefix + 'JASA'] = float(loan['jasa'] if pd.notnull(loan['jasa']) else 0)
        else:
            row[prefix + 'ANGS'] = 0
            row[prefix + 'KE-DR'] = ''
            row[prefix + 'JASA'] = 0
            
    # Elektronik
    elk_loans = member_loans[member_loans['product_code'] == 'ELK-01']
    for i in range(2):
        prefix = f'ELK_{i+1}_'
        if i < len(elk_loans):
            loan = elk_loans.iloc[i]
            x = loan['paid_count'] + 1
            y = loan['tenor_months']
            row[prefix + 'ANGS'] = float(loan['angsuran'])
            row[prefix + 'KE-DR'] = f"{int(x)}/{int(y)}"
            row[prefix + 'JASA'] = float(loan['jasa'] if pd.notnull(loan['jasa']) else 0)
        else:
            row[prefix + 'ANGS'] = 0
            row[prefix + 'KE-DR'] = ''
            row[prefix + 'JASA'] = 0

    # Bank
    bnk_loans = member_loans[member_loans['product_code'] == 'BNK-01']
    if not bnk_loans.empty:
        loan = bnk_loans.iloc[0]
        x = loan['paid_count'] + 1
        y = loan['tenor_months']
        row['BANK_ANGS'] = float(loan['angsuran'])
        row['BANK_KE-DR'] = f"{int(x)}/{int(y)}"
        row['BANK_JASA'] = float(loan['jasa'] if pd.notnull(loan['jasa']) else 0)
    else:
        row['BANK_ANGS'] = 0
        row['BANK_KE-DR'] = ''
        row['BANK_JASA'] = 0

    # Toko
    tok = toko_raw[toko_raw['member_id'] == member_id]
    row['TOKO'] = float(tok['toko_total'].values[0]) if not tok.empty else 0
    row['LAIN LAIN'] = 0
    
    # Totals
    total_potongan = (
        row['SIMP POKOK'] + row['SIMP WAJIB'] + row['SIMP SUKARELA'] +
        row['KKCT_1_ANGS'] + row['KKCT_1_JASA'] +
        row['KKCT_2_ANGS'] + row['KKCT_2_JASA'] +
        row['KKCT_3_ANGS'] + row['KKCT_3_JASA'] +
        row['ELK_1_ANGS'] + row['ELK_1_JASA'] +
        row['ELK_2_ANGS'] + row['ELK_2_JASA'] +
        row['BANK_ANGS'] + row['BANK_JASA'] +
        row['TOKO']
    )
    
    row['TOTAL POTONGAN KOPERASI'] = total_potongan
    
    report_data.append(row)

df_report = pd.DataFrame(report_data)

# Create an Excel writer object
excel_path = 'Summary_Report_Koperasi.xlsx'
df_report.to_excel(excel_path, index=False)

print(f"Report generated successfully at {os.path.abspath(excel_path)}")