/**
 * Direct Sports Query Service
 *
 * Provides clean, direct access to sports data without filtering or blocking.
 * Call these functions directly to get accurate data from the database.
 */

import { getSportsDb } from '@/lib/sportsDb';

export interface PlayerInfo {
  id: string;
  name: string;
  team: string;
  teamFullName?: string;
  league: string;
  position?: string;
  teamHistory?: Array<{ team: string; firstGame: string; lastGame: string }>;
}

export interface TeamRoster {
  team: string;
  teamFullName: string;
  league: string;
  players: Array<{ name: string; position?: string }>;
}

export interface GameInfo {
  id: string;
  date: string;
  homeTeam: string;
  awayTeam: string;
  homeScore?: number;
  awayScore?: number;
  status?: string;
  moneylineHome?: number;
  moneylineAway?: number;
  spreadHome?: number;
  total?: number;
}

/**
 * Get player's current team and info
 */
export async function getPlayerTeam(playerName: string): Promise<PlayerInfo | null> {
  const db = getSportsDb();

  // Try CanonicalPlayer first (most accurate)
  const canonical = await db.canonicalPlayer.findFirst({
    where: {
      OR: [
        { fullName: { contains: playerName, mode: 'insensitive' } },
        { normalizedName: { contains: playerName.toLowerCase().replace(/[^a-z\s]/g, '') } },
      ],
      league: { in: ['nba', 'nfl', 'nhl', 'mlb'] }
    },
    include: { team: true }
  });

  if (canonical) {
    // Get team history from game metrics
    const teamHistory = await db.$queryRaw<Array<{team: string, first_game: Date, last_game: Date}>>`
      SELECT team, MIN("gameDate") as first_game, MAX("gameDate") as last_game
      FROM "PlayerGameMetric"
      WHERE "playerName" ILIKE ${`%${playerName}%`}
        AND team IS NOT NULL
        AND "gameDate" >= NOW() - INTERVAL '1 year'
      GROUP BY team
      ORDER BY MIN("gameDate")
    `;

    return {
      id: canonical.id.toString(),
      name: canonical.fullName,
      team: canonical.team?.abbr || 'Unknown',
      teamFullName: canonical.team?.fullName,
      league: canonical.league,
      position: canonical.position || undefined,
      teamHistory: teamHistory.map(t => ({
        team: t.team,
        firstGame: t.first_game.toISOString().split('T')[0],
        lastGame: t.last_game.toISOString().split('T')[0]
      }))
    };
  }

  // Fallback: Get from recent game metrics
  const recentGame = await db.playerGameMetric.findFirst({
    where: {
      playerName: { contains: playerName, mode: 'insensitive' },
      team: { not: null }
    },
    orderBy: { gameDate: 'desc' }
  });

  if (recentGame?.team) {
    return {
      id: recentGame.id.toString(),
      name: recentGame.playerName || playerName,
      team: recentGame.team,
      league: recentGame.league,
      position: recentGame.position || undefined
    };
  }

  // Final fallback: Player table
  const player = await db.player.findFirst({
    where: {
      name: { contains: playerName, mode: 'insensitive' },
      team: { not: '' }
    }
  });

  if (player?.team) {
    return {
      id: player.id.toString(),
      name: player.name,
      team: player.team,
      league: player.league,
      position: player.position || undefined
    };
  }

  return null;
}

/**
 * Get team roster
 */
export async function getTeamRoster(teamQuery: string, league?: string): Promise<TeamRoster | null> {
  const db = getSportsDb();

  // Find team
  const team = await db.canonicalTeam.findFirst({
    where: {
      OR: [
        { abbr: { equals: teamQuery.toUpperCase(), mode: 'insensitive' } },
        { fullName: { contains: teamQuery, mode: 'insensitive' } },
        { nickname: { contains: teamQuery, mode: 'insensitive' } },
        { city: { contains: teamQuery, mode: 'insensitive' } },
      ],
      ...(league ? { league: league.toLowerCase() } : {})
    }
  });

  if (!team) return null;

  // Get players from CanonicalPlayer
  const players = await db.canonicalPlayer.findMany({
    where: { teamId: team.id, isActive: true },
    orderBy: { fullName: 'asc' }
  });

  if (players.length > 0) {
    return {
      team: team.abbr,
      teamFullName: team.fullName,
      league: team.league,
      players: players.map(p => ({ name: p.fullName, position: p.position || undefined }))
    };
  }

  // Fallback: Get from Player table
  const fallbackPlayers = await db.player.findMany({
    where: {
      OR: [
        { team: { contains: team.abbr, mode: 'insensitive' } },
        { team: { contains: team.fullName, mode: 'insensitive' } },
      ],
      league: team.league
    },
    orderBy: { name: 'asc' },
    take: 100
  });

  if (fallbackPlayers.length > 0) {
    return {
      team: team.abbr,
      teamFullName: team.fullName,
      league: team.league,
      players: fallbackPlayers.map(p => ({ name: p.name, position: p.position || undefined }))
    };
  }

  // Final fallback: Get from recent game metrics
  const recentPlayers = await db.$queryRaw<Array<{player_name: string, position: string | null}>>`
    SELECT DISTINCT "playerName" as player_name, "position"
    FROM "PlayerGameMetric"
    WHERE team ILIKE ${`%${team.abbr}%`}
      AND "gameDate" >= NOW() - INTERVAL '30 days'
    ORDER BY "playerName"
    LIMIT 50
  `;

  return {
    team: team.abbr,
    teamFullName: team.fullName,
    league: team.league,
    players: recentPlayers.map(p => ({ name: p.player_name, position: p.position || undefined }))
  };
}

/**
 * Get player stats for a date range
 */
export async function getPlayerStats(
  playerName: string,
  options?: { days?: number; season?: number; statKeys?: string[] }
): Promise<Array<{ date: string; team: string; stats: Record<string, number> }>> {
  const db = getSportsDb();
  const { days = 30, season, statKeys } = options || {};

  const dateFilter = season
    ? { gameDate: { gte: new Date(`${season}-01-01`), lte: new Date(`${season}-12-31`) } }
    : { gameDate: { gte: new Date(Date.now() - days * 24 * 60 * 60 * 1000) } };

  const metrics = await db.playerGameMetric.findMany({
    where: {
      playerName: { contains: playerName, mode: 'insensitive' },
      ...dateFilter,
      ...(statKeys ? { statKey: { in: statKeys } } : {})
    },
    orderBy: { gameDate: 'desc' },
    take: 500
  });

  // Group by game date
  const gameMap = new Map<string, { team: string; stats: Record<string, number> }>();

  for (const m of metrics) {
    const dateKey = m.gameDate.toISOString().split('T')[0];
    if (!gameMap.has(dateKey)) {
      gameMap.set(dateKey, { team: m.team || 'UNK', stats: {} });
    }
    const game = gameMap.get(dateKey)!;
    game.stats[m.statKey] = m.value;
  }

  return Array.from(gameMap.entries()).map(([date, data]) => ({
    date,
    team: data.team,
    stats: data.stats
  }));
}

/**
 * Get upcoming games for a team or league
 */
export async function getUpcomingGames(
  options: { team?: string; league?: string; days?: number }
): Promise<GameInfo[]> {
  const db = getSportsDb();
  const { team, league, days = 7 } = options;

  const now = new Date();
  const futureDate = new Date(Date.now() + days * 24 * 60 * 60 * 1000);

  const games = await db.sportsGame.findMany({
    where: {
      gameDate: { gte: now, lte: futureDate },
      ...(league ? { league: league.toLowerCase() } : {}),
      ...(team ? {
        OR: [
          { homeTeam: { contains: team, mode: 'insensitive' } },
          { awayTeam: { contains: team, mode: 'insensitive' } },
        ]
      } : {})
    },
    orderBy: { gameDate: 'asc' },
    take: 50
  });

  return games.map(g => ({
    id: g.id.toString(),
    date: g.gameDate.toISOString(),
    homeTeam: g.homeTeam,
    awayTeam: g.awayTeam,
    homeScore: g.homeScore ?? undefined,
    awayScore: g.awayScore ?? undefined,
    status: g.status ?? undefined,
    moneylineHome: g.moneylineHome ?? undefined,
    moneylineAway: g.moneylineAway ?? undefined,
    spreadHome: g.spreadHome ?? undefined,
    total: g.total ?? undefined
  }));
}

/**
 * Get recent games/results
 */
export async function getRecentGames(
  options: { team?: string; league?: string; days?: number }
): Promise<GameInfo[]> {
  const db = getSportsDb();
  const { team, league, days = 7 } = options;

  const now = new Date();
  const pastDate = new Date(Date.now() - days * 24 * 60 * 60 * 1000);

  const games = await db.sportsGame.findMany({
    where: {
      gameDate: { gte: pastDate, lte: now },
      homeScore: { not: null },
      ...(league ? { league: league.toLowerCase() } : {}),
      ...(team ? {
        OR: [
          { homeTeam: { contains: team, mode: 'insensitive' } },
          { awayTeam: { contains: team, mode: 'insensitive' } },
        ]
      } : {})
    },
    orderBy: { gameDate: 'desc' },
    take: 50
  });

  return games.map(g => ({
    id: g.id.toString(),
    date: g.gameDate.toISOString(),
    homeTeam: g.homeTeam,
    awayTeam: g.awayTeam,
    homeScore: g.homeScore ?? undefined,
    awayScore: g.awayScore ?? undefined,
    status: g.status ?? undefined,
    moneylineHome: g.moneylineHome ?? undefined,
    moneylineAway: g.moneylineAway ?? undefined,
    spreadHome: g.spreadHome ?? undefined,
    total: g.total ?? undefined
  }));
}

