#!/bin/bash
#
# Universal White-Label Deployment Script
#
# This script is fully configurable via .env variables for any installation.
#
# Required .env variables for SERVER environment:
#   DEPLOY_ENVIRONMENT=server           - Set to "server" for remote deployments
#   DEPLOY_SERVER_TYPE=dev|prod         - "dev" or "prod" (required for server)
#   DEPLOY_APP_DIR=/path/to/app         - Full path to application directory
#   APP_URL=https://yourdomain.org      - Your application URL
#   DEPLOY_WS_URL=wss://ws.yourdomain   - Your WebSocket server URL
#   DEPLOY_WEB_USER=www-data            - Web server user (optional, defaults to www-data)
#   DEPLOY_WEB_GROUP=www-data           - Web server group (optional, defaults to www-data)
#
# For LOCAL environment (defaults):
#   DEPLOY_ENVIRONMENT=local            - Set to "local" or leave unset
#   DEPLOY_WS_URL=ws://localhost:8080   - Local WebSocket URL (optional)
#   APP_URL=http://localhost:8000       - Local app URL (optional)
#
# Command line options:
#   -m  Skip migrations
#   -n  Skip npm build
#   -e  Specify environment (server or local) - overrides DEPLOY_ENVIRONMENT
#   -d  Force development build (not recommended for server environments)
#
# Build behavior:
#   - SERVER environments: Always uses 'npm run build' (production) for proper standalone assets
#   - LOCAL environments: Uses 'npm run dev' (development) for faster builds
#   - Use -d flag to force 'npm run dev' on servers (only for debugging, not recommended)
#
# Examples:
#   ./deploy.sh              (full deployment with local settings)
#   ./deploy.sh -m -n        (skip migrations and npm build)
#   ./deploy.sh -e server    (deploy to server using .env configuration with production build)
#

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

# Function for printing section headers
section_header() {
  echo -e "\n${BLUE}===========================================================${NC}"
  echo -e "${BLUE}   $1${NC}"
  echo -e "${BLUE}===========================================================${NC}\n"
}

# Error handling
error_exit() {
  echo -e "${RED}ERROR: $1${NC}" >&2
  exit 1
}

# Function to prompt for MySQL deployment credentials if not in .env
prompt_mysql_deploy_credentials() {
  if [ -z "$DB_DEPLOY_USERNAME" ]; then
    echo -e "${YELLOW}MySQL user with GRANT privileges needed to manage ALTER permission${NC}"
    echo -e "${YELLOW}This can be root or a dedicated deployment user${NC}"
    read -p "MySQL username [root]: " DB_DEPLOY_USERNAME
    DB_DEPLOY_USERNAME="${DB_DEPLOY_USERNAME:-root}"
  fi

  if [ -z "$DB_DEPLOY_PASSWORD" ]; then
    read -sp "MySQL password: " DB_DEPLOY_PASSWORD
    echo ""
  fi
}

# Function to execute MySQL commands with deployment user credentials
mysql_deploy_exec() {
  local sql="$1"

  # Ensure we have deployment credentials
  if [ -z "$DB_DEPLOY_USERNAME" ]; then
    return 1
  fi

  # Execute with credentials
  if [ -n "$DB_DEPLOY_PASSWORD" ]; then
    mysql -u "$DB_DEPLOY_USERNAME" -p"$DB_DEPLOY_PASSWORD" -e "$sql" 2>/dev/null
  else
    mysql -u "$DB_DEPLOY_USERNAME" -e "$sql" 2>/dev/null
  fi
}

