import { Router } from 'express';
import pool from '../db';

const router = Router();

const OPENROUTER_URL = 'https://openrouter.ai/api/v1/chat/completions';

// GET /api/pool/backtests — list all backtest runs
router.get('/backtests', async (_req, res) => {
  try {
    const result = await pool.query(`
      SELECT id, name, status, config, results, queries_total, queries_done,
             created_at, completed_at
      FROM cache_backtests
      ORDER BY created_at DESC
      LIMIT 50
    `);
    res.json({ backtests: result.rows });
  } catch (err: any) {
    console.error('Pool backtests list error:', err);
    res.status(500).json({ error: err.message });
  }
});

// GET /api/pool/backtests/:id — single backtest with detail rows
router.get('/backtests/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const result = await pool.query(`
      SELECT id, name, status, config, results, detail_rows,
             queries_total, queries_done, created_at, completed_at
      FROM cache_backtests
      WHERE id = $1
    `, [id]);

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

    res.json({ backtest: result.rows[0] });
  } catch (err: any) {
    console.error('Pool backtest detail error:', err);
    res.status(500).json({ error: err.message });
  }
});

// POST /api/pool/backtests — create + run backtest async
router.post('/backtests', async (req, res) => {
  try {
    const { name, model, similarityThreshold, sampleSize, agent } = req.body;

    if (!name || !model) {
      res.status(400).json({ error: 'name and model are required' });
      return;
    }

    const threshold = parseFloat(similarityThreshold) || 0.6;
    const sample = Math.min(parseInt(sampleSize) || 25, 100);

    const config = { model, similarityThreshold: threshold, sampleSize: sample, agent: agent || null };

    // Create the backtest row
    const insertResult = await pool.query(`
      INSERT INTO cache_backtests (name, status, config, queries_total)
      VALUES ($1, 'running', $2, $3)
      RETURNING id
    `, [name, JSON.stringify(config), sample]);

    const backtestId = insertResult.rows[0].id;

    // Return immediately, run backtest async
    res.json({ id: backtestId, status: 'running' });

    // Fire-and-forget backtest execution
    runBacktest(backtestId, config).catch(err => {
      console.error(`Backtest ${backtestId} failed:`, err);
      pool.query(`UPDATE cache_backtests SET status = 'failed', results = $2 WHERE id = $1`,
        [backtestId, JSON.stringify({ error: err.message })]);
    });

  } catch (err: any) {
    console.error('Pool backtest create error:', err);
    res.status(500).json({ error: err.message });
  }
});

// DELETE /api/pool/backtests/:id — delete a backtest run
router.delete('/backtests/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const result = await pool.query(`DELETE FROM cache_backtests WHERE id = $1 RETURNING id`, [id]);
    if (result.rows.length === 0) {
      res.status(404).json({ error: 'Backtest not found' });
      return;
    }
    res.json({ deleted: true });
  } catch (err: any) {
    console.error('Pool backtest delete error:', err);
    res.status(500).json({ error: err.message });
  }
});

// ── Backtest execution engine ──

async function runBacktest(backtestId: string, config: {
  model: string;
  similarityThreshold: number;
  sampleSize: number;
  agent: string | null;
}) {
  const apiKey = process.env.OPENROUTER_API_KEY;
  if (!apiKey) {
    throw new Error('OPENROUTER_API_KEY not configured');
  }

  // Pull recent cached queries with responses
  let agentFilter = '';
  const params: any[] = [config.sampleSize];
  if (config.agent) {
    agentFilter = 'AND agent = $2';
    params.push(config.agent);
  }

  const queriesResult = await pool.query(`
    SELECT id, normalized_query, original_query, agent, query_category, league,
           response_payload, response_tokens
    FROM query_cache
    WHERE expires_at > NOW()
      AND response_payload IS NOT NULL
      AND LENGTH(response_payload) > 50
      ${agentFilter}
    ORDER BY hit_count DESC, created_at DESC
    LIMIT $1
  `, params);

  const queries = queriesResult.rows;
  const actualTotal = queries.length;

  // Update actual total
  await pool.query(`UPDATE cache_backtests SET queries_total = $2 WHERE id = $1`, [backtestId, actualTotal]);

  if (actualTotal === 0) {
    await pool.query(`
      UPDATE cache_backtests
      SET status = 'completed', queries_done = 0, completed_at = NOW(),
          results = $2
      WHERE id = $1
    `, [backtestId, JSON.stringify({
      avg_similarity: 0, total_cost: 0, avg_latency: 0,
      match_count: 0, miss_count: 0,
    })]);
    return;
  }

  const detailRows: any[] = [];
  let totalSimilarity = 0;
  let totalCost = 0;
  let totalLatency = 0;
  let matchCount = 0;
  let missCount = 0;

  for (let i = 0; i < queries.length; i++) {
    const q = queries[i];
    const startMs = Date.now();

    try {
      // Call OpenRouter
      const orResponse = await fetch(OPENROUTER_URL, {
        method: 'POST',
        headers: {
          'Authorization': `Bearer ${apiKey}`,
          'Content-Type': 'application/json',
          'HTTP-Referer': 'https://crm.sportsclaw.guru',
          'X-Title': 'CRM Pool Backtest',
        },
        body: JSON.stringify({
          model: config.model,
          messages: [
            { role: 'user', content: q.original_query || q.normalized_query },
          ],
          max_tokens: 1000,
        }),
      });

      const orData: any = await orResponse.json();
      const latencyMs = Date.now() - startMs;
      const modelResponse = orData.choices?.[0]?.message?.content || '';
      const cost = parseFloat(orData.usage?.total_cost || '0') ||
                   (orData.usage?.prompt_tokens || 0) * 0.000001 +
                   (orData.usage?.completion_tokens || 0) * 0.000002;

      // Compare using pg_trgm similarity
      const simResult = await pool.query(
        `SELECT similarity($1, $2) AS sim`,
        [
          (q.response_payload || '').substring(0, 5000),
          modelResponse.substring(0, 5000),
        ]
      );
      const similarity = parseFloat(simResult.rows[0]?.sim || '0');

      const isMatch = similarity >= config.similarityThreshold;
      if (isMatch) matchCount++;
      else missCount++;

      totalSimilarity += similarity;
      totalCost += cost;
      totalLatency += latencyMs;

      detailRows.push({
        query: (q.original_query || q.normalized_query || '').substring(0, 500),
        cachedResponse: (q.response_payload || '').substring(0, 1000),
        modelResponse: modelResponse.substring(0, 1000),
        similarity: Math.round(similarity * 1000) / 1000,
        cost: Math.round(cost * 100000) / 100000,
        latencyMs,
        agent: q.agent,
        category: q.query_category,
      });

    } catch (queryErr: any) {
      const latencyMs = Date.now() - startMs;
      missCount++;
      detailRows.push({
        query: (q.original_query || q.normalized_query || '').substring(0, 500),
        cachedResponse: (q.response_payload || '').substring(0, 1000),
        modelResponse: `[Error: ${queryErr.message}]`,
        similarity: 0,
        cost: 0,
        latencyMs,
        agent: q.agent,
        category: q.query_category,
      });
    }

    // Update progress every query
    await pool.query(`
      UPDATE cache_backtests SET queries_done = $2, detail_rows = $3 WHERE id = $1
    `, [backtestId, i + 1, JSON.stringify(detailRows)]);
  }

  // Final aggregation
  const results = {
    avg_similarity: Math.round((totalSimilarity / actualTotal) * 1000) / 1000,
    total_cost: Math.round(totalCost * 100000) / 100000,
    avg_latency: Math.round(totalLatency / actualTotal),
    match_count: matchCount,
    miss_count: missCount,
  };

  await pool.query(`
    UPDATE cache_backtests
    SET status = 'completed', results = $2, detail_rows = $3, completed_at = NOW()
    WHERE id = $1
  `, [backtestId, JSON.stringify(results), JSON.stringify(detailRows)]);
}

export default router;