'use strict';

/**
 * routes/security-posture.js — Read-only Oracle DB security scanner.
 *
 * Routes:
 *   POST /api/security-posture/:connectionId/run     — run all 10 check groups, score 0-100, persist
 *   GET  /api/security-posture/history/:scanId       — fetch single saved scan by ID (auth + ownership)
 *   GET  /api/security-posture/:connectionId/history — last 10 scans for a connection
 *
 * HARD CONSTRAINTS:
 *   - All Oracle queries are read-only SELECTs; no ALTER, no REVOKE, no DDL.
 *   - No new proxy ops; proxy connections use the existing /api/run_sql agent-channel path.
 *   - Oracle-maintained accounts excluded via DBA_USERS.ORACLE_MAINTAINED='Y' (12c+).
 *   - Required Oracle grants documented in db/setup/tunevault_reader.sql.
 *
 * Check groups (weighted average → score 0-100):
 *   sp_default_passwords  15%  DBA_USERS_WITH_DEFPWD
 *   sp_priv_escalation    15%  ANY privs / ADMIN OPTION / PUBLIC roles / SYSDBA / powerful pkgs
 *                              (EBS-aware: DBMS_SQL + EBS standard pkg grants collapsed to info)
 *   sp_public_grants      12%  DBA_SYS_PRIVS WHERE GRANTEE='PUBLIC'
 *   sp_init_params        12%  V$PARAMETER security-critical init params
 *   sp_password_policy    10%  DBA_PROFILES DEFAULT profile resource limits
 *   sp_audit_trail        10%  V$PARAMETER audit_trail + AUDIT_UNIFIED_POLICIES count
 *   sp_encryption        ADVISORY (optional, excluded from score)
 *                              V$ENCRYPTION_WALLET + DBA_TABLESPACES encrypted flag
 *   sp_account_hygiene     8%  DBA_USERS open / schema-only / DBA-privileged
 *   sp_db_links            4%  DBA_DB_LINKS — PUBLIC and fixed-user links
 *   sp_patch_currency      4%  V$INSTANCE version_full + DBA_REGISTRY_SQLPATCH last patch
 */

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/security-posture');

// ─── Group weights (must sum to 100) ─────────────────────────────────────────

const WEIGHTS = {
  sp_default_passwords : 15,
  sp_priv_escalation   : 15,
  sp_public_grants     : 12,
  sp_init_params       : 12,
  sp_password_policy   : 10,
  sp_audit_trail       : 10,
  sp_encryption        : 10,
  sp_account_hygiene   :  8,
  sp_db_links          :  4,
  sp_patch_currency    :  4,
};

// Optional groups: policy/licensing-dependent checks excluded from headline score.
// Findings capped at 'info'; badge shows ADVISORY instead of WARN/FAIL.
// computeOverallScore skips these entirely so non-optional weights auto-normalize.
const OPTIONAL_GROUPS = new Set(['sp_encryption']);

// ─── 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';
}

// Classify a caught error into a badge-level status.
// 'error'   → ORA-00942 / insufficient privileges → badge: NEEDS GRANT
// 'timeout' → network/channel timeout              → badge: TIMEOUT
// 'partial' → any other failure                   → badge: PARTIAL
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';
}

// ─── Dangerous privileges that must never be granted to PUBLIC ───────────────

const DANGEROUS_PUBLIC_PRIVS = new Set([
  'CREATE PROCEDURE', 'CREATE ANY PROCEDURE', 'CREATE ANY TRIGGER',
  'ALTER SYSTEM', 'ALTER DATABASE',
  'DROP ANY TABLE', 'DROP ANY PROCEDURE', 'DROP ANY TRIGGER',
  'EXECUTE ANY PROCEDURE', 'EXECUTE ANY PROGRAM',
  'CREATE LIBRARY', 'CREATE ANY LIBRARY', 'EXECUTE ANY LIBRARY',
  'GRANT ANY PRIVILEGE', 'GRANT ANY OBJECT PRIVILEGE', 'GRANT ANY ROLE',
]);

// ─── Query function factories ─────────────────────────────────────────────────
//
// makeProxyQueryFn — routes each SELECT through /api/run_sql on the target agent.
//   targetConnId : oracle_connections.id of the agent that will execute the SQL
//   sqlCreds     : { service_name, username, password, host, port } forwarded to the agent
//
// makeDirectQueryFn — opens an oracledb thin-mode connection per query (direct/TNS only).
//
// Both return async (sql) => { rows: [{COL: val, ...}] } so check functions are path-agnostic.

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 (_) {} }
    }
  };
}

// ─── Helper: find paired DB proxy connection with credentials ─────────────────
// Used when an apps-tier proxy connection is scanned: security checks are
// DB-level (DBA_USERS, DBA_SYS_PRIVS, etc.) so they must run on the DB agent.

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 ─────────────────────────────────────────────────────────────

/**
 * sp_default_passwords — accounts with Oracle-shipped default passwords still set.
 * Score: 100 if count==0; deduct 25 per open default-password account (min 0).
 */
