#!/usr/bin/env bash
# Verifies every factual claim in the article "PgBouncer vs Pgpool-II".
# Builds one throwaway PostgreSQL 17 cluster (port 55621), runs PgBouncer (6432) and
# Pgpool-II (55633) against it, proves each claim, and destroys everything it created.
# Touches nothing outside its own scratch directory. Existing databases are never used.
#
# Usage: bash verify-pgbouncer-vs-pgpool-ii.sh
# Requires: PostgreSQL 17 binaries on PATH. pgbouncer and pgpool are each optional:
# a missing binary produces honest SKIPs for that tool's claims, never a fake PASS.

set -u

export PATH="/opt/homebrew/opt/postgresql@17/bin:/opt/homebrew/opt/pgpool-ii/bin:$PATH"

PASS=0; FAIL=0; SKIP=0
pass() { echo "PASS $*"; PASS=$((PASS+1)); }
fail() { echo "FAIL $*"; FAIL=$((FAIL+1)); }
skip() { echo "SKIP $*"; SKIP=$((SKIP+1)); }

PG_PORT=55621; PGB_PORT=6432; PGP_PORT=55633; PCP_PORT=55634
BASE=$(mktemp -d "${TMPDIR:-/tmp}/hw_pool_verify.XXXXXX")
# AF_UNIX socket paths are capped at 103 bytes on macOS; pgpool refuses to run
# without a unix socket, so it gets a deliberately short scratch dir of its own.
SOCKDIR=$(mktemp -d /tmp/hwv_sock.XXXXXX)
U="hwcmp_$RANDOM"
PGB_PID=""; PGP_PID=""

cleanup() {
  [ -n "$PGP_PID" ] && kill "$PGP_PID" >/dev/null 2>&1
  [ -f "$BASE/pgbouncer.pid" ] && kill "$(cat "$BASE/pgbouncer.pid")" >/dev/null 2>&1
  sleep 1
  [ -d "$BASE/pg" ] && pg_ctl -D "$BASE/pg" -m immediate stop >/dev/null 2>&1
  rm -rf "$BASE" "$SOCKDIR"
}
trap cleanup EXIT

for bin in initdb pg_ctl psql createdb; do
  command -v "$bin" >/dev/null 2>&1 || { skip "prerequisite: $bin not on PATH"; echo "PASS=$PASS FAIL=$FAIL SKIP=$SKIP"; exit 0; }
done
for p in $PG_PORT $PGB_PORT $PGP_PORT $PCP_PORT; do
  if lsof -nP -iTCP:$p -sTCP:LISTEN >/dev/null 2>&1; then
    skip "port $p already in use - cannot build isolated services"
    echo "PASS=$PASS FAIL=$FAIL SKIP=$SKIP"; exit 0
  fi
done

q()  { psql -h 127.0.0.1 -p "$1" -U "$U" -X -Atq "$U" -c "$2" 2>&1; }

# ---------- scratch backend ----------
initdb -D "$BASE/pg" -U "$U" --auth=trust >"$BASE/initdb.log" 2>&1 || {
  fail "initdb scratch cluster"; echo "PASS=$PASS FAIL=$FAIL SKIP=$SKIP"; exit 1; }
cat >> "$BASE/pg/postgresql.conf" <<EOF
port = $PG_PORT
listen_addresses = '127.0.0.1'
unix_socket_directories = '$SOCKDIR'
EOF
pg_ctl -D "$BASE/pg" -l "$BASE/pg.log" -w start >/dev/null 2>&1 || {
  fail "start scratch cluster"; echo "PASS=$PASS FAIL=$FAIL SKIP=$SKIP"; exit 1; }
createdb -h 127.0.0.1 -p $PG_PORT -U "$U" "$U" >/dev/null 2>&1

PGVER=$(q $PG_PORT "SHOW server_version;")
case "$PGVER" in
  17.*) pass "backend is PostgreSQL 17.x (got $PGVER)" ;;
  *)    skip "article was measured on 17.11; this server is $PGVER - results may differ" ;;
esac

# ---------- PgBouncer ----------
if ! command -v pgbouncer >/dev/null 2>&1; then
  skip "pgbouncer not installed - skipping all 7 PgBouncer claims (brew install pgbouncer)"
else
  V=$(pgbouncer --version 2>&1 | head -1)
  case "$V" in
    PgBouncer\ 1.*) pass "pgbouncer --version reports 1.x (got: $V)" ;;
    *)              fail "pgbouncer --version (got: $V)" ;;
  esac

  cat > "$BASE/pgbouncer.ini" <<EOF
