#!/usr/bin/env bash
# verify-pgpool-ii-load-balancing.sh
#
# Rebuilds the whole lab from pgpool-ii-load-balancing.html and re-measures it:
# a PostgreSQL primary, a streaming standby, and a Pgpool-II instance in
# streaming_replication mode, all on scratch ports, all deleted on exit.
#
# Requires: PostgreSQL 17 client+server binaries and Pgpool-II on PATH.
#           (macOS: brew install postgresql@17 pgpool-ii)
# Network checks against pgpool.net report SKIP when offline; they never FAIL
# for lack of a network.
#
# Run:  bash verify-pgpool-ii-load-balancing.sh
# Exit: 0 if every checkable claim PASSes, 1 otherwise.

set -u

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

PRI_PORT=55611
SBY_PORT=55612
POOL_PORT=55699
PCP_PORT=55698

# Short path: pgpool's unix sockets must fit in 103 bytes.
B="$(mktemp -d /tmp/hwlbv.XXXXXX)"

PASS=0; FAIL=0; SKIP=0
ok()   { printf 'PASS %s\n' "$1"; PASS=$((PASS+1)); }
bad()  { printf 'FAIL %s\n' "$1"; FAIL=$((FAIL+1)); }
skip() { printf 'SKIP %s\n' "$1"; SKIP=$((SKIP+1)); }
check(){ if [ "$2" = "$3" ]; then ok "$1"; else bad "$1 (expected [$3], got [$2])"; fi; }

cleanup() {
  pgpool -f "$B/pgp/pgpool.conf" -m fast stop  >/dev/null 2>&1
  pg_ctl -D "$B/sby" -m immediate stop         >/dev/null 2>&1
  pg_ctl -D "$B/pri" -m immediate stop         >/dev/null 2>&1
  sleep 1
  rm -rf "$B"
}
trap cleanup EXIT

have() { command -v "$1" >/dev/null 2>&1; }

if ! have initdb || ! have psql; then
  skip "A-D everything (PostgreSQL server binaries not on PATH)"
  printf '\nPASS=%d FAIL=%d SKIP=%d\n' "$PASS" "$FAIL" "$SKIP"
  exit 0
fi
if ! have pgpool; then
  skip "A-C Pgpool-II experiments (pgpool not on PATH: brew install pgpool-ii)"
  PGPOOL_OK=0
else
  PGPOOL_OK=1
fi

printf '== Building the cluster ==\n'
mkdir -p "$B/pri" "$B/sby" "$B/pgp"
initdb -D "$B/pri" -U postgres --auth-local=trust --auth-host=trust >/dev/null 2>&1
cat >> "$B/pri/postgresql.conf" <<EOF
port = $PRI_PORT
listen_addresses = '127.0.0.1'
wal_level = replica
max_wal_senders = 5
hot_standby = on
EOF
echo "host replication all 127.0.0.1/32 trust" >> "$B/pri/pg_hba.conf"
pg_ctl -D "$B/pri" -l "$B/pri.log" start >/dev/null 2>&1
sleep 2
psql -h 127.0.0.1 -p $PRI_PORT -U postgres -qc "create database hw_lb_demo" >/dev/null 2>&1
psql -h 127.0.0.1 -p $PRI_PORT -U postgres -d hw_lb_demo -qc \
  "create table t(id int primary key, v text); insert into t select g,'row'||g from generate_series(1,1000) g" >/dev/null 2>&1
pg_basebackup -h 127.0.0.1 -p $PRI_PORT -U postgres -D "$B/sby" -R -X stream >/dev/null 2>&1
chmod 700 "$B/sby"
# BSD and GNU sed differ on -i; rewrite the file instead.
sed "s/^port = $PRI_PORT/port = $SBY_PORT/" "$B/sby/postgresql.conf" > "$B/sby/pc.tmp" \
  && mv "$B/sby/pc.tmp" "$B/sby/postgresql.conf"
pg_ctl -D "$B/sby" -l "$B/sby.log" start >/dev/null 2>&1
sleep 2

