/**
 * db/oracle/verify-migration.js — independent check that Neon and Oracle agree.
 *
 *   node db/oracle/verify-migration.js
 *
 * Deliberately does NOT reuse migrate-data.js's bookkeeping: it re-counts both
 * sides and re-reads real values, so a bug in the migrator cannot mark itself OK.
 *
 * Two comparisons here are subtler than they look, and both bit the first run:
 *
 * - LIVE DRIFT. Neon is still serving production, so a table can legitimately
 *   gain rows after the copy. A raw count mismatch is therefore not proof of
 *   loss. When counts differ, this checks whether every extra Neon row has an id
 *   ABOVE Oracle's high-water mark; if so it is drift (reported), and only rows
 *   at or below the mark going missing counts as a failure.
 *
 * - LENGTH() IS NOT COMPARABLE ACROSS ENGINES. Postgres LENGTH() counts
 *   codepoints; Oracle counts UTF-16 units, so one emoji (U+1F534, as used in
 *   TuneVault's AI summaries) reads as 1 in Postgres and 2 in Oracle. Comparing
 *   lengths reports a phantom mismatch on identical text. Content is compared by
 *   SHA-256 of the actual string instead.
 */

const crypto = require('crypto');
const path = require('path');
const { Pool } = require('pg');
const db = require(path.join(__dirname, '..', 'oracle-client'));

const SKIP = new Set(['oracle_doc_chunks']);
const TABLE_RENAMES = { _migrations: 'migrations' };
const oraTable = (t) => (TABLE_RENAMES[t] || t).toUpperCase();

const pg = new Pool({ connectionString: process.env.DATABASE_URL, ssl: { rejectUnauthorized: false } });

let pass = 0, fail = 0;
const check = (label, cond, detail = '') => {
  if (cond) { pass++; console.log(`  PASS  ${label}`); }
  else { fail++; console.log(`  FAIL  ${label}  ${detail}`); }
};

/** Does this table have a plain numeric id we can use as a high-water mark? */
async function hasNumericId(t) {
  const r = await pg.query(
    `SELECT 1 FROM information_schema.columns
      WHERE table_schema='public' AND table_name=$1 AND column_name='id'
        AND data_type IN ('integer','bigint','smallint')`, [t]);
  return r.rowCount > 0;
}

