-- Risk Management Test Data
-- This script inserts sample risk rules and test transactions for both POS and QR
-- Database: shukria_transactions

-- Clean up existing test data
DELETE FROM risk_rule_hits WHERE transaction_id LIKE 'TEST_%' OR transaction_id IN (SELECT transaction_id FROM pos_transactions WHERE merchant_id = 'TEST_MERCHANT_001');
DELETE FROM pos_transactions WHERE merchant_id = 'TEST_MERCHANT_001';
DELETE FROM transactions WHERE merchant_id = 'TEST_MERCHANT_001';
DELETE FROM risk_rules WHERE category = 'TestCategory';
DELETE FROM merchants WHERE name = 'Test Risk Merchant Store';

SELECT '=== CLEANING COMPLETE ===\n' as Status;

-- Insert test merchant
INSERT INTO merchants (id, name, email, phone, status, category, mcc_code, risk_tier, inserted_at, updated_at)
VALUES (
  UNHEX(REPLACE(UUID(), '-', '')),
  'Test Risk Merchant Store',
  'test@riskmerchant.com',
  '+971501234567',
  'active',
  'Cat A',
  '5411',
  'SME/SMB',
  NOW(),
  NOW()
);

SELECT '✓ Created test merchant' as Status;

-- Get the merchant ID for reference
SET @merchant_id = (SELECT HEX(id) FROM merchants WHERE name = 'Test Risk Merchant Store' LIMIT 1);

-- Create Risk Rules
INSERT INTO risk_rules (name, description, category, rule_type, enabled, execution_order, parameters, inserted_at, updated_at)
VALUES 
('High Amount Transaction Test', 'Flags transactions over $5,000', 'TestCategory', 'hold', TRUE, 1, 
 JSON_OBJECT('amount_threshold', 5000.0), NOW(), NOW()),
 
('Rapid Transaction Alert', 'Alerts on more than 3 transactions within 5 minutes', 'TestCategory', 'alert', TRUE, 2,
 JSON_OBJECT('transaction_count', 3, 'time_window_minutes', 5), NOW(), NOW()),
 
('Round Amount Pattern', 'Flags suspiciously round transaction amounts', 'TestCategory', 'alert', TRUE, 3,
 JSON_OBJECT('round_threshold', 1000), NOW(), NOW());

SELECT '✓ Created 3 risk rules' as Status;

-- Insert QR Test Transactions (using transactions table)
SELECT '\n--- Creating QR Test Transactions ---' as Status;

-- QR 1: Normal transaction (SHOULD PASS)
INSERT INTO transactions (
  patient_name, charge_rate, email, mobile_no, processing_id, transaction_id, 
  transaction_amount, merchant_id, device_id, transaction_location, status, 
  inserted_at, updated_at
) VALUES (
  'Customer QR1', 150.75, 'customer1@test.com', '+971501111111', 'PROC_QR_001',
  'TEST_QR_001', 150.75, 'TEST_MERCHANT_001', 'QR_DEVICE_001', 'Store Counter A',
  'success', DATE_SUB(NOW(), INTERVAL 1 HOUR), DATE_SUB(NOW(), INTERVAL 1 HOUR)
);
SELECT '  ✓ QR Transaction 1: $150.75 (Normal - should PASS)' as Status;

-- QR 2: High amount (SHOULD TRIGGER HOLD)  
INSERT INTO transactions (
  patient_name, charge_rate, email, mobile_no, processing_id, transaction_id,
  transaction_amount, merchant_id, device_id, transaction_location, status,
  inserted_at, updated_at
) VALUES (
  'Customer QR2', 7500.00, 'customer2@test.com', '+971501111112', 'PROC_QR_002',
  'TEST_QR_002', 7500.00, 'TEST_MERCHANT_001', 'QR_DEVICE_001', 'Store Counter A',
  'success', DATE_SUB(NOW(), INTERVAL 50 MINUTE), DATE_SUB(NOW(), INTERVAL 50 MINUTE)
);
SELECT '  ✓ QR Transaction 2: $7,500.00 (HIGH AMOUNT - should trigger HOLD)' as Status;

