#!/usr/bin/env bash
# verify-load-balancing-ha-tools.sh
#
# Checks every claim in load-balancing-ha-tools.html.
#
#   A. maintenance dates, live from the GitHub API
#   B. repmgr's own compatibility matrix and PgJDBC's own deprecation notice
#   C. the PgJDBC experiments, re-run for real against a throwaway PostgreSQL
#      primary + streaming standby that this script creates and deletes
#
# Requires curl + python3; section C additionally needs a JDK and the PostgreSQL
# server binaries. Anything missing reports SKIP, never a fake PASS.
#
# Run:  bash verify-load-balancing-ha-tools.sh
# Exit: 0 if every checkable claim PASSes, 1 otherwise.

set -u

export PATH="/opt/homebrew/opt/postgresql@17/bin:/opt/homebrew/opt/openjdk/bin:$PATH"

PRI_PORT=55621
SBY_PORT=55622
W="$(mktemp -d "${TMPDIR:-/tmp}/verify-halb.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)); }

cleanup() {
  [ -d "$W/sby" ] && pg_ctl -D "$W/sby" -m immediate stop >/dev/null 2>&1
  [ -d "$W/pri" ] && pg_ctl -D "$W/pri" -m immediate stop >/dev/null 2>&1
  sleep 1
  rm -rf "$W"
}
trap cleanup EXIT

ONLINE=1
curl -sL --max-time 20 -o /dev/null https://api.github.com/ 2>/dev/null || ONLINE=0

printf '== A. Maintenance status (GitHub API) ==\n'
GH_OK=1
if [ "$ONLINE" -eq 0 ]; then
  GH_OK=0
else
  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]*) GH_OK=0;; *) [ "$REM" -ge 15 ] || GH_OK=0;; esac
  [ "$GH_OK" -eq 1 ] || printf '     GitHub API budget exhausted (remaining=%s); section A will SKIP.\n' "${REM:-?}"
fi

if [ "$GH_OK" -eq 0 ]; then
  skip "A1-A5 maintenance checks (offline or GitHub API rate-limited)"
else
  LIVE=0
  for R in pgbouncer/pgbouncer patroni/patroni haproxy/haproxy hapostgres/pg_auto_failover \
           citusdata/citus pgjdbc/pgjdbc pgpool/pgpool2 EnterpriseDB/repmgr; do
    curl -s --max-time 25 "https://api.github.com/repos/$R" > "$W/repo.json"
    LINE=$(python3 -c "
import json,sys
try:
    d=json.load(open(sys.argv[1]))
    print('%-28s pushed=%s archived=%s' % (sys.argv[2], d.get('pushed_at','?'), d.get('archived','?')))
except Exception:
    print('%-28s UNREADABLE' % sys.argv[2])
" "$W/repo.json" "$R")
    printf '     %s\n' "$LINE"
    case "$LINE" in *"archived=False"*) LIVE=$((LIVE+1));; esac
  done
  if [ "$LIVE" -eq 8 ]; then
    ok "A1 all eight projects are readable and none is archived on GitHub"
  else
    bad "A1 expected 8 live, unarchived repositories, got $LIVE"
  fi

  LC=$(curl -s --max-time 25 "https://api.github.com/repos/EnterpriseDB/repmgr/commits?per_page=1" \
       | python3 -c "import sys,json;print(json.load(sys.stdin)[0]['commit']['committer']['date'][:10])" 2>/dev/null)
  printf '     repmgr last commit on master: %s\n' "${LC:-unknown}"
  case "${LC:-}" in 2024-*|2025-*) ok "A2 repmgr's newest commit on master is still from 2024/2025";;
                    "")            skip "A2 repmgr commit date (API call failed)";;
                    *)             bad "A2 repmgr has committed since ($LC) -- the article is out of date";; esac

  REL=$(curl -s --max-time 25 "https://api.github.com/repos/EnterpriseDB/repmgr/releases/latest" \
        | python3 -c "import sys,json;d=json.load(sys.stdin);print(d.get('tag_name'),d.get('published_at','')[:10])" 2>/dev/null)
  printf '     repmgr latest release: %s\n' "${REL:-unknown}"
  case "$REL" in "v5.5.0 2024-11-22") ok "A3 repmgr's latest release is still v5.5.0 (2024-11-22)";;
                 "None "|"")          skip "A3 repmgr release lookup (API call failed)";;
                 *)                   bad "A3 repmgr's latest release changed: $REL";; esac

  RD=$(curl -s -o /dev/null -w '%{http_code}' --max-time 25 https://api.github.com/repos/citusdata/pg_auto_failover)
  case "$RD" in 301) ok "A4 citusdata/pg_auto_failover still redirects (it moved to hapostgres)";;
                403) skip "A4 pg_auto_failover redirect check (API rate-limited)";;
                *)   bad "A4 expected a 301 redirect for citusdata/pg_auto_failover, got $RD";; esac

  LIC=$(curl -s --max-time 25 https://api.github.com/repos/citusdata/citus \
        | python3 -c "import sys,json;print(json.load(sys.stdin)['license']['spdx_id'])" 2>/dev/null)
  case "$LIC" in AGPL-3.0) ok "A5 Citus is licensed AGPL-3.0 (free software, contra the old page)";;
                 "")       skip "A5 Citus licence lookup (API call failed)";;
                 *)        bad "A5 Citus licence is now '$LIC', not AGPL-3.0";; esac