[databases]
$U = host=127.0.0.1 port=$PG_PORT dbname=$U

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = $PGB_PORT
unix_socket_dir =
auth_type = trust
auth_file = $BASE/userlist.txt
admin_users = $U
pool_mode = transaction
default_pool_size = 2
logfile = $BASE/pgbouncer.log
pidfile = $BASE/pgbouncer.pid
EOF
  echo "\"$U\" \"\"" > "$BASE/userlist.txt"
  pgbouncer -d "$BASE/pgbouncer.ini" >/dev/null 2>&1; sleep 1

  GOT=$(q $PGB_PORT "SELECT current_database() || '|' || inet_server_port();")
  [ "$GOT" = "$U|$PG_PORT" ] \
    && pass "psql -p $PGB_PORT reaches the backend: inet_server_port() = $PG_PORT" \
    || fail "connection through pgbouncer (got: $GOT)"

  # 5 concurrent clients vs default_pool_size = 2
  SLEEPERS=""
  for i in 1 2 3 4 5; do q $PGB_PORT "SELECT pg_sleep(4);" >/dev/null 2>&1 & SLEEPERS="$SLEEPERS $!"; done
  sleep 1.5
  ROW=$(psql -h 127.0.0.1 -p $PGB_PORT -U "$U" -X -Atq pgbouncer -c "SHOW POOLS;" 2>&1 | grep "^$U|")
  CLA=$(echo "$ROW" | cut -d'|' -f3); CLW=$(echo "$ROW" | cut -d'|' -f4); SVA=$(echo "$ROW" | cut -d'|' -f7)
  [ "$CLA" = "2" ] && [ "$CLW" = "3" ] \
    && pass "SHOW POOLS: 5 clients -> cl_active=2, cl_waiting=3" \
    || fail "SHOW POOLS client split (cl_active=$CLA cl_waiting=$CLW, expected 2/3)"
  [ "$SVA" = "2" ] \
    && pass "SHOW POOLS: sv_active=2 (pool cap respected)" \
    || fail "SHOW POOLS sv_active=$SVA, expected 2"
  BK=$(q $PG_PORT "SELECT count(*) FROM pg_stat_activity WHERE datname='$U' AND pid <> pg_backend_pid();")
  [ "$BK" = "2" ] \
    && pass "pg_stat_activity on the backend confirms only 2 server connections for 5 clients" \
    || fail "backend connection count during load (got: $BK, expected 2)"
  wait $SLEEPERS

  # session-state leak in transaction pooling (pool of 1 forces backend sharing)
  kill "$(cat "$BASE/pgbouncer.pid")" >/dev/null 2>&1; sleep 1
  sed -i '' 's/default_pool_size = 2/default_pool_size = 1/' "$BASE/pgbouncer.ini" 2>/dev/null \
    || sed -i 's/default_pool_size = 2/default_pool_size = 1/' "$BASE/pgbouncer.ini"
  pgbouncer -d "$BASE/pgbouncer.ini" >/dev/null 2>&1; sleep 1
  q $PGB_PORT "SET statement_timeout = '5s';" >/dev/null
  LEAK=$(q $PGB_PORT "SHOW statement_timeout;")
  [ "$LEAK" = "5s" ] \
    && pass "transaction pooling leaked SET statement_timeout='5s' to a brand-new client" \
    || fail "expected leaked statement_timeout=5s through pgbouncer (got: $LEAK)"
  DIRECT=$(q $PG_PORT "SHOW statement_timeout;")
  [ "$DIRECT" = "0" ] \
    && pass "a fresh direct backend connection still shows the default (0)" \
    || fail "direct backend statement_timeout (got: $DIRECT, expected 0)"

  NPROC=$(pgrep -x pgbouncer | wc -l | tr -d ' ')
  [ "$NPROC" = "1" ] \
    && pass "pgbouncer runs as exactly 1 OS process" \
    || fail "pgbouncer process count (got: $NPROC, expected 1)"
fi

# ---------- Pgpool-II ----------
if ! command -v pgpool >/dev/null 2>&1; then
  skip "pgpool not installed - skipping all 7 Pgpool-II claims (brew install pgpool-ii)"
else
  V=$(pgpool --version 2>&1 | head -1)
  case "$V" in
    *pgpool-II\ version*) pass "pgpool --version reports: $V" ;;
    *)                    fail "pgpool --version (got: $V)" ;;
  esac

  cat > "$BASE/pgpool.conf" <<EOF
