'use strict';

/**
 * routes/resilience.js — Oracle DB resilience scanner (backup, HA, recovery).
 *
 * Routes:
 *   POST /api/resilience/:connectionId/run     — run all 5 check groups, score 0-100, persist
 *   GET  /api/resilience/history/:scanId       — fetch single saved scan by ID (auth + ownership)
 *   GET  /api/resilience/:connectionId/history — last 10 scans for a connection
 *
 * Check groups (weighted average → score 0-100):
 *   backup_currency   30%  V$BACKUP_SET_DETAILS + V$RMAN_CONFIGURATION
 *   archivelog_mode   25%  V$DATABASE log_mode
 *   dataguard_lag     20%  V$MANAGED_STANDBY + V$DATAGUARD_STATS  (OPTIONAL if no standby)
 *   fra_headroom      15%  V$RECOVERY_FILE_DEST                    (OPTIONAL if FRA not configured)
 *   redo_log_health   10%  V$LOG + V$LOG_HISTORY
 */

const express = require('express');
const router  = express.Router();

const pool    = require('../db/index');
const { requireAuth } = require('../middleware/auth');
const { decrypt }     = require('../crypto-utils');
const channel         = require('../services/agent-channel');
const { getConnectionForScan, saveScan, getHistory, upsertFindings, resolveStaleFindings } = require('../db/resilience');

// ─── Group weights (non-optional must sum to 100) ────────────────────────────

const WEIGHTS = {
  backup_currency  : 30,
  archivelog_mode  : 25,
  dataguard_lag    : 20,
  fra_headroom     : 15,
  redo_log_health  : 10,
};

// Optional groups are excluded from the headline score when the feature is not
// configured (no standby for DG, no FRA for fra_headroom).
// The check functions themselves set optional:true dynamically.
const OPTIONAL_GROUPS = new Set(['dataguard_lag', 'fra_headroom']);

// ─── Status derivation ────────────────────────────────────────────────────────

function deriveStatus(score, findings) {
  if (score === null) return 'error';
  if (findings.some(f => f.severity === 'critical')) return 'fail';
  if (findings.length > 0) return 'warn';
  return 'pass';
}

function errorStatus(errMsg) {
  const msg = (errMsg || '').toLowerCase();
  if (/ora-00942|insufficient privileges|table or view does not exist/.test(msg)) return 'error';
  if (/timeout|timed out|etimedout/.test(msg)) return 'timeout';
  return 'partial';
}

// ─── Query function factories ─────────────────────────────────────────────────

function makeProxyQueryFn(targetConnId, sqlCreds) {
  return async function queryViaAgent(sql) {
    const agentOnline = await Promise.race([
      channel.isAgentConnected(targetConnId),
      new Promise(r => setTimeout(() => r(false), 5000)),
    ]);
    if (!agentOnline) throw new Error('Agent is not connected. Wait for agent check-in and retry.');

    const proxyResp = await channel.sendToAgent(targetConnId, {
      method: 'POST',
      path: '/api/run_sql',
      body: { sql, ...sqlCreds },
    }, 60000);

    const body = (proxyResp && proxyResp.body) ? proxyResp.body : {};
    if (!body.success) throw new Error(body.error || 'Proxy SQL failed');

    const cols    = body.columns || [];
    const rawRows = body.rows    || [];
    return {
      rows: rawRows.map(function(row) {
        const obj = {};
        cols.forEach(function(c, i) { obj[c] = row[i]; });
        return obj;
      }),
      columns: cols,
      rawRows,
    };
  };
}

function makeDirectQueryFn(conn, decryptedPassword, oracledb) {
  const connectString = `${conn.host}:${conn.port || 1521}/${conn.service_name}`;
  return async function queryDirect(sql) {
    let dbConn;
    try {
      dbConn = await oracledb.getConnection({
        user: conn.username,
        password: decryptedPassword,
        connectString,
        connectTimeout: 30,
      });
      const result = await dbConn.execute(sql, [], { outFormat: oracledb.OUT_FORMAT_OBJECT });
      const columns = (result.metaData || []).map(m => m.name);
      const rows    = result.rows || [];
      return { rows, columns, rawRows: rows.map(r => columns.map(c => r[c])) };
    } finally {
      if (dbConn) { try { await dbConn.close(); } catch (_) {} }
    }
  };
}

