#!/usr/bin/env bash
# Restore drill: rebuild a THROWAWAY database from a backup archive, verify
# object counts and the append-only audit trigger, then drop it.
# Never touches bousla_ops.
#
# Usage: bash scripts/restore-drill.sh <file.dump> [drill_db]
# The app user cannot create databases (least privilege by design), so pass
# PGADMIN_USER/PGADMIN_PASSWORD for the create/drop steps (restore + verify
# run as the app user, proving the backup works with production-grade rights).
set -euo pipefail

ROOT="$(cd "$(dirname "$0")/.." && pwd)"
DUMP="${1:?usage: restore-drill.sh <file.dump> [drill_db]}"
DRILL_DB="${2:-bousla_restore_drill}"

# DB settings: explicit env wins; otherwise apps/api/.env defaults
if [ -f "$ROOT/apps/api/.env" ]; then
  while IFS='=' read -r k v; do
    case "$k" in
      PGHOST|PGPORT|PGUSER|PGPASSWORD) export "$k=${!k:-$v}" ;;
    esac
  done < <(grep -E '^(PGHOST|PGPORT|PGUSER|PGPASSWORD)=' "$ROOT/apps/api/.env" || true)
fi
: "${PGHOST:=127.0.0.1}" "${PGPORT:=5433}" "${PGUSER:=bousla_app}"
export PGHOST PGPORT
APP_USER="$PGUSER"
APP_PASSWORD="$PGPASSWORD"

: "${PGADMIN_USER:?set PGADMIN_USER (the cluster superuser; dev: postgres + .pgdata/superuser_password.txt)}"
export PGUSER="$PGADMIN_USER" PGPASSWORD="$PGADMIN_PASSWORD"

echo "drill: recreating $DRILL_DB"
psql -d postgres -v ON_ERROR_STOP=1 -c "DROP DATABASE IF EXISTS $DRILL_DB;"
psql -d postgres -v ON_ERROR_STOP=1 -c "CREATE DATABASE $DRILL_DB OWNER $APP_USER;"

echo "drill: restoring $DUMP as the app user"
export PGUSER="$APP_USER" PGPASSWORD="$APP_PASSWORD"
pg_restore -d "$DRILL_DB" --no-owner --no-privileges "$DUMP"

MEMBERS=$(psql -d "$DRILL_DB" -tAc "SELECT count(*) FROM members")
AUDIT=$(psql -d "$DRILL_DB" -tAc "SELECT count(*) FROM audit_logs")
MIGRATIONS=$(psql -d "$DRILL_DB" -tAc "SELECT count(*) FROM schema_migrations")
echo "drill: members=$MEMBERS audit_rows=$AUDIT migrations=$MIGRATIONS"

# Integrity: the append-only audit trigger must survive backup/restore
if psql -d "$DRILL_DB" -tAc "UPDATE audit_logs SET action = action WHERE id = (SELECT min(id) FROM audit_logs)" >/dev/null 2>&1; then
  echo "drill FAILED: audit_logs UPDATE allowed after restore (append-only trigger missing)" >&2
  exit 1
fi

echo "drill PASSED: append-only audit trigger intact; dropping $DRILL_DB"
export PGUSER="$PGADMIN_USER" PGPASSWORD="$PGADMIN_PASSWORD"
psql -d postgres -c "DROP DATABASE IF EXISTS $DRILL_DB;"
echo "restore drill complete — this backup is proven restorable"