/**
 * db/oracle/migrate-data.js — copy application data from Neon (PostgreSQL) into
 * Oracle Autonomous DB 26ai.
 *
 *   node db/oracle/migrate-data.js [--only=t1,t2] [--dry-run] [--batch=500] [--truncate]
 *
 * Source : Neon, via DATABASE_URL (.env) — read-only, never written to.
 * Target : Oracle, via db/oracle-client.js (ADMIN, tunevaultdb_tp).
 *
 * Design notes that matter:
 *
 * - FK ORDER. Tables load parent-first via a topological sort of the FK graph.
 *   That is not sufficient on its own: `users.team_id -> teams` and
 *   `teams.owner_id -> users` form a genuine cycle, and `roles.approval_required_from`
 *   is self-referencing, so NO ordering can satisfy every constraint. FKs are
 *   therefore disabled for the load and re-enabled afterwards WITH VALIDATE, which
 *   makes Oracle check every existing row — integrity is proven at the end rather
 *   than assumed from the ordering.
 *
 * - BIND TYPES are derived from Oracle's own column metadata (user_tab_columns),
 *   not guessed from the JS values, so a NULL-heavy batch cannot pick the wrong
 *   type. Verified against the live DB: JSON takes a JS object, BOOLEAN a JS
 *   boolean, TIMESTAMP WITH TIME ZONE a JS Date, CLOB a long string.
 *
 * - IDENTITY columns are GENERATED BY DEFAULT, so explicit PK values insert fine,
 *   but the sequence does not advance. resetIdentitySequences() fixes that at the
 *   end; without it the first application insert collides on the PK.
 *
 * - A failing table is logged and skipped; the run continues. Each table commits
 *   per batch, so a partial table is possible and is reported as a count mismatch.
 */

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

// ── config ──────────────────────────────────────────────────────────────────

/** Handled separately: 22k rows with a 1536-dim vector column. */
const SKIP_TABLES = new Set(['oracle_doc_chunks']);

/** Renames applied when the schema was generated (see db/oracle/README.md). */
const TABLE_RENAMES = { _migrations: 'migrations' };
const COLUMN_RENAMES = { 'connection_health_runs.trigger': 'run_trigger' };

const args = process.argv.slice(2);
const argVal = (name, dflt) => {
  const a = args.find((x) => x.startsWith(`--${name}=`));
  return a ? a.slice(name.length + 3) : dflt;
};
const ONLY = argVal('only', '') ? argVal('only', '').split(',').map((s) => s.trim()) : null;
const DRY_RUN = args.includes('--dry-run');
/** Empty target tables first, so a re-run replaces rather than duplicates. */
const TRUNCATE = args.includes('--truncate');
const BATCH = Number(argVal('batch', 500));

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

// ── helpers ─────────────────────────────────────────────────────────────────

const oraTable = (t) => (TABLE_RENAMES[t] || t).toUpperCase();
const oraColumn = (t, c) => (COLUMN_RENAMES[`${t}.${c}`] || c).toUpperCase();

/** Oracle bind descriptor for a column, from its declared data type. */
function bindDefFor(dataType, maxObservedBytes) {
  switch (dataType) {
    case 'JSON': return { type: oracledb.DB_TYPE_JSON };
    case 'CLOB': return { type: oracledb.DB_TYPE_CLOB };
    case 'BLOB': return { type: oracledb.DB_TYPE_BLOB };
    case 'BOOLEAN': return { type: oracledb.DB_TYPE_BOOLEAN };
    case 'NUMBER': return { type: oracledb.DB_TYPE_NUMBER };
    case 'DATE': return { type: oracledb.DB_TYPE_DATE };
    case 'VECTOR': return { type: oracledb.DB_TYPE_VECTOR };
    default:
      if (/^TIMESTAMP.*WITH TIME ZONE$/.test(dataType)) return { type: oracledb.DB_TYPE_TIMESTAMP_TZ };
      if (/^TIMESTAMP/.test(dataType)) return { type: oracledb.DB_TYPE_TIMESTAMP };
      // VARCHAR2 / CHAR / NVARCHAR2 — maxSize is in BYTES and must fit the batch.
      return { type: oracledb.DB_TYPE_VARCHAR, maxSize: Math.min(32767, Math.max(1, maxObservedBytes)) };
  }
}

