/**
 * db/oracle/static-verify.js — content verify of no-timestamp tables (READ-ONLY).
 *
 *   node -r dotenv/config db/oracle/static-verify.js
 *
 * Companion to gap-recon.js. gap-recon can only compare no-insert-timestamp tables
 * by TOTAL count — which could let a Neon-only row hide behind Oracle's post-cutover
 * growth. This enumerates those tables and diffs them by CONTENT: it pulls each
 * row's best identity key from both DBs and reports any key present on Neon but not
 * Oracle. Used at cutover to confirm the 13 no-timestamp tables were clean.
 *
 * NOTHING is written.
 */

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 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'`)).rows;
  const byTable = new Map();
  for (const r of cols) {
    if (!byTable.has(r.t)) byTable.set(r.t, { all: new Set(), ts: new Set() });
    byTable.get(r.t).all.add(r.c);
    if (r.d.startsWith('timestamp')) byTable.get(r.t).ts.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 = byTable.get(t).ts; for (const p of TS_PREF) if (s.has(p)) return p; return null; };
  const noTs = baseTables.filter(t => t !== '_migrations' && t !== 'oracle_doc_chunks' && !pickTs(t));

  // Best identity key to diff on. NOTE: a table with none of these (e.g. mutable
  // single-row state) falls back to its first column — a weak key; such tables are
  // operational state, not business data (flagged in the README caveat).
  const KEY_PREF = ['id','slug','key','name','user_id','connection_id','sql_id','token','email'];
  const pickKey = t => { const s = byTable.get(t).all; for (const k of KEY_PREF) if (s.has(k)) return k; return [...s][0]; };

  console.log(`No-insert-timestamp base tables: ${noTs.length}\n`);
  console.log(`${'table'.padEnd(30)} ${'key'.padEnd(14)} ${'Neon'.padEnd(6)} ${'Oracle'.padEnd(7)} verdict`);
  console.log('─'.repeat(78));

  let neonOnlyTotal = 0, issues = [];
  for (const t of noTs) {
    const key = pickKey(t);
    try {
      const nRows = (await pg.query(`SELECT ${key} k FROM "${t}"`)).rows.map(r => String(r.k));
      const oRows = (await ora.query(`SELECT ${key} k FROM ${oraTbl(t)}`)).rows.map(r => String(r.k));
      const oSet = new Set(oRows);
      const neonOnly = [...new Set(nRows)].filter(k => !oSet.has(k));
      const verdict = neonOnly.length === 0 ? 'OK ✓' : `✗ ${neonOnly.length} Neon-only key(s): ${neonOnly.slice(0,5).join(',')}${neonOnly.length>5?'…':''}`;
      if (neonOnly.length) { neonOnlyTotal += neonOnly.length; issues.push(t); }
      console.log(`${t.padEnd(30)} ${key.padEnd(14)} ${String(nRows.length).padEnd(6)} ${String(oRows.length).padEnd(7)} ${verdict}`);
    } catch (e) {
      console.log(`${t.padEnd(30)} ${key.padEnd(14)} ${'—'.padEnd(6)} ${'—'.padEnd(7)} ERR ${e.message.slice(0,40)}`);
    }
  }
  console.log('\n' + '─'.repeat(78));
  console.log(`==== ${neonOnlyTotal===0 ? 'ALL '+noTs.length+' STATIC TABLES CLEAN — no Neon-only rows by key' : issues.length+' table(s) with Neon-only rows: '+issues.join(', ')} ====`);
  await pg.end(); await ora.close(0);
})().catch(e => { console.error('FATAL', e.message); process.exit(1); });
