#!/usr/bin/env bash
# ============================================================
# HalaVoice server — restore ALL databases from a server backup
#
# Restores the dumps produced by ops/server-backup.sh:
#   <backup>/databases/pg/*.sql.gz      (+ _globals.sql.gz = roles)
#   <backup>/databases/mysql/*.sql.gz
#
# Usage (run as root on the NEW server):
#   ops/server-db-restore.sh --dir <backup-dir> [options]
#
# Options:
#   --dir DIR        backup dir (default: newest backups/server-*)
#   --pg-only        restore only PostgreSQL
#   --mysql-only     restore only MySQL
#   --only NAME      restore a single database by name
#   --pg-super URL   postgres superuser URL (else: .env EVOLUTION_DB_URL,
#                    else: peer auth as the postgres OS user)
#   --yes            don't prompt (DESTRUCTIVE: overwrites existing DBs)
#
# ⚠️  DESTRUCTIVE: existing databases of the same name are overwritten.
# NOTE on privileges:
#   • PostgreSQL: roles are recreated from _globals; each DB is then
#     owned/granted to a role matching its name (cPanel convention).
#   • MySQL: these per-DB dumps do NOT include MySQL users/grants
#     (cPanel manages those). Recreate the DB users in cPanel/WHM, or
#     restore the cPanel account, then run this to load the data.
# ============================================================
set -uo pipefail

REPO_ROOT="/home/ashraffarid2010/halavoice.store"
DIR="" ; MODE="both" ; ONLY="" ; PG_SUPER="" ; ASSUME_YES=0
while [ $# -gt 0 ]; do
  case "$1" in
    --dir) DIR="$2"; shift ;;
    --pg-only) MODE="pg" ;;
    --mysql-only) MODE="mysql" ;;
    --only) ONLY="$2"; shift ;;
    --pg-super) PG_SUPER="$2"; shift ;;
    --yes) ASSUME_YES=1 ;;
    -h|--help) sed -n '2,30p' "$0"; exit 0 ;;
    *) echo "unknown arg: $1" >&2; exit 2 ;;
  esac
  shift
done

log(){ echo -e "\033[1;36m[db-restore]\033[0m $*"; }
ok(){ echo -e "\033[1;32m[ ok ]\033[0m $*"; }
warn(){ echo -e "\033[1;33m[warn]\033[0m $*" >&2; }
die(){ echo -e "\033[1;31m[FAIL]\033[0m $*" >&2; exit 1; }

[ "$(id -u)" = 0 ] || die "run as root (sudo)"
[ -n "$DIR" ] || DIR="$(ls -1dt "$REPO_ROOT"/backups/server-* 2>/dev/null | head -1)"
[ -n "$DIR" ] && [ -d "$DIR" ] || die "backup dir not found (pass --dir)"
# accept either the server-<TS> dir or a pulled db-and-config dir
[ -d "$DIR/databases" ] || die "no databases/ under $DIR"
log "Restoring from: $DIR"

if [ "$ASSUME_YES" != 1 ]; then
  warn "This OVERWRITES existing databases of the same name."
  read -r -p "  Proceed? [type YES]: " a; [ "$a" = "YES" ] || die "aborted"
fi

