#!/bin/bash

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

DB_NAME="shukria_transactions"
DB_USER="root"
DB_PASS="dataaegis123"
BACKUP_DIR="backups"
MIGRATION_FILE="priv/repo/migrations/03_add_round_figure_multiple_merchants_rule.sql"

echo -e "${BLUE}======================================${NC}"
echo -e "${BLUE}Add Round Figure Multiple Merchants Rule${NC}"
echo -e "${BLUE}======================================${NC}\n"

# Step 1: Create backup directory
echo -e "${YELLOW}Step 1: Creating backup directory...${NC}"
mkdir -p $BACKUP_DIR

# Step 2: Backup current risk_rules table
BACKUP_FILE="${BACKUP_DIR}/risk_rules_backup_$(date +%Y%m%d_%H%M%S).sql"
echo -e "${YELLOW}Step 2: Backing up risk_rules table to ${BACKUP_FILE}...${NC}"
mysqldump -u $DB_USER -p$DB_PASS $DB_NAME risk_rules > $BACKUP_FILE
echo -e "${GREEN}✓ Backup created successfully${NC}\n"

# Step 3: Show current rule count
echo -e "${YELLOW}Step 3: Current rule count per category:${NC}"
mysql -u $DB_USER -p$DB_PASS -D $DB_NAME -e "SELECT category, COUNT(*) as total_rules FROM risk_rules GROUP BY category"
echo ""

# Step 4: Check if rule already exists
echo -e "${YELLOW}Step 4: Checking if rule already exists...${NC}"
EXISTING_COUNT=$(mysql -u $DB_USER -p$DB_PASS -D $DB_NAME -se "SELECT COUNT(*) FROM risk_rules WHERE name = 'Round Figure Transaction using same card at Multiple Merchants'")
if [ "$EXISTING_COUNT" -gt 0 ]; then
    echo -e "${RED}✗ Rule already exists! Found $EXISTING_COUNT records.${NC}"
    echo -e "${RED}Aborting migration.${NC}\n"
    exit 1
fi
echo -e "${GREEN}✓ Rule does not exist yet${NC}\n"

# Step 5: Show rules that will be renumbered
echo -e "${YELLOW}Step 5: Rules at order 12+ that will be shifted to 13+:${NC}"
mysql -u $DB_USER -p$DB_PASS -D $DB_NAME -e "SELECT execution_order, name, category FROM risk_rules WHERE execution_order >= 12 ORDER BY execution_order, category LIMIT 8"
echo ""

# Step 6: Confirmation
echo -e "${YELLOW}Step 6: Ready to add new rule at order 12${NC}"
echo -e "This will:"
echo -e "  - Insert 'Round Figure Transaction using same card at Multiple Merchants' at order 12"
echo -e "  - Shift existing rules at order 12+ to order 13+"
echo -e "  - Apply to all 4 categories (Cat A, B, C, D)"
echo ""
read -p "Do you want to proceed? (yes/no): " CONFIRM

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

# Step 7: Execute migration
echo -e "\n${YELLOW}Step 7: Executing migration...${NC}"
mysql -u $DB_USER -p$DB_PASS -D $DB_NAME < $MIGRATION_FILE

if [ $? -eq 0 ]; then
    echo -e "${GREEN}✓ Migration executed successfully${NC}\n"
else
    echo -e "${RED}✗ Migration failed!${NC}"
    echo -e "${YELLOW}You can restore from backup: $BACKUP_FILE${NC}\n"
    exit 1
fi

# Step 8: Verify the new rule
echo -e "${YELLOW}Step 8: Verifying new rule details...${NC}"
mysql -u $DB_USER -p$DB_PASS -D $DB_NAME -e "
SELECT 
  execution_order,
  name,
  category,
  rule_type,
  enabled,
  JSON_EXTRACT(parameters, '$.round_figure_multiple') as round_multiple,
  JSON_EXTRACT(parameters, '$.min_merchant_count') as min_merchants,
  JSON_EXTRACT(parameters, '$.time_window_hours') as time_window
FROM risk_rules 
WHERE name LIKE '%Round Figure%Multiple Merchants%'
ORDER BY category"
echo ""

# Step 9: Final count verification
echo -e "${YELLOW}Step 9: Final rule count per category:${NC}"
mysql -u $DB_USER -p$DB_PASS -D $DB_NAME -e "SELECT category, COUNT(*) as total_rules FROM risk_rules GROUP BY category"
echo ""

echo -e "${GREEN}======================================${NC}"
echo -e "${GREEN}✓ Round Figure Multiple Merchants Rule Added Successfully!${NC}"
echo -e "${GREEN}======================================${NC}\n"
echo -e "${BLUE}Summary:${NC}"
echo -e "  • Rule added at order 12 for all 4 categories"
echo -e "  • Existing rules shifted from order 12+ to 13+"
echo -e "  • Backup saved: ${BACKUP_FILE}"
echo -e "  • ${YELLOW}NOTE: Evaluation function 'evaluate_round_figure_multiple_merchants_rule/3' needs to be implemented in context.ex${NC}"
echo ""