/**
 * Weather Report CRM Routes
 *
 * Direct DB queries on shared sportsdb for Weather Report admin views.
 */

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

const router = Router();

// GET /api/weather-report/inventory — all items, optional ?bucket=DATA_FEED|MODEL|AGENT
router.get('/inventory', async (req: Request, res: Response) => {
  try {
    const bucket = req.query.bucket as string | undefined;
    let query = 'SELECT * FROM rm_weather_report_inventory';
    const params: any[] = [];

    if (bucket && ['DATA_FEED', 'MODEL', 'AGENT'].includes(bucket)) {
      query += ' WHERE bucket = $1';
      params.push(bucket);
    }
    query += ' ORDER BY bucket, name';

    const { rows } = await pool.query(query, params);
    res.json({ items: rows });
  } catch (err) {
    console.error('WR inventory error:', err);
    res.status(500).json({ error: 'Failed to load inventory' });
  }
});

// PATCH /api/weather-report/inventory/:id — toggle status
router.patch('/inventory/:id', async (req: Request, res: Response) => {
  try {
    const { status } = req.body;
    if (!status || !['active', 'inactive', 'error'].includes(status)) {
      res.status(400).json({ error: 'Status must be active, inactive, or error' });
      return;
    }
    const { rows } = await pool.query(
      `UPDATE rm_weather_report_inventory SET status = $1, updated_at = NOW() WHERE id = $2 RETURNING *`,
      [status, req.params.id]
    );
    if (rows.length === 0) {
      res.status(404).json({ error: 'Item not found' });
      return;
    }
    res.json(rows[0]);
  } catch (err) {
    console.error('WR inventory update error:', err);
    res.status(500).json({ error: 'Failed to update inventory' });
  }
});

// GET /api/weather-report/runs — last N runs
router.get('/runs', async (req: Request, res: Response) => {
  try {
    const limit = Math.min(parseInt(req.query.limit as string) || 7, 50);
    const { rows } = await pool.query(
      `SELECT id, run_at, date_et, schedule_name, total_contests_found,
              total_forecasts_generated, total_skipped_due_to_cap, total_failures,
              total_odds_refreshed, duration_ms, status, error_message, created_at
       FROM rm_weather_report_runs ORDER BY run_at DESC LIMIT $1`,
      [limit]
    );
    res.json({ runs: rows });
  } catch (err) {
    console.error('WR runs error:', err);
    res.status(500).json({ error: 'Failed to load runs' });
  }
});

// GET /api/weather-report/runs/:id — single run with full log
router.get('/runs/:id', async (req: Request, res: Response) => {
  try {
    const { rows } = await pool.query(
      `SELECT * FROM rm_weather_report_runs WHERE id = $1`,
      [req.params.id]
    );
    if (rows.length === 0) {
      res.status(404).json({ error: 'Run not found' });
      return;
    }
    res.json(rows[0]);
  } catch (err) {
    console.error('WR run detail error:', err);
    res.status(500).json({ error: 'Failed to load run' });
  }
});

// GET /api/weather-report/today — today's precompute summary
router.get('/today', async (_req: Request, res: Response) => {
  try {
    const dateET = new Date().toLocaleDateString('en-CA', { timeZone: 'America/New_York' });

    // Latest run
    const { rows: runs } = await pool.query(
      `SELECT id, run_at, date_et, schedule_name, total_contests_found,
              total_forecasts_generated, total_skipped_due_to_cap, total_failures,
              total_odds_refreshed, duration_ms, status
       FROM rm_weather_report_runs WHERE date_et = $1 ORDER BY run_at DESC LIMIT 1`,
      [dateET]
    );

    // Precomputed counts by type
    const { rows: counts } = await pool.query(
      `SELECT forecast_type, COUNT(*)::int as count,
              COUNT(*) FILTER (WHERE status = 'ACTIVE')::int as active,
              COUNT(*) FILTER (WHERE status = 'FAILED')::int as failed
       FROM rm_forecast_precomputed WHERE date_et = $1 GROUP BY forecast_type`,
      [dateET]
    );

    // Per-league
    const { rows: byLeague } = await pool.query(
      `SELECT league,
              COUNT(*)::int as total,
              COUNT(*) FILTER (WHERE forecast_type = 'GAME_MARKETS')::int as game_markets,
              COUNT(*) FILTER (WHERE forecast_type = 'TEAM_PROPS')::int as team_props
       FROM rm_forecast_precomputed
       WHERE date_et = $1 AND status = 'ACTIVE'
       GROUP BY league ORDER BY league`,
      [dateET]
    );

    // Events eligible
    const { rows: eventCount } = await pool.query(
      `SELECT COUNT(*)::int as total FROM rm_events
       WHERE starts_at::date = $1::date AND status = 'scheduled'`,
      [dateET]
    );

    // Legacy cache count
    const { rows: legacyCount } = await pool.query(
      `SELECT COUNT(*)::int as total FROM rm_forecast_cache
       WHERE starts_at::date = $1::date`,
      [dateET]
    );

    const dailyUsed = counts.reduce((sum: number, c: any) => sum + c.count, 0);

    res.json({
      date_et: dateET,
      last_run: runs[0] || null,
      forecast_counts: counts,
      by_league: byLeague,
      events_eligible: eventCount[0]?.total || 0,
      legacy_cached: legacyCount[0]?.total || 0,
      daily_cap: 200,
      daily_used: dailyUsed,
      daily_remaining: 200 - dailyUsed,
    });
  } catch (err) {
    console.error('WR today error:', err);
    res.status(500).json({ error: 'Failed to load today status' });
  }
});

// GET /api/weather-report/precomputed — today's precomputed forecasts list
router.get('/precomputed', async (req: Request, res: Response) => {
  try {
    const dateET = new Date().toLocaleDateString('en-CA', { timeZone: 'America/New_York' });
    const league = (req.query.league as string || '').toLowerCase();

    let query = `
      SELECT fp.id, fp.date_et, fp.league, fp.event_id, fp.forecast_type,
             fp.team_id, fp.team_side, fp.model_version, fp.confidence_score,
             fp.generated_at, fp.expires_at, fp.status,
             e.home_team, e.away_team, e.home_short, e.away_short, e.starts_at
      FROM rm_forecast_precomputed fp
      LEFT JOIN rm_events e ON fp.event_id = e.event_id
      WHERE fp.date_et = $1
    `;
    const params: any[] = [dateET];

    if (league) {
      query += ` AND fp.league = $2`;
      params.push(league);
    }
    query += ` ORDER BY e.starts_at ASC NULLS LAST, fp.forecast_type`;

    const { rows } = await pool.query(query, params);
    res.json({ date_et: dateET, forecasts: rows });
  } catch (err) {
    console.error('WR precomputed error:', err);
    res.status(500).json({ error: 'Failed to load precomputed forecasts' });
  }
});

export default router;