async function findPairedDbConnWithCreds(ebsInstanceName, userId) {
  if (!ebsInstanceName) return null;
  const { rows } = await pool.query(
    `SELECT id, host, port, service_name, username, encrypted_password
     FROM oracle_connections
     WHERE ebs_instance_name = $1
       AND user_id = $2
       AND server_type IN ('db', 'both')
       AND connection_type = 'proxy'
     ORDER BY server_type
     LIMIT 1`,
    [ebsInstanceName, userId]
  );
  if (!rows.length) return null;
  const r = rows[0];
  return {
    id:          r.id,
    host:        r.host,
    port:        r.port || 1521,
    serviceName: r.service_name,
    username:    r.username,
    password:    r.encrypted_password ? decrypt(r.encrypted_password) : '',
  };
}

// ─── Check groups ─────────────────────────────────────────────────────────────

/**
 * backup_currency — RMAN backup recency.
 * Score: 100 if last backup < 24h; 50 if 24-48h; 0 if > 48h or no backup at all.
 */
async function checkBackupCurrency(queryFn) {
  const findings = [];
  const queries  = [];
  let score = 100;

  const SQL_BACKUP = `SELECT MAX(completion_time) AS last_backup
    FROM v$backup_set_details
    WHERE backup_type IN ('D', 'I')`;

  const SQL_RMAN_CFG = `SELECT name, value FROM v$rman_configuration ORDER BY name`;

  let lastBackup = null;
  try {
    const r = await queryFn(SQL_BACKUP);
    lastBackup = ((r.rows || [])[0] || {}).LAST_BACKUP || null;
    queries.push({ sql: SQL_BACKUP, columns: r.columns || [], rawRows: r.rawRows || [] });
  } catch (err) {
    queries.push({ sql: SQL_BACKUP, columns: [], rawRows: [], error: err.message });
    return { id: 'backup_currency', name: 'Backup Currency', score: null, error: err.message, findings: [], status: errorStatus(err.message), queries };
  }

  try {
    const r2 = await queryFn(SQL_RMAN_CFG);
    queries.push({ sql: SQL_RMAN_CFG, columns: r2.columns || [], rawRows: r2.rawRows || [] });
  } catch (err) {
    queries.push({ sql: SQL_RMAN_CFG, columns: [], rawRows: [], error: err.message });
  }

  if (!lastBackup) {
    score = 0;
    findings.push({
      key: 'RMAN_NO_BACKUP',
      check_id: 'backup_currency',
      severity: 'critical',
      title: 'No RMAN backup found',
      metric_line: 'v$backup_set_details: no completed backup',
      remediation: 'No RMAN backup found. Configure and run: RMAN> BACKUP DATABASE PLUS ARCHIVELOG; Schedule daily via cron or Oracle Scheduler.',
    });
  } else {
    const backupDate = new Date(lastBackup);
    const ageHours   = (Date.now() - backupDate.getTime()) / (1000 * 60 * 60);
    if (ageHours > 48) {
      score = 0;
      findings.push({
        key: 'RMAN_LAST_BACKUP_STALE',
        check_id: 'backup_currency',
        severity: 'critical',
        title: `Last backup is ${Math.round(ageHours)} hours old — critically overdue`,
        metric_line: `last_backup = ${backupDate.toISOString().split('T')[0]}  (${Math.round(ageHours)}h ago)`,
        remediation: 'Last backup is over 24 hours old. Verify RMAN schedule is running. Check: SELECT * FROM V$RMAN_STATUS ORDER BY start_time DESC;',
      });
    } else if (ageHours > 24) {
      score = 50;
      findings.push({
        key: 'RMAN_LAST_BACKUP',
        check_id: 'backup_currency',
        severity: 'warning',
        title: `Last backup is ${Math.round(ageHours)} hours old`,
        metric_line: `last_backup = ${backupDate.toISOString().split('T')[0]}  (${Math.round(ageHours)}h ago)`,
        remediation: 'Last backup is over 24 hours old. Verify RMAN schedule is running. Check: SELECT * FROM V$RMAN_STATUS ORDER BY start_time DESC;',
      });
    }
  }

  return { id: 'backup_currency', name: 'Backup Currency', score, findings, status: deriveStatus(score, findings), queries };
}

