-- ========================================
-- Migration: Add Missing Rule - High Transactions Decline Rate Merchant's
-- Order: 11
-- Date: March 26, 2026
-- Description: Adds the missing "High Transactions Decline Rate Merchant's" rule
-- ========================================

START TRANSACTION;

-- Insert the new rule for Cat A
INSERT INTO risk_rules (
    name, 
    description, 
    category, 
    rule_type, 
    execution_order, 
    mcc_codes, 
    parameters, 
    enabled,
    inserted_at,
    updated_at
) VALUES (
    'High Transactions Decline Rate Merchant''s',
    'Transaction coming from high decline rate Merchant''s. Average 20% decline rates in last 30 days (Are highlighted in daily risk report for scrutiny and further actions)',
    'Cat A',
    'hold',
    11,
    '[]',
    '{"decline_rate_threshold": 0.20, "period_days": 30, "fraud_response_codes": ["04", "07", "41", "43", "59", "63", "65", "67", "69"]}',
    1,
    NOW(),
    NOW()
);

-- Insert the new rule for Cat B
INSERT INTO risk_rules (
    name, 
    description, 
    category, 
    rule_type, 
    execution_order, 
    mcc_codes, 
    parameters, 
    enabled,
    inserted_at,
    updated_at
) VALUES (
    'High Transactions Decline Rate Merchant''s',
    'Transaction coming from high decline rate Merchant''s. Average 20% decline rates in last 30 days (Are highlighted in daily risk report for scrutiny and further actions)',
    'Cat B',
    'hold',
    11,
    '[]',
    '{"decline_rate_threshold": 0.20, "period_days": 30, "fraud_response_codes": ["04", "07", "41", "43", "59", "63", "65", "67", "69"]}',
    1,
    NOW(),
    NOW()
);

-- Insert the new rule for Cat C
INSERT INTO risk_rules (
    name, 
    description, 
    category, 
    rule_type, 
    execution_order, 
    mcc_codes, 
    parameters, 
    enabled,
    inserted_at,
    updated_at
) VALUES (
    'High Transactions Decline Rate Merchant''s',
    'Transaction coming from high decline rate Merchant''s. Average 20% decline rates in last 30 days (Are highlighted in daily risk report for scrutiny and further actions)',
    'Cat C',
    'hold',
    11,
    '[]',
    '{"decline_rate_threshold": 0.20, "period_days": 30, "fraud_response_codes": ["04", "07", "41", "43", "59", "63", "65", "67", "69"]}',
    1,
    NOW(),
    NOW()
);

-- Insert the new rule for Cat D
INSERT INTO risk_rules (
    name, 
    description, 
    category, 
    rule_type, 
    execution_order, 
    mcc_codes, 
    parameters, 
    enabled,
    inserted_at,
    updated_at
) VALUES (
    'High Transactions Decline Rate Merchant''s',
    'Transaction coming from high decline rate Merchant''s. Average 20% decline rates in last 30 days (Are highlighted in daily risk report for scrutiny and further actions)',
    'Cat D',
    'hold',
    11,
    '[]',
    '{"decline_rate_threshold": 0.20, "period_days": 30, "fraud_response_codes": ["04", "07", "41", "43", "59", "63", "65", "67", "69"]}',
    1,
    NOW(),
    NOW()
);

-- Update execution_order for existing rules at order 11 and above (shift them down)
-- This needs to be done BEFORE inserting to avoid conflicts
UPDATE risk_rules 
SET execution_order = execution_order + 1,
    updated_at = NOW()
WHERE execution_order >= 11
AND name != 'High Transactions Decline Rate Merchant''s'
AND category IN ('Cat A', 'Cat B', 'Cat C', 'Cat D');

COMMIT;

-- Verification: Check the new rule was added
SELECT 
    'New Rule Added' as Status,
    name,
    category,
    execution_order,
    enabled,
    rule_type
FROM risk_rules
WHERE name = 'High Transactions Decline Rate Merchant''s'
ORDER BY category;

-- Verification: Check total count per category
SELECT 
    'Total Rules Per Category' as Status,
    category,
    COUNT(*) as rule_count
FROM risk_rules
GROUP BY category
ORDER BY category;

-- Expected: 32 rules per category (31 + 1 new rule)
