/**
 * db/installer-validation.js — installer_validation_runs persistence.
 *
 * Owns: installer_validation_runs table (read + write).
 * Does NOT own: triggering runs (routes/installer-validation.js),
 *               GitHub Actions workflow (CI layer).
 *
 * Supports two row types:
 *   greenfield  — os='ol7'|'ol8', topology='greenfield-OL7'|'greenfield-OL8'
 *   EBS live    — os='ebs12210',  topology='live-EBS-12.2.10-db-dev',
 *                 checks_passed/checks_total/ssh_runbook_executed populated
 */

'use strict';

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

/**
 * Insert a new run row. Returns the created row.
 * @param {object} run
 * @param {string} run.run_id        - UUID for this run batch
 * @param {string} run.os            - 'ol7' | 'ol8' | 'ebs12210'
 * @param {string} run.trigger_source - 'cron' | 'deploy_webhook' | 'manual'
 * @param {string} [run.topology]    - topology label (optional, set on insert for EBS rows)
 */
async function insertRun({ run_id, os, trigger_source = 'cron', topology = null }) {
  const ins = await pool.query(
    `INSERT INTO installer_validation_runs (run_id, os, trigger_source, topology)
     VALUES (:p1, :p2, :p3, :p4)
     RETURNING id INTO :out_id`,
    { p1: run_id, p2: os, p3: trigger_source, p4: topology, out_id: pool.outNumber() }
  );
  const result = await pool.query(
    `SELECT * FROM installer_validation_runs WHERE id = :p1`,
    { p1: ins.outBinds.out_id[0] }
  );
  return result.rows[0];
}

/**
 * Update a run row with probe results and final outcome.
 * @param {number} id - row id
 * @param {object} data - fields to update
 */
async function updateRun(id, data) {
  const allowed = [
    'finished_at', 'install_sha', 'agent_version', 'kernel_version',
    'probe_1_status', 'probe_1_ms', 'probe_1_error',
    'probe_2_status', 'probe_2_ms', 'probe_2_error',
    'probe_3_status', 'probe_3_ms', 'probe_3_error',
    'probe_4_status', 'probe_4_ms', 'probe_4_error',
    'probe_5_status', 'probe_5_ms', 'probe_5_error',
    'probe_6_status', 'probe_6_ms', 'probe_6_error',
    'probe_7_status', 'probe_7_ms', 'probe_7_error',
    'overall', 'duration_total_ms', 'error_message',
    // EBS topology fields
    'topology', 'checks_passed', 'checks_total', 'ssh_runbook_executed',
  ];

  const updates = [];
  const binds = {};
  let idx = 1;

  for (const key of allowed) {
    if (key in data) {
      const p = `p${idx++}`;
      updates.push(`${key} = :${p}`);
      binds[p] = key === 'ssh_runbook_executed' ? pool.bool(data[key]) : data[key];
    }
  }

  if (updates.length === 0) return null;

  binds.p_id = id;
  const res = await pool.query(
    `UPDATE installer_validation_runs
     SET ${updates.join(', ')}
     WHERE id = :p_id`,
    binds
  );
  if (!res.rowCount) return null;
  const result = await pool.query(
    `SELECT * FROM installer_validation_runs WHERE id = :p_id`,
    { p_id: id }
  );
  return result.rows[0];
}

/**
 * Get the latest run for each OS (OL7 + OL8 greenfield only).
 * Returns { ol7: row|null, ol8: row|null }
 */
async function getLatestRuns() {
  const result = await pool.query(`
    SELECT * FROM (
      SELECT t.*, ROW_NUMBER() OVER (PARTITION BY os ORDER BY started_at DESC) AS rn
      FROM installer_validation_runs t
      WHERE overall != 'pending'
        AND os IN ('ol7', 'ol8')
    ) WHERE rn = 1
  `);
  const out = { ol7: null, ol8: null };
  for (const row of result.rows) {
    out[row.os] = row;
  }
  return out;
}

/**
 * Get the latest EBS live-topology validation run.
 * Returns the most recent row with os='ebs12210', or null.
 */
async function getLatestEbsRun() {
  const result = await pool.query(`
    SELECT *
    FROM installer_validation_runs
    WHERE os = 'ebs12210'
      AND overall != 'pending'
    ORDER BY started_at DESC
    FETCH FIRST 1 ROWS ONLY
  `);
  return result.rows[0] || null;
}

/**
 * Get 30-day history for EBS topology runs.
 */
async function getEbsHistory() {
  const result = await pool.query(`
    SELECT id, run_id, started_at, overall, duration_total_ms, install_sha,
           trigger_source, agent_version, checks_passed, checks_total, ssh_runbook_executed
    FROM installer_validation_runs
    WHERE os = 'ebs12210'
      AND overall != 'pending'
      AND started_at > CURRENT_TIMESTAMP - INTERVAL '30' DAY
    ORDER BY started_at DESC
    FETCH FIRST 120 ROWS ONLY
  `);
  return result.rows;
}

/**
 * Get 30-day history for a given OS (one row per run, newest first).
 * Returns up to 120 runs (4 per day × 30 days).
 */
async function getHistory(os) {
  const result = await pool.query(
    `SELECT id, run_id, started_at, overall, duration_total_ms, install_sha, trigger_source
     FROM installer_validation_runs
     WHERE os = :p1
       AND overall != 'pending'
       AND started_at > CURRENT_TIMESTAMP - INTERVAL '30' DAY
     ORDER BY started_at DESC
     FETCH FIRST 120 ROWS ONLY`,
    { p1: os }
  );
  return result.rows;
}

/**
 * Get a single run by id.
 */
async function getRunById(id) {
  const result = await pool.query(
    `SELECT * FROM installer_validation_runs WHERE id = :p1`,
    { p1: id }
  );
  return result.rows[0] || null;
}

/**
 * Get all pending runs (started_at > 30min ago means stale — mark as error).
 * Used by cleanup job on startup.
 */
async function getStaleRuns() {
  const result = await pool.query(`
    SELECT id FROM installer_validation_runs
    WHERE overall = 'pending'
      AND started_at < CURRENT_TIMESTAMP - INTERVAL '30' MINUTE
  `);
  return result.rows;
}

/**
 * Mark stale pending runs as error (cleanup on startup).
 */
async function markStaleRunsAsError() {
  await pool.query(`
    UPDATE installer_validation_runs
    SET overall = 'error',
        error_message = 'Run timed out — no result received',
        finished_at = CURRENT_TIMESTAMP
    WHERE overall = 'pending'
      AND started_at < CURRENT_TIMESTAMP - INTERVAL '30' MINUTE
  `);
}

/**
 * Find an existing run row by run_id + os. Returns null if not found.
 */
async function findRunByRunId(run_id, os) {
  const result = await pool.query(
    `SELECT id FROM installer_validation_runs WHERE run_id = :p1 AND os = :p2 FETCH FIRST 1 ROWS ONLY`,
    { p1: run_id, p2: os }
  );
  return result.rows[0] || null;
}

module.exports = {
  insertRun,
  updateRun,
  getLatestRuns,
  getLatestEbsRun,
  getEbsHistory,
  getHistory,
  getRunById,
  getStaleRuns,
  markStaleRunsAsError,
  findRunByRunId,
};
