'use strict';

/**
 * routes/reports.js — Split DB / EBS / Combined report downloads.
 *
 * Owns: GET /api/reports/:connectionId/db   (pdf|xlsx)
 *       GET /api/reports/:connectionId/ebs  (pdf|xlsx)
 *       GET /api/reports/:connectionId/combined (pdf|xlsx)
 * Does NOT own: Oracle queries, health check execution, auth middleware.
 *
 * Each endpoint resolves the latest completed health_check for the
 * connection, then streams the requested artifact directly to the response.
 *
 * Filename convention:
 *   tunevault-db-{instance}-{YYYYMMDD}.{ext}
 *   tunevault-ebs-{instance}-{YYYYMMDD}.{ext}
 *   tunevault-full-{instance}-{YYYYMMDD}.{ext}
 */

const express = require('express');
const PDFDocument = require('pdfkit');
const ExcelJS = require('exceljs');
const pool = require('../db/index');
const { requireAuth, requireRole } = require('../middleware/auth');
const { trackEvent } = require('../db/analytics');

const router = express.Router();

// ── Brand colours (shared across all PDF generators here) ─────────────────────
const C = {
  bg:          '#FFFFFF',
  bgAlt:       '#F8F9FC',
  text:        '#1A1A2E',
  textDim:     '#6B6B8A',
  textLight:   '#FFFFFF',
  border:      '#E2E4EE',
  accent:      '#D4871A',        // TuneVault gold
  accentLight: '#FDF3E3',
  headerBg:    '#0A0A14',        // near-black header
  green:       '#059669',
  greenBg:     '#D1FAE5',
  yellow:      '#B45309',
  yellowBg:    '#FEF3C7',
  red:         '#DC2626',
  redBg:       '#FEE2E2',
  blue:        '#2563EB',
  tableHeader: '#1E1E2E',
  tableHdrTxt: '#FFFFFF',
  tableRowAlt: '#F4F5FA',
};
const PW = 595;   // A4 width
const PH = 842;   // A4 height
const MG = 44;    // page margin
const CW = PW - MG * 2;

// ── ExcelJS style helpers ──────────────────────────────────────────────────────
const XL_HDR_FILL = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF1E1E2E' } };
const XL_HDR_FONT = { bold: true, color: { argb: 'FFFFFFFF' }, size: 11 };
const XL_WARN_FONT = { color: { argb: 'FFB45309' } };
const XL_CRIT_FONT = { color: { argb: 'FFDC2626' } };
const XL_OK_FONT   = { color: { argb: 'FF059669' } };

function xlAddHeaders(sheet, headers) {
  const row = sheet.addRow(headers);
  row.eachCell(cell => {
    cell.fill = XL_HDR_FILL;
    cell.font = XL_HDR_FONT;
    cell.alignment = { horizontal: 'center' };
  });
}

function xlStatusFont(v, critThresh, warnThresh) {
  if (v > critThresh) return XL_CRIT_FONT;
  if (v > warnThresh) return XL_WARN_FONT;
  return XL_OK_FONT;
}

// ── DB category check ids (used to filter check_results) ──────────────────────
// EBS category checks have category = 'ebs_operations' or check_id starts with ebs_/cm/wf/etc.
// We split at the metrics level — m.ebs_detected flag + m.ebs_operations vs core DB metrics.

// ── Helpers: filename + date ───────────────────────────────────────────────────
function safeName(s) {
  return (s || 'oracle').replace(/[^a-z0-9_\-]/gi, '_').substring(0, 40);
}

function yyyymmdd(d) {
  const dt = d ? new Date(d) : new Date();
  return dt.toISOString().slice(0, 10).replace(/-/g, '');
}

function scoreLabel(s) {
  if (s >= 90) return 'Excellent';
  if (s >= 75) return 'Good';
  if (s >= 50) return 'Fair';
  return 'Critical';
}

// ── Security Posture report constants (mirrors routes/security-posture.js) ─────
const SP_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,
};
const SP_OPTIONAL = new Set(['sp_encryption']);
const SP_ACCENT   = '#6D28D9'; // violet-700
const SP_ACCENT_LIGHT = '#C4B5FD';

// ── Resilience report constants (mirrors routes/resilience.js) ─────────────────
const RES_WEIGHTS = {
  backup_currency: 30, archivelog_mode: 25, dataguard_lag: 20,
  fra_headroom: 15, redo_log_health: 10,
};
const RES_OPTIONAL = new Set(['dataguard_lag', 'fra_headroom']);
const RES_ACCENT   = '#0D9488'; // teal-600
const RES_ACCENT_LIGHT = '#99F6E4';

// ── DB PDF helpers ─────────────────────────────────────────────────────────────

function drawDbCoverPage(doc, data) {
  const m = data.metrics || {};
  const instance = m.instance || {};

  // Dark header band
  doc.rect(0, 0, PW, 100).fill(C.headerBg);
  // Gold accent strip
  doc.rect(0, 100, PW, 5).fill(C.accent);

  // Logo badge
  doc.rect(MG, 18, 38, 38).fillAndStroke(C.accent, C.accent);
  doc.font('Helvetica-Bold').fontSize(17).fillColor(C.headerBg)
    .text('TV', MG, 29, { width: 38, align: 'center' });

  // Wordmark
  doc.font('Helvetica-Bold').fontSize(20).fillColor(C.textLight)
    .text('TuneVault', MG + 50, 22);
  doc.font('Helvetica').fontSize(10).fillColor(C.accent)
    .text('Oracle Database Health Report', MG + 50, 46);

  // Edition badges: SE / EE / RAC / ASM
  const badges = [];
  if (instance.edition) badges.push(instance.edition.includes('Enterprise') ? 'EE' : 'SE');
  if (m.rac_nodes || instance.rac) badges.push('RAC');
  if (m.asm_diskgroups || instance.asm) badges.push('ASM');
  if (data.is_demo) badges.push('DEMO');
  let bx = MG + 50;
  const by = 64;
  for (const badge of badges) {
    const bw = badge.length * 7 + 10;
    doc.rect(bx, by, bw, 14).fill('#2A2A4A');
    doc.font('Helvetica-Bold').fontSize(7).fillColor(C.accent)
      .text(badge, bx + 5, by + 3);
    bx += bw + 5;
  }

  // Date stamp top-right
  const dateStr = new Date(data.created_at).toLocaleString('en-US', {
    month: 'short', day: 'numeric', year: 'numeric',
    hour: '2-digit', minute: '2-digit', timeZoneName: 'short',
  });
  doc.font('Helvetica').fontSize(9).fillColor(C.textDim)
    .text(dateStr, 0, 36, { width: PW - MG, align: 'right' });

  // Below header: instance name + meta
  const subY = 120;
  doc.font('Helvetica-Bold').fontSize(20).fillColor(C.text)
    .text(data.connection_name || 'Health Check', MG, subY);

  const meta = [];
  if (instance.db_name) meta.push(instance.db_name);
  if (instance.version) meta.push(`Oracle ${instance.version}`);
  if (instance.host_name) meta.push(instance.host_name);
  else if (data.host) meta.push(data.host);
  if (data.username) meta.push(`User: ${data.username}`);

  doc.font('Helvetica').fontSize(11).fillColor(C.textDim)
    .text(meta.join('  ·  '), MG, subY + 24);

  doc.moveTo(MG, subY + 48).lineTo(PW - MG, subY + 48)
    .strokeColor(C.border).lineWidth(1).stroke();

  return subY + 68;
}

function drawEbsCoverPage(doc, data) {
  const m = data.metrics || {};
  const ebs = m.ebs_operations || {};

  // Dark header band — same frame, different accent colour strip
  doc.rect(0, 0, PW, 100).fill(C.headerBg);
  doc.rect(0, 100, PW, 5).fill('#C2410C');  // orange-700 for EBS differentiation

  // Logo badge (orange accent)
  doc.rect(MG, 18, 38, 38).fillAndStroke('#C2410C', '#C2410C');
  doc.font('Helvetica-Bold').fontSize(17).fillColor(C.textLight)
    .text('EBS', MG, 29, { width: 38, align: 'center' });

  // Wordmark
  doc.font('Helvetica-Bold').fontSize(20).fillColor(C.textLight)
    .text('TuneVault', MG + 50, 22);
  doc.font('Helvetica').fontSize(10).fillColor('#F97316')  // orange-500
    .text('Oracle E-Business Suite Operations Report', MG + 50, 46);

  // Module badges: CM / OPP / WF / ADOP
  const modules = [];
  if (ebs.concurrent_managers) modules.push('CM');
  if (ebs.concurrent_managers?.cm_opp || m.opp_status) modules.push('OPP');
  if (ebs.workflow) modules.push('WF');
  if (ebs.adop_state || m.adop_sessions) modules.push('ADOP');
  if (data.is_demo) modules.push('DEMO');
  let bx = MG + 50;
  const by = 64;
  for (const mod of modules) {
    const bw = mod.length * 7 + 10;
    doc.rect(bx, by, bw, 14).fill('#2A1A0A');
    doc.font('Helvetica-Bold').fontSize(7).fillColor('#F97316')
      .text(mod, bx + 5, by + 3);
    bx += bw + 5;
  }

  // EBS version + date
  const ebsVersion = m.ebs_version || 'EBS 12.2.x';
  const dateStr = new Date(data.created_at).toLocaleString('en-US', {
    month: 'short', day: 'numeric', year: 'numeric',
    hour: '2-digit', minute: '2-digit', timeZoneName: 'short',
  });
  doc.font('Helvetica').fontSize(9).fillColor(C.textDim)
    .text(dateStr, 0, 36, { width: PW - MG, align: 'right' });

  const subY = 120;
  doc.font('Helvetica-Bold').fontSize(20).fillColor(C.text)
    .text(data.connection_name || 'EBS Health Check', MG, subY);
  doc.font('Helvetica').fontSize(11).fillColor(C.textDim)
    .text(`${ebsVersion}  ·  ${data.host || ''}`, MG, subY + 24);

  doc.moveTo(MG, subY + 48).lineTo(PW - MG, subY + 48)
    .strokeColor(C.border).lineWidth(1).stroke();

  return subY + 68;
}

function drawCombinedCoverPage(doc, data) {
  const m = data.metrics || {};
  const instance = m.instance || {};

  doc.rect(0, 0, PW, 100).fill(C.headerBg);
  // Two-tone accent strip: gold + orange
  doc.rect(0, 100, PW / 2, 5).fill(C.accent);
  doc.rect(PW / 2, 100, PW / 2, 5).fill('#C2410C');

  doc.rect(MG, 18, 38, 38).fillAndStroke(C.accent, C.accent);
  doc.font('Helvetica-Bold').fontSize(14).fillColor(C.headerBg)
    .text('TV', MG, 29, { width: 38, align: 'center' });

  doc.font('Helvetica-Bold').fontSize(20).fillColor(C.textLight)
    .text('TuneVault', MG + 50, 22);
  doc.font('Helvetica').fontSize(10).fillColor(C.accent)
    .text('TuneVault Full Stack Report', MG + 50, 46);

  // DB + EBS badge rows
  const dbBadges = [];
  if (instance.edition) dbBadges.push(instance.edition.includes('Enterprise') ? 'EE' : 'SE');
  if (m.rac_nodes || instance.rac) dbBadges.push('RAC');
  if (m.asm_diskgroups || instance.asm) dbBadges.push('ASM');
  const ebsOps = m.ebs_operations || {};
  const ebsBadges = ['CM', 'WF', 'ADOP'].filter(b =>
    (b === 'CM' && ebsOps.concurrent_managers) ||
    (b === 'WF' && ebsOps.workflow) ||
    (b === 'ADOP' && ebsOps.adop_state)
  );
  if (data.is_demo) dbBadges.push('DEMO');

  let bx = MG + 50;
  const by = 64;
  for (const badge of [...dbBadges, ...ebsBadges]) {
    const bw = badge.length * 7 + 10;
    const isEbs = ebsBadges.includes(badge);
    doc.rect(bx, by, bw, 14).fill(isEbs ? '#2A1A0A' : '#2A2A4A');
    doc.font('Helvetica-Bold').fontSize(7).fillColor(isEbs ? '#F97316' : C.accent)
      .text(badge, bx + 5, by + 3);
    bx += bw + 5;
  }

  const dateStr = new Date(data.created_at).toLocaleString('en-US', {
    month: 'short', day: 'numeric', year: 'numeric',
    hour: '2-digit', minute: '2-digit', timeZoneName: 'short',
  });
  doc.font('Helvetica').fontSize(9).fillColor(C.textDim)
    .text(dateStr, 0, 36, { width: PW - MG, align: 'right' });

  const subY = 120;
  doc.font('Helvetica-Bold').fontSize(20).fillColor(C.text)
    .text(data.connection_name || 'Full Stack Report', MG, subY);

  const meta = [];
  if (instance.db_name) meta.push(instance.db_name);
  if (instance.version) meta.push(`Oracle ${instance.version}`);
  if (m.ebs_version) meta.push(m.ebs_version);
  if (instance.host_name) meta.push(instance.host_name);
  doc.font('Helvetica').fontSize(11).fillColor(C.textDim)
    .text(meta.join('  ·  '), MG, subY + 24);

  doc.moveTo(MG, subY + 48).lineTo(PW - MG, subY + 48)
    .strokeColor(C.border).lineWidth(1).stroke();

  return subY + 68;
}

