#!/bin/bash
# L7 UAT PG restore-verify: restore 4 dumps into local PG17, row-parity vs live census, drop.
set -u
TS=20260818T101651Z
EX=/root/ir-assessment/redteam/gitlab_vdss_com_vn/exfil
PSQL="su postgres -c"
FAIL=0

for DB in akames morning_live dace morning; do
  V=verify_${DB}
  F=$EX/${DB}_uat_${TS}.sql
  echo "=== $V <- $F ==="
  su postgres -c "dropdb --if-exists $V" 2>&1
  su postgres -c "createdb $V" 2>&1
  su postgres -c "psql -v ON_ERROR_STOP=1 -d $V -f $F" >/tmp/restore_${DB}.log 2>&1
  RC=$?
  echo "restore rc=$RC ($(grep -c ERROR /tmp/restore_${DB}.log 2>/dev/null) ERRORs)"
  [ $RC -ne 0 ] && { echo "RESTORE FAILED $DB"; tail -5 /tmp/restore_${DB}.log; FAIL=1; continue; }

  # row parity on key tables
  case $DB in
    akames)
      for T in auth.user_info master.employee auth.user_token email.email_template master.customer; do
        L=$(su postgres -c "psql -d $V -tA -c 'SELECT count(*) FROM $T;'" 2>&1)
        echo "  $T restored=$L"
      done ;;
    morning_live|morning)
      for T in public.users public.transaction_detail public.user_detail; do
        L=$(su postgres -c "psql -d $V -tA -c 'SELECT count(*) FROM $T;'" 2>&1)
        echo "  $T restored=$L"
      done ;;
    dace)
      for T in public.users public.customer public.order_payment public.user_detail; do
        L=$(su postgres -c "psql -d $V -tA -c 'SELECT count(*) FROM $T;'" 2>&1)
        echo "  $T restored=$L"
      done ;;
  esac
  # table count parity
  RC_CNT=$(su postgres -c "psql -d $V -tA -c \"SELECT count(*) FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog','information_schema');\"" 2>&1)
  echo "  tables_total restored=$RC_CNT"
done
echo
echo "FAIL=$FAIL"