async function checkDefaultPasswords(queryFn, isEbs) {
  const findings = [];
  const queries = [];
  let score = 100;
  let rows;

  const sql = `SELECT u.USERNAME, u.ACCOUNT_STATUS
       FROM DBA_USERS_WITH_DEFPWD d
       JOIN DBA_USERS u ON u.USERNAME = d.USERNAME
       WHERE u.ACCOUNT_STATUS = 'OPEN'
       ORDER BY u.USERNAME`;

  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: 'sp_default_passwords', name: 'Default Passwords', score: null, error: err.message, findings: [], status: errorStatus(err.message), queries };
  }

  if (rows.length > 0) {
    score = Math.max(0, 100 - rows.length * 25);
    const names = rows.map(r => r.USERNAME).join(', ');

    // EBS-managed application accounts keep their passwords in sync with FND_USER and
    // internal EBS tables. A raw ALTER USER desyncs them and breaks application logins
    // even after the DB password changes — they must be changed via FNDCPASS / AFPASSWD.
    const EBS_FOUNDATION = new Set(['APPLSYS', 'APPS', 'APPS_NE']); // APPLSYS+APPS change as a pair
    const EBS_GATEWAY    = new Set(['APPLSYSPUB']);                 // public/gateway (GWYUID) account
    const flagged        = rows.map(r => r.USERNAME);
    const ebsFoundation  = flagged.filter(n => EBS_FOUNDATION.has(n));
    const ebsGateway     = flagged.filter(n => EBS_GATEWAY.has(n));
    const nonEbs         = flagged.filter(n => !EBS_FOUNDATION.has(n) && !EBS_GATEWAY.has(n));

    let remediation;
    if (ebsFoundation.length || ebsGateway.length) {
      const parts = ['Change these default passwords. EBS-managed accounts MUST be changed with FNDCPASS / AFPASSWD — never ALTER USER (a raw ALTER USER desyncs FND_USER + internal EBS tables and breaks application logins even after the DB password is technically changed).'];
      if (ebsFoundation.length) {
        parts.push(`\nEBS foundation accounts (${ebsFoundation.join(', ')}) — APPLSYS and APPS are changed together as a pair by one command:\n  FNDCPASS apps/<apps_pwd> 0 Y system/<system_pwd> SYSTEM APPLSYS <new_password>\n  -- 12.2+ wrapper (prompts for the passwords interactively):\n  AFPASSWD -c apps@<TWO_TASK> -s APPLSYS`);
      }
      if (ebsGateway.length) {
        parts.push(`\nAPPLSYSPUB is the EBS public/gateway (GWYUID) account — changing it also requires updating GWYUID via AutoConfig and the DBC file, so follow the documented procedure (MOS Note 1674063.1, "Changing Applications Schema Passwords using FNDCPASS / AFPASSWD"). Do NOT use ALTER USER.`);
      }
      if (nonEbs.length) {
        parts.push(`\nNon-EBS Oracle accounts (${nonEbs.join(', ')}) — ALTER USER is correct here:\n${nonEbs.map(n => `  ALTER USER ${n} IDENTIFIED BY "<strong-password>";`).join('\n')}`);
      }
      if (isEbs) {
        parts.push(`\nEBS application product schemas (GL, AR, AP, INV, …) are also FND-managed — change those with FNDCPASS ORACLE <SCHEMA> <pwd> (one schema) or FNDCPASS ALLORACLE <pwd> (all), not ALTER USER.`);
      }
      parts.push(`\nLocking an unused account is safe via ALTER USER (it does not change the password):\n  ALTER USER <name> ACCOUNT LOCK;`);
      remediation = parts.join('\n');
    } else {
      // No EBS-managed accounts among the flagged set — ALTER USER is the correct fix.
      remediation = `Change default passwords immediately:\n${rows.map(r => `  ALTER USER ${r.USERNAME} IDENTIFIED BY "<strong-password>";`).join('\n')}\nThen LOCK accounts not needed:\n  ALTER USER <name> ACCOUNT LOCK;`;
    }

    findings.push({
      key: 'sp_default_passwords_open',
      check_id: 'sp_default_passwords',
      severity: rows.length >= 3 ? 'critical' : 'warning',
      title: `${rows.length} account(s) using default Oracle passwords`,
      metric_line: names,
      remediation,
    });
  }

  return { id: 'sp_default_passwords', name: 'Default Passwords', score, findings, status: deriveStatus(score, findings), queries };
}

/**
 * sp_public_grants — dangerous system privileges granted to PUBLIC.
 * Score: 100 if none; deduct 20 per dangerous grant (min 0).
 */
async function checkPublicGrants(queryFn) {
  const findings = [];
  const queries = [];
  let score = 100;
  let rows;

  const sql = `SELECT PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE = 'PUBLIC' ORDER BY PRIVILEGE`;

  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: 'sp_public_grants', name: 'Dangerous PUBLIC Grants', score: null, error: err.message, findings: [], status: errorStatus(err.message), queries };
  }

  const allPublic = rows.map(r => r.PRIVILEGE);
  const dangerous = allPublic.filter(p => DANGEROUS_PUBLIC_PRIVS.has(p));

  if (dangerous.length > 0) {
    score = Math.max(0, 100 - dangerous.length * 20);
    findings.push({
      key: 'sp_public_grants_dangerous',
      check_id: 'sp_public_grants',
      severity: dangerous.some(p => ['ALTER SYSTEM', 'ALTER DATABASE', 'GRANT ANY PRIVILEGE'].includes(p)) ? 'critical' : 'warning',
      title: `${dangerous.length} dangerous privilege(s) granted to PUBLIC`,
      metric_line: dangerous.join(', '),
      remediation: `Remove dangerous grants from PUBLIC:\n${dangerous.map(p => `  REVOKE ${p} FROM PUBLIC;`).join('\n')}\nPublic grants are inherited by ALL users — this significantly widens the attack surface.`,
    });
  }

  return { id: 'sp_public_grants', name: 'Dangerous PUBLIC Grants', score, findings, status: deriveStatus(score, findings), queries, detail: { all_public_grants: allPublic.length, dangerous_count: dangerous.length } };
}

/**
 * sp_account_hygiene — open user accounts, schema-only (no auth), DBA count.
 */
async function checkAccountHygiene(queryFn) {
  const findings = [];
  const queries = [];
  let score = 100;
  let usersRows, dbaRows;

  const SCHEMA_ACCOUNTS = new Set([
    'SYS', 'SYSTEM', 'DBSNMP', 'SYSMAN', 'OUTLN', 'MDSYS', 'ORDSYS', 'XDB',
    'CTXSYS', 'WMSYS', 'EXFSYS', 'APPQOSSYS', 'DBSFWUSER', 'OJVMSYS',
    'DVSYS', 'LBACSYS', 'GSMADMIN_INTERNAL', 'GGSYS', 'ANONYMOUS', 'FLOWS_FILES',
    'APEX_040000', 'APEX_040200', 'APEX_050000', 'APEX_180100', 'APEX_190100',
    'APEX_200100', 'APEX_210100', 'APEX_220100', 'HR', 'OE', 'PM', 'SH', 'SPATIAL_WFS_ADMIN_USR',
  ]);

  const sql1 = `SELECT USERNAME, ACCOUNT_STATUS, PROFILE, AUTHENTICATION_TYPE FROM DBA_USERS ORDER BY USERNAME`;
  const sql2 = `SELECT GRANTEE FROM DBA_SYS_PRIVS
       WHERE PRIVILEGE = 'DBA'
         AND GRANTEE NOT IN ('SYS','SYSTEM','DBA','SYSMAN','DBSNMP','APPQOSSYS')
         AND GRANTEE NOT LIKE 'APEX_%'
       ORDER BY GRANTEE`;

  try {
    const r1 = await queryFn(sql1);
    usersRows = r1.rows || [];
    queries.push({ sql: sql1, columns: r1.columns || [], rawRows: r1.rawRows || [] });
  } catch (err) {
    queries.push({ sql: sql1, columns: [], rawRows: [], error: err.message });
    return { id: 'sp_account_hygiene', name: 'Account Hygiene', score: null, error: err.message, findings: [], status: errorStatus(err.message), queries };
  }

  let dbaQueryError = null;
  try {
    const r2 = await queryFn(sql2);
    dbaRows = r2.rows || [];
    queries.push({ sql: sql2, columns: r2.columns || [], rawRows: r2.rawRows || [] });
  } catch (err) {
    queries.push({ sql: sql2, columns: [], rawRows: [], error: err.message });
    dbaQueryError = err.message;
  }

  const openUsers    = usersRows.filter(u => u.ACCOUNT_STATUS === 'OPEN');
  const schemaNoAuth = openUsers.filter(u => !SCHEMA_ACCOUNTS.has(u.USERNAME) && u.AUTHENTICATION_TYPE === 'NONE');
  const dbaUsers     = dbaRows.map(r => r.GRANTEE);

  if (schemaNoAuth.length > 0) {
    score -= Math.min(40, schemaNoAuth.length * 10);
    findings.push({
      key: 'sp_account_hygiene_noauth',
      check_id: 'sp_account_hygiene',
      severity: 'critical',
      title: `${schemaNoAuth.length} account(s) open with no authentication`,
      metric_line: schemaNoAuth.map(u => u.USERNAME).join(', '),
      remediation: `Schema-only accounts with no authentication can be connected without a password. Lock or set password:\n${schemaNoAuth.map(u => `  ALTER USER ${u.USERNAME} ACCOUNT LOCK;`).join('\n')}`,
    });
  }

  if (dbaUsers.length > 5) {
    score -= Math.min(30, (dbaUsers.length - 5) * 5);
    findings.push({
      key: 'sp_account_hygiene_dba_count',
      check_id: 'sp_account_hygiene',
      severity: dbaUsers.length > 10 ? 'critical' : 'warning',
      title: `${dbaUsers.length} non-standard accounts have DBA privilege`,
      metric_line: dbaUsers.slice(0, 10).join(', ') + (dbaUsers.length > 10 ? ` (+${dbaUsers.length - 10} more)` : ''),
      remediation: `DBA is the most powerful Oracle privilege. Review each account and revoke if not needed:\n  REVOKE DBA FROM <username>;\nReplace broad DBA with targeted roles (RESOURCE, CONNECT, specific object grants).`,
    });
  }

  score = Math.max(0, score);
  const hygieneStatus = dbaQueryError
    ? (findings.length > 0 ? 'partial' : 'partial')
    : deriveStatus(score, findings);
  return { id: 'sp_account_hygiene', name: 'Account Hygiene', score, findings, status: hygieneStatus, queries, detail: { open_users: openUsers.length, total_users: usersRows.length, dba_count: dbaUsers.length }, ...(dbaQueryError ? { partial_error: dbaQueryError } : {}) };
}

