/**
 * db/oracle/parity-check.js — live Neon vs live Oracle schema parity.
 *
 *   node db/oracle/parity-check.js
 *
 * A stricter check than the DDL-time column count: it diffs the two RUNNING
 * databases and covers constraints, not just column presence. Read-only.
 *
 * Run it again immediately before the DNS cutover — Neon is still live, so a new
 * '' in a NOT NULL text column, or a newly added migration, can reintroduce drift
 * (see db/oracle/README.md, "Schema parity audit"). Exits non-zero on any
 * cutover-blocking gap; the benign, documented differences are printed as INFO.
 *
 * Three phases:
 *   1. table + column presence           (missing on Oracle = blocking)
 *   2. CHECK allowed-value sets (enum) + NOT NULL + DEFAULT
 *        - value writable on Neon that an Oracle CHECK rejects   = blocking
 *        - Oracle NOT NULL where Neon allows NULL                = blocking
 *   3. numeric / non-enum CHECKs (normalized clause compare)
 */

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 } });

// ── known, deliberate differences (NOT drift) ────────────────────────────────
const RENAMED = new Map([
  ['connection_health_runs.trigger', 'connection_health_runs.run_trigger'], // TRIGGER is reserved
]);
const NEON_ONLY_TABLES = new Set(['_migrations', 'migrations', 'pgmigrations']); // pg-migrate ledger
// NOT NULL on Neon, nullable on Oracle — both '' -> NULL, behaviour-preserving.
const DOCUMENTED_NULL_RELAX = new Set([
  'connection_schedules.alert_email',   // a migrated '' row
  'user_mfa.totp_secret',               // disableMfa() writes '' to clear the secret
]);

