/**
 * db/upgrade-verifications.js — Post-upgrade verification persistence.
 *
 * Owns: verification_payload / verification_status / verified_at on agent_upgrade_audit;
 *       post-upgrade rows in installer_validation_runs.
 * Does NOT own: upgrade dispatch logic (routes/agent.js), audit lifecycle (db/agent-upgrade-audit.js),
 *               operator email (called from routes/upgrade-verifications.js).
 */

'use strict';

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

/**
 * Attach a verification payload to the most-recent completed audit row for a connection.
 *
 * The agent sends: { verification_id, checks_passed, checks_total,
 *                    api_test_status, probe_7, probe_8, timestamp }
 *
 * Returns the audit row id that was updated, or null if none found.
 */
async function saveVerification({ connectionId, auditId, payload, status }) {
  // If caller knows the audit id, use it directly. Otherwise find the latest completed row.
  let targetId = auditId || null;

  if (!targetId) {
    const find = await pool.query(
      `SELECT id FROM agent_upgrade_audit
       WHERE connection_id = :p1
         AND status = 'completed'
       ORDER BY completed_at DESC
       FETCH FIRST 1 ROWS ONLY`,
      { p1: connectionId }
    );
    targetId = find.rows[0]?.id || null;
  }

  if (!targetId) return null;

  const upd = await pool.query(
    `UPDATE agent_upgrade_audit
     SET verification_payload = :p2,
         verification_status  = :p3,
         verified_at          = CURRENT_TIMESTAMP
     WHERE id = :p1`,
    { p1: targetId, p2: pool.clob(JSON.stringify(payload)), p3: status }
  );
  if (!upd.rowCount) return null;
  const result = await pool.query(
    `SELECT id, connection_id, to_version, verification_status, verified_at
     FROM agent_upgrade_audit WHERE id = :p1`, { p1: targetId });
  return result.rows[0] || null;
}

/**
 * Insert a post-upgrade validation run into installer_validation_runs.
 *
 * topology = 'post-upgrade-v3-to-v6'
 * os       = 'post-upgrade'
 *
 * Uses the same schema as EBS live rows (checks_passed, checks_total, topology).
 */
async function insertPostUpgradeValidationRun({
  connectionId,
  connectionName,
  fromVersion,
  toVersion,
  runId,
  agentVersion,
  checksPassed,
  checksTotal,
  probe7Status,
  probe8Status,
  overallOutcome,
  durationMs,
}) {
  // Insert into installer_validation_runs
  const insertResult = await pool.query(
    `INSERT INTO installer_validation_runs
       (run_id, os, trigger_source, topology, started_at, finished_at,
        agent_version, overall, duration_total_ms,
        checks_passed, checks_total, ssh_runbook_executed,
        probe_7_status, probe_8_status,
        error_message)
     VALUES (:p1, 'post-upgrade', 'post-upgrade-agent', 'post-upgrade-v3-to-v6',
             CURRENT_TIMESTAMP, CURRENT_TIMESTAMP,
             :p2, :p3, :p4,
             :p5, :p6, FALSE,
             :p7, :p8,
             :p9)
     RETURNING id INTO :out_id`,
    {
      p1: runId,
      p2: agentVersion || toVersion,
      p3: overallOutcome,
      p4: durationMs || null,
      p5: checksPassed,
      p6: checksTotal,
      p7: probe7Status,
      p8: probe8Status,
      // Store from→to version + connection info in error_message for context
      p9: `connection:${connectionName || connectionId} from:${fromVersion} to:${toVersion}`,
      out_id: pool.outNumber(),
    }
  );

  return insertResult.outBinds.out_id[0] || null;
}

/**
 * Get the latest verification payload for a connection.
 * Used by /connections Version badge tooltip.
 */
async function getLatestVerificationForConnection(connectionId) {
  const result = await pool.query(
    `SELECT a.id, a.connection_id, a.from_version, a.to_version,
            a.verification_payload, a.verification_status, a.verified_at,
            a.completed_at, a.status
     FROM agent_upgrade_audit a
     WHERE a.connection_id = :p1
       AND a.verified_at IS NOT NULL
     ORDER BY a.verified_at DESC
     FETCH FIRST 1 ROWS ONLY`,
    { p1: connectionId }
  );
  return result.rows[0] || null;
}

/**
 * Get latest verification payloads for multiple connections.
 * Returns Map<connectionId, row>.
 */
async function getLatestVerificationsForConnections(connectionIds) {
  if (!connectionIds || connectionIds.length === 0) return {};
  const binds = {}; const names = connectionIds.map((v, i) => { binds['c' + i] = v; return ':c' + i; });
  const result = await pool.query(
    `SELECT id, connection_id, from_version, to_version,
            verification_payload, verification_status, verified_at, rn FROM (
       SELECT id, connection_id, from_version, to_version,
              verification_payload, verification_status, verified_at,
              ROW_NUMBER() OVER (PARTITION BY connection_id ORDER BY verified_at DESC) AS rn
       FROM agent_upgrade_audit
       WHERE connection_id IN (${names.join(', ')})
         AND verified_at IS NOT NULL
     ) WHERE rn = 1`,
    binds
  );
  const map = {};
  for (const row of result.rows) {
    map[row.connection_id] = row;
  }
  return map;
}

/**
 * Get the last 20 post-upgrade validation runs for /status/installer display.
 * Joins oracle_connections for connection name.
 */
async function getRecentUpgradeVerifications({ limit = 20 } = {}) {
  const result = await pool.query(
    `SELECT a.id, a.connection_id, oc.name AS connection_name,
            a.from_version, a.to_version,
            a.verification_payload, a.verification_status, a.verified_at,
            a.triggered_at, a.completed_at, a.status AS upgrade_status
     FROM agent_upgrade_audit a
     LEFT JOIN oracle_connections oc ON oc.id = a.connection_id
     WHERE a.verified_at IS NOT NULL
     ORDER BY a.verified_at DESC
     FETCH FIRST :p1 ROWS ONLY`,
    { p1: limit }
  );
  return result.rows;
}

/**
 * Mark a connection as upgrade-degraded when verification fails.
 * Sets oracle_connections.status = 'upgrade-degraded'.
 */
async function markConnectionUpgradeDegraded(connectionId) {
  await pool.query(
    `UPDATE oracle_connections
     SET status = 'upgrade-degraded'
     WHERE id = :p1`,
    { p1: connectionId }
  );
}

/**
 * Clear upgrade-degraded status after a successful re-verification.
 *
 * Resets to 'active' (the column default), NOT NULL — status is NOT NULL, so the
 * old `SET status = NULL` would ORA-01407 the moment a row actually reached
 * 'upgrade-degraded' (pre-existing bug #10c, previously latent because #10b kept
 * any row from getting there).
 */
async function clearUpgradeDegradedStatus(connectionId) {
  await pool.query(
    `UPDATE oracle_connections
     SET status = 'active'
     WHERE id = :p1
       AND status = 'upgrade-degraded'`,
    { p1: connectionId }
  );
}

module.exports = {
  saveVerification,
  insertPostUpgradeValidationRun,
  getLatestVerificationForConnection,
  getLatestVerificationsForConnections,
  getRecentUpgradeVerifications,
  markConnectionUpgradeDegraded,
  clearUpgradeDegradedStatus,
};