/**
 * sp_password_policy — DEFAULT profile settings for password complexity/expiry.
 */
async function checkPasswordPolicy(queryFn) {
  const findings = [];
  const queries = [];
  let score = 100;
  let rows;

  const sql = `SELECT RESOURCE_NAME, LIMIT
       FROM DBA_PROFILES
       WHERE PROFILE = 'DEFAULT'
         AND RESOURCE_NAME IN (
           'PASSWORD_VERIFY_FUNCTION',
           'FAILED_LOGIN_ATTEMPTS',
           'PASSWORD_LOCK_TIME',
           'PASSWORD_LIFE_TIME',
           'PASSWORD_REUSE_TIME',
           'PASSWORD_REUSE_MAX',
           'PASSWORD_GRACE_TIME'
         )
       ORDER BY RESOURCE_NAME`;

  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: 'sp_password_policy', name: 'Password Policy', score: null, error: err.message, findings: [], status: errorStatus(err.message), queries };
  }

  const limits = {};
  for (const row of rows) limits[row.RESOURCE_NAME] = row.LIMIT;

  const verifyFn = limits['PASSWORD_VERIFY_FUNCTION'];
  if (!verifyFn || verifyFn === 'NULL' || verifyFn === 'DEFAULT') {
    score -= 30;
    findings.push({
      key: 'sp_password_policy_verify_fn',
      check_id: 'sp_password_policy',
      severity: 'critical',
      title: 'No password complexity function set on DEFAULT profile',
      metric_line: 'PASSWORD_VERIFY_FUNCTION = ' + (verifyFn || 'NULL'),
      remediation: `Enable Oracle's built-in complexity function:\n  ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION ORA12C_STRONG_VERIFY_FUNCTION;\nThis enforces minimum length, mixed case, special characters, and blocks usernames-as-passwords.`,
    });
  }

  const failedLogins = limits['FAILED_LOGIN_ATTEMPTS'];
  if (!failedLogins || failedLogins === 'UNLIMITED' || failedLogins === 'DEFAULT') {
    score -= 25;
    findings.push({
      key: 'sp_password_policy_lockout',
      check_id: 'sp_password_policy',
      severity: 'warning',
      title: 'No failed login lockout configured on DEFAULT profile',
      metric_line: 'FAILED_LOGIN_ATTEMPTS = ' + (failedLogins || 'UNLIMITED'),
      remediation: `Set a lockout threshold (10 is a reasonable starting point):\n  ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS 10;\n  ALTER PROFILE DEFAULT LIMIT PASSWORD_LOCK_TIME 1/24;  -- 1 hour`,
    });
  }

  const lifetime = limits['PASSWORD_LIFE_TIME'];
  if (!lifetime || lifetime === 'UNLIMITED' || lifetime === 'DEFAULT') {
    score -= 15;
    findings.push({
      key: 'sp_password_policy_lifetime',
      check_id: 'sp_password_policy',
      severity: 'info',
      title: 'Passwords do not expire on DEFAULT profile',
      metric_line: 'PASSWORD_LIFE_TIME = ' + (lifetime || 'UNLIMITED'),
      remediation: `Consider setting password expiry (180 days is a common baseline):\n  ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 180;\n  ALTER PROFILE DEFAULT LIMIT PASSWORD_GRACE_TIME 7;`,
    });
  }

  score = Math.max(0, score);
  return { id: 'sp_password_policy', name: 'Password Policy', score, findings, status: deriveStatus(score, findings), queries, detail: limits };
}

/**
 * sp_audit_trail — audit_trail parameter, unified audit, DDL logging.
 */
