/**
 * Weather Report Routes
 *
 * Admin endpoints for:
 *   - Inventory of data feeds, models, agents
 *   - Run history and logs
 *   - Cache-first precomputed forecast retrieval
 *   - Inventory status toggling
 */

import { Router, Request, Response } from 'express';
import { authMiddleware } from '../middleware/auth';
import { auditAdminAccess, requireAdminAccess } from '../middleware/admin';
import pool from '../db';
import {
  getMlbAlertAssets,
  getMlbIssueHistory,
  getStoredMlbZeroCandidateIssues,
  getMlbZeroCandidateIssues,
  summarizeMlbAlertAssets,
  summarizeMlbZeroCandidateIssues,
} from '../services/mlb-alerts';
import { describeForecastAsset, summarizeForecastCountRows } from '../services/forecast-asset-taxonomy';
import {
  fetchMlbMarketCompletionInsights,
  fetchOperationalCronRuns,
  fetchOperationalCronSummary,
  fetchOperationalCronSummaries,
  fetchStoredMlbMarketRows,
  summarizeStoredMlbMarketRows,
} from '../services/mlb-market-reporter';
import { isUuid } from '../lib/uuid';

const router = Router();
const adminMiddleware = [authMiddleware, requireAdminAccess, auditAdminAccess('weather-report')] as const;

function extractMlbAlerts(vendorInputsSummary: any): any[] {
  if (!vendorInputsSummary || typeof vendorInputsSummary !== 'object') return [];
  const alerts = vendorInputsSummary.mlb_alerts;
  return Array.isArray(alerts) ? alerts : [];
}

function extractPlayerPropPipeline(row: any): any | null {
  const payloadPipeline = row?.forecast_payload?.metadata?.player_prop_pipeline;
  if (payloadPipeline && typeof payloadPipeline === 'object') return payloadPipeline;
  const vendorPipeline = row?.vendor_inputs_summary?.player_prop_pipeline;
  if (vendorPipeline && typeof vendorPipeline === 'object') return vendorPipeline;
  return null;
}

function summarizePlayerPropPipeline(rows: any[]): any {
  const teamRows = rows
    .map((row) => ({
      event_id: row.event_id,
      team_id: row.team_id,
      team_side: row.team_side,
      diagnostics: extractPlayerPropPipeline(row),
    }))
    .filter((row) => row.diagnostics);

  const summary = {
    teamBundles: teamRows.length,
    rawProps: 0,
    filteredProps: 0,
    storedPlayerProps: 0,
    supplementalProps: 0,
    rescuedBundles: 0,
    rescueShortfallBundles: 0,
    droppedMissingPricing: 0,
    droppedNonRoster: 0,
    storeFailures: 0,
    zeroStoredBundles: 0,
    zeroFilteredBundles: 0,
    sourceBackedSuppressionReasons: {} as Record<string, number>,
    teamsWithDrops: [] as Array<{
      eventId: string;
      teamId: string | null;
      teamSide: string | null;
      droppedMissingPricing: number;
      droppedNonRoster: number;
      storeFailures: number;
      storedPlayerProps: number;
      supplementalProps: number;
      sourceBackedRescueUsed: boolean;
      sourceBackedRescueShortfall: number;
    }>,
  };

  for (const row of teamRows) {
    const diagnostics = row.diagnostics || {};
    const rawProps = Number(diagnostics.rawPropCount || 0);
    const filteredProps = Number(diagnostics.filteredPropCount || 0);
    const storedPlayerProps = Number(diagnostics.playerPropsStored || 0);
    const supplementalProps = Number(diagnostics.supplementalPropCount || 0);
    const sourceBackedRescueUsed = Boolean(diagnostics.sourceBackedRescueUsed);
    const sourceBackedRescueShortfall = Number(diagnostics.sourceBackedRescueShortfall || 0);
    const droppedMissingPricing = Number(diagnostics.droppedMissingPricing || 0);
    const droppedNonRoster = Number(diagnostics.droppedNonRoster || 0);
    const storeFailures = Number(diagnostics.storeFailures || 0);
    const suppressionReasons = diagnostics.sourceBackedSuppressionReasons && typeof diagnostics.sourceBackedSuppressionReasons === 'object'
      ? diagnostics.sourceBackedSuppressionReasons
      : {};

    summary.rawProps += rawProps;
    summary.filteredProps += filteredProps;
    summary.storedPlayerProps += storedPlayerProps;
    summary.supplementalProps += supplementalProps;
    if (sourceBackedRescueUsed) summary.rescuedBundles++;
    if (sourceBackedRescueShortfall > 0) summary.rescueShortfallBundles++;
    summary.droppedMissingPricing += droppedMissingPricing;
    summary.droppedNonRoster += droppedNonRoster;
    summary.storeFailures += storeFailures;
    if (filteredProps === 0) summary.zeroFilteredBundles++;
    if (storedPlayerProps === 0) summary.zeroStoredBundles++;
    for (const [reason, count] of Object.entries(suppressionReasons)) {
      summary.sourceBackedSuppressionReasons[reason] = (summary.sourceBackedSuppressionReasons[reason] || 0) + Number(count || 0);
    }

    if (droppedMissingPricing > 0 || droppedNonRoster > 0 || storeFailures > 0 || sourceBackedRescueUsed || sourceBackedRescueShortfall > 0) {
      summary.teamsWithDrops.push({
        eventId: row.event_id,
        teamId: row.team_id,
        teamSide: row.team_side,
        droppedMissingPricing,
        droppedNonRoster,
        storeFailures,
        storedPlayerProps,
        supplementalProps,
        sourceBackedRescueUsed,
        sourceBackedRescueShortfall,
      });
    }
  }

  summary.teamsWithDrops.sort((a, b) => (
    (Number(b.sourceBackedRescueUsed) * 1000 + b.sourceBackedRescueShortfall * 100 + b.droppedMissingPricing + b.droppedNonRoster + b.storeFailures)
    - (Number(a.sourceBackedRescueUsed) * 1000 + a.sourceBackedRescueShortfall * 100 + a.droppedMissingPricing + a.droppedNonRoster + a.storeFailures)
  ));
  summary.teamsWithDrops = summary.teamsWithDrops.slice(0, 20);
  return summary;
}

