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

const router = Router();

// ── Model pricing — loaded from DB with fallback ──

const DEFAULT_PRICING: Record<string, { input: number; output: number }> = {
  'anthropic/claude-opus-4-6': { input: 15, output: 75 },
  'anthropic/claude-sonnet-4-20250514': { input: 3, output: 15 },
  'grok-4-1-fast-reasoning': { input: 3, output: 15 },
  'grok-3': { input: 3, output: 15 },
};

let _pricingCache: Record<string, { input: number; output: number }> | null = null;
let _pricingCacheTime = 0;

async function getModelPricing(): Promise<Record<string, { input: number; output: number }>> {
  // Cache for 5 minutes
  if (_pricingCache && Date.now() - _pricingCacheTime < 300_000) return _pricingCache;
  try {
    const { rows } = await pool.query(
      'SELECT model_name, input_cost_per_m, output_cost_per_m FROM rm_model_pricing WHERE active = true'
    );
    if (rows.length > 0) {
      const pricing: Record<string, { input: number; output: number }> = {};
      for (const row of rows) {
        pricing[row.model_name] = {
          input: parseFloat(row.input_cost_per_m),
          output: parseFloat(row.output_cost_per_m),
        };
      }
      _pricingCache = pricing;
      _pricingCacheTime = Date.now();
      return pricing;
    }
  } catch {}
  return DEFAULT_PRICING;
}

function estimateCost(pricing: Record<string, { input: number; output: number }>, model: string, inputTokens: number, outputTokens: number): number {
  const p = pricing[model] || { input: 3, output: 15 };
  return (inputTokens / 1_000_000) * p.input + (outputTokens / 1_000_000) * p.output;
}

// ── Helper: build date range WHERE clause ──

function dateRangeClause(field: string, days: number): string {
  return `${field} >= CURRENT_DATE - INTERVAL '${days} days'`;
}