# Function to grant ALTER permission to application database user
grant_alter_permission() {
  local db_user=$(php artisan tinker --execute="echo config('database.connections.mysql.username');" 2>/dev/null | grep -v ">>>" | grep -v "Psy" | tr -d '\n' | xargs)
  local db_name=$(php artisan tinker --execute="echo config('database.connections.mysql.database');" 2>/dev/null | grep -v ">>>" | grep -v "Psy" | tr -d '\n' | xargs)
  local db_host=$(php artisan tinker --execute="echo config('database.connections.mysql.host');" 2>/dev/null | grep -v ">>>" | grep -v "Psy" | tr -d '\n' | xargs)

  if [ -z "$db_user" ] || [ -z "$db_name" ]; then
    echo -e "${RED}✗ Could not determine database user or name from Laravel config${NC}"
    return 1
  fi

  echo -e "${BLUE}Database: $db_name${NC}"
  echo -e "${BLUE}User: $db_user${NC}"
  echo -e "${BLUE}Connection Host: $db_host${NC}"
  echo ""

  # Prompt for MySQL deployment credentials if not already set
  prompt_mysql_deploy_credentials

  # Test connection first
  if ! mysql_deploy_exec "SELECT 1;" >/dev/null 2>&1; then
    echo -e "${RED}✗ MySQL connection failed. Check credentials.${NC}"
    return 1
  fi

  # Determine which host(s) to grant to based on Laravel config
  local grant_hosts=()
  if [ "$db_host" = "127.0.0.1" ]; then
    grant_hosts+=("127.0.0.1")
    echo -e "${BLUE}Granting ALTER to 'timebank_cc_dev'@'127.0.0.1' (Laravel connects via IP)${NC}"
  elif [ "$db_host" = "localhost" ]; then
    grant_hosts+=("localhost")
    echo -e "${BLUE}Granting ALTER to 'timebank_cc_dev'@'localhost' (Laravel connects via socket)${NC}"
  else
    # If it's something else, grant to both to be safe
    grant_hosts+=("localhost" "127.0.0.1")
    echo -e "${BLUE}Granting ALTER to both localhost and 127.0.0.1 (host: $db_host)${NC}"
  fi

  # Grant ALTER, CREATE, DROP to the appropriate host(s) (all needed for migrations)
  for host in "${grant_hosts[@]}"; do
    if ! mysql_deploy_exec "GRANT ALTER, CREATE, DROP ON \`$db_name\`.* TO '$db_user'@'$host';"; then
      echo -e "${YELLOW}⚠ Warning: Could not grant to $host (user may not exist)${NC}"
    fi
  done

  # Flush privileges
  mysql_deploy_exec "FLUSH PRIVILEGES;"

  # Verify the grant actually worked by checking the host Laravel uses
  echo -e "${BLUE}Verifying ALTER permission was granted...${NC}"
  local verify_host="${grant_hosts[0]}"
  local has_alter=$(mysql_deploy_exec "SHOW GRANTS FOR '$db_user'@'$verify_host';" 2>/dev/null | grep -i "ALTER" | wc -l)

  if [ "$has_alter" -gt 0 ]; then
    echo -e "${GREEN}✓ ALTER permission verified for $db_user@$verify_host on $db_name${NC}"
    return 0
  else
    echo -e "${RED}✗ ALTER permission was NOT granted (verification failed)${NC}"
    echo -e "${YELLOW}Current grants for $db_user@$verify_host:${NC}"
    mysql_deploy_exec "SHOW GRANTS FOR '$db_user'@'$verify_host';" 2>/dev/null || echo "Could not retrieve grants"
    return 1
  fi
}

# Function to grant UPDATE/DELETE on any tables missing those permissions (e.g. tables added after restricted user was created)
grant_missing_table_permissions() {
  local db_user=$(php artisan tinker --execute="echo config('database.connections.mysql.username');" 2>/dev/null | grep -v ">>>" | grep -v "Psy" | tr -d '\n' | xargs)
  local db_name=$(php artisan tinker --execute="echo config('database.connections.mysql.database');" 2>/dev/null | grep -v ">>>" | grep -v "Psy" | tr -d '\n' | xargs)
  local db_host=$(php artisan tinker --execute="echo config('database.connections.mysql.host');" 2>/dev/null | grep -v ">>>" | grep -v "Psy" | tr -d '\n' | xargs)

  if [ -z "$db_user" ] || [ -z "$db_name" ]; then
    echo -e "${YELLOW}Warning: Could not determine database user or name. Skipping table permission sync.${NC}"
    return 1
  fi

  # Immutable tables that should never get UPDATE/DELETE
  local immutable_tables="transactions transaction_types"

  # Determine host(s) to grant to
  local grant_hosts=()
  if [ "$db_host" = "127.0.0.1" ]; then
    grant_hosts+=("127.0.0.1")
  elif [ "$db_host" = "localhost" ]; then
    grant_hosts+=("localhost")
  else
    grant_hosts+=("localhost" "127.0.0.1")
  fi

  echo -e "${BLUE}Checking for tables missing UPDATE/DELETE permissions for $db_user...${NC}"

  # Get all tables in the database
  local all_tables
  if [ -n "$DB_DEPLOY_PASSWORD" ]; then
    all_tables=$(mysql -u "$DB_DEPLOY_USERNAME" -p"$DB_DEPLOY_PASSWORD" -N -e "SHOW TABLES FROM \`$db_name\`;" 2>/dev/null)
  else
    all_tables=$(mysql -u "$DB_DEPLOY_USERNAME" -N -e "SHOW TABLES FROM \`$db_name\`;" 2>/dev/null)
  fi

  if [ -z "$all_tables" ]; then
    echo -e "${YELLOW}Warning: Could not retrieve table list. Skipping table permission sync.${NC}"
    return 1
  fi

  local granted_count=0
  for table in $all_tables; do
    # Skip immutable tables
    if [[ " $immutable_tables " =~ " $table " ]]; then
      continue
    fi

    # Grant UPDATE, DELETE for this table to all relevant hosts
    for host in "${grant_hosts[@]}"; do
      mysql_deploy_exec "GRANT UPDATE, DELETE ON \`$db_name\`.\`$table\` TO '$db_user'@'$host';" 2>/dev/null
    done
    granted_count=$((granted_count + 1))
  done

  mysql_deploy_exec "FLUSH PRIVILEGES;"
  echo -e "${GREEN}✓ UPDATE/DELETE permissions synced for $granted_count tables (excluding immutable tables)${NC}"
}

