-- ============================================================================
-- Fix LOAN-000131 - Correct Interest and Processing Fee Calculation
-- ============================================================================
-- The loan was created with wrong rates:
--   Interest: 2,000 (should be 6,000 for 90 days at 10% per month)
--   Processing Fee: 400 (should be 1,000 at 5%)
-- ============================================================================

-- Step 1: Check current values
SELECT 
    '=== CURRENT VALUES ===' as info;

SELECT 
    loan_number,
    principal_amount,
    interest_amount as current_interest,
    processing_fee as current_processing_fee,
    total_amount as current_total,
    duration_days,
    -- Calculate what it should be
    (principal_amount * 0.10 * (duration_days / 30)) as correct_interest,
    (principal_amount * 0.05) as correct_processing_fee,
    (principal_amount + (principal_amount * 0.10 * (duration_days / 30)) + (principal_amount * 0.05)) as correct_total
FROM loans
WHERE loan_number = 'LOAN-000131';

-- Step 2: Fix the loan
SELECT 
    '=== APPLYING FIX ===' as info;

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';

-- Step 3: Fix the application
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';

-- Step 4: Verify the fix
SELECT 
    '=== VERIFICATION ===' as info;

SELECT 
    loan_number,
    principal_amount,
    interest_amount,
    processing_fee,
    total_amount,
    duration_days,
    (SELECT COALESCE(SUM(amount), 0) FROM repayments WHERE loan_id = loans.id) as amount_paid,
    (total_amount - (SELECT COALESCE(SUM(amount), 0) FROM repayments WHERE loan_id = loans.id)) as outstanding_balance,
    -- Verify calculation
    CASE 
        WHEN ABS(interest_amount - (principal_amount * 0.10 * (duration_days / 30))) < 0.01 
        THEN '✓ Interest Correct' 
        ELSE '✗ Interest Wrong' 
    END as interest_check,
    CASE 
        WHEN ABS(processing_fee - (principal_amount * 0.05)) < 0.01 
        THEN '✓ Processing Fee Correct' 
        ELSE '✗ Processing Fee Wrong' 
    END as processing_fee_check,
    CASE 
        WHEN ABS(total_amount - (principal_amount + interest_amount + processing_fee)) < 0.01 
        THEN '✓ Total Correct' 
        ELSE '✗ Total Wrong' 
    END as total_check
FROM loans
WHERE loan_number = 'LOAN-000131';

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