#!/bin/bash
# ============================================================================
# Risk Rules Migration Execution Script
# Date: March 26, 2026
# Purpose: Execute risk rules update migration safely
# ============================================================================

set -e  # Exit on any error

DB_HOST="localhost"
DB_USER="root"
DB_PASS="dataaegis123"
DB_NAME="shukria_transactions"
MIGRATION_FILE="priv/repo/migrations/update_risk_rules_to_match_excel.sql"
BACKUP_FILE="risk_rules_backup_$(date +%Y%m%d_%H%M%S).sql"

echo "============================================================================"
echo "RISK RULES MIGRATION EXECUTION"
echo "Date: $(date)"
echo "============================================================================"
echo ""

# Check if migration file exists
if [ ! -f "$MIGRATION_FILE" ]; then
    echo "❌ ERROR: Migration file not found: $MIGRATION_FILE"
    exit 1
fi

echo "📋 Step 1: Creating backup of current risk_rules table..."
mysqldump -u $DB_USER -p$DB_PASS $DB_NAME risk_rules > "$BACKUP_FILE"
if [ $? -eq 0 ]; then
    echo "✅ Backup created: $BACKUP_FILE"
else
    echo "❌ ERROR: Backup failed"
    exit 1
fi

echo ""
echo "📊 Step 2: Current state - Checking rule count..."
mysql -u $DB_USER -p$DB_PASS $DB_NAME -e "SELECT category, COUNT(*) as count FROM risk_rules GROUP BY category;"

echo ""
echo "⚠️  Step 3: About to execute migration..."
echo "This will:"
echo "  - Delete 16 extra rules"
echo "  - Update 17 rule descriptions"
echo "  - Add 12 new rules"
echo "  - Fix duplicate order numbers"
echo ""
read -p "Continue with migration? (yes/no): " confirm

if [ "$confirm" != "yes" ]; then
    echo "❌ Migration cancelled by user"
    exit 0
fi

echo ""
echo "🚀 Step 4: Executing migration..."
mysql -u $DB_USER -p$DB_PASS $DB_NAME < "$MIGRATION_FILE"

if [ $? -eq 0 ]; then
    echo "✅ Migration executed successfully"
else
    echo "❌ ERROR: Migration failed"
    echo "To rollback, run:"
    echo "mysql -u $DB_USER -p$DB_PASS $DB_NAME < $BACKUP_FILE"
    exit 1
fi

echo ""
echo "📊 Step 5: Verification - Checking new state..."
echo ""
echo "Rule count by category:"
mysql -u $DB_USER -p$DB_PASS $DB_NAME -e "SELECT category, COUNT(*) as count FROM risk_rules GROUP BY category;"

echo ""
echo "Checking for duplicate order numbers:"
mysql -u $DB_USER -p$DB_PASS $DB_NAME -e "
SELECT execution_order, category, COUNT(*) as duplicates 
FROM risk_rules 
GROUP BY execution_order, category 
HAVING COUNT(*) > 1;"

echo ""
echo "Verifying new RED rules were added:"
mysql -u $DB_USER -p$DB_PASS $DB_NAME -e "
SELECT name, category, enabled 
FROM risk_rules 
WHERE name IN (
    'Suspicious Unusual transaction volume',
    'Online Figures Transactions at Multiple Merchants',
    'Transaction followed by Fraud-Suspect declined (Same Card)',
    'Self Card Transactions by Merchant'
) 
LIMIT 10;"

echo ""
echo "Verifying deleted rules are gone (should show 0 rows):"
mysql -u $DB_USER -p$DB_PASS $DB_NAME -e "
SELECT COUNT(*) as should_be_zero 
FROM risk_rules 
WHERE name IN (
    'High Volume Low Velocity Transactions',
    'ATL - Local Transactions Higher than 25K'
);"

echo ""
echo "============================================================================"
echo "✅ MIGRATION COMPLETED SUCCESSFULLY"
echo "============================================================================"
echo "Backup file: $BACKUP_FILE"
echo ""
echo "To rollback if needed:"
echo "  mysql -u $DB_USER -p$DB_PASS $DB_NAME < $BACKUP_FILE"
echo ""
echo "============================================================================"