/**
 * db/addm-runs.js — PostgreSQL queries for the ADDM run history.
 *
 * Owns: addm_runs table CRUD — inserting new run records, reading history
 *       per-connection, and loading a single run for detail view.
 * Does NOT own: Oracle query execution, auth, Pool construction,
 *               connection credential storage.
 */

'use strict';

const pool = require('./index');

/**
 * insertAddmRun — persist a completed ADDM execution result.
 *
 * @param {object} p
 * @param {number}   p.connectionId
 * @param {number}   p.createdBy       — user id
 * @param {string|null} p.taskName
 * @param {number|null} p.taskId
 * @param {string|null} p.dbId
 * @param {string|null} p.container
 * @param {number|null} p.beginSnapId
 * @param {number|null} p.endSnapId
 * @param {string|null} p.beginSnapTime
 * @param {string|null} p.endSnapTime
 * @param {number|null} p.dbTimeSeconds
 * @param {number|null} p.avgActiveSessions
 * @param {Array}    p.findings
 * @param {string|null} p.rawReportText
 * @param {string|null} p.aiCommentary
 * @param {boolean}  p.isIdle
 * @param {string|null} p.runError
 * @param {number|null} p.snapshotMs
 * @param {number|null} p.analysisMs
 * @param {number|null} p.totalMs
 * @returns {Promise<object>} inserted row
 */
async function insertAddmRun(p) {
  // Oracle has no RETURNING * — the generated id comes back through an OUT bind
  // and the row is read back by id. Enumerating every column as an OUT bind was
  // rejected deliberately: ensureColumns() adds columns at runtime, so a hand
  // written list would silently drift out of date.
  const ins = await pool.query(
    `INSERT INTO addm_runs
       (connection_id, created_by, task_name, task_id, db_id, container,
        begin_snap_id, end_snap_id, begin_snap_time, end_snap_time,
        db_time_seconds, avg_active_sessions, findings, raw_report_text,
        ai_commentary, is_idle, run_error, snapshot_ms, analysis_ms, total_ms)
     VALUES
       (:p1,:p2,:p3,:p4,:p5,:p6,:p7,:p8,:p9,:p10,:p11,:p12,:p13,:p14,:p15,:p16,:p17,:p18,:p19,:p20)
     RETURNING id INTO :out_id`,
    {
      p1:  p.connectionId,
      p2:  p.createdBy,
      p3:  p.taskName       || null,
      p4:  p.taskId         || null,
      p5:  p.dbId           || null,
      p6:  p.container      || null,
      p7:  p.beginSnapId    || null,
      p8:  p.endSnapId      || null,
      p9:  p.beginSnapTime  || null,
      p10: p.endSnapTime    || null,
      p11: p.dbTimeSeconds  != null ? p.dbTimeSeconds : null,
      p12: p.avgActiveSessions != null ? p.avgActiveSessions : null,
      // findings is a JSON column — a JS array cannot be bound directly (NJS-044)
      p13: JSON.stringify(p.findings || []),
      p14: p.rawReportText  || null,
      p15: p.aiCommentary   || null,
      p16: p.isIdle         ? true : false,
      p17: p.runError       || null,
      p18: p.snapshotMs     || null,
      p19: p.analysisMs     || null,
      p20: p.totalMs        || null,
      out_id: pool.outNumber(),
    }
  );
  const id = ins.outBinds.out_id[0];
  const result = await pool.query(`SELECT * FROM addm_runs WHERE id = :p1`, { p1: id });
  return result.rows[0];
}

/**
 * getAddmRuns — most recent N runs for a connection.
 *
 * @param {number} connectionId
 * @param {number} [limit=20]
 * @returns {Promise<Array>}
 */
async function getAddmRuns(connectionId, limit = 20) {
  const result = await pool.query(
    // jsonb_array_length(x) -> JSON_VALUE(x, '$.size()' RETURNING NUMBER)
    `SELECT id, created_at, task_name, task_id, container,
            begin_snap_id, end_snap_id, begin_snap_time, end_snap_time,
            db_time_seconds, avg_active_sessions, is_idle, run_error,
            total_ms,
            JSON_VALUE(findings, '$.size()' RETURNING NUMBER) AS finding_count
     FROM addm_runs
     WHERE connection_id = :p1
     ORDER BY created_at DESC
     FETCH FIRST :p2 ROWS ONLY`,
    { p1: connectionId, p2: limit }
  );
  return result.rows;
}

/**
 * getAddmRun — single run by id (ownership-enforced via connection join).
 *
 * @param {number} runId
 * @param {number} userId
 * @returns {Promise<object|null>}
 */
async function getAddmRun(runId, userId) {
  const result = await pool.query(
    `SELECT r.*
     FROM addm_runs r
     JOIN oracle_connections c ON c.id = r.connection_id
     WHERE r.id = :p1 AND c.user_id = :p2`,
    { p1: runId, p2: userId }
  );
  return result.rows[0] || null;
}

/**
 * getConnectionForAddm — load an oracle_connection (ownership-checked).
 *
 * @param {number} connId
 * @param {number} userId
 * @returns {Promise<object|null>}
 */
async function getConnectionForAddm(connId, userId) {
  const result = await pool.query(
    `SELECT id, host, port, service_name, username, encrypted_password,
            connection_type, proxy_url, proxy_api_key_enc
     FROM oracle_connections
     WHERE id = :p1 AND user_id = :p2`,
    { p1: connId, p2: userId }
  );
  return result.rows[0] || null;
}

module.exports = {
  insertAddmRun,
  getAddmRuns,
  getAddmRun,
  getConnectionForAddm,
};