let blocking = 0;
const RISK = (m) => { blocking++; console.log('  ✗ ' + m); };
const INFO = (m) => console.log('  · ' + m);
const setdiff = (a, b) => [...a].filter((x) => !b.has(x));
const lits = (s) => { const out = new Set(); const re = /'((?:[^']|'')*)'/g; let m; while ((m = re.exec(s || ''))) out.add(m[1].replace(/''/g, "'")); return out; };
const hasStr = (s) => /'/.test(s || '');
const canon = (s) => String(s).toLowerCase()
  .replace(/::[a-z0-9_ ]+/g, '').replace(/"/g, '').replace(/\s+/g, '')
  .replace(/\((?=[a-z0-9_])/g, '').replace(/[()]/g, '').replace(/(\d)\.0+(?!\d)/g, '$1');

(async () => {
  // ═══ Phase 1 — tables + columns ═══════════════════════════════════════════
  console.log('── Phase 1: tables + columns');
  const nCols = (await pg.query(
    `SELECT table_name t, column_name c FROM information_schema.columns WHERE table_schema='public'`)).rows;
  const nViews = (await pg.query(
    `SELECT table_name FROM information_schema.views WHERE table_schema='public'`)).rows.map((r) => r.table_name);
  const neonT = new Map();
  for (const r of nCols) { if (!neonT.has(r.t)) neonT.set(r.t, new Set()); neonT.get(r.t).add(r.c.toLowerCase()); }

  const oCols = (await ora.query(`SELECT table_name t, column_name c FROM user_tab_columns`)).rows;
  const oraT = new Map();
  for (const r of oCols) { const t = r.t.toLowerCase(); if (!oraT.has(t)) oraT.set(t, new Set()); oraT.get(t).add(r.c.toLowerCase()); }
  console.log(`  Neon: ${neonT.size} tables (${nViews.length} views)   Oracle: ${oraT.size} tables`);

  for (const [t, cols] of neonT) {
    if (NEON_ONLY_TABLES.has(t) || nViews.includes(t)) continue;
    if (!oraT.has(t)) { RISK(`table missing on Oracle: ${t}`); continue; }
    const oc = oraT.get(t);
    for (const c of cols) {
      if (oc.has(c)) continue;
      const key = `${t}.${c}`;
      if (RENAMED.has(key) && oc.has(RENAMED.get(key).split('.')[1])) continue; // renamed, present
      RISK(`column missing on Oracle: ${key}`);
    }
  }
  const extra = [...oraT.keys()].filter((t) => !neonT.has(t) && !NEON_ONLY_TABLES.has(t));
  if (extra.length) INFO(`Oracle-only objects (internals, not app schema): ${extra.join(', ')}`);

  // ═══ Phase 2 — CHECK (enum) + NOT NULL + DEFAULT ══════════════════════════
  console.log('\n── Phase 2: CHECK enum sets + NOT NULL + DEFAULT');
  const nChk = (await pg.query(
    `SELECT tc.table_name t, cc.check_clause c
     FROM information_schema.check_constraints cc
     JOIN information_schema.table_constraints tc
       ON tc.constraint_name=cc.constraint_name AND tc.constraint_schema=cc.constraint_schema
     WHERE tc.table_schema='public' AND tc.constraint_type='CHECK' AND cc.check_clause NOT LIKE '%IS NOT NULL%'`)).rows;
  const oChk = (await ora.query(
    `SELECT table_name t, search_condition_vc c FROM user_constraints
     WHERE constraint_type='C' AND search_condition_vc IS NOT NULL AND UPPER(search_condition_vc) NOT LIKE '%IS NOT NULL%'`)).rows;
  const bag = (rows) => { const m = new Map(); for (const r of rows) { const t = r.t.toLowerCase(); if (!m.has(t)) m.set(t, new Set()); for (const v of lits(r.c)) m.get(t).add(v); } return m; };
  const nCL = bag(nChk), oCL = bag(oChk);
  for (const t of new Set([...nCL.keys(), ...oCL.keys()])) {
    const neonOnly = setdiff(nCL.get(t) || new Set(), oCL.get(t) || new Set());
    if (neonOnly.length) RISK(`${t}: Oracle CHECK rejects ${JSON.stringify(neonOnly)} (writable on Neon)`);
  }

  const nMeta = new Map();
  for (const r of (await pg.query(
    `SELECT table_name t, column_name c, is_nullable nul, (column_default IS NOT NULL OR is_identity='YES') hasdef
     FROM information_schema.columns WHERE table_schema='public'`)).rows)
    nMeta.set(`${r.t.toLowerCase()}.${r.c.toLowerCase()}`, { nn: r.nul === 'NO', def: r.hasdef });
  const oIdent = new Set((await ora.query(`SELECT table_name t, column_name c FROM user_tab_identity_cols`)).rows.map((r) => `${r.t.toLowerCase()}.${r.c.toLowerCase()}`));
  const oMeta = new Map();
  for (const r of (await ora.query(
    `SELECT table_name t, column_name c, nullable nul, CASE WHEN data_default IS NOT NULL THEN 1 ELSE 0 END hasdef FROM user_tab_columns`)).rows) {
    const k = `${r.t.toLowerCase()}.${r.c.toLowerCase()}`;
    oMeta.set(k, { nn: r.nul === 'N', def: r.hasdef === 1 || oIdent.has(k) });
  }
  for (const [k, nm] of nMeta) {
    const om = oMeta.get(k); if (!om) continue;
    if (om.nn && !nm.nn) RISK(`NOT NULL on Oracle but NULLABLE on Neon: ${k}`);
    if (nm.nn && !om.nn) { DOCUMENTED_NULL_RELAX.has(k) ? INFO(`documented NOT NULL relaxation: ${k}`) : RISK(`undocumented NOT NULL relaxation (Neon NOT NULL, Oracle nullable): ${k}`); }
    if (nm.def && !om.def) INFO(`default on Neon, none on Oracle: ${k}`);
    if (om.def && !nm.def) INFO(`default on Oracle, none on Neon: ${k}`);
  }

  // ═══ Phase 3 — numeric / non-enum CHECKs ══════════════════════════════════
  console.log('\n── Phase 3: numeric / non-enum CHECKs');
  const grp = (rows) => { const m = new Map(); for (const r of rows.filter((x) => !hasStr(x.c))) { const t = r.t.toLowerCase(); if (!m.has(t)) m.set(t, new Map()); m.get(t).set(canon(r.c), r.c.trim()); } return m; };
  const N = grp(nChk), O = grp(oChk);
  for (const t of new Set([...N.keys(), ...O.keys()])) {
    const n = N.get(t) || new Map(), o = O.get(t) || new Map();
    const nOnly = [...n].filter(([k]) => !o.has(k)), oOnly = [...o].filter(([k]) => !n.has(k));
    // A Neon-only numeric clause with no Oracle equivalent could be a stricter domain on
    // Neon than Oracle — surface it. (Pure ARRAY-vs-IN representation collides after canon.)
    if (nOnly.length && !oOnly.length) nOnly.forEach(([, v]) => RISK(`${t}: numeric CHECK on Neon has no Oracle match: ${v}`));
    else if (nOnly.length || oOnly.length) { INFO(`${t}: CHECK differs in form only — Neon ${JSON.stringify(nOnly.map((x) => x[1]))} / Oracle ${JSON.stringify(oOnly.map((x) => x[1]))}`); }
  }

  await pg.end(); await ora.close(0);
  console.log(`\n==== ${blocking === 0 ? 'PARITY OK — no cutover-blocking drift' : blocking + ' CUTOVER-BLOCKING ITEM(S)'} ====`);
  process.exit(blocking ? 1 : 0);
})().catch((e) => { console.error('FATAL', e.message); process.exit(2); });
