/**
 * Canonical Entity Resolver
 *
 * Provides lookup functions to resolve entities (teams, players, games)
 * to their canonical IDs, handling the various external ID formats
 * from different data sources.
 *
 * This is the normalization layer that all ingestion scripts should use.
 */

import { getSportsDb, isSportsDbEnabled } from './sportsDb';
import { normalizeTeam } from './teamNormalization';

// ============================================================
// Types
// ============================================================

export interface ResolvedTeam {
  id: bigint;
  abbr: string;
  fullName: string;
  league: string;
}

export interface ResolvedPlayer {
  id: bigint;
  fullName: string;
  normalizedName: string;
  league: string;
  teamId: bigint | null;
}

export interface ResolvedGame {
  id: bigint;
  league: string;
  season: number;
  gameDate: Date;
  homeTeamId: bigint;
  awayTeamId: bigint;
  existing: boolean; // true if game already existed, false if newly created
}

export interface TeamLookupInput {
  name?: string;
  abbr?: string;
  externalId?: string;
  source?: string;
}

export interface PlayerLookupInput {
  name?: string;
  externalId?: string;
  source?: string;
  team?: string;
}

export interface GameLookupInput {
  date: Date | string;
  home: string; // Team name/abbr
  away: string; // Team name/abbr
  externalId?: string;
  source?: string;
  season?: number;
}

// ============================================================
// Name Normalization Utilities
// ============================================================

/**
 * Normalize a player name for matching
 * - Lowercase
 * - Remove accents/diacritics
 * - Remove suffixes (Jr., Sr., III, etc.)
 * - Standardize spacing
 */
export function normalizePlayerName(name: string): string {
  if (!name) return '';

  return name
    .toLowerCase()
    // Remove accents/diacritics
    .normalize('NFD')
    .replace(/[\u0300-\u036f]/g, '')
    // Remove common suffixes
    .replace(/\s+(jr\.?|sr\.?|iii|ii|iv|v)$/i, '')
    // Remove periods and extra punctuation
    .replace(/\./g, '')
    // Standardize whitespace
    .replace(/\s+/g, ' ')
    .trim();
}

/**
 * Parse SGO-style player ID to extract name
 * e.g., "LEBRON_JAMES_1_NBA" -> "lebron james"
 */
export function parseSgoPlayerId(sgoId: string): { name: string; league: string } | null {
  if (!sgoId || typeof sgoId !== 'string') return null;

  // Check for league suffix
  const leagueMatch = sgoId.match(/_(NBA|NFL|NHL|MLB|WNBA|NCAAB|NCAAF|MMA|UFC)$/i);
  if (!leagueMatch) return null;

  const league = leagueMatch[1].toLowerCase();
  const withoutLeague = sgoId.slice(0, -leagueMatch[0].length);

  // Check for number suffix (player IDs have this)
  const parts = withoutLeague.split('_');
  const lastPart = parts[parts.length - 1];

  if (!/^\d+$/.test(lastPart)) {
    // No number suffix - might be a team ID
    return null;
  }

  // Remove the number and join the name parts
  const nameParts = parts.slice(0, -1);
  const name = nameParts.join(' ').toLowerCase();

  return { name, league };
}

/**
 * Parse SGO-style team ID to extract name
 * e.g., "CHICAGO_BULLS_NBA" -> { name: "chicago bulls", league: "nba" }
 */
export function parseSgoTeamId(sgoId: string): { name: string; league: string } | null {
  if (!sgoId || typeof sgoId !== 'string') return null;

  const leagueMatch = sgoId.match(/_(NBA|NFL|NHL|MLB|WNBA|NCAAB|NCAAF)$/i);
  if (!leagueMatch) return null;

  const league = leagueMatch[1].toLowerCase();
  const name = sgoId.slice(0, -leagueMatch[0].length).replace(/_/g, ' ').toLowerCase();

  return { name, league };
}

// ============================================================
// Team Resolution
// ============================================================

/**
 * Resolve a team to its canonical record
 *
 * @param league - League code (nba, nfl, etc.)
 * @param input - Team identifier (name, abbreviation, or external ID)
 * @returns Resolved team or null if not found
 */
