-- Migration: Add "Round Figure Transaction using same card at Multiple Merchants" rule at order 12
-- Date: 2026-03-26
-- Description: Adds missing RED rule from Excel specification

START TRANSACTION;

-- Step 1: Shift existing rules at order 12 and above to make space
UPDATE risk_rules 
SET execution_order = execution_order + 1 
WHERE execution_order >= 12 
AND category IN ('Cat A', 'Cat B', 'Cat C', 'Cat D');

-- Step 2: Insert new rule for all 4 categories at order 12
INSERT INTO risk_rules (
  name,
  description,
  category,
  rule_type,
  execution_order,
  mcc_codes,
  parameters,
  enabled,
  inserted_at,
  updated_at
) VALUES
-- Cat A
(
  'Round Figure Transaction using same card at Multiple Merchants',
  'Scenario which includes transaction in round figures (In Multiple of 100) using the same card and at multiple merchant location in the same city in a same day – Sum Transaction value => 2000 AED (Are highlighted in daily risk report for scrutiny and further actions)',
  'Cat A',
  'hold',
  12,
  '{}',
  '{"round_figure_multiple": 100, "min_merchant_count": 2, "time_window_hours": 24, "same_city_required": true, "min_total_amount": 2000, "currency": "AED"}',
  1,
  NOW(),
  NOW()
),
-- Cat B
(
  'Round Figure Transaction using same card at Multiple Merchants',
  'Scenario which includes transaction in round figures (In Multiple of 100) using the same card and at multiple merchant location in the same city in a same day – Sum Transaction value => 2000 AED (Are highlighted in daily risk report for scrutiny and further actions)',
  'Cat B',
  'hold',
  12,
  '{}',
  '{"round_figure_multiple": 100, "min_merchant_count": 2, "time_window_hours": 24, "same_city_required": true, "min_total_amount": 2000, "currency": "AED"}',
  1,
  NOW(),
  NOW()
),
-- Cat C
(
  'Round Figure Transaction using same card at Multiple Merchants',
  'Scenario which includes transaction in round figures (In Multiple of 100) using the same card and at multiple merchant location in the same city in a same day – Sum Transaction value => 2000 AED (Are highlighted in daily risk report for scrutiny and further actions)',
  'Cat C',
  'hold',
  12,
  '{}',
  '{"round_figure_multiple": 100, "min_merchant_count": 2, "time_window_hours": 24, "same_city_required": true, "min_total_amount": 2000, "currency": "AED"}',
  1,
  NOW(),
  NOW()
),
-- Cat D
(
  'Round Figure Transaction using same card at Multiple Merchants',
  'Scenario which includes transaction in round figures (In Multiple of 100) using the same card and at multiple merchant location in the same city in a same day – Sum Transaction value => 2000 AED (Are highlighted in daily risk report for scrutiny and further actions)',
  'Cat D',
  'hold',
  12,
  '{}',
  '{"round_figure_multiple": 100, "min_merchant_count": 2, "time_window_hours": 24, "same_city_required": true, "min_total_amount": 2000, "currency": "AED"}',
  1,
  NOW(),
  NOW()
);

-- Step 3: Verification - Show newly added rule
SELECT 
  'New Rule Added' as Status,
  name,
  GROUP_CONCAT(CONCAT(category, ': execution_order: ', execution_order, ', enabled: ', enabled, ', rule_type: ', rule_type) SEPARATOR '\n') as details
FROM risk_rules
WHERE name = 'Round Figure Transaction using same card at Multiple Merchants'
GROUP BY name;

-- Step 4: Verification - Count rules per category
SELECT category, COUNT(*) as total_rules
FROM risk_rules
GROUP BY category;

COMMIT;