// ── PDF drawing primitives ─────────────────────────────────────────────────────

function pdfSectionTitle(doc, y, title, accent) {
  const color = accent || C.accent;
  if (y > PH - MG - 80) { doc.addPage(); y = MG; }
  doc.rect(MG, y, CW, 22).fill('#F4F5FA');
  doc.font('Helvetica-Bold').fontSize(10).fillColor(color)
    .text(title.toUpperCase(), MG + 8, y + 6, { width: CW - 16 });
  return y + 26;
}

function pdfPageCheck(doc, y, needed) {
  if (y > PH - MG - (needed || 80)) { doc.addPage(); y = MG; }
  return y;
}

function pdfKVRow(doc, y, label, value, alt) {
  doc.rect(MG, y, CW, 18).fill(alt ? C.bgAlt || '#F8F9FC' : C.bg);
  doc.font('Helvetica').fontSize(9).fillColor(C.textDim).text(label, MG + 6, y + 4);
  doc.font('Helvetica-Bold').fontSize(9).fillColor(C.text)
    .text(String(value || '—').substring(0, 60), MG + 220, y + 4, { width: CW - 230 });
  return y + 18;
}

function pdfTableHeader(doc, y, cols, widths) {
  doc.rect(MG, y, CW, 18).fill(C.tableHeader);
  let x = MG;
  for (let i = 0; i < cols.length; i++) {
    doc.font('Helvetica-Bold').fontSize(8).fillColor(C.tableHdrTxt)
      .text(cols[i], x + 4, y + 4, { width: widths[i] - 8, ellipsis: true });
    x += widths[i];
  }
  return y + 18;
}

function pdfTableRow(doc, y, cells, widths, alt) {
  doc.rect(MG, y, CW, 18).fill(alt ? C.tableRowAlt : C.bg);
  let x = MG;
  for (let i = 0; i < cells.length; i++) {
    doc.font('Helvetica').fontSize(8).fillColor(C.text)
      .text(String(cells[i] || '').substring(0, 80), x + 4, y + 4, { width: widths[i] - 8, ellipsis: true });
    x += widths[i];
  }
  return y + 18;
}

function pdfFooter(doc, connectionName) {
  const fy = PH - MG;
  doc.rect(MG, fy - 12, CW, 1).fill(C.border);
  doc.font('Helvetica').fontSize(7).fillColor(C.textDim)
    .text(`TuneVault  |  ${connectionName || 'Oracle Health Report'}`, MG, fy - 6, { width: CW - 60 });
  doc.font('Helvetica').fontSize(7).fillColor(C.textDim)
    .text('tunevault.app', MG, fy - 6, { width: CW, align: 'right' });
}

// ── DB-only PDF sections ───────────────────────────────────────────────────────

function drawDbSummaryKV(doc, y, data) {
  const m = data.metrics || {};
  const s = data.scores || {};
  const instance = m.instance || {};

  y = pdfSectionTitle(doc, y, 'Database Summary');

  const rows = [
    ['Overall Score', `${data.overall_score || 0} / 100 (${scoreLabel(data.overall_score || 0)})`],
    ['Database', instance.db_name || '—'],
    ['Oracle Version', instance.version || '—'],
    ['Host', instance.host_name || data.host || '—'],
    ['Uptime', instance.uptime_days != null ? instance.uptime_days + ' days' : '—'],
    ['CPUs', instance.cpus || '—'],
    ['SGA Target', instance.sga_target_gb != null ? instance.sga_target_gb + ' GB' : '—'],
    ['Storage Score', (s.tablespace || 0) + ' / 100'],
    ['Performance Score', (s.wait_events || 0) + ' / 100'],
    ['Memory Score', (s.memory || 0) + ' / 100'],
    ['Security Score', (s.security || 0) + ' / 100'],
    ['Backup Score', (s.backup || 0) + ' / 100'],
  ];
  rows.forEach(([k, v], i) => { y = pdfKVRow(doc, y, k, v, i % 2 === 1); });
  return y + 12;
}

function drawDbTablespaces(doc, y, tablespaces) {
  if (!tablespaces || tablespaces.length === 0) return y;
  y = pdfPageCheck(doc, y, 120);
  y = pdfSectionTitle(doc, y, 'Storage — Tablespaces');
  const cols = ['Tablespace', 'Used GB', 'Total GB', 'Usage %', 'Autoextend', 'Status'];
  const widths = [140, 70, 70, 65, 80, 82];
  y = pdfTableHeader(doc, y, cols, widths);
  tablespaces.forEach((t, i) => {
    y = pdfPageCheck(doc, y, 22);
    const status = t.pct_used > 90 ? 'CRITICAL' : t.pct_used > 80 ? 'WARNING' : 'OK';
    y = pdfTableRow(doc, y, [
      t.name, t.used_gb, t.total_gb, t.pct_used + '%',
      t.autoextend ? 'ON' : 'OFF', status,
    ], widths, i % 2 === 1);
  });
  return y + 12;
}

function drawDbWaitEvents(doc, y, waitEvents) {
  if (!waitEvents || waitEvents.length === 0) return y;
  y = pdfPageCheck(doc, y, 100);
  y = pdfSectionTitle(doc, y, 'Performance — Top Wait Events');
  const cols = ['Event', 'Wait Class', '% DB Time', 'Avg Wait ms'];
  const widths = [210, 120, 90, 87];
  y = pdfTableHeader(doc, y, cols, widths);
  waitEvents.filter(w => w.pct_db_time > 0).slice(0, 15).forEach((w, i) => {
    y = pdfPageCheck(doc, y, 22);
    y = pdfTableRow(doc, y, [
      w.event, w.wait_class, w.pct_db_time + '%', w.avg_wait_ms,
    ], widths, i % 2 === 1);
  });
  return y + 12;
}

function drawDbTopSql(doc, y, topSql) {
  if (!topSql || topSql.length === 0) return y;
  y = pdfPageCheck(doc, y, 100);
  y = pdfSectionTitle(doc, y, 'Performance — Top SQL by Elapsed Time');
  const cols = ['SQL ID', 'ms/exec', 'Executions', 'Issue', 'SQL (truncated)'];
  const widths = [90, 65, 80, 100, 172];
  y = pdfTableHeader(doc, y, cols, widths);
  topSql.slice(0, 15).forEach((s, i) => {
    y = pdfPageCheck(doc, y, 22);
    y = pdfTableRow(doc, y, [
      s.sql_id, s.elapsed_per_exec_ms, s.executions,
      (s.issue || '').substring(0, 25), (s.sql_text || '').substring(0, 50),
    ], widths, i % 2 === 1);
  });
  return y + 12;
}

function drawDbSecurity(doc, y, m) {
  y = pdfPageCheck(doc, y, 80);
  y = pdfSectionTitle(doc, y, 'Security');
  const sec = m.security || {};
  const rows = [
    ['Audit Trail', sec.audit_trail || '—'],
    ['Failed Logins (24h)', sec.failed_logins_24h ?? '—'],
    ['Default Passwords', sec.default_passwords ?? '—'],
    ['Open Public Synonyms', sec.open_public_synonyms ?? '—'],
    ['DB Vault Enabled', sec.db_vault_enabled ? 'YES' : (sec.db_vault_enabled === false ? 'NO' : '—')],
  ];
  rows.forEach(([k, v], i) => { y = pdfKVRow(doc, y, k, v, i % 2 === 1); });
  return y + 12;
}

function drawDbBackup(doc, y, backup) {
  if (!backup) return y;
  y = pdfPageCheck(doc, y, 100);
  y = pdfSectionTitle(doc, y, 'Backup & Recovery');
  const bk = backup;
  const rows = [
    ['Overall Backup Status', (bk.overall_status || 'unknown').toUpperCase()],
    ['RMAN Configured', bk.rman_backup?.rman_available ? 'YES' : 'NO'],
    ['Last Full Backup', bk.rman_backup?.full_backup_hours_ago != null ? bk.rman_backup.full_backup_hours_ago + 'h ago' : 'NONE'],
    ['Archive Mode', bk.archivelog_rate?.log_mode || '—'],
    ['Log Switches/Hour', bk.archivelog_rate?.switches_per_hour ?? '—'],
    ['FRA Usage', bk.fra_usage?.pct_used != null ? bk.fra_usage.pct_used + '%' : '—'],
    ['Backup Corruptions', bk.backup_validation?.total_corruptions ?? '—'],
  ];
  rows.forEach(([k, v], i) => { y = pdfKVRow(doc, y, k, v, i % 2 === 1); });
  return y + 12;
}

function drawDbMemory(doc, y, sga, pga) {
  y = pdfPageCheck(doc, y, 100);
  y = pdfSectionTitle(doc, y, 'Memory — SGA / PGA');
  const rows = [
    ['SGA Size', (sga?.sga_size_gb || 0) + ' GB'],
    ['Buffer Cache', (sga?.buffer_cache_gb || 0) + ' GB'],
    ['Buffer Cache Hit Ratio', (sga?.buffer_cache_hit_ratio || 0) + '%'],
    ['Library Cache Hit Ratio', (sga?.library_cache_hit_ratio || 0) + '%'],
    ['Shared Pool Free', (sga?.shared_pool_free_pct || 0) + '%'],
    ['Hard Parses/sec', sga?.hard_parses_per_sec || 0],
    ['PGA Target', (pga?.pga_target_gb || 0) + ' GB'],
    ['PGA Allocated', (pga?.pga_allocated_gb || 0) + ' GB'],
    ['PGA Optimal %', (pga?.optimal_executions_pct || 0) + '%'],
  ];
  rows.forEach(([k, v], i) => { y = pdfKVRow(doc, y, k, v, i % 2 === 1); });
  return y + 12;
}

function drawDbRacAsm(doc, y, m) {
  const rac = m.rac_nodes;
  const asm = m.asm_diskgroups;
  if (!rac && !asm) return y;

  if (rac && rac.length > 0) {
    y = pdfPageCheck(doc, y, 80);
    y = pdfSectionTitle(doc, y, 'RAC Nodes');
    const cols = ['Node', 'Status', 'Open Mode'];
    const widths = [170, 120, 217];
    y = pdfTableHeader(doc, y, cols, widths);
    rac.forEach((n, i) => {
      y = pdfPageCheck(doc, y, 22);
      y = pdfTableRow(doc, y, [n.instance_name || n.node, n.status, n.open_mode || '—'], widths, i % 2 === 1);
    });
    y += 12;
  }

  if (asm && asm.length > 0) {
    y = pdfPageCheck(doc, y, 80);
    y = pdfSectionTitle(doc, y, 'ASM Disk Groups');
    const cols = ['Disk Group', 'State', 'Total GB', 'Free GB', 'Usable GB'];
    const widths = [130, 80, 80, 80, 137];
    y = pdfTableHeader(doc, y, cols, widths);
    asm.forEach((g, i) => {
      y = pdfPageCheck(doc, y, 22);
      y = pdfTableRow(doc, y, [g.name, g.state, g.total_gb, g.free_gb, g.usable_gb], widths, i % 2 === 1);
    });
    y += 12;
  }

  return y;
}

