-- Batch Number Logic Database Verification Script
-- Run this script to verify the merchant_batch_numbers table and related data

-- Check if the merchant_batch_numbers table exists
SHOW TABLES LIKE 'merchant_batch_numbers';

-- Show table structure
DESCRIBE merchant_batch_numbers;

-- Check current merchant batch numbers
SELECT 
    merchant_id,
    batch_number,
    provider_id,
    inserted_at,
    updated_at
FROM merchant_batch_numbers 
ORDER BY merchant_id, inserted_at;

-- Check settlements with batch numbers
SELECT 
    settlement_id,
    merchant_id,
    merchant_tag,
    batch_number,
    status,
    date,
    amount,
    provider_id
FROM settlements 
WHERE batch_number IS NOT NULL 
ORDER BY merchant_id, date DESC;

-- Check for any batch number mismatches or inconsistencies
SELECT 
    s.merchant_id,
    s.batch_number as settlement_batch,
    mb.batch_number as current_batch,
    CASE 
        WHEN s.batch_number > mb.batch_number THEN 'Settlement batch higher than current'
        WHEN s.batch_number = mb.batch_number THEN 'Batch numbers match'
        ELSE 'Settlement batch lower than current'
    END as status
FROM settlements s
JOIN merchant_batch_numbers mb ON s.merchant_id = mb.merchant_id
WHERE s.batch_number IS NOT NULL
ORDER BY s.merchant_id, s.date DESC;

-- Sample queries for testing

-- Get next batch number for a merchant (simulates the function)
SELECT 
    merchant_id,
    batch_number + 1 as next_batch_number
FROM merchant_batch_numbers 
WHERE merchant_id = 'TEST_MERCHANT_001';

-- Count settlements per merchant by batch
SELECT 
    merchant_id,
    batch_number,
    COUNT(*) as settlement_count,
    SUM(amount) as total_amount
FROM settlements 
WHERE batch_number IS NOT NULL 
GROUP BY merchant_id, batch_number
ORDER BY merchant_id, batch_number;