-- QR 3: Round amount (SHOULD TRIGGER ALERT)
INSERT INTO transactions (
  patient_name, charge_rate, email, mobile_no, processing_id, transaction_id,
  transaction_amount, merchant_id, device_id, transaction_location, status,
  inserted_at, updated_at
) VALUES (
  'Customer QR3', 3000.00, 'customer3@test.com', '+971501111113', 'PROC_QR_003',
  'TEST_QR_003', 3000.00, 'TEST_MERCHANT_001', 'QR_DEVICE_002', 'Store Counter B',
  'success', DATE_SUB(NOW(), INTERVAL 40 MINUTE), DATE_SUB(NOW(), INTERVAL 40 MINUTE)
);
SELECT '  ✓ QR Transaction 3: $3,000.00 (ROUND AMOUNT - should trigger ALERT)' as Status;

-- QR 4-6: Rapid succession (SHOULD TRIGGER ALERT for rapid transactions)
INSERT INTO transactions (patient_name, charge_rate, email, mobile_no, processing_id, transaction_id, transaction_amount, merchant_id, device_id, transaction_location, status, inserted_at, updated_at) 
VALUES 
('Customer QR4', 250.50, 'customer4@test.com', '+971501111114', 'PROC_QR_004', 'TEST_QR_004', 250.50, 'TEST_MERCHANT_001', 'QR_DEVICE_003', 'Store Counter C', 'success', DATE_SUB(NOW(), INTERVAL 5 MINUTE), DATE_SUB(NOW(), INTERVAL 5 MINUTE)),
('Customer QR5', 189.99, 'customer5@test.com', '+971501111115', 'PROC_QR_005', 'TEST_QR_005', 189.99, 'TEST_MERCHANT_001', 'QR_DEVICE_003', 'Store Counter C', 'success', DATE_SUB(NOW(), INTERVAL 3 MINUTE), DATE_SUB(NOW(), INTERVAL 3 MINUTE)),
('Customer QR6', 425.00, 'customer6@test.com', '+971501111116', 'PROC_QR_006', 'TEST_QR_006', 425.00, 'TEST_MERCHANT_001', 'QR_DEVICE_003', 'Store Counter C', 'success', DATE_SUB(NOW(), INTERVAL 1 MINUTE), DATE_SUB(NOW(), INTERVAL 1 MINUTE));

SELECT '  ✓ QR Transactions 4-6: Rapid succession (should trigger RAPID ALERT)' as Status;

-- Insert POS Test Transactions
SELECT '\n--- Creating POS Test Transactions ---' as Status;

-- POS 1: Normal transaction (SHOULD PASS)
INSERT INTO pos_transactions (
  transaction_id, merchant_id, terminal_id, amount, currency, card_type,
  card_number_masked, status, transaction_time, location, inserted_at, updated_at
) VALUES (
  'TEST_POS_001', 'TEST_MERCHANT_001', 'POS_TERM_001', 89.99, 'AED', 'domestic',
  '****1234', 'success', DATE_SUB(NOW(), INTERVAL 55 MINUTE), 'POS Terminal 1', 
  DATE_SUB(NOW(), INTERVAL 55 MINUTE), DATE_SUB(NOW(), INTERVAL 55 MINUTE)
);
SELECT '  ✓ POS Transaction 1: $89.99 (Normal - should PASS)' as Status;

-- POS 2: High amount (SHOULD TRIGGER HOLD)
INSERT INTO pos_transactions (
  transaction_id, merchant_id, terminal_id, amount, currency, card_type,
  card_number_masked, status, transaction_time, location, inserted_at, updated_at
) VALUES (
  'TEST_POS_002', 'TEST_MERCHANT_001', 'POS_TERM_001', 12500.00, 'AED', 'domestic',
  '****5678', 'success', DATE_SUB(NOW(), INTERVAL 45 MINUTE), 'POS Terminal 1',
  DATE_SUB(NOW(), INTERVAL 45 MINUTE), DATE_SUB(NOW(), INTERVAL 45 MINUTE)
);
SELECT '  ✓ POS Transaction 2: $12,500.00 (HIGH AMOUNT - should trigger HOLD)' as Status;