/** Coerce one Postgres value to what the Oracle bind type expects. */
function coerce(value, dataType) {
  if (value === null || value === undefined) return null;
  switch (dataType) {
    case 'JSON':
      // jsonb arrives as an object/array; text[] as a JS array. Strings are
      // already-serialised JSON and are parsed so the driver sends real JSON.
      if (typeof value === 'string') { try { return JSON.parse(value); } catch { return value; } }
      return value;
    case 'NUMBER': {
      // pg returns int8/numeric as strings to avoid precision loss.
      const n = typeof value === 'number' ? value : Number(value);
      if (!Number.isFinite(n)) return null;
      if (!Number.isSafeInteger(n) && Number.isInteger(n)) {
        console.warn(`    ! value ${value} exceeds JS safe-integer range`);
      }
      return n;
    }
    case 'BOOLEAN':
      return typeof value === 'boolean' ? value : value === 't' || value === 'true' || value === 1;
    case 'CLOB':
      return typeof value === 'string' ? value : JSON.stringify(value);
    case 'BLOB':
      return Buffer.isBuffer(value) ? value : Buffer.from(String(value));
    case 'DATE':
      return value instanceof Date ? value : new Date(value);
    default:
      if (/^TIMESTAMP/.test(dataType)) return value instanceof Date ? value : new Date(value);
      if (typeof value === 'string') return value;
      if (value instanceof Date) return value.toISOString();
      if (Array.isArray(value) || typeof value === 'object') return JSON.stringify(value);
      return String(value);
  }
}

/** Kahn topological sort, parents first. Cycles are broken deterministically. */
function topoSort(tables, fks) {
  const deps = new Map(tables.map((t) => [t, new Set()]));
  for (const f of fks) {
    if (f.child === f.parent) continue;                 // self-FK: not an ordering constraint
    if (deps.has(f.child) && deps.has(f.parent)) deps.get(f.child).add(f.parent);
  }
  const out = [];
  const done = new Set();
  const broken = [];
  let pool = [...tables];
  while (pool.length) {
    const ready = pool.filter((t) => [...deps.get(t)].every((p) => done.has(p)));
    if (!ready.length) {
      // Cycle: emit the most-depended-upon remaining table and record the break.
      const pick = pool.slice().sort((a, b) => deps.get(a).size - deps.get(b).size)[0];
      broken.push(pick);
      out.push(pick); done.add(pick);
      pool = pool.filter((t) => t !== pick);
      continue;
    }
    for (const t of ready.sort()) { out.push(t); done.add(t); }
    pool = pool.filter((t) => !done.has(t));
  }
  return { order: out, broken };
}

// ── main ────────────────────────────────────────────────────────────────────

