'use strict';

/**
 * routes/mcp.js — TuneVault MCP (Model Context Protocol) server.
 *
 * Implements the MCP Streamable HTTP transport (stateless — each POST is independent).
 * No SDK required: plain JSON-RPC 2.0 over HTTP.
 *
 * Auth: Bearer <api-key>  (generate at /settings/api, Team plan+)
 *
 * Routes (mounted at /mcp):
 *   GET  /mcp         — discovery JSON (no auth)
 *   GET  /mcp/setup   — human-readable setup guide (canonical)
 *   POST /mcp         — JSON-RPC 2.0 endpoint
 *
 * Tools (15):
 *   list_connections, run_health_check, get_latest_health_check,
 *   security_posture_scan, list_tuneops_tickets, run_sql_query,
 *   get_vulnerability_exposure, get_resilience_status,
 *   create_tuneops_ticket, get_fleet_status, get_adop_status,
 *   run_ebs_health_check, get_security_posture, get_patch_advisor,
 *   list_health_check_history
 */

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

const pool                = require('../db/index');
const { getUserByApiKey } = require('../db/api-keys');
const { decrypt }         = require('../crypto-utils');
const channel             = require('../services/agent-channel');
const dbTuneops           = require('../db/tuneops-tickets');

// ── Constants ────────────────────────────────────────────────────────────────

const MCP_VERSION    = '2024-11-05';
const SERVER_VERSION = '1.0.0';

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

// ── Tool definitions ─────────────────────────────────────────────────────────

const TOOLS = [
  {
    name: 'list_connections',
    description: 'List all Oracle database connections available in TuneVault for the authenticated user.',
    inputSchema: { type: 'object', properties: {}, required: [] },
  },
  {
    name: 'run_health_check',
    description: 'Return a link to trigger a health check for a connection and instructions on how to retrieve results once complete.',
    inputSchema: {
      type: 'object',
      properties: {
        connection_id: { type: 'number', description: 'Numeric ID of the Oracle connection (from list_connections).' },
      },
      required: ['connection_id'],
    },
  },
  {
    name: 'get_latest_health_check',
    description: 'Return the most recent health check result for a connection — score, summary, top recommended action, and critical/warning findings.',
    inputSchema: {
      type: 'object',
      properties: {
        connection_id: { type: 'number', description: 'Numeric ID of the Oracle connection.' },
      },
      required: ['connection_id'],
    },
  },
  {
    name: 'security_posture_scan',
    description: 'Return the latest Security Posture scan result for a connection. Covers default passwords, privilege escalation, public grants, init params, audit trail, encryption, DB links, and patch currency.',
    inputSchema: {
      type: 'object',
      properties: {
        connection_id: { type: 'number', description: 'Numeric ID of the Oracle connection.' },
      },
      required: ['connection_id'],
    },
  },
  {
    name: 'list_tuneops_tickets',
    description: 'List TuneOps tickets — auto-generated DBA issues and recommendations for the user\'s Oracle connections.',
    inputSchema: {
      type: 'object',
      properties: {
        connection_id: { type: 'number', description: 'Filter by connection ID (optional).' },
        status:        { type: 'string', description: 'Filter by status: OPEN, ACKNOWLEDGED, EXECUTING, RESOLVED, REOPENED, DEFERRED.' },
        severity:      { type: 'string', description: 'Filter by severity: critical, warning, info.' },
        limit:         { type: 'number', description: 'Max tickets to return (default 20, max 100).' },
      },
      required: [],
    },
  },
  {
    name: 'run_sql_query',
    description: 'Execute a read-only SELECT or WITH query against an Oracle database via the proxy agent. Only SELECT/WITH permitted. Returns up to 200 rows as a markdown table. username and password are optional — if omitted, stored connection credentials are used.',
    inputSchema: {
      type: 'object',
      properties: {
        connection_id: { type: 'number', description: 'Numeric ID of the Oracle connection (must use proxy agent, not direct TCP).' },
        sql:           { type: 'string', description: 'The SELECT or WITH SQL query to execute.' },
        username:      { type: 'string', description: 'Oracle username. Omit to use stored connection credentials.' },
        password:      { type: 'string', description: 'Oracle password. Omit to use stored connection credentials.' },
      },
      required: ['connection_id', 'sql'],
    },
  },
  {
    name: 'get_vulnerability_exposure',
    description: 'Run a live vulnerability scan — detects unpatched CVEs by comparing the DB version and applied SQL patches against the TuneVault CVE index. Requires a proxy-agent connection.',
    inputSchema: {
      type: 'object',
      properties: {
        connection_id: { type: 'number', description: 'Numeric ID of the Oracle connection (must use proxy agent).' },
      },
      required: ['connection_id'],
    },
  },
  {
    name: 'get_resilience_status',
    description: 'Return the latest Resilience scan for a connection: RMAN backup currency, archivelog mode, Data Guard, FRA usage, and redo log health.',
    inputSchema: {
      type: 'object',
      properties: {
        connection_id: { type: 'number', description: 'Numeric ID of the Oracle connection.' },
      },
      required: ['connection_id'],
    },
  },
  {
    name: 'create_tuneops_ticket',
    description: 'Create a new TuneOps ticket manually — use this to log a DBA task, issue, or observation that needs tracking. Returns the ticket number (TO-XXXX) and details.',
    inputSchema: {
      type: 'object',
      properties: {
        title:         { type: 'string',  description: 'Short title for the ticket (required).' },
        description:   { type: 'string',  description: 'Detailed description of the issue or task.' },
        severity:      { type: 'string',  description: 'Severity level: critical, warning, or info (default: warning).' },
        connection_id: { type: 'number',  description: 'Oracle connection this ticket relates to (optional).' },
      },
      required: ['title'],
    },
  },
  {
    name: 'get_fleet_status',
    description: 'Return a live fleet overview — all Oracle connections for the user with online/offline status, latest health scores, agent versions, and last-seen timestamps.',
    inputSchema: { type: 'object', properties: {}, required: [] },
  },
  {
    name: 'get_adop_status',
    description: 'Query live ADOP (AD Online Patching) session status for an EBS database connection. Returns active patch cycles, current phase (prepare/apply/finalize/cutover/cleanup), session ID, and node name. Requires APPS schema access — pass username/password to override stored credentials.',
    inputSchema: {
      type: 'object',
      properties: {
        connection_id: { type: 'number', description: 'Numeric ID of the Oracle DB connection (proxy-agent, must have APPS schema access).' },
        username:      { type: 'string', description: 'Oracle username (e.g. apps). Omit to use stored connection credentials.' },
        password:      { type: 'string', description: 'Oracle password. Omit to use stored connection credentials.' },
      },
      required: ['connection_id'],
    },
  },
  {
    name: 'run_ebs_health_check',
    description: 'Run a live EBS application-tier health check on an app-tier connection — calls the agent\'s /api/ebs-app-healthcheck endpoint. Returns findings, score, critical/warning counts, and top issues. connection_id must be an apps or both server_type connection.',
    inputSchema: {
      type: 'object',
      properties: {
        connection_id: { type: 'number', description: 'Numeric ID of the EBS app-tier Oracle connection (server_type = apps or both).' },
      },
      required: ['connection_id'],
    },
  },
  {
    name: 'get_security_posture',
    description: 'Return a concise structured summary of the latest Security Posture scan from the database. Shows score, scan date, and critical/warning group breakdowns. Does NOT re-run the scan — reads saved results only.',
    inputSchema: {
      type: 'object',
      properties: {
        connection_id: { type: 'number', description: 'Numeric ID of the Oracle connection.' },
      },
      required: ['connection_id'],
    },
  },
  {
    name: 'get_patch_advisor',
    description: 'Return current Oracle patch level and top 5 recommended patches for a DB connection. Checks advisor_findings for cached data; falls back to live dba_registry_sqlpatch query via the proxy agent. Requires a proxy-agent connection.',
    inputSchema: {
      type: 'object',
      properties: {
        connection_id: { type: 'number', description: 'Numeric ID of the Oracle DB connection (proxy-agent).' },
      },
      required: ['connection_id'],
    },
  },
  {
    name: 'list_health_check_history',
    description: 'List the last N health check runs for a connection — score, status, timestamp, and critical/warning finding counts per run.',
    inputSchema: {
      type: 'object',
      properties: {
        connection_id: { type: 'number', description: 'Numeric ID of the Oracle connection.' },
        limit:         { type: 'number', description: 'Number of runs to return (default 10, max 30).' },
      },
      required: ['connection_id'],
    },
  },
  {
    name: 'list_ebs_patches',
    description: 'List EBS patches applied via ADOP — queries AD_ADOP_SESSION_PATCHES for bug numbers, status, session type, fs_clone status, and start/end dates. Works with app-tier or DB-tier connections. Pass username=apps + password to query APPS schema.',
    inputSchema: {
      type: 'object',
      properties: {
        connection_id: { type: 'number',  description: 'Numeric ID of the Oracle connection (app-tier or DB-tier, proxy-agent).' },
        username:      { type: 'string',  description: 'Oracle username (e.g. apps). Omit to use stored credentials.' },
        password:      { type: 'string',  description: 'Oracle password.' },
        limit:         { type: 'number',  description: 'Number of patch rows to return (default 20, max 100).' },
        session_id:    { type: 'number',  description: 'Filter to a specific ADOP session ID (optional).' },
      },
      required: ['connection_id'],
    },
  },
  {
    name: 'get_concurrent_manager_status',
    description: 'Query FND_CONCURRENT_QUEUES_VL for Concurrent Manager status — running/max processes, control code, manager type. App-tier connections auto-pair to the DB agent. Pass username=apps + password for APPS schema access.',
    inputSchema: {
      type: 'object',
      properties: {
        connection_id: { type: 'number', description: 'Numeric ID of the Oracle connection (app-tier or DB-tier, proxy-agent).' },
        username:      { type: 'string', description: 'Oracle username (e.g. apps). Omit to use stored credentials.' },
        password:      { type: 'string', description: 'Oracle password.' },
      },
      required: ['connection_id'],
    },
  },
  {
    name: 'get_tablespace_usage',
    description: 'Query DBA_TABLESPACE_USAGE_METRICS + DBA_TABLESPACES for used_mb, total_mb, pct_used. Flags tablespaces >85% as warning and >95% as critical. DB-tier connection required.',
    inputSchema: {
      type: 'object',
      properties: {
        connection_id: { type: 'number', description: 'Numeric ID of the Oracle DB connection (proxy-agent).' },
      },
      required: ['connection_id'],
    },
  },
  {
    name: 'get_blocking_sessions',
    description: 'Query V$SESSION for blocking and blocked sessions. Returns blocker details (SID, username, SQL, wait event) and blocked session count. Returns a clean message when no blocking sessions exist. DB-tier connection required.',
    inputSchema: {
      type: 'object',
      properties: {
        connection_id: { type: 'number', description: 'Numeric ID of the Oracle DB connection (proxy-agent).' },
      },
      required: ['connection_id'],
    },
  },
  {
    name: 'get_workflow_errors',
    description: 'Query WF_ITEM_ACTIVITY_STATUSES for Workflow items in ERROR status within the last N hours. Returns item_type, item_key, activity, error_name, error_message. App-tier connections auto-pair to DB agent. Pass username=apps + password.',
    inputSchema: {
      type: 'object',
      properties: {
        connection_id: { type: 'number', description: 'Numeric ID of the Oracle connection (app-tier or DB-tier, proxy-agent).' },
        hours:         { type: 'number', description: 'Look back this many hours (default 24, max 168).' },
        username:      { type: 'string', description: 'Oracle username (e.g. apps). Omit to use stored credentials.' },
        password:      { type: 'string', description: 'Oracle password.' },
      },
      required: ['connection_id'],
    },
  },
];