async function checkAuditTrail(queryFn) {
  const findings = [];
  const queries = [];
  let score = 100;
  let paramRows;

  const sql1 = `SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME IN ('audit_trail','enable_ddl_logging') ORDER BY NAME`;
  const sql2 = `SELECT COUNT(*) AS CNT FROM AUDIT_UNIFIED_POLICIES WHERE ENABLED_OPT = 'BY USER'`;

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

  const paramMap = {};
  for (const row of paramRows) paramMap[row.NAME] = (row.VALUE || '').toUpperCase();

  const auditTrail = paramMap['audit_trail'] || 'NONE';
  const ddlLogging = paramMap['enable_ddl_logging'] || 'FALSE';
  let isUnified = false;

  try {
    const r2 = await queryFn(sql2);
    if ((r2.rows[0] && (r2.rows[0].CNT || 0)) > 0) isUnified = true;
    queries.push({ sql: sql2, columns: r2.columns || [], rawRows: r2.rawRows || [] });
  } catch (e) {
    queries.push({ sql: sql2, columns: [], rawRows: [], error: e.message });
  }

  if (auditTrail === 'NONE' && !isUnified) {
    score -= 50;
    findings.push({
      key: 'sp_audit_trail_none',
      check_id: 'sp_audit_trail',
      severity: 'critical',
      title: 'Database auditing is disabled (audit_trail=NONE)',
      metric_line: 'audit_trail = NONE',
      remediation: `Enable auditing:\n  -- Recommended (12c+ Unified Audit, no reboot needed):\n  AUDIT POLICY ORA_SECURECONFIG;\n\n  -- Legacy (requires restart):\n  ALTER SYSTEM SET audit_trail=DB,EXTENDED SCOPE=SPFILE;\n  SHUTDOWN IMMEDIATE; STARTUP;`,
    });
  } else if (['OS', 'NONE'].includes(auditTrail) && !isUnified) {
    score -= 20;
    findings.push({
      key: 'sp_audit_trail_os_only',
      check_id: 'sp_audit_trail',
      severity: 'info',
      title: `Audit trail is OS-only — consider DB,EXTENDED for richer queryability`,
      metric_line: 'audit_trail = ' + auditTrail,
      remediation: `Switch to DB,EXTENDED for SQL bind variable capture:\n  ALTER SYSTEM SET audit_trail=DB,EXTENDED SCOPE=SPFILE;\nThen restart the instance.`,
    });
  }

  if (ddlLogging !== 'TRUE') {
    score -= 10;
    findings.push({
      key: 'sp_audit_trail_ddl',
      check_id: 'sp_audit_trail',
      severity: 'info',
      title: 'DDL change logging is not enabled',
      metric_line: 'enable_ddl_logging = ' + (paramMap['enable_ddl_logging'] || 'FALSE'),
      remediation: `Enable DDL logging (no restart needed):\n  ALTER SYSTEM SET enable_ddl_logging=TRUE;`,
    });
  }

  score = Math.max(0, score);
  return {
    id: 'sp_audit_trail', name: 'Audit Configuration', score, findings,
    status: deriveStatus(score, findings), queries,
    detail: { audit_trail: auditTrail, unified_audit: isUnified, ddl_logging: ddlLogging === 'TRUE' },
  };
}

// ─── 6. sp_init_params — security-critical initialization parameters ─────────
//
// One query fetches all relevant parameters. Checks:
//   o7_dictionary_accessibility, remote_os_authent, remote_os_roles, os_roles  → must be FALSE
//   remote_login_passwordfile                                                   → must be EXCLUSIVE
//   sec_max_failed_login_attempts                                               → must be 3–10
//   sec_return_server_release_banner                                            → must be FALSE
//   utl_file_dir                                                                → must be empty
//   resource_limit                                                              → must be TRUE

async function checkInitParams(queryFn) {
  const findings = [];
  const queries  = [];
  let score = 100;

  const sql = `SELECT name, value FROM v$parameter WHERE name IN (
    'o7_dictionary_accessibility','remote_os_authent','remote_os_roles','os_roles',
    'remote_login_passwordfile','sec_case_sensitive_logon',
    'sec_max_failed_login_attempts','sec_return_server_release_banner',
    'utl_file_dir','resource_limit','global_names'
  ) ORDER BY name`;

  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: 'sp_init_params', name: 'Security Init Parameters', score: null, error: err.message, findings: [], status: errorStatus(err.message), queries };
  }

  // Build lowercase param → lowercase value map
  const p = {};
  for (const row of rows) p[(row.NAME || '').toLowerCase()] = (row.VALUE || '').toLowerCase().trim();

  // Helper: flag if actual value matches the insecure value
  const flag = (param, insecure, sev, deduct, title, fix) => {
    const actual = p[param];
    if (actual === undefined) return;
    const bad = Array.isArray(insecure) ? insecure.includes(actual) : actual === insecure;
    if (bad) {
      score = Math.max(0, score - deduct);
      findings.push({ key: `sp_init_params_${param}`, check_id: 'sp_init_params', severity: sev,
        title, metric_line: `${param} = ${actual}`, remediation: fix });
    }
  };

  flag('o7_dictionary_accessibility', 'true', 'critical', 20,
    'o7_dictionary_accessibility=TRUE — non-DBA users can access SYS objects',
    'ALTER SYSTEM SET o7_dictionary_accessibility=FALSE SCOPE=SPFILE;  -- restart required');
  flag('remote_os_authent', 'true', 'critical', 20,
    'remote_os_authent=TRUE — OS-authenticated users can connect without a password',
    'ALTER SYSTEM SET remote_os_authent=FALSE SCOPE=SPFILE;  -- restart required');
  flag('remote_os_roles', 'true', 'warning', 10,
    'remote_os_roles=TRUE — the OS can assign database roles to remote users',
    'ALTER SYSTEM SET remote_os_roles=FALSE SCOPE=SPFILE;  -- restart required');
  flag('os_roles', 'true', 'warning', 10,
    'os_roles=TRUE — role management delegated to the OS, bypassing Oracle RBAC',
    'ALTER SYSTEM SET os_roles=FALSE SCOPE=SPFILE;  -- restart required');
  flag('sec_return_server_release_banner', 'true', 'info', 5,
    'sec_return_server_release_banner=TRUE — exact Oracle version advertised to clients',
    'ALTER SYSTEM SET sec_return_server_release_banner=FALSE;  -- dynamic, no restart');
  flag('resource_limit', 'false', 'info', 5,
    'resource_limit=FALSE — DBA_PROFILES CPU and session limits are not enforced',
    'ALTER SYSTEM SET resource_limit=TRUE;  -- dynamic, no restart');

  const plf = p['remote_login_passwordfile'];
  if (plf !== undefined && plf !== 'exclusive') {
    score = Math.max(0, score - 10);
    findings.push({
      key: 'sp_init_params_remote_login_passwordfile', check_id: 'sp_init_params',
      severity: plf === 'shared' ? 'critical' : 'warning',
      title: `remote_login_passwordfile=${plf} — EXCLUSIVE is recommended`,
      metric_line: `remote_login_passwordfile = ${plf}  (expected: exclusive)`,
      remediation: plf === 'shared'
        ? 'SHARED allows multiple databases to share one password file — enables privilege abuse across databases.\n  ALTER SYSTEM SET remote_login_passwordfile=EXCLUSIVE SCOPE=SPFILE;  -- restart required'
        : 'EXCLUSIVE ensures only this database controls its password file and SYSDBA/SYSOPER membership.\n  ALTER SYSTEM SET remote_login_passwordfile=EXCLUSIVE SCOPE=SPFILE;  -- restart required',
    });
  }

  const mfRaw = p['sec_max_failed_login_attempts'];
  const maxFail = parseInt(mfRaw || '0', 10);
  if (mfRaw !== undefined && (isNaN(maxFail) || maxFail === 0 || maxFail > 10)) {
    score = Math.max(0, score - 5);
    findings.push({
      key: 'sp_init_params_sec_max_failed', check_id: 'sp_init_params', severity: 'info',
      title: `sec_max_failed_login_attempts=${mfRaw} — tighten to 5–10`,
      metric_line: `sec_max_failed_login_attempts = ${mfRaw}`,
      remediation: 'ALTER SYSTEM SET sec_max_failed_login_attempts=10;  -- dynamic, no restart',
    });
  }

  const ufd = p['utl_file_dir'];
  if (ufd !== undefined && ufd !== '' && ufd !== 'none') {
    score = Math.max(0, score - 10);
    findings.push({
      key: 'sp_init_params_utl_file_dir', check_id: 'sp_init_params', severity: 'warning',
      title: 'utl_file_dir is set — all users get UTL_FILE access to those OS paths',
      metric_line: `utl_file_dir = ${p['utl_file_dir']}`,
      remediation: 'Migrate to DIRECTORY objects (CREATE DIRECTORY) scoped to specific schemas.\n  ALTER SYSTEM SET utl_file_dir=\'\' SCOPE=SPFILE;  -- restart required',
    });
  }

  score = Math.max(0, score);
  return { id: 'sp_init_params', name: 'Security Init Parameters', score, findings, status: deriveStatus(score, findings), queries };
}

