/**
 * routes/dba-agent.js — standalone spy-themed DBA Agent (separate from TuneBot).
 *
 * Owns: GET /agent (mission-terminal UI), GET /api/agent/context (clearance +
 *       connection list), POST /api/agent/chat (Professional+ gated, RAG + a
 *       5-tool autonomous read-only investigation loop with two SSE event types:
 *       {thinking} intel lines and {token} answer deltas).
 * Does NOT own: TuneBot (routes/tunebot.js) — deliberately decoupled; the agent
 *       installer router (routes/agent.js) — different feature at /api/agent/*.
 *
 * Mounted at '/' in server.js (full paths declared here) so /api/agent/chat and
 * /api/agent/context sit alongside the installer router without collision.
 */

'use strict';

const express = require('express');
const path = require('path');
const pool = require('../db/index');
const { requireAuth, ADMIN_EMAILS } = require('../middleware/auth');
const { searchDocChunks } = require('../db/oracle-docs');
const { trackEvent } = require('../db/analytics');
// Reuse the SQL Console's validated, proxy/direct-TCP/SSH-aware execution path.
const { getConnParams, executeSQL, blockNonSelect } = require('./console');
const { getAdvisorFindings } = require('../db/advisor-findings');
const { resolveTier } = require('../services/tier-limits');
const { getUserPlanTier } = require('../db/tier-usage');

const router = express.Router();

// ── Tier gating: Professional+ (internal `team` and up) ────────────────────────
// Marketing "Professional" == internal tier `team`; "Professional+" therefore
// means tier >= team (everything above free/`starter`). Admins bypass.
const TIER_RANK = { starter: 0, team: 1, business: 2, enterprise: 3 };
const MIN_RANK = TIER_RANK.team;

async function resolveClearance(user) {
  const isAdmin = ADMIN_EMAILS.has((user.email || '').toLowerCase());
  if (isAdmin) return { tier: 'enterprise', cleared: true, upgrade_to: null };
  const planTier = await getUserPlanTier(user.id);
  const tier = resolveTier(planTier, false);
  const cleared = (TIER_RANK[tier] ?? 0) >= MIN_RANK;
  return { tier, cleared, upgrade_to: cleared ? null : 'team' };
}

// Gate for the chat endpoint. Unlike the cap middlewares (which fail OPEN), a paid
// feature fails CLOSED — a quota-check error must not hand out the feature for free.
async function requireProfessionalTier(req, res, next) {
  try {
    const { cleared, tier, upgrade_to } = await resolveClearance(req.user);
    if (cleared) return next();
    return res.status(402).json({ error: 'clearance_denied', tier, upgrade_to });
  } catch (err) {
    console.error('[dba-agent] clearance check failed:', err.message);
    return res.status(402).json({ error: 'clearance_denied', tier: 'unknown', upgrade_to: 'team' });
  }
}

// ── Rate limit: 10 chat calls/min per user ─────────────────────────────────────
const chatCalls = new Map();
function chatRateLimit(req, res, next) {
  const key = `${req.user.id}`;
  const now = Date.now();
  const recent = (chatCalls.get(key) || []).filter(t => now - t < 60000);
  if (recent.length >= 10) {
    return res.status(429).json({ error: 'Too many requests — stand down a moment before redeploying.' });
  }
  recent.push(now);
  chatCalls.set(key, recent);
  next();
}

// ── RAG retrieval (same pattern as TuneBot; kept local so /agent is decoupled) ──
let _openaiClient = null;
function getOpenAIClient() {
  if (!process.env.OPENAI_API_KEY) return null;
  if (!_openaiClient) {
    const OpenAI = require('openai');
    _openaiClient = new OpenAI({ apiKey: process.env.OPENAI_API_KEY });
  }
  return _openaiClient;
}

async function retrieveDocChunks(query) {
  const client = getOpenAIClient();
  if (!client) return [];
  const res = await client.embeddings.create({
    model: 'text-embedding-3-small',
    input: query,
    dimensions: 1536,
  });
  const embedding = res.data?.[0]?.embedding;
  if (!embedding) return [];
  return searchDocChunks(embedding, { limit: 5, minSimilarity: 0.3 });
}

function formatDocContext(chunks) {
  const lines = ['## Oracle Documentation (retrieved for this mission)'];
  lines.push('Excerpts from official Oracle docs most relevant to the brief. Cite the source doc name when you use one; otherwise rely on your own Oracle DBA expertise.');
  lines.push('');
  chunks.forEach((c, i) => {
    const src = c.doc_name.replace(/\.pdf$/i, '');
    lines.push(`### [${i + 1}] ${src}`);
    lines.push((c.content || '').slice(0, 1200).trim());
    lines.push('');
  });
  return lines.join('\n');
}

