/**
 * db/index.js — the single database seam for the application.
 *
 * Owns: connection pool construction and the pool-shaped interface that the ~70
 * entity modules in db/ consume (`pool.query(sql, params)` and, for the three
 * transactional modules, `pool.connect()` -> `client.query` / `client.release`).
 * Does NOT own: query logic (see the entity files in db/).
 *
 * MIGRATION (Neon/Postgres -> Oracle ADB 26ai). This module is the switch:
 *
 *     TUNEVAULT_DB unset | 'postgres'  -> Neon (default; unchanged behaviour)
 *     TUNEVAULT_DB = 'oracle'          -> Oracle ADB via db/oracle-client.js
 *
 * Selecting the backend with an env var rather than a code change is deliberate:
 * the DNS cutover becomes a config flip, and rolling back is the same flip rather
 * than a revert-and-redeploy. Nothing else in db/ needs to know which engine it
 * is talking to.
 *
 * IMPORTANT: the entity modules still contain PostgreSQL dialect SQL. Setting
 * TUNEVAULT_DB=oracle is only safe once those are translated — the switch exists
 * so that work can land without touching this file again.
 */

const USE_ORACLE = (process.env.TUNEVAULT_DB || 'postgres').toLowerCase() === 'oracle';

if (!USE_ORACLE) {
  // ── PostgreSQL (Neon) — the live path, behaviour unchanged ────────────────
  const { Pool } = require('pg');

  if (!process.env.DATABASE_URL) {
    // Fail loudly at import time so misconfiguration surfaces immediately
    throw new Error('DATABASE_URL is required');
  }

  const pool = new Pool({
    connectionString: process.env.DATABASE_URL,
    ssl: process.env.DATABASE_URL.includes('localhost') ? false : { rejectUnauthorized: false }
  });

  module.exports = pool;
} else {
  // ── Oracle ADB — same surface, different engine ───────────────────────────
  const oracle = require('./oracle-client');

  /**
   * A pg-Client-shaped wrapper over one pooled Oracle connection, for the modules
   * that do pool.connect() / BEGIN / COMMIT / client.release().
   *
   * Transactions matter here: oracle-client's query() runs with autoCommit=true,
   * so a transaction MUST hold its own connection. BEGIN is a no-op because
   * Oracle opens a transaction implicitly on the first DML.
   */
  function wrapClient(conn) {
    return {
      async query(sql, params = []) {
        const trimmed = String(sql).trim().toUpperCase();
        if (trimmed === 'BEGIN') return { rows: [], rowCount: 0 };   // implicit in Oracle
        if (trimmed === 'COMMIT') { await conn.commit(); return { rows: [], rowCount: 0 }; }
        if (trimmed === 'ROLLBACK') { await conn.rollback(); return { rows: [], rowCount: 0 }; }
        const result = await conn.execute(sql, params, { autoCommit: false });
        const rows = oracle.lowercaseRows(result);
        return { rows, rowCount: result.rows ? rows.length : (result.rowsAffected || 0) };
      },
      release() { conn.close().catch(() => {}); },
    };
  }

  module.exports = {
    /** Same shape as pg's pool.query — { rows, rowCount }, lowercase keys. */
    query: (sql, params = [], options = {}) => oracle.query(sql, params, options),
    /**
     * OUT-bind descriptor for `RETURNING id INTO :out_id`.
     *
     * Oracle has no `RETURNING *`, so the agreed pattern is: return the generated
     * id into an OUT bind, then SELECT the row back. Exposed here so the ~23
     * modules that used RETURNING * do not each have to import the driver.
     * Read the value as `result.outBinds.out_id[0]`.
     */
    outNumber: () => ({ dir: oracle.oracledb.BIND_OUT, type: oracle.oracledb.NUMBER }),
    outString: (maxSize = 4000) => ({ dir: oracle.oracledb.BIND_OUT, type: oracle.oracledb.STRING, maxSize }),
    /**
     * Bind a string as a CLOB.
     *
     * A plain string bind is VARCHAR2 and caps at 32767 bytes. The driver infers
     * CLOB from the target column on a plain INSERT/UPDATE, but NOT inside
     * `MERGE ... USING (SELECT :bind FROM DUAL)`, where the bind has no target
     * column — so long text there must say so explicitly.
     */
    clob: (value) => ({ val: value == null ? null : String(value), type: oracle.oracledb.DB_TYPE_CLOB }),
    /**
     * Bind a value as TIMESTAMP WITH TIME ZONE.
     *
     * Needed wherever a possibly-null timestamp bind meets a timestamp column in
     * COALESCE/CASE: an untyped null binds as CHAR, and Oracle refuses to mix
     * CHAR with TIMESTAMP in those expressions (ORA-00932). Accepts a Date or an
     * ISO string; anything unparseable becomes NULL rather than a bad date.
     */
    /** Bind as BOOLEAN — same ORA-00932 reason as tstz(), for BOOLEAN columns. */
    bool: (value) => ({
      val: value == null ? null : !!value,
      type: oracle.oracledb.DB_TYPE_BOOLEAN,
    }),
    /** Bind as NUMBER — same ORA-00932 reason as tstz(), for NUMBER columns. */
    num: (value) => ({
      val: value == null || value === '' || Number.isNaN(Number(value)) ? null : Number(value),
      type: oracle.oracledb.DB_TYPE_NUMBER,
    }),
    tstz: (value) => {
      let d = null;
      if (value instanceof Date) d = value;
      else if (value != null && value !== '') { const p = new Date(value); if (!isNaN(p.getTime())) d = p; }
      return { val: d, type: oracle.oracledb.DB_TYPE_TIMESTAMP_TZ };
    },
    /**
     * Bind a JS number[] as a native VECTOR(…, FLOAT32).
     *
     * The doc-chunk column is VECTOR(1536, FLOAT32); a plain JS array binds as
     * FLOAT64 and ORA-51812s inside VECTOR_DISTANCE ("dimension formats (FLOAT32,
     * FLOAT64)"), so the array is coerced to a Float32Array to match the column
     * format. Same path the vector migration used on the write side.
     */
    vector: (arr) => ({
      val: arr == null ? null : Float32Array.from(arr),
      type: oracle.oracledb.DB_TYPE_VECTOR,
    }),
    /** Same shape as pg's pool.connect — a client with query()/release(). */
    connect: async () => wrapClient(await oracle.getConnection()),
    /** Escape hatch for code that needs the raw driver connection. */
    getConnection: () => oracle.getConnection(),
    end: (drainSeconds) => oracle.close(drainSeconds),
    oracledb: oracle.oracledb,
    _engine: 'oracle',
  };
}
