/**
 * db/oracle/migrate-vectors.js — copy oracle_doc_chunks (the RAG store) from
 * Neon/pgvector into Oracle 26ai's native VECTOR type.
 *
 *   node db/oracle/migrate-vectors.js [--batch=50] [--page=500] [--keep-index] [--resume]
 *
 * Split out of migrate-data.js because this one table is ~430 MB of embedding
 * text and needs handling the rest of the schema does not:
 *
 * - PAGED READS. pgvector returns each embedding as a ~19 KB TEXT literal
 *   ("[0.0015,...]"), so `SELECT *` over 22,276 rows would pull ~430 MB into one
 *   result set. Rows are read by keyset pagination (id > last ORDER BY id) so
 *   memory stays flat regardless of table size.
 *
 * - INDEX REBUILD. The IVF vector index was created when the table was EMPTY, so
 *   its centroids were derived from nothing. Loading under it is both slower and
 *   leaves a degenerate index. The index is dropped before the load and recreated
 *   afterwards so centroids are computed from the real 22k vectors. --keep-index
 *   skips that if you want to inspect the existing one.
 *
 * - TIMEOUTS AND RESUME. A run without them hung indefinitely partway through:
 *   Neon dropped the connection, and node-postgres has NO default query timeout,
 *   so the read never returned and never errored (Oracle's session sat idle on
 *   "SQL*Net message from client" for 11 minutes, proving the stall was upstream).
 *   Page reads now have a client and server side timeout plus TCP keepalive, and
 *   are retried. --resume continues from Oracle's highest loaded id instead of
 *   truncating, so an interrupted run does not start over.
 *
 * Verified bind path: pgvector text -> Float32Array -> DB_TYPE_VECTOR, read back
 * as Float32Array with VECTOR_DISTANCE(v, v, COSINE) = 0.
 */

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

const TABLE = 'oracle_doc_chunks';
const ORA = 'ORACLE_DOC_CHUNKS';
const VECTOR_INDEX = 'ORACLE_DOC_CHUNKS_EMBEDDING_IDX';
const CREATE_VECTOR_INDEX =
  `CREATE VECTOR INDEX ${VECTOR_INDEX} ON ${ORA} (EMBEDDING)
     ORGANIZATION NEIGHBOR PARTITIONS DISTANCE COSINE WITH TARGET ACCURACY 95`;

const args = process.argv.slice(2);
const argVal = (n, d) => {
  const a = args.find((x) => x.startsWith(`--${n}=`));
  return a ? Number(a.slice(n.length + 3)) : d;
};
const BATCH = argVal('batch', 50);
const PAGE = argVal('page', 500);
const KEEP_INDEX = args.includes('--keep-index');
const RESUME = args.includes('--resume');

// Timeouts are load-bearing, not hygiene: without query_timeout a dropped Neon
// connection hangs the read forever instead of raising, and the run stalls silently.
const pg = new Pool({
  connectionString: process.env.DATABASE_URL,
  ssl: { rejectUnauthorized: false },
  keepAlive: true,
  connectionTimeoutMillis: 20000,
  idleTimeoutMillis: 30000,
  query_timeout: 120000,                       // client side
  statement_timeout: 120000,                   // server side
});

/** Read one page, retrying transient drops (Neon suspends idle endpoints). */
async function readPage(sql, params, attempts = 3) {
  let lastErr;
  for (let i = 1; i <= attempts; i++) {
    try { return await pg.query(sql, params); }
    catch (e) {
      lastErr = e;
      console.log(`  ! page read attempt ${i}/${attempts} failed: ${e.message.split('\n')[0]}`);
      await new Promise((r) => setTimeout(r, 2000 * i));
    }
  }
  throw lastErr;
}

/** pgvector text literal -> Float32Array. */
function parseVector(text, dims) {
  if (text === null || text === undefined) return null;
  if (text instanceof Float32Array) return text;
  const arr = typeof text === 'string' ? JSON.parse(text) : text;
  if (!Array.isArray(arr)) throw new Error(`unexpected embedding shape: ${typeof text}`);
  if (dims && arr.length !== dims) throw new Error(`expected ${dims} dims, got ${arr.length}`);
  return Float32Array.from(arr);
}

