-- Test Query 1: View all test transactions with their cutoff status
SELECT 
    transaction_ref_number,
    transaction_amount,
    DATE_FORMAT(inserted_at, '%Y-%m-%d %H:%i:%s') as transaction_time,
    TIME(inserted_at) as time_only,
    CASE 
        WHEN TIME(inserted_at) <= '04:30:00' THEN 'Include ✅'
        ELSE 'Exclude ❌'
    END as settlement_status
FROM transactions 
WHERE transaction_ref_number LIKE 'TEST_%'
ORDER BY inserted_at;

-- Test Query 2: Summary of included vs excluded amounts
SELECT 
    SUM(CASE WHEN TIME(inserted_at) <= '04:30:00' THEN transaction_amount ELSE 0 END) as included_amount,
    SUM(CASE WHEN TIME(inserted_at) > '04:30:00' THEN transaction_amount ELSE 0 END) as excluded_amount,
    COUNT(CASE WHEN TIME(inserted_at) <= '04:30:00' THEN 1 END) as included_count,
    COUNT(CASE WHEN TIME(inserted_at) > '04:30:00' THEN 1 END) as excluded_count
FROM transactions 
WHERE transaction_ref_number LIKE 'TEST_%';

-- Test Query 3: Group transactions by settlement status
SELECT 
    CASE 
        WHEN TIME(inserted_at) <= '04:30:00' THEN 'Included in Settlement'
        ELSE 'Excluded from Settlement'
    END as settlement_group,
    COUNT(*) as transaction_count,
    SUM(transaction_amount) as total_amount,
    MIN(TIME(inserted_at)) as earliest_time,
    MAX(TIME(inserted_at)) as latest_time
FROM transactions 
WHERE transaction_ref_number LIKE 'TEST_%'
GROUP BY 
    CASE 
        WHEN TIME(inserted_at) <= '04:30:00' THEN 'Included in Settlement'
        ELSE 'Excluded from Settlement'
    END;

-- Test Query 4: Detailed transaction list with time comparison
SELECT 
    transaction_ref_number,
    transaction_amount,
    TIME(inserted_at) as transaction_time,
    '04:30:00' as cutoff_time,
    CASE 
        WHEN TIME(inserted_at) <= '04:30:00' THEN '✅ Before or at cutoff'
        ELSE '❌ After cutoff'
    END as status,
    TIMEDIFF('04:30:00', TIME(inserted_at)) as time_until_cutoff
FROM transactions 
WHERE transaction_ref_number LIKE 'TEST_%'
ORDER BY inserted_at;