export async function resolveCanonicalTeam(
  league: string,
  input: string | TeamLookupInput
): Promise<ResolvedTeam | null> {
  if (!isSportsDbEnabled()) return null;

  const prisma = getSportsDb();
  const leagueNorm = league.toLowerCase();

  // Normalize input
  const lookup: TeamLookupInput =
    typeof input === 'string' ? { name: input, abbr: input, externalId: input } : input;

  // Try normalized abbreviation first
  const abbr = normalizeTeam(lookup.abbr || lookup.name || '', leagueNorm);
  if (abbr) {
    const byAbbr = await prisma.canonicalTeam.findUnique({
      where: { league_abbr: { league: leagueNorm, abbr } },
    });
    if (byAbbr) {
      return {
        id: byAbbr.id,
        abbr: byAbbr.abbr,
        fullName: byAbbr.fullName,
        league: byAbbr.league,
      };
    }
  }

  // Try by SGO ID
  if (lookup.externalId && lookup.source === 'sgo') {
    const bySgoId = await prisma.canonicalTeam.findFirst({
      where: { league: leagueNorm, sgoId: lookup.externalId },
    });
    if (bySgoId) {
      return {
        id: bySgoId.id,
        abbr: bySgoId.abbr,
        fullName: bySgoId.fullName,
        league: bySgoId.league,
      };
    }
  }

  // Try via TeamAlias lookup
  const aliasInput = lookup.externalId || lookup.name || lookup.abbr;
  if (aliasInput) {
    const alias = await prisma.teamAlias.findFirst({
      where: {
        alias: { equals: aliasInput, mode: 'insensitive' },
        team: { league: leagueNorm },
      },
      include: { team: true },
    });
    if (alias?.team) {
      return {
        id: alias.team.id,
        abbr: alias.team.abbr,
        fullName: alias.team.fullName,
        league: alias.team.league,
      };
    }
  }

  return null;
}

/**
 * Resolve or create a canonical team
 * Creates the team if it doesn't exist
 */
export async function resolveOrCreateCanonicalTeam(
  league: string,
  input: TeamLookupInput & { fullName: string }
): Promise<ResolvedTeam> {
  // Try to resolve existing
  const existing = await resolveCanonicalTeam(league, input);
  if (existing) return existing;

  // Create new team
  const prisma = getSportsDb();
  const leagueNorm = league.toLowerCase();
  const abbr = normalizeTeam(input.abbr || input.name || '', leagueNorm) || input.abbr || 'UNK';

  const created = await prisma.canonicalTeam.create({
    data: {
      league: leagueNorm,
      abbr,
      fullName: input.fullName,
      city: input.fullName.replace(/\s+\w+$/, ''), // Extract city (rough heuristic)
      sgoId: input.source === 'sgo' ? input.externalId : null,
    },
  });

  // Create alias for the external ID if provided
  if (input.externalId && input.source) {
    await prisma.teamAlias.create({
      data: {
        teamId: created.id,
        source: input.source,
        alias: input.externalId,
        aliasType: 'external_id',
      },
    }).catch(() => {}); // Ignore if alias already exists
  }

  return {
    id: created.id,
    abbr: created.abbr,
    fullName: created.fullName,
    league: created.league,
  };
}

// ============================================================
// Player Resolution
// ============================================================

/**
 * Resolve a player to their canonical record
 *
 * @param league - League code
 * @param input - Player identifier (name or external ID with source)
 * @returns Resolved player or null if not found
 */
