#!/usr/bin/env bash
#
# postgresql-health-check-ubuntu.sh
#
# Read-only health check for a PostgreSQL environment on Ubuntu.
#
# Runs ~30 checks across configuration, memory, autovacuum, bloat, indexes,
# connections, cache, replication, backup and security, and prints a report with
# a severity per finding and the command that fixes it.
#
#   - READ ONLY. It runs SELECT and SHOW only. It never writes, never changes a
#     setting, never touches the network, and never sends your data anywhere.
#   - Safe on production. Every catalog query is bounded and cheap; the optional
#     bloat estimate is the heaviest and can be skipped with SKIP_BLOAT=1.
#
# Usage:
#   sudo -u postgres ./postgresql-health-check-ubuntu.sh
#   sudo -u postgres DATABASE=app ./postgresql-health-check-ubuntu.sh
#   sudo -u postgres FULL_REPORT=1 ./postgresql-health-check-ubuntu.sh
#
# Environment:
#   DATABASE     database to inspect for table-level checks (default: current)
#   SKIP_BLOAT   set to 1 to skip the bloat estimate on very large catalogs
#   FULL_REPORT  set to 1 to collect a shareable report for expert review
#   PGHOST/PGPORT/PGUSER  standard libpq variables
#
set -Eeuo pipefail

# ── Contact details shown at the end of the report ────────────────────────────
CONTACT_NAME="${CONTACT_NAME:-João Victor Oliveira — PG Monitoring}"
CONTACT_WHATSAPP="${CONTACT_WHATSAPP:-+55 62 98156-1666}"
CONTACT_EMAIL="${CONTACT_EMAIL:-joao.victor.32@hotmail.com}"
CONTACT_SITE="${CONTACT_SITE:-https://pgmonitoring.com}"

DATABASE="${DATABASE:-}"
SKIP_BLOAT="${SKIP_BLOAT:-0}"
FULL_REPORT="${FULL_REPORT:-0}"

CRITICAL_COUNT=0
WARNING_COUNT=0
OK_COUNT=0
FINDINGS_FILE="$(mktemp)"
trap 'rm -f "$FINDINGS_FILE"' EXIT

if [[ -t 1 ]]; then
  BOLD=$'\033[1m'; RED=$'\033[31m'; YELLOW=$'\033[33m'
  GREEN=$'\033[32m'; CYAN=$'\033[36m'; RESET=$'\033[0m'
else
  BOLD=''; RED=''; YELLOW=''; GREEN=''; CYAN=''; RESET=''
fi

fail() {
  echo "ERROR: $*" >&2
  exit 1
}

section() {
  printf '\n%s── %s %s\n' "$BOLD$CYAN" "$1" "$RESET"
}

# report <CRITICAL|WARNING|OK> <title> <detail> [fix]
report() {
  local level="$1" title="$2" detail="$3" fix="${4:-}"
  case "$level" in
    CRITICAL) CRITICAL_COUNT=$((CRITICAL_COUNT + 1))
              printf '  %s[CRITICAL]%s %s\n' "$RED$BOLD" "$RESET" "$title" ;;
    WARNING)  WARNING_COUNT=$((WARNING_COUNT + 1))
              printf '  %s[WARNING ]%s %s\n' "$YELLOW" "$RESET" "$title" ;;
    OK)       OK_COUNT=$((OK_COUNT + 1))
              printf '  %s[   OK   ]%s %s\n' "$GREEN" "$RESET" "$title" ;;
  esac
  [[ -n "$detail" ]] && printf '              %s\n' "$detail"
  [[ -n "$fix" ]] && printf '              %sfix:%s %s\n' "$BOLD" "$RESET" "$fix"
  printf '%s\t%s\t%s\t%s\n' "$level" "$title" "$detail" "$fix" >> "$FINDINGS_FILE"
}

# Scalar query against the maintenance database.
q() {
  psql -XAtq --no-password -d "${PGDATABASE:-postgres}" -c "$1" 2>/dev/null || echo ""
}

# Scalar query against the database being inspected.
qd() {
  psql -XAtq --no-password -d "$DATABASE" -c "$1" 2>/dev/null || echo ""
}

# Integer comparison that tolerates an empty result from a failed query.
gt() { [[ -n "$1" && "$1" != "" ]] && awk -v a="$1" -v b="$2" 'BEGIN { exit !(a > b) }'; }
lt() { [[ -n "$1" && "$1" != "" ]] && awk -v a="$1" -v b="$2" 'BEGIN { exit !(a < b) }'; }

command -v psql >/dev/null || fail "psql not found. Install postgresql-client."

SERVER_VERSION="$(q 'SHOW server_version')"
[[ -n "$SERVER_VERSION" ]] || fail "Cannot connect to PostgreSQL. Run as: sudo -u postgres $0"
SERVER_VERSION_NUM="$(q 'SHOW server_version_num')"
[[ -z "$DATABASE" ]] && DATABASE="$(q 'SELECT current_database()')"