// ─── 7. sp_priv_escalation — privilege escalation surface ────────────────────
//
// Five sequential SELECTs build a full picture of escalation paths:
//   1. ANY-privilege grants to non-maintained users
//   2. Privileges granted WITH ADMIN OPTION to non-maintained users
//   3. Roles granted to PUBLIC
//   4. SYSDBA/SYSOPER beyond SYS in the password file
//   5. EXECUTE on DBMS_JAVA/DBMS_SQL/UTL_FILE/DBMS_LOB/DBMS_SCHEDULER to non-maintained users

// isEbs: when true, collapses DBMS_SQL and EBS-standard package grants into info messages
//        so that expected EBS configuration doesn't artificially tank the score.
//        KEEPS as real findings: GRANT ANY PRIVILEGE/ROLE, PUBLIC roles, SYSDBA beyond SYS,
//        PUBLIC EXECUTE on any package, and EXECUTE grants where APPS is NOT a co-grantee.
async function checkPrivEscalation(queryFn, isEbs) {
  const findings = [];
  const queries  = [];
  let score = 100;

  // Helper: run one SQL, always push to queries (error captured, never throws)
  const run = async (sql) => {
    try {
      const r = await queryFn(sql);
      queries.push({ sql, columns: r.columns || [], rawRows: r.rawRows || [] });
      return r.rows || [];
    } catch (err) {
      queries.push({ sql, columns: [], rawRows: [], error: err.message });
      return null;
    }
  };

  const SQL_ANY = `SELECT grantee, privilege FROM dba_sys_privs
   WHERE privilege IN (
     'SELECT ANY TABLE','EXECUTE ANY PROCEDURE','CREATE ANY PROCEDURE','ALTER ANY TABLE',
     'GRANT ANY PRIVILEGE','GRANT ANY ROLE','CREATE ANY TABLE','DROP ANY TABLE'
   )
   AND grantee NOT IN (SELECT username FROM dba_users WHERE oracle_maintained='Y')
   AND grantee NOT IN (SELECT role FROM dba_roles WHERE oracle_maintained='Y')
   ORDER BY grantee, privilege`;

  const SQL_ADMIN = `SELECT grantee, privilege FROM dba_sys_privs
   WHERE admin_option='YES'
   AND grantee NOT IN (SELECT username FROM dba_users WHERE oracle_maintained='Y')
   AND grantee NOT IN (SELECT role FROM dba_roles WHERE oracle_maintained='Y')
   ORDER BY grantee, privilege`;

  const SQL_PUB_ROLES = `SELECT granted_role FROM dba_role_privs WHERE grantee='PUBLIC' ORDER BY granted_role`;

  const SQL_PWFILE = `SELECT username, sysdba, sysoper FROM v$pwfile_users WHERE username<>'SYS' ORDER BY username`;

  const SQL_EXEC_PKGS = `SELECT grantee, table_name FROM dba_tab_privs
   WHERE table_name IN ('DBMS_JAVA','DBMS_SQL','UTL_FILE','DBMS_LOB','DBMS_SCHEDULER')
   AND privilege='EXECUTE'
   AND grantee NOT IN (SELECT username FROM dba_users WHERE oracle_maintained='Y')
   AND grantee NOT IN (SELECT role FROM dba_roles WHERE oracle_maintained='Y')
   ORDER BY grantee, table_name`;

  const anyRows     = await run(SQL_ANY);
  const adminRows   = await run(SQL_ADMIN);
  const pubRoleRows = await run(SQL_PUB_ROLES);
  const pwfileRows  = await run(SQL_PWFILE);
  const execPkgRows = await run(SQL_EXEC_PKGS);

  // Track failures — find first error message for status classification
  const failedPE = queries.filter(q => q.error);
  const primaryPEErr = failedPE.length > 0 ? failedPE[0].error : null;
  const allPEFailed = anyRows === null && adminRows === null && pubRoleRows === null && pwfileRows === null && execPkgRows === null;

  const CRITICAL_ANY = new Set(['GRANT ANY PRIVILEGE','GRANT ANY ROLE','CREATE ANY PROCEDURE','EXECUTE ANY PROCEDURE']);

  if (anyRows !== null && anyRows.length > 0) {
    score = Math.max(0, score - Math.min(40, anyRows.length * 8));
    findings.push({
      key: 'sp_priv_escalation_any_privs', check_id: 'sp_priv_escalation',
      severity: anyRows.some(r => CRITICAL_ANY.has(r.PRIVILEGE)) ? 'critical' : 'warning',
      title: `${anyRows.length} dangerous ANY-privilege grant(s) to non-system users`,
      metric_line: [...new Set(anyRows.map(r => r.GRANTEE))].join(', '),
      remediation: `ANY privileges give access across every schema. Review and revoke:\n` +
        anyRows.slice(0, 10).map(r => `  REVOKE ${r.PRIVILEGE} FROM ${r.GRANTEE};`).join('\n') +
        (anyRows.length > 10 ? `\n  … and ${anyRows.length - 10} more` : '') +
        '\nReplace with targeted object-level grants where possible.',
    });
  }

  if (adminRows && adminRows.length > 0) {
    score = Math.max(0, score - Math.min(20, adminRows.length * 5));
    findings.push({
      key: 'sp_priv_escalation_admin_option', check_id: 'sp_priv_escalation',
      severity: 'warning',
      title: `${adminRows.length} privilege(s) granted WITH ADMIN OPTION to non-system users`,
      metric_line: adminRows.slice(0, 6).map(r => `${r.GRANTEE}: ${r.PRIVILEGE}`).join(', '),
      remediation: `WITH ADMIN OPTION lets grantees re-grant the privilege without an audit trail:\n` +
        adminRows.slice(0, 8).map(r => `  REVOKE ${r.PRIVILEGE} FROM ${r.GRANTEE};  -- re-grant without ADMIN OPTION if needed`).join('\n'),
    });
  }

  if (pubRoleRows && pubRoleRows.length > 0) {
    score = Math.max(0, score - Math.min(30, pubRoleRows.length * 15));
    findings.push({
      key: 'sp_priv_escalation_public_roles', check_id: 'sp_priv_escalation',
      severity: 'critical',
      title: `${pubRoleRows.length} role(s) granted to PUBLIC — inherited by every user`,
      metric_line: pubRoleRows.map(r => r.GRANTED_ROLE).join(', '),
      remediation: `Roles on PUBLIC are automatically inherited by ALL users including future accounts:\n` +
        pubRoleRows.map(r => `  REVOKE ${r.GRANTED_ROLE} FROM PUBLIC;`).join('\n'),
    });
  }

  if (pwfileRows && pwfileRows.length > 0) {
    score = Math.max(0, score - Math.min(30, pwfileRows.length * 15));
    findings.push({
      key: 'sp_priv_escalation_sysdba', check_id: 'sp_priv_escalation',
      severity: 'critical',
      title: `${pwfileRows.length} non-SYS account(s) with SYSDBA or SYSOPER in password file`,
      metric_line: pwfileRows.map(r => `${r.USERNAME} (sysdba:${r.SYSDBA})`).join(', '),
      remediation: `Only SYS should hold SYSDBA. Revoke from all others:\n` +
        pwfileRows.map(r => `  REVOKE SYSDBA FROM ${r.USERNAME};  -- or SYSOPER as applicable`).join('\n'),
    });
  }

  if (execPkgRows && execPkgRows.length > 0) {
    if (isEbs) {
      // DBMS_SQL: EBS grants it universally to hundreds of product schemas — collapse into info
      const dbmsSqlRows = execPkgRows.filter(r => r.TABLE_NAME === 'DBMS_SQL');
      if (dbmsSqlRows.length > 0) {
        findings.push({
          key: 'sp_priv_escalation_exec_pkgs_dbms_sql_ebs', check_id: 'sp_priv_escalation',
          severity: 'info',
          title: `${dbmsSqlRows.length} EBS schema(s) hold EXECUTE on DBMS_SQL — expected for EBS`,
          metric_line: dbmsSqlRows.slice(0, 5).map(r => r.GRANTEE).join(', ') + (dbmsSqlRows.length > 5 ? ` (+${dbmsSqlRows.length - 5} more)` : ''),
          remediation: `DBMS_SQL EXECUTE grants to EBS product schemas are standard and required by the product. No action needed.\nFull grantee list visible in Check details below.`,
        });
      }

      // For remaining packages: separate PUBLIC (always flag) from schema grants
      const otherPkgRows = execPkgRows.filter(r => r.TABLE_NAME !== 'DBMS_SQL');
      if (otherPkgRows.length > 0) {
        const publicGrants    = otherPkgRows.filter(r => r.GRANTEE === 'PUBLIC');
        const nonPublicGrants = otherPkgRows.filter(r => r.GRANTEE !== 'PUBLIC');

        // PUBLIC → always a real finding regardless of EBS
        if (publicGrants.length > 0) {
          score = Math.max(0, score - Math.min(20, publicGrants.length * 10));
          findings.push({
            key: 'sp_priv_escalation_exec_pkgs_public', check_id: 'sp_priv_escalation',
            severity: 'critical',
            title: `${publicGrants.length} high-risk package(s) granted EXECUTE to PUBLIC`,
            metric_line: publicGrants.map(r => `PUBLIC → ${r.TABLE_NAME}`).join(', '),
            remediation: `EXECUTE on high-risk packages granted to PUBLIC is accessible by every connected user:\n` +
              publicGrants.map(r => `  REVOKE EXECUTE ON ${r.TABLE_NAME} FROM PUBLIC;`).join('\n'),
          });
        }

        // Non-PUBLIC: if APPS is a co-grantee on a package → that package is EBS-standard → info
        const pkgToGrantees = {};
        for (const r of nonPublicGrants) {
          if (!pkgToGrantees[r.TABLE_NAME]) pkgToGrantees[r.TABLE_NAME] = [];
          pkgToGrantees[r.TABLE_NAME].push(r.GRANTEE);
        }

        const ebsExpectedPkgs = [];
        const nonEbsGrants    = [];

        for (const [pkg, grantees] of Object.entries(pkgToGrantees)) {
          if (grantees.includes('APPS')) {
            // APPS is a co-grantee → EBS standard configuration
            ebsExpectedPkgs.push({ pkg, count: grantees.length });
          } else {
            grantees.forEach(g => nonEbsGrants.push({ GRANTEE: g, TABLE_NAME: pkg }));
          }
        }

        if (ebsExpectedPkgs.length > 0) {
          findings.push({
            key: 'sp_priv_escalation_exec_pkgs_ebs', check_id: 'sp_priv_escalation',
            severity: 'info',
            title: `EBS product schemas hold EXECUTE on high-risk packages — expected`,
            metric_line: ebsExpectedPkgs.map(e => `${e.count} schema(s) → ${e.pkg}`).join('; '),
            remediation: `EXECUTE on ${ebsExpectedPkgs.map(e => e.pkg).join(', ')} is standard EBS configuration (APPS is among the grantees). No action required.\nFull list in Check details below.`,
          });
        }

        if (nonEbsGrants.length > 0) {
          score = Math.max(0, score - Math.min(20, nonEbsGrants.length * 5));
          findings.push({
            key: 'sp_priv_escalation_exec_pkgs_nonebs', check_id: 'sp_priv_escalation',
            severity: 'warning',
            title: `${nonEbsGrants.length} non-EBS schema(s) have EXECUTE on high-risk packages`,
            metric_line: nonEbsGrants.map(r => `${r.GRANTEE} → ${r.TABLE_NAME}`).join(', '),
            remediation: `These EXECUTE grants are not expected in a standard EBS configuration:\n` +
              nonEbsGrants.map(r => `  REVOKE EXECUTE ON ${r.TABLE_NAME} FROM ${r.GRANTEE};`).join('\n') +
              '\nGrant only to schemas that genuinely require each package.',
          });
        }
      }
    } else {
      // Non-EBS: flag everything as before
      score = Math.max(0, score - Math.min(20, execPkgRows.length * 5));
      findings.push({
        key: 'sp_priv_escalation_exec_pkgs', check_id: 'sp_priv_escalation',
        severity: 'warning',
        title: `${execPkgRows.length} non-system user(s) have EXECUTE on high-risk packages`,
        metric_line: execPkgRows.map(r => `${r.GRANTEE} → ${r.TABLE_NAME}`).join(', '),
        remediation: `These packages can read/write OS files, execute arbitrary code, or schedule jobs:\n` +
          execPkgRows.map(r => `  REVOKE EXECUTE ON ${r.TABLE_NAME} FROM ${r.GRANTEE};`).join('\n') +
          '\nGrant only to the specific schemas that genuinely require each package.',
      });
    }
  }

  score = Math.max(0, score);
  const peStatus = allPEFailed
    ? errorStatus(primaryPEErr)
    : (primaryPEErr ? 'partial' : deriveStatus(score, findings));
  return {
    id: 'sp_priv_escalation', name: 'Privilege Escalation Surface',
    score: allPEFailed ? null : score,
    findings, status: peStatus, queries,
    ...(primaryPEErr ? { error: primaryPEErr } : {}),
  };
}

