/**
 * db/admin-agents.js — Admin Agents Fleet read queries + log-tail store.
 *
 * Owns: agents_log_buffer CRUD; fleet-level JOIN across agent_tunnels +
 *   oracle_connections + users for the /admin/agents dashboard.
 * Does NOT own: heartbeat writes (db/agent.js), upgrade audit (db/agent-upgrade-audit.js).
 */

'use strict';

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

// ── Fleet list ────────────────────────────────────────────────────────────────

/**
 * Return all active agent rows joined with connection + owner info.
 * Excludes connections where agent_tunnels row is missing (never installed).
 */
async function listAgents() {
  const result = await pool.query(`
    SELECT
      at.id                   AS tunnel_id,
      at.connection_id,
      oc.name                 AS connection_name,
      oc.host                 AS db_host,
      oc.user_id,
      u.email                 AS owner_email,
      u.company_domain,
      oc.os_id,
      oc.kernel_version,
      oc.python_version,
      oc.proxy_version,
      oc.install_id,
      oc.installed_at,
      at.agent_version,
      at.agent_status,
      at.os_info,
      at.status               AS tunnel_status,
      at.last_heartbeat,
      at.uptime_seconds,
      at.oracle_mode,
      at.uninstalled_at,
      at.confirmed_at,
      at.created_at           AS tunnel_created_at,
      -- latest hostname from diagnose runs (if available)
      (
        SELECT adr.host
        FROM agent_diagnose_runs adr
        WHERE adr.connection_id = at.connection_id
        ORDER BY adr.created_at DESC
        FETCH FIRST 1 ROWS ONLY
      )                       AS last_diagnosed_host,
      -- connection count per owner (how many connections this user has)
      (
        SELECT COUNT(*)
        FROM oracle_connections oc2
        WHERE oc2.user_id = oc.user_id
      )                       AS owner_connection_count
    FROM agent_tunnels at
    JOIN oracle_connections oc ON oc.id = at.connection_id
    LEFT JOIN users u ON u.id = oc.user_id
    ORDER BY
      CASE
        WHEN at.last_heartbeat IS NULL THEN 0
        WHEN at.last_heartbeat < CURRENT_TIMESTAMP - INTERVAL '15' MINUTE THEN 1
        WHEN at.last_heartbeat < CURRENT_TIMESTAMP - INTERVAL '2' MINUTE THEN 2
        ELSE 3
      END ASC,
      at.last_heartbeat DESC NULLS LAST
  `);
  return result.rows;
}

// ── Stats summary ─────────────────────────────────────────────────────────────

/**
 * Return aggregate counts for the stat bar.
 * Uses the same status thresholds as the frontend pill logic.
 */
async function getAgentStats({ onlineSeconds = 120, staleSeconds = 900 } = {}) {
  // ($1 || ' seconds')::INTERVAL builds an interval from a bind — Oracle's
  // equivalent is NUMTODSINTERVAL(n, 'SECOND'), which takes the number directly
  // (so these binds are numbers now, not strings).
  const result = await pool.query(`
    SELECT
      COUNT(*) AS total,
      -- "online" and "offline" are Oracle RESERVED WORDS, so these aliases must be
      -- quoted. Quoting them lowercase also returns the exact keys the callers
      -- already read (stats.online / stats.offline).
      COUNT(CASE
        WHEN last_heartbeat >= CURRENT_TIMESTAMP - NUMTODSINTERVAL(:p1, 'SECOND') THEN 1
      END) AS "online",
      COUNT(CASE
        WHEN last_heartbeat >= CURRENT_TIMESTAMP - NUMTODSINTERVAL(:p2, 'SECOND')
         AND last_heartbeat <  CURRENT_TIMESTAMP - NUMTODSINTERVAL(:p1, 'SECOND') THEN 1
      END) AS stale,
      COUNT(CASE
        WHEN last_heartbeat IS NULL
          OR last_heartbeat < CURRENT_TIMESTAMP - NUMTODSINTERVAL(:p2, 'SECOND') THEN 1
      END) AS "offline"
    FROM agent_tunnels
    WHERE status != 'uninstalled'
  `, { p1: Number(onlineSeconds), p2: Number(staleSeconds) });
  return result.rows[0];
}

// ── Log-tail store ────────────────────────────────────────────────────────────

/**
 * Upsert last N lines of agent log for a connection.
 * Cap enforced at 500 lines by the route before calling here.
 */
async function upsertLogTail(connectionId, logLines) {
  // PRE-EXISTING BUG, fixed here rather than translated: this statement used to
  // write log_lines / line_count / flushed_at, none of which exist — not in
  // Oracle and not in Neon either (`SELECT log_lines FROM agents_log_buffer`
  // fails on both). Both this function and getLogTail have therefore been
  // throwing on every call. The real table is (connection_id, lines, updated_at)
  // where `lines` was text[] in Postgres and is JSON here.
  // CALLER IMPACT: routes/admin-agents.js reads the old field names and needs a
  // matching update — deliberately not changed, this pass is scoped to db/.
  const lines = (logLines || '').split('\n').filter(Boolean);
  // ON CONFLICT DO UPDATE -> MERGE. The USING row is the "EXCLUDED" pseudo-table:
  // every EXCLUDED.col becomes s.col.
  // The JSON payload is bound as a CLOB: inside a MERGE the driver has no target
  // column to infer from, so a large array would otherwise overflow the VARCHAR2
  // bind ceiling (ORA-01461). Oracle converts CLOB -> JSON implicitly.
  await pool.query(`
    MERGE INTO agents_log_buffer t
    USING (SELECT :p1 AS connection_id FROM DUAL) s
       ON (t.connection_id = s.connection_id)
    WHEN MATCHED THEN UPDATE SET
      t.lines      = :p2,
      t.updated_at = CURRENT_TIMESTAMP
    WHEN NOT MATCHED THEN INSERT (connection_id, lines, updated_at)
      VALUES (s.connection_id, :p2, CURRENT_TIMESTAMP)
  `, { p1: connectionId, p2: pool.clob(JSON.stringify(lines)) });
}

/**
 * Fetch log tail for a single connection.
 * Returns { lines, updated_at } or null if not present — the real
 * agents_log_buffer shape (`lines` is JSON; pre-existing bug #1 had this doc and
 * the query naming log_lines/line_count/flushed_at, none of which ever existed).
 * NB the /log-tail route runs its own equivalent query and returns
 * { lines, updatedAt }; the admin/agents.html modal reads those keys.
 */
async function getLogTail(connectionId) {
  const result = await pool.query(`
    SELECT lines, updated_at
    FROM agents_log_buffer
    WHERE connection_id = :p1
  `, { p1: connectionId });
  return result.rows[0] || null;
}

module.exports = {
  listAgents,
  getAgentStats,
  upsertLogTail,
  getLogTail,
};