/**
 * archivelog_mode — database archive log mode.
 * Score: 100 if ARCHIVELOG; 0 if NOARCHIVELOG.
 */
async function checkArchivelogMode(queryFn) {
  const findings = [];
  const queries  = [];
  let score = 100;

  const sql = `SELECT log_mode FROM v$database`;

  let rows;
  try {
    const r = await queryFn(sql);
    rows = r.rows || [];
    queries.push({ sql, columns: r.columns || [], rawRows: r.rawRows || [] });
  } catch (err) {
    queries.push({ sql, columns: [], rawRows: [], error: err.message });
    return { id: 'archivelog_mode', name: 'Archive Log Mode', score: null, error: err.message, findings: [], status: errorStatus(err.message), queries };
  }

  const logMode = ((rows[0] || {}).LOG_MODE || '').toUpperCase();

  if (logMode === 'NOARCHIVELOG') {
    score = 0;
    findings.push({
      key: 'ARCHIVELOG_DISABLED',
      check_id: 'archivelog_mode',
      severity: 'critical',
      title: 'Database is in NOARCHIVELOG mode — point-in-time recovery is impossible',
      metric_line: 'log_mode = NOARCHIVELOG',
      remediation: 'Database is in NOARCHIVELOG mode — point-in-time recovery is impossible. Enable with: SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN;',
    });
  } else if (logMode === 'ARCHIVELOG') {
    findings.push({
      key: 'ARCHIVELOG_ENABLED',
      check_id: 'archivelog_mode',
      severity: 'info',
      title: 'Database is in ARCHIVELOG mode',
      metric_line: 'log_mode = ARCHIVELOG',
      remediation: '',
    });
  }

  return { id: 'archivelog_mode', name: 'Archive Log Mode', score, findings: findings.filter(f => f.severity !== 'info'), status: deriveStatus(score, []), queries, detail: { log_mode: logMode } };
}

/**
 * dataguard_lag — Data Guard apply lag.
 * OPTIONAL when no standby is configured.
 * Score: 100 if lag < 60s; 70 if 60-300s; 0 if > 300s or transport broken.
 */