// ── EBS-only PDF sections ──────────────────────────────────────────────────────

function drawEbsSummary(doc, y, data) {
  const m = data.metrics || {};
  const ebs = m.ebs_operations || {};

  y = pdfSectionTitle(doc, y, 'EBS Operations Summary', '#C2410C');
  const cm = ebs.concurrent_managers || {};
  const wf = ebs.workflow || {};
  const rows = [
    ['Connection', data.connection_name || '—'],
    ['EBS Version', m.ebs_version || 'EBS 12.2.x'],
    ['EBS Detected', m.ebs_detected ? 'YES' : 'NO'],
    ['ICM Running / Target', cm.cm01 ? `${cm.cm01.running_processes} / ${cm.cm01.max_processes}` : '—'],
    ['Pending CM Requests', cm.cm02?.pending_requests ?? '—'],
    ['CM Error Requests (24h)', cm.cm10?.error_requests_24h ?? '—'],
    ['WF Errors', wf.wf02?.error_count ?? '—'],
    ['WF Deferred Queue', wf.wf03?.deferred_ready ?? '—'],
    ['WF Notifications >2h', wf.wf08?.pending_over_2h ?? '—'],
  ];
  rows.forEach(([k, v], i) => { y = pdfKVRow(doc, y, k, v, i % 2 === 1); });
  return y + 12;
}

function drawEbsConcurrentManagers(doc, y, cm) {
  if (!cm) return y;
  y = pdfPageCheck(doc, y, 100);
  y = pdfSectionTitle(doc, y, 'Concurrent Managers', '#C2410C');
  const rows = [
    ['ICM Running / Target', cm.cm01 ? `${cm.cm01.running_processes} / ${cm.cm01.max_processes}` : '—'],
    ['ICM Control Code', cm.cm01?.control_code || '—'],
    ['Pending Requests (P/I)', cm.cm02?.pending_requests ?? '—'],
    ['Avg Runtime (24h)', cm.cm05?.avg_runtime_secs != null ? cm.cm05.avg_runtime_secs + 's' : '—'],
    ['Completed (24h)', cm.cm05?.completed_24h ?? '—'],
    ['Error Requests (24h)', cm.cm10?.error_requests_24h ?? '—'],
  ];
  rows.forEach(([k, v], i) => { y = pdfKVRow(doc, y, k, v, i % 2 === 1); });

  if (cm.cm09 && cm.cm09.length > 0) {
    y += 8;
    const cols = ['Program', 'Runtime (min)', 'Started'];
    const widths = [260, 110, 137];
    y = pdfTableHeader(doc, y, cols, widths);
    cm.cm09.slice(0, 10).forEach((r, i) => {
      y = pdfPageCheck(doc, y, 22);
      y = pdfTableRow(doc, y, [
        r.program, Math.round((r.runtime_secs || 0) / 60), r.start_time,
      ], widths, i % 2 === 1);
    });
  }
  return y + 12;
}

function drawEbsWorkflow(doc, y, wf) {
  if (!wf) return y;
  y = pdfPageCheck(doc, y, 80);
  y = pdfSectionTitle(doc, y, 'Workflow', '#C2410C');
  const rows = [
    ['Error Items', wf.wf02?.error_count ?? '—'],
    ['Deferred Queue (state=0)', wf.wf03?.deferred_ready ?? '—'],
    ['Notifications Pending >2h', wf.wf08?.pending_over_2h ?? '—'],
    ['Notifications Pending >8h', wf.wf08?.pending_over_8h ?? '—'],
  ];
  rows.forEach(([k, v], i) => { y = pdfKVRow(doc, y, k, v, i % 2 === 1); });
  return y + 12;
}

function drawEbsOaCore(doc, y, ebs) {
  // OACore / OPP data lives in managed_servers / opp
  const servers = ebs.managed_servers || [];
  const opp = ebs.opp_status || ebs.opp || null;
  if (!servers.length && !opp) return y;
  y = pdfPageCheck(doc, y, 80);
  y = pdfSectionTitle(doc, y, 'OACore / OPP / Managed Servers', '#C2410C');
  if (servers.length > 0) {
    const cols = ['Server', 'Status'];
    const widths = [350, 157];
    y = pdfTableHeader(doc, y, cols, widths);
    servers.forEach((s, i) => {
      y = pdfPageCheck(doc, y, 22);
      y = pdfTableRow(doc, y, [s.label || s.name, s.status || '—'], widths, i % 2 === 1);
    });
    y += 8;
  }
  if (opp) {
    const rows = [
      ['OPP Status', opp.status || '—'],
      ['OPP Queue Depth', opp.queue_depth ?? '—'],
    ];
    rows.forEach(([k, v], i) => { y = pdfKVRow(doc, y, k, v, i % 2 === 1); });
  }
  return y + 12;
}

function drawEbsSecurity(doc, y, sec) {
  if (!sec) return y;
  y = pdfPageCheck(doc, y, 80);
  y = pdfSectionTitle(doc, y, 'EBS Security', '#C2410C');
  const rows = [
    ['Signon Audit Level', sec.sc12?.signon_audit_level || '—'],
    ['Sysadmin User Count', sec.sc14?.length ?? '—'],
  ];
  rows.forEach(([k, v], i) => { y = pdfKVRow(doc, y, k, v, i % 2 === 1); });
  return y + 12;
}

function drawEbsAdop(doc, y, ebs) {
  const adop = ebs.adop_state || ebs.adop;
  if (!adop || !adop.sessions || adop.sessions.length === 0) return y;
  y = pdfPageCheck(doc, y, 80);
  y = pdfSectionTitle(doc, y, 'ADOP Sessions', '#C2410C');
  const cols = ['Session ID', 'Phase', 'Status', 'Started', 'Patch'];
  const widths = [80, 80, 80, 100, 167];
  y = pdfTableHeader(doc, y, cols, widths);
  adop.sessions.slice(0, 10).forEach((s, i) => {
    y = pdfPageCheck(doc, y, 22);
    y = pdfTableRow(doc, y, [
      s.session_id, s.phase, s.status,
      String(s.start_date || '—').substring(0, 10), s.patch_name || '—',
    ], widths, i % 2 === 1);
  });
  return y + 12;
}

// ── Full PDF renderers (DB-only, EBS-only, Combined) ──────────────────────────

function generateDbPDF(data, out) {
  const doc = new PDFDocument({
    size: 'A4',
    margins: { top: MG, bottom: MG, left: MG, right: MG },
    info: {
      Title: `Oracle Database Health Report — ${data.connection_name}`,
      Author: 'TuneVault', Subject: 'Oracle Database Health Check',
      Creator: 'TuneVault', Producer: 'TuneVault',
    },
    compress: true,
    bufferPages: true,
    autoFirstPage: true,
  });
  if (out) doc.pipe(out);

  const m = data.metrics || {};
  let y = drawDbCoverPage(doc, data);
  y = drawDbSummaryKV(doc, y, data);
  doc.addPage(); y = MG;
  y = drawDbTablespaces(doc, y, m.tablespaces || []);
  y = drawDbWaitEvents(doc, y, m.wait_events || []);
  y = drawDbTopSql(doc, y, m.top_sql || []);
  y = drawDbSecurity(doc, y, m);
  y = drawDbBackup(doc, y, m.backup_stats || null);
  y = drawDbMemory(doc, y, m.sga_stats || {}, m.pga_stats || {});
  drawDbRacAsm(doc, y, m);

  const range = doc.bufferedPageRange();
  for (let i = range.start; i < range.start + range.count; i++) {
    doc.switchToPage(i);
    pdfFooter(doc, data.connection_name);
  }

  doc.end();
  return doc;
}

function generateEbsPDF(data, out) {
  const doc = new PDFDocument({
    size: 'A4',
    margins: { top: MG, bottom: MG, left: MG, right: MG },
    info: {
      Title: `Oracle E-Business Suite Operations Report — ${data.connection_name}`,
      Author: 'TuneVault', Subject: 'Oracle EBS Operations Health Check',
      Creator: 'TuneVault', Producer: 'TuneVault',
    },
    compress: true,
    bufferPages: true,
    autoFirstPage: true,
  });
  if (out) doc.pipe(out);

  const m = data.metrics || {};
  const ebs = m.ebs_operations || {};

  let y = drawEbsCoverPage(doc, data);
  y = drawEbsSummary(doc, y, data);
  doc.addPage(); y = MG;
  y = drawEbsConcurrentManagers(doc, y, ebs.concurrent_managers);
  y = drawEbsWorkflow(doc, y, ebs.workflow);
  y = drawEbsOaCore(doc, y, ebs);
  y = drawEbsSecurity(doc, y, ebs.security);
  drawEbsAdop(doc, y, ebs);

  const range = doc.bufferedPageRange();
  for (let i = range.start; i < range.start + range.count; i++) {
    doc.switchToPage(i);
    pdfFooter(doc, data.connection_name);
  }

  doc.end();
  return doc;
}

function generateAppTierPDF(data, out) {
  const doc = new PDFDocument({
    size: 'A4',
    margins: { top: MG, bottom: MG, left: MG, right: MG },
    info: {
      Title: `EBS App Tier Report — ${data.connection_name}`,
      Author: 'TuneVault', Subject: 'Oracle E-Business Suite App Tier Health Report',
      Creator: 'TuneVault', Producer: 'TuneVault',
    },
    compress: true,
    bufferPages: true,
    autoFirstPage: true,
  });
  if (out) doc.pipe(out);

  const m = data.metrics || {};
  const findings = m.findings || [];
  const checksOk = m.checks_ok || [];

  // Cover page — blue accent for app tier
  doc.rect(0, 0, PW, 100).fill(C.headerBg);
  doc.rect(0, 100, PW, 5).fill('#2563EB');
  doc.rect(MG, 18, 38, 38).fillAndStroke('#2563EB', '#2563EB');
  doc.font('Helvetica-Bold').fontSize(14).fillColor(C.textLight)
    .text('APP', MG, 29, { width: 38, align: 'center' });
  doc.font('Helvetica-Bold').fontSize(20).fillColor(C.textLight)
    .text('TuneVault', MG + 50, 22);
  doc.font('Helvetica').fontSize(10).fillColor('#60A5FA')
    .text('Oracle EBS App Tier Health Report', MG + 50, 46);

  const dateStr = new Date(data.created_at).toLocaleString('en-US', {
    month: 'short', day: 'numeric', year: 'numeric',
    hour: '2-digit', minute: '2-digit', timeZoneName: 'short',
  });
  doc.font('Helvetica').fontSize(9).fillColor(C.textDim)
    .text(dateStr, 0, 36, { width: PW - MG, align: 'right' });

  const subY = 120;
  doc.font('Helvetica-Bold').fontSize(20).fillColor(C.text)
    .text(data.connection_name || 'App Tier Report', MG, subY);
  doc.font('Helvetica').fontSize(11).fillColor(C.textDim)
    .text(`App Tier  ·  ${data.host || ''}`, MG, subY + 24);
  doc.moveTo(MG, subY + 48).lineTo(PW - MG, subY + 48)
    .strokeColor(C.border).lineWidth(1).stroke();

  let y = subY + 68;

  // Summary KV
  y = pdfSectionTitle(doc, y, 'Summary', '#2563EB');
  const totals = m.checks_total || (findings.length + checksOk.length);
  const crits  = findings.filter(f => f.severity === 'critical').length;
  const warns  = findings.filter(f => f.severity === 'warning').length;
  [
    ['Connection',      data.connection_name || '—'],
    ['Host',            data.host || '—'],
    ['Total Checks',    String(totals)],
    ['Checks Passed',   String(checksOk.length)],
    ['Warnings',        String(warns)],
    ['Critical Issues', String(crits)],
  ].forEach(([label, value], i) => { y = pdfKVRow(doc, y, label, value, i % 2 === 1); });

  // Findings section
  if (findings.length > 0) {
    doc.addPage(); y = MG;
    y = pdfSectionTitle(doc, y, 'Findings', '#DC2626');
    y = pdfTableHeader(doc, y, ['Severity', 'Check', 'Details'], [70, 160, CW - 230]);
    findings.forEach((f, i) => {
      y = pdfPageCheck(doc, y, 20);
      const sev = (f.severity || 'info').toUpperCase();
      y = pdfTableRow(doc, y, [sev, f.title || f.check_id || '—', f.details || '—'], [70, 160, CW - 230], i % 2 === 1);
    });
  }

  // Checks passed section
  if (checksOk.length > 0) {
    y = pdfPageCheck(doc, y, 60);
    y = pdfSectionTitle(doc, y, 'Checks Passed', '#059669');
    checksOk.forEach((c, i) => {
      y = pdfPageCheck(doc, y, 20);
      y = pdfTableRow(doc, y, ['OK', c.title || c.check_id || '—', c.details || '—'], [70, 160, CW - 230], i % 2 === 1);
    });
  }

  const range = doc.bufferedPageRange();
  for (let i = range.start; i < range.start + range.count; i++) {
    doc.switchToPage(i);
    pdfFooter(doc, data.connection_name);
  }
  doc.end();
  return doc;
}

