-- Short, clear descriptions for all risk rules

-- Rule 1: Suspicious International transactions
UPDATE risk_rules SET description = 'Flags international card transactions with amounts equal to or exceeding AED 5,000. This prevents potentially fraudulent high-value cross-border transactions from being processed immediately, allowing the fraud team time to verify legitimacy.' WHERE id = 1;

-- Rule 2: Multiple Int'l card, low value
UPDATE risk_rules SET description = 'Detects potential card testing attacks where the same international card makes 2+ transactions between AED 2,000-5,000 within 60 minutes. This pattern commonly indicates fraudsters testing stolen credentials before attempting larger purchases.' WHERE id = 2;

-- Rule 3: Abnormal Time transaction
UPDATE risk_rules SET description = 'Flags transactions occurring between 12:00 AM and 7:00 AM, excluding 24-hour businesses (hospitals, hotels, fuel stations, restaurants). Late-night activity outside expected business types often indicates unauthorized terminal access or compromised cards.' WHERE id = 3;

-- Rule 4: Split transaction
UPDATE risk_rules SET description = 'Detects when the same card makes multiple transactions at the same terminal within 10 minutes that total AED 10,000+. This catches attempts to split large purchases into smaller amounts to evade detection thresholds.' WHERE id = 4;

-- Rule 5: Duplicate transaction
UPDATE risk_rules SET description = 'Identifies suspicious duplicate transactions with identical card, terminal, and amount within 10 minutes totaling AED 10,000+. This pattern often indicates replay attacks, terminal exploitation, or deliberate double-charging fraud.' WHERE id = 5;

-- Rule 6: High Velocity transaction
UPDATE risk_rules SET description = 'Flags rapid transactions (2 minutes or less apart) at the same terminal with amounts AED 500+. This velocity is physically impossible for legitimate card-present transactions and indicates automated fraud or compromised terminals.' WHERE id = 6;

-- Rule 7: Above-average ticket size
UPDATE risk_rules SET description = 'Detects transactions 4x higher than the merchant\'s 30-day average for high-risk categories (telecommunications, electronics, music stores, service stations). This catches fraudsters processing unusually large transactions after account compromise.' WHERE id = 7;

-- Rule 8: Dormant Merchant
UPDATE risk_rules SET description = 'Flags merchants who process transactions after 30+ days of complete inactivity. Inactive accounts are targets for fraudsters as monitoring is reduced and sudden activity may not trigger immediate attention.' WHERE id = 8;

-- Rule 9: High Value Transaction
UPDATE risk_rules SET description = 'Holds any single transaction of AED 10,000 or more for manual review. High-value transactions require enhanced verification due to significant financial impact if fraudulent and increased chargeback risk.' WHERE id = 9;

-- Rule 10: High Frequency Chargeback Merchant
UPDATE risk_rules SET description = 'Monitors merchants receiving 2+ chargebacks within 30 days. High chargeback rates indicate potential fraud attacks, poor business practices, or customer dispute issues requiring enhanced screening.' WHERE id = 10;

-- Rule 11: Excessive Refunds/Reversals
UPDATE risk_rules SET description = 'Alerts when merchant refund/reversal rate exceeds 10% over 30 days. Excessive refunds may indicate return fraud schemes, money laundering, or merchant collusion requiring investigation.' WHERE id = 11;

-- Rule 12: Out-of-Profile Geographic
UPDATE risk_rules SET description = 'Flags card usage in locations that deviate 3+ standard deviations from the cardholder\'s 30-day geographic pattern. Abnormal locations often indicate card theft or account compromise with remote usage.' WHERE id = 12;

-- Rule 13: Machines of High Value Fallback Transactions
UPDATE risk_rules SET description = 'Alerts on transactions over AED 50 using fallback (chip failed) or magstripe entry modes. Fraudsters often damage chips to force less secure magstripe mode, or counterfeit cards fail chip authentication.' WHERE id = 13;

-- Rule 14: Decline Transaction Monitoring
UPDATE risk_rules SET description = 'Detects 10+ declined transactions at the same merchant within 20 minutes. This pattern indicates credential testing attacks where fraudsters validate stolen card batches.' WHERE id = 14;

