/**
 * db/agent-upgrade-audit.js — Persistence for auto-upgrade lifecycle events.
 *
 * Owns: agent_upgrade_audit CRUD, auto_upgrade_enabled toggle on oracle_connections.
 * Does NOT own: upgrade work-item dispatch (routes/agent.js), agent channel (services/agent-channel.js).
 */

'use strict';

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

// ── Auto-upgrade policy ───────────────────────────────────────────────────────

/**
 * Return auto_upgrade_enabled for a connection.
 * Also returns connection_type, plan_tier, and any in-flight audit row.
 */
async function getUpgradePolicy(connectionId) {
  const result = await pool.query(
    `SELECT id, auto_upgrade_enabled, connection_type, server_type, proxy_version
     FROM oracle_connections WHERE id = :p1`,
    { p1: connectionId }
  );
  return result.rows[0] || null;
}

/**
 * Set auto_upgrade_enabled for a connection.
 */
async function setAutoUpgradeEnabled(connectionId, enabled) {
  await pool.query(
    `UPDATE oracle_connections SET auto_upgrade_enabled = :p1 WHERE id = :p2`,
    { p1: !!enabled, p2: connectionId }
  );
}

// ── Safety rails ──────────────────────────────────────────────────────────────

/**
 * Count failed auto-upgrades for this connection in the last 24h.
 * If ≥ 2, auto-upgrade is suppressed until a human intervenes.
 */
async function recentFailureCount(connectionId) {
  const result = await pool.query(
    `SELECT COUNT(*) AS cnt
     FROM agent_upgrade_audit
     WHERE connection_id = :p1
       AND status = 'failed'
       AND triggered_at > CURRENT_TIMESTAMP - INTERVAL '24' HOUR`,
    { p1: connectionId }
  );
  return parseInt(result.rows[0]?.cnt || '0', 10);
}

/**
 * Return the most recent queued-or-in-progress audit row for a connection.
 * Used to enforce the 6h dedup window — don't re-enqueue if one is already running.
 */
async function getActiveUpgrade(connectionId) {
  const result = await pool.query(
    `SELECT id, status, triggered_at
     FROM agent_upgrade_audit
     WHERE connection_id = :p1
       AND status IN ('queued', 'in_progress')
       AND triggered_at > CURRENT_TIMESTAMP - INTERVAL '6' HOUR
     ORDER BY triggered_at DESC
     FETCH FIRST 1 ROWS ONLY`,
    { p1: connectionId }
  );
  return result.rows[0] || null;
}

// ── Audit row lifecycle ───────────────────────────────────────────────────────

/**
 * Insert a new audit row with status='queued'.
 * Atomic: skips the insert if a queued/in_progress row already exists for this
 * connection, preventing duplicate rows from concurrent heartbeat callbacks.
 * Returns the new row id, or null if dedup suppressed the insert.
 */
async function insertUpgradeAudit({ connectionId, fromVersion, toVersion, triggeredBy }) {
  // FLAGGED PATTERN — conditional INSERT ... SELECT ... WHERE NOT EXISTS RETURNING.
  // Oracle supports RETURNING only on a single-row INSERT ... VALUES, never on
  // INSERT ... SELECT, so the original statement cannot be translated literally.
  // MERGE keeps the dedup atomic in ONE statement (a separate SELECT-then-INSERT
  // would reopen the concurrent-heartbeat race this guard exists to close), and
  // the id is read back afterwards. WHEN MATCHED is omitted entirely: this is a
  // dedup guard, so an existing queued/in_progress row must be left untouched.
  const merged = await pool.query(
    `MERGE INTO agent_upgrade_audit t
     USING (SELECT :p1 AS connection_id FROM DUAL) s
        ON (t.connection_id = s.connection_id AND t.status IN ('queued', 'in_progress'))
     WHEN NOT MATCHED THEN INSERT
       (connection_id, from_version, to_version, triggered_by, status)
       VALUES (:p1, :p2, :p3, :p4, 'queued')`,
    { p1: connectionId, p2: fromVersion || null, p3: toVersion, p4: triggeredBy || 'auto-stale-policy' }
  );
  if (!merged.rowCount) return null;          // dedup suppressed the insert
  const row = await pool.query(
    `SELECT id FROM agent_upgrade_audit
      WHERE connection_id = :p1 AND status = 'queued'
      ORDER BY triggered_at DESC, id DESC FETCH FIRST 1 ROWS ONLY`,
    { p1: connectionId }
  );
  return row.rows[0]?.id || null;
}

/**
 * Transition an audit row to 'in_progress'.
 */
async function markUpgradeInProgress(auditId) {
  await pool.query(
    `UPDATE agent_upgrade_audit SET status = 'in_progress' WHERE id = :p1`,
    { p1: auditId }
  );
}

/**
 * Transition an audit row to 'completed', stamp completed_at.
 */
async function markUpgradeCompleted(auditId) {
  await pool.query(
    `UPDATE agent_upgrade_audit
     SET status = 'completed', completed_at = CURRENT_TIMESTAMP
     WHERE id = :p1`,
    { p1: auditId }
  );
}

/**
 * Transition an audit row to 'failed', record reason.
 */
async function markUpgradeFailed(auditId, error) {
  await pool.query(
    `UPDATE agent_upgrade_audit
     SET status = 'failed', completed_at = CURRENT_TIMESTAMP, error = :p2
     WHERE id = :p1`,
    { p1: auditId, p2: error || 'unknown' }
  );
}

