'use strict';

/**
 * routes/vulnerability.js — Vulnerability Exposure scanner.
 *
 * Routes:
 *   GET /api/vulnerability/:connectionId/scan
 *     Queries the live database for Oracle RDBMS version + applied SQL patches,
 *     cross-references against oracle-patches.json CVE index, and returns unfixed
 *     CVEs sorted by severity. Works for both proxy (agent-channel) and direct TCP.
 *
 *   GET /api/vulnerability/:connectionId/history
 *     Last 10 persisted vulnerability scans for this connection.
 */

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

const pool            = require('../db/index');
const { requireAuth } = require('../middleware/auth');
const { decrypt }     = require('../crypto-utils');
const channel         = require('../services/agent-channel');

// ── Patch index ───────────────────────────────────────────────────────────────

const PATCH_INDEX = JSON.parse(
  fs.readFileSync(path.join(__dirname, '..', 'data', 'oracle-patches.json'), 'utf8')
);

// ── Oracle queries ────────────────────────────────────────────────────────────

const VERSION_QUERY = `SELECT version, version_full FROM v$instance`;

const SQL_PATCH_QUERY = `
SELECT patch_id, version, action, status,
       TO_CHAR(action_time, 'YYYY-MM-DD HH24:MI:SS') AS action_time,
       description, bundle_series
FROM sys.dba_registry_sqlpatch
ORDER BY action_time DESC
FETCH FIRST 30 ROWS ONLY`;

const REGISTRY_HISTORY_QUERY = `
SELECT action_time, action, id AS patch_id, version, comments AS description
FROM sys.dba_registry_history
ORDER BY action_time DESC
FETCH FIRST 10 ROWS ONLY`;

// ── Connection loader ─────────────────────────────────────────────────────────

async function getConn(connId, userId) {
  const { rows } = await pool.query(
    `SELECT id, host, port, service_name, username, encrypted_password, connection_type, server_type
     FROM oracle_connections
     WHERE id = :p1 AND user_id = :p2`,
    { p1: connId, p2: userId }
  );
  return rows[0] || null;
}

// ── Query function factory ────────────────────────────────────────────────────