printf '%s\n' "$BOLD"
printf '  PostgreSQL Environment Health Check\n'
printf '  %s\n' "$CONTACT_SITE"
printf '%s\n' "$RESET"
printf '  Server    : PostgreSQL %s\n' "$SERVER_VERSION"
printf '  Host      : %s\n' "$(hostname -f 2>/dev/null || hostname)"
printf '  Database  : %s\n' "$DATABASE"
printf '  Date      : %s\n' "$(date --iso-8601=seconds)"
printf '  Mode      : read-only (no changes are made)\n'

# ── 1. Version support ────────────────────────────────────────────────────────
section "Version and uptime"

if lt "$SERVER_VERSION_NUM" 130000; then
  report CRITICAL "PostgreSQL $SERVER_VERSION is end of life" \
    "This version no longer receives security or bug fixes from the community." \
    "Plan a major upgrade with pg_upgrade; test on a restored copy first."
elif lt "$SERVER_VERSION_NUM" 150000; then
  report WARNING "PostgreSQL $SERVER_VERSION is supported but dated" \
    "Newer majors bring substantial performance and observability improvements." \
    "Evaluate an upgrade path during the next maintenance window."
else
  report OK "PostgreSQL $SERVER_VERSION is a currently supported major version" "" ""
fi

UPTIME_SEC="$(q "SELECT EXTRACT(epoch FROM now() - pg_postmaster_start_time())::bigint")"
UPTIME_HUMAN="$(q "SELECT date_trunc('second', now() - pg_postmaster_start_time())::text")"
if lt "$UPTIME_SEC" 3600; then
  report WARNING "Server restarted less than one hour ago (up $UPTIME_HUMAN)" \
    "Statistics below cover a short window, so rates may not be representative." \
    "Re-run this check after the server has been up for a full workload cycle."
else
  report OK "Uptime: $UPTIME_HUMAN" "" ""
fi

# ── 2. Memory configuration ───────────────────────────────────────────────────
section "Memory configuration"

TOTAL_RAM_MB="$(awk '/MemTotal/ { printf "%d", $2 / 1024 }' /proc/meminfo 2>/dev/null || echo "")"
SHARED_BUFFERS_MB="$(q "SELECT (setting::bigint * current_setting('block_size')::bigint / 1024 / 1024) FROM pg_settings WHERE name = 'shared_buffers'")"

if [[ -n "$TOTAL_RAM_MB" && -n "$SHARED_BUFFERS_MB" ]]; then
  SB_PCT="$(awk -v s="$SHARED_BUFFERS_MB" -v t="$TOTAL_RAM_MB" 'BEGIN { printf "%.1f", (s / t) * 100 }')"
  TARGET_MB=$(( TOTAL_RAM_MB / 4 ))
  if lt "$SB_PCT" 10; then
    report CRITICAL "shared_buffers is only ${SB_PCT}% of RAM (${SHARED_BUFFERS_MB} MB of ${TOTAL_RAM_MB} MB)" \
      "The default 128MB is a common leftover; it forces the server to re-read from disk constantly." \
      "ALTER SYSTEM SET shared_buffers = '${TARGET_MB}MB';  -- then restart"
  elif gt "$SB_PCT" 40; then
    report WARNING "shared_buffers is ${SB_PCT}% of RAM (${SHARED_BUFFERS_MB} MB)" \
      "Above ~40% the OS page cache gets starved and double buffering wastes memory." \
      "Consider reducing toward 25% of RAM (${TARGET_MB}MB)."
  else
    report OK "shared_buffers: ${SHARED_BUFFERS_MB} MB (${SB_PCT}% of RAM)" "" ""
  fi
fi

WORK_MEM_KB="$(q "SELECT setting::bigint FROM pg_settings WHERE name = 'work_mem'")"
MAX_CONN="$(q "SELECT setting::int FROM pg_settings WHERE name = 'max_connections'")"
if [[ -n "$WORK_MEM_KB" ]]; then
  WORK_MEM_MB=$(( WORK_MEM_KB / 1024 ))
  if lt "$WORK_MEM_KB" 8192; then
    report WARNING "work_mem is only ${WORK_MEM_KB} kB" \
      "Sorts and hashes that exceed it spill to temporary files on disk, which is far slower." \
      "Raise gradually (e.g. 16MB) and watch temp file creation; it is allocated per operation."
  else
    # Worst case if every connection runs one memory-hungry node at once.
    WORST_CASE_MB=$(( (WORK_MEM_KB / 1024) * MAX_CONN ))
    if [[ -n "$TOTAL_RAM_MB" ]] && gt "$WORST_CASE_MB" "$TOTAL_RAM_MB"; then
      report WARNING "work_mem (${WORK_MEM_MB} MB) x max_connections (${MAX_CONN}) = ${WORST_CASE_MB} MB, above total RAM" \
        "work_mem is per sort/hash node, not per connection, so peaks can exhaust memory and trigger the OOM killer." \
        "Lower work_mem, lower max_connections, or put a connection pooler in front."
    else
      report OK "work_mem: ${WORK_MEM_MB} MB" "" ""
    fi
  fi
