import { Router } from 'express';
import pool from '../db';
import { normalizeQuery } from '../cache/normalizer';
import { storeInCache, lookupCache, getBreakerStatus } from '../cache/cache-service';
import { getRedis } from '../cache/similarity';

/**
 * Cache analytics dashboard API routes.
 * Mounted at /api/cache in index.ts.
 */

const router = Router();

// GET /api/cache/stats — overview stats (hit rate, entries, tokens saved)
router.get('/stats', async (_req, res) => {
  try {
    const r = getRedis();
    const agents = ['sportsclaw', 'cryptoclaw', 'main'];

    // Get real-time Redis counters
    const pipeline = r.pipeline();
    for (const agent of agents) {
      pipeline.get(`qcache:metrics:${agent}:hits`);
      pipeline.get(`qcache:metrics:${agent}:misses`);
      pipeline.get(`qcache:metrics:${agent}:saved`);
    }
    const counters = await pipeline.exec();

    // Get PG aggregates
    const [entriesResult, todayEventsResult, totalSavedResult] = await Promise.all([
      pool.query(`
        SELECT
          COUNT(*) AS total_entries,
          COUNT(*) FILTER (WHERE expires_at > NOW()) AS active_entries,
          SUM(hit_count) AS total_hits
        FROM query_cache
      `),
      pool.query(`
        SELECT
          event_type,
          COUNT(*) AS cnt,
          SUM(tokens_saved) AS tokens_saved
        FROM cache_events
        WHERE created_at::date = CURRENT_DATE
        GROUP BY event_type
      `),
      pool.query(`
        SELECT COALESCE(SUM(tokens_saved), 0) AS total_saved
        FROM cache_metrics_daily
      `),
    ]);

    const entries = entriesResult.rows[0];
    const todayEvents: Record<string, { count: number; tokensSaved: number }> = {};
    for (const row of todayEventsResult.rows) {
      todayEvents[row.event_type] = {
        count: parseInt(row.cnt),
        tokensSaved: parseInt(row.tokens_saved) || 0,
      };
    }

    // Build per-agent real-time stats
    const agentStats: Record<string, any> = {};
    for (let i = 0; i < agents.length; i++) {
      const hits = parseInt((counters?.[i * 3]?.[1] as string) || '0') || 0;
      const misses = parseInt((counters?.[i * 3 + 1]?.[1] as string) || '0') || 0;
      const saved = parseInt((counters?.[i * 3 + 2]?.[1] as string) || '0') || 0;
      agentStats[agents[i]] = { hits, misses, saved };
    }

    const todayHits = (todayEvents.hit?.count || 0);
    const todayMisses = (todayEvents.miss?.count || 0);
    const todayTotal = todayHits + todayMisses;

    res.json({
      totalEntries: parseInt(entries.total_entries),
      activeEntries: parseInt(entries.active_entries),
      totalHitsAllTime: parseInt(entries.total_hits) || 0,
      totalTokensSavedAllTime: parseInt(totalSavedResult.rows[0].total_saved),
      today: {
        hits: todayHits,
        misses: todayMisses,
        total: todayTotal,
        hitRate: todayTotal > 0 ? (todayHits / todayTotal * 100).toFixed(1) : '0.0',
        tokensSaved: todayEvents.hit?.tokensSaved || 0,
        warmed: todayEvents.warm?.count || 0,
      },
      agents: agentStats,
      circuitBreaker: getBreakerStatus(),
    });
  } catch (err: any) {
    console.error('Cache stats error:', err);
    res.status(500).json({ error: err.message });
  }
});

// GET /api/cache/entries — paginated cache entries
router.get('/entries', async (req, res) => {
  try {
    const page = Math.max(1, parseInt(req.query.page as string) || 1);
    const limit = Math.min(100, parseInt(req.query.limit as string) || 25);
    const offset = (page - 1) * limit;
    const search = req.query.search as string;
    const agent = req.query.agent as string;

    let conditions = 'TRUE';
    const params: any[] = [];
    let paramIdx = 1;

    if (agent) {
      conditions += ` AND agent = $${paramIdx}`;
      params.push(agent);
      paramIdx++;
    }

    if (search) {
      conditions += ` AND normalized_query ILIKE $${paramIdx}`;
      params.push(`%${search}%`);
      paramIdx++;
    }

    const [countResult, dataResult] = await Promise.all([
      pool.query(`SELECT COUNT(*) FROM query_cache WHERE ${conditions}`, params),
      pool.query(`
        SELECT id, cache_key, normalized_query, original_query, agent, query_category,
               league, teams, game_date, created_at, expires_at, last_hit_at,
               hit_count, response_tokens, source, model_used,
               CASE WHEN expires_at > NOW() THEN true ELSE false END AS is_active,
               LENGTH(response_payload) AS response_length
        FROM query_cache
        WHERE ${conditions}
        ORDER BY created_at DESC
        LIMIT $${paramIdx} OFFSET $${paramIdx + 1}
      `, [...params, limit, offset]),
    ]);

    res.json({
      entries: dataResult.rows,
      total: parseInt(countResult.rows[0].count),
      page, limit,
    });
  } catch (err: any) {
    console.error('Cache entries error:', err);
    res.status(500).json({ error: err.message });
  }
});