// ═══════════════════════════════════════════════════════════════
// GET /api/api-usage/summary?days=30 — main summary (backward compat)
// ═══════════════════════════════════════════════════════════════
router.get('/summary', async (req: Request, res: Response) => {
  try {
    const days = Math.min(90, parseInt(req.query.days as string) || 30);
    const pricing = await getModelPricing();

    const [todaySummary, todayByCategory, todayByProvider, dailyTotals, dailyByCategory, dailyByProvider, byLeague, topEvents, costRows] = await Promise.all([
      // Today's summary
      pool.query(`
        SELECT COUNT(*)::int as total_calls,
          COALESCE(SUM(input_tokens), 0)::bigint as total_input,
          COALESCE(SUM(output_tokens), 0)::bigint as total_output,
          COALESCE(SUM(total_tokens), 0)::bigint as total_tokens,
          COUNT(*) FILTER (WHERE success = true)::int as success_count,
          COUNT(*) FILTER (WHERE success = false)::int as error_count,
          ROUND(AVG(response_time_ms) FILTER (WHERE success = true))::int as avg_response_ms
        FROM rm_api_usage WHERE created_at >= CURRENT_DATE
      `),
      // Today by category
      pool.query(`
        SELECT category, COUNT(*)::int as calls,
          COALESCE(SUM(total_tokens), 0)::bigint as tokens,
          COALESCE(SUM(input_tokens), 0)::bigint as input_tokens,
          COALESCE(SUM(output_tokens), 0)::bigint as output_tokens
        FROM rm_api_usage WHERE created_at >= CURRENT_DATE
        GROUP BY category ORDER BY tokens DESC
      `),
      // Today by provider
      pool.query(`
        SELECT provider, model, COUNT(*)::int as calls,
          COALESCE(SUM(total_tokens), 0)::bigint as tokens,
          COALESCE(SUM(input_tokens), 0)::bigint as input_tokens,
          COALESCE(SUM(output_tokens), 0)::bigint as output_tokens
        FROM rm_api_usage WHERE created_at >= CURRENT_DATE
        GROUP BY provider, model ORDER BY tokens DESC
      `),
      // Daily totals
      pool.query(`
        SELECT created_at::date as day, COUNT(*)::int as calls,
          COALESCE(SUM(total_tokens), 0)::bigint as tokens,
          COALESCE(SUM(input_tokens), 0)::bigint as input_tokens,
          COALESCE(SUM(output_tokens), 0)::bigint as output_tokens
        FROM rm_api_usage WHERE ${dateRangeClause('created_at', days)}
        GROUP BY day ORDER BY day
      `),
      // Daily by category
      pool.query(`
        SELECT created_at::date as day, category, COUNT(*)::int as calls,
          COALESCE(SUM(total_tokens), 0)::bigint as tokens
        FROM rm_api_usage WHERE ${dateRangeClause('created_at', days)}
        GROUP BY day, category ORDER BY day
      `),
      // Daily by provider
      pool.query(`
        SELECT created_at::date as day, provider, COUNT(*)::int as calls,
          COALESCE(SUM(total_tokens), 0)::bigint as tokens
        FROM rm_api_usage WHERE ${dateRangeClause('created_at', days)}
        GROUP BY day, provider ORDER BY day
      `),
      // By league
      pool.query(`
        SELECT league, COUNT(*)::int as calls,
          COALESCE(SUM(total_tokens), 0)::bigint as tokens,
          COALESCE(SUM(input_tokens), 0)::bigint as input_tokens,
          COALESCE(SUM(output_tokens), 0)::bigint as output_tokens
        FROM rm_api_usage WHERE ${dateRangeClause('created_at', days)} AND league IS NOT NULL
        GROUP BY league ORDER BY tokens DESC
      `),
      // Top events (7 days)
      pool.query(`
        SELECT event_id, league, COUNT(*)::int as calls,
          COALESCE(SUM(total_tokens), 0)::bigint as tokens
        FROM rm_api_usage WHERE created_at >= CURRENT_DATE - INTERVAL '7 days' AND event_id IS NOT NULL
        GROUP BY event_id, league ORDER BY tokens DESC LIMIT 20
      `),
      // Cost by model (full range)
      pool.query(`
        SELECT model, COALESCE(SUM(input_tokens), 0)::bigint as input_tokens,
          COALESCE(SUM(output_tokens), 0)::bigint as output_tokens
        FROM rm_api_usage WHERE ${dateRangeClause('created_at', days)}
        GROUP BY model
      `),
    ]);

    // Cost calculations
    let todayCost = 0;
    for (const row of todayByProvider.rows) {
      todayCost += estimateCost(pricing, row.model, Number(row.input_tokens), Number(row.output_tokens));
    }
    let totalCostRange = 0;
    for (const row of costRows.rows) {
      totalCostRange += estimateCost(pricing, row.model, Number(row.input_tokens), Number(row.output_tokens));
    }

    // Daily cost trend
    const dailyCosts = dailyTotals.rows.map((row: any) => {
      // Approximate daily cost using average model mix
      const dayInputRatio = Number(row.input_tokens) / Math.max(Number(row.tokens), 1);
      const dayInput = Number(row.input_tokens);
      const dayOutput = Number(row.output_tokens);
      // Use weighted average pricing from today's provider mix or fallback
      let dayCost = 0;
      for (const [model, p] of Object.entries(pricing)) {
        dayCost = (dayInput / 1_000_000) * 10 + (dayOutput / 1_000_000) * 50; // rough weighted avg
      }
      return { ...row, cost: Math.round(dayCost * 100) / 100 };
    });

    res.json({
      today: {
        ...todaySummary.rows[0],
        estimated_cost: Math.round(todayCost * 100) / 100,
      },
      todayByCategory: todayByCategory.rows,
      todayByProvider: todayByProvider.rows,
      dailyTotals: dailyTotals.rows,
      dailyByCategory: dailyByCategory.rows,
      dailyByProvider: dailyByProvider.rows,
      byLeague: byLeague.rows,
      topEvents: topEvents.rows,
      cost30d: Math.round(totalCostRange * 100) / 100,
      days,
      pricing,
    });
  } catch (err) {
    console.error('API usage summary error:', err);
    res.status(500).json({ error: 'Failed to load API usage data' });
  }
});