/**
 * Search for any entity (player, team, game)
 */
export async function searchSports(query: string): Promise<{
  players: PlayerInfo[];
  teams: Array<{ abbr: string; fullName: string; league: string }>;
}> {
  const db = getSportsDb();

  // Search players
  const players = await db.canonicalPlayer.findMany({
    where: {
      OR: [
        { fullName: { contains: query, mode: 'insensitive' } },
        { normalizedName: { contains: query.toLowerCase() } },
      ]
    },
    include: { team: true },
    take: 10
  });

  // Search teams
  const teams = await db.canonicalTeam.findMany({
    where: {
      OR: [
        { abbr: { contains: query, mode: 'insensitive' } },
        { fullName: { contains: query, mode: 'insensitive' } },
        { nickname: { contains: query, mode: 'insensitive' } },
        { city: { contains: query, mode: 'insensitive' } },
      ]
    },
    take: 10
  });

  return {
    players: players.map(p => ({
      id: p.id.toString(),
      name: p.fullName,
      team: p.team?.abbr || 'Unknown',
      teamFullName: p.team?.fullName,
      league: p.league,
      position: p.position || undefined
    })),
    teams: teams.map(t => ({
      abbr: t.abbr,
      fullName: t.fullName,
      league: t.league
    }))
  };
}

/**
 * Get player trade history
 */
export async function getPlayerTradeHistory(playerName: string): Promise<Array<{
  team: string;
  firstGame: string;
  lastGame: string;
  gamesPlayed: number;
}>> {
  const db = getSportsDb();

  const history = await db.$queryRaw<Array<{
    team: string;
    first_game: Date;
    last_game: Date;
    games: bigint;
  }>>`
    SELECT
      team,
      MIN("gameDate") as first_game,
      MAX("gameDate") as last_game,
      COUNT(DISTINCT "gameDate") as games
    FROM "PlayerGameMetric"
    WHERE "playerName" ILIKE ${`%${playerName}%`}
      AND team IS NOT NULL
    GROUP BY team
    ORDER BY MIN("gameDate")
  `;

  return history.map(h => ({
    team: h.team,
    firstGame: h.first_game.toISOString().split('T')[0],
    lastGame: h.last_game.toISOString().split('T')[0],
    gamesPlayed: Number(h.games)
  }));
}

/**
 * Format response for chat - call this to get formatted output for users
 */
export function formatPlayerResponse(player: PlayerInfo): string {
  let response = `**${player.name}** plays for the **${player.teamFullName || player.team}**\n`;
  response += `- League: ${player.league.toUpperCase()}\n`;
  if (player.position) response += `- Position: ${player.position}\n`;

  if (player.teamHistory && player.teamHistory.length > 1) {
    response += `\n**Team History:**\n`;
    player.teamHistory.forEach(t => {
      response += `- ${t.team}: ${t.firstGame} to ${t.lastGame}\n`;
    });
  }

  return response;
}

export function formatRosterResponse(roster: TeamRoster): string {
  let response = `**${roster.teamFullName} Roster** (${roster.league.toUpperCase()})\n\n`;

  if (roster.players.length === 0) {
    response += `No players found in database.\n`;
  } else {
    roster.players.forEach(p => {
      response += `- ${p.name}${p.position ? ` (${p.position})` : ''}\n`;
    });
    response += `\n*${roster.players.length} players*`;
  }

  return response;
}

// ==================== EXTERNAL API ACCESS ====================

/**
 * Get live odds from the Odds API
 */
export async function getLiveOdds(league: string): Promise<any[]> {
  const db = getSportsDb();

  // Get most recent odds from GameOdds table
  const recentOdds = await db.gameOdds.findMany({
    where: {
      league: league.toLowerCase(),
      fetchedAt: { gte: new Date(Date.now() - 24 * 60 * 60 * 1000) }
    },
    orderBy: { fetchedAt: 'desc' },
    take: 100
  });

  return recentOdds;
}

/**
 * Get consensus odds for upcoming games
 */
export async function getConsensusOdds(league: string): Promise<any[]> {
  const db = getSportsDb();

  const consensus = await db.consensusOdds.findMany({
    where: {
      league: league.toLowerCase(),
      gameDate: { gte: new Date() }
    },
    orderBy: { gameDate: 'asc' },
    take: 50
  });

  return consensus;
}

/**
 * Get line movements for a game
 */
export async function getLineMovements(gameId: string | number): Promise<any[]> {
  const db = getSportsDb();

  const snapshots = await db.oddsSnapshot.findMany({
    where: { gameId: BigInt(gameId) },
    orderBy: { capturedAt: 'asc' }
  });

  return snapshots.map(s => ({
    capturedAt: s.capturedAt,
    moneylineHome: s.moneylineHome,
    moneylineAway: s.moneylineAway,
    spreadHome: s.spreadHome,
    total: s.total
  }));
}

/**
 * Get injuries for a team/league
 */
export async function getInjuries(options: { team?: string; league?: string }): Promise<any[]> {
  const db = getSportsDb();
  const { team, league } = options;

  const injuries = await db.injury.findMany({
    where: {
      ...(league ? { league: league.toLowerCase() } : {}),
      ...(team ? { team: { contains: team, mode: 'insensitive' } } : {}),
      status: { not: 'Active' }
    },
    orderBy: { updatedAt: 'desc' },
    take: 50
  });

  return injuries;
}

/**
 * Get player props for upcoming games
 */
export async function getPlayerProps(options: { player?: string; team?: string; league?: string }): Promise<any[]> {
  const db = getSportsDb();
  const { player, team, league } = options;

  const props = await db.playerPropLine.findMany({
    where: {
      ...(league ? { league: league.toLowerCase() } : {}),
      ...(player ? { playerName: { contains: player, mode: 'insensitive' } } : {}),
      ...(team ? { team: { contains: team, mode: 'insensitive' } } : {}),
      gameDate: { gte: new Date() }
    },
    orderBy: { gameDate: 'asc' },
    take: 100
  });

  return props;
}

/**
 * Get standings for a league
 */
export async function getStandings(league: string, season?: number): Promise<any[]> {
  const db = getSportsDb();
  const currentSeason = season || new Date().getFullYear();

  const standings = await db.teamStanding.findMany({
    where: {
      league: league.toLowerCase(),
      season: currentSeason
    },
    orderBy: [{ conference: 'asc' }, { division: 'asc' }, { wins: 'desc' }]
  });

  return standings;
}

/**
 * Get team schedule
 */
export async function getTeamSchedule(team: string, options?: { upcoming?: boolean; past?: boolean }): Promise<GameInfo[]> {
  const db = getSportsDb();
  const { upcoming = true, past = false } = options || {};

  const now = new Date();
  const games = await db.sportsGame.findMany({
    where: {
      OR: [
        { homeTeam: { contains: team, mode: 'insensitive' } },
        { awayTeam: { contains: team, mode: 'insensitive' } },
      ],
      ...(upcoming && !past ? { gameDate: { gte: now } } : {}),
      ...(past && !upcoming ? { gameDate: { lt: now } } : {}),
    },
    orderBy: { gameDate: upcoming ? 'asc' : 'desc' },
    take: 20
  });

  return games.map(g => ({
    id: g.id.toString(),
    date: g.gameDate.toISOString(),
    homeTeam: g.homeTeam,
    awayTeam: g.awayTeam,
    homeScore: g.homeScore ?? undefined,
    awayScore: g.awayScore ?? undefined,
    status: g.status ?? undefined,
    moneylineHome: g.moneylineHome ?? undefined,
    moneylineAway: g.moneylineAway ?? undefined,
    spreadHome: g.spreadHome ?? undefined,
    total: g.total ?? undefined
  }));
}

/**
 * Get betting trends/analytics
 */
export async function getBettingTrends(options: { team?: string; league?: string }): Promise<any> {
  const db = getSportsDb();
  const { team, league } = options;

  // Get ATS records
  const atsRecords = await db.teamATSRecord.findMany({
    where: {
      ...(league ? { league: league.toLowerCase() } : {}),
      ...(team ? { team: { contains: team, mode: 'insensitive' } } : {})
    },
    take: 50
  });

  // Get situational trends
  const situational = await db.situationalTrend.findMany({
    where: {
      ...(league ? { league: league.toLowerCase() } : {}),
      ...(team ? { team: { contains: team, mode: 'insensitive' } } : {})
    },
    take: 50
  });

  return { atsRecords, situational };
}

/**
 * Execute any raw SQL query (for advanced users)
 */
export async function executeRawQuery(sql: string, params: any[] = []): Promise<any[]> {
  const db = getSportsDb();

  // Safety: only allow SELECT queries
  if (!sql.trim().toLowerCase().startsWith('select')) {
    throw new Error('Only SELECT queries are allowed');
  }

  const results = await db.$queryRawUnsafe(sql, ...params);
  return results as any[];
}

/**
 * Get data freshness info (when was data last updated)
 */