// ── JSON-RPC wire helpers ────────────────────────────────────────────────────

function rpcOk(id, result)        { return { jsonrpc: '2.0', id, result }; }
function rpcErr(id, code, message){ return { jsonrpc: '2.0', id, error: { code, message } }; }
function text(str)                { return { content: [{ type: 'text', text: String(str) }] }; }

// ── Tool: list_connections ────────────────────────────────────────────────────

async function toolListConnections(_args, user) {
  const { rows } = await pool.query(
    `SELECT id, name, host, port, service_name, server_type, connection_type,
            proxy_version, connectivity_mode, ebs_instance_name
     FROM oracle_connections
     WHERE user_id = $1
     ORDER BY name`,
    [user.id]
  );
  if (!rows.length) return text('No Oracle connections found for this user.');

  const lines = ['# Oracle Connections\n'];
  rows.forEach(c => {
    lines.push(`## ${c.name} (ID: ${c.id})`);
    lines.push(`- Host: ${c.host || '(proxy-only — no direct host)'}`);
    if (c.port)             lines.push(`- Port: ${c.port}`);
    if (c.service_name)     lines.push(`- Service: ${c.service_name}`);
    lines.push(`- Type: ${c.server_type || 'db'} / ${c.connection_type || 'direct'}`);
    if (c.connectivity_mode) lines.push(`- Connectivity: ${c.connectivity_mode}`);
    if (c.ebs_instance_name) lines.push(`- EBS Instance: ${c.ebs_instance_name}`);
    if (c.proxy_version)    lines.push(`- Agent Version: ${c.proxy_version}`);
    lines.push('');
  });
  return text(lines.join('\n'));
}

// ── Tool: run_health_check ────────────────────────────────────────────────────

async function toolRunHealthCheck(args, user) {
  const connId = Number(args.connection_id);
  const { rows } = await pool.query(
    `SELECT id, name FROM oracle_connections WHERE id = $1 AND user_id = $2`,
    [connId, user.id]
  );
  if (!rows.length) return text(`Connection ${connId} not found or not accessible.`);
  const conn = rows[0];
  return text(
    `To run a health check on **${conn.name}** (connection ${connId}):\n\n` +
    `1. Open https://tunevault.app/connections\n` +
    `2. Find "${conn.name}" and click **Run Health Check**\n` +
    `3. Wait 30–90 seconds for results\n\n` +
    `Then call \`get_latest_health_check\` with \`connection_id: ${connId}\` to retrieve the results.`
  );
}

// ── Tool: get_latest_health_check ─────────────────────────────────────────────

async function toolGetLatestHealthCheck(args, user) {
  const connId = Number(args.connection_id);
  const { rows } = await pool.query(
    `SELECT hc.id, hc.overall_score, hc.summary_text, hc.top_action,
            hc.ebs_summary, hc.status, hc.created_at, hc.connection_name
     FROM health_checks hc
     WHERE hc.connection_id = $1
       AND hc.connection_id IN (SELECT id FROM oracle_connections WHERE user_id = $2)
     ORDER BY hc.created_at DESC LIMIT 1`,
    [connId, user.id]
  );
  if (!rows.length) {
    return text(
      `No health checks found for connection ${connId}.\n\n` +
      `Trigger one from: https://tunevault.app/connections`
    );
  }
  const hc = rows[0];

  const findings = await pool.query(
    `SELECT check_id, check_category, status, metric_name, metric_value, ai_summary, recommendation
     FROM check_results
     WHERE connection_id = $1
       AND run_id = (
         SELECT run_id FROM check_results
         WHERE connection_id = $1
         ORDER BY executed_at DESC LIMIT 1
       )
       AND status IN ('red', 'amber')
     ORDER BY CASE status WHEN 'red' THEN 0 WHEN 'amber' THEN 1 ELSE 2 END,
              check_category, check_id
     LIMIT 10`,
    [connId]
  );

  const lines = [
    `# Health Check — ${hc.connection_name}`,
    `**Score:** ${hc.overall_score ?? 'N/A'}/100`,
    `**Status:** ${hc.status}`,
    `**Run at:** ${new Date(hc.created_at).toISOString()}`,
    '',
    '## Summary',
    hc.summary_text || 'No summary yet — analysis may still be in progress.',
    '',
  ];
  if (hc.top_action) {
    lines.push('## Top Recommended Action');
    lines.push(hc.top_action);
    lines.push('');
  }
  if (findings.rows.length) {
    lines.push('## Critical & Warning Findings');
    findings.rows.forEach(f => {
      const val = (f.metric_value != null) ? ` (${f.metric_name}: ${f.metric_value})` : '';
      lines.push(`- **[${(f.status || '').toUpperCase()}] ${f.check_id}**${val}`);
      if (f.ai_summary)    lines.push(`  ${f.ai_summary}`);
      if (f.recommendation) lines.push(`  → ${f.recommendation}`);
    });
    lines.push('');
  }
  if (hc.ebs_summary) {
    lines.push('## EBS Summary');
    lines.push(hc.ebs_summary);
    lines.push('');
  }
  lines.push(`View full report: https://tunevault.app/report?id=${hc.id}`);
  return text(lines.join('\n'));
}

// ── Tool: security_posture_scan ───────────────────────────────────────────────

async function toolSecurityPostureScan(args, user) {
  const connId = Number(args.connection_id);
  const { rows } = await pool.query(
    `SELECT id, title, generated_data, generated_at AS created_at
     FROM compliance_reports
     WHERE report_type = 'security_posture'
       AND user_id = $1
       AND (row_counts->>'connection_id')::int = $2
     ORDER BY date_from DESC LIMIT 1`,
    [user.id, connId]
  );
  if (!rows.length) {
    return text(
      `No Security Posture scan found for connection ${connId}.\n\n` +
      `Run one from: https://tunevault.app/vault?conn=${connId}`
    );
  }
  const data     = rows[0].generated_data;
  const scanDate = new Date(rows[0].created_at).toISOString();

  const lines = [
    `# Security Posture — ${data.connection_name || `Connection ${connId}`}`,
    `**Score:** ${data.score ?? 'N/A'}/100`,
    `**Scanned at:** ${scanDate}`,
  ];
  if (data.incomplete) {
    lines.push(`\n⚠ Incomplete: ${data.incomplete_message || 'Some checks could not complete.'}`);
  }
  lines.push('');

  for (const [key, g] of Object.entries(data.groups || {})) {
    const scorePart = (g.score != null && g.maxScore != null) ? ` (${g.score}/${g.maxScore})` : '';
    lines.push(`## ${g.label || key} — ${g.status || 'unknown'}${scorePart}`);
    if (g.findings && g.findings.length) {
      g.findings.forEach(f => {
        lines.push(`- **[${(f.severity || 'warning').toUpperCase()}]** ${f.title}${f.detail ? ': ' + f.detail : ''}`);
      });
    } else {
      lines.push('- ✓ No findings.');
    }
    lines.push('');
  }
  return text(lines.join('\n'));
}

// ── Tool: list_tuneops_tickets ────────────────────────────────────────────────

async function toolListTuneopsTickets(args, user) {
  const companyId = user.company_domain || user.email.split('@')[1] || `user_${user.id}`;
  const limit = Math.min(Number(args.limit) || 20, 100);
  const conditions = ['t.company_id = $1'];
  const params = [companyId];
  let p = 2;

  if (args.connection_id) { conditions.push(`t.connection_id = $${p++}`); params.push(Number(args.connection_id)); }
  if (args.status)        { conditions.push(`t.status = $${p++}`);        params.push(String(args.status)); }
  if (args.severity)      { conditions.push(`t.severity = $${p++}`);      params.push(String(args.severity)); }
  params.push(limit);

  const { rows } = await pool.query(
    `SELECT t.ticket_number, t.severity, t.status, t.title, t.description,
            t.recommended_fix, t.connection_id, t.created_at, t.resolved_at,
            oc.name AS connection_name
     FROM tuneops_tickets t
     LEFT JOIN oracle_connections oc ON oc.id = t.connection_id
     WHERE ${conditions.join(' AND ')}
     ORDER BY CASE t.severity WHEN 'critical' THEN 1 WHEN 'warning' THEN 2 ELSE 3 END,
              t.created_at DESC
     LIMIT $${p}`,
    params
  );
  if (!rows.length) return text('No TuneOps tickets found matching the criteria.');

  const lines = [`# TuneOps Tickets (${rows.length} shown)\n`];
  rows.forEach(t => {
    lines.push(`## ${t.ticket_number} — ${t.title}`);
    lines.push(`- **Severity:** ${t.severity}  **Status:** ${t.status}`);
    if (t.connection_name) lines.push(`- **Connection:** ${t.connection_name} (ID: ${t.connection_id})`);
    lines.push(`- **Created:** ${new Date(t.created_at).toISOString()}`);
    if (t.description)    lines.push(`- ${t.description}`);
    if (t.recommended_fix) lines.push(`- **Fix:** ${t.recommended_fix}`);
    if (t.resolved_at)    lines.push(`- **Resolved:** ${new Date(t.resolved_at).toISOString()}`);
    lines.push('');
  });
  return text(lines.join('\n'));
}

// ── Tool: run_sql_query ───────────────────────────────────────────────────────

const BLOCK_SQL = /^\s*(INSERT|UPDATE|DELETE|DROP|ALTER|TRUNCATE|CREATE|MERGE|GRANT|REVOKE)\b/i;

