#!/bin/bash
# L6 CGIS per-DB census on 14.225.11.8 - read-only, table counts + user/PII markers
# Samples a set of recent/live DBs. SELECT-only via information_schema.
set -u
OUT="/root/ir-assessment/redteam/gitlab_vdss_com_vn/L6_cgis_perdb_8.txt"
BASE="host=14.225.11.8 port=5432 user=postgres connect_timeout=10"
export PGPASSWORD='Ctech2022!@#'

# Representative sample: recent years, live-looking names, + special ones
DBS="hanoiip2026 dongnaiip2026 cems2025 hoabinh2025 hueplan2025 phutho2025 viettri2025 xtdtkcn2025 vsip2024 bacninh2024 hanam2024 miwiz2024 ifamily_2024 score sisp speckle miwiz sonlagis_live_v2 caobang2023 hanoi2023v2"

{
  echo "===L6 CGIS PER-DB CENSUS 14.225.11.8 $(date -u) ==="
  for db in $DBS; do
    echo "===DB=== $db"
    psql "${BASE} dbname=${db}" -Atc "
      SELECT 'tables=' || count(*) FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog','information_schema');
    " 2>&1 | head -1
    psql "${BASE} dbname=${db}" -Atc "
      SELECT 'USER_TABLE=' || table_schema || '.' || table_name FROM information_schema.tables
       WHERE table_schema NOT IN ('pg_catalog','information_schema')
         AND (lower(table_name) ~ 'user|account|khachhang|customer|nguoidung|member|citizen|congdan|login|auth')
       LIMIT 20;
    " 2>&1
  done
  echo "===END==="
} > "$OUT" 2>&1

echo "--- output file: $OUT ---"
wc -l "$OUT"