import { Router, Request, Response } from 'express';
import pool from '../db';
import { getTeamAbbr, formatScoreAbbr } from '../lib/team-abbreviations';
import { selectBenchmarkForecast, computeWinRate, sanitizeClosingSpreadForLeague } from '../services/benchmark-grading';
import { canonicalizePlayerPropStat, getPlayerPropLabelForLeague } from '../services/player-prop-market-registry';
import { sanitizeGameOddsForLeague } from '../services/sgo';

const router = Router();
const GRADE_TOTALS = process.env.GRADE_TOTALS_ENABLED === 'true';

const RM_PROP_TIER_CASE = `
  CASE
    WHEN model_confidence >= 0.85 THEN 'A+'
    WHEN model_confidence >= 0.70 THEN 'A'
    WHEN model_confidence >= 0.55 THEN 'B+'
    WHEN model_confidence IS NOT NULL THEN 'B'
    ELSE NULL
  END
`;

const BENCHMARK_GRADED_FILTER = `AND final_grade IS NOT NULL`;

function spreadForecastTypeFilter(alias = ''): string {
  if (!GRADE_TOTALS) return '';
  const prefix = alias ? `${alias}.` : '';
  return ` AND (${prefix}forecast_type = 'spread' OR ${prefix}forecast_type IS NULL)`;
}

function normalizeSqlAlias(alias = ''): string {
  const normalized = String(alias || '').trim();
  if (!normalized) return '';
  if (!/^[a-z_][a-z0-9_]*$/i.test(normalized)) {
    throw new Error(`Invalid SQL alias: ${alias}`);
  }
  return `${normalized}.`;
}

function publicGameConfidenceSql(alias = ''): string {
  const prefix = normalizeSqlAlias(alias);
  return `COALESCE(${prefix}composite_confidence, ${prefix}confidence_score)`;
}

function archivedGameConfidenceSql(archiveAlias = 'af'): string {
  return publicGameConfidenceSql(archiveAlias);
}

function publicGameTierCase(alias = ''): string {
  const confidenceSql = publicGameConfidenceSql(alias);
  return `
    CASE
      WHEN ${confidenceSql} >= 0.85 THEN 'A+'
      WHEN ${confidenceSql} >= 0.70 THEN 'A'
      WHEN ${confidenceSql} >= 0.55 THEN 'B+'
      WHEN ${confidenceSql} IS NOT NULL THEN 'B'
      ELSE NULL
    END
  `;
}

function parseJsonLike(value: unknown): any | null {
  if (!value) return null;
  if (typeof value === 'object') return value;
  if (typeof value !== 'string') return null;
  try {
    return JSON.parse(value);
  } catch {
    return null;
  }
}

function sanitizeDisplayMoneyline(value: unknown): number | null {
  if (value == null) return null;
  const numeric = Number(value);
  if (!Number.isFinite(numeric) || numeric === 0) return null;
  // Public ledgers should not display corrupted alt-market prices like -19463 or +2548.
  if (Math.abs(numeric) > 1000) return null;
  return numeric;
}

type TierAggregate = {
  total: number;
  wins: number;
  pushes: number;
};

type LeagueAggregate = {
  total: number;
  wins: number;
  pushes: number;
  avgClv: number | null;
  clvPosRate: number | null;
  tierData: Record<string, TierAggregate>;
};

function publicPlayerMetricStatKeySql(alias = 'p'): string {
  return `CASE
    WHEN ${alias}.league = 'nhl' AND LOWER(COALESCE(${alias}.prop_stat, '')) IN ('shots_ongoal', 'shots_on_goal', 'sog') THEN 'shots'
    WHEN ${alias}.league = 'nhl' AND LOWER(COALESCE(${alias}.prop_stat, '')) IN ('goalie_saves', 'saves', 'save') THEN 'saves'
    WHEN LOWER(COALESCE(${alias}.prop_stat, '')) IN ('threepointersmade', 'three_pointers_made', 'threes') THEN 'threePointersMade'
    WHEN ${alias}.league = 'mlb' AND LOWER(COALESCE(${alias}.prop_stat, '')) IN ('point', 'points', 'run', 'runs', 'batting_runs') THEN 'mlb_runs'
    WHEN ${alias}.league = 'mlb' AND LOWER(COALESCE(${alias}.prop_stat, '')) IN ('hit', 'hits', 'batting_hits') THEN 'hits'
    WHEN ${alias}.league = 'mlb' AND LOWER(COALESCE(${alias}.prop_stat, '')) IN ('homerun', 'homeruns', 'home_run', 'home_runs', 'batting_homeruns') THEN 'batting_homeRuns'
    WHEN ${alias}.league = 'mlb' AND LOWER(COALESCE(${alias}.prop_stat, '')) IN ('strikeout', 'strikeouts', 'batting_strikeouts', 'pitching_strikeouts') THEN 'pitching_strikeouts'
    WHEN ${alias}.league = 'mlb' AND LOWER(COALESCE(${alias}.prop_stat, '')) IN ('batting_totalbases', 'totalbase', 'totalbases', 'total_bases') THEN 'total_bases'
    WHEN ${alias}.league = 'mlb' AND LOWER(COALESCE(${alias}.prop_stat, '')) IN ('batting_rbi', 'rbi', 'rbis') THEN 'batting_RBI'
    WHEN ${alias}.league = 'mlb' AND LOWER(COALESCE(${alias}.prop_stat, '')) IN ('batting_basesonballs', 'batting_walks', 'battingwalks', 'walk', 'walks') THEN 'batting_basesOnBalls'
    WHEN ${alias}.league = 'mlb' AND LOWER(COALESCE(${alias}.prop_stat, '')) IN ('pitching_hits', 'hitsallowed', 'hitallowed') THEN 'pitching_hits'
    WHEN ${alias}.league = 'mlb' AND LOWER(COALESCE(${alias}.prop_stat, '')) IN ('pitching_basesonballs', 'pitching_walks', 'pitchingwalks', 'walksallowed', 'walkallowed') THEN 'pitching_basesOnBalls'
    WHEN ${alias}.league = 'mlb' AND LOWER(COALESCE(${alias}.prop_stat, '')) IN ('pitching_earnedruns', 'earnedrun', 'earnedruns') THEN 'pitching_earnedRuns'
    WHEN ${alias}.league = 'mlb' AND LOWER(COALESCE(${alias}.prop_stat, '')) IN ('pitching_outs', 'outsrecorded', 'outs') THEN 'pitching_outs'
    ELSE ${alias}.prop_stat
  END`;
}

/** Derive Rainwire blog slug — must match blog-generator.ts generateSlug() */
function makeSlug(league: string, homeTeam: string, awayTeam: string, gameDate: string): string {
  const clean = (s: string) => s.toLowerCase().replace(/[^a-z0-9]+/g, '-').replace(/(^-|-$)/g, '');
  return `${clean(league)}-${clean(awayTeam)}-vs-${clean(homeTeam)}-${gameDate}`;
}

/** Batch-resolve which slugs have published Rainwire articles */
async function resolveRainwireSlugs(slugs: string[]): Promise<Set<string>> {
  if (slugs.length === 0) return new Set();
  const { rows } = await pool.query(
    `SELECT slug FROM rm_blog_posts WHERE slug = ANY($1) AND status = 'published'`,
    [slugs]
  );
  return new Set(rows.map((r: any) => r.slug));
}