export async function resolveCanonicalPlayer(
  league: string,
  input: string | PlayerLookupInput
): Promise<ResolvedPlayer | null> {
  if (!isSportsDbEnabled()) return null;

  const prisma = getSportsDb();
  const leagueNorm = league.toLowerCase();

  // Normalize input
  const lookup: PlayerLookupInput =
    typeof input === 'string' ? { name: input, externalId: input } : input;

  // Try by normalized name first
  if (lookup.name) {
    const normalized = normalizePlayerName(lookup.name);
    const byName = await prisma.canonicalPlayer.findUnique({
      where: { league_normalizedName: { league: leagueNorm, normalizedName: normalized } },
    });
    if (byName) {
      return {
        id: byName.id,
        fullName: byName.fullName,
        normalizedName: byName.normalizedName,
        league: byName.league,
        teamId: byName.teamId,
      };
    }
  }

  // Try by SGO ID
  if (lookup.externalId) {
    // Check if it's an SGO-format ID
    const parsed = parseSgoPlayerId(lookup.externalId);
    if (parsed) {
      const bySgoId = await prisma.canonicalPlayer.findFirst({
        where: { league: leagueNorm, sgoId: lookup.externalId },
      });
      if (bySgoId) {
        return {
          id: bySgoId.id,
          fullName: bySgoId.fullName,
          normalizedName: bySgoId.normalizedName,
          league: bySgoId.league,
          teamId: bySgoId.teamId,
        };
      }

      // Try by parsed name
      const byParsedName = await prisma.canonicalPlayer.findUnique({
        where: { league_normalizedName: { league: leagueNorm, normalizedName: parsed.name } },
      });
      if (byParsedName) {
        return {
          id: byParsedName.id,
          fullName: byParsedName.fullName,
          normalizedName: byParsedName.normalizedName,
          league: byParsedName.league,
          teamId: byParsedName.teamId,
        };
      }
    }
  }

  // Try by BDL ID
  if (lookup.externalId && lookup.source === 'bdl') {
    const byBdlId = await prisma.canonicalPlayer.findFirst({
      where: { league: leagueNorm, bdlId: String(lookup.externalId) },
    });
    if (byBdlId) {
      return {
        id: byBdlId.id,
        fullName: byBdlId.fullName,
        normalizedName: byBdlId.normalizedName,
        league: byBdlId.league,
        teamId: byBdlId.teamId,
      };
    }
  }

  // Try by ESPN ID
  if (lookup.externalId && lookup.source === 'espn') {
    const byEspnId = await prisma.canonicalPlayer.findFirst({
      where: { league: leagueNorm, espnId: String(lookup.externalId) },
    });
    if (byEspnId) {
      return {
        id: byEspnId.id,
        fullName: byEspnId.fullName,
        normalizedName: byEspnId.normalizedName,
        league: byEspnId.league,
        teamId: byEspnId.teamId,
      };
    }
  }

  // Try via PlayerAlias lookup
  const aliasInput = lookup.externalId || lookup.name;
  if (aliasInput) {
    const alias = await prisma.playerAlias.findFirst({
      where: {
        alias: { equals: aliasInput, mode: 'insensitive' },
        player: { league: leagueNorm },
      },
      include: { player: true },
    });
    if (alias?.player) {
      return {
        id: alias.player.id,
        fullName: alias.player.fullName,
        normalizedName: alias.player.normalizedName,
        league: alias.player.league,
        teamId: alias.player.teamId,
      };
    }
  }

  return null;
}

/**
 * Resolve or create a canonical player
 * Creates the player if they don't exist
 */
export async function resolveOrCreateCanonicalPlayer(
  league: string,
  input: PlayerLookupInput & { name: string }
): Promise<ResolvedPlayer> {
  // Try to resolve existing
  const existing = await resolveCanonicalPlayer(league, input);
  if (existing) return existing;

  // Resolve team if provided
  let teamId: bigint | null = null;
  if (input.team) {
    const team = await resolveCanonicalTeam(league, input.team);
    teamId = team?.id ?? null;
  }

  // Create new player
  const prisma = getSportsDb();
  const leagueNorm = league.toLowerCase();
  const normalizedName = normalizePlayerName(input.name);

  // Parse SGO ID to extract external ID mapping
  let sgoId: string | null = null;
  let bdlId: string | null = null;
  let espnId: string | null = null;

  if (input.externalId && input.source === 'sgo') {
    sgoId = input.externalId;
  } else if (input.externalId && input.source === 'bdl') {
    bdlId = input.externalId;
  } else if (input.externalId && input.source === 'espn') {
    espnId = input.externalId;
  }

  const created = await prisma.canonicalPlayer.create({
    data: {
      league: leagueNorm,
      fullName: input.name,
      normalizedName,
      teamId,
      sgoId,
      bdlId,
      espnId,
    },
  });

  // Create alias for the external ID if provided
  if (input.externalId && input.source) {
    await prisma.playerAlias.create({
      data: {
        playerId: created.id,
        source: input.source,
        alias: input.externalId,
        aliasType: 'external_id',
      },
    }).catch(() => {}); // Ignore if alias already exists
  }

  return {
    id: created.id,
    fullName: created.fullName,
    normalizedName: created.normalizedName,
    league: created.league,
    teamId: created.teamId,
  };
}