function buildOperationalSummary(row: any): any {
  const alerts = extractMlbAlerts(row.vendor_inputs_summary);
  const suppressedReason = row.forecast_payload?.metadata?.mlb_suppressed_reason || null;
  return {
    status: row.status,
    alert_count: alerts.length,
    alerts,
    suppressed_reason: suppressedReason,
    has_mlb_snapshot: !!row.vendor_inputs_summary?.mlb_snapshot,
  };
}

function buildAssetResponse(row: any): any {
  const descriptor = describeForecastAsset(row.forecast_type, row.forecast_payload);
  return {
    asset_type: row.forecast_type,
    asset_label: descriptor.assetLabel,
    market_family: descriptor.marketFamily,
    market_origin: descriptor.marketOrigin,
    source_backed: descriptor.sourceBacked,
    legacy_bundle: descriptor.legacyBundle,
    player_role: descriptor.playerRole,
    status: row.status,
    team_id: row.team_id,
    team_side: row.team_side,
    player_name: row.player_name,
    confidence: row.confidence_score,
    model_version: row.model_version,
    generated_at: row.generated_at,
    payload: row.forecast_payload,
    operational: buildOperationalSummary(row),
  };
}

async function resolveMlbCoverageDate(dateET?: string | null): Promise<string | null> {
  if (dateET) return dateET;
  const { rows } = await pool.query(
    `SELECT MAX((starts_at AT TIME ZONE 'America/New_York')::date)::text AS date_et
     FROM rm_events
     WHERE league = 'mlb'`,
  ).catch(() => ({ rows: [] as any[] }));
  return rows[0]?.date_et || null;
}

