/**
 * db/first-run.js — First-run health pack queries.
 *
 * Owns: queries for the first-run wow-moment feature:
 *       connection data for triggering, HC status polling,
 *       finding retrieval + ranking, AI summary caching,
 *       first_run_completed_at lifecycle management.
 * Does NOT own: health check execution (server.js via app.locals),
 *               AI inference (lib/polsia-ai.js),
 *               finding weights (config/finding_weights.json).
 */

'use strict';

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

// Load finding weights once at startup (file-based, no redeploy needed).
let _weights = null;
function getWeights() {
  if (!_weights) {
    try {
      const weightsPath = path.join(__dirname, '..', 'config', 'finding_weights.json');
      _weights = JSON.parse(fs.readFileSync(weightsPath, 'utf8'));
    } catch (err) {
      console.error('[first-run] Could not load finding_weights.json:', err.message);
      _weights = { severity_score: {}, blast_radius: {}, time_to_fix_inverse: {} };
    }
  }
  return _weights;
}

// ── Connection data ───────────────────────────────────────────────────────────

/**
 * Fetch all columns needed to trigger a health check + check first_run state.
 * Returns null if not found or not owned by userId.
 */
async function getConnectionForFirstRun(connectionId, userId) {
  const { rows } = await pool.query(
    `SELECT id, name, host, port, service_name, username, encrypted_password,
            connection_type, proxy_url, proxy_api_key_enc, user_id,
            first_run_completed_at, server_type, apps_pwd_enc, weblogic_pwd_enc
     FROM oracle_connections
     WHERE id = :p1 AND (user_id = :p2 OR user_id IS NULL)`,
    { p1: connectionId, p2: userId }
  );
  return rows[0] || null;
}

/**
 * Mark the first-run health pack as triggered. Idempotent via WHERE clause.
 * Returns true if the row was actually updated (i.e. we won the race).
 */
async function markFirstRunTriggered(connectionId) {
  const { rowCount } = await pool.query(
    `UPDATE oracle_connections
     SET first_run_completed_at = CURRENT_TIMESTAMP
     WHERE id = :p1 AND first_run_completed_at IS NULL`,
    { p1: connectionId }
  );
  return rowCount > 0;
}

// ── Health check status ───────────────────────────────────────────────────────

/**
 * Count prior completed health check runs for this connection.
 * Used to gate auto-trigger (only fires when count = 0 before the first pack).
 */
async function countPriorRuns(connectionId) {
  const { rows } = await pool.query(
    `SELECT COUNT(*) AS cnt
     FROM health_checks
     WHERE connection_id = :p1 AND is_demo = false AND status = 'completed'`,
    { p1: connectionId }
  );
  return rows[0].cnt;
}

/**
 * Fetch the most recent health check run for a connection.
 * Returns { id, status, overall_score, created_at, completed_at } or null.
 */
async function getLatestHealthRun(connectionId) {
  const { rows } = await pool.query(
    `SELECT id, status, overall_score, created_at, completed_at
     FROM health_checks
     WHERE connection_id = :p1 AND is_demo = false
     ORDER BY created_at DESC
     FETCH FIRST 1 ROWS ONLY`,
    { p1: connectionId }
  );
  return rows[0] || null;
}

// ── Findings retrieval + ranking ──────────────────────────────────────────────

/**
 * Score a single check_results row using data-driven weights.
 * Formula: severity_score × blast_radius × time_to_fix_inverse
 * Higher = more urgent. Easy to tune by editing finding_weights.json.
 */
function scoreRow(row) {
  const w = getWeights();
  const sev   = (w.severity_score[row.status]                     ?? w.severity_score['info']  ?? 1);
  const blast = (w.blast_radius[row.check_id]                     ?? w.blast_radius['default']  ?? 1);
  const ttfi  = (w.time_to_fix_inverse[row.check_id]              ?? w.time_to_fix_inverse['default'] ?? 1);
  return sev * blast * ttfi;
}

/**
 * Return the top N ranked findings from a connection's most recent completed run.
 * Only returns non-green findings (status red/amber/warning/critical/info).
 * Each row is augmented with `score` and `ai_summary` (may be null, caller generates it).
 *
 * Takes a CONNECTION id, not a health_checks.id (pre-existing bug #6): check_results
 * are stamped with a per-run UUID run_id that is independent of health_checks.id, so
 * the old `WHERE run_id = <health_checks.id>` compared a VARCHAR2 UUID to a NUMBER and
 * threw ORA-01722 on every real row. The latest run is derived from check_results
 * itself — the same correlation-by-recency db/fleet.js relies on.
 */