// ── DBA agent tools (all read-only) ────────────────────────────────────────────
const DBA_AGENT_TOOLS = [
  {
    name: 'run_sql',
    description: 'Run a read-only SQL query (SELECT/WITH only) against the target Oracle database and return the rows. Use for ad-hoc investigation not covered by the other tools. DDL/DML is rejected.',
    input_schema: {
      type: 'object',
      properties: { sql: { type: 'string', description: 'A single read-only SELECT or WITH query. No trailing semicolon needed.' } },
      required: ['sql'],
    },
  },
  {
    name: 'get_health_check',
    description: 'Fetch the latest TuneVault health check findings (red/critical and amber/warning) for the target connection. Use first to see known problems before querying the database.',
    input_schema: { type: 'object', properties: {} },
  },
  {
    name: 'get_addm_findings',
    description: 'Fetch the latest ADDM (Automatic Database Diagnostic Monitor) findings and AI summary for the target. Use to see Oracle-diagnosed performance root causes.',
    input_schema: { type: 'object', properties: {} },
  },
  {
    name: 'get_active_sessions',
    description: 'List currently active USER sessions with wait class, wait event, and SQL id. Use to see what is running now and whether sessions are stuck on waits or blocking.',
    input_schema: { type: 'object', properties: {} },
  },
  {
    name: 'get_top_sql',
    description: 'Return the top 10 SQL statements by total elapsed time from v$sql (with executions and buffer gets). Use to find the heaviest queries when investigating slowness.',
    input_schema: { type: 'object', properties: {} },
  },
];

// User-facing intel line emitted before each tool runs.
const THINKING_MESSAGES = {
  run_sql:             '🔍 Running SQL query...',
  get_active_sessions: '👤 Checking active sessions...',
  get_top_sql:         '📊 Pulling top SQL by elapsed time...',
  get_health_check:    '🏥 Reviewing health check findings...',
  get_addm_findings:   '🧠 Analyzing ADDM diagnostics...',
};

const ACTIVE_SESSIONS_SQL =
  "SELECT sid, serial#, username, status, wait_class, event, sql_id " +
  "FROM v$session WHERE type='USER' AND status='ACTIVE'";

const TOP_SQL_SQL =
  "SELECT * FROM (" +
  "SELECT sql_id, executions, elapsed_time, buffer_gets, " +
  "SUBSTR(sql_text,1,200) AS sql_text " +
  "FROM v$sql ORDER BY elapsed_time DESC" +
  ") WHERE ROWNUM <= 10";

function formatSqlResult(result) {
  if (!result) return 'No result.';
  if (result.error) return `Query error: ${result.error}`;
  const cols = result.cols || [];
  const rows = result.rows || [];
  if (!rows.length) return `(0 rows) columns: ${cols.join(', ') || '—'}`;
  const capped = rows.slice(0, 50);
  const lines = capped.map(r => cols.map(c => `${c}=${r[c] ?? ''}`).join(' | '));
  let out = `${result.rowCount ?? rows.length} row(s):\n` + lines.join('\n');
  if (rows.length > capped.length) out += `\n… (${rows.length - capped.length} more rows truncated)`;
  return out.slice(0, 6000);
}

/**
 * Execute one tool. Returns { text, isError } — never throws, so a failure is
 * reported back to Claude as a tool_result instead of crashing the loop.
 */