fi

EFFECTIVE_CACHE_MB="$(q "SELECT (setting::bigint * current_setting('block_size')::bigint / 1024 / 1024) FROM pg_settings WHERE name = 'effective_cache_size'")"
if [[ -n "$EFFECTIVE_CACHE_MB" && -n "$TOTAL_RAM_MB" ]]; then
  EC_PCT="$(awk -v s="$EFFECTIVE_CACHE_MB" -v t="$TOTAL_RAM_MB" 'BEGIN { printf "%.0f", (s / t) * 100 }')"
  if lt "$EC_PCT" 40; then
    report WARNING "effective_cache_size is only ${EC_PCT}% of RAM (${EFFECTIVE_CACHE_MB} MB)" \
      "It allocates nothing; it tells the planner how much cache exists. Too low pushes it away from index scans." \
      "ALTER SYSTEM SET effective_cache_size = '$(( TOTAL_RAM_MB * 3 / 4 ))MB';  -- reload is enough"
  else
    report OK "effective_cache_size: ${EFFECTIVE_CACHE_MB} MB (${EC_PCT}% of RAM)" "" ""
  fi
fi

# ── 3. Storage planner settings ───────────────────────────────────────────────
section "Planner and storage settings"

RANDOM_COST="$(q "SELECT setting FROM pg_settings WHERE name = 'random_page_cost'")"
if [[ -n "$RANDOM_COST" ]] && gt "$RANDOM_COST" 2.0; then
  report WARNING "random_page_cost is $RANDOM_COST" \
    "The 4.0 default models spinning disks. On SSD or NVMe it makes the planner avoid indexes it should use." \
    "On SSD: ALTER SYSTEM SET random_page_cost = 1.1;  -- reload is enough"
else
  report OK "random_page_cost: $RANDOM_COST" "" ""
fi

CHECKPOINT_TIMEOUT="$(q "SELECT setting::int FROM pg_settings WHERE name = 'checkpoint_timeout'")"
MAX_WAL_SIZE_MB="$(q "SELECT setting::bigint FROM pg_settings WHERE name = 'max_wal_size'")"
CHECKPOINTS_TIMED="$(q "SELECT checkpoints_timed FROM pg_stat_bgwriter" 2>/dev/null)"
CHECKPOINTS_REQ="$(q "SELECT checkpoints_req FROM pg_stat_bgwriter" 2>/dev/null)"
# PostgreSQL 17 moved these counters to pg_stat_checkpointer.
if [[ -z "$CHECKPOINTS_TIMED" ]]; then
  CHECKPOINTS_TIMED="$(q "SELECT num_timed FROM pg_stat_checkpointer")"
  CHECKPOINTS_REQ="$(q "SELECT num_requested FROM pg_stat_checkpointer")"
fi
if [[ -n "$CHECKPOINTS_REQ" && -n "$CHECKPOINTS_TIMED" ]]; then
  TOTAL_CP=$(( CHECKPOINTS_REQ + CHECKPOINTS_TIMED ))
  if (( TOTAL_CP > 20 )); then
    REQ_PCT=$(( CHECKPOINTS_REQ * 100 / TOTAL_CP ))
    if (( REQ_PCT > 30 )); then
      report WARNING "${REQ_PCT}% of checkpoints are forced by WAL volume, not by time" \
        "max_wal_size (${MAX_WAL_SIZE_MB} MB) is too small for the write rate, causing frequent I/O spikes." \
        "Raise max_wal_size (e.g. 4GB) so checkpoints are driven by checkpoint_timeout instead."
    else
      report OK "Checkpoints are mostly time-driven (${REQ_PCT}% forced)" "" ""
    fi
  fi
fi

if lt "$CHECKPOINT_TIMEOUT" 600; then
  report WARNING "checkpoint_timeout is ${CHECKPOINT_TIMEOUT}s" \
    "Frequent checkpoints repeatedly flush the same pages and add write amplification." \
    "ALTER SYSTEM SET checkpoint_timeout = '15min';"
fi

# ── 4. Connections ────────────────────────────────────────────────────────────
section "Connections"

CURRENT_CONN="$(q "SELECT count(*) FROM pg_stat_activity")"
CONN_PCT=$(( CURRENT_CONN * 100 / ${MAX_CONN:-100} ))
if (( CONN_PCT > 90 )); then
  report CRITICAL "Connection usage at ${CONN_PCT}% (${CURRENT_CONN}/${MAX_CONN})" \
    "New connections will be refused once the limit is reached." \
    "Add a pooler (PgBouncer) rather than raising max_connections; each backend costs memory."
