/**
 * feed-serializer.ts
 *
 * Serializes forecast data into the API shapes consumed by the frontend.
 * Two feeds:
 *   1. Upcoming — compact summary of pregame forecasts (count by league)
 *   2. Recently Graded — live pending + completed forecasts (full detail)
 *
 * All status/tier/formatting logic delegates to forecast-lifecycle.ts.
 */

import pool from '../db';
import {
  ForecastStatus,
  classifyTier,
  classifyDailyPickTier,
  MIN_DISPLAY_CONFIDENCE,
  formatClosingLine,
  formatForecastLine,
  formatPlayerForecastLine,
  formatPlayerClosingLine,
  deriveScores,
} from './forecast-lifecycle';

const PUBLIC_CONFIDENCE_SQL = 'COALESCE(fc.composite_confidence, fc.confidence_score)';
const PUBLIC_CONFIDENCE_TIER_CASE = `
  CASE
    WHEN ${PUBLIC_CONFIDENCE_SQL} >= 0.85 THEN 'A+'
    WHEN ${PUBLIC_CONFIDENCE_SQL} >= 0.70 THEN 'A'
    WHEN ${PUBLIC_CONFIDENCE_SQL} >= 0.55 THEN 'B+'
    WHEN ${PUBLIC_CONFIDENCE_SQL} IS NOT NULL THEN 'B'
    ELSE NULL
  END
`;

// ─── Types ──────────────────────────────────────────────────

export interface UpcomingLeagueSummary {
  league: string;
  count: number;
  earliestStart: string;
  latestStart: string;
  games: UpcomingGame[];
}

export interface UpcomingGame {
  eventId: string;
  league: string;
  homeTeam: string;
  awayTeam: string;
  startsAt: string;
  confidenceScore: number;
  tier: string;
}

export interface UpcomingFeed {
  totalCount: number;
  byLeague: UpcomingLeagueSummary[];
}

export interface LedgerRow {
  eventDate: string;
  league: string;
  entityType: string;
  entityLabel: string;
  actionType: string;
  actionValue: number | null;
  direction: string | null;
  confidenceTier: string | null;
  resultValue: number | null;
  grade: string;
  clvValue: number | null;
  homeTeam: string | null;
  awayTeam: string | null;
  homeScore: number | null;
  awayScore: number | null;
  closingLine: string | null;
  forecastLine: string | null;
}

// ─── Upcoming Feed ──────────────────────────────────────────

/**
 * Fetch upcoming (not-yet-started) forecasts grouped by league.
 * These are UPCOMING_QUALIFIED — contest scheduled, not started.
 */
export async function fetchUpcomingFeed(): Promise<UpcomingFeed> {
  const result = await pool.query(`
    SELECT
      fc.league,
      COUNT(*) as count,
      MIN(fc.starts_at) as earliest_start,
      MAX(fc.starts_at) as latest_start,
      json_agg(json_build_object(
        'eventId', fc.event_id,
        'league', fc.league,
        'homeTeam', fc.home_team,
        'awayTeam', fc.away_team,
        'startsAt', fc.starts_at,
        'confidenceScore', ${PUBLIC_CONFIDENCE_SQL},
        'tier', ${PUBLIC_CONFIDENCE_TIER_CASE}
      ) ORDER BY fc.starts_at) as games
    FROM rm_forecast_cache fc
    LEFT JOIN rm_forecast_accuracy_v2 fa ON fc.event_id = fa.event_id
    WHERE fa.event_id IS NULL
      AND ${PUBLIC_CONFIDENCE_SQL} >= $1
      AND fc.starts_at > NOW()
      AND fc.starts_at::date <= CURRENT_DATE + 1
    GROUP BY fc.league
    ORDER BY MIN(fc.starts_at)
  `, [MIN_DISPLAY_CONFIDENCE]);

  return {
    totalCount: result.rows.reduce((sum: number, r: any) => sum + parseInt(r.count, 10), 0),
    byLeague: result.rows.map((r: any) => ({
      league: r.league,
      count: parseInt(r.count, 10),
      earliestStart: r.earliest_start,
      latestStart: r.latest_start,
      games: r.games,
    })),
  };
}

// ─── Recently Graded Feed ───────────────────────────────────

/**
 * Fetch the graded forecast ledger — LIVE_PENDING + FINAL_GRADED.
 * Combines DailyPick props (PIFF track record) + rm_forecast_accuracy_v2 game results.
 *
 * Key rule: UPCOMING_QUALIFIED forecasts are EXCLUDED.
 * Only started or completed contests appear here.
 */
