-- SQL Solution to fix LOAN-000069 rollover fee double-counting bug
-- Run this in your database (phpMyAdmin or MySQL command line)

-- Step 1: View the current state of LOAN-000069
SELECT 
    loan_number,
    principal_amount,
    interest_amount,
    processing_fee,
    total_amount,
    (principal_amount + interest_amount + processing_fee) as calculated_from_fields
FROM loans 
WHERE loan_number = 'LOAN-000069';

-- Expected result:
-- principal: 12,200
-- interest: 2,440
-- processing_fee: 1,464 (WRONG - includes rollover fee)
-- total_amount: 16,104 (WRONG)
-- calculated_from_fields: 16,104

-- Step 2: Calculate the correct processing fee (2% of principal)
-- For this loan: 12,200 * 0.02 = 244

-- Step 3: Fix LOAN-000069
UPDATE loans 
SET 
    processing_fee = 244.00,  -- Correct 2% processing fee
    total_amount = 14884.00   -- 12,200 + 2,440 + 244
WHERE loan_number = 'LOAN-000069';

-- Step 4: Also fix the loan application
UPDATE loan_applications la
INNER JOIN loans l ON la.id = l.application_id
SET 
    la.processing_fee_amount = 244.00,
    la.total_amount = 14884.00
WHERE l.loan_number = 'LOAN-000069';

-- Step 5: Verify the fix
SELECT 
    loan_number,
    principal_amount,
    interest_amount,
    processing_fee,
    total_amount,
    (principal_amount + interest_amount + processing_fee) as calculated_from_fields,
    CASE 
        WHEN total_amount = (principal_amount + interest_amount + processing_fee) 
        THEN 'CORRECT ✓' 
        ELSE 'WRONG ✗' 
    END as status
FROM loans 
WHERE loan_number = 'LOAN-000069';

-- Expected result after fix:
-- principal: 12,200
-- interest: 2,440
-- processing_fee: 244 (CORRECT)
-- total_amount: 14,884 (CORRECT)
-- calculated_from_fields: 14,884
-- status: CORRECT ✓

-------------------------------------------------------------------
-- OPTIONAL: Fix ALL affected rollover loans
-------------------------------------------------------------------

-- Find all loans with incorrect processing_fee or total_amount
SELECT 
    l.loan_number,
    l.principal_amount,
    l.interest_amount,
    l.processing_fee as current_processing_fee,
    (l.principal_amount * lp.processing_fee / 100) as correct_processing_fee,
    l.total_amount as current_total,
    (l.principal_amount + l.interest_amount + (l.principal_amount * lp.processing_fee / 100)) as correct_total,
    (l.total_amount - (l.principal_amount + l.interest_amount + (l.principal_amount * lp.processing_fee / 100))) 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
WHERE 
    l.status = 'active'
    AND (
        l.processing_fee != (l.principal_amount * lp.processing_fee / 100)
        OR l.total_amount != (l.principal_amount + l.interest_amount + (l.principal_amount * lp.processing_fee / 100))
    );

-- Fix ALL affected loans (use with caution!)
UPDATE 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  
SET 
    l.processing_fee = (l.principal_amount * lp.processing_fee / 100),
    l.total_amount = l.principal_amount + l.interest_amount + (l.principal_amount * lp.processing_fee / 100)
WHERE 
    l.status = 'active'
    AND (
        l.processing_fee != (l.principal_amount * lp.processing_fee / 100)
        OR l.total_amount != (l.principal_amount + l.interest_amount + (l.principal_amount * lp.processing_fee / 100))
    );

-- Also fix the corresponding loan applications
UPDATE loan_applications la
INNER JOIN loans l ON la.id = l.application_id
INNER JOIN loan_products lp ON la.loan_product_id = lp.id
SET 
    la.processing_fee_amount = (l.principal_amount * lp.processing_fee / 100),
    la.total_amount = l.principal_amount + l.interest_amount + (l.principal_amount * lp.processing_fee / 100)
WHERE 
    l.status = 'active'
    AND (
        la.processing_fee_amount != (l.principal_amount * lp.processing_fee / 100)
        OR la.total_amount != (l.principal_amount + l.interest_amount + (l.principal_amount * lp.processing_fee / 100))
    );