// ═══════════════════════════════════════════════════════════════
// GET /api/api-usage/period?period=daily|weekly|monthly&days=30
// Aggregated cost views
// ═══════════════════════════════════════════════════════════════
router.get('/period', async (req: Request, res: Response) => {
  try {
    const period = (req.query.period as string) || 'daily';
    const days = Math.min(365, parseInt(req.query.days as string) || (period === 'monthly' ? 365 : period === 'weekly' ? 90 : 30));
    const pricing = await getModelPricing();

    let groupBy: string;
    let dateExpr: string;
    if (period === 'weekly') {
      dateExpr = `DATE_TRUNC('week', created_at)::date`;
      groupBy = dateExpr;
    } else if (period === 'monthly') {
      dateExpr = `DATE_TRUNC('month', created_at)::date`;
      groupBy = dateExpr;
    } else {
      dateExpr = `created_at::date`;
      groupBy = dateExpr;
    }

    // Aggregated totals per period
    const { rows: periodTotals } = await pool.query(`
      SELECT ${dateExpr} as period_start,
        COUNT(*)::int as total_calls,
        COALESCE(SUM(input_tokens), 0)::bigint as input_tokens,
        COALESCE(SUM(output_tokens), 0)::bigint as output_tokens,
        COALESCE(SUM(total_tokens), 0)::bigint as total_tokens,
        COUNT(*) FILTER (WHERE success = false)::int as error_count,
        ROUND(AVG(response_time_ms) FILTER (WHERE success = true))::int as avg_response_ms
      FROM rm_api_usage
      WHERE ${dateRangeClause('created_at', days)}
      GROUP BY ${groupBy} ORDER BY period_start
    `);

    // By feature per period
    const { rows: periodByFeature } = await pool.query(`
      SELECT ${dateExpr} as period_start, category as feature_type,
        COUNT(*)::int as calls,
        COALESCE(SUM(total_tokens), 0)::bigint as tokens,
        COALESCE(SUM(input_tokens), 0)::bigint as input_tokens,
        COALESCE(SUM(output_tokens), 0)::bigint as output_tokens
      FROM rm_api_usage
      WHERE ${dateRangeClause('created_at', days)}
      GROUP BY ${groupBy}, category ORDER BY period_start
    `);

    // Compute costs
    const totalsWithCost = periodTotals.map((row: any) => ({
      ...row,
      cost: Math.round(periodByFeature
        .filter((f: any) => f.period_start === row.period_start)
        .reduce((sum: number, f: any) => {
          // Use most common model pricing as approximation
          return sum + (Number(f.input_tokens) / 1_000_000) * 15 + (Number(f.output_tokens) / 1_000_000) * 75;
        }, 0) * 100) / 100,
    }));

    // Per-model cost for the full range
    const { rows: modelCosts } = await pool.query(`
      SELECT model, provider,
        COALESCE(SUM(input_tokens), 0)::bigint as input_tokens,
        COALESCE(SUM(output_tokens), 0)::bigint as output_tokens,
        COALESCE(SUM(total_tokens), 0)::bigint as total_tokens,
        COUNT(*)::int as calls
      FROM rm_api_usage
      WHERE ${dateRangeClause('created_at', days)}
      GROUP BY model, provider ORDER BY total_tokens DESC
    `);

    const modelCostsWithEstimate = modelCosts.map((row: any) => ({
      ...row,
      cost: Math.round(estimateCost(pricing, row.model, Number(row.input_tokens), Number(row.output_tokens)) * 100) / 100,
    }));

    // Grand totals
    let grandTotalCost = 0;
    for (const row of modelCosts) {
      grandTotalCost += estimateCost(pricing, row.model, Number(row.input_tokens), Number(row.output_tokens));
    }

    res.json({
      period,
      days,
      totals: totalsWithCost,
      byFeature: periodByFeature,
      modelCosts: modelCostsWithEstimate,
      grandTotal: {
        calls: periodTotals.reduce((s: number, r: any) => s + r.total_calls, 0),
        tokens: periodTotals.reduce((s: number, r: any) => s + Number(r.total_tokens), 0),
        cost: Math.round(grandTotalCost * 100) / 100,
        errors: periodTotals.reduce((s: number, r: any) => s + r.error_count, 0),
      },
    });
  } catch (err) {
    console.error('API usage period error:', err);
    res.status(500).json({ error: 'Failed to load period data' });
  }
});