// GET /api/stats/model — public, no auth required
router.get('/model', async (_req: Request, res: Response) => {
  try {
    // Forecast count + avg confidence (always available)
    const forecastStats = await pool.query(
      `SELECT COUNT(*) as total, AVG(COALESCE(composite_confidence, confidence_score)) as avg_conf
       FROM rm_forecast_cache
       WHERE COALESCE(is_minor_league, false) = false`
    );
    const totalForecasts = parseInt(forecastStats.rows[0]?.total || '0', 10);
    const avgConfidence = forecastStats.rows[0]?.avg_conf
      ? Math.round(parseFloat(forecastStats.rows[0].avg_conf) * 1000) / 10
      : null;

    // Benchmark-graded stats from rm_forecast_accuracy_v2 — only rows with final_grade count.
    const gradedStats = await pool.query(
      `SELECT
         COUNT(*) as total_graded,
         SUM(CASE WHEN final_grade = 'W' THEN 1 ELSE 0 END) as wins,
         SUM(CASE WHEN final_grade = 'P' THEN 1 ELSE 0 END) as pushes,
         AVG(accuracy_pct) as avg_accuracy
       FROM rm_forecast_accuracy_v2
       WHERE actual_winner IS NOT NULL
         AND COALESCE(is_preseason, false) = false
         ${BENCHMARK_GRADED_FILTER}
         ${spreadForecastTypeFilter()}`
    );
    const resolvedUngradedStats = await pool.query(
      `SELECT COUNT(*) as total
       FROM rm_forecast_accuracy_v2
       WHERE actual_winner IS NOT NULL
         AND final_grade IS NULL
         AND COALESCE(is_preseason, false) = false
         ${spreadForecastTypeFilter()}`
    );
    const totalGraded = parseInt(gradedStats.rows[0]?.total_graded || '0', 10);
    const wins = parseInt(gradedStats.rows[0]?.wins || '0', 10);
    const gradedPushes = parseInt(gradedStats.rows[0]?.pushes || '0', 10);
    const gradedDecisions = totalGraded - gradedPushes;
    const winRate = gradedDecisions > 0 ? Math.round((wins / gradedDecisions) * 1000) / 10 : null;
    const ungradedResolved = parseInt(resolvedUngradedStats.rows[0]?.total || '0', 10);
    const gameLosses = Math.max(gradedDecisions - wins, 0);

    // Native graded player props from rm_pick_clv
    const propStats = await pool.query(
      `SELECT
         COUNT(*) as total,
         SUM(CASE WHEN result='W' THEN 1 ELSE 0 END) as wins,
         SUM(CASE WHEN result='L' THEN 1 ELSE 0 END) as losses,
         SUM(CASE WHEN result='P' THEN 1 ELSE 0 END) as pushes
       FROM rm_pick_clv
       WHERE pick_type = 'player_prop'
         AND result IS NOT NULL
         AND v2_eligible = true`
    );
    const propGraded = parseInt(propStats.rows[0]?.total || '0', 10);
    const propWins = parseInt(propStats.rows[0]?.wins || '0', 10);
    const propLosses = parseInt(propStats.rows[0]?.losses || '0', 10);
    const propPushes = parseInt(propStats.rows[0]?.pushes || '0', 10);
    const propDecisions = propGraded - propPushes;
    const propWinRate = propDecisions > 0 ? Math.round((propWins / propDecisions) * 1000) / 10 : null;

    // CLV stats from native graded player props
    let avgCLV: number | null = null;
    let clvPositiveRate: number | null = null;
    let roi: number | null = null;
    let trackingSince: string | null = null;
    let clvSource: 'player_props' | 'benchmark_games' | null = null;
    let clvSourceLabel: string | null = null;

    try {
      const clvResult = await pool.query(
        `SELECT
           ROUND(AVG(clv_line)::numeric, 2) as avg_clv_pct,
           ROUND(AVG(CASE WHEN clv_line > 0 THEN 1 ELSE 0 END)::numeric * 100, 1) as pos_clv_rate,
           MIN(COALESCE(resolved_at, created_at)) as first_graded
         FROM rm_pick_clv
         WHERE pick_type = 'player_prop'
           AND result IS NOT NULL
           AND v2_eligible = true
           AND clv_line IS NOT NULL`
      );
      const clvRow = clvResult.rows[0];
      if (clvRow) {
        avgCLV = clvRow.avg_clv_pct ? parseFloat(clvRow.avg_clv_pct) : null;
        clvPositiveRate = clvRow.pos_clv_rate ? parseFloat(clvRow.pos_clv_rate) : null;
        trackingSince = clvRow.first_graded || null;
        if (trackingSince || avgCLV !== null || clvPositiveRate !== null) {
          clvSource = 'player_props';
          clvSourceLabel = 'Closing-line tracked player props';
        }
      }

      // If no prop CLV, fall back to benchmark games
      if (!trackingSince && totalGraded > 0) {
        const faClv = await pool.query(
          `SELECT AVG(accuracy_pct) as avg_clv,
             SUM(CASE WHEN accuracy_pct > 0 THEN 1 ELSE 0 END)::float / NULLIF(COUNT(*), 0) * 100 as clv_positive_rate,
             MIN(resolved_at) as first_resolved
           FROM rm_forecast_accuracy_v2
           WHERE actual_winner IS NOT NULL
             AND COALESCE(is_preseason, false) = false
             ${BENCHMARK_GRADED_FILTER}
             ${spreadForecastTypeFilter()}`
        );
        if (faClv.rows[0]) {
          avgCLV = faClv.rows[0].avg_clv ? Math.round(parseFloat(faClv.rows[0].avg_clv) * 10) / 10 : null;
          clvPositiveRate = faClv.rows[0].clv_positive_rate ? Math.round(parseFloat(faClv.rows[0].clv_positive_rate) * 10) / 10 : null;
          trackingSince = faClv.rows[0].first_resolved || null;
          if (trackingSince || avgCLV !== null || clvPositiveRate !== null) {
            clvSource = 'benchmark_games';
            clvSourceLabel = 'Benchmark-graded team forecasts';
          }
        }
      }
    } catch {
      // CLV columns may not exist yet
    }

    // Primary headline now reflects benchmark games only.
    const effectiveGraded = totalGraded;
    const effectiveWinRate = winRate;

    // By league breakdown
    const byLeagueResult = await pool.query(
      `SELECT league, COUNT(*) as total,
         AVG(COALESCE(composite_confidence, confidence_score)) as avg_conf
       FROM rm_forecast_cache
       WHERE COALESCE(is_minor_league, false) = false
       GROUP BY league
       ORDER BY COUNT(*) DESC`
    );
    const byLeague: Record<string, any> = {};
    for (const row of byLeagueResult.rows) {
      byLeague[row.league] = {
        totalForecasts: parseInt(row.total, 10),
        avgConfidence: row.avg_conf ? Math.round(parseFloat(row.avg_conf) * 1000) / 10 : null,
      };
    }

    res.json({
      totalForecasts,
      avgConfidence,
      totalGraded: effectiveGraded,
      winRate: effectiveWinRate,
      ungradedResolved,
      roi,
      roiAvailable: false,
      roiReason: 'ROI is intentionally hidden until stake sizing and price history are normalized across the public game and prop ledgers.',
      avgCLV,
      clvPositiveRate,
      trackingSince,
      clvSource,
      clvSourceLabel,
      benchmarkGames: {
        graded: totalGraded,
        wins,
        losses: gameLosses,
        pushes: gradedPushes,
        winRate,
        resolvedAwaitingGrade: ungradedResolved,
        sourceLabel: 'Benchmark-graded team forecasts',
      },
      playerProps: {
        graded: propGraded,
        wins: propWins,
        losses: propLosses,
        pushes: propPushes,
        winRate: propWinRate,
        sourceLabel: 'Native graded player props',
      },
      combined: {
        graded: totalGraded + propGraded,
        wins: wins + propWins,
        losses: gameLosses + propLosses,
        pushes: gradedPushes + propPushes,
        winRate: (() => {
          const combinedGraded = totalGraded + propGraded;
          const combinedPushes = gradedPushes + propPushes;
          const combinedDecisions = combinedGraded - combinedPushes;
          return combinedDecisions > 0 ? Math.round(((wins + propWins) / combinedDecisions) * 1000) / 10 : null;
        })(),
        sourceLabel: 'Games and props reported separately',
      },
      byLeague,
    });
  } catch (err) {
    console.error('Stats error:', err);
    res.status(500).json({ error: 'Failed to fetch stats' });
  }
});

// GET /api/stats/clv — aggregated CLV by league, pick_type, storm_tier
router.get('/clv', async (_req: Request, res: Response) => {
  try {
    // Summary by league + pick type from rm_pick_clv
    const summaryResult = await pool.query(`
      SELECT league, pick_type,
        COUNT(*) as total,
        SUM(CASE WHEN result='W' THEN 1 ELSE 0 END) as wins,
        ROUND(AVG(clv_line)::numeric, 2) as avg_clv,
        ROUND(AVG(CASE WHEN clv_line > 0 THEN 1 ELSE 0 END) * 100, 1) as pos_rate,
        ROUND(SUM(CASE WHEN result='W' THEN 1 ELSE 0 END)::numeric /
          NULLIF(SUM(CASE WHEN result IN ('W','L') THEN 1 ELSE 0 END), 0) * 100, 1) as win_rate
      FROM rm_pick_clv
      WHERE result IN ('W','L')
        AND v2_eligible = true
      GROUP BY league, pick_type
      ORDER BY league, pick_type
    `);

    const summary: Record<string, any> = {};
    for (const row of summaryResult.rows) {
      if (!summary[row.league]) summary[row.league] = {};
      const pickKey = row.pick_type.includes('prop') ? 'props' : 'game';
      summary[row.league][pickKey] = {
        avgClv: parseFloat(row.avg_clv) || 0,
        posRate: parseFloat(row.pos_rate) || 0,
        winRate: parseFloat(row.win_rate) || 0,
        total: parseInt(row.total, 10),
      };
    }

    // Bridge PIFF track record from DailyPick if rm_pick_clv has no resolved data
    if (Object.keys(summary).length === 0) {
      try {
        const { rows: piffRows } = await pool.query(`
          SELECT
            CASE WHEN algo_version LIKE '%nhl%' THEN 'nhl'
                 WHEN algo_version LIKE '%soccer%' THEN 'soccer'
                 ELSE 'nba' END as league,
            COUNT(*) as total,
            SUM(CASE WHEN result='W' THEN 1 ELSE 0 END) as wins,
            ROUND(AVG(clv_value)::numeric * 100, 2) as avg_clv,
            ROUND(AVG(CASE WHEN clv_value > 0 THEN 1 ELSE 0 END)::numeric * 100, 1) as pos_rate,
            ROUND(SUM(CASE WHEN result='W' THEN 1 ELSE 0 END)::numeric /
              NULLIF(SUM(CASE WHEN result IN ('W','L') THEN 1 ELSE 0 END), 0) * 100, 1) as win_rate
          FROM "DailyPick"
          WHERE result IN ('W','L') AND clv_value IS NOT NULL
            AND rec_line IS NOT NULL
          GROUP BY 1
        `);
        for (const row of piffRows) {
          summary[row.league] = {
            props: {
              avgClv: parseFloat(row.avg_clv) || 0,
              posRate: parseFloat(row.pos_rate) || 0,
              winRate: parseFloat(row.win_rate) || 0,
              total: parseInt(row.total, 10),
            },
          };
        }
      } catch { /* DailyPick may not have data */ }
    }

    // By storm tier
    const tierResult = await pool.query(`
      SELECT storm_tier,
        ROUND(AVG(clv_line)::numeric, 2) as avg_clv,
        ROUND(SUM(CASE WHEN result='W' THEN 1 ELSE 0 END)::numeric /
          NULLIF(SUM(CASE WHEN result IN ('W','L') THEN 1 ELSE 0 END), 0) * 100, 1) as win_rate,
        COUNT(*) as total
      FROM rm_pick_clv
      WHERE result IN ('W','L') AND storm_tier IS NOT NULL
        AND v2_eligible = true
      GROUP BY storm_tier
      ORDER BY storm_tier DESC
    `);

    const byTier: Record<string, any> = {};
    for (const row of tierResult.rows) {
      byTier[String(row.storm_tier)] = {
        avgClv: parseFloat(row.avg_clv) || 0,
        winRate: parseFloat(row.win_rate) || 0,
        total: parseInt(row.total, 10),
      };
    }

    res.json({ summary, byTier });
  } catch (err) {
    console.error('CLV stats error:', err);
    res.status(500).json({ error: 'Failed to fetch CLV stats' });
  }
});