export async function getDataFreshness(): Promise<{
  lastOddsUpdate: Date | null;
  lastScoresUpdate: Date | null;
  lastPropsUpdate: Date | null;
  gamesWithOddsToday: number;
}> {
  const db = getSportsDb();

  const [lastOdds, lastGame, lastProp, todayGames] = await Promise.all([
    db.gameOdds.findFirst({ orderBy: { fetchedAt: 'desc' }, select: { fetchedAt: true } }),
    db.sportsGame.findFirst({
      where: { homeScore: { not: null } },
      orderBy: { updatedAt: 'desc' },
      select: { updatedAt: true }
    }),
    db.playerPropLine.findFirst({ orderBy: { createdAt: 'desc' }, select: { createdAt: true } }),
    db.sportsGame.count({
      where: {
        gameDate: {
          gte: new Date(new Date().setHours(0, 0, 0, 0)),
          lt: new Date(new Date().setHours(23, 59, 59, 999))
        },
        moneylineHome: { not: null }
      }
    })
  ]);

  return {
    lastOddsUpdate: lastOdds?.fetchedAt || null,
    lastScoresUpdate: lastGame?.updatedAt || null,
    lastPropsUpdate: lastProp?.createdAt || null,
    gamesWithOddsToday: todayGames
  };
}

// ==================== STRATEGY TESTING & BACKTESTING ====================

export interface StrategyResult {
  strategyName: string;
  league: string;
  season?: number;
  totalBets: number;
  wins: number;
  losses: number;
  pushes: number;
  winRate: number;
  roi: number;
  netProfit: number;
  avgOdds: number;
  bestStreak: number;
  worstStreak: number;
  sampleBets?: Array<{
    date: string;
    matchup: string;
    bet: string;
    odds: number;
    result: string;
    profit: number;
  }>;
}

export interface PropsBacktestResult {
  propType: string;
  league: string;
  totalBets: number;
  wins: number;
  losses: number;
  pushes: number;
  winRate: number;
  roi: number;
  netProfit: number;
  avgLine: number;
  sampleBets?: Array<{
    date: string;
    player: string;
    line: number;
    actual: number;
    result: string;
  }>;
}

/**
 * Run a betting strategy backtest
 */
export async function runStrategyBacktest(options: {
  strategy: string;
  league?: string;
  season?: number;
  betType?: 'moneyline' | 'spread' | 'total';
  stake?: number;
}): Promise<StrategyResult | null> {
  const db = getSportsDb();
  const { strategy, league, season = new Date().getFullYear(), betType = 'moneyline', stake = 100 } = options;

  const strategyLower = strategy.toLowerCase();

  // Define strategy filters based on common betting strategies
  // Pre-filter at database level for efficiency and accuracy
  let whereClause: any = {
    homeScore: { not: null },
    awayScore: { not: null },
    moneylineHome: { not: null },
    moneylineAway: { not: null }
  };

  if (league) {
    whereClause.league = league.toLowerCase();
  }

  // Add strategy-specific filters at the database level for accuracy
  if (strategyLower.includes('home') && strategyLower.includes('underdog')) {
    // Home underdog = positive home moneyline
    whereClause.moneylineHome = { gt: 0 };
  } else if (strategyLower.includes('away') && strategyLower.includes('underdog')) {
    // Away underdog = positive away moneyline
    whereClause.moneylineAway = { gt: 0 };
  } else if (strategyLower.includes('home') && strategyLower.includes('favorite')) {
    // Home favorite = negative home moneyline
    whereClause.moneylineHome = { lt: 0 };
  } else if (strategyLower.includes('heavy') && strategyLower.includes('favorite')) {
    // Heavy favorite = odds < -200
    whereClause.OR = [
      { moneylineHome: { lt: -200 } },
      { moneylineAway: { lt: -200 } }
    ];
  } else if (strategyLower.includes('big') && strategyLower.includes('underdog')) {
    // Big underdog = odds > +200
    whereClause.OR = [
      { moneylineHome: { gt: 200 } },
      { moneylineAway: { gt: 200 } }
    ];
  }

  // Season filter - handle sports that span two calendar years
  // Database stores season based on when it STARTS:
  // - NBA/NHL 2025-26 season is stored as season=2025 (starts Oct 2025)
  // - NFL 2025-26 season is stored as season=2025 (starts Sep 2025)
  // When user says "2026 season", they mean the current season ending in 2026
  const now = new Date();
  let seasonStart: Date;
  let seasonEnd: Date;
  let dbSeason: number;

  const leagueLower = (league || '').toLowerCase();

  if (['nba', 'nhl'].includes(leagueLower)) {
    // NBA/NHL: "2026 season" = 2025-26 season = season field 2025
    // Date range: Oct 2025 - Jun 2026
    dbSeason = season - 1;
    seasonStart = new Date(`${season - 1}-10-01`);
    seasonEnd = new Date(`${season}-06-30`);
  } else if (['nfl', 'ncaaf'].includes(leagueLower)) {
    // NFL/NCAAF: "2026 season" = 2025-26 season = season field 2025
    // Date range: Sep 2025 - Feb 2026
    dbSeason = season - 1;
    seasonStart = new Date(`${season - 1}-09-01`);
    seasonEnd = new Date(`${season}-02-28`);
  } else if (['mlb'].includes(leagueLower)) {
    // MLB: "2026 season" = season field 2026
    // Date range: Mar 2026 - Oct 2026
    dbSeason = season;
    seasonStart = new Date(`${season}-03-01`);
    seasonEnd = new Date(`${season}-10-31`);
  } else {
    // Default: full calendar year
    dbSeason = season;
    seasonStart = new Date(`${season}-01-01`);
    seasonEnd = new Date(`${season}-12-31`);
  }

  // Limit to current date to only include completed games
  if (seasonEnd > now) {
    seasonEnd = now;
  }

  whereClause.gameDate = {
    gte: seasonStart,
    lte: seasonEnd
  };

  // Use the correct season field value based on how the database stores it
  whereClause.season = dbSeason;

  console.log(`[Backtest] Season ${season} (db season=${dbSeason}), date range: ${seasonStart.toISOString().slice(0,10)} to ${seasonEnd.toISOString().slice(0,10)}`);

  // Fetch games
  const games = await db.sportsGame.findMany({
    where: whereClause,
    orderBy: { gameDate: 'asc' },
    take: 5000
  });

  console.log(`[Backtest] Strategy: ${strategy}, League: ${league || 'all'}, Season: ${season}`);
  console.log(`[Backtest] Found ${games.length} games matching criteria`);

  if (games.length === 0) return null;

  let wins = 0, losses = 0, pushes = 0;
  let totalWagered = 0, totalReturned = 0;
  let oddsSum = 0;
  let currentStreak = 0, bestStreak = 0, worstStreak = 0;
  const sampleBets: StrategyResult['sampleBets'] = [];

  for (const game of games) {
    // Skip games without proper scores
    if (game.homeScore === null || game.awayScore === null) continue;
    if (game.moneylineHome === null || game.moneylineAway === null) continue;

    let betSide: 'home' | 'away' | null = null;
    let odds = 0;
    let description = '';

    // Strategy: Home Favorites (already filtered at DB level, but validate)
    if (strategyLower.includes('home') && strategyLower.includes('favorite')) {
      if (game.moneylineHome < 0) {
        betSide = 'home';
        odds = game.moneylineHome;
        description = `${game.homeTeam} ML ${odds}`;
      }
    }
    // Strategy: Away Underdogs (already filtered at DB level, but validate)
    else if (strategyLower.includes('away') && strategyLower.includes('underdog')) {
      if (game.moneylineAway > 0) {
        betSide = 'away';
        odds = game.moneylineAway;
        description = `${game.awayTeam} ML +${odds}`;
      }
    }
    // Strategy: Home Underdogs (already filtered at DB level, but validate)
    else if (strategyLower.includes('home') && strategyLower.includes('underdog')) {
      if (game.moneylineHome > 0) {
        betSide = 'home';
        odds = game.moneylineHome;
        description = `${game.homeTeam} ML +${odds}`;
      }
    }
    // Strategy: Heavy Favorites (odds < -200)
    else if (strategyLower.includes('heavy') && strategyLower.includes('favorite')) {
      const homeOdds = game.moneylineHome;
      const awayOdds = game.moneylineAway;
      if (homeOdds < -200) {
        betSide = 'home';
        odds = homeOdds;
        description = `${game.homeTeam} ML ${odds}`;
      } else if (awayOdds < -200) {
        betSide = 'away';
        odds = awayOdds;
        description = `${game.awayTeam} ML ${odds}`;
      }
    }
    // Strategy: Big Underdogs (odds > +200)
    else if (strategyLower.includes('big') && strategyLower.includes('underdog')) {
      const homeOdds = game.moneylineHome;
      const awayOdds = game.moneylineAway;
      if (homeOdds > 200) {
        betSide = 'home';
        odds = homeOdds;
        description = `${game.homeTeam} ML +${odds}`;
      } else if (awayOdds > 200) {
        betSide = 'away';
        odds = awayOdds;
        description = `${game.awayTeam} ML +${odds}`;
      }
    }
    // Strategy: Overs (totals betting)
    else if (strategyLower.includes('over') && game.total) {
      const actualTotal = (game.homeScore || 0) + (game.awayScore || 0);
      betSide = 'home'; // dummy for counting
      odds = -110;
      description = `Over ${game.total}`;

      // Override result calculation for totals
      const won = actualTotal > game.total;
      const push = actualTotal === game.total;

      if (push) {
        pushes++;
        totalWagered += stake;
        totalReturned += stake;
      } else if (won) {
        wins++;
        totalWagered += stake;
        totalReturned += stake + (stake * (100 / 110));
        currentStreak = Math.max(1, currentStreak + 1);
        bestStreak = Math.max(bestStreak, currentStreak);
      } else {
        losses++;
        totalWagered += stake;
        currentStreak = Math.min(-1, currentStreak - 1);
        worstStreak = Math.min(worstStreak, currentStreak);
      }

      if (sampleBets.length < 10) {
        sampleBets.push({
          date: game.gameDate.toISOString().split('T')[0],
          matchup: `${game.awayTeam} @ ${game.homeTeam}`,
          bet: description,
          odds: -110,
          result: push ? 'push' : (won ? 'win' : 'loss'),
          profit: push ? 0 : (won ? (stake * (100 / 110)) : -stake)
        });
      }
      continue;
    }
    // Strategy: Unders (totals betting)
    else if (strategyLower.includes('under') && game.total) {
      const actualTotal = (game.homeScore || 0) + (game.awayScore || 0);
      betSide = 'home';
      odds = -110;
      description = `Under ${game.total}`;

      const won = actualTotal < game.total;
      const push = actualTotal === game.total;

      if (push) {
        pushes++;
        totalWagered += stake;
        totalReturned += stake;
      } else if (won) {
        wins++;
        totalWagered += stake;
        totalReturned += stake + (stake * (100 / 110));
        currentStreak = Math.max(1, currentStreak + 1);
        bestStreak = Math.max(bestStreak, currentStreak);
      } else {
        losses++;
        totalWagered += stake;
        currentStreak = Math.min(-1, currentStreak - 1);
        worstStreak = Math.min(worstStreak, currentStreak);
      }

      if (sampleBets.length < 10) {
        sampleBets.push({
          date: game.gameDate.toISOString().split('T')[0],
          matchup: `${game.awayTeam} @ ${game.homeTeam}`,
          bet: description,
          odds: -110,
          result: push ? 'push' : (won ? 'win' : 'loss'),
          profit: push ? 0 : (won ? (stake * (100 / 110)) : -stake)
        });
      }
      continue;
    }
    // Default: All favorites (bet on the favorite in every game)
    else if (strategyLower.includes('favorite') && !strategyLower.includes('home') && !strategyLower.includes('away')) {
      const homeOdds = game.moneylineHome;
      const awayOdds = game.moneylineAway;
      // Favorite has lower (more negative) odds
      if (homeOdds < awayOdds && homeOdds < 0) {
        betSide = 'home';
        odds = homeOdds;
        description = `${game.homeTeam} ML ${odds}`;
      } else if (awayOdds < homeOdds && awayOdds < 0) {
        betSide = 'away';
        odds = awayOdds;
        description = `${game.awayTeam} ML ${odds}`;
      }
    }
    // Default: All underdogs (bet on the underdog in every game)
    else if ((strategyLower.includes('underdog') || strategyLower.includes('dog')) && !strategyLower.includes('home') && !strategyLower.includes('away')) {
      const homeOdds = game.moneylineHome;
      const awayOdds = game.moneylineAway;
      // Underdog has higher (more positive) odds
      if (homeOdds > awayOdds && homeOdds > 0) {
        betSide = 'home';
        odds = homeOdds;
        description = `${game.homeTeam} ML +${odds}`;
      } else if (awayOdds > homeOdds && awayOdds > 0) {
        betSide = 'away';
        odds = awayOdds;
        description = `${game.awayTeam} ML +${odds}`;
      }
    }

    if (!betSide || odds === 0) continue;

    // Determine result
    const homeWon = (game.homeScore || 0) > (game.awayScore || 0);
    const awayWon = (game.awayScore || 0) > (game.homeScore || 0);
    const tie = game.homeScore === game.awayScore;

    let result: 'win' | 'loss' | 'push';
    let profit: number;

    if (tie) {
      result = 'push';
      profit = 0;
      pushes++;
      totalWagered += stake;
      totalReturned += stake;
    } else if ((betSide === 'home' && homeWon) || (betSide === 'away' && awayWon)) {
      result = 'win';
      profit = odds > 0 ? stake * (odds / 100) : stake * (100 / Math.abs(odds));
      wins++;
      totalWagered += stake;
      totalReturned += stake + profit;
      currentStreak = Math.max(1, currentStreak + 1);
      bestStreak = Math.max(bestStreak, currentStreak);
    } else {
      result = 'loss';
      profit = -stake;
      losses++;
      totalWagered += stake;
      currentStreak = Math.min(-1, currentStreak - 1);
      worstStreak = Math.min(worstStreak, currentStreak);
    }

    oddsSum += odds;

    if (sampleBets.length < 10) {
      sampleBets.push({
        date: game.gameDate.toISOString().split('T')[0],
        matchup: `${game.awayTeam} @ ${game.homeTeam}`,
        bet: description,
        odds,
        result,
        profit
      });
    }
  }

  const totalBets = wins + losses + pushes;
  if (totalBets === 0) {
    console.log(`[Backtest] No qualifying bets found for strategy: ${strategy}`);
    return null;
  }

  const netProfit = totalReturned - totalWagered;
  const winRate = wins + losses > 0 ? (wins / (wins + losses)) * 100 : 0;
  const roi = totalWagered > 0 ? (netProfit / totalWagered) * 100 : 0;

  console.log(`[Backtest] Results: ${wins}W-${losses}L-${pushes}P, ROI: ${roi.toFixed(1)}%`);

  return {
    strategyName: strategy,
    league: league || 'all',
    season,
    totalBets,
    wins,
    losses,
    pushes,
    winRate,
    roi,
    netProfit,
    avgOdds: totalBets > 0 ? oddsSum / totalBets : 0,
    bestStreak,
    worstStreak: Math.abs(worstStreak),
    sampleBets
  };
}

