-- ============================================================================
-- Fix Mwamba Loan Calculation Issue
-- ============================================================================
-- This script fixes loans where total_amount doesn't equal the sum of
-- principal_amount + interest_amount + processing_fee
--
-- Issue: LOAN-000131 has total_amount = 22,400 when it should be 27,000
-- ============================================================================

-- Step 1: Check LOAN-000131 specifically
SELECT 
    '=== LOAN-000131 DIAGNOSIS ===' as info;

SELECT 
    l.loan_number,
    CONCAT(u.first_name, ' ', u.last_name) as borrower,
    lp.name as product,
    l.principal_amount as principal,
    l.interest_amount as interest,
    l.processing_fee as processing_fee,
    l.total_amount as stored_total,
    (l.principal_amount + l.interest_amount + l.processing_fee) as correct_total,
    (l.principal_amount + l.interest_amount + l.processing_fee - l.total_amount) as difference,
    (SELECT COALESCE(SUM(amount), 0) FROM repayments WHERE loan_id = l.id) as amount_paid,
    (l.total_amount - (SELECT COALESCE(SUM(amount), 0) FROM repayments WHERE loan_id = l.id)) as stored_outstanding,
    ((l.principal_amount + l.interest_amount + l.processing_fee) - (SELECT COALESCE(SUM(amount), 0) FROM repayments WHERE loan_id = l.id)) as correct_outstanding
FROM loans l
INNER JOIN loan_applications la ON l.application_id = la.id
INNER JOIN loan_products lp ON la.loan_product_id = lp.id
INNER JOIN users u ON l.borrower_id = u.id
WHERE l.loan_number = 'LOAN-000131';

-- Step 2: Check all loans with incorrect total_amount
SELECT 
    '=== ALL LOANS WITH INCORRECT TOTALS ===' as info;

SELECT 
    l.loan_number,
    CONCAT(u.first_name, ' ', u.last_name) as borrower,
    lp.name as product,
    l.principal_amount,
    l.interest_amount,
    l.processing_fee,
    l.total_amount as stored_total,
    (l.principal_amount + l.interest_amount + l.processing_fee) as correct_total,
    (l.principal_amount + l.interest_amount + l.processing_fee - l.total_amount) as difference
FROM loans l
INNER JOIN loan_applications la ON l.application_id = la.id
INNER JOIN loan_products lp ON la.loan_product_id = lp.id
INNER JOIN users u ON l.borrower_id = u.id
WHERE ABS(l.total_amount - (l.principal_amount + l.interest_amount + l.processing_fee)) > 0.01
ORDER BY l.created_at DESC;

-- Step 3: Fix LOAN-000131
SELECT 
    '=== FIXING LOAN-000131 ===' as info;

UPDATE loans 
SET total_amount = principal_amount + interest_amount + processing_fee
WHERE loan_number = 'LOAN-000131'
AND ABS(total_amount - (principal_amount + interest_amount + processing_fee)) > 0.01;

-- Step 4: Fix the corresponding application
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 l.loan_number = 'LOAN-000131'
AND ABS(la.total_amount - (l.principal_amount + l.interest_amount + l.processing_fee)) > 0.01;

-- Step 5: Fix all other loans with incorrect total_amount
SELECT 
    '=== FIXING ALL LOANS WITH INCORRECT TOTALS ===' as info;

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

-- Step 6: Fix all corresponding applications
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;

-- Step 7: Verify LOAN-000131 after fix
SELECT 
    '=== VERIFICATION: LOAN-000131 AFTER FIX ===' as info;

SELECT 
    l.loan_number,
    CONCAT(u.first_name, ' ', u.last_name) as borrower,
    lp.name as product,
    l.principal_amount as principal,
    l.interest_amount as interest,
    l.processing_fee as processing_fee,
    l.total_amount as total,
    (l.principal_amount + l.interest_amount + l.processing_fee) as calculated_total,
    CASE 
        WHEN ABS(l.total_amount - (l.principal_amount + l.interest_amount + l.processing_fee)) < 0.01 
        THEN '✓ CORRECT' 
        ELSE '✗ STILL WRONG' 
    END as status,
    (SELECT COALESCE(SUM(amount), 0) FROM repayments WHERE loan_id = l.id) as amount_paid,
    (l.total_amount - (SELECT COALESCE(SUM(amount), 0) FROM repayments WHERE loan_id = l.id)) as outstanding_balance
FROM loans l
INNER JOIN loan_applications la ON l.application_id = la.id
INNER JOIN loan_products lp ON la.loan_product_id = lp.id
INNER JOIN users u ON l.borrower_id = u.id
WHERE l.loan_number = 'LOAN-000131';

-- Step 8: Verify all loans are now correct
SELECT 
    '=== FINAL VERIFICATION: ALL LOANS ===' as info;

SELECT 
    COUNT(*) as total_loans,
    SUM(CASE WHEN ABS(l.total_amount - (l.principal_amount + l.interest_amount + l.processing_fee)) < 0.01 THEN 1 ELSE 0 END) as correct_loans,
    SUM(CASE WHEN ABS(l.total_amount - (l.principal_amount + l.interest_amount + l.processing_fee)) >= 0.01 THEN 1 ELSE 0 END) as incorrect_loans
FROM loans l;

-- Step 9: Show summary of changes
SELECT 
    '=== SUMMARY OF CHANGES ===' as info;

SELECT 
    'Loans Fixed' as metric,
    COUNT(*) as count
FROM loans 
WHERE ABS(total_amount - (principal_amount + interest_amount + processing_fee)) < 0.01;

-- ============================================================================
-- EXPECTED RESULTS FOR LOAN-000131:
-- ============================================================================
-- Principal Amount: 20,000.00
-- Interest Amount: 6,000.00
-- Processing Fee: 1,000.00
-- Total Amount: 27,000.00 (was 22,400.00)
-- Amount Paid: 3,200.00
-- Outstanding Balance: 23,800.00 (was 19,200.00)
-- ============================================================================