-- SQL queries to diagnose why amount_paid is showing 0 in production

-- 1. Check the specific loans mentioned
SELECT 
    loan_number,
    status,
    is_deleted,
    is_rolled_over,
    principal_amount,
    total_amount,
    created_at
FROM loans 
WHERE loan_number IN ('LOAN-000128', 'LOAN-000127', 'LOAN-000110', 'LOAN-000109', 'LOAN-000096');

-- 2. Check if these loans have repayment records
SELECT 
    l.loan_number,
    COUNT(r.id) as repayment_count,
    SUM(r.amount) as total_paid
FROM loans l
LEFT JOIN repayments r ON l.id = r.loan_id
WHERE l.loan_number IN ('LOAN-000128', 'LOAN-000127', 'LOAN-000110', 'LOAN-000109', 'LOAN-000096')
GROUP BY l.loan_number, l.id;

-- 3. Check repayment details for LOAN-000128
SELECT 
    r.id,
    r.amount,
    r.payment_date,
    r.payment_method,
    r.created_at
FROM repayments r
JOIN loans l ON r.loan_id = l.id
WHERE l.loan_number = 'LOAN-000128'
ORDER BY r.payment_date DESC;

-- 4. Check if there's a foreign key issue
SELECT 
    r.id as repayment_id,
    r.loan_id,
    r.amount,
    r.payment_date,
    l.loan_number
FROM repayments r
LEFT JOIN loans l ON r.loan_id = l.id
WHERE l.loan_number IN ('LOAN-000128', 'LOAN-000127', 'LOAN-000110', 'LOAN-000109', 'LOAN-000096')
ORDER BY r.payment_date DESC;

-- 5. Check all repayments for these loans (even if loan_id doesn't match)
SELECT 
    r.id,
    r.loan_id,
    r.amount,
    r.payment_date,
    r.payment_method
FROM repayments r
WHERE r.loan_id IN (
    SELECT id FROM loans 
    WHERE loan_number IN ('LOAN-000128', 'LOAN-000127', 'LOAN-000110', 'LOAN-000109', 'LOAN-000096')
);

-- 6. Check if there are orphaned repayments (repayments without matching loans)
SELECT 
    r.id,
    r.loan_id,
    r.amount,
    r.payment_date,
    CASE 
        WHEN l.id IS NULL THEN 'ORPHANED'
        ELSE 'OK'
    END as status
FROM repayments r
LEFT JOIN loans l ON r.loan_id = l.id
WHERE r.loan_id IN (
    SELECT id FROM loans 
    WHERE loan_number IN ('LOAN-000128', 'LOAN-000127', 'LOAN-000110', 'LOAN-000109', 'LOAN-000096')
);

-- 7. Check the table structure to ensure loan_id column exists
DESCRIBE repayments;

-- 8. Check if there's a related_name issue - look for any column that might link to loans
SHOW COLUMNS FROM repayments LIKE '%loan%';

-- 9. Count total repayments vs loans
SELECT 
    'Total Loans' as metric,
    COUNT(*) as count
FROM loans
UNION ALL
SELECT 
    'Total Repayments' as metric,
    COUNT(*) as count
FROM repayments
UNION ALL
SELECT 
    'Loans with Repayments' as metric,
    COUNT(DISTINCT r.loan_id) as count
FROM repayments r;

-- 10. Check if the issue is with UUID vs integer IDs
SELECT 
    l.id as loan_id,
    l.loan_number,
    r.id as repayment_id,
    r.loan_id as repayment_loan_id,
    r.amount,
    CASE 
        WHEN l.id = r.loan_id THEN 'MATCH'
        ELSE 'MISMATCH'
    END as id_match
FROM loans l
LEFT JOIN repayments r ON l.id = r.loan_id
WHERE l.loan_number IN ('LOAN-000128', 'LOAN-000127', 'LOAN-000110', 'LOAN-000109', 'LOAN-000096')
LIMIT 20;