REC_PRI=$(psql -h 127.0.0.1 -p $PRI_PORT -U postgres -Atc "select pg_is_in_recovery()" 2>/dev/null)
REC_SBY=$(psql -h 127.0.0.1 -p $SBY_PORT -U postgres -Atc "select pg_is_in_recovery()" 2>/dev/null)
check "S1 primary is not in recovery"  "$REC_PRI" "f"
check "S2 standby is in recovery"      "$REC_SBY" "t"
ROWS=$(psql -h 127.0.0.1 -p $SBY_PORT -U postgres -d hw_lb_demo -Atc "select count(*) from t" 2>/dev/null)
check "S3 standby replicated the 1000 test rows" "$ROWS" "1000"

mkconf() { # $1 load_balance_mode  $2 weight0  $3 weight1
  cat > "$B/pgp/pgpool.conf" <<EOF
listen_addresses = '127.0.0.1'
port = $POOL_PORT
socket_dir = '$B/pgp'
pcp_listen_addresses = '127.0.0.1'
pcp_port = $PCP_PORT
pcp_socket_dir = '$B/pgp'
logdir = '$B/pgp'
pid_file_name = '$B/pgp/pgpool.pid'
backend_clustering_mode = 'streaming_replication'
backend_hostname0 = '127.0.0.1'
backend_port0 = $PRI_PORT
backend_weight0 = $2
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = '127.0.0.1'
backend_port1 = $SBY_PORT
backend_weight1 = $3
backend_flag1 = 'ALLOW_TO_FAILOVER'
enable_pool_hba = off
num_init_children = 4
max_pool = 2
connection_cache = off
load_balance_mode = $1
sr_check_period = 5
sr_check_user = 'postgres'
sr_check_password = ''
sr_check_database = 'postgres'
health_check_period = 10
health_check_timeout = 5
health_check_user = 'postgres'
health_check_database = 'postgres'
log_destination = 'stderr'
memory_cache_enabled = off
enable_shared_relcache = off
memqcache_total_size = 1MB
memqcache_max_num_cache = 1024
EOF
}

restart_pool() {
  pgpool -f "$B/pgp/pgpool.conf" -m fast stop >/dev/null 2>&1
  sleep 1
  rm -f "$B/pgp/pgpool_status"
  ( pgpool -f "$B/pgp/pgpool.conf" -n > "$B/pgp/pgpool.log" 2>&1 & )
  sleep 4
}

# Echoes "<select_cnt node0> <select_cnt node1>" after $1 SELECTs.
run_selects() {
  local n="$1" i
  for i in $(seq 1 "$n"); do
    PGCONNECT_TIMEOUT=5 psql -h 127.0.0.1 -p $POOL_PORT -U postgres -d hw_lb_demo \
      -Atqc "SELECT count(*) FROM t" >/dev/null 2>&1
  done
  psql -h 127.0.0.1 -p $POOL_PORT -U postgres -d hw_lb_demo -Atc "SHOW POOL_NODES" 2>/dev/null \
    | awk -F'|' '{printf "%s ", $9}'
}