# Function to revoke ALTER permission from application database user
revoke_alter_permission() {
  echo -e "${BLUE}Revoking ALTER permission (principle of least privilege)...${NC}"

  local db_user=$(php artisan tinker --execute="echo config('database.connections.mysql.username');" 2>/dev/null | grep -v ">>>" | grep -v "Psy" | tr -d '\n' | xargs)
  local db_name=$(php artisan tinker --execute="echo config('database.connections.mysql.database');" 2>/dev/null | grep -v ">>>" | grep -v "Psy" | tr -d '\n' | xargs)
  local db_host=$(php artisan tinker --execute="echo config('database.connections.mysql.host');" 2>/dev/null | grep -v ">>>" | grep -v "Psy" | tr -d '\n' | xargs)

  if [ -z "$db_user" ] || [ -z "$db_name" ]; then
    echo -e "${YELLOW}Warning: Could not determine database user or name. Skipping permission revoke.${NC}"
    return 1
  fi

  # Determine which host(s) to revoke from based on Laravel config
  local revoke_hosts=()
  if [ "$db_host" = "127.0.0.1" ]; then
    revoke_hosts+=("127.0.0.1")
  elif [ "$db_host" = "localhost" ]; then
    revoke_hosts+=("localhost")
  else
    revoke_hosts+=("localhost" "127.0.0.1")
  fi

  # Revoke ALTER from the appropriate host(s)
  local revoked=0
  for host in "${revoke_hosts[@]}"; do
    if mysql_deploy_exec "REVOKE ALTER ON \`$db_name\`.* FROM '$db_user'@'$host';"; then
      echo -e "${GREEN}✓ ALTER permission revoked from $db_user@$host${NC}"
      revoked=1
    fi
  done

  mysql_deploy_exec "FLUSH PRIVILEGES;"

  if [ $revoked -eq 1 ]; then
    return 0
  else
    echo -e "${YELLOW}Warning: Could not revoke ALTER permission. This is not critical but reduces security.${NC}"
    return 1
  fi
}

# Load .env variables
set -a
source .env 2>/dev/null
set +a

# Parse command line arguments
skip_migrations=false
skip_npm=false
environment=""
dev_build=false

while getopts "mne:d" opt; do
  case $opt in
    m) skip_migrations=true ;;
    n) skip_npm=true ;;
    e) environment="$OPTARG" ;;
    d) dev_build=true ;;
    *) ;;
  esac
done

# Determine environment from -e flag or DEPLOY_ENVIRONMENT
if [ -z "$environment" ]; then
  # Check DEPLOY_ENVIRONMENT from .env
  if [ -n "$DEPLOY_ENVIRONMENT" ]; then
    environment="$DEPLOY_ENVIRONMENT"
  else
    # Default to local if not specified
    environment="local"
  fi
fi

