#!/bin/bash
# ========================================
# Script: Add High Transactions Decline Rate Merchant's Rule
# Order: 11
# Date: March 26, 2026
# Description: Adds missing rule with proper order sequencing
# ========================================

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/02_add_high_decline_rate_rule.sql"
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")

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

echo "=========================================="
echo "Add High Decline Rate Rule Migration"
echo "Order #11"
echo "=========================================="
echo ""

# Step 1: Create backup
echo -e "${YELLOW}Step 1: Creating backup...${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}"
else
    echo -e "${RED}✗ Backup failed! Aborting.${NC}"
    exit 1
fi
echo ""

# Step 2: Show current state
echo -e "${YELLOW}Step 2: 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 3: Check if rule already exists
echo -e "${YELLOW}Step 3: Checking if rule already exists...${NC}"
EXISTS=$(mysql -u "$DB_USER" -p"$DB_PASS" -D "$DB_NAME" -se "
SELECT COUNT(*) 
FROM risk_rules 
WHERE name = 'High Transactions Decline Rate Merchant\'s';" 2>/dev/null)

if [ "$EXISTS" -gt 0 ]; then
    echo -e "${RED}✗ Rule already exists! Aborting.${NC}"
    echo -e "${YELLOW}Found $EXISTS existing records with this name${NC}"
    exit 1
else
    echo -e "${GREEN}✓ Rule does not exist, proceeding...${NC}"
fi
echo ""

# Step 4: Show rules that will be affected (order >= 11)
echo -e "${YELLOW}Step 4: Rules that will be renumbered (order >= 11):${NC}"
mysql -u "$DB_USER" -p"$DB_PASS" -D "$DB_NAME" -e "
SELECT 
    execution_order as 'Current Order',
    name,
    category
FROM risk_rules
WHERE execution_order >= 11
AND category = 'Cat A'
ORDER BY execution_order
LIMIT 10;" 2>/dev/null
echo -e "${BLUE}(These will shift to order 12+)${NC}"
echo ""

# Step 5: Execute migration
echo -e "${YELLOW}Step 5: Executing migration...${NC}"
read -p "Add 'High Transactions Decline Rate Merchant's' at Order 11? (yes/no): " CONFIRM

if [ "$CONFIRM" != "yes" ]; then
    echo -e "${RED}Migration cancelled${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: mysql -u $DB_USER -p $DB_NAME < $BACKUP_FILE${NC}"
    exit 1
fi
echo ""

# Step 6: Verify addition
echo -e "${YELLOW}Step 6: Verifying rule was added...${NC}"
NEW_RULE_COUNT=$(mysql -u "$DB_USER" -p"$DB_PASS" -D "$DB_NAME" -se "
SELECT COUNT(*) 
FROM risk_rules 
WHERE name = 'High Transactions Decline Rate Merchant\'s';" 2>/dev/null)

if [ "$NEW_RULE_COUNT" -eq 4 ]; then
    echo -e "${GREEN}✓ Rule added successfully to all 4 categories${NC}"
else
    echo -e "${RED}✗ Warning: Expected 4 records, found $NEW_RULE_COUNT${NC}"
fi
echo ""

# Step 7: Show the new rule details
echo -e "${YELLOW}Step 7: New rule details:${NC}"
mysql -u "$DB_USER" -p"$DB_PASS" -D "$DB_NAME" -e "
SELECT 
    execution_order as 'Order',
    name as 'Rule Name',
    category as 'Category',
    rule_type as 'Type',
    enabled as 'Enabled'
FROM risk_rules
WHERE name = 'High Transactions Decline Rate Merchant\'s'
ORDER BY category;" 2>/dev/null
echo ""

# Step 8: Final count
echo -e "${YELLOW}Step 8: Final rule count:${NC}"
NEW_COUNT=$(mysql -u "$DB_USER" -p"$DB_PASS" -D "$DB_NAME" -se "SELECT COUNT(*) FROM risk_rules;" 2>/dev/null)
ADDED_COUNT=$((NEW_COUNT - CURRENT_COUNT))
echo -e "${GREEN}✓ Previous: $CURRENT_COUNT${NC}"
echo -e "${GREEN}✓ Current: $NEW_COUNT${NC}"
echo -e "${GREEN}✓ Added: $ADDED_COUNT rules${NC}"
echo ""

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 ""
echo -e "${GREEN}Expected: 32 rules per category (total 128)${NC}"
echo ""

# Summary
echo "=========================================="
echo -e "${GREEN}Migration Completed Successfully!${NC}"
echo "=========================================="
echo ""
echo "Rule Added:"
echo "  - Name: High Transactions Decline Rate Merchant's"
echo "  - Order: 11"
echo "  - Type: hold"
echo "  - Threshold: 20% decline rate in 30 days"
echo "  - Response Codes: 04, 07, 41, 43, 59, 63, 65, 67, 69"
echo "  - Categories: Cat A, Cat B, Cat C, Cat D"
echo ""
echo "Backup: $BACKUP_FILE"
echo ""
echo -e "${YELLOW}Note: Existing evaluation code already supports this rule${NC}"
echo -e "${YELLOW}Function: evaluate_high_decline_rate_rule/3 in context.ex${NC}"
echo ""