/**
 * db/oracle/gap-recon.js — pre-cutover delta reconciliation (READ-ONLY).
 *
 *   node -r dotenv/config db/oracle/gap-recon.js
 *
 * Companion to parity-check.js (schema) and verify-migration.js (bulk copy). This
 * one answers: did any row written to Neon in the pre-cutover window fail to reach
 * Oracle? Bulk copy was 2026-07-23; Neon kept taking writes until ~07-24 17:54; the
 * planned delta sync was not run at the flip. See README "Cutover — completed &
 * verified data-clean" for the result (2 ephemeral tables only, no data lost).
 *
 * Why counts-by-timestamp, not id: identity sequences were reset at migration, so
 * Neon's gap-window ids now COLLIDE with Oracle's post-cutover ids — an id diff
 * would be wrong. Timestamps travel with the row on copy and Oracle's post-cutover
 * rows fall AFTER the window, so `neonCount - oraCount` in the window cleanly
 * isolates Neon-only rows. Tables with no insert-timestamp fall back to a total
 * count and are content-verified separately by static-verify.js.
 *
 * NOTHING is written — no truncate, no insert, no update. The window is the fixed
 * historical cutover window; edit WIN_* only to re-scope a hypothetical re-run.
 */

const path = require('path');
const { Pool } = require('pg');
const ora = require(path.join(__dirname, '..', 'oracle-client'));
const pg = new Pool({ connectionString: process.env.DATABASE_URL, ssl: { rejectUnauthorized: false } });

const WIN_START = '2026-07-23 00:00:00';
const WIN_END   = '2026-07-24 17:54:38';   // Neon's last observed write

// Preferred insert-time columns, best first. Mutable stamps (updated_at, last_*_at)
// deliberately excluded — they'd move for in-place updates and muddy the window.
const TS_PREF = ['created_at','occurred_at','recorded_at','executed_at','sent_at','captured_at',
  'accessed_at','ran_at','run_at','requested_at','started_at','triggered_at','uploaded_at',
  'generated_at','attempted_at','logged_at','ts','checked_at','first_seen_at','install_sha'];
const oraTbl = t => (t === '_migrations' ? 'migrations' : t).toUpperCase();

(async () => {
  const cols = (await pg.query(
    `SELECT table_name t, column_name c, data_type d
     FROM information_schema.columns
     WHERE table_schema='public' AND data_type LIKE 'timestamp%'`)).rows;
  const tsByTable = new Map();
  for (const r of cols) { if (!tsByTable.has(r.t)) tsByTable.set(r.t, new Set()); tsByTable.get(r.t).add(r.c); }
  const baseTables = (await pg.query(
    `SELECT table_name t FROM information_schema.tables
     WHERE table_schema='public' AND table_type='BASE TABLE' ORDER BY table_name`)).rows.map(r => r.t);

  const pickTs = (t) => { const s = tsByTable.get(t); if (!s) return null; for (const p of TS_PREF) if (s.has(p)) return p; return null; };
  const nWin = (t,c) => pg.query(`SELECT COUNT(*) n FROM "${t}" WHERE ${c} >= $1 AND ${c} <= $2`, [WIN_START, WIN_END]).then(r=>+r.rows[0].n);
  const oWin = (t,c) => ora.query(`SELECT COUNT(*) n FROM ${oraTbl(t)} WHERE ${c} >= TIMESTAMP '${WIN_START}' AND ${c} <= TIMESTAMP '${WIN_END}'`).then(r=>+r.rows[0].n);
  const nTot = (t) => pg.query(`SELECT COUNT(*) n FROM "${t}"`).then(r=>+r.rows[0].n);
  const oTot = (t) => ora.query(`SELECT COUNT(*) n FROM ${oraTbl(t)}`).then(r=>+r.rows[0].n);

  const gaps = [], clean = [], noTsNeonMore = [], noTsOk = [], errors = [];
  for (const t of baseTables) {
    if (t === '_migrations' || t === 'oracle_doc_chunks') continue; // pg-migrate ledger / vector store (bulk, no gap writes)
    const c = pickTs(t);
    try {
      if (c) {
        const [n, o] = await Promise.all([nWin(t,c), oWin(t,c)]);
        if (n > o) gaps.push({ t, c, n, o, delta: n - o }); else clean.push(t);
      } else {
        const [n, o] = await Promise.all([nTot(t), oTot(t)]);
        if (n > o) noTsNeonMore.push({ t, n, o, delta: n - o }); else noTsOk.push(t);
      }
    } catch (e) { errors.push(`${t}: ${e.message.slice(0,60)}`); }
  }

  console.log(`Window: [${WIN_START}  →  ${WIN_END}]  (read-only, nothing written)\n`);
  console.log(`═══ GAP — Neon has in-window rows that Oracle does NOT (by insert timestamp) ═══`);
  if (gaps.length) gaps.sort((a,b)=>b.delta-a.delta).forEach(g => console.log(`  ✗ ${g.t.padEnd(34)} ${g.c.padEnd(13)} Neon ${g.n}  Oracle ${g.o}  → ${g.delta} Neon-only`));
  else console.log('  none ✓');

  console.log(`\n═══ No insert-timestamp — TOTAL count, Neon > Oracle (see static-verify.js for content diff) ═══`);
  if (noTsNeonMore.length) noTsNeonMore.sort((a,b)=>b.delta-a.delta).forEach(g => console.log(`  ? ${g.t.padEnd(34)} total Neon ${g.n}  Oracle ${g.o}  → ${g.delta} more on Neon`));
  else console.log('  none ✓');

  console.log(`\n── summary ──`);
  console.log(`windowed tables clean (Neon ≤ Oracle in-window): ${clean.length}`);
  console.log(`no-ts tables where Oracle ≥ Neon total:          ${noTsOk.length}`);
  if (errors.length) { console.log(`\nerrors (${errors.length}):`); errors.forEach(e => console.log('  ! ' + e)); }

  const gapTotal = gaps.reduce((s,g)=>s+g.delta,0) + noTsNeonMore.reduce((s,g)=>s+g.delta,0);
  console.log(`\n==== ${gaps.length===0 && noTsNeonMore.length===0 ? 'NO GAP — every Neon in-window row is reflected in Oracle' : (gaps.length+noTsNeonMore.length)+' table(s), '+gapTotal+' Neon-only row(s)'} ====`);
  await pg.end(); await ora.close(0);
})().catch(e => { console.error('FATAL', e.message); process.exit(1); });