if [ "$PGPOOL_OK" -eq 1 ]; then
  printf '\n== A. load_balance_mode=on, weights 1:1 ==\n'
  mkconf on 1 1; restart_pool
  set -- $(run_selects 100)
  A0=${1:-x}; A1=${2:-x}
  printf '     select_cnt: node0=%s node1=%s\n' "$A0" "$A1"
  if [ "$A0" -gt 0 ] 2>/dev/null && [ "$A1" -gt 0 ] 2>/dev/null && [ $((A0+A1)) -eq 100 ]; then
    ok "A1 both nodes served SELECTs and the counts total 100"
  else
    bad "A1 expected a split totalling 100, got node0=$A0 node1=$A1"
  fi

  printf '\n== B. load_balance_mode=on, weights 1:9 ==\n'
  mkconf on 1 9; restart_pool
  set -- $(run_selects 100)
  B0=${1:-x}; B1=${2:-x}
  printf '     select_cnt: node0=%s node1=%s\n' "$B0" "$B1"
  if [ "$B1" -gt "$B0" ] 2>/dev/null; then
    ok "B1 the 0.9-weighted standby served the majority ($B1 vs $B0)"
  else
    bad "B1 expected the standby to serve the majority, got node0=$B0 node1=$B1"
  fi

  printf '\n== C. load_balance_mode=off with the SAME 1:9 weights ==\n'
  mkconf off 1 9; restart_pool
  LBW=$(psql -h 127.0.0.1 -p $POOL_PORT -U postgres -d hw_lb_demo -Atc "SHOW POOL_NODES" 2>/dev/null \
        | awk -F'|' 'NR==2{gsub(/ /,"",$6); print $6}')
  set -- $(run_selects 100)
  C0=${1:-x}; C1=${2:-x}
  printf '     lb_weight(node1)=%s  select_cnt: node0=%s node1=%s\n' "$LBW" "$C0" "$C1"
  check "C1 node 1 still reports lb_weight 0.900000" "$LBW" "0.900000"
  check "C2 but node 1 served zero SELECTs"          "$C1"  "0"
  check "C3 and node 0 served all 100"               "$C0"  "100"

  printf '\n== D. writes and in-transaction reads ==\n'
  mkconf on 1 9; restart_pool
  W=$(psql -h 127.0.0.1 -p $POOL_PORT -U postgres -d hw_lb_demo -Atqc \
       "INSERT INTO t VALUES (100001,'via-pgpool')" 2>&1; echo "rc=$?")
  case "$W" in *rc=0*) ok "D1 INSERT through Pgpool-II succeeds despite a 0.9 standby weight";;
               *)      bad "D1 INSERT through Pgpool-II failed: $W";; esac
  E=$(psql -h 127.0.0.1 -p $SBY_PORT -U postgres -d hw_lb_demo -Atqc \
       "INSERT INTO t VALUES (100002,'direct')" 2>&1)
  case "$E" in *"read-only transaction"*) ok "D2 the same INSERT sent straight to the standby is rejected";;
               *)                         bad "D2 expected a read-only error from the standby, got: $E";; esac

  restart_pool
  psql -h 127.0.0.1 -p $POOL_PORT -U postgres -d hw_lb_demo -q >/dev/null 2>&1 <<'SQL'
BEGIN;
INSERT INTO t VALUES (100003,'in-txn');
SELECT count(*) FROM t;
SELECT count(*) FROM t;
SELECT count(*) FROM t;
COMMIT;
SQL
  set -- $(psql -h 127.0.0.1 -p $POOL_PORT -U postgres -d hw_lb_demo -Atc "SHOW POOL_NODES" 2>/dev/null \
           | awk -F'|' '{printf "%s ", $9}')
  D0=${1:-x}; D1=${2:-x}
  printf '     after BEGIN/INSERT/3xSELECT/COMMIT: node0=%s node1=%s\n' "$D0" "$D1"
  check "D3 all three post-write SELECTs went to the primary" "$D0" "3"
  check "D4 none went to the standby"                          "$D1" "0"

  printf '\n== E. defaults, read out of the running server ==\n'
  grep -v '^load_balance_mode' "$B/pgp/pgpool.conf" > "$B/pgp/nodefault.conf"
  pgpool -f "$B/pgp/pgpool.conf" -m fast stop >/dev/null 2>&1; sleep 1
  rm -f "$B/pgp/pgpool_status"
  ( pgpool -f "$B/pgp/nodefault.conf" -n > "$B/pgp/nd.log" 2>&1 & )
  sleep 4
  g() { psql -h 127.0.0.1 -p $POOL_PORT -U postgres -d hw_lb_demo -Atc "PGPOOL SHOW $1" 2>/dev/null; }
  check "E1 load_balance_mode defaults to on in 4.7.2"                "$(g load_balance_mode)"             "on"
  check "E2 statement_level_load_balance defaults to off"             "$(g statement_level_load_balance)"  "off"
  check "E3 disable_load_balance_on_write defaults to 'transaction'"  "$(g disable_load_balance_on_write)" "transaction"
  V=$(pgpool --version 2>&1 | head -1)
  printf '     %s\n' "$V"
  case "$V" in *4.7.2*) ok "E4 the version under test is Pgpool-II 4.7.2";;
               *)       bad "E4 expected Pgpool-II 4.7.2, got: $V";; esac
  pgpool -f "$B/pgp/nodefault.conf" -m fast stop >/dev/null 2>&1
else
  skip "A-E live Pgpool-II experiments (pgpool not installed)"
fi