async function checkDataguardLag(queryFn) {
  const findings = [];
  const queries  = [];
  let score = 100;

  const SQL_STANDBY = `SELECT dest_id, status, type, database_mode FROM v$managed_standby WHERE rownum <= 10`;
  const SQL_DG_STATS = `SELECT name, value FROM v$dataguard_stats WHERE name IN ('apply lag', 'transport lag') ORDER BY name`;

  let standbyRows = null;
  let dgStatsRows = null;

  try {
    const r = await queryFn(SQL_STANDBY);
    standbyRows = r.rows || [];
    queries.push({ sql: SQL_STANDBY, columns: r.columns || [], rawRows: r.rawRows || [] });
  } catch (err) {
    queries.push({ sql: SQL_STANDBY, columns: [], rawRows: [], error: err.message });
    return { id: 'dataguard_lag', name: 'Data Guard Lag', score: null, error: err.message, findings: [], status: errorStatus(err.message), queries };
  }

  try {
    const r2 = await queryFn(SQL_DG_STATS);
    dgStatsRows = r2.rows || [];
    queries.push({ sql: SQL_DG_STATS, columns: r2.columns || [], rawRows: r2.rawRows || [] });
  } catch (err) {
    queries.push({ sql: SQL_DG_STATS, columns: [], rawRows: [], error: err.message });
  }

  // If no standby destinations are found, this is advisory only
  if (!standbyRows.length) {
    return {
      id: 'dataguard_lag', name: 'Data Guard Lag',
      score: null, findings: [],
      status: 'advisory',
      optional: true,
      queries,
      detail: { configured: false },
    };
  }

  // Parse apply lag from v$dataguard_stats
  const stats = {};
  for (const row of (dgStatsRows || [])) {
    stats[(row.NAME || '').toLowerCase()] = row.VALUE;
  }

  const applyLagStr = stats['apply lag'] || null;

  // Convert "+HH:MM:SS" or "HH:MM:SS" to seconds
  function parseLagSeconds(s) {
    if (!s) return null;
    const m = String(s).match(/(\d+)\s*day[s]?\s*(\d+):(\d+):(\d+)|(\d+):(\d+):(\d+)/i);
    if (!m) return null;
    if (m[1] !== undefined) return parseInt(m[1]) * 86400 + parseInt(m[2]) * 3600 + parseInt(m[3]) * 60 + parseInt(m[4]);
    return parseInt(m[5]) * 3600 + parseInt(m[6]) * 60 + parseInt(m[7]);
  }

  const lagSeconds = parseLagSeconds(applyLagStr);

  if (lagSeconds === null) {
    // Standby exists but lag can't be read — flag as warning
    score = 60;
    findings.push({
      key: 'DG_LAG_WARN',
      check_id: 'dataguard_lag',
      severity: 'warning',
      title: 'Data Guard standby detected but apply lag could not be determined',
      metric_line: `apply lag = ${applyLagStr || 'unknown'}`,
      remediation: 'Data Guard apply lag is elevated (60-300s). Check redo transport: SELECT * FROM V$DATAGUARD_STATS;',
    });
  } else if (lagSeconds > 300) {
    score = 0;
    findings.push({
      key: 'DG_LAG_FAIL',
      check_id: 'dataguard_lag',
      severity: 'critical',
      title: `Data Guard apply lag is ${Math.round(lagSeconds / 60)}m ${lagSeconds % 60}s — exceeds 5 minutes`,
      metric_line: `apply lag = ${applyLagStr}  (${lagSeconds}s)`,
      remediation: 'Data Guard apply lag exceeds 5 minutes or transport is broken. Immediate investigation required: SELECT * FROM V$MANAGED_STANDBY; SELECT MESSAGE FROM V$DATAGUARD_STATUS ORDER BY TIMESTAMP DESC;',
    });
  } else if (lagSeconds > 60) {
    score = 70;
    findings.push({
      key: 'DG_LAG_WARN',
      check_id: 'dataguard_lag',
      severity: 'warning',
      title: `Data Guard apply lag is ${lagSeconds}s — elevated`,
      metric_line: `apply lag = ${applyLagStr}  (${lagSeconds}s)`,
      remediation: 'Data Guard apply lag is elevated (60-300s). Check redo transport: SELECT * FROM V$DATAGUARD_STATS;',
    });
  } else {
    findings.push({
      key: 'DG_LAG_OK',
      check_id: 'dataguard_lag',
      severity: 'info',
      title: `Data Guard apply lag is ${lagSeconds}s — within acceptable range`,
      metric_line: `apply lag = ${applyLagStr}  (${lagSeconds}s)`,
      remediation: '',
    });
  }

  return {
    id: 'dataguard_lag', name: 'Data Guard Lag',
    score, findings: findings.filter(f => f.severity !== 'info'),
    status: deriveStatus(score, findings.filter(f => f.severity !== 'info')),
    queries,
    detail: { configured: true, apply_lag_seconds: lagSeconds, apply_lag_str: applyLagStr },
  };
}

/**
 * fra_headroom — Fast Recovery Area usage.
 * OPTIONAL when FRA is not configured.
 * Score: 100 if < 70%; 70 if 70-85%; 0 if > 85%.
 */
