/**
 * db/connection-health.js — connection_health_runs persistence.
 *
 * Owns: connection_health_runs (one row per diagnostic run per connection).
 * Does NOT own: oracle_connections CRUD, agent_tunnels, probe execution logic.
 *
 * Health status is derived at read time from the most recent run:
 *   green  = ran_at within 15 min AND passed === total
 *   yellow = ran_at within 60 min OR only probe 5–7 failing (non-critical)
 *   red    = ran_at > 60 min ago OR probe 1–4 failing
 *   unknown= no run recorded
 */

'use strict';

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

// ── Probe names (for tooltip labels in the UI) ────────────────────────────────

const PROBE_NAMES = [
  'Agent online',
  'SSH bastion hop',
  'TNS listener responds',
  'Auth + SELECT_CATALOG_ROLE',
  'Sample query latency',
  'End-to-end health check',
  'Proxy version current',
  'Key matches cloud',
];

// ── Insert a completed diagnostic run ─────────────────────────────────────────

async function insertHealthRun({ connectionId, probes, passed, total, agentVersion, agentUptimeS, trigger }) {
  // probes = array of { id, name, status, detail, ms } (indices 1–8)
  const findProbe = (id) => probes.find(p => p.id === id) || {};
  const p1 = findProbe(1), p2 = findProbe(2), p3 = findProbe(3);
  const p4 = findProbe(4), p5 = findProbe(5), p6 = findProbe(6);
  const p7 = findProbe(7), p8 = findProbe(8);

  // 'trigger' was renamed to 'run_trigger' during the schema port — TRIGGER is an
  // Oracle reserved word and this port carries no quoted identifiers.
  // CALLERS still pass { trigger }, so only the COLUMN name changes here.
  const ins = await pool.query(
    `INSERT INTO connection_health_runs
       (connection_id, ran_at,
        probe_1_status, probe_1_ms, probe_1_detail,
        probe_2_status, probe_2_ms, probe_2_detail,
        probe_3_status, probe_3_ms, probe_3_detail,
        probe_4_status, probe_4_ms, probe_4_detail,
        probe_5_status, probe_5_ms, probe_5_detail,
        probe_6_status, probe_6_ms, probe_6_detail,
        probe_7_status, probe_7_ms, probe_7_detail,
        probe_8_status, probe_8_ms, probe_8_detail,
        passed, total, agent_version, agent_uptime_s, run_trigger)
     VALUES
       (:p1, CURRENT_TIMESTAMP,
        :p2, :p3, :p4,
        :p5, :p6, :p7,
        :p8, :p9, :p10,
        :p11, :p12, :p13,
        :p14, :p15, :p16,
        :p17, :p18, :p19,
        :p20, :p21, :p22,
        :p23, :p24, :p25,
        :p26, :p27, :p28, :p29, :p30)
     RETURNING id INTO :out_id`,
    {
      p1: connectionId,
      p2: p1.status || null, p3: p1.ms || null, p4: p1.detail || null,
      p5: p2.status || null, p6: p2.ms || null, p7: p2.detail || null,
      p8: p3.status || null, p9: p3.ms || null, p10: p3.detail || null,
      p11: p4.status || null, p12: p4.ms || null, p13: p4.detail || null,
      p14: p5.status || null, p15: p5.ms || null, p16: p5.detail || null,
      p17: p6.status || null, p18: p6.ms || null, p19: p6.detail || null,
      p20: p7.status || null, p21: p7.ms || null, p22: p7.detail || null,
      p23: p8.status || null, p24: p8.ms || null, p25: p8.detail || null,
      p26: passed || 0, p27: total || 8,
      p28: agentVersion || null, p29: agentUptimeS || null,
      p30: trigger || 'manual',
      out_id: pool.outNumber(),
    }
  );
  const result = await pool.query(
    `SELECT id, ran_at FROM connection_health_runs WHERE id = :p1`,
    { p1: ins.outBinds.out_id[0] }
  );
  return result.rows[0];
}

// ── Fetch the latest run for a single connection ──────────────────────────────

async function getLatestRunForConnection(connectionId) {
  const result = await pool.query(
    `SELECT * FROM connection_health_runs
     WHERE connection_id = :p1
     ORDER BY ran_at DESC
     FETCH FIRST 1 ROWS ONLY`,
    { p1: connectionId }
  );
  return result.rows[0] || null;
}

// ── Fetch latest run for every connection owned by a user ─────────────────────
// Returns a map: connectionId → run row (or null if never run)

