-- Insert Risk Rule Hits for flagged transactions
-- This populates the supervisor dashboard with transactions that triggered rules

-- Get rule IDs
SET @high_amount_rule_id = (SELECT id FROM risk_rules WHERE name = 'High Amount Transaction Test' LIMIT 1);
SET @rapid_txn_rule_id = (SELECT id FROM risk_rules WHERE name = 'Rapid Transaction Alert' LIMIT 1);
SET @round_amount_rule_id = (SELECT id FROM risk_rules WHERE name = 'Round Amount Pattern' LIMIT 1);

-- Insert risk hits for QR transactions that triggered rules
-- Using actual numeric transaction IDs from database

-- QR Transaction 12612 (TEST_QR_002): High amount ($7,500) - HOLD
INSERT INTO risk_rule_hits (
  transaction_id, transaction_type, merchant_id, category, status, 
  triggered_at, rule_id, metadata, inserted_at, updated_at
) VALUES (
  12612, 'qr', 'TEST_MERCHANT_001', 'Cat A', 'hold',
  DATE_SUB(NOW(), INTERVAL 50 MINUTE), @high_amount_rule_id,
  JSON_OBJECT('amount', 7500.00, 'threshold', 5000.00, 'reason', 'Transaction amount exceeds threshold', 'transaction_ref', 'TEST_QR_002'),
  DATE_SUB(NOW(), INTERVAL 50 MINUTE), DATE_SUB(NOW(), INTERVAL 50 MINUTE)
);

-- QR Transaction 12613 (TEST_QR_003): Round amount ($3,000) - ALERT
INSERT INTO risk_rule_hits (
  transaction_id, transaction_type, merchant_id, category, status,
  triggered_at, rule_id, metadata, inserted_at, updated_at
) VALUES (
  12613, 'qr', 'TEST_MERCHANT_001', 'Cat A', 'alert',
  DATE_SUB(NOW(), INTERVAL 40 MINUTE), @round_amount_rule_id,
  JSON_OBJECT('amount', 3000.00, 'pattern', 'round_amount', 'reason', 'Suspiciously round transaction amount', 'transaction_ref', 'TEST_QR_003'),
  DATE_SUB(NOW(), INTERVAL 40 MINUTE), DATE_SUB(NOW(), INTERVAL 40 MINUTE)
);

-- QR Transaction 12614 (TEST_QR_004): Rapid transactions - ALERT
INSERT INTO risk_rule_hits (
  transaction_id, transaction_type, merchant_id, category, status,
  triggered_at, rule_id, metadata, inserted_at, updated_at
) VALUES (
  12614, 'qr', 'TEST_MERCHANT_001', 'Cat A', 'alert',
  DATE_SUB(NOW(), INTERVAL 5 MINUTE), @rapid_txn_rule_id,
  JSON_OBJECT('count', 3, 'time_window', 5, 'device_id', 'QR_DEVICE_003', 'reason', 'Rapid transaction pattern detected', 'transaction_ref', 'TEST_QR_004'),
  DATE_SUB(NOW(), INTERVAL 5 MINUTE), DATE_SUB(NOW(), INTERVAL 5 MINUTE)
);

-- Insert risk hits for POS transactions that triggered rules

-- POS Transaction 5 (TEST_POS_002): High amount ($12,500) - HOLD
INSERT INTO risk_rule_hits (
  transaction_id, transaction_type, merchant_id, category, status,
  triggered_at, rule_id, metadata, inserted_at, updated_at
) VALUES (
  5, 'pos', 'TEST_MERCHANT_001', 'Cat A', 'hold',
  DATE_SUB(NOW(), INTERVAL 45 MINUTE), @high_amount_rule_id,
  JSON_OBJECT('amount', 12500.00, 'threshold', 5000.00, 'card_type', 'domestic', 'reason', 'Transaction amount exceeds threshold', 'transaction_ref', 'TEST_POS_002'),
  DATE_SUB(NOW(), INTERVAL 45 MINUTE), DATE_SUB(NOW(), INTERVAL 45 MINUTE)
);

