/**
 * db/agent-install-failures.js — Install failure ingestion + admin read.
 *
 * Owns: agent_install_failures (append-only log of POST /api/agent/install-failures calls).
 * Does NOT own: agent_tunnels, oracle_connections, agent_diagnose_runs.
 */

'use strict';

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

/**
 * Shared WHERE builder for the two filtered queries, which previously carried
 * two copies of the same clause list drifting apart by a table prefix.
 *
 * Oracle notes: named binds (positional would mis-bind an out-of-order list);
 * `('24 hours')::interval` has no Oracle equivalent, so the window is expressed
 * as NUMTODSINTERVAL(n, unit); ILIKE becomes UPPER() LIKE UPPER(); and
 * ip_address is VARCHAR2 here so the ::text cast is dropped.
 *
 * @param {string} prefix — table alias prefix, e.g. 'f.' or ''
 */
function buildFailureFilter({ errorClass, installerVersion, since, q }, prefix = '') {
  const clauses = [];
  const binds = {};

  if (errorClass) { binds.p_error_class = errorClass; clauses.push(`${prefix}error_class = :p_error_class`); }
  if (installerVersion) { binds.p_version = installerVersion; clauses.push(`${prefix}installer_version = :p_version`); }

  if (since && since !== 'all') {
    // was: { '24h': '24 hours', '7d': '7 days', '30d': '30 days' }
    const intervalMap = { '24h': [24, 'HOUR'], '7d': [7, 'DAY'], '30d': [30, 'DAY'] };
    const spec = intervalMap[since];
    if (spec) {
      binds.p_window = spec[0];
      clauses.push(`${prefix}created_at >= CURRENT_TIMESTAMP - NUMTODSINTERVAL(:p_window, '${spec[1]}')`);
    }
  }

  if (q) {
    binds.p_q = `%${q}%`;
    clauses.push(`(UPPER(${prefix}host) LIKE UPPER(:p_q) OR UPPER(${prefix}ip_address) LIKE UPPER(:p_q))`);
  }

  return { where: clauses.length ? `WHERE ${clauses.join(' AND ')}` : '', binds };
}

/**
 * Record a failed install attempt from install.sh.
 * connection_id may be null (not yet provisioned / provisioning failed).
 */
async function insertInstallFailure({
  connectionId,
  host,
  errorClass,
  journalctlTail,
  installLogTail,
  installerVersion,
  osInfo,
  ipAddress,
  userAgent,
}) {
  // RETURNING id, created_at -> return the id through an OUT bind and read the
  // row back, rather than adding a second typed OUT bind for the timestamp.
  const ins = await pool.query(
    `INSERT INTO agent_install_failures
       (connection_id, host, error_class, journalctl_tail, install_log_tail, installer_version, os_info, ip_address, user_agent)
     VALUES (:p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9)
     RETURNING id INTO :out_id`,
    {
      p1: connectionId || null,
      p2: host || null,
      p3: errorClass || null,
      p4: journalctlTail ? String(journalctlTail).slice(0, 32768) : null,
      p5: installLogTail ? String(installLogTail).slice(0, 32768) : null,
      p6: installerVersion || null,
      p7: osInfo || null,
      p8: ipAddress || null,
      p9: userAgent ? String(userAgent).slice(0, 512) : null,
      out_id: pool.outNumber(),
    }
  );
  const result = await pool.query(
    `SELECT id, created_at FROM agent_install_failures WHERE id = :p1`,
    { p1: ins.outBinds.out_id[0] }
  );
  return result.rows[0];
}

/**
 * Count distinct hosts that reported error_class='systemd_failed'
 * in the last windowMinutes minutes. Used by the alert gate.
 */
async function countRecentSystemdFailed(windowMinutes = 10) {
  // ip_address is VARCHAR2 here (Postgres inet), so the ::text cast is dropped.
  // ($1 || ' minutes')::interval -> NUMTODSINTERVAL(n, 'MINUTE'), taking a number.
  const result = await pool.query(
    `SELECT COUNT(DISTINCT COALESCE(host, ip_address, 'unknown')) AS cnt
     FROM agent_install_failures
     WHERE error_class = 'systemd_failed'
       AND created_at >= CURRENT_TIMESTAMP - NUMTODSINTERVAL(:p1, 'MINUTE')`,
    { p1: Number(windowMinutes) }
  );
  return parseInt(result.rows[0].cnt, 10);
}

