#!/bin/bash
# =============================================================================
# Apply pending schema migrations. Production entry point.
#
# Shipped in muli-neo-database and installed to /usr/local/muli/bin. Unlike the
# development wrapper (src/database/migrate.sh, which drives dbmate), this uses
# nothing but psql — the documented fallback in schema-migrations.md §11.
#
# It reads exactly the same files and writes exactly the same schema_migrations
# ledger as dbmate, so the two are interchangeable: a database migrated here can
# be inspected with dbmate in the dev container and vice versa.
#
# Production applies migrations. It never creates them.
#
#   muli-db-migrate                     apply to every database found
#   muli-db-migrate <database> <port>   apply to one database
#   muli-db-migrate --status            report pending counts, change nothing
#
# Exit codes: 0 all applied (or nothing pending), 1 a migration failed.
# =============================================================================
set -euo pipefail

DB_ROOT="${MULI_DB_ROOT:-/usr/local/muli/database}"
PG_VERSION="${MULI_PG_VERSION:-14}"
PG_CLUSTER_ROOT="${MULI_PG_CLUSTER_ROOT:-/usr/local/mulipg/${PG_VERSION}}"
SUPERUSER="${MULI_DB_SUPERUSER:-postgres}"
STATUS_ONLY=false
LOCK_ID=8090411   # arbitrary but fixed; serialises concurrent runners

log()  { echo "[muli-db-migrate] $*"; }
warn() { echo "[muli-db-migrate] WARNING: $*" >&2; }
die()  { echo "[muli-db-migrate] ERROR: $*" >&2; exit 1; }

[ -d "$DB_ROOT" ] || die "migration tree not found at ${DB_ROOT}"

# --- tier and port derivation ------------------------------------------------
# Mirrors the legacy scheme: login_db on 59999, cdb<contract> on 60000, and a
# contract database on 50000 + (first digit)(digits 3-5)(letter index).
tier_of() {
    case "$1" in
        login_db)  echo login ;;
        cdb*)      echo common ;;
        [0-9][0-9][0-9][0-9][0-9][A-I]) echo contract ;;
        *)         echo "" ;;
    esac
}