async function fetchMlbCoverageRows(dateET: string): Promise<any[]> {
  const { rows } = await pool.query(
    `
      WITH events AS (
        SELECT
          e.event_id,
          e.starts_at,
          e.home_team,
          e.away_team,
          e.home_short,
          e.away_short,
          (e.starts_at AT TIME ZONE 'America/New_York')::date AS date_et
        FROM rm_events e
        WHERE e.league = 'mlb'
          AND (e.starts_at AT TIME ZONE 'America/New_York')::date = $1::date
      ),
      forecasts AS (
        SELECT DISTINCT event_id
        FROM rm_forecast_cache
        WHERE league = 'mlb'
      ),
      props AS (
        SELECT
          date_et,
          event_id,
          COUNT(*) FILTER (WHERE forecast_type = 'PLAYER_PROP' AND status = 'ACTIVE')::int AS active_player_props,
          COUNT(*) FILTER (WHERE forecast_type = 'PLAYER_PROP' AND status = 'STALE')::int AS stale_player_props,
          COUNT(*) FILTER (WHERE forecast_type = 'TEAM_PROPS' AND status = 'ACTIVE')::int AS active_team_props,
          COUNT(*) FILTER (WHERE forecast_type = 'TEAM_PROPS' AND status = 'STALE')::int AS stale_team_props
        FROM rm_forecast_precomputed
        WHERE league = 'mlb'
          AND date_et = $1::date
        GROUP BY 1, 2
      ),
      feed AS (
        SELECT
          e.event_id,
          COUNT(ppl.*)::int AS raw_feed_rows
        FROM events e
        LEFT JOIN "PlayerPropLine" ppl
          ON ppl.league = 'mlb'
         AND COALESCE(ppl."marketScope", 'full_game') = 'full_game'
         AND ((ppl."gameStart")::timestamptz AT TIME ZONE 'America/New_York')::date = e.date_et
         AND (
           (UPPER(COALESCE(ppl."homeTeam", '')) = UPPER(COALESCE(e.home_short, '')) AND UPPER(COALESCE(ppl."awayTeam", '')) = UPPER(COALESCE(e.away_short, '')))
           OR (UPPER(COALESCE(ppl."homeTeam", '')) = UPPER(COALESCE(e.away_short, '')) AND UPPER(COALESCE(ppl."awayTeam", '')) = UPPER(COALESCE(e.home_short, '')))
         )
        GROUP BY 1
      ),
      normalized AS (
        SELECT
          event_id,
          COUNT(*)::int AS normalized_market_rows
        FROM rm_mlb_normalized_player_prop_markets
        WHERE (starts_at AT TIME ZONE 'America/New_York')::date = $1::date
        GROUP BY 1
      ),
      game_odds AS (
        SELECT
          e.event_id,
          COUNT(go.*)::int AS game_odds_rows
        FROM events e
        LEFT JOIN "GameOdds" go
          ON go.league = 'mlb'
         AND (go."gameDate" AT TIME ZONE 'America/New_York')::date = e.date_et
         AND (
           (LOWER(COALESCE(go."homeTeam", '')) = LOWER(COALESCE(e.home_team, '')) AND LOWER(COALESCE(go."awayTeam", '')) = LOWER(COALESCE(e.away_team, '')))
           OR (LOWER(COALESCE(go."homeTeam", '')) = LOWER(COALESCE(e.away_team, '')) AND LOWER(COALESCE(go."awayTeam", '')) = LOWER(COALESCE(e.home_team, '')))
         )
        GROUP BY 1
      ),
      line_movement AS (
        SELECT
          e.event_id,
          COUNT(lm.*)::int AS line_movement_rows
        FROM events e
        LEFT JOIN "LineMovement" lm
          ON lm.league = 'mlb'
         AND lm."gameDate" = e.date_et
         AND (
           (LOWER(COALESCE(lm."homeTeam", '')) = LOWER(COALESCE(e.home_team, '')) AND LOWER(COALESCE(lm."awayTeam", '')) = LOWER(COALESCE(e.away_team, '')))
           OR (LOWER(COALESCE(lm."homeTeam", '')) = LOWER(COALESCE(e.away_team, '')) AND LOWER(COALESCE(lm."awayTeam", '')) = LOWER(COALESCE(e.home_team, '')))
         )
        GROUP BY 1
      ),
      injuries AS (
        SELECT
          e.event_id,
          COUNT(i.*)::int AS injury_rows
        FROM events e
        LEFT JOIN current_player_injuries i
          ON LOWER(i.league) = 'mlb'
         AND UPPER(COALESCE(i.team, '')) IN (UPPER(COALESCE(e.home_short, '')), UPPER(COALESCE(e.away_short, '')))
        GROUP BY 1
      ),
      news AS (
        SELECT
          e.event_id,
          COUNT(n.*)::int AS matchup_news_rows
        FROM events e
        LEFT JOIN rm_news_links n
          ON LOWER(COALESCE(n.sport, '')) = 'mlb'
         AND DATE(n.published_at AT TIME ZONE 'America/New_York') BETWEEN e.date_et - 1 AND e.date_et
         AND (
           LOWER(COALESCE(n.title, '')) LIKE '%' || LOWER(COALESCE(e.home_team, '')) || '%'
           OR LOWER(COALESCE(n.title, '')) LIKE '%' || LOWER(COALESCE(e.away_team, '')) || '%'
           OR LOWER(COALESCE(n.description, '')) LIKE '%' || LOWER(COALESCE(e.home_team, '')) || '%'
           OR LOWER(COALESCE(n.description, '')) LIKE '%' || LOWER(COALESCE(e.away_team, '')) || '%'
         )
        GROUP BY 1
      )
      SELECT
        e.event_id,
        e.starts_at,
        e.home_team,
        e.away_team,
        e.home_short,
        e.away_short,
        (f.event_id IS NOT NULL) AS has_forecast,
        COALESCE(p.active_player_props, 0) AS active_player_props,
        COALESCE(p.stale_player_props, 0) AS stale_player_props,
        COALESCE(p.active_team_props, 0) AS active_team_props,
        COALESCE(p.stale_team_props, 0) AS stale_team_props,
        COALESCE(fd.raw_feed_rows, 0) AS raw_feed_rows,
        COALESCE(nm.normalized_market_rows, 0) AS normalized_market_rows,
        COALESCE(go.game_odds_rows, 0) AS game_odds_rows,
        COALESCE(lm.line_movement_rows, 0) AS line_movement_rows,
        COALESCE(i.injury_rows, 0) AS injury_rows,
        COALESCE(n.matchup_news_rows, 0) AS matchup_news_rows
      FROM events e
      LEFT JOIN forecasts f ON f.event_id = e.event_id
      LEFT JOIN props p ON p.event_id = e.event_id AND p.date_et = e.date_et
      LEFT JOIN feed fd ON fd.event_id = e.event_id
      LEFT JOIN normalized nm ON nm.event_id = e.event_id
      LEFT JOIN game_odds go ON go.event_id = e.event_id
      LEFT JOIN line_movement lm ON lm.event_id = e.event_id
      LEFT JOIN injuries i ON i.event_id = e.event_id
      LEFT JOIN news n ON n.event_id = e.event_id
      ORDER BY e.starts_at ASC, e.away_team ASC, e.home_team ASC
    `,
    [dateET],
  ).catch(() => ({ rows: [] as any[] }));

  return rows;
}