elif (( CONN_PCT > 80 )); then
  report WARNING "Connection usage at ${CONN_PCT}% (${CURRENT_CONN}/${MAX_CONN})" \
    "Little headroom left for traffic spikes or maintenance sessions." \
    "Review application pool sizes, or put PgBouncer in front."
else
  report OK "Connection usage: ${CONN_PCT}% (${CURRENT_CONN}/${MAX_CONN})" "" ""
fi

if gt "$MAX_CONN" 300; then
  report WARNING "max_connections is ${MAX_CONN}" \
    "Each connection is a process with its own memory; hundreds of mostly-idle backends waste RAM and CPU." \
    "Use PgBouncer in transaction mode and lower max_connections to what the server can truly serve."
fi

IDLE_IN_TX="$(q "SELECT count(*) FROM pg_stat_activity
  WHERE state = 'idle in transaction' AND state_change < now() - interval '5 minutes'")"
if gt "$IDLE_IN_TX" 10; then
  report CRITICAL "${IDLE_IN_TX} connections idle in transaction for over 5 minutes" \
    "They hold locks and block vacuum from cleaning dead rows, which causes bloat and blocks DDL." \
    "Fix the application's transaction handling; set idle_in_transaction_session_timeout as a safety net."
elif gt "$IDLE_IN_TX" 3; then
  report WARNING "${IDLE_IN_TX} connections idle in transaction for over 5 minutes" \
    "These hold back the vacuum horizon and can block schema changes." \
    "ALTER SYSTEM SET idle_in_transaction_session_timeout = '10min';"
else
  report OK "No significant idle-in-transaction sessions" "" ""
fi

LONG_QUERIES="$(q "SELECT count(*) FROM pg_stat_activity
  WHERE state = 'active' AND backend_type = 'client backend'
    AND query_start < now() - interval '60 seconds'")"
if gt "$LONG_QUERIES" 3; then
  report WARNING "${LONG_QUERIES} queries running for over 60 seconds" \
    "Long queries hold snapshots that prevent vacuum from reclaiming dead rows." \
    "Inspect: SELECT pid, now()-query_start AS duration, query FROM pg_stat_activity WHERE state='active' ORDER BY 2 DESC;"
else
  report OK "No long-running queries above 60s right now" "" ""
fi

BLOCKED="$(q "SELECT count(*) FROM pg_stat_activity WHERE wait_event_type = 'Lock'")"
if gt "$BLOCKED" 5; then
  report CRITICAL "${BLOCKED} sessions blocked waiting on locks" \
    "A lock queue is forming; throughput is degrading now." \
    "SELECT pid, pg_blocking_pids(pid), query FROM pg_stat_activity WHERE wait_event_type='Lock';"
elif gt "$BLOCKED" 1; then
  report WARNING "${BLOCKED} sessions blocked waiting on locks" "" \
    "SELECT pid, pg_blocking_pids(pid), query FROM pg_stat_activity WHERE wait_event_type='Lock';"
else
  report OK "No lock contention detected" "" ""
fi

# ── 5. Cache efficiency ───────────────────────────────────────────────────────
section "Cache efficiency"

CACHE_HIT="$(q "SELECT round(100.0 * sum(blks_hit) / NULLIF(sum(blks_hit) + sum(blks_read), 0), 2)
  FROM pg_stat_database WHERE datname = current_database()")"
if [[ -n "$CACHE_HIT" ]]; then
  if lt "$CACHE_HIT" 90; then
    report CRITICAL "Cache hit ratio is ${CACHE_HIT}%" \
      "Most reads are going to disk. Expect high latency across the whole workload." \
      "Raise shared_buffers, or find the queries doing large sequential scans."
  elif lt "$CACHE_HIT" 95; then
    report WARNING "Cache hit ratio is ${CACHE_HIT}%" \
      "Healthy OLTP systems usually sit above 99%." \
      "Check for missing indexes causing large scans, then consider more shared_buffers."
  else
    report OK "Cache hit ratio: ${CACHE_HIT}%" "" ""
  fi
fi

TEMP_FILES="$(q "SELECT temp_files FROM pg_stat_database WHERE datname = current_database()")"
TEMP_BYTES="$(q "SELECT pg_size_pretty(temp_bytes) FROM pg_stat_database WHERE datname = current_database()")"
if gt "$TEMP_FILES" 1000; then
  report WARNING "${TEMP_FILES} temporary files written (${TEMP_BYTES} total)" \
    "Sorts and hashes are spilling to disk because work_mem is too small for them." \
    "Raise work_mem for the sessions that need it, or add indexes to avoid the sorts."
else
  report OK "Temporary file usage is low (${TEMP_FILES} files)" "" ""
fi

# ── 6. Autovacuum and bloat ───────────────────────────────────────────────────
section "Autovacuum and table health"

AUTOVACUUM="$(q "SELECT setting FROM pg_settings WHERE name = 'autovacuum'")"
if [[ "$AUTOVACUUM" != "on" ]]; then
  report CRITICAL "autovacuum is OFF" \
    "Dead rows are never reclaimed. This ends in table bloat and eventually transaction ID wraparound." \
    "ALTER SYSTEM SET autovacuum = on;  -- then reload. Never leave this off."
