/**
 * db/install-jobs.js — SSH push-install job persistence.
 *
 * Owns: install_jobs rows (audit trail, status, timing)
 *       job_log_lines rows (per-job stdout/stderr append)
 * Does NOT own: SSH credentials (never written to DB — held in-memory only),
 *               oracle_connections CRUD (db/agent.js),
 *               agent tunnel records (db/agent.js).
 */

'use strict';

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

/**
 * Create a new install job row.
 * Returns the persisted row with auto-assigned id.
 */
async function createJob({ userId, connectionId, host, sshPort, sshUser }) {
  const ins = await pool.query(
    `INSERT INTO install_jobs
       (user_id, connection_id, host, ssh_port, ssh_user, status, started_at, created_at)
     VALUES (:p1, :p2, :p3, :p4, :p5, 'queued', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
     RETURNING id INTO :out_id`,
    { p1: userId, p2: connectionId || null, p3: host, p4: sshPort || 22, p5: sshUser,
      out_id: pool.outNumber() }
  );
  const result = await pool.query(
    `SELECT id, user_id, connection_id, host, ssh_port, ssh_user, status, started_at
     FROM install_jobs WHERE id = :p1`,
    { p1: ins.outBinds.out_id[0] }
  );
  return result.rows[0];
}

/**
 * Transition a job to a new lifecycle status.
 * Valid statuses: queued → connecting → preflight → installing → verifying → success | failed
 */
async function setStatus(jobId, status) {
  await pool.query(
    `UPDATE install_jobs SET status = :p2 WHERE id = :p1`,
    { p1: jobId, p2: status }
  );
}

/**
 * Mark job complete (success or failed) with optional exit code and error message.
 */
async function finishJob(jobId, { status, exitCode, errorMessage, connectionId }) {
  await pool.query(
    `UPDATE install_jobs
     SET status        = :p2,
         exit_code     = :p3,
         error_message = :p4,
         connection_id = COALESCE(:p5, connection_id),
         finished_at   = CURRENT_TIMESTAMP,
         duration_ms   = ROUND((
           EXTRACT(DAY    FROM (SYSTIMESTAMP - started_at)) * 86400 +
           EXTRACT(HOUR   FROM (SYSTIMESTAMP - started_at)) * 3600 +
           EXTRACT(MINUTE FROM (SYSTIMESTAMP - started_at)) * 60 +
           EXTRACT(SECOND FROM (SYSTIMESTAMP - started_at))
         ) * 1000)
     WHERE id = :p1`,
    { p1: jobId, p2: status, p3: exitCode ?? null, p4: errorMessage ?? null,
      p5: pool.num(connectionId ?? null) }
  );
}

/**
 * Append a log line (fire-and-forget friendly — caller should .catch(() => {})).
 * seq is caller-managed for ordering guarantee (pass monotonically increasing integer).
 */
async function appendLine(jobId, { seq, stream, line }) {
  await pool.query(
    `INSERT INTO job_log_lines (job_id, seq, stream, line, ts)
     VALUES (:p1, :p2, :p3, :p4, CURRENT_TIMESTAMP)`,
    // job_log_lines.line is VARCHAR2(4000 CHAR) on Oracle (Neon's was unbounded
    // text). Slice to the column width, not 4096, or a long line overflows with
    // ORA-12899 — and appendLine is fire-and-forget, so the whole line would be
    // silently dropped rather than truncated.
    { p1: jobId, p2: seq, p3: stream || 'stdout', p4: (line || '').slice(0, 4000) }
  );
}

/**
 * Get a single job by id (ownership check is caller's responsibility).
 */
async function getJob(jobId) {
  const result = await pool.query(
    `SELECT id, user_id, connection_id, host, ssh_port, ssh_user,
            status, exit_code, error_message, started_at, finished_at, duration_ms
     FROM install_jobs WHERE id = :p1`,
    { p1: jobId }
  );
  return result.rows[0] || null;
}

/**
 * Count active (non-terminal) jobs for a given user_id.
 * Used for the 3-concurrent cap.
 */
async function countActiveForUser(userId) {
  const result = await pool.query(
    `SELECT COUNT(*) AS cnt FROM install_jobs
     WHERE user_id = :p1 AND status NOT IN ('success','failed')`,
    { p1: userId }
  );
  return parseInt(result.rows[0].cnt, 10);
}

/**
 * Count jobs started within the last 24 hours for a given user_id.
 * Used for the 20/day cap.
 */
async function countTodayForUser(userId) {
  const result = await pool.query(
    `SELECT COUNT(*) AS cnt FROM install_jobs
     WHERE user_id = :p1 AND started_at > CURRENT_TIMESTAMP - INTERVAL '24' HOUR`,
    { p1: userId }
  );
  return parseInt(result.rows[0].cnt, 10);
}

/**
 * Recent log lines for a job in sequence order (used to replay on reconnect).
 */
async function getLogLines(jobId, afterSeq = 0) {
  const result = await pool.query(
    `SELECT seq, stream, line, ts FROM job_log_lines
     WHERE job_id = :p1 AND seq > :p2
     ORDER BY seq ASC`,
    { p1: jobId, p2: afterSeq }
  );
  return result.rows;
}

/**
 * Set the connection_id on an in-flight job once the draft connection is created.
 */
async function setConnectionId(jobId, connectionId) {
  await pool.query(
    `UPDATE install_jobs SET connection_id = :p2 WHERE id = :p1`,
    { p1: jobId, p2: connectionId }
  );
}

module.exports = {
  createJob,
  setStatus,
  finishJob,
  appendLine,
  getJob,
  countActiveForUser,
  countTodayForUser,
  getLogLines,
  setConnectionId,
};