# Configure environment-specific settings
if [ "$environment" = "server" ]; then
  # Require .env variables for server environment
  if [ -z "$DEPLOY_APP_DIR" ]; then
    error_exit "DEPLOY_APP_DIR must be set in .env for server environment"
  fi

  APP_DIR="$DEPLOY_APP_DIR"
  WEB_USER="${DEPLOY_WEB_USER:-www-data}"
  WEB_GROUP="${DEPLOY_WEB_GROUP:-www-data}"

  # Determine server type from .env (required for server environment)
  if [ -n "$DEPLOY_SERVER_TYPE" ]; then
    SERVER_TYPE="${DEPLOY_SERVER_TYPE^^}"  # Convert to uppercase
  else
    error_exit "DEPLOY_SERVER_TYPE must be set in .env (dev or prod)"
  fi

  # APP_URL is required for server environment
  if [ -z "$APP_URL" ]; then
    error_exit "APP_URL must be set in .env for server environment"
  fi

  # DEPLOY_WS_URL is required for server environment
  if [ -z "$DEPLOY_WS_URL" ]; then
    error_exit "DEPLOY_WS_URL must be set in .env for server environment"
  fi
  WS_URL="$DEPLOY_WS_URL"

  # Determine build mode - server environments always use production builds
  # (use -d flag to override and force development build if needed)
  if [ "$dev_build" = true ]; then
    BUILD_MODE="dev"
    ENV_NAME="${SERVER_TYPE}-SERVER (Development Build - forced with -d flag)"
  else
    BUILD_MODE="build"
    ENV_NAME="${SERVER_TYPE}-SERVER (Production Build)"
  fi
else
  # Local environment settings
  APP_DIR="${DEPLOY_APP_DIR:-$(pwd)}"
  WEB_USER="${DEPLOY_WEB_USER:-$(whoami)}"
  WEB_GROUP="${DEPLOY_WEB_GROUP:-$(whoami)}"

  # Use APP_URL from .env if set, otherwise default to localhost
  APP_URL="${APP_URL:-http://localhost:8000}"

  # Use DEPLOY_WS_URL from .env if set, otherwise default to localhost WebSocket
  WS_URL="${DEPLOY_WS_URL:-ws://localhost:8080}"

  BUILD_MODE="dev"
  ENV_NAME="LOCAL"
fi

# Start deployment
section_header "DEPLOYING APPLICATION ($ENV_NAME ENVIRONMENT)"

# Change to application directory
cd "$APP_DIR" || error_exit "Failed to change to project directory ($APP_DIR)"

# Check for uncommitted changes
if [[ -n $(git status -s) ]]; then
  echo -e "${YELLOW}Warning: You have uncommitted changes${NC}"
  echo "Continue anyway? (y/n)"
  read -r response
  if [[ ! "$response" =~ ^[Yy]$ ]]; then
    echo "Deployment cancelled."
    exit 0
  fi
fi

# Copy example config files if actual files don't exist
section_header "CHECKING CONFIGURATION FILES"
config_files_to_check=(
  "config/themes.php"
  "config/timebank-default.php"
  "config/timebank_cc.php"
)

for config_file in "${config_files_to_check[@]}"; do
  if [ ! -f "$config_file" ] && [ -f "${config_file}.example" ]; then
    echo -e "${YELLOW}Creating $config_file from example template...${NC}"
    cp "${config_file}.example" "$config_file"
    echo -e "${GREEN}Created $config_file${NC}"
  elif [ -f "$config_file" ]; then
    echo -e "${GREEN}$config_file exists (custom installation config)${NC}"
  else
    echo -e "${YELLOW}Warning: Neither $config_file nor ${config_file}.example found${NC}"
  fi
done

# Pull latest code
section_header "PULLING LATEST CODE"
# Attempt to pull and capture stderr to check for specific errors
pull_output=$(git pull origin main 2>&1)
pull_exit_code=$?

if [ $pull_exit_code -ne 0 ]; then
  # Check if the error is due to local changes or untracked files that would be overwritten
  if echo "$pull_output" | grep -q -E "overwritten by merge|untracked working tree files"; then
    echo -e "${YELLOW}Git pull failed due to local file conflicts:${NC}"
    echo "$pull_output" # Show the conflicting files
    echo -e "${YELLOW}What would you like to do?${NC}"
    echo "  1) Stash local changes and continue"
    echo "  2) Discard local changes and remove untracked files to continue"
    echo "  3) Cancel deployment"
    read -p "Enter your choice (1-3): " choice

    case $choice in
      1)
        echo "Stashing local changes (including untracked files)..."
        git stash --include-untracked || error_exit "Failed to stash changes."
        echo "Retrying git pull..."
        git pull origin main || error_exit "Git pull failed after stashing."
        ;;
      2)
        echo "Discarding local changes and removing untracked files..."
        git reset --hard HEAD || error_exit "Failed to discard changes."
        git clean -fd || error_exit "Failed to remove untracked files."
        echo "Retrying git pull..."
        git pull origin main || error_exit "Git pull failed after cleaning."
        ;;
      3)
        echo "Deployment cancelled."
        exit 0
        ;;
    esac
  else
    # It's a different git error, so fail as before
    echo "$pull_output"
    error_exit "Git pull failed with an unexpected error."
  fi