async function getTopFindings(connectionId, limit = 5) {
  const { rows } = await pool.query(
    `SELECT id, check_id, check_category, status,
            metric_name, metric_value, metric_unit,
            raw_payload, ai_summary, recommendation
     FROM check_results
     WHERE connection_id = :p1
       AND run_id = (
         SELECT run_id FROM check_results
         WHERE connection_id = :p1
         GROUP BY run_id
         ORDER BY MAX(executed_at) DESC
         FETCH FIRST 1 ROW ONLY
       )
       AND status NOT IN ('green','ok')
       AND check_category != 'error'
     ORDER BY
       CASE status
         WHEN 'red'      THEN 1
         WHEN 'critical' THEN 1
         WHEN 'amber'    THEN 2
         WHEN 'warning'  THEN 2
         WHEN 'info'     THEN 3
         ELSE 4
       END,
       check_id`,
    { p1: connectionId }
  );

  // Apply data-driven weights and sort descending
  const scored = rows
    .map(r => ({ ...r, _score: scoreRow(r), raw_payload: r.raw_payload || {} }))
    .sort((a, b) => b._score - a._score);

  return scored.slice(0, limit);
}

/**
 * Persist an AI-generated summary back to the check_results row.
 * Fire-and-forget safe — caller should not await if non-blocking preferred.
 */
async function cacheAiSummary(checkResultId, aiSummary) {
  await pool.query(
    `UPDATE check_results SET ai_summary = :p1 WHERE id = :p2 AND ai_summary IS NULL`,
    { p1: pool.clob(aiSummary), p2: checkResultId }
  );
}

/**
 * Mark a check_results row as operator-resolved.
 * Ownership enforced via check_results.connection_id (the route has already
 * verified the caller owns this connection).
 *
 * pre-existing bug #6: the old guard was `run_id IN (SELECT id FROM health_checks
 * WHERE connection_id = :p2)`, comparing the VARCHAR2 UUID run_id to a NUMBER
 * health_checks.id — ORA-01722, so no row ever matched. check_results carries
 * connection_id directly, so scope on that instead (no health_checks join needed).
 */
async function resolveCheckResult(checkResultId, connectionId) {
  await pool.query(
    `UPDATE check_results
     SET status = 'ok',
         ai_summary = COALESCE(ai_summary, TO_CLOB('Marked resolved by operator.'))
     WHERE id = :p1 AND connection_id = :p2`,
    { p1: checkResultId, p2: connectionId }
  );
}

/**
 * Snooze a check_results row for 24 hours (writes snoozed_until to raw_payload JSONB).
 * Returns the snoozed_until ISO timestamp.
 */
async function snoozeCheckResult(checkResultId, connectionId) {
  const snoozedUntil = new Date(Date.now() + 24 * 60 * 60 * 1000).toISOString();
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    // Scope by check_results.connection_id directly (pre-existing bug #6 — the
    // old run_id IN (SELECT id FROM health_checks …) compared a UUID to a NUMBER
    // and matched nothing). The route already verified connection ownership.
    const { rows } = await client.query(
      `SELECT raw_payload FROM check_results
        WHERE id = :p1 AND connection_id = :p2
        FOR UPDATE`,
      { p1: checkResultId, p2: connectionId }
    );
    if (rows.length) {
      const cur = rows[0].raw_payload && typeof rows[0].raw_payload === 'object' ? rows[0].raw_payload : {};
      const next = { ...cur, snoozed_until: snoozedUntil };
      await client.query(
        `UPDATE check_results SET raw_payload = :p2 WHERE id = :p1`,
        { p1: checkResultId, p2: JSON.stringify(next) }
      );
    }
    await client.query('COMMIT');
  } catch (err) {
    await client.query('ROLLBACK').catch(() => {});
    throw err;
  } finally {
    client.release();
  }
  return snoozedUntil;
}

module.exports = {
  getConnectionForFirstRun,
  markFirstRunTriggered,
  countPriorRuns,
  getLatestHealthRun,
  getTopFindings,
  cacheAiSummary,
  resolveCheckResult,
  snoozeCheckResult,
};
