/**
 * db/ebs-deep-reports.js — PostgreSQL queries for Deep EBS Health Reports.
 *
 * Owns: CRUD on the ebs_deep_reports table.
 * Does NOT own: Oracle query execution, AI analysis, auth, or Pool construction.
 */

'use strict';

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

/**
 * createEbsDeepReport — insert a new Deep EBS report row.
 *
 * @param {object} params
 * @param {number} params.userId
 * @param {number} params.connectionId
 * @param {string} params.connectionName
 * @param {object} params.findingsJson
 * @param {string|null} params.aiAnalysis
 * @param {boolean} [params.isDemo]
 * @returns {Promise<{id: number}>}
 */
async function createEbsDeepReport({ userId, connectionId, connectionName, findingsJson, aiAnalysis = null, isDemo = false }) {
  // findings_json is a full Deep EBS result set and ai_analysis is generated
  // prose — both can exceed the 32767-byte VARCHAR2 bind ceiling, so both are
  // bound as CLOBs.
  const ins = await pool.query(
    `INSERT INTO ebs_deep_reports (user_id, connection_id, connection_name, findings_json, ai_analysis, is_demo)
     VALUES (:p1, :p2, :p3, :p4, :p5, :p6)
     RETURNING id INTO :out_id`,
    { p1: userId, p2: connectionId, p3: connectionName,
      p4: pool.clob(JSON.stringify(findingsJson)),
      p5: pool.clob(aiAnalysis), p6: pool.bool(isDemo),
      out_id: pool.outNumber() }
  );
  const result = await pool.query(
    `SELECT id, created_at FROM ebs_deep_reports WHERE id = :p1`,
    { p1: ins.outBinds.out_id[0] }
  );
  return result.rows[0];
}

/**
 * getEbsDeepReport — fetch a single Deep EBS report by id.
 * Returns null if not found.
 *
 * @param {number} reportId
 * @param {number|null} userId  Pass null to skip ownership check (admin / demo access).
 * @returns {Promise<object|null>}
 */
async function getEbsDeepReport(reportId, userId = null) {
  let query, params;
  if (userId !== null) {
    query = `SELECT r.*, oc.name AS connection_name_live
             FROM ebs_deep_reports r
             LEFT JOIN oracle_connections oc ON oc.id = r.connection_id
             WHERE r.id = :p1 AND (r.user_id = :p2 OR r.is_demo = TRUE)`;
    params = { p1: reportId, p2: userId };
  } else {
    query = `SELECT r.*, oc.name AS connection_name_live
             FROM ebs_deep_reports r
             LEFT JOIN oracle_connections oc ON oc.id = r.connection_id
             WHERE r.id = :p1`;
    params = { p1: reportId };
  }
  const result = await pool.query(query, params);
  return result.rows[0] || null;
}

/**
 * getDemoEbsDeepReport — find the most recent demo report row.
 * Used to serve /report/ebs/demo without requiring auth.
 *
 * @returns {Promise<object|null>}
 */
async function getDemoEbsDeepReport() {
  const result = await pool.query(
    `SELECT * FROM ebs_deep_reports WHERE is_demo = TRUE ORDER BY created_at DESC FETCH FIRST 1 ROWS ONLY`
  );
  return result.rows[0] || null;
}

/**
 * updateEbsDeepReportAi — patch ai_analysis on an existing row once the AI call completes.
 *
 * @param {number} reportId
 * @param {string} aiAnalysis
 */
async function updateEbsDeepReportAi(reportId, aiAnalysis) {
  await pool.query(
    `UPDATE ebs_deep_reports SET ai_analysis = :p2 WHERE id = :p1`,
    { p1: reportId, p2: pool.clob(aiAnalysis) }
  );
}

module.exports = { createEbsDeepReport, getEbsDeepReport, getDemoEbsDeepReport, updateEbsDeepReportAi };