(async () => {
  const t0 = Date.now();

  const expected = Number((await pg.query(`SELECT COUNT(*) AS n FROM ${TABLE}`)).rows[0].n);
  const already = Number((await db.query(`SELECT COUNT(*) AS n FROM ${ORA}`)).rows[0].n);
  console.log(`neon rows      : ${expected}`);
  console.log(`oracle rows    : ${already}`);
  console.log(`batch ${BATCH}, page ${PAGE}\n`);

  let startId = 0;
  if (RESUME && already > 0) {
    startId = Number((await db.query(`SELECT NVL(MAX(ID), 0) AS m FROM ${ORA}`)).rows[0].m);
    console.log(`resuming from id > ${startId} (${already} rows already loaded)`);
  } else if (already > 0) {
    console.log(`clearing ${already} existing row(s) so this run replaces rather than duplicates`);
    await db.query(`TRUNCATE TABLE ${ORA}`);
  }

  // Column types from Oracle itself, so bindDefs cannot drift from the schema.
  const cols = (await db.query(
    `SELECT column_name, data_type FROM user_tab_columns WHERE table_name = :1 ORDER BY column_id`,
    [ORA])).rows;
  const typeOf = Object.fromEntries(cols.map((c) => [c.column_name, c.data_type]));
  const NAMES = ['ID', 'DOC_NAME', 'CHUNK_INDEX', 'CONTENT', 'TOKEN_COUNT', 'EMBEDDING', 'CREATED_AT'];
  const SRC = ['id', 'doc_name', 'chunk_index', 'content', 'token_count', 'embedding', 'created_at'];

  const bindDefs = {};
  NAMES.forEach((n, i) => {
    const t = typeOf[n];
    if (t === 'VECTOR') bindDefs[`b${i}`] = { type: oracledb.DB_TYPE_VECTOR };
    else if (t === 'CLOB') bindDefs[`b${i}`] = { type: oracledb.DB_TYPE_CLOB };
    else if (t === 'NUMBER') bindDefs[`b${i}`] = { type: oracledb.DB_TYPE_NUMBER };
    else if (/^TIMESTAMP.*WITH TIME ZONE$/.test(t)) bindDefs[`b${i}`] = { type: oracledb.DB_TYPE_TIMESTAMP_TZ };
    else if (/^TIMESTAMP/.test(t)) bindDefs[`b${i}`] = { type: oracledb.DB_TYPE_TIMESTAMP };
    else bindDefs[`b${i}`] = { type: oracledb.DB_TYPE_VARCHAR, maxSize: 4000 };
  });
  const SQL = `INSERT INTO ${ORA} (${NAMES.join(', ')}) VALUES (${NAMES.map((_, i) => `:b${i}`).join(', ')})`;

  // ── drop the vector index so centroids are built from real data afterwards ──
  if (!KEEP_INDEX) {
    try { await db.query(`DROP INDEX ${VECTOR_INDEX}`); console.log('dropped vector index for the load'); }
    catch (e) { console.log(`vector index not dropped: ${e.message.split('\n')[0]}`); }
  }

  // ── paged copy ────────────────────────────────────────────────────────────
  let lastId = startId;
  let done = 0;
  let nextLog = 500;
  const failures = [];
  const conn = await db.getConnection();

  try {
    for (;;) {
      const page = await readPage(
        `SELECT ${SRC.map((c) => `"${c}"`).join(', ')} FROM ${TABLE}
          WHERE id > $1 ORDER BY id LIMIT $2`, [lastId, PAGE]);
      if (!page.rows.length) break;
      lastId = page.rows[page.rows.length - 1].id;

      for (let i = 0; i < page.rows.length; i += BATCH) {
        const slice = page.rows.slice(i, i + BATCH);
        let binds;
        try {
          binds = slice.map((row) => {
            const o = {};
            SRC.forEach((c, n) => {
              const v = row[c];
              if (NAMES[n] === 'EMBEDDING') o[`b${n}`] = parseVector(v, 1536);
              else if (typeOf[NAMES[n]] === 'NUMBER') o[`b${n}`] = v === null ? null : Number(v);
              else o[`b${n}`] = v === null ? undefined : v;
              if (o[`b${n}`] === undefined) o[`b${n}`] = null;
            });
            return o;
          });
        } catch (e) {
          failures.push({ ids: slice.map((r) => r.id), err: `parse: ${e.message}` });
          continue;
        }

        try {
          await conn.executeMany(SQL, binds, { bindDefs, autoCommit: true });
          done += slice.length;
        } catch (e) {
          failures.push({ ids: slice.map((r) => r.id), err: e.message.split('\n')[0] });
        }

        if (done >= nextLog) {
          const total = (RESUME ? already : 0) + done;      // rows in Oracle overall
          const pct = ((total / expected) * 100).toFixed(1);
          const rate = Math.round(done / ((Date.now() - t0) / 1000));
          const eta = rate > 0 ? Math.round((expected - total) / rate) : 0;
          console.log(`  ${String(total).padStart(6)} / ${expected}  (${pct}%)  ${rate} rows/s  eta ${eta}s`);
          nextLog = Math.floor(done / 500) * 500 + 500;
        }
      }
    }
  } finally {
    await conn.close();
  }

  console.log(`\ncopied ${done} rows in ${((Date.now() - t0) / 1000).toFixed(1)}s`);
  if (failures.length) {
    console.log(`FAILED batches: ${failures.length}`);
    for (const f of failures.slice(0, 10)) console.log(`  ids ${f.ids[0]}..${f.ids[f.ids.length - 1]}: ${f.err}`);
  }

  // ── rebuild the vector index on the loaded data ───────────────────────────
  if (!KEEP_INDEX) {
    const ti = Date.now();
    try {
      await db.query(CREATE_VECTOR_INDEX);
      console.log(`rebuilt vector index in ${((Date.now() - ti) / 1000).toFixed(1)}s`);
    } catch (e) { console.error(`vector index rebuild FAILED: ${e.message.split('\n')[0]}`); }
  }

  // ── verify ────────────────────────────────────────────────────────────────
  const final = Number((await db.query(`SELECT COUNT(*) AS n FROM ${ORA}`)).rows[0].n);
  const nulls = Number((await db.query(`SELECT COUNT(*) AS n FROM ${ORA} WHERE EMBEDDING IS NULL`)).rows[0].n);
  console.log(`\noracle COUNT(*) = ${final}   expected ${expected}   ${final === expected ? 'MATCH' : 'MISMATCH'}`);
  console.log(`rows with NULL embedding: ${nulls}`);

  await pg.end();
  await db.close(0);
  process.exit(final === expected && !failures.length ? 0 : 2);
})().catch(async (e) => {
  console.error('FATAL:', e.message);
  await pg.end().catch(() => {}); await db.close(0).catch(() => {});
  process.exit(1);
});