-- Rule 15: New Onboarded Merchant High Decline
UPDATE risk_rules SET description = 'Monitors merchants under 90 days old with 5+ declines within 20 minutes. New merchants with high decline rates may indicate fraudulent applications or compromised accounts being exploited.' WHERE id = 15;

-- Rule 16: MI - Local Transaction Higher than 500K
UPDATE risk_rules SET description = 'Holds local card transactions exceeding AED 500,000. This amount represents major financial risk requiring multi-level verification and executive approval before processing.' WHERE id = 16;

-- Rule 17: MI - Regional Transaction Higher than 1500K
UPDATE risk_rules SET description = 'Monitors regional (GCC) card transactions over AED 1,500,000. Cat A merchants receive alerts only, while Cat B/C/D merchants require holds due to unusual transaction size for their tier.' WHERE id = 17;

-- Rule 18: MI - Local Transaction Higher than 400K
UPDATE risk_rules SET description = 'Holds local card transactions over AED 400,000 for non-Cat A merchants. Cat B/C/D merchants rarely process legitimate transactions at this level, indicating potential compromise or unauthorized use.' WHERE id = 18;

-- Rule 19: MI - International Transaction Higher than 100K
UPDATE risk_rules SET description = 'Holds international card transactions exceeding AED 100,000. Cross-border high-value transactions carry elevated fraud risk and are challenging to investigate and recover if fraudulent.' WHERE id = 19;

-- Rule 20: MI - International Transaction Higher than 25K
UPDATE risk_rules SET description = 'Generates alerts (not holds) for international transactions over AED 25,000. This monitoring layer enables analyst review without disrupting legitimate cross-border commerce.' WHERE id = 20;

-- Rule 21: MV - 10 Cards Same BIN
UPDATE risk_rules SET description = 'Alerts when 10+ different cards from the same bank (BIN) are used at one merchant within 30 minutes. This indicates batch fraud from stolen cards or a data breach targeting specific issuers.' WHERE id = 21;

-- Rule 22: MH - 20 Cards Same BIN
UPDATE risk_rules SET description = 'Alerts on 20+ cards from the same bank used at one merchant in 30 minutes. This volume indicates major organized fraud requiring immediate intervention and potential law enforcement notification.' WHERE id = 22;

-- Rule 23: MI - Daily Amount Limit Per Card
UPDATE risk_rules SET description = 'Alerts when a single card accumulates AED 100,000-200,000 in daily transactions. Daily limits prevent large-scale fraud when compromised cards are maxed out within 24 hours.' WHERE id = 23;

-- Rule 24: High Value Transaction on New Merchant (First 90 Days)
UPDATE risk_rules SET description = 'Alerts on transactions exceeding AED 25,000 at merchants under 90 days old. New merchants lack transaction history to establish patterns, requiring enhanced validation for high-value activity.' WHERE id = 24;

-- Rule 25: High Value Transaction on New Merchant
UPDATE risk_rules SET description = 'Monitors high-value transactions at merchants with recent category changes within 90 days. Category changes may indicate account takeover or business model shifts requiring validation.' WHERE id = 25;

-- Rule 26: MI - Suspicious International Transaction (High-Risk Countries)
UPDATE risk_rules SET description = 'Alerts on transactions over AED 5,000 from Iraq, Egypt, Libya, or Mozambique. These countries have elevated fraud rates and require enhanced scrutiny and compliance verification.' WHERE id = 26;

-- Rule 27: Suspicious High Value Manual Entry
UPDATE risk_rules SET description = 'Flags high-value manually keyed transactions at card-present merchants. Manual entry bypasses chip security and is common in card-not-present fraud using stolen numbers.' WHERE id = 27;

-- Rule 28: New Merchant International Card (First 50 Days)
UPDATE risk_rules SET description = 'Monitors international card usage at merchants under 50 days old. New merchants using cross-border transactions need enhanced validation during the high-risk establishment period.' WHERE id = 28;

-- Rule 29: Expired Card Reuse at Same Merchant
UPDATE risk_rules SET description = 'Alerts when expired cards attempt transactions. This indicates improper merchant card storage, fake expiration dates, or security control failures in the payment flow.' WHERE id = 29;

