#!/usr/bin/env bash
# =============================================================================
#  db_restore.sh — Wild Dubai · XAMPP MySQL Database Restore Script
#  Run from project root: bash packages/database/db_restore.sh
# =============================================================================

set -euo pipefail   # Exit on any error, undefined variable, or pipe failure

# ── Config ────────────────────────────────────────────────────────────────────
MYSQL_BIN="/Applications/XAMPP/xamppfiles/bin/mysql"
MYSQL_USER="root"
MYSQL_PASS=""        # Leave empty — XAMPP default root has no password
DB_NAME="wild_dubai"
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
SCHEMA_FILE="$SCRIPT_DIR/schema.sql"
SEED_FILE="$SCRIPT_DIR/seed.sql"

# ── Colors ────────────────────────────────────────────────────────────────────
GREEN='\033[0;32m'; YELLOW='\033[1;33m'; RED='\033[0;31m'; NC='\033[0m'
info()    { echo -e "${GREEN}[INFO]${NC}  $*"; }
warn()    { echo -e "${YELLOW}[WARN]${NC}  $*"; }
error()   { echo -e "${RED}[ERROR]${NC} $*"; exit 1; }

# ── Helper: run mysql command ─────────────────────────────────────────────────
run_mysql() {
  if [[ -n "$MYSQL_PASS" ]]; then
    "$MYSQL_BIN" -u "$MYSQL_USER" -p"$MYSQL_PASS" "$@"
  else
    "$MYSQL_BIN" -u "$MYSQL_USER" "$@"
  fi
}

echo ""
echo "============================================================"
echo "  🦁  Wild Dubai — Database Restore Script"
echo "============================================================"
echo ""

# ── Step 1: Preflight checks ──────────────────────────────────────────────────
info "Step 1/5 · Running preflight checks..."

[[ -x "$MYSQL_BIN" ]] || error "MySQL binary not found at: $MYSQL_BIN\n  → Is XAMPP installed? Is it the right path?"
[[ -f "$SCHEMA_FILE" ]] || error "schema.sql not found at: $SCHEMA_FILE"
[[ -f "$SEED_FILE"   ]] || error "seed.sql not found at: $SEED_FILE"

# Verify XAMPP MySQL is actually running
run_mysql --execute="SELECT 1;" > /dev/null 2>&1 \
  || error "Cannot connect to MySQL. Is XAMPP MySQL server running? Check the XAMPP Control Panel."

info "  ✓ MySQL binary found and reachable"
info "  ✓ schema.sql found ($(du -sh "$SCHEMA_FILE" | cut -f1))"
info "  ✓ seed.sql found   ($(du -sh "$SEED_FILE"   | cut -f1))"
echo ""

# ── Step 2: Drop & recreate database ─────────────────────────────────────────
info "Step 2/5 · Dropping existing database (if any) and recreating..."

run_mysql --execute="
  DROP DATABASE IF EXISTS \`$DB_NAME\`;
  CREATE DATABASE \`$DB_NAME\`
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;
"

info "  ✓ Database '${DB_NAME}' created (utf8mb4, utf8mb4_unicode_ci)"
echo ""

# ── Step 3: Import schema.sql ─────────────────────────────────────────────────
info "Step 3/5 · Importing schema.sql (with FK checks disabled)..."

# Wrap the schema import in FK-check guards so tables can be created in any order
{
  echo "SET FOREIGN_KEY_CHECKS = 0;"
  cat "$SCHEMA_FILE"
  echo "SET FOREIGN_KEY_CHECKS = 1;"
} | run_mysql "$DB_NAME"

info "  ✓ schema.sql imported successfully"
echo ""

# ── Step 4: Import seed.sql ───────────────────────────────────────────────────
info "Step 4/5 · Importing seed.sql..."

{
  echo "SET FOREIGN_KEY_CHECKS = 0;"
  cat "$SEED_FILE"
  echo "SET FOREIGN_KEY_CHECKS = 1;"
} | run_mysql "$DB_NAME"

info "  ✓ seed.sql imported successfully"
echo ""

# ── Step 5: Verify ────────────────────────────────────────────────────────────
info "Step 5/5 · Verifying — listing all tables in '${DB_NAME}'..."
echo ""

run_mysql --execute="
  SELECT
    TABLE_NAME        AS 'Table',
    TABLE_ROWS        AS 'Rows (approx)',
    ENGINE            AS 'Engine',
    TABLE_COLLATION   AS 'Collation'
  FROM information_schema.TABLES
  WHERE TABLE_SCHEMA = '${DB_NAME}'
  ORDER BY TABLE_NAME;
"

echo ""
echo "============================================================"
echo -e "  ${GREEN}✅  Database restore complete!${NC}"
echo "    Host : localhost:3306"
echo "    DB   : ${DB_NAME}"
echo ""
echo "  Your Next.js / Express backend should now connect:"
echo "    DATABASE_URL=mysql://root:@localhost:3306/${DB_NAME}"
echo "============================================================"
echo ""