-- Test transactions for cutoff time verification (04:30:00 IST cutoff)
-- Date: 2025-10-16 (Current test date)

-- Clear existing test data if needed
DELETE FROM transactions WHERE transaction_ref_number LIKE 'CUTOFF_TEST%';

-- Insert test transactions
INSERT INTO transactions 
(transaction_ref_number, transaction_amount, inserted_at, updated_at, status, merchant_id, device_id, provider_name) 
VALUES 
-- Before cutoff (should be included)
('CUTOFF_TEST_1', 100.00, '2025-10-16 04:29:00', '2025-10-16 04:29:00', 'success', 'MERCH001', 'TERM001', 'TEST_PROVIDER'),

-- Exactly at cutoff (should be included)
('CUTOFF_TEST_2', 50.00, '2025-10-16 04:30:00', '2025-10-16 04:30:00', 'success', 'MERCH001', 'TERM001', 'TEST_PROVIDER'),

-- After cutoff (should be excluded)
('CUTOFF_TEST_3', 200.00, '2025-10-16 04:31:00', '2025-10-16 04:31:00', 'success', 'MERCH001', 'TERM001', 'TEST_PROVIDER');

-- Query to verify inserted transactions
SELECT 
    transaction_ref_number,
    transaction_amount,
    inserted_at,
    CASE 
        WHEN TIME(inserted_at) <= '04:30:00' THEN 'Include ✅'
        ELSE 'Exclude ❌'
    END as settlement_status
FROM transactions 
WHERE transaction_ref_number LIKE 'CUTOFF_TEST%'
ORDER BY inserted_at;

-- Summary query to show total amount that should be included
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
FROM transactions 
WHERE transaction_ref_number LIKE 'CUTOFF_TEST%';