async function checkFraHeadroom(queryFn) {
  const findings = [];
  const queries  = [];
  let score = 100;

  const sql = `SELECT name, space_limit, space_used, space_reclaimable, number_of_files
    FROM v$recovery_file_dest`;

  let rows;
  try {
    const r = await queryFn(sql);
    rows = r.rows || [];
    queries.push({ sql, columns: r.columns || [], rawRows: r.rawRows || [] });
  } catch (err) {
    queries.push({ sql, columns: [], rawRows: [], error: err.message });
    return { id: 'fra_headroom', name: 'FRA Headroom', score: null, error: err.message, findings: [], status: errorStatus(err.message), queries };
  }

  if (!rows.length || !rows[0].SPACE_LIMIT || rows[0].SPACE_LIMIT === 0) {
    return {
      id: 'fra_headroom', name: 'FRA Headroom',
      score: null, findings: [],
      status: 'advisory',
      optional: true,
      queries,
      detail: { configured: false },
    };
  }

  const fra         = rows[0];
  const spaceLimit  = Number(fra.SPACE_LIMIT)  || 0;
  const spaceUsed   = Number(fra.SPACE_USED)   || 0;
  const reclaimable = Number(fra.SPACE_RECLAIMABLE) || 0;
  const netUsed     = spaceUsed - reclaimable;
  const usedPct     = spaceLimit > 0 ? Math.round((netUsed / spaceLimit) * 100) : 0;

  if (usedPct > 85) {
    score = 0;
    findings.push({
      key: 'FRA_CRITICAL',
      check_id: 'fra_headroom',
      severity: 'critical',
      title: `FRA is ${usedPct}% full — backups may fail soon`,
      metric_line: `used=${usedPct}%  limit=${Math.round(spaceLimit / 1073741824)}GB`,
      remediation: 'FRA above 85% — backups may fail soon. RMAN> DELETE OBSOLETE; or increase DB_RECOVERY_FILE_DEST_SIZE.',
    });
  } else if (usedPct > 70) {
    score = 70;
    findings.push({
      key: 'FRA_WARN',
      check_id: 'fra_headroom',
      severity: 'warning',
      title: `FRA is ${usedPct}% full`,
      metric_line: `used=${usedPct}%  limit=${Math.round(spaceLimit / 1073741824)}GB`,
      remediation: 'FRA usage above 70%. Free space or increase limit: ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=<new_size>;',
    });
  } else {
    findings.push({
      key: 'FRA_OK',
      check_id: 'fra_headroom',
      severity: 'info',
      title: `FRA is ${usedPct}% full — adequate headroom`,
      metric_line: `used=${usedPct}%  limit=${Math.round(spaceLimit / 1073741824)}GB`,
      remediation: '',
    });
  }

  return {
    id: 'fra_headroom', name: 'FRA Headroom',
    score, findings: findings.filter(f => f.severity !== 'info'),
    status: deriveStatus(score, findings.filter(f => f.severity !== 'info')),
    queries,
    detail: { configured: true, used_pct: usedPct, space_limit_gb: Math.round(spaceLimit / 1073741824) },
  };
}

/**
 * redo_log_health — redo log group configuration.
 * Score: 100 if all groups have ≥ 2 members and none INVALID;
 *        deduct 20 per single-member group; 0 if any INVALID.
 */