// ═══════════════════════════════════════════════════════════════
// GET /api/api-usage/forecast-performance?days=30
// Cache hit rate, regenerations, response times
// ═══════════════════════════════════════════════════════════════
router.get('/forecast-performance', async (req: Request, res: Response) => {
  try {
    const days = Math.min(90, parseInt(req.query.days as string) || 30);

    const [summary, dailyBreakdown, regenStats] = await Promise.all([
      // Overall cache performance
      pool.query(`
        SELECT
          COUNT(*)::int as total_opens,
          COUNT(*) FILTER (WHERE cache_status = 'hit')::int as cache_hits,
          COUNT(*) FILTER (WHERE cache_status = 'not_ready')::int as not_ready,
          COUNT(*) FILTER (WHERE cache_status = 'miss')::int as cache_misses,
          ROUND(AVG(response_time_ms) FILTER (WHERE cache_status = 'hit'))::int as avg_hit_ms,
          ROUND(AVG(response_time_ms) FILTER (WHERE cache_status = 'not_ready'))::int as avg_not_ready_ms,
          ROUND(100.0 * COUNT(*) FILTER (WHERE cache_status = 'hit') / GREATEST(COUNT(*), 1), 1) as cache_hit_pct
        FROM rm_forecast_open_log
        WHERE ${dateRangeClause('created_at', days)}
      `),
      // Daily cache breakdown
      pool.query(`
        SELECT created_at::date as day,
          COUNT(*)::int as total,
          COUNT(*) FILTER (WHERE cache_status = 'hit')::int as hits,
          COUNT(*) FILTER (WHERE cache_status = 'not_ready')::int as not_ready,
          COUNT(*) FILTER (WHERE cache_status = 'miss')::int as misses,
          ROUND(AVG(response_time_ms))::int as avg_ms
        FROM rm_forecast_open_log
        WHERE ${dateRangeClause('created_at', days)}
        GROUP BY day ORDER BY day
      `),
      // Regeneration stats from api_usage
      pool.query(`
        SELECT created_at::date as day,
          COUNT(*)::int as regen_count,
          COALESCE(SUM(total_tokens), 0)::bigint as tokens
        FROM rm_api_usage
        WHERE ${dateRangeClause('created_at', days)}
          AND category = 'forecast'
          AND subcategory != 'precomputed'
        GROUP BY day ORDER BY day
      `),
    ]);

    res.json({
      summary: summary.rows[0] || { total_opens: 0, cache_hits: 0, not_ready: 0, cache_misses: 0, cache_hit_pct: 0 },
      dailyBreakdown: dailyBreakdown.rows,
      regenerations: regenStats.rows,
      days,
    });
  } catch (err) {
    console.error('Forecast performance error:', err);
    res.status(500).json({ error: 'Failed to load forecast performance' });
  }
});

