#!/usr/bin/env python
"""
Final script to populate loans using Django ORM
"""
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, LoanApplication
from django.utils import timezone
from django.db import connection

User = get_user_model()

print("=" * 60)
print("CREATING SAMPLE LOANS")
print("=" * 60)

# Get data
borrowers = list(User.objects.filter(role='borrower')[:80])
officers = list(User.objects.filter(role='loan_officer'))

if not borrowers:
    print("✗ No borrowers found!")
    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")

# Create loans
print("\nCreating loans...")
statuses = ['pending', 'approved', 'disbursed', 'active', 'completed', 'defaulted']
loans_created = 0

for i, borrower in enumerate(borrowers):
    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
    principal = Decimal(random.randint(int(min_amt), int(max_amt)))
    duration_days = random.randint(min_dur, max_dur)
    
    # Calculate fees
    proc_fee = principal * Decimal(processing_fee) / 100
    interest = principal * Decimal(interest_rate) / 100
    total = principal + proc_fee + interest
    
    # Random date
    days_ago = random.randint(1, 180)
    disb_date = timezone.now() - timedelta(days=days_ago)
    due = disb_date + timedelta(days=duration_days)
    
    # 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:
        loan = Loan.objects.create(
            borrower=borrower,
            principal_amount=principal,
            processing_fee=proc_fee,
            interest_amount=interest,
            total_amount=total,
            duration_days=duration_days,
            status=status,
            disbursement_date=disb_date if status in ['disbursed', 'active', 'completed', 'defaulted'] else None,
            due_date=due if status in ['active', 'completed', 'defaulted'] else None,
        )
        
        # Add repayments for active/completed loans
        if status in ['active', 'completed', 'defaulted']:
            if status == 'completed':
                # Fully paid
                Repayment.objects.create(
                    loan=loan,
                    amount=total,
                    payment_date=disb_date + timedelta(days=random.randint(1, duration_days)),
                    payment_method='mpesa',
                    transaction_code=f'MPE{random.randint(100000, 999999)}',
                    received_by=officer
                )
            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):
                    Repayment.objects.create(
                        loan=loan,
                        amount=payment_amount,
                        payment_date=disb_date + timedelta(days=random.randint(1, duration_days)),
                        payment_method=random.choice(['mpesa', 'cash', 'bank']),
                        transaction_code=f'TXN{random.randint(100000, 999999)}',
                        received_by=officer
                    )
        
        loans_created += 1
        if (i + 1) % 20 == 0:
            print(f"  ✓ Created {i+1} loans...")
    
    except Exception as e:
        print(f"  ✗ Error creating loan for {borrower.get_full_name()}: {e}")

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

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

with connection.cursor() as cursor:
    cursor.execute("SELECT COUNT(*) FROM loan_products")
    print(f"Loan Products: {cursor.fetchone()[0]}")

print(f"Loans: {Loan.objects.count()}")
for status in ['pending', 'approved', 'disbursed', 'active', 'completed', 'defaulted']:
    count = Loan.objects.filter(status=status).count()
    if count > 0:
        print(f"  - {status.title()}: {count}")

print(f"Repayments: {Repayment.objects.count()}")

from expenses.models import Expense
print(f"Expenses: {Expense.objects.count()}")

print("\n" + "=" * 60)
print("✓ Database populated with sample data!")
print("=" * 60)
print("\nServer is running at: http://localhost:8000")
print("\nLOGIN CREDENTIALS:")
print("-" * 60)
print("Admin: admin / (check your admin password)")
print("Manager: manager1@branch.com / manager123")
print("Loan Officer: officer1@branch.com / officer123")
print("Borrower: borrower1@example.com / borrower123")
print("-" * 60)