#!/usr/bin/env bash
#
# cutover-restore-and-rehydrate.sh
#
# Phase 3 cutover helper for the TEST box.
#
# Purpose:
#   1. Restore the paperclip DB from a provided dump source.
#   2. ALWAYS re-apply bridge views from /home/ubuntu/scripts/bridge-views.sql
#      afterward. Bridge views are NOT plugin migrations -- a fresh restore
#      drops them, so they must be re-hydrated every single time.
#   3. Re-verify the 3 bridge views resolve and return rows.
#   4. Re-verify plugins return to "ready".
#
# Safe to re-run (idempotent): the restore drops/recreates objects, the bridge
# views SQL is expected to be CREATE OR REPLACE, and all verification is
# read-only.
#
# Usage:
#   ./cutover-restore-and-rehydrate.sh <db-dump-source>
#
set -euo pipefail

# ---------------------------------------------------------------------------
# Configuration
# ---------------------------------------------------------------------------
PGHOST="/tmp"
PGPORT="54329"
PGUSER="paperclip"
PGDATABASE="paperclip"

BRIDGE_VIEWS_SQL="/home/ubuntu/scripts/bridge-views.sql"

# Expected bridge-view row counts (from last known-good cutover context).
# Used as a sanity floor: views must resolve AND return rows.
EXPECTED_KNOWLEDGE_INTAKE=247
EXPECTED_DEEP_ANALYSIS_ISSUES=186
EXPECTED_SUBSCRIPTIONS_VIEW=1779

# Plugin readiness expectations.
EXPECTED_PLUGINS_READY=11
EXPECTED_PLUGINS_TOTAL=11   # 11 ready of the 11 installed (5 others uninstalled)

# Convenience wrapper so every psql call uses the same connection.
PSQL=(psql -h "${PGHOST}" -p "${PGPORT}" -U "${PGUSER}" -d "${PGDATABASE}" -v ON_ERROR_STOP=1)

# ---------------------------------------------------------------------------
# Helpers
# ---------------------------------------------------------------------------
checkpoint() {
  echo ""
  echo "=== $* ==="
}

fail() {
  echo ""
  echo "FATAL: $*" >&2
  exit 1
}

# Run a scalar query and trim whitespace.
scalar() {
  "${PSQL[@]}" -tA -c "$1"
}

