-- Fix loan totals in LOCAL database
-- Run this against localhost:3306/acbptxvs_branch_system

USE acbptxvs_branch_system;

-- Show loans with incorrect totals
SELECT 
    loan_number,
    principal_amount,
    interest_amount,
    processing_fee,
    total_amount as current_total,
    (principal_amount + interest_amount + processing_fee) as correct_total,
    (principal_amount + interest_amount + processing_fee - total_amount) as difference
FROM loans
WHERE total_amount != (principal_amount + interest_amount + processing_fee)
ORDER BY loan_number;

-- Fix all incorrect loans
UPDATE loans
SET total_amount = principal_amount + interest_amount + processing_fee
WHERE total_amount != (principal_amount + interest_amount + processing_fee);

-- Verify the fix
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;