else
  report OK "autovacuum is enabled" "" ""
fi

DEAD_TABLES="$(qd "SELECT count(*) FROM pg_stat_user_tables
  WHERE n_dead_tup > 10000 AND n_dead_tup > n_live_tup * 0.2")"
if gt "$DEAD_TABLES" 0; then
  WORST="$(qd "SELECT relname || ' (' || n_dead_tup || ' dead vs ' || n_live_tup || ' live)'
    FROM pg_stat_user_tables WHERE n_dead_tup > 10000
    ORDER BY n_dead_tup DESC LIMIT 1")"
  report WARNING "${DEAD_TABLES} tables have more than 20% dead rows" \
    "Worst: ${WORST}. Queries read dead rows too, so scans get slower as bloat grows." \
    "Check autovacuum is keeping up; lower autovacuum_vacuum_scale_factor on large tables."
else
  report OK "No significantly bloated tables detected" "" ""
fi

NEVER_VACUUMED="$(qd "SELECT count(*) FROM pg_stat_user_tables
  WHERE last_autovacuum IS NULL AND last_vacuum IS NULL AND n_live_tup > 100000")"
if gt "$NEVER_VACUUMED" 0; then
  report WARNING "${NEVER_VACUUMED} large tables have never been vacuumed" \
    "Autovacuum may not be reaching them, often because it is too slow or throttled." \
    "Review autovacuum_max_workers and autovacuum_vacuum_cost_delay."
fi

STALE_STATS="$(qd "SELECT count(*) FROM pg_stat_user_tables
  WHERE (last_autoanalyze IS NULL AND last_analyze IS NULL) AND n_live_tup > 100000")"
if gt "$STALE_STATS" 0; then
  report WARNING "${STALE_STATS} large tables have no planner statistics" \
    "Without statistics the planner guesses, which is the usual cause of a sudden bad plan." \
    "Run: vacuumdb --analyze-in-stages --dbname=${DATABASE}"
fi

# Transaction ID wraparound: the one that takes the database fully offline.
MAX_AGE="$(q "SELECT max(age(datfrozenxid)) FROM pg_database")"
if [[ -n "$MAX_AGE" ]]; then
  if gt "$MAX_AGE" 1500000000; then
    report CRITICAL "Transaction ID age is ${MAX_AGE}" \
      "At 2 billion PostgreSQL refuses new writes to protect the data. This is an outage in waiting." \
      "Vacuum the oldest databases now: vacuumdb --all --freeze --jobs=4"
  elif gt "$MAX_AGE" 1000000000; then
    report WARNING "Transaction ID age is ${MAX_AGE}" \
      "Approaching the wraparound threshold; autovacuum is not freezing fast enough." \
      "Investigate blockers: long transactions, abandoned replication slots, prepared transactions."
  else
    report OK "Transaction ID age is healthy (${MAX_AGE})" "" ""
  fi
fi

# ── 7. Indexes ────────────────────────────────────────────────────────────────
section "Indexes"

UNUSED_IDX="$(qd "SELECT count(*) FROM pg_stat_user_indexes s
  JOIN pg_index i ON i.indexrelid = s.indexrelid
  WHERE s.idx_scan = 0 AND NOT i.indisunique AND NOT i.indisprimary
    AND pg_relation_size(s.indexrelid) > 10485760")"
if gt "$UNUSED_IDX" 0; then
  UNUSED_SIZE="$(qd "SELECT pg_size_pretty(sum(pg_relation_size(s.indexrelid)))
    FROM pg_stat_user_indexes s JOIN pg_index i ON i.indexrelid = s.indexrelid
    WHERE s.idx_scan = 0 AND NOT i.indisunique AND NOT i.indisprimary
      AND pg_relation_size(s.indexrelid) > 10485760")"
  report WARNING "${UNUSED_IDX} unused indexes larger than 10 MB (${UNUSED_SIZE} total)" \
    "Every one slows down INSERT, UPDATE and DELETE and enlarges backups, while serving no read." \
    "Confirm across a full business cycle first, then DROP INDEX CONCURRENTLY."
else
  report OK "No large unused indexes found" "" ""
fi

# Foreign keys with no supporting index: silent cause of very slow DELETEs.
FK_NO_INDEX="$(qd "
  SELECT count(*)
    FROM pg_constraint c
    JOIN pg_class t ON t.oid = c.conrelid
   WHERE c.contype = 'f'
     AND NOT EXISTS (
       SELECT 1 FROM pg_index i
        WHERE i.indrelid = c.conrelid
          AND (i.indkey::smallint[])[0:array_length(c.conkey,1)-1] @> c.conkey
     )")"
