-- Direct MySQL INSERT queries for testing batch numbers
-- Run these one by one in your MySQL terminal after logging in with:
-- mysql -u root -pdataaegis123

USE shukria_transactions;

-- Insert test transactions for today (2025-09-29)
INSERT INTO transactions (transaction_id, merchant_id, merchant_tag, bank_user_id, provider_id, amount, currency, pay_mode, transaction_status, settlement_status, settlement_date_time, batch_number, inserted_at, updated_at) VALUES ('TXN_20250929_001', 'TEST_MERCHANT_001', 'TEST_TAG_001', 'BANK_USER_001', 3, 1500.00, 'AED', 'QR', 'SUCCESS', 'unmatched', '2025-09-29 08:00:00', 'BATCH_001', NOW(), NOW());

INSERT INTO transactions (transaction_id, merchant_id, merchant_tag, bank_user_id, provider_id, amount, currency, pay_mode, transaction_status, settlement_status, settlement_date_time, batch_number, inserted_at, updated_at) VALUES ('TXN_20250929_002', 'TEST_MERCHANT_001', 'TEST_TAG_001', 'BANK_USER_001', 3, 2500.00, 'AED', 'QR', 'SUCCESS', 'unmatched', '2025-09-29 08:15:00', 'BATCH_001', NOW(), NOW());

INSERT INTO transactions (transaction_id, merchant_id, merchant_tag, bank_user_id, provider_id, amount, currency, pay_mode, transaction_status, settlement_status, settlement_date_time, batch_number, inserted_at, updated_at) VALUES ('TXN_20250929_003', 'TEST_MERCHANT_001', 'TEST_TAG_001', 'BANK_USER_001', 3, 750.00, 'AED', 'QR', 'SUCCESS', 'unmatched', '2025-09-29 08:30:00', 'BATCH_001', NOW(), NOW());

INSERT INTO transactions (transaction_id, merchant_id, merchant_tag, bank_user_id, provider_id, amount, currency, pay_mode, transaction_status, settlement_status, settlement_date_time, batch_number, inserted_at, updated_at) VALUES ('TXN_20250929_004', 'TEST_MERCHANT_002', 'TEST_TAG_002', 'BANK_USER_002', 4, 3200.00, 'AED', 'QR', 'SUCCESS', 'unmatched', '2025-09-29 09:00:00', 'BATCH_002', NOW(), NOW());

INSERT INTO transactions (transaction_id, merchant_id, merchant_tag, bank_user_id, provider_id, amount, currency, pay_mode, transaction_status, settlement_status, settlement_date_time, batch_number, inserted_at, updated_at) VALUES ('TXN_20250929_005', 'TEST_MERCHANT_002', 'TEST_TAG_002', 'BANK_USER_002', 4, 1800.00, 'AED', 'QR', 'SUCCESS', 'unmatched', '2025-09-29 09:20:00', 'BATCH_002', NOW(), NOW());

INSERT INTO transactions (transaction_id, merchant_id, merchant_tag, bank_user_id, provider_id, amount, currency, pay_mode, transaction_status, settlement_status, settlement_date_time, batch_number, inserted_at, updated_at) VALUES ('TXN_20250929_006', 'TEST_MERCHANT_003', 'TEST_TAG_003', 'BANK_USER_003', 3, 4500.00, 'AED', 'QR', 'SUCCESS', 'unmatched', '2025-09-29 10:00:00', 'BATCH_003', NOW(), NOW());

INSERT INTO transactions (transaction_id, merchant_id, merchant_tag, bank_user_id, provider_id, amount, currency, pay_mode, transaction_status, settlement_status, settlement_date_time, batch_number, inserted_at, updated_at) VALUES ('TXN_20250929_007', 'TEST_MERCHANT_003', 'TEST_TAG_003', 'BANK_USER_003', 3, 650.00, 'AED', 'QR', 'SUCCESS', 'unmatched', '2025-09-29 10:15:00', 'BATCH_003', NOW(), NOW());

-- Verify the data was inserted
SELECT 'INSERTED TRANSACTIONS' as status, COUNT(*) as count FROM transactions WHERE DATE(inserted_at) = '2025-09-29' AND merchant_id LIKE 'TEST_%';

SELECT merchant_id, provider_id, COUNT(*) as txn_count, SUM(amount) as total_amount FROM transactions WHERE DATE(inserted_at) = '2025-09-29' AND merchant_id LIKE 'TEST_%' GROUP BY merchant_id, provider_id;
