"""
Production Loan Calculation Investigation Script
This script will investigate all calculations for a specific loan showing 56,000 total
Run this on production and send me the output
"""
import os
import sys

# Set production database credentials
os.environ['DB_NAME'] = 'xygbfpsg_graz'
os.environ['DB_USER'] = 'xygbfpsg_graz'
os.environ['DB_PASSWORD'] = ',qdN3O_!}oC67(]W'
os.environ['DB_HOST'] = 'localhost'
os.environ['DB_PORT'] = '3306'

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

from loans.models import Loan, LoanApplication, LoanProduct
from decimal import Decimal
from django.contrib.auth import get_user_model
User = get_user_model()

print(f"\n{'='*80}")
print(f"PRODUCTION LOAN CALCULATION INVESTIGATION")
print(f"{'='*80}\n")

try:
    # Search for the loan with 56,000 total
    print("Searching for loan with total_amount = 56,000...")
    loans_56k = Loan.objects.filter(total_amount=56000).select_related(
        'borrower', 'application', 'application__loan_product'
    )

    if loans_56k.exists():
        for loan in loans_56k:
            print(f"\n{'='*80}")
            print(f"FOUND LOAN: {loan.loan_number}")
            print(f"{'='*80}\n")
            
            print(f"BASIC INFO:")
            print(f"  Loan Number: {loan.loan_number}")
            print(f"  Borrower: {loan.borrower.get_full_name()}")
            print(f"  Phone: {loan.borrower.phone_number}")
            print(f"  Status: {loan.status}")
            print(f"  Created: {loan.created_at}")
            print(f"  Disbursement Date: {loan.disbursement_date}")
            print(f"  Due Date: {loan.due_date}")
            
            print(f"\nLOAN PRODUCT:")
            print(f"  Name: {loan.application.loan_product.name}")
            print(f"  Type: {loan.application.loan_product.product_type}")
            print(f"  Interest Rate (Model): {loan.application.loan_product.interest_rate}%")
            print(f"  Processing Fee (Model): {loan.application.loan_product.processing_fee}%")
            print(f"  Interest Rate (Settings): {loan.application.loan_product.get_interest_rate()}%")
            print(f"  Processing Fee (Settings): {loan.application.loan_product.get_processing_fee()}%")
            
            print(f"\nSTORED VALUES IN DATABASE:")
            print(f"  Principal Amount:    KES {loan.principal_amount:>15,.2f}")
            print(f"  Interest Amount:     KES {loan.interest_amount:>15,.2f}")
            print(f"  Processing Fee:      KES {loan.processing_fee:>15,.2f}")
            print(f"  Total Amount:        KES {loan.total_amount:>15,.2f}")
            print(f"  Duration:            {loan.duration_days:>15} days")
            
            # Calculate months
            months = loan.duration_days / 30
            print(f"  Duration (months):   {months:>15.2f} months")
            
            print(f"\nCALCULATION BREAKDOWN:")
            print(f"  Step 1: Calculate Interest")
            print(f"    Formula: Principal × Interest Rate × Months")
            
            # Method 1: Using stored interest rate
            interest_calc_1 = loan.principal_amount * (loan.application.loan_product.interest_rate / Decimal('100')) * Decimal(str(months))
            print(f"    Using Model Rate: {loan.principal_amount:,.2f} × {loan.application.loan_product.interest_rate}% × {months:.2f}")
            print(f"    = KES {interest_calc_1:,.2f}")
            
            # Method 2: Using system settings
            interest_calc_2 = loan.principal_amount * (Decimal(str(loan.application.loan_product.get_interest_rate())) / Decimal('100')) * Decimal(str(months))
            print(f"    Using Settings Rate: {loan.principal_amount:,.2f} × {loan.application.loan_product.get_interest_rate()}% × {months:.2f}")
            print(f"    = KES {interest_calc_2:,.2f}")
            
            # Method 3: Using product method
            interest_calc_3 = loan.application.loan_product.calculate_interest(loan.principal_amount, months)
            print(f"    Using Product Method: KES {interest_calc_3:,.2f}")
            
            # Method 4: Using display method
            interest_calc_4 = loan.get_display_interest_amount()
            print(f"    Using Display Method: KES {interest_calc_4:,.2f}")
            
            print(f"\n  Step 2: Calculate Processing Fee")
            print(f"    Formula: Principal × Processing Fee Rate")
            
            # Check if it's Boost Plus (monthly processing fee)
            is_boost_plus = loan.application.loan_product.product_type == 'boost_plus'
            if is_boost_plus:
                print(f"    NOTE: This is Boost Plus - processing fee is charged MONTHLY")
            
            # Method 1: Using stored rate
            processing_fee_calc_1 = loan.principal_amount * (loan.application.loan_product.processing_fee / Decimal('100'))
            if is_boost_plus:
                processing_fee_calc_1 *= Decimal(str(months))
            print(f"    Using Model Rate: {loan.principal_amount:,.2f} × {loan.application.loan_product.processing_fee}%")
            if is_boost_plus:
                print(f"    × {months:.2f} months (Boost Plus)")
            print(f"    = KES {processing_fee_calc_1:,.2f}")
            
            # Method 2: Using system settings
            processing_fee_calc_2 = loan.principal_amount * (Decimal(str(loan.application.loan_product.get_processing_fee())) / Decimal('100'))
            if is_boost_plus:
                processing_fee_calc_2 *= Decimal(str(months))
            print(f"    Using Settings Rate: {loan.principal_amount:,.2f} × {loan.application.loan_product.get_processing_fee()}%")
            if is_boost_plus:
                print(f"    × {months:.2f} months (Boost Plus)")
            print(f"    = KES {processing_fee_calc_2:,.2f}")
            
            # Method 3: Using product method
            processing_fee_calc_3 = loan.application.loan_product.calculate_processing_fee(loan.principal_amount, months)
            print(f"    Using Product Method: KES {processing_fee_calc_3:,.2f}")
            
            # Method 4: Using display method
            processing_fee_calc_4 = loan.get_display_processing_fee_amount()
            print(f"    Using Display Method: KES {processing_fee_calc_4:,.2f}")
            
            print(f"\n  Step 3: Calculate Total")
            print(f"    Formula: Principal + Interest + Processing Fee")
            
            # Calculate all possible totals
            total_calc_1 = loan.principal_amount + interest_calc_1 + processing_fee_calc_1
            total_calc_2 = loan.principal_amount + interest_calc_2 + processing_fee_calc_2
            total_calc_3 = loan.principal_amount + interest_calc_3 + processing_fee_calc_3
            total_calc_4 = loan.principal_amount + interest_calc_4 + processing_fee_calc_4
            
            print(f"    Using Model Rates: {loan.principal_amount:,.2f} + {interest_calc_1:,.2f} + {processing_fee_calc_1:,.2f}")
            print(f"    = KES {total_calc_1:,.2f}")
            
            print(f"    Using Settings Rates: {loan.principal_amount:,.2f} + {interest_calc_2:,.2f} + {processing_fee_calc_2:,.2f}")
            print(f"    = KES {total_calc_2:,.2f}")
            
            print(f"    Using Product Methods: {loan.principal_amount:,.2f} + {interest_calc_3:,.2f} + {processing_fee_calc_3:,.2f}")
            print(f"    = KES {total_calc_3:,.2f}")
            
            print(f"    Using Display Methods: {loan.principal_amount:,.2f} + {interest_calc_4:,.2f} + {processing_fee_calc_4:,.2f}")
            print(f"    = KES {total_calc_4:,.2f}")
            
            print(f"\nCOMPARISON:")
            print(f"  Database Total:           KES {loan.total_amount:>15,.2f}")
            print(f"  Correct Total (Method 1): KES {total_calc_1:>15,.2f}  Diff: {total_calc_1 - loan.total_amount:>10,.2f}")
            print(f"  Correct Total (Method 2): KES {total_calc_2:>15,.2f}  Diff: {total_calc_2 - loan.total_amount:>10,.2f}")
            print(f"  Correct Total (Method 3): KES {total_calc_3:>15,.2f}  Diff: {total_calc_3 - loan.total_amount:>10,.2f}")
            print(f"  Correct Total (Method 4): KES {total_calc_4:>15,.2f}  Diff: {total_calc_4 - loan.total_amount:>10,.2f}")
            
            # Identify the issue
            print(f"\nISSUE ANALYSIS:")
            
            # Check if interest is wrong
            if abs(loan.interest_amount - interest_calc_3) > Decimal('0.01'):
                print(f"  ❌ Interest amount is INCORRECT")
                print(f"     Stored: KES {loan.interest_amount:,.2f}")
                print(f"     Should be: KES {interest_calc_3:,.2f}")
                print(f"     Difference: KES {interest_calc_3 - loan.interest_amount:,.2f}")
            else:
                print(f"  ✅ Interest amount is correct")
            
            # Check if processing fee is wrong
            if abs(loan.processing_fee - processing_fee_calc_3) > Decimal('0.01'):
                print(f"  ❌ Processing fee is INCORRECT")
                print(f"     Stored: KES {loan.processing_fee:,.2f}")
                print(f"     Should be: KES {processing_fee_calc_3:,.2f}")
                print(f"     Difference: KES {processing_fee_calc_3 - loan.processing_fee:,.2f}")
            else:
                print(f"  ✅ Processing fee is correct")
            
            # Check if total is wrong
            correct_total = loan.principal_amount + loan.interest_amount + loan.processing_fee
            if abs(loan.total_amount - correct_total) > Decimal('0.01'):
                print(f"  ❌ Total amount calculation is INCORRECT")
                print(f"     Stored: KES {loan.total_amount:,.2f}")
                print(f"     Should be (using stored values): KES {correct_total:,.2f}")
                print(f"     Difference: KES {correct_total - loan.total_amount:,.2f}")
                print(f"     This means: total_amount ≠ principal + interest + processing_fee")
            else:
                print(f"  ✅ Total amount calculation is correct (principal + interest + processing_fee)")
            
            # Check application values
            print(f"\nLOAN APPLICATION VALUES:")
            print(f"  Application Number: {loan.application.application_number}")
            print(f"  Requested Amount:   KES {loan.application.requested_amount:,.2f}")
            print(f"  Requested Duration: {loan.application.requested_duration} days")
            print(f"  Interest Amount:    KES {loan.application.interest_amount:,.2f}")
            print(f"  Processing Fee:     KES {loan.application.processing_fee_amount:,.2f}")
            print(f"  Total Amount:       KES {loan.application.total_amount:,.2f}")
            
            # Check if application values match loan values
            if loan.application.requested_amount != loan.principal_amount:
                print(f"  ⚠️  Application amount ≠ Loan principal")
            if loan.application.interest_amount != loan.interest_amount:
                print(f"  ⚠️  Application interest ≠ Loan interest")
            if loan.application.processing_fee_amount != loan.processing_fee:
                print(f"  ⚠️  Application processing fee ≠ Loan processing fee")
            if loan.application.total_amount != loan.total_amount:
                print(f"  ⚠️  Application total ≠ Loan total")
            
            print(f"\n{'='*80}\n")

    else:
        print("\n❌ No loan found with total_amount = 56,000")
        print("\nSearching for loans with principal = 50,000...")
        
        loans_50k = Loan.objects.filter(principal_amount=50000).select_related(
            'borrower', 'application', 'application__loan_product'
        ).order_by('-created_at')[:10]
        
        if loans_50k.exists():
            print(f"\nFound {loans_50k.count()} recent loan(s) with principal = 50,000:\n")
            for loan in loans_50k:
                correct_total = loan.principal_amount + loan.interest_amount + loan.processing_fee
                print(f"  {loan.loan_number} | {loan.borrower.get_full_name()}")
                print(f"    Product: {loan.application.loan_product.name}")
                print(f"    Duration: {loan.duration_days} days")
                print(f"    Principal: {loan.principal_amount:,.2f}")
                print(f"    Interest: {loan.interest_amount:,.2f}")
                print(f"    Processing Fee: {loan.processing_fee:,.2f}")
                print(f"    Total (DB): {loan.total_amount:,.2f}")
                print(f"    Total (Calc): {correct_total:,.2f}")
                if abs(loan.total_amount - correct_total) > Decimal('0.01'):
                    print(f"    ❌ MISMATCH: {correct_total - loan.total_amount:,.2f}")
                else:
                    print(f"    ✅ Correct")
                print()
        else:
            print("\n❌ No loans found with principal = 50,000")
            print("\nSearching for ALL recent loans...")
            
            all_loans = Loan.objects.all().select_related(
                'borrower', 'application', 'application__loan_product'
            ).order_by('-created_at')[:10]
            
            print(f"\nFound {all_loans.count()} recent loan(s):\n")
            for loan in all_loans:
                correct_total = loan.principal_amount + loan.interest_amount + loan.processing_fee
                print(f"  {loan.loan_number} | {loan.borrower.get_full_name()}")
                print(f"    Product: {loan.application.loan_product.name}")
                print(f"    Duration: {loan.duration_days} days")
                print(f"    Principal: {loan.principal_amount:,.2f}")
                print(f"    Total (DB): {loan.total_amount:,.2f}")
                print(f"    Total (Calc): {correct_total:,.2f}")
                if abs(loan.total_amount - correct_total) > Decimal('0.01'):
                    print(f"    ❌ MISMATCH: {correct_total - loan.total_amount:,.2f}")
                print()

except Exception as e:
    print(f"\n❌ ERROR: {str(e)}")
    import traceback
    traceback.print_exc()
    sys.exit(1)

print(f"\n{'='*80}")
print(f"INVESTIGATION COMPLETE")
print(f"{'='*80}\n")