fi

printf '\n== B. Claims quoted from the projects own source ==\n'
if [ "$ONLINE" -eq 0 ]; then
  skip "B1-B2 source-quote checks (offline)"
else
  curl -sL --max-time 30 https://raw.githubusercontent.com/EnterpriseDB/repmgr/master/doc/install-requirements.xml \
       -o "$W/repmgr.xml"
  MATRIX=$(python3 - "$W/repmgr.xml" <<'PY'
import re,sys,html
t=open(sys.argv[1],encoding='utf-8',errors='replace').read()
t=re.sub(r'<[^>]+>',' ',t); t=re.sub(r'\s+',' ',html.unescape(t))
i=t.find('repmgr; 5.5 Yes'); print(t[i:i+120] if i>=0 else t[-400:])
PY
)
  printf '     %s\n' "$(printf '%s' "$MATRIX" | cut -c1-200)"
  case "$MATRIX" in *"13, 14, 15, 16, 17"*)
      ok "B1 repmgr 5.5's compatibility matrix still stops at PostgreSQL 17";;
    *"18"*) bad "B1 repmgr's matrix now mentions 18 -- the article is out of date";;
    *)      bad "B1 could not find the repmgr compatibility row";; esac

  curl -sL --max-time 30 \
    https://raw.githubusercontent.com/pgjdbc/pgjdbc/master/pgjdbc/src/main/java/org/postgresql/ds/PGPoolingDataSource.java \
    -o "$W/pool.java"
  if grep -q '@deprecated Since 42.0.0, instead of this class you should use a fully featured connection pool' "$W/pool.java"; then
    ok "B2 PGPoolingDataSource still carries the quoted @deprecated notice"
  else
    bad "B2 the quoted PGPoolingDataSource deprecation notice was not found"
  fi
fi

printf '\n== C. The PgJDBC experiments, re-run ==\n'
if ! command -v initdb >/dev/null 2>&1 || ! command -v java >/dev/null 2>&1 || [ "$ONLINE" -eq 0 ]; then
  skip "C1-C4 PgJDBC experiments (needs a JDK, PostgreSQL server binaries and a network)"
else
  mkdir -p "$W/pri" "$W/sby"
  initdb -D "$W/pri" -U postgres --auth-local=trust --auth-host=trust >/dev/null 2>&1
  cat >> "$W/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" >> "$W/pri/pg_hba.conf"
  pg_ctl -D "$W/pri" -l "$W/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)" >/dev/null 2>&1
  pg_basebackup -h 127.0.0.1 -p $PRI_PORT -U postgres -D "$W/sby" -R -X stream >/dev/null 2>&1
  chmod 700 "$W/sby"
  sed "s/^port = $PRI_PORT/port = $SBY_PORT/" "$W/sby/postgresql.conf" > "$W/sby/pc" && mv "$W/sby/pc" "$W/sby/postgresql.conf"
  pg_ctl -D "$W/sby" -l "$W/sby.log" start >/dev/null 2>&1; sleep 2

  JAR="$W/pgjdbc.jar"
  curl -sL --max-time 120 -o "$JAR" \
    https://repo1.maven.org/maven2/org/postgresql/postgresql/42.7.13/postgresql-42.7.13.jar
  if [ ! -s "$JAR" ]; then
    skip "C1-C4 PgJDBC experiments (could not download the driver)"
  else
    cat > "$W/V.java" <<JAVA
import java.sql.*;
import java.util.*;

