-- ============================================================================
-- FIX LOAN TOTAL AMOUNTS
-- ============================================================================
-- This script fixes loans where total_amount is incorrectly calculated
-- Correct formula: total_amount = principal_amount + interest_amount + processing_fee
--
-- Run this in MySQL:
--   mysql -u acbptxvs_phin -p acbptxvs_branch_system < fix_all_loan_totals.sql
-- Or in MySQL Workbench/phpMyAdmin
-- ============================================================================

USE acbptxvs_branch_system;

-- Show current database
SELECT DATABASE() as current_database;

-- ============================================================================
-- STEP 1: IDENTIFY LOANS WITH INCORRECT TOTALS
-- ============================================================================

SELECT 
    '=== LOANS WITH INCORRECT TOTALS ===' as '';

SELECT 
    l.loan_number,
    CONCAT(u.first_name, ' ', u.last_name) as borrower_name,
    l.principal_amount,
    l.interest_amount,
    l.processing_fee,
    l.total_amount as current_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
FROM loans l
LEFT JOIN users u ON l.borrower_id = u.id
WHERE l.total_amount != (l.principal_amount + l.interest_amount + l.processing_fee)
ORDER BY l.loan_number;

-- Count incorrect loans
SELECT 
    COUNT(*) as incorrect_loans_count
FROM loans
WHERE total_amount != (principal_amount + interest_amount + processing_fee);

-- ============================================================================
-- STEP 2: FIX ALL INCORRECT LOANS
-- ============================================================================

SELECT 
    '=== FIXING LOANS ===' as '';

UPDATE loans
SET total_amount = principal_amount + interest_amount + processing_fee
WHERE total_amount != (principal_amount + interest_amount + processing_fee);

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

-- ============================================================================
-- STEP 3: VERIFY THE FIX
-- ============================================================================

SELECT 
    '=== VERIFICATION ===' as '';

-- Check if any incorrect loans remain
SELECT 
    COUNT(*) as remaining_incorrect_loans
FROM loans
WHERE total_amount != (principal_amount + interest_amount + processing_fee);

-- Show specific loans mentioned in the issue
SELECT 
    '=== SPECIFIC LOANS (LOAN-000193, LOAN-000195) ===' as '';

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 'INCORRECT ✗'
    END as status
FROM loans
WHERE loan_number IN ('LOAN-000193', 'LOAN-000195')
ORDER BY loan_number;

-- Show all loans summary
SELECT 
    '=== ALL LOANS SUMMARY ===' as '';

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;

SELECT 
    '=== FIX COMPLETE ===' as '';