-- Fix Loan Total Amount Calculation
-- This SQL script fixes loans where total_amount is incorrectly calculated
-- Correct formula: total_amount = principal_amount + interest_amount + processing_fee

-- First, let's see which loans have incorrect totals
SELECT 
    loan_number,
    CONCAT(first_name, ' ', last_name) as borrower_name,
    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
LEFT JOIN users_customuser ON loans.borrower_id = users_customuser.id
WHERE total_amount != (principal_amount + interest_amount + processing_fee)
ORDER BY loan_number;

-- Now fix all loans with incorrect totals
UPDATE loans
SET total_amount = principal_amount + interest_amount + processing_fee
WHERE total_amount != (principal_amount + interest_amount + processing_fee);

-- Show how many rows were affected
SELECT ROW_COUNT() as loans_fixed;

-- Verify the specific loans mentioned
SELECT 
    loan_number,
    principal_amount,
    interest_amount,
    processing_fee,
    total_amount,
    (principal_amount + interest_amount + processing_fee) as calculated_total
FROM loans
WHERE loan_number IN ('LOAN-000193', 'LOAN-000195')
ORDER BY loan_number;