/**
 * routes/tunebot.js — TuneBot context-aware chat API.
 *
 * Owns: GET /context (connection metadata + latest health check + tickets),
 *       POST /chat (AI response with injected Oracle environment context).
 * Does NOT own: health check execution, connection CRUD, ticket management,
 *               TuneBot UI (public/tunebot.js), OpenAI client initialisation (server.js).
 *
 * Context is scoped to the authenticated user's selected connection.
 * If no health check exists yet, /context returns { noHealthCheck: true }.
 */

'use strict';

const express = require('express');
const pool = require('../db/index');
const { requireAuth } = require('../middleware/auth');
const { searchDocChunks } = require('../db/oracle-docs');
const { trackEvent } = require('../db/analytics');

const router = express.Router();

// ── Oracle docs RAG retrieval ─────────────────────────────────────────────────
// Embeds the user's question with the SAME model used at ingestion
// (text-embedding-3-small, 1536 dims — see scripts/ingest-oracle-docs.js) and
// pulls the closest documentation chunks from oracle_doc_chunks. Best-effort:
// returns [] if OpenAI isn't configured, the table is absent, or anything fails,
// so TuneBot degrades to connection-context-only rather than erroring.
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 });
}

/** Render retrieved chunks as a citable markdown block for the system prompt. */
function formatDocContext(chunks) {
  const lines = ['## Oracle Documentation (retrieved for this question)'];
  lines.push('Excerpts from official Oracle docs most relevant to the question. Cite the source doc name when you use one; if they do not cover the question, rely on your own Oracle DBA knowledge.');
  lines.push('');
  chunks.forEach((c, i) => {
    const src = c.doc_name.replace(/\.pdf$/i, '');
    // Cap each excerpt so a few chunks can't blow up the prompt.
    const excerpt = (c.content || '').slice(0, 1200).trim();
    lines.push(`### [${i + 1}] ${src}`);
    lines.push(excerpt);
    lines.push('');
  });
  return lines.join('\n');
}

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