/**
 * Run player props backtest
 */
export async function runPropsBacktest(options: {
  league: string;
  propType?: string;
  betType?: 'over' | 'under' | 'both';
  minLine?: number;
  maxLine?: number;
  season?: number;
}): Promise<PropsBacktestResult[]> {
  // Import the existing props backtest module
  const { backtestFromPropOutcomes, backtestPlayerProps } = await import('@/lib/playerPropsBacktest');

  const results = await backtestFromPropOutcomes({
    league: options.league,
    propTypes: options.propType ? [options.propType] : undefined,
    betType: options.betType || 'over',
    minLine: options.minLine,
    maxLine: options.maxLine,
    season: options.season
  });

  return results.map(r => ({
    propType: r.prop_type,
    league: options.league,
    totalBets: r.total_bets,
    wins: r.wins,
    losses: r.losses,
    pushes: r.pushes,
    winRate: r.win_rate,
    roi: r.roi,
    netProfit: r.net_profit,
    avgLine: 0, // Could calculate from trades
    sampleBets: r.trades?.slice(0, 10).map(t => ({
      date: t.date,
      player: t.player_name,
      line: t.line,
      actual: t.actual,
      result: t.outcome
    }))
  }));
}

/**
 * Get available betting strategies
 */
export function getAvailableStrategies(): Array<{ name: string; description: string }> {
  return [
    { name: 'home favorites', description: 'Bet on home teams when they are favored' },
    { name: 'away underdogs', description: 'Bet on away teams when they are underdogs' },
    { name: 'home underdogs', description: 'Bet on home teams when they are underdogs' },
    { name: 'heavy favorites', description: 'Bet on teams with odds < -200' },
    { name: 'big underdogs', description: 'Bet on underdogs with odds > +200' },
    { name: 'overs', description: 'Bet on game totals going over the line' },
    { name: 'unders', description: 'Bet on game totals going under the line' },
    { name: 'all favorites', description: 'Bet on the favorite in every game' },
    { name: 'all underdogs', description: 'Bet on the underdog in every game' }
  ];
}

/**
 * Format strategy result for chat display
 */
export function formatStrategyResult(result: StrategyResult): string {
  const profitEmoji = result.netProfit >= 0 ? '📈' : '📉';
  const roiStr = result.roi >= 0 ? `+${result.roi.toFixed(1)}%` : `${result.roi.toFixed(1)}%`;
  const profitStr = result.netProfit >= 0 ? `+$${result.netProfit.toFixed(2)}` : `-$${Math.abs(result.netProfit).toFixed(2)}`;

  let response = `**Strategy Backtest: ${result.strategyName}**\n`;
  response += `League: ${result.league.toUpperCase()} | Season: ${result.season || new Date().getFullYear()}\n\n`;
  response += `${profitEmoji} **Results:**\n`;
  response += `- Record: ${result.wins}W - ${result.losses}L - ${result.pushes}P (${result.totalBets} total bets)\n`;
  response += `- Win Rate: ${result.winRate.toFixed(1)}%\n`;
  response += `- ROI: ${roiStr}\n`;
  response += `- Net Profit: ${profitStr} (on $100 units)\n`;
  response += `- Avg Odds: ${result.avgOdds > 0 ? '+' : ''}${result.avgOdds.toFixed(0)}\n`;
  response += `- Best Streak: ${result.bestStreak}W | Worst Streak: ${result.worstStreak}L\n`;

  if (result.sampleBets && result.sampleBets.length > 0) {
    response += `\n**Sample Bets:**\n`;
    result.sampleBets.slice(0, 5).forEach(b => {
      const profitStr = b.profit >= 0 ? `+$${b.profit.toFixed(0)}` : `-$${Math.abs(b.profit).toFixed(0)}`;
      response += `- ${b.date}: ${b.matchup} | ${b.bet} → ${b.result.toUpperCase()} (${profitStr})\n`;
    });
  }

  response += `\n*Note: Past performance does not guarantee future results.*`;
  return response;
}

