"""
Diagnose and fix the Mwamba loan LOAN-000131 calculation issue.

The issue is that the total_amount stored in the database doesn't match
the sum of principal_amount + interest_amount + processing_fee.
"""

import os
import django
from decimal import Decimal

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

from loans.models import Loan, LoanApplication
from django.db.models import Sum

def diagnose_and_fix_loan_131():
    """Diagnose and fix LOAN-000131"""
    
    print("=" * 80)
    print("DIAGNOSING MWAMBA LOAN LOAN-000131")
    print("=" * 80)
    
    try:
        loan = Loan.objects.get(loan_number='LOAN-000131')
    except Loan.DoesNotExist:
        print("\n✗ Loan LOAN-000131 not found!")
        return
    
    print(f"\nLoan Details:")
    print(f"  Loan Number: {loan.loan_number}")
    print(f"  Borrower: {loan.borrower.get_full_name()}")
    print(f"  Product: {loan.application.loan_product.name}")
    print(f"  Status: {loan.status}")
    print(f"  Duration: {loan.duration_days} days")
    
    print(f"\n" + "-" * 80)
    print("CURRENT DATABASE VALUES")
    print("-" * 80)
    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}")
    
    # Calculate what it should be
    correct_total = loan.principal_amount + loan.interest_amount + loan.processing_fee
    
    print(f"\n" + "-" * 80)
    print("CORRECT CALCULATION")
    print("-" * 80)
    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"  = Total: KES {correct_total:,.2f}")
    
    # Check if there's a mismatch
    if abs(loan.total_amount - correct_total) > Decimal('0.01'):
        print(f"\n⚠️  MISMATCH DETECTED!")
        print(f"  Stored Total: KES {loan.total_amount:,.2f}")
        print(f"  Correct Total: KES {correct_total:,.2f}")
        print(f"  Difference: KES {correct_total - loan.total_amount:,.2f}")
    else:
        print(f"\n✓ Total amount is correct!")
    
    # Check repayments
    print(f"\n" + "-" * 80)
    print("REPAYMENT ANALYSIS")
    print("-" * 80)
    
    repayments = loan.repayments.all().order_by('payment_date')
    total_paid = repayments.aggregate(total=Sum('amount'))['total'] or Decimal('0.00')
    
    print(f"  Number of Repayments: {repayments.count()}")
    print(f"  Total Amount Paid: KES {total_paid:,.2f}")
    
    for repayment in repayments:
        print(f"    - {repayment.payment_date.strftime('%Y-%m-%d')}: KES {repayment.amount:,.2f} ({repayment.payment_method})")
    
    # Calculate outstanding with correct total
    correct_outstanding = correct_total - total_paid
    stored_outstanding = loan.total_amount - total_paid
    
    print(f"\n" + "-" * 80)
    print("OUTSTANDING BALANCE")
    print("-" * 80)
    print(f"  With Stored Total: KES {stored_outstanding:,.2f}")
    print(f"  With Correct Total: KES {correct_outstanding:,.2f}")
    print(f"  Difference: KES {correct_outstanding - stored_outstanding:,.2f}")
    
    # Check if we need to fix
    if abs(loan.total_amount - correct_total) > Decimal('0.01'):
        print(f"\n" + "=" * 80)
        print("FIX REQUIRED")
        print("=" * 80)
        
        response = input(f"\nDo you want to fix the total_amount from KES {loan.total_amount:,.2f} to KES {correct_total:,.2f}? (yes/no): ")
        
        if response.lower() in ['yes', 'y']:
            # Update the loan
            old_total = loan.total_amount
            loan.total_amount = correct_total
            loan.save()
            
            print(f"\n✓ Fixed!")
            print(f"  Old Total: KES {old_total:,.2f}")
            print(f"  New Total: KES {correct_total:,.2f}")
            print(f"  New Outstanding: KES {correct_outstanding:,.2f}")
            
            # Also check and fix the application if needed
            application = loan.application
            if abs(application.total_amount - correct_total) > Decimal('0.01'):
                print(f"\n  Also fixing application total_amount...")
                application.total_amount = correct_total
                application.save()
                print(f"  ✓ Application fixed!")
        else:
            print(f"\n  Fix cancelled.")
    else:
        print(f"\n✓ No fix needed - total_amount is correct!")
    
    print(f"\n" + "=" * 80)
    print("DIAGNOSIS COMPLETE")
    print("=" * 80)


def check_all_mwamba_loans():
    """Check all Mwamba loans for similar issues"""
    
    print("\n" + "=" * 80)
    print("CHECKING ALL MWAMBA LOANS")
    print("=" * 80)
    
    from loans.models import LoanProduct
    
    try:
        mwamba = LoanProduct.objects.get(product_type='mwamba')
    except LoanProduct.DoesNotExist:
        print("\n✗ Mwamba product not found!")
        return
    
    # Get all Mwamba loans
    mwamba_loans = Loan.objects.filter(
        application__loan_product=mwamba
    ).select_related('borrower', 'application')
    
    print(f"\nFound {mwamba_loans.count()} Mwamba loans")
    
    issues_found = []
    
    for loan in mwamba_loans:
        correct_total = loan.principal_amount + loan.interest_amount + loan.processing_fee
        
        if abs(loan.total_amount - correct_total) > Decimal('0.01'):
            issues_found.append({
                'loan': loan,
                'stored_total': loan.total_amount,
                'correct_total': correct_total,
                'difference': correct_total - loan.total_amount
            })
    
    if issues_found:
        print(f"\n⚠️  Found {len(issues_found)} loans with incorrect total_amount:")
        print(f"\n{'Loan Number':<15} {'Borrower':<25} {'Stored Total':<15} {'Correct Total':<15} {'Difference':<15}")
        print("-" * 90)
        
        for issue in issues_found:
            loan = issue['loan']
            print(f"{loan.loan_number:<15} {loan.borrower.get_full_name():<25} "
                  f"KES {issue['stored_total']:>10,.2f} KES {issue['correct_total']:>10,.2f} "
                  f"KES {issue['difference']:>10,.2f}")
        
        response = input(f"\nDo you want to fix all {len(issues_found)} loans? (yes/no): ")
        
        if response.lower() in ['yes', 'y']:
            fixed_count = 0
            for issue in issues_found:
                loan = issue['loan']
                loan.total_amount = issue['correct_total']
                loan.save()
                
                # Also fix application
                application = loan.application
                if abs(application.total_amount - issue['correct_total']) > Decimal('0.01'):
                    application.total_amount = issue['correct_total']
                    application.save()
                
                fixed_count += 1
            
            print(f"\n✓ Fixed {fixed_count} loans!")
        else:
            print(f"\n  Fix cancelled.")
    else:
        print(f"\n✓ All Mwamba loans have correct total_amount!")


if __name__ == '__main__':
    try:
        # First diagnose the specific loan
        diagnose_and_fix_loan_131()
        
        # Then check all Mwamba loans
        check_all_mwamba_loans()
        
    except Exception as e:
        print(f"\n✗ Error: {str(e)}")
        import traceback
        traceback.print_exc()