// ─── 8. sp_encryption — TDE wallet status and tablespace encryption ───────────
//
// Three SELECTs: v$encryption_wallet (wallet state), dba_tablespaces (unencrypted),
// dba_encrypted_columns (column-level TDE count). Wallet query failure is graceful
// (TDE may not be licensed) — only penalizes if wallet is empty or closed.

async function checkEncryption(queryFn) {
  const findings = [];
  const queries  = [];
  let score = 100;

  const run = async (sql) => {
    try {
      const r = await queryFn(sql);
      queries.push({ sql, columns: r.columns || [], rawRows: r.rawRows || [] });
      return r.rows || [];
    } catch (err) {
      queries.push({ sql, columns: [], rawRows: [], error: err.message });
      return null;
    }
  };

  const SQL_WALLET = `SELECT wrl_type, status, wallet_type FROM v$encryption_wallet`;
  const SQL_UNENC  = `SELECT tablespace_name FROM dba_tablespaces
   WHERE encrypted='NO'
   AND tablespace_name NOT IN ('SYSTEM','SYSAUX','TEMP','UNDOTBS1')
   ORDER BY tablespace_name`;
  const SQL_COLS   = `SELECT count(*) AS enc_cols FROM dba_encrypted_columns`;

  const walletRows = await run(SQL_WALLET);
  const unencRows  = await run(SQL_UNENC);
  await run(SQL_COLS);  // captured in queries for visibility; not scored independently

  if (walletRows === null) {
    // v$encryption_wallet inaccessible — TDE not configured or not licensed; skip wallet checks
  } else if (walletRows.length === 0) {
    score = Math.max(0, score - 30);
    findings.push({
      key: 'sp_encryption_no_tde', check_id: 'sp_encryption', severity: 'warning',
      title: 'TDE keystore not configured (v$encryption_wallet is empty)',
      metric_line: 'v$encryption_wallet: 0 rows',
      remediation: 'Configure TDE to protect data at rest:\n' +
        '  ADMINISTER KEY MANAGEMENT CREATE KEYSTORE \'<wallet_dir>\' IDENTIFIED BY "<pwd>";\n' +
        '  ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "<pwd>";\n' +
        '  ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "<pwd>" WITH BACKUP;\n' +
        'TDE is included in Oracle EE 19c+ without an extra license.',
    });
  } else {
    const wallet  = walletRows[0];
    const wStatus = (wallet.STATUS || '').toUpperCase();
    const wType   = (wallet.WALLET_TYPE || '').toUpperCase();

    if (wStatus !== 'OPEN') {
      score = Math.max(0, score - 25);
      findings.push({
        key: 'sp_encryption_wallet_closed', check_id: 'sp_encryption', severity: 'critical',
        title: `TDE wallet is ${wStatus} — encrypted data is inaccessible or unprotected`,
        metric_line: `status=${wStatus}  wallet_type=${wType}`,
        remediation: 'Open the TDE wallet:\n' +
          '  ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "<pwd>";\n' +
          'If AUTOLOGIN, investigate why auto-open failed on startup.',
      });
    } else if (wType === 'AUTOLOGIN') {
      score = Math.max(0, score - 10);
      findings.push({
        key: 'sp_encryption_autologin', check_id: 'sp_encryption', severity: 'info',
        title: 'TDE wallet is AUTOLOGIN — master key stored in an obfuscated .sso file on disk',
        metric_line: `wallet_type=${wType}  status=${wStatus}`,
        remediation: 'If an attacker copies the disk, the master key is recoverable from the .sso file.\n' +
          'Consider Local Auto-Login (Oracle 12.2+) as a balanced alternative, or manual-open wallets for the highest key protection.',
      });
    }
  }

  if (unencRows && unencRows.length > 0) {
    score = Math.max(0, score - Math.min(30, unencRows.length * 5));
    findings.push({
      key: 'sp_encryption_unenc_tablespaces', check_id: 'sp_encryption', severity: 'warning',
      title: `${unencRows.length} non-system tablespace(s) not encrypted with TDE`,
      metric_line: unencRows.map(r => r.TABLESPACE_NAME).join(', '),
      remediation: 'Encrypt tablespaces online (Oracle 12.2+, no downtime required):\n' +
        unencRows.slice(0, 5).map(r =>
          `  ALTER TABLESPACE ${r.TABLESPACE_NAME} ENCRYPTION ONLINE USING AES256 ENCRYPT FILE_NAME_CONVERT=NONE;`
        ).join('\n') +
        '\nOlder releases require the tablespace to be taken offline.',
    });
  }

  score = Math.max(0, score);
  // Optional group: cap all findings to 'info'; map warn/fail → advisory badge
  findings.forEach(f => { f.severity = 'info'; });
  const encStatus = findings.length > 0 ? 'advisory' : (score === null ? 'error' : 'pass');
  return { id: 'sp_encryption', name: 'Encryption (TDE)', score, findings, status: encStatus, queries, optional: true };
}