printf '\n== F. documentation claims, against pgpool.net ==\n'
ONLINE=1
curl -sL --max-time 15 -o /dev/null https://www.pgpool.net/ 2>/dev/null || ONLINE=0
text() {
  curl -sL --max-time 30 "$1" 2>/dev/null | python3 -c "
import sys,re,html
t=sys.stdin.read()
t=re.sub(r'(?s)<(script|style).*?</\1>',' ',t)
t=re.sub(r'<[^>]+>',' ',t)
sys.stdout.write(re.sub(r'\s+',' ',html.unescape(t)))"
}
if [ "$ONLINE" -eq 1 ]; then
  T42=$(text https://www.pgpool.net/docs/42/en/html/runtime-config-load-balancing.html)
  T43=$(text https://www.pgpool.net/docs/43/en/html/runtime-config-load-balancing.html)
  case "$T42" in *"load_balance_mode ( boolean ) When set to on"*"Default is off"*)
      ok "F1 the 4.2 docs say load_balance_mode defaults to off";;
    *) bad "F1 'Default is off' not found in the 4.2 load_balance_mode entry";; esac
  case "$T43" in *"load_balance_mode ( boolean ) When set to on"*"Default is on"*)
      ok "F2 the 4.3 docs say load_balance_mode defaults to on";;
    *) bad "F2 'Default is on' not found in the 4.3 load_balance_mode entry";; esac

  TSR=$(text https://www.pgpool.net/docs/latest/en/html/runtime-streaming-replication-check.html)
  case "$TSR" in *"Specifies the PostgreSQL user name to perform streaming replication check. Default is '' (empty)."*)
      ok "F3 sr_check_user is documented as defaulting to empty";;
    *) bad "F3 the quoted sr_check_user sentence was not found";; esac

  TLB=$(text https://www.pgpool.net/docs/latest/en/html/runtime-config-load-balancing.html)
  case "$TLB" in *"decided at the session start time and will not be changed until the session ends"*)
      ok "F4 load balancing is documented as chosen per session";;
    *) bad "F4 the quoted per-session sentence was not found";; esac

  TRQ=$(text https://www.pgpool.net/docs/latest/en/html/install-requirements.html)
  case "$TRQ" in *"Windows is not supported"*)
      ok "F5 the docs still say Windows is not supported";;
    *) bad "F5 'Windows is not supported' was not found in the requirements page";; esac

  # GitHub's unauthenticated API is 60 calls/hour; skip rather than fail when spent.
  REM=$(curl -s --max-time 20 https://api.github.com/rate_limit \
        | python3 -c "import sys,json;print(json.load(sys.stdin)['resources']['core']['remaining'])" 2>/dev/null)
  case "${REM:-0}" in ''|*[!0-9]*) REM=0;; esac
  if [ "$REM" -lt 5 ]; then
    skip "F6-F8 GitHub API checks (unauthenticated budget exhausted, remaining=$REM)"
  else
    R=$(curl -s --max-time 25 https://api.github.com/repos/pgpool/pgpooladmin 2>/dev/null)
    case "$R" in *'"pushed_at": "2021-06-17'*)
        ok "F6 the pgpoolAdmin repository was last pushed on 2021-06-17";;
      *) bad "F6 pgpoolAdmin's last push date is no longer 2021-06-17";; esac
    TAGS=$(curl -s --max-time 25 'https://api.github.com/repos/pgpool/pgpooladmin/tags?per_page=100' 2>/dev/null)
    case "$TAGS" in *'"V4_3'*) bad "F7 a pgpoolAdmin 4.3 tag exists -- the article's claim would be wrong";;
                    *)         ok "F7 no pgpoolAdmin tag for Pgpool-II 4.3 or later exists";; esac

    LIC=$(curl -s --max-time 25 https://api.github.com/repos/citusdata/citus 2>/dev/null)
    case "$LIC" in *'"spdx_id": "AGPL-3.0"'*) ok "F8 Citus is licensed AGPL-3.0 (not a paid product)";;
                   *) bad "F8 Citus's licence is no longer reported as AGPL-3.0";; esac
  fi
else
  skip "F1-F8 documentation and repository checks (offline)"
fi

printf '\n== G. not verifiable here ==\n'
printf 'SKIP G1 failover / health_check firing (no backend was killed)\n'
printf 'SKIP G2 watchdog and virtual IP (single host)\n'
SKIP=$((SKIP+2))

printf '\nPASS=%d FAIL=%d SKIP=%d\n' "$PASS" "$FAIL" "$SKIP"
[ "$FAIL" -eq 0 ] || exit 1
exit 0