-- Migration: Replicate Cat A rules to Cat B, Cat C, and Cat D
-- This will delete all existing Cat B, C, D rules and copy Cat A rules
-- Date: 2026-03-27

START TRANSACTION;

-- Step 1: Backup current state (for reference)
-- Note: This is already backed up, but good to have timestamp reference
SELECT CONCAT('Backup timestamp: ', NOW()) as backup_info;
SELECT 
  category, 
  COUNT(*) as rule_count,
  MIN(execution_order) as min_order,
  MAX(execution_order) as max_order
FROM risk_rules 
GROUP BY category 
ORDER BY category;

-- Step 2: Delete all Cat B, C, D rules
DELETE FROM risk_rules WHERE category IN ('Cat B', 'Cat C', 'Cat D');

SELECT CONCAT('Deleted Cat B, C, D rules. Remaining rules: ', COUNT(*)) as status
FROM risk_rules;

-- Step 3: Insert Cat B rules (copy from Cat A)
INSERT INTO risk_rules (
  name,
  description,
  category,
  rule_type,
  execution_order,
  mcc_codes,
  parameters,
  enabled,
  inserted_at,
  updated_at
)
SELECT 
  name,
  description,
  'Cat B' as category,
  rule_type,
  execution_order,
  mcc_codes,
  parameters,
  enabled,
  NOW() as inserted_at,
  NOW() as updated_at
FROM risk_rules 
WHERE category = 'Cat A'
ORDER BY execution_order;

SELECT CONCAT('Inserted Cat B rules: ', COUNT(*)) as status
FROM risk_rules 
WHERE category = 'Cat B';

-- Step 4: Insert Cat C rules (copy from Cat A)
INSERT INTO risk_rules (
  name,
  description,
  category,
  rule_type,
  execution_order,
  mcc_codes,
  parameters,
  enabled,
  inserted_at,
  updated_at
)
SELECT 
  name,
  description,
  'Cat C' as category,
  rule_type,
  execution_order,
  mcc_codes,
  parameters,
  enabled,
  NOW() as inserted_at,
  NOW() as updated_at
FROM risk_rules 
WHERE category = 'Cat A'
ORDER BY execution_order;

SELECT CONCAT('Inserted Cat C rules: ', COUNT(*)) as status
FROM risk_rules 
WHERE category = 'Cat C';

-- Step 5: Insert Cat D rules (copy from Cat A)
INSERT INTO risk_rules (
  name,
  description,
  category,
  rule_type,
  execution_order,
  mcc_codes,
  parameters,
  enabled,
  inserted_at,
  updated_at
)
SELECT 
  name,
  description,
  'Cat D' as category,
  rule_type,
  execution_order,
  mcc_codes,
  parameters,
  enabled,
  NOW() as inserted_at,
  NOW() as updated_at
FROM risk_rules 
WHERE category = 'Cat A'
ORDER BY execution_order;

SELECT CONCAT('Inserted Cat D rules: ', COUNT(*)) as status
FROM risk_rules 
WHERE category = 'Cat D';

-- Step 6: Verification - Final counts
SELECT 
  category, 
  COUNT(*) as total_rules,
  COUNT(DISTINCT execution_order) as unique_orders,
  MIN(execution_order) as min_order,
  MAX(execution_order) as max_order
FROM risk_rules 
GROUP BY category 
ORDER BY category;

-- Step 7: Verify all categories have same rule names at same orders
SELECT 
  execution_order,
  COUNT(DISTINCT name) as unique_names,
  GROUP_CONCAT(DISTINCT category ORDER BY category) as categories,
  MAX(CASE WHEN category = 'Cat A' THEN name END) as cat_a_name
FROM risk_rules
GROUP BY execution_order
HAVING COUNT(DISTINCT name) > 1
ORDER BY execution_order;

-- If above query returns rows, it means there are inconsistencies

COMMIT;

SELECT '=== REPLICATION COMPLETE ===' as status;
SELECT 'All Cat B, C, D rules now match Cat A exactly' as message;