/**
 * Filtered + paginated list for the admin page.
 * Filters: error_class, installer_version, since (24h/7d/30d/all), q (host/ip search).
 * Returns rows newest-first, with resolved rows included (caller can filter client-side).
 */
async function getFilteredFailures({ errorClass, installerVersion, since, q, limit = 50, offset = 0 }) {
  const { where, binds } = buildFailureFilter({ errorClass, installerVersion, since, q }, 'f.');

  const result = await pool.query(
    `SELECT f.id, f.connection_id, f.host, f.error_class,
            f.installer_version, f.os_info, f.ip_address,
            f.journalctl_tail, f.install_log_tail,
            f.created_at, f.resolved_at,
            oc.name AS connection_name
     FROM agent_install_failures f
     LEFT JOIN oracle_connections oc ON oc.id = f.connection_id
     ${where}
     ORDER BY f.created_at DESC
     OFFSET :p_offset ROWS FETCH NEXT :p_limit ROWS ONLY`,
    { ...binds, p_offset: offset, p_limit: limit }
  );
  return result.rows;
}

/**
 * Stats strip: counts for 24h, 7d, unique hosts (24h), most common error_class (24h).
 */
async function getStats() {
  // FILTER (WHERE x) -> COUNT(CASE WHEN x THEN ... END). For the DISTINCT count
  // the CASE has to yield the *expression*, not 1, so distinctness is preserved.
  // Oracle will not accept a scalar subquery alongside aggregates without a
  // GROUP BY (ORA-00937) — Postgres allows it. The aggregates therefore stand
  // alone in an inline view and the subquery is selected from that.
  const result = await pool.query(`
    SELECT agg.failures_24h,
           agg.failures_7d,
           agg.unique_hosts_24h,
           (
             SELECT error_class
             FROM agent_install_failures
             WHERE created_at >= CURRENT_TIMESTAMP - INTERVAL '24' HOUR
               AND error_class IS NOT NULL
             GROUP BY error_class
             ORDER BY COUNT(*) DESC
             FETCH FIRST 1 ROWS ONLY
           ) AS top_error_class
    FROM (
      SELECT
        COUNT(CASE WHEN created_at >= CURRENT_TIMESTAMP - INTERVAL '24' HOUR THEN 1 END) AS failures_24h,
        COUNT(CASE WHEN created_at >= CURRENT_TIMESTAMP - INTERVAL '7' DAY   THEN 1 END) AS failures_7d,
        COUNT(DISTINCT CASE WHEN created_at >= CURRENT_TIMESTAMP - INTERVAL '24' HOUR
                            THEN COALESCE(host, ip_address) END)                         AS unique_hosts_24h
      FROM agent_install_failures
    ) agg
  `);
  return result.rows[0];
}

/**
 * Distinct error_class values (for filter dropdown).
 */
async function getDistinctErrorClasses() {
  const result = await pool.query(
    `SELECT DISTINCT error_class FROM agent_install_failures
     WHERE error_class IS NOT NULL ORDER BY error_class`
  );
  return result.rows.map(r => r.error_class);
}

/**
 * Distinct installer_version values (for filter dropdown).
 */
async function getDistinctVersions() {
  const result = await pool.query(
    `SELECT DISTINCT installer_version FROM agent_install_failures
     WHERE installer_version IS NOT NULL ORDER BY installer_version DESC`
  );
  return result.rows.map(r => r.installer_version);
}

/**
 * Count distinct hosts with a specific error_class + installer_version in last windowMinutes.
 * Used by the alert gate in routes/install-failures.js to detect failure spikes.
 */
async function countDistinctHostsForPair(errorClass, installerVersion, windowMinutes = 10) {
  const { rows } = await pool.query(
    `SELECT COUNT(DISTINCT COALESCE(host, ip_address, 'unknown')) AS cnt
     FROM agent_install_failures
     WHERE error_class = :p1
       AND installer_version = :p2
       AND created_at >= CURRENT_TIMESTAMP - NUMTODSINTERVAL(:p3, 'MINUTE')`,
    { p1: errorClass, p2: installerVersion, p3: Number(windowMinutes) }
  );
  return parseInt(rows[0].cnt, 10);
}

/**
 * Mark a single failure row as resolved.
 */