// GET /api/stats/clv/recent — last 50 resolved picks with CLV values
router.get('/clv/recent', async (_req: Request, res: Response) => {
  try {
    // First try Rainmaker's own CLV table
    const { rows } = await pool.query(`
      SELECT league, pick_type, player_name, prop_stat, direction,
        rec_line, close_line, clv_line, result, storm_tier, created_at
      FROM rm_pick_clv
      WHERE result IS NOT NULL AND v2_eligible = true
      ORDER BY resolved_at DESC NULLS LAST, created_at DESC
      LIMIT 50
    `);

    // If Rainmaker CLV is empty, bridge from DailyPick (PIFF track record)
    if (rows.length === 0) {
      const { rows: piffRows } = await pool.query(`
        SELECT 'nba' as league, 'player_prop' as pick_type,
          player_name, stat as prop_stat, over_under as direction,
          rec_line, clv_line as close_line,
          ROUND(clv_value::numeric * 100, 2) as clv_line,
          result, NULL::int as storm_tier, game_date as created_at
        FROM "DailyPick"
        WHERE result IN ('W','L') AND clv_value IS NOT NULL
          AND rec_line IS NOT NULL
        ORDER BY game_date DESC
        LIMIT 50
      `);

      return res.json({
        source: 'piff',
        recentPicks: piffRows.map((r: any) => ({
          league: r.league,
          pickType: r.pick_type,
          player: r.player_name,
          propStat: canonicalizePlayerPropStat(r.league, r.prop_stat) || r.prop_stat,
          direction: r.direction,
          recLine: parseFloat(r.rec_line),
          closeLine: parseFloat(r.close_line),
          clvLine: parseFloat(r.clv_line),
          result: r.result,
          stormTier: r.storm_tier,
          date: r.created_at,
        })),
      });
    }

    res.json({
      source: 'rainmaker',
      recentPicks: rows.map((r: any) => {
        const canonicalPropStat = canonicalizePlayerPropStat(r.league, r.prop_stat) || r.prop_stat;
        return {
          league: r.league,
          pickType: r.pick_type,
          player: r.player_name,
          propStat: canonicalPropStat,
          direction: r.direction,
          recLine: parseFloat(r.rec_line),
          closeLine: parseFloat(r.close_line),
          clvLine: parseFloat(r.clv_line),
          result: r.result,
          stormTier: r.storm_tier,
          date: r.created_at,
        };
      }),
    });
  } catch (err) {
    console.error('CLV recent error:', err);
    res.status(500).json({ error: 'Failed to fetch recent CLV' });
  }
});

// GET /api/stats/kpi — public KPI dashboard
router.get('/kpi', async (_req: Request, res: Response) => {
  try {
    // Model KPI stats from materialized view
    let modelKpis: any[] = [];
    try {
      const { rows } = await pool.query('SELECT * FROM rm_model_kpi_stats');
      modelKpis = rows;
    } catch { /* view may be empty */ }

    // CLV summary from materialized view
    let clvSummary: any[] = [];
    try {
      const { rows } = await pool.query('SELECT * FROM rm_clv_summary');
      clvSummary = rows;
    } catch { /* view may be empty */ }

    // Composite confidence stats
    const compositeStats = await pool.query(`
      SELECT
        COUNT(*) FILTER (WHERE composite_confidence IS NOT NULL) as composite_count,
        AVG(composite_confidence) FILTER (WHERE composite_confidence IS NOT NULL) as avg_composite,
        AVG(composite_confidence) FILTER (WHERE composite_confidence IS NOT NULL AND league = 'nba') as avg_composite_nba
      FROM rm_forecast_cache
    `).catch(() => ({ rows: [{}] }));

    const propIntelResult = await pool.query(`
      SELECT
        COUNT(*)::int as total,
        SUM(CASE WHEN result = 'W' THEN 1 ELSE 0 END)::int as wins,
        SUM(CASE WHEN result = 'P' THEN 1 ELSE 0 END)::int as pushes,
        ROUND(AVG(clv_line) FILTER (WHERE clv_line IS NOT NULL)::numeric, 2) as avg_clv
      FROM rm_pick_clv
      WHERE pick_type = 'player_prop'
        AND result IS NOT NULL
        AND v2_eligible = true
    `).catch(() => ({ rows: [{}] }));

    const propIntel = propIntelResult.rows[0] || {};
    const propIntelTotal = parseInt(propIntel.total || '0', 10);
    const propIntelWins = parseInt(propIntel.wins || '0', 10);
    const propIntelPushes = parseInt(propIntel.pushes || '0', 10);
    const propIntelDecisions = propIntelTotal - propIntelPushes;
    const propIntelWinRate = propIntelDecisions > 0 ? Math.round((propIntelWins / propIntelDecisions) * 1000) / 10 : null;
    const propIntelAvgClv = propIntel.avg_clv != null ? parseFloat(propIntel.avg_clv) : null;
    const clvPart = propIntelAvgClv != null ? ` | ${propIntelAvgClv > 0 ? '+' : ''}${propIntelAvgClv} CLV` : '';
    const piffTrackRecord = propIntelDecisions > 0
      ? `${propIntelWinRate}% HR${clvPart} (${propIntelTotal})`
      : null;

    res.json({
      modelKpis,
      clvSummary,
      compositeStats: compositeStats.rows[0] || {},
      piffTrackRecord,
      digimonTrackRecord: null,
    });
  } catch (err) {
    console.error('KPI stats error:', err);
    res.status(500).json({ error: 'Failed to fetch KPI stats' });
  }
});