async function generateAppTierXLSX(data, res, filename) {
  const m = data.metrics || {};
  const findings = m.findings || [];
  const checksOk = m.checks_ok || [];
  const wb = new ExcelJS.Workbook();
  wb.creator = 'TuneVault';
  wb.created = new Date();

  // Summary sheet
  const sum = wb.addWorksheet('Summary');
  sum.columns = [{ header: 'Metric', key: 'k', width: 28 }, { header: 'Value', key: 'v', width: 30 }];
  xlAddHeaders(sum, ['Metric', 'Value']);
  const totals = m.checks_total || (findings.length + checksOk.length);
  [
    ['Connection', data.connection_name],
    ['Host', data.host || '—'],
    ['Total Checks', totals],
    ['Checks Passed', checksOk.length],
    ['Warnings', findings.filter(f => f.severity === 'warning').length],
    ['Critical Issues', findings.filter(f => f.severity === 'critical').length],
    ['Generated', new Date(data.created_at).toLocaleString()],
  ].forEach(([k, v]) => sum.addRow({ k, v: String(v ?? '—') }));

  // Findings sheet
  const findSheet = wb.addWorksheet('Findings');
  findSheet.columns = [
    { header: 'Severity', key: 's', width: 12 },
    { header: 'Check ID', key: 'id', width: 22 },
    { header: 'Title', key: 't', width: 40 },
    { header: 'Details', key: 'd', width: 60 },
  ];
  xlAddHeaders(findSheet, ['Severity', 'Check ID', 'Title', 'Details']);
  findings.forEach(f => {
    const row = findSheet.addRow({ s: f.severity || 'info', id: f.check_id || '—', t: f.title || '—', d: f.details || '—' });
    const font = f.severity === 'critical' ? XL_CRIT_FONT : f.severity === 'warning' ? XL_WARN_FONT : {};
    if (font.color) row.getCell('s').font = font;
  });

  // Checks Passed sheet
  const okSheet = wb.addWorksheet('Checks Passed');
  okSheet.columns = [
    { header: 'Check ID', key: 'id', width: 22 },
    { header: 'Title', key: 't', width: 40 },
    { header: 'Details', key: 'd', width: 60 },
  ];
  xlAddHeaders(okSheet, ['Check ID', 'Title', 'Details']);
  checksOk.forEach(c => okSheet.addRow({ id: c.check_id || '—', t: c.title || '—', d: c.details || '—' }));

  res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  res.setHeader('Content-Disposition', `attachment; filename="${filename}"`);
  await wb.xlsx.write(res);
  res.end();
}

function generateCombinedPDF(data, out) {
  const doc = new PDFDocument({
    size: 'A4',
    margins: { top: MG, bottom: MG, left: MG, right: MG },
    info: {
      Title: `TuneVault Full Stack Report — ${data.connection_name}`,
      Author: 'TuneVault', Subject: 'Oracle Database + EBS Full Stack Health Report',
      Creator: 'TuneVault', Producer: 'TuneVault',
    },
    compress: true,
    bufferPages: true,
    autoFirstPage: true,
  });
  if (out) doc.pipe(out);

  const m = data.metrics || {};
  const ebs = m.ebs_operations || {};

  let y = drawCombinedCoverPage(doc, data);

  // ─ DB sections ─
  y = pdfSectionTitle(doc, y, '━━━  PART 1: DATABASE  ━━━');
  y = drawDbSummaryKV(doc, y, data);
  doc.addPage(); y = MG;
  y = drawDbTablespaces(doc, y, m.tablespaces || []);
  y = drawDbWaitEvents(doc, y, m.wait_events || []);
  y = drawDbTopSql(doc, y, m.top_sql || []);
  y = drawDbSecurity(doc, y, m);
  y = drawDbBackup(doc, y, m.backup_stats || null);
  y = drawDbMemory(doc, y, m.sga_stats || {}, m.pga_stats || {});
  y = drawDbRacAsm(doc, y, m);

  // ─ EBS sections — only when EBS was detected on this instance ─
  if (m.ebs_detected) {
    doc.addPage(); y = MG;
    y = pdfSectionTitle(doc, y, '━━━  PART 2: EBS OPERATIONS  ━━━', '#C2410C');
    y = drawEbsSummary(doc, y, data);
    y = drawEbsConcurrentManagers(doc, y, ebs.concurrent_managers);
    y = drawEbsWorkflow(doc, y, ebs.workflow);
    y = drawEbsOaCore(doc, y, ebs);
    y = drawEbsSecurity(doc, y, ebs.security);
    drawEbsAdop(doc, y, ebs);
  }

  const range = doc.bufferedPageRange();
  for (let i = range.start; i < range.start + range.count; i++) {
    doc.switchToPage(i);
    pdfFooter(doc, data.connection_name);
  }

  doc.end();
  return doc;
}

// ── XLSX generators ────────────────────────────────────────────────────────────

async function generateDbXLSX(data, res, filename) {
  const m = data.metrics || {};
  const s = data.scores || {};
  const wb = new ExcelJS.Workbook();
  wb.creator = 'TuneVault';
  wb.created = new Date();

  // Summary sheet
  const sum = wb.addWorksheet('Summary');
  sum.columns = [{ header: 'Metric', key: 'k', width: 28 }, { header: 'Value', key: 'v', width: 24 }];
  xlAddHeaders(sum, ['Metric', 'Value']);
  const instance = m.instance || {};
  [
    ['Connection', data.connection_name],
    ['Database', instance.db_name || '—'],
    ['Oracle Version', instance.version || '—'],
    ['Host', instance.host_name || data.host || '—'],
    ['Overall Score', data.overall_score],
    ['Storage Score', s.tablespace || 0],
    ['Performance Score', s.wait_events || 0],
    ['SQL Score', s.sql_performance || 0],
    ['Memory Score', s.memory || 0],
    ['Security Score', s.security || 0],
    ['Backup Score', s.backup || 0],
    ['Generated', new Date(data.created_at).toLocaleString()],
  ].forEach(([k, v]) => sum.addRow({ k, v }));

  // Storage sheet
  if (m.tablespaces && m.tablespaces.length > 0) {
    const ts = wb.addWorksheet('Storage');
    ts.columns = [
      { header: 'Name', key: 'name', width: 24 }, { header: 'Used GB', key: 'used_gb', width: 12 },
      { header: 'Total GB', key: 'total_gb', width: 12 }, { header: 'Usage %', key: 'pct', width: 10 },
      { header: 'Autoextend', key: 'ae', width: 12 }, { header: 'Status', key: 'st', width: 12 },
    ];
    xlAddHeaders(ts, ['Name', 'Used GB', 'Total GB', 'Usage %', 'Autoextend', 'Status']);
    m.tablespaces.forEach(t => {
      const row = ts.addRow({ name: t.name, used_gb: t.used_gb, total_gb: t.total_gb, pct: t.pct_used, ae: t.autoextend ? 'ON' : 'OFF', st: t.pct_used > 90 ? 'CRITICAL' : t.pct_used > 80 ? 'WARNING' : 'OK' });
      row.getCell('pct').font = xlStatusFont(t.pct_used, 90, 80);
    });
  }

  // Performance sheet (wait events)
  if (m.wait_events && m.wait_events.length > 0) {
    const we = wb.addWorksheet('Performance');
    we.columns = [
      { header: 'Event', key: 'event', width: 36 }, { header: 'Wait Class', key: 'wc', width: 20 },
      { header: '% DB Time', key: 'pct', width: 12 }, { header: 'Avg Wait ms', key: 'avg', width: 14 },
    ];
    xlAddHeaders(we, ['Event', 'Wait Class', '% DB Time', 'Avg Wait ms']);
    m.wait_events.filter(w => w.pct_db_time > 0).forEach(w => {
      const row = we.addRow({ event: w.event, wc: w.wait_class, pct: w.pct_db_time, avg: w.avg_wait_ms });
      row.getCell('pct').font = xlStatusFont(w.pct_db_time, 10, 5);
    });
  }

  // Security sheet
  {
    const sec = wb.addWorksheet('Security');
    sec.columns = [{ header: 'Metric', key: 'k', width: 30 }, { header: 'Value', key: 'v', width: 24 }];
    xlAddHeaders(sec, ['Metric', 'Value']);
    const secData = m.security || {};
    [
      ['Audit Trail', secData.audit_trail || '—'],
      ['Failed Logins (24h)', secData.failed_logins_24h ?? '—'],
      ['Default Passwords', secData.default_passwords ?? '—'],
      ['DB Vault Enabled', secData.db_vault_enabled ? 'YES' : (secData.db_vault_enabled === false ? 'NO' : '—')],
    ].forEach(([k, v]) => sec.addRow({ k, v: String(v) }));
  }

  // Backup sheet
  if (m.backup_stats) {
    const bk = wb.addWorksheet('Backup');
    bk.columns = [{ header: 'Check', key: 'c', width: 30 }, { header: 'Status', key: 's', width: 14 }, { header: 'Detail', key: 'd', width: 44 }];
    xlAddHeaders(bk, ['Check', 'Status', 'Detail']);
    const b = m.backup_stats;
    bk.addRow({ c: 'Overall', s: (b.overall_status || 'unknown').toUpperCase(), d: '' });
    if (b.rman_backup) {
      const r = b.rman_backup;
      bk.addRow({ c: 'RMAN Configured', s: r.rman_available ? 'YES' : 'NO', d: '' });
      bk.addRow({ c: 'Last Full Backup', s: (r.status || 'unknown').toUpperCase(), d: r.full_backup_hours_ago != null ? r.full_backup_hours_ago + 'h ago' : 'NONE' });
    }
    if (b.archivelog_rate) {
      bk.addRow({ c: 'Archive Mode', s: b.archivelog_rate.log_mode || 'UNKNOWN', d: '' });
      bk.addRow({ c: 'Log Switches/Hour', s: '', d: String(b.archivelog_rate.switches_per_hour || 0) });
    }
  }

  // RAC sheet
  if (m.rac_nodes && m.rac_nodes.length > 0) {
    const rac = wb.addWorksheet('RAC');
    rac.columns = [{ header: 'Node', key: 'n', width: 28 }, { header: 'Status', key: 's', width: 16 }, { header: 'Open Mode', key: 'o', width: 18 }];
    xlAddHeaders(rac, ['Node', 'Status', 'Open Mode']);
    m.rac_nodes.forEach(n => rac.addRow({ n: n.instance_name || n.node, s: n.status, o: n.open_mode || '—' }));
  }

  // ASM sheet
  if (m.asm_diskgroups && m.asm_diskgroups.length > 0) {
    const asm = wb.addWorksheet('ASM');
    asm.columns = [
      { header: 'Disk Group', key: 'n', width: 24 }, { header: 'State', key: 's', width: 14 },
      { header: 'Total GB', key: 'tg', width: 12 }, { header: 'Free GB', key: 'fg', width: 12 },
      { header: 'Usable GB', key: 'ug', width: 12 },
    ];
    xlAddHeaders(asm, ['Disk Group', 'State', 'Total GB', 'Free GB', 'Usable GB']);
    m.asm_diskgroups.forEach(g => asm.addRow({ n: g.name, s: g.state, tg: g.total_gb, fg: g.free_gb, ug: g.usable_gb }));
  }

  res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  res.setHeader('Content-Disposition', `attachment; filename="${filename}"`);
  await wb.xlsx.write(res);
  res.end();
}

