-- ========================================
-- Migration: Delete 16 Extra Rules Not Requested by Client
-- Date: March 26, 2026
-- Description: Removes 16 rules that were configured but not part of client's requirement list
-- ========================================

-- Backup note: Consider backing up the risk_rules table before running this migration
-- Run: mysqldump -u root -p shukria_transactions risk_rules > risk_rules_backup_20260326.sql

START TRANSACTION;

-- Delete the 16 extra rules (from all categories: Cat A, Cat B, Cat C, Cat D)
-- These rules were not in the client's Excel specification

-- Order 22: High Value Transaction on New Merchant
DELETE FROM risk_rules 
WHERE name = 'High Value Transaction on New Merchant' 
AND category IN ('Cat A', 'Cat B', 'Cat C', 'Cat D');

-- Order 29: Suspicious High Value (50000 AED) Transaction
DELETE FROM risk_rules 
WHERE name = 'Suspicious High Value (50000 AED) Transaction' 
AND category IN ('Cat A', 'Cat B', 'Cat C', 'Cat D');

-- Order 31: Transactions Followed by Frequent Declined Transactions (PTR)
DELETE FROM risk_rules 
WHERE name = 'Transactions Followed by Frequent Declined Transactions (PTR)' 
AND category IN ('Cat A', 'Cat B', 'Cat C', 'Cat D');

-- Order 35: High Volume Low Velocity Transactions
DELETE FROM risk_rules 
WHERE name = 'High Volume Low Velocity Transactions' 
AND category IN ('Cat A', 'Cat B', 'Cat C', 'Cat D');

-- Order 36: ATL - Local Transactions Higher than 25K
DELETE FROM risk_rules 
WHERE name = 'ATL - Local Transactions Higher than 25K' 
AND category IN ('Cat A', 'Cat B', 'Cat C', 'Cat D');

-- Order 37: AL - Regional Transactions Higher than 50K
DELETE FROM risk_rules 
WHERE name = 'AL - Regional Transactions Higher than 50K' 
AND category IN ('Cat A', 'Cat B', 'Cat C', 'Cat D');

-- Order 38: VVL - International Transactions Higher than 10K
DELETE FROM risk_rules 
WHERE name = 'VVL - International Transactions Higher than 10K' 
AND category IN ('Cat A', 'Cat B', 'Cat C', 'Cat D');

-- Order 39: AL - Duplicate Transactions Higher than 10K
DELETE FROM risk_rules 
WHERE name = 'AL - Duplicate Transactions Higher than 10K' 
AND category IN ('Cat A', 'Cat B', 'Cat C', 'Cat D');

-- Order 40: ATL - Local Transactions Higher than 10K
DELETE FROM risk_rules 
WHERE name = 'ATL - Local Transactions Higher than 10K' 
AND category IN ('Cat A', 'Cat B', 'Cat C', 'Cat D');

-- Order 41: Duplicate Transactions in Regions Less than 5K
DELETE FROM risk_rules 
WHERE name = 'Duplicate Transactions in Regions Less than 5K' 
AND category IN ('Cat A', 'Cat B', 'Cat C', 'Cat D');

-- Order 42: ATL - International Transactions Higher than 5K
DELETE FROM risk_rules 
WHERE name = 'ATL - International Transactions Higher than 5K' 
AND category IN ('Cat A', 'Cat B', 'Cat C', 'Cat D');

-- Order 43: Duplicate Split Transaction Less than 1K
DELETE FROM risk_rules 
WHERE name = 'Duplicate Split Transaction Less than 1K' 
AND category IN ('Cat A', 'Cat B', 'Cat C', 'Cat D');

-- Order 44: HH - International Transactions Higher than 10K
DELETE FROM risk_rules 
WHERE name = 'HH - International Transactions Higher than 10K' 
AND category IN ('Cat A', 'Cat B', 'Cat C', 'Cat D');

-- Order 45: ALL - Regional Transactions Higher than 400K
DELETE FROM risk_rules 
WHERE name = 'ALL - Regional Transactions Higher than 400K' 
AND category IN ('Cat A', 'Cat B', 'Cat C', 'Cat D');

-- Order 46: High Fixed Daily Transaction
DELETE FROM risk_rules 
WHERE name = 'High Fixed Daily Transaction' 
AND category IN ('Cat A', 'Cat B', 'Cat C', 'Cat D');

-- Order 47: AL - Local Transactions Higher than 200K
DELETE FROM risk_rules 
WHERE name = 'AL - Local Transactions Higher than 200K' 
AND category IN ('Cat A', 'Cat B', 'Cat C', 'Cat D');

-- Summary of deletions
SELECT 
    'Deletion Summary' as Action,
    COUNT(*) as RulesDeleted
FROM risk_rules
WHERE name IN (
    'High Value Transaction on New Merchant',
    'Suspicious High Value (50000 AED) Transaction',
    'Transactions Followed by Frequent Declined Transactions (PTR)',
    'High Volume Low Velocity Transactions',
    'ATL - Local Transactions Higher than 25K',
    'AL - Regional Transactions Higher than 50K',
    'VVL - International Transactions Higher than 10K',
    'AL - Duplicate Transactions Higher than 10K',
    'ATL - Local Transactions Higher than 10K',
    'Duplicate Transactions in Regions Less than 5K',
    'ATL - International Transactions Higher than 5K',
    'Duplicate Split Transaction Less than 1K',
    'HH - International Transactions Higher than 10K',
    'ALL - Regional Transactions Higher than 400K',
    'High Fixed Daily Transaction',
    'AL - Local Transactions Higher than 200K'
)
AND category IN ('Cat A', 'Cat B', 'Cat C', 'Cat D');

-- This should show 0 if all deletions were successful

COMMIT;

-- Verification: Check remaining rules count
SELECT 
    'Remaining Rules After Deletion' as Status,
    category,
    COUNT(*) as RuleCount
FROM risk_rules
GROUP BY category
ORDER BY category;

-- Expected result: Each category should now have 31 rules (47 - 16 = 31)