// ═══════════════════════════════════════════════════════════════
// GET /api/api-usage/batch-summary?days=30
// Per-league, per-batch-type cost summary
// ═══════════════════════════════════════════════════════════════
router.get('/batch-summary', async (req: Request, res: Response) => {
  try {
    const days = Math.min(90, parseInt(req.query.days as string) || 30);

    const [batchSummary, dailyBatch] = await Promise.all([
      // Aggregate batch summary
      pool.query(`
        SELECT batch_type, league,
          SUM(total_forecasts)::int as total_forecasts,
          SUM(total_tokens)::bigint as total_tokens,
          SUM(total_cost)::numeric(10,2) as total_cost,
          SUM(error_count)::int as errors,
          ROUND(AVG(duration_ms))::int as avg_duration_ms
        FROM rm_forecast_batch_summary
        WHERE ${dateRangeClause('date', days)}
        GROUP BY batch_type, league
        ORDER BY total_cost DESC
      `),
      // Daily batch totals
      pool.query(`
        SELECT date, batch_type,
          SUM(total_forecasts)::int as forecasts,
          SUM(total_tokens)::bigint as tokens,
          SUM(total_cost)::numeric(10,2) as cost,
          SUM(error_count)::int as errors
        FROM rm_forecast_batch_summary
        WHERE ${dateRangeClause('date', days)}
        GROUP BY date, batch_type
        ORDER BY date
      `),
    ]);

    res.json({
      batchSummary: batchSummary.rows,
      dailyBatch: dailyBatch.rows,
      days,
    });
  } catch (err) {
    console.error('Batch summary error:', err);
    res.status(500).json({ error: 'Failed to load batch summary' });
  }
});

// ═══════════════════════════════════════════════════════════════
// GET /api/api-usage/errors?days=30
// Error and retry tracking
// ═══════════════════════════════════════════════════════════════
router.get('/errors', async (req: Request, res: Response) => {
  try {
    const days = Math.min(90, parseInt(req.query.days as string) || 30);

    const [dailyErrors, byModel, recentErrors] = await Promise.all([
      // Daily error counts
      pool.query(`
        SELECT created_at::date as day,
          COUNT(*) FILTER (WHERE success = false)::int as errors,
          COUNT(*) FILTER (WHERE success = true)::int as successes,
          ROUND(100.0 * COUNT(*) FILTER (WHERE success = false) / GREATEST(COUNT(*), 1), 1) as error_rate
        FROM rm_api_usage
        WHERE ${dateRangeClause('created_at', days)}
        GROUP BY day ORDER BY day
      `),
      // Error rate by model
      pool.query(`
        SELECT model, provider,
          COUNT(*)::int as total_calls,
          COUNT(*) FILTER (WHERE success = false)::int as errors,
          ROUND(100.0 * COUNT(*) FILTER (WHERE success = false) / GREATEST(COUNT(*), 1), 1) as error_rate,
          ROUND(AVG(response_time_ms) FILTER (WHERE success = true))::int as avg_response_ms
        FROM rm_api_usage
        WHERE ${dateRangeClause('created_at', days)}
        GROUP BY model, provider ORDER BY errors DESC
      `),
      // Recent errors (last 50)
      pool.query(`
        SELECT id, created_at, category, model, provider, league, event_id,
          error_message, response_time_ms
        FROM rm_api_usage
        WHERE success = false AND ${dateRangeClause('created_at', days)}
        ORDER BY created_at DESC LIMIT 50
      `),
    ]);

    res.json({
      dailyErrors: dailyErrors.rows,
      byModel: byModel.rows,
      recentErrors: recentErrors.rows,
      days,
    });
  } catch (err) {
    console.error('Errors endpoint error:', err);
    res.status(500).json({ error: 'Failed to load error data' });
  }
});

// ═══════════════════════════════════════════════════════════════
// GET /api/api-usage/model-pricing — current pricing config
// ═══════════════════════════════════════════════════════════════
router.get('/model-pricing', async (_req: Request, res: Response) => {
  try {
    const { rows } = await pool.query(
      'SELECT model_name, provider, input_cost_per_m, output_cost_per_m, active, updated_at FROM rm_model_pricing ORDER BY provider, model_name'
    );
    res.json({ pricing: rows });
  } catch (err) {
    console.error('Model pricing error:', err);
    res.status(500).json({ error: 'Failed to load model pricing' });
  }
});