else
  # Git pull was successful on the first try
  echo "$pull_output"
fi

# Check for configuration updates (after pulling latest .example files)
section_header "CHECKING FOR CONFIGURATION UPDATES"
if php artisan config:merge --all --dry-run 2>/dev/null | grep -q "Found [0-9]"; then
  echo -e "${YELLOW}New configuration keys available in .example files${NC}"
  echo -e "${BLUE}Review changes with: php artisan config:merge --all --dry-run${NC}"
  echo -e "${BLUE}Documentation: references/CONFIG_MANAGEMENT.md${NC}"
  echo ""
  read -p "Would you like to merge new keys now? (y/N) " -n 1 -r
  echo
  if [[ $REPLY =~ ^[Yy]$ ]]; then
    php artisan config:merge --all || echo -e "${YELLOW}Config merge skipped or failed${NC}"
  else
    echo -e "${YELLOW}Skipping config merge - you can run manually later with: php artisan config:merge --all${NC}"
    echo -e "${BLUE}See references/CONFIG_MANAGEMENT.md for details on manual merge process${NC}"
  fi
else
  echo -e "${GREEN}Configuration is up to date${NC}"
fi

# Install dependencies
section_header "INSTALLING PHP DEPENDENCIES"
COMPOSER_ALLOW_SUPERUSER=1 composer install --no-interaction --prefer-dist --optimize-autoloader || error_exit "Composer install failed"

# Clear caches
section_header "CLEARING CACHES"
php artisan optimize:clear || echo -e "${YELLOW}Warning: Failed to clear some caches${NC}"

# Restart queue workers
section_header "RESTARTING QUEUE WORKERS"

if [ "$environment" = "server" ]; then
  # Server environment - restart systemd services
  echo -e "${BLUE}Restarting systemd queue worker services...${NC}"

  # Restart high priority worker
  if sudo systemctl is-active --quiet timebank-high-priority-worker; then
    echo -e "${BLUE}Restarting high priority worker...${NC}"
    sudo systemctl restart timebank-high-priority-worker || echo -e "${YELLOW}Warning: Failed to restart high priority worker${NC}"
  fi

  # Restart main queue worker
  if sudo systemctl is-active --quiet timebank-queue-worker; then
    echo -e "${BLUE}Restarting main queue worker...${NC}"
    sudo systemctl restart timebank-queue-worker || echo -e "${YELLOW}Warning: Failed to restart main queue worker${NC}"
  fi

  # Restart mailing workers (1-3)
  for i in 1 2 3; do
    if sudo systemctl is-active --quiet timebank-mailing-worker-$i; then
      echo -e "${BLUE}Restarting mailing worker $i...${NC}"
      sudo systemctl restart timebank-mailing-worker-$i || echo -e "${YELLOW}Warning: Failed to restart mailing worker $i${NC}"
    fi
  done

  echo -e "${GREEN}All active queue workers have been restarted${NC}"
else
  # Local environment - use queue:restart command
  php artisan queue:restart || echo -e "${YELLOW}Warning: Failed to restart queue workers. If you have queue workers running, restart them manually.${NC}"
fi

# Link storage
section_header "LINKING STORAGE"
php artisan storage:link || echo -e "${YELLOW}Warning: Failed to link storage. This is normal if the link already exists.${NC}"