if gt "$FK_NO_INDEX" 0; then
  report WARNING "${FK_NO_INDEX} foreign keys have no supporting index" \
    "Every DELETE or UPDATE on the parent table scans the whole child table to check the constraint." \
    "Create an index on each referencing column; this routinely turns 47s deletes into 1ms."
else
  report OK "All foreign keys have supporting indexes" "" ""
fi

DUPLICATE_IDX="$(qd "
  SELECT count(*) FROM (
    SELECT indrelid, indkey, count(*) AS n
      FROM pg_index GROUP BY indrelid, indkey HAVING count(*) > 1
  ) d")"
if gt "$DUPLICATE_IDX" 0; then
  report WARNING "${DUPLICATE_IDX} sets of duplicate indexes (same table, same columns)" \
    "Redundant indexes double the write cost and the storage for no benefit." \
    "Compare definitions in pg_indexes and drop the redundant one."
fi

# ── 8. Replication ────────────────────────────────────────────────────────────
section "Replication"

IS_REPLICA="$(q "SELECT pg_is_in_recovery()")"
if [[ "$IS_REPLICA" == "t" ]]; then
  LAG_SEC="$(q "SELECT COALESCE(EXTRACT(epoch FROM now() - pg_last_xact_replay_timestamp())::int, 0)")"
  if gt "$LAG_SEC" 60; then
    report CRITICAL "This is a replica lagging ${LAG_SEC}s behind the primary" \
      "Reads here return stale data, and failover would lose everything not yet replayed." ""
  else
    report OK "This is a replica, lag ${LAG_SEC}s" "" ""
  fi
else
  REPLICA_COUNT="$(q "SELECT count(*) FROM pg_stat_replication")"
  if gt "$REPLICA_COUNT" 0; then
    MAX_LAG="$(q "SELECT COALESCE(round(max(EXTRACT(epoch FROM replay_lag))::numeric, 1), 0) FROM pg_stat_replication")"
    if gt "$MAX_LAG" 60; then
      report CRITICAL "${REPLICA_COUNT} replicas connected, worst replay lag ${MAX_LAG}s" \
        "A failover right now would lose the un-replayed changes." \
        "Check network throughput, replica disk I/O, and long queries blocking replay on the standby."
    elif gt "$MAX_LAG" 10; then
      report WARNING "${REPLICA_COUNT} replicas connected, worst replay lag ${MAX_LAG}s" "" ""
    else
      report OK "${REPLICA_COUNT} replicas connected, max lag ${MAX_LAG}s" "" ""
    fi
  else
    report WARNING "No streaming replicas connected" \
      "There is no hot standby to fail over to; recovery depends entirely on backups." \
      "Consider a physical replica if the recovery time objective is tight."
  fi
fi

# Inactive slots retain WAL forever and are a classic cause of a full disk.
INACTIVE_SLOTS="$(q "SELECT count(*) FROM pg_replication_slots WHERE NOT active")"
if gt "$INACTIVE_SLOTS" 0; then
  SLOT_NAMES="$(q "SELECT string_agg(slot_name, ', ') FROM pg_replication_slots WHERE NOT active")"
  report CRITICAL "${INACTIVE_SLOTS} inactive replication slots: ${SLOT_NAMES}" \
    "An abandoned slot makes the server retain WAL indefinitely until pg_wal fills the disk and the server stops." \
    "If the consumer is truly gone: SELECT pg_drop_replication_slot('<name>');"
else
  report OK "No abandoned replication slots" "" ""
fi

# ── 9. Backup and WAL ─────────────────────────────────────────────────────────
section "Backup and WAL"

ARCHIVE_MODE="$(q "SELECT setting FROM pg_settings WHERE name = 'archive_mode'")"
if [[ "$ARCHIVE_MODE" != "on" ]]; then
  report CRITICAL "WAL archiving is disabled (archive_mode = ${ARCHIVE_MODE})" \
    "Point-in-time recovery is impossible. Recovery is limited to whatever your last dump captured." \
    "Enable archive_mode and archive_command: ${CONTACT_SITE}/blog/postgresql-backup-pg-basebackup-pitr-ubuntu"
else
  FAILED_ARCHIVES="$(q "SELECT failed_count FROM pg_stat_archiver")"
  LAST_FAIL="$(q "SELECT COALESCE(last_failed_time::text, '') FROM pg_stat_archiver")"
  if gt "$FAILED_ARCHIVES" 0; then
    report CRITICAL "WAL archiving has ${FAILED_ARCHIVES} failures (last: ${LAST_FAIL})" \
      "WAL is accumulating in pg_wal and the recovery chain has gaps. This breaks PITR silently." \
      "Check the archive_command destination: permissions, free space, and network reachability."
  else
    report OK "WAL archiving is enabled and healthy" "" ""
  fi
fi

