#!/usr/bin/env python
"""
Add loans, repayments, and expenses to the database
"""
import os
import sys
import django
from datetime import datetime, timedelta
from decimal import Decimal
import random

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings')
django.setup()

from django.contrib.auth import get_user_model
from users.models import Branch
from loans.models import Loan, Repayment
from expenses.models import Expense
from django.utils import timezone
from django.db import connection

User = get_user_model()

print("=" * 60)
print("ADDING LOANS AND SAMPLE DATA")
print("=" * 60)

# Get data
borrowers = list(User.objects.filter(role='borrower'))
officers = list(User.objects.filter(role='loan_officer'))
branches = list(Branch.objects.all())

if not borrowers:
    print("✗ No borrowers found! Run populate_sample_data.py first")
    sys.exit(1)

if not officers:
    print("✗ No loan officers found!")
    sys.exit(1)

# Get loan products
with connection.cursor() as cursor:
    cursor.execute("SELECT id, name, min_amount, max_amount, interest_rate, processing_fee, min_duration, max_duration FROM loan_products WHERE is_active = 1")
    products = cursor.fetchall()

if not products:
    print("✗ No loan products found!")
    sys.exit(1)

print(f"\nFound:")
print(f"  - {len(borrowers)} borrowers")
print(f"  - {len(officers)} loan officers")
print(f"  - {len(products)} loan products")
print(f"  - {len(branches)} branches")

# Create loans
print("\n" + "=" * 60)
print("Creating loans...")
print("=" * 60)

statuses = ['pending', 'approved', 'disbursed', 'active', 'completed', 'defaulted']
loans_created = 0

for i in range(min(80, len(borrowers))):
    borrower = borrowers[i]
    officer = random.choice(officers)
    product = random.choice(products)
    
    # Unpack product data
    product_id, product_name, min_amt, max_amt, interest_rate, processing_fee, min_dur, max_dur = product
    
    # Random loan amount
    amount = Decimal(random.randint(int(min_amt), int(max_amt)))
    duration_days = random.randint(min_dur, max_dur)
    
    # Calculate fees
    proc_fee = amount * Decimal(processing_fee) / 100
    interest = amount * Decimal(interest_rate) / 100
    total = amount + proc_fee + interest
    
    # Random date
    days_ago = random.randint(1, 180)
    app_date = timezone.now() - timedelta(days=days_ago)
    
    # Status based on age
    if days_ago < 7:
        status = random.choice(['pending', 'approved'])
    elif days_ago < 30:
        status = random.choice(['approved', 'disbursed', 'active'])
    elif days_ago < 90:
        status = random.choice(['active', 'completed'])
    else:
        status = random.choice(['completed', 'defaulted'])
    
    try:
        with connection.cursor() as cursor:
            cursor.execute("""
                INSERT INTO loans (
                    borrower_id, loan_product_id, amount, processing_fee, interest_amount,
                    total_amount, duration_days, status, loan_officer_id, application_date,
                    approval_date, disbursement_date, due_date, created_at, updated_at
                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW(), NOW())
            """, [
                str(borrower.id), product_id, amount, proc_fee, interest,
                total, duration_days, status, str(officer.id), app_date,
                app_date + timedelta(days=1) if status != 'pending' else None,
                app_date + timedelta(days=2) if status in ['disbursed', 'active', 'completed'] else None,
                app_date + timedelta(days=duration_days + 2) if status in ['active', 'completed', 'defaulted'] else None
            ])
            loan_id = cursor.lastrowid
            
            # Add repayments for active/completed loans
            if status in ['active', 'completed', 'defaulted']:
                if status == 'completed':
                    # Fully paid
                    cursor.execute("""
                        INSERT INTO repayments (
                            loan_id, amount, payment_date, payment_method,
                            transaction_code, received_by_id, created_at, updated_at
                        ) VALUES (%s, %s, %s, %s, %s, %s, NOW(), NOW())
                    """, [
                        loan_id, total,
                        app_date + timedelta(days=random.randint(1, duration_days)),
                        'mpesa', f'MPE{random.randint(100000, 999999)}',
                        str(officer.id)
                    ])
                else:
                    # Partial payments
                    paid_amount = total * Decimal(random.uniform(0.2, 0.8))
                    num_payments = random.randint(1, 3)
                    payment_amount = paid_amount / num_payments
                    
                    for j in range(num_payments):
                        cursor.execute("""
                            INSERT INTO repayments (
                                loan_id, amount, payment_date, payment_method,
                                transaction_code, received_by_id, created_at, updated_at
                            ) VALUES (%s, %s, %s, %s, %s, %s, NOW(), NOW())
                        """, [
                            loan_id, payment_amount,
                            app_date + timedelta(days=random.randint(1, duration_days)),
                            random.choice(['mpesa', 'cash', 'bank']),
                            f'TXN{random.randint(100000, 999999)}',
                            str(officer.id)
                        ])
        
        loans_created += 1
        if (i + 1) % 20 == 0:
            print(f"  ✓ Created {i+1} loans...")
    
    except Exception as e:
        print(f"  ✗ Error creating loan: {e}")

