/**
 * db/agent-restart-events.js — Agent restart-event persistence.
 *
 * Owns: agent_restart_events (append-only restart log) + agent_in_restart_loop flag on oracle_connections.
 * Does NOT own: heartbeat, poll, registration — those stay in db/agent.js.
 */

'use strict';

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

// ── Insert a restart event ────────────────────────────────────────────────────

async function insertRestartEvent({ connectionId, reasonCode, lastStageReached, lastError, uptimeSeconds, restartSequenceId }) {
  const ins = await pool.query(
    `INSERT INTO agent_restart_events
       (connection_id, reason_code, last_stage_reached, last_error, uptime_seconds, restart_sequence_id)
     VALUES (:p1, :p2, :p3, :p4, :p5, :p6)
     RETURNING id INTO :out_id`,
    {
      p1: connectionId, p2: reasonCode, p3: lastStageReached || null, p4: lastError || null,
      p5: uptimeSeconds != null ? parseInt(uptimeSeconds, 10) : null, p6: restartSequenceId || null,
      out_id: pool.outNumber(),
    }
  );
  const result = await pool.query(
    `SELECT id, recorded_at FROM agent_restart_events WHERE id = :p1`,
    { p1: ins.outBinds.out_id[0] }
  );
  return result.rows[0];
}

// ── Count restarts in a time window ─────────────────────────────────────────

async function countRestarts(connectionId, windowMinutes) {
  const result = await pool.query(
    `SELECT COUNT(*) AS cnt
     FROM agent_restart_events
     WHERE connection_id = :p1
       AND recorded_at > CURRENT_TIMESTAMP - NUMTODSINTERVAL(:p2, 'MINUTE')`,
    { p1: connectionId, p2: Number(windowMinutes) }
  );
  return result.rows[0].cnt;
}

// ── Count restarts for a specific reason_code in window ─────────────────────
// Used for loop detection: ≥5 restarts with same reason in 10 min.

async function countRestartsByReason(connectionId, reasonCode, windowMinutes) {
  const result = await pool.query(
    `SELECT COUNT(*) AS cnt
     FROM agent_restart_events
     WHERE connection_id = :p1
       AND reason_code = :p2
       AND recorded_at > CURRENT_TIMESTAMP - NUMTODSINTERVAL(:p3, 'MINUTE')`,
    { p1: connectionId, p2: reasonCode, p3: Number(windowMinutes) }
  );
  return result.rows[0].cnt;
}

// ── Restart counts for agent list (1h and 24h) ───────────────────────────────

async function getRestartCounts(connectionId) {
  const result = await pool.query(
    `SELECT
       COUNT(CASE WHEN recorded_at > CURRENT_TIMESTAMP - INTERVAL '1' HOUR  THEN 1 END) AS count_1h,
       COUNT(CASE WHEN recorded_at > CURRENT_TIMESTAMP - INTERVAL '24' HOUR THEN 1 END) AS count_24h
     FROM agent_restart_events
     WHERE connection_id = :p1`,
    { p1: connectionId }
  );
  return result.rows[0];
}

// ── Flip / clear restart-loop flag on oracle_connections ────────────────────

async function setRestartLoopFlag(connectionId, inLoop, reasonCode) {
  if (inLoop) {
    // NOTE: the placeholders here ran $2 before $1. Under positional binds Oracle
    // would have assigned by order of appearance and written the connection id
    // into agent_restart_loop_reason, with no error — named binds keep the
    // original mapping.
    await pool.query(
      `UPDATE oracle_connections
       SET agent_in_restart_loop     = TRUE,
           agent_restart_loop_reason = :p2,
           agent_restart_loop_at     = CURRENT_TIMESTAMP
       WHERE id = :p1`,
      { p1: connectionId, p2: reasonCode }
    );
  } else {
    await pool.query(
      `UPDATE oracle_connections
       SET agent_in_restart_loop     = FALSE,
           agent_restart_loop_reason = NULL,
           agent_restart_loop_at     = NULL
       WHERE id = :p1`,
      { p1: connectionId }
    );
  }
}

// ── Clear loop flag on successful heartbeat ───────────────────────────────────

async function clearRestartLoopIfSet(connectionId) {
  await pool.query(
    `UPDATE oracle_connections
     SET agent_in_restart_loop     = FALSE,
         agent_restart_loop_reason = NULL,
         agent_restart_loop_at     = NULL
     WHERE id = :p1 AND agent_in_restart_loop = TRUE`,
    { p1: connectionId }
  );
}

module.exports = {
  insertRestartEvent,
  countRestarts,
  countRestartsByReason,
  getRestartCounts,
  setRestartLoopFlag,
  clearRestartLoopIfSet,
};