# ---------- PostgreSQL ----------
restore_pg() {
  local pgdir="$DIR/databases/pg"
  [ -d "$pgdir" ] || { warn "no PostgreSQL dumps"; return; }
  # superuser connection
  if [ -z "$PG_SUPER" ] && [ -f "$REPO_ROOT/.env" ]; then
    PG_SUPER="$(grep '^EVOLUTION_DB_URL=' "$REPO_ROOT/.env" 2>/dev/null | cut -d= -f2- | sed -E 's#/[^/]+$#/postgres#')"
  fi
  local PSQL PGDUMP_CONN=""
  if [ -n "$PG_SUPER" ]; then
    export PGPASSWORD="$(echo "$PG_SUPER" | sed -E 's#.*://[^:]+:([^@]+)@.*#\1#')"
    local h u; h="$(echo "$PG_SUPER" | sed -E 's#.*@([^:/]+).*#\1#')"; u="$(echo "$PG_SUPER" | sed -E 's#.*://([^:]+):.*#\1#')"
    PSQL=(psql -U "$u" -h "$h")
  else
    warn "no superuser URL — using peer auth as postgres OS user"
    PSQL=(sudo -u postgres psql)
  fi
  "${PSQL[@]}" -tAc "SELECT 1" >/dev/null 2>&1 || die "cannot connect to PostgreSQL as superuser"

  # roles/globals first (idempotent; ignore 'already exists')
  if [ -f "$pgdir/_globals.sql.gz" ] && [ -z "$ONLY" ]; then
    log "Restoring roles/globals…"
    gzip -dc "$pgdir/_globals.sql.gz" | "${PSQL[@]}" -v ON_ERROR_STOP=0 >/dev/null 2>&1 || true
    ok "roles restored"
  fi

  for f in "$pgdir"/*.sql.gz; do
    [ -e "$f" ] || continue
    local db; db="$(basename "$f" .sql.gz)"
    [ "$db" = "_globals" ] && continue
    [ -n "$ONLY" ] && [ "$db" != "$ONLY" ] && continue
    log "PostgreSQL: $db"
    "${PSQL[@]}" -tAc "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='$db' AND pid<>pg_backend_pid()" >/dev/null 2>&1 || true
    "${PSQL[@]}" -c "DROP DATABASE IF EXISTS \"$db\"" >/dev/null 2>&1 || true
    # own by a same-named role if it exists (cPanel convention), else default
    if "${PSQL[@]}" -tAc "SELECT 1 FROM pg_roles WHERE rolname='$db'" 2>/dev/null | grep -q 1; then
      "${PSQL[@]}" -c "CREATE DATABASE \"$db\" OWNER \"$db\"" >/dev/null 2>&1 || "${PSQL[@]}" -c "CREATE DATABASE \"$db\"" >/dev/null 2>&1
    else
      "${PSQL[@]}" -c "CREATE DATABASE \"$db\"" >/dev/null 2>&1
    fi
    gzip -dc "$f" | "${PSQL[@]}" -v ON_ERROR_STOP=0 -d "$db" >/dev/null 2>&1
    # make the app role able to use the data
    if "${PSQL[@]}" -tAc "SELECT 1 FROM pg_roles WHERE rolname='$db'" 2>/dev/null | grep -q 1; then
      "${PSQL[@]}" -d "$db" -c "ALTER DATABASE \"$db\" OWNER TO \"$db\"; GRANT ALL ON SCHEMA public TO \"$db\"; GRANT ALL ON ALL TABLES IN SCHEMA public TO \"$db\"; GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO \"$db\"; GRANT ALL ON ALL FUNCTIONS IN SCHEMA public TO \"$db\";" >/dev/null 2>&1 || true
    fi
    ok "  restored $db"
  done
  unset PGPASSWORD
}

# ---------- MySQL ----------
restore_mysql() {
  local mydir="$DIR/databases/mysql"
  [ -d "$mydir" ] || { warn "no MySQL dumps"; return; }
  HOME=/root mysql -N -e "SELECT 1" >/dev/null 2>&1 || die "no MySQL root access (/root/.my.cnf)"
  for f in "$mydir"/*.sql.gz; do
    [ -e "$f" ] || continue
    local db; db="$(basename "$f" .sql.gz)"
    [ -n "$ONLY" ] && [ "$db" != "$ONLY" ] && continue
    log "MySQL: $db"
    HOME=/root mysql -e "CREATE DATABASE IF NOT EXISTS \`$db\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci" 2>/dev/null || true
    gzip -dc "$f" | HOME=/root mysql "$db" 2>/dev/null && ok "  restored $db" || warn "  $db had errors (review)"
  done
}

case "$MODE" in
  pg)    restore_pg ;;
  mysql) restore_mysql ;;
  both)  restore_pg; restore_mysql ;;
esac

echo
ok "Database restore finished."
echo "Reminders:"
echo "  • MySQL users/grants are cPanel-managed — recreate DB users in WHM/cPanel if the apps can't connect."
echo "  • Verify each app's DATABASE_URL / config points at this server."