-- =====================================================
-- BATCH NUMBER TESTING - REAL TIME WITH TODAY'S DATE
-- Date: 2025-09-29
-- Purpose: Insert test data to verify batch number increment
-- =====================================================

USE shukria_transactions;

-- Clean up any existing test data for today
DELETE FROM settlement_transactions WHERE DATE(created_at) = '2025-09-29';
DELETE FROM settlements WHERE DATE(date) = '2025-09-29';
DELETE FROM transactions WHERE DATE(inserted_at) = '2025-09-29' AND merchant_tag LIKE 'TEST_%';

-- Reset batch numbers for test merchants (optional - comment out if you want to keep existing)
-- UPDATE merchant_batch_numbers SET batch_number = '000000' WHERE merchant_id IN ('TEST_MERCHANT_001', 'TEST_MERCHANT_002', 'TEST_MERCHANT_003');

-- =====================================================
-- 1. INSERT TEST TRANSACTIONS FOR TODAY (2025-09-29)
-- =====================================================

-- Test Merchant 1 - Multiple transactions
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 
-- Merchant 1 - Batch 1 transactions
('TXN_20250929_001', 'TEST_MERCHANT_001', 'TEST_TAG_001', 'BANK_001', 3, 
 1500.00, 'AED', 'QR', 'SUCCESS', 'unmatched', 
 '2025-09-29 08:00:00', 'BATCH_001', NOW(), NOW()),
 
('TXN_20250929_002', 'TEST_MERCHANT_001', 'TEST_TAG_001', 'BANK_001', 3, 
 2500.00, 'AED', 'QR', 'SUCCESS', 'unmatched', 
 '2025-09-29 08:15:00', 'BATCH_001', NOW(), NOW()),

('TXN_20250929_003', 'TEST_MERCHANT_001', 'TEST_TAG_001', 'BANK_001', 3, 
 750.00, 'AED', 'QR', 'SUCCESS', 'unmatched', 
 '2025-09-29 08:30:00', 'BATCH_001', NOW(), NOW()),

-- Merchant 2 - Different provider, different batch
('TXN_20250929_004', 'TEST_MERCHANT_002', 'TEST_TAG_002', 'BANK_002', 4, 
 3200.00, 'AED', 'QR', 'SUCCESS', 'unmatched', 
 '2025-09-29 09:00:00', 'BATCH_002', NOW(), NOW()),

('TXN_20250929_005', 'TEST_MERCHANT_002', 'TEST_TAG_002', 'BANK_002', 4, 
 1800.00, 'AED', 'QR', 'SUCCESS', 'unmatched', 
 '2025-09-29 09:20:00', 'BATCH_002', NOW(), NOW()),

-- Merchant 3 - Same provider as Merchant 1 (should get different batch number)
('TXN_20250929_006', 'TEST_MERCHANT_003', 'TEST_TAG_003', 'BANK_003', 3, 
 4500.00, 'AED', 'QR', 'SUCCESS', 'unmatched', 
 '2025-09-29 10:00:00', 'BATCH_003', NOW(), NOW()),

('TXN_20250929_007', 'TEST_MERCHANT_003', 'TEST_TAG_003', 'BANK_003', 3, 
 650.00, 'AED', 'QR', 'SUCCESS', 'unmatched', 
 '2025-09-29 10:15:00', 'BATCH_003', NOW(), NOW()),

('TXN_20250929_008', 'TEST_MERCHANT_003', 'TEST_TAG_003', 'BANK_003', 3, 
 1250.00, 'AED', 'QR', 'SUCCESS', 'unmatched', 
 '2025-09-29 10:30:00', 'BATCH_003', NOW(), NOW());

-- =====================================================
-- 2. VERIFY INSERTED TRANSACTIONS
-- =====================================================
SELECT 
    '=== INSERTED TRANSACTIONS FOR TODAY ===' as status,
    COUNT(*) as total_transactions,
    SUM(amount) as total_amount,
    GROUP_CONCAT(DISTINCT merchant_id) as merchants,
    GROUP_CONCAT(DISTINCT provider_id) as providers
FROM transactions 
WHERE DATE(inserted_at) = '2025-09-29' 
AND merchant_tag LIKE 'TEST_%';

-- Detailed view
SELECT 
    transaction_id, merchant_id, merchant_tag, bank_user_id, provider_id,
    amount, currency, settlement_status, batch_number,
    DATE_FORMAT(inserted_at, '%Y-%m-%d %H:%i:%s') as created_time
FROM transactions 
WHERE DATE(inserted_at) = '2025-09-29' 
AND merchant_tag LIKE 'TEST_%'
ORDER BY merchant_id, inserted_at;

-- =====================================================
-- 3. CHECK CURRENT BATCH NUMBERS BEFORE EOD
-- =====================================================
SELECT 
    '=== BATCH NUMBERS BEFORE EOD PROCESSING ===' as status;
    
SELECT 
    merchant_id, batch_number, provider_id,
    DATE_FORMAT(updated_at, '%Y-%m-%d %H:%i:%s') as last_updated
FROM merchant_batch_numbers 
WHERE merchant_id IN ('TEST_MERCHANT_001', 'TEST_MERCHANT_002', 'TEST_MERCHANT_003')
ORDER BY merchant_id, provider_id;

-- =====================================================
-- 4. SIMULATE WHAT EOD PROCESSING WILL DO
-- =====================================================
-- This shows what merchants/providers combinations exist and need batch numbers
SELECT 
    '=== MERCHANT/PROVIDER COMBINATIONS NEEDING BATCH NUMBERS ===' as status;

SELECT DISTINCT
    merchant_id,
    provider_id,
    COUNT(*) as transaction_count,
    SUM(amount) as total_amount,
    GROUP_CONCAT(DISTINCT batch_number) as transaction_batches
FROM transactions 
WHERE DATE(inserted_at) = '2025-09-29' 
AND merchant_tag LIKE 'TEST_%'
AND settlement_status = 'unmatched'
GROUP BY merchant_id, provider_id
ORDER BY merchant_id, provider_id;
