#!/usr/bin/env bash
# build_db_at.sh <repo> <commit> <dbname> [last_migration_prefix]
# Recreates <dbname> on the local experiment cluster and applies the migrations
# exactly as they existed at <commit>, each in its own transaction, as the
# schema owner (wayclub_admin). Optionally stops after the migration whose
# filename starts with <last_migration_prefix>.
set -euo pipefail
REPO=$1; COMMIT=$2; DB=$3; LAST=${4:-}
PSQL=${PSQL:-/opt/homebrew/opt/postgresql@17/bin/psql}
PORT=${PGPORT_EXP:-55499}
SU="$PSQL -h localhost -p $PORT -U postgres -v ON_ERROR_STOP=1 -q"
$SU -d postgres -c "drop database if exists \"$DB\" with (force)" -c "create database \"$DB\" owner wayclub_admin"
TMP=$(mktemp -d)
# The project runner (scripts/migrate.mjs) creates its ledger before migration 001;
# migration 008 references it, so create the same table here.
$PSQL -h localhost -p $PORT -U wayclub_admin -d "$DB" -q -c "create table if not exists schema_migrations (filename text primary key, applied_at timestamptz not null default now(), checksum text)"
for f in $(git -C "$REPO" ls-tree --name-only "$COMMIT" infra/postgres/migrations/ | sort); do
  b=$(basename "$f"); [[ $b == *.sql ]] || continue
  git -C "$REPO" show "$COMMIT:$f" > "$TMP/$b"
  $PSQL -h localhost -p $PORT -U wayclub_admin -d "$DB" -v ON_ERROR_STOP=1 -q -1 -f "$TMP/$b" >/dev/null 2>"$TMP/$b.err" || { echo "FAILED $b"; cat "$TMP/$b.err"; exit 1; }
  echo "applied $b"
  [[ -n "$LAST" && $b == $LAST* ]] && break
done
rm -rf "$TMP"