(async () => {
  const t0 = Date.now();
  console.log(`batch size ${BATCH}${DRY_RUN ? '  [DRY RUN — no writes]' : ''}\n`);

  // Oracle column metadata
  const colRows = (await db.query(
    `SELECT table_name, column_name, data_type, data_length, column_id
       FROM user_tab_columns ORDER BY table_name, column_id`)).rows;
  const oraCols = {};
  for (const r of colRows) {
    (oraCols[r.table_name] ||= new Map()).set(r.column_name, { type: r.data_type, len: r.data_length });
  }

  // Oracle FK graph (child -> parent)
  const fkRows = (await db.query(
    `SELECT c.constraint_name, c.table_name AS child, p.table_name AS parent
       FROM user_constraints c
       JOIN user_constraints p ON p.constraint_name = c.r_constraint_name
      WHERE c.constraint_type = 'R'`)).rows;
  const fks = fkRows.map((r) => ({ name: r.constraint_name, child: r.child, parent: r.parent }));

  // Neon tables
  let neonTables = (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);
  if (ONLY) neonTables = neonTables.filter((t) => ONLY.includes(t));

  const { order, broken } = topoSort(neonTables, fks.map((f) => ({
    child: Object.keys(TABLE_RENAMES).find((k) => oraTable(k) === f.child) || f.child.toLowerCase(),
    parent: Object.keys(TABLE_RENAMES).find((k) => oraTable(k) === f.parent) || f.parent.toLowerCase(),
  })));
  if (broken.length) {
    console.log(`FK graph has cycles; order broken at: ${broken.join(', ')}`);
    console.log('(handled by disabling FKs for the load and re-validating afterwards)\n');
  }

  // ── disable FKs ───────────────────────────────────────────────────────────
  if (!DRY_RUN) {
    let off = 0;
    for (const f of fks) {
      try { await db.query(`ALTER TABLE ${f.child} DISABLE CONSTRAINT ${f.name}`); off++; } catch (e) {
        console.error(`  ! could not disable ${f.name}: ${e.message.split('\n')[0]}`);
      }
    }
    console.log(`disabled ${off}/${fks.length} FK constraints for the load`);
  }

  // ── optional truncate (FKs are already disabled, so order does not matter) ─
  if (TRUNCATE && !DRY_RUN) {
    let cleared = 0;
    for (const t of order) {
      if (SKIP_TABLES.has(t)) continue;
      const T = oraTable(t);
      if (!oraCols[T]) continue;
      try { await db.query(`TRUNCATE TABLE ${T}`); cleared++; }
      catch { try { await db.query(`DELETE FROM ${T}`); cleared++; } catch (e2) {
        console.error(`  ! could not clear ${T}: ${e2.message.split('\n')[0]}`);
      } }
    }
    console.log(`cleared ${cleared} target tables (--truncate)`);
  }
  console.log('');

  // ── copy ──────────────────────────────────────────────────────────────────
  const results = [];
  console.log('table                                    neon   oracle  match');
  console.log('─'.repeat(66));

  for (const t of order) {
    const T = oraTable(t);
    if (SKIP_TABLES.has(t)) { results.push({ t, skipped: true }); continue; }
    if (!oraCols[T]) { results.push({ t, error: 'no such table in Oracle' }); continue; }

    let neonCount = 0;
    try {
      neonCount = Number((await pg.query(`SELECT COUNT(*) AS n FROM "${t}"`)).rows[0].n);
      if (neonCount === 0) {
        results.push({ t, neon: 0, oracle: 0, match: true, empty: true });
        console.log(`${t.padEnd(38)} ${String(0).padStart(6)} ${String(0).padStart(8)}  ok (empty)`);
        continue;
      }

      const src = await pg.query(`SELECT * FROM "${t}"`);
      const neonColNames = src.fields.map((f) => f.name);
      const usable = neonColNames.filter((c) => oraCols[T].has(oraColumn(t, c)));
      const dropped = neonColNames.filter((c) => !oraCols[T].has(oraColumn(t, c)));
      if (dropped.length) console.log(`    ! ${t}: no Oracle column for ${dropped.join(', ')} — skipped`);

      const oraNames = usable.map((c) => oraColumn(t, c));
      const types = oraNames.map((c) => oraCols[T].get(c).type);
      const sql = `INSERT INTO ${T} (${oraNames.join(', ')}) VALUES (${oraNames.map((_, i) => `:b${i}`).join(', ')})`;

      let inserted = 0;
      if (!DRY_RUN) {
        for (let i = 0; i < src.rows.length; i += BATCH) {
          const slice = src.rows.slice(i, i + BATCH);
          const binds = slice.map((row) => {
            const o = {};
            usable.forEach((c, n) => { o[`b${n}`] = coerce(row[c], types[n]); });
            return o;
          });
          // maxSize must cover the widest value in THIS batch, measured in bytes.
          const bindDefs = {};
          oraNames.forEach((c, n) => {
            let widest = 1;
            if (!/JSON|CLOB|BLOB|BOOLEAN|NUMBER|DATE|TIMESTAMP|VECTOR/.test(types[n])) {
              for (const b of binds) {
                const v = b[`b${n}`];
                if (typeof v === 'string') widest = Math.max(widest, Buffer.byteLength(v, 'utf8'));
              }
            }
            bindDefs[`b${n}`] = bindDefFor(types[n], widest);
          });
          const r = await db.getConnection().then(async (conn) => {
            try {
              const res = await conn.executeMany(sql, binds, { bindDefs, autoCommit: true });
              return res.rowsAffected;
            } finally { await conn.close(); }
          });
          inserted += r;
        }
      }

      const oracleCount = DRY_RUN ? 0
        : Number((await db.query(`SELECT COUNT(*) AS n FROM ${T}`)).rows[0].n);
      const match = DRY_RUN ? null : oracleCount === neonCount;
      results.push({ t, neon: neonCount, oracle: oracleCount, inserted, match });
      console.log(`${t.padEnd(38)} ${String(neonCount).padStart(6)} ${String(oracleCount).padStart(8)}  ${match ? 'yes' : 'NO'}`);
    } catch (e) {
      const msg = e.message.split('\n')[0];
      results.push({ t, neon: neonCount, oracle: null, match: false, error: msg });
      console.log(`${t.padEnd(38)} ${String(neonCount).padStart(6)} ${'—'.padStart(8)}  FAILED`);
      console.log(`    ${msg}`);
    }
  }

  // ── re-enable FKs (VALIDATE proves referential integrity) ─────────────────
  const fkFailures = [];
  if (!DRY_RUN) {
    console.log('\nre-enabling FK constraints with VALIDATE…');
    for (const f of fks) {
      try { await db.query(`ALTER TABLE ${f.child} ENABLE VALIDATE CONSTRAINT ${f.name}`); } catch (e) {
        fkFailures.push({ name: f.name, child: f.child, err: e.message.split('\n')[0] });
      }
    }
    console.log(`  ${fks.length - fkFailures.length}/${fks.length} enabled+validated`);
    for (const f of fkFailures) console.log(`  ! ${f.child}.${f.name}: ${f.err}`);
  }

  // ── identity sequences ────────────────────────────────────────────────────
  const seqFixed = DRY_RUN ? [] : await resetIdentitySequences(oraCols);

  // ── summary ───────────────────────────────────────────────────────────────
  const matched = results.filter((r) => r.match === true);
  const failed = results.filter((r) => r.error || r.match === false);
  const skipped = results.filter((r) => r.skipped);
  const rows = matched.reduce((a, r) => a + (r.oracle || 0), 0);

  console.log('\n' + '═'.repeat(66));
  console.log(`SUMMARY  (${((Date.now() - t0) / 1000).toFixed(1)}s)`);
  console.log(`  tables matched : ${matched.length}   (${matched.filter((r) => r.empty).length} were empty)`);
  console.log(`  tables failed  : ${failed.length}`);
  console.log(`  tables skipped : ${skipped.length}  ${skipped.map((r) => r.t).join(', ')}`);
  console.log(`  rows copied    : ${rows}`);
  console.log(`  FK re-validate : ${fkFailures.length ? `${fkFailures.length} FAILED` : 'all clean'}`);
  console.log(`  sequences reset: ${seqFixed.length}`);
  for (const f of failed) console.log(`  ! ${f.t}: ${f.error || `count mismatch neon=${f.neon} oracle=${f.oracle}`}`);

  await pg.end();
  await db.close(0);
  process.exit(failed.length || fkFailures.length ? 2 : 0);
})().catch(async (e) => {
  console.error('FATAL:', e.message);
  await pg.end().catch(() => {});
  await db.close(0).catch(() => {});
  process.exit(1);
});

/**
 * Identity columns are GENERATED BY DEFAULT, so explicit IDs load fine but leave
 * the sequence at 1. Restart each at max(id)+1 or the first app insert collides.
 */
async function resetIdentitySequences(oraCols) {
  const idents = (await db.query(
    `SELECT table_name, column_name FROM user_tab_identity_cols`)).rows;
  const fixed = [];
  for (const r of idents) {
    try {
      const m = await db.query(`SELECT NVL(MAX(${r.column_name}), 0) + 1 AS nxt FROM ${r.table_name}`);
      const nxt = Number(m.rows[0].nxt);
      await db.query(
        `ALTER TABLE ${r.table_name} MODIFY (${r.column_name} GENERATED BY DEFAULT AS IDENTITY (START WITH ${nxt}))`);
      fixed.push(`${r.table_name}.${r.column_name}=${nxt}`);
    } catch (e) {
      console.error(`  ! sequence reset failed for ${r.table_name}.${r.column_name}: ${e.message.split('\n')[0]}`);
    }
  }
  return fixed;
}
