-- ============================================================================
-- FIX LOAN AMOUNTS IN DATABASE
-- ============================================================================
-- This script updates interest_amount, processing_fee, and total_amount
-- for all loans to match the correct calculated values based on current rates
--
-- For LOAN-000193 (Mwamba product):
--   Principal: 26,200.00
--   Interest: 7,860.00 (30% of principal)
--   Processing Fee: 1,310.00 (5% of principal)
--   Total: 35,370.00
-- ============================================================================

USE acbptxvs_branch_system;

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

-- ============================================================================
-- STEP 1: SHOW LOANS WITH INCORRECT AMOUNTS
-- ============================================================================
SELECT '=== LOANS WITH INCORRECT AMOUNTS ===' as '';

-- For Mwamba loans specifically (30% interest, 5% processing fee)
SELECT 
    l.loan_number,
    CONCAT(u.first_name, ' ', u.last_name) as borrower_name,
    lp.product_type,
    l.principal_amount,
    l.interest_amount as current_interest,
    (l.principal_amount * 0.30) as correct_interest,
    l.processing_fee as current_fee,
    (l.principal_amount * 0.05) as correct_fee,
    l.total_amount as current_total,
    (l.principal_amount + (l.principal_amount * 0.30) + (l.principal_amount * 0.05)) as correct_total
FROM loans l
LEFT JOIN users u ON l.borrower_id = u.id
LEFT JOIN loan_applications la ON l.application_id = la.id
LEFT JOIN loan_products lp ON la.loan_product_id = lp.id
WHERE lp.product_type = 'mwamba'
  AND (
    l.interest_amount != (l.principal_amount * 0.30)
    OR l.processing_fee != (l.principal_amount * 0.05)
    OR l.total_amount != (l.principal_amount + (l.principal_amount * 0.30) + (l.principal_amount * 0.05))
  )
ORDER BY l.loan_number;

-- ============================================================================
-- STEP 2: FIX MWAMBA LOANS (30% interest, 5% processing fee)
-- ============================================================================
SELECT '=== FIXING MWAMBA LOANS ===' as '';

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.interest_amount = l.principal_amount * 0.30,
    l.processing_fee = l.principal_amount * 0.05,
    l.total_amount = l.principal_amount + (l.principal_amount * 0.30) + (l.principal_amount * 0.05)
WHERE lp.product_type = 'mwamba';

SELECT ROW_COUNT() as mwamba_loans_fixed;

-- ============================================================================
-- STEP 3: FIX OTHER LOAN PRODUCTS (if needed)
-- ============================================================================
-- Add similar UPDATE statements for other products if needed
-- Example for Boost (20% interest, 2% processing fee):
-- 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.interest_amount = l.principal_amount * 0.20,
--     l.processing_fee = l.principal_amount * 0.02,
--     l.total_amount = l.principal_amount + (l.principal_amount * 0.20) + (l.principal_amount * 0.02)
-- WHERE lp.product_type = 'boost';

-- ============================================================================
-- STEP 4: VERIFY THE FIX
-- ============================================================================
SELECT '=== VERIFICATION ===' as '';

-- Check LOAN-000193 specifically
SELECT '=== LOAN-000193 VERIFICATION ===' 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 = 'LOAN-000193';

-- Check all Mwamba loans
SELECT '=== ALL MWAMBA LOANS VERIFICATION ===' as '';
SELECT 
    COUNT(*) as total_mwamba_loans,
    SUM(CASE 
        WHEN l.interest_amount = (l.principal_amount * 0.30)
         AND l.processing_fee = (l.principal_amount * 0.05)
         AND l.total_amount = (l.principal_amount + (l.principal_amount * 0.30) + (l.principal_amount * 0.05))
        THEN 1 ELSE 0 
    END) as correct_loans,
    SUM(CASE 
        WHEN l.interest_amount != (l.principal_amount * 0.30)
          OR l.processing_fee != (l.principal_amount * 0.05)
          OR l.total_amount != (l.principal_amount + (l.principal_amount * 0.30) + (l.principal_amount * 0.05))
        THEN 1 ELSE 0 
    END) as incorrect_loans
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 lp.product_type = 'mwamba';

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