/**
 * db/agent-diagnose.js — agent_diagnose_runs CRUD.
 *
 * Owns: insert + read operations for agent diagnose run records.
 * Does NOT own: connection ownership checks, API key validation (routes/agent.js).
 */

'use strict';

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

/**
 * Insert a new diagnose run row.
 *
 * @param {object} data
 * @param {number} data.connectionId
 * @param {string} [data.agentVersion]
 * @param {string} [data.host]
 * @param {string[]} [data.detectedSids]
 * @param {string[]} [data.listenerServices]
 * @param {string} [data.chosenService]
 * @param {Array}  [data.probes]        — [{id, name, status, detail}]
 * @param {number} [data.roundtripMs]
 * @returns {Promise<object>} inserted row
 */
async function insertDiagnoseRun(data) {
  const {
    connectionId,
    agentVersion = null,
    host = null,
    detectedSids = [],
    listenerServices = [],
    chosenService = null,
    probes = [],
    roundtripMs = null,
  } = data;

  const probes_arr = Array.isArray(probes) ? probes : [];
  const passCount = probes_arr.filter(p => p.status === 'pass').length;
  const failCount = probes_arr.filter(p => p.status === 'fail').length;
  const skipCount = probes_arr.filter(p => p.status === 'skip').length;
  const overallStatus = failCount > 0 ? 'fail' : 'pass';

  // detected_sids and listener_services were text[] in Postgres (a JS array bound
  // directly); they are JSON columns here, so they must be serialised like every
  // other JSON payload. Reads still come back as JS arrays, so callers are unchanged.
  const ins = await pool.query(
    `INSERT INTO agent_diagnose_runs
       (connection_id, agent_version, host, detected_sids, listener_services, chosen_service,
        probes, roundtrip_ms, overall_status, pass_count, fail_count, skip_count)
     VALUES (:p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9, :p10, :p11, :p12)
     RETURNING id INTO :out_id`,
    {
      p1: connectionId,
      p2: agentVersion,
      p3: host,
      p4: JSON.stringify(detectedSids || []),
      p5: JSON.stringify(listenerServices || []),
      p6: chosenService,
      p7: JSON.stringify(probes_arr),
      p8: roundtripMs,
      p9: overallStatus,
      p10: passCount,
      p11: failCount,
      p12: skipCount,
      out_id: pool.outNumber(),
    }
  );
  const res = await pool.query(
    `SELECT * FROM agent_diagnose_runs WHERE id = :p1`,
    { p1: ins.outBinds.out_id[0] }
  );
  return res.rows[0];
}

/**
 * Get the latest diagnose run for a connection.
 * Returns null if no runs exist.
 */
async function getLatestDiagnoseRun(connectionId) {
  const res = await pool.query(
    `SELECT * FROM agent_diagnose_runs
     WHERE connection_id = :p1
     ORDER BY created_at DESC
     FETCH FIRST 1 ROWS ONLY`,
    { p1: connectionId }
  );
  return res.rows[0] || null;
}

/**
 * Get the latest diagnose runs for multiple connections in one query.
 * Returns a map of connectionId -> row.
 *
 * @param {number[]} connectionIds
 * @returns {Promise<Record<number, object>>}
 */
async function getLatestDiagnoseRunsForConnections(connectionIds) {
  if (!connectionIds.length) return {};
  // DISTINCT ON (connection_id) ... ORDER BY connection_id, created_at DESC
  // -> ROW_NUMBER() OVER (PARTITION BY connection_id ORDER BY created_at DESC) = 1.
  // The wrapper adds an `rn` column to the result; callers only read named fields,
  // so it is left in rather than enumerating columns (which would drift as
  // ensureColumns() adds them).
  const binds = {};
  const inList = connectionIds.map((id, i) => { binds[`p${i}`] = id; return `:p${i}`; }).join(', ');
  const res = await pool.query(
    `SELECT * FROM (
       SELECT t.*, ROW_NUMBER() OVER (PARTITION BY connection_id ORDER BY created_at DESC) AS rn
         FROM agent_diagnose_runs t
        WHERE connection_id IN (${inList})
     ) WHERE rn = 1`,
    binds
  );
  const map = {};
  for (const row of res.rows) {
    map[row.connection_id] = row;
  }
  return map;
}

/**
 * List recent diagnose runs for a connection (for history view).
 * Returns up to `limit` rows, newest first.
 */
async function listDiagnoseRuns(connectionId, limit = 20) {
  const res = await pool.query(
    `SELECT * FROM agent_diagnose_runs
     WHERE connection_id = :p1
     ORDER BY created_at DESC
     FETCH FIRST :p2 ROWS ONLY`,
    { p1: connectionId, p2: limit }
  );
  return res.rows;
}

module.exports = {
  insertDiagnoseRun,
  getLatestDiagnoseRun,
  getLatestDiagnoseRunsForConnections,
  listDiagnoseRuns,
};