// GET /api/cache/top-queries — most frequently hit cached queries
router.get('/top-queries', async (req, res) => {
  try {
    const limit = Math.min(50, parseInt(req.query.limit as string) || 20);
    const agent = req.query.agent as string;

    let conditions = 'hit_count > 0 AND expires_at > NOW()';
    const params: any[] = [];
    let paramIdx = 1;

    if (agent) {
      conditions += ` AND agent = $${paramIdx}`;
      params.push(agent);
      paramIdx++;
    }

    const result = await pool.query(`
      SELECT id, normalized_query, agent, query_category, league,
             hit_count, response_tokens, created_at, last_hit_at,
             (hit_count * COALESCE(response_tokens, 0)) AS total_tokens_saved
      FROM query_cache
      WHERE ${conditions}
      ORDER BY hit_count DESC
      LIMIT $${paramIdx}
    `, [...params, limit]);

    res.json({ queries: result.rows });
  } catch (err: any) {
    console.error('Top queries error:', err);
    res.status(500).json({ error: err.message });
  }
});

// GET /api/cache/events — recent cache events
router.get('/events', async (req, res) => {
  try {
    const limit = Math.min(200, parseInt(req.query.limit as string) || 50);
    const eventType = req.query.type as string;
    const agent = req.query.agent as string;

    let conditions = 'TRUE';
    const params: any[] = [];
    let paramIdx = 1;

    if (eventType) {
      conditions += ` AND ce.event_type = $${paramIdx}`;
      params.push(eventType);
      paramIdx++;
    }

    if (agent) {
      conditions += ` AND ce.agent = $${paramIdx}`;
      params.push(agent);
      paramIdx++;
    }

    const result = await pool.query(`
      SELECT ce.id, ce.event_type, ce.agent, ce.normalized_query,
             ce.similarity_score, ce.tokens_saved, ce.latency_ms, ce.created_at,
             qc.query_category, qc.league
      FROM cache_events ce
      LEFT JOIN query_cache qc ON ce.cache_entry_id = qc.id
      WHERE ${conditions}
      ORDER BY ce.created_at DESC
      LIMIT $${paramIdx}
    `, [...params, limit]);

    res.json({ events: result.rows });
  } catch (err: any) {
    console.error('Cache events error:', err);
    res.status(500).json({ error: err.message });
  }
});

// GET /api/cache/daily — daily metrics for charts
router.get('/daily', async (req, res) => {
  try {
    const days = Math.min(90, parseInt(req.query.days as string) || 30);
    const agent = req.query.agent as string;

    let conditions = `date >= CURRENT_DATE - interval '${days} days'`;
    const params: any[] = [];
    let paramIdx = 1;

    if (agent) {
      conditions += ` AND agent = $${paramIdx}`;
      params.push(agent);
      paramIdx++;
    }

    const result = await pool.query(`
      SELECT date, agent, total_queries, cache_hits, cache_misses,
             tokens_saved, unique_queries, top_queries,
             CASE WHEN total_queries > 0
               THEN ROUND(cache_hits::numeric / total_queries * 100, 1)
               ELSE 0 END AS hit_rate
      FROM cache_metrics_daily
      WHERE ${conditions}
      ORDER BY date DESC, agent
    `, params);

    res.json({ metrics: result.rows });
  } catch (err: any) {
    console.error('Daily metrics error:', err);
    res.status(500).json({ error: err.message });
  }
});

// DELETE /api/cache/entry/:id — manually evict a cache entry
router.delete('/entry/:id', async (req, res) => {
  try {
    const { id } = req.params;

    // Get the entry first to clear Redis
    const entry = await pool.query(
      `SELECT cache_key, agent FROM query_cache WHERE id = $1`, [id]
    );

    if (entry.rows.length === 0) {
      res.status(404).json({ error: 'Entry not found' });
      return;
    }

    const { cache_key, agent } = entry.rows[0];

    // Delete from Redis
    try {
      const r = getRedis();
      await r.del(`qcache:${agent}:${cache_key}`);
    } catch {}

    // Delete from PG
    await pool.query(`DELETE FROM query_cache WHERE id = $1`, [id]);

    res.json({ deleted: true });
  } catch (err: any) {
    console.error('Cache evict error:', err);
    res.status(500).json({ error: err.message });
  }
});

