/**
 * db/advisor-findings.js — PostgreSQL queries for the Performance Advisor panel.
 *
 * Owns: advisor_findings table CRUD — upsert and read per-connection
 *       Oracle advisor snapshots (ADDM tasks, findings, recommendations,
 *       SQL Tuning Advisor tasks, AI summary).
 * Does NOT own: Oracle query execution (routes/performance-advisor.js),
 *               oracle_connections storage, auth, Pool construction.
 */

'use strict';

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

/**
 * upsertAdvisorFindings — replace all advisor data for a connection.
 * One row per connection (UNIQUE on connection_id); fetch overwrites prior result.
 *
 * @param {object} params
 * @param {number}   params.connectionId
 * @param {Array}    params.addmTasks
 * @param {Array}    params.findings
 * @param {Array}    params.recommendations
 * @param {Array}    params.sqlTuningTasks
 * @param {string|null} params.aiSummary
 * @param {boolean}  params.licensed
 * @param {string|null} params.notLicensedReason
 * @param {string|null} params.fetchError
 * @returns {Promise<object>} the upserted row
 */
async function upsertAdvisorFindings({
  connectionId,
  addmTasks,
  findings,
  recommendations,
  sqlTuningTasks,
  aiSummary,
  licensed,
  notLicensedReason,
  fetchError,
}) {
  // ON CONFLICT DO UPDATE ... RETURNING * becomes MERGE + a read-back: Oracle's
  // MERGE has no RETURNING clause at all. The read-back is keyed on
  // connection_id, which is the UNIQUE column the upsert conflicts on, so no
  // OUT bind is needed here.
  // The four JSON payloads are bound as CLOBs — inside MERGE ... USING there is
  // no target column for the driver to infer from, and findings/recommendations
  // routinely exceed the 32767-byte VARCHAR2 bind ceiling.
  await pool.query(
    `MERGE INTO advisor_findings t
     USING (SELECT :p1 AS connection_id FROM DUAL) s
        ON (t.connection_id = s.connection_id)
     WHEN MATCHED THEN UPDATE SET
       t.fetched_at          = CURRENT_TIMESTAMP,
       t.addm_tasks          = :p2,
       t.findings            = :p3,
       t.recommendations     = :p4,
       t.sql_tuning_tasks    = :p5,
       t.ai_summary          = :p6,
       t.licensed            = :p7,
       t.not_licensed_reason = :p8,
       t.fetch_error         = :p9
     WHEN NOT MATCHED THEN INSERT
       (connection_id, fetched_at, addm_tasks, findings, recommendations,
        sql_tuning_tasks, ai_summary, licensed, not_licensed_reason, fetch_error)
       VALUES (s.connection_id, CURRENT_TIMESTAMP, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9)`,
    {
      p1: connectionId,
      p2: pool.clob(JSON.stringify(addmTasks || [])),
      p3: pool.clob(JSON.stringify(findings || [])),
      p4: pool.clob(JSON.stringify(recommendations || [])),
      p5: pool.clob(JSON.stringify(sqlTuningTasks || [])),
      p6: aiSummary || null,
      p7: licensed !== false,
      p8: notLicensedReason || null,
      p9: fetchError || null,
    }
  );
  const result = await pool.query(
    `SELECT * FROM advisor_findings WHERE connection_id = :p1`,
    { p1: connectionId }
  );
  return result.rows[0];
}

/**
 * getAdvisorFindings — load cached advisor findings for a connection.
 * Returns null if no row exists yet.
 *
 * @param {number} connectionId
 * @returns {Promise<object|null>}
 */
async function getAdvisorFindings(connectionId) {
  const result = await pool.query(
    `SELECT * FROM advisor_findings WHERE connection_id = :p1`,
    { p1: connectionId }
  );
  return result.rows[0] || null;
}

/**
 * getConnectionForAdvisor — load an oracle_connection (ownership-checked).
 * Returns null if not found or not owned by userId.
 *
 * @param {number} connId
 * @param {number} userId
 * @returns {Promise<object|null>}
 */
async function getConnectionForAdvisor(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;
}

/**
 * getUserForAdvisor — load a user row by id (used by auth middleware).
 *
 * @param {number} userId
 * @returns {Promise<{id, email}|null>}
 */
async function getUserForAdvisor(userId) {
  const result = await pool.query('SELECT id, email FROM users WHERE id = :p1', { p1: userId });
  return result.rows[0] || null;
}

module.exports = {
  upsertAdvisorFindings,
  getAdvisorFindings,
  getConnectionForAdvisor,
  getUserForAdvisor,
};