async function executeAgentTool(name, input, ctx) {
  try {
    switch (name) {
      case 'run_sql': {
        const sql = (input && input.sql || '').trim();
        if (!sql) return { text: 'No SQL provided.', isError: true };
        const blocked = blockNonSelect(sql);
        if (blocked) return { text: `Blocked: ${blocked}`, isError: true };
        const result = await executeSQL(ctx.connParams, sql, 100, 30000, ctx.userId);
        return { text: formatSqlResult(result), isError: !!result.error };
      }
      case 'get_active_sessions': {
        const result = await executeSQL(ctx.connParams, ACTIVE_SESSIONS_SQL, 100, 30000, ctx.userId);
        return { text: formatSqlResult(result), isError: !!result.error };
      }
      case 'get_top_sql': {
        const result = await executeSQL(ctx.connParams, TOP_SQL_SQL, 100, 30000, ctx.userId);
        return { text: formatSqlResult(result), isError: !!result.error };
      }
      case 'get_health_check': {
        const { rows } = await pool.query(
          `SELECT check_id, check_category, status, metric_name, metric_value, metric_unit,
                  ai_summary, recommendation
             FROM check_results
            WHERE connection_id = :p1
              AND run_id = (SELECT run_id FROM check_results
                             WHERE connection_id = :p1
                             ORDER BY executed_at DESC FETCH FIRST 1 ROWS ONLY)
              AND status IN ('red','amber')
            ORDER BY CASE status WHEN 'red' THEN 0 WHEN 'amber' THEN 1 ELSE 2 END,
                     check_category, check_id
            FETCH FIRST 30 ROWS ONLY`,
          { p1: ctx.connectionId }
        );
        if (!rows.length) return { text: 'No red/amber findings in the latest health check (or no health check has run).', isError: false };
        const lines = rows.map(f => {
          const val = (f.metric_value !== null && f.metric_value !== undefined)
            ? ` — ${f.metric_value}${f.metric_unit ? ' ' + f.metric_unit : ''}` : '';
          return `[${f.status}] ${f.check_id} (${f.check_category})${val}: ${f.ai_summary || ''}${f.recommendation ? ' → ' + f.recommendation : ''}`;
        });
        return { text: lines.join('\n').slice(0, 6000), isError: false };
      }
      case 'get_addm_findings': {
        const row = await getAdvisorFindings(ctx.connectionId);
        if (!row) return { text: 'No ADDM/advisor snapshot captured for this connection yet. Run the Performance Advisor to populate it.', isError: false };
        const findings = Array.isArray(row.findings) ? row.findings : [];
        const parts = [];
        if (row.ai_summary) parts.push(`AI summary: ${row.ai_summary}`);
        if (row.licensed === false) parts.push(`(Diagnostics Pack not licensed: ${row.not_licensed_reason || 'unknown'})`);
        if (findings.length) {
          parts.push('ADDM findings:');
          findings.slice(0, 15).forEach(f => {
            parts.push(`- ${f.finding || f.name || f.message || JSON.stringify(f)}${f.impact ? ` (impact: ${f.impact})` : ''}`);
          });
        } else if (!row.ai_summary) {
          parts.push('No ADDM findings recorded.');
        }
        return { text: parts.join('\n').slice(0, 6000), isError: false };
      }
      default:
        return { text: `Unknown tool: ${name}`, isError: true };
    }
  } catch (err) {
    return { text: `Tool "${name}" failed: ${err.message}`, isError: true };
  }
}

// ── System prompt (spy/007 persona) ────────────────────────────────────────────
function buildSystemPrompt(connName, docContext) {
  const lines = [
    'You are AGENT-007, an elite autonomous Oracle DBA field agent operating inside TuneVault.',
    'You investigate the target database with read-only tools, then deliver a concise mission report.',
    'Persona: terse, confident, tactical — but always technically precise and correct. A little spy flavor is fine; accuracy is non-negotiable.',
    'Format the final answer in markdown. Lead with the finding (the root cause / answer), then the evidence, then the recommended action.',
    '',
    '## Investigation doctrine',
    'You have read-only tools to inspect THIS target Oracle database: get_health_check, get_addm_findings, get_active_sessions, get_top_sql, and run_sql (SELECT/WITH only).',
    'When given a mission brief (e.g. "why is my DB slow", "find blocking sessions"), gather intel with the tools BEFORE answering: check health findings and ADDM first, then query live state, chaining calls to reach a root cause. Ground every claim in what the tools actually returned — never fabricate metrics. Keep it focused; a few decisive calls beat many.',
    '',
    `## Target database\n- Connection: ${connName || 'the selected connection'}`,
  ];
  if (docContext) { lines.push(''); lines.push(docContext); }
  return lines.join('\n');
}

// ── GET /api/agent/context — clearance + connection roster ─────────────────────
router.get('/api/agent/context', requireAuth, async (req, res) => {
  try {
    const clearance = await resolveClearance(req.user);
    if (!clearance.cleared) {
      return res.json({ cleared: false, tier: clearance.tier, upgrade_to: clearance.upgrade_to, connections: [] });
    }
    const { rows } = await pool.query(
      `SELECT id, name FROM oracle_connections
        WHERE user_id = :p1 OR user_id IS NULL
        ORDER BY created_at DESC`,
      { p1: req.user.id }
    );
    const requested = req.query.connectionId ? parseInt(req.query.connectionId, 10) : null;
    const active = (requested && rows.find(c => c.id === requested)) || rows[0] || null;
    return res.json({
      cleared: true,
      tier: clearance.tier,
      connections: rows,
      activeConnection: active ? { id: active.id, name: active.name } : null,
    });
  } catch (err) {
    console.error('[dba-agent] /context error:', err.message);
    res.status(500).json({ error: 'Failed to load agent context' });
  }
});