// ── Model Performance V2 ───────────────────────────────────
// GET /api/stats/performance?window=7|30&page=1&limit=25
router.get('/performance', async (req: Request, res: Response) => {
  try {
    const windowDays = parseInt(req.query.window as string) === 30 ? 30 : 7;
    const page = Math.max(1, parseInt(req.query.page as string) || 1);
    const limit = Math.min(100, Math.max(1, parseInt(req.query.limit as string) || 25));
    const offset = (page - 1) * limit;
    const tierFilter = (req.query.tier as string || '').trim();
    const leagueFilter = (req.query.league as string || '').trim();
    const kindParam = ((req.query.kind as string) || 'all').trim().toLowerCase();
    const performanceKind = kindParam === 'props' || kindParam === 'games' ? kindParam : 'all';
    const includeProps = performanceKind !== 'games';
    const includeGames = performanceKind !== 'props';

    // ─── Summary metrics (rolling window) ───
    const rainmakerPropSummaryPromise = includeProps
      ? pool.query(`
        SELECT
          COUNT(*) as total,
          SUM(CASE WHEN result='W' THEN 1 ELSE 0 END) as wins,
          SUM(CASE WHEN result='P' THEN 1 ELSE 0 END) as pushes,
          ROUND(AVG(clv_line)::numeric, 2) as avg_clv,
          ROUND(AVG(CASE WHEN clv_line > 0 THEN 1 ELSE 0 END)::numeric * 100, 1) as clv_pos_rate,
          COUNT(*) FILTER (WHERE clv_line IS NOT NULL) as clv_sample,
          COUNT(DISTINCT DATE(COALESCE(resolved_at, created_at))) as resolved_dates,
          MIN(COALESCE(resolved_at, created_at)) as first_resolved,
          MAX(COALESCE(resolved_at, created_at)) as last_resolved
        FROM rm_pick_clv
        WHERE pick_type = 'player_prop'
          AND result IS NOT NULL
          AND v2_eligible = true
          AND COALESCE(resolved_at, created_at) >= NOW() - ($1::int || ' days')::interval
      `, [windowDays])
      : Promise.resolve({ rows: [{ total: '0', wins: '0', pushes: '0' }] });
    const gameSummaryPromise = includeGames
      ? pool.query(`
        SELECT
          COUNT(*) as total,
          SUM(CASE WHEN final_grade = 'W' THEN 1 ELSE 0 END) as wins,
          SUM(CASE WHEN final_grade = 'P' THEN 1 ELSE 0 END) as pushes
        FROM rm_forecast_accuracy_v2
        WHERE actual_winner IS NOT NULL AND COALESCE(is_preseason, false) = false
          ${BENCHMARK_GRADED_FILTER}
          ${spreadForecastTypeFilter()}
          AND COALESCE(resolved_at, event_date::timestamp) >= NOW() - ($1::int || ' days')::interval
      `, [windowDays])
      : Promise.resolve({ rows: [{ total: '0', wins: '0', pushes: '0' }] });
    const [rainmakerPropSummaryResult, gameResult] = await Promise.all([
      rainmakerPropSummaryPromise,
      gameSummaryPromise,
    ]);
    const rainmakerPropSummary = rainmakerPropSummaryResult.rows[0];
    const rainmakerPropTotal = parseInt(rainmakerPropSummary?.total || '0', 10);
    const rainmakerPropWins = parseInt(rainmakerPropSummary?.wins || '0', 10);
    const rainmakerPropPushes = parseInt(rainmakerPropSummary?.pushes || '0', 10);
    const propTotalForecasts = rainmakerPropTotal;
    const propWins = rainmakerPropWins;
    const propPushes = rainmakerPropPushes;
    const avgClv = rainmakerPropSummary?.avg_clv ? parseFloat(rainmakerPropSummary.avg_clv) : null;
    const clvPosRate = rainmakerPropSummary?.clv_pos_rate ? parseFloat(rainmakerPropSummary.clv_pos_rate) : null;
    const clvSample = parseInt(rainmakerPropSummary?.clv_sample || '0', 10);
    const resolvedDates = parseInt(rainmakerPropSummary?.resolved_dates || '0', 10);

    // Supplement with rm_forecast_accuracy_v2 for game-level forecasts.
    // Only benchmark-graded rows count in benchmark performance.
    const gameTotalRaw = parseInt(gameResult.rows[0]?.total || '0', 10);
    const gameWinsRaw = parseInt(gameResult.rows[0]?.wins || '0', 10);
    const gamePushesRaw = parseInt(gameResult.rows[0]?.pushes || '0', 10);

    // Combined totals — pushes excluded from win rate denominator
    const combinedTotal = (includeProps ? propTotalForecasts : 0) + (includeGames ? gameTotalRaw : 0);
    const combinedWins = (includeProps ? propWins : 0) + (includeGames ? gameWinsRaw : 0);
    const combinedPushes = (includeProps ? propPushes : 0) + (includeGames ? gamePushesRaw : 0);

    // ─── By Tier ────────────────────────────────────────────
    // Only use current PIFF 3.x prop rows in tier stats.
    // Older prop engines were not calibrated on the same confidence scale,
    // so mixing them into A+/A/B+ makes the "best picks" buckets misleading.
    const rainmakerPropTierPromise = includeProps
      ? pool.query(`
        SELECT
          ${RM_PROP_TIER_CASE} as tier,
          COUNT(*) as total,
          SUM(CASE WHEN result='W' THEN 1 ELSE 0 END) as wins,
          SUM(CASE WHEN result='P' THEN 1 ELSE 0 END) as pushes
        FROM rm_pick_clv
        WHERE pick_type = 'player_prop'
          AND result IS NOT NULL
          AND v2_eligible = true
          AND COALESCE(resolved_at, created_at) >= NOW() - ($1::int || ' days')::interval
        GROUP BY 1
        ORDER BY 1
      `, [windowDays])
      : Promise.resolve({ rows: [] });

    // Also get game-level tiers from rm_forecast_accuracy_v2 + rm_forecast_cache confidence.
    // Only benchmark-graded rows count here.
    const gameTierPromise = includeGames
      ? pool.query(`
      SELECT
        ${publicGameTierCase('af')} as tier,
        COUNT(*) as total,
        SUM(CASE WHEN fa.final_grade = 'W' THEN 1 ELSE 0 END) as wins,
        SUM(CASE WHEN fa.final_grade = 'P' THEN 1 ELSE 0 END) as pushes
      FROM rm_forecast_accuracy_v2 fa
      LEFT JOIN rm_archived_forecasts af ON fa.event_id = af.event_id
      WHERE fa.actual_winner IS NOT NULL
        AND fa.final_grade IS NOT NULL
        ${spreadForecastTypeFilter('fa')}
        AND COALESCE(fa.resolved_at, fa.event_date::timestamp) >= NOW() - ($1::int || ' days')::interval
        AND ${archivedGameConfidenceSql('af')} IS NOT NULL
      GROUP BY 1
    `, [windowDays])
      : Promise.resolve({ rows: [] });
    const [rainmakerPropTierResult, gameTierResult] = await Promise.all([
      rainmakerPropTierPromise,
      gameTierPromise,
    ]);

    // Merge tier results — track W, L, P separately
    const tierMap: Record<string, { total: number; wins: number; pushes: number }> = {};
    if (includeProps) {
      for (const row of rainmakerPropTierResult.rows) {
        if (!row.tier) continue;
        if (!tierMap[row.tier]) tierMap[row.tier] = { total: 0, wins: 0, pushes: 0 };
        tierMap[row.tier].total += parseInt(row.total, 10);
        tierMap[row.tier].wins += parseInt(row.wins, 10);
        tierMap[row.tier].pushes += parseInt(row.pushes || '0', 10);
      }
    }
    if (includeGames) {
      for (const row of gameTierResult.rows) {
        if (!tierMap[row.tier]) tierMap[row.tier] = { total: 0, wins: 0, pushes: 0 };
        tierMap[row.tier].total += parseInt(row.total, 10);
        tierMap[row.tier].wins += parseInt(row.wins, 10);
        tierMap[row.tier].pushes += parseInt(row.pushes || '0', 10);
      }
    }
    // ─── By League (rolling window) ───
    const rainmakerPropLeaguePromise = includeProps
      ? pool.query(`
        SELECT
          league,
          COUNT(*) as total,
          SUM(CASE WHEN result='W' THEN 1 ELSE 0 END) as wins,
          SUM(CASE WHEN result='P' THEN 1 ELSE 0 END) as pushes,
          ROUND(AVG(clv_line)::numeric, 2) as avg_clv,
          ROUND(AVG(CASE WHEN clv_line > 0 THEN 1 ELSE 0 END)::numeric * 100, 1) as clv_pos_rate
        FROM rm_pick_clv
        WHERE pick_type = 'player_prop'
          AND result IS NOT NULL
          AND v2_eligible = true
          AND COALESCE(resolved_at, created_at) >= NOW() - ($1::int || ' days')::interval
        GROUP BY 1
        ORDER BY 2 DESC
      `, [windowDays])
      : Promise.resolve({ rows: [] });

    const leagueGamePromise = includeGames
      ? pool.query(`
      SELECT league,
        COUNT(*) as total,
        SUM(CASE WHEN final_grade = 'W' THEN 1 ELSE 0 END) as wins,
        SUM(CASE WHEN final_grade = 'P' THEN 1 ELSE 0 END) as pushes
      FROM rm_forecast_accuracy_v2
      WHERE actual_winner IS NOT NULL AND COALESCE(is_preseason, false) = false
        ${BENCHMARK_GRADED_FILTER}
        ${spreadForecastTypeFilter()}
        AND COALESCE(resolved_at, event_date::timestamp) >= NOW() - ($1::int || ' days')::interval
      GROUP BY league
    `, [windowDays])
      : Promise.resolve({ rows: [] });

    // ─── Per-League Tier Breakdown ───
    const rainmakerPropLeagueTierPromise = includeProps
      ? pool.query(`
        SELECT
          league,
          ${RM_PROP_TIER_CASE} as tier,
          COUNT(*) as total,
          SUM(CASE WHEN result='W' THEN 1 ELSE 0 END) as wins,
          SUM(CASE WHEN result='P' THEN 1 ELSE 0 END) as pushes
        FROM rm_pick_clv
        WHERE pick_type = 'player_prop'
          AND result IS NOT NULL
          AND v2_eligible = true
          AND COALESCE(resolved_at, created_at) >= NOW() - ($1::int || ' days')::interval
        GROUP BY 1, 2
      `, [windowDays])
      : Promise.resolve({ rows: [] });

    const leagueTierGamePromise = includeGames
      ? pool.query(`
      SELECT fa.league,
        ${publicGameTierCase('af')} as tier,
        COUNT(*) as total,
        SUM(CASE WHEN fa.final_grade = 'W' THEN 1 ELSE 0 END) as wins,
        SUM(CASE WHEN fa.final_grade = 'P' THEN 1 ELSE 0 END) as pushes
      FROM rm_forecast_accuracy_v2 fa
      LEFT JOIN rm_archived_forecasts af ON fa.event_id = af.event_id
      WHERE fa.actual_winner IS NOT NULL
        AND fa.final_grade IS NOT NULL
        ${spreadForecastTypeFilter('fa')}
        AND COALESCE(fa.resolved_at, fa.event_date::timestamp) >= NOW() - ($1::int || ' days')::interval
        AND ${archivedGameConfidenceSql('af')} IS NOT NULL
      GROUP BY 1, 2
    `, [windowDays])
      : Promise.resolve({ rows: [] });
    const [
      rainmakerPropLeagueResult,
      leagueGameResult,
      rainmakerPropLeagueTierResult,
      leagueTierGameResult,
    ] = await Promise.all([
      rainmakerPropLeaguePromise,
      leagueGamePromise,
      rainmakerPropLeagueTierPromise,
      leagueTierGamePromise,
    ]);

    // Merge league data — track pushes for correct win rate calculation
    const leagueMap: Record<string, LeagueAggregate> = {};
    if (includeProps) {
      for (const row of rainmakerPropLeagueResult.rows) {
        if (!leagueMap[row.league]) {
          leagueMap[row.league] = {
            total: 0,
            wins: 0,
            pushes: 0,
            avgClv: null,
            clvPosRate: null,
            tierData: {},
          };
        }
        leagueMap[row.league].total += parseInt(row.total, 10);
        leagueMap[row.league].wins += parseInt(row.wins, 10);
        leagueMap[row.league].pushes += parseInt(row.pushes || '0', 10);
        leagueMap[row.league].avgClv = row.avg_clv ? parseFloat(row.avg_clv) : null;
        leagueMap[row.league].clvPosRate = row.clv_pos_rate ? parseFloat(row.clv_pos_rate) : null;
      }
    }
    if (includeGames) {
      for (const row of leagueGameResult.rows) {
        if (!leagueMap[row.league]) leagueMap[row.league] = { total: 0, wins: 0, pushes: 0, avgClv: null, clvPosRate: null, tierData: {} };
        leagueMap[row.league].total += parseInt(row.total, 10);
        leagueMap[row.league].wins += parseInt(row.wins, 10);
        leagueMap[row.league].pushes += parseInt(row.pushes || '0', 10);
      }
    }

    // Merge per-league tier data
    if (includeProps) {
      for (const row of rainmakerPropLeagueTierResult.rows) {
        if (!row.tier) continue;
        if (!leagueMap[row.league]) leagueMap[row.league] = { total: 0, wins: 0, pushes: 0, avgClv: null, clvPosRate: null, tierData: {} };
        if (!leagueMap[row.league].tierData[row.tier]) leagueMap[row.league].tierData[row.tier] = { total: 0, wins: 0, pushes: 0 };
        leagueMap[row.league].tierData[row.tier].total += parseInt(row.total, 10);
        leagueMap[row.league].tierData[row.tier].wins += parseInt(row.wins, 10);
        leagueMap[row.league].tierData[row.tier].pushes += parseInt(row.pushes || '0', 10);
      }
    }
    if (includeGames) {
      for (const row of leagueTierGameResult.rows) {
        if (!leagueMap[row.league]) leagueMap[row.league] = { total: 0, wins: 0, pushes: 0, avgClv: null, clvPosRate: null, tierData: {} };
        if (!leagueMap[row.league].tierData[row.tier]) leagueMap[row.league].tierData[row.tier] = { total: 0, wins: 0, pushes: 0 };
        leagueMap[row.league].tierData[row.tier].total += parseInt(row.total, 10);
        leagueMap[row.league].tierData[row.tier].wins += parseInt(row.wins, 10);
        leagueMap[row.league].tierData[row.tier].pushes += parseInt(row.pushes || '0', 10);
      }
    }

    const TIER_ORDER = ['A+', 'A', 'B+', 'B'];
    const aggregateForSummary = (() => {
      if (leagueFilter && tierFilter) {
        const td = leagueMap[leagueFilter]?.tierData?.[tierFilter];
        return td ? { total: td.total, wins: td.wins, pushes: td.pushes, avgClv: leagueMap[leagueFilter]?.avgClv ?? null, clvPosRate: leagueMap[leagueFilter]?.clvPosRate ?? null } : { total: 0, wins: 0, pushes: 0, avgClv: null, clvPosRate: null };
      }
      if (leagueFilter) {
        const leagueData = leagueMap[leagueFilter];
        return leagueData ? { total: leagueData.total, wins: leagueData.wins, pushes: leagueData.pushes, avgClv: leagueData.avgClv, clvPosRate: leagueData.clvPosRate } : { total: 0, wins: 0, pushes: 0, avgClv: null, clvPosRate: null };
      }
      if (tierFilter) {
        const td = tierMap[tierFilter];
        return td ? { total: td.total, wins: td.wins, pushes: td.pushes, avgClv: null, clvPosRate: null } : { total: 0, wins: 0, pushes: 0, avgClv: null, clvPosRate: null };
      }
      return {
        total: combinedTotal,
        wins: combinedWins,
        pushes: combinedPushes,
        avgClv,
        clvPosRate,
      };
    })();
    const filteredTotal = aggregateForSummary.total;
    const filteredPushes = aggregateForSummary.pushes;
    const filteredDecisions = filteredTotal - filteredPushes;
    const filteredWinRate = filteredDecisions > 0
      ? Math.round((aggregateForSummary.wins / filteredDecisions) * 1000) / 10
      : null;
    const filteredAvgClv = aggregateForSummary.avgClv;
    const filteredClvPosRate = aggregateForSummary.clvPosRate;

    const filteredTierSource: Record<string, TierAggregate> = (() => {
      if (leagueFilter) {
        return leagueMap[leagueFilter]?.tierData || {};
      }
      return tierMap;
    })();

    const byTier = Object.entries(filteredTierSource)
      .filter(([tier]) => !tierFilter || tier === tierFilter)
      .map(([tier, d]) => {
        const decisions = d.total - d.pushes;
        return {
          tier,
          winRate: decisions > 0 ? Math.round((d.wins / decisions) * 1000) / 10 : 0,
          total: d.total,
          pushes: d.pushes,
          wins: d.wins,
          losses: d.total - d.wins - d.pushes,
        };
      })
      .sort((a, b) => TIER_ORDER.indexOf(a.tier) - TIER_ORDER.indexOf(b.tier));
    const filteredTierTotal = byTier.reduce((sum, row) => sum + row.total, 0);
    const filteredUntieredTotal = includeGames && !includeProps && !tierFilter
      ? Math.max(filteredTotal - filteredTierTotal, 0)
      : 0;
    const filteredTierCoveragePct = includeGames && !includeProps && !tierFilter && filteredTotal > 0
      ? Math.round((filteredTierTotal / filteredTotal) * 1000) / 10
      : null;

    const byLeague = Object.entries(leagueMap)
      .filter(([league]) => !leagueFilter || league === leagueFilter)
      .map(([league, d]) => {
        const scoped = tierFilter
          ? (d.tierData[tierFilter] || { total: 0, wins: 0, pushes: 0 })
          : { total: d.total, wins: d.wins, pushes: d.pushes };
        const scopedTieredTotal = tierFilter
          ? scoped.total
          : Object.values(d.tierData).reduce((sum, tierRow) => sum + tierRow.total, 0);
        const scopedUntieredTotal = includeGames && !includeProps && !tierFilter
          ? Math.max(d.total - scopedTieredTotal, 0)
          : 0;
        const aPlusData = tierFilter && tierFilter !== 'A+'
          ? { total: 0, wins: 0, pushes: 0 }
          : (d.tierData['A+'] || { total: 0, wins: 0, pushes: 0 });
        const tierWinRates: Record<string, { winRatePct: number; sampleN: number; pushes: number }> = {};
        for (const tier of TIER_ORDER) {
          if (tierFilter && tier !== tierFilter) continue;
          const td = d.tierData[tier];
          if (td && td.total > 0) {
            const tierDecisions = td.total - td.pushes;
            tierWinRates[tier] = {
              winRatePct: tierDecisions > 0 ? Math.round((td.wins / tierDecisions) * 1000) / 10 : 0,
              sampleN: td.total,
              pushes: td.pushes,
            };
          }
        }
        const leagueDecisions = scoped.total - scoped.pushes;
        const aPlusDecisions = aPlusData.total - aPlusData.pushes;
        return {
          league,
          total: scoped.total,
          pushes: scoped.pushes,
          winRate: leagueDecisions > 0 ? Math.round((scoped.wins / leagueDecisions) * 1000) / 10 : 0,
          avgClv: d.avgClv,
          clvPosRate: d.clvPosRate,
          aPlusWinRate: aPlusDecisions > 0 ? Math.round((aPlusData.wins / aPlusDecisions) * 1000) / 10 : null,
          aPlusSampleN: aPlusData.total,
          tieredForecasts: scopedTieredTotal,
          untieredForecasts: scopedUntieredTotal,
          tierCoveragePct: !tierFilter && scoped.total > 0
            ? Math.round((scopedTieredTotal / scoped.total) * 1000) / 10
            : null,
          tierWinRates,
        };
      })
      .filter((row) => row.total > 0 || !tierFilter)
      .sort((a, b) => b.total - a.total);

    // ─── Graded Forecast Ledger (paginated, filterable by tier/league) ───
    const filterParts: string[] = [];
    const ledgerParams: any[] = [windowDays];
    let pIdx = 2;
    if (tierFilter) { filterParts.push(`confidence_tier = $${pIdx}`); ledgerParams.push(tierFilter); pIdx++; }
    if (leagueFilter) { filterParts.push(`league = $${pIdx}`); ledgerParams.push(leagueFilter); pIdx++; }
    const limitIdx = pIdx; ledgerParams.push(limit); pIdx++;
    const offsetIdx = pIdx; ledgerParams.push(offset);
    const filterWhere = filterParts.length > 0 ? `WHERE ${filterParts.join(' AND ')}` : '';

    const ledgerParts: string[] = [];
    if (includeProps) {
      ledgerParts.push(`
        (
          SELECT
            COALESCE((ev.starts_at AT TIME ZONE 'America/New_York')::date, p.created_at::date)::text as event_date,
            p.league,
            'props' as source_kind,
            'PLAYER' as entity_type,
            p.player_name as entity_label,
            UPPER(COALESCE(p.prop_stat, '')) as action_type,
            p.rec_line::float as action_value,
            p.direction,
            ${RM_PROP_TIER_CASE.replace(/model_confidence/g, 'p.model_confidence')} as confidence_tier,
            metric.value::float as result_value,
            p.result as grade,
            p.clv_line::float as clv_value,
            COALESCE((ev.starts_at AT TIME ZONE 'America/New_York')::date, p.created_at::date) as sort_date,
            ev.home_team as home_team,
            ev.away_team as away_team,
            NULL::numeric as home_score,
            NULL::numeric as away_score,
            NULL::text as actual_winner,
            NULL::text as benchmark_source,
            NULL::text as odds_data_raw,
            NULL::text as forecast_data_raw,
            p.event_id as trace_event_id
          FROM rm_pick_clv p
          LEFT JOIN rm_events ev ON ev.event_id = p.event_id
          LEFT JOIN LATERAL (
            SELECT pgm.value
            FROM "PlayerGameMetric" pgm
            WHERE pgm.league = p.league
              AND pgm."playerName" ILIKE p.player_name
              AND pgm."statKey" = ${publicPlayerMetricStatKeySql('p')}
              AND pgm."gameDate" = DATE(COALESCE(ev.starts_at, p.created_at))
            ORDER BY pgm."gameDate" DESC, pgm."createdAt" DESC, pgm.id DESC
            LIMIT 1
          ) metric ON true
          WHERE p.pick_type = 'player_prop'
            AND p.result IS NOT NULL
            AND p.v2_eligible = true
            AND COALESCE(p.resolved_at, p.created_at) >= NOW() - ($1::int || ' days')::interval
        )
      `);
    }
    if (includeGames) {
      ledgerParts.push(`
        (
          SELECT
            COALESCE(fa.event_date, (COALESCE(af.starts_at, fc.starts_at, ev.starts_at) AT TIME ZONE 'America/New_York')::date)::text as event_date,
            fa.league,
            'games' as source_kind,
            'TEAM' as entity_type,
            SPLIT_PART(COALESCE(af.home_team, fc.home_team, ev.home_team), ' ', array_length(string_to_array(COALESCE(af.home_team, fc.home_team, ev.home_team), ' '), 1)) || ' vs ' ||
            SPLIT_PART(COALESCE(af.away_team, fc.away_team, ev.away_team), ' ', array_length(string_to_array(COALESCE(af.away_team, fc.away_team, ev.away_team), ' '), 1)) as entity_label,
            'SPRD' as action_type,
            COALESCE(fa.benchmark_forecast, fa.predicted_spread, 0)::float as action_value,
            NULL as direction,
            ${publicGameTierCase('af')} as confidence_tier,
            fa.actual_spread::float as result_value,
            fa.final_grade as grade,
            NULL::float as clv_value,
            COALESCE(fa.resolved_at::date, fa.event_date, (COALESCE(af.starts_at, fc.starts_at, ev.starts_at) AT TIME ZONE 'America/New_York')::date) as sort_date,
            COALESCE(af.home_team, fc.home_team, ev.home_team) as home_team,
            COALESCE(af.away_team, fc.away_team, ev.away_team) as away_team,
            fa.home_score,
            fa.away_score,
            fa.actual_winner,
            fa.benchmark_source,
            COALESCE(af.odds_data, fc.odds_data)::text as odds_data_raw,
            COALESCE(af.forecast_data, fc.forecast_data)::text as forecast_data_raw,
            ev.moneyline::text as event_moneyline_raw,
            ev.spread::text as event_spread_raw,
            ev.total::text as event_total_raw,
            fa.event_id as trace_event_id,
            COALESCE(af.winner_pick, fa.predicted_winner) as predicted_winner,
            fa.closing_market,
            fa.benchmark_forecast
          FROM rm_forecast_accuracy_v2 fa
          LEFT JOIN rm_archived_forecasts af ON fa.event_id = af.event_id
          LEFT JOIN rm_forecast_cache fc ON fa.event_id = fc.event_id
          LEFT JOIN rm_events ev ON fa.event_id = ev.event_id
          WHERE fa.actual_winner IS NOT NULL
            AND COALESCE(fa.is_preseason, false) = false
            AND fa.final_grade IS NOT NULL
            AND COALESCE(af.event_id, fc.event_id, ev.event_id) IS NOT NULL
            ${spreadForecastTypeFilter('fa')}
            AND COALESCE(fa.resolved_at, fa.event_date::timestamp) >= NOW() - ($1::int || ' days')::interval
        )
      `);
    }
    const combinedCTE = `
      WITH combined AS (
        ${ledgerParts.join('\n        UNION ALL\n')}
      )`;

    const ledgerQuery = `${combinedCTE}
      SELECT *, COUNT(*) OVER()::int as total_rows
      FROM combined ${filterWhere}
      ORDER BY sort_date DESC, entity_label
      LIMIT $${limitIdx} OFFSET $${offsetIdx}
    `;
    const ledgerResult = await pool.query(ledgerQuery, ledgerParams);
    const totalRows = ledgerResult.rows[0]?.total_rows ?? 0;
    const totalPages = Math.ceil(totalRows / limit);

    const ledger = ledgerResult.rows.map((r: any) => {
      // Build final score with abbreviations: "BOS 131 - LAL 111"
      const finalScore = formatScoreAbbr(r.home_team, r.away_team, r.home_score, r.away_score, r.league);

      // ── Benchmark Grading Engine (best_valid_number policy) ──────────────
      // For TEAM entries: compute benchmark forecast (most advantageous valid
      // number between original model forecast and closing market), then grade
      // using that number. The displayed forecast MUST equal the benchmark.
      // See benchmark-grading.ts for full policy documentation.

      let forecastLine: string | null = null;
      let closingLine: string | null = null;
      const canonicalPlayerStat = r.entity_type === 'PLAYER'
        ? canonicalizePlayerPropStat(r.league, r.action_type)
        : null;
      const playerActionType = r.entity_type === 'PLAYER'
        ? (getPlayerPropLabelForLeague(r.league, canonicalPlayerStat) || canonicalPlayerStat || r.action_type || '')
        : r.action_type;
      const playerActionTypeDisplay = r.entity_type === 'PLAYER'
        ? String(playerActionType || '').toUpperCase()
        : r.action_type;

      if (r.entity_type === 'PLAYER') {
        const dir = r.direction === 'over' ? 'Over' : r.direction === 'under' ? 'Under' : '';
        const stat = playerActionTypeDisplay || '';
        if (dir && r.action_value != null) forecastLine = `${stat} ${dir} ${r.action_value}`;
        if (r.action_value != null) closingLine = `${playerActionTypeDisplay} ${r.action_value}`;
      }

      if (r.entity_type === 'TEAM') {
        try {
          const fd = parseJsonLike(r.forecast_data_raw);
          const cachedOdds = parseJsonLike(r.odds_data_raw);
          const eventMoneyline = parseJsonLike(r.event_moneyline_raw);
          const eventSpread = parseJsonLike(r.event_spread_raw);
          const eventTotal = parseJsonLike(r.event_total_raw);
          const odds = sanitizeGameOddsForLeague(r.league, {
            moneyline: cachedOdds?.moneyline || eventMoneyline || { home: null, away: null },
            spread: cachedOdds?.spread || eventSpread || { home: null, away: null },
            total: cachedOdds?.total || eventTotal || { over: null, under: null },
          });
          const fs = fd?.forecast_side || fd?.winner_pick || r.predicted_winner;

          if (fs) {
            const isHome = fs === r.home_team;
            const side: 'home' | 'away' = isHome ? 'home' : 'away';
            const teamShort = getTeamAbbr(fs);

            // Extract original forecast spread and closing market spread
            const originalSpread: number | null = fd?.projected_lines?.spread?.[side] ?? null;
            const closingSpread: number | null = sanitizeClosingSpreadForLeague(
              r.league,
              odds?.spread?.[side]?.line ?? r.closing_market ?? null,
            );

            // Select benchmark forecast (most advantageous valid number between original and close)
            const { value: derivedBenchmarkSpread } = selectBenchmarkForecast('spread', side, originalSpread, closingSpread);
            const benchmarkSpread = r.benchmark_forecast ?? derivedBenchmarkSpread;

            // Build forecastLine from BENCHMARK value (not original)
            // RULE: displayed forecast MUST match the benchmark used for grading
            if (benchmarkSpread != null) {
              forecastLine = `${teamShort} ${benchmarkSpread > 0 ? '+' + benchmarkSpread : benchmarkSpread === 0 ? 'PK' : benchmarkSpread}`;
            }
            // Add moneyline if available
            const projectedMl = sanitizeDisplayMoneyline(fd?.projected_lines?.moneyline?.[side] ?? null);
            if (projectedMl != null) {
              const ml = projectedMl;
              const mlStr = ml > 0 ? `+${ml}` : `${ml}`;
              forecastLine = forecastLine ? `${forecastLine} / ML ${mlStr}` : `${teamShort} ML ${mlStr}`;
            }

            // Build closing line from market data
            if (closingSpread != null) {
              const so = odds?.spread?.[side]?.odds;
              closingLine = `${teamShort} ${closingSpread > 0 ? '+' + closingSpread : closingSpread === 0 ? 'PK' : closingSpread}${so != null ? ` (${so > 0 ? '+' : ''}${so})` : ''}`;
            }
            const closingMl = sanitizeDisplayMoneyline(odds?.moneyline?.[side] ?? null);
            if (closingMl != null) {
              const ml = closingMl;
              const mlStr = ml > 0 ? `+${ml}` : `${ml}`;
              closingLine = closingLine ? `${closingLine} / ML ${mlStr}` : `${teamShort} ML ${mlStr}`;
            }
          }
        } catch {}
      }

      // Build matchup label with abbreviations for teams
      const entityLabel = r.entity_type === 'TEAM' && r.home_team && r.away_team
        ? `${getTeamAbbr(r.home_team)} vs ${getTeamAbbr(r.away_team)}`
        : (r.entity_label || r.trace_event_id);

      // For PLAYER props: show the actual stat result as the "final score"
      // e.g., "27 PTS (Over 8)" instead of "LAL 127 - DEN 125"
      let displayScore = finalScore;
      if (r.entity_type === 'PLAYER' && r.result_value != null) {
        const statLabel = playerActionTypeDisplay;
        const dir = r.direction === 'over' ? 'O' : r.direction === 'under' ? 'U' : '';
        const lineVal = r.action_value != null ? r.action_value : '';
        const hit = (r.direction === 'over' && r.result_value > r.action_value) ||
                    (r.direction === 'under' && r.result_value < r.action_value);
        const resultEmoji = r.grade === 'W' ? '✅' : r.grade === 'L' ? '❌' : '➖';
        displayScore = `${r.result_value} ${statLabel} ${resultEmoji} (${dir}${lineVal})`;
      }

      return {
        eventDate: r.event_date,
        league: r.league,
        entityType: r.entity_type,
        entityLabel,
        actionType: r.entity_type === 'PLAYER' ? playerActionTypeDisplay : r.action_type,
        actionValue: r.action_value,
        direction: r.direction,
        confidenceTier: r.confidence_tier,
        sourceKind: r.source_kind,
        resultValue: r.result_value,
        grade: r.grade,
        clvValue: r.clv_value,
        finalScore: displayScore,
        gameScore: r.entity_type === 'PLAYER' ? finalScore : null,
        forecastLine,
        closingLine,
        rainwireUrl: null as string | null,
        eventId: r.trace_event_id || null,
        // Rainwire slug data for TEAM entries (resolved below)
        _homeTeam: r.home_team,
        _awayTeam: r.away_team,
      };
    });

    // Resolve Rainwire article slugs for TEAM entries
    const teamSlugs: string[] = [];
    for (const row of ledger) {
      if (row.entityType === 'TEAM' && row._homeTeam && row._awayTeam && row.eventDate) {
        teamSlugs.push(makeSlug(row.league, row._homeTeam, row._awayTeam, row.eventDate));
      }
    }
    const validLedgerSlugs = await resolveRainwireSlugs(teamSlugs);
    for (const row of ledger) {
      if (row.entityType === 'TEAM' && row._homeTeam && row._awayTeam && row.eventDate) {
        const slug = makeSlug(row.league, row._homeTeam, row._awayTeam, row.eventDate);
        row.rainwireUrl = validLedgerSlugs.has(slug) ? `/rain-wire/${row.league}/${slug}` : null;
      } else {
        row.rainwireUrl = null;
      }
      delete row._homeTeam;
      delete row._awayTeam;
    }

    res.json({
      kind: performanceKind,
      window: windowDays,
      summary: {
        totalForecasts: filteredTotal,
        winRate: filteredWinRate,
        pushes: filteredPushes,
        avgClv: performanceKind === 'props' ? filteredAvgClv : null,
        clvPosRate: performanceKind === 'props' ? filteredClvPosRate : null,
        sourceLabel: performanceKind === 'props'
          ? 'Native graded player props'
          : performanceKind === 'games'
            ? 'Benchmark-graded team forecasts'
            : 'Mixed benchmark games and tracked props',
        sourceDetail: performanceKind === 'props'
          ? `Uses only rm_pick_clv player props in the selected window.${clvSample > 0 ? ` CLV coverage: ${clvSample} of ${propTotalForecasts} graded props.` : ''}${resolvedDates > 0 ? ` Resolved across ${resolvedDates} day${resolvedDates === 1 ? '' : 's'}.` : ''}`
          : performanceKind === 'games'
            ? (filteredUntieredTotal > 0
                ? `Uses benchmark-graded rm_forecast_accuracy_v2 rows with archived forecast snapshots frozen at publish time. Confidence tiers currently cover ${filteredTierCoveragePct}% of graded games; ${filteredUntieredTotal} graded forecast${filteredUntieredTotal === 1 ? '' : 's'} ${filteredUntieredTotal === 1 ? 'remains' : 'remain'} untiered because the archived public confidence snapshot is missing.`
                : 'Uses benchmark-graded rm_forecast_accuracy_v2 rows with archived forecast snapshots frozen at publish time. Confidence tiers currently cover all graded games.')
            : 'Combines benchmark-graded team forecasts with closing-line tracked player props.',
        tieredForecasts: filteredTierTotal,
        untieredForecasts: filteredUntieredTotal,
        tierCoveragePct: filteredTierCoveragePct,
        roiAvailable: false,
        roiReason: 'ROI is intentionally hidden until stake sizing and price history are normalized across the public game and prop ledgers.',
      },
      byTier,
      byLeague,
      ledger,
      pagination: {
        page,
        limit,
        totalRows,
        totalPages,
      },
    });
  } catch (err) {
    console.error('Performance V2 error:', err);
    res.status(500).json({ error: 'Failed to fetch performance data' });
  }
});

