================================================================================
MWAMBA LOAN CALCULATION FIX - QUICK REFERENCE
================================================================================

PROBLEM:
--------
Loan LOAN-000131 shows:
  Interest: KES 2,000 (WRONG - should be 6,000 for 90 days)
  Processing Fee: KES 400 (WRONG - should be 1,000)
  Total Amount: KES 22,400 (WRONG - should be 27,000)
  Outstanding: KES 19,200 (WRONG - should be 23,800)

ROOT CAUSE:
-----------
The loan was created with WRONG interest and processing fee rates:
  - Interest Rate Used: ~3.33% per month (should be 10%)
  - Processing Fee Used: 2% (should be 5%)

QUICK FIX (Copy & Paste into MySQL/phpMyAdmin):
------------------------------------------------

-- Fix LOAN-000131 with correct rates
UPDATE loans
SET 
    interest_amount = principal_amount * 0.10 * (duration_days / 30),
    processing_fee = principal_amount * 0.05,
    total_amount = principal_amount + (principal_amount * 0.10 * (duration_days / 30)) + (principal_amount * 0.05)
WHERE loan_number = 'LOAN-000131';

-- Fix the application too
UPDATE loan_applications la
INNER JOIN loans l ON l.application_id = la.id
SET 
    la.interest_amount = l.interest_amount,
    la.processing_fee_amount = l.processing_fee,
    la.total_amount = l.total_amount
WHERE l.loan_number = 'LOAN-000131';

-- Verify fix
SELECT 
    loan_number,
    principal_amount,
    interest_amount,
    processing_fee,
    total_amount,
    (SELECT SUM(amount) FROM repayments WHERE loan_id = loans.id) as paid,
    (total_amount - (SELECT COALESCE(SUM(amount), 0) FROM repayments WHERE loan_id = loans.id)) as outstanding
FROM loans
WHERE loan_number = 'LOAN-000131';

EXPECTED RESULT:
----------------
interest_amount = 6000.00 (was 2000.00)
processing_fee = 1000.00 (was 400.00)
total_amount = 27000.00 (was 22400.00)
outstanding = 23800.00 (was 19200.00)

FIX ALL AFFECTED LOANS:
-----------------------

-- Check how many loans are affected
SELECT COUNT(*) FROM loans 
WHERE ABS(total_amount - (principal_amount + interest_amount + processing_fee)) > 0.01;

-- Fix all
UPDATE loans 
SET total_amount = principal_amount + interest_amount + processing_fee
WHERE ABS(total_amount - (principal_amount + interest_amount + processing_fee)) > 0.01;

UPDATE loan_applications la
INNER JOIN loans l ON l.application_id = la.id
SET la.total_amount = l.principal_amount + l.interest_amount + l.processing_fee
WHERE ABS(la.total_amount - (l.principal_amount + l.interest_amount + l.processing_fee)) > 0.01;

================================================================================
DONE! Refresh the loan page to see correct amounts.
================================================================================
