/**
 * db/fleet.js — Fleet overview query functions.
 *
 * Owns: aggregated per-connection fleet status (last check, severity counts, drift deltas,
 *       schedule state). Read-only — no mutations.
 * Does NOT own: connection CRUD, health check execution, finding_history mutations (see db/schedules.js).
 */

'use strict';

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

/**
 * Get fleet overview for a user (or all connections for admin).
 *
 * Returns one row per oracle_connection with:
 *   connection_id, name, env_tag, db_version, ebs_detected,
 *   last_check_at, status (ok|amber|red|stale|never_run),
 *   red_count, amber_count,
 *   top_finding_title, top_finding_severity,
 *   drift_since_last_run: { new, resolved, worsened },
 *   autonomous_enabled, next_run_at
 *
 * Status logic:
 *   never_run — no completed health_check ever
 *   stale     — last completed check > 24h ago
 *   red       — any check_results severity=red in latest run
 *   amber     — any check_results severity=amber in latest run (no red)
 *   ok        — latest run exists, no red/amber findings
 *
 * @param {number|null} userId — null means admin (returns all connections)
 */
async function getFleetOverview(userId) {
  // Step 1: Get all connections scoped to the user (or all for admin).
  const connParams = {};
  const connWhere = userId !== null
    ? 'WHERE oc.user_id = :p1'
    : '';
  if (userId !== null) connParams.p1 = userId;

  // connection_id = ANY($arr) has no Oracle equivalent (arrays cannot bind), so
  // every array filter below is expanded to an IN list of one named bind each.
  // Each query gets its OWN binds object, so reusing the 'c'/'r' prefixes is safe.
  const inList = (arr, prefix) => {
    const binds = {};
    const names = arr.map((v, i) => { binds[`${prefix}${i}`] = v; return `:${prefix}${i}`; });
    return { clause: names.join(', '), binds };
  };

  let connResult;
  try {
    connResult = await pool.query(
      `SELECT
         oc.id                AS connection_id,
         oc.name,
         oc.oracle_version    AS db_version,
         oc.is_ebs            AS ebs_detected,
         oc.server_type,
         oc.ebs_instance_name,
         oc.user_id
       FROM oracle_connections oc
       ${connWhere}
       ORDER BY oc.created_at DESC`,
      connParams
    );
  } catch (err) {
    // Defensive: if a newer column (server_type/ebs_instance_name/is_ebs) is not
    // yet in the DB, fall back to a null projection. ORA-00904 "invalid
    // identifier" is Oracle's missing-column signal (Postgres used 42703). The
    // projected NULLs need no ::type cast — there is no UNION to infer against.
    if (err.code === '42703' || /column/i.test(err.message) || /ORA-00904/.test(err.message)) {
      connResult = await pool.query(
        `SELECT
           oc.id                AS connection_id,
           oc.name,
           oc.oracle_version    AS db_version,
           NULL                 AS ebs_detected,
           NULL                 AS server_type,
           NULL                 AS ebs_instance_name,
           oc.user_id
         FROM oracle_connections oc
         ${connWhere}
         ORDER BY oc.created_at DESC`,
        connParams
      );
    } else {
      throw err;
    }
  }

  if (connResult.rows.length === 0) return [];

  const connectionIds = connResult.rows.map(r => r.connection_id);

  // Step 2: Latest completed health_check per connection.
  const lc = inList(connectionIds, 'c');
  const latestChecks = await pool.query(
    `SELECT connection_id, hc_id, run_id, overall_score, last_check_at, rn FROM (
       SELECT
         connection_id,
         id   AS hc_id,
         id   AS run_id,
         overall_score,
         completed_at AS last_check_at,
         ROW_NUMBER() OVER (PARTITION BY connection_id ORDER BY completed_at DESC) AS rn
       FROM health_checks
       WHERE connection_id IN (${lc.clause})
         AND status = 'completed'
         AND is_demo = false
     ) WHERE rn = 1`,
    lc.binds
  );

  const latestByConn = {};
  for (const r of latestChecks.rows) {
    latestByConn[r.connection_id] = r;
  }

  // Step 3: Severity counts from check_results.
  // check_results uses UUID run_id independent of health_checks.id
  // Join by connection_id and get latest run_id per connection
  const runIds = [];
  if (connectionIds.length > 0) {
    const lr = inList(connectionIds, 'c');
    const latestRunResult = await pool.query(
      `SELECT connection_id, run_id, rn FROM (
         SELECT connection_id, run_id,
                ROW_NUMBER() OVER (PARTITION BY connection_id ORDER BY executed_at DESC) AS rn
         FROM check_results
         WHERE connection_id IN (${lr.clause})
       ) WHERE rn = 1`,
      lr.binds
    );
    for (const r of latestRunResult.rows) {
      runIds.push(r.run_id);
      // Update latestByConn with the correct UUID run_id
      if (latestByConn[r.connection_id]) {
        latestByConn[r.connection_id].run_id = r.run_id;
      }
    }
  }
  const _unusedRunIds = runIds; // keep for compatibility

  let severityByRun = {};
  let topFindingByRun = {};

  if (runIds.length > 0) {
    const sr = inList(runIds, 'r');
    const sevResult = await pool.query(
      `SELECT
         run_id,
         COUNT(CASE WHEN status IN ('red','critical')  THEN 1 END) AS red_count,
         COUNT(CASE WHEN status IN ('amber','warning') THEN 1 END) AS amber_count
       FROM check_results
       WHERE run_id IN (${sr.clause})
         AND status NOT IN ('ok','green','info','pass','skipped','n/a')
       GROUP BY run_id`,
      sr.binds
    );
    for (const r of sevResult.rows) {
      severityByRun[r.run_id] = {
        red_count: parseInt(r.red_count, 10) || 0,
        amber_count: parseInt(r.amber_count, 10) || 0
      };
    }

    // Top finding (worst severity) per run.
    const tr = inList(runIds, 'r');
    const topResult = await pool.query(
      `SELECT run_id, title, severity, rn FROM (
         SELECT
           run_id,
           check_id AS title,
           status AS severity,
           ROW_NUMBER() OVER (
             PARTITION BY run_id
             ORDER BY CASE status
                        WHEN 'critical' THEN 1
                        WHEN 'red'      THEN 2
                        WHEN 'amber'    THEN 3
                        WHEN 'warning'  THEN 4
                        ELSE 5
                      END
           ) AS rn
         FROM check_results
         WHERE run_id IN (${tr.clause})
           AND status IN ('red','critical','amber','warning')
       ) WHERE rn = 1`,
      tr.binds
    );
    for (const r of topResult.rows) {
      topFindingByRun[r.run_id] = { title: r.title, severity: r.severity };
    }
  }

  // Step 4: Drift counts from finding_history (new/worsened/resolved since prev run).
  // "New" = first_seen_at >= start of last run; "resolved" = resolved_at >= start of last run.
  let driftByConn = {};
  if (connectionIds.length > 0) {
    const dr = inList(connectionIds, 'c');
    const driftResult = await pool.query(
      `SELECT
         fh.connection_id,
         COUNT(CASE
           WHEN fh.resolved_at IS NULL
             AND fh.first_seen_at >= CURRENT_TIMESTAMP - INTERVAL '25' HOUR
           THEN 1 END) AS new_count,
         COUNT(CASE
           WHEN fh.resolved_at IS NOT NULL
             AND fh.resolved_at >= CURRENT_TIMESTAMP - INTERVAL '25' HOUR
           THEN 1 END) AS resolved_count,
         0 AS worsened_count
       FROM finding_history fh
       WHERE fh.connection_id IN (${dr.clause})
       GROUP BY fh.connection_id`,
      dr.binds
    );
    for (const r of driftResult.rows) {
      driftByConn[r.connection_id] = {
        new: parseInt(r.new_count, 10) || 0,
        resolved: parseInt(r.resolved_count, 10) || 0,
        worsened: parseInt(r.worsened_count, 10) || 0
      };
    }
  }

  // Step 5: Schedule state per connection.
  let scheduleByConn = {};
  if (connectionIds.length > 0) {
    const sc = inList(connectionIds, 'c');
    const schedResult = await pool.query(
      `SELECT connection_id, enabled, next_run_at
       FROM connection_schedules
       WHERE connection_id IN (${sc.clause})`,
      sc.binds
    );
    for (const r of schedResult.rows) {
      scheduleByConn[r.connection_id] = {
        autonomous_enabled: r.enabled,
        next_run_at: r.next_run_at
      };
    }
  }

  // Step 6: Assemble the response rows.
  const now = Date.now();
  const STALE_MS = 24 * 60 * 60 * 1000;

  return connResult.rows.map(conn => {
    const latest = latestByConn[conn.connection_id];
    const sev = latest ? (severityByRun[latest.run_id] || { red_count: 0, amber_count: 0 }) : null;
    const top = latest ? (topFindingByRun[latest.run_id] || null) : null;
    const drift = driftByConn[conn.connection_id] || { new: 0, resolved: 0, worsened: 0 };
    const sched = scheduleByConn[conn.connection_id] || { autonomous_enabled: false, next_run_at: null };

    // Compute status
    let status;
    if (!latest) {
      status = 'never_run';
    } else if (now - new Date(latest.last_check_at).getTime() > STALE_MS) {
      status = 'stale';
    } else if (sev && sev.red_count > 0) {
      status = 'red';
    } else if (sev && sev.amber_count > 0) {
      status = 'amber';
    } else {
      status = 'ok';
    }

    return {
      connection_id:       conn.connection_id,
      name:                conn.name || 'Unnamed',
      env_tag:             deriveEnvTag(conn.name),
      db_version:          conn.db_version || null,
      ebs_detected:        conn.ebs_detected || false,
      server_type:         conn.server_type  || null,
      ebs_instance_name:   conn.ebs_instance_name || null,
      last_check_at:       latest ? latest.last_check_at : null,
      overall_score:       latest ? (latest.overall_score || 0) : null,
      latest_hc_id:        latest ? (latest.hc_id || null) : null,
      status,
      red_count:           sev ? sev.red_count   : 0,
      amber_count:         sev ? sev.amber_count : 0,
      top_finding_title:   top ? top.title    : null,
      top_finding_severity:top ? top.severity : null,
      drift_since_last_run: drift,
      autonomous_enabled:  sched.autonomous_enabled,
      next_run_at:         sched.next_run_at
    };
  });
}

/**
 * Derive env tag from connection name.
 * Looks for PROD, UAT, DEV, TEST, STG/STAGE in the name (case-insensitive).
 * Returns 'PROD' | 'UAT' | 'DEV' | 'TEST' | 'STG' | null.
 */
function deriveEnvTag(name) {
  if (!name) return null;
  const u = name.toUpperCase();
  if (/\bPROD\b/.test(u)) return 'PROD';
  if (/\bUAT\b/.test(u))  return 'UAT';
  if (/\bSTG\b|\bSTAGE\b/.test(u)) return 'STG';
  if (/\bTEST\b/.test(u)) return 'TEST';
  if (/\bDEV\b/.test(u))  return 'DEV';
  return null;
}

module.exports = { getFleetOverview };