export async function fetchRecentlyGradedFeed(
  windowDays: number,
  limit: number,
  offset: number
): Promise<{ rows: any[]; totalRows: number }> {
  const ledgerQuery = `
    (
      SELECT
        game_date::text as event_date,
        CASE WHEN algo_version LIKE '%nhl%' THEN 'nhl'
             WHEN algo_version LIKE '%soccer%' THEN 'soccer'
             ELSE 'nba' END as league,
        'PLAYER' as entity_type,
        player_name as entity_label,
        UPPER(COALESCE(stat, '')) as action_type,
        rec_line::float as action_value,
        over_under as direction,
        CASE
          WHEN algo_version LIKE 'piff3.0_%' AND lock_level = 'T1_LOCK' THEN 'A+'
          WHEN algo_version LIKE 'piff3.0_%' AND lock_level = 'T2_STRONG' THEN 'A'
          WHEN algo_version LIKE 'piff3.0_%' AND lock_level = 'T3_SOLID' THEN 'B+'
          WHEN algo_version LIKE 'piff3.0_%' AND mc_prob >= 0.92 THEN 'A+'
          WHEN algo_version LIKE 'piff3.0_%' AND mc_prob >= 0.78 THEN 'A'
          WHEN algo_version LIKE 'piff3.0_%' AND mc_prob >= 0.60 THEN 'B+'
          WHEN algo_version = 'legacy' AND lock_level = 'SUPER LOCK' THEN 'A+'
          WHEN algo_version = 'legacy' AND lock_level = 'STRONG LOCK' THEN 'A'
          WHEN algo_version = 'legacy' AND lock_level = 'LOCK' THEN 'B+'
          WHEN mc_prob >= 0.85 THEN 'A+'
          WHEN mc_prob >= 0.70 THEN 'A'
          WHEN mc_prob >= 0.55 THEN 'B+'
          ELSE 'B'
        END as confidence_tier,
        actual_stat::float as result_value,
        result as grade,
        ROUND(clv_value::numeric * 100, 2)::float as clv_value,
        game_date as sort_date,
        NULL::text as home_team,
        NULL::text as away_team,
        NULL::int as home_score,
        NULL::int as away_score,
        NULL::text as predicted_winner,
        NULL::text as odds_data_raw,
        NULL::text as forecast_data_raw
      FROM "DailyPick"
      WHERE result IN ('W','L')
        AND rec_line IS NOT NULL
        AND game_date >= CURRENT_DATE - $1::int
        AND (
          (algo_version LIKE 'piff3.0_%' AND (lock_level IN ('T1_LOCK', 'T2_STRONG', 'T3_SOLID') OR mc_prob >= 0.60))
          OR
          (algo_version = 'legacy' AND lock_level IN ('SUPER LOCK', 'STRONG LOCK', 'LOCK'))
        )
    )
    UNION ALL
    (
      SELECT
        COALESCE(fc.starts_at::date, fa.resolved_at::date)::text as event_date,
        fa.league,
        'TEAM' as entity_type,
        SPLIT_PART(fa.predicted_winner, ' ', array_length(string_to_array(fa.predicted_winner, ' '), 1)) as entity_label,
        'SPRD' as action_type,
        COALESCE(fa.predicted_spread, 0)::float as action_value,
        NULL as direction,
        ${PUBLIC_CONFIDENCE_TIER_CASE} as confidence_tier,
        fa.actual_spread::float as result_value,
        COALESCE(
          fa.final_grade,
          CASE
            WHEN fa.actual_winner = 'DRAW' THEN 'P'
            WHEN fa.predicted_winner = fa.actual_winner THEN 'W'
            ELSE 'L'
          END
        ) as grade,
        NULL::float as clv_value,
        COALESCE(fa.resolved_at::date, fc.starts_at::date) as sort_date,
        fc.home_team,
        fc.away_team,
        CASE WHEN fa.actual_total IS NOT NULL AND fa.actual_spread IS NOT NULL
          THEN ROUND((fa.actual_total + fa.actual_spread) / 2)::int END as home_score,
        CASE WHEN fa.actual_total IS NOT NULL AND fa.actual_spread IS NOT NULL
          THEN ROUND((fa.actual_total - fa.actual_spread) / 2)::int END as away_score,
        fa.predicted_winner as predicted_winner,
        fc.odds_data::text as odds_data_raw,
        fc.forecast_data::text as forecast_data_raw
      FROM rm_forecast_accuracy_v2 fa
      LEFT JOIN rm_forecast_cache fc ON fa.event_id = fc.event_id
      WHERE fa.actual_winner IS NOT NULL
        AND fa.final_grade IS NOT NULL
        AND COALESCE(fa.resolved_at, fc.starts_at) >= NOW() - ($1::int || ' days')::interval
        AND ${PUBLIC_CONFIDENCE_SQL} >= ${MIN_DISPLAY_CONFIDENCE}
    )
    UNION ALL
    (
      SELECT
        fc.starts_at::date::text as event_date,
        fc.league,
        'TEAM' as entity_type,
        SPLIT_PART(fc.home_team, ' ', array_length(string_to_array(fc.home_team, ' '), 1)) || ' vs ' ||
        SPLIT_PART(fc.away_team, ' ', array_length(string_to_array(fc.away_team, ' '), 1)) as entity_label,
        'SPRD' as action_type,
        NULL::float as action_value,
        NULL as direction,
        ${PUBLIC_CONFIDENCE_TIER_CASE} as confidence_tier,
        NULL::float as result_value,
        '${ForecastStatus.LIVE_PENDING}' as grade,
        NULL::float as clv_value,
        fc.starts_at::date as sort_date,
        fc.home_team,
        fc.away_team,
        NULL::int as home_score,
        NULL::int as away_score,
        NULL::text as predicted_winner,
        fc.odds_data::text as odds_data_raw,
        fc.forecast_data::text as forecast_data_raw
      FROM rm_forecast_cache fc
      LEFT JOIN rm_forecast_accuracy_v2 fa ON fc.event_id = fa.event_id
      WHERE fa.event_id IS NULL
        AND ${PUBLIC_CONFIDENCE_SQL} >= ${MIN_DISPLAY_CONFIDENCE}
        AND fc.starts_at <= NOW()
        AND fc.starts_at::date >= (NOW() AT TIME ZONE 'America/New_York')::date
    )
    ORDER BY sort_date DESC, entity_type, entity_label
    LIMIT $2 OFFSET $3
  `;

  const ledgerResult = await pool.query(ledgerQuery, [windowDays, limit, offset]);

  // Count for pagination
  const countQuery = `
    SELECT (
      (SELECT COUNT(*) FROM "DailyPick" WHERE result IN ('W','L') AND rec_line IS NOT NULL AND game_date >= CURRENT_DATE - $1::int AND ((algo_version LIKE 'piff3.0_%' AND (lock_level IN ('T1_LOCK', 'T2_STRONG', 'T3_SOLID') OR mc_prob >= 0.60)) OR (algo_version = 'legacy' AND lock_level IN ('SUPER LOCK', 'STRONG LOCK', 'LOCK'))))
      +
      (SELECT COUNT(*) FROM rm_forecast_accuracy_v2 fa
       LEFT JOIN rm_forecast_cache fc ON fa.event_id = fc.event_id
       WHERE fa.actual_winner IS NOT NULL
         AND fa.final_grade IS NOT NULL
         AND fa.resolved_at >= NOW() - ($1::int || ' days')::interval
         AND ${PUBLIC_CONFIDENCE_SQL} >= ${MIN_DISPLAY_CONFIDENCE})
      +
      (SELECT COUNT(*) FROM rm_forecast_cache fc
       LEFT JOIN rm_forecast_accuracy_v2 fa ON fc.event_id = fa.event_id
       WHERE fa.event_id IS NULL
         AND ${PUBLIC_CONFIDENCE_SQL} >= ${MIN_DISPLAY_CONFIDENCE}
         AND fc.starts_at <= NOW()
         AND fc.starts_at::date >= (NOW() AT TIME ZONE 'America/New_York')::date)
    ) as total
  `;
  const countResult = await pool.query(countQuery, [windowDays]);
  const totalRows = parseInt(countResult.rows[0]?.total || '0', 10);

  return { rows: ledgerResult.rows, totalRows };
}