/**
 * Format props backtest result for chat display
 */
export function formatPropsResult(results: PropsBacktestResult[]): string {
  if (results.length === 0) {
    return 'No props backtest data available for the specified criteria.';
  }

  let response = `**Player Props Backtest Results:**\n\n`;

  results.slice(0, 5).forEach(r => {
    const profitEmoji = r.netProfit >= 0 ? '📈' : '📉';
    const roiStr = r.roi >= 0 ? `+${r.roi.toFixed(1)}%` : `${r.roi.toFixed(1)}%`;

    response += `**${r.propType.replace(/_/g, ' ').toUpperCase()}** (${r.league.toUpperCase()})\n`;
    response += `- Bets: ${r.totalBets} | Win Rate: ${r.winRate.toFixed(1)}%\n`;
    response += `- ${profitEmoji} ROI: ${roiStr} | Profit: $${r.netProfit.toFixed(2)}\n\n`;
  });

  response += `*Based on historical props vs actual stats.*`;
  return response;
}

// ==================== SELF-LEARNING & ERROR HANDLING ====================

interface QueryError {
  query: string;
  error: string;
  timestamp: Date;
  resolved: boolean;
  solution?: string;
}

// In-memory error tracking for learning
const queryErrors: QueryError[] = [];
const queryPatterns: Map<string, { count: number; successRate: number }> = new Map();

/**
 * Log a query error for learning
 */
export function logQueryError(query: string, error: string): void {
  queryErrors.push({
    query,
    error,
    timestamp: new Date(),
    resolved: false
  });

  // Keep only last 100 errors
  if (queryErrors.length > 100) {
    queryErrors.shift();
  }
}

/**
 * Mark a query as successfully handled (for learning)
 */
export function logQuerySuccess(pattern: string): void {
  const existing = queryPatterns.get(pattern) || { count: 0, successRate: 1.0 };
  existing.count++;
  existing.successRate = Math.min(1.0, existing.successRate + 0.01);
  queryPatterns.set(pattern, existing);
}

/**
 * Try to auto-fix common query errors
 */
export async function autoFixQuery(query: string, errorType: string): Promise<{ fixed: boolean; suggestion?: string; data?: any }> {
  const queryLower = query.toLowerCase();

  // Fix: Team name variations
  if (errorType === 'team_not_found') {
    const teamAliases: Record<string, string[]> = {
      'lakers': ['la lakers', 'los angeles lakers', 'lal'],
      'warriors': ['gsw', 'golden state', 'gs warriors'],
      'celtics': ['boston celtics', 'bos', 'boston'],
      'yankees': ['ny yankees', 'new york yankees', 'nyy'],
      'dodgers': ['la dodgers', 'los angeles dodgers', 'lad'],
      'chiefs': ['kc chiefs', 'kansas city', 'kc'],
      'eagles': ['philly eagles', 'philadelphia', 'phi']
    };

    for (const [canonical, aliases] of Object.entries(teamAliases)) {
      if (aliases.some(a => queryLower.includes(a))) {
        return { fixed: true, suggestion: `Did you mean "${canonical}"?` };
      }
    }
  }

  // Fix: Player name spelling
  if (errorType === 'player_not_found') {
    const db = getSportsDb();

    // Try fuzzy search
    const words = queryLower.split(' ').filter(w => w.length > 2);
    for (const word of words) {
      const similar = await db.canonicalPlayer.findMany({
        where: { normalizedName: { contains: word } },
        take: 3
      });
      if (similar.length > 0) {
        return {
          fixed: true,
          suggestion: `Did you mean: ${similar.map(p => p.fullName).join(', ')}?`,
          data: similar
        };
      }
    }
  }

  // Fix: Date/season issues
  if (errorType === 'no_data_for_period') {
    const currentYear = new Date().getFullYear();
    return {
      fixed: true,
      suggestion: `Try specifying a season. Available: ${currentYear - 2}, ${currentYear - 1}, ${currentYear}`
    };
  }

  return { fixed: false };
}

/**
 * Get system health and data status
 */
export async function getSystemHealth(): Promise<{
  status: 'healthy' | 'degraded' | 'error';
  checks: Array<{ name: string; status: string; lastUpdate?: Date }>;
  recentErrors: number;
  querySuccessRate: number;
}> {
  const db = getSportsDb();

  const checks: Array<{ name: string; status: string; lastUpdate?: Date }> = [];

  // Check database connection
  try {
    await db.$queryRaw`SELECT 1`;
    checks.push({ name: 'Database', status: 'ok' });
  } catch {
    checks.push({ name: 'Database', status: 'error' });
  }

  // Check data freshness
  const freshness = await getDataFreshness();

  const oddsAge = freshness.lastOddsUpdate
    ? (Date.now() - freshness.lastOddsUpdate.getTime()) / (1000 * 60 * 60)
    : 999;

  checks.push({
    name: 'Odds Data',
    status: oddsAge < 24 ? 'ok' : oddsAge < 48 ? 'stale' : 'outdated',
    lastUpdate: freshness.lastOddsUpdate || undefined
  });

  checks.push({
    name: 'Props Data',
    status: freshness.lastPropsUpdate ? 'ok' : 'missing',
    lastUpdate: freshness.lastPropsUpdate || undefined
  });

  // Calculate overall status
  const hasError = checks.some(c => c.status === 'error');
  const hasStale = checks.some(c => c.status === 'stale' || c.status === 'outdated');

  // Query success rate
  let totalQueries = 0;
  let successfulQueries = 0;
  queryPatterns.forEach(p => {
    totalQueries += p.count;
    successfulQueries += p.count * p.successRate;
  });

  return {
    status: hasError ? 'error' : hasStale ? 'degraded' : 'healthy',
    checks,
    recentErrors: queryErrors.filter(e => !e.resolved).length,
    querySuccessRate: totalQueries > 0 ? successfulQueries / totalQueries : 1.0
  };
}

/**
 * Format system health for chat display
 */
export function formatSystemHealth(health: Awaited<ReturnType<typeof getSystemHealth>>): string {
  const statusEmoji = health.status === 'healthy' ? '✅' : health.status === 'degraded' ? '⚠️' : '❌';

  let response = `${statusEmoji} **System Status: ${health.status.toUpperCase()}**\n\n`;

  response += `**Components:**\n`;
  health.checks.forEach(c => {
    const emoji = c.status === 'ok' ? '✅' : c.status === 'stale' ? '⚠️' : '❌';
    response += `- ${emoji} ${c.name}: ${c.status}`;
    if (c.lastUpdate) {
      response += ` (updated ${c.lastUpdate.toLocaleString()})`;
    }
    response += '\n';
  });

  response += `\n**Performance:**\n`;
  response += `- Query Success Rate: ${(health.querySuccessRate * 100).toFixed(1)}%\n`;
  response += `- Unresolved Errors: ${health.recentErrors}\n`;

  return response;
}

// ==================== LIVE API ACCESS ====================

/**
 * Fetch live/fresh data from external APIs when database is stale
 */

interface LiveOddsResult {
  source: string;
  fetchedAt: Date;
  games: Array<{
    homeTeam: string;
    awayTeam: string;
    gameTime: string;
    moneylineHome?: number;
    moneylineAway?: number;
    spreadHome?: number;
    total?: number;
    status?: string;
  }>;
}

interface LiveScoresResult {
  source: string;
  fetchedAt: Date;
  games: Array<{
    homeTeam: string;
    awayTeam: string;
    homeScore?: number;
    awayScore?: number;
    status: string;
    period?: string;
    clock?: string;
  }>;
}

/**
 * Fetch live odds directly from SportsGameOdds API
 */
export async function fetchLiveOddsFromApi(league: string): Promise<LiveOddsResult | null> {
  try {
    const { ensureDataAvailable } = await import('@/lib/onDemandFetch');

    const today = new Date().toISOString().slice(0, 10);
    const result = await ensureDataAvailable({
      league,
      startDate: today,
      endDate: today,
      force: true
    });

    if (result.fetched && result.gamesAdded > 0) {
      // Get the freshly fetched data from database
      const db = getSportsDb();
      const games = await db.sportsGame.findMany({
        where: {
          league: league.toLowerCase(),
          gameDate: {
            gte: new Date(`${today}T00:00:00Z`),
            lte: new Date(`${today}T23:59:59Z`)
          },
          moneylineHome: { not: null }
        },
        orderBy: { gameDate: 'asc' },
        take: 50
      });

      return {
        source: 'sportsgameodds',
        fetchedAt: new Date(),
        games: games.map(g => ({
          homeTeam: g.homeTeam,
          awayTeam: g.awayTeam,
          gameTime: g.gameDate.toISOString(),
          moneylineHome: g.moneylineHome ?? undefined,
          moneylineAway: g.moneylineAway ?? undefined,
          spreadHome: g.spreadHome ?? undefined,
          total: g.total ?? undefined,
          status: g.status ?? 'scheduled'
        }))
      };
    }

    return null;
  } catch (error) {
    console.error('[DirectQuery] Live odds fetch error:', error);
    return null;
  }
}

/**
 * Fetch live scores from ESPN API
 */
