#!/bin/bash
# ========================================
# Script: Execute Risk Rules Deletion Migration
# Date: March 26, 2026
# Description: Safely deletes 16 extra rules with backup
# ========================================

set -e  # Exit on any error

# Configuration
DB_NAME="shukria_transactions"
DB_USER="root"
DB_PASS="dataaegis123"
BACKUP_DIR="/var/www/internaltesting/kaleesh/prverification/pr-11-folder/backups"
MIGRATION_FILE="/var/www/internaltesting/kaleesh/prverification/pr-11-folder/priv/repo/migrations/01_delete_extra_rules.sql"
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")

# Colors for output
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
NC='\033[0m' # No Color

echo "=========================================="
echo "Risk Rules Deletion Migration"
echo "=========================================="
echo ""

# Step 1: Create backup directory if it doesn't exist
echo -e "${YELLOW}Step 1: Creating backup directory...${NC}"
mkdir -p "$BACKUP_DIR"
echo -e "${GREEN}✓ Backup directory ready${NC}"
echo ""

# Step 2: Backup current risk_rules table
echo -e "${YELLOW}Step 2: Backing up risk_rules table...${NC}"
BACKUP_FILE="${BACKUP_DIR}/risk_rules_backup_${TIMESTAMP}.sql"
mysqldump -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" risk_rules > "$BACKUP_FILE" 2>/dev/null
if [ $? -eq 0 ]; then
    echo -e "${GREEN}✓ Backup created: $BACKUP_FILE${NC}"
    BACKUP_SIZE=$(du -h "$BACKUP_FILE" | cut -f1)
    echo -e "${GREEN}  Backup size: $BACKUP_SIZE${NC}"
else
    echo -e "${RED}✗ Backup failed! Aborting migration.${NC}"
    exit 1
fi
echo ""

# Step 3: Show current rule count
echo -e "${YELLOW}Step 3: Checking current rule count...${NC}"
CURRENT_COUNT=$(mysql -u "$DB_USER" -p"$DB_PASS" -D "$DB_NAME" -se "SELECT COUNT(*) FROM risk_rules;" 2>/dev/null)
echo -e "${GREEN}✓ Current total rules: $CURRENT_COUNT${NC}"
mysql -u "$DB_USER" -p"$DB_PASS" -D "$DB_NAME" -e "
SELECT 
    category,
    COUNT(*) as rule_count
FROM risk_rules
GROUP BY category
ORDER BY category;" 2>/dev/null
echo ""

# Step 4: Execute migration
echo -e "${YELLOW}Step 4: Executing deletion migration...${NC}"
echo -e "${YELLOW}This will delete 16 extra rules (4 rules x 4 categories = 64 total records)${NC}"
echo ""
read -p "Are you sure you want to proceed? (yes/no): " CONFIRM

if [ "$CONFIRM" != "yes" ]; then
    echo -e "${RED}Migration cancelled by user.${NC}"
    exit 0
fi

echo ""
echo -e "${YELLOW}Running migration...${NC}"
mysql -u "$DB_USER" -p"$DB_PASS" -D "$DB_NAME" < "$MIGRATION_FILE" 2>/dev/null

if [ $? -eq 0 ]; then
    echo -e "${GREEN}✓ Migration executed successfully${NC}"
else
    echo -e "${RED}✗ Migration failed!${NC}"
    echo -e "${YELLOW}To restore from backup, run:${NC}"
    echo -e "${YELLOW}mysql -u $DB_USER -p $DB_NAME < $BACKUP_FILE${NC}"
    exit 1
fi
echo ""

# Step 5: Verify deletion
echo -e "${YELLOW}Step 5: Verifying deletion...${NC}"
NEW_COUNT=$(mysql -u "$DB_USER" -p"$DB_PASS" -D "$DB_NAME" -se "SELECT COUNT(*) FROM risk_rules;" 2>/dev/null)
DELETED_COUNT=$((CURRENT_COUNT - NEW_COUNT))
echo -e "${GREEN}✓ Previous count: $CURRENT_COUNT${NC}"
echo -e "${GREEN}✓ Current count: $NEW_COUNT${NC}"
echo -e "${GREEN}✓ Rules deleted: $DELETED_COUNT${NC}"
echo ""

# Expected: 64 rules deleted (16 rules x 4 categories)
if [ "$DELETED_COUNT" -eq 64 ]; then
    echo -e "${GREEN}✓ Deletion count matches expected (64 records)${NC}"
else
    echo -e "${YELLOW}⚠ Warning: Expected 64 deletions, got $DELETED_COUNT${NC}"
fi
echo ""

# Step 6: Show updated rule count per category
echo -e "${YELLOW}Step 6: Final rule count per category:${NC}"
mysql -u "$DB_USER" -p"$DB_PASS" -D "$DB_NAME" -e "
SELECT 
    category,
    COUNT(*) as rule_count
FROM risk_rules
GROUP BY category
ORDER BY category;" 2>/dev/null
echo ""

# Expected: Each category should have 31 rules (47 - 16 = 31)
echo -e "${GREEN}Expected: 31 rules per category (total 124 rules)${NC}"
echo ""

# Step 7: List deleted rules (verification)
echo -e "${YELLOW}Step 7: Verifying deleted rules are gone...${NC}"
REMAINING=$(mysql -u "$DB_USER" -p"$DB_PASS" -D "$DB_NAME" -se "
SELECT COUNT(*) 
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'
);" 2>/dev/null)

if [ "$REMAINING" -eq 0 ]; then
    echo -e "${GREEN}✓ All 16 extra rules successfully deleted from all categories${NC}"
else
    echo -e "${RED}✗ Warning: $REMAINING rules still remain in database${NC}"
fi
echo ""

# Summary
echo "=========================================="
echo -e "${GREEN}Migration Completed Successfully!${NC}"
echo "=========================================="
echo ""
echo "Summary:"
echo "  - Backup: $BACKUP_FILE"
echo "  - Rules before: $CURRENT_COUNT"
echo "  - Rules after: $NEW_COUNT"
echo "  - Rules deleted: $DELETED_COUNT"
echo ""
echo "Next Steps:"
echo "  1. Review the remaining rules"
echo "  2. Provide details for YELLOW rules (description updates)"
echo "  3. Provide details for RED rules (missing rules to add)"
echo ""
echo -e "${YELLOW}To restore if needed:${NC}"
echo -e "${YELLOW}mysql -u $DB_USER -p $DB_NAME < $BACKUP_FILE${NC}"
echo ""