async function getLatestRunsForUser(userId) {
  const result = await pool.query(
    `SELECT * FROM (
       SELECT chr.*,
              ROW_NUMBER() OVER (PARTITION BY chr.connection_id ORDER BY chr.ran_at DESC) AS rn
         FROM connection_health_runs chr
         JOIN oracle_connections oc ON oc.id = chr.connection_id
        WHERE oc.user_id = :p1 OR oc.user_id IS NULL
     ) WHERE rn = 1`,
    { p1: userId }
  );
  const map = {};
  for (const row of result.rows) {
    map[row.connection_id] = row;
  }
  return map;
}

// ── Derive health status from a run row ───────────────────────────────────────
// Returns: { status, score, last_checked_at, agent_uptime_s, agent_version, failing_probes }

function deriveHealthStatus(run) {
  if (!run) {
    return { status: 'unknown', score: null, last_checked_at: null, agent_uptime_s: null, agent_version: null, failing_probes: [] };
  }

  const ranAt = new Date(run.ran_at);
  const ageMs = Date.now() - ranAt.getTime();
  const fifteenMin = 15 * 60 * 1000;
  const oneHour    = 60 * 60 * 1000;

  const probes = [1, 2, 3, 4, 5, 6, 7, 8].map(i => ({
    id: i,
    name: PROBE_NAMES[i - 1],
    status: run[`probe_${i}_status`] || 'skip',
    ms: run[`probe_${i}_ms`] || null,
    detail: run[`probe_${i}_detail`] || null,
  }));

  const failing = probes.filter(p => p.status === 'fail');
  const failIds = new Set(failing.map(p => p.id));

  // Critical probes 1–4: any failure → red
  const criticalFail = [1, 2, 3, 4].some(id => failIds.has(id));

  // A skipped probe is "does not apply here", not "not proven". Green used to
  // require passed === total, so ANY skip pinned a healthy server at yellow for
  // ever — and skips are the normal case, not the exception:
  //   - probe 2 (SSH bastion) skips on every connection without a bastion, which
  //     is most of them;
  //   - probes 3-6 (TNS/auth/query/end-to-end) skip on app-tier connections,
  //     which ship without an Oracle client by design.
  // So an app server that answered everything asked of it still showed amber,
  // which is how a fleet dashboard teaches people to ignore its own dots.
  // Grade on the probes that actually ran.
  const applicable = probes.filter(p => p.status === 'pass' || p.status === 'fail');

  let status;
  if (ageMs > oneHour || criticalFail) {
    status = 'red';
  } else if (ageMs <= fifteenMin && failing.length === 0 && applicable.length > 0) {
    status = 'green';
  } else {
    // Within 1h, no critical failures — yellow
    status = 'yellow';
  }

  return {
    status,
    score: run.passed,
    total: run.total,
    applicable: applicable.length,
    skipped: probes.length - applicable.length,
    last_checked_at: run.ran_at,
    agent_uptime_s: run.agent_uptime_s || null,
    agent_version: run.agent_version || null,
    failing_probes: failing.map(p => p.name),
    probes,
  };
}

// ── Fetch connection IDs eligible for the background sweeper ─────────────────
// Returns agent (proxy) connections that:
//   - have an active agent channel heartbeat in agent_tunnels
//   - have NOT been run in the last 4 minutes (avoid stampede on 5min cron)

async function getConnectionsForSweep() {
  const result = await pool.query(
    `SELECT oc.id AS connection_id, oc.user_id,
            oc.service_name, oc.username, oc.encrypted_password,
            oc.host, oc.port
     FROM oracle_connections oc
     JOIN agent_tunnels at ON at.connection_id = oc.id
     WHERE oc.connection_type = 'proxy'
       AND at.status = 'active'
       AND at.last_heartbeat > CURRENT_TIMESTAMP - INTERVAL '5' MINUTE
       AND NOT EXISTS (
         SELECT 1 FROM connection_health_runs chr
         WHERE chr.connection_id = oc.id
           AND chr.ran_at > CURRENT_TIMESTAMP - INTERVAL '4' MINUTE
       )
     ORDER BY oc.id`
  );
  return result.rows;
}

// ── Prune old runs (keep last 50 per connection) ──────────────────────────────

async function pruneOldRuns() {
  await pool.query(`
    DELETE FROM connection_health_runs
    WHERE id NOT IN (
      SELECT id FROM (
        SELECT id, ROW_NUMBER() OVER (PARTITION BY connection_id ORDER BY ran_at DESC) AS rn
        FROM connection_health_runs
      ) ranked
      WHERE rn <= 50
    )
  `);
}

module.exports = {
  PROBE_NAMES,
  insertHealthRun,
  getLatestRunForConnection,
  getLatestRunsForUser,
  deriveHealthStatus,
  getConnectionsForSweep,
  pruneOldRuns,
};
