#!/usr/bin/env bash
# Apply prospector SQL migrations to the local DB, tracked by a ledger so each
# file runs exactly once. Idempotent — safe to re-run.
#
#   scripts/migrate.sh          apply pending migrations
#   ./run db:migrate            (preferred entrypoint)
#
# Honors PROSPECTOR_DB_* from .env.local. Override psql with PSQL=/path/to/psql.
set -euo pipefail

. "$(dirname "${BASH_SOURCE[0]}")/lib.sh"
load_env

DB_NAME="${PROSPECTOR_DB_NAME:-prospector}"
MIG_DIR="$REPO_ROOT/migrations"

PSQL_BIN="$(find_psql)"
[[ -n "$PSQL_BIN" ]] || die "psql not found. Install PostgreSQL (brew install postgresql@16) or set PSQL=/path/to/psql."

[[ -d "$MIG_DIR" ]] || die "no migrations directory at $MIG_DIR"

run_sql() { "$PSQL_BIN" -v ON_ERROR_STOP=1 -q -d "$(pg_uri "$DB_NAME")" "$@"; }

# 0. Ensure the target database exists (connect via the maintenance 'postgres' db).
info "ensuring database '$DB_NAME' exists"
if ! "$PSQL_BIN" -tAc "SELECT 1 FROM pg_database WHERE datname='$DB_NAME'" \
       -d "$(pg_uri postgres)" 2>/dev/null | grep -q 1; then
  "$PSQL_BIN" -v ON_ERROR_STOP=1 -d "$(pg_uri postgres)" -c "CREATE DATABASE \"$DB_NAME\"" \
    || die "could not create database '$DB_NAME' (is the Postgres server running and reachable?)"
  ok "created database '$DB_NAME'"
else
  ok "database '$DB_NAME' present"
fi

# 1. Ledger of applied migrations.
run_sql -c "CREATE TABLE IF NOT EXISTS _prospector_migrations (
              filename TEXT PRIMARY KEY,
              applied_at TIMESTAMPTZ NOT NULL DEFAULT now())" >/dev/null

# 2. Apply each *.sql in lexical order, skipping any already in the ledger. Each
#    file + its ledger insert run in one transaction (-1) so a failure rolls back.
applied=0
shopt -s nullglob
for f in "$MIG_DIR"/*.sql; do
  name="$(basename "$f")"
  if run_sql -tAc "SELECT 1 FROM _prospector_migrations WHERE filename='$name'" | grep -q 1; then
    continue
  fi
  info "applying $name"
  run_sql -1 -f "$f" \
    -c "INSERT INTO _prospector_migrations(filename) VALUES ('$name')" \
    || die "migration $name failed"
  applied=$((applied + 1))
done

if [[ "$applied" -eq 0 ]]; then
  ok "database up to date — no pending migrations"
else
  ok "applied $applied migration(s)"
fi