#!/usr/bin/env bash
# deploy/migrate-test.sh — Test migrations against a production data snapshot
#
# USAGE:
#   ./deploy/migrate-test.sh [snapshot.sql.gz]
#
# If no snapshot is given the script creates a fresh pg_dump from the live DB.
#
# WHAT IT DOES:
#   1. Creates a temporary test database (musehub_migrate_test_<timestamp>)
#   2. Restores the snapshot into it
#   3. Runs alembic upgrade head
#   4. Verifies alembic_version == HEAD
#   5. Runs alembic downgrade -1 (one step back)
#   6. Runs alembic upgrade head again (round-trip check)
#   7. Drops the temporary database
#   8. Exits 0 on success, 1 on any failure
#
# Run this BEFORE applying migrations to production.
#
# REQUIREMENTS:
#   - psql and pg_dump/pg_restore in PATH
#   - Environment variables (or .env):
#       DATABASE_URL     postgresql+asyncpg://user:pass@host/dbname
#       PG_ADMIN_URL     postgresql://user:pass@host/postgres   (for CREATE/DROP DB)
#   - Python env with alembic + app deps activated

set -euo pipefail

# ── Config ────────────────────────────────────────────────────────────────────
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
TEST_DB="musehub_migrate_test_${TIMESTAMP}"

# Parse source DB details from DATABASE_URL
SOURCE_URL="${DATABASE_URL:-}"
if [[ -z "$SOURCE_URL" ]]; then
  echo "ERROR: DATABASE_URL must be set." >&2
  exit 1
fi

# Strip asyncpg driver prefix for psql compatibility
SYNC_SOURCE_URL="${SOURCE_URL/+asyncpg/}"
SYNC_SOURCE_URL="${SYNC_SOURCE_URL/+aiosqlite/}"

# Admin URL defaults to same host/user but connects to 'postgres' maintenance DB
PG_ADMIN_URL="${PG_ADMIN_URL:-${SYNC_SOURCE_URL%/*}/postgres}"

SNAPSHOT_FILE="${1:-}"

# ── Helpers ───────────────────────────────────────────────────────────────────
log() { echo "[$(date +%H:%M:%S)] $*"; }
die() { echo "ERROR: $*" >&2; exit 1; }

cleanup() {
  log "Dropping temporary database ${TEST_DB} …"
  psql "$PG_ADMIN_URL" -c "DROP DATABASE IF EXISTS ${TEST_DB};" >/dev/null 2>&1 || true
}
trap cleanup EXIT

# ── Step 1: Create temp DB ────────────────────────────────────────────────────
log "Creating temporary database ${TEST_DB} …"
psql "$PG_ADMIN_URL" -c "CREATE DATABASE ${TEST_DB};" >/dev/null

# Build test DB URL (replace dbname in the source URL)
TEST_URL="${SYNC_SOURCE_URL%/*}/${TEST_DB}"

# ── Step 2: Obtain / restore snapshot ────────────────────────────────────────
if [[ -n "$SNAPSHOT_FILE" ]]; then
  log "Restoring snapshot from ${SNAPSHOT_FILE} …"
  if [[ "$SNAPSHOT_FILE" == *.gz ]]; then
    gunzip -c "$SNAPSHOT_FILE" | psql "$TEST_URL" >/dev/null
  else
    psql "$TEST_URL" < "$SNAPSHOT_FILE" >/dev/null
  fi
  log "Snapshot restored."
else
  log "No snapshot provided — using fresh empty database."
fi

# ── Step 3: Upgrade to HEAD ───────────────────────────────────────────────────
log "Running alembic upgrade head …"
DATABASE_URL="${TEST_URL/postgresql/postgresql+asyncpg}" alembic upgrade head

# ── Step 4: Verify HEAD revision ─────────────────────────────────────────────
ACTUAL_HEAD=$(psql "$TEST_URL" -tAc "SELECT version_num FROM alembic_version LIMIT 1;" 2>/dev/null || echo "")
EXPECTED_HEAD=$(python3 -c "
from alembic.config import Config
from alembic.script import ScriptDirectory
cfg = Config('alembic.ini')
sd = ScriptDirectory.from_config(cfg)
heads = sd.get_heads()
print(heads[0][:4] if heads else '')
")

if [[ "$ACTUAL_HEAD" != *"$EXPECTED_HEAD"* ]]; then
  die "HEAD mismatch after upgrade: DB has '$ACTUAL_HEAD', expected prefix '$EXPECTED_HEAD'"
fi
log "HEAD verified: ${ACTUAL_HEAD} ✓"

# ── Step 5: Downgrade one step ────────────────────────────────────────────────
log "Running alembic downgrade -1 (one step back) …"
DATABASE_URL="${TEST_URL/postgresql/postgresql+asyncpg}" alembic downgrade -1

# ── Step 6: Upgrade again (round-trip) ───────────────────────────────────────
log "Running alembic upgrade head (round-trip) …"
DATABASE_URL="${TEST_URL/postgresql/postgresql+asyncpg}" alembic upgrade head

log "Migration round-trip test passed ✓"
log "Temporary database ${TEST_DB} will be dropped on exit."