#!/usr/bin/env bash
set -euo pipefail

SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
DEFAULT_ENV_FILE="${SCRIPT_DIR}/.env"

ENV_FILE="${ENV_FILE:-$DEFAULT_ENV_FILE}"

CLI_DB=""
CLI_OVERWRITE=""
CLI_RESTORE_ROLES=""
CLI_REQUEST_ID=""
NON_INTERACTIVE="${NON_INTERACTIVE:-no}"
JSON_ONLY="${JSON_ONLY:-no}"

while [[ $# -gt 0 ]]; do
  case "$1" in
    --env-file)
      [[ $# -ge 2 ]] || { echo "Argument manquant pour --env-file" >&2; exit 1; }
      ENV_FILE="$2"
      shift 2
      ;;
    --db)
      [[ $# -ge 2 ]] || { echo "Argument manquant pour --db" >&2; exit 1; }
      CLI_DB="$2"
      shift 2
      ;;
    --overwrite)
      [[ $# -ge 2 ]] || { echo "Argument manquant pour --overwrite" >&2; exit 1; }
      CLI_OVERWRITE="$2"
      shift 2
      ;;
    --restore-roles)
      [[ $# -ge 2 ]] || { echo "Argument manquant pour --restore-roles" >&2; exit 1; }
      CLI_RESTORE_ROLES="$2"
      shift 2
      ;;
    --request-id)
      [[ $# -ge 2 ]] || { echo "Argument manquant pour --request-id" >&2; exit 1; }
      CLI_REQUEST_ID="$2"
      shift 2
      ;;
    --non-interactive)
      NON_INTERACTIVE="yes"
      shift
      ;;
    --json-only)
      JSON_ONLY="yes"
      shift
      ;;
    *)
      echo "Argument inconnu : $1" >&2
      exit 1
      ;;
  esac
done

json_escape() {
  python3 - <<'PY' "$1"
import json, sys
print(json.dumps(sys.argv[1]))
PY
}

print_json_and_exit() {
  local status="$1"
  local message="$2"
  local exit_code="$3"

  printf '{'
  printf '"status":%s,' "$(json_escape "$status")"
  printf '"message":%s,' "$(json_escape "$message")"
  printf '"request_id":%s,' "$(json_escape "${REQUEST_ID:-}")"
  printf '"environment":%s,' "$(json_escape "${ENV_NAME:-}")"
  printf '"database":%s,' "$(json_escape "${DB:-}")"
  printf '"dump_file":%s,' "$(json_escape "${LAST_REMOTE_DB_DUMP:-}")"
  printf '"log_file":%s' "$(json_escape "${LOG_FILE:-}")"
  printf '}\n'
  exit "$exit_code"
}

print_stdout() {
  [[ "$JSON_ONLY" == "yes" ]] || echo "$*"
}

LOG_FILE=/dev/stderr

log() {
  local msg="[$(date '+%Y-%m-%d %H:%M:%S')] $*"
  echo "$msg" >>"$LOG_FILE"
  print_stdout "$msg"
}

fail() {
  log "ERROR: $*"
  print_json_and_exit "error" "$*" 1
}

require_cmd() {
  command -v "$1" >/dev/null 2>&1 || fail "commande requise absente : $1"
}

has_cmd() {
  command -v "$1" >/dev/null 2>&1
}

download_remote_file() {
  local remote_path="$1"
  local local_path="$2"
  local local_dir

  local_dir="$(dirname "$local_path")"
  mkdir -p "$local_dir" || fail "impossible de créer le dossier local de restauration : $local_dir"

  if scp "${SCP_OPTS[@]}" "${REMOTE_SSH}:${remote_path}" "$local_path" >>"$LOG_FILE" 2>&1; then
    return 0
  fi

  log "Téléchargement scp standard échoué, tentative avec scp -O"
  scp -O "${SCP_OPTS[@]}" "${REMOTE_SSH}:${remote_path}" "$local_path" >>"$LOG_FILE" 2>&1
}

to_bool_yes_no() {
  local v="${1:-}"
  v="${v,,}"
  case "$v" in
    yes|y|oui|o|true|1) echo "yes" ;;
    # Valeur vide traitée comme "no" pour conserver le comportement historique.
    no|n|non|false|0|"") echo "no" ;;
    *) return 1 ;;
  esac
}

is_tty() {
  [[ -t 0 && -t 1 ]]
}

sql_escape_literal() {
  local s="${1:-}"
  s="${s//\'/\'\'}"
  printf "%s" "$s"
}

validate_db_name() {
  local db_name="${1:-}"

  [[ -n "$db_name" ]] || return 1
  [[ "$db_name" =~ ^[a-zA-Z0-9_]+$ ]] || return 1
}

build_excluded_roles_regex() {
  local roles_string="${1:-}"
  local role
  local -a escaped_roles=()

  read -r -a roles_array <<< "$roles_string"

  for role in "${roles_array[@]}"; do
    [[ -n "$role" ]] || continue
    [[ "$role" =~ ^[a-zA-Z0-9_][a-zA-Z0-9_-]*$ ]] || continue
    escaped_roles+=("$role")
  done

  if [[ "${#escaped_roles[@]}" -eq 0 ]]; then
    return 1
  fi

  local joined=""
  local first="yes"
  for role in "${escaped_roles[@]}"; do
    if [[ "$first" == "yes" ]]; then
      joined="$role"
      first="no"
    else
      joined+="|$role"
    fi
  done

  printf '%s' "$joined"
}

send_discord() {
  local message="$1"
  local payload=""

  [[ -n "$DISCORD_WEBHOOK_URL" ]] || return 0
  has_cmd jq || return 0
  has_cmd curl || return 0

  payload="$(jq -n --arg content "$message" '{content: $content}')" || return 0

  curl -fsS "$DISCORD_WEBHOOK_URL" \
    -H "Content-Type: application/json" \
    -d "$payload" \
    >/dev/null || true
}

cleanup() {
  rm -f \
    "${LOCAL_DB_DUMP_FILE:-}" \
    "${LOCAL_ROLES_FILE:-}" \
    "${FILTERED_ROLES_FILE:-}" \
    "${ROLES_CREATE_LIST:-}" \
    "${ROLES_APPLY_FILE:-}"
}
trap cleanup EXIT

[[ -f "$ENV_FILE" ]] || {
  echo '{"status":"error","message":"fichier .env cible introuvable"}'
  exit 1
}

set -a
# shellcheck disable=SC1090
source "$ENV_FILE"
set +a

: "${ENV_NAME:?Variable ENV_NAME manquante}"
: "${PGHOST:?Variable PGHOST manquante}"
: "${PGPORT:?Variable PGPORT manquante}"
: "${PGUSER:?Variable PGUSER manquante}"
: "${PGPASSWORD:?Variable PGPASSWORD manquante}"
: "${DBS:?Variable DBS manquante}"
: "${BACKUP_REMOTE_USER:?Variable BACKUP_REMOTE_USER manquante}"
: "${BACKUP_REMOTE_HOST:?Variable BACKUP_REMOTE_HOST manquante}"
: "${BACKUP_REMOTE_DIR:?Variable BACKUP_REMOTE_DIR manquante}"
: "${SSH_KEY:?Variable SSH_KEY manquante}"
: "${BACKUP_LOG_DIR:?Variable BACKUP_LOG_DIR manquante}"

LOCAL_RESTORE_BASE_DIR="${LOCAL_RESTORE_BASE_DIR:-${SCRIPT_DIR}/restore_tmp}"
REMOTE_ROLES_DIR_NAME="${REMOTE_ROLES_DIR_NAME:-user}"
SSH_CONNECT_TIMEOUT="${SSH_CONNECT_TIMEOUT:-8}"
BACKUP_REMOTE_SSH_PORT="${BACKUP_REMOTE_SSH_PORT:-22}"
DISCORD_WEBHOOK_URL="${DISCORD_WEBHOOK_URL:-}"
EXCLUDED_RESTORE_ROLES="${EXCLUDED_RESTORE_ROLES:-postgres}"

REQUEST_ID="${CLI_REQUEST_ID:-${REQUEST_ID:-}}"
REQUESTED_DB="${CLI_DB:-${REQUESTED_DB:-}}"
ALLOW_OVERWRITE_RAW="${CLI_OVERWRITE:-${ALLOW_OVERWRITE:-no}}"
RESTORE_ROLES_RAW="${CLI_RESTORE_ROLES:-${RESTORE_ROLES:-yes}}"

ALLOW_OVERWRITE="$(to_bool_yes_no "$ALLOW_OVERWRITE_RAW")" || {
  echo '{"status":"error","message":"ALLOW_OVERWRITE invalide"}'
  exit 1
}

RESTORE_ROLES="$(to_bool_yes_no "$RESTORE_ROLES_RAW")" || {
  echo '{"status":"error","message":"RESTORE_ROLES invalide"}'
  exit 1
}

[[ "$PGPORT" =~ ^[0-9]+$ ]] || fail "PGPORT invalide"
[[ "$ENV_NAME" =~ ^[a-zA-Z0-9_-]+$ ]] || fail "ENV_NAME invalide"
[[ "$BACKUP_REMOTE_SSH_PORT" =~ ^[0-9]+$ ]] || fail "BACKUP_REMOTE_SSH_PORT invalide"

mkdir -p "$BACKUP_LOG_DIR" || {
  echo '{"status":"error","message":"impossible de créer le dossier de logs"}'
  exit 1
}

TIMESTAMP="$(date '+%Y-%m-%d_%H-%M-%S')"
SAFE_REQUEST_ID="${REQUEST_ID:-manual}"
SAFE_REQUEST_ID="${SAFE_REQUEST_ID//[^a-zA-Z0-9_.-]/_}"

LOG_FILE="${BACKUP_LOG_DIR}/restore_${ENV_NAME,,}_${SAFE_REQUEST_ID}_${TIMESTAMP}.log"
touch "$LOG_FILE" || {
  echo '{"status":"error","message":"impossible de créer le log"}'
  exit 1
}

LOCAL_RESTORE_DIR="${LOCAL_RESTORE_BASE_DIR}/${SAFE_REQUEST_ID}_${TIMESTAMP}"
mkdir -p "$LOCAL_RESTORE_DIR" || fail "impossible de créer le dossier temporaire local"

EXCLUDED_ROLES_REGEX=""
if EXCLUDED_ROLES_REGEX="$(build_excluded_roles_regex "$EXCLUDED_RESTORE_ROLES")"; then
  log "Rôles exclus de la restauration : $EXCLUDED_RESTORE_ROLES"
else
  log "Aucun rôle exclu de la restauration."
fi

for cmd in ssh scp psql pg_restore createdb dropdb python3 grep sed find basename curl; do
  require_cmd "$cmd"
done

CHECK_SCRIPT="${SCRIPT_DIR}/Checkup/check-postgresql.sh"
if [[ -x "$CHECK_SCRIPT" ]]; then
  log "Précheck PostgreSQL déjà effectué par check-target-readiness.sh"
else
  fail "script introuvable ou non exécutable : $CHECK_SCRIPT"
fi

[[ -f "$SSH_KEY" ]] || fail "clé SSH source backup introuvable : $SSH_KEY"
[[ -r "$SSH_KEY" ]] || fail "clé SSH source backup non lisible : $SSH_KEY"
[[ ! -L "$SSH_KEY" ]] || fail "clé SSH source backup ne doit pas être un lien symbolique : $SSH_KEY"

export PGPASSWORD

SSH_OPTS=(
  -i "$SSH_KEY"
  -p "$BACKUP_REMOTE_SSH_PORT"
  -o IdentitiesOnly=yes
  -o BatchMode=yes
  -o ConnectTimeout="$SSH_CONNECT_TIMEOUT"
  -o StrictHostKeyChecking=yes
)

SCP_OPTS=(
  -i "$SSH_KEY"
  -P "$BACKUP_REMOTE_SSH_PORT"
  -o IdentitiesOnly=yes
  -o BatchMode=yes
  -o ConnectTimeout="$SSH_CONNECT_TIMEOUT"
  -o StrictHostKeyChecking=yes
)

REMOTE_SSH="${BACKUP_REMOTE_USER}@${BACKUP_REMOTE_HOST}"

read -r -a DBS_ARRAY <<< "$DBS"
[[ "${#DBS_ARRAY[@]}" -gt 0 ]] || fail "aucune base définie dans DBS"

if [[ -z "$REQUESTED_DB" ]]; then
  if [[ "$NON_INTERACTIVE" == "yes" ]]; then
    fail "REQUESTED_DB manquante en mode non interactif"
  fi

  if is_tty; then
    print_stdout "Bases disponibles :"
    for i in "${!DBS_ARRAY[@]}"; do
      print_stdout "  $((i + 1))) ${DBS_ARRAY[$i]}"
    done
    echo
    read -r -p "Sélectionnez le numéro de la base à restaurer : " DB_INDEX
    [[ "$DB_INDEX" =~ ^[0-9]+$ ]] || fail "numéro de base invalide"
    (( DB_INDEX >= 1 && DB_INDEX <= ${#DBS_ARRAY[@]} )) || fail "numéro hors plage"
    REQUESTED_DB="${DBS_ARRAY[$((DB_INDEX - 1))]}"
  else
    fail "REQUESTED_DB manquante et aucune interaction terminal disponible"
  fi
fi

DB=""
for candidate in "${DBS_ARRAY[@]}"; do
  if [[ "$candidate" == "$REQUESTED_DB" ]]; then
    DB="$candidate"
    break
  fi
done

[[ -n "$DB" ]] || fail "base refusée : non présente dans DBS"
validate_db_name "$DB" || fail "nom de base invalide"

log "Environnement : $ENV_NAME"
log "Base cible : $DB"
log "Request ID : ${REQUEST_ID:-N/A}"
log "Overwrite : $ALLOW_OVERWRITE"
log "Restore roles : $RESTORE_ROLES"

if ! psql -h "$PGHOST" -p "$PGPORT" -U "$PGUSER" -d postgres -c "SELECT 1;" \
  >>"$LOG_FILE" 2>&1; then
  fail "connexion PostgreSQL locale impossible avec PGUSER=${PGUSER}"
fi

log "Test SSH vers ${REMOTE_SSH}"
if ! ssh "${SSH_OPTS[@]}" "$REMOTE_SSH" "exit 0" >>"$LOG_FILE" 2>&1; then
  fail "connexion SSH impossible vers ${REMOTE_SSH}"
fi

REMOTE_DB_DIR="${BACKUP_REMOTE_DIR}/${DB}"
REMOTE_ROLES_DIR="${BACKUP_REMOTE_DIR}/${REMOTE_ROLES_DIR_NAME}"

LAST_REMOTE_DB_DUMP="$(
  ssh "${SSH_OPTS[@]}" "$REMOTE_SSH" \
    "find '${REMOTE_DB_DIR}' -maxdepth 1 -type f -name '${DB}_*.dump' | LC_ALL=C sort | tail -n 1"
)"

[[ -n "$LAST_REMOTE_DB_DUMP" ]] || fail "aucun dump trouvé pour ${DB} dans ${REMOTE_DB_DIR}"
log "Dernier dump sélectionné : ${LAST_REMOTE_DB_DUMP}"

LAST_REMOTE_ROLES_FILE=""
if [[ "$RESTORE_ROLES" == "yes" ]]; then
  LAST_REMOTE_ROLES_FILE="$(
    ssh "${SSH_OPTS[@]}" "$REMOTE_SSH" \
      "find '${REMOTE_ROLES_DIR}' -maxdepth 1 -type f -name 'user_*.sql' | LC_ALL=C sort | tail -n 1"
  )"
  if [[ -n "$LAST_REMOTE_ROLES_FILE" ]]; then
    log "Dernier fichier rôles sélectionné : ${LAST_REMOTE_ROLES_FILE}"
  else
    log "Aucun fichier rôles trouvé ; la restauration des rôles sera ignorée."
  fi
else
  log "Restauration des rôles désactivée."
fi

LOCAL_DB_DUMP_FILE="${LOCAL_RESTORE_DIR}/$(basename "$LAST_REMOTE_DB_DUMP")"
LOCAL_ROLES_FILE=""

log "Téléchargement du dump principal"
download_remote_file "$LAST_REMOTE_DB_DUMP" "$LOCAL_DB_DUMP_FILE" \
  || fail "échec téléchargement du dump principal"

if [[ -n "$LAST_REMOTE_ROLES_FILE" ]]; then
  LOCAL_ROLES_FILE="${LOCAL_RESTORE_DIR}/$(basename "$LAST_REMOTE_ROLES_FILE")"
  log "Téléchargement du fichier des rôles"
  download_remote_file "$LAST_REMOTE_ROLES_FILE" "$LOCAL_ROLES_FILE" \
    || fail "échec téléchargement du fichier des rôles"
fi

DB_EXISTS="$(
  psql -h "$PGHOST" -p "$PGPORT" -U "$PGUSER" -d postgres -tAc \
    "SELECT 1 FROM pg_database WHERE datname='$(sql_escape_literal "$DB")'" \
    2>>"$LOG_FILE" || true
)"

if [[ "$DB_EXISTS" == "1" ]]; then
  if [[ "$ALLOW_OVERWRITE" != "yes" ]]; then
    if [[ "$NON_INTERACTIVE" == "yes" || ! -t 0 ]]; then
      fail "la base existe déjà et overwrite n'est pas autorisé"
    fi

    read -r -p "La base '${DB}' existe déjà. Voulez-vous l'écraser ? (oui/non) : " CONFIRM_OVERWRITE
    CONFIRM_OVERWRITE="$(to_bool_yes_no "$CONFIRM_OVERWRITE")" || fail "réponse overwrite invalide"
    [[ "$CONFIRM_OVERWRITE" == "yes" ]] || fail "restauration annulée par l'utilisateur"
  fi

  log "Suppression de la base existante : ${DB}"
  dropdb -h "$PGHOST" -p "$PGPORT" -U "$PGUSER" --if-exists "$DB" \
    >>"$LOG_FILE" 2>&1 || fail "échec suppression base ${DB}"
fi

if [[ -n "$LOCAL_ROLES_FILE" ]]; then
  log "Restauration des rôles depuis : ${LOCAL_ROLES_FILE}"

  FILTERED_ROLES_FILE="${LOCAL_RESTORE_DIR}/filtered_$(basename "$LOCAL_ROLES_FILE")"
  ROLES_CREATE_LIST="${LOCAL_RESTORE_DIR}/roles_to_create_$(basename "$LOCAL_ROLES_FILE")"
  ROLES_APPLY_FILE="${LOCAL_RESTORE_DIR}/roles_apply_$(basename "$LOCAL_ROLES_FILE")"

  if [[ -n "$EXCLUDED_ROLES_REGEX" ]]; then
    grep -viE "^(CREATE ROLE|ALTER ROLE) (${EXCLUDED_ROLES_REGEX})\\b" "$LOCAL_ROLES_FILE" \
      > "$FILTERED_ROLES_FILE" || true
  else
    cp "$LOCAL_ROLES_FILE" "$FILTERED_ROLES_FILE"
  fi

  # Une exécution sous un rôle non superuser ne peut pas restaurer l'attribut
  # SUPERUSER ; on ignore donc ces lignes pour laisser passer le reste.
  sed -i -E '/^ALTER ROLE .* (NO)?SUPERUSER\b/d' "$FILTERED_ROLES_FILE"

  log "Fichier des rôles filtré généré : ${FILTERED_ROLES_FILE}"

  sed -nE 's/^CREATE ROLE "?([^" ;]+)"?;$/\1/p' "$FILTERED_ROLES_FILE" \
    > "$ROLES_CREATE_LIST" || true

  if [[ -s "$ROLES_CREATE_LIST" ]]; then
    while IFS= read -r role_name; do
      [[ -n "$role_name" ]] || continue
      [[ "$role_name" =~ ^[a-zA-Z0-9_][a-zA-Z0-9_-]*$ ]] || {
        log "Rôle ignoré car non conforme : ${role_name}"
        continue
      }

      ROLE_EXISTS="$(
        psql -h "$PGHOST" -p "$PGPORT" -U "$PGUSER" -d postgres -tAc \
          "SELECT 1 FROM pg_roles WHERE rolname='$(sql_escape_literal "$role_name")'" \
          2>>"$LOG_FILE" || true
      )"

      if [[ "$ROLE_EXISTS" != "1" ]]; then
        log "Création du rôle manquant : ${role_name}"
        psql -v ON_ERROR_STOP=1 \
          -h "$PGHOST" -p "$PGPORT" -U "$PGUSER" -d postgres \
          -c "CREATE ROLE \"${role_name}\";" \
          >>"$LOG_FILE" 2>&1 || fail "échec création rôle ${role_name}"
      else
        log "Rôle déjà présent : ${role_name}"
      fi
    done < "$ROLES_CREATE_LIST"
  fi

  grep -viE '^CREATE ROLE ' "$FILTERED_ROLES_FILE" > "$ROLES_APPLY_FILE" || true

  log "Application ALTER ROLE / privilèges / memberships"
  psql -v ON_ERROR_STOP=1 \
    -h "$PGHOST" \
    -p "$PGPORT" \
    -U "$PGUSER" \
    -d postgres \
    -f "$ROLES_APPLY_FILE" \
    >>"$LOG_FILE" 2>&1 || fail "échec restauration rôles"
else
  log "Aucune restauration des rôles effectuée."
fi

log "Création de la base : ${DB}"
createdb -h "$PGHOST" -p "$PGPORT" -U "$PGUSER" "$DB" \
  >>"$LOG_FILE" 2>&1 || fail "échec création base ${DB}"

log "Restauration de la base ${DB}"
pg_restore \
  -h "$PGHOST" \
  -p "$PGPORT" \
  -U "$PGUSER" \
  -d "$DB" \
  --clean \
  --if-exists \
  --no-owner \
  --no-privileges \
  "$LOCAL_DB_DUMP_FILE" \
  >>"$LOG_FILE" 2>&1 || fail "échec restauration base ${DB}"

SUCCESS_MESSAGE="✅ REBUILD BDD ${ENV_NAME}
Base restaurée : ${DB}
Hôte PostgreSQL : ${PGHOST}:${PGPORT}
Dump utilisé : $(basename "$LAST_REMOTE_DB_DUMP")
Log : ${LOG_FILE}"

send_discord "$SUCCESS_MESSAGE"

log "Restauration terminée avec succès pour ${DB}"
print_json_and_exit "success" "restauration terminée avec succès" 0