-- Migration: Add "Round Figure Transaction on same Terminal" rule at order 13
-- Date: 2026-03-26
-- Description: Adds missing RED rule from Excel specification

START TRANSACTION;

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

-- Step 2: Insert new rule for all 4 categories at order 13
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 on same Terminal',
  'Scenario which includes high frequency of transactions in round figures on any single Merchant (in Multiple of 100) using any card – Round figure transaction rate is more than 25% in last 90 days (Are highlighted in daily risk report for scrutiny and further actions)',
  'Cat A',
  'hold',
  13,
  '{}',
  '{"round_figure_threshold_percent": 0.25, "period_days": 90, "round_multiples": [100]}',
  1,
  NOW(),
  NOW()
),
-- Cat B
(
  'Round Figure Transaction on same Terminal',
  'Scenario which includes high frequency of transactions in round figures on any single Merchant (in Multiple of 100) using any card – Round figure transaction rate is more than 25% in last 90 days (Are highlighted in daily risk report for scrutiny and further actions)',
  'Cat B',
  'hold',
  13,
  '{}',
  '{"round_figure_threshold_percent": 0.25, "period_days": 90, "round_multiples": [100]}',
  1,
  NOW(),
  NOW()
),
-- Cat C
(
  'Round Figure Transaction on same Terminal',
  'Scenario which includes high frequency of transactions in round figures on any single Merchant (in Multiple of 100) using any card – Round figure transaction rate is more than 25% in last 90 days (Are highlighted in daily risk report for scrutiny and further actions)',
  'Cat C',
  'hold',
  13,
  '{}',
  '{"round_figure_threshold_percent": 0.25, "period_days": 90, "round_multiples": [100]}',
  1,
  NOW(),
  NOW()
),
-- Cat D
(
  'Round Figure Transaction on same Terminal',
  'Scenario which includes high frequency of transactions in round figures on any single Merchant (in Multiple of 100) using any card – Round figure transaction rate is more than 25% in last 90 days (Are highlighted in daily risk report for scrutiny and further actions)',
  'Cat D',
  'hold',
  13,
  '{}',
  '{"round_figure_threshold_percent": 0.25, "period_days": 90, "round_multiples": [100]}',
  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 on same Terminal'
GROUP BY name;

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

COMMIT;
