/**
 * routes/ebs-deep-reports.js — Deep EBS Health Reports: run + view + export.
 *
 * Owns: POST /api/ebs-deep/run, GET /report/ebs/:id, GET /api/ebs-deep/report/:id,
 *       GET /api/ebs-deep/report/:id/pdf
 * Does NOT own: EBS Live Status polling, SSH command whitelist (those stay in
 *               routes/ebs-deep.js), regular Oracle health checks.
 *
 * Mounted at: / (see server.js: app.use('/', require('./routes/ebs-deep-reports')))
 */

'use strict';

const express  = require('express');
const path     = require('path');
const OpenAI   = require('openai');

const pool              = require('../db/index');
const { requireAuth, getTokenFromRequest, verifyToken } = require('../middleware/auth');
const { getConnectionById } = require('../db/ebs-deep');
const {
  createEbsDeepReport,
  getEbsDeepReport,
  getDemoEbsDeepReport,
  updateEbsDeepReportAi,
} = require('../db/ebs-deep-reports');
const { decrypt }       = require('../crypto-utils');
const channel           = require('../services/agent-channel');

const router = express.Router();

// ─── Oracle client (lazy-loaded) ─────────────────────────────────────────────

let _oracleClient = null;
function getOracleClient() {
  if (!_oracleClient) {
    try { _oracleClient = require('../oracle-client'); } catch (e) { return null; }
  }
  return _oracleClient;
}

// ─── Proxy query helpers ──────────────────────────────────────────────────────

function inlineBinds(sql, binds) {
  if (!binds || !binds.length) return sql;
  let s = sql;
  for (let i = binds.length; i >= 1; i--) {
    const val = binds[i - 1];
    const esc = typeof val === 'number' ? String(val) : `'${String(val).replace(/'/g, "''")}'`;
    s = s.replace(new RegExp(':' + i + '\\b', 'g'), esc);
  }
  if (s.includes(':name') && binds[0] !== undefined) {
    const val = binds[0];
    const esc = typeof val === 'number' ? String(val) : `'${String(val).replace(/'/g, "''")}'`;
    s = s.replace(/:name\b/g, esc);
  }
  return s;
}

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,
    };
  };
}