WAL_SIZE="$(q "SELECT pg_size_pretty(sum(size)) FROM pg_ls_waldir()")"
WAL_COUNT="$(q "SELECT count(*) FROM pg_ls_waldir()")"
if gt "$WAL_COUNT" 500; then
  report WARNING "pg_wal holds ${WAL_COUNT} segments (${WAL_SIZE})" \
    "Segments are accumulating faster than they are archived or recycled." \
    "Check archiving failures and inactive replication slots above."
else
  report OK "pg_wal size: ${WAL_SIZE} (${WAL_COUNT} segments)" "" ""
fi

DATA_CHECKSUMS="$(q "SELECT setting FROM pg_settings WHERE name = 'data_checksums'")"
if [[ "$DATA_CHECKSUMS" != "on" ]]; then
  report WARNING "data_checksums is off" \
    "Silent disk corruption will not be detected; it gets copied into backups unnoticed." \
    "Enabling requires initdb or pg_checksums with the cluster stopped. Plan it for the next migration."
else
  report OK "data_checksums is enabled" "" ""
fi

# ── 10. Security ──────────────────────────────────────────────────────────────
section "Security"

TRUST_AUTH="$(q "SELECT count(*) FROM pg_hba_file_rules
  WHERE auth_method = 'trust' AND type != 'local'" 2>/dev/null)"
if gt "$TRUST_AUTH" 0; then
  report CRITICAL "${TRUST_AUTH} pg_hba.conf rules use 'trust' for non-local connections" \
    "Anyone who can reach the port connects as any user, with no password." \
    "Replace trust with scram-sha-256 in pg_hba.conf and reload."
else
  report OK "No 'trust' authentication for remote connections" "" ""
fi

MD5_USERS="$(q "SELECT count(*) FROM pg_authid WHERE rolpassword LIKE 'md5%'" 2>/dev/null)"
if gt "$MD5_USERS" 0; then
  report WARNING "${MD5_USERS} roles still use md5 password hashing" \
    "md5 is deprecated and weak; scram-sha-256 has been the default since PostgreSQL 14." \
    "Set password_encryption = 'scram-sha-256' and have those users reset their passwords."
fi

SUPERUSERS="$(q "SELECT count(*) FROM pg_roles WHERE rolsuper AND rolcanlogin")"
if gt "$SUPERUSERS" 3; then
  SUPERUSER_LIST="$(q "SELECT string_agg(rolname, ', ') FROM pg_roles WHERE rolsuper AND rolcanlogin")"
  report WARNING "${SUPERUSERS} login roles have superuser: ${SUPERUSER_LIST}" \
    "Superusers bypass every permission check, including row-level security." \
    "Grant targeted roles (pg_read_all_data, pg_monitor) instead of superuser."
else
  report OK "${SUPERUSERS} superuser login roles" "" ""
fi

LISTEN_ADDR="$(q "SELECT setting FROM pg_settings WHERE name = 'listen_addresses'")"
SSL_ON="$(q "SELECT setting FROM pg_settings WHERE name = 'ssl'")"
if [[ "$LISTEN_ADDR" == "*" && "$SSL_ON" != "on" ]]; then
  report CRITICAL "Server listens on all interfaces with SSL disabled" \
    "Credentials and query data cross the network in clear text." \
    "Enable ssl = on with a certificate, and restrict listen_addresses to known interfaces."
elif [[ "$SSL_ON" != "on" ]]; then
  report WARNING "SSL is disabled" \
    "Connections are unencrypted. Acceptable only if every client is on the same trusted host." \
    "ALTER SYSTEM SET ssl = on;  -- requires a certificate and a reload"
else
  report OK "SSL is enabled" "" ""
fi

LOG_MIN_DURATION="$(q "SELECT setting::int FROM pg_settings WHERE name = 'log_min_duration_statement'")"
if [[ "$LOG_MIN_DURATION" == "-1" ]]; then
  report WARNING "log_min_duration_statement is off" \
    "Slow queries are never logged, so post-incident analysis has nothing to work with." \
    "ALTER SYSTEM SET log_min_duration_statement = '1s';"
else
  report OK "Slow query logging enabled (>${LOG_MIN_DURATION}ms)" "" ""
fi

PG_STAT_STATEMENTS="$(q "SELECT count(*) FROM pg_extension WHERE extname = 'pg_stat_statements'")"
if [[ "$PG_STAT_STATEMENTS" == "0" ]]; then
  report WARNING "pg_stat_statements is not installed" \
    "Without it there is no reliable way to know which queries consume the server's time." \
    "Add to shared_preload_libraries, restart, then: CREATE EXTENSION pg_stat_statements;"
else
  report OK "pg_stat_statements is installed" "" ""
fi

# ── Summary ───────────────────────────────────────────────────────────────────
TOTAL=$(( CRITICAL_COUNT + WARNING_COUNT + OK_COUNT ))
printf '\n%s══ Summary ══%s\n\n' "$BOLD$CYAN" "$RESET"
printf '  %sCritical : %-3s%s  need attention now\n' "$RED$BOLD" "$CRITICAL_COUNT" "$RESET"
printf '  %sWarnings : %-3s%s  should be planned\n' "$YELLOW" "$WARNING_COUNT" "$RESET"
printf '  %sPassed   : %-3s%s  of %s checks\n' "$GREEN" "$OK_COUNT" "$RESET" "$TOTAL"