// ── GET /api/tunebot/context?connectionId=X ───────────────────────────────────
// Returns: connection metadata, latest health check summary, top critical findings,
//          open/recent TuneOps tickets. All scoped to the authenticated user.
router.get('/context', requireAuth, async (req, res) => {
  try {
    const userId = req.user.id;
    const connectionId = req.query.connectionId ? parseInt(req.query.connectionId, 10) : null;

    // ── 1. Connections list (user-scoped) ─────────────────────────────────────
    const connResult = await pool.query(
      `SELECT id, name, host, port, service_name, oracle_version, connection_type, is_ebs, ebs_checks_enabled
       FROM oracle_connections
       WHERE user_id = $1 OR user_id IS NULL
       ORDER BY created_at DESC`,
      [userId]
    );
    const connections = connResult.rows;

    // Determine active connection
    let activeConn = null;
    if (connectionId) {
      activeConn = connections.find(c => c.id === connectionId) || null;
    }
    if (!activeConn && connections.length > 0) {
      activeConn = connections[0]; // Default to most recent
    }

    if (!activeConn) {
      return res.json({
        noConnections: true,
        connections: [],
        message: 'No Oracle connections found. Add a connection to get started.'
      });
    }

    // ── 2. Latest completed health check for active connection ────────────────
    // health_checks does not have is_ebs — EBS detection comes from oracle_connections
    const hcResult = await pool.query(
      `SELECT id, overall_score, summary_text, top_action, ebs_summary, ebs_action,
              ai_recommendations, created_at, completed_at, status
       FROM health_checks
       WHERE connection_id = $1
         AND (user_id = $2 OR user_id IS NULL)
         AND status = 'completed'
       ORDER BY completed_at DESC LIMIT 1`,
      [activeConn.id, userId]
    );

    if (hcResult.rows.length === 0) {
      return res.json({
        noHealthCheck: true,
        activeConnection: {
          id: activeConn.id,
          name: activeConn.name,
          host: activeConn.host || null,
          oracle_version: activeConn.oracle_version,
          connection_type: activeConn.connection_type,
          is_ebs: activeConn.is_ebs
        },
        connections: connections.map(c => ({ id: c.id, name: c.name })),
        message: `No completed health check found for "${activeConn.name}". Run a health check first.`
      });
    }

    const hc = hcResult.rows[0];

    // ── 3. Critical + amber check results from the latest run ─────────────────
    // check_results.run_id is a UUID (crypto.randomUUID) unrelated to health_checks.id (SERIAL).
    // Look up findings by connection_id + latest run_id for that connection.
    const findingsResult = await pool.query(
      `SELECT check_id, check_category, status, metric_name, metric_value, metric_unit,
              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 25`,
      [activeConn.id]
    );

    // ── 4. Open/recent TuneOps tickets for this connection ────────────────────
    let tickets = [];
    try {
      // company_id from user email domain — matches tuneops.js pattern
      const companyId = req.user.company_domain || req.user.email.split('@')[1] || `user_${userId}`;
      const ticketResult = await pool.query(
        `SELECT ticket_number, title, severity, status, created_at, updated_at
         FROM tuneops_tickets
         WHERE company_id = $1
           AND connection_id = $2
           AND status NOT IN ('RESOLVED')
         ORDER BY
           CASE severity WHEN 'critical' THEN 0 WHEN 'warning' THEN 1 ELSE 2 END,
           updated_at DESC
         LIMIT 10`,
        [companyId, activeConn.id]
      );
      tickets = ticketResult.rows;
    } catch (e) {
      // tuneops_tickets may not exist in all environments
      console.log('[tunebot] tuneops_tickets not available:', e.message);
    }

    return res.json({
      activeConnection: {
        id: activeConn.id,
        name: activeConn.name,
        host: activeConn.host || null,
        oracle_version: activeConn.oracle_version || 'Unknown',
        connection_type: activeConn.connection_type || 'direct',
        is_ebs: !!(activeConn.is_ebs || activeConn.ebs_checks_enabled)
      },
      connections: connections.map(c => ({ id: c.id, name: c.name })),
      healthCheck: {
        id: hc.id,
        score: hc.overall_score,
        completedAt: hc.completed_at,
        summary: hc.summary_text,
        topAction: hc.top_action,
        ebsSummary: hc.ebs_summary,
        ebsAction: hc.ebs_action,
        aiRecommendations: hc.ai_recommendations,
        findings: findingsResult.rows
      },
      tickets
    });
  } catch (err) {
    console.error('[tunebot] /context error:', err.message);
    res.status(500).json({ error: 'Failed to load context' });
  }
});

