-- ============================================================================
-- MWAMBA LOAN TOTAL AMOUNT DIAGNOSTIC AND FIX SCRIPT
-- ============================================================================
-- This script will:
-- 1. Show all loans with incorrect totals
-- 2. Show specific Mwamba loans (LOAN-000193, LOAN-000195)
-- 3. Fix all incorrect totals
-- ============================================================================

-- First, check if we're in the right database
SELECT DATABASE() as current_database;

-- Count total loans
SELECT COUNT(*) as total_loans FROM loans;

-- ============================================================================
-- PART 1: SHOW ALL LOANS WITH INCORRECT TOTALS
-- ============================================================================
SELECT 
    '=== LOANS WITH INCORRECT TOTALS ===' as section;

SELECT 
    l.loan_number,
    CONCAT(u.first_name, ' ', u.last_name) as borrower_name,
    lp.name as product_name,
    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,
    CASE 
        WHEN l.total_amount = (l.principal_amount + l.interest_amount) THEN 'Missing processing fee'
        WHEN l.total_amount = (l.principal_amount + l.interest_amount - l.processing_fee) THEN 'Subtracting processing fee instead of adding'
        ELSE 'Unknown calculation error'
    END as error_type
FROM loans l
JOIN users_customuser u ON l.borrower_id = u.id
JOIN loan_applications la ON l.application_id = la.id
JOIN loan_products lp ON la.loan_product_id = lp.id
WHERE l.total_amount != (l.principal_amount + l.interest_amount + l.processing_fee)
ORDER BY l.loan_number;

-- ============================================================================
-- PART 2: DETAILED ANALYSIS OF SPECIFIC MWAMBA LOANS
-- ============================================================================
SELECT 
    '=== LOAN-000193 ANALYSIS ===' as section;

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,
    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,
    CONCAT('Principal (', l.principal_amount, ') + Interest (', l.interest_amount, ') + Fee (', l.processing_fee, ') = ', 
           (l.principal_amount + l.interest_amount + l.processing_fee)) as calculation
FROM loans l
JOIN users_customuser u ON l.borrower_id = u.id
JOIN loan_applications la ON l.application_id = la.id
JOIN loan_products lp ON la.loan_product_id = lp.id
WHERE l.loan_number = 'LOAN-000193';

SELECT 
    '=== LOAN-000195 ANALYSIS ===' as section;

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,
    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,
    CONCAT('Principal (', l.principal_amount, ') + Interest (', l.interest_amount, ') + Fee (', l.processing_fee, ') = ', 
           (l.principal_amount + l.interest_amount + l.processing_fee)) as calculation
FROM loans l
JOIN users_customuser u ON l.borrower_id = u.id
JOIN loan_applications la ON l.application_id = la.id
JOIN loan_products lp ON la.loan_product_id = lp.id
WHERE l.loan_number = 'LOAN-000195';

-- ============================================================================
-- PART 3: CHECK ALL MWAMBA LOANS
-- ============================================================================
SELECT 
    '=== ALL MWAMBA LOANS ===' as section;

SELECT 
    l.loan_number,
    CONCAT(u.first_name, ' ', u.last_name) as borrower,
    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,
    CASE 
        WHEN l.total_amount = (l.principal_amount + l.interest_amount + l.processing_fee) THEN '✓ CORRECT'
        ELSE '✗ INCORRECT'
    END as status
FROM loans l
JOIN users_customuser u ON l.borrower_id = u.id
JOIN loan_applications la ON l.application_id = la.id
JOIN loan_products lp ON la.loan_product_id = lp.id
WHERE lp.product_type = 'mwamba'
ORDER BY l.loan_number;

-- ============================================================================
-- PART 4: FIX ALL INCORRECT TOTALS
-- ============================================================================
-- UNCOMMENT THE LINES BELOW TO FIX THE LOANS
-- WARNING: This will update the database!

/*
SELECT 
    '=== FIXING INCORRECT TOTALS ===' as section;

-- Show what will be fixed
SELECT 
    loan_number,
    total_amount as old_total,
    (principal_amount + interest_amount + processing_fee) as new_total
FROM loans
WHERE total_amount != (principal_amount + interest_amount + processing_fee);

-- Perform the fix
UPDATE loans
SET total_amount = principal_amount + interest_amount + processing_fee
WHERE total_amount != (principal_amount + interest_amount + processing_fee);

-- Show results
SELECT 
    CONCAT('Fixed ', ROW_COUNT(), ' loan(s)') as result;

-- Verify the fix
SELECT 
    loan_number,
    principal_amount,
    interest_amount,
    processing_fee,
    total_amount,
    (principal_amount + interest_amount + processing_fee) as calculated_total,
    CASE 
        WHEN total_amount = (principal_amount + interest_amount + processing_fee) THEN '✓ CORRECT'
        ELSE '✗ STILL INCORRECT'
    END as status
FROM loans
WHERE loan_number IN ('LOAN-000193', 'LOAN-000195');
*/

-- ============================================================================
-- SUMMARY
-- ============================================================================
SELECT 
    '=== SUMMARY ===' as section;

SELECT 
    COUNT(*) as total_loans,
    SUM(CASE WHEN total_amount = (principal_amount + interest_amount + processing_fee) THEN 1 ELSE 0 END) as correct_loans,
    SUM(CASE WHEN total_amount != (principal_amount + interest_amount + processing_fee) THEN 1 ELSE 0 END) as incorrect_loans
FROM loans;