export async function fetchLiveScoresFromApi(league: string): Promise<LiveScoresResult | null> {
  try {
    const espnLeagueMap: Record<string, { sport: string; league: string }> = {
      nba: { sport: 'basketball', league: 'nba' },
      nfl: { sport: 'football', league: 'nfl' },
      mlb: { sport: 'baseball', league: 'mlb' },
      nhl: { sport: 'hockey', league: 'nhl' },
      ncaab: { sport: 'basketball', league: 'mens-college-basketball' },
      ncaaf: { sport: 'football', league: 'college-football' }
    };

    const config = espnLeagueMap[league.toLowerCase()];
    if (!config) return null;

    const url = `https://site.api.espn.com/apis/site/v2/sports/${config.sport}/${config.league}/scoreboard`;
    const response = await fetch(url, { next: { revalidate: 60 } });

    if (!response.ok) return null;

    const data = await response.json();
    const events = data?.events || [];

    return {
      source: 'espn',
      fetchedAt: new Date(),
      games: events.map((event: any) => {
        const competition = event?.competitions?.[0] || {};
        const homeTeam = competition?.competitors?.find((c: any) => c.homeAway === 'home');
        const awayTeam = competition?.competitors?.find((c: any) => c.homeAway === 'away');
        const status = event?.status?.type?.description || 'Unknown';

        return {
          homeTeam: homeTeam?.team?.abbreviation || 'UNK',
          awayTeam: awayTeam?.team?.abbreviation || 'UNK',
          homeScore: parseInt(homeTeam?.score) || undefined,
          awayScore: parseInt(awayTeam?.score) || undefined,
          status,
          period: event?.status?.period?.toString(),
          clock: event?.status?.displayClock
        };
      })
    };
  } catch (error) {
    console.error('[DirectQuery] Live scores fetch error:', error);
    return null;
  }
}

/**
 * Smart data fetch - checks staleness and fetches from API if needed
 */
export async function smartFetch(options: {
  query: string;
  league?: string;
  forceRefresh?: boolean;
}): Promise<{ data: any; source: string; fresh: boolean }> {
  const { query, league, forceRefresh = false } = options;
  const queryLower = query.toLowerCase();

  // Detect what type of data is needed
  const needsOdds = queryLower.includes('odds') || queryLower.includes('line') || queryLower.includes('spread');
  const needsScores = queryLower.includes('score') || queryLower.includes('live') || queryLower.includes('result');
  const needsProps = queryLower.includes('prop') || queryLower.includes('player');

  // Check data freshness
  const freshness = await getDataFreshness();
  const oddsAge = freshness.lastOddsUpdate
    ? (Date.now() - freshness.lastOddsUpdate.getTime()) / (1000 * 60 * 60)
    : 999;

  const isStale = oddsAge > 6; // Consider stale after 6 hours

  // If data is stale or forced, fetch from API
  if ((isStale || forceRefresh) && league) {
    if (needsOdds) {
      const liveOdds = await fetchLiveOddsFromApi(league);
      if (liveOdds && liveOdds.games.length > 0) {
        return { data: liveOdds, source: 'api', fresh: true };
      }
    }

    if (needsScores) {
      const liveScores = await fetchLiveScoresFromApi(league);
      if (liveScores && liveScores.games.length > 0) {
        return { data: liveScores, source: 'api', fresh: true };
      }
    }
  }

  // Otherwise return from database
  const db = getSportsDb();

  if (needsOdds && league) {
    const games = await getUpcomingGames({ league, days: 7 });
    return { data: games, source: 'database', fresh: !isStale };
  }

  if (needsScores && league) {
    const games = await getRecentGames({ league, days: 1 });
    return { data: games, source: 'database', fresh: !isStale };
  }

  return { data: null, source: 'none', fresh: false };
}

/**
 * Refresh all data for a league from APIs
 */
export async function refreshLeagueData(league: string): Promise<{
  oddsUpdated: number;
  propsUpdated: number;
  errors: string[];
}> {
  const errors: string[] = [];
  let oddsUpdated = 0;
  let propsUpdated = 0;

  try {
    const { fetchGamesOnDemand } = await import('@/lib/onDemandFetch');

    const today = new Date();
    const endDate = new Date(today);
    endDate.setDate(endDate.getDate() + 7);

    const result = await fetchGamesOnDemand({
      league,
      startDate: today.toISOString().slice(0, 10),
      endDate: endDate.toISOString().slice(0, 10),
      force: true,
      includeProps: true
    });

    oddsUpdated = result.gamesAdded;
    propsUpdated = result.propsAdded;

    if (result.error) {
      errors.push(result.error);
    }
  } catch (e: any) {
    errors.push(e.message || String(e));
  }

  return { oddsUpdated, propsUpdated, errors };
}

/**
 * Format live odds for chat display
 */
export function formatLiveOdds(result: LiveOddsResult): string {
  let response = `**Live Odds** (${result.source.toUpperCase()} - ${result.fetchedAt.toLocaleTimeString()})\n\n`;

  if (result.games.length === 0) {
    return response + 'No games with odds currently available.';
  }

  result.games.slice(0, 10).forEach(g => {
    const time = new Date(g.gameTime).toLocaleTimeString([], { hour: '2-digit', minute: '2-digit' });
    response += `**${g.awayTeam} @ ${g.homeTeam}** (${time})\n`;
    if (g.moneylineHome && g.moneylineAway) {
      response += `  ML: ${g.awayTeam} ${g.moneylineAway > 0 ? '+' : ''}${g.moneylineAway} | ${g.homeTeam} ${g.moneylineHome > 0 ? '+' : ''}${g.moneylineHome}\n`;
    }
    if (g.spreadHome) {
      response += `  Spread: ${g.homeTeam} ${g.spreadHome > 0 ? '+' : ''}${g.spreadHome}\n`;
    }
    if (g.total) {
      response += `  Total: ${g.total}\n`;
    }
    response += '\n';
  });

  return response;
}

/**
 * Format live scores for chat display
 */
export function formatLiveScores(result: LiveScoresResult): string {
  let response = `**Live Scores** (${result.source.toUpperCase()} - ${result.fetchedAt.toLocaleTimeString()})\n\n`;

  if (result.games.length === 0) {
    return response + 'No live games currently.';
  }

  result.games.forEach(g => {
    const score = g.homeScore !== undefined && g.awayScore !== undefined
      ? `${g.awayScore} - ${g.homeScore}`
      : 'vs';

    let statusInfo = g.status;
    if (g.period && g.clock) {
      statusInfo = `${g.period}Q ${g.clock}`;
    }

    response += `**${g.awayTeam} ${score} ${g.homeTeam}** - ${statusInfo}\n`;
  });

  return response;
}

/**
 * Get available API sources
 */
export function getAvailableApis(): Array<{ name: string; status: string; leagues: string[]; description: string }> {
  return [
    {
      name: 'SportsGameOdds',
      status: process.env.SPORTSGAMEODDS_API_KEY ? 'configured' : 'not configured',
      leagues: ['nba', 'nfl', 'mlb', 'nhl', 'ncaab', 'ncaaf', 'epl', 'ufc'],
      description: 'Primary odds source - 80+ bookmakers, live lines'
    },
    {
      name: 'ESPN',
      status: 'available',
      leagues: ['nba', 'nfl', 'mlb', 'nhl', 'ncaab', 'ncaaf'],
      description: 'Live scores, box scores, schedules (free, no key)'
    },
    {
      name: 'BallDontLie',
      status: process.env.BALLDONTLIE_API_KEY ? 'configured' : 'not configured',
      leagues: ['nba', 'nfl', 'mlb', 'nhl'],
      description: 'Player stats, rosters, game logs'
    },
    {
      name: 'The Odds API',
      status: process.env.THE_ODDS_API_KEY ? 'configured' : 'not configured',
      leagues: ['nba', 'nfl', 'mlb', 'nhl', 'epl'],
      description: 'Alternative odds source for cross-validation'
    },
    {
      name: 'OpenWeatherMap',
      status: process.env.OPENWEATHERMAP_API_KEY ? 'configured' : 'not configured',
      leagues: ['nfl', 'mlb', 'ncaaf'],
      description: 'Weather data for outdoor games betting impact'
    },
    {
      name: 'WeatherAPI',
      status: process.env.WEATHERAPI_KEY ? 'configured' : 'not configured',
      leagues: ['nfl', 'mlb', 'ncaaf'],
      description: 'Backup weather source'
    },
    {
      name: 'Football Data',
      status: process.env.FOOTBALL_DATA_API_KEY ? 'configured' : 'not configured',
      leagues: ['epl', 'laliga', 'bundesliga', 'seriea', 'ligue1'],
      description: 'European soccer stats and fixtures'
    },
    {
      name: 'CFBD',
      status: process.env.CFBD_API_KEY ? 'configured' : 'not configured',
      leagues: ['ncaaf'],
      description: 'College football detailed stats'
    },
    {
      name: 'Grok (xAI)',
      status: process.env.GROK_API_KEY ? 'configured' : 'not configured',
      leagues: ['all'],
      description: 'AI analysis and research assistance'
    }
  ];
}

// ==================== ADDITIONAL API INTEGRATIONS ====================

/**
 * Get weather data for outdoor games
 */