// ── POST /api/tunebot/chat ────────────────────────────────────────────────────
// Body: { message: string, connectionId?: number, context?: object, history?: [] }
// Streams SSE: data: {"token":"..."}\n\n  …  data: {"done":true}\n\n
router.post('/chat', requireAuth, chatRateLimit, async (req, res) => {
  const { message, context, history = [] } = req.body;
  if (!message || typeof message !== 'string' || message.trim().length < 2) {
    return res.status(400).json({ error: 'Message is required' });
  }
  // TuneBot is AI-powered and optional (e.g. self-hosted without an Anthropic key).
  // Return a clean 503 before opening the SSE stream / constructing the client, so
  // the SDK never fails with a raw auth error.
  if (!process.env.ANTHROPIC_API_KEY) {
    return res.status(503).json({ error: 'TuneBot is not configured — ANTHROPIC_API_KEY is not set' });
  }

  // Fire-and-forget: record a tunebot_query event (drives the onboarding
  // "try_tunebot" step). trackEvent swallows its own errors — never awaited.
  trackEvent({ eventName: 'tunebot_query', userId: req.user.id });

  // RAG: pull the most relevant Oracle-doc excerpts for this question (best-effort).
  let docContext = '';
  try {
    const chunks = await retrieveDocChunks(message.trim());
    if (chunks.length) {
      docContext = formatDocContext(chunks);
      console.log(`[tunebot] retrieved ${chunks.length} doc chunk(s) (top sim ${Number(chunks[0].similarity).toFixed(3)})`);
    }
  } catch (err) {
    console.log('[tunebot] doc retrieval skipped:', err.message);
  }

  const systemPrompt = buildSystemPrompt(context, 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() }
  ];
  res.setHeader('Content-Type', 'text/event-stream');
  res.setHeader('Cache-Control', 'no-cache');
  res.setHeader('Connection', 'keep-alive');
  res.flushHeaders();
  try {
    const Anthropic = require('@anthropic-ai/sdk');
    const anthropic = new Anthropic.default({ apiKey: process.env.ANTHROPIC_API_KEY });
    const stream = anthropic.messages.stream({
      model: 'claude-sonnet-4-6',
      max_tokens: 800,
      system: systemPrompt,
      messages,
    });
    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`);
      }
    }
    res.write(`data: ${JSON.stringify({ done: true })}\n\n`);
    res.end();
  } catch (err) {
    console.error('[tunebot] stream error:', err.message);
    res.write(`data: ${JSON.stringify({ error: 'AI temporarily unavailable' })}\n\n`);
    res.end();
  }
});

// ── Prompt builder ────────────────────────────────────────────────────────────

const TUNEVAULT_PRODUCT_KNOWLEDGE = `## TuneVault Product Knowledge
- **What it does:** Connects to Oracle databases and runs 200+ health checks across 13 DB categories + 5 EBS categories. Results display in a 9-tab dashboard with PDF/XLSX export.
- **Agent install:** \`curl -fsSL https://tunevault.app/install.sh | sudo TUNEVAULT_TOKEN=<token> TUNEVAULT_API=https://tunevault.app bash\`
- **Get a token:** Go to /connections/new in the TuneVault UI — the token is generated there.
- **Agent:** A Python service (oracle-proxy.py) that runs on the Oracle server. Installs to /opt/tunevault/. Managed by systemd as tunevault-agent.service.
- **Supported OS:** Oracle Linux (OEL) 7/8/9, RHEL 7/8/9, Amazon Linux 2/2023, Ubuntu/Debian.
- **Connectivity:** Outbound polling only — no inbound ports, no firewall rules, no VPN required. Agent polls the TuneVault cloud every 25s.
- **DB user:** Creates a least-privilege \`tunevault_reader\` role with SELECT_CATALOG_ROLE. Script at /docs/privileges.
- **Pricing:** See /pricing page. Free tier available; paid plans unlock deeper checks and EBS operations.
- **Support:** support@tunevault.app`;

function buildSystemPrompt(ctx, docContext) {
  const base = buildBaseSystemPrompt(ctx);
  // Retrieved Oracle-doc excerpts apply regardless of connection/health-check state,
  // so append them to whichever base-prompt branch was produced.
  return docContext ? `${base}\n\n${docContext}` : base;
}

function buildBaseSystemPrompt(ctx) {
  const lines = [
    'You are TuneBot, an expert Oracle DBA assistant embedded in TuneVault — a database health monitoring platform.',
    'Your job is to give concrete, actionable DBA advice. Be direct and specific. Lead with the answer.',
    'Format responses in markdown. Use bullet points and code blocks where relevant.',
    'Keep responses concise — 3-6 sentences unless the question requires more detail.',
    '',
    TUNEVAULT_PRODUCT_KNOWLEDGE,
    '',
  ];

  if (!ctx) {
    lines.push('No Oracle environment context is currently available. Answer based on general Oracle DBA knowledge.');
    lines.push('The user can share their connection context by opening TuneBot while viewing their dashboard.');
    return lines.join('\n');
  }

  if (ctx.noConnections) {
    lines.push('The user has no Oracle connections saved yet. Help them get started with TuneVault setup.');
    return lines.join('\n');
  }

  if (ctx.noHealthCheck) {
    const c = ctx.activeConnection || {};
    lines.push(`## Current Connection`);
    lines.push(`- **Name:** ${c.name || 'Unknown'}`);
    lines.push(`- **Oracle Version:** ${c.oracle_version || 'Unknown'}`);
    lines.push(`- **Type:** ${c.connection_type || 'direct'}`);
    lines.push(`- **EBS:** ${c.is_ebs ? 'Yes' : 'No'}`);
    lines.push('');
    lines.push('No health check has been run for this connection yet. If the user asks about issues, encourage them to run a health check first. You can still answer general Oracle questions.');
    return lines.join('\n');
  }

  const conn = ctx.activeConnection || {};
  const hc = ctx.healthCheck || {};
  const tickets = ctx.tickets || [];
  const findings = hc.findings || [];

  // Connection context
  lines.push('## Active Oracle Environment');
  lines.push(`- **Connection:** ${conn.name}`);
  lines.push(`- **Oracle Version:** ${conn.oracle_version || 'Unknown'}`);
  lines.push(`- **Connection Type:** ${conn.connection_type || 'direct'}`);
  lines.push(`- **EBS Detected:** ${conn.is_ebs ? 'Yes — E-Business Suite detected' : 'No'}`);
  lines.push('');

  // Health check summary
  lines.push('## Latest Health Check');
  lines.push(`- **Overall Score:** ${hc.score !== undefined && hc.score !== null ? hc.score + '/100' : 'N/A'}`);
  lines.push(`- **Run At:** ${hc.completedAt ? new Date(hc.completedAt).toUTCString() : 'Unknown'}`);
  if (hc.summary) lines.push(`- **AI Summary:** ${hc.summary}`);
  if (hc.topAction) lines.push(`- **Top Recommended Action:** ${hc.topAction}`);
  if (conn.is_ebs) {
    if (hc.ebsSummary) lines.push(`- **EBS Summary:** ${hc.ebsSummary}`);
    if (hc.ebsAction) lines.push(`- **EBS Top Action:** ${hc.ebsAction}`);
  }
  lines.push('');

  // Findings (red and amber)
  const redFindings = findings.filter(f => f.status === 'red');
  const amberFindings = findings.filter(f => f.status === 'amber');

  if (redFindings.length > 0) {
    lines.push('## Critical Findings (Red)');
    redFindings.forEach(f => {
      const val = f.metric_value !== undefined && f.metric_value !== null
        ? ` — ${f.metric_value}${f.metric_unit ? ' ' + f.metric_unit : ''}`
        : '';
      lines.push(`- **${f.check_id}** [${f.check_category}]${val}: ${f.ai_summary || ''}${f.recommendation ? ' → ' + f.recommendation : ''}`);
    });
    lines.push('');
  }

  if (amberFindings.length > 0) {
    lines.push('## Warning Findings (Amber)');
    amberFindings.slice(0, 8).forEach(f => {
      const val = f.metric_value !== undefined && f.metric_value !== null
        ? ` — ${f.metric_value}${f.metric_unit ? ' ' + f.metric_unit : ''}`
        : '';
      lines.push(`- **${f.check_id}** [${f.check_category}]${val}: ${f.ai_summary || ''}${f.recommendation ? ' → ' + f.recommendation : ''}`);
    });
    lines.push('');
  }

  if (findings.length === 0) {
    lines.push('## Findings');
    lines.push('All checks passed — no critical or warning findings.');
    lines.push('');
  }

  // AI recommendations (structured JSONB from the health check analysis)
  const recs = hc.aiRecommendations;
  if (recs && Array.isArray(recs) && recs.length > 0) {
    lines.push('## AI Recommendations');
    recs.slice(0, 6).forEach(r => {
      const conf = r.confidence ? ` [${r.confidence}]` : '';
      lines.push(`- ${r.title || r.category || 'Recommendation'}${conf}: ${r.summary || r.fix_sql || ''}`);
    });
    lines.push('');
  }

  // Open tickets
  if (tickets.length > 0) {
    lines.push('## Open TuneOps Tickets');
    tickets.forEach(t => {
      lines.push(`- **${t.ticket_number}** [${t.severity}/${t.status}]: ${t.title}`);
    });
    lines.push('');
  }

  lines.push(`## TuneVault Features & Navigation

Pages and what they do:
- /clients — Multi-client fleet dashboard. Groups Oracle servers by client and EBS instance. Has a Fleet Overview tab. Replaces the old /fleet and /msp pages.
- /connections (nav: Servers) — Manage all Oracle server connections. Add, edit, delete servers.
- /connections/new (nav: Add Server) — Add a new Oracle server via agent install wizard.
- /dashboard — Per-connection 9-tab health dashboard (Performance, Security, Storage, EBS, etc.)
- /db-ops — Database operations across categories: Critical/Daily (tablespace, locks, sessions, redo logs), Performance, Maintenance/Periodic (objects, stats, scheduler, users), Start/Stop (instance startup/shutdown, listener, PDB management).
- /ebs-ops — EBS operations: Service Status (start/stop Apache, OPMN, WLS, Forms, OACore from browser), Concurrent Processing (CM control, running/pending/failed requests), WF Mailer (start/stop/reset), Patching & ADOP (run all 9 ADOP phases directly from browser — prepare/apply/finalize/cutover/cleanup/validate/actualize/fs_clone/abort), ETCC patch currency checker.
- /tune — Performance advisors: ADDM, SQL Tuning Advisor, AWR Wait Events, Index Usage, Compression, Space, Statistics, Memory, Segment advisors.
- /security-posture — 10-group security scan scored 0-100. Checks default passwords, privilege escalation, TDE, audit trail, patch currency etc.
- /vault — Encrypted credential vault for SSH keys and Oracle passwords.
- /tuneops — Built-in DBA ticketing system. Auto-creates tickets from health check findings. TO-XXXX ticket numbers, RBAC approval workflows.
- /mcp — MCP server for Claude Desktop/Code integration. 20 Oracle tools accessible via AI assistants.
- /reports — PDF and XLSX health check reports.
- /patches — Oracle patch advisor. Checks installed patches vs latest CPU/PSU.
- /sql-console — SQL editor with syntax highlighting. Read-only SELECT queries via agent.
- /settings — Account settings, API keys, notifications, SSO.
- /pricing — Pricing plans: Starter $49/conn (1-5 conns), Professional $35/conn (6-25), Business $25/conn (26-80), Enterprise $18/conn (81-300), Managed Services $12/conn (300+). Free tier: 1 connection, 5 health checks, no credit card.
- TuneVault is significantly cheaper than alternatives: SolarWinds DPA ~$125/instance/month, Datadog DBM ~$70/instance/month, Quest Foglight ~$150/instance/month. TuneVault starts at $49/connection/month with no feature gating.

Key concepts:
- Connections are Oracle servers with an installed agent (oracle-proxy.py). Agent communicates outbound only — no inbound ports needed.
- EBS Instance Name groups DB and app tier servers belonging to the same EBS environment.
- Client groups multiple EBS instances under one customer/organization for multi-client management.
- Health score is 0-100. Green ≥80, Amber 50-79, Red <50.
- ADOP phases can be executed directly from TuneVault browser UI — no SSH needed. Go to /ebs-ops → Patching & ADOP tab and click any phase button.
- TuneBot (this assistant) is context-aware — it knows the current connection's health score and findings.
`);

  lines.push('When answering questions, reference the specific findings, scores, and metrics above. Be a DBA who knows this exact database, not a generic assistant.');
  lines.push('If the user asks about something not covered by the health check data, answer from Oracle DBA knowledge and note that running a health check would give you specific data.');

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

module.exports = router;