async function resolveFailure(id) {
  await pool.query(
    `UPDATE agent_install_failures SET resolved_at = CURRENT_TIMESTAMP WHERE id = :p1`,
    { p1: id }
  );
}

/**
 * Total count matching filter (for pagination).
 */
async function countFilteredFailures({ errorClass, installerVersion, since, q }) {
  // Same predicate set as getFilteredFailures — shared so the two cannot drift.
  const { where, binds } = buildFailureFilter({ errorClass, installerVersion, since, q });
  const result = await pool.query(
    `SELECT COUNT(*) AS cnt FROM agent_install_failures ${where}`,
    binds
  );
  return parseInt(result.rows[0].cnt, 10);
}

/**
 * List recent failures for the legacy admin page. Returns last N rows, newest first.
 */
async function getRecentFailures(limit = 100) {
  const result = await pool.query(
    `SELECT f.id, f.connection_id, f.host, f.error_class,
            f.installer_version, f.os_info,
            f.journalctl_tail, f.install_log_tail,
            f.created_at, f.resolved_at,
            oc.name AS connection_name
     FROM agent_install_failures f
     LEFT JOIN oracle_connections oc ON oc.id = f.connection_id
     ORDER BY f.created_at DESC
     FETCH FIRST :p1 ROWS ONLY`,
    { p1: limit }
  );
  return result.rows;
}

/**
 * Seed 3 fake rows for local dev/testing. Idempotent — skips if dev rows exist.
 */
async function seedDevRows() {
  const { rows } = await pool.query(
    `SELECT COUNT(*) AS cnt FROM agent_install_failures WHERE host LIKE 'dev-seed-%'`
  );
  if (parseInt(rows[0].cnt, 10) >= 3) return;

  const seeds = [
    {
      host: 'dev-seed-ol7.example.com',
      error_class: 'systemd_failed',
      installer_version: '7.5.0',
      os_info: 'OracleLinux 7.9',
      journalctl_tail: 'May 24 02:00:01 dev-seed-ol7 systemd[1]: tunevault-agent.service: Main process exited, code=exited, status=1/FAILURE\nMay 24 02:00:01 dev-seed-ol7 systemd[1]: Failed to start TuneVault Oracle Proxy Agent.\nMay 24 02:00:02 dev-seed-ol7 systemd[1]: tunevault-agent.service: Unit entered failed state.',
      install_log_tail: '[ERROR] systemd unit failed to start after 30s',
    },
    {
      host: 'dev-seed-ol8.example.com',
      error_class: 'no_heartbeat',
      installer_version: '7.5.0',
      os_info: 'OracleLinux 8.8',
      journalctl_tail: 'May 24 01:45:00 dev-seed-ol8 oracle-proxy[12345]: Stage: boot\nMay 24 01:45:01 dev-seed-ol8 oracle-proxy[12345]: Stage: env_loaded\nMay 24 01:45:02 dev-seed-ol8 oracle-proxy[12345]: Stage: python_deps_ok',
      install_log_tail: '[WARN] No heartbeat received after 60 seconds',
    },
    {
      host: 'dev-seed-rhel8.example.com',
      error_class: 'module_import_error',
      installer_version: '7.4.0',
      os_info: 'RHEL 8.6',
      journalctl_tail: 'May 23 23:00:00 dev-seed-rhel8 oracle-proxy[9999]: ModuleNotFoundError: No module named \'oracledb\'\nMay 23 23:00:00 dev-seed-rhel8 systemd[1]: tunevault-agent.service: Main process exited, code=exited, status=5/NOTINSTALLED',
      install_log_tail: '[ERROR] Python import check failed: oracledb not found',
    },
  ];

  for (const s of seeds) {
    await pool.query(
      `INSERT INTO agent_install_failures (host, error_class, installer_version, os_info, journalctl_tail, install_log_tail)
       VALUES (:p1, :p2, :p3, :p4, :p5, :p6)`,
      { p1: s.host, p2: s.error_class, p3: s.installer_version, p4: s.os_info,
        p5: s.journalctl_tail, p6: s.install_log_tail }
    );
  }
}

module.exports = {
  insertInstallFailure,
  getRecentFailures,
  countRecentSystemdFailed,
  countDistinctHostsForPair,
  getFilteredFailures,
  getStats,
  getDistinctErrorClasses,
  getDistinctVersions,
  resolveFailure,
  countFilteredFailures,
  seedDevRows,
};
