#!/bin/bash
# UAT PG full census: akames, morning_live, dace, morning on 14.225.11.28:5432
# read-only: SELECT counts only. creds noti/nOTI@2025@# (validated, repo-hardcoded)
export PGPASSWORD='nOTI@2025@#'
H=14.225.11.28
P=5432
U=noti

for DB in akames morning_live dace morning; do
  echo "=== DB: $DB ==="
  # total user tables
  T=$(timeout 20 psql -h $H -p $P -U $U -d $DB -tA -c \
    "SELECT count(*) FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog','information_schema');" 2>&1)
  echo "user_tables_total: $T"
  # per-schema breakdown
  echo "schemas:"
  timeout 20 psql -h $H -p $P -U $U -d $DB -tA -c \
    "SELECT table_schema||'|'||count(*) FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog','information_schema') GROUP BY table_schema ORDER BY 2 DESC;" 2>&1
  # auth/sensitive tables with row counts
  echo "sensitive_tables:"
  timeout 30 psql -h $H -p $P -U $U -d $DB -tA <<'SQL' 2>&1
SELECT t.table_schema||'.'||t.table_name||'|'||
  (SELECT count(*) FROM information_schema.columns c WHERE c.table_schema=t.table_schema AND c.table_name=t.table_name)
FROM information_schema.tables t
WHERE t.table_schema NOT IN ('pg_catalog','information_schema')
  AND (t.table_name ILIKE '%user%' OR t.table_name ILIKE '%token%' OR t.table_name ILIKE '%employee%'
       OR t.table_name ILIKE '%account%' OR t.table_name ILIKE '%password%' OR t.table_name ILIKE '%credential%'
       OR t.table_name ILIKE '%customer%' OR t.table_name ILIKE '%transaction%' OR t.table_name ILIKE '%payment%'
       OR t.table_name ILIKE '%mail%' OR t.table_name ILIKE '%otp%')
ORDER BY 1 LIMIT 40;
SQL
  # db size + liveness
  timeout 20 psql -h $H -p $P -U $U -d $DB -tA -c "SELECT 'size|'||pg_size_pretty(pg_database_size('$DB')); SELECT 'now|'||now();" 2>&1
  echo
done
echo "=== pg_dump version available ==="
pg_dump --version
ls /usr/lib/postgresql/*/bin/pg_dump 2>/dev/null