port_of() {
    local db="$1" contract letter idx
    case "$(tier_of "$db")" in
        login)  echo 59999; return ;;
        common) echo 60000; return ;;
    esac
    contract="${db:0:5}"
    letter="${db:5:1}"
    idx=$(awk -v c="$letter" 'BEGIN{print index("ABCDEFGHI", toupper(c))}')
    [ "$idx" -gt 0 ] || return 1
    echo $(( 50000 + 10#"${contract:0:1}${contract:2:3}${idx}" ))
}

# --- discovery ---------------------------------------------------------------
# By PostgreSQL cluster directory, the same way run_muli_updates.sh finds
# databases. contract_list is a display-name source, not a reliable inventory.
discover_databases() {
    [ -d "$PG_CLUSTER_ROOT" ] || { warn "no clusters under ${PG_CLUSTER_ROOT}"; return 0; }
    local d name
    for d in "${PG_CLUSTER_ROOT}"/*pgsql; do
        [ -d "$d" ] || continue
        name=$(basename "$d"); name="${name%pgsql}"
        [ -n "$(tier_of "$name")" ] || continue
        echo "$name"
    done
}

# --- the applier -------------------------------------------------------------
psql_q() { psql -v ON_ERROR_STOP=1 -qtA -U "$SUPERUSER" -p "$2" -d "$1"; }

applied_versions() { # applied_versions <db> <port>
    psql -tAq -U "$SUPERUSER" -p "$2" -d "$1" \
        -c "SELECT version FROM schema_migrations" 2>/dev/null || true
}

apply_one() { # apply_one <db> <port> <tier>
    local db="$1" port="$2" tier="$3"
    local dir="${DB_ROOT}/${tier}/migrations"
    [ -d "$dir" ] || { warn "no migrations for tier ${tier}"; return 0; }

    # The ledger is created by the superuser but read by the application, which
    # connects as muli - startm compares max(version) against the version the
    # package ships. Without this grant every login is refused with
    # "permission denied for table schema_migrations".
    psql -v ON_ERROR_STOP=1 -q -U "$SUPERUSER" -p "$port" -d "$db" \
        -c "CREATE TABLE IF NOT EXISTS schema_migrations (version varchar(255) PRIMARY KEY);
            GRANT SELECT ON schema_migrations TO muli;" \
        || die "${db}: cannot reach database or create ledger"

    # --- adoption (schema-migrations.md §7.2) --------------------------------
    # A database the legacy scripts built has no ledger. Without this, the
    # baseline would look pending and we would try to recreate the entire
    # schema over live data. If the ledger is empty but the database already
    # has tables, record the baseline as applied without running it.
    local ledger_count table_count baseline
    ledger_count=$(psql -tAq -U "$SUPERUSER" -p "$port" -d "$db" \
        -c "SELECT count(*) FROM schema_migrations")
    if [ "$ledger_count" -eq 0 ]; then
        table_count=$(psql -tAq -U "$SUPERUSER" -p "$port" -d "$db" -c \
            "SELECT count(*) FROM pg_class c JOIN pg_namespace n ON n.oid=c.relnamespace
              WHERE n.nspname='public' AND c.relkind='r' AND c.relname <> 'schema_migrations'")
        if [ "${table_count:-0}" -gt 0 ]; then
            baseline=$(basename "$(ls "${dir}"/*.sql 2>/dev/null | head -1)")
            baseline="${baseline%%_*}"
            if [ -n "$baseline" ]; then
                if [ "$STATUS_ONLY" = true ]; then
                    log "${db} (${tier}): would adopt at baseline ${baseline} (${table_count} existing tables)"
                else
                    log "${db}: pre-existing database with ${table_count} tables; adopting at baseline ${baseline}"
                    psql -v ON_ERROR_STOP=1 -q -U "$SUPERUSER" -p "$port" -d "$db" \
                        -c "INSERT INTO schema_migrations (version) VALUES ('${baseline}') ON CONFLICT DO NOTHING"
                fi
            fi
        fi
    fi

    local applied pending=0 failed=0 file version body txn
    applied=$(applied_versions "$db" "$port")

    for file in "$dir"/*.sql; do
        [ -f "$file" ] || continue
        version=$(basename "$file"); version="${version%%_*}"
        printf '%s\n' "$applied" | grep -qx "$version" && continue
        pending=$((pending + 1))

        if [ "$STATUS_ONLY" = true ]; then
            log "  pending: $(basename "$file")"
            continue
        fi

        # Everything between `-- migrate:up` and `-- migrate:down`.
        body=$(awk '/^-- migrate:up/{f=1;next} /^-- migrate:down/{f=0} f' "$file")
        [ -n "${body//[[:space:]]/}" ] || { warn "$(basename "$file") has an empty up section"; }

        # Some statements cannot run inside a transaction (CREATE INDEX
        # CONCURRENTLY); the file opts out on the marker line, as dbmate does.
        txn=true
        grep -qE '^-- migrate:up .*transaction:false' "$file" && txn=false

        log "  applying $(basename "$file")"
        if [ "$txn" = true ]; then
            if ! printf 'BEGIN;\n%s\nINSERT INTO schema_migrations (version) VALUES (%s);\nCOMMIT;\n' \
                    "$body" "$(printf "'%s'" "$version")" \
                 | psql -v ON_ERROR_STOP=1 -q -U "$SUPERUSER" -p "$port" -d "$db"; then
                failed=1; break
            fi
        else
            if ! printf '%s\n' "$body" \
                 | psql -v ON_ERROR_STOP=1 -q -U "$SUPERUSER" -p "$port" -d "$db"; then
                failed=1; break
            fi
            psql -v ON_ERROR_STOP=1 -q -U "$SUPERUSER" -p "$port" -d "$db" \
                 -c "INSERT INTO schema_migrations (version) VALUES ('${version}')"
        fi
    done

    if [ "$failed" -ne 0 ]; then
        die "${db}: migration failed. Earlier migrations in this run remain applied; recover from backup."
    fi
    if [ "$STATUS_ONLY" = true ]; then
        log "${db} (${tier}): ${pending} pending"
    elif [ "$pending" -eq 0 ]; then
        log "${db} (${tier}): up to date"
    else
        log "${db} (${tier}): applied ${pending}"
    fi
}

# --- entry point -------------------------------------------------------------
case "${1:-}" in
    --status) STATUS_ONLY=true; shift ;;
    -h|--help) sed -n '3,22p' "$0" | sed 's/^# \{0,1\}//'; exit 0 ;;
esac

if [ $# -eq 2 ]; then
    TARGETS="$1"; ONE_PORT="$2"
else
    TARGETS=$(discover_databases); ONE_PORT=""
fi

[ -n "$TARGETS" ] || { log "no databases found; nothing to do"; exit 0; }

rc=0
for db in $TARGETS; do
    tier=$(tier_of "$db")
    port="${ONE_PORT:-$(port_of "$db")}" || { warn "cannot derive a port for ${db}, skipping"; continue; }

    # Serialise concurrent runners, as dbmate does. Session-scoped: released
    # when this psql exits, so it only guards the check, not the whole apply.
    if ! psql -tAq -U "$SUPERUSER" -p "$port" -d "$db" \
            -c "SELECT pg_try_advisory_lock(${LOCK_ID})" 2>/dev/null | grep -qx t; then
        warn "${db}: another migration run holds the lock, skipping"
        continue
    fi

    apply_one "$db" "$port" "$tier" || rc=1
done
exit $rc
