/**
 * db/smoke-test-runs.js — Smoke test run persistence for smoke_test_runs table.
 *
 * Owns: CRUD on smoke_test_runs (create, read, update steps/status).
 * Does NOT own: smoke test execution logic (routes/admin-agent-smoke-test.js),
 *               agent channel communication (services/agent-channel.js).
 */

'use strict';

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

/**
 * Create a new smoke test run row with status='running'.
 * Returns the new run's id and started_at.
 */
async function createRun(connectionId, triggeredByUserId) {
  const ins = await pool.query(
    `INSERT INTO smoke_test_runs (connection_id, triggered_by_user_id, steps_jsonb, overall_status)
     VALUES (:p1, :p2, '[]', 'running')
     RETURNING id INTO :out_id`,
    { p1: connectionId, p2: triggeredByUserId || null, out_id: pool.outNumber() }
  );
  const result = await pool.query(
    `SELECT id, started_at FROM smoke_test_runs WHERE id = :p1`,
    { p1: ins.outBinds.out_id[0] }
  );
  return result.rows[0];
}

/**
 * Append a completed step to steps_jsonb and persist.
 * step: { step, label, status, duration_ms, detail, error_msg }
 */
async function appendStep(runId, step) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const { rows } = await client.query(
      `SELECT steps_jsonb FROM smoke_test_runs WHERE id = :p1 FOR UPDATE`, { p1: runId });
    if (!rows.length) { await client.query('ROLLBACK'); return; }
    const cur = Array.isArray(rows[0].steps_jsonb) ? rows[0].steps_jsonb : [];
    await client.query(
      `UPDATE smoke_test_runs SET steps_jsonb = :p2 WHERE id = :p1`,
      { p1: runId, p2: JSON.stringify(cur.concat([step])) });
    await client.query('COMMIT');
  } catch (err) {
    await client.query('ROLLBACK').catch(() => {});
    throw err;
  } finally {
    client.release();
  }
}

/**
 * Mark the run finished with overall_status and record finished_at.
 */
async function finishRun(runId, overallStatus) {
  await pool.query(
    `UPDATE smoke_test_runs
     SET overall_status = :p2, finished_at = CURRENT_TIMESTAMP
     WHERE id = :p1`,
    { p1: runId, p2: overallStatus }
  );
}

/**
 * Fetch a run by id (for polling).
 */
async function getRun(runId) {
  const result = await pool.query(
    `SELECT id, connection_id, started_at, finished_at, overall_status,
            steps_jsonb, triggered_by_user_id
     FROM smoke_test_runs
     WHERE id = :p1`,
    { p1: runId }
  );
  return result.rows[0] || null;
}

/**
 * Get the most recent run for a connection (for UI display).
 */
async function getLatestRun(connectionId) {
  const result = await pool.query(
    `SELECT id, connection_id, started_at, finished_at, overall_status, steps_jsonb
     FROM smoke_test_runs
     WHERE connection_id = :p1
     ORDER BY started_at DESC
     FETCH FIRST 1 ROWS ONLY`,
    { p1: connectionId }
  );
  return result.rows[0] || null;
}

/**
 * Count runs today for this connection (for daily soft cap enforcement).
 */
async function countRunsToday(connectionId) {
  const result = await pool.query(
    `SELECT COUNT(*) AS cnt
     FROM smoke_test_runs
     WHERE connection_id = :p1
       AND started_at >= CURRENT_TIMESTAMP - INTERVAL '24' HOUR`,
    { p1: connectionId }
  );
  return parseInt(result.rows[0].cnt, 10);
}

/**
 * Check if a run is currently in flight (status='running') for this connection.
 */
async function getActiveRun(connectionId) {
  const result = await pool.query(
    `SELECT id, started_at
     FROM smoke_test_runs
     WHERE connection_id = :p1
       AND overall_status = 'running'
       AND started_at > CURRENT_TIMESTAMP - INTERVAL '5' MINUTE
     ORDER BY started_at DESC
     FETCH FIRST 1 ROWS ONLY`,
    { p1: connectionId }
  );
  return result.rows[0] || null;
}

module.exports = {
  createRun,
  appendStep,
  finishRun,
  getRun,
  getLatestRun,
  countRunsToday,
  getActiveRun,
};