public class V {
    static String where(Connection c) throws SQLException {
        try (Statement s = c.createStatement();
             ResultSet r = s.executeQuery("select case when pg_is_in_recovery() then 'standby' else 'primary' end")) {
            r.next(); return r.getString(1);
        }
    }
    static Map<String,Integer> conns(String url, int n) throws Exception {
        Map<String,Integer> h = new TreeMap<>();
        for (int i = 0; i < n; i++) {
            try (Connection c = DriverManager.getConnection(url, "postgres", "")) {
                h.merge(where(c), 1, Integer::sum);
                for (int q = 0; q < 3; q++) h.merge("q:" + where(c), 1, Integer::sum);
            }
        }
        return h;
    }
    public static void main(String[] a) throws Exception {
        String hosts = "127.0.0.1:$PRI_PORT,127.0.0.1:$SBY_PORT";
        String base  = "jdbc:postgresql://" + hosts + "/hw_lb_demo";
        Map<String,Integer> A = conns(base + "?loadBalanceHosts=true", 40);
        Map<String,Integer> B = conns(base + "?loadBalanceHosts=false", 40);
        Map<String,Integer> C = conns(base + "?loadBalanceHosts=true&targetServerType=primary", 20);
        Map<String,Integer> D = conns(base + "?loadBalanceHosts=true&targetServerType=preferSecondary", 20);
        System.out.println("A " + A);
        System.out.println("B " + B);
        System.out.println("C " + C);
        System.out.println("D " + D);
        int ok = 0, failed = 0; String err = null;
        for (int i = 0; i < 30; i++) {
            try (Connection c = DriverManager.getConnection(base + "?loadBalanceHosts=true", "postgres", "");
                 Statement s = c.createStatement()) {
                s.executeUpdate("INSERT INTO t VALUES (" + (300000 + i) + ",'x')");
                ok++;
            } catch (SQLException e) { failed++; if (err == null) err = e.getMessage(); }
        }
        System.out.println("W ok=" + ok + " failed=" + failed + " err=" + err);
    }
}
JAVA
    ( cd "$W" && java -cp "$JAR" V.java > out.txt 2>&1 )
    sed 's/^/     /' "$W/out.txt"

    AL=$(grep '^A ' "$W/out.txt")
    if printf '%s' "$AL" | grep -q 'primary=' && printf '%s' "$AL" | grep -q 'standby='; then
      ok "C1 loadBalanceHosts=true spread 40 connections over both hosts"
    else
      bad "C1 loadBalanceHosts=true did not use both hosts: $AL"
    fi
    if python3 - "$W/out.txt" <<'PY'
import re,sys
t=open(sys.argv[1]).read()
m=dict(re.findall(r'(q?:?\w+)=(\d+)', re.search(r'^A .*$', t, re.M).group(0)))
p=int(m.get('primary',0)); s=int(m.get('standby',0))
qp=int(m.get('q:primary',0)); qs=int(m.get('q:standby',0))
sys.exit(0 if qp == 3*p and qs == 3*s else 1)
PY
    then
      ok "C2 every query stayed on its connection's host (queries = 3 x connections, per host)"
    else
      bad "C2 query counts are not exactly 3x the per-host connection counts"
    fi
    BL=$(grep '^B ' "$W/out.txt")
    if printf '%s' "$BL" | grep -q 'standby='; then
      bad "C3 loadBalanceHosts=false reached the standby: $BL"
    else
      ok "C3 loadBalanceHosts=false (the default) sent all 40 connections to the first host"
    fi
    CL=$(grep '^C ' "$W/out.txt"); DL=$(grep '^D ' "$W/out.txt")
    if printf '%s' "$CL" | grep -q 'standby=' ; then
      bad "C4 targetServerType=primary reached the standby: $CL"
    elif printf '%s' "$DL" | grep -q 'primary=' ; then
      bad "C4 targetServerType=preferSecondary reached the primary: $DL"
    else
      ok "C4 targetServerType pinned connections correctly in both directions"
    fi
    WL=$(grep '^W ' "$W/out.txt")
    if printf '%s' "$WL" | grep -q 'failed=0'; then
      bad "C5 no INSERT failed -- the article's write-failure claim would be wrong: $WL"
    elif printf '%s' "$WL" | grep -q 'read-only transaction'; then
      ok "C5 INSERTs over loadBalanceHosts=true failed on the standby ($WL)"
    else
      bad "C5 unexpected write result: $WL"
    fi
  fi
fi

printf '\n== D. not verifiable on this hardware ==\n'
printf 'SKIP D1 Patroni / repmgr / pg_auto_failover deployment (no Linux host)\n'
printf 'SKIP D2 PgBouncer and HAProxy under load                (not deployed)\n'
printf 'SKIP D3 any failover event                              (nothing was killed)\n'
SKIP=$((SKIP+3))

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