async function toolRunSqlQuery(args, user) {
  const connId = Number(args.connection_id);
  const { sql } = args;

  if (!sql || !sql.trim()) return text('No SQL provided.');
  const clean = sql.replace(/\/\*[\s\S]*?\*\//g, '').replace(/--[^\n]*/g, '').trimStart();
  if (BLOCK_SQL.test(clean)) {
    return text('SQL Console is read-only — only SELECT and WITH queries are permitted.');
  }

  const { rows: connRows } = await pool.query(
    `SELECT id, name, host, port, service_name, connection_type, username, encrypted_password
     FROM oracle_connections WHERE id = $1 AND user_id = $2`,
    [connId, user.id]
  );
  if (!connRows.length) return text(`Connection ${connId} not found or not accessible.`);
  const conn = connRows[0];
  if (conn.connection_type !== 'proxy') {
    return text(
      `run_sql_query requires a proxy-agent connection. "${conn.name}" uses direct TCP. ` +
      `Only proxy-agent connections are supported via MCP.`
    );
  }

  // Fall back to stored credentials when caller omits username/password.
  const username = args.username || conn.username || null;
  const password = args.password || (conn.encrypted_password ? decrypt(conn.encrypted_password) : '');
  if (!username) return text('No username provided and connection has no stored Oracle credentials.');

  const alive = await Promise.race([
    channel.isAgentConnected(connId),
    new Promise(r => setTimeout(() => r(false), 5000)),
  ]);
  if (!alive) return text(`Agent for "${conn.name}" is offline. Check the connection in TuneVault.`);

  let resp;
  try {
    resp = await channel.sendToAgent(connId, {
      method: 'POST', path: '/api/run_sql',
      body: {
        sql: sql.trim(), username, password,
        service_name: conn.service_name, host: conn.host, port: conn.port || 1521,
        os_auth: false, max_rows: 200,
      },
    }, 30000);
  } catch (e) { return text(`Query failed: ${e.message}`); }

  const result = resp.body;
  if (!result.success) return text(`Query error: ${result.error || 'Unknown error'}`);

  const cols    = result.columns || [];
  const rawRows = result.rows || [];
  const MAX     = 200;
  const mapped  = rawRows.slice(0, MAX).map(row =>
    Object.fromEntries(cols.map((c, i) => [c, row[i] ?? null]))
  );
  const truncated = rawRows.length > MAX;
  if (!mapped.length) return text('Query returned 0 rows.');

  const header = '| ' + cols.join(' | ') + ' |';
  const sep    = '| ' + cols.map(() => '---').join(' | ') + ' |';
  const body   = mapped.map(row => '| ' + cols.map(c => String(row[c] ?? '')).join(' | ') + ' |');
  const note   = truncated ? `\n\n⚠ Showing first ${MAX} of ${rawRows.length} rows.` : '';
  return text(`${mapped.length} row(s) returned.\n\n${[header, sep, ...body].join('\n')}${note}`);
}

// ── Tool: get_vulnerability_exposure ─────────────────────────────────────────

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

async function toolGetVulnerabilityExposure(args, user) {
  const connId = Number(args.connection_id);
  const { rows: connRows } = await pool.query(
    `SELECT id, name, host, port, service_name, connection_type, username, encrypted_password,
            ebs_instance_name, server_type
     FROM oracle_connections WHERE id = $1 AND user_id = $2`,
    [connId, user.id]
  );
  if (!connRows.length) return text(`Connection ${connId} not found or not accessible.`);
  const conn = connRows[0];

  if (conn.connection_type !== 'proxy') {
    return text(
      `get_vulnerability_exposure requires a proxy-agent connection. ` +
      `"${conn.name}" uses direct TCP — only proxy-agent connections are supported.`
    );
  }

  const alive = await Promise.race([
    channel.isAgentConnected(connId),
    new Promise(r => setTimeout(() => r(false), 5000)),
  ]);
  if (!alive) return text(`Agent for "${conn.name}" is offline. Check the connection in TuneVault.`);

  const sqlCreds = {
    service_name: conn.service_name,
    username:     conn.username,
    password:     conn.encrypted_password ? decrypt(conn.encrypted_password) : '',
    host:         conn.host,
    port:         conn.port || 1521,
    os_auth:      !conn.username,
  };
  async function runQuery(sql) {
    const resp = await channel.sendToAgent(connId, {
      method: 'POST', path: '/api/run_sql',
      body: { sql, ...sqlCreds },
    }, 60000);
    if (!resp.body.success) throw new Error(resp.body.error || 'Query failed');
    const cols = (resp.body.columns || []).map(c => c.toLowerCase());
    return (resp.body.rows || []).map(row =>
      Object.fromEntries(cols.map((c, i) => [c, row[i] ?? null]))
    );
  }

  let rawVersion = null;
  try {
    const vRows = await runQuery(VULN_VERSION_SQL);
    rawVersion = vRows[0]?.version_full || vRows[0]?.version || null;
  } catch (e) {
    return text(`Could not query Oracle version: ${e.message}`);
  }

  let appliedPatchIds = [];
  try {
    const pRows = await runQuery(VULN_PATCH_SQL);
    appliedPatchIds = pRows.map(r => String(r.patch_id)).filter(Boolean);
  } catch (_) { /* proceed with empty list */ }

  const ver = rawVersion || '';
  let versionKey = null;
  if      (/^19\./.test(ver))                      versionKey = '19c';
  else if (/^21\./.test(ver))                      versionKey = '21c';
  else if (/^23\./.test(ver) || /^26\./.test(ver)) versionKey = '26ai';
  else if (/^12\.2/.test(ver))                     versionKey = '12.2';
  else if (/^18\./.test(ver))                      versionKey = '18c';

  const meta       = PATCH_INDEX._meta || {};
  const isEbs      = !!(conn.ebs_instance_name || conn.server_type === 'db');

  // ── RDBMS CVE analysis ──────────────────────────────────────────────────────
  const rdbmsPatches = versionKey ? (PATCH_INDEX.rdbms?.[versionKey]?.release_update_patches || []) : [];
  const appliedSet   = new Set(appliedPatchIds.map(String));
  let highestAppliedIdx = rdbmsPatches.length;
  for (let i = 0; i < rdbmsPatches.length; i++) {
    if (appliedSet.has(String(rdbmsPatches[i].patch_number))) { highestAppliedIdx = i; break; }
  }
  const rdbmsUnfixed = [];
  for (let i = 0; i < highestAppliedIdx; i++) {
    const p = rdbmsPatches[i];
    for (const cve of (p.cves || [])) {
      rdbmsUnfixed.push({ cve_id: cve, severity: p.severity || 'HIGH', cvss: p.cvss_max || null,
        description: p.highlights || '', fix_patch: p.patch_number, fix_label: p.label });
    }
  }

  // ── EBS RUP CVE analysis (auto-detected via AD_APPLIED_PATCHES) ─────────────
  const EBS_RUP_SQL = `SELECT TO_CHAR(patch_number) AS patch_number FROM ad_applied_patches WHERE TO_CHAR(patch_number) IN ('34174657','33267777') ORDER BY creation_date DESC FETCH FIRST 1 ROW ONLY`;
  let ebsUnfixed = [];
  let ebsDetected = false;
  let ebsAppliedLabel = null;
  try {
    const ebsRows = await runQuery(EBS_RUP_SQL);
    ebsDetected = true;
    const appliedEbsSet = new Set(ebsRows.map(r => String(r.patch_number)));
    const ebsPatches = PATCH_INDEX.ebs?.['12.2']?.adop_patches || [];
    let highestEbsIdx = ebsPatches.length;
    for (let i = 0; i < ebsPatches.length; i++) {
      if (appliedEbsSet.has(String(ebsPatches[i].patch_number))) { highestEbsIdx = i; break; }
    }
    if (highestEbsIdx < ebsPatches.length) ebsAppliedLabel = ebsPatches[highestEbsIdx].label;
    for (let i = 0; i < highestEbsIdx; i++) {
      const p = ebsPatches[i];
      for (const cve of (p.cves || [])) {
        ebsUnfixed.push({ cve_id: cve, severity: p.severity || 'HIGH', cvss: p.cvss_max || null,
          description: `EBS 12.2: ${p.highlights || p.label}`, fix_patch: p.patch_number, fix_label: p.label });
      }
    }
  } catch (_) { /* AD_APPLIED_PATCHES not found — non-EBS DB */ }

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

  const sev = { CRITICAL: 0, HIGH: 1, MEDIUM: 2, LOW: 3 };
  const sortBySev = arr => arr.sort((a, b) => (sev[(a.severity||'').toUpperCase()] ?? 9) - (sev[(b.severity||'').toUpperCase()] ?? 9));
  sortBySev(rdbmsUnfixed); sortBySev(ebsUnfixed); sortBySev(wlsUnverified);

  const totalConfirmed = rdbmsUnfixed.length + ebsUnfixed.length;
  const critical = [...rdbmsUnfixed, ...ebsUnfixed].filter(c => (c.severity||'').toUpperCase() === 'CRITICAL').length;
  const high     = [...rdbmsUnfixed, ...ebsUnfixed].filter(c => (c.severity||'').toUpperCase() === 'HIGH').length;
  const medium   = [...rdbmsUnfixed, ...ebsUnfixed].filter(c => (c.severity||'').toUpperCase() === 'MEDIUM').length;

  const lines = [
    `# Vulnerability Exposure — ${conn.name}`,
    `**Oracle Version:** ${rawVersion || 'Unknown'}`,
    `**Version Key:** ${versionKey || 'unrecognised (no CVE data)'}`,
    `**Scanned at:** ${new Date().toISOString()}`,
    `**Confirmed unfixed CVEs:** ${totalConfirmed} (Critical: ${critical}, High: ${high}, Medium: ${medium})`,
    `**Patch index:** last refreshed ${meta.LAST_REFRESHED || 'unknown'} — next update due ${meta.next_refresh_due || 'unknown'} (${meta.refresh_cadence || 'quarterly'})`,
    '',
  ];

  // RDBMS section
  if (!rdbmsUnfixed.length) {
    lines.push('## Oracle DB CVEs\n✓ No unpatched RDBMS CVEs found for this Oracle version.');
  } else {
    lines.push('## Oracle DB CVEs');
    rdbmsUnfixed.slice(0, 20).forEach(c => {
      lines.push(`- **[${(c.severity||'').toUpperCase()}] ${c.cve_id}** (CVSS ${c.cvss || 'N/A'}) — ${c.description || ''}`);
      lines.push(`  Fix: Apply patch ${c.fix_patch} (${c.fix_label || ''})`);
    });
    const topPatch = rdbmsUnfixed[0]?.fix_patch;
    if (topPatch) {
      lines.push('');
      lines.push('**OPatch Steps (highest-priority DB patch):**');
      lines.push('```bash');
      lines.push(`unzip p${topPatch}_Linux-x86-64.zip && cd ${topPatch}`);
      lines.push(`$ORACLE_HOME/OPatch/opatch apply`);
      lines.push(`$ORACLE_HOME/OPatch/opatch lspatches | grep ${topPatch}`);
      lines.push('```');
    }
  }
  lines.push('');

  // EBS section
  if (ebsDetected) {
    if (!ebsUnfixed.length) {
      lines.push(`## EBS 12.2 CVEs\n✓ EBS is current${ebsAppliedLabel ? ` (${ebsAppliedLabel} applied)` : ''}.`);
    } else {
      lines.push(`## EBS 12.2 CVEs${ebsAppliedLabel ? ` (applied: ${ebsAppliedLabel})` : ''}`);
      ebsUnfixed.forEach(c => {
        lines.push(`- **[${(c.severity||'').toUpperCase()}] ${c.cve_id}** (CVSS ${c.cvss || 'N/A'}) — ${c.description || ''}`);
        lines.push(`  Fix: Apply ${c.fix_label} (ADOP patch ${c.fix_patch})`);
      });
    }
    lines.push('');
  }

  // WebLogic / FMW section
  if (wlsEosNote) {
    lines.push('## WebLogic / FMW Tech Stack');
    lines.push(`⚠ ${wlsEosNote}`);
    lines.push('');
  }

  return text(lines.join('\n'));
}

// ── Tool: get_resilience_status ───────────────────────────────────────────────

async function toolGetResilienceStatus(args, user) {
  const connId = Number(args.connection_id);
  const { rows } = await pool.query(
    `SELECT id, title, generated_data, generated_at AS created_at
     FROM compliance_reports
     WHERE report_type = 'resilience'
       AND user_id = $1
       AND (row_counts->>'connection_id')::int = $2
     ORDER BY date_from DESC LIMIT 1`,
    [user.id, connId]
  );
  if (!rows.length) {
    return text(
      `No Resilience scan found for connection ${connId}.\n\n` +
      `Run one from: https://tunevault.app/vault?conn=${connId}`
    );
  }
  const data     = rows[0].generated_data;
  const scanDate = new Date(rows[0].created_at).toISOString();

  const lines = [
    `# Resilience Status — ${data.connection_name || `Connection ${connId}`}`,
    `**Score:** ${data.score ?? 'N/A'}/100`,
    `**Scanned at:** ${scanDate}`,
  ];
  if (data.incomplete) {
    lines.push(`\n⚠ Incomplete: ${data.incomplete_message || 'Some checks could not complete.'}`);
  }
  lines.push('');

  for (const [key, g] of Object.entries(data.groups || {})) {
    const scorePart = (g.score != null && g.maxScore != null) ? ` (${g.score}/${g.maxScore})` : '';
    lines.push(`## ${g.label || key} — ${g.status || 'unknown'}${scorePart}`);
    if (g.findings && g.findings.length) {
      g.findings.forEach(f => {
        lines.push(`- **[${(f.severity || 'warning').toUpperCase()}]** ${f.title}${f.detail ? ': ' + f.detail : ''}`);
      });
    } else {
      lines.push('- ✓ No findings.');
    }
    lines.push('');
  }
  return text(lines.join('\n'));
}

// ── Tool: create_tuneops_ticket ───────────────────────────────────────────────

async function toolCreateTuneopsTicket(args, user) {
  const { title, description, connection_id } = args;
  if (!title || !String(title).trim()) return text('title is required.');

  const VALID_SEVERITIES = ['critical', 'warning', 'info'];
  const severity = String(args.severity || 'warning').toLowerCase();
  if (!VALID_SEVERITIES.includes(severity)) {
    return text(`severity must be one of: ${VALID_SEVERITIES.join(', ')}`);
  }

  if (connection_id) {
    const { rows } = await pool.query(
      `SELECT id FROM oracle_connections WHERE id = $1 AND user_id = $2`,
      [Number(connection_id), user.id]
    );
    if (!rows.length) return text(`Connection ${connection_id} not found or not accessible.`);
  }

  const companyId = user.company_domain || user.email.split('@')[1] || `user_${user.id}`;
  const ticket = await dbTuneops.createTicket({
    companyId,
    connectionId:    connection_id ? Number(connection_id) : null,
    title:           String(title).trim(),
    description:     description ? String(description).trim() : null,
    severity,
    status:          'OPEN',
    source:          'manual',
    sourceReference: { created_by_mcp: true, user_id: user.id },
  });

  return text(
    `# TuneOps Ticket Created\n\n` +
    `**Ticket:** ${ticket.ticket_number}\n` +
    `**Title:** ${ticket.title}\n` +
    `**Severity:** ${ticket.severity}\n` +
    `**Status:** ${ticket.status}\n` +
    `**Created:** ${new Date(ticket.created_at).toISOString()}\n` +
    (ticket.connection_id ? `**Connection ID:** ${ticket.connection_id}\n` : '') +
    `\nView at: https://tunevault.app/tuneops`
  );
}

// ── Tool: get_fleet_status ────────────────────────────────────────────────────

async function toolGetFleetStatus(_args, user) {
  const { rows } = await pool.query(
    `SELECT oc.id, oc.name, oc.server_type, oc.proxy_version,
            oc.ebs_instance_name, oc.connectivity_mode,
            hc.overall_score, hc.status AS hc_status, hc.created_at AS last_hc_at,
            acs.last_heartbeat_at
     FROM oracle_connections oc
     LEFT JOIN LATERAL (
       SELECT overall_score, status, created_at
       FROM health_checks
       WHERE connection_id = oc.id
       ORDER BY created_at DESC LIMIT 1
     ) hc ON true
     LEFT JOIN agent_channel_state acs ON acs.agent_id = oc.id
     WHERE oc.user_id = $1
     ORDER BY oc.name`,
    [user.id]
  );
  if (!rows.length) return text('No connections found for this user.');

  const now = Date.now();
  const ONLINE_MS = 5 * 60 * 1000;

  const connections = rows.map(c => {
    const lastHB = c.last_heartbeat_at ? new Date(c.last_heartbeat_at).getTime() : 0;
    return {
      id:           c.id,
      name:         c.name,
      server_type:  c.server_type || 'db',
      agent_version: c.proxy_version || null,
      last_seen:    c.last_heartbeat_at ? new Date(c.last_heartbeat_at).toISOString() : null,
      health_score: c.overall_score ?? null,
      last_hc_at:   c.last_hc_at ? new Date(c.last_hc_at).toISOString() : null,
      ebs_instance: c.ebs_instance_name || null,
      online:       lastHB > 0 && (now - lastHB) < ONLINE_MS,
    };
  });

  const total   = connections.length;
  const online  = connections.filter(c => c.online).length;
  const offline = total - online;

  const lines = [
    `# Fleet Status`,
    `**Total:** ${total}  |  **Online:** ${online}  |  **Offline:** ${offline}`,
    '',
  ];
  connections.forEach(c => {
    const icon    = c.online ? '🟢' : '⚫';
    const score   = c.health_score != null ? ` | Score: ${c.health_score}/100` : '';
    lines.push(`${icon} **${c.name}** (ID: ${c.id}) — ${c.server_type}${score}`);
    if (c.agent_version) lines.push(`   Agent: v${c.agent_version}`);
    if (c.last_seen)     lines.push(`   Last seen: ${c.last_seen}`);
    if (c.last_hc_at)    lines.push(`   Last HC: ${c.last_hc_at}`);
    if (c.ebs_instance)  lines.push(`   EBS: ${c.ebs_instance}`);
    lines.push('');
  });

  return text(lines.join('\n'));
}

// ── Shared helper: resolve SQL-execution connection ───────────────────────────
// App-tier agents have no Oracle driver. For any tool that needs to run SQL,
// call this to get the connection that should actually receive the /api/run_sql
// command. Returns { sqlConn, sqlConnId, displayName, error } where error is a
// text() response to return immediately if pairing failed.

async function getPairedDbConn(conn, connId, user, toolName) {
  if (!['apps', 'both'].includes(conn.server_type)) {
    return { sqlConn: conn, sqlConnId: connId, displayName: conn.name, error: null };
  }
  if (!conn.ebs_instance_name) {
    return {
      error: text(
        `"${conn.name}" is an app-tier connection but has no EBS Instance Name set.\n` +
        `Set ebs_instance_name on both app-tier and DB-tier connections so they can be paired.`
      ),
    };
  }
  const { rows: dbRows } = await pool.query(
    `SELECT id, name, host, port, service_name, connection_type, username, encrypted_password,
            server_type, ebs_instance_name
     FROM oracle_connections
     WHERE ebs_instance_name = $1
       AND server_type IN ('db', 'both')
       AND connection_type = 'proxy'
       AND user_id = $2
       AND id != $3
     LIMIT 1`,
    [conn.ebs_instance_name, user.id, connId]
  );
  if (!dbRows.length) {
    return {
      error: text(
        `Cannot find a paired DB-tier proxy connection for "${conn.name}" ` +
        `(EBS instance: ${conn.ebs_instance_name}).\n` +
        `Ensure the DB-tier connection also has ebs_instance_name="${conn.ebs_instance_name}" set.`
      ),
    };
  }
  return {
    sqlConn:     dbRows[0],
    sqlConnId:   dbRows[0].id,
    displayName: `${conn.name} (via ${dbRows[0].name})`,
    error:       null,
  };
}

// ── Tool: get_adop_status ─────────────────────────────────────────────────────

// AD_ADOP_SESSIONS real schema (EBS 12.2): status cols are VARCHAR2(1) codes
// (N=not started, R=running, S=success, F=failed, X=aborted/skipped).
// Date cols are *_START_DATE / *_END_DATE, not *_date.
const SQL_ADOP_STATUS = `
  SELECT
    s.adop_session_id,
    s.status,
    s.prepare_status,
    s.apply_status,
    s.finalize_status,
    s.cutover_status,
    s.cleanup_status,
    s.abort_status,
    s.node_name,
    s.node_type,
    TO_CHAR(s.prepare_start_date,  'YYYY-MM-DD HH24:MI:SS') AS prepare_start_date,
    TO_CHAR(s.apply_start_date,    'YYYY-MM-DD HH24:MI:SS') AS apply_start_date,
    TO_CHAR(s.finalize_start_date, 'YYYY-MM-DD HH24:MI:SS') AS finalize_start_date,
    TO_CHAR(s.cutover_start_date,  'YYYY-MM-DD HH24:MI:SS') AS cutover_start_date,
    TO_CHAR(s.cleanup_start_date,  'YYYY-MM-DD HH24:MI:SS') AS cleanup_start_date,
    TO_CHAR(s.abort_start_date,    'YYYY-MM-DD HH24:MI:SS') AS abort_start_date
  FROM AD_ADOP_SESSIONS s
  ORDER BY s.adop_session_id DESC
  FETCH FIRST 5 ROWS ONLY`;

async function toolGetAdopStatus(args, user) {
  const connId = Number(args.connection_id);
  const { rows: connRows } = await pool.query(
    `SELECT id, name, host, port, service_name, connection_type, username, encrypted_password,
            server_type, ebs_instance_name
     FROM oracle_connections WHERE id = $1 AND user_id = $2`,
    [connId, user.id]
  );
  if (!connRows.length) return text(`Connection ${connId} not found or not accessible.`);
  const conn = connRows[0];

  if (conn.connection_type !== 'proxy') {
    return text(
      `get_adop_status requires a proxy-agent connection. ` +
      `"${conn.name}" uses direct TCP — only proxy-agent connections are supported.`
    );
  }

  const paired = await getPairedDbConn(conn, connId, user, 'get_adop_status');
  if (paired.error) return paired.error;
  const { sqlConn, sqlConnId } = paired;

  const alive = await Promise.race([
    channel.isAgentConnected(sqlConnId),
    new Promise(r => setTimeout(() => r(false), 5000)),
  ]);
  if (!alive) return text(`Agent for "${sqlConn.name}" is offline. Check the connection in TuneVault.`);

  const username = args.username || sqlConn.username || null;
  const password = args.password || (sqlConn.encrypted_password ? decrypt(sqlConn.encrypted_password) : '');
  if (!username) return text('No username provided and connection has no stored Oracle credentials. Pass username/password (e.g. apps/<apps_pwd>).');

  let resp;
  try {
    resp = await channel.sendToAgent(sqlConnId, {
      method: 'POST', path: '/api/run_sql',
      body: { sql: SQL_ADOP_STATUS, username, password,
              service_name: sqlConn.service_name, host: sqlConn.host,
              port: sqlConn.port || 1521, os_auth: false, max_rows: 5 },
    }, 60000);
  } catch (e) { return text(`ADOP query failed: ${e.message}`); }

  const result = resp.body;
  if (!result.success) {
    const err = result.error || '';
    if (/ORA-00942|does not exist/i.test(err)) {
      return text(
        `AD_ADOP_SESSIONS not accessible — this view requires APPS schema access.\n` +
        `Pass username=apps and password=<apps_pwd> to query as the APPS user.`
      );
    }
    return text(`ADOP query error: ${err || 'Unknown error'}`);
  }

  const cols    = (result.columns || []).map(c => c.toLowerCase());
  const adoptRows = (result.rows || []).map(row =>
    Object.fromEntries(cols.map((c, i) => [c, row[i] ?? null]))
  );

  const displayName = paired.displayName;

  if (!adoptRows.length) {
    return text(`# ADOP Status — ${displayName}\n\n✓ No active ADOP sessions. No patch cycle in progress.`);
  }

  // VARCHAR2(1) status codes used in AD_ADOP_SESSIONS
  const STATUS_LABEL = { N: 'Not started', R: 'Running', S: 'Success', C: 'Completed', F: 'Failed', X: 'Not triggered', Y: 'Completed', n: 'N/A' };
  const decodeStatus = v => STATUS_LABEL[v] || v || '—';

  const PHASE_FIELDS = [
    { status: 'prepare_status',  start: 'prepare_start_date',  phase: 'Prepare'  },
    { status: 'apply_status',    start: 'apply_start_date',    phase: 'Apply'    },
    { status: 'finalize_status', start: 'finalize_start_date', phase: 'Finalize' },
    { status: 'cutover_status',  start: 'cutover_start_date',  phase: 'Cutover'  },
    { status: 'cleanup_status',  start: 'cleanup_start_date',  phase: 'Cleanup'  },
    { status: 'abort_status',    start: 'abort_start_date',    phase: 'Abort'    },
  ];

  const lines = [`# ADOP Status — ${displayName}\n`];
  adoptRows.forEach(s => {
    // Current phase = last phase whose status is Running, or last one started.
    const running = PHASE_FIELDS.find(p => s[p.status] === 'R');
    const started = [...PHASE_FIELDS].reverse().find(p => s[p.start]);
    const currentPhase = running?.phase || started?.phase || decodeStatus(s.status);

    lines.push(`## Session ${s.adop_session_id}`);
    lines.push(`- **Overall status:** ${decodeStatus(s.status)}`);
    lines.push(`- **Current phase:** ${currentPhase}`);
    if (s.node_name) lines.push(`- **Node:** ${s.node_name}${s.node_type ? ` (${s.node_type})` : ''}`);
    lines.push('');
    lines.push('| Phase | Status | Started |');
    lines.push('|-------|--------|---------|');
    PHASE_FIELDS.forEach(p => {
      const st = decodeStatus(s[p.status]);
      const started = s[p.start] || '—';
      if (s[p.status] && s[p.status] !== 'N') {
        lines.push(`| ${p.phase} | ${st} | ${started} |`);
      }
    });
    lines.push('');
  });

  return text(lines.join('\n'));
}

// ── Tool: run_ebs_health_check ────────────────────────────────────────────────

async function toolRunEbsHealthCheck(args, user) {
  const connId = Number(args.connection_id);
  const { rows: connRows } = await pool.query(
    `SELECT id, name, connection_type, server_type, apps_pwd_enc, weblogic_pwd_enc
     FROM oracle_connections WHERE id = $1 AND user_id = $2`,
    [connId, user.id]
  );
  if (!connRows.length) return text(`Connection ${connId} not found or not accessible.`);
  const conn = connRows[0];

  if (conn.connection_type !== 'proxy') {
    return text(`run_ebs_health_check requires a proxy-agent connection. "${conn.name}" uses direct TCP.`);
  }
  if (!['apps', 'both'].includes(conn.server_type)) {
    return text(
      `run_ebs_health_check requires an EBS app-tier connection (server_type = apps or both). ` +
      `"${conn.name}" is server_type="${conn.server_type}". Use the app-tier connection, not the DB connection.`
    );
  }

  const alive = await Promise.race([
    channel.isAgentConnected(connId),
    new Promise(r => setTimeout(() => r(false), 5000)),
  ]);
  if (!alive) return text(`Agent for "${conn.name}" is offline. Check the connection in TuneVault.`);

  const appBody = {};
  if (conn.apps_pwd_enc)     appBody.apps_pwd     = decrypt(conn.apps_pwd_enc);
  if (conn.weblogic_pwd_enc) appBody.weblogic_pwd = decrypt(conn.weblogic_pwd_enc);

  let resp;
  try {
    resp = await channel.sendToAgent(connId, {
      method: 'POST', path: '/api/ebs-app-healthcheck',
      body: appBody,
    }, 300000); // 5-minute timeout matching server.js pattern
  } catch (e) { return text(`EBS health check failed: ${e.message}`); }

  const parsed = resp.body || {};
  if (resp.statusCode !== 200 || !parsed.success) {
    return text(`EBS health check error: ${parsed.error || `HTTP ${resp.statusCode}`}`);
  }

  const findings  = parsed.findings  || [];
  const checksOk  = parsed.checks_ok || [];
  const score     = parsed.score     || parsed._app_score || 0;
  const criticals = findings.filter(f => f.severity === 'critical' || f.level === 'critical');
  const warnings  = findings.filter(f => f.severity === 'warning'  || f.level === 'warning');

  const lines = [
    `# EBS Health Check — ${conn.name}`,
    `**Score:** ${score}/100`,
    `**Checks passed:** ${checksOk.length}  |  **Critical:** ${criticals.length}  |  **Warnings:** ${warnings.length}`,
    `**Run at:** ${parsed.ran_at || new Date().toISOString()}`,
    '',
  ];

  if (criticals.length) {
    lines.push('## Critical Findings');
    criticals.forEach(f => {
      lines.push(`- **${f.check || f.name || f.title || 'Unknown'}**: ${f.message || f.detail || f.output || ''}`);
    });
    lines.push('');
  }
  if (warnings.length) {
    lines.push('## Warnings');
    warnings.forEach(f => {
      lines.push(`- **${f.check || f.name || f.title || 'Unknown'}**: ${f.message || f.detail || f.output || ''}`);
    });
    lines.push('');
  }
  if (!criticals.length && !warnings.length) {
    lines.push('✓ All EBS app-tier checks passed — no critical or warning findings.');
  }

  return text(lines.join('\n'));
}

// ── Tool: get_security_posture ────────────────────────────────────────────────

async function toolGetSecurityPosture(args, user) {
  const connId = Number(args.connection_id);
  const { rows } = await pool.query(
    `SELECT generated_data, generated_at AS created_at
     FROM compliance_reports
     WHERE report_type = 'security_posture'
       AND user_id = $1
       AND (row_counts->>'connection_id')::int = $2
     ORDER BY date_from DESC LIMIT 1`,
    [user.id, connId]
  );
  if (!rows.length) {
    return text(
      `No Security Posture scan found for connection ${connId}.\n` +
      `Run one from: https://tunevault.app/vault?conn=${connId}`
    );
  }
  const data     = rows[0].generated_data;
  const scanDate = new Date(rows[0].created_at).toISOString();

  const criticalGroups = [];
  const warningGroups  = [];
  for (const [, g] of Object.entries(data.groups || {})) {
    if (!g.findings || !g.findings.length) continue;
    const label = g.label || '';
    const crits = g.findings.filter(f => (f.severity || '').toLowerCase() === 'critical').map(f => f.title);
    const warns = g.findings.filter(f => (f.severity || '').toLowerCase() !== 'critical').map(f => f.title);
    if (crits.length) criticalGroups.push({ group: label, findings: crits });
    if (warns.length) warningGroups.push({ group: label, findings: warns });
  }

  const lines = [
    `# Security Posture — Connection ${connId}`,
    `**Score:** ${data.score ?? 'N/A'}/100`,
    `**Scanned:** ${scanDate}`,
    `**Incomplete:** ${data.incomplete ? 'yes' : 'no'}`,
    '',
  ];
  if (criticalGroups.length) {
    lines.push('## Critical Groups');
    criticalGroups.forEach(g => {
      lines.push(`### ${g.group}`);
      g.findings.forEach(f => lines.push(`- ${f}`));
    });
    lines.push('');
  }
  if (warningGroups.length) {
    lines.push('## Warning Groups');
    warningGroups.forEach(g => {
      lines.push(`### ${g.group}`);
      g.findings.forEach(f => lines.push(`- ${f}`));
    });
    lines.push('');
  }
  if (!criticalGroups.length && !warningGroups.length) {
    lines.push('✓ No security findings — all groups clean.');
  }
  return text(lines.join('\n'));
}

// ── Tool: get_patch_advisor ───────────────────────────────────────────────────

const SQL_CURRENT_PATCH = `
SELECT patch_id, version, TO_CHAR(action_time,'YYYY-MM-DD') AS action_time, description, bundle_series
FROM   sys.dba_registry_sqlpatch
WHERE  action = 'APPLY' AND status = 'SUCCESS'
ORDER  BY action_time DESC
FETCH  FIRST 1 ROW ONLY`;

const SQL_ORACLE_VERSION = `SELECT version_full FROM v$instance`;

async function toolGetPatchAdvisor(args, user) {
  const connId = Number(args.connection_id);
  const { rows: connRows } = await pool.query(
    `SELECT id, name, connection_type, host, port, service_name, username, encrypted_password
     FROM oracle_connections WHERE id = $1 AND user_id = $2`,
    [connId, user.id]
  );
  if (!connRows.length) return text(`Connection ${connId} not found or not accessible.`);
  const conn = connRows[0];

  // ── Try advisor_findings cache first ──────────────────────────────────────
  const { rows: afRows } = await pool.query(
    `SELECT recommendations, ai_summary, fetched_at, fetch_error
     FROM advisor_findings WHERE connection_id = $1`,
    [connId]
  );

  let currentPatch = null;
  let patchDate    = null;
  let recommendations = [];

  if (conn.connection_type !== 'proxy') {
    // No live query for direct TCP — use saved advisor findings only
    if (afRows.length && (afRows[0].recommendations || []).length) {
      const saved = afRows[0];
      const lines = [
        `# Patch Advisor — ${conn.name}`,
        `**Advisor data fetched:** ${new Date(saved.fetched_at).toISOString()}`,
        saved.ai_summary ? `\n${saved.ai_summary}` : '',
        '',
        '## Top Recommendations',
      ];
      (saved.recommendations || []).slice(0, 5).forEach((r, i) => {
        lines.push(`${i + 1}. ${r.finding || r.recommendation || r.message || JSON.stringify(r)}`);
      });
      return text(lines.join('\n'));
    }
    return text(
      `get_patch_advisor requires a proxy-agent connection for live queries. ` +
      `"${conn.name}" uses direct TCP and has no cached advisor data.`
    );
  }

  const alive = await Promise.race([
    channel.isAgentConnected(connId),
    new Promise(r => setTimeout(() => r(false), 5000)),
  ]);
  if (!alive) return text(`Agent for "${conn.name}" is offline.`);

  const creds = {
    service_name: conn.service_name, username: conn.username,
    password: conn.encrypted_password ? decrypt(conn.encrypted_password) : '',
    host: conn.host, port: conn.port || 1521, os_auth: false,
  };
  async function runQuery(sql) {
    const r = await channel.sendToAgent(connId, {
      method: 'POST', path: '/api/run_sql', body: { sql, ...creds },
    }, 30000);
    if (!r.body.success) throw new Error(r.body.error || 'Query failed');
    const cols = (r.body.columns || []).map(c => c.toLowerCase());
    return (r.body.rows || []).map(row => Object.fromEntries(cols.map((c, i) => [c, row[i] ?? null])));
  }

  // ── Detect Oracle version ──────────────────────────────────────────────────
  let rawVersion = '';
  try {
    const vRows = await runQuery(SQL_ORACLE_VERSION);
    rawVersion = vRows[0]?.version_full || '';
  } catch (_) {}

  let versionKey = null;
  if      (/^19\./.test(rawVersion)) versionKey = '19c';
  else if (/^21\./.test(rawVersion)) versionKey = '21c';
  else if (/^23\./.test(rawVersion) || /^26\./.test(rawVersion)) versionKey = '26ai';
  else if (/^12\.2/.test(rawVersion)) versionKey = '12.2';

  // ── Current patch level ────────────────────────────────────────────────────
  try {
    const pRows = await runQuery(SQL_CURRENT_PATCH);
    if (pRows[0]) {
      currentPatch = String(pRows[0].patch_id);
      patchDate    = pRows[0].action_time;
    }
  } catch (_) {}

  // ── Gap analysis vs PATCH_INDEX ───────────────────────────────────────────
  const allPatches = versionKey ? (PATCH_INDEX.rdbms?.[versionKey]?.release_update_patches || []) : [];
  const appliedSet = new Set(currentPatch ? [currentPatch] : []);
  let currentIdx = allPatches.length;
  for (let i = 0; i < allPatches.length; i++) {
    if (appliedSet.has(String(allPatches[i].patch_number))) { currentIdx = i; break; }
  }
  recommendations = allPatches.slice(0, currentIdx).slice(0, 5).map(p => ({
    patch_number: p.patch_number,
    label:        p.label,
    severity:     p.severity || 'HIGH',
    cvss:         p.cvss_max || null,
    cves:         p.cves || [],
    highlights:   p.highlights || '',
  }));

  const meta = PATCH_INDEX._meta || {};
  const lines = [
    `# Patch Advisor — ${conn.name}`,
    `**Oracle Version:** ${rawVersion || 'Unknown'}`,
    `**Current patch:** ${currentPatch || 'None detected'} ${patchDate ? `(applied ${patchDate})` : ''}`,
    `**Patch index:** refreshed ${meta.LAST_REFRESHED || 'unknown'}, next due ${meta.next_refresh_due || 'unknown'}`,
    '',
  ];

  if (!recommendations.length) {
    lines.push('✓ No missing patches found — DB is current for this Oracle version.');
  } else {
    lines.push(`## Top ${recommendations.length} Recommended Patches`);
    recommendations.forEach((p, i) => {
      const cveStr = p.cves.length ? ` — CVEs: ${p.cves.slice(0,3).join(', ')}${p.cves.length > 3 ? '…' : ''}` : '';
      lines.push(`${i + 1}. **Patch ${p.patch_number}** [${p.label}] (${p.severity}${p.cvss ? `, CVSS ${p.cvss}` : ''}${cveStr})`);
      if (p.highlights) lines.push(`   ${p.highlights}`);
    });
  }

  // Include cached AI summary if present
  if (afRows.length && afRows[0].ai_summary) {
    lines.push('');
    lines.push('## Advisor AI Summary (cached)');
    lines.push(afRows[0].ai_summary);
  }

  return text(lines.join('\n'));
}

// ── Tool: list_health_check_history ──────────────────────────────────────────

async function toolListHealthCheckHistory(args, user) {
  const connId = Number(args.connection_id);
  const limit  = Math.min(Number(args.limit) || 10, 30);

  const { rows: connRows } = await pool.query(
    `SELECT id, name FROM oracle_connections WHERE id = $1 AND user_id = $2`,
    [connId, user.id]
  );
  if (!connRows.length) return text(`Connection ${connId} not found or not accessible.`);
  const conn = connRows[0];

  const { rows } = await pool.query(
    `SELECT hc.id, hc.overall_score, hc.status, hc.created_at
     FROM health_checks hc
     WHERE hc.connection_id = $1
       AND hc.connection_id IN (SELECT id FROM oracle_connections WHERE user_id = $2)
     ORDER BY hc.created_at DESC
     LIMIT $3`,
    [connId, user.id, limit]
  );

  if (!rows.length) {
    return text(`No health check history for "${conn.name}" (connection ${connId}).`);
  }

  const lines = [`# Health Check History — ${conn.name} (last ${rows.length} runs)\n`];
  lines.push('| # | Score | Status | Run at | Report |');
  lines.push('|---|-------|--------|--------|--------|');
  rows.forEach((hc, i) => {
    const score = hc.overall_score != null ? `${hc.overall_score}/100` : 'N/A';
    const dt    = new Date(hc.created_at).toISOString().replace('T', ' ').slice(0, 19);
    lines.push(`| ${i + 1} | ${score} | ${hc.status || '—'} | ${dt} | [view](https://tunevault.app/report?id=${hc.id}) |`);
  });
  lines.push('');
  lines.push(`View reports: https://tunevault.app/reports`);
  return text(lines.join('\n'));
}

// ── Tool: list_ebs_patches ────────────────────────────────────────────────────

const PATCH_STATUS_LABEL = { S: 'Success', R: 'Running', F: 'Failed', N: 'Not started', X: 'Skipped', C: 'Completed' };
const decodePathStatus = v => PATCH_STATUS_LABEL[v] || v || '—';

async function toolListEbsPatches(args, user) {
  const connId = Number(args.connection_id);
  const limit  = Math.min(Number(args.limit) || 20, 100);

  const { rows: connRows } = await pool.query(
    `SELECT id, name, host, port, service_name, connection_type, username, encrypted_password,
            server_type, ebs_instance_name
     FROM oracle_connections WHERE id = $1 AND user_id = $2`,
    [connId, user.id]
  );
  if (!connRows.length) return text(`Connection ${connId} not found or not accessible.`);
  const conn = connRows[0];

  if (conn.connection_type !== 'proxy') {
    return text(`list_ebs_patches requires a proxy-agent connection. "${conn.name}" uses direct TCP.`);
  }

  const paired = await getPairedDbConn(conn, connId, user, 'list_ebs_patches');
  if (paired.error) return paired.error;
  const { sqlConn, sqlConnId, displayName } = paired;

  const alive = await Promise.race([
    channel.isAgentConnected(sqlConnId),
    new Promise(r => setTimeout(() => r(false), 5000)),
  ]);
  if (!alive) return text(`Agent for "${sqlConn.name}" is offline.`);

  const username = args.username || sqlConn.username || null;
  const password = args.password || (sqlConn.encrypted_password ? decrypt(sqlConn.encrypted_password) : '');
  if (!username) return text('No username provided. Pass username=apps and password=<apps_pwd>.');

  const sessionFilter = args.session_id ? `WHERE sp.adop_session_id = ${Number(args.session_id)}` : '';
  const sql = `
    SELECT
      sp.adop_session_id,
      sp.bug_number,
      sp.status,
      sp.node_name,
      sp.session_type,
      sp.clone_status,
      TO_CHAR(sp.start_date, 'YYYY-MM-DD HH24:MI:SS') AS start_date,
      TO_CHAR(sp.end_date,   'YYYY-MM-DD HH24:MI:SS') AS end_date,
      sp.driver_file_name
    FROM AD_ADOP_SESSION_PATCHES sp
    ${sessionFilter}
    ORDER BY sp.start_date DESC NULLS LAST
    FETCH FIRST ${limit} ROWS ONLY`;

  let resp;
  try {
    resp = await channel.sendToAgent(sqlConnId, {
      method: 'POST', path: '/api/run_sql',
      body: { sql, username, password,
              service_name: sqlConn.service_name, host: sqlConn.host,
              port: sqlConn.port || 1521, os_auth: false, max_rows: limit },
    }, 60000);
  } catch (e) { return text(`Patch query failed: ${e.message}`); }

  const result = resp.body;
  if (!result.success) {
    const err = result.error || '';
    if (/ORA-00942|does not exist/i.test(err)) {
      return text(`AD_ADOP_SESSION_PATCHES not accessible — pass username=apps and password=<apps_pwd>.`);
    }
    return text(`Patch query error: ${err || 'Unknown error'}`);
  }

  const cols = (result.columns || []).map(c => c.toLowerCase());
  const patchRows = (result.rows || []).map(row =>
    Object.fromEntries(cols.map((c, i) => [c, row[i] ?? null]))
  );

  if (!patchRows.length) {
    return text(`# EBS Patches — ${conn.name}\n\nNo patch records found in AD_ADOP_SESSION_PATCHES.`);
  }

  const lines = [`# EBS Patches Applied — ${displayName} (${patchRows.length} rows)\n`];
  lines.push('| Session | Bug/Patch | Status | Type | Clone Status | Started | Ended |');
  lines.push('|---------|-----------|--------|------|--------------|---------|-------|');
  patchRows.forEach(p => {
    lines.push(
      `| ${p.adop_session_id} | ${p.bug_number} | ${decodePathStatus(p.status)} ` +
      `| ${p.session_type || '—'} | ${p.clone_status || '—'} ` +
      `| ${p.start_date || '—'} | ${p.end_date || '—'} |`
    );
  });
  return text(lines.join('\n'));
}

// ── Tool: get_concurrent_manager_status ──────────────────────────────────────

const CM_CONTROL_CODE = {
  A: 'Activate',  D: 'Deactivate', E: 'Deactivate (after requests)',
  N: 'No action', T: 'Terminate',  V: 'Verify',
  X: 'Abort',
};

const SQL_CM_STATUS = `
  SELECT
    q.concurrent_queue_name,
    q.user_concurrent_queue_name,
    q.running_processes,
    q.max_processes,
    q.control_code,
    q.manager_type,
    q.enabled_flag,
    q.node_name
  FROM fnd_concurrent_queues_vl q
  WHERE q.enabled_flag = 'Y'
    AND q.max_processes > 0
  ORDER BY q.running_processes DESC, q.user_concurrent_queue_name`;

async function toolGetConcurrentManagerStatus(args, user) {
  const connId = Number(args.connection_id);
  const { rows: connRows } = await pool.query(
    `SELECT id, name, host, port, service_name, connection_type, username, encrypted_password,
            server_type, ebs_instance_name
     FROM oracle_connections WHERE id = $1 AND user_id = $2`,
    [connId, user.id]
  );
  if (!connRows.length) return text(`Connection ${connId} not found or not accessible.`);
  const conn = connRows[0];
  if (conn.connection_type !== 'proxy') return text(`get_concurrent_manager_status requires a proxy-agent connection.`);

  const paired = await getPairedDbConn(conn, connId, user, 'get_concurrent_manager_status');
  if (paired.error) return paired.error;
  const { sqlConn, sqlConnId, displayName } = paired;

  const alive = await Promise.race([channel.isAgentConnected(sqlConnId), new Promise(r => setTimeout(() => r(false), 5000))]);
  if (!alive) return text(`Agent for "${sqlConn.name}" is offline.`);

  const username = args.username || sqlConn.username || null;
  const password = args.password || (sqlConn.encrypted_password ? decrypt(sqlConn.encrypted_password) : '');
  if (!username) return text('No username provided. Pass username=apps and password=<apps_pwd>.');

  let resp;
  try {
    resp = await channel.sendToAgent(sqlConnId, {
      method: 'POST', path: '/api/run_sql',
      body: { sql: SQL_CM_STATUS, username, password,
              service_name: sqlConn.service_name, host: sqlConn.host,
              port: sqlConn.port || 1521, os_auth: false, max_rows: 100 },
    }, 60000);
  } catch (e) { return text(`CM query failed: ${e.message}`); }

  const result = resp.body;
  if (!result.success) {
    if (/ORA-00942|does not exist/i.test(result.error || '')) {
      return text(`FND_CONCURRENT_QUEUES_VL not accessible — pass username=apps and password=<apps_pwd>.`);
    }
    return text(`CM query error: ${result.error || 'Unknown error'}`);
  }

  const cols = (result.columns || []).map(c => c.toLowerCase());
  const managers = (result.rows || []).map(row => Object.fromEntries(cols.map((c, i) => [c, row[i] ?? null])));
  if (!managers.length) return text(`# Concurrent Manager Status — ${displayName}\n\nNo active managers found.`);

  const down    = managers.filter(m => Number(m.running_processes) === 0);
  const running = managers.filter(m => Number(m.running_processes) > 0);

  const lines = [
    `# Concurrent Manager Status — ${displayName}`,
    `**Total managers:** ${managers.length}  |  **Running:** ${running.length}  |  **Down:** ${down.length}`,
    '',
    '| Manager | Running | Max | Control | Node |',
    '|---------|---------|-----|---------|------|',
  ];
  managers.forEach(m => {
    const ctrl   = CM_CONTROL_CODE[m.control_code] || m.control_code || 'Normal';
    const status = Number(m.running_processes) === 0 ? '⚠ 0' : m.running_processes;
    lines.push(`| ${m.user_concurrent_queue_name || m.concurrent_queue_name} | ${status} | ${m.max_processes} | ${ctrl} | ${m.node_name || '—'} |`);
  });
  return text(lines.join('\n'));
}

// ── Tool: get_tablespace_usage ────────────────────────────────────────────────

const SQL_TS_USAGE = `
  SELECT
    m.tablespace_name,
    t.contents,
    t.status,
    ROUND(m.used_space        * t.block_size / 1048576, 1) AS used_mb,
    ROUND(m.tablespace_size   * t.block_size / 1048576, 1) AS total_mb,
    ROUND(m.used_percent, 1)                               AS pct_used
  FROM dba_tablespace_usage_metrics m
  JOIN dba_tablespaces t ON t.tablespace_name = m.tablespace_name
  WHERE t.contents != 'TEMPORARY' OR m.used_percent > 50
  ORDER BY m.used_percent DESC`;

const SQL_CDB_CHECK = `SELECT cdb FROM v$database`;

async function toolGetTablespaceUsage(args, user) {
  const connId = Number(args.connection_id);
  const { rows: connRows } = await pool.query(
    `SELECT id, name, host, port, service_name, connection_type, username, encrypted_password, server_type
     FROM oracle_connections WHERE id = $1 AND user_id = $2`,
    [connId, user.id]
  );
  if (!connRows.length) return text(`Connection ${connId} not found or not accessible.`);
  const conn = connRows[0];
  if (conn.connection_type !== 'proxy') return text(`get_tablespace_usage requires a proxy-agent connection.`);

  const alive = await Promise.race([channel.isAgentConnected(connId), new Promise(r => setTimeout(() => r(false), 5000))]);
  if (!alive) return text(`Agent for "${conn.name}" is offline.`);

  const agentCreds = {
    username: conn.username, password: conn.encrypted_password ? decrypt(conn.encrypted_password) : '',
    service_name: conn.service_name, host: conn.host, port: conn.port || 1521, os_auth: false,
  };

  // Probe CDB status in parallel with the main tablespace query.
  const [resp, cdbResp] = await Promise.all([
    channel.sendToAgent(connId, {
      method: 'POST', path: '/api/run_sql',
      body: { sql: SQL_TS_USAGE, ...agentCreds, max_rows: 200 },
    }, 60000).catch(e => ({ body: { success: false, error: e.message } })),
    channel.sendToAgent(connId, {
      method: 'POST', path: '/api/run_sql',
      body: { sql: SQL_CDB_CHECK, ...agentCreds, max_rows: 1 },
    }, 15000).catch(() => null),
  ]);

  // Derive CDB flag — non-blocking; ignore any error.
  let isCdb = false;
  if (cdbResp?.body?.success && cdbResp.body.rows?.length) {
    isCdb = String(cdbResp.body.rows[0][0]).toUpperCase() === 'YES';
  }

  const result = resp.body;
  if (!result.success) return text(`Tablespace query error: ${result.error || 'Unknown error'}`);

  const cols = (result.columns || []).map(c => c.toLowerCase());
  const tsList = (result.rows || []).map(row => Object.fromEntries(cols.map((c, i) => [c, row[i] ?? null])));
  if (!tsList.length) return text(`# Tablespace Usage — ${conn.name}\n\nNo tablespace data returned.`);

  const critical = tsList.filter(t => Number(t.pct_used) >= 95);
  const warning  = tsList.filter(t => Number(t.pct_used) >= 85 && Number(t.pct_used) < 95);

  const lines = [
    `# Tablespace Usage — ${conn.name}`,
    `**Total:** ${tsList.length}  |  **Critical (≥95%):** ${critical.length}  |  **Warning (≥85%):** ${warning.length}`,
    '',
    ...(isCdb ? ['> ℹ️ Connected to CDB root — showing all PDB tablespaces. Connect to a specific PDB service for scoped results.', ''] : []),
    '| Tablespace | Type | Status | Used MB | Total MB | % Used | Alert |',
    '|------------|------|--------|---------|----------|--------|-------|',
  ];
  tsList.forEach(t => {
    const pct   = Number(t.pct_used);
    const alert = pct >= 95 ? '🔴 CRITICAL' : pct >= 85 ? '🟡 WARNING' : '✓';
    lines.push(`| ${t.tablespace_name} | ${t.contents || '—'} | ${t.status || '—'} | ${t.used_mb} | ${t.total_mb} | ${t.pct_used}% | ${alert} |`);
  });
  return text(lines.join('\n'));
}

// ── Tool: get_blocking_sessions ───────────────────────────────────────────────

const SQL_BLOCKING = `
  SELECT
    b.sid                                          AS blocker_sid,
    b.serial#                                      AS blocker_serial,
    b.username                                     AS blocker_user,
    b.status                                       AS blocker_status,
    b.wait_class                                   AS blocker_wait_class,
    b.event                                        AS blocker_event,
    ROUND(b.seconds_in_wait)                       AS seconds_waiting,
    COUNT(w.sid)                                   AS blocked_count,
    SUBSTR(sq.sql_text, 1, 120)                    AS blocker_sql
  FROM v$session b
  JOIN v$session w ON w.blocking_session = b.sid
  LEFT JOIN v$sql sq ON sq.sql_id = b.sql_id AND sq.child_number = b.sql_child_number
  WHERE b.blocking_session IS NULL
  GROUP BY b.sid, b.serial#, b.username, b.status, b.wait_class, b.event,
           b.seconds_in_wait, sq.sql_text
  ORDER BY blocked_count DESC, b.seconds_in_wait DESC`;

async function toolGetBlockingSessions(args, user) {
  const connId = Number(args.connection_id);
  const { rows: connRows } = await pool.query(
    `SELECT id, name, host, port, service_name, connection_type, username, encrypted_password, server_type
     FROM oracle_connections WHERE id = $1 AND user_id = $2`,
    [connId, user.id]
  );
  if (!connRows.length) return text(`Connection ${connId} not found or not accessible.`);
  const conn = connRows[0];
  if (conn.connection_type !== 'proxy') return text(`get_blocking_sessions requires a proxy-agent connection.`);

  const alive = await Promise.race([channel.isAgentConnected(connId), new Promise(r => setTimeout(() => r(false), 5000))]);
  if (!alive) return text(`Agent for "${conn.name}" is offline.`);

  let resp;
  try {
    resp = await channel.sendToAgent(connId, {
      method: 'POST', path: '/api/run_sql',
      body: { sql: SQL_BLOCKING,
              username: conn.username, password: conn.encrypted_password ? decrypt(conn.encrypted_password) : '',
              service_name: conn.service_name, host: conn.host, port: conn.port || 1521,
              os_auth: false, max_rows: 50 },
    }, 60000);
  } catch (e) { return text(`Blocking session query failed: ${e.message}`); }

  const result = resp.body;
  if (!result.success) return text(`Blocking session query error: ${result.error || 'Unknown error'}`);

  const cols     = (result.columns || []).map(c => c.toLowerCase());
  const sessions = (result.rows || []).map(row => Object.fromEntries(cols.map((c, i) => [c, row[i] ?? null])));

  if (!sessions.length) {
    return text(`# Blocking Sessions — ${conn.name}\n\n✓ No blocking sessions detected.`);
  }

  const totalBlocked = sessions.reduce((sum, s) => sum + Number(s.blocked_count || 0), 0);
  const lines = [
    `# Blocking Sessions — ${conn.name}`,
    `**Blockers:** ${sessions.length}  |  **Total blocked sessions:** ${totalBlocked}`,
    '',
    '| Blocker SID | User | Status | Wait Event | Secs Waiting | Blocking | SQL (120 chars) |',
    '|-------------|------|--------|------------|-------------|----------|-----------------|',
  ];
  sessions.forEach(s => {
    const sql = (s.blocker_sql || '—').replace(/\s+/g, ' ').trim();
    lines.push(`| ${s.blocker_sid}/${s.blocker_serial} | ${s.blocker_user || '—'} | ${s.blocker_status || '—'} | ${s.blocker_event || '—'} | ${s.seconds_waiting || 0}s | ${s.blocked_count} session(s) | \`${sql}\` |`);
  });
  return text(lines.join('\n'));
}

// ── Tool: get_workflow_errors ─────────────────────────────────────────────────

async function toolGetWorkflowErrors(args, user) {
  const connId = Number(args.connection_id);
  const hours  = Math.min(Number(args.hours) || 24, 168);

  const { rows: connRows } = await pool.query(
    `SELECT id, name, host, port, service_name, connection_type, username, encrypted_password,
            server_type, ebs_instance_name
     FROM oracle_connections WHERE id = $1 AND user_id = $2`,
    [connId, user.id]
  );
  if (!connRows.length) return text(`Connection ${connId} not found or not accessible.`);
  const conn = connRows[0];
  if (conn.connection_type !== 'proxy') return text(`get_workflow_errors requires a proxy-agent connection.`);

  const paired = await getPairedDbConn(conn, connId, user, 'get_workflow_errors');
  if (paired.error) return paired.error;
  const { sqlConn, sqlConnId, displayName } = paired;

  const alive = await Promise.race([channel.isAgentConnected(sqlConnId), new Promise(r => setTimeout(() => r(false), 5000))]);
  if (!alive) return text(`Agent for "${sqlConn.name}" is offline.`);

  const username = args.username || sqlConn.username || null;
  const password = args.password || (sqlConn.encrypted_password ? decrypt(sqlConn.encrypted_password) : '');
  if (!username) return text('No username provided. Pass username=apps and password=<apps_pwd>.');

  const sql = `
    SELECT
      s.item_type,
      s.item_key,
      s.error_name,
      SUBSTR(s.error_message, 1, 200) AS error_message,
      TO_CHAR(s.begin_date, 'YYYY-MM-DD HH24:MI:SS') AS begin_date
    FROM wf_item_activity_statuses s
    WHERE s.activity_status = 'ERROR'
      AND s.begin_date >= SYSDATE - ${hours}/24
    ORDER BY s.begin_date DESC
    FETCH FIRST 100 ROWS ONLY`;

  let resp;
  try {
    resp = await channel.sendToAgent(sqlConnId, {
      method: 'POST', path: '/api/run_sql',
      body: { sql, username, password,
              service_name: sqlConn.service_name, host: sqlConn.host,
              port: sqlConn.port || 1521, os_auth: false, max_rows: 100 },
    }, 60000);
  } catch (e) { return text(`Workflow query failed: ${e.message}`); }

  const result = resp.body;
  if (!result.success) {
    if (/ORA-00942|does not exist/i.test(result.error || '')) {
      return text(`WF_ITEM_ACTIVITY_STATUSES not accessible — pass username=apps and password=<apps_pwd>.`);
    }
    return text(`Workflow query error: ${result.error || 'Unknown error'}`);
  }

  const cols   = (result.columns || []).map(c => c.toLowerCase());
  const errors = (result.rows || []).map(row => Object.fromEntries(cols.map((c, i) => [c, row[i] ?? null])));

  if (!errors.length) {
    return text(`# Workflow Errors — ${displayName}\n\n✓ No Workflow errors in the last ${hours} hour(s).`);
  }

  const byType = {};
  errors.forEach(e => { byType[e.item_type] = (byType[e.item_type] || 0) + 1; });

  const lines = [
    `# Workflow Errors — ${displayName}`,
    `**Period:** last ${hours} hour(s)  |  **Total errors:** ${errors.length}`,
    '',
    '**By item type:** ' + Object.entries(byType).map(([k,v]) => `${k}: ${v}`).join(', '),
    '',
    '| Item Type | Item Key | Error Name | Error Message | Begin Date |',
    '|-----------|----------|------------|---------------|------------|',
  ];
  errors.forEach(e => {
    const msg = (e.error_message || '—').replace(/\n/g, ' ').slice(0, 80);
    lines.push(`| ${e.item_type} | ${e.item_key} | ${e.error_name || '—'} | ${msg} | ${e.begin_date} |`);
  });
  return text(lines.join('\n'));
}

// ── MCP method dispatcher ─────────────────────────────────────────────────────

async function dispatchMethod(method, params, user) {
  switch (method) {
    case 'initialize':
      return {
        protocolVersion: MCP_VERSION,
        capabilities: { tools: {} },
        serverInfo: { name: 'tunevault', version: SERVER_VERSION },
      };
    case 'ping':
      return {};
    case 'tools/list':
      return { tools: TOOLS };
    case 'tools/call': {
      const name = (params || {}).name;
      const args = (params || {}).arguments || {};
      switch (name) {
        case 'list_connections':           return toolListConnections(args, user);
        case 'run_health_check':           return toolRunHealthCheck(args, user);
        case 'get_latest_health_check':    return toolGetLatestHealthCheck(args, user);
        case 'security_posture_scan':      return toolSecurityPostureScan(args, user);
        case 'list_tuneops_tickets':       return toolListTuneopsTickets(args, user);
        case 'run_sql_query':              return toolRunSqlQuery(args, user);
        case 'get_vulnerability_exposure': return toolGetVulnerabilityExposure(args, user);
        case 'get_resilience_status':      return toolGetResilienceStatus(args, user);
        case 'create_tuneops_ticket':      return toolCreateTuneopsTicket(args, user);
        case 'get_fleet_status':           return toolGetFleetStatus(args, user);
        case 'get_adop_status':            return toolGetAdopStatus(args, user);
        case 'run_ebs_health_check':       return toolRunEbsHealthCheck(args, user);
        case 'get_security_posture':       return toolGetSecurityPosture(args, user);
        case 'get_patch_advisor':          return toolGetPatchAdvisor(args, user);
        case 'list_health_check_history':  return toolListHealthCheckHistory(args, user);
        case 'list_ebs_patches':                  return toolListEbsPatches(args, user);
        case 'get_concurrent_manager_status':     return toolGetConcurrentManagerStatus(args, user);
        case 'get_tablespace_usage':              return toolGetTablespaceUsage(args, user);
        case 'get_blocking_sessions':             return toolGetBlockingSessions(args, user);
        case 'get_workflow_errors':               return toolGetWorkflowErrors(args, user);
        default: { const e = new Error(`Unknown tool: ${name}`); e.code = -32601; throw e; }
      }
    }
    default:
      if (method.startsWith('notifications/')) return null;
      { const e = new Error(`Method not found: ${method}`); e.code = -32601; throw e; }
  }
}

// ── Express routes ────────────────────────────────────────────────────────────

// GET /mcp/setup — human-readable setup guide (canonical URL)
router.get('/setup', (req, res) => {
  res.sendFile(path.join(__dirname, '..', 'public', 'mcp.html'));
});

// GET /mcp/docs — MCP tool reference page
router.get('/docs', (req, res) => {
  res.sendFile(path.join(__dirname, '..', 'public', 'mcp-docs.html'));
});

// GET /mcp — discovery JSON, no auth required
router.get('/', (req, res) => {
  res.json({
    name:            'TuneVault MCP Server',
    version:         SERVER_VERSION,
    protocolVersion: MCP_VERSION,
    description:     '15 Oracle DBA tools for Claude Desktop and Claude Code — health checks, EBS app-tier health, security posture, patch advisor, vulnerability exposure, SQL queries, ADOP status, fleet overview, TuneOps tickets, and more.',
    endpoint:        '/mcp',
    auth:            'Bearer <api-key>  (generate at /settings/api, Team plan+)',
    setup:           '/mcp/setup',
    tools:           TOOLS.map(t => ({ name: t.name, description: t.description })),
  });
});

// POST /mcp — JSON-RPC 2.0 MCP endpoint
router.post('/', async (req, res) => {
  const rawKey = (req.headers.authorization || '').replace(/^Bearer\s+/i, '').trim();
  if (!rawKey) {
    return res.status(401).json(rpcErr(null, -32001, 'Missing API key. Generate one at https://tunevault.app/settings/api'));
  }

  const user = await getUserByApiKey(rawKey).catch(() => null);
  if (!user) {
    return res.status(401).json(rpcErr(null, -32001, 'Invalid or revoked API key.'));
  }

  const body      = req.body;
  const isBatch   = Array.isArray(body);
  const messages  = isBatch ? body : [body];
  const responses = [];

  for (const msg of messages) {
    const { jsonrpc, id, method, params } = msg || {};
    if (jsonrpc !== '2.0' || !method) {
      responses.push(rpcErr(id ?? null, -32600, 'Invalid JSON-RPC 2.0 request'));
      continue;
    }
    const isNotification = id === undefined || id === null;
    try {
      const result = await dispatchMethod(method, params, user);
      if (!isNotification && result !== null) responses.push(rpcOk(id, result));
    } catch (e) {
      if (!isNotification) responses.push(rpcErr(id, typeof e.code === 'number' ? e.code : -32603, e.message || 'Internal error'));
    }
  }

  if (responses.length === 0) return res.status(204).end();
  res.json(isBatch ? responses : responses[0]);
});

module.exports = router;
