-- Migration: Add Fraud-Suspect declined transaction rules at orders 14 and 15
-- Date: 2026-03-26
-- Description: Adds missing RED rules from Excel specification

START TRANSACTION;

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

-- Step 2: Insert Order 14 - Transaction followed by Fraud-Suspect declined transaction with Same Card
INSERT INTO risk_rules (
  name,
  description,
  category,
  rule_type,
  execution_order,
  mcc_codes,
  parameters,
  enabled,
  inserted_at,
  updated_at
) VALUES
-- Cat A
(
  'Transaction followed by Fraud-Suspect declined transaction with Same Card',
  'Transaction that are followed by declined transactions with fraudulent-suspect response codes, with same card, are kept on Hold and scrutinized for further action (If to be put on hold or release in Merchant account)',
  'Cat A',
  'hold',
  14,
  '{}',
  '{"fraud_response_codes": ["04", "07", "41", "43", "59", "63", "65", "67", "69"], "time_window_minutes": 60, "lookback_count": 5}',
  1,
  NOW(),
  NOW()
),
-- Cat B
(
  'Transaction followed by Fraud-Suspect declined transaction with Same Card',
  'Transaction that are followed by declined transactions with fraudulent-suspect response codes, with same card, are kept on Hold and scrutinized for further action (If to be put on hold or release in Merchant account)',
  'Cat B',
  'hold',
  14,
  '{}',
  '{"fraud_response_codes": ["04", "07", "41", "43", "59", "63", "65", "67", "69"], "time_window_minutes": 60, "lookback_count": 5}',
  1,
  NOW(),
  NOW()
),
-- Cat C
(
  'Transaction followed by Fraud-Suspect declined transaction with Same Card',
  'Transaction that are followed by declined transactions with fraudulent-suspect response codes, with same card, are kept on Hold and scrutinized for further action (If to be put on hold or release in Merchant account)',
  'Cat C',
  'hold',
  14,
  '{}',
  '{"fraud_response_codes": ["04", "07", "41", "43", "59", "63", "65", "67", "69"], "time_window_minutes": 60, "lookback_count": 5}',
  1,
  NOW(),
  NOW()
),
-- Cat D
(
  'Transaction followed by Fraud-Suspect declined transaction with Same Card',
  'Transaction that are followed by declined transactions with fraudulent-suspect response codes, with same card, are kept on Hold and scrutinized for further action (If to be put on hold or release in Merchant account)',
  'Cat D',
  'hold',
  14,
  '{}',
  '{"fraud_response_codes": ["04", "07", "41", "43", "59", "63", "65", "67", "69"], "time_window_minutes": 60, "lookback_count": 5}',
  1,
  NOW(),
  NOW()
);

-- Step 3: Insert Order 15 - Transaction followed by Fraud-Suspect declined transaction with different Card but same amount
INSERT INTO risk_rules (
  name,
  description,
  category,
  rule_type,
  execution_order,
  mcc_codes,
  parameters,
  enabled,
  inserted_at,
  updated_at
) VALUES
-- Cat A
(
  'Transaction followed by Fraud-Suspect declined transaction with different Card but same amount',
  'Transaction that are followed by declined transactions with fraudulent-suspect response codes, with different card but same amount, are kept on Hold and scrutinized for further action (If to be put on hold or release in Merchant account)',
  'Cat A',
  'hold',
  15,
  '{}',
  '{"fraud_response_codes": ["04", "07", "41", "43", "59", "63", "65", "67", "69"], "time_window_minutes": 60, "amount_match_threshold": 0.99}',
  1,
  NOW(),
  NOW()
),
-- Cat B
(
  'Transaction followed by Fraud-Suspect declined transaction with different Card but same amount',
  'Transaction that are followed by declined transactions with fraudulent-suspect response codes, with different card but same amount, are kept on Hold and scrutinized for further action (If to be put on hold or release in Merchant account)',
  'Cat B',
  'hold',
  15,
  '{}',
  '{"fraud_response_codes": ["04", "07", "41", "43", "59", "63", "65", "67", "69"], "time_window_minutes": 60, "amount_match_threshold": 0.99}',
  1,
  NOW(),
  NOW()
),
-- Cat C
(
  'Transaction followed by Fraud-Suspect declined transaction with different Card but same amount',
  'Transaction that are followed by declined transactions with fraudulent-suspect response codes, with different card but same amount, are kept on Hold and scrutinized for further action (If to be put on hold or release in Merchant account)',
  'Cat C',
  'hold',
  15,
  '{}',
  '{"fraud_response_codes": ["04", "07", "41", "43", "59", "63", "65", "67", "69"], "time_window_minutes": 60, "amount_match_threshold": 0.99}',
  1,
  NOW(),
  NOW()
),
-- Cat D
(
  'Transaction followed by Fraud-Suspect declined transaction with different Card but same amount',
  'Transaction that are followed by declined transactions with fraudulent-suspect response codes, with different card but same amount, are kept on Hold and scrutinized for further action (If to be put on hold or release in Merchant account)',
  'Cat D',
  'hold',
  15,
  '{}',
  '{"fraud_response_codes": ["04", "07", "41", "43", "59", "63", "65", "67", "69"], "time_window_minutes": 60, "amount_match_threshold": 0.99}',
  1,
  NOW(),
  NOW()
);

-- Step 4: Verification - Show newly added rules
SELECT 
  execution_order,
  name,
  category,
  rule_type,
  enabled
FROM risk_rules
WHERE execution_order IN (14, 15)
ORDER BY execution_order, category;

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

COMMIT;
