/**
 * db/validation-runs.js — Persistence for validation_runs table.
 *
 * Owns: CRUD on validation_runs (create, read, update results, share token).
 * Does NOT own: suite orchestration (services/validation-suite.js),
 *               API endpoints (routes/validation-suite.js).
 */

'use strict';

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

/**
 * Create a new validation run in 'running' state.
 * Returns { id, started_at }.
 */
async function createRun(connectionId, userId) {
  const ins = await pool.query(
    `INSERT INTO validation_runs (connection_id, user_id, status, full_results_json)
     VALUES (:p1, :p2, 'running', '[]')
     RETURNING id INTO :out_id`,
    { p1: connectionId, p2: userId || null, out_id: pool.outNumber() }
  );
  const result = await pool.query(
    `SELECT id, started_at FROM validation_runs WHERE id = :p1`, { p1: ins.outBinds.out_id[0] });
  return result.rows[0];
}

/**
 * Append a single result row to full_results_json.
 * row: { category, name, status, duration_ms, detail }
 */
async function appendResult(runId, row) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const { rows } = await client.query(
      `SELECT full_results_json FROM validation_runs WHERE id = :p1 FOR UPDATE`, { p1: runId });
    if (!rows.length) { await client.query('ROLLBACK'); return; }
    const cur = Array.isArray(rows[0].full_results_json) ? rows[0].full_results_json : [];
    await client.query(
      `UPDATE validation_runs SET full_results_json = :p2 WHERE id = :p1`,
      { p1: runId, p2: pool.clob(JSON.stringify(cur.concat([row]))) });
    await client.query('COMMIT');
  } catch (err) {
    await client.query('ROLLBACK').catch(() => {});
    throw err;
  } finally {
    client.release();
  }
}

/**
 * Finish a run: set status, finished_at, summary_json.
 */
async function finishRun(runId, status, summaryJson) {
  await pool.query(
    `UPDATE validation_runs
     SET status       = :p1,
         finished_at  = CURRENT_TIMESTAMP,
         summary_json = :p2
     WHERE id = :p3`,
    { p1: status, p2: pool.clob(JSON.stringify(summaryJson)), p3: runId }
  );
}

/**
 * Fetch a single run by id (for polling + report view).
 */
async function getRun(runId) {
  const result = await pool.query(
    `SELECT vr.id, vr.connection_id, vr.user_id, vr.started_at, vr.finished_at,
            vr.status, vr.share_token, vr.share_expires_at,
            vr.summary_json, vr.full_results_json,
            oc.name AS connection_name
     FROM validation_runs vr
     LEFT JOIN oracle_connections oc ON oc.id = vr.connection_id
     WHERE vr.id = :p1`,
    { p1: runId }
  );
  return result.rows[0] || null;
}

/**
 * Fetch run by share_token (public access, no auth required).
 * Returns null if token expired.
 */
async function getRunByToken(token) {
  const result = await pool.query(
    `SELECT vr.id, vr.connection_id, vr.started_at, vr.finished_at,
            vr.status, vr.share_token, vr.share_expires_at,
            vr.summary_json, vr.full_results_json,
            oc.name AS connection_name
     FROM validation_runs vr
     LEFT JOIN oracle_connections oc ON oc.id = vr.connection_id
     WHERE vr.share_token = :p1
       AND vr.share_expires_at > CURRENT_TIMESTAMP`,
    { p1: token }
  );
  return result.rows[0] || null;
}

/**
 * Get last 10 runs for a connection (history list).
 */
async function getRunHistory(connectionId, limit = 10) {
  const result = await pool.query(
    `SELECT id, started_at, finished_at, status, summary_json
     FROM validation_runs
     WHERE connection_id = :p1
     ORDER BY started_at DESC
     FETCH FIRST :p2 ROWS ONLY`,
    { p1: connectionId, p2: limit }
  );
  return result.rows;
}

/**
 * Issue or re-issue a 7-day share token for a finished run.
 * Returns the token string.
 */
async function issueShareToken(runId) {
  // HMAC-based token: run ID + random nonce, keyed by SESSION_SECRET so forgeable only server-side
  const secret = process.env.SESSION_SECRET || 'tunevault-dev-secret';
  const nonce  = crypto.randomBytes(16).toString('hex');
  const token  = crypto.createHmac('sha256', secret)
                       .update(`vrun:${runId}:${nonce}`)
                       .digest('hex') + nonce;

  const expires = new Date(Date.now() + 7 * 24 * 60 * 60 * 1000); // 7 days
  await pool.query(
    `UPDATE validation_runs
     SET share_token = :p1, share_expires_at = :p2
     WHERE id = :p3`,
    { p1: token, p2: pool.tstz(expires), p3: runId }
  );
  return token;
}

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

module.exports = {
  createRun,
  appendResult,
  finishRun,
  getRun,
  getRunByToken,
  getRunHistory,
  issueShareToken,
  getActiveRun,
};
