-- Test Settlement Data for Mercury CSV API Testing
-- Run this script to create test data

-- Insert test settlement record
INSERT INTO settlements (
  id, 
  settlement_id, 
  merchant_id, 
  date, 
  status, 
  amount,
  merchant_tag,
  bank_user_id,
  total_transaction_count,
  gross_settlement_amount,
  gross_settlement_currency,
  mdr_charges,
  mdr_charges_currency,
  tax_on_mdr,
  tax_on_mdr_currency,
  net_settlement_amount,
  net_settlement_currency,
  mismatch_count,
  inserted_at,
  updated_at
) VALUES (
  UNHEX(REPLACE('01234567-89ab-cdef-0123-456789abcdef', '-', '')), -- binary_id
  'SETT_TEST_001',
  'test123',
  '2025-07-25',
  'settled',
  1500.00,
  'UB776WH',
  'MERCURY_AANI123',
  3,
  1500.00,
  'AED',
  75.00,
  'AED',
  3.75,
  'AED',
  1421.25,
  'AED',
  0,
  NOW(),
  NOW()
);

-- Insert test transactions linked to the settlement
INSERT INTO transactions (
  id,
  transaction_id,
  settlement_id,
  qr_id,
  terminal_id,
  transaction_amount,
  charge_rate,
  pay_mode,
  settlement_status,
  inserted_at,
  updated_at
) VALUES 
(
  UNHEX(REPLACE('11111111-1111-1111-1111-111111111111', '-', '')),
  'aani_pay_2030560038715669_204775745070000',
  'SETT_TEST_001',
  'AANI9876543210000000',
  '90080001',
  500.00,
  25.00,
  'QR',
  'settled',
  '2025-07-25 12:35:00',
  '2025-07-25 12:35:00'
),
(
  UNHEX(REPLACE('22222222-2222-2222-2222-222222222222', '-', '')),
  'aani_pay_2030560038715670_204775745070001',
  'SETT_TEST_001',
  'AANI9876543210000000',
  '90080001',
  300.00,
  15.00,
  'QR',
  'settled',
  '2025-07-25 12:40:00',
  '2025-07-25 12:40:00'
),
(
  UNHEX(REPLACE('33333333-3333-3333-3333-333333333333', '-', '')),
  'aani_pay_2030560038715671_204775745070002',
  'SETT_TEST_001',
  'AANI9876543210000000',
  '90080001',
  700.00,
  35.00,
  'QR',
  'settled',
  '2025-07-25 12:50:00',
  '2025-07-25 12:50:00'
);

-- Verify the data was inserted
SELECT 'Settlements created:' as info;
SELECT settlement_id, merchant_id, date, status, amount FROM settlements WHERE merchant_id = 'test123';

SELECT 'Transactions created:' as info;  
SELECT transaction_id, transaction_amount, settlement_status, settlement_id FROM transactions 
WHERE settlement_id = 'SETT_TEST_001';
