"""
Test script to diagnose Mwamba loan total amount calculation issue

This script will:
1. Connect to the database
2. Query Mwamba loans (LOAN-000193 and LOAN-000195)
3. Show the stored values vs calculated values
4. Identify where the calculation went wrong
5. Optionally fix the issues

Usage:
    python test_mwamba_loan_issue.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

def test_mwamba_loans():
    print("=" * 80)
    print("MWAMBA LOAN TOTAL AMOUNT DIAGNOSTIC TEST")
    print("=" * 80)
    print()
    
    # Get all loans
    all_loans = Loan.objects.all()
    print(f"📊 Total loans in database: {all_loans.count()}")
    
    if all_loans.count() == 0:
        print("❌ No loans found in database!")
        print()
        print("Possible reasons:")
        print("1. Database is empty")
        print("2. Database connection issue")
        print("3. Wrong database selected")
        print()
        print("Current database settings:")
        from django.conf import settings
        db_settings = settings.DATABASES['default']
        print(f"   Engine: {db_settings['ENGINE']}")
        print(f"   Name: {db_settings['NAME']}")
        print(f"   Host: {db_settings['HOST']}")
        print(f"   Port: {db_settings['PORT']}")
        print(f"   User: {db_settings['USER']}")
        return
    
    print()
    print("=" * 80)
    print("CHECKING SPECIFIC MWAMBA LOANS")
    print("=" * 80)
    print()
    
    # Check specific loans mentioned
    problem_loans = ['LOAN-000193', 'LOAN-000195']
    
    for loan_number in problem_loans:
        try:
            loan = Loan.objects.get(loan_number=loan_number)
            print(f"🔍 Analyzing {loan_number}")
            print(f"   Borrower: {loan.borrower.get_full_name()}")
            print(f"   Product: {loan.application.loan_product.name}")
            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 (stored): KES {loan.total_amount:,.2f}")
            print()
            
            # Calculate correct values
            correct_total = loan.principal_amount + loan.interest_amount + loan.processing_fee
            print("   ✅ CALCULATED VALUES:")
            print(f"      Principal: KES {loan.principal_amount:,.2f}")
            print(f"      + Interest: KES {loan.interest_amount:,.2f}")
            print(f"      + Processing Fee: KES {loan.processing_fee:,.2f}")
            print(f"      = Correct Total: KES {correct_total:,.2f}")
            print()
            
            # Compare
            if loan.total_amount != correct_total:
                difference = correct_total - loan.total_amount
                print(f"   ❌ MISMATCH DETECTED!")
                print(f"      Stored Total: KES {loan.total_amount:,.2f}")
                print(f"      Correct Total: KES {correct_total:,.2f}")
                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.interest_amount - loan.processing_fee)
                
                print("   🔎 ANALYZING WRONG CALCULATION:")
                if loan.total_amount == wrong_calc_1:
                    print(f"      ⚠️ Total = Principal + Interest (missing processing fee)")
                    print(f"      Formula used: {loan.principal_amount:,.2f} + {loan.interest_amount:,.2f} = {wrong_calc_1:,.2f}")
                elif loan.total_amount == wrong_calc_2:
                    print(f"      ⚠️ Total = Principal + Interest - Processing Fee (subtracting instead of adding)")
                    print(f"      Formula used: {loan.principal_amount:,.2f} + {loan.interest_amount:,.2f} - {loan.processing_fee:,.2f} = {wrong_calc_2:,.2f}")
                elif loan.total_amount == wrong_calc_3:
                    print(f"      ⚠️ Total = Principal + (Interest - Processing Fee)")
                    print(f"      Formula used: {loan.principal_amount:,.2f} + ({loan.interest_amount:,.2f} - {loan.processing_fee:,.2f}) = {wrong_calc_3:,.2f}")
                else:
                    print(f"      ❓ Unknown calculation method")
                    print(f"      Stored value doesn't match any common wrong formula")
                
            else:
                print(f"   ✅ CORRECT! Total amount is properly calculated.")
            
            print()
            print("-" * 80)
            print()
            
        except Loan.DoesNotExist:
            print(f"❌ {loan_number} not found in database")
            print()
    
    # Check all Mwamba loans
    print("=" * 80)
    print("CHECKING ALL MWAMBA LOANS")
    print("=" * 80)
    print()
    
    try:
        mwamba_product = LoanProduct.objects.get(product_type='mwamba')
        mwamba_loans = Loan.objects.filter(application__loan_product=mwamba_product)
        
        print(f"Found {mwamba_loans.count()} Mwamba loan(s)")
        print()
        
        incorrect_count = 0
        for loan in mwamba_loans:
            correct_total = loan.principal_amount + loan.interest_amount + loan.processing_fee
            if loan.total_amount != correct_total:
                incorrect_count += 1
                difference = correct_total - loan.total_amount
                print(f"❌ {loan.loan_number}: KES {loan.total_amount:,.2f} (should be {correct_total:,.2f}, diff: {difference:,.2f})")
        
        if incorrect_count == 0:
            print("✅ All Mwamba loans have correct totals!")
        else:
            print()
            print(f"Found {incorrect_count} Mwamba loan(s) with incorrect totals")
            
    except LoanProduct.DoesNotExist:
        print("❌ Mwamba loan product not found in database")
    
    print()
    print("=" * 80)
    print("SUMMARY")
    print("=" * 80)
    print()
    
    # Count all incorrect loans
    all_incorrect = []
    for loan in all_loans:
        correct_total = loan.principal_amount + loan.interest_amount + loan.processing_fee
        if loan.total_amount != correct_total:
            all_incorrect.append({
                'loan_number': loan.loan_number,
                'product': loan.application.loan_product.name,
                'stored': loan.total_amount,
                'correct': correct_total,
                'difference': correct_total - loan.total_amount
            })
    
    if all_incorrect:
        print(f"❌ Found {len(all_incorrect)} loan(s) with incorrect totals:")
        print()
        for loan_info in all_incorrect:
            print(f"   {loan_info['loan_number']} ({loan_info['product']})")
            print(f"      Stored: KES {loan_info['stored']:,.2f}")
            print(f"      Correct: KES {loan_info['correct']:,.2f}")
            print(f"      Difference: KES {loan_info['difference']:,.2f}")
            print()
        
        # Ask if user wants to fix
        print("=" * 80)
        response = input("Do you want to fix these loans? (yes/no): ").strip().lower()
        
        if response in ['yes', 'y']:
            print()
            print("Fixing loans...")
            fixed_count = 0
            for loan_info in all_incorrect:
                try:
                    loan = Loan.objects.get(loan_number=loan_info['loan_number'])
                    loan.total_amount = loan_info['correct']
                    loan.save()
                    print(f"✅ Fixed {loan_info['loan_number']}")
                    fixed_count += 1
                except Exception as e:
                    print(f"❌ Error fixing {loan_info['loan_number']}: {e}")
            
            print()
            print(f"✅ Successfully fixed {fixed_count} loan(s)!")
        else:
            print()
            print("No changes made. Run this script again to fix the loans.")
    else:
        print("✅ All loans have correct total amounts!")
    
    print()
    print("=" * 80)
    print("TEST COMPLETE")
    print("=" * 80)

if __name__ == '__main__':
    test_mwamba_loans()