-- Migration: Add International BIN rule at order 40
-- Date: 2026-03-26
-- Description: Adds final RED missing rule from Excel specification

START TRANSACTION;

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

-- Step 2: Insert Order 40 - International BIN ( International Trax 100% 1 day)
INSERT INTO risk_rules (
  name,
  description,
  category,
  rule_type,
  execution_order,
  mcc_codes,
  parameters,
  enabled,
  inserted_at,
  updated_at
) VALUES
-- Cat A
(
  'International BIN ( International Trax 100% 1 day)',
  '100% transactions in 24h is on International BINs',
  'Cat A',
  'alert',
  40,
  '{}',
  '{"time_window_hours": 24, "international_bin_threshold_percent": 1.00, "min_transaction_count": 1}',
  1,
  NOW(),
  NOW()
),
-- Cat B
(
  'International BIN ( International Trax 100% 1 day)',
  '100% transactions in 24h is on International BINs',
  'Cat B',
  'alert',
  40,
  '{}',
  '{"time_window_hours": 24, "international_bin_threshold_percent": 1.00, "min_transaction_count": 1}',
  1,
  NOW(),
  NOW()
),
-- Cat C
(
  'International BIN ( International Trax 100% 1 day)',
  '100% transactions in 24h is on International BINs',
  'Cat C',
  'alert',
  40,
  '{}',
  '{"time_window_hours": 24, "international_bin_threshold_percent": 1.00, "min_transaction_count": 1}',
  1,
  NOW(),
  NOW()
),
-- Cat D
(
  'International BIN ( International Trax 100% 1 day)',
  '100% transactions in 24h is on International BINs',
  'Cat D',
  'alert',
  40,
  '{}',
  '{"time_window_hours": 24, "international_bin_threshold_percent": 1.00, "min_transaction_count": 1}',
  1,
  NOW(),
  NOW()
);

-- Step 3: Verification - Show newly added rule
SELECT 
  execution_order,
  name,
  description,
  category,
  rule_type,
  enabled
FROM risk_rules
WHERE execution_order = 40
ORDER BY category;

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

COMMIT;