-- Rule 30: High Amount of Aggregative Refunds
UPDATE risk_rules SET description = 'Flags abnormal accumulated refund volumes. High aggregate refunds may indicate return fraud schemes, money laundering, or merchant collusion requiring investigation.' WHERE id = 30;

-- Rule 31: High Value Single Refund Alert
UPDATE risk_rules SET description = 'Alerts on individual high-value refund transactions. Large refunds warrant scrutiny due to significant financial impact and potential merchant account compromise.' WHERE id = 31;

-- Rule 125: Round Figure Transaction on same Terminal
UPDATE risk_rules SET description = 'Flags multiple exact round-amount transactions (1000, 5000, 10000) at the same terminal. Round patterns are uncommon in legitimate purchases and common in money laundering or card testing.' WHERE id = 125;

-- Rule 126: Transaction followed by Fraud-Suspect declined
UPDATE risk_rules SET description = 'Detects cards with successful transactions immediately before/after fraud-flagged declines. This pattern indicates card compromise discovered mid-usage or progressive fraud testing.' WHERE id = 126;

-- Rule 127: Transaction followed by Wrong PIN Entry Attempts
UPDATE risk_rules SET description = 'Alerts when successful transactions are followed by failed PIN attempts. This sequence suggests different users (theft) as legitimate cardholders know their PIN.' WHERE id = 127;

-- Rule 128: Daily Volume Above Average (4x)
UPDATE risk_rules SET description = 'Flags merchant daily volumes exceeding 4x the rolling average. Sudden volume spikes indicate potential account compromise, terminal exploitation, or organized fraud attacks.' WHERE id = 128;

-- Rule 129: Multiple Transactions with Same Amount
UPDATE risk_rules SET description = 'Detects suspicious patterns of identical-amount transactions. Multiple exact matches are uncommon in legitimate commerce and indicate card testing or predetermined fraud schemes.' WHERE id = 129;

-- Rule 130: Self Card Transactions by Merchant
UPDATE risk_rules SET description = 'Flags merchants processing transactions with their own payment cards. Self-card processing violates terms of service and indicates merchant fee fraud or money laundering.' WHERE id = 130;

-- Rule 131: High Value International Same Card
UPDATE risk_rules SET description = 'Monitors repeated high-value transactions on the same international card. This pattern may indicate progressive fraud testing, money laundering, or business email compromise.' WHERE id = 131;

-- Rule 132: Out-of-Profile Geographic Shift (Domestic to International)
UPDATE risk_rules SET description = 'Detects cards suddenly shifting from domestic to international usage. Geographic shifts often indicate card theft, data compromise, or account takeover with fraudulent cross-border purchases.' WHERE id = 132;

-- Rule 133: High Chargeback Rate (>1%)
UPDATE risk_rules SET description = 'Alerts when merchant chargebacks exceed 1% of transactions. This violates card network thresholds and indicates fraud attacks, poor business practices, or delivery failures.' WHERE id = 133;

-- Rule 134: High Fraud Rate (>0.5%)
UPDATE risk_rules SET description = 'Flags merchants with confirmed fraud exceeding 0.5% of volume (industry average is 0.1-0.3%). High fraud rates indicate security weaknesses or business model vulnerabilities.' WHERE id = 134;

-- Rule 135: Round or Unusual Amount Patterns (Alert)
UPDATE risk_rules SET description = 'Uses pattern recognition to identify statistically unusual transaction amounts. Suspicious patterns include excessive round numbers, repeated amounts, or just-below-threshold structuring.' WHERE id = 135;

-- Rule 136: High Decline/Authorization Failure Rate (>20%)
UPDATE risk_rules SET description = 'Alerts when merchant declines exceed 20% of attempts. High decline rates indicate card testing fraud, payment system issues, or merchant configuration problems.' WHERE id = 136;

-- Rule 137: Sudden Drop in Daily Transaction Volume
UPDATE risk_rules SET description = 'Detects sudden decreases in merchant volume. Drops may indicate business closure, fraud discovery, account compromise, or regulatory action requiring immediate investigation.' WHERE id = 137;

-- Now sync all categories (B, C, D) from Cat A
UPDATE risk_rules r1
JOIN risk_rules r2 ON r1.name = r2.name AND r2.category = 'Cat A'
SET r1.description = r2.description
WHERE r1.category IN ('Cat B', 'Cat C', 'Cat D');