function summarizeMlbCoverageRows(rows: any[]): any {
  return {
    totalEvents: rows.length,
    eventsWithForecasts: rows.filter((row) => row.has_forecast).length,
    eventsWithActivePlayerProps: rows.filter((row) => Number(row.active_player_props || 0) > 0).length,
    eventsWithStalePlayerProps: rows.filter((row) => Number(row.stale_player_props || 0) > 0).length,
    activePlayerProps: rows.reduce((sum, row) => sum + Number(row.active_player_props || 0), 0),
    stalePlayerProps: rows.reduce((sum, row) => sum + Number(row.stale_player_props || 0), 0),
    eventsWithRawFeed: rows.filter((row) => Number(row.raw_feed_rows || 0) > 0).length,
    rawFeedRows: rows.reduce((sum, row) => sum + Number(row.raw_feed_rows || 0), 0),
    eventsWithNormalizedMarkets: rows.filter((row) => Number(row.normalized_market_rows || 0) > 0).length,
    normalizedMarketRows: rows.reduce((sum, row) => sum + Number(row.normalized_market_rows || 0), 0),
    eventsWithGameOdds: rows.filter((row) => Number(row.game_odds_rows || 0) > 0).length,
    gameOddsRows: rows.reduce((sum, row) => sum + Number(row.game_odds_rows || 0), 0),
    eventsWithLineMovement: rows.filter((row) => Number(row.line_movement_rows || 0) > 0).length,
    lineMovementRows: rows.reduce((sum, row) => sum + Number(row.line_movement_rows || 0), 0),
    eventsWithInjuries: rows.filter((row) => Number(row.injury_rows || 0) > 0).length,
    injuryRows: rows.reduce((sum, row) => sum + Number(row.injury_rows || 0), 0),
    eventsWithMatchupNews: rows.filter((row) => Number(row.matchup_news_rows || 0) > 0).length,
    matchupNewsRows: rows.reduce((sum, row) => sum + Number(row.matchup_news_rows || 0), 0),
  };
}

function toCamelKey(key: string): string {
  return key.replace(/_([a-z])/g, (_match, char: string) => char.toUpperCase());
}

function camelizeValue(value: any): any {
  if (Array.isArray(value)) {
    return value.map((entry) => camelizeValue(entry));
  }
  if (!value || typeof value !== 'object' || value instanceof Date) {
    return value;
  }

  return Object.fromEntries(
    Object.entries(value).map(([key, entry]) => [toCamelKey(key), camelizeValue(entry)]),
  );
}

function mapCronRow(row: any): any {
  return {
    slug: row.slug,
    name: row.name,
    schedule: row.schedule,
    description: row.description,
    category: row.category,
    priority: row.priority,
    status: row.status,
    safeMode: Boolean(row.safe_mode),
    nonBlocking: Boolean(row.non_blocking),
    lastRun: row.last_run,
    avgRuntimeMs: row.avg_runtime_ms == null ? null : Number(row.avg_runtime_ms),
    errorRate: row.error_rate == null ? null : Number(row.error_rate),
    notes: Array.isArray(row.notes) ? row.notes : [],
    metrics: camelizeValue(row.metrics || {}),
  };
}

function mapCronRunRow(row: any): any {
  return {
    id: row.id,
    cronSlug: row.cron_slug,
    startedAt: row.started_at,
    completedAt: row.completed_at,
    status: row.status,
    durationMs: row.duration_ms == null ? null : Number(row.duration_ms),
    metrics: camelizeValue(row.metrics || {}),
    errorMessage: row.error_message || null,
  };
}

function buildSourceBucketSummary(bySource: Record<string, number>): {
  fanduel: number;
  draftkings: number;
  other: number;
} {
  const fanduel = Number(bySource.fanduel || 0);
  const draftkings = Number(bySource.draftkings || 0);
  const other = Object.entries(bySource).reduce((total, [source, count]) => {
    if (source === 'fanduel' || source === 'draftkings') return total;
    return total + Number(count || 0);
  }, 0);

  return { fanduel, draftkings, other };
}

// ── Inventory Endpoints ──

// GET /api/weather-report/inventory — all items, optional ?bucket=DATA_FEED|MODEL|AGENT
router.get('/inventory', ...adminMiddleware, 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('Weather report inventory error:', err);
    res.status(500).json({ error: 'Internal server error' });
  }
});

