-- SQL queries to check for rolled-over loans in production

-- 1. Count all active loans (including rolled-over)
SELECT COUNT(*) as total_active_loans
FROM loans 
WHERE status = 'active' AND is_deleted = 0;

-- 2. Count rolled-over loans
SELECT COUNT(*) as rolled_over_loans
FROM loans 
WHERE (is_rolled_over = 1 OR status = 'rolled_over');

-- 3. List rolled-over loans (first 10)
SELECT 
    loan_number,
    status,
    is_rolled_over,
    principal_amount,
    total_amount,
    due_date,
    borrower_id
FROM loans 
WHERE (is_rolled_over = 1 OR status = 'rolled_over')
LIMIT 10;

-- 4. Count active loans excluding rolled-over (what reports should show)
SELECT COUNT(*) as active_non_rolled_loans
FROM loans 
WHERE status = 'active' 
  AND is_deleted = 0 
  AND is_rolled_over = 0 
  AND status != 'rolled_over';

-- 5. Find the specific loan mentioned (LOAN-000128)
SELECT 
    loan_number,
    status,
    is_rolled_over,
    is_deleted,
    principal_amount,
    total_amount,
    due_date,
    created_at,
    updated_at
FROM loans 
WHERE loan_number = 'LOAN-000128';

-- 6. Check if there are any inconsistent rollover states
-- (loans with is_rolled_over=1 but status != 'rolled_over')
SELECT 
    loan_number,
    status,
    is_rolled_over,
    principal_amount
FROM loans 
WHERE is_rolled_over = 1 AND status != 'rolled_over';

-- 7. Check if there are any inconsistent rollover states (reverse)
-- (loans with status='rolled_over' but is_rolled_over=0)
SELECT 
    loan_number,
    status,
    is_rolled_over,
    principal_amount
FROM loans 
WHERE status = 'rolled_over' AND is_rolled_over = 0;