-- POS 3: Round amount (SHOULD TRIGGER ALERT)
INSERT INTO pos_transactions (
  transaction_id, merchant_id, terminal_id, amount, currency, card_type,
  card_number_masked, status, transaction_time, location, inserted_at, updated_at
) VALUES (
  'TEST_POS_003', 'TEST_MERCHANT_001', 'POS_TERM_002', 5000.00, 'AED', 'international',
  '****9012', 'success', DATE_SUB(NOW(), INTERVAL 30 MINUTE), 'POS Terminal 2',
  DATE_SUB(NOW(), INTERVAL 30 MINUTE), DATE_SUB(NOW(), INTERVAL 30 MINUTE)
);
SELECT '  ✓ POS Transaction 3: $5,000.00 (ROUND AMOUNT >= 5000 should trigger HOLD + ALERT)' as Status;

-- POS 4-7: Rapid succession (SHOULD TRIGGER ALERT)
INSERT INTO pos_transactions (transaction_id, merchant_id, terminal_id, amount, currency, card_type, card_number_masked, status, transaction_time, location, inserted_at, updated_at)
VALUES
('TEST_POS_004', 'TEST_MERCHANT_001', 'POS_TERM_003', 125.00, 'AED', 'domestic', '****3456', 'success', DATE_SUB(NOW(), INTERVAL 240 SECOND), 'POS Terminal 3', DATE_SUB(NOW(), INTERVAL 240 SECOND), DATE_SUB(NOW(), INTERVAL 240 SECOND)),
('TEST_POS_005', 'TEST_MERCHANT_001', 'POS_TERM_003', 235.50, 'AED', 'domestic', '****3456', 'success', DATE_SUB(NOW(), INTERVAL 150 SECOND), 'POS Terminal 3', DATE_SUB(NOW(), INTERVAL 150 SECOND), DATE_SUB(NOW(), INTERVAL 150 SECOND)),
('TEST_POS_006', 'TEST_MERCHANT_001', 'POS_TERM_003', 99.99, 'AED', 'domestic', '****3456', 'success', DATE_SUB(NOW(), INTERVAL 90 SECOND), 'POS Terminal 3', DATE_SUB(NOW(), INTERVAL 90 SECOND), DATE_SUB(NOW(), INTERVAL 90 SECOND)),
('TEST_POS_007', 'TEST_MERCHANT_001', 'POS_TERM_003', 445.00, 'AED', 'domestic', '****3456', 'success', DATE_SUB(NOW(), INTERVAL 30 SECOND), 'POS Terminal 3', DATE_SUB(NOW(), INTERVAL 30 SECOND), DATE_SUB(NOW(), INTERVAL 30 SECOND));

SELECT '  ✓ POS Transactions 4-7: Rapid succession (should trigger RAPID ALERT)' as Status;

-- Summary of created data
SELECT '\n============================================================' as '';
SELECT '📊 DATA CREATION SUMMARY' as '';
SELECT '============================================================' as '';
SELECT CONCAT('Merchant Created: 1 (', 'TEST_MERCHANT_001', ')') as '';
SELECT CONCAT('QR Transactions: ', COUNT(*), ' transactions') as '' FROM transactions WHERE merchant_id = 'TEST_MERCHANT_001';
SELECT CONCAT('POS Transactions: ', COUNT(*), ' transactions') as '' FROM pos_transactions WHERE merchant_id = 'TEST_MERCHANT_001';
SELECT CONCAT('Risk Rules: ', COUNT(*), ' rules') as '' FROM risk_rules WHERE category = 'TestCategory';
SELECT '============================================================' as '';

-- Analysis: Which transactions meet which rules
SELECT '\n============================================================' as '';
SELECT '🔍 TRANSACTION RISK ANALYSIS' as '';
SELECT '============================================================' as '';

-- QR Transactions Analysis
SELECT '\n--- QR TRANSACTIONS ---' as '';

SELECT 
  transaction_id as 'Transaction ID',
  CONCAT('$', FORMAT(transaction_amount, 2)) as 'Amount',
  CASE
    WHEN transaction_amount > 5000 THEN '⚠ HOLD (High Amount > $5,000)'
    WHEN transaction_amount % 1000 = 0 AND transaction_amount >= 1000 THEN '⚠ ALERT (Round Amount)'
    ELSE '✓ PASS'
  END as 'Rule Evaluation',
  DATE_FORMAT(inserted_at, '%Y-%m-%d %H:%i:%s') as 'Time'
FROM transactions
WHERE merchant_id = 'TEST_MERCHANT_001'
ORDER BY inserted_at;