# Run migrations
if [ "$skip_migrations" = false ]; then
  section_header "RUNNING MIGRATIONS"

  # Grant ALTER permission temporarily for migrations
  echo -e "${YELLOW}Note: ALTER permission is needed to modify database schema during migrations${NC}"

  # Try to grant ALTER permission (with retry on failure)
  GRANTED_ALTER=1
  MAX_ATTEMPTS=3
  ATTEMPT=1

  while [ $GRANTED_ALTER -ne 0 ] && [ $ATTEMPT -le $MAX_ATTEMPTS ]; do
    if [ $ATTEMPT -gt 1 ]; then
      echo -e "${YELLOW}Retry attempt $ATTEMPT of $MAX_ATTEMPTS${NC}"
    fi

    grant_alter_permission
    GRANTED_ALTER=$?

    if [ $GRANTED_ALTER -ne 0 ] && [ $ATTEMPT -lt $MAX_ATTEMPTS ]; then
      echo -e "${RED}Failed to grant ALTER permission${NC}"
      read -p "Press Enter to retry, or Ctrl+C to abort deployment... "
    fi

    ATTEMPT=$((ATTEMPT + 1))
  done

  # Check if we successfully granted ALTER permission
  if [ $GRANTED_ALTER -ne 0 ]; then
    echo -e "${RED}━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━${NC}"
    echo -e "${RED}DEPLOYMENT FAILED: Could not grant ALTER permission${NC}"
    echo -e "${RED}Database migrations require ALTER permission to modify schema${NC}"
    echo -e "${RED}━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━${NC}"
    echo ""
    echo -e "${YELLOW}TROUBLESHOOTING:${NC}"
    echo -e "1. Verify MySQL credentials have GRANT privileges on the database"
    echo -e "2. Add credentials to .env to avoid prompts:"
    echo -e "   ${BLUE}DB_DEPLOY_USERNAME=root${NC}"
    echo -e "   ${BLUE}DB_DEPLOY_PASSWORD=your_password${NC}"
    echo -e "3. Or manually grant permission:"
    echo -e "   ${BLUE}mysql -u root -p -e \"GRANT ALTER, CREATE, DROP ON \\\`timebank_cc\\\`.* TO 'timebank_cc_dev'@'localhost';\"${NC}"
    echo ""
    exit 1
  fi

  echo -e "${GREEN}✓ ALTER permission granted - proceeding with migrations${NC}"

  # Clear Laravel's database connection cache to ensure new permissions are used
  echo -e "${BLUE}Clearing Laravel caches to apply new permissions...${NC}"
  php artisan config:clear >/dev/null 2>&1
  php artisan cache:clear >/dev/null 2>&1
  echo ""

  # Create database backup before migrations using the new backup system
  echo -e "${YELLOW}Creating pre-deployment database backup...${NC}"
  if [ -x "scripts/backup-database.sh" ]; then
    # Use the new backup system with pre-restore type
    # Note: Cleanup is handled automatically by scripts/cleanup-backups.sh
    # which runs based on backup-retention.conf settings
    if ./scripts/backup-database.sh pre-restore; then
      echo -e "${GREEN}Database backup created successfully${NC}"
      echo -e "${BLUE}(Retention managed by backup-retention.conf: keeps ${PRE_RESTORE_COUNT_LIMIT:-5} backups or ${PRE_RESTORE_RETENTION:-14} days)${NC}"
    else
      echo -e "${YELLOW}Warning: Database backup failed${NC}"
    fi
  else
    # Fallback to old backup method if new script doesn't exist
    DB_CONNECTION=$(php artisan tinker --execute="echo config('database.default');" | grep -v ">>>" | grep -v "Psy")
    DB_DATABASE=$(php artisan tinker --execute="echo config('database.connections.$DB_CONNECTION.database');" | grep -v ">>>" | grep -v "Psy")
    DB_USERNAME=$(php artisan tinker --execute="echo config('database.connections.$DB_CONNECTION.username');" | grep -v ">>>" | grep -v "Psy")
    DB_PASSWORD=$(php artisan tinker --execute="echo config('database.connections.$DB_CONNECTION.password');" | grep -v ">>>" | grep -v "Psy")

    mkdir -p storage/backups
    BACKUP_FILE="storage/backups/db-backup-$(date +'%Y-%m-%d-%H%M%S').sql"

    if mysqldump -u "$DB_USERNAME" -p"$DB_PASSWORD" "$DB_DATABASE" > "$BACKUP_FILE" 2>/dev/null; then
      echo -e "${GREEN}Database backup created at $BACKUP_FILE${NC}"

      # Clean up old deploy backups in legacy location (keep only last 3)
      echo -e "${YELLOW}Cleaning up old legacy deploy backups (keeping last 3)...${NC}"
      ls -t storage/backups/db-backup-*.sql 2>/dev/null | tail -n +4 | xargs rm -f 2>/dev/null || true
      echo -e "${GREEN}Legacy backups cleaned up${NC}"
    else
      echo -e "${YELLOW}Warning: Database backup failed${NC}"
    fi
  fi

  # Elasticsearch re-indexing
  section_header "RE-INDEXING ELASTICSEARCH USING re-index-search.sh"

  # Call the re-index-search script
  bash scripts/re-index-search.sh || error_exit "Re-indexing failed"

  # Migrations header with safety information
  section_header "MIGRATIONS"
  echo -e "${GREEN}ℹ  Database migrations will modify the schema but will NOT remove existing data${NC}"
  echo -e "${GREEN}ℹ  A backup was created above and can be restored if needed${NC}"
  echo ""
  echo -e "${BLUE}To restore from backup if needed:${NC}"
  echo -e "  1. Find backup: ${YELLOW}ls -lt storage/backups/ | head -5${NC}"
  echo -e "  2. Restore: ${YELLOW}mysql -u [user] -p [database] < storage/backups/[backup-file].sql${NC}"
  echo -e "  3. Or use: ${YELLOW}./scripts/restore-database.sh [backup-file]${NC} (if script exists)"
  echo ""

  # Run migrations with deploy credentials (CREATE TABLE requires privileges the app user may lack)
  echo -e "${BLUE}Running: php artisan migrate${NC}"
  echo -e "${YELLOW}This will execute any pending database migrations${NC}"
  DB_USERNAME="$DB_DEPLOY_USERNAME" DB_PASSWORD="$DB_DEPLOY_PASSWORD" php artisan migrate || error_exit "Migrations failed"

  # Run database updates (for schema changes and data migrations)
  section_header "RUNNING DATABASE UPDATES"
  echo -e "${BLUE}Running: php artisan database:update${NC}"
  echo -e "${YELLOW}This will execute schema changes and data migrations${NC}"
  DB_USERNAME="$DB_DEPLOY_USERNAME" DB_PASSWORD="$DB_DEPLOY_PASSWORD" php artisan database:update || error_exit "Database updates failed"

  # Run category seeders
  section_header "RUNNING CATEGORY SEEDERS"
  echo -e "${BLUE}Running: php artisan db:seed --class=ExtendCategoriesSeeder${NC}"
  echo -e "${YELLOW}This will seed/update category data${NC}"
  php artisan db:seed --class=ExtendCategoriesSeeder || error_exit "ExtendCategoriesSeeder failed"

  echo -e "${BLUE}Running: php artisan db:seed --class=ExtendCategoryTranslationsSeeder${NC}"
  echo -e "${YELLOW}This will seed/update category translations${NC}"
  php artisan db:seed --class=ExtendCategoryTranslationsSeeder || error_exit "ExtendCategoryTranslationsSeeder failed"

  # Grant UPDATE/DELETE on any tables added since restricted user was created
  section_header "SYNCING TABLE PERMISSIONS"
  grant_missing_table_permissions

  # Revoke ALTER permission after migrations complete
  if [ $GRANTED_ALTER -eq 0 ]; then
    revoke_alter_permission
  fi
