/**
 * db/oracle-client.js — connection pool for TuneVault's OWN application database
 * on Oracle Autonomous DB 26ai (OCI, ap-hyderabad-1).
 *
 * NOT to be confused with the repo-root `oracle-client.js`, which connects to
 * CUSTOMER Oracle databases to run health checks. Different purpose, different
 * credentials, different lifecycle:
 *   /oracle-client.js      → customer DBs being monitored (many, transient)
 *   /db/oracle-client.js   → TuneVault's own app data (one, long-lived)  ← this file
 *
 * This is the Oracle-side counterpart of db/index.js (the Postgres/Neon Pool).
 * MIGRATION STATUS: parallel build. Nothing imports this yet; Neon remains the
 * live application database until the Cloudflare DNS cutover. Do not wire this
 * into server.js or any db/ entity module until the schema has been ported.
 *
 * Thin mode: node-oracledb runs thin by default (no Oracle Instant Client). Thin
 * mode requires the PEM wallet (ewallet.pem) — it cannot read cwallet.sso — and
 * ADB ships that PEM with an ENCRYPTED PRIVATE KEY, so ORACLE_ADB_WALLET_PASSWORD
 * is required, not optional.
 */

const oracledb = require('oracledb');

// Rows as plain objects ({col: val}), matching the shape pg returns in rows[].
oracledb.outFormat = oracledb.OUT_FORMAT_OBJECT;
// CLOBs/BLOBs as JS string/Buffer rather than lazy Lob streams — the app's
// query helpers assume materialized values (JSONB columns will land as CLOBs).
oracledb.fetchAsString = [oracledb.CLOB];

const DEFAULT_WALLET_DIR = 'E:\\Downloads\\OCI_Tunevault\\Wallet_tunevaultdb';

// Inline TNS descriptor for the tunevaultdb_tp service (matches tnsnames.ora in
// the wallet). Inlined deliberately: no dependency on TNS_ADMIN resolution.
const DEFAULT_CONNECT_STRING =
  '(description=(retry_count=20)(retry_delay=3)' +
  '(address=(protocol=tcps)(port=1522)(host=adb.ap-hyderabad-1.oraclecloud.com))' +
  '(connect_data=(service_name=g2ce1a85e5f6f51_tunevaultdb_tp.adb.oraclecloud.com))' +
  '(security=(ssl_server_dn_match=yes)))';

const config = {
  user: process.env.ORACLE_ADB_USER || 'ADMIN',
  password: process.env.ORACLE_ADB_PASSWORD,
  connectString: process.env.ORACLE_ADB_CONNECT_STRING || DEFAULT_CONNECT_STRING,

  // Thin-mode mTLS: directory holding ewallet.pem, plus the password that
  // decrypts its private key (set when the wallet was downloaded from OCI).
  walletLocation: process.env.ORACLE_ADB_WALLET_DIR || DEFAULT_WALLET_DIR,
  walletPassword: process.env.ORACLE_ADB_WALLET_PASSWORD,
  configDir: process.env.ORACLE_ADB_WALLET_DIR || DEFAULT_WALLET_DIR,

  poolMin: Number(process.env.ORACLE_ADB_POOL_MIN || 0),
  poolMax: Number(process.env.ORACLE_ADB_POOL_MAX || 10),
  poolIncrement: 1,
  poolTimeout: 60,          // seconds an idle connection may sit above poolMin
  queueTimeout: 30000,      // ms a caller waits for a free connection
};

// ── Wallet delivery via env var (hosted environments with no wallet on disk) ──
// Render/OCI Compute have no wallet files, but thin mode needs ewallet.pem ON DISK.
// So we ship it as a base64 env var (ORACLE_WALLET_B64), materialize it to a fresh
// per-process temp dir at startup, and point walletLocation there — overriding the
// DEFAULT_WALLET_DIR / ORACLE_ADB_WALLET_DIR disk path. cwallet.sso
// (ORACLE_WALLET_SSO_B64) is optional: thin mode does not read it, but it is
// written when supplied so a thick-mode fallback or tooling still finds it.
if (process.env.ORACLE_WALLET_B64) {
  const fs = require('fs');
  const os = require('os');
  const path = require('path');
  const dir = fs.mkdtempSync(path.join(os.tmpdir(), 'tv-wallet-'));
  const write = (name, b64) =>
    fs.writeFileSync(path.join(dir, name), Buffer.from(b64, 'base64'), { mode: 0o600 });
  write('ewallet.pem', process.env.ORACLE_WALLET_B64);
  if (process.env.ORACLE_WALLET_SSO_B64) write('cwallet.sso', process.env.ORACLE_WALLET_SSO_B64);
  config.walletLocation = dir;
  config.configDir = dir;
}

