-- Real-time cutoff testing script
-- This will insert transactions with current timestamp

-- Set the session time zone to IST for accurate testing

-- Function to add small time offsets to CURRENT_TIMESTAMP
SET @current_time = CURRENT_TIMESTAMP;
SET @one_minute_before = DATE_SUB(@current_time, INTERVAL 1 MINUTE);
SET @one_minute_after = DATE_ADD(@current_time, INTERVAL 1 MINUTE);

-- Show current testing time
SELECT 
    DATE_FORMAT(@one_minute_before, '%Y-%m-%d %H:%i:%s') as one_min_before,
    DATE_FORMAT(@current_time, '%Y-%m-%d %H:%i:%s') as current_time,
    DATE_FORMAT(@one_minute_after, '%Y-%m-%d %H:%i:%s') as one_min_after;

-- Clear any previous test data
DELETE FROM transactions WHERE transaction_ref_number LIKE 'REALTIME_TEST%';

-- Insert real-time test transactions
INSERT INTO transactions 
(transaction_ref_number, transaction_amount, inserted_at, updated_at, status, merchant_id, device_id, provider_name) 
VALUES 
-- 1 minute before current time
('REALTIME_TEST_1', 100.00, @one_minute_before, @one_minute_before, 'success', 'MERCH001', 'TERM001', 'TEST_PROVIDER'),

-- At current time
('REALTIME_TEST_2', 50.00, @current_time, @current_time, 'success', 'MERCH001', 'TERM001', 'TEST_PROVIDER'),

-- 1 minute after current time
('REALTIME_TEST_3', 200.00, @one_minute_after, @one_minute_after, 'success', 'MERCH001', 'TERM001', 'TEST_PROVIDER');

-- Show inserted transactions with their times
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 'REALTIME_TEST%'
ORDER BY inserted_at;

-- Show settlement amounts summary
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 'REALTIME_TEST%';