if (( CRITICAL_COUNT > 0 )); then
  printf '\n  %sThis environment has %s critical finding(s).%s\n' "$RED$BOLD" "$CRITICAL_COUNT" "$RESET"
  printf '  Critical items risk data loss, an outage, or a security exposure.\n'
elif (( WARNING_COUNT > 3 )); then
  printf '\n  %sNo critical issues, but %s warnings worth planning.%s\n' "$YELLOW" "$WARNING_COUNT" "$RESET"
else
  printf '\n  %sThis environment looks healthy.%s\n' "$GREEN$BOLD" "$RESET"
fi

# ── Optional: collect a shareable report for expert review ────────────────────
if [[ "$FULL_REPORT" == "1" ]]; then
  printf '\n%s══ Full report ══%s\n\n' "$BOLD$CYAN" "$RESET"
  printf '  This writes the findings above, plus your contact details, to a local\n'
  printf '  file that you can send us for a detailed analysis.\n\n'
  printf '  %sNothing is transmitted by this script.%s The file stays on this server\n' "$BOLD" "$RESET"
  printf '  until you choose to send it. Press Ctrl-C to skip.\n\n'

  read -r -p "  Name        : " LEAD_NAME
  read -r -p "  Company     : " LEAD_COMPANY
  read -r -p "  Email       : " LEAD_EMAIL
  read -r -p "  Phone       : " LEAD_PHONE

  REPORT_FILE="pg-health-check-$(date +%Y%m%d%H%M%S).txt"
  {
    echo "PostgreSQL Environment Health Check"
    echo "Generated: $(date --iso-8601=seconds)"
    echo
    echo "── Contact ──"
    echo "Name    : ${LEAD_NAME}"
    echo "Company : ${LEAD_COMPANY}"
    echo "Email   : ${LEAD_EMAIL}"
    echo "Phone   : ${LEAD_PHONE}"
    echo
    echo "── Environment ──"
    echo "PostgreSQL : ${SERVER_VERSION}"
    echo "Host       : $(hostname -f 2>/dev/null || hostname)"
    echo "Database   : ${DATABASE}"
    echo "RAM        : ${TOTAL_RAM_MB:-unknown} MB"
    echo "Uptime     : ${UPTIME_HUMAN}"
    echo
    echo "── Results: ${CRITICAL_COUNT} critical, ${WARNING_COUNT} warnings, ${OK_COUNT} passed ──"
    echo
    while IFS=$'\t' read -r level title detail fix; do
      [[ "$level" == "OK" ]] && continue
      echo "[${level}] ${title}"
      [[ -n "$detail" ]] && echo "    ${detail}"
      [[ -n "$fix" ]] && echo "    fix: ${fix}"
      echo
    done < "$FINDINGS_FILE"
    echo "── Database sizes ──"
    psql -XAtq --no-password -d postgres -c \
      "SELECT datname || ': ' || pg_size_pretty(pg_database_size(datname))
         FROM pg_database WHERE datallowconn ORDER BY pg_database_size(datname) DESC" 2>/dev/null
  } > "$REPORT_FILE"

  chmod 600 "$REPORT_FILE"
  printf '\n  %sReport written to: %s%s\n' "$GREEN$BOLD" "$REPORT_FILE" "$RESET"
  printf '  Review it (it contains only the data shown above), then send it to:\n\n'
  printf '    WhatsApp : %s\n' "$CONTACT_WHATSAPP"
  printf '    Email    : %s\n\n' "$CONTACT_EMAIL"
  printf '  We will reply with an analysis of the findings and what to fix first.\n'
fi

# ── Contact ───────────────────────────────────────────────────────────────────
printf '\n%s══ Need help with these findings? ══%s\n\n' "$BOLD$CYAN" "$RESET"
printf '  %s\n' "$CONTACT_NAME"
printf '  WhatsApp : %s\n' "$CONTACT_WHATSAPP"
printf '  Email    : %s\n' "$CONTACT_EMAIL"
printf '  Site     : %s\n\n' "$CONTACT_SITE"
if (( CRITICAL_COUNT > 0 || WARNING_COUNT > 3 )); then
  printf '  Re-run with FULL_REPORT=1 to generate a shareable report:\n'
  printf '    %ssudo -u postgres FULL_REPORT=1 %s%s\n\n' "$BOLD" "$0" "$RESET"
fi
printf '  This check is a snapshot. Most of what hurts a database builds up\n'
printf '  gradually — bloat, plan drift, a slow leak in connections. PG Monitoring\n'
printf '  watches these continuously and alerts before they become incidents.\n\n'

(( CRITICAL_COUNT > 0 )) && exit 1
exit 0