let poolPromise = null;

/**
 * Lazily create (once) and return the pool.
 *
 * Deliberately NOT created at import time — unlike db/index.js, which throws on a
 * missing DATABASE_URL at require. During the parallel build this module may be
 * required by tooling on a machine with no ADB credentials, and that must not be
 * fatal. The failure surfaces on first use instead.
 */
function getPool() {
  if (!poolPromise) {
    if (!config.password) {
      return Promise.reject(new Error('ORACLE_ADB_PASSWORD is required (ADB user password)'));
    }
    if (!config.walletPassword) {
      return Promise.reject(new Error(
        'ORACLE_ADB_WALLET_PASSWORD is required — ADB ewallet.pem holds an encrypted private key ' +
        'and thin mode cannot use cwallet.sso'
      ));
    }
    poolPromise = oracledb.createPool(config).catch((err) => {
      poolPromise = null;   // let the next caller retry rather than caching the failure
      throw err;
    });
  }
  return poolPromise;
}

/**
 * Lowercase every result key.
 *
 * The Oracle schema uses UNQUOTED identifiers, so Oracle folds them to uppercase
 * and node-oracledb returns rows keyed USER_ID / CREATED_AT. Every call site in
 * this app reads row.user_id, so without this the whole data layer silently
 * returns undefined rather than failing loudly. Postgres returned lowercase, so
 * lowercasing here is what keeps ~72 db/ modules working unchanged.
 *
 * The key map is built once per result set from metaData and reused for every
 * row — this runs on every query, so it must not be a per-row Object.entries().
 * A column that is already lowercase (a quoted identifier, or an aliased
 * expression) passes through untouched.
 */
function lowercaseRows(result) {
  const rows = result.rows;
  if (!rows || !rows.length || !result.metaData) return rows || [];
  if (Array.isArray(rows[0])) return rows;          // OUT_FORMAT_ARRAY — nothing to key

  const from = result.metaData.map((m) => m.name);
  const to = from.map((n) => n.toLowerCase());
  if (from.every((n, i) => n === to[i])) return rows;   // already lowercase

  const n = from.length;
  const out = new Array(rows.length);
  for (let r = 0; r < rows.length; r++) {
    const src = rows[r];
    const dst = {};
    for (let c = 0; c < n; c++) dst[to[c]] = src[from[c]];
    out[r] = dst;
  }
  return out;
}

/**
 * Run a statement and return { rows, rowCount } — deliberately shaped like the
 * pg result object so call sites port with minimal churn. Row keys are
 * lowercased (see lowercaseRows) so row.user_id keeps working.
 *
 * Binds are Oracle-style (:name or :1), NOT Postgres $1. Nothing is rewritten
 * here; query translation is a later migration step.
 */
async function query(sql, binds = [], options = {}) {
  const pool = await getPool();
  const conn = await pool.getConnection();
  try {
    const result = await conn.execute(sql, binds, { autoCommit: true, ...options });
    const rows = lowercaseRows(result);
    return {
      rows,
      rowCount: result.rows ? rows.length : (result.rowsAffected || 0),
      metaData: result.metaData,
      // Needed by the `RETURNING id INTO :out_id` pattern that replaces
      // Postgres's RETURNING * — read as result.outBinds.out_id[0].
      outBinds: result.outBinds,
    };
  } finally {
    await conn.close();   // returns the connection to the pool
  }
}

/**
 * Borrow a connection for multi-statement work (transactions). Caller must close().
 *
 * NOTE: a raw connection bypasses query(), so its rows come back UPPERCASE-keyed.
 * Pass the execute() result through lowercaseRows() to get the same shape.
 */
async function getConnection() {
  const pool = await getPool();
  return pool.getConnection();
}

/** Drain the pool — for graceful shutdown and test teardown. */
async function close(drainSeconds = 10) {
  if (!poolPromise) return;
  const pool = await poolPromise.catch(() => null);
  poolPromise = null;
  if (pool) await pool.close(drainSeconds);
}

module.exports = { getPool, query, getConnection, lowercaseRows, close, oracledb, config };