-- POS Transactions Analysis
SELECT '\n--- POS TRANSACTIONS ---' as '';

SELECT 
  transaction_id as 'Transaction ID',
  CONCAT('$', FORMAT(amount, 2)) as 'Amount',
  card_type as 'Card Type',
  CASE
    WHEN amount > 5000 THEN '⚠ HOLD (High Amount > $5,000)'
    WHEN amount % 1000 = 0 AND amount >= 1000 THEN '⚠ ALERT (Round Amount)'
    ELSE '✓ PASS'
  END as 'Rule Evaluation',
  DATE_FORMAT(transaction_time, '%Y-%m-%d %H:%i:%s') as 'Time'
FROM pos_transactions
WHERE merchant_id = 'TEST_MERCHANT_001'
ORDER BY transaction_time;

-- Rapid Transaction Detection
SELECT '\n--- RAPID TRANSACTION DETECTION ---' as '';

SELECT 
  'QR_DEVICE_003' as 'Device/Terminal',
  'QR' as 'Type',
  COUNT(*) as 'Transactions in 5 min',
  CASE 
    WHEN COUNT(*) > 3 THEN '⚠ ALERT (More than 3 txns in 5 min)'
    ELSE '✓ Normal'
  END as 'Status',
  CONCAT('$', FORMAT(SUM(transaction_amount), 2)) as 'Total Amount'
FROM transactions
WHERE merchant_id = 'TEST_MERCHANT_001' 
  AND device_id = 'QR_DEVICE_003'
  AND inserted_at >= DATE_SUB(NOW(), INTERVAL 5 MINUTE)
UNION ALL
SELECT  
  'POS_TERM_003' as 'Device/Terminal',
  'POS' as 'Type',
  COUNT(*) as 'Transactions in 5 min',
  CASE 
    WHEN COUNT(*) > 3 THEN '⚠ ALERT (More than 3 txns in 5 min)'
    ELSE '✓ Normal'
  END as 'Status',
  CONCAT('$', FORMAT(SUM(amount), 2)) as 'Total Amount'
FROM pos_transactions
WHERE merchant_id = 'TEST_MERCHANT_001'
  AND terminal_id = 'POS_TERM_003'
  AND transaction_time >= DATE_SUB(NOW(), INTERVAL 5 MINUTE);

-- Overall Statistics
SELECT '\n============================================================' as '';
SELECT '📈 OVERALL STATISTICS' as '';
SELECT '============================================================' as '';

SELECT 
  'QR' as 'Transaction Type',
  COUNT(*) as 'Total',
  SUM(CASE WHEN transaction_amount > 5000 THEN 1 ELSE 0 END) as 'HIGH AMOUNT (HOLD)',
  SUM(CASE WHEN transaction_amount % 1000 = 0 AND transaction_amount >= 1000 AND transaction_amount <= 5000 THEN 1 ELSE 0 END) as 'ROUND AMOUNT (ALERT)',
  SUM(CASE WHEN transaction_amount <= 5000 AND NOT (transaction_amount % 1000 = 0 AND transaction_amount >= 1000) THEN 1 ELSE 0 END) as 'PASSED'
FROM transactions
WHERE merchant_id = 'TEST_MERCHANT_001'
UNION ALL
SELECT 
  'POS' as 'Transaction Type',
  COUNT(*) as 'Total',
  SUM(CASE WHEN amount > 5000 THEN 1 ELSE 0 END) as 'HIGH AMOUNT (HOLD)',
  SUM(CASE WHEN amount % 1000 = 0 AND amount >= 1000 AND amount <= 5000 THEN 1 ELSE 0 END) as 'ROUND AMOUNT (ALERT)',
  SUM(CASE WHEN amount <= 5000 AND NOT (amount % 1000 = 0 AND amount >= 1000) THEN 1 ELSE 0 END) as 'PASSED'
FROM pos_transactions
WHERE merchant_id = 'TEST_MERCHANT_001';

SELECT '============================================================' as '';
SELECT '\n✅ Test data inserted successfully!' as '';
SELECT 'Visit http://demo.ctrmv.com:4099/risk-management/rules to manage rules' as '';
SELECT 'Visit http://demo.ctrmv.com:4099/risk-management/supervisor to review flagged transactions' as '';
SELECT '============================================================' as '';
