#!/usr/bin/env bash
# CancerIndex — restore a pg_dump custom archive into a NEW database (CLAUDE.md §172 "test restore").
#
#   bash deploy/restore.sh <dump-file> [--target <dbname>] [--jobs N]
#
# Creates `cancerindex_restore_<YYYYMMDD-HHMMSS>` (or --target) on the same server as PG_URL /
# DATABASE_URL, restores into it and prints row counts of the core tables. It NEVER touches the
# production database: promoting a restore is a deliberate manual step (rename databases, or point
# DATABASE_URL at the restored one, after stopping the PM2 processes).
set -uo pipefail

DUMP="${1:-}"; shift || true
[ -n "$DUMP" ] && [ -f "$DUMP" ] || { echo "usage: $0 <dump-file> [--target dbname] [--jobs N]" >&2; exit 2; }
TARGET=""; JOBS="${RESTORE_JOBS:-4}"
while [ $# -gt 0 ]; do
  case "$1" in
    --target) TARGET="$2"; shift 2 ;;
    --jobs) JOBS="$2"; shift 2 ;;
    *) echo "unknown option $1" >&2; exit 2 ;;
  esac
done

SRC_URL="${DATABASE_URL:-postgres://localhost:5432/cancerindex}"
# Server URL without the database name (…/dbname → …/), used for createdb + the target connection.
SERVER_URL="$(printf '%s' "$SRC_URL" | sed -E 's#^(postgres(ql)?://[^/]*)/[^?]*#\1/#')"
TARGET="${TARGET:-cancerindex_restore_$(date +%Y%m%d-%H%M%S)}"
case "$TARGET" in cancerindex|postgres|template0|template1) echo "refusing to restore into '$TARGET'" >&2; exit 2 ;; esac
TARGET_URL="${SERVER_URL}${TARGET}"

command -v pg_restore >/dev/null || { echo "pg_restore not found" >&2; exit 1; }
pg_restore --list "$DUMP" >/dev/null || { echo "not a readable pg_dump archive: $DUMP" >&2; exit 1; }

echo "== restoring $(basename "$DUMP") → $TARGET (server ${SERVER_URL%/})"
if psql -d "${SERVER_URL}postgres" -Atc "SELECT 1 FROM pg_database WHERE datname = '$TARGET'" | grep -q 1; then
  echo "database $TARGET already exists — choose another --target" >&2; exit 1
fi
psql -d "${SERVER_URL}postgres" -v ON_ERROR_STOP=1 -qc "CREATE DATABASE \"$TARGET\"" || exit 1
psql -d "$TARGET_URL" -v ON_ERROR_STOP=1 -qc "CREATE EXTENSION IF NOT EXISTS pg_trgm; CREATE EXTENSION IF NOT EXISTS unaccent;" || exit 1
psql -d "$TARGET_URL" -qc "CREATE EXTENSION IF NOT EXISTS vector" 2>/dev/null || echo "note: pgvector not available — vector columns will fail to restore if present"

t0=$(date +%s)
# --no-owner/--no-acl: the dump was taken that way; -j parallel restore for large tables.
if ! pg_restore --no-owner --no-acl --exit-on-error -j "$JOBS" -d "$TARGET_URL" "$DUMP"; then
  echo "pg_restore FAILED — database $TARGET left in place for inspection (drop it with: dropdb $TARGET)" >&2
  exit 1
fi
echo "restored in $(( $(date +%s) - t0 )) s"
echo "== row counts ($TARGET)"
psql -d "$TARGET_URL" -Atc "
  SELECT rpad(t, 28) || count FROM (
    SELECT 'cancers' AS t, count(*)::text AS count FROM cancers UNION ALL
    SELECT 'provenance', count(*)::text FROM provenance UNION ALL
    SELECT 'source_records', count(*)::text FROM source_records UNION ALL
    SELECT 'ingest_runs', count(*)::text FROM ingest_runs UNION ALL
    SELECT 'clinical_trials', count(*)::text FROM clinical_trials UNION ALL
    SELECT 'rankings', count(*)::text FROM rankings
  ) x"
echo "== done. Inspect with: psql $TARGET_URL"
echo "   To promote: stop PM2 processes, then rename databases (ALTER DATABASE … RENAME TO …) or point DATABASE_URL at $TARGET."
echo "   To discard: dropdb $TARGET"