listen_addresses = '127.0.0.1'
port = $PGP_PORT
pcp_listen_addresses = '127.0.0.1'
pcp_port = $PCP_PORT
unix_socket_directories = '$SOCKDIR'
pcp_socket_dir = '$SOCKDIR'
backend_hostname0 = '127.0.0.1'
backend_port0 = $PG_PORT
backend_weight0 = 1
backend_flag0 = 'ALWAYS_PRIMARY'
enable_pool_hba = off
pool_passwd = ''
num_init_children = 4
max_pool = 1
pid_file_name = '$BASE/pgpool.pid'
logdir = '$BASE'
log_destination = 'stderr'
health_check_period = 0
sr_check_period = 0
EOF

  # On a stock macOS (kern.sysv.shmmax = 4194304) pgpool's default ~134MB
  # query-cache shared memory demand is fatal. Only assert where that limit holds.
  SHMMAX=$(sysctl -n kern.sysv.shmmax 2>/dev/null || echo unknown)
  if [ "$SHMMAX" = "4194304" ]; then
    pgpool -n -f "$BASE/pgpool.conf" >"$BASE/pgpool_fail.log" 2>&1 &
    T=$!; sleep 3; kill "$T" >/dev/null 2>&1; wait "$T" 2>/dev/null
    if grep -q "could not create shared memory for request size" "$BASE/pgpool_fail.log"; then
      REQ=$(grep -o 'request size: [0-9]*' "$BASE/pgpool_fail.log" | head -1 | grep -o '[0-9]*')
      pass "with default memqcache sizing pgpool dies: shared memory request $REQ vs shmmax $SHMMAX"
    else
      fail "expected pgpool shared-memory FATAL under 4MB shmmax (see pgpool_fail.log)"
    fi
  else
    skip "kern.sysv.shmmax is $SHMMAX, not the stock 4194304 - shared-memory startup failure not reproducible here"
  fi

  cat >> "$BASE/pgpool.conf" <<'EOF'
memory_cache_enabled = off
memqcache_total_size = 1MB
memqcache_max_num_cache = 10000
EOF
  pgpool -n -f "$BASE/pgpool.conf" >"$BASE/pgpool.log" 2>&1 &
  PGP_PID=$!; sleep 3

  GOT=$(q $PGP_PORT "SELECT current_database() || '|' || inet_server_port();")
  [ "$GOT" = "$U|$PG_PORT" ] \
    && pass "psql -p $PGP_PORT through pgpool reaches the backend: inet_server_port() = $PG_PORT" \
    || fail "connection through pgpool (got: $GOT)"

  NODE=$(q $PGP_PORT "SHOW POOL_NODES;" | head -1)
  case "$NODE" in
    0\|127.0.0.1\|$PG_PORT\|up\|*) pass "SHOW POOL_NODES reports node 0 on $PG_PORT with status up" ;;
    *) fail "SHOW POOL_NODES (got: $NODE)" ;;
  esac

  q $PGP_PORT "SET statement_timeout = '5s';" >/dev/null
  NOLEAK=$(q $PGP_PORT "SHOW statement_timeout;")
  [ "$NOLEAK" = "0" ] \
    && pass "pgpool session pooling: a new client sees the default statement_timeout (no leak)" \
    || fail "expected no state leak through pgpool (got: $NOLEAK)"

  NPROC=$(pgrep -f "pgpool -n -f $BASE/pgpool.conf" | wc -l | tr -d ' ')
  [ "$NPROC" -ge 6 ] \
    && pass "pgpool runs as $NPROC OS processes with num_init_children=4 (vs 1 for pgbouncer)" \
    || fail "pgpool process count (got: $NPROC, expected >= 6)"

  # num_init_children is a hard concurrency cap: 5th client blocks, is not rejected
  SLEEPERS=""
  for i in 1 2 3 4; do q $PGP_PORT "SELECT pg_sleep(4);" >/dev/null 2>&1 & SLEEPERS="$SLEEPERS $!"; done
  sleep 1
  T0=$(date +%s)
  FIFTH=$(q $PGP_PORT "SELECT 'in';")
  T1=$(date +%s); WAITED=$((T1-T0))
  if [ "$FIFTH" = "in" ] && [ "$WAITED" -ge 1 ]; then
    pass "5th client was blocked ${WAITED}s until a child freed, then served (not rejected)"
  else
    fail "5th-client saturation (result: $FIFTH, waited: ${WAITED}s, expected >=1s block)"
  fi
  wait $SLEEPERS
fi

# ---------- untestable here ----------
skip "pgpool load balancing / automatic failover / watchdog (needs multiple PostgreSQL servers; one backend configured) - see pgpool.net/docs/latest/en/html/intro-whatis.html"

echo "PASS=$PASS FAIL=$FAIL SKIP=$SKIP"
[ "$FAIL" -eq 0 ] || exit 1
exit 0