function makeProxyQueryFn(connId, conn) {
  const sqlCreds = {
    service_name: conn.service_name,
    username:     conn.username,
    password:     conn.encrypted_password ? decrypt(conn.encrypted_password) : '',
    host:         conn.host,
    port:         conn.port || 1521,
    os_auth:      !conn.username,
  };
  return async function(sql) {
    const agentOnline = await Promise.race([
      channel.isAgentConnected(connId),
      new Promise(r => setTimeout(() => r(false), 5000)),
    ]);
    if (!agentOnline) throw new Error('Agent is not connected. Wait for agent check-in and retry.');

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

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

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

function makeDirectQueryFn(conn) {
  const connectString = `${conn.host}:${conn.port || 1521}/${conn.service_name}`;
  const creds = { username: conn.username, password: conn.encrypted_password ? decrypt(conn.encrypted_password) : '' };
  return async function(sql) {
    const oracledb = require('oracledb');
    let dbConn;
    try {
      dbConn = await oracledb.getConnection({
        user: creds.username,
        password: creds.password,
        connectString,
        connectTimeout: 30,
      });
      const result = await dbConn.execute(sql, [], { outFormat: oracledb.OUT_FORMAT_OBJECT });
      return result.rows || [];
    } finally {
      if (dbConn) { try { await dbConn.close(); } catch (_) {} }
    }
  };
}

// ── Version normalisation ─────────────────────────────────────────────────────

function normaliseVersion(rawVersion) {
  if (!rawVersion) return null;
  if (/^19\./.test(rawVersion)) return '19c';
  if (/^21\./.test(rawVersion)) return '21c';
  if (/^23\./.test(rawVersion) || /^26\./.test(rawVersion)) return '26ai';
  if (/^12\.2/.test(rawVersion)) return '12.2';
  if (/^18\./.test(rawVersion)) return '18c';
  return null;
}

const EBS_RUP_SQL = `SELECT TO_CHAR(patch_number) AS patch_number FROM ad_applied_patches WHERE TO_CHAR(patch_number) IN ('34174657','33267777') ORDER BY creation_date DESC FETCH FIRST 1 ROW ONLY`;

// ── CVE analysis ──────────────────────────────────────────────────────────────

function analyzeCves(versionKey, appliedPatchIds, appliedEbsPatchIds = null) {
  const unfixed = [];

  // Oracle RDBMS CVEs
  if (versionKey && PATCH_INDEX.rdbms && PATCH_INDEX.rdbms[versionKey]) {
    const patches = PATCH_INDEX.rdbms[versionKey].release_update_patches || [];
    const appliedSet = new Set(appliedPatchIds.map(String));
    let highestAppliedIdx = patches.length;
    for (let i = 0; i < patches.length; i++) {
      if (appliedSet.has(String(patches[i].patch_number))) {
        highestAppliedIdx = i;
        break;
      }
    }
    for (let i = 0; i < highestAppliedIdx; i++) {
      const p = patches[i];
      for (const cve of (p.cves || [])) {
        unfixed.push({
          cve_id:      cve,
          severity:    p.severity || 'HIGH',
          cvss_max:    p.cvss_max || null,
          component:   'Oracle Database ' + (PATCH_INDEX.rdbms[versionKey].version_label || versionKey),
          fix_patch:   p.patch_number,
          fix_label:   p.label,
          fix_date:    p.release_date,
          cpu_quarter: p.cpu_quarter,
          highlights:  p.highlights || null,
          patch_type:  'opatch',
          source:      'rdbms',
        });
      }
    }
  }

  // EBS 12.2 RUP CVEs (only when EBS detected via AD_APPLIED_PATCHES)
  if (appliedEbsPatchIds !== null) {
    const ebsPatches  = PATCH_INDEX.ebs?.['12.2']?.adop_patches || [];
    const appliedEbsSet = new Set(appliedEbsPatchIds.map(String));
    let highestEbsIdx = ebsPatches.length;
    for (let i = 0; i < ebsPatches.length; i++) {
      if (appliedEbsSet.has(String(ebsPatches[i].patch_number))) { highestEbsIdx = i; break; }
    }
    for (let i = 0; i < highestEbsIdx; i++) {
      const p = ebsPatches[i];
      for (const cve of (p.cves || [])) {
        unfixed.push({
          cve_id:      cve,
          severity:    p.severity || 'HIGH',
          cvss_max:    p.cvss_max || null,
          component:   `EBS 12.2 — ${p.label}`,
          fix_patch:   p.patch_number,
          fix_label:   p.label,
          fix_date:    p.release_date,
          cpu_quarter: p.cpu_quarter,
          highlights:  p.highlights || null,
          patch_type:  'adop',
          source:      'ebs',
        });
      }
    }
  }

  return unfixed;
}

function severityOrder(sev) {
  const s = (sev || '').toUpperCase();
  if (s === 'CRITICAL') return 0;
  if (s === 'HIGH')     return 1;
  if (s === 'MEDIUM')   return 2;
  return 3;
}

// ── Route: GET /:connectionId/scan ────────────────────────────────────────────

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

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

  const queryFn = conn.connection_type === 'proxy'
    ? makeProxyQueryFn(connId, conn)
    : makeDirectQueryFn(conn);

  let rawVersion  = null;
  let sqlPatches  = [];
  let patchSource = 'dba_registry_sqlpatch';

  // 1. Version
  try {
    const rows = await queryFn(VERSION_QUERY);
    const row  = rows[0] || {};
    rawVersion = row.VERSION_FULL || row.version_full || row.VERSION || row.version || null;
  } catch (e) {
    console.error('[vuln] version query failed:', e.message);
    return res.status(503).json({ error: 'Failed to connect to database: ' + e.message });
  }

  const versionKey = normaliseVersion(rawVersion);

  // 2. Applied patches
  try {
    const rows = await queryFn(SQL_PATCH_QUERY);
    sqlPatches = rows.map(r => ({
      patch_id:    r.PATCH_ID    || r.patch_id,
      version:     r.VERSION     || r.version,
      action:      r.ACTION      || r.action,
      status:      r.STATUS      || r.status,
      action_time: r.ACTION_TIME || r.action_time,
      description: r.DESCRIPTION || r.description,
      bundle_series: r.BUNDLE_SERIES || r.bundle_series,
    }));
  } catch (_) {
    // Fallback to legacy table
    try {
      const rows = await queryFn(REGISTRY_HISTORY_QUERY);
      sqlPatches = rows.map(r => ({
        patch_id:    r.PATCH_ID    || r.patch_id,
        action:      r.ACTION      || r.action,
        action_time: r.ACTION_TIME || r.action_time,
        description: r.DESCRIPTION || r.description,
      }));
      patchSource = 'dba_registry_history';
    } catch (_2) {
      patchSource = 'none';
    }
  }

  const appliedPatchIds = sqlPatches
    .filter(p => (p.action || '').toUpperCase() === 'APPLY')
    .map(p => p.patch_id)
    .filter(Boolean);

  // 3. EBS RUP patch level (null = non-EBS DB; [] = EBS but no known RUP applied)
  let appliedEbsPatchIds = null;
  let ebsCurrentLabel    = null;
  try {
    const ebsRows      = await queryFn(EBS_RUP_SQL);
    appliedEbsPatchIds = ebsRows.map(r => String(r.PATCH_NUMBER || r.patch_number));
    const ebsPatches   = PATCH_INDEX.ebs?.['12.2']?.adop_patches || [];
    const appliedEbsSet = new Set(appliedEbsPatchIds);
    for (const p of ebsPatches) {
      if (appliedEbsSet.has(String(p.patch_number))) { ebsCurrentLabel = p.label; break; }
    }
  } catch (_) { /* non-EBS DB — AD_APPLIED_PATCHES not found */ }

  // 4. CVE analysis (RDBMS + EBS)
  const unfixedCves = analyzeCves(versionKey, appliedPatchIds, appliedEbsPatchIds);

  // 4b. WebLogic / FMW tech stack note for EBS 12.2
  // EBS 12.2.x ships with WebLogic 10.3.6 + FMW 11.1.1.7/11.1.1.9 — both reached
  // End of Extended Support Dec 2022. Oracle no longer issues CPU patches for them.
  // Remediation = upgrade via Oracle EBS technology stack modernization (MOS 2834240.1).
  // WLS 12.2.1.4 / 14.1.1.0 entries in the patch index are for standalone WLS, not EBS.
  const wlsUnverified = [];
  const wlsEosNote = appliedEbsPatchIds !== null && conn.server_type === 'apps'
    ? 'WebLogic 10.3.6 / FMW 11.1.1.x (EBS 12.2 standard stack) reached End of Extended Support Dec 2022 — no CPU patches available. Apply Oracle EBS technology stack upgrade (MOS Note 2834240.1).'
    : null;

  // Sort: critical → high → medium; within same severity by CVSS desc
  unfixedCves.sort((a, b) => {
    const sevDiff = severityOrder(a.severity) - severityOrder(b.severity);
    if (sevDiff !== 0) return sevDiff;
    return (b.cvss_max || 0) - (a.cvss_max || 0);
  });

  // Dedup CVE IDs (same CVE may appear in multiple patches)
  const seen = new Set();
  const dedupedCves = unfixedCves.filter(c => {
    if (seen.has(c.cve_id)) return false;
    seen.add(c.cve_id);
    return true;
  });

  // 4. Determine current patch level
  let currentPatchLabel = null;
  let currentPatchDate  = null;
  if (versionKey && PATCH_INDEX.rdbms && PATCH_INDEX.rdbms[versionKey]) {
    const patches   = PATCH_INDEX.rdbms[versionKey].release_update_patches || [];
    const appliedSet = new Set(appliedPatchIds.map(String));
    for (const p of patches) {
      if (appliedSet.has(String(p.patch_number))) {
        currentPatchLabel = p.label;
        currentPatchDate  = p.release_date;
        break;
      }
    }
  }

  // 5. Build OPatch runbook for most critical unfixed patch
  const uniqueFixPatches = [...new Set(dedupedCves.map(c => c.fix_patch))];
  const runbookSteps = uniqueFixPatches.slice(0, 5).map(patchNum => {
    const cve = dedupedCves.find(c => c.fix_patch === patchNum);
    return {
      patch_number: patchNum,
      label:        cve?.fix_label || patchNum,
      severity:     cve?.severity || 'HIGH',
      commands: [
        `# Download patch ${patchNum} from My Oracle Support`,
        `unzip p${patchNum}_${rawVersion ? rawVersion.replace(/\./g, '') : 'XXX'}_Linux-x86-64.zip`,
        `cd ${patchNum}`,
        `$ORACLE_HOME/OPatch/opatch apply`,
        `$ORACLE_HOME/OPatch/opatch lspatches | grep ${patchNum}`,
      ],
    };
  });

  const meta = PATCH_INDEX._meta || {};
  res.json({
    success:            true,
    connection_id:      connId,
    scanned_at:         new Date().toISOString(),
    db_version:         rawVersion,
    version_key:        versionKey,
    current_patch:      currentPatchLabel,
    current_patch_date: currentPatchDate,
    patch_source:       patchSource,
    applied_patch_count: appliedPatchIds.length,
    ebs_detected:       appliedEbsPatchIds !== null,
    ebs_current_rup:    ebsCurrentLabel,
    unfixed_cves:       dedupedCves,
    cve_count:          dedupedCves.length,
    critical_count:     dedupedCves.filter(c => (c.severity || '').toUpperCase() === 'CRITICAL').length,
    high_count:         dedupedCves.filter(c => (c.severity || '').toUpperCase() === 'HIGH').length,
    medium_count:       dedupedCves.filter(c => (c.severity || '').toUpperCase() === 'MEDIUM').length,
    wls_cves_unverified: wlsUnverified,
    wls_eos_note:        wlsEosNote,
    opatch_runbook:     runbookSteps,
    agent_channel:      conn.connection_type === 'proxy',
    patch_index: {
      last_refreshed:   meta.LAST_REFRESHED   || null,
      next_refresh_due: meta.next_refresh_due  || null,
      refresh_cadence:  meta.refresh_cadence   || 'quarterly',
    },
  });
});

module.exports = router;