// ─── Row Serializer ─────────────────────────────────────────

/**
 * Transform a raw DB row into a clean LedgerRow for the API response.
 * All formatting logic centralized here.
 */
export function serializeLedgerRow(r: any): LedgerRow {
  let closingLine: string | null = null;
  let forecastLine: string | null = null;

  // Team-level forecasts
  if (r.entity_type === 'TEAM') {
    closingLine = formatClosingLine(r.odds_data_raw, r.predicted_winner, r.home_team);
    forecastLine = formatForecastLine(r.forecast_data_raw, r.predicted_winner, r.home_team);
  }

  // Player props
  if (r.entity_type === 'PLAYER') {
    forecastLine = formatPlayerForecastLine(r.action_type, r.direction, r.action_value);
    if (!closingLine) {
      closingLine = formatPlayerClosingLine(r.action_type, r.action_value);
    }
  }

  return {
    eventDate: r.event_date,
    league: r.league,
    entityType: r.entity_type,
    entityLabel: r.entity_label,
    actionType: r.action_type,
    actionValue: r.action_value,
    direction: r.direction,
    confidenceTier: r.confidence_tier,
    resultValue: r.result_value,
    grade: r.grade,
    clvValue: r.clv_value,
    homeTeam: r.home_team,
    awayTeam: r.away_team,
    homeScore: r.home_score,
    awayScore: r.away_score,
    closingLine,
    forecastLine,
  };
}