// PATCH /api/weather-report/inventory/:id — toggle status
router.patch('/inventory/:id', ...adminMiddleware, 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('Weather report inventory update error:', err);
    res.status(500).json({ error: 'Internal server error' });
  }
});

// ── Run History ──

// GET /api/weather-report/runs — last N runs
router.get('/runs', ...adminMiddleware, async (req: Request, res: Response) => {
  try {
    const limit = Math.min(parseInt(req.query.limit as string) || 7, 50);
    const { rows } = await pool.query(
      `SELECT * FROM rm_weather_report_runs ORDER BY run_at DESC LIMIT $1`,
      [limit]
    );
    res.json({ runs: rows });
  } catch (err) {
    console.error('Weather report runs error:', err);
    res.status(500).json({ error: 'Internal server error' });
  }
});

// GET /api/weather-report/runs/:id — single run with full log
router.get('/runs/:id', ...adminMiddleware, async (req: Request, res: Response) => {
  try {
    if (!isUuid(req.params.id)) {
      res.status(400).json({ error: 'Invalid run id' });
      return;
    }

    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('Weather report run detail error:', err);
    res.status(500).json({ error: 'Internal server error' });
  }
});

// ── Today's Status ──

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

    // Today's run
    const { rows: runs } = await pool.query(
      `SELECT * FROM rm_weather_report_runs WHERE date_et = $1 ORDER BY run_at DESC LIMIT 1`,
      [dateET]
    );

    // Today's precomputed counts
    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,
              COUNT(*) FILTER (WHERE status = 'STALE')::int as stale,
              COUNT(*) FILTER (WHERE status = 'EXPIRED')::int as expired
       FROM rm_forecast_precomputed
       WHERE date_et = $1
       GROUP BY forecast_type`,
      [dateET]
    );

    // Per-league/type breakdown
    const { rows: byLeagueType } = await pool.query(
      `SELECT league,
              forecast_type,
              COUNT(*)::int as total,
              COUNT(*) FILTER (WHERE status = 'STALE')::int as stale_assets,
              COUNT(*) FILTER (WHERE status = 'EXPIRED')::int as expired_assets,
              COUNT(*) FILTER (
                WHERE COALESCE(jsonb_array_length(vendor_inputs_summary->'mlb_alerts'), 0) > 0
              )::int as alert_assets
       FROM rm_forecast_precomputed
       WHERE date_et = $1
       GROUP BY league, forecast_type
       ORDER BY league, forecast_type`,
      [dateET]
    );

    const countsSummary = summarizeForecastCountRows(counts);
    const byLeagueMap = new Map<string, any>();
    for (const row of byLeagueType) {
      const league = String(row.league || 'unknown');
      if (!byLeagueMap.has(league)) {
        byLeagueMap.set(league, {
          league,
          total: 0,
          stale_assets: 0,
          expired_assets: 0,
          alert_assets: 0,
          forecast_counts_by_type: {},
          forecast_counts_by_family: {},
        });
      }
      const bucket = byLeagueMap.get(league);
      const count = Number(row.total || 0);
      const descriptor = describeForecastAsset(row.forecast_type);
      bucket.total += count;
      bucket.stale_assets += Number(row.stale_assets || 0);
      bucket.expired_assets += Number(row.expired_assets || 0);
      bucket.alert_assets += Number(row.alert_assets || 0);
      bucket.forecast_counts_by_type[row.forecast_type] = count;
      bucket.forecast_counts_by_family[descriptor.marketFamily] = (bucket.forecast_counts_by_family[descriptor.marketFamily] || 0) + count;
    }
    const byLeague = Array.from(byLeagueMap.values()).sort((a, b) => String(a.league).localeCompare(String(b.league)));

    const mlb_alerts = await getMlbAlertAssets(dateET);
    const mlb_alert_issues = await getMlbZeroCandidateIssues(dateET);
    const crons = await fetchOperationalCronSummaries();
    const { rows: teamPropPipelineRows } = await pool.query(
      `SELECT event_id, team_id, team_side, forecast_payload, vendor_inputs_summary
         FROM rm_forecast_precomputed
        WHERE date_et = $1
          AND forecast_type = 'TEAM_PROPS'`,
      [dateET],
    );

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

    res.json({
      date_et: dateET,
      last_run: runs[0] || null,
      forecast_counts: counts,
      by_league: byLeague,
      mlb_alerts,
      mlb_alert_summary: summarizeMlbAlertAssets(mlb_alerts),
      mlb_alert_issue_summary: summarizeMlbZeroCandidateIssues(mlb_alert_issues),
      player_prop_pipeline: summarizePlayerPropPipeline(teamPropPipelineRows),
      crons,
      events_eligible: eventCount[0]?.total || 0,
      daily_cap: Number(process.env.WEATHER_REPORT_DAILY_CAP || 600),
      daily_used: countsSummary.total,
      daily_remaining: Math.max(0, Number(process.env.WEATHER_REPORT_DAILY_CAP || 600) - countsSummary.total),
      cap_warning_threshold: Number(process.env.WEATHER_REPORT_CAP_WARN_THRESHOLD || 0.9),
      cap_warning_assets: Math.round(Number(process.env.WEATHER_REPORT_DAILY_CAP || 600) * Number(process.env.WEATHER_REPORT_CAP_WARN_THRESHOLD || 0.9)),
      cap_mode: process.env.WEATHER_REPORT_ENFORCE_CAP === 'true' ? 'enforced' : 'monitoring_only',
      forecast_counts_by_type: countsSummary.byType,
      forecast_counts_by_family: countsSummary.byFamily,
    });
  } catch (err) {
    console.error('Weather report today error:', err);
    res.status(500).json({ error: 'Internal server error' });
  }
});

// GET /api/weather-report/crons — operational cron registry + latest metrics
router.get('/crons', ...adminMiddleware, async (_req: Request, res: Response) => {
  try {
    const crons = await fetchOperationalCronSummaries();
    res.json({ crons: crons.map(mapCronRow) });
  } catch (err) {
    console.error('Weather report crons error:', err);
    res.status(500).json({ error: 'Internal server error' });
  }
});

// GET /api/weather-report/crons/:slug/runs?limit=20 — cron-specific run history
router.get('/crons/:slug/runs', ...adminMiddleware, async (req: Request, res: Response) => {
  try {
    const limit = Math.min(parseInt(req.query.limit as string) || 20, 100);
    const slug = String(req.params.slug);
    const cron = await fetchOperationalCronSummary(slug);
    if (!cron) {
      res.status(404).json({ error: 'Cron not found' });
      return;
    }
    const runs = await fetchOperationalCronRuns(slug, limit);
    res.json({
      slug,
      cron: mapCronRow(cron),
      runs: runs.map(mapCronRunRow),
    });
  } catch (err) {
    console.error('Weather report cron runs error:', err);
    res.status(500).json({ error: 'Internal server error' });
  }
});

// GET /api/weather-report/mlb-market-completion?eventId=...&completenessStatus=...
router.get('/mlb-market-completion', ...adminMiddleware, async (req: Request, res: Response) => {
  try {
    const filters = {
      eventId: req.query.eventId ? String(req.query.eventId) : null,
      completenessStatus: req.query.completenessStatus ? String(req.query.completenessStatus) : null,
    };
    const allRows = await fetchStoredMlbMarketRows({
      ...filters,
      limit: null,
    });
    const rows = await fetchStoredMlbMarketRows({
      ...filters,
      limit: req.query.limit ? Number(req.query.limit) : 250,
    });
    const summaryBase = summarizeStoredMlbMarketRows(allRows);
    const insights = await fetchMlbMarketCompletionInsights(filters);
    const completed = summaryBase.sourceComplete + summaryBase.multiSourceComplete;
    const gapFillAttempts = summaryBase.multiSourceComplete + summaryBase.incomplete;
    const gapFillSuccesses = summaryBase.multiSourceComplete;
    const completionRate = summaryBase.totalMarkets > 0 ? completed / summaryBase.totalMarkets : 0;
    const gapFillSuccessRate = gapFillAttempts > 0 ? gapFillSuccesses / gapFillAttempts : 0;
    const sourceBuckets = buildSourceBucketSummary(summaryBase.bySource);
    const summary = {
      ...summaryBase,
      completed,
      completionRate,
      completionRatePct: Number((completionRate * 100).toFixed(2)),
      gapFillAttempts,
      gapFillSuccesses,
      gapFillSuccessRate,
      gapFillSuccessRatePct: Number((gapFillSuccessRate * 100).toFixed(2)),
      sourceDistribution: summaryBase.bySource,
      sourceBuckets,
      incompleteWithAlternateLines: insights.incompleteWithAlternateLines,
      incompleteWithAlternateLinesPct: summaryBase.incomplete > 0
        ? Number(((insights.incompleteWithAlternateLines / summaryBase.incomplete) * 100).toFixed(2))
        : 0,
      incompleteWithoutAlternateLines: insights.incompleteWithoutAlternateLines,
      incompleteWithoutAlternateLinesPct: summaryBase.incomplete > 0
        ? Number(((insights.incompleteWithoutAlternateLines / summaryBase.incomplete) * 100).toFixed(2))
        : 0,
      exactSideBreakdown: insights.exactSideBreakdown,
    };

    res.json({
      summary,
      totalCount: allRows.length,
      count: rows.length,
      appliedFilters: {
        eventId: filters.eventId,
        completenessStatus: filters.completenessStatus,
      },
      markets: rows.map((row: any) => ({
        eventId: row.event_id,
        startsAt: row.starts_at,
        homeTeam: row.home_team,
        awayTeam: row.away_team,
        playerId: row.player_id,
        playerName: row.player_name,
        teamShort: row.team_short,
        statType: row.stat_type,
        normalizedStatType: row.normalized_stat_type,
        line: Number(row.line),
        marketName: row.market_name,
        primarySource: row.primary_source,
        completionMethod: row.completion_method,
        completenessStatus: row.completeness_status,
        isGapFilled: row.is_gap_filled,
        availableSides: row.available_sides || [],
        over: row.over_payload,
        under: row.under_payload,
        sourceMap: row.source_map || [],
        rawMarketCount: row.raw_market_count,
        updatedAt: row.updated_at,
      })),
    });
  } catch (err) {
    console.error('Weather report mlb-market-completion error:', err);
    res.status(500).json({ error: 'Internal server error' });
  }
});

// GET /api/weather-report/mlb-alerts — focused MLB operational alert view
router.get('/mlb-alerts', ...adminMiddleware, async (req: Request, res: Response) => {
  try {
    const dateET = (req.query.date_et as string) || new Date().toLocaleDateString('en-CA', { timeZone: 'America/New_York' });
    const assets = await getMlbAlertAssets(dateET);
    const issues = await getStoredMlbZeroCandidateIssues(dateET);
    const computedIssues = issues.length > 0 ? null : await getMlbZeroCandidateIssues(dateET);
    const issueRows = issues.length > 0 ? issues : (computedIssues || []);
    res.json({
      date_et: dateET,
      summary: summarizeMlbAlertAssets(assets),
      issue_summary: summarizeMlbZeroCandidateIssues(issueRows),
      issues: issueRows,
      issue_source: issues.length > 0 ? 'stored' : 'computed',
      assets,
    });
  } catch (err) {
    console.error('Weather report mlb-alerts error:', err);
    res.status(500).json({ error: 'Internal server error' });
  }
});

// GET /api/weather-report/mlb-alert-history?days=7 — recent MLB issue trends
router.get('/mlb-alert-history', ...adminMiddleware, async (req: Request, res: Response) => {
  try {
    const days = Number(req.query.days || 7);
    const history = await getMlbIssueHistory(days);
    res.json({
      days: Math.max(1, Math.min(Number.isFinite(days) ? days : 7, 30)),
      ...history,
    });
  } catch (err) {
    console.error('Weather report mlb-alert-history error:', err);
    res.status(500).json({ error: 'Internal server error' });
  }
});

// GET /api/weather-report/mlb-coverage?date_et=YYYY-MM-DD — focused MLB slate coverage view
router.get('/mlb-coverage', ...adminMiddleware, async (req: Request, res: Response) => {
  try {
    const dateET = await resolveMlbCoverageDate(req.query.date_et ? String(req.query.date_et) : null);
    if (!dateET) {
      res.status(404).json({ error: 'No MLB events found' });
      return;
    }

    const rows = await fetchMlbCoverageRows(dateET);
    res.json({
      date_et: dateET,
      summary: summarizeMlbCoverageRows(rows),
      events: rows.map((row) => ({
        eventId: row.event_id,
        startsAt: row.starts_at,
        matchup: `${row.away_short || row.away_team} @ ${row.home_short || row.home_team}`,
        hasForecast: Boolean(row.has_forecast),
        activePlayerProps: Number(row.active_player_props || 0),
        stalePlayerProps: Number(row.stale_player_props || 0),
        activeTeamProps: Number(row.active_team_props || 0),
        staleTeamProps: Number(row.stale_team_props || 0),
        rawFeedRows: Number(row.raw_feed_rows || 0),
        normalizedMarketRows: Number(row.normalized_market_rows || 0),
        gameOddsRows: Number(row.game_odds_rows || 0),
        lineMovementRows: Number(row.line_movement_rows || 0),
        injuryRows: Number(row.injury_rows || 0),
        matchupNewsRows: Number(row.matchup_news_rows || 0),
      })),
    });
  } catch (err) {
    console.error('Weather report mlb-coverage error:', err);
    res.status(500).json({ error: 'Internal server error' });
  }
});

// ── Cache-First Forecast Retrieval (Public) ──

// GET /api/weather-report/forecasts/today?league=nba — today's precomputed forecasts
router.get('/forecasts/today', 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.*, e.home_team, e.away_team, e.home_short, e.away_short, e.starts_at,
             e.moneyline, e.spread, e.total
      FROM rm_forecast_precomputed fp
      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, fp.forecast_type`;

    const { rows } = await pool.query(query, params);
    res.json({
      date_et: dateET,
      forecasts: rows.map((row: any) => ({
        ...row,
        asset: buildAssetResponse(row),
        operational: buildOperationalSummary(row),
      })),
    });
  } catch (err) {
    console.error('Weather report forecasts today error:', err);
    res.status(500).json({ error: 'Internal server error' });
  }
});