async function checkRedoLogHealth(queryFn) {
  const findings = [];
  const queries  = [];
  let score = 100;

  const SQL_LOGS = `SELECT l.group#, l.members, l.status, l.archived
    FROM v$log l
    ORDER BY l.group#`;

  const SQL_HIST = `SELECT COUNT(*) AS cnt FROM v$log_history WHERE first_time > SYSDATE - 7`;

  let logRows = [];
  try {
    const r = await queryFn(SQL_LOGS);
    logRows = r.rows || [];
    queries.push({ sql: SQL_LOGS, columns: r.columns || [], rawRows: r.rawRows || [] });
  } catch (err) {
    queries.push({ sql: SQL_LOGS, columns: [], rawRows: [], error: err.message });
    return { id: 'redo_log_health', name: 'Redo Log Health', score: null, error: err.message, findings: [], status: errorStatus(err.message), queries };
  }

  try {
    const r2 = await queryFn(SQL_HIST);
    queries.push({ sql: SQL_HIST, columns: r2.columns || [], rawRows: r2.rawRows || [] });
  } catch (err) {
    queries.push({ sql: SQL_HIST, columns: [], rawRows: [], error: err.message });
  }

  const invalidGroups = logRows.filter(r => (r.STATUS || '').toUpperCase() === 'INVALID');
  const singleMember  = logRows.filter(r => Number(r.MEMBERS || 1) < 2);

  if (invalidGroups.length > 0) {
    score = 0;
    findings.push({
      key: 'REDO_INVALID',
      check_id: 'redo_log_health',
      severity: 'critical',
      title: `${invalidGroups.length} redo log group(s) have INVALID status`,
      metric_line: invalidGroups.map(r => `group ${r['GROUP#'] || r.GROUP_}: ${r.STATUS}`).join(', '),
      remediation: 'INVALID redo log groups indicate corruption. Run RMAN validation and reinitialize affected groups:\n  ALTER DATABASE CLEAR LOGFILE GROUP <n>;',
    });
  }

  if (singleMember.length > 0) {
    score = Math.max(0, score - Math.min(60, singleMember.length * 20));
    findings.push({
      key: 'REDO_SINGLE_MEMBER',
      check_id: 'redo_log_health',
      severity: 'warning',
      title: `${singleMember.length} redo log group(s) have only 1 member`,
      metric_line: singleMember.map(r => `group ${r['GROUP#'] || r.GROUP_} (${r.MEMBERS} member)`).join(', '),
      remediation: 'One or more redo log groups have only 1 member. Add a second member for redundancy: ALTER DATABASE ADD LOGFILE MEMBER \'<path>\' TO GROUP <n>;',
    });
  }

  if (!invalidGroups.length && !singleMember.length && logRows.length > 0) {
    findings.push({
      key: 'REDO_MIRRORED',
      check_id: 'redo_log_health',
      severity: 'info',
      title: `All ${logRows.length} redo log group(s) have ≥ 2 members`,
      metric_line: `${logRows.length} groups, all mirrored`,
      remediation: '',
    });
  }

  score = Math.max(0, score);
  return {
    id: 'redo_log_health', name: 'Redo Log Health',
    score, findings: findings.filter(f => f.severity !== 'info'),
    status: deriveStatus(score, findings.filter(f => f.severity !== 'info')),
    queries,
    detail: { groups: logRows.length, invalid: invalidGroups.length, single_member: singleMember.length },
  };
}

// ─── Scoring ──────────────────────────────────────────────────────────────────

function computeOverallScore(groups) {
  let total      = 0;
  let weightUsed = 0;
  let errorCount = 0;

  for (const g of groups) {
    // A group is optional if it declared optional:true (dynamic) OR is in OPTIONAL_GROUPS and not configured
    const isOptional = g.optional === true || (OPTIONAL_GROUPS.has(g.id) && !g.detail?.configured);
    if (isOptional) continue;
    if (g.score === null) {
      errorCount++;
      continue;
    }
    const w = WEIGHTS[g.id] || 0;
    total      += g.score * w;
    weightUsed += w;
  }

  const score = weightUsed ? Math.round(total / weightUsed) : null;
  return { score, incomplete: errorCount > 0, errorCount };
}

// ─── POST /api/resilience/:connectionId/run ───────────────────────────────────

