-- 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 reference_no LIKE 'CUTOFF_TEST%';

-- Insert test transactions
INSERT INTO transactions 
(reference_no, amount, transaction_date, status, merchant_id, terminal_id, card_type) 
VALUES 
-- Before cutoff (should be included)
('CUTOFF_TEST_1', 100.00, '2025-10-16 04:29:00', 'SUCCESS', 'MERCH001', 'TERM001', 'VISA'),

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

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

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

-- Summary query to show total amount that should be included
SELECT 
    SUM(CASE WHEN TIME(transaction_date) <= '04:30:00' THEN amount ELSE 0 END) as included_amount,
    SUM(CASE WHEN TIME(transaction_date) > '04:30:00' THEN amount ELSE 0 END) as excluded_amount
FROM transactions 
WHERE reference_no LIKE 'CUTOFF_TEST%';