// GET /api/stats/social — social proof counters (public)
router.get('/social', async (_req: Request, res: Response) => {
  try {
    const result = await pool.query(`
      SELECT
        (SELECT COUNT(*) FROM rm_users) as total_users,
        (SELECT COUNT(*) FROM rm_users WHERE created_at::date = (NOW() AT TIME ZONE 'America/New_York')::date) as joined_today,
        (SELECT COUNT(*) FROM rm_user_picks WHERE created_at::date = (NOW() AT TIME ZONE 'America/New_York')::date) as picks_today
    `);
    const r = result.rows[0] || {};
    res.json({
      totalUsers: parseInt(r.total_users || '0', 10),
      joinedToday: parseInt(r.joined_today || '0', 10),
      picksViewedToday: parseInt(r.picks_today || '0', 10),
    });
  } catch (err) {
    console.error('Social stats error:', err);
    res.json({ totalUsers: 0, joinedToday: 0, picksViewedToday: 0 });
  }
});

// GET /api/stats/upcoming — today's pending (ungraded) forecasts
router.get('/upcoming', async (_req: Request, res: Response) => {
  try {
    const { rows } = await pool.query(`
      SELECT
        fc.event_id, fc.league, fc.home_team, fc.away_team,
        COALESCE(fc.composite_confidence, fc.confidence_score) as public_confidence,
        fc.starts_at, fc.forecast_data,
        COALESCE(props.ready_prop_count, 0) as ready_prop_count,
        e.status as event_status, e.home_short, e.away_short,
        e.spread, e.moneyline, e.total
      FROM rm_forecast_cache fc
      LEFT JOIN rm_events e ON fc.event_id = e.event_id
      LEFT JOIN LATERAL (
        SELECT COUNT(*) FILTER (
          WHERE fp.forecast_type = 'PLAYER_PROP'
            AND (
              fp.status = 'ACTIVE'
              OR (fp.status = 'STALE' AND fp.expires_at > NOW())
            )
            AND COALESCE(fp.forecast_payload->>'odds', fp.forecast_payload->'signal_table_row'->>'odds', '') ~ '^[+-]?[0-9]+(\\.[0-9]+)?$'
        )::int as ready_prop_count
        FROM rm_forecast_precomputed fp
        WHERE fp.event_id = fc.event_id
      ) props ON true
      LEFT JOIN rm_forecast_accuracy_v2 fa ON fc.event_id = fa.event_id
      WHERE (fc.starts_at AT TIME ZONE 'America/New_York')::date = (NOW() AT TIME ZONE 'America/New_York')::date
        AND fc.starts_at > NOW()
        AND COALESCE(fc.is_minor_league, false) = false
        AND LOWER(COALESCE(e.status, 'scheduled')) NOT IN ('ended', 'final', 'complete', 'completed', 'closed', 'cancelled', 'canceled')
        AND (fa.id IS NULL OR fa.actual_winner IS NULL)
      ORDER BY fc.starts_at ASC
    `);

    const forecasts = rows.map((r: any) => {
      const conf = r.public_confidence ? parseFloat(r.public_confidence) : null;
      let confidenceTier: string | null = null;
      if (conf !== null) {
        if (conf >= 0.85) confidenceTier = 'A+';
        else if (conf >= 0.70) confidenceTier = 'A';
        else if (conf >= 0.55) confidenceTier = 'B+';
        else confidenceTier = 'B';
      }
      const startsAt = new Date(r.starts_at);
      const eventDate = startsAt.toLocaleDateString('en-CA', { timeZone: 'America/New_York' });

      let forecastSummary: string | null = null;
      if (r.forecast_data) {
        const fd = r.forecast_data;
        if (fd.forecast_side) {
          const teamShort = getTeamAbbr(fd.forecast_side);
          const isHome = fd.forecast_side === r.home_team;
          const spreadSide = isHome ? 'home' : 'away';
          const spreadVal = fd.projected_lines?.spread?.[spreadSide];
          if (spreadVal != null) forecastSummary = `${teamShort} ${spreadVal > 0 ? '+' + spreadVal : spreadVal === 0 ? 'PK' : spreadVal}`;
          else forecastSummary = teamShort;
        } else if (fd.winner_pick) {
          forecastSummary = getTeamAbbr(fd.winner_pick);
        }
      }

      // Extract player props from forecast_data
      const propHighlights: any[] = [];
      if ((r.ready_prop_count || 0) > 0 && r.forecast_data?.prop_highlights) {
        for (const ph of r.forecast_data.prop_highlights) {
          propHighlights.push({
            player: ph.player || null,
            prop: ph.prop || null,
            recommendation: ph.recommendation || null,
            reasoning: ph.reasoning || null,
          });
        }
      }

      return {
        eventId: r.event_id,
        eventDate,
        league: r.league,
        homeTeam: r.home_team,
        awayTeam: r.away_team,
        homeShort: r.home_short || getTeamAbbr(r.home_team),
        awayShort: r.away_short || getTeamAbbr(r.away_team),
        startsAt: r.starts_at,
        confidenceTier,
        confidenceScore: conf,
        forecastSummary,
        eventStatus: r.event_status || 'scheduled',
        spread: r.spread || null,
        moneyline: r.moneyline || null,
        total: r.total || null,
        propHighlights,
      };
    });

    // Resolve Rainwire article slugs
    const slugMap: Record<string, string> = {};
    for (const f of forecasts) {
      const slug = makeSlug(f.league, f.homeTeam, f.awayTeam, f.eventDate);
      slugMap[slug] = slug;
    }
    const validSlugs = await resolveRainwireSlugs(Object.keys(slugMap));
    const enriched = forecasts.map((f: any) => {
      const slug = makeSlug(f.league, f.homeTeam, f.awayTeam, f.eventDate);
      return {
        ...f,
        rainwireUrl: validSlugs.has(slug) ? `/rain-wire/${f.league}/${slug}` : null,
      };
    });

    res.json({ forecasts: enriched, total: enriched.length });
  } catch (err) {
    console.error('Upcoming forecasts error:', err);
    res.status(500).json({ error: 'Failed to fetch upcoming forecasts' });
  }
});

