import { NextRequest, NextResponse } from 'next/server';
import { getSportsDb } from '@/lib/sportsDb';

type SituationType = 'bounce_back' | 'road_trip_return' | 'losing_streak' | 'winning_streak' | 'all';

interface SituationalGame {
  team: string;
  gameDate: Date;
  opponent: string;
  location: 'HOME' | 'AWAY';
  teamScore: number;
  oppScore: number;
  spread: number;
  covered: boolean;
  margin: number;
  marginVsSpread: number;
  winsBeforeGame: number;
  lossesBeforeGame: number;
  winPctBeforeGame: number;
  streakType: 'W' | 'L' | null;
  streakLength: number;
  roadGamesBefore: number;
  isFirstHomeAfterRoad: boolean;
}

interface SituationalSummary {
  situation: string;
  games: number;
  covers: number;
  coverRate: number;
  edge: number;
  avgMarginVsSpread: number;
  recommendation: string;
}

/**
 * GET /api/analytics/ats-situational
 * Analyze ATS performance in specific situations like bounce-backs, road trip returns, streaks
 *
 * Query params:
 * - league: nba, nfl, nhl, mlb (default: nba)
 * - situation: bounce_back, road_trip_return, losing_streak, winning_streak, all (default: bounce_back)
 * - minRecord: minimum win pct as decimal (default: 0.5 for .500)
 * - minStreak: minimum streak length (default: 3)
 * - season: year (default: 2025)
 * - today: if "true", show today's bounce-back candidates
 */