async function findPairedDbConnForEbs(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 = :p1 AND user_id = :p2
       AND server_type IN ('db', 'both') AND connection_type = 'proxy'
     ORDER BY server_type FETCH FIRST 1 ROWS ONLY`,
    { p1: ebsInstanceName, p2: 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: decrypt(r.encrypted_password),
  };
}

// ─── Fix-command whitelist ────────────────────────────────────────────────────
//
// Each entry maps a finding category + condition to a list of whitelisted shell
// command strings. The AI is given the KEYS only — it cannot invent commands.
// Any command string emitted by AI that is not in this exact set is stripped
// before rendering.

const FIX_COMMANDS = {
  wf_mailer_restart: {
    label: 'Restart Workflow Mailer',
    commands: [
      '$ADMIN_SCRIPTS_HOME/adcmctl.sh stop apps/<pw>',
      '$ADMIN_SCRIPTS_HOME/adcmctl.sh start apps/<pw>',
    ],
  },
  listener_start: {
    label: 'Start Apps Listener',
    commands: ['$ADMIN_SCRIPTS_HOME/adalnctl.sh start'],
  },
  listener_stop: {
    label: 'Stop Apps Listener',
    commands: ['$ADMIN_SCRIPTS_HOME/adalnctl.sh stop'],
  },
  managed_server_start_oacore: {
    label: 'Start OACore Managed Server',
    commands: ['$ADMIN_SCRIPTS_HOME/admanagedsrvctl.sh start oacore_server1'],
  },
  managed_server_start_forms: {
    label: 'Start Forms Managed Server',
    commands: ['$ADMIN_SCRIPTS_HOME/admanagedsrvctl.sh start forms_server1'],
  },
  managed_server_start_oafm: {
    label: 'Start OAFM Managed Server',
    commands: ['$ADMIN_SCRIPTS_HOME/admanagedsrvctl.sh start oafm_server1'],
  },
  full_restart: {
    label: 'Full Application Tier Restart',
    commands: [
      '$ADMIN_SCRIPTS_HOME/adstpall.sh apps/<pw>',
      '$ADMIN_SCRIPTS_HOME/adstrtal.sh apps/<pw>',
    ],
  },
  full_stop: {
    label: 'Stop All Application Tier Services',
    commands: ['$ADMIN_SCRIPTS_HOME/adstpall.sh apps/<pw>'],
  },
  full_start: {
    label: 'Start All Application Tier Services',
    commands: ['$ADMIN_SCRIPTS_HOME/adstrtal.sh apps/<pw>'],
  },
};

// All allowed command strings as a Set for O(1) validation
const ALLOWED_COMMANDS = new Set();
for (const entry of Object.values(FIX_COMMANDS)) {
  for (const cmd of entry.commands) ALLOWED_COMMANDS.add(cmd);
}

/**
 * validateCommands — strips any command string not in the whitelist.
 * Returns an array of {label, commands} with only safe commands.
 * Logs violations for audit.
 */
function validateCommands(suggestedKeys) {
  if (!Array.isArray(suggestedKeys)) return [];
  const result = [];
  for (const key of suggestedKeys) {
    if (typeof key !== 'string') {
      console.log('[ebs-deep-reports] FIX_CMD_VIOLATION: non-string key rejected', { key });
      continue;
    }
    // Treat key as a FIX_COMMANDS key (preferred) or a raw command string (fallback)
    if (FIX_COMMANDS[key]) {
      result.push({ label: FIX_COMMANDS[key].label, commands: FIX_COMMANDS[key].commands });
    } else if (ALLOWED_COMMANDS.has(key)) {
      // Raw command string — wrap it
      result.push({ label: key, commands: [key] });
    } else {
      console.log('[ebs-deep-reports] FIX_CMD_VIOLATION: unlisted command rejected', { key });
    }
  }
  return result;
}

// ─── EBS Deep Oracle queries ──────────────────────────────────────────────────

/**
 * runEbsDeepQueries — execute all EBS-specific diagnostic queries against the
 * Oracle connection and return a structured findings object.
 *
 * Returns an object with sections: concurrent_processing, workflow_mailer,
 * managed_servers, listener, adop_state, error_log_tail.
 */
async function runEbsDeepQueries(connParams, queryFn) {
  let connection;
  let safeExec;

  if (queryFn) {
    safeExec = async function(sql, binds) {
      try {
        const result = await queryFn(inlineBinds(sql, binds));
        const cols = result.columns || [];
        return { rows: result.rows.map(function(row) { return cols.map(function(c) { return row[c]; }); }) };
      } catch (e) { return null; }
    };
  } else {
    const oracledb = require('oracledb');
    const connectString = `${connParams.host}:${connParams.port || 1521}/${connParams.serviceName}`;
    connection = await oracledb.getConnection({
      user: connParams.username,
      password: connParams.password,
      connectString,
      connectTimeout: 30,
    });
    safeExec = async function(sql, binds = []) {
      try {
        return await connection.execute(sql, binds, { outFormat: oracledb.OUT_FORMAT_ARRAY });
      } catch (e) {
        return null;
      }
    };
  }

  try {
    // ── 1. Concurrent Processing ──────────────────────────────────────────
    const cmStatusR = await safeExec(
      `SELECT running_processes, target_processes, max_processes
       FROM apps.fnd_concurrent_queues_vl
       WHERE concurrent_queue_name = 'FNDICM' AND enabled_flag = 'Y'`
    );
    const pendingR = await safeExec(
      `SELECT COUNT(*) FROM apps.fnd_concurrent_requests WHERE phase_code = 'P' AND status_code = 'I'`
    );
    const errorR = await safeExec(
      `SELECT COUNT(*) FROM apps.fnd_concurrent_requests
       WHERE status_code IN ('E','X','D') AND actual_completion_date > SYSDATE - 1`
    );
    const longRunR = await safeExec(
      `SELECT request_id, concurrent_program_name,
              ROUND((SYSDATE - actual_start_date) * 24 * 60) AS run_mins
       FROM apps.fnd_concurrent_requests
       WHERE phase_code = 'R' AND actual_start_date < SYSDATE - 1/24
       ORDER BY run_mins DESC
       FETCH FIRST 5 ROWS ONLY`
    );

    const cmRow = cmStatusR?.rows?.[0];
    const concurrent_processing = {
      icm_running:       Number(cmRow?.[0] || 0),
      icm_target:        Number(cmRow?.[1] || cmRow?.[2] || 0),
      pending_requests:  Number(pendingR?.rows?.[0]?.[0] || 0),
      error_requests_24h: Number(errorR?.rows?.[0]?.[0] || 0),
      long_running: (longRunR?.rows || []).map(r => ({
        request_id: r[0],
        program: r[1],
        run_mins: Number(r[2] || 0),
      })),
    };

    // ── 2. Workflow Mailer ────────────────────────────────────────────────
    const wfStatusR = await safeExec(
      `SELECT component_status FROM apps.fnd_svc_components
       WHERE component_type LIKE 'WF_MAILER%' AND ROWNUM = 1`
    );
    const stuckR = await safeExec(
      `SELECT COUNT(*) FROM apps.wf_notifications
       WHERE mail_status = 'MAIL' AND status = 'OPEN' AND begin_date < SYSDATE - 1/24`
    );
    const wfErrR = await safeExec(`SELECT COUNT(*) FROM apps.wf_error`);
    const pendOver2hR = await safeExec(
      `SELECT COUNT(*) FROM apps.wf_notifications
       WHERE mail_status = 'MAIL' AND status = 'OPEN' AND begin_date < SYSDATE - 2/24`
    );

    const workflow_mailer = {
      status:          String(wfStatusR?.rows?.[0]?.[0] || 'UNKNOWN').toUpperCase(),
      stuck_count:     Number(stuckR?.rows?.[0]?.[0] || 0),
      error_count:     Number(wfErrR?.rows?.[0]?.[0] || 0),
      pending_over_2h: Number(pendOver2hR?.rows?.[0]?.[0] || 0),
    };

    // ── 3. Managed Servers ────────────────────────────────────────────────
    const serverDefs = [
      { name: 'oacore_server1', label: 'OACore' },
      { name: 'forms_server1',  label: 'Forms'  },
      { name: 'oafm_server1',   label: 'OAFM'   },
    ];
    const managed_servers = [];
    for (const srv of serverDefs) {
      const srvR = await safeExec(
        `SELECT component_status FROM apps.fnd_svc_components
         WHERE LOWER(component_name) LIKE :name AND ROWNUM = 1`,
        [`%${srv.name.split('_')[0].toLowerCase()}%`]
      );
      const rawStatus = String(srvR?.rows?.[0]?.[0] || 'UNKNOWN').toUpperCase();
      managed_servers.push({ name: srv.name, label: srv.label, status: rawStatus });
    }

    // OPP (Output Post Processor)
    const oppR = await safeExec(
      `SELECT component_status FROM apps.fnd_svc_components
       WHERE component_name LIKE '%Output Post%' AND ROWNUM = 1`
    );
    const oppQR = await safeExec(
      `SELECT COUNT(*) FROM apps.fnd_concurrent_requests
       WHERE phase_code = 'P' AND concurrent_program_name = 'FNDCPOPP'`
    );
    const opp = {
      status:      String(oppR?.rows?.[0]?.[0] || 'UNKNOWN').toUpperCase(),
      queue_depth: Number(oppQR?.rows?.[0]?.[0] || 0),
    };

    // ── 4. Listener ───────────────────────────────────────────────────────
    const lisR = await safeExec(
      `SELECT metval_clob FROM apps.fnd_oam_metval
       WHERE metname = 'APPS_JDBC_URL' AND ROWNUM = 1`
    );
    let listener = { status: 'UNKNOWN', port: null };
    if (lisR?.rows?.[0]) {
      const url = String(lisR.rows[0][0] || '');
      const portMatch = url.match(/:(\d+)\//);
      listener = { status: 'UP', port: portMatch ? Number(portMatch[1]) : 1521 };
    }

    // ── 5. ADOP State ─────────────────────────────────────────────────────
    const adopR = await safeExec(
      `SELECT session_id, phase, status, start_date, patch_name
       FROM apps.ad_adop_sessions
       ORDER BY start_date DESC
       FETCH FIRST 3 ROWS ONLY`
    );
    const adop_state = {
      sessions: (adopR?.rows || []).map(r => ({
        session_id: r[0],
        phase:  r[1],
        status: r[2],
        start_date: r[3],
        patch_name: r[4],
      })),
    };

    // ── 6. Error Log Tail ─────────────────────────────────────────────────
    const logR = await safeExec(
      `SELECT log_sequence, module, message_text, timestamp
       FROM apps.fnd_log_messages
       WHERE log_level = 6
         AND timestamp > SYSDATE - 1/24
       ORDER BY timestamp DESC
       FETCH FIRST 20 ROWS ONLY`
    );
    const error_log_tail = {
      entries: (logR?.rows || []).map(r => ({
        seq:     r[0],
        module:  r[1],
        message: String(r[2] || '').substring(0, 300),
        ts:      r[3],
      })),
    };

    return {
      fetched_at: new Date().toISOString(),
      concurrent_processing,
      workflow_mailer,
      managed_servers,
      opp,
      listener,
      adop_state,
      error_log_tail,
    };
  } finally {
    if (connection) {
      try { await connection.close(); } catch (e) { /* ignore */ }
    }
  }
}

// ─── Demo fixture ─────────────────────────────────────────────────────────────

function getDemoFindings() {
  return {
    fetched_at: new Date().toISOString(),
    is_demo: true,
    concurrent_processing: {
      icm_running: 8,
      icm_target: 10,
      pending_requests: 142,
      error_requests_24h: 37,
      long_running: [
        { request_id: 10023441, program: 'RAXTRX', run_mins: 187 },
        { request_id: 10023389, program: 'GLPPOS', run_mins: 143 },
      ],
    },
    workflow_mailer: {
      status: 'STOPPED',
      stuck_count: 1044,
      error_count: 147,
      pending_over_2h: 891,
    },
    managed_servers: [
      { name: 'oacore_server1', label: 'OACore', status: 'RUNNING' },
      { name: 'forms_server1',  label: 'Forms',  status: 'RUNNING' },
      { name: 'oafm_server1',   label: 'OAFM',   status: 'FAILED'  },
    ],
    opp: { status: 'RUNNING', queue_depth: 0 },
    listener: { status: 'UP', port: 1521 },
    adop_state: {
      sessions: [
        { session_id: 44, phase: 'apply', status: 'completed', start_date: '2026-04-28', patch_name: '35293444' },
        { session_id: 43, phase: 'apply', status: 'completed', start_date: '2026-03-15', patch_name: '34760988' },
      ],
    },
    error_log_tail: {
      entries: [
        { seq: 90001, module: 'WF_MAILER', message: 'Connection refused to SMTP host mx1.corp.local:25', ts: new Date(Date.now() - 300000).toISOString() },
        { seq: 89998, module: 'FNDOPP',    message: 'OPP timed out waiting for concurrent request 10023441', ts: new Date(Date.now() - 600000).toISOString() },
        { seq: 89994, module: 'WF_MAILER', message: 'Maximum retry attempts exceeded for notification 77021', ts: new Date(Date.now() - 900000).toISOString() },
      ],
    },
  };
}

// ─── AI analysis ─────────────────────────────────────────────────────────────

/**
 * generateAiAnalysis — produce per-section root-cause one-liners and suggested
 * fix command keys from the whitelist. Returns structured JSON.
 *
 * Strict: the AI is told to ONLY suggest keys from FIX_COMMANDS. The output is
 * then validated by validateCommands() before being stored or rendered.
 */
async function generateAiAnalysis(findings) {
  let openai;
  try {
    openai = new OpenAI();
  } catch (e) {
    return null;
  }

  const fixKeys = Object.keys(FIX_COMMANDS).join(', ');
  const prompt = `You are an Oracle E-Business Suite 12.2 DBA assistant. Analyze these EBS health findings and return a JSON object.

FINDINGS:
${JSON.stringify(findings, null, 2)}

Return JSON ONLY (no markdown) with this exact structure:
{
  "concurrent_processing": {
    "status": "ok|warn|crit",
    "summary": "one sentence",
    "fix_keys": []
  },
  "workflow_mailer": {
    "status": "ok|warn|crit",
    "summary": "one sentence",
    "fix_keys": []
  },
  "managed_servers": {
    "status": "ok|warn|crit",
    "summary": "one sentence",
    "fix_keys": []
  },
  "listener": {
    "status": "ok|warn|crit",
    "summary": "one sentence",
    "fix_keys": []
  },
  "adop_state": {
    "status": "ok|warn|crit",
    "summary": "one sentence",
    "fix_keys": []
  },
  "error_log_tail": {
    "status": "ok|warn|crit",
    "summary": "one sentence",
    "fix_keys": []
  },
  "overall_summary": "2-3 sentences describing the most critical issues"
}

Rules:
- fix_keys MUST only contain values from this exact list: ${fixKeys}
- Use "crit" only for service-down or data-loss-risk conditions
- Do not invent commands or scripts
- Keep summaries under 15 words`;

  try {
    const resp = await openai.chat.completions.create({
      model: 'gpt-4o-mini',
      messages: [{ role: 'user', content: prompt }],
      temperature: 0.2,
      max_tokens: 800,
    });
    const text = resp.choices?.[0]?.message?.content || '';
    const json = JSON.parse(text.replace(/```json\n?/g, '').replace(/```\n?/g, '').trim());
    // Validate fix_keys in each section
    for (const section of Object.values(json)) {
      if (section && Array.isArray(section.fix_keys)) {
        section.fix_keys = validateCommands(section.fix_keys);
      }
    }
    return json;
  } catch (e) {
    console.log('[ebs-deep-reports] AI analysis failed:', e.message);
    return null;
  }
}

// ─── ROUTES ──────────────────────────────────────────────────────────────────

/**
 * POST /api/ebs-deep/run — run a Deep EBS check and create a report.
 * Body: { connection_id: number }
 * Returns: { report_id: number, redirect: '/report/ebs/<id>' }
 */
router.post('/api/ebs-deep/run', requireAuth, async (req, res) => {
  try {
    const { connection_id } = req.body;
    if (!connection_id) {
      return res.status(400).json({ error: 'connection_id is required' });
    }

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

    let findings;

    if (conn.connection_type === 'proxy') {
      let targetConnId = conn.id;
      let sqlCreds;
      if (conn.server_type === 'apps') {
        const paired = await findPairedDbConnForEbs(conn.ebs_instance_name, req.user.id);
        if (!paired) return res.status(503).json({ error: 'No paired DB-tier proxy connection found. Set ebs_instance_name on both connections.' });
        targetConnId = paired.id;
        sqlCreds = { service_name: paired.serviceName, username: paired.username, password: paired.password };
      } else {
        sqlCreds = { service_name: conn.service_name, username: conn.username, password: decrypt(conn.encrypted_password) };
      }
      const agentOnline = await Promise.race([
        channel.isAgentConnected(targetConnId),
        new Promise(r => setTimeout(() => r(false), 5000)),
      ]);
      if (!agentOnline) return res.status(503).json({ error: 'Agent is not connected. Wait for agent check-in and retry.' });
      const queryFn = makeProxyQueryFn(targetConnId, sqlCreds);
      const connParams = { host: conn.host || '', port: conn.port || 1521, serviceName: conn.service_name };
      findings = await runEbsDeepQueries(connParams, queryFn);
    } else {
      const oracle = getOracleClient();
      if (!oracle) {
        return res.status(503).json({ error: 'Oracle client unavailable on this server' });
      }
      const connParams = {
        host:        conn.host,
        port:        conn.port || 1521,
        serviceName: conn.service_name,
        username:    conn.username,
        password:    decrypt(conn.encrypted_password),
      };
      findings = await runEbsDeepQueries(connParams);
    }

    // Create the report row immediately so we can redirect the user
    const row = await createEbsDeepReport({
      userId:         req.user.id,
      connectionId:   conn.id,
      connectionName: conn.name,
      findingsJson:   findings,
      aiAnalysis:     null,
      isDemo:         !!findings.is_demo,
    });

    // Kick off AI analysis in background (don't block the redirect)
    setImmediate(async () => {
      try {
        const ai = await generateAiAnalysis(findings);
        if (ai) await updateEbsDeepReportAi(row.id, JSON.stringify(ai));
      } catch (e) {
        console.log('[ebs-deep-reports] background AI error for report', row.id, e.message);
      }
    });

    res.json({ report_id: row.id, redirect: `/report/ebs/${row.id}` });
  } catch (err) {
    console.error('[ebs-deep-reports] Run error:', err);
    res.status(500).json({ error: 'Deep EBS check failed', detail: err.message });
  }
});

/**
 * GET /report/ebs/demo — static sample EBS report (no auth, no API calls).
 * Redirects to /sample-ebs-report which serves a fully static page.
 */
router.get('/report/ebs/demo', (req, res) => {
  res.redirect(301, '/sample-ebs-report');
});

/**
 * GET /report/ebs/:id — serve the Deep EBS report page.
 * The page fetches its own data via /api/ebs-deep/report/:id.
 */
router.get('/report/ebs/:id', requireAuth, (req, res) => {
  res.sendFile(path.join(__dirname, '..', 'public', 'report-ebs.html'));
});

/**
 * GET /api/ebs-deep/report/:id — return report JSON.
 * Authenticated; also serves demo report to anyone if id === 'demo'.
 */
router.get('/api/ebs-deep/report/:id', async (req, res) => {
  try {
    const { id } = req.params;

    // Demo mode — return in-memory fixture, no DB row needed
    if (id === 'demo') {
      const findings = getDemoFindings();
      const ai = await generateAiAnalysis(findings).catch(() => null);
      return res.json({
        id: 'demo',
        is_demo: true,
        connection_name: 'Demo EBS Instance (EBS 12.2.12)',
        created_at: new Date().toISOString(),
        findings_json: findings,
        ai_analysis: ai,
      });
    }

    // Auth check for non-demo reports
    const payload = verifyToken(getTokenFromRequest(req));
    if (!payload) return res.status(401).json({ error: 'Authentication required' });

    const userResult = await pool.query('SELECT id FROM users WHERE id = :p1', { p1: payload.userId });
    if (userResult.rows.length === 0) return res.status(401).json({ error: 'User not found' });
    const userId = userResult.rows[0].id;

    const report = await getEbsDeepReport(Number(id), userId);
    if (!report) return res.status(404).json({ error: 'Report not found' });

    let aiAnalysis = null;
    try { aiAnalysis = report.ai_analysis ? JSON.parse(report.ai_analysis) : null; } catch (e) { /* bad JSON */ }

    res.json({
      id: report.id,
      is_demo: report.is_demo,
      connection_name: report.connection_name,
      connection_id: report.connection_id || null,
      created_at: report.created_at,
      findings_json: typeof report.findings_json === 'string' ? JSON.parse(report.findings_json) : report.findings_json,
      ai_analysis: aiAnalysis,
    });
  } catch (err) {
    console.error('[ebs-deep-reports] Report fetch error:', err);
    res.status(500).json({ error: 'Failed to fetch report' });
  }
});

/**
 * GET /api/ebs-deep/report/:id/pdf — stream a PDF of the Deep EBS report.
 */
router.get('/api/ebs-deep/report/:id/pdf', async (req, res) => {
  try {
    const { id } = req.params;
    let reportData;

    if (id === 'demo') {
      reportData = {
        id: 'demo',
        is_demo: true,
        connection_name: 'Demo EBS Instance (EBS 12.2.12)',
        created_at: new Date().toISOString(),
        findings_json: getDemoFindings(),
        ai_analysis: null,
      };
    } else {
      const payload = verifyToken(getTokenFromRequest(req));
      if (!payload) return res.status(401).json({ error: 'Authentication required' });
      const userResult = await pool.query('SELECT id FROM users WHERE id = :p1', { p1: payload.userId });
      if (userResult.rows.length === 0) return res.status(401).json({ error: 'User not found' });

      const report = await getEbsDeepReport(Number(id), userResult.rows[0].id);
      if (!report) return res.status(404).json({ error: 'Report not found' });

      let aiAnalysis = null;
      try { aiAnalysis = report.ai_analysis ? JSON.parse(report.ai_analysis) : null; } catch (e) { /* bad JSON */ }

      reportData = {
        id: report.id,
        is_demo: report.is_demo,
        connection_name: report.connection_name,
        created_at: report.created_at,
        findings_json: typeof report.findings_json === 'string' ? JSON.parse(report.findings_json) : report.findings_json,
        ai_analysis: aiAnalysis,
      };
    }

    const { generateEbsDeepPDF } = require('../pdf-generator-ebs');
    const doc = generateEbsDeepPDF(reportData);

    const filename = `deep-ebs-report-${String(id).replace(/[^a-z0-9-]/gi, '')}.pdf`;
    res.setHeader('Content-Type', 'application/pdf');
    res.setHeader('Content-Disposition', `attachment; filename="${filename}"`);
    doc.pipe(res);
    doc.end();
  } catch (err) {
    console.error('[ebs-deep-reports] PDF error:', err);
    res.status(500).json({ error: 'PDF generation failed' });
  }
});

module.exports = router;