export async function getWeatherForGames(options: { league?: string; team?: string }): Promise<any[]> {
  const db = getSportsDb();
  const { league, team } = options;

  const where: any = {
    gameDate: { gte: new Date() },
    isIndoor: false
  };

  if (league) where.league = league.toLowerCase();
  if (team) {
    where.OR = [
      { homeTeam: { contains: team, mode: 'insensitive' } },
      { awayTeam: { contains: team, mode: 'insensitive' } }
    ];
  }

  const weatherData = await db.gameWeather.findMany({
    where,
    orderBy: { gameDate: 'asc' },
    take: 20
  });

  return weatherData.map(w => ({
    game: `${w.awayTeam} @ ${w.homeTeam}`,
    league: w.league,
    gameDate: w.gameDate,
    temperature: w.temperature,
    windSpeed: w.windSpeed,
    windDirection: w.windDirection,
    condition: w.condition,
    precipChance: w.precipChance,
    impact: w.overallImpact,
    bettingAdvice: formatWeatherAdvice(w)
  }));
}

function formatWeatherAdvice(w: any): string {
  const advice: string[] = [];

  if (w.overallImpact === 'SIGNIFICANT') {
    if (w.windImpact === 'HIGH') {
      advice.push('High wind - expect lower passing/kicking production');
    }
    if (w.tempImpact === 'COLD') {
      advice.push('Cold weather - lower scoring likely');
    }
    if (w.precipImpact === 'WET') {
      advice.push('Wet conditions - more turnovers expected');
    }
  }

  return advice.length > 0 ? advice.join('; ') : 'Normal conditions';
}

/**
 * Format weather data for chat
 */
export function formatWeatherResponse(weatherData: any[]): string {
  if (weatherData.length === 0) {
    return 'No outdoor games with weather data found.';
  }

  let response = `**Weather Impact for Upcoming Games:**\n\n`;

  const significant = weatherData.filter(w => w.impact === 'SIGNIFICANT');
  if (significant.length > 0) {
    response += `**High Impact Games:**\n`;
    significant.forEach(w => {
      const date = new Date(w.gameDate).toLocaleDateString();
      response += `- ${w.game} (${date})\n`;
      response += `  ${w.temperature ? Math.round(w.temperature) + '°F' : ''} ${w.windSpeed ? w.windSpeed + ' mph wind' : ''}\n`;
      response += `  💡 ${w.bettingAdvice}\n\n`;
    });
  }

  const normal = weatherData.filter(w => w.impact !== 'SIGNIFICANT').slice(0, 5);
  if (normal.length > 0) {
    response += `**Other Games:**\n`;
    normal.forEach(w => {
      const date = new Date(w.gameDate).toLocaleDateString();
      response += `- ${w.game} (${date}): ${w.condition || 'Clear'}\n`;
    });
  }

  return response;
}

/**
 * Fetch player stats from BallDontLie API
 */
export async function fetchPlayerStatsFromBdl(playerName: string, options?: { season?: number }): Promise<any> {
  try {
    const { BallDontLieService } = await import('@/services/BallDontLieService');
    const bdl = BallDontLieService.getInstance();

    // Search for player
    const players = await bdl.searchPlayers(playerName);
    if (!players || players.length === 0) {
      return null;
    }

    const player = players[0];
    const season = options?.season || new Date().getFullYear();

    // Get season averages
    const stats = await bdl.getPlayerSeasonAverages(player.id, season);

    return {
      player: {
        name: `${player.first_name} ${player.last_name}`,
        team: player.team?.abbreviation || 'N/A',
        position: player.position
      },
      stats,
      source: 'balldontlie'
    };
  } catch (error) {
    console.error('[DirectQuery] BDL stats error:', error);
    return null;
  }
}

/**
 * Comprehensive API query - tries multiple sources
 */
export async function queryAllSources(query: string, options?: { league?: string }): Promise<{
  results: any[];
  sources: string[];
  errors: string[];
}> {
  const results: any[] = [];
  const sources: string[] = [];
  const errors: string[] = [];
  const queryLower = query.toLowerCase();
  const league = options?.league;

  // Try database first (fastest)
  try {
    if (queryLower.includes('odds') || queryLower.includes('line')) {
      const games = league ? await getUpcomingGames({ league, days: 7 }) : [];
      if (games.length > 0) {
        results.push({ type: 'odds', data: games });
        sources.push('database');
      }
    }

    if (queryLower.includes('weather')) {
      const weather = await getWeatherForGames({ league });
      if (weather.length > 0) {
        results.push({ type: 'weather', data: weather });
        sources.push('database');
      }
    }

    if (queryLower.includes('standings')) {
      const standings = league ? await getStandings(league) : [];
      if (standings.length > 0) {
        results.push({ type: 'standings', data: standings });
        sources.push('database');
      }
    }
  } catch (e: any) {
    errors.push(`Database: ${e.message}`);
  }

  // Try live APIs if database is empty or for real-time data
  if (results.length === 0 || queryLower.includes('live') || queryLower.includes('now')) {
    try {
      if (league && (queryLower.includes('score') || queryLower.includes('live'))) {
        const scores = await fetchLiveScoresFromApi(league);
        if (scores && scores.games.length > 0) {
          results.push({ type: 'scores', data: scores });
          sources.push('espn');
        }
      }
    } catch (e: any) {
      errors.push(`ESPN: ${e.message}`);
    }

    try {
      if (league && queryLower.includes('odds')) {
        const odds = await fetchLiveOddsFromApi(league);
        if (odds && odds.games.length > 0) {
          results.push({ type: 'live_odds', data: odds });
          sources.push('sportsgameodds');
        }
      }
    } catch (e: any) {
      errors.push(`SportsGameOdds: ${e.message}`);
    }
  }

  return { results, sources, errors };
}

/**
 * Format API sources status for chat
 */
export function formatApiStatus(): string {
  const apis = getAvailableApis();

  let response = `**Available Data Sources:**\n\n`;

  apis.forEach(api => {
    const emoji = api.status === 'configured' || api.status === 'available' ? '✅' : '⚠️';
    response += `${emoji} **${api.name}**\n`;
    response += `   ${api.description}\n`;
    response += `   Leagues: ${api.leagues.join(', ')}\n\n`;
  });

  const configured = apis.filter(a => a.status === 'configured' || a.status === 'available').length;
  response += `\n*${configured}/${apis.length} sources active*`;

  return response;
}

// ==================== COMPREHENSIVE MULTI-SOURCE DATA ====================

/**
 * Fetch historical game with score from ESPN
 */
export async function fetchGameFromEspn(options: {
  league: string;
  homeTeam?: string;
  awayTeam?: string;
  date?: string;
}): Promise<any> {
  const { league, homeTeam, awayTeam, date } = options;

  const espnLeagueMap: Record<string, { sport: string; league: string }> = {
    nba: { sport: 'basketball', league: 'nba' },
    nfl: { sport: 'football', league: 'nfl' },
    mlb: { sport: 'baseball', league: 'mlb' },
    nhl: { sport: 'hockey', league: 'nhl' },
    ncaab: { sport: 'basketball', league: 'mens-college-basketball' },
    ncaaf: { sport: 'football', league: 'college-football' }
  };

  const config = espnLeagueMap[league.toLowerCase()];
  if (!config) return null;

  try {
    const dateStr = date ? date.replace(/-/g, '') : new Date().toISOString().slice(0, 10).replace(/-/g, '');
    const url = `https://site.api.espn.com/apis/site/v2/sports/${config.sport}/${config.league}/scoreboard?dates=${dateStr}`;

    const response = await fetch(url);
    if (!response.ok) return null;

    const data = await response.json();
    const events = data?.events || [];

    // Find matching game
    for (const event of events) {
      const competition = event?.competitions?.[0] || {};
      const home = competition?.competitors?.find((c: any) => c.homeAway === 'home');
      const away = competition?.competitors?.find((c: any) => c.homeAway === 'away');

      const homeAbbr = home?.team?.abbreviation?.toUpperCase() || '';
      const awayAbbr = away?.team?.abbreviation?.toUpperCase() || '';

      if (homeTeam && awayTeam) {
        if (homeAbbr.includes(homeTeam.toUpperCase()) || awayAbbr.includes(awayTeam.toUpperCase())) {
          return {
            homeTeam: home?.team?.displayName,
            awayTeam: away?.team?.displayName,
            homeScore: parseInt(home?.score),
            awayScore: parseInt(away?.score),
            status: event?.status?.type?.description,
            date: event?.date,
            venue: competition?.venue?.fullName,
            source: 'espn'
          };
        }
      }
    }

    // Return all games if no specific match requested
    if (!homeTeam && !awayTeam) {
      return events.map((event: any) => {
        const competition = event?.competitions?.[0] || {};
        const home = competition?.competitors?.find((c: any) => c.homeAway === 'home');
        const away = competition?.competitors?.find((c: any) => c.homeAway === 'away');
        return {
          homeTeam: home?.team?.displayName,
          awayTeam: away?.team?.displayName,
          homeScore: parseInt(home?.score) || 0,
          awayScore: parseInt(away?.score) || 0,
          status: event?.status?.type?.description,
          source: 'espn'
        };
      });
    }

    return null;
  } catch (e) {
    console.error('[DirectQuery] ESPN fetch error:', e);
    return null;
  }
}

/**
 * Fetch player box score from ESPN
 */
