#!/bin/sh
# Emergency restore of a pg_dump backup (docker/backup-db.sh) into the
# production Supabase Postgres DB. NOT wired into crontab - manual/disaster
# recovery use only.
#
# Usage:
#   docker/restore-db.sh                 # list available backups, exit
#   docker/restore-db.sh <dump-file>      # restore that backup (asks to type
#                                          # RESTORE to confirm)
#   FORCE=1 docker/restore-db.sh <file>   # skip the confirmation prompt
#                                          # (only for scripted/non-interactive
#                                          # use - know what you're doing)
#
# <dump-file> can be a bare filename (resolved inside BACKUP_DIR) or a full
# path.
#
# Restores via a throwaway postgres:17-alpine container (matches the
# Supabase server's Postgres 17.6) against DIRECT_URL (the non-pooled
# port-5432 connection - pg_restore against the pgbouncer-fronted
# DATABASE_URL is unreliable), using:
#   --clean --if-exists   drop existing objects before recreating them, so
#                          the restore is idempotent against a live DB
#   --no-owner --no-privileges
#                          skip ALTER OWNER / GRANT statements - avoids
#                          errors if the restoring role doesn't exactly match
#                          the dump's original roles
#   -j 4                   parallel restore (custom format only)
#
# CAVEAT: this restores the WHOLE database dump, including Supabase-internal
# schemas (auth, storage, extensions, ...). This app uses Clerk for identity
# (not Supabase Auth), so `auth` is normally empty/unused here. `storage`
# holds Supabase Storage's *metadata* rows (bucket/object records) - the
# actual file bytes live in Supabase's object store separately and are not
# part of this dump, so a restore can leave storage.objects rows pointing at
# files that were since deleted, or miss rows for files uploaded after the
# backup. Full data consistency after a restore is not guaranteed for
# storage - review before treating the app as fully recovered.
set -eu

SCRIPT_DIR="$(cd "$(dirname "$0")" && pwd)"
ENV_FILE="$SCRIPT_DIR/.env"
LOG_FILE="$SCRIPT_DIR/restore.log"
BACKUP_DIR="/srv/dev-disk-by-uuid-ad295d7f-a870-4f70-9de2-dfded1fabf7f/BACKUPALL/pawfeed"
PG_IMAGE="postgres:17-alpine"

log() {
  printf '%s [restore-db] %s\n' "$(date -Iseconds)" "$1" >> "$LOG_FILE"
}

list_backups() {
  echo "Available backups in $BACKUP_DIR:"
  ls -lh "$BACKUP_DIR"/pawfeed-*.dump 2>/dev/null | awk '{print "  " $NF " (" $5 ")"}' \
    || echo "  (none found)"
  echo
  echo "Usage: $0 <dump-file>"
}

if [ $# -eq 0 ]; then
  list_backups
  exit 0
fi

DUMP_ARG="$1"
if [ -f "$DUMP_ARG" ]; then
  DUMP_PATH="$DUMP_ARG"
elif [ -f "$BACKUP_DIR/$DUMP_ARG" ]; then
  DUMP_PATH="$BACKUP_DIR/$DUMP_ARG"
else
  echo "ERROR: backup file not found: $DUMP_ARG" >&2
  echo >&2
  list_backups
  exit 1
fi

DUMP_DIR="$(cd "$(dirname "$DUMP_PATH")" && pwd)"
DUMP_NAME="$(basename "$DUMP_PATH")"

if [ ! -f "$ENV_FILE" ]; then
  echo "ERROR: $ENV_FILE not found" >&2
  exit 1
fi

DIRECT_URL="$(grep -E '^DIRECT_URL=' "$ENV_FILE" | head -n1 | cut -d= -f2- | sed -e 's/^"//' -e 's/"$//')"
if [ -z "$DIRECT_URL" ]; then
  echo "ERROR: DIRECT_URL not set in $ENV_FILE" >&2
  exit 1
fi

TARGET_HOST="$(printf '%s' "$DIRECT_URL" | sed -E 's#^postgresql://[^@]+@([^/]+)/.*#\1#')"

echo "!!! This will DROP AND RECREATE objects in the LIVE database !!!"
echo "  Backup file: $DUMP_PATH"
echo "  Target:      $TARGET_HOST (from DIRECT_URL in $ENV_FILE)"
echo

if [ "${FORCE:-}" != "1" ]; then
  printf 'Type RESTORE to continue: '
  read -r CONFIRM
  if [ "$CONFIRM" != "RESTORE" ]; then
    echo "Aborted (no changes made)."
    exit 1
  fi
fi

log "starting restore of $DUMP_NAME onto $TARGET_HOST"

OUT_FILE="$(mktemp)"
trap 'rm -f "$OUT_FILE"' EXIT

# Captured to a temp file (not piped straight to `tee`) so the exit status
# checked below is docker run's own, not a pipeline's last stage.
set +e
docker run --rm \
  -e DB_URL="$DIRECT_URL" \
  -e FILE_NAME="$DUMP_NAME" \
  -v "$DUMP_DIR:/backup:ro" \
  "$PG_IMAGE" \
  sh -c 'pg_restore --clean --if-exists --no-owner --no-privileges -j 4 -d "$DB_URL" "/backup/$FILE_NAME"' \
  > "$OUT_FILE" 2>&1
STATUS=$?
set -e

cat "$OUT_FILE"
cat "$OUT_FILE" >> "$LOG_FILE"

if [ "$STATUS" -eq 0 ]; then
  log "OK: restored $DUMP_NAME onto $TARGET_HOST"
  echo
  echo "Restore finished. Spot-check the app before trusting it's fully recovered"
  echo "(see the storage-metadata caveat at the top of this script)."
else
  log "ERROR: restore of $DUMP_NAME failed (exit $STATUS), see output above"
  exit 1
fi