#!/usr/bin/env bash

set -euo pipefail

# BeepZone Database Updater
# Applies SQL update scripts from backend/database/dev/ to the configured database.

SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
WORK_DIR="${SCRIPT_DIR}"

# Check for MariaDB/MySQL client - try common brew locations on macOS
MYSQL_CLIENT=""
if command -v mariadb >/dev/null 2>&1; then
  MYSQL_CLIENT="mariadb"
elif command -v mysql >/dev/null 2>&1; then
  MYSQL_CLIENT="mysql"
elif [[ -x /opt/homebrew/opt/mysql-client/bin/mysql ]]; then
  MYSQL_CLIENT="/opt/homebrew/opt/mysql-client/bin/mysql"
  export PATH="/opt/homebrew/opt/mysql-client/bin:$PATH"
elif [[ -x /opt/homebrew/opt/mariadb/bin/mariadb ]]; then
  MYSQL_CLIENT="/opt/homebrew/opt/mariadb/bin/mariadb"
  export PATH="/opt/homebrew/opt/mariadb/bin:$PATH"
elif [[ -x /opt/homebrew/bin/mariadb ]]; then
  MYSQL_CLIENT="/opt/homebrew/bin/mariadb"
  export PATH="/opt/homebrew/bin:$PATH"
elif [[ -x /opt/homebrew/bin/mysql ]]; then
  MYSQL_CLIENT="/opt/homebrew/bin/mysql"
  export PATH="/opt/homebrew/bin:$PATH"
elif [[ -x /usr/local/opt/mysql-client/bin/mysql ]]; then
  MYSQL_CLIENT="/usr/local/opt/mysql-client/bin/mysql"
  export PATH="/usr/local/opt/mysql-client/bin:$PATH"
elif [[ -x /usr/local/bin/mariadb ]]; then
  MYSQL_CLIENT="/usr/local/bin/mariadb"
  export PATH="/usr/local/bin:$PATH"
elif [[ -x /usr/local/bin/mysql ]]; then
  MYSQL_CLIENT="/usr/local/bin/mysql"
  export PATH="/usr/local/bin:$PATH"
else
  echo "[ERROR] MariaDB/MySQL client is required but not found."
  exit 1
fi

ENV_FILE="$SCRIPT_DIR/.env"

# Load existing settings from .env if present
if [[ -f "$ENV_FILE" ]]; then
  source "$ENV_FILE"
else
  echo "[ERROR] .env file not found. Please run beepzone-helper.sh first to configure the environment."
  exit 1
fi

# Set defaults if not loaded from .env (though they should be)
: "${DB_HOST:=127.0.0.1}"
: "${DB_PORT:=3306}"
: "${DB_NAME:=beepzone}"
: "${DB_USER:=beepzone}"
: "${DB_PASS:=beepzone}"

echo "=== BeepZone Database Updater ==="
echo "Target Database: $DB_NAME on $DB_HOST:$DB_PORT"
echo "User: $DB_USER"
echo ""

UPDATES_DIR="$WORK_DIR/backend/database/dev"

if [[ ! -d "$UPDATES_DIR" ]]; then
  echo "[ERROR] Updates directory not found: $UPDATES_DIR"
  exit 1
fi

# Find update scripts
update_scripts=($(find "$UPDATES_DIR" -name "update_*.sql" | sort))

if [[ ${#update_scripts[@]} -eq 0 ]]; then
  echo "No update scripts found in $UPDATES_DIR."
  exit 0
fi

echo "Found ${#update_scripts[@]} update script(s):"
for script in "${update_scripts[@]}"; do
  echo " - $(basename "$script")"
done
echo ""

read -p "Do you want to apply these updates? (y/N) " -n 1 -r
echo ""
if [[ ! $REPLY =~ ^[Yy]$ ]]; then
  echo "Aborted."
  exit 0
fi

echo ""
for script in "${update_scripts[@]}"; do
  script_name=$(basename "$script")
  echo "Applying $script_name..."
  
  if "$MYSQL_CLIENT" -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" < "$script"; then
    echo " [OK] $script_name applied successfully."
  else
    echo " [ERROR] Failed to apply $script_name."
    echo "Stopping further updates."
    exit 1
  fi
done

echo ""
echo "All updates applied successfully."