async function generateEbsXLSX(data, res, filename) {
  const m = data.metrics || {};
  const ebs = m.ebs_operations || {};
  const wb = new ExcelJS.Workbook();
  wb.creator = 'TuneVault';
  wb.created = new Date();

  // Summary sheet
  const sum = wb.addWorksheet('Summary');
  sum.columns = [{ header: 'Metric', key: 'k', width: 30 }, { header: 'Value', key: 'v', width: 24 }];
  xlAddHeaders(sum, ['Metric', 'Value']);
  const cm = ebs.concurrent_managers || {};
  const wf = ebs.workflow || {};
  [
    ['Connection', data.connection_name],
    ['EBS Version', m.ebs_version || 'EBS 12.2.x'],
    ['Generated', new Date(data.created_at).toLocaleString()],
    ['ICM Running / Target', cm.cm01 ? `${cm.cm01.running_processes} / ${cm.cm01.max_processes}` : '—'],
    ['Pending CM Requests', cm.cm02?.pending_requests ?? '—'],
    ['CM Error Requests (24h)', cm.cm10?.error_requests_24h ?? '—'],
    ['WF Error Items', wf.wf02?.error_count ?? '—'],
    ['WF Deferred Queue', wf.wf03?.deferred_ready ?? '—'],
    ['WF Notifications >2h', wf.wf08?.pending_over_2h ?? '—'],
  ].forEach(([k, v]) => sum.addRow({ k, v: String(v ?? '—') }));

  // Concurrent Managers sheet
  {
    const cmSheet = wb.addWorksheet('Concurrent Managers');
    cmSheet.columns = [
      { header: 'Check', key: 'c', width: 30 }, { header: 'Status', key: 's', width: 12 }, { header: 'Value', key: 'v', width: 20 }, { header: 'Notes', key: 'n', width: 36 },
    ];
    xlAddHeaders(cmSheet, ['Check', 'Status', 'Value', 'Notes']);
    if (cm.cm01) cmSheet.addRow({ c: 'Internal Manager', s: cm.cm01.running_processes === 0 ? 'FAIL' : 'OK', v: `${cm.cm01.running_processes}/${cm.cm01.max_processes} proc`, n: `Control: ${cm.cm01.control_code}` });
    if (cm.cm02) cmSheet.addRow({ c: 'Pending Requests', s: cm.cm02.pending_requests > 200 ? 'FAIL' : cm.cm02.pending_requests > 50 ? 'WARN' : 'OK', v: String(cm.cm02.pending_requests), n: 'Phase=P Status=I' });
    if (cm.cm05) cmSheet.addRow({ c: 'Avg Runtime (24h)', s: cm.cm05.avg_runtime_secs > 3600 ? 'WARN' : 'OK', v: `${cm.cm05.avg_runtime_secs}s`, n: `${cm.cm05.completed_24h} completed` });
    if (cm.cm10) cmSheet.addRow({ c: 'Error Requests (24h)', s: cm.cm10.error_requests_24h > 20 ? 'FAIL' : cm.cm10.error_requests_24h > 5 ? 'WARN' : 'OK', v: String(cm.cm10.error_requests_24h), n: 'Status IN (E,X,D)' });
    if (cm.cm09 && cm.cm09.length) {
      cmSheet.addRow({});
      cmSheet.addRow({ c: 'Long-Running Requests (>7d)', s: '', v: '', n: '' });
      cm.cm09.forEach(r => cmSheet.addRow({ c: r.program, s: `${Math.round((r.runtime_secs || 0) / 60)} min`, v: r.start_time, n: '' }));
    }
  }

  // OPP sheet
  {
    const oppSheet = wb.addWorksheet('OPP');
    oppSheet.columns = [{ header: 'Metric', key: 'k', width: 28 }, { header: 'Value', key: 'v', width: 20 }];
    xlAddHeaders(oppSheet, ['Metric', 'Value']);
    const opp = ebs.opp_status || ebs.opp;
    if (opp) {
      oppSheet.addRow({ k: 'OPP Status', v: opp.status || '—' });
      oppSheet.addRow({ k: 'OPP Queue Depth', v: String(opp.queue_depth ?? '—') });
    }
    const servers = ebs.managed_servers || [];
    if (servers.length) {
      oppSheet.addRow({});
      oppSheet.addRow({ k: 'Managed Server', v: 'Status' });
      servers.forEach(s => oppSheet.addRow({ k: s.label || s.name, v: s.status || '—' }));
    }
  }

  // OACore is covered by OPP sheet above (same managed servers data)

  // Workflow sheet
  {
    const wfSheet = wb.addWorksheet('Workflow');
    wfSheet.columns = [{ header: 'Metric', key: 'k', width: 34 }, { header: 'Value', key: 'v', width: 20 }];
    xlAddHeaders(wfSheet, ['Metric', 'Value']);
    [
      ['WF Error Items', wf.wf02?.error_count ?? '—'],
      ['WF Deferred Queue (state=0)', wf.wf03?.deferred_ready ?? '—'],
      ['WF Notifications Pending >2h', wf.wf08?.pending_over_2h ?? '—'],
      ['WF Notifications Pending >8h', wf.wf08?.pending_over_8h ?? '—'],
    ].forEach(([k, v]) => wfSheet.addRow({ k, v: String(v) }));
  }

  // ADOP Sessions sheet
  {
    const adopSheet = wb.addWorksheet('ADOP Sessions');
    adopSheet.columns = [
      { header: 'Session ID', key: 'sid', width: 14 }, { header: 'Phase', key: 'phase', width: 14 },
      { header: 'Status', key: 'st', width: 14 }, { header: 'Started', key: 'sd', width: 18 }, { header: 'Patch', key: 'p', width: 30 },
    ];
    xlAddHeaders(adopSheet, ['Session ID', 'Phase', 'Status', 'Started', 'Patch']);
    const adop = ebs.adop_state || ebs.adop;
    if (adop && adop.sessions) {
      adop.sessions.forEach(s => adopSheet.addRow({ sid: s.session_id, phase: s.phase, st: s.status, sd: String(s.start_date || '—').substring(0, 10), p: s.patch_name || '—' }));
    }
  }

  res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  res.setHeader('Content-Disposition', `attachment; filename="${filename}"`);
  await wb.xlsx.write(res);
  res.end();
}

async function generateCombinedXLSX(data, res, filename) {
  const m = data.metrics || {};
  const s = data.scores || {};
  const ebs = m.ebs_operations || {};
  const wb = new ExcelJS.Workbook();
  wb.creator = 'TuneVault';
  wb.created = new Date();

  // Master summary
  const sum = wb.addWorksheet('Summary');
  sum.columns = [{ header: 'Metric', key: 'k', width: 30 }, { header: 'Value', key: 'v', width: 24 }];
  xlAddHeaders(sum, ['Metric', 'Value']);
  const instance = m.instance || {};
  const cm = ebs.concurrent_managers || {};
  const wf = ebs.workflow || {};
  [
    ['Connection', data.connection_name],
    ['Database', instance.db_name || '—'],
    ['Oracle Version', instance.version || '—'],
    ['EBS Version', m.ebs_version || (m.ebs_detected ? 'EBS 12.2.x' : 'Not detected')],
    ['Host', instance.host_name || data.host || '—'],
    ['Overall DB Score', data.overall_score],
    ['Storage Score', s.tablespace || 0],
    ['Performance Score', s.wait_events || 0],
    ['Memory Score', s.memory || 0],
    ['ICM Running / Target', cm.cm01 ? `${cm.cm01.running_processes} / ${cm.cm01.max_processes}` : '—'],
    ['Pending CM Requests', cm.cm02?.pending_requests ?? '—'],
    ['WF Error Items', wf.wf02?.error_count ?? '—'],
    ['Generated', new Date(data.created_at).toLocaleString()],
  ].forEach(([k, v]) => sum.addRow({ k, v: String(v ?? '—') }));

  // Re-use the individual generators' sheet-building patterns inline:
  // DB sheets
  if (m.tablespaces && m.tablespaces.length > 0) {
    const ts = wb.addWorksheet('DB - Storage');
    ts.columns = [{ header: 'Name', key: 'n', width: 24 }, { header: 'Used GB', key: 'u', width: 12 }, { header: 'Total GB', key: 't', width: 12 }, { header: 'Usage %', key: 'p', width: 10 }, { header: 'Status', key: 's', width: 12 }];
    xlAddHeaders(ts, ['Name', 'Used GB', 'Total GB', 'Usage %', 'Status']);
    m.tablespaces.forEach(t => ts.addRow({ n: t.name, u: t.used_gb, t: t.total_gb, p: t.pct_used, s: t.pct_used > 90 ? 'CRITICAL' : t.pct_used > 80 ? 'WARNING' : 'OK' }));
  }
  if (m.wait_events && m.wait_events.length > 0) {
    const we = wb.addWorksheet('DB - Performance');
    we.columns = [{ header: 'Event', key: 'e', width: 36 }, { header: 'Wait Class', key: 'wc', width: 20 }, { header: '% DB Time', key: 'p', width: 12 }, { header: 'Avg Wait ms', key: 'a', width: 14 }];
    xlAddHeaders(we, ['Event', 'Wait Class', '% DB Time', 'Avg Wait ms']);
    m.wait_events.filter(w => w.pct_db_time > 0).forEach(w => we.addRow({ e: w.event, wc: w.wait_class, p: w.pct_db_time, a: w.avg_wait_ms }));
  }
  if (m.backup_stats) {
    const bk = wb.addWorksheet('DB - Backup');
    bk.columns = [{ header: 'Check', key: 'c', width: 28 }, { header: 'Status', key: 's', width: 12 }, { header: 'Detail', key: 'd', width: 40 }];
    xlAddHeaders(bk, ['Check', 'Status', 'Detail']);
    const b = m.backup_stats;
    bk.addRow({ c: 'Overall', s: (b.overall_status || 'unknown').toUpperCase(), d: '' });
    if (b.rman_backup) bk.addRow({ c: 'Last Full Backup', s: (b.rman_backup.status || 'unknown').toUpperCase(), d: b.rman_backup.full_backup_hours_ago != null ? b.rman_backup.full_backup_hours_ago + 'h ago' : 'NONE' });
  }

  // EBS sheets
  if (m.ebs_detected) {
    const cmSheet = wb.addWorksheet('EBS - Concurrent Managers');
    cmSheet.columns = [{ header: 'Check', key: 'c', width: 28 }, { header: 'Status', key: 's', width: 12 }, { header: 'Value', key: 'v', width: 20 }, { header: 'Notes', key: 'n', width: 36 }];
    xlAddHeaders(cmSheet, ['Check', 'Status', 'Value', 'Notes']);
    if (cm.cm01) cmSheet.addRow({ c: 'Internal Manager', s: cm.cm01.running_processes === 0 ? 'FAIL' : 'OK', v: `${cm.cm01.running_processes}/${cm.cm01.max_processes}`, n: `Control: ${cm.cm01.control_code}` });
    if (cm.cm02) cmSheet.addRow({ c: 'Pending Requests', s: cm.cm02.pending_requests > 200 ? 'FAIL' : 'OK', v: String(cm.cm02.pending_requests), n: '' });
    if (cm.cm10) cmSheet.addRow({ c: 'Error Requests (24h)', s: cm.cm10.error_requests_24h > 20 ? 'FAIL' : 'OK', v: String(cm.cm10.error_requests_24h), n: '' });

    const wfSheet = wb.addWorksheet('EBS - Workflow');
    wfSheet.columns = [{ header: 'Metric', key: 'k', width: 34 }, { header: 'Value', key: 'v', width: 20 }];
    xlAddHeaders(wfSheet, ['Metric', 'Value']);
    [
      ['WF Error Items', wf.wf02?.error_count ?? '—'],
      ['WF Deferred Queue', wf.wf03?.deferred_ready ?? '—'],
      ['WF Notifications >2h', wf.wf08?.pending_over_2h ?? '—'],
    ].forEach(([k, v]) => wfSheet.addRow({ k, v: String(v) }));

    const adopSheet = wb.addWorksheet('EBS - ADOP Sessions');
    adopSheet.columns = [{ header: 'Session ID', key: 'sid', width: 14 }, { header: 'Phase', key: 'ph', width: 14 }, { header: 'Status', key: 'st', width: 14 }, { header: 'Patch', key: 'p', width: 30 }];
    xlAddHeaders(adopSheet, ['Session ID', 'Phase', 'Status', 'Patch']);
    const adop = ebs.adop_state || ebs.adop;
    if (adop && adop.sessions) {
      adop.sessions.forEach(s => adopSheet.addRow({ sid: s.session_id, ph: s.phase, st: s.status, p: s.patch_name || '—' }));
    }
  }

  res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  res.setHeader('Content-Disposition', `attachment; filename="${filename}"`);
  await wb.xlsx.write(res);
  res.end();
}

