#!/usr/bin/env bash
# scripts/prod-psql — psql into the LIVE precis_prod DB via a cluster node.
#
# scripts/db is LOCAL only (precis / precis_test). Prod sits behind pgbouncer
# on the cluster; reach it by hopping through a node (caspar/melchior) as
# agent_rw. Wraps the ssh-agent workaround (-o IdentityAgent=none) and the
# fixed pgbouncer coordinates so you stop retyping them.
#
# ⚠ agent_rw is WRITE-capable and this is PRODUCTION. Prefer read-only SELECTs.
#
# Usage:
#   scripts/prod-psql "SELECT count(*) FROM refs;"    # one-shot query
#   scripts/prod-psql "\d refs"                        # psql backslash cmds too
#   echo "SELECT ..." | scripts/prod-psql              # piped SQL (stdin)
#   scripts/prod-psql                                  # interactive shell
#
#   scripts/prod-psql --ro "SELECT ..."               # read-only: agent_ro role,
#                                                      # wrapped in BEGIN READ ONLY
#
# ⚠ Never `SET` a session setting here (`SET default_transaction_read_only`,
# `SET SESSION …`, `RESET …`). pgbouncer runs transaction pooling: a
# session-level SET stays on the SHARED server connection and every later
# client inherits it — on 2026-10-02 one such "read-only guard" made every
# agent_rw write fleet-wide fail until pgbouncer was reconnected. This script
# refuses those statements. For a read-only guard use --ro; inside your own
# transaction, `SET LOCAL` is fine (scripts/guide-web's comment says the same).
#
# Overrides (env):
#   PRECIS_PROD_SSH_HOST=melchior     # default caspar
#   PRECIS_PROD_PSQL_OPTS="-At"       # extra psql flags (e.g. terse -At, -x)
set -euo pipefail

RO=0
if [[ "${1:-}" == "--ro" ]]; then
    RO=1
    shift
fi

HOST="${PRECIS_PROD_SSH_HOST:-caspar}"
# Resolved from the gitignored cluster overlay — the address must not live in
# this public repo (see scripts/lib/pgb-host.sh).
. "$(dirname "$0")/lib/pgb-host.sh"
PGB_HOST="$(resolve_pgb_host)"
PGB_PORT=6432
PGUSER=agent_rw
# --ro: the grants-based read-only role (SELECT-only; enforced per statement
# server-side, so pooling-proof — the same choice scripts/guide-web makes).
if [[ $RO -eq 1 ]]; then
    PGUSER=agent_ro
fi
PGDB=precis_prod
OPTS="${PRECIS_PROD_PSQL_OPTS:-}"

# Fixed connection string — no user input interpolated, so it's quoting-safe.
# -P pager=off and ON_ERROR_STOP make one-shot output clean and fail loudly.
REMOTE="psql -h ${PGB_HOST} -p ${PGB_PORT} -U ${PGUSER} -d ${PGDB} -P pager=off -v ON_ERROR_STOP=1 ${OPTS}"
# -X (skip the remote user's ~/.psqlrc) on the SCRIPTED paths only. That file
# is written for humans — on caspar it sets `\timing on`, `\x auto` and
# unicode `\pset` borders — and every one of those lands on STDOUT, so a
# caller parsing a `-At` scalar gets "1755892345\nTime: 0.4 ms" instead of a
# number. Bit the deploy drain (never once ran, 2026-08-22) and would bite
# scripts/deploy's canary heartbeat check on its first use. The interactive
# branch below deliberately keeps ~/.psqlrc — that's a human at a prompt.
REMOTE_SCRIPTED="psql -X -h ${PGB_HOST} -p ${PGB_PORT} -U ${PGUSER} -d ${PGDB} -P pager=off -v ON_ERROR_STOP=1 ${OPTS}"

# Refuse a session-level SET / RESET (see the ⚠ above). Statement-level, so
# `UPDATE … SET col = …` passes; `SET LOCAL`, `SET TRANSACTION` and
# `SET CONSTRAINTS` are transaction-scoped and pass too.
refuse_session_set() {
    if printf '%s' "$1" | perl -0777 -ne '
        s{--[^\n]*}{}g; s{/\*.*?\*/}{}gs;
        for my $st (split /;/) {
            next unless $st =~ /^\s*(SET|RESET)\b/i;
            next if $st =~ /^\s*SET\s+(LOCAL|TRANSACTION|CONSTRAINTS)\b/i;
            exit 1;
        }
        exit 0;'; then
        return 0
    fi
    echo "prod-psql: refused — a session-level SET/RESET through pgbouncer (transaction pooling) sticks to the shared server connection and poisons every later client. Use --ro for a read-only guard, or SET LOCAL inside BEGIN … COMMIT." >&2
    exit 2
}

# --ro wraps the input in one read-only transaction (belt to agent_ro's
# braces). A COMMIT inside the input would end it early; that is the
# caller's business, the role still refuses writes. An unterminated user
# statement would swallow the COMMIT ("syntax error at or near COMMIT"), so
# a missing trailing `;` is appended on its own line (safe after a trailing
# `-- comment`, and an empty statement is a no-op in psql).
wrap_ro() {
    if [[ $RO -eq 1 ]]; then
        local body="$1" trimmed
        trimmed="${body%"${body##*[![:space:]]}"}"
        if [[ "$trimmed" != *\; ]]; then
            body+=$'\n;'
        fi
        printf 'BEGIN READ ONLY;\n%s\nCOMMIT;\n' "$body"
    else
        printf '%s\n' "$1"
    fi
}

if [[ $# -gt 0 ]]; then
    # One-shot: the SQL goes over ssh via psql's STDIN, never interpolated into
    # the remote command line — so any quotes/`$`/`;` in the SQL are safe.
    SQL="$*"
    refuse_session_set "$SQL"
    wrap_ro "$SQL" | ssh -o IdentityAgent=none "$HOST" "$REMOTE_SCRIPTED"
elif [[ ! -t 0 ]]; then
    # Piped SQL on stdin — read it whole so it can be checked first.
    SQL="$(cat)"
    refuse_session_set "$SQL"
    wrap_ro "$SQL" | ssh -o IdentityAgent=none "$HOST" "$REMOTE_SCRIPTED"
else
    # Interactive: nothing to pre-check; the ⚠ above applies to what you type.
    # With --ro the session is agent_ro, which cannot write at all.
    # No args, interactive terminal → interactive psql (allocate a tty).
    exec ssh -t -o IdentityAgent=none "$HOST" "$REMOTE"
fi