export async function fetchBoxScoreFromEspn(options: {
  league: string;
  gameId?: string;
  date?: string;
  homeTeam?: string;
  awayTeam?: string;
}): Promise<any> {
  const { league, gameId, date, homeTeam, awayTeam } = options;

  const espnLeagueMap: Record<string, { sport: string; league: string }> = {
    nba: { sport: 'basketball', league: 'nba' },
    nfl: { sport: 'football', league: 'nfl' },
    mlb: { sport: 'baseball', league: 'mlb' },
    nhl: { sport: 'hockey', league: 'nhl' }
  };

  const config = espnLeagueMap[league.toLowerCase()];
  if (!config) return null;

  try {
    let eventId = gameId;

    // If no gameId, find it from scoreboard
    if (!eventId && date) {
      const dateStr = date.replace(/-/g, '');
      const scoreboardUrl = `https://site.api.espn.com/apis/site/v2/sports/${config.sport}/${config.league}/scoreboard?dates=${dateStr}`;
      const sbResponse = await fetch(scoreboardUrl);
      const sbData = await sbResponse.json();

      for (const event of sbData?.events || []) {
        const competition = event?.competitions?.[0];
        const home = competition?.competitors?.find((c: any) => c.homeAway === 'home');
        const away = competition?.competitors?.find((c: any) => c.homeAway === 'away');

        if (homeTeam && away) {
          const homeAbbr = home?.team?.abbreviation?.toUpperCase();
          const awayAbbr = away?.team?.abbreviation?.toUpperCase();
          if (homeAbbr?.includes(homeTeam.toUpperCase()) || awayAbbr?.includes(awayTeam?.toUpperCase() || '')) {
            eventId = event.id;
            break;
          }
        }
      }
    }

    if (!eventId) return null;

    const url = `https://site.api.espn.com/apis/site/v2/sports/${config.sport}/${config.league}/summary?event=${eventId}`;
    const response = await fetch(url);
    if (!response.ok) return null;

    const data = await response.json();
    const boxscore = data?.boxscore;

    if (!boxscore) return null;

    // Extract player stats
    const playerStats: any[] = [];
    const teams = boxscore?.players || [];

    for (const team of teams) {
      const teamAbbr = team?.team?.abbreviation;
      const categories = team?.statistics || [];

      for (const category of categories) {
        const athletes = category?.athletes || [];
        const labels = category?.labels || [];

        for (const athlete of athletes) {
          const stats: Record<string, number> = {};
          const values = athlete?.stats || [];

          for (let i = 0; i < labels.length; i++) {
            const label = String(labels[i]).toLowerCase();
            const val = parseFloat(values[i]);
            if (!isNaN(val)) stats[label] = val;
          }

          if (Object.keys(stats).length > 0) {
            playerStats.push({
              name: athlete?.athlete?.displayName,
              team: teamAbbr,
              position: athlete?.athlete?.position?.abbreviation,
              stats,
              source: 'espn'
            });
          }
        }
      }
    }

    return {
      players: playerStats,
      gameInfo: {
        homeTeam: data?.header?.competitions?.[0]?.competitors?.find((c: any) => c.homeAway === 'home')?.team?.displayName,
        awayTeam: data?.header?.competitions?.[0]?.competitors?.find((c: any) => c.homeAway === 'away')?.team?.displayName,
        date: data?.header?.competitions?.[0]?.date
      },
      source: 'espn'
    };
  } catch (e) {
    console.error('[DirectQuery] ESPN boxscore error:', e);
    return null;
  }
}

/**
 * Fetch live odds from SportsGameOdds API
 */
export async function fetchOddsFromSgo(league: string): Promise<any[]> {
  try {
    const { SportsGameOddsClient } = await import('@/lib/sportsgameodds');

    const client = new SportsGameOddsClient();
    const leagueKey = league.toLowerCase() as any;

    const events = await client.getEvents({ league: leagueKey, oddsAvailable: true });

    return events.map(e => ({
      homeTeam: e.homeTeam,
      awayTeam: e.awayTeam,
      gameDate: e.gameDate,
      moneylineHome: e.odds.moneylineHome,
      moneylineAway: e.odds.moneylineAway,
      spreadHome: e.odds.spreadHome,
      total: e.odds.total,
      source: 'sportsgameodds'
    }));
  } catch (e) {
    console.error('[DirectQuery] SGO fetch error:', e);
    return [];
  }
}

/**
 * Fetch player props from SportsGameOdds API
 */
export async function fetchPropsFromSgo(league: string): Promise<any[]> {
  try {
    const { SportsGameOddsClient } = await import('@/lib/sportsgameodds');

    const client = new SportsGameOddsClient();
    const leagueKey = league.toLowerCase() as any;

    const events = await client.getPlayerProps(leagueKey);

    const allProps: any[] = [];
    for (const event of events) {
      for (const prop of event.props) {
        allProps.push({
          game: `${event.awayTeam} @ ${event.homeTeam}`,
          gameDate: event.gameDate,
          player: prop.playerName,
          market: prop.market,
          line: prop.line,
          overOdds: prop.overOdds,
          source: 'sportsgameodds'
        });
      }
    }

    return allProps;
  } catch (e) {
    console.error('[DirectQuery] SGO props error:', e);
    return [];
  }
}

/**
 * Smart comprehensive query - uses database + live APIs as needed
 */
export async function comprehensiveQuery(options: {
  type: 'game' | 'player' | 'odds' | 'props' | 'boxscore';
  league: string;
  team?: string;
  player?: string;
  date?: string;
  forceApi?: boolean;
}): Promise<{ data: any; sources: string[]; fresh: boolean }> {
  const { type, league, team, player, date, forceApi } = options;
  const sources: string[] = [];
  let data: any = null;

  // Try database first unless forceApi
  if (!forceApi) {
    const db = getSportsDb();

    if (type === 'game' && team && date) {
      const game = await db.sportsGame.findFirst({
        where: {
          league: league.toLowerCase(),
          gameDate: { gte: new Date(`${date}T00:00:00Z`), lte: new Date(`${date}T23:59:59Z`) },
          OR: [
            { homeTeam: { contains: team, mode: 'insensitive' } },
            { awayTeam: { contains: team, mode: 'insensitive' } }
          ]
        }
      });
      if (game) {
        sources.push('database');
        data = game;
      }
    }

    if (type === 'player' && player) {
      const stats = await getPlayerStats(player, { days: 30 });
      if (stats.length > 0) {
        sources.push('database');
        data = stats;
      }
    }

    if (type === 'odds' && team) {
      const games = await getTeamSchedule(team, { upcoming: true });
      const withOdds = games.filter(g => g.moneylineHome);
      if (withOdds.length > 0) {
        sources.push('database');
        data = withOdds;
      }
    }
  }

  // If no data from database or forceApi, try live APIs
  if (!data || forceApi) {
    if (type === 'game' || type === 'boxscore') {
      const espnData = await fetchGameFromEspn({ league, homeTeam: team, date });
      if (espnData) {
        sources.push('espn');
        data = espnData;
      }
    }

    if (type === 'odds') {
      const sgoData = await fetchOddsFromSgo(league);
      if (sgoData.length > 0) {
        sources.push('sportsgameodds');
        data = team ? sgoData.filter(g =>
          g.homeTeam?.toLowerCase().includes(team.toLowerCase()) ||
          g.awayTeam?.toLowerCase().includes(team.toLowerCase())
        ) : sgoData;
      }
    }

    if (type === 'props') {
      const sgoProps = await fetchPropsFromSgo(league);
      if (sgoProps.length > 0) {
        sources.push('sportsgameodds');
        data = player ? sgoProps.filter(p =>
          p.player?.toLowerCase().includes(player.toLowerCase())
        ) : sgoProps;
      }
    }

    if (type === 'boxscore' && date) {
      const boxscore = await fetchBoxScoreFromEspn({ league, date, homeTeam: team });
      if (boxscore) {
        sources.push('espn');
        data = boxscore;
      }
    }
  }

  return {
    data,
    sources,
    fresh: sources.includes('espn') || sources.includes('sportsgameodds')
  };
}

/**
 * Format comprehensive query result for chat
 */
export function formatComprehensiveResult(result: { data: any; sources: string[]; fresh: boolean }, queryType: string): string {
  if (!result.data) {
    return `No data found. Sources checked: ${result.sources.join(', ') || 'database'}`;
  }

  let response = '';

  if (queryType === 'game' && !Array.isArray(result.data)) {
    const g = result.data;
    response = `**${g.awayTeam} @ ${g.homeTeam}**\n`;
    if (g.homeScore !== undefined) {
      response += `Score: ${g.awayScore} - ${g.homeScore}\n`;
    }
    if (g.status) response += `Status: ${g.status}\n`;
    if (g.moneylineHome) {
      response += `ML: ${g.awayTeam} ${g.moneylineAway} | ${g.homeTeam} ${g.moneylineHome}\n`;
    }
  } else if (queryType === 'boxscore' && result.data.players) {
    response = `**Box Score: ${result.data.gameInfo?.awayTeam} @ ${result.data.gameInfo?.homeTeam}**\n\n`;
    const topPlayers = result.data.players.slice(0, 10);
    for (const p of topPlayers) {
      const pts = p.stats?.pts || p.stats?.points || 0;
      const reb = p.stats?.reb || p.stats?.rebounds || 0;
      const ast = p.stats?.ast || p.stats?.assists || 0;
      response += `**${p.name}** (${p.team}): ${pts}pts ${reb}reb ${ast}ast\n`;
    }
  } else if (Array.isArray(result.data)) {
    response = `Found ${result.data.length} results:\n\n`;
    result.data.slice(0, 5).forEach((item: any) => {
      response += `- ${JSON.stringify(item).slice(0, 100)}...\n`;
    });
  }

  response += `\n*Sources: ${result.sources.join(', ')} | Fresh: ${result.fresh ? 'Yes' : 'Cached'}*`;
  return response;
}