// ============================================================
// Game Resolution
// ============================================================

/**
 * Resolve a game to its canonical record
 *
 * @param league - League code
 * @param input - Game identifier (date + teams, or external ID)
 * @returns Resolved game or null if not found
 */
export async function resolveCanonicalGame(
  league: string,
  input: GameLookupInput
): Promise<ResolvedGame | null> {
  if (!isSportsDbEnabled()) return null;

  const prisma = getSportsDb();
  const leagueNorm = league.toLowerCase();

  // Parse date
  const gameDate = typeof input.date === 'string' ? new Date(input.date) : input.date;
  const dateOnly = new Date(gameDate.toISOString().slice(0, 10));

  // Resolve teams
  const homeTeam = await resolveCanonicalTeam(leagueNorm, input.home);
  const awayTeam = await resolveCanonicalTeam(leagueNorm, input.away);

  if (!homeTeam || !awayTeam) {
    return null;
  }

  // Calculate season
  const season = input.season ?? calculateSeason(leagueNorm, gameDate);

  // Try by external ID first if provided
  if (input.externalId && input.source === 'sgo') {
    const bySgoId = await prisma.canonicalGame.findFirst({
      where: { league: leagueNorm, sgoEventId: input.externalId },
    });
    if (bySgoId) {
      return {
        id: bySgoId.id,
        league: bySgoId.league,
        season: bySgoId.season,
        gameDate: bySgoId.gameDate,
        homeTeamId: bySgoId.homeTeamId,
        awayTeamId: bySgoId.awayTeamId,
        existing: true,
      };
    }
  }

  // Try by date and teams
  const byTeams = await prisma.canonicalGame.findUnique({
    where: {
      league_season_gameDate_homeTeamId_awayTeamId: {
        league: leagueNorm,
        season,
        gameDate: dateOnly,
        homeTeamId: homeTeam.id,
        awayTeamId: awayTeam.id,
      },
    },
  });

  if (byTeams) {
    return {
      id: byTeams.id,
      league: byTeams.league,
      season: byTeams.season,
      gameDate: byTeams.gameDate,
      homeTeamId: byTeams.homeTeamId,
      awayTeamId: byTeams.awayTeamId,
      existing: true,
    };
  }

  return null;
}

/**
 * Resolve or create a canonical game
 * Creates the game if it doesn't exist
 */
export async function resolveOrCreateCanonicalGame(
  league: string,
  input: GameLookupInput
): Promise<ResolvedGame> {
  // Try to resolve existing
  const existing = await resolveCanonicalGame(league, input);
  if (existing) return existing;

  const prisma = getSportsDb();
  const leagueNorm = league.toLowerCase();

  // Parse date
  const gameDate = typeof input.date === 'string' ? new Date(input.date) : input.date;
  const dateOnly = new Date(gameDate.toISOString().slice(0, 10));

  // Resolve or create teams
  const homeTeam = await resolveOrCreateCanonicalTeam(leagueNorm, {
    name: input.home,
    abbr: input.home,
    fullName: input.home,
  });
  const awayTeam = await resolveOrCreateCanonicalTeam(leagueNorm, {
    name: input.away,
    abbr: input.away,
    fullName: input.away,
  });

  // Calculate season
  const season = input.season ?? calculateSeason(leagueNorm, gameDate);

  // Set external IDs
  let sgoEventId: string | null = null;
  if (input.externalId && input.source === 'sgo') {
    sgoEventId = input.externalId;
  }

  const created = await prisma.canonicalGame.create({
    data: {
      league: leagueNorm,
      season,
      gameDate: dateOnly,
      homeTeamId: homeTeam.id,
      awayTeamId: awayTeam.id,
      sgoEventId,
    },
  });

  return {
    id: created.id,
    league: created.league,
    season: created.season,
    gameDate: created.gameDate,
    homeTeamId: created.homeTeamId,
    awayTeamId: created.awayTeamId,
    existing: false,
  };
}