router.post('/:connectionId/run', requireAuth, async (req, res) => {
  const connId = parseInt(req.params.connectionId, 10);
  if (!connId) return res.status(400).json({ error: 'Invalid connection ID' });

  const conn = await getConnectionForScan(connId, req.user.id);
  if (!conn) return res.status(404).json({ error: 'Connection not found' });

  let queryFn;

  if (conn.connection_type === 'proxy') {
    let targetConnId   = conn.id;
    let sqlUsername    = conn.username     || '';
    let sqlPassword    = conn.encrypted_password ? decrypt(conn.encrypted_password) : '';
    let sqlServiceName = conn.service_name || '';
    let sqlHost        = conn.host         || 'localhost';
    let sqlPort        = conn.port         || 1521;

    if (conn.server_type === 'apps') {
      const paired = await findPairedDbConnWithCreds(conn.ebs_instance_name, req.user.id).catch(() => null);
      if (!paired) {
        return res.status(503).json({
          error: conn.ebs_instance_name
            ? `No paired DB agent found for EBS instance "${conn.ebs_instance_name}". Add a DB-type connection with the same EBS Instance Name and install its agent.`
            : 'This connection has no EBS Instance Name set. Edit the connection and set an EBS Instance Name that matches a DB-type connection.',
        });
      }
      targetConnId   = paired.id;
      sqlUsername    = paired.username;
      sqlPassword    = paired.password;
      sqlServiceName = paired.serviceName;
      sqlHost        = paired.host;
      sqlPort        = paired.port;
    }

    console.log('[resilience/run] connId=%d targetConnId=%d serverType=%s', connId, targetConnId, conn.server_type);

    let agentOnline = false;
    try {
      agentOnline = await Promise.race([
        channel.isAgentConnected(targetConnId),
        new Promise(resolve => setTimeout(() => resolve(false), 5000)),
      ]);
    } catch (err) {
      console.error('[resilience/run] isAgentConnected error:', err.message);
      return res.status(500).json({ error: 'Agent check failed: ' + err.message });
    }

    if (!agentOnline) {
      const hint = conn.server_type === 'apps'
        ? 'The paired DB agent is not connected. Start the DB server agent and retry.'
        : 'Agent is not connected. Wait for the agent to check in, then retry.';
      return res.status(503).json({ error: hint });
    }

    queryFn = makeProxyQueryFn(targetConnId, {
      service_name: sqlServiceName,
      username:     sqlUsername,
      password:     sqlPassword,
      host:         sqlHost,
      port:         sqlPort,
    });

  } else {
    let oracledb;
    try { oracledb = require('oracledb'); } catch (_) {
      return res.status(503).json({ error: 'Oracle client not available on this server' });
    }
    queryFn = makeDirectQueryFn(conn, decrypt(conn.encrypted_password), oracledb);
  }

  const startedAt = Date.now();

  const groups = [];
  for (const fn of [
    () => checkBackupCurrency(queryFn),
    () => checkArchivelogMode(queryFn),
    () => checkDataguardLag(queryFn),
    () => checkFraHeadroom(queryFn),
    () => checkRedoLogHealth(queryFn),
  ]) {
    groups.push(await fn());
  }

  const { score, incomplete, errorCount } = computeOverallScore(groups);
  const allFindings = groups.flatMap(g => g.findings || []);

  const result = {
    connection_id:   connId,
    connection_name: conn.name || `${conn.host}:${conn.port}/${conn.service_name}`,
    score,
    incomplete: incomplete || false,
    incomplete_message: incomplete ? `Incomplete — ${errorCount} check group(s) could not run` : null,
    scanned_at: new Date().toISOString(),
    duration_ms: Date.now() - startedAt,
    groups,
    finding_count: allFindings.length,
  };

  try {
    const scanId = await saveScan(req.user.id, req.user.company_domain, req.user.email, connId, result);
    result.scan_id = scanId;
    if (allFindings.length > 0) await upsertFindings(connId, allFindings);
    await resolveStaleFindings(connId, allFindings.map(f => f.key));
  } catch (err) {
    console.error('[resilience] persist error:', err);
    result.persist_error = err.message;
  }

  res.json({ success: true, result });
});

// ─── GET /api/resilience/history/:scanId ─────────────────────────────────────

router.get('/history/:scanId', requireAuth, async (req, res) => {
  const scanId = parseInt(req.params.scanId, 10);
  if (!scanId) return res.status(400).json({ error: 'Invalid scan ID' });

  try {
    const { rows } = await pool.query(
      `SELECT id, generated_data
       FROM compliance_reports
       WHERE id = $1
         AND report_type = 'resilience'
         AND user_id = $2`,
      [scanId, req.user.id]
    );
    if (!rows.length) return res.status(404).json({ error: 'Scan not found' });

    const result = { ...rows[0].generated_data, scan_id: rows[0].id };
    res.json({ result });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// ─── GET /api/resilience/:connectionId/history ────────────────────────────────

router.get('/:connectionId/history', requireAuth, async (req, res) => {
  const connId = parseInt(req.params.connectionId, 10);
  if (!connId) return res.status(400).json({ error: 'Invalid connection ID' });

  const conn = await getConnectionForScan(connId, req.user.id);
  if (!conn) return res.status(404).json({ error: 'Connection not found' });

  try {
    const rows = await getHistory(connId, req.user.id);
    res.json({ scans: rows.map(r => ({ id: r.id, created_at: r.created_at, score: r.row_counts?.score, title: r.title, result: r.generated_data })) });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

module.exports = router;