// ── Security Posture PDF generator ────────────────────────────────────────────

function generateSecurityPosturePDF(result, out) {
  const doc = new PDFDocument({
    size: 'A4',
    margins: { top: MG, bottom: MG, left: MG, right: MG },
    info: {
      Title: `Security Posture Report — ${result.connection_name || 'Scan'}`,
      Author: 'TuneVault', Subject: 'Oracle DB Security Posture Scan',
      Creator: 'TuneVault', Producer: 'TuneVault',
    },
    compress: true,
    bufferPages: true,
    autoFirstPage: true,
  });
  if (out) doc.pipe(out);

  // ── Cover page ──
  doc.rect(0, 0, PW, 100).fill(C.headerBg);
  doc.rect(0, 100, PW, 5).fill(SP_ACCENT);
  doc.rect(MG, 18, 38, 38).fillAndStroke(SP_ACCENT, SP_ACCENT);
  doc.font('Helvetica-Bold').fontSize(12).fillColor(C.textLight)
    .text('SEC', MG, 29, { width: 38, align: 'center' });
  doc.font('Helvetica-Bold').fontSize(20).fillColor(C.textLight).text('TuneVault', MG + 50, 22);
  doc.font('Helvetica').fontSize(10).fillColor(SP_ACCENT_LIGHT)
    .text('Oracle DB Security Posture Report', MG + 50, 46);

  const dateStr = new Date(result.scanned_at).toLocaleString('en-US', {
    month: 'short', day: 'numeric', year: 'numeric',
    hour: '2-digit', minute: '2-digit', timeZoneName: 'short',
  });
  doc.font('Helvetica').fontSize(9).fillColor(C.textDim)
    .text(dateStr, 0, 36, { width: PW - MG, align: 'right' });

  const score = result.score;
  const scoreColor = score === null ? C.textDim
    : score >= 90 ? C.green : score >= 75 ? '#16A34A' : score >= 50 ? C.yellow : C.red;
  const subY = 120;
  doc.font('Helvetica-Bold').fontSize(20).fillColor(C.text)
    .text(result.connection_name || 'Security Posture Scan', MG, subY);
  doc.font('Helvetica-Bold').fontSize(14).fillColor(scoreColor)
    .text(score !== null ? `${score}/100 — ${scoreLabel(score)}` : 'Score: N/A', MG, subY + 26);
  doc.font('Helvetica').fontSize(11).fillColor(C.textDim)
    .text(`${result.finding_count || 0} finding(s)  ·  ${(result.groups || []).length} groups checked`, MG, subY + 44);
  doc.moveTo(MG, subY + 64).lineTo(PW - MG, subY + 64).strokeColor(C.border).lineWidth(1).stroke();
  let y = subY + 84;

  // ── Scan summary KV ──
  y = pdfSectionTitle(doc, y, 'Scan Summary', SP_ACCENT);
  [
    ['Connection', result.connection_name || '—'],
    ['Score', score !== null ? `${score}/100 (${scoreLabel(score)})` : 'N/A'],
    ['Scanned At', new Date(result.scanned_at).toLocaleString()],
    ['Duration', result.duration_ms ? Math.round(result.duration_ms / 1000) + 's' : '—'],
    ['Total Findings', String(result.finding_count || 0)],
    ['Groups Checked', String((result.groups || []).length)],
  ].forEach(([k, v], i) => { y = pdfKVRow(doc, y, k, v, i % 2 === 1); });
  y += 14;

  // ── Group overview table ──
  y = pdfPageCheck(doc, y, 120);
  y = pdfSectionTitle(doc, y, 'Score Overview by Group', SP_ACCENT);
  y = pdfTableHeader(doc, y, ['Check Group', 'Weight', 'Score', 'Status'], [240, 70, 80, 117]);
  (result.groups || []).forEach((g, i) => {
    y = pdfPageCheck(doc, y, 22);
    const optional = SP_OPTIONAL.has(g.id);
    const wt = optional ? 'Optional' : (SP_WEIGHTS[g.id] || 0) + '%';
    const sc = g.score !== null && g.score !== undefined ? g.score + '/100' : 'N/A';
    const st = (g.status || 'unknown').toUpperCase();
    y = pdfTableRow(doc, y, [g.name || g.id, wt, sc, st], [240, 70, 80, 117], i % 2 === 1);
  });
  y += 16;

  // ── Per-group finding detail ──
  // Column widths as proportions of usable page width.
  const COLW      = [55, 130, 172, 150]; // Severity | Title | Metric Line | Remediation — must sum to CW (507)
  const MIN_ROW_H = 40;                  // minimum row height so wrapped text never clips

  for (const g of (result.groups || [])) {
    const optional = SP_OPTIONAL.has(g.id);
    const wLabel = optional ? '(Optional — not counted in score)' : `${SP_WEIGHTS[g.id] || 0}% weight`;
    y = pdfPageCheck(doc, y, 90);
    y = pdfSectionTitle(doc, y, `${g.name || g.id}  ·  ${wLabel}`, SP_ACCENT);

    if (g.error) {
      doc.font('Helvetica').fontSize(9).fillColor(C.red)
        .text('Query error: ' + g.error, MG + 6, y + 4, { width: CW - 12 });
      y += 20;
    } else if (!g.findings || !g.findings.length) {
      doc.font('Helvetica').fontSize(9).fillColor(C.green)
        .text('✓ No issues found', MG + 6, y + 4);
      y += 20;
    } else {
      y = pdfTableHeader(doc, y, ['Severity', 'Title', 'Metric Line', 'Remediation'], COLW);
      g.findings.forEach((f, fi) => {
        const sev       = (f.severity || 'info').toUpperCase();
        const title     = String(f.title || '');
        const metricRaw = String(f.metric_line || '');
        // Truncate metric to 120 chars — full value already shown in findings section above
        const metric    = metricRaw.length > 120 ? metricRaw.substring(0, 120) + '…' : metricRaw;
        const remLine   = (f.remediation || '').split('\n')[0].substring(0, 80);

        // Pre-calculate per-cell wrap heights so each row is tall enough for all content
        doc.font('Helvetica-Bold').fontSize(7);
        const sevH    = doc.heightOfString(sev, { width: COLW[0] - 8 });
        doc.font('Helvetica').fontSize(8);
        const titleH  = doc.heightOfString(title,  { width: COLW[1] - 8 });
        const metricH = doc.heightOfString(metric, { width: COLW[2] - 8 });
        doc.font('Helvetica').fontSize(7);
        const remH    = doc.heightOfString(remLine, { width: COLW[3] - 8 });
        const rowH    = Math.max(MIN_ROW_H, sevH + 12, titleH + 12, metricH + 12, remH + 12);

        y = pdfPageCheck(doc, y, rowH);
        doc.rect(MG, y, CW, rowH).fill(fi % 2 === 1 ? C.tableRowAlt : C.bg);

        const sevColorMap = { critical: C.red, warning: C.yellow, info: C.blue, advisory: C.blue };
        const sevColor = sevColorMap[f.severity] || C.textDim;

        let x = MG;
        // Severity — single word, no line-break needed
        doc.font('Helvetica-Bold').fontSize(7).fillColor(sevColor)
          .text(sev, x + 4, y + 6, { width: COLW[0] - 8, lineBreak: false });
        x += COLW[0];
        // Title — wraps within column width
        doc.font('Helvetica').fontSize(8).fillColor(C.text)
          .text(title, x + 4, y + 5, { width: COLW[1] - 8 });
        x += COLW[1];
        // Metric line — truncated to 120 chars, wraps within column width
        doc.font('Helvetica').fontSize(8).fillColor(C.textDim)
          .text(metric, x + 4, y + 5, { width: COLW[2] - 8 });
        x += COLW[2];
        // Remediation first line — wraps within column width
        doc.font('Helvetica').fontSize(7).fillColor(C.textDim)
          .text(remLine, x + 4, y + 5, { width: COLW[3] - 8 });
        y += rowH;
      });
    }
    y += 8;
  }

  // ── Page footers ──
  const range = doc.bufferedPageRange();
  for (let i = range.start; i < range.start + range.count; i++) {
    doc.switchToPage(i);
    pdfFooter(doc, result.connection_name || 'Security Posture Report');
  }
  doc.end();
  return doc;
}

// ── Security Posture XLSX generator ───────────────────────────────────────────

async function generateSecurityPostureXLSX(result, res, filename) {
  const wb = new ExcelJS.Workbook();
  wb.creator = 'TuneVault';
  wb.created = new Date();

  // Summary sheet: score meta + per-group status
  const sum = wb.addWorksheet('Summary');
  sum.columns = [
    { header: 'Metric', key: 'k', width: 36 },
    { header: 'Value',  key: 'v', width: 28 },
  ];
  xlAddHeaders(sum, ['Metric', 'Value']);
  [
    ['Connection', result.connection_name || '—'],
    ['Score', result.score !== null && result.score !== undefined ? `${result.score}/100 (${scoreLabel(result.score || 0)})` : 'N/A'],
    ['Scanned At', new Date(result.scanned_at).toLocaleString()],
    ['Duration (ms)', result.duration_ms || '—'],
    ['Total Findings', result.finding_count || 0],
  ].forEach(([k, v]) => sum.addRow({ k, v: String(v) }));

  sum.addRow({});
  sum.addRow({ k: 'CHECK GROUP', v: 'SCORE  |  WEIGHT  |  STATUS' });
  const grpHdrRow = sum.lastRow;
  grpHdrRow.eachCell(cell => { cell.font = { bold: true }; });

  for (const g of (result.groups || [])) {
    const optional = SP_OPTIONAL.has(g.id);
    const wt = optional ? 'Optional' : `${SP_WEIGHTS[g.id] || 0}%`;
    const sc = g.score !== null && g.score !== undefined ? `${g.score}/100` : 'N/A';
    const st = (g.status || 'unknown').toUpperCase();
    const row = sum.addRow({ k: (g.name || g.id) + (optional ? ' (Optional)' : ''), v: `${sc}  |  ${wt}  |  ${st}` });
    const statusFont = g.status === 'pass' ? XL_OK_FONT : g.status === 'fail' ? XL_CRIT_FONT : g.status === 'warn' ? XL_WARN_FONT : {};
    if (statusFont.color) row.getCell('v').font = statusFont;
  }

  // Findings sheet: one row per finding across all groups
  const find = wb.addWorksheet('Findings');
  find.columns = [
    { header: 'Group',       key: 'grp',    width: 30 },
    { header: 'Severity',    key: 'sev',    width: 12 },
    { header: 'Title',       key: 'title',  width: 30 },
    { header: 'Metric Line', key: 'metric', width: 40 },
    { header: 'Remediation', key: 'rem',    width: 50 },
  ];
  xlAddHeaders(find, ['Group', 'Severity', 'Title', 'Metric Line', 'Remediation']);

  for (const g of (result.groups || [])) {
    for (const f of (g.findings || [])) {
      const row = find.addRow({
        grp:    g.name || g.id,
        sev:    (f.severity || 'info').toUpperCase(),
        title:  f.title   || '',
        metric: f.metric_line || '',
        rem:    f.remediation || '',
      });
      const sevFont = f.severity === 'critical' ? XL_CRIT_FONT
        : f.severity === 'warning' ? XL_WARN_FONT
        : f.severity === 'info' || f.severity === 'advisory' ? { color: { argb: 'FF2563EB' } } : {};
      if (sevFont.color) row.getCell('sev').font = sevFont;
      row.getCell('rem').alignment = { wrapText: true };
    }
  }

  res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  res.setHeader('Content-Disposition', `attachment; filename="${filename}"`);
  await wb.xlsx.write(res);
  res.end();
}

