-- Migration: Add Alert Generation rules at orders 22-27
-- Date: 2026-03-26
-- Description: Adds missing RED rules from Excel specification (Alert generation category)

START TRANSACTION;

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

-- Step 2: Insert Order 22 - High Chargeback Rate
INSERT INTO risk_rules (
  name,
  description,
  category,
  rule_type,
  execution_order,
  mcc_codes,
  parameters,
  enabled,
  inserted_at,
  updated_at
) VALUES
('High Chargeback Rate', 'Merchants with chargeback rates exceeding 1% of total transactions or more in a 30-day period', 'Cat A', 'alert', 22, '{}', '{"chargeback_threshold_percent": 0.01, "period_days": 30}', 1, NOW(), NOW()),
('High Chargeback Rate', 'Merchants with chargeback rates exceeding 1% of total transactions or more in a 30-day period', 'Cat B', 'alert', 22, '{}', '{"chargeback_threshold_percent": 0.01, "period_days": 30}', 1, NOW(), NOW()),
('High Chargeback Rate', 'Merchants with chargeback rates exceeding 1% of total transactions or more in a 30-day period', 'Cat C', 'alert', 22, '{}', '{"chargeback_threshold_percent": 0.01, "period_days": 30}', 1, NOW(), NOW()),
('High Chargeback Rate', 'Merchants with chargeback rates exceeding 1% of total transactions or more in a 30-day period', 'Cat D', 'alert', 22, '{}', '{"chargeback_threshold_percent": 0.01, "period_days": 30}', 1, NOW(), NOW());

-- Step 3: Insert Order 23 - High Fraud Rate
INSERT INTO risk_rules (
  name,
  description,
  category,
  rule_type,
  execution_order,
  mcc_codes,
  parameters,
  enabled,
  inserted_at,
  updated_at
) VALUES
('High Fraud Rate', 'Merchants with fraud rates exceeding 0.5% of total transactions or more in a calendar month. All MCCs to be included. Compare total refunds to total successful sales', 'Cat A', 'alert', 23, '{}', '{"fraud_threshold_percent": 0.005, "period_days": 30}', 1, NOW(), NOW()),
('High Fraud Rate', 'Merchants with fraud rates exceeding 0.5% of total transactions or more in a calendar month. All MCCs to be included. Compare total refunds to total successful sales', 'Cat B', 'alert', 23, '{}', '{"fraud_threshold_percent": 0.005, "period_days": 30}', 1, NOW(), NOW()),
('High Fraud Rate', 'Merchants with fraud rates exceeding 0.5% of total transactions or more in a calendar month. All MCCs to be included. Compare total refunds to total successful sales', 'Cat C', 'alert', 23, '{}', '{"fraud_threshold_percent": 0.005, "period_days": 30}', 1, NOW(), NOW()),
('High Fraud Rate', 'Merchants with fraud rates exceeding 0.5% of total transactions or more in a calendar month. All MCCs to be included. Compare total refunds to total successful sales', 'Cat D', 'alert', 23, '{}', '{"fraud_threshold_percent": 0.005, "period_days": 30}', 1, NOW(), NOW());

-- Step 4: Insert Order 24 - Round or Unusual Amount Patterns
INSERT INTO risk_rules (
  name,
  description,
  category,
  rule_type,
  execution_order,
  mcc_codes,
  parameters,
  enabled,
  inserted_at,
  updated_at
) VALUES
('Round or Unusual Amount Patterns', 'Unusually high proportion (>25%) of transactions in round figures (multiples of 100/1000) at a single merchant within a rolling 90-day window.', 'Cat A', 'alert', 24, '{}', '{"round_figure_threshold_percent": 0.25, "period_days": 90, "round_multiples": [100, 1000]}', 1, NOW(), NOW()),
('Round or Unusual Amount Patterns', 'Unusually high proportion (>25%) of transactions in round figures (multiples of 100/1000) at a single merchant within a rolling 90-day window.', 'Cat B', 'alert', 24, '{}', '{"round_figure_threshold_percent": 0.25, "period_days": 90, "round_multiples": [100, 1000]}', 1, NOW(), NOW()),
('Round or Unusual Amount Patterns', 'Unusually high proportion (>25%) of transactions in round figures (multiples of 100/1000) at a single merchant within a rolling 90-day window.', 'Cat C', 'alert', 24, '{}', '{"round_figure_threshold_percent": 0.25, "period_days": 90, "round_multiples": [100, 1000]}', 1, NOW(), NOW()),
('Round or Unusual Amount Patterns', 'Unusually high proportion (>25%) of transactions in round figures (multiples of 100/1000) at a single merchant within a rolling 90-day window.', 'Cat D', 'alert', 24, '{}', '{"round_figure_threshold_percent": 0.25, "period_days": 90, "round_multiples": [100, 1000]}', 1, NOW(), NOW());