print(f"\n✓ Total loans created: {loans_created}")

# Create expenses
print("\n" + "=" * 60)
print("Creating expenses...")
print("=" * 60)

categories = ['operational', 'staff', 'marketing', 'utilities', 'office', 'transport', 'maintenance']
payment_methods = ['cash', 'mpesa', 'bank', 'cheque']
expenses_created = 0

for i in range(100):
    branch = random.choice(branches)
    staff = random.choice(officers)
    days_ago = random.randint(1, 180)
    expense_date = (timezone.now() - timedelta(days=days_ago)).date()
    
    expense = Expense.objects.create(
        branch=branch,
        staff=staff,
        title=f'{random.choice(categories).title()} Expense',
        description=f'Sample expense for {random.choice(categories)}',
        category=random.choice(categories),
        amount=Decimal(random.randint(500, 50000)),
        payment_method=random.choice(payment_methods),
        paid_to=f'Vendor {random.randint(1, 50)}',
        status=random.choice(['approved', 'approved', 'approved', 'pending']),
        expense_date=expense_date,
        reference_number=f'REF{random.randint(10000, 99999)}'
    )
    expenses_created += 1
    
    if (i + 1) % 25 == 0:
        print(f"  ✓ Created {i+1} expenses...")

print(f"\n✓ Total expenses created: {expenses_created}")

# Final summary
print("\n" + "=" * 60)
print("FINAL SUMMARY")
print("=" * 60)
print(f"Branches: {Branch.objects.count()}")
print(f"Users: {User.objects.count()}")
print(f"  - Borrowers: {User.objects.filter(role='borrower').count()}")
print(f"  - Loan Officers: {User.objects.filter(role='loan_officer').count()}")
print(f"  - Branch Managers: {User.objects.filter(role='branch_manager').count()}")

with connection.cursor() as cursor:
    cursor.execute("SELECT COUNT(*) FROM loan_products")
    print(f"Loan Products: {cursor.fetchone()[0]}")
    
    cursor.execute("SELECT COUNT(*) FROM loans")
    total_loans = cursor.fetchone()[0]
    print(f"Loans: {total_loans}")
    
    cursor.execute("SELECT status, COUNT(*) FROM loans GROUP BY status")
    for status, count in cursor.fetchall():
        print(f"  - {status}: {count}")
    
    cursor.execute("SELECT COUNT(*) FROM repayments")
    print(f"Repayments: {cursor.fetchone()[0]}")

print(f"Expenses: {Expense.objects.count()}")

print("\n" + "=" * 60)
print("✓ Sample data added successfully!")
print("=" * 60)
print("\nYou can now access the system at http://localhost:8000")
print("Login with: admin / admin (or check your admin credentials)")