// ── Resilience PDF generator ───────────────────────────────────────────────────

function generateResiliencePDF(result, out) {
  const doc = new PDFDocument({
    size: 'A4',
    margins: { top: MG, bottom: MG, left: MG, right: MG },
    info: {
      Title: `Resilience Report — ${result.connection_name || 'Scan'}`,
      Author: 'TuneVault', Subject: 'Oracle DB Resilience Scan',
      Creator: 'TuneVault', Producer: 'TuneVault',
    },
    compress: true,
    bufferPages: true,
    autoFirstPage: true,
  });
  if (out) doc.pipe(out);

  // ── Cover page ──
  doc.rect(0, 0, PW, 100).fill(C.headerBg);
  doc.rect(0, 100, PW, 5).fill(RES_ACCENT);
  doc.rect(MG, 18, 38, 38).fillAndStroke(RES_ACCENT, RES_ACCENT);
  doc.font('Helvetica-Bold').fontSize(12).fillColor(C.textLight)
    .text('RES', MG, 29, { width: 38, align: 'center' });
  doc.font('Helvetica-Bold').fontSize(20).fillColor(C.textLight).text('TuneVault', MG + 50, 22);
  doc.font('Helvetica').fontSize(10).fillColor(RES_ACCENT_LIGHT)
    .text('Oracle DB Resilience Report', MG + 50, 46);

  const dateStr = new Date(result.scanned_at).toLocaleString('en-US', {
    month: 'short', day: 'numeric', year: 'numeric',
    hour: '2-digit', minute: '2-digit', timeZoneName: 'short',
  });
  doc.font('Helvetica').fontSize(9).fillColor(C.textDim)
    .text(dateStr, 0, 36, { width: PW - MG, align: 'right' });

  const score = result.score;
  const scoreColor = score === null ? C.textDim
    : score >= 90 ? C.green : score >= 75 ? '#16A34A' : score >= 50 ? C.yellow : C.red;
  const subY = 120;
  doc.font('Helvetica-Bold').fontSize(20).fillColor(C.text)
    .text(result.connection_name || 'Resilience Scan', MG, subY);
  doc.font('Helvetica-Bold').fontSize(14).fillColor(scoreColor)
    .text(score !== null ? `${score}/100 — ${scoreLabel(score)}` : 'Score: N/A', MG, subY + 26);
  doc.font('Helvetica').fontSize(11).fillColor(C.textDim)
    .text(`${result.finding_count || 0} finding(s)  ·  ${(result.groups || []).length} groups checked`, MG, subY + 44);
  doc.moveTo(MG, subY + 64).lineTo(PW - MG, subY + 64).strokeColor(C.border).lineWidth(1).stroke();
  let y = subY + 84;

  // ── Scan summary KV ──
  y = pdfSectionTitle(doc, y, 'Scan Summary', RES_ACCENT);
  [
    ['Connection', result.connection_name || '—'],
    ['Score', score !== null ? `${score}/100 (${scoreLabel(score)})` : 'N/A'],
    ['Scanned At', new Date(result.scanned_at).toLocaleString()],
    ['Duration', result.duration_ms ? Math.round(result.duration_ms / 1000) + 's' : '—'],
    ['Total Findings', String(result.finding_count || 0)],
    ['Groups Checked', String((result.groups || []).length)],
  ].forEach(([k, v], i) => { y = pdfKVRow(doc, y, k, v, i % 2 === 1); });
  y += 14;

  // ── Group overview table ──
  y = pdfPageCheck(doc, y, 120);
  y = pdfSectionTitle(doc, y, 'Score Overview by Group', RES_ACCENT);
  y = pdfTableHeader(doc, y, ['Check Group', 'Weight', 'Score', 'Status'], [240, 70, 80, 117]);
  (result.groups || []).forEach((g, i) => {
    y = pdfPageCheck(doc, y, 22);
    const optional = g.optional === true || RES_OPTIONAL.has(g.id);
    const wt = optional ? 'Optional' : (RES_WEIGHTS[g.id] || 0) + '%';
    const sc = g.score !== null && g.score !== undefined ? g.score + '/100' : 'N/A';
    const st = (g.status || 'unknown').toUpperCase();
    y = pdfTableRow(doc, y, [g.name || g.id, wt, sc, st], [240, 70, 80, 117], i % 2 === 1);
  });
  y += 16;

  // ── Per-group finding detail ──
  const COLW      = [55, 130, 172, 150];
  const MIN_ROW_H = 40;

  for (const g of (result.groups || [])) {
    const optional = g.optional === true || RES_OPTIONAL.has(g.id);
    const wLabel = optional ? '(Optional — not counted in score)' : `${RES_WEIGHTS[g.id] || 0}% weight`;
    y = pdfPageCheck(doc, y, 90);
    y = pdfSectionTitle(doc, y, `${g.name || g.id}  ·  ${wLabel}`, RES_ACCENT);

    if (g.error) {
      doc.font('Helvetica').fontSize(9).fillColor(C.red)
        .text('Query error: ' + g.error, MG + 6, y + 4, { width: CW - 12 });
      y += 20;
    } else if (!g.findings || !g.findings.length) {
      doc.font('Helvetica').fontSize(9).fillColor(C.green)
        .text('✓ No issues found', MG + 6, y + 4);
      y += 20;
    } else {
      y = pdfTableHeader(doc, y, ['Severity', 'Title', 'Metric Line', 'Remediation'], COLW);
      g.findings.forEach((f, fi) => {
        const sev       = (f.severity || 'info').toUpperCase();
        const title     = String(f.title || '');
        const metricRaw = String(f.metric_line || '');
        const metric    = metricRaw.length > 120 ? metricRaw.substring(0, 120) + '…' : metricRaw;
        const remLine   = (f.remediation || '').split('\n')[0].substring(0, 80);

        doc.font('Helvetica-Bold').fontSize(7);
        const sevH    = doc.heightOfString(sev, { width: COLW[0] - 8 });
        doc.font('Helvetica').fontSize(8);
        const titleH  = doc.heightOfString(title,  { width: COLW[1] - 8 });
        const metricH = doc.heightOfString(metric, { width: COLW[2] - 8 });
        doc.font('Helvetica').fontSize(7);
        const remH    = doc.heightOfString(remLine, { width: COLW[3] - 8 });
        const rowH    = Math.max(MIN_ROW_H, sevH + 12, titleH + 12, metricH + 12, remH + 12);

        y = pdfPageCheck(doc, y, rowH);
        doc.rect(MG, y, CW, rowH).fill(fi % 2 === 1 ? C.tableRowAlt : C.bg);

        const sevColorMap = { critical: C.red, warning: C.yellow, info: C.blue, advisory: C.blue };
        const sevColor = sevColorMap[f.severity] || C.textDim;

        let x = MG;
        doc.font('Helvetica-Bold').fontSize(7).fillColor(sevColor)
          .text(sev, x + 4, y + 6, { width: COLW[0] - 8, lineBreak: false });
        x += COLW[0];
        doc.font('Helvetica').fontSize(8).fillColor(C.text)
          .text(title, x + 4, y + 5, { width: COLW[1] - 8 });
        x += COLW[1];
        doc.font('Helvetica').fontSize(8).fillColor(C.textDim)
          .text(metric, x + 4, y + 5, { width: COLW[2] - 8 });
        x += COLW[2];
        doc.font('Helvetica').fontSize(7).fillColor(C.textDim)
          .text(remLine, x + 4, y + 5, { width: COLW[3] - 8 });
        y += rowH;
      });
    }
    y += 8;
  }

  // ── Page footers ──
  const range = doc.bufferedPageRange();
  for (let i = range.start; i < range.start + range.count; i++) {
    doc.switchToPage(i);
    pdfFooter(doc, result.connection_name || 'Resilience Report');
  }
  doc.end();
  return doc;
}

// ── Resilience XLSX generator ──────────────────────────────────────────────────

async function generateResilienceXLSX(result, res, filename) {
  const wb = new ExcelJS.Workbook();
  wb.creator = 'TuneVault';
  wb.created = new Date();

  const sum = wb.addWorksheet('Summary');
  sum.columns = [
    { header: 'Metric', key: 'k', width: 36 },
    { header: 'Value',  key: 'v', width: 28 },
  ];
  xlAddHeaders(sum, ['Metric', 'Value']);
  [
    ['Connection', result.connection_name || '—'],
    ['Score', result.score !== null && result.score !== undefined ? `${result.score}/100 (${scoreLabel(result.score || 0)})` : 'N/A'],
    ['Scanned At', new Date(result.scanned_at).toLocaleString()],
    ['Duration (ms)', result.duration_ms || '—'],
    ['Total Findings', result.finding_count || 0],
  ].forEach(([k, v]) => sum.addRow({ k, v: String(v) }));

  sum.addRow({});
  sum.addRow({ k: 'CHECK GROUP', v: 'SCORE  |  WEIGHT  |  STATUS' });
  const grpHdrRow = sum.lastRow;
  grpHdrRow.eachCell(cell => { cell.font = { bold: true }; });

  for (const g of (result.groups || [])) {
    const optional = g.optional === true || RES_OPTIONAL.has(g.id);
    const wt = optional ? 'Optional' : `${RES_WEIGHTS[g.id] || 0}%`;
    const sc = g.score !== null && g.score !== undefined ? `${g.score}/100` : 'N/A';
    const st = (g.status || 'unknown').toUpperCase();
    const row = sum.addRow({ k: (g.name || g.id) + (optional ? ' (Optional)' : ''), v: `${sc}  |  ${wt}  |  ${st}` });
    const statusFont = g.status === 'pass' ? XL_OK_FONT : g.status === 'fail' ? XL_CRIT_FONT : g.status === 'warn' ? XL_WARN_FONT : {};
    if (statusFont.color) row.getCell('v').font = statusFont;
  }

  const find = wb.addWorksheet('Findings');
  find.columns = [
    { header: 'Group',       key: 'grp',    width: 30 },
    { header: 'Severity',    key: 'sev',    width: 12 },
    { header: 'Title',       key: 'title',  width: 30 },
    { header: 'Metric Line', key: 'metric', width: 40 },
    { header: 'Remediation', key: 'rem',    width: 50 },
  ];
  xlAddHeaders(find, ['Group', 'Severity', 'Title', 'Metric Line', 'Remediation']);

  for (const g of (result.groups || [])) {
    for (const f of (g.findings || [])) {
      const row = find.addRow({
        grp:    g.name || g.id,
        sev:    (f.severity || 'info').toUpperCase(),
        title:  f.title   || '',
        metric: f.metric_line || '',
        rem:    f.remediation || '',
      });
      const sevFont = f.severity === 'critical' ? XL_CRIT_FONT
        : f.severity === 'warning' ? XL_WARN_FONT
        : f.severity === 'info' || f.severity === 'advisory' ? { color: { argb: 'FF2563EB' } } : {};
      if (sevFont.color) row.getCell('sev').font = sevFont;
      row.getCell('rem').alignment = { wrapText: true };
    }
  }

  res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  res.setHeader('Content-Disposition', `attachment; filename="${filename}"`);
  await wb.xlsx.write(res);
  res.end();
}

// ── DB health check lookup ─────────────────────────────────────────────────────