(async () => {
  const tables = (await pg.query(
    `SELECT table_name FROM information_schema.tables
      WHERE table_schema='public' AND table_type='BASE TABLE' ORDER BY table_name`)).rows.map((r) => r.table_name);

  console.log('── row counts');
  const lost = [];
  const drifted = [];
  let neonTotal = 0, oraTotal = 0, compared = 0;

  for (const t of tables) {
    if (SKIP.has(t)) continue;
    const n = Number((await pg.query(`SELECT COUNT(*) AS n FROM "${t}"`)).rows[0].n);
    const o = Number((await db.query(`SELECT COUNT(*) AS n FROM ${oraTable(t)}`)).rows[0].n);
    neonTotal += n; oraTotal += o; compared++;
    if (n === o) continue;

    if (n > o && await hasNumericId(t)) {
      const mark = (await db.query(`SELECT NVL(MAX(id), 0) AS m FROM ${oraTable(t)}`)).rows[0].m;
      const atOrBelow = Number((await pg.query(
        `SELECT COUNT(*) AS n FROM "${t}" WHERE id <= $1`, [mark])).rows[0].n);
      const oraAtOrBelow = Number((await db.query(
        `SELECT COUNT(*) AS n FROM ${oraTable(t)} WHERE id <= :1`, [mark])).rows[0].n);
      if (atOrBelow === oraAtOrBelow) { drifted.push({ t, extra: n - o, mark }); continue; }
      lost.push(`${t}: ${atOrBelow - oraAtOrBelow} row(s) at or below id ${mark} missing`);
    } else {
      lost.push(`${t}: neon=${n} oracle=${o}`);
    }
  }

  console.log(`  tables compared : ${compared}`);
  console.log(`  neon rows       : ${neonTotal}`);
  console.log(`  oracle rows     : ${oraTotal}`);
  if (drifted.length) {
    console.log(`  live drift      : ${drifted.length} table(s) — Neon gained rows AFTER the copy, none lost`);
    for (const d of drifted) console.log(`      ${d.t}: +${d.extra} new rows above id ${d.mark}`);
  }
  check('no rows lost (drift excluded)', lost.length === 0, lost.join(' | '));

  console.log('\n── data spot checks');
  const nu = (await pg.query(`SELECT id, email FROM users ORDER BY id LIMIT 1`)).rows[0];
  const ou = (await db.query(`SELECT id, email FROM USERS WHERE id = :1`, [nu.id])).rows[0];
  check('users: id + email identical', ou && ou.email === nu.email);

  const nj = (await pg.query(
    `SELECT id, remediate_services FROM connection_schedules WHERE remediate_services IS NOT NULL LIMIT 1`)).rows[0];
  if (nj) {
    const oj = (await db.query(`SELECT remediate_services FROM CONNECTION_SCHEDULES WHERE id = :1`, [nj.id])).rows[0];
    const v = typeof oj.remediate_services === 'string' ? JSON.parse(oj.remediate_services) : oj.remediate_services;
    check('jsonb array preserved exactly', JSON.stringify(v) === JSON.stringify(nj.remediate_services));
  }

  const na = (await pg.query(`SELECT id, oracle_sids FROM agent_tunnels WHERE oracle_sids IS NOT NULL LIMIT 1`)).rows[0];
  if (na) {
    const oa = (await db.query(`SELECT oracle_sids FROM AGENT_TUNNELS WHERE id = :1`, [na.id])).rows[0];
    const v = typeof oa.oracle_sids === 'string' ? JSON.parse(oa.oracle_sids) : oa.oracle_sids;
    check('text[] became a real JSON array',
      Array.isArray(v) && JSON.stringify(v) === JSON.stringify(na.oracle_sids));
  }

  const nb = (await pg.query(`SELECT id, enabled, auto_remediate FROM connection_schedules ORDER BY id`)).rows;
  const ob = (await db.query(`SELECT id, enabled, auto_remediate FROM CONNECTION_SCHEDULES ORDER BY id`)).rows;
  check('booleans preserved (incl. false)',
    nb.length === ob.length &&
    nb.every((r, i) => ob[i].enabled === r.enabled && ob[i].auto_remediate === r.auto_remediate));

  const nt = (await pg.query(
    `SELECT id, created_at FROM health_checks WHERE created_at IS NOT NULL ORDER BY id LIMIT 1`)).rows[0];
  const ot = (await db.query(`SELECT created_at FROM HEALTH_CHECKS WHERE id = :1`, [nt.id])).rows[0];
  check('timestamptz instant preserved (0 ms drift)',
    new Date(ot.created_at).getTime() === new Date(nt.created_at).getTime());

  // Content equality by hash — NOT by LENGTH(), see header.
  const nc = (await pg.query(
    `SELECT id, ai_analysis FROM health_checks WHERE ai_analysis IS NOT NULL
      ORDER BY LENGTH(ai_analysis) DESC LIMIT 1`)).rows[0];
  if (nc) {
    const oc = (await db.query(`SELECT ai_analysis FROM HEALTH_CHECKS WHERE id = :1`, [nc.id])).rows[0];
    const h = (s) => crypto.createHash('sha256').update(s, 'utf8').digest('hex');
    check(`largest CLOB byte-identical (${Buffer.byteLength(nc.ai_analysis)} bytes)`,
      h(nc.ai_analysis) === h(oc.ai_analysis));
  }

  const oe = (await db.query(`SELECT alert_email FROM CONNECTION_SCHEDULES WHERE id = 26`)).rows[0];
  if (oe) check("PG '' stored as NULL (Oracle cannot hold '' — documented)", oe.alert_email === null);

  console.log('\n── integrity');
  const fk = (await db.query(
    `SELECT COUNT(*) AS n FROM user_constraints
      WHERE constraint_type='R' AND status='ENABLED' AND validated='VALIDATED'`)).rows[0];
  check('all 106 FKs enabled AND validated', Number(fk.n) === 106, `${fk.n}/106`);
  const bad = (await db.query(`SELECT COUNT(*) AS n FROM user_objects WHERE status <> 'VALID'`)).rows[0];
  check('no invalid objects', Number(bad.n) === 0);

  const seq = (await db.query(`
    SELECT i.table_name, i.column_name, s.last_number
      FROM user_tab_identity_cols i
      JOIN user_sequences s ON s.sequence_name = i.sequence_name`)).rows;
  let seqBad = [];
  for (const r of seq) {
    const mx = (await db.query(`SELECT NVL(MAX(${r.column_name}),0) AS m FROM ${r.table_name}`)).rows[0];
    if (Number(r.last_number) <= Number(mx.m)) seqBad.push(`${r.table_name}(next=${r.last_number} max=${mx.m})`);
  }
  check(`all ${seq.length} identity sequences past their max id`, seqBad.length === 0, seqBad.join(', '));

  console.log(`\n${pass} passed, ${fail} failed`);
  await pg.end();
  await db.close(0);
  process.exit(fail ? 2 : 0);
})().catch(async (e) => {
  console.error('FAILED:', e.message);
  await pg.end().catch(() => {}); await db.close(0).catch(() => {});
  process.exit(1);
});