// GET /api/weather-report/forecasts/:eventId — cache-first retrieval for a single event
router.get('/forecasts/:eventId', async (req: Request, res: Response) => {
  try {
    const eventId = req.params.eventId;
    const dateET = new Date().toLocaleDateString('en-CA', { timeZone: 'America/New_York' });

    // Enforce today_et
    const { rows } = await pool.query(
      `SELECT * FROM rm_forecast_precomputed
       WHERE event_id = $1 AND date_et = $2 AND status = 'ACTIVE'
       ORDER BY forecast_type, team_side`,
      [eventId, dateET]
    );

    if (rows.length === 0) {
      // Fall back to legacy rm_forecast_cache
      const { rows: legacy } = await pool.query(
        `SELECT * FROM rm_forecast_cache WHERE event_id = $1`,
        [eventId]
      );
      if (legacy.length > 0) {
        res.json({
          source: 'legacy_cache',
          game_markets: legacy[0].forecast_data,
          odds: legacy[0].odds_data,
          confidence: legacy[0].confidence_score,
        });
        return;
      }
      res.status(404).json({ error: 'No precomputed forecast available', available: false });
      return;
    }

    // Organize into buckets
    const gameMarkets = rows.find(r => r.forecast_type === 'GAME_MARKETS');
    const homeProps = rows.find(r => r.forecast_type === 'TEAM_PROPS' && r.team_side === 'home');
    const awayProps = rows.find(r => r.forecast_type === 'TEAM_PROPS' && r.team_side === 'away');

    const assets = rows.map((row: any) => buildAssetResponse(row));
    res.json({
      source: 'precomputed',
      event_id: eventId,
      date_et: dateET,
      assets,
      game_markets: gameMarkets ? {
        payload: gameMarkets.forecast_payload,
        confidence: gameMarkets.confidence_score,
        model_version: gameMarkets.model_version,
        generated_at: gameMarkets.generated_at,
        operational: buildOperationalSummary(gameMarkets),
      } : null,
      team_props_home: homeProps ? {
        team_id: homeProps.team_id,
        payload: homeProps.forecast_payload,
        model_version: homeProps.model_version,
        generated_at: homeProps.generated_at,
        operational: buildOperationalSummary(homeProps),
      } : null,
      team_props_away: awayProps ? {
        team_id: awayProps.team_id,
        payload: awayProps.forecast_payload,
        model_version: awayProps.model_version,
        generated_at: awayProps.generated_at,
        operational: buildOperationalSummary(awayProps),
      } : null,
      game_total: assets.find((asset: any) => asset.asset_type === 'GAME_TOTAL') || null,
      run_line: assets.find((asset: any) => asset.asset_type === 'MLB_RUN_LINE') || null,
      mlb_f5_bundle: assets.find((asset: any) => asset.asset_type === 'MLB_F5') || null,
      mlb_f5_side: assets.find((asset: any) => asset.asset_type === 'MLB_F5_SIDE') || null,
      mlb_f5_total: assets.find((asset: any) => asset.asset_type === 'MLB_F5_TOTAL') || null,
    });
  } catch (err) {
    console.error('Weather report forecast retrieval error:', err);
    res.status(500).json({ error: 'Internal server error' });
  }
});

