-- Test data for Mercury_REFUND_TEST refund functionality
-- This script creates sample transactions, refunds, and settlement data

USE shukria_transactions;

-- Clean up existing test data first
DELETE FROM settlement_transactions WHERE settlement_id IN (
  SELECT HEX(id) FROM settlements WHERE bank_user_id = 'Mercury_REFUND_TEST'
);

DELETE FROM transactions WHERE bank_user_id = 'Mercury_REFUND_TEST';
DELETE FROM settlements WHERE bank_user_id = 'Mercury_REFUND_TEST';

-- Insert sample regular 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,
  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', NOW(), NOW()),
('TXN_REFUND_002', 150.00, 'success', 'Mercury_REFUND_TEST', 'REF_002', 'PAY_002', 'Mercury', 'BATCH_001', 'matched', 'US421RF', NOW(), NOW()),
('TXN_REFUND_003', 300.00, 'success', 'Mercury_REFUND_TEST', 'REF_003', 'PAY_003', 'Mercury', 'BATCH_001', 'matched', 'US421RF', NOW(), NOW()),

-- Refund transactions (negative amounts)
('TXN_REFUND_004', -100.00, 'refunded', 'Mercury_REFUND_TEST', 'REF_004', 'PAY_004', 'Mercury', 'BATCH_002', 'matched', 'US421RF', NOW(), NOW()),
('TXN_REFUND_005', -50.00, 'refunded', 'Mercury_REFUND_TEST', 'REF_005', 'PAY_005', 'Mercury', 'BATCH_002', 'matched', 'US421RF', NOW(), NOW());

-- Update refund reference IDs for refund transactions
UPDATE transactions 
SET refund_reference_id = CASE 
  WHEN transaction_id = 'TXN_REFUND_004' THEN 'REFUND_001'
  WHEN transaction_id = 'TXN_REFUND_005' THEN 'REFUND_002'
  ELSE refund_reference_id
END
WHERE bank_user_id = 'Mercury_REFUND_TEST' AND transaction_amount < 0;

-- Create a settlement record with correct total amount
-- Total: 200 + 150 + 300 - 100 - 50 = 500
INSERT INTO settlements (
  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',
  '2025-09-30',
  500.00,
  500.00,
  5,
  'COMPLETED',
  'US421RF',
  'COMBINED_BATCH',
  NOW(),
  NOW()
);

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

-- Insert settlement_transactions records
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(), '-', '')),
  transaction_id,
  transaction_amount,
  'AED',
  status,
  COALESCE(settlement_date_time, inserted_at),
  0,
  0,
  transaction_amount,
  @settlement_id,
  NOW(),
  NOW()
FROM transactions 
WHERE bank_user_id = 'Mercury_REFUND_TEST';

-- Verify the data
SELECT 'SETTLEMENT DATA:' as info;
SELECT 
  bank_user_id,
  date,
  gross_settlement_amount,
  amount,
  total_transaction_count,
  status,
  HEX(id) as settlement_id
FROM settlements 
WHERE bank_user_id = 'Mercury_REFUND_TEST';

SELECT 'TRANSACTION DATA:' as info;
SELECT 
  transaction_id,
  transaction_amount,
  status,
  refund_reference_id,
  batch_number
FROM transactions 
WHERE bank_user_id = 'Mercury_REFUND_TEST'
ORDER BY transaction_amount DESC;

SELECT 'SETTLEMENT TRANSACTIONS DATA:' as info;
SELECT 
  st.transaction_id,
  st.transaction_amount,
  st.transaction_status,
  st.settlement_id
FROM settlement_transactions st
WHERE st.settlement_id = @settlement_id;

SELECT 'AMOUNT VERIFICATION:' as info;
SELECT 
  s.gross_settlement_amount as settlement_total,
  SUM(st.transaction_amount) as transactions_sum,
  (s.gross_settlement_amount - SUM(st.transaction_amount)) as difference
FROM settlements s
JOIN settlement_transactions st ON HEX(s.id) = st.settlement_id
WHERE s.bank_user_id = 'Mercury_REFUND_TEST'
GROUP BY s.id, s.gross_settlement_amount;