// ─── 9. sp_db_links — database link inventory ────────────────────────────────
//
// Checks dba_db_links for PUBLIC links (accessible by everyone) and fixed-user
// links (embedded remote credentials). Zero links → immediate PASS.

async function checkDbLinks(queryFn) {
  const findings = [];
  const queries  = [];
  let score = 100;

  const sql = `SELECT owner, db_link, username, host FROM dba_db_links ORDER BY owner, db_link`;

  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: 'sp_db_links', name: 'Database Links', score: null, error: err.message, findings: [], status: errorStatus(err.message), queries };
  }

  if (!rows.length) {
    return { id: 'sp_db_links', name: 'Database Links', score, findings, status: 'pass', queries };
  }

  const publicLinks    = rows.filter(r => r.OWNER === 'PUBLIC');
  const fixedUserLinks = rows.filter(r => r.OWNER !== 'PUBLIC' && r.USERNAME);

  if (publicLinks.length > 0) {
    score = Math.max(0, score - Math.min(60, publicLinks.length * 30));
    findings.push({
      key: 'sp_db_links_public', check_id: 'sp_db_links', severity: 'critical',
      title: `${publicLinks.length} PUBLIC database link(s) — usable by every connected user`,
      metric_line: publicLinks.map(r => `${r.DB_LINK} → ${r.HOST}`).join(', '),
      remediation: `PUBLIC db links require no explicit grant; any user can access the remote DB through them:\n` +
        publicLinks.map(r => `  DROP PUBLIC DATABASE LINK ${r.DB_LINK};`).join('\n') +
        '\nRecreate as private links owned by the specific schema that needs them.',
    });
  }

  if (fixedUserLinks.length > 0) {
    score = Math.max(0, score - Math.min(30, fixedUserLinks.length * 10));
    findings.push({
      key: 'sp_db_links_fixed_user', check_id: 'sp_db_links', severity: 'warning',
      title: `${fixedUserLinks.length} fixed-user database link(s) with embedded credentials`,
      metric_line: fixedUserLinks.map(r => `${r.OWNER}.${r.DB_LINK} (remote user: ${r.USERNAME})`).join(', '),
      remediation: `Fixed-user links embed a hardcoded remote username in the data dictionary.\n` +
        `If the remote password rotates, the link silently breaks. Prefer "connected user" links.\n` +
        fixedUserLinks.map(r => `  DROP DATABASE LINK ${r.DB_LINK};  -- connect as ${r.OWNER} first`).join('\n'),
    });
  }

  score = Math.max(0, score);
  return { id: 'sp_db_links', name: 'Database Links', score, findings, status: deriveStatus(score, findings), queries };
}