// POST /api/cache/warm — manually trigger a warming cycle
router.post('/warm', async (_req, res) => {
  try {
    // Import warming logic inline to avoid circular deps
    const { normalizeQuery: normalize } = require('../cache/normalizer');
    const { checkSafety } = require('../cache/safety-filter');
    const { computeTTL } = require('../cache/ttl-strategy');
    const { storeEntry: store, exactMatch: exact } = require('../cache/similarity');

    const agents = ['sportsclaw', 'cryptoclaw', 'main'];
    let totalWarmed = 0;

    for (const agent of agents) {
      const result = await pool.query(`
        SELECT m.content AS user_content, resp.content AS assistant_content
        FROM crm_messages m
        JOIN crm_sessions s ON m.session_id = s.id
        LEFT JOIN LATERAL (
          SELECT r.content
          FROM crm_messages r
          WHERE r.session_id = m.session_id
            AND r.timestamp > m.timestamp
            AND r.timestamp < (m.timestamp + interval '5 minutes')
            AND r.role = 'assistant' AND r.content_type = 'text'
            AND r.content IS NOT NULL AND LENGTH(r.content) > 50
          ORDER BY r.timestamp ASC LIMIT 1
        ) resp ON true
        WHERE s.agent = $1 AND m.role = 'user' AND m.content_type = 'text'
          AND m.content IS NOT NULL AND LENGTH(m.content) > 0
          AND m.timestamp > NOW() - interval '60 minutes'
      `, [agent]);

      for (const row of result.rows) {
        if (!row.assistant_content) continue;
        const normalized = normalize(row.user_content, agent);
        if (!normalized) continue;
        const safety = checkSafety(normalized.normalized, row.assistant_content);
        if (!safety.isCacheable) continue;
        const existing = await exact(agent, normalized.cacheKey);
        if (existing) continue;
        const { ttlSeconds } = await computeTTL(normalized.category, normalized.league, normalized.teams, normalized.gameDate);
        if (ttlSeconds === 0) continue;

        await store({
          cacheKey: normalized.cacheKey,
          normalizedQuery: normalized.normalized,
          originalQuery: row.user_content,
          agent,
          category: normalized.category,
          responsePayload: row.assistant_content,
          responseTokens: Math.ceil(row.assistant_content.length / 4),
          league: normalized.league,
          teams: normalized.teams,
          gameDate: normalized.gameDate,
          ttlSeconds,
          modelUsed: null, tokensIn: null, tokensOut: null,
          source: 'warm' as const,
        });
        totalWarmed++;
      }
    }

    res.json({ warmed: totalWarmed });
  } catch (err: any) {
    console.error('Manual warm error:', err);
    res.status(500).json({ error: err.message });
  }
});

// GET /api/cache/ab-stats — A/B test comparison (control vs experiment)
router.get('/ab-stats', async (_req, res) => {
  try {
    const result = await pool.query(`
      SELECT
        ab_group,
        COUNT(*) AS total,
        COUNT(*) FILTER (WHERE event_type = 'hit') AS hits,
        COUNT(*) FILTER (WHERE event_type = 'miss') AS misses,
        COALESCE(SUM(tokens_saved), 0) AS tokens_saved,
        ROUND(AVG(latency_ms) FILTER (WHERE latency_ms > 0), 1) AS avg_latency_ms,
        CASE WHEN COUNT(*) > 0
          THEN ROUND(COUNT(*) FILTER (WHERE event_type = 'hit')::numeric / COUNT(*) * 100, 1)
          ELSE 0
        END AS hit_rate
      FROM cache_events
      WHERE ab_group IS NOT NULL
        AND created_at > NOW() - interval '7 days'
      GROUP BY ab_group
      ORDER BY ab_group
    `);

    const groups: Record<string, any> = {};
    for (const row of result.rows) {
      groups[row.ab_group] = {
        total: parseInt(row.total),
        hits: parseInt(row.hits),
        misses: parseInt(row.misses),
        tokensSaved: parseInt(row.tokens_saved),
        avgLatencyMs: parseFloat(row.avg_latency_ms) || 0,
        hitRate: parseFloat(row.hit_rate),
      };
    }

    res.json({ groups, controlPct: parseInt(process.env.CACHE_AB_CONTROL_PCT || '10') });
  } catch (err: any) {
    console.error('A/B stats error:', err);
    res.status(500).json({ error: err.message });
  }
});

// GET /api/cache/entry/:id/response — get cached response text for a specific entry
router.get('/entry/:id/response', async (req, res) => {
  try {
    const { id } = req.params;
    const result = await pool.query(
      `SELECT response_payload FROM query_cache WHERE id = $1`, [id]
    );
    if (result.rows.length === 0) {
      res.status(404).json({ error: 'Entry not found' });
      return;
    }
    res.json({ response: result.rows[0].response_payload });
  } catch (err: any) {
    console.error('Entry response error:', err);
    res.status(500).json({ error: err.message });
  }
});

// GET /api/cache/savings — token savings projection from SQL view
router.get('/savings', async (_req, res) => {
  try {
    const result = await pool.query(`SELECT * FROM cache_savings_summary`);
    if (result.rows.length === 0) {
      res.json({
        q_total: 0, total_hits: 0, total_misses: 0, total_tokens_saved: 0,
        q_per_day: 0, repeat_rate_r: 0, hit_rate_h: 0,
        avg_tokens_t: 1200, avg_tokens_tc: 60,
        projected_tokens_saved_per_day: 0, projected_tokens_saved_per_month: 0,
        projected_cost_saved_per_month: 0,
      });
      return;
    }
    res.json(result.rows[0]);
  } catch (err: any) {
    console.error('Cache savings error:', err);
    res.status(500).json({ error: err.message });
  }
});

export default router;