// GET /api/weather-report/forecasts/:eventId/props?team=home|away — team props from cache
router.get('/forecasts/:eventId/props', async (req: Request, res: Response) => {
  try {
    const eventId = req.params.eventId;
    const team = req.query.team as string;
    const dateET = new Date().toLocaleDateString('en-CA', { timeZone: 'America/New_York' });

    if (!team || (team !== 'home' && team !== 'away')) {
      res.status(400).json({ error: 'team query param must be "home" or "away"' });
      return;
    }

    const { rows } = await pool.query(
      `SELECT * FROM rm_forecast_precomputed
       WHERE event_id = $1 AND date_et = $2 AND forecast_type = 'TEAM_PROPS'
         AND team_side = $3
       ORDER BY CASE WHEN status = 'ACTIVE' THEN 0 WHEN status = 'STALE' THEN 1 ELSE 2 END, generated_at DESC`,
      [eventId, dateET, team]
    );

    if (rows.length === 0) {
      // Fall back to legacy rm_team_props_cache
      const { rows: legacy } = await pool.query(
        `SELECT * FROM rm_team_props_cache WHERE event_id = $1 AND team = $2`,
        [eventId, team]
      );
      if (legacy.length > 0) {
        res.json({ source: 'legacy_cache', props: legacy[0].props_data });
        return;
      }
      res.status(404).json({ error: 'No cached team props available', available: false });
      return;
    }

    res.json({
      source: 'precomputed',
      team_id: rows[0].team_id,
      team_side: team,
      status: rows[0].status,
      payload: rows[0].forecast_payload,
      model_version: rows[0].model_version,
      generated_at: rows[0].generated_at,
      operational: buildOperationalSummary(rows[0]),
    });
  } catch (err) {
    console.error('Weather report props retrieval error:', err);
    res.status(500).json({ error: 'Internal server error' });
  }
});

export default router;