else
  echo -e "${YELLOW}Skipping migrations (--skip-migrations flag provided)${NC}"
fi

# Verify transaction immutability (critical security check)
section_header "VERIFYING TRANSACTION IMMUTABILITY"

if [ -x "scripts/test-transaction-immutability.sh" ]; then
  echo -e "${BLUE}Running transaction immutability security test...${NC}"

  if ./scripts/test-transaction-immutability.sh; then
    echo -e "${GREEN}✓ Transaction immutability verified - financial records are protected${NC}"
  else
    echo -e "${RED}━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━${NC}"
    echo -e "${RED}DEPLOYMENT FAILED: Transaction immutability test failed${NC}"
    echo -e "${RED}Financial transaction records are NOT properly protected!${NC}"
    echo -e "${RED}━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━${NC}"
    echo ""
    echo -e "${YELLOW}CRITICAL SECURITY ISSUE:${NC}"
    echo -e "Database user has permission to UPDATE or DELETE transaction records."
    echo -e "This violates financial audit requirements for immutable transaction logs."
    echo ""
    echo -e "${YELLOW}RECOMMENDED ACTIONS:${NC}"
    echo -e "${YELLOW}1.${NC} Run: ${BLUE}./scripts/create-restricted-db-user-safe.sh${NC}"
    echo -e "${YELLOW}2.${NC} Update ${BLUE}.env${NC} with restricted database user credentials"
    echo -e "${YELLOW}3.${NC} Clear config cache: ${BLUE}php artisan config:clear${NC}"
    echo -e "${YELLOW}4.${NC} Re-run deployment: ${BLUE}./deploy.sh${NC}"
    echo ""
    echo -e "${YELLOW}For detailed instructions, see:${NC}"
    echo -e "  ${BLUE}references/TRANSACTION_IMMUTABILITY_FIX.md${NC}"
    echo ""
    exit 1
  fi
elif [ -f "scripts/test-transaction-immutability.sh" ]; then
  echo -e "${YELLOW}⚠ Warning: Transaction immutability test script exists but is not executable${NC}"
  echo -e "${YELLOW}  Run: chmod +x scripts/test-transaction-immutability.sh${NC}"
  echo -e "${YELLOW}  Skipping immutability verification${NC}"