# ---------------------------------------------------------------------------
# Argument handling
# ---------------------------------------------------------------------------
if [[ $# -ne 1 ]]; then
  fail "usage: $0 <db-dump-source>"
fi

DUMP_SOURCE="$1"

if [[ ! -f "${DUMP_SOURCE}" ]]; then
  fail "dump source not found or not a regular file: ${DUMP_SOURCE}"
fi

if [[ ! -r "${DUMP_SOURCE}" ]]; then
  fail "dump source not readable: ${DUMP_SOURCE}"
fi

checkpoint "Phase 3 cutover: restore + rehydrate starting"
echo "Host:        ${PGHOST}"
echo "Port:        ${PGPORT}"
echo "User:        ${PGUSER}"
echo "Database:    ${PGDATABASE}"
echo "Dump source: ${DUMP_SOURCE}"
echo "Bridge SQL:  ${BRIDGE_VIEWS_SQL}"

# ---------------------------------------------------------------------------
# Pre-flight checks
# ---------------------------------------------------------------------------
checkpoint "Pre-flight: verifying connectivity and prerequisites"

if ! "${PSQL[@]}" -tA -c "SELECT 1;" >/dev/null 2>&1; then
  fail "cannot connect to ${PGDATABASE} on ${PGHOST}:${PGPORT} as ${PGUSER}"
fi
echo "OK: database connection succeeded."

if [[ ! -f "${BRIDGE_VIEWS_SQL}" ]]; then
  fail "bridge views SQL not found: ${BRIDGE_VIEWS_SQL} (cannot rehydrate)"
fi
echo "OK: bridge views SQL present at ${BRIDGE_VIEWS_SQL}."

# ---------------------------------------------------------------------------
# Step 1 + 2: Restore the dump
# ---------------------------------------------------------------------------
# Detect dump format. pg_restore handles custom/directory/tar archives; a plain
# SQL dump is fed straight to psql. Both paths are idempotent in the sense that
# they recreate the schema/data from the dump on each run.
checkpoint "Step 1/4: Restoring database from dump"

DUMP_HEADER="$(head -c 5 "${DUMP_SOURCE}" 2>/dev/null || true)"

if [[ "${DUMP_HEADER}" == "PGDMP" ]]; then
  echo "Detected pg_dump custom/archive format (PGDMP header)."
  echo "Restoring with pg_restore (--clean --if-exists --no-owner)..."
  pg_restore \
    -h "${PGHOST}" \
    -p "${PGPORT}" \
    -U "${PGUSER}" \
    -d "${PGDATABASE}" \
    --clean \
    --if-exists \
    --no-owner \
    "${DUMP_SOURCE}"
else
  echo "Detected plain SQL dump (no PGDMP header)."
  echo "Restoring with psql..."
  "${PSQL[@]}" -f "${DUMP_SOURCE}"
fi

echo "OK: restore completed."

# ---------------------------------------------------------------------------
# Step 3: ALWAYS re-apply bridge views
# ---------------------------------------------------------------------------
# Bridge views are NOT plugin migrations; a fresh restore drops them. They must
# be re-created on every run regardless of restore outcome. The SQL is expected
# to use CREATE OR REPLACE so this is safe to re-run.
checkpoint "Step 2/4: Re-applying bridge views (always)"
echo "Applying ${BRIDGE_VIEWS_SQL}..."
"${PSQL[@]}" -f "${BRIDGE_VIEWS_SQL}"
echo "OK: bridge views re-applied."

# ---------------------------------------------------------------------------
# Step 4a: Verify the 3 bridge views resolve and return rows
# ---------------------------------------------------------------------------
checkpoint "Step 3/4: Verifying bridge views resolve and return rows"

verify_view() {
  local view="$1"
  local expected="$2"
  local count

  # First confirm the view resolves (planner can build it). COUNT(*) both
  # resolves the view and returns the row count.
  if ! count="$(scalar "SELECT COUNT(*) FROM ${view};")"; then
    fail "bridge view '${view}' did NOT resolve (query failed)"
  fi

  if ! [[ "${count}" =~ ^[0-9]+$ ]]; then
    fail "bridge view '${view}' returned a non-numeric count: '${count}'"
  fi

  if [[ "${count}" -eq 0 ]]; then
    fail "bridge view '${view}' resolved but returned 0 rows (expected ~${expected})"
  fi

  if [[ "${count}" -ne "${expected}" ]]; then
    echo "WARN: ${view} row count = ${count} (expected ${expected}); resolves and non-empty, continuing."
  else
    echo "OK: ${view} resolves, rows = ${count} (matches expected ${expected})."
  fi
}

verify_view "knowledge_intake"        "${EXPECTED_KNOWLEDGE_INTAKE}"
verify_view "deep_analysis_issues"    "${EXPECTED_DEEP_ANALYSIS_ISSUES}"
verify_view "subscriptions_view"      "${EXPECTED_SUBSCRIPTIONS_VIEW}"

echo "OK: all 3 bridge views resolve and return rows."

# ---------------------------------------------------------------------------
# Step 4b: Verify plugins return to "ready"
# ---------------------------------------------------------------------------
checkpoint "Step 4/4: Verifying plugins return to ready"

# Count plugins by status. We expect EXPECTED_PLUGINS_READY of the installed
# plugins to report 'ready'. Uninstalled rows are excluded from the readiness
# denominator.
READY_COUNT="$(scalar "SELECT COUNT(*) FROM plugins WHERE status = 'ready';")"

if ! [[ "${READY_COUNT}" =~ ^[0-9]+$ ]]; then
  fail "could not read plugin readiness (got: '${READY_COUNT}')"
fi

echo "Plugins reporting 'ready': ${READY_COUNT} (expected ${EXPECTED_PLUGINS_READY})."

# Show a quick breakdown for the operator's log.
echo "Plugin status breakdown:"
"${PSQL[@]}" -c "SELECT status, COUNT(*) AS n FROM plugins GROUP BY status ORDER BY status;"

if [[ "${READY_COUNT}" -lt "${EXPECTED_PLUGINS_READY}" ]]; then
  fail "plugin readiness regressed: ${READY_COUNT} ready < expected ${EXPECTED_PLUGINS_READY}"
fi

if [[ "${READY_COUNT}" -gt "${EXPECTED_PLUGINS_READY}" ]]; then
  echo "WARN: more plugins ready (${READY_COUNT}) than expected (${EXPECTED_PLUGINS_READY}); continuing."
fi

echo "OK: plugins are ready (${READY_COUNT}/${EXPECTED_PLUGINS_TOTAL} installed)."

# ---------------------------------------------------------------------------
# Final verification summary
# ---------------------------------------------------------------------------
checkpoint "FINAL VERIFICATION SUMMARY"

KI="$(scalar "SELECT COUNT(*) FROM knowledge_intake;")"
DAI="$(scalar "SELECT COUNT(*) FROM deep_analysis_issues;")"
SUB="$(scalar "SELECT COUNT(*) FROM subscriptions_view;")"
RDY="$(scalar "SELECT COUNT(*) FROM plugins WHERE status = 'ready';")"
TOT="$(scalar "SELECT COUNT(*) FROM plugins;")"

echo "  bridge view  knowledge_intake      = ${KI}  (expected ${EXPECTED_KNOWLEDGE_INTAKE})"
echo "  bridge view  deep_analysis_issues  = ${DAI}  (expected ${EXPECTED_DEEP_ANALYSIS_ISSUES})"
echo "  bridge view  subscriptions_view    = ${SUB}  (expected ${EXPECTED_SUBSCRIPTIONS_VIEW})"
echo "  plugins ready                       = ${RDY}/${EXPECTED_PLUGINS_READY}  (rows in plugins table: ${TOT})"
echo ""
echo "SUCCESS: restore + bridge-view rehydration complete and verified."
echo "Re-running this script is safe and will reproduce this state."

exit 0