-- Step 5: Insert Order 25 - High Decline/Authorization Failure Rate
INSERT INTO risk_rules (
  name,
  description,
  category,
  rule_type,
  execution_order,
  mcc_codes,
  parameters,
  enabled,
  inserted_at,
  updated_at
) VALUES
('High Decline/Authorization Failure Rate', 'Merchants where >20% of transaction attempts are declined. Analyze the decline reasons and compare to merchant''s historical pattern.', 'Cat A', 'alert', 25, '{}', '{"decline_rate_threshold": 0.20, "period_days": 30, "fraud_response_codes": ["04", "07", "41", "43", "59", "63", "65", "67", "69"]}', 1, NOW(), NOW()),
('High Decline/Authorization Failure Rate', 'Merchants where >20% of transaction attempts are declined. Analyze the decline reasons and compare to merchant''s historical pattern.', 'Cat B', 'alert', 25, '{}', '{"decline_rate_threshold": 0.20, "period_days": 30, "fraud_response_codes": ["04", "07", "41", "43", "59", "63", "65", "67", "69"]}', 1, NOW(), NOW()),
('High Decline/Authorization Failure Rate', 'Merchants where >20% of transaction attempts are declined. Analyze the decline reasons and compare to merchant''s historical pattern.', 'Cat C', 'alert', 25, '{}', '{"decline_rate_threshold": 0.20, "period_days": 30, "fraud_response_codes": ["04", "07", "41", "43", "59", "63", "65", "67", "69"]}', 1, NOW(), NOW()),
('High Decline/Authorization Failure Rate', 'Merchants where >20% of transaction attempts are declined. Analyze the decline reasons and compare to merchant''s historical pattern.', 'Cat D', 'alert', 25, '{}', '{"decline_rate_threshold": 0.20, "period_days": 30, "fraud_response_codes": ["04", "07", "41", "43", "59", "63", "65", "67", "69"]}', 1, NOW(), NOW());

-- Step 6: Insert Order 26 - Sudden Drop in Daily Transaction Volume
INSERT INTO risk_rules (
  name,
  description,
  category,
  rule_type,
  execution_order,
  mcc_codes,
  parameters,
  enabled,
  inserted_at,
  updated_at
) VALUES
('Sudden Drop in Daily Transaction Volume', 'Alert if a merchant''s daily transaction volume drops by more than 50% compared to their average daily volume over the past 30 days, and this drop persists for 3 consecutive days', 'Cat A', 'alert', 26, '{}', '{"volume_drop_threshold_percent": 0.50, "baseline_period_days": 30, "consecutive_days_required": 3}', 1, NOW(), NOW()),
('Sudden Drop in Daily Transaction Volume', 'Alert if a merchant''s daily transaction volume drops by more than 50% compared to their average daily volume over the past 30 days, and this drop persists for 3 consecutive days', 'Cat B', 'alert', 26, '{}', '{"volume_drop_threshold_percent": 0.50, "baseline_period_days": 30, "consecutive_days_required": 3}', 1, NOW(), NOW()),
('Sudden Drop in Daily Transaction Volume', 'Alert if a merchant''s daily transaction volume drops by more than 50% compared to their average daily volume over the past 30 days, and this drop persists for 3 consecutive days', 'Cat C', 'alert', 26, '{}', '{"volume_drop_threshold_percent": 0.50, "baseline_period_days": 30, "consecutive_days_required": 3}', 1, NOW(), NOW()),
('Sudden Drop in Daily Transaction Volume', 'Alert if a merchant''s daily transaction volume drops by more than 50% compared to their average daily volume over the past 30 days, and this drop persists for 3 consecutive days', 'Cat D', 'alert', 26, '{}', '{"volume_drop_threshold_percent": 0.50, "baseline_period_days": 30, "consecutive_days_required": 3}', 1, NOW(), NOW());

-- Step 7: Insert Order 27 - Significant Decrease in Weekly/Monthly Volume
INSERT INTO risk_rules (
  name,
  description,
  category,
  rule_type,
  execution_order,
  mcc_codes,
  parameters,
  enabled,
  inserted_at,
  updated_at
) VALUES
('Significant Decrease in Weekly/Monthly Volume', 'Alert if the merchant''s total weekly (or monthly) transaction volume drops by more than 50% compared to the average of the previous 4 weeks (or months)', 'Cat A', 'alert', 27, '{}', '{"volume_drop_threshold_percent": 0.50, "comparison_period_weeks": 4, "comparison_period_months": 1}', 1, NOW(), NOW()),
('Significant Decrease in Weekly/Monthly Volume', 'Alert if the merchant''s total weekly (or monthly) transaction volume drops by more than 50% compared to the average of the previous 4 weeks (or months)', 'Cat B', 'alert', 27, '{}', '{"volume_drop_threshold_percent": 0.50, "comparison_period_weeks": 4, "comparison_period_months": 1}', 1, NOW(), NOW()),
('Significant Decrease in Weekly/Monthly Volume', 'Alert if the merchant''s total weekly (or monthly) transaction volume drops by more than 50% compared to the average of the previous 4 weeks (or months)', 'Cat C', 'alert', 27, '{}', '{"volume_drop_threshold_percent": 0.50, "comparison_period_weeks": 4, "comparison_period_months": 1}', 1, NOW(), NOW()),
('Significant Decrease in Weekly/Monthly Volume', 'Alert if the merchant''s total weekly (or monthly) transaction volume drops by more than 50% compared to the average of the previous 4 weeks (or months)', 'Cat D', 'alert', 27, '{}', '{"volume_drop_threshold_percent": 0.50, "comparison_period_weeks": 4, "comparison_period_months": 1}', 1, NOW(), NOW());

-- Step 8: Verification - Show newly added rules
SELECT 
  execution_order,
  name,
  category,
  rule_type,
  enabled
FROM risk_rules
WHERE execution_order BETWEEN 22 AND 27
ORDER BY execution_order, category;

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

COMMIT;