/**
 * After a successful heartbeat where the version now matches to_version,
 * complete any in-flight queued/in_progress audit rows for this connection.
 * Used by the heartbeat handler to close the loop when the agent checks back in.
 */
async function completeUpgradeOnHeartbeat(connectionId, newVersion) {
  const result = await pool.query(
    `UPDATE agent_upgrade_audit
     SET status = 'completed', completed_at = CURRENT_TIMESTAMP
     WHERE connection_id = :p1
       AND status IN ('queued', 'in_progress')
       AND to_version = :p2`,
    { p1: connectionId, p2: newVersion }
  );
  // RETURNING id existed only so the caller could count rows via .rows.length;
  // Oracle reports the same number as rowCount, so the clause is dropped.
  return result.rowCount;
}

/**
 * Fail timed-out in-progress upgrades (>10 minutes without completion).
 * Called periodically by the cron in routes/agent.js.
 */
async function expireTimedOutUpgrades() {
  const result = await pool.query(
    `UPDATE agent_upgrade_audit
     SET status = 'failed', completed_at = CURRENT_TIMESTAMP, error = 'timed out — no version confirmation after 10 min'
     WHERE status IN ('queued', 'in_progress')
       AND triggered_at < CURRENT_TIMESTAMP - INTERVAL '10' MINUTE`
  );
  return result.rowCount;
}

// ── Manual reset ─────────────────────────────────────────────────────────────

/**
 * Push recent failed rows for a connection outside the 24h suppression window
 * by back-dating their triggered_at to 25h ago. Preserves audit history;
 * only removes the time-window suppression so auto-upgrade can retry.
 * Returns the number of rows affected.
 */
async function resetRecentFailures(connectionId) {
  const result = await pool.query(
    `UPDATE agent_upgrade_audit
     SET triggered_at = CURRENT_TIMESTAMP - INTERVAL '25' HOUR
     WHERE connection_id = :p1
       AND status = 'failed'
       AND triggered_at > CURRENT_TIMESTAMP - INTERVAL '24' HOUR`,
    { p1: connectionId }
  );
  return result.rowCount;
}

/**
 * Delete all failed audit rows. Returns count of deleted rows.
 * Callable from the admin "Clear Failed" action only.
 */
async function clearFailedAudits() {
  const result = await pool.query(
    `DELETE FROM agent_upgrade_audit WHERE status = 'failed'`
  );
  return result.rowCount;
}

// ── Audit list queries ────────────────────────────────────────────────────────

/**
 * Return the most recent audit row per connection for status badges on /connections.
 * Returns a Map<connectionId, auditRow>.
 */
async function getLatestAuditsForConnections(connectionIds) {
  if (!connectionIds || connectionIds.length === 0) return {};
  // DISTINCT ON -> ROW_NUMBER() (approved pattern, site 2 of 8). = ANY(array)
  // becomes a generated IN list, since Oracle cannot bind an array.
  const binds = {};
  const inList = connectionIds.map((id, i) => { binds[`p${i}`] = id; return `:p${i}`; }).join(', ');
  const result = await pool.query(
    `SELECT * FROM (
       SELECT id, connection_id, from_version, to_version, triggered_by,
              triggered_at, completed_at, status, error,
              ROW_NUMBER() OVER (PARTITION BY connection_id ORDER BY triggered_at DESC) AS rn
         FROM agent_upgrade_audit
        WHERE connection_id IN (${inList})
     ) WHERE rn = 1`,
    binds
  );
  const map = {};
  for (const row of result.rows) {
    map[row.connection_id] = row;
  }
  return map;
}

/**
 * Return last 100 audit rows for the admin page, newest first.
 * Joins oracle_connections for connection name display.
 */
async function listRecentAudits({ limit = 100 } = {}) {
  const result = await pool.query(
    `SELECT a.id, a.connection_id, oc.name AS connection_name,
            a.from_version, a.to_version, a.triggered_by,
            a.triggered_at, a.completed_at, a.status, a.error,
            -- FLAGGED: EXTRACT(EPOCH FROM interval) has no Oracle equivalent.
            -- A timestamp difference yields an INTERVAL DAY TO SECOND, so the
            -- seconds are recomposed from its parts. This is exact (verified
            -- against a known 97445.5s interval); ROUND matches the original ::int.
            ROUND(
              EXTRACT(DAY    FROM (a.completed_at - a.triggered_at)) * 86400 +
              EXTRACT(HOUR   FROM (a.completed_at - a.triggered_at)) * 3600 +
              EXTRACT(MINUTE FROM (a.completed_at - a.triggered_at)) * 60 +
              EXTRACT(SECOND FROM (a.completed_at - a.triggered_at))
            ) AS duration_s
     FROM agent_upgrade_audit a
     LEFT JOIN oracle_connections oc ON oc.id = a.connection_id
     ORDER BY a.triggered_at DESC
     FETCH FIRST :p1 ROWS ONLY`,
    { p1: limit }
  );
  return result.rows;
}

module.exports = {
  getUpgradePolicy,
  setAutoUpgradeEnabled,
  recentFailureCount,
  resetRecentFailures,
  getActiveUpgrade,
  insertUpgradeAudit,
  markUpgradeInProgress,
  markUpgradeCompleted,
  markUpgradeFailed,
  completeUpgradeOnHeartbeat,
  expireTimedOutUpgrades,
  clearFailedAudits,
  getLatestAuditsForConnections,
  listRecentAudits,
};