// ============================================================
// Batch Operations
// ============================================================

/**
 * Batch resolve multiple players at once (more efficient than individual calls)
 */
export async function batchResolveCanonicalPlayers(
  league: string,
  inputs: PlayerLookupInput[]
): Promise<Map<string, ResolvedPlayer | null>> {
  const results = new Map<string, ResolvedPlayer | null>();

  for (const input of inputs) {
    const key = input.externalId || input.name || '';
    const resolved = await resolveCanonicalPlayer(league, input);
    results.set(key, resolved);
  }

  return results;
}

/**
 * Backfill canonical IDs in PlayerGameMetric records
 */
export async function backfillCanonicalIds(options: {
  league: string;
  batchSize?: number;
  limit?: number;
}): Promise<{ updated: number; failed: number }> {
  if (!isSportsDbEnabled()) return { updated: 0, failed: 0 };

  const prisma = getSportsDb();
  const { league, batchSize = 100, limit = 10000 } = options;

  let updated = 0;
  let failed = 0;

  // Find records without canonical IDs
  const records = await prisma.playerGameMetric.findMany({
    where: {
      league: league.toLowerCase(),
      canonicalPlayerId: null,
    },
    select: { id: true, playerExternalId: true, playerName: true },
    take: limit,
  });

  // Process in batches
  for (let i = 0; i < records.length; i += batchSize) {
    const batch = records.slice(i, i + batchSize);

    for (const record of batch) {
      const player = await resolveCanonicalPlayer(league, {
        name: record.playerName || undefined,
        externalId: record.playerExternalId,
      });

      if (player) {
        await prisma.playerGameMetric.update({
          where: { id: record.id },
          data: { canonicalPlayerId: player.id },
        });
        updated++;
      } else {
        failed++;
      }
    }
  }

  return { updated, failed };
}

// ============================================================
// Utility Functions
// ============================================================

/**
 * Calculate the season year for a given date and league
 * NBA/NHL: Season spans Oct-Jun, labeled by start year (2024 for Oct 2024 - Jun 2025)
 * NFL: Season spans Sep-Feb, labeled by start year
 * MLB: Season spans Apr-Oct, labeled by calendar year
 */
function calculateSeason(league: string, date: Date): number {
  const year = date.getFullYear();
  const month = date.getMonth() + 1; // 1-indexed

  switch (league.toLowerCase()) {
    case 'nba':
    case 'wnba':
    case 'nhl':
    case 'ncaab':
      // Oct-Jun season, labeled by start year
      return month >= 10 ? year : year - 1;

    case 'nfl':
    case 'ncaaf':
      // Sep-Feb season, labeled by start year
      return month >= 9 ? year : year - 1;

    case 'mlb':
      // Apr-Oct, labeled by calendar year
      return year;

    default:
      // Default to calendar year
      return year;
  }
}

/**
 * Get opponent team given player's team and game teams
 */
export async function getOpponentTeam(
  league: string,
  playerTeamAbbr: string,
  homeTeamAbbr: string,
  awayTeamAbbr: string
): Promise<ResolvedTeam | null> {
  const normalizedPlayer = normalizeTeam(playerTeamAbbr, league);
  const normalizedHome = normalizeTeam(homeTeamAbbr, league);
  const normalizedAway = normalizeTeam(awayTeamAbbr, league);

  if (normalizedPlayer === normalizedHome) {
    return resolveCanonicalTeam(league, normalizedAway || awayTeamAbbr);
  } else if (normalizedPlayer === normalizedAway) {
    return resolveCanonicalTeam(league, normalizedHome || homeTeamAbbr);
  }

  return null;
}