async function resolveHealthCheck(connectionId) {
  // Get oracle_connections row for display name + EBS flag
  const connResult = await pool.query(
    'SELECT id, name, host FROM oracle_connections WHERE id = :p1',
    { p1: connectionId }
  );
  if (connResult.rows.length === 0) return null;
  const conn = connResult.rows[0];

  // Latest completed health check for this connection
  const hcResult = await pool.query(
    `SELECT * FROM health_checks
     WHERE connection_id = :p1 AND status = 'completed'
     ORDER BY COALESCE(completed_at, created_at) DESC FETCH FIRST 1 ROWS ONLY`,
    { p1: connectionId }
  );
  if (hcResult.rows.length === 0) return null;
  const hc = hcResult.rows[0];

  return { conn, hc };
}

// ── Routes ─────────────────────────────────────────────────────────────────────

// GET /api/reports/demo/db?format=pdf|xlsx — demo health check export (no connection required)
// MUST be defined before /:connectionId/db to avoid 'demo' being captured as a connectionId param.
router.get('/demo/db', requireAuth, async (req, res) => {
  try {
    const format = (req.query.format || 'pdf').toLowerCase();
    const hcResult = await pool.query(
      `SELECT * FROM health_checks
       WHERE user_id = :p1 AND is_demo = TRUE AND status = 'completed'
       ORDER BY COALESCE(completed_at, created_at) DESC FETCH FIRST 1 ROWS ONLY`,
      { p1: req.user.id }
    );
    if (hcResult.rows.length === 0) {
      return res.status(404).json({ error: 'No demo health check found. Run a demo first.' });
    }
    const hc = hcResult.rows[0];
    const m = hc.metrics || {};
    const { getSummaryScores } = require('../demo-data');
    const scores = getSummaryScores(m);
    const data = {
      id: hc.id,
      connection_name: 'PRODDB01 Demo',
      host: 'ora-prod-01.corp.internal',
      is_demo: true,
      overall_score: hc.overall_score || 61,
      scores,
      metrics: m,
      ai_analysis: hc.ai_analysis,
      summary_text: hc.summary_text,
      top_action: hc.top_action,
      ebs_summary: hc.ebs_summary,
      ebs_action: hc.ebs_action,
      created_at: hc.created_at,
    };
    const date = yyyymmdd(hc.completed_at || hc.created_at);
    if (format === 'xlsx') {
      return generateDbXLSX(data, res, `tunevault-demo-db-${date}.xlsx`);
    }
    res.setHeader('Content-Type', 'application/pdf');
    res.setHeader('Content-Disposition', `attachment; filename="tunevault-demo-db-${date}.pdf"`);
    generateDbPDF(data, res);
  } catch (err) {
    console.error('[reports] demo db error', err.message);
    if (!res.headersSent) res.status(500).json({ error: 'Failed to generate demo report' });
  }
});

// GET /api/reports/security-posture/:reportId?format=pdf|xlsx — download a stored security posture scan
// Defined before /:connectionId/* routes; literal prefix avoids any parameter collision.
router.get('/security-posture/:reportId', requireAuth, requireRole('junior_dba'), async (req, res) => {
  try {
    const reportId = parseInt(req.params.reportId, 10);
    const format   = (req.query.format || 'pdf').toLowerCase();

    if (!reportId) return res.status(400).json({ error: 'Invalid report ID' });

    const { rows } = await pool.query(
      `SELECT id, title, generated_data, row_counts, date_from
       FROM compliance_reports
       WHERE id = :p1 AND user_id = :p2 AND report_type = 'security_posture'`,
      { p1: reportId, p2: req.user.id }
    );
    if (!rows.length) return res.status(404).json({ error: 'Security posture scan not found' });

    const result = rows[0].generated_data;  // JSONB — already parsed by pg
    const connName = safeName(result.connection_name || 'oracle');
    const date     = yyyymmdd(result.scanned_at || rows[0].date_from);
    const base     = `tunevault-secscan-${connName}-${date}`;

    if (format === 'xlsx') {
      return generateSecurityPostureXLSX(result, res, base + '.xlsx');
    }
    res.setHeader('Content-Type', 'application/pdf');
    res.setHeader('Content-Disposition', `attachment; filename="${base}.pdf"`);
    generateSecurityPosturePDF(result, res);
  } catch (err) {
    console.error('[reports] security-posture error', err.message);
    if (!res.headersSent) res.status(500).json({ error: 'Failed to generate security posture report' });
  }
});

// GET /api/reports/resilience/:reportId?format=pdf|xlsx — download a stored resilience scan
router.get('/resilience/:reportId', requireAuth, requireRole('junior_dba'), async (req, res) => {
  try {
    const reportId = parseInt(req.params.reportId, 10);
    const format   = (req.query.format || 'pdf').toLowerCase();

    if (!reportId) return res.status(400).json({ error: 'Invalid report ID' });

    const { rows } = await pool.query(
      `SELECT id, title, generated_data, row_counts, date_from
       FROM compliance_reports
       WHERE id = :p1 AND user_id = :p2 AND report_type = 'resilience'`,
      { p1: reportId, p2: req.user.id }
    );
    if (!rows.length) return res.status(404).json({ error: 'Resilience scan not found' });

    const result   = rows[0].generated_data;
    const connName = safeName(result.connection_name || 'oracle');
    const date     = yyyymmdd(result.scanned_at || rows[0].date_from);
    const base     = `tunevault-resilience-${connName}-${date}`;

    if (format === 'xlsx') {
      return generateResilienceXLSX(result, res, base + '.xlsx');
    }
    res.setHeader('Content-Type', 'application/pdf');
    res.setHeader('Content-Disposition', `attachment; filename="${base}.pdf"`);
    generateResiliencePDF(result, res);
  } catch (err) {
    console.error('[reports] resilience error', err.message);
    if (!res.headersSent) res.status(500).json({ error: 'Failed to generate resilience report' });
  }
});

// GET /api/reports/:connectionId/db?format=pdf|xlsx — junior_dba+ can export
router.get('/:connectionId/db', requireAuth, requireRole('junior_dba'), async (req, res) => {
  try {
    const { connectionId } = req.params;
    const format = (req.query.format || 'pdf').toLowerCase();
    if (format === 'pdf') {
      // Fire-and-forget: drives onboarding "download_report".
      trackEvent({ eventName: 'report_downloaded', userId: req.user.id, properties: { scope: 'db', format: 'pdf' } });
    }

    const row = await resolveHealthCheck(connectionId);
    if (!row) return res.status(404).json({ error: 'No completed health check found for this connection' });
    const { conn, hc } = row;

    const { getSummaryScores } = require('../demo-data');
    const m = hc.metrics || {};
    const scores = m && Object.keys(m).length > 0 ? getSummaryScores(m) : {};
    const data = {
      id: hc.id, connection_name: conn.name || hc.connection_name,
      username: hc.username, host: conn.host || hc.host,
      service_name: hc.service_name, is_demo: hc.is_demo,
      overall_score: hc.overall_score, scores, metrics: m,
      ai_analysis: hc.ai_analysis, summary_text: hc.summary_text,
      top_action: hc.top_action, created_at: hc.created_at,
    };

    const instance = safeName(m.instance?.db_name || conn.name || 'oracle');
    const date = yyyymmdd(hc.completed_at || hc.created_at);

    if (format === 'xlsx') {
      return generateDbXLSX(data, res, `tunevault-db-${instance}-${date}.xlsx`);
    }
    const filename = `tunevault-db-${instance}-${date}.pdf`;
    res.setHeader('Content-Type', 'application/pdf');
    res.setHeader('Content-Disposition', `attachment; filename="${filename}"`);
    generateDbPDF(data, res);
  } catch (err) {
    console.error('[reports] db error', err.message);
    if (!res.headersSent) res.status(500).json({ error: 'Failed to generate DB report' });
  }
});

// GET /api/reports/:connectionId/ebs?format=pdf|xlsx — junior_dba+
router.get('/:connectionId/ebs', requireAuth, requireRole('junior_dba'), async (req, res) => {
  try {
    const { connectionId } = req.params;
    const format = (req.query.format || 'pdf').toLowerCase();
    if (format === 'pdf') {
      // Fire-and-forget: drives onboarding "download_report".
      trackEvent({ eventName: 'report_downloaded', userId: req.user.id, properties: { scope: 'ebs', format: 'pdf' } });
    }

    const row = await resolveHealthCheck(connectionId);
    if (!row) return res.status(404).json({ error: 'No completed health check found for this connection' });
    const { conn, hc } = row;

    const m = hc.metrics || {};
    const isAppTier = m.server_type === 'apps';
    if (!isAppTier && !m.ebs_detected) {
      return res.status(404).json({ error: 'EBS not detected on this instance' });
    }

    const { getSummaryScores } = require('../demo-data');
    const scores = m && Object.keys(m).length > 0 ? getSummaryScores(m) : {};
    const data = {
      id: hc.id, connection_name: conn.name || hc.connection_name,
      username: hc.username, host: conn.host || hc.host,
      service_name: hc.service_name, is_demo: hc.is_demo,
      overall_score: hc.overall_score, scores, metrics: m,
      ai_analysis: hc.ai_analysis, ebs_summary: hc.ebs_summary,
      ebs_action: hc.ebs_action, created_at: hc.created_at,
    };

    const instance = safeName(m.instance?.db_name || conn.name || 'oracle');
    const date = yyyymmdd(hc.completed_at || hc.created_at);

    if (isAppTier) {
      if (format === 'xlsx') {
        return generateAppTierXLSX(data, res, `tunevault-apptier-${instance}-${date}.xlsx`);
      }
      const filename = `tunevault-apptier-${instance}-${date}.pdf`;
      res.setHeader('Content-Type', 'application/pdf');
      res.setHeader('Content-Disposition', `attachment; filename="${filename}"`);
      return generateAppTierPDF(data, res);
    }

    if (format === 'xlsx') {
      return generateEbsXLSX(data, res, `tunevault-ebs-${instance}-${date}.xlsx`);
    }
    const filename = `tunevault-ebs-${instance}-${date}.pdf`;
    res.setHeader('Content-Type', 'application/pdf');
    res.setHeader('Content-Disposition', `attachment; filename="${filename}"`);
    generateEbsPDF(data, res);
  } catch (err) {
    console.error('[reports] ebs error', err.message);
    if (!res.headersSent) res.status(500).json({ error: 'Failed to generate EBS report' });
  }
});

// GET /api/reports/:connectionId/combined?format=pdf|xlsx — junior_dba+
router.get('/:connectionId/combined', requireAuth, requireRole('junior_dba'), async (req, res) => {
  try {
    const { connectionId } = req.params;
    const format = (req.query.format || 'pdf').toLowerCase();
    if (format === 'pdf') {
      // Fire-and-forget: drives onboarding "download_report".
      trackEvent({ eventName: 'report_downloaded', userId: req.user.id, properties: { scope: 'combined', format: 'pdf' } });
    }

    const row = await resolveHealthCheck(connectionId);
    if (!row) return res.status(404).json({ error: 'No completed health check found for this connection' });
    const { conn, hc } = row;

    const { getSummaryScores } = require('../demo-data');
    const m = hc.metrics || {};
    const scores = m && Object.keys(m).length > 0 ? getSummaryScores(m) : {};
    const data = {
      id: hc.id, connection_name: conn.name || hc.connection_name,
      username: hc.username, host: conn.host || hc.host,
      service_name: hc.service_name, is_demo: hc.is_demo,
      overall_score: hc.overall_score, scores, metrics: m,
      ai_analysis: hc.ai_analysis, summary_text: hc.summary_text,
      ebs_summary: hc.ebs_summary, ebs_action: hc.ebs_action,
      created_at: hc.created_at,
    };

    const instance = safeName(m.instance?.db_name || conn.name || 'oracle');
    const date = yyyymmdd(hc.completed_at || hc.created_at);

    if (format === 'xlsx') {
      return generateCombinedXLSX(data, res, `tunevault-full-${instance}-${date}.xlsx`);
    }
    const filename = `tunevault-full-${instance}-${date}.pdf`;
    res.setHeader('Content-Type', 'application/pdf');
    res.setHeader('Content-Disposition', `attachment; filename="${filename}"`);
    generateCombinedPDF(data, res);
  } catch (err) {
    console.error('[reports] combined error', err.message);
    if (!res.headersSent) res.status(500).json({ error: 'Failed to generate combined report' });
  }
});

module.exports = router;
