-- Migration: Add Orders 20 and 21 rules for Cat A
-- Rule 20: Out-of-Profile Geographic Behavior (Domestic to International Shift)
-- Rule 21: Excessive Refunds or Reversals
-- Date: 2026-03-27

START TRANSACTION;

-- Insert Order 20 rule for Cat A
INSERT INTO risk_rules (
  name,
  description,
  category,
  rule_type,
  execution_order,
  mcc_codes,
  parameters,
  enabled,
  inserted_at,
  updated_at
) VALUES (
  'Out-of-Profile Geographic Behavior (Domestic to International Shift)',
  'Merchant suddenly starts receiving significant transactions from cards issued in new, unrelated geographies (Immediate effective date)',
  'Cat A',
  'hold',
  20,
  '{"all": true}',
  '{"shift_threshold_percent": 0.30, "period_days": 7, "geographic_shift": "domestic_to_international", "action": "hold"}',
  1,
  NOW(),
  NOW()
);

-- Insert Order 21 rule for Cat A
INSERT INTO risk_rules (
  name,
  description,
  category,
  rule_type,
  execution_order,
  mcc_codes,
  parameters,
  enabled,
  inserted_at,
  updated_at
) VALUES (
  'Excessive Refunds or Reversals',
  'Scenario: Merchants with refund/reversal rates exceeding 10% of total transactions greater than Rs 50 of Total',
  'Cat A',
  'alert',
  21,
  '{"all": true}',
  '{"refund_threshold_percent": 0.10, "min_transaction_amount": 50, "currency": "AED", "action": "alert_generation"}',
  1,
  NOW(),
  NOW()
);

-- Verify insertion
SELECT 
  execution_order,
  name,
  category,
  rule_type,
  enabled
FROM risk_rules 
WHERE execution_order IN (20, 21)
ORDER BY execution_order, category;

-- Count rules per category
SELECT 
  category,
  COUNT(*) as total_rules
FROM risk_rules 
GROUP BY category 
ORDER BY category;

COMMIT;