-- POS Transaction 6 (TEST_POS_003): $5,000 - Both HOLD and ALERT
INSERT INTO risk_rule_hits (
  transaction_id, transaction_type, merchant_id, category, status,
  triggered_at, rule_id, metadata, inserted_at, updated_at
) VALUES 
(
  6, 'pos', 'TEST_MERCHANT_001', 'Cat A', 'hold',
  DATE_SUB(NOW(), INTERVAL 30 MINUTE), @high_amount_rule_id,
  JSON_OBJECT('amount', 5000.00, 'threshold', 5000.00, 'card_type', 'international', 'reason', 'Transaction amount at threshold boundary', 'transaction_ref', 'TEST_POS_003'),
  DATE_SUB(NOW(), INTERVAL 30 MINUTE), DATE_SUB(NOW(), INTERVAL 30 MINUTE)
),
(
  6, 'pos', 'TEST_MERCHANT_001', 'Cat A', 'alert',
  DATE_SUB(NOW(), INTERVAL 30 MINUTE), @round_amount_rule_id,
  JSON_OBJECT('amount', 5000.00, 'pattern', 'round_amount', 'card_type', 'international', 'reason', 'Suspiciously round amount on international card', 'transaction_ref', 'TEST_POS_003'),
  DATE_SUB(NOW(), INTERVAL 30 MINUTE), DATE_SUB(NOW(), INTERVAL 30 MINUTE)
);

-- POS Transaction 10 (TEST_POS_007): Rapid transactions (4 in 4 minutes) - ALERT
INSERT INTO risk_rule_hits (
  transaction_id, transaction_type, merchant_id, category, status,
  triggered_at, rule_id, metadata, inserted_at, updated_at
) VALUES (
  10, 'pos', 'TEST_MERCHANT_001', 'Cat A', 'alert',
  DATE_SUB(NOW(), INTERVAL 30 SECOND), @rapid_txn_rule_id,
  JSON_OBJECT('count', 4, 'time_window', 4, 'terminal_id', 'POS_TERM_003', 'reason', 'More than 3 transactions in 5 minutes', 'transaction_ref', 'TEST_POS_007'),
  DATE_SUB(NOW(), INTERVAL 30 SECOND), DATE_SUB(NOW(), INTERVAL 30 SECOND)
);

-- Display results
SELECT '\n============================================================' as '';
SELECT '✅ RISK RULE HITS CREATED' as '';
SELECT '============================================================' as '';

SELECT 
  CONCAT('Total Hits: ', COUNT(*)) as 'Summary'
FROM risk_rule_hits 
WHERE merchant_id = 'TEST_MERCHANT_001';

SELECT 
  r.transaction_id as 'Txn ID',
  r.transaction_type as 'Type',
  r.status as 'Status',
  (SELECT name FROM risk_rules WHERE id = r.rule_id LIMIT 1) as 'Rule Triggered',
  JSON_UNQUOTE(JSON_EXTRACT(r.metadata, '$.transaction_ref')) as 'Reference',
  JSON_UNQUOTE(JSON_EXTRACT(r.metadata, '$.amount')) as 'Amount',
  DATE_FORMAT(r.triggered_at, '%H:%i:%s') as 'Time'
FROM risk_rule_hits r
WHERE merchant_id = 'TEST_MERCHANT_001'
ORDER BY r.triggered_at DESC;

SELECT '============================================================' as '';
SELECT 'Status Breakdown:' as '';
SELECT 
  status as 'Status',
  COUNT(*) as 'Count'
FROM risk_rule_hits
WHERE merchant_id = 'TEST_MERCHANT_001'
GROUP BY status;

SELECT '============================================================' as '';
SELECT '🔍 Now refresh the supervisor dashboard to see flagged transactions!' as '';
SELECT 'Visit: http://demo.ctrmv.com:4099/risk-management/supervisor' as '';
SELECT '============================================================' as '';
