/**
 * db/agent-command-results.js — agent_command_results + agent_crash_alerts_sent CRUD.
 *
 * Owns: one-shot command dispatch records (journalctl pulls), crash alert dedup state.
 * Does NOT own: agent tunnels, heartbeat recording, install failure logs (agent-install-failures.js).
 */

'use strict';

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

// ── agent_command_results ─────────────────────────────────────────────────────

/**
 * Create a pending command result row. Returns row with id.
 */
async function createCommandResult({ connectionId, command, requestedBy }) {
  // RETURNING * -> RETURNING id INTO :out_id, then read the row back by id.
  const ins = await pool.query(
    `INSERT INTO agent_command_results (connection_id, command, status, requested_by)
     VALUES (:p1, :p2, 'pending', :p3)
     RETURNING id INTO :out_id`,
    { p1: connectionId, p2: command, p3: requestedBy || null, out_id: pool.outNumber() }
  );
  const res = await pool.query(
    `SELECT * FROM agent_command_results WHERE id = :p1`,
    { p1: ins.outBinds.out_id[0] }
  );
  return res.rows[0];
}

/**
 * Mark a command result as completed (agent responded or fallback used).
 */
async function completeCommandResult({ id, output, exitCode, errorMessage }) {
  // NOTE: :p2 is referenced twice (the CASE and the SET). That is exactly the
  // shape that breaks under positional binds — with named binds one value feeds
  // both references, matching the original $2 semantics.
  const upd = await pool.query(
    `UPDATE agent_command_results
     SET status        = CASE WHEN :p2 IS NOT NULL THEN 'completed' ELSE 'error' END,
         output        = :p2,
         exit_code     = :p3,
         error_message = :p4,
         completed_at  = CURRENT_TIMESTAMP
     WHERE id = :p1`,
    { p1: id, p2: output || null, p3: exitCode || null, p4: errorMessage || null }
  );
  // UPDATE ... RETURNING * -> read back by the id we already have. rowCount tells
  // us whether the row existed, preserving the original null-when-missing result.
  if (!upd.rowCount) return null;
  const res = await pool.query(`SELECT * FROM agent_command_results WHERE id = :p1`, { p1: id });
  return res.rows[0] || null;
}

/**
 * Get the latest command result for a connection (any command).
 */
async function getLatestCommandResult(connectionId) {
  const res = await pool.query(
    `SELECT * FROM agent_command_results
     WHERE connection_id = :p1
     ORDER BY requested_at DESC
     FETCH FIRST 1 ROWS ONLY`,
    { p1: connectionId }
  );
  return res.rows[0] || null;
}

/**
 * Get the latest journalctl pull result for a connection.
 */
async function getLatestJournalctlResult(connectionId) {
  const res = await pool.query(
    `SELECT * FROM agent_command_results
     WHERE connection_id = :p1
       AND command LIKE 'journalctl%'
     ORDER BY requested_at DESC
     FETCH FIRST 1 ROWS ONLY`,
    { p1: connectionId }
  );
  return res.rows[0] || null;
}

// ── agent_crash_alerts_sent — dedup so we only email once per incident ────────

/**
 * Returns true if a crash-loop alert was already sent for this connection
 * within the last 24 hours.
 */
async function crashAlertAlreadySent(connectionId) {
  const res = await pool.query(
    `SELECT 1 FROM agent_crash_alerts_sent
     WHERE connection_id = :p1
       AND sent_at > CURRENT_TIMESTAMP - INTERVAL '24' HOUR
     FETCH FIRST 1 ROWS ONLY`,
    { p1: connectionId }
  );
  return res.rows.length > 0;
}

/**
 * Upsert: record that a crash-loop alert was sent for this connection.
 */
async function recordCrashAlertSent({ connectionId, recipient, agentHealth }) {
  // ON CONFLICT DO UPDATE -> MERGE; EXCLUDED.col becomes the USING row's column.
  await pool.query(
    `MERGE INTO agent_crash_alerts_sent t
     USING (SELECT :p1 AS connection_id, :p2 AS recipient, :p3 AS agent_health FROM DUAL) s
        ON (t.connection_id = s.connection_id)
     WHEN MATCHED THEN UPDATE SET
       t.sent_at      = CURRENT_TIMESTAMP,
       t.recipient    = s.recipient,
       t.agent_health = s.agent_health
     WHEN NOT MATCHED THEN INSERT (connection_id, recipient, agent_health, sent_at)
       VALUES (s.connection_id, s.recipient, s.agent_health, CURRENT_TIMESTAMP)`,
    { p1: connectionId, p2: recipient || null, p3: agentHealth || null }
  );
}

/**
 * Clear the alert-sent flag when the agent recovers (starts heartbeating again).
 * Called from the heartbeat handler.
 */
async function clearCrashAlert(connectionId) {
  await pool.query(
    `DELETE FROM agent_crash_alerts_sent WHERE connection_id = :p1`,
    { p1: connectionId }
  );
}

module.exports = {
  createCommandResult,
  completeCommandResult,
  getLatestCommandResult,
  getLatestJournalctlResult,
  crashAlertAlreadySent,
  recordCrashAlertSent,
  clearCrashAlert,
};