export async function GET(request: NextRequest) {
  const sp = request.nextUrl.searchParams;
  const league = sp.get('league') || 'nba';
  const situation = (sp.get('situation') || 'bounce_back') as SituationType;
  const minRecord = parseFloat(sp.get('minRecord') || '0.5');
  const minStreak = parseInt(sp.get('minStreak') || '3');
  const season = parseInt(sp.get('season') || '2025');
  const showToday = sp.get('today') === 'true';

  try {
    const prisma = getSportsDb();

    // First, build a comprehensive dataset with running records and streaks
    // Note: Using simpler approach to avoid nested window functions
    const gamesDataRaw = await prisma.$queryRawUnsafe(`
      WITH team_games_flat AS (
        -- Flatten to one row per team per game
        SELECT
          g.id,
          g."homeTeam" as team,
          g."awayTeam" as opponent,
          'HOME' as location,
          g."gameDate"::date as game_date,
          g."homeScore" as team_score,
          g."awayScore" as opp_score,
          g."spreadHome" as spread,
          g."homeScore" - g."awayScore" as margin,
          CASE WHEN g."homeScore" > g."awayScore" THEN 1 ELSE 0 END as won,
          CASE
            WHEN (g."homeScore" - g."awayScore") > -g."spreadHome" THEN 1
            WHEN (g."homeScore" - g."awayScore") < -g."spreadHome" THEN 0
            ELSE 0.5
          END as covered
        FROM "SportsGame" g
        WHERE g.league = $1
          AND g.season = $2
          AND g."homeScore" > 0
          AND g."awayScore" > 0
          AND g."spreadHome" IS NOT NULL

        UNION ALL

        SELECT
          g.id,
          g."awayTeam" as team,
          g."homeTeam" as opponent,
          'AWAY' as location,
          g."gameDate"::date as game_date,
          g."awayScore" as team_score,
          g."homeScore" as opp_score,
          -g."spreadHome" as spread,
          g."awayScore" - g."homeScore" as margin,
          CASE WHEN g."awayScore" > g."homeScore" THEN 1 ELSE 0 END as won,
          CASE
            WHEN (g."awayScore" - g."homeScore") > g."spreadHome" THEN 1
            WHEN (g."awayScore" - g."homeScore") < g."spreadHome" THEN 0
            ELSE 0.5
          END as covered
        FROM "SportsGame" g
        WHERE g.league = $1
          AND g.season = $2
          AND g."homeScore" > 0
          AND g."awayScore" > 0
          AND g."spreadHome" IS NOT NULL
      ),
      team_games_numbered AS (
        SELECT
          *,
          ROW_NUMBER() OVER (PARTITION BY team ORDER BY game_date, id) as game_num
        FROM team_games_flat
      ),
      running_records AS (
        -- Calculate running W-L record before each game using self-join
        SELECT
          t.*,
          COALESCE(SUM(t2.won), 0)::int as wins_before,
          (COALESCE(COUNT(t2.*), 0) - COALESCE(SUM(t2.won), 0))::int as losses_before
        FROM team_games_numbered t
        LEFT JOIN team_games_numbered t2
          ON t.team = t2.team
          AND t2.game_num < t.game_num
        GROUP BY t.id, t.team, t.opponent, t.location, t.game_date, t.team_score,
                 t.opp_score, t.spread, t.margin, t.won, t.covered, t.game_num
      ),
      with_prev_games AS (
        -- Get previous game info for streak calculation
        SELECT
          r.*,
          ROUND(CASE WHEN (wins_before + losses_before) > 0
            THEN 100.0 * wins_before / (wins_before + losses_before)
            ELSE 50.0 END, 1) as win_pct_before,
          LAG(won) OVER (PARTITION BY team ORDER BY game_num) as prev_won,
          LAG(location) OVER (PARTITION BY team ORDER BY game_num) as prev_location
        FROM running_records r
      ),
      with_streak_groups AS (
        -- Mark streak changes
        SELECT
          w.*,
          CASE WHEN won != COALESCE(prev_won, won) THEN 1 ELSE 0 END as streak_break
        FROM with_prev_games w
      ),
      with_streak_ids AS (
        -- Assign streak IDs
        SELECT
          s.*,
          SUM(streak_break) OVER (PARTITION BY team ORDER BY game_num) as streak_id
        FROM with_streak_groups s
      ),
      streak_lengths AS (
        -- Calculate streak length by counting games in same streak_id
        SELECT
          team,
          game_num,
          streak_id,
          ROW_NUMBER() OVER (PARTITION BY team, streak_id ORDER BY game_num) as streak_position
        FROM with_streak_ids
      ),
      final_data AS (
        SELECT
          w.team,
          w.game_date,
          w.opponent,
          w.location,
          w.team_score,
          w.opp_score,
          w.spread,
          w.margin,
          w.covered,
          w.wins_before,
          w.losses_before,
          w.win_pct_before,
          CASE WHEN w.prev_won = 1 THEN 'W' WHEN w.prev_won = 0 THEN 'L' ELSE NULL END as streak_type,
          COALESCE(sl.streak_position - 1, 0)::int as streak_length,
          CASE WHEN w.location = 'HOME' AND w.prev_location = 'AWAY' THEN true ELSE false END as first_home_after_road
        FROM with_streak_ids w
        LEFT JOIN streak_lengths sl ON w.team = sl.team AND w.game_num = sl.game_num
      )
      SELECT * FROM final_data
      WHERE wins_before + losses_before >= 5
      ORDER BY game_date DESC, team
    `, league, season) as Array<Record<string, unknown>>;

    // Parse the raw data
    const allGames: SituationalGame[] = gamesDataRaw.map(row => ({
      team: String(row.team),
      gameDate: new Date(String(row.game_date)),
      opponent: String(row.opponent),
      location: String(row.location) as 'HOME' | 'AWAY',
      teamScore: Number(row.team_score),
      oppScore: Number(row.opp_score),
      spread: Number(row.spread),
      covered: Number(row.covered) >= 0.5,
      margin: Number(row.margin),
      marginVsSpread: Number(row.margin) + Number(row.spread),
      winsBeforeGame: Number(row.wins_before),
      lossesBeforeGame: Number(row.losses_before),
      winPctBeforeGame: Number(row.win_pct_before) / 100,
      streakType: row.streak_type ? (String(row.streak_type) as 'W' | 'L') : null,
      streakLength: Number(row.streak_length) || 0,
      roadGamesBefore: 0, // Simplified - would need additional CTE to track
      isFirstHomeAfterRoad: Boolean(row.first_home_after_road),
    }));

    // Filter by situation
    let situationalGames: SituationalGame[] = [];
    let situationLabel = '';

    switch (situation) {
      case 'bounce_back':
        // Teams with winning record (.500+) on losing streak (3+) playing at home
        situationalGames = allGames.filter(g =>
          g.winPctBeforeGame >= minRecord &&
          g.streakType === 'L' &&
          g.streakLength >= minStreak &&
          g.location === 'HOME'
        );
        situationLabel = `Bounce-Back: Teams above ${(minRecord * 100).toFixed(0)}% on ${minStreak}+ game losing streak, playing at HOME`;
        break;

      case 'road_trip_return':
        // Teams returning home after any road game (simplified - first home after road)
        situationalGames = allGames.filter(g =>
          g.isFirstHomeAfterRoad
        );
        situationLabel = `Road Trip Return: First home game after road game`;
        break;

      case 'losing_streak':
        // Any team on losing streak
        situationalGames = allGames.filter(g =>
          g.streakType === 'L' &&
          g.streakLength >= minStreak
        );
        situationLabel = `Losing Streak: Teams on ${minStreak}+ game losing streak`;
        break;

      case 'winning_streak':
        // Any team on winning streak
        situationalGames = allGames.filter(g =>
          g.streakType === 'W' &&
          g.streakLength >= minStreak
        );
        situationLabel = `Winning Streak: Teams on ${minStreak}+ game winning streak`;
        break;

      case 'all':
      default:
        // Show all situations
        situationalGames = allGames;
        situationLabel = 'All games with situational data';
    }

    // Calculate summary statistics
    const totalGames = situationalGames.length;
    const totalCovers = situationalGames.filter(g => g.covered).length;
    const coverRate = totalGames > 0 ? (100 * totalCovers / totalGames) : 0;
    const edge = coverRate - 50;
    const avgMarginVsSpread = totalGames > 0
      ? situationalGames.reduce((acc, g) => acc + g.marginVsSpread, 0) / totalGames
      : 0;

    // Break down by team
    const teamBreakdown = new Map<string, { games: number; covers: number; avgMargin: number }>();
    for (const g of situationalGames) {
      const existing = teamBreakdown.get(g.team) || { games: 0, covers: 0, avgMargin: 0 };
      existing.games++;
      if (g.covered) existing.covers++;
      existing.avgMargin = (existing.avgMargin * (existing.games - 1) + g.marginVsSpread) / existing.games;
      teamBreakdown.set(g.team, existing);
    }

    const teamStats = Array.from(teamBreakdown.entries())
      .map(([team, stats]) => ({
        team,
        games: stats.games,
        covers: stats.covers,
        coverRate: stats.games > 0 ? (100 * stats.covers / stats.games) : 0,
        edge: stats.games > 0 ? (100 * stats.covers / stats.games) - 50 : 0,
        avgMarginVsSpread: stats.avgMargin,
      }))
      .filter(t => t.games >= 3)
      .sort((a, b) => b.edge - a.edge);

    // Get today's candidates if requested
    let todaysCandidates: Array<{
      team: string;
      opponent: string;
      location: string;
      spread: number | null;
      winsBeforeGame: number;
      lossesBeforeGame: number;
      winPct: number;
      currentStreak: string;
      roadGamesBefore?: number;
      situationMatch: string[];
    }> = [];

    if (showToday) {
      const today = new Date().toISOString().split('T')[0];
      const todaysGamesRaw = await prisma.$queryRawUnsafe(`
        SELECT
          g."homeTeam",
          g."awayTeam",
          g."spreadHome",
          g."gameDate"
        FROM "SportsGame" g
        WHERE g.league = $1
          AND g."gameDate"::date = $2::date
          AND g."homeScore" IS NULL
      `, league, today) as Array<Record<string, unknown>>;

      // For each team playing today, check their current situation
      for (const game of todaysGamesRaw) {
        const homeTeam = String(game.homeTeam);
        const awayTeam = String(game.awayTeam);
        const spread = game.spreadHome ? Number(game.spreadHome) : null;

        // Check home team situation
        const homeGames = allGames.filter(g => g.team === homeTeam).sort((a, b) => b.gameDate.getTime() - a.gameDate.getTime());
        if (homeGames.length > 0) {
          const latest = homeGames[0];
          const situations: string[] = [];

          if (latest.winPctBeforeGame >= minRecord && latest.streakType === 'L' && latest.streakLength >= minStreak - 1) {
            situations.push('BOUNCE_BACK_CANDIDATE');
          }
          if (latest.roadGamesBefore >= minStreak - 1) {
            situations.push('ROAD_TRIP_RETURN');
          }

          if (situations.length > 0 || latest.streakLength >= 2) {
            todaysCandidates.push({
              team: homeTeam,
              opponent: awayTeam,
              location: 'HOME',
              spread: spread,
              winsBeforeGame: latest.winsBeforeGame + (latest.margin > 0 ? 1 : 0),
              lossesBeforeGame: latest.lossesBeforeGame + (latest.margin < 0 ? 1 : 0),
              winPct: ((latest.winsBeforeGame + (latest.margin > 0 ? 1 : 0)) /
                       (latest.winsBeforeGame + latest.lossesBeforeGame + 1)),
              currentStreak: `${latest.streakType}${latest.streakLength + (latest.margin > 0 ? (latest.streakType === 'W' ? 1 : 0) : (latest.streakType === 'L' ? 1 : 0))}`,
              roadGamesBefore: 0, // Home game
              situationMatch: situations,
            });
          }
        }

        // Check away team situation
        const awayGames = allGames.filter(g => g.team === awayTeam).sort((a, b) => b.gameDate.getTime() - a.gameDate.getTime());
        if (awayGames.length > 0) {
          const latest = awayGames[0];
          const situations: string[] = [];

          if (latest.streakType === 'W' && latest.streakLength >= minStreak - 1) {
            situations.push('HOT_STREAK');
          }

          if (situations.length > 0 || latest.streakLength >= 2) {
            todaysCandidates.push({
              team: awayTeam,
              opponent: homeTeam,
              location: 'AWAY',
              spread: spread ? -spread : null,
              winsBeforeGame: latest.winsBeforeGame + (latest.margin > 0 ? 1 : 0),
              lossesBeforeGame: latest.lossesBeforeGame + (latest.margin < 0 ? 1 : 0),
              winPct: ((latest.winsBeforeGame + (latest.margin > 0 ? 1 : 0)) /
                       (latest.winsBeforeGame + latest.lossesBeforeGame + 1)),
              currentStreak: `${latest.streakType}${latest.streakLength + (latest.margin > 0 ? (latest.streakType === 'W' ? 1 : 0) : (latest.streakType === 'L' ? 1 : 0))}`,
              roadGamesBefore: latest.roadGamesBefore + (latest.location === 'AWAY' ? 1 : 0),
              situationMatch: situations,
            });
          }
        }
      }

      // Filter to only bounce-back candidates if that's the situation
      if (situation === 'bounce_back') {
        todaysCandidates = todaysCandidates.filter(c => c.situationMatch.includes('BOUNCE_BACK_CANDIDATE'));
      }
    }

    // Build response
    const recommendation = edge > 10
      ? 'HISTORICALLY PROFITABLE SPOT'
      : edge > 5
        ? 'SLIGHT EDGE'
        : edge < -10
          ? 'FADE THIS SITUATION'
          : 'NEUTRAL';

    return NextResponse.json({
      success: true,
      league: league.toUpperCase(),
      season,
      situation,
      filters: {
        minRecord: `${(minRecord * 100).toFixed(0)}%`,
        minStreak,
      },
      analysis: {
        description: situationLabel,
        methodology: 'Running record and streak calculated before each game. Cover = margin beats spread.',
        dataSource: 'SportsDB historical games',
      },
      summary: {
        totalGames,
        totalCovers,
        coverRate: coverRate.toFixed(1),
        edge: edge.toFixed(1),
        avgMarginVsSpread: avgMarginVsSpread.toFixed(1),
        recommendation,
      },
      topTeams: teamStats.slice(0, 10),
      worstTeams: teamStats.slice(-5).reverse(),
      ...(showToday && {
        todaysCandidates: todaysCandidates.slice(0, 10),
        todaysInsight: todaysCandidates.length > 0
          ? `Found ${todaysCandidates.length} potential ${situation} candidates for today's games`
          : `No ${situation} candidates found for today's games`,
      }),
      recentGames: situationalGames.slice(0, 20).map(g => ({
        team: g.team,
        date: g.gameDate.toISOString().split('T')[0],
        opponent: g.opponent,
        location: g.location,
        result: g.margin > 0 ? 'W' : 'L',
        score: `${g.teamScore}-${g.oppScore}`,
        spread: g.spread.toFixed(1),
        covered: g.covered ? 'YES' : 'NO',
        marginVsSpread: g.marginVsSpread.toFixed(1),
        recordBeforeGame: `${g.winsBeforeGame}-${g.lossesBeforeGame}`,
        streakBeforeGame: g.streakType ? `${g.streakType}${g.streakLength}` : 'N/A',
      })),
      insight: `${league.toUpperCase()} ${situationLabel}: ${totalGames} games, ${coverRate.toFixed(1)}% cover rate (${edge > 0 ? '+' : ''}${edge.toFixed(1)}% edge). ${recommendation}`,
    });

  } catch (error) {
    const errorMessage = error instanceof Error ? error.message : String(error);
    console.error('[analytics/ats-situational] Error:', errorMessage);
    return NextResponse.json(
      { success: false, error: `Failed to analyze situational ATS: ${errorMessage}` },
      { status: 500 }
    );
  }
}