else
  echo -e "${YELLOW}⚠ Warning: Transaction immutability test script not found${NC}"
  echo -e "${YELLOW}  Expected location: scripts/test-transaction-immutability.sh${NC}"
  echo -e "${YELLOW}  Skipping immutability verification${NC}"
fi

# Build assets
if [ "$skip_npm" = false ]; then
  section_header "BUILDING ASSETS (${BUILD_MODE} mode)"
  npm install --no-audit || error_exit "NPM install failed"

  # Use build mode determined earlier
  npm run $BUILD_MODE || error_exit "NPM run $BUILD_MODE failed"
else
  echo -e "${YELLOW}Skipping NPM build (--skip-npm flag provided)${NC}"
fi

# Set permissions (with environment-specific handling)
section_header "SETTING PERMISSIONS"

if [ "$environment" = "server" ]; then
  # Server environment - use sudo
  sudo chown -R $WEB_USER:$WEB_GROUP storage bootstrap/cache public/storage public/build || error_exit "Failed to set owner permissions"
  sudo chmod -R 775 storage bootstrap/cache public/build || error_exit "Failed to set directory permissions"
else
  # Local environment - no sudo needed
  chmod -R 775 storage bootstrap/cache public/build || echo -e "${YELLOW}Warning: Permission change failed${NC}"
  echo -e "${YELLOW}Note: Local environment - no ownership changes performed${NC}"
fi

# Set up HTMLPurifier cache directory
section_header "SETTING UP HTMLPURIFIER CACHE DIRECTORY"
echo -e "${BLUE}Configuring HTMLPurifier cache directory to prevent permission errors...${NC}"

if [ -f "deployment-htmlpurifier-fix.sh" ]; then
  # Run the HTMLPurifier setup script with the same web user/group
  bash deployment-htmlpurifier-fix.sh "$WEB_USER" "$WEB_GROUP" || echo -e "${YELLOW}Warning: HTMLPurifier setup encountered issues but deployment will continue${NC}"
else
  echo -e "${YELLOW}Warning: deployment-htmlpurifier-fix.sh not found, skipping HTMLPurifier setup${NC}"
  echo -e "${YELLOW}HTMLPurifier will auto-create cache directory on first use${NC}"
fi

# Set up Laravel scheduler cron job (server environment only)
if [ "$environment" = "server" ]; then
  section_header "SETTING UP LARAVEL SCHEDULER"

  # Create the cron job entry
  CRON_ENTRY="* * * * * cd $APP_DIR && php artisan schedule:run >> /dev/null 2>&1"

  # Check if the cron job already exists for www-data user
  if sudo crontab -u $WEB_USER -l 2>/dev/null | grep -q "artisan schedule:run"; then
    echo -e "${YELLOW}Laravel scheduler cron job already exists for $WEB_USER user${NC}"
  else
    echo -e "${BLUE}Adding Laravel scheduler cron job for $WEB_USER user...${NC}"

    # Get existing crontab (if any) and add our entry
    (sudo crontab -u $WEB_USER -l 2>/dev/null; echo "$CRON_ENTRY") | sudo crontab -u $WEB_USER -

    if [ $? -eq 0 ]; then
      echo -e "${GREEN}Laravel scheduler cron job added successfully${NC}"
    else
      echo -e "${YELLOW}Warning: Failed to add Laravel scheduler cron job${NC}"
      echo -e "${YELLOW}   You may need to manually add this cron job:${NC}"
      echo -e "${YELLOW}   $CRON_ENTRY${NC}"
    fi
  fi
else
  echo -e "${YELLOW}Note: Scheduler cron job setup skipped for local environment${NC}"
fi

# Finish
section_header "APPLICATION DEPLOYED SUCCESSFULLY!"
echo -e "${GREEN}Deployment completed at: $(date)${NC}"
echo -e "${YELLOW}Don't forget to check your .env file for any needed changes${NC}"

# Print helpful post-deployment info
section_header "ENVIRONMENT INFO"
echo "App URL: $APP_URL"
echo "Laravel Version: $(php artisan --version)"
echo "PHP Version: $(php -v | head -n 1)"
echo "WebSocket Server: $WS_URL"
if [ "$environment" = "server" ]; then
  echo "Build Mode: ${BUILD_MODE} (server always uses production builds)"
else
  echo "Build Mode: ${BUILD_MODE} (local uses development builds)"
fi