"""
Test script to diagnose why Mwamba loans have incorrect total amounts

Run with: python test_mwamba_loan_totals.py
"""

import os
import django

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

from loans.models import Loan, LoanProduct
from decimal import Decimal

print("=" * 80)
print("MWAMBA LOAN TOTAL AMOUNT DIAGNOSTIC TEST")
print("=" * 80)
print()

# Get Mwamba product
try:
    mwamba_product = LoanProduct.objects.get(product_type='mwamba')
    print(f"✓ Found Mwamba product: {mwamba_product.name}")
    print(f"  Interest Rate: {mwamba_product.interest_rate}%")
    print(f"  Processing Fee: {mwamba_product.processing_fee}%")
    print()
except LoanProduct.DoesNotExist:
    print("❌ Mwamba product not found!")
    exit(1)

# Get all loans
all_loans = Loan.objects.all()
print(f"📊 Total loans in database: {all_loans.count()}")
print()

if all_loans.count() == 0:
    print("⚠️  WARNING: No loans found in database!")
    print()
    print("This could mean:")
    print("1. The database is empty")
    print("2. Database connection issue")
    print("3. Wrong database selected")
    print()
    print("Checking database connection...")
    from django.db import connection
    print(f"Database: {connection.settings_dict['NAME']}")
    print(f"Host: {connection.settings_dict['HOST']}")
    print(f"User: {connection.settings_dict['USER']}")
    print()
    exit(0)

# Get Mwamba loans
mwamba_loans = all_loans.filter(application__loan_product__product_type='mwamba')
print(f"📊 Mwamba loans found: {mwamba_loans.count()}")
print()

if mwamba_loans.count() == 0:
    print("⚠️  No Mwamba loans found!")
    print()
    # Show all loans
    print("All loans in database:")
    for loan in all_loans[:10]:
        print(f"  - {loan.loan_number}: {loan.application.loan_product.name}")
    print()
    exit(0)

# Check each Mwamba loan
print("=" * 80)
print("CHECKING MWAMBA LOANS")
print("=" * 80)
print()

incorrect_loans = []
correct_loans = []

for loan in mwamba_loans:
    print(f"Loan: {loan.loan_number}")
    print(f"Borrower: {loan.borrower.get_full_name()}")
    print(f"Product: {loan.application.loan_product.name}")
    print(f"Duration: {loan.duration_days} days")
    print()
    
    # Show stored values
    print("  STORED VALUES IN DATABASE:")
    print(f"    Principal Amount: KES {loan.principal_amount:,.2f}")
    print(f"    Interest Amount: KES {loan.interest_amount:,.2f}")
    print(f"    Processing Fee: KES {loan.processing_fee:,.2f}")
    print(f"    Total Amount: KES {loan.total_amount:,.2f}")
    print()
    
    # Calculate what it SHOULD be
    correct_total = loan.principal_amount + loan.interest_amount + loan.processing_fee
    print("  CALCULATED VALUES:")
    print(f"    Correct Total: KES {correct_total:,.2f}")
    print(f"    Formula: {loan.principal_amount:,.2f} + {loan.interest_amount:,.2f} + {loan.processing_fee:,.2f}")
    print()
    
    # Check if correct
    if loan.total_amount == correct_total:
        print("  ✅ STATUS: CORRECT")
        correct_loans.append(loan.loan_number)
    else:
        difference = correct_total - loan.total_amount
        print(f"  ❌ STATUS: INCORRECT")
        print(f"  ⚠️  Difference: KES {difference:,.2f}")
        print()
        
        # Try to figure out the wrong calculation
        wrong_calc_1 = loan.principal_amount + loan.interest_amount
        wrong_calc_2 = loan.principal_amount + loan.interest_amount - loan.processing_fee
        wrong_calc_3 = loan.principal_amount + loan.processing_fee
        
        print("  POSSIBLE WRONG CALCULATIONS:")
        if loan.total_amount == wrong_calc_1:
            print(f"    ⚠️  Matches: Principal + Interest (missing processing fee)")
        elif loan.total_amount == wrong_calc_2:
            print(f"    ⚠️  Matches: Principal + Interest - Processing Fee (subtracting instead of adding)")
        elif loan.total_amount == wrong_calc_3:
            print(f"    ⚠️  Matches: Principal + Processing Fee (missing interest)")
        else:
            print(f"    ❓ Unknown calculation pattern")
        
        incorrect_loans.append({
            'loan_number': loan.loan_number,
            'borrower': loan.borrower.get_full_name(),
            'principal': loan.principal_amount,
            'interest': loan.interest_amount,
            'processing_fee': loan.processing_fee,
            'stored_total': loan.total_amount,
            'correct_total': correct_total,
            'difference': difference
        })
    
    print()
    print("-" * 80)
    print()

# Summary
print("=" * 80)
print("SUMMARY")
print("=" * 80)
print(f"Total Mwamba Loans: {mwamba_loans.count()}")
print(f"✅ Correct: {len(correct_loans)}")
print(f"❌ Incorrect: {len(incorrect_loans)}")
print()

if incorrect_loans:
    print("INCORRECT LOANS DETAILS:")
    print()
    for loan_info in incorrect_loans:
        print(f"  {loan_info['loan_number']} - {loan_info['borrower']}")
        print(f"    Principal: KES {loan_info['principal']:,.2f}")
        print(f"    Interest: KES {loan_info['interest']:,.2f}")
        print(f"    Processing Fee: KES {loan_info['processing_fee']:,.2f}")
        print(f"    Stored Total: KES {loan_info['stored_total']:,.2f} ❌")
        print(f"    Correct Total: KES {loan_info['correct_total']:,.2f} ✅")
        print(f"    Difference: +KES {loan_info['difference']:,.2f}")
        print()
    
    print("=" * 80)
    print("RECOMMENDATION")
    print("=" * 80)
    print()
    print("To fix these loans, run:")
    print("  python manage.py fix_loan_totals")
    print()
    print("Or execute this SQL:")
    print()
    print("  UPDATE loans")
    print("  SET total_amount = principal_amount + interest_amount + processing_fee")
    print("  WHERE total_amount != (principal_amount + interest_amount + processing_fee);")
    print()
else:
    print("✅ All Mwamba loans have correct totals!")

print("=" * 80)