
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;

-- 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;
SELECT '  ✓ POS Transaction 1: $89.99 (Normal - should PASS)' as Status;
SELECT '  ✓ POS Transaction 2: $12,500.00 (HIGH AMOUNT - should trigger HOLD)' as Status;
SELECT '  ✓ POS Transaction 3: $5,000.00 (ROUND AMOUNT >= 5000 should trigger HOLD + ALERT)' as Status;

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 '============================================================' 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 '';

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

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

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 '';