// ═══════════════════════════════════════════════════════════════
// GET /api/api-usage/export?days=30&format=csv
// CSV export of usage data
// ═══════════════════════════════════════════════════════════════
router.get('/export', async (req: Request, res: Response) => {
  try {
    const days = Math.min(365, parseInt(req.query.days as string) || 30);
    const exportType = (req.query.type as string) || 'daily';
    const pricing = await getModelPricing();

    if (exportType === 'raw') {
      // Raw API usage logs
      const { rows } = await pool.query(`
        SELECT created_at, category, subcategory, provider, model, league, event_id,
          input_tokens, output_tokens, total_tokens, response_time_ms, success, error_message
        FROM rm_api_usage
        WHERE ${dateRangeClause('created_at', days)}
        ORDER BY created_at DESC
        LIMIT 10000
      `);

      const header = 'timestamp,category,subcategory,provider,model,league,event_id,input_tokens,output_tokens,total_tokens,response_time_ms,success,error_message\n';
      const csvRows = rows.map((r: any) =>
        `${r.created_at},${r.category},${r.subcategory || ''},${r.provider},${r.model},${r.league || ''},${r.event_id || ''},${r.input_tokens},${r.output_tokens},${r.total_tokens},${r.response_time_ms || ''},${r.success},"${(r.error_message || '').replace(/"/g, '""')}"`
      ).join('\n');

      res.setHeader('Content-Type', 'text/csv');
      res.setHeader('Content-Disposition', `attachment; filename=api-usage-raw-${days}d.csv`);
      res.send(header + csvRows);
    } else if (exportType === 'league') {
      // Per-league cost breakdown
      const { rows } = await pool.query(`
        SELECT league, model, provider,
          COUNT(*)::int as calls,
          COALESCE(SUM(input_tokens), 0)::bigint as input_tokens,
          COALESCE(SUM(output_tokens), 0)::bigint as output_tokens,
          COALESCE(SUM(total_tokens), 0)::bigint as total_tokens
        FROM rm_api_usage
        WHERE ${dateRangeClause('created_at', days)} AND league IS NOT NULL
        GROUP BY league, model, provider
        ORDER BY league, total_tokens DESC
      `);

      const header = 'league,model,provider,calls,input_tokens,output_tokens,total_tokens,estimated_cost\n';
      const csvRows = rows.map((r: any) => {
        const cost = estimateCost(pricing, r.model, Number(r.input_tokens), Number(r.output_tokens));
        return `${r.league},${r.model},${r.provider},${r.calls},${r.input_tokens},${r.output_tokens},${r.total_tokens},${Math.round(cost * 100) / 100}`;
      }).join('\n');

      res.setHeader('Content-Type', 'text/csv');
      res.setHeader('Content-Disposition', `attachment; filename=api-usage-by-league-${days}d.csv`);
      res.send(header + csvRows);
    } else {
      // Daily summary (default)
      const { rows } = await pool.query(`
        SELECT created_at::date as date, category,
          COUNT(*)::int as calls,
          COALESCE(SUM(input_tokens), 0)::bigint as input_tokens,
          COALESCE(SUM(output_tokens), 0)::bigint as output_tokens,
          COALESCE(SUM(total_tokens), 0)::bigint as total_tokens,
          COUNT(*) FILTER (WHERE success = false)::int as errors
        FROM rm_api_usage
        WHERE ${dateRangeClause('created_at', days)}
        GROUP BY date, category ORDER BY date DESC, category
      `);

      const header = 'date,category,calls,input_tokens,output_tokens,total_tokens,errors,estimated_cost\n';
      const csvRows = rows.map((r: any) => {
        // Use average pricing for cost approximation
        const cost = (Number(r.input_tokens) / 1_000_000) * 10 + (Number(r.output_tokens) / 1_000_000) * 50;
        return `${r.date},${r.category},${r.calls},${r.input_tokens},${r.output_tokens},${r.total_tokens},${r.errors},${Math.round(cost * 100) / 100}`;
      }).join('\n');

      res.setHeader('Content-Type', 'text/csv');
      res.setHeader('Content-Disposition', `attachment; filename=api-usage-daily-${days}d.csv`);
      res.send(header + csvRows);
    }
  } catch (err) {
    console.error('Export error:', err);
    res.status(500).json({ error: 'Failed to export data' });
  }
});

export default router;