-- Add refund test data for Mercury_REFUND_TEST on 2025-09-30
-- This will create transactions and settlement data to test refund functionality

USE shukria_transactions;

-- Clean up any existing test data first
DELETE FROM settlement_transactions WHERE settlement_id = 'SETT_REFUND_TEST_20250930';
DELETE FROM transactions WHERE bank_user_id = 'Mercury_REFUND_TEST' AND DATE(inserted_at) = '2025-09-30';
DELETE FROM settlements WHERE bank_user_id = 'Mercury_REFUND_TEST' AND date = '2025-09-30';

-- Insert test transactions for Mercury_REFUND_TEST
INSERT INTO transactions (
  transaction_id,
  transaction_amount,
  status,
  bank_user_id,
  transaction_ref_number,
  payment_reference_id,
  provider_name,
  batch_number,
  settlement_status,
  merchant_tag,
  refund_reference_id,
  settlement_date_time,
  inserted_at,
  updated_at
) VALUES 
-- Regular successful transactions
('TXN_REFUND_001', 200.00, 'success', 'Mercury_REFUND_TEST', 'REF_001', 'PAY_001', 'Mercury', 'BATCH_001', 'matched', 'US421RF', NULL, '2025-09-30 10:00:00', '2025-09-30 10:00:00', '2025-09-30 10:00:00'),
('TXN_REFUND_002', 150.00, 'success', 'Mercury_REFUND_TEST', 'REF_002', 'PAY_002', 'Mercury', 'BATCH_001', 'matched', 'US421RF', NULL, '2025-09-30 11:00:00', '2025-09-30 11:00:00', '2025-09-30 11:00:00'),
('TXN_REFUND_003', 300.00, 'success', 'Mercury_REFUND_TEST', 'REF_003', 'PAY_003', 'Mercury', 'BATCH_001', 'matched', 'US421RF', NULL, '2025-09-30 12:00:00', '2025-09-30 12:00:00', '2025-09-30 12:00:00'),

-- Refund transactions (negative amounts)
('TXN_REFUND_004', -100.00, 'refunded', 'Mercury_REFUND_TEST', 'REF_004', 'REFUND_001', 'Mercury', 'BATCH_002', 'matched', 'US421RF', 'REFUND_REF_001', '2025-09-30 14:00:00', '2025-09-30 14:00:00', '2025-09-30 14:00:00'),
('TXN_REFUND_005', -50.00, 'refunded', 'Mercury_REFUND_TEST', 'REF_005', 'REFUND_002', 'Mercury', 'BATCH_002', 'matched', 'US421RF', 'REFUND_REF_002', '2025-09-30 15:00:00', '2025-09-30 15:00:00', '2025-09-30 15:00:00');

-- Calculate the net settlement amount: 200 + 150 + 300 - 100 - 50 = 500
INSERT INTO settlements (
  id,
  merchant_id,
  bank_user_id,
  date,
  gross_settlement_amount,
  amount,
  total_transaction_count,
  status,
  merchant_tag,
  batch_number,
  inserted_at,
  updated_at
) VALUES (
  UNHEX(REPLACE(UUID(), '-', '')),
  'Mercury_REFUND_TEST',
  'Mercury_REFUND_TEST',
  '2025-09-30',
  500.00,
  500.00,
  5,
  'COMPLETED',
  'US421RF',
  'REFUND_TEST_BATCH',
  '2025-09-30 16:00:00',
  '2025-09-30 16:00:00'
);

-- Get the settlement ID for linking transactions
SET @settlement_hex_id = (SELECT HEX(id) FROM settlements WHERE bank_user_id = 'Mercury_REFUND_TEST' AND date = '2025-09-30');

-- Insert settlement_transactions records to link transactions to settlement
INSERT INTO settlement_transactions (
  id,
  transaction_id,
  transaction_amount,
  transaction_currency,
  transaction_status,
  transaction_time,
  mdr_charge,
  tax_on_mdr,
  net_received_amount,
  settlement_id,
  inserted_at,
  updated_at
) 
SELECT 
  UNHEX(REPLACE(UUID(), '-', '')),
  t.transaction_id,
  t.transaction_amount,
  'AED',
  t.status,
  t.settlement_date_time,
  0.00,
  0.00,
  t.transaction_amount,
  @settlement_hex_id,
  NOW(),
  NOW()
FROM transactions t
WHERE t.bank_user_id = 'Mercury_REFUND_TEST' 
  AND DATE(t.inserted_at) = '2025-09-30';

-- Verify the data
SELECT 'SETTLEMENT CREATED:' as status;
SELECT 
  bank_user_id,
  date,
  gross_settlement_amount,
  total_transaction_count,
  status
FROM settlements 
WHERE bank_user_id = 'Mercury_REFUND_TEST' AND date = '2025-09-30';

SELECT 'TRANSACTIONS CREATED:' as status;
SELECT 
  transaction_id,
  transaction_amount,
  status,
  refund_reference_id,
  'Type' = CASE 
    WHEN transaction_amount < 0 THEN 'REFUND'
    ELSE 'PAYMENT'
  END as transaction_type
FROM transactions 
WHERE bank_user_id = 'Mercury_REFUND_TEST' 
  AND DATE(inserted_at) = '2025-09-30'
ORDER BY transaction_amount DESC;

SELECT 'AMOUNT VERIFICATION:' as status;
SELECT 
  s.gross_settlement_amount as settlement_amount,
  SUM(t.transaction_amount) as calculated_total,
  (s.gross_settlement_amount - SUM(t.transaction_amount)) as difference
FROM settlements s
JOIN transactions t ON t.bank_user_id = s.bank_user_id AND DATE(t.inserted_at) = s.date
WHERE s.bank_user_id = 'Mercury_REFUND_TEST' AND s.date = '2025-09-30'
GROUP BY s.id, s.gross_settlement_amount;