// ─── 10. sp_patch_currency — Oracle version and Release Update recency ────────
//
// Fetches version_full from v$instance (18c+), falls back to v$version banner.
// Checks dba_registry_sqlpatch for last applied patch date.
// Warns if > 12 months; critical if > 24 months or no history at all.

async function checkPatchCurrency(queryFn) {
  const findings = [];
  const queries  = [];
  let score = 100;
  let versionStr = null;

  const SQL_VER    = `SELECT version_full FROM v$instance`;
  const SQL_VER_FB = `SELECT banner FROM v$version WHERE rownum=1`;
  const SQL_PATCH  = `SELECT MAX(action_time) AS last_patch FROM dba_registry_sqlpatch`;

  // Try version_full (18c+); fall back to banner
  try {
    const r = await queryFn(SQL_VER);
    versionStr = ((r.rows || [])[0] || {}).VERSION_FULL || null;
    queries.push({ sql: SQL_VER, columns: r.columns || [], rawRows: r.rawRows || [] });
  } catch (err) {
    queries.push({ sql: SQL_VER, columns: [], rawRows: [], error: err.message });
    try {
      const r2 = await queryFn(SQL_VER_FB);
      versionStr = ((r2.rows || [])[0] || {}).BANNER || null;
      queries.push({ sql: SQL_VER_FB, columns: r2.columns || [], rawRows: r2.rawRows || [] });
    } catch (err2) {
      queries.push({ sql: SQL_VER_FB, columns: [], rawRows: [], error: err2.message });
    }
  }

  // Last applied patch
  let lastPatch = null;
  try {
    const r = await queryFn(SQL_PATCH);
    lastPatch = ((r.rows || [])[0] || {}).LAST_PATCH || null;
    queries.push({ sql: SQL_PATCH, columns: r.columns || [], rawRows: r.rawRows || [] });
  } catch (err) {
    queries.push({ sql: SQL_PATCH, columns: [], rawRows: [], error: err.message });
  }

  if (lastPatch === null) {
    score = Math.max(0, score - 30);
    findings.push({
      key: 'sp_patch_currency_no_history', check_id: 'sp_patch_currency', severity: 'warning',
      title: 'No patch history in dba_registry_sqlpatch',
      metric_line: 'dba_registry_sqlpatch MAX(action_time) = NULL',
      remediation: `No Oracle Release Update has been applied, or this is pre-12.1.\n` +
        `Apply the latest RU from My Oracle Support. See MOS Note 2118136.2.\n` +
        `Current version: ${versionStr || 'unknown'}`,
    });
  } else {
    const patchDate = new Date(lastPatch);
    const ageMonths = (Date.now() - patchDate.getTime()) / (1000 * 60 * 60 * 24 * 30.44);
    if (ageMonths > 24) {
      score = Math.max(0, score - 40);
      findings.push({
        key: 'sp_patch_currency_very_old', check_id: 'sp_patch_currency', severity: 'critical',
        title: `Last patch is ${Math.round(ageMonths)} months old — critically out of date`,
        metric_line: `last_patch = ${patchDate.toISOString().split('T')[0]}  (${Math.round(ageMonths)} months ago)`,
        remediation: `Over 2 years without patching exposes the database to hundreds of known CVEs.\n` +
          `Apply the latest Oracle Release Update immediately.\n` +
          `Version: ${versionStr || 'unknown'} — see MOS Note 2118136.2.`,
      });
    } else if (ageMonths > 12) {
      score = Math.max(0, score - 20);
      findings.push({
        key: 'sp_patch_currency_stale', check_id: 'sp_patch_currency', severity: 'warning',
        title: `Last patch is ${Math.round(ageMonths)} months old — consider a Release Update`,
        metric_line: `last_patch = ${patchDate.toISOString().split('T')[0]}  (${Math.round(ageMonths)} months ago)`,
        remediation: `Oracle releases quarterly CPU patches. Aim to apply each RU within 6 months of release.\n` +
          `Version: ${versionStr || 'unknown'} — see MOS Note 2118136.2.`,
      });
    }
  }

  score = Math.max(0, score);
  return { id: 'sp_patch_currency', name: 'Patch Currency', score, findings, status: deriveStatus(score, findings), queries };
}

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

function computeOverallScore(groups) {
  let total = 0;
  let weightUsed = 0;
  let errorCount = 0;
  for (const g of groups) {
    if (OPTIONAL_GROUPS.has(g.id)) continue;
    if (g.score === null) {
      errorCount++;
      continue;  // exclude errored groups from denominator; flag incomplete instead
    }
    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/security-posture/: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') {
    // ── AGENT PATH — route SQL through /api/run_sql on the target agent ──
    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') {
      // Security checks are DB-level → route to the paired DB agent using DB credentials
      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('[security-posture/run] connId=%d targetConnId=%d serverType=%s', connId, targetConnId, conn.server_type);

    // Verify agent is up before starting the scan
    let agentOnline = false;
    try {
      agentOnline = await Promise.race([
        channel.isAgentConnected(targetConnId),
        new Promise(resolve => setTimeout(() => resolve(false), 5000)),
      ]);
    } catch (err) {
      console.error('[security-posture/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 {
    // ── DIRECT PATH — oracledb thin-client (direct/TNS connections only) ──
    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();

  // EBS detection: apps-type connection OR ebs_instance_name is set → expect EBS-specific grants
  const isEbs = conn.server_type === 'apps' || conn.server_type === 'both' || !!conn.ebs_instance_name;

  // Sequential: the agent is serial — parallel Promise.all floods the queue and causes timeouts.
  const groups = [];
  for (const fn of [
    () => checkDefaultPasswords(queryFn, isEbs),
    () => checkPrivEscalation(queryFn, isEbs),
    () => checkPublicGrants(queryFn),
    () => checkInitParams(queryFn),
    () => checkPasswordPolicy(queryFn),
    () => checkAuditTrail(queryFn),
    () => checkEncryption(queryFn),
    () => checkAccountHygiene(queryFn),
    () => checkDbLinks(queryFn),
    () => checkPatchCurrency(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('[security-posture] persist error:', err);
    result.persist_error = err.message;
  }

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

// ─── GET /api/security-posture/history/:scanId ───────────────────────────────
// Returns the full generated_data for a single saved scan.
// Ownership enforced by user_id match on compliance_reports.

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 = 'security_posture'
         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/security-posture/: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;