// ── POST /api/agent/chat — gated, streaming tool-calling investigation ─────────
router.post('/api/agent/chat', requireAuth, requireProfessionalTier, chatRateLimit, async (req, res) => {
  const { message, connectionId, history = [] } = req.body || {};
  if (!message || typeof message !== 'string' || message.trim().length < 2) {
    return res.status(400).json({ error: 'Mission brief is required' });
  }
  if (!process.env.ANTHROPIC_API_KEY) {
    return res.status(503).json({ error: 'DBA Agent is not configured — ANTHROPIC_API_KEY is not set' });
  }

  // Load the target connection (user-scoped → ownership enforced). Tools require it.
  const connId = connectionId ? parseInt(connectionId, 10) : null;
  let connParams = null;
  if (connId) {
    try { connParams = await getConnParams(connId, req.user.id); }
    catch (e) { console.log('[dba-agent] connParams load failed:', e.message); }
  }
  if (!connParams) {
    return res.status(400).json({ error: 'Select a valid target database you own before deploying the agent.' });
  }

  // Fire-and-forget: record a dba_agent_query event (drives onboarding "try_dba_agent").
  trackEvent({ eventName: 'dba_agent_query', userId: req.user.id, properties: { connection_id: connId } });

  // RAG (best-effort) → doc context in the system prompt.
  let docContext = '';
  try {
    const chunks = await retrieveDocChunks(message.trim());
    if (chunks.length) {
      docContext = formatDocContext(chunks);
      console.log(`[dba-agent] retrieved ${chunks.length} doc chunk(s)`);
    }
  } catch (err) {
    console.log('[dba-agent] doc retrieval skipped:', err.message);
  }

  const systemPrompt = buildSystemPrompt(connParams.name, docContext);
  const recentHistory = (Array.isArray(history) ? history : []).slice(-6);
  const messages = [
    ...recentHistory.map(t => ({ role: t.role === 'user' ? 'user' : 'assistant', content: t.content || t.text || '' })),
    { role: 'user', content: message.trim() },
  ];
  const toolCtx = { connParams, connectionId: connId, userId: req.user.id };

  res.setHeader('Content-Type', 'text/event-stream');
  res.setHeader('Cache-Control', 'no-cache');
  res.setHeader('Connection', 'keep-alive');
  res.flushHeaders();

  const MAX_ITERS = 5;
  try {
    const Anthropic = require('@anthropic-ai/sdk');
    const anthropic = new Anthropic.default({ apiKey: process.env.ANTHROPIC_API_KEY });

    for (let i = 0; i < MAX_ITERS; i++) {
      const streamOpts = { model: 'claude-sonnet-4-6', max_tokens: 900, system: systemPrompt, messages };
      if (i < MAX_ITERS - 1) streamOpts.tools = DBA_AGENT_TOOLS; // drop tools on final turn → force an answer

      const stream = anthropic.messages.stream(streamOpts);
      for await (const chunk of stream) {
        if (chunk.type === 'content_block_delta' && chunk.delta?.type === 'text_delta') {
          res.write(`data: ${JSON.stringify({ token: chunk.delta.text })}\n\n`);
        }
      }
      const finalMsg = await stream.finalMessage();
      messages.push({ role: 'assistant', content: finalMsg.content });
      if (finalMsg.stop_reason !== 'tool_use') break; // final answer delivered

      // Emit an intel line before each tool, run it, then feed all results back in one user message.
      const toolUses = finalMsg.content.filter(b => b.type === 'tool_use');
      const toolResults = [];
      for (const tu of toolUses) {
        res.write(`data: ${JSON.stringify({ thinking: THINKING_MESSAGES[tu.name] || '⚡ Working...' })}\n\n`);
        const { text, isError } = await executeAgentTool(tu.name, tu.input, toolCtx);
        console.log(`[dba-agent] tool ${tu.name}${isError ? ' (error)' : ''} conn=${connId}`);
        toolResults.push({ type: 'tool_result', tool_use_id: tu.id, content: text, is_error: isError });
      }
      messages.push({ role: 'user', content: toolResults });
    }

    res.write(`data: ${JSON.stringify({ done: true })}\n\n`);
    res.end();
  } catch (err) {
    console.error('[dba-agent] stream error:', err.message);
    res.write(`data: ${JSON.stringify({ error: 'Agent comms lost — AI temporarily unavailable' })}\n\n`);
    res.end();
  }
});

// ── GET /agent — mission terminal UI ───────────────────────────────────────────
router.get('/agent', requireAuth, (req, res) => {
  res.sendFile(path.join(__dirname, '..', 'public', 'agent.html'));
});

module.exports = router;