// GET /api/stats/traffic-router — Phase 2 scoreboard for paid router slugs and variants
router.get('/traffic-router', async (req: Request, res: Response) => {
  try {
    const days = Math.min(Math.max(parseInt(String(req.query.days || '30'), 10) || 30, 1), 90);
    const slug = typeof req.query.slug === 'string' ? req.query.slug : null;
    const source = typeof req.query.source === 'string' ? req.query.source : null;

    const filters: string[] = ['c.created_at >= NOW() - ($1::int * INTERVAL \'1 day\')'];
    const values: any[] = [days];
    let idx = 2;

    if (slug) {
      filters.push(`c.slug = $${idx++}`);
      values.push(slug);
    }
    if (source) {
      filters.push(`c.source = $${idx++}`);
      values.push(source);
    }

    const whereClause = filters.length > 0 ? `WHERE ${filters.join(' AND ')}` : '';

    const { rows } = await pool.query(
      `
      WITH click_base AS (
        SELECT
          c.click_id,
          c.slug,
          c.source,
          c.campaign,
          c.creative,
          c.ad_type,
          c.assigned_variant,
          c.created_at
        FROM rm_go_clicks c
        ${whereClause}
      ),
      per_click AS (
        SELECT
          cb.*,
          EXISTS (
            SELECT 1
            FROM site_pageviews pv
            WHERE pv.site = 'rainmaker'
              AND pv.attribution ->> 'click_id' = cb.click_id::text
          ) AS has_landing_view,
          EXISTS (
            SELECT 1
            FROM rm_user_behavior ub
            WHERE ub.event_type = 'landing_primary_cta_click'
              AND ub.event_data -> 'attribution' ->> 'click_id' = cb.click_id::text
          ) OR EXISTS (
            SELECT 1
            FROM site_conversions sc
            WHERE sc.site = 'rainmaker'
              AND sc.event = 'landing_primary_cta_click'
              AND sc.event_data -> 'attribution' ->> 'click_id' = cb.click_id::text
          ) AS has_primary_cta,
          EXISTS (
            SELECT 1
            FROM rm_user_behavior ub
            WHERE ub.event_type = 'landing_secondary_cta_click'
              AND ub.event_data -> 'attribution' ->> 'click_id' = cb.click_id::text
          ) OR EXISTS (
            SELECT 1
            FROM site_conversions sc
            WHERE sc.site = 'rainmaker'
              AND sc.event = 'landing_secondary_cta_click'
              AND sc.event_data -> 'attribution' ->> 'click_id' = cb.click_id::text
          ) AS has_secondary_cta,
          EXISTS (
            SELECT 1
            FROM site_conversions sc
            WHERE sc.site = 'rainmaker'
              AND sc.event IN ('signup', 'quick_signup')
              AND sc.event_data -> 'attribution' ->> 'click_id' = cb.click_id::text
          ) AS has_signup,
          EXISTS (
            SELECT 1
            FROM rm_user_behavior ub
            WHERE ub.event_type = 'checkout_start'
              AND ub.event_data -> 'attribution' ->> 'click_id' = cb.click_id::text
          ) OR EXISTS (
            SELECT 1
            FROM site_conversions sc
            WHERE sc.site = 'rainmaker'
              AND sc.event = 'checkout_start'
              AND sc.event_data -> 'attribution' ->> 'click_id' = cb.click_id::text
          ) AS has_checkout_start,
          EXISTS (
            SELECT 1
            FROM site_conversions sc
            WHERE sc.site = 'rainmaker'
              AND sc.event = 'purchase_complete'
              AND sc.event_data -> 'attribution' ->> 'click_id' = cb.click_id::text
          ) OR EXISTS (
            SELECT 1
            FROM rm_purchases p
            WHERE p.attribution ->> 'click_id' = cb.click_id::text
          ) AS has_purchase
        FROM click_base cb
      )
      SELECT
        slug,
        source,
        campaign,
        creative,
        ad_type,
        assigned_variant AS landing_variant,
        COUNT(*)::int AS clicks,
        COUNT(*) FILTER (WHERE has_landing_view)::int AS landing_views,
        COUNT(*) FILTER (WHERE has_primary_cta)::int AS primary_cta_clicks,
        COUNT(*) FILTER (WHERE has_secondary_cta)::int AS secondary_cta_clicks,
        COUNT(*) FILTER (WHERE has_signup)::int AS signups,
        COUNT(*) FILTER (WHERE has_checkout_start)::int AS checkout_starts,
        COUNT(*) FILTER (WHERE has_purchase)::int AS purchases,
        ROUND((COUNT(*) FILTER (WHERE has_signup)::numeric / NULLIF(COUNT(*), 0)) * 100, 2) AS signup_rate,
        ROUND((COUNT(*) FILTER (WHERE has_purchase)::numeric / NULLIF(COUNT(*), 0)) * 100, 2) AS purchase_rate,
        MIN(created_at) AS first_click_at,
        MAX(created_at) AS last_click_at
      FROM per_click
      GROUP BY slug, source, campaign, creative, ad_type, assigned_variant
      ORDER BY clicks DESC, last_click_at DESC
      `,
      values
    );

    const summary = rows.reduce((acc: any, row: any) => {
      acc.clicks += row.clicks || 0;
      acc.landing_views += row.landing_views || 0;
      acc.primary_cta_clicks += row.primary_cta_clicks || 0;
      acc.secondary_cta_clicks += row.secondary_cta_clicks || 0;
      acc.signups += row.signups || 0;
      acc.checkout_starts += row.checkout_starts || 0;
      acc.purchases += row.purchases || 0;
      return acc;
    }, {
      clicks: 0,
      landing_views: 0,
      primary_cta_clicks: 0,
      secondary_cta_clicks: 0,
      signups: 0,
      checkout_starts: 0,
      purchases: 0,
    });

    res.json({
      window_days: days,
      filters: { slug, source },
      summary: {
        ...summary,
        signup_rate: summary.clicks ? Math.round((summary.signups / summary.clicks) * 10000) / 100 : 0,
        purchase_rate: summary.clicks ? Math.round((summary.purchases / summary.clicks) * 10000) / 100 : 0,
      },
      rows,
    });
  } catch (err) {
    console.error('Traffic router stats error:', err);
    res.status(500).json({ error: 'Failed to fetch traffic router stats' });
  }
});

export default router;