#!/usr/bin/env npx ts-node /** * Migration Script: Populate Canonical Entity Tables * * This script migrates existing data to the new canonical entity tables: * 1. CanonicalTeam + TeamAlias - from SportsGame teams + teamNormalization mappings * 2. CanonicalPlayer + PlayerAlias - from Player table + PlayerMapping * 3. CanonicalGame - deduplicated from SportsGame * 4. Backfill canonical IDs in PlayerGameMetric, PlayerPropLine * * Usage: * npx ts-node scripts/migrate-to-canonical.ts --phase=teams * npx ts-node scripts/migrate-to-canonical.ts --phase=players * npx ts-node scripts/migrate-to-canonical.ts --phase=games * npx ts-node scripts/migrate-to-canonical.ts --phase=backfill * npx ts-node scripts/migrate-to-canonical.ts --phase=all * npx ts-node scripts/migrate-to-canonical.ts --league=nba --phase=all */ import { PrismaClient } from '../prisma_sports/generated/sports-client'; const prisma = new PrismaClient(); // ============================================================ // Team Data // ============================================================ // Full team data for all major leagues const TEAM_DATA: Record> = { nba: [ { abbr: 'ATL', fullName: 'Atlanta Hawks', city: 'Atlanta', nickname: 'Hawks', conference: 'Eastern', division: 'Southeast', aliases: ['atlanta_hawks_nba', 'hawks'] }, { abbr: 'BOS', fullName: 'Boston Celtics', city: 'Boston', nickname: 'Celtics', conference: 'Eastern', division: 'Atlantic', aliases: ['boston_celtics_nba', 'celtics'] }, { abbr: 'BKN', fullName: 'Brooklyn Nets', city: 'Brooklyn', nickname: 'Nets', conference: 'Eastern', division: 'Atlantic', aliases: ['brooklyn_nets_nba', 'nets', 'bk', 'brk'] }, { abbr: 'CHA', fullName: 'Charlotte Hornets', city: 'Charlotte', nickname: 'Hornets', conference: 'Eastern', division: 'Southeast', aliases: ['charlotte_hornets_nba', 'hornets', 'cho'] }, { abbr: 'CHI', fullName: 'Chicago Bulls', city: 'Chicago', nickname: 'Bulls', conference: 'Eastern', division: 'Central', aliases: ['chicago_bulls_nba', 'bulls'] }, { abbr: 'CLE', fullName: 'Cleveland Cavaliers', city: 'Cleveland', nickname: 'Cavaliers', conference: 'Eastern', division: 'Central', aliases: ['cleveland_cavaliers_nba', 'cavaliers', 'cavs'] }, { abbr: 'DAL', fullName: 'Dallas Mavericks', city: 'Dallas', nickname: 'Mavericks', conference: 'Western', division: 'Southwest', aliases: ['dallas_mavericks_nba', 'mavericks', 'mavs'] }, { abbr: 'DEN', fullName: 'Denver Nuggets', city: 'Denver', nickname: 'Nuggets', conference: 'Western', division: 'Northwest', aliases: ['denver_nuggets_nba', 'nuggets'] }, { abbr: 'DET', fullName: 'Detroit Pistons', city: 'Detroit', nickname: 'Pistons', conference: 'Eastern', division: 'Central', aliases: ['detroit_pistons_nba', 'pistons'] }, { abbr: 'GSW', fullName: 'Golden State Warriors', city: 'Golden State', nickname: 'Warriors', conference: 'Western', division: 'Pacific', aliases: ['golden_state_warriors_nba', 'warriors', 'gs'] }, { abbr: 'HOU', fullName: 'Houston Rockets', city: 'Houston', nickname: 'Rockets', conference: 'Western', division: 'Southwest', aliases: ['houston_rockets_nba', 'rockets'] }, { abbr: 'IND', fullName: 'Indiana Pacers', city: 'Indiana', nickname: 'Pacers', conference: 'Eastern', division: 'Central', aliases: ['indiana_pacers_nba', 'pacers'] }, { abbr: 'LAC', fullName: 'Los Angeles Clippers', city: 'Los Angeles', nickname: 'Clippers', conference: 'Western', division: 'Pacific', aliases: ['los_angeles_clippers_nba', 'clippers', 'la clippers'] }, { abbr: 'LAL', fullName: 'Los Angeles Lakers', city: 'Los Angeles', nickname: 'Lakers', conference: 'Western', division: 'Pacific', aliases: ['los_angeles_lakers_nba', 'lakers', 'la lakers'] }, { abbr: 'MEM', fullName: 'Memphis Grizzlies', city: 'Memphis', nickname: 'Grizzlies', conference: 'Western', division: 'Southwest', aliases: ['memphis_grizzlies_nba', 'grizzlies'] }, { abbr: 'MIA', fullName: 'Miami Heat', city: 'Miami', nickname: 'Heat', conference: 'Eastern', division: 'Southeast', aliases: ['miami_heat_nba', 'heat'] }, { abbr: 'MIL', fullName: 'Milwaukee Bucks', city: 'Milwaukee', nickname: 'Bucks', conference: 'Eastern', division: 'Central', aliases: ['milwaukee_bucks_nba', 'bucks'] }, { abbr: 'MIN', fullName: 'Minnesota Timberwolves', city: 'Minnesota', nickname: 'Timberwolves', conference: 'Western', division: 'Northwest', aliases: ['minnesota_timberwolves_nba', 'timberwolves', 'wolves'] }, { abbr: 'NOP', fullName: 'New Orleans Pelicans', city: 'New Orleans', nickname: 'Pelicans', conference: 'Western', division: 'Southwest', aliases: ['new_orleans_pelicans_nba', 'pelicans', 'no'] }, { abbr: 'NYK', fullName: 'New York Knicks', city: 'New York', nickname: 'Knicks', conference: 'Eastern', division: 'Atlantic', aliases: ['new_york_knicks_nba', 'knicks', 'ny'] }, { abbr: 'OKC', fullName: 'Oklahoma City Thunder', city: 'Oklahoma City', nickname: 'Thunder', conference: 'Western', division: 'Northwest', aliases: ['oklahoma_city_thunder_nba', 'thunder'] }, { abbr: 'ORL', fullName: 'Orlando Magic', city: 'Orlando', nickname: 'Magic', conference: 'Eastern', division: 'Southeast', aliases: ['orlando_magic_nba', 'magic'] }, { abbr: 'PHI', fullName: 'Philadelphia 76ers', city: 'Philadelphia', nickname: '76ers', conference: 'Eastern', division: 'Atlantic', aliases: ['philadelphia_76ers_nba', '76ers', 'sixers'] }, { abbr: 'PHX', fullName: 'Phoenix Suns', city: 'Phoenix', nickname: 'Suns', conference: 'Western', division: 'Pacific', aliases: ['phoenix_suns_nba', 'suns', 'pho'] }, { abbr: 'POR', fullName: 'Portland Trail Blazers', city: 'Portland', nickname: 'Trail Blazers', conference: 'Western', division: 'Northwest', aliases: ['portland_trail_blazers_nba', 'trail blazers', 'blazers'] }, { abbr: 'SAC', fullName: 'Sacramento Kings', city: 'Sacramento', nickname: 'Kings', conference: 'Western', division: 'Pacific', aliases: ['sacramento_kings_nba', 'kings'] }, { abbr: 'SAS', fullName: 'San Antonio Spurs', city: 'San Antonio', nickname: 'Spurs', conference: 'Western', division: 'Southwest', aliases: ['san_antonio_spurs_nba', 'spurs', 'sa'] }, { abbr: 'TOR', fullName: 'Toronto Raptors', city: 'Toronto', nickname: 'Raptors', conference: 'Eastern', division: 'Atlantic', aliases: ['toronto_raptors_nba', 'raptors'] }, { abbr: 'UTA', fullName: 'Utah Jazz', city: 'Utah', nickname: 'Jazz', conference: 'Western', division: 'Northwest', aliases: ['utah_jazz_nba', 'jazz'] }, { abbr: 'WAS', fullName: 'Washington Wizards', city: 'Washington', nickname: 'Wizards', conference: 'Eastern', division: 'Southeast', aliases: ['washington_wizards_nba', 'wizards', 'wsh'] }, ], nfl: [ { abbr: 'ARI', fullName: 'Arizona Cardinals', city: 'Arizona', nickname: 'Cardinals', conference: 'NFC', division: 'West', aliases: ['arizona_cardinals_nfl', 'cardinals', 'az'] }, { abbr: 'ATL', fullName: 'Atlanta Falcons', city: 'Atlanta', nickname: 'Falcons', conference: 'NFC', division: 'South', aliases: ['atlanta_falcons_nfl', 'falcons'] }, { abbr: 'BAL', fullName: 'Baltimore Ravens', city: 'Baltimore', nickname: 'Ravens', conference: 'AFC', division: 'North', aliases: ['baltimore_ravens_nfl', 'ravens'] }, { abbr: 'BUF', fullName: 'Buffalo Bills', city: 'Buffalo', nickname: 'Bills', conference: 'AFC', division: 'East', aliases: ['buffalo_bills_nfl', 'bills'] }, { abbr: 'CAR', fullName: 'Carolina Panthers', city: 'Carolina', nickname: 'Panthers', conference: 'NFC', division: 'South', aliases: ['carolina_panthers_nfl', 'panthers'] }, { abbr: 'CHI', fullName: 'Chicago Bears', city: 'Chicago', nickname: 'Bears', conference: 'NFC', division: 'North', aliases: ['chicago_bears_nfl', 'bears'] }, { abbr: 'CIN', fullName: 'Cincinnati Bengals', city: 'Cincinnati', nickname: 'Bengals', conference: 'AFC', division: 'North', aliases: ['cincinnati_bengals_nfl', 'bengals'] }, { abbr: 'CLE', fullName: 'Cleveland Browns', city: 'Cleveland', nickname: 'Browns', conference: 'AFC', division: 'North', aliases: ['cleveland_browns_nfl', 'browns'] }, { abbr: 'DAL', fullName: 'Dallas Cowboys', city: 'Dallas', nickname: 'Cowboys', conference: 'NFC', division: 'East', aliases: ['dallas_cowboys_nfl', 'cowboys'] }, { abbr: 'DEN', fullName: 'Denver Broncos', city: 'Denver', nickname: 'Broncos', conference: 'AFC', division: 'West', aliases: ['denver_broncos_nfl', 'broncos'] }, { abbr: 'DET', fullName: 'Detroit Lions', city: 'Detroit', nickname: 'Lions', conference: 'NFC', division: 'North', aliases: ['detroit_lions_nfl', 'lions'] }, { abbr: 'GB', fullName: 'Green Bay Packers', city: 'Green Bay', nickname: 'Packers', conference: 'NFC', division: 'North', aliases: ['green_bay_packers_nfl', 'packers', 'gnb'] }, { abbr: 'HOU', fullName: 'Houston Texans', city: 'Houston', nickname: 'Texans', conference: 'AFC', division: 'South', aliases: ['houston_texans_nfl', 'texans'] }, { abbr: 'IND', fullName: 'Indianapolis Colts', city: 'Indianapolis', nickname: 'Colts', conference: 'AFC', division: 'South', aliases: ['indianapolis_colts_nfl', 'colts'] }, { abbr: 'JAX', fullName: 'Jacksonville Jaguars', city: 'Jacksonville', nickname: 'Jaguars', conference: 'AFC', division: 'South', aliases: ['jacksonville_jaguars_nfl', 'jaguars', 'jac'] }, { abbr: 'KC', fullName: 'Kansas City Chiefs', city: 'Kansas City', nickname: 'Chiefs', conference: 'AFC', division: 'West', aliases: ['kansas_city_chiefs_nfl', 'chiefs', 'kan'] }, { abbr: 'LV', fullName: 'Las Vegas Raiders', city: 'Las Vegas', nickname: 'Raiders', conference: 'AFC', division: 'West', aliases: ['las_vegas_raiders_nfl', 'raiders', 'lvr', 'oak'] }, { abbr: 'LAC', fullName: 'Los Angeles Chargers', city: 'Los Angeles', nickname: 'Chargers', conference: 'AFC', division: 'West', aliases: ['los_angeles_chargers_nfl', 'chargers', 'la chargers', 'sd'] }, { abbr: 'LAR', fullName: 'Los Angeles Rams', city: 'Los Angeles', nickname: 'Rams', conference: 'NFC', division: 'West', aliases: ['los_angeles_rams_nfl', 'rams', 'la rams', 'stl'] }, { abbr: 'MIA', fullName: 'Miami Dolphins', city: 'Miami', nickname: 'Dolphins', conference: 'AFC', division: 'East', aliases: ['miami_dolphins_nfl', 'dolphins'] }, { abbr: 'MIN', fullName: 'Minnesota Vikings', city: 'Minnesota', nickname: 'Vikings', conference: 'NFC', division: 'North', aliases: ['minnesota_vikings_nfl', 'vikings'] }, { abbr: 'NE', fullName: 'New England Patriots', city: 'New England', nickname: 'Patriots', conference: 'AFC', division: 'East', aliases: ['new_england_patriots_nfl', 'patriots', 'pats', 'nwe'] }, { abbr: 'NO', fullName: 'New Orleans Saints', city: 'New Orleans', nickname: 'Saints', conference: 'NFC', division: 'South', aliases: ['new_orleans_saints_nfl', 'saints', 'nor'] }, { abbr: 'NYG', fullName: 'New York Giants', city: 'New York', nickname: 'Giants', conference: 'NFC', division: 'East', aliases: ['new_york_giants_nfl', 'giants'] }, { abbr: 'NYJ', fullName: 'New York Jets', city: 'New York', nickname: 'Jets', conference: 'AFC', division: 'East', aliases: ['new_york_jets_nfl', 'jets'] }, { abbr: 'PHI', fullName: 'Philadelphia Eagles', city: 'Philadelphia', nickname: 'Eagles', conference: 'NFC', division: 'East', aliases: ['philadelphia_eagles_nfl', 'eagles'] }, { abbr: 'PIT', fullName: 'Pittsburgh Steelers', city: 'Pittsburgh', nickname: 'Steelers', conference: 'AFC', division: 'North', aliases: ['pittsburgh_steelers_nfl', 'steelers'] }, { abbr: 'SF', fullName: 'San Francisco 49ers', city: 'San Francisco', nickname: '49ers', conference: 'NFC', division: 'West', aliases: ['san_francisco_49ers_nfl', '49ers', 'niners', 'sfo'] }, { abbr: 'SEA', fullName: 'Seattle Seahawks', city: 'Seattle', nickname: 'Seahawks', conference: 'NFC', division: 'West', aliases: ['seattle_seahawks_nfl', 'seahawks'] }, { abbr: 'TB', fullName: 'Tampa Bay Buccaneers', city: 'Tampa Bay', nickname: 'Buccaneers', conference: 'NFC', division: 'South', aliases: ['tampa_bay_buccaneers_nfl', 'buccaneers', 'bucs', 'tbb'] }, { abbr: 'TEN', fullName: 'Tennessee Titans', city: 'Tennessee', nickname: 'Titans', conference: 'AFC', division: 'South', aliases: ['tennessee_titans_nfl', 'titans'] }, { abbr: 'WAS', fullName: 'Washington Commanders', city: 'Washington', nickname: 'Commanders', conference: 'NFC', division: 'East', aliases: ['washington_commanders_nfl', 'commanders', 'wsh'] }, ], nhl: [ { abbr: 'ANA', fullName: 'Anaheim Ducks', city: 'Anaheim', nickname: 'Ducks', conference: 'Western', division: 'Pacific', aliases: ['anaheim_ducks_nhl', 'ducks'] }, { abbr: 'BOS', fullName: 'Boston Bruins', city: 'Boston', nickname: 'Bruins', conference: 'Eastern', division: 'Atlantic', aliases: ['boston_bruins_nhl', 'bruins'] }, { abbr: 'BUF', fullName: 'Buffalo Sabres', city: 'Buffalo', nickname: 'Sabres', conference: 'Eastern', division: 'Atlantic', aliases: ['buffalo_sabres_nhl', 'sabres'] }, { abbr: 'CGY', fullName: 'Calgary Flames', city: 'Calgary', nickname: 'Flames', conference: 'Western', division: 'Pacific', aliases: ['calgary_flames_nhl', 'flames', 'cal'] }, { abbr: 'CAR', fullName: 'Carolina Hurricanes', city: 'Carolina', nickname: 'Hurricanes', conference: 'Eastern', division: 'Metropolitan', aliases: ['carolina_hurricanes_nhl', 'hurricanes', 'canes'] }, { abbr: 'CHI', fullName: 'Chicago Blackhawks', city: 'Chicago', nickname: 'Blackhawks', conference: 'Western', division: 'Central', aliases: ['chicago_blackhawks_nhl', 'blackhawks'] }, { abbr: 'COL', fullName: 'Colorado Avalanche', city: 'Colorado', nickname: 'Avalanche', conference: 'Western', division: 'Central', aliases: ['colorado_avalanche_nhl', 'avalanche', 'avs'] }, { abbr: 'CBJ', fullName: 'Columbus Blue Jackets', city: 'Columbus', nickname: 'Blue Jackets', conference: 'Eastern', division: 'Metropolitan', aliases: ['columbus_blue_jackets_nhl', 'blue jackets'] }, { abbr: 'DAL', fullName: 'Dallas Stars', city: 'Dallas', nickname: 'Stars', conference: 'Western', division: 'Central', aliases: ['dallas_stars_nhl', 'stars'] }, { abbr: 'DET', fullName: 'Detroit Red Wings', city: 'Detroit', nickname: 'Red Wings', conference: 'Eastern', division: 'Atlantic', aliases: ['detroit_red_wings_nhl', 'red wings'] }, { abbr: 'EDM', fullName: 'Edmonton Oilers', city: 'Edmonton', nickname: 'Oilers', conference: 'Western', division: 'Pacific', aliases: ['edmonton_oilers_nhl', 'oilers'] }, { abbr: 'FLA', fullName: 'Florida Panthers', city: 'Florida', nickname: 'Panthers', conference: 'Eastern', division: 'Atlantic', aliases: ['florida_panthers_nhl', 'panthers'] }, { abbr: 'LA', fullName: 'Los Angeles Kings', city: 'Los Angeles', nickname: 'Kings', conference: 'Western', division: 'Pacific', aliases: ['los_angeles_kings_nhl', 'kings', 'lak'] }, { abbr: 'MIN', fullName: 'Minnesota Wild', city: 'Minnesota', nickname: 'Wild', conference: 'Western', division: 'Central', aliases: ['minnesota_wild_nhl', 'wild'] }, { abbr: 'MTL', fullName: 'Montreal Canadiens', city: 'Montreal', nickname: 'Canadiens', conference: 'Eastern', division: 'Atlantic', aliases: ['montreal_canadiens_nhl', 'canadiens', 'habs'] }, { abbr: 'NSH', fullName: 'Nashville Predators', city: 'Nashville', nickname: 'Predators', conference: 'Western', division: 'Central', aliases: ['nashville_predators_nhl', 'predators', 'preds'] }, { abbr: 'NJD', fullName: 'New Jersey Devils', city: 'New Jersey', nickname: 'Devils', conference: 'Eastern', division: 'Metropolitan', aliases: ['new_jersey_devils_nhl', 'devils', 'nj'] }, { abbr: 'NYI', fullName: 'New York Islanders', city: 'New York', nickname: 'Islanders', conference: 'Eastern', division: 'Metropolitan', aliases: ['new_york_islanders_nhl', 'islanders', 'isles'] }, { abbr: 'NYR', fullName: 'New York Rangers', city: 'New York', nickname: 'Rangers', conference: 'Eastern', division: 'Metropolitan', aliases: ['new_york_rangers_nhl', 'rangers'] }, { abbr: 'OTT', fullName: 'Ottawa Senators', city: 'Ottawa', nickname: 'Senators', conference: 'Eastern', division: 'Atlantic', aliases: ['ottawa_senators_nhl', 'senators', 'sens'] }, { abbr: 'PHI', fullName: 'Philadelphia Flyers', city: 'Philadelphia', nickname: 'Flyers', conference: 'Eastern', division: 'Metropolitan', aliases: ['philadelphia_flyers_nhl', 'flyers'] }, { abbr: 'PIT', fullName: 'Pittsburgh Penguins', city: 'Pittsburgh', nickname: 'Penguins', conference: 'Eastern', division: 'Metropolitan', aliases: ['pittsburgh_penguins_nhl', 'penguins', 'pens'] }, { abbr: 'SJ', fullName: 'San Jose Sharks', city: 'San Jose', nickname: 'Sharks', conference: 'Western', division: 'Pacific', aliases: ['san_jose_sharks_nhl', 'sharks', 'sjs'] }, { abbr: 'SEA', fullName: 'Seattle Kraken', city: 'Seattle', nickname: 'Kraken', conference: 'Western', division: 'Pacific', aliases: ['seattle_kraken_nhl', 'kraken'] }, { abbr: 'STL', fullName: 'St. Louis Blues', city: 'St. Louis', nickname: 'Blues', conference: 'Western', division: 'Central', aliases: ['st_louis_blues_nhl', 'blues'] }, { abbr: 'TB', fullName: 'Tampa Bay Lightning', city: 'Tampa Bay', nickname: 'Lightning', conference: 'Eastern', division: 'Atlantic', aliases: ['tampa_bay_lightning_nhl', 'lightning', 'bolts'] }, { abbr: 'TOR', fullName: 'Toronto Maple Leafs', city: 'Toronto', nickname: 'Maple Leafs', conference: 'Eastern', division: 'Atlantic', aliases: ['toronto_maple_leafs_nhl', 'maple leafs', 'leafs'] }, { abbr: 'UTA', fullName: 'Utah Hockey Club', city: 'Utah', nickname: 'Hockey Club', conference: 'Western', division: 'Central', aliases: ['utah_hockey_club_nhl', 'arizona_coyotes_nhl', 'coyotes'] }, { abbr: 'VAN', fullName: 'Vancouver Canucks', city: 'Vancouver', nickname: 'Canucks', conference: 'Western', division: 'Pacific', aliases: ['vancouver_canucks_nhl', 'canucks'] }, { abbr: 'VGK', fullName: 'Vegas Golden Knights', city: 'Las Vegas', nickname: 'Golden Knights', conference: 'Western', division: 'Pacific', aliases: ['vegas_golden_knights_nhl', 'golden knights', 'knights', 'vegas'] }, { abbr: 'WAS', fullName: 'Washington Capitals', city: 'Washington', nickname: 'Capitals', conference: 'Eastern', division: 'Metropolitan', aliases: ['washington_capitals_nhl', 'capitals', 'caps'] }, { abbr: 'WPG', fullName: 'Winnipeg Jets', city: 'Winnipeg', nickname: 'Jets', conference: 'Western', division: 'Central', aliases: ['winnipeg_jets_nhl', 'jets', 'win'] }, ], mlb: [ { abbr: 'ARI', fullName: 'Arizona Diamondbacks', city: 'Arizona', nickname: 'Diamondbacks', conference: 'NL', division: 'West', aliases: ['arizona_diamondbacks_mlb', 'diamondbacks', 'dbacks'] }, { abbr: 'ATL', fullName: 'Atlanta Braves', city: 'Atlanta', nickname: 'Braves', conference: 'NL', division: 'East', aliases: ['atlanta_braves_mlb', 'braves'] }, { abbr: 'BAL', fullName: 'Baltimore Orioles', city: 'Baltimore', nickname: 'Orioles', conference: 'AL', division: 'East', aliases: ['baltimore_orioles_mlb', 'orioles', 'os'] }, { abbr: 'BOS', fullName: 'Boston Red Sox', city: 'Boston', nickname: 'Red Sox', conference: 'AL', division: 'East', aliases: ['boston_red_sox_mlb', 'red sox'] }, { abbr: 'CHC', fullName: 'Chicago Cubs', city: 'Chicago', nickname: 'Cubs', conference: 'NL', division: 'Central', aliases: ['chicago_cubs_mlb', 'cubs'] }, { abbr: 'CWS', fullName: 'Chicago White Sox', city: 'Chicago', nickname: 'White Sox', conference: 'AL', division: 'Central', aliases: ['chicago_white_sox_mlb', 'white sox', 'chw'] }, { abbr: 'CIN', fullName: 'Cincinnati Reds', city: 'Cincinnati', nickname: 'Reds', conference: 'NL', division: 'Central', aliases: ['cincinnati_reds_mlb', 'reds'] }, { abbr: 'CLE', fullName: 'Cleveland Guardians', city: 'Cleveland', nickname: 'Guardians', conference: 'AL', division: 'Central', aliases: ['cleveland_guardians_mlb', 'guardians', 'indians'] }, { abbr: 'COL', fullName: 'Colorado Rockies', city: 'Colorado', nickname: 'Rockies', conference: 'NL', division: 'West', aliases: ['colorado_rockies_mlb', 'rockies'] }, { abbr: 'DET', fullName: 'Detroit Tigers', city: 'Detroit', nickname: 'Tigers', conference: 'AL', division: 'Central', aliases: ['detroit_tigers_mlb', 'tigers'] }, { abbr: 'HOU', fullName: 'Houston Astros', city: 'Houston', nickname: 'Astros', conference: 'AL', division: 'West', aliases: ['houston_astros_mlb', 'astros', 'stros'] }, { abbr: 'KC', fullName: 'Kansas City Royals', city: 'Kansas City', nickname: 'Royals', conference: 'AL', division: 'Central', aliases: ['kansas_city_royals_mlb', 'royals'] }, { abbr: 'LAA', fullName: 'Los Angeles Angels', city: 'Los Angeles', nickname: 'Angels', conference: 'AL', division: 'West', aliases: ['los_angeles_angels_mlb', 'angels', 'la angels'] }, { abbr: 'LAD', fullName: 'Los Angeles Dodgers', city: 'Los Angeles', nickname: 'Dodgers', conference: 'NL', division: 'West', aliases: ['los_angeles_dodgers_mlb', 'dodgers', 'la dodgers'] }, { abbr: 'MIA', fullName: 'Miami Marlins', city: 'Miami', nickname: 'Marlins', conference: 'NL', division: 'East', aliases: ['miami_marlins_mlb', 'marlins'] }, { abbr: 'MIL', fullName: 'Milwaukee Brewers', city: 'Milwaukee', nickname: 'Brewers', conference: 'NL', division: 'Central', aliases: ['milwaukee_brewers_mlb', 'brewers', 'brew crew'] }, { abbr: 'MIN', fullName: 'Minnesota Twins', city: 'Minnesota', nickname: 'Twins', conference: 'AL', division: 'Central', aliases: ['minnesota_twins_mlb', 'twins'] }, { abbr: 'NYM', fullName: 'New York Mets', city: 'New York', nickname: 'Mets', conference: 'NL', division: 'East', aliases: ['new_york_mets_mlb', 'mets'] }, { abbr: 'NYY', fullName: 'New York Yankees', city: 'New York', nickname: 'Yankees', conference: 'AL', division: 'East', aliases: ['new_york_yankees_mlb', 'yankees', 'yanks'] }, { abbr: 'OAK', fullName: 'Oakland Athletics', city: 'Oakland', nickname: 'Athletics', conference: 'AL', division: 'West', aliases: ['oakland_athletics_mlb', 'athletics', 'as'] }, { abbr: 'PHI', fullName: 'Philadelphia Phillies', city: 'Philadelphia', nickname: 'Phillies', conference: 'NL', division: 'East', aliases: ['philadelphia_phillies_mlb', 'phillies', 'phils'] }, { abbr: 'PIT', fullName: 'Pittsburgh Pirates', city: 'Pittsburgh', nickname: 'Pirates', conference: 'NL', division: 'Central', aliases: ['pittsburgh_pirates_mlb', 'pirates', 'buccos'] }, { abbr: 'SD', fullName: 'San Diego Padres', city: 'San Diego', nickname: 'Padres', conference: 'NL', division: 'West', aliases: ['san_diego_padres_mlb', 'padres'] }, { abbr: 'SF', fullName: 'San Francisco Giants', city: 'San Francisco', nickname: 'Giants', conference: 'NL', division: 'West', aliases: ['san_francisco_giants_mlb', 'giants'] }, { abbr: 'SEA', fullName: 'Seattle Mariners', city: 'Seattle', nickname: 'Mariners', conference: 'AL', division: 'West', aliases: ['seattle_mariners_mlb', 'mariners', 'ms'] }, { abbr: 'STL', fullName: 'St. Louis Cardinals', city: 'St. Louis', nickname: 'Cardinals', conference: 'NL', division: 'Central', aliases: ['st_louis_cardinals_mlb', 'cardinals'] }, { abbr: 'TB', fullName: 'Tampa Bay Rays', city: 'Tampa Bay', nickname: 'Rays', conference: 'AL', division: 'East', aliases: ['tampa_bay_rays_mlb', 'rays'] }, { abbr: 'TEX', fullName: 'Texas Rangers', city: 'Texas', nickname: 'Rangers', conference: 'AL', division: 'West', aliases: ['texas_rangers_mlb', 'rangers'] }, { abbr: 'TOR', fullName: 'Toronto Blue Jays', city: 'Toronto', nickname: 'Blue Jays', conference: 'AL', division: 'East', aliases: ['toronto_blue_jays_mlb', 'blue jays', 'jays'] }, { abbr: 'WAS', fullName: 'Washington Nationals', city: 'Washington', nickname: 'Nationals', conference: 'NL', division: 'East', aliases: ['washington_nationals_mlb', 'nationals', 'nats'] }, ], }; // ============================================================ // Phase 1: Migrate Teams // ============================================================ async function migrateTeams(leagues: string[]): Promise { console.log('\n=== Phase 1: Migrating Teams ===\n'); for (const league of leagues) { const teams = TEAM_DATA[league]; if (!teams) { console.log(` No team data for ${league}, skipping`); continue; } console.log(`Processing ${league.toUpperCase()} (${teams.length} teams)...`); let created = 0; let aliasesCreated = 0; for (const team of teams) { // Upsert canonical team const canonicalTeam = await prisma.canonicalTeam.upsert({ where: { league_abbr: { league, abbr: team.abbr } }, create: { league, abbr: team.abbr, fullName: team.fullName, city: team.city, nickname: team.nickname, conference: team.conference, division: team.division, sgoId: team.aliases.find(a => a.includes(`_${league}`))?.toUpperCase(), }, update: { fullName: team.fullName, city: team.city, nickname: team.nickname, conference: team.conference, division: team.division, }, }); created++; // Create aliases for (const alias of team.aliases) { try { await prisma.teamAlias.upsert({ where: { source_alias: { source: 'canonical', alias: alias.toLowerCase() } }, create: { teamId: canonicalTeam.id, source: 'canonical', alias: alias.toLowerCase(), aliasType: alias.includes(`_${league}`) ? 'external_id' : 'name', }, update: { teamId: canonicalTeam.id }, }); aliasesCreated++; } catch (e) { // Ignore duplicate aliases } } // Also add abbreviation as alias try { await prisma.teamAlias.upsert({ where: { source_alias: { source: 'canonical', alias: team.abbr.toLowerCase() } }, create: { teamId: canonicalTeam.id, source: 'canonical', alias: team.abbr.toLowerCase(), aliasType: 'abbr', isPrimary: true, }, update: { teamId: canonicalTeam.id }, }); aliasesCreated++; } catch (e) { // Ignore } } console.log(` ${league.toUpperCase()}: ${created} teams, ${aliasesCreated} aliases`); } // Also extract teams from existing SportsGame records console.log('\nExtracting additional teams from SportsGame...'); const games = await prisma.sportsGame.findMany({ select: { league: true, homeTeam: true, awayTeam: true }, distinct: ['league', 'homeTeam', 'awayTeam'], take: 10000, }); const seenTeams = new Set(); let additionalAliases = 0; for (const game of games) { for (const teamName of [game.homeTeam, game.awayTeam]) { const key = `${game.league}:${teamName}`; if (seenTeams.has(key)) continue; seenTeams.add(key); // Try to find canonical team by normalized name const existingAlias = await prisma.teamAlias.findFirst({ where: { alias: { equals: teamName.toLowerCase(), mode: 'insensitive' } }, include: { team: true }, }); if (existingAlias) continue; // Already have this alias // Try to find by abbreviation const normalized = teamName.toUpperCase().slice(0, 3); const canonicalTeam = await prisma.canonicalTeam.findUnique({ where: { league_abbr: { league: game.league, abbr: normalized } }, }); if (canonicalTeam) { try { await prisma.teamAlias.create({ data: { teamId: canonicalTeam.id, source: 'sportsdb', alias: teamName.toLowerCase(), aliasType: 'name', }, }); additionalAliases++; } catch (e) { // Ignore duplicates } } } } console.log(` Added ${additionalAliases} aliases from SportsGame records`); } // ============================================================ // Phase 2: Migrate Players // ============================================================ function normalizePlayerName(name: string): string { if (!name) return ''; return name .toLowerCase() .normalize('NFD') .replace(/[\u0300-\u036f]/g, '') .replace(/\s+(jr\.?|sr\.?|iii|ii|iv|v)$/i, '') .replace(/\./g, '') .replace(/\s+/g, ' ') .trim(); } function parseSgoPlayerId(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|MMA|UFC)$/i); if (!leagueMatch) return null; const league = leagueMatch[1].toLowerCase(); const withoutLeague = sgoId.slice(0, -leagueMatch[0].length); const parts = withoutLeague.split('_'); const lastPart = parts[parts.length - 1]; if (!/^\d+$/.test(lastPart)) return null; const nameParts = parts.slice(0, -1); return { name: nameParts.join(' ').toLowerCase(), league }; } async function migratePlayers(leagues: string[]): Promise { console.log('\n=== Phase 2: Migrating Players ===\n'); for (const league of leagues) { console.log(`Processing ${league.toUpperCase()} players...`); // Get all players from Player table const players = await prisma.player.findMany({ where: { league }, take: 50000, }); console.log(` Found ${players.length} players in Player table`); let created = 0; let aliasesCreated = 0; let skipped = 0; for (const player of players) { const normalizedName = normalizePlayerName(player.name); if (!normalizedName) { skipped++; continue; } // Look up team let teamId: bigint | null = null; if (player.team) { const team = await prisma.canonicalTeam.findFirst({ where: { league, OR: [ { abbr: player.team.toUpperCase() }, { aliases: { some: { alias: { equals: player.team.toLowerCase(), mode: 'insensitive' } } } }, ], }, }); teamId = team?.id ?? null; } // Parse SGO ID if it looks like one let sgoId: string | null = null; if (player.externalPlayerId && player.externalPlayerId.includes('_')) { const parsed = parseSgoPlayerId(player.externalPlayerId); if (parsed) { sgoId = player.externalPlayerId; } } // Determine ID type for non-SGO IDs let bdlId: string | null = null; let espnId: string | null = null; if (player.externalPlayerId && !sgoId) { // Numeric IDs are likely BDL or ESPN if (/^\d+$/.test(player.externalPlayerId)) { // We can't easily distinguish - default to using the existing source logic // For now, store in bdlId if numeric (most common) bdlId = player.externalPlayerId; } } // Upsert canonical player try { const canonicalPlayer = await prisma.canonicalPlayer.upsert({ where: { league_normalizedName: { league, normalizedName } }, create: { league, fullName: player.name, normalizedName, position: player.position, teamId, sgoId, bdlId, espnId, }, update: { fullName: player.name, position: player.position ?? undefined, teamId: teamId ?? undefined, sgoId: sgoId ?? undefined, bdlId: bdlId ?? undefined, }, }); created++; // Create alias for external ID if (player.externalPlayerId) { try { await prisma.playerAlias.upsert({ where: { source_alias: { source: 'player_table', alias: player.externalPlayerId } }, create: { playerId: canonicalPlayer.id, source: 'player_table', alias: player.externalPlayerId, aliasType: 'external_id', }, update: { playerId: canonicalPlayer.id }, }); aliasesCreated++; } catch (e) { // Ignore duplicates } } } catch (e) { skipped++; } } console.log(` ${league.toUpperCase()}: ${created} players, ${aliasesCreated} aliases, ${skipped} skipped`); // Also process PlayerMapping table if it exists try { const mappings = await prisma.playerMapping.findMany({ where: { league }, take: 50000, }); console.log(` Found ${mappings.length} entries in PlayerMapping table`); let mappingAliases = 0; for (const mapping of mappings) { const canonicalPlayer = await prisma.canonicalPlayer.findUnique({ where: { league_normalizedName: { league, normalizedName: mapping.normalizedName } }, }); if (!canonicalPlayer) continue; // Update external IDs const updates: any = {}; if (mapping.sgoId && !canonicalPlayer.sgoId) updates.sgoId = mapping.sgoId; if (mapping.bdlId && !canonicalPlayer.bdlId) updates.bdlId = mapping.bdlId; if (mapping.espnId && !canonicalPlayer.espnId) updates.espnId = mapping.espnId; if (mapping.nbaComId && !canonicalPlayer.nbaComId) updates.nbaComId = mapping.nbaComId; if (Object.keys(updates).length > 0) { await prisma.canonicalPlayer.update({ where: { id: canonicalPlayer.id }, data: updates, }); } // Create aliases for each ID const ids = [ { source: 'sgo', id: mapping.sgoId }, { source: 'bdl', id: mapping.bdlId }, { source: 'espn', id: mapping.espnId }, { source: 'nba_com', id: mapping.nbaComId }, ]; for (const { source, id } of ids) { if (!id) continue; try { await prisma.playerAlias.upsert({ where: { source_alias: { source, alias: id } }, create: { playerId: canonicalPlayer.id, source, alias: id, aliasType: 'external_id', }, update: { playerId: canonicalPlayer.id }, }); mappingAliases++; } catch (e) { // Ignore } } } console.log(` Added ${mappingAliases} aliases from PlayerMapping`); } catch (e) { console.log(` PlayerMapping not available or error: ${e}`); } } } // ============================================================ // Phase 3: Migrate Games // ============================================================ async function migrateGames(leagues: string[]): Promise { console.log('\n=== Phase 3: Migrating Games (Deduplication) ===\n'); for (const league of leagues) { console.log(`Processing ${league.toUpperCase()} games...`); // Get all games from SportsGame const games = await prisma.sportsGame.findMany({ where: { league }, orderBy: { gameDate: 'desc' }, take: 50000, }); console.log(` Found ${games.length} games in SportsGame table`); let created = 0; let duplicates = 0; let failed = 0; for (const game of games) { // Resolve home and away teams const homeTeam = await prisma.canonicalTeam.findFirst({ where: { league, OR: [ { abbr: game.homeTeam.toUpperCase() }, { aliases: { some: { alias: { equals: game.homeTeam.toLowerCase(), mode: 'insensitive' } } } }, ], }, }); const awayTeam = await prisma.canonicalTeam.findFirst({ where: { league, OR: [ { abbr: game.awayTeam.toUpperCase() }, { aliases: { some: { alias: { equals: game.awayTeam.toLowerCase(), mode: 'insensitive' } } } }, ], }, }); if (!homeTeam || !awayTeam) { failed++; continue; } // Normalize date to date-only const gameDate = new Date(game.gameDate.toISOString().slice(0, 10)); // Determine external ID and source let sgoEventId: string | null = null; let espnEventId: string | null = null; let kaggleId: string | null = null; if (game.externalGameId) { if (game.externalGameId.startsWith('sgo:')) { sgoEventId = game.externalGameId; } else if (game.externalGameId.startsWith('espn:')) { espnEventId = game.externalGameId; } else if (game.externalGameId.startsWith('kaggle-')) { kaggleId = game.externalGameId; } else if (/^\d+$/.test(game.externalGameId)) { // Numeric ID, likely ESPN espnEventId = game.externalGameId; } } // Try to upsert canonical game try { const existing = await prisma.canonicalGame.findUnique({ where: { league_season_gameDate_homeTeamId_awayTeamId: { league, season: game.season, gameDate, homeTeamId: homeTeam.id, awayTeamId: awayTeam.id, }, }, }); if (existing) { // Update with additional external IDs if available const updates: any = {}; if (sgoEventId && !existing.sgoEventId) updates.sgoEventId = sgoEventId; if (espnEventId && !existing.espnEventId) updates.espnEventId = espnEventId; if (kaggleId && !existing.kaggleId) updates.kaggleId = kaggleId; if (game.homeScore != null && !existing.homeScore) updates.homeScore = game.homeScore; if (game.awayScore != null && !existing.awayScore) updates.awayScore = game.awayScore; if (game.moneylineHome != null && !existing.moneylineHome) updates.moneylineHome = game.moneylineHome; if (game.moneylineAway != null && !existing.moneylineAway) updates.moneylineAway = game.moneylineAway; if (game.spreadHome != null && !existing.spreadHome) updates.spreadHome = game.spreadHome; if (game.total != null && !existing.total) updates.total = game.total; if (Object.keys(updates).length > 0) { await prisma.canonicalGame.update({ where: { id: existing.id }, data: updates, }); } duplicates++; } else { await prisma.canonicalGame.create({ data: { league, season: game.season, gameDate, homeTeamId: homeTeam.id, awayTeamId: awayTeam.id, status: game.status || 'final', homeScore: game.homeScore, awayScore: game.awayScore, moneylineHome: game.moneylineHome, moneylineAway: game.moneylineAway, spreadHome: game.spreadHome, spreadAway: game.spreadAway, total: game.total, openingMoneylineHome: game.openingMoneylineHome, openingMoneylineAway: game.openingMoneylineAway, openingSpreadHome: game.openingSpreadHome, openingTotal: game.openingTotal, closingMoneylineHome: game.closingMoneylineHome, closingMoneylineAway: game.closingMoneylineAway, closingSpreadHome: game.closingSpreadHome, closingTotal: game.closingTotal, sgoEventId, espnEventId, kaggleId, }, }); created++; } } catch (e) { failed++; } } console.log(` ${league.toUpperCase()}: ${created} created, ${duplicates} merged, ${failed} failed`); } } // ============================================================ // Phase 4: Backfill Canonical IDs // ============================================================ async function backfillCanonicalIds(leagues: string[]): Promise { console.log('\n=== Phase 4: Backfilling Canonical IDs ===\n'); for (const league of leagues) { console.log(`Processing ${league.toUpperCase()}...`); // Backfill PlayerGameMetric console.log(' Backfilling PlayerGameMetric...'); const metrics = await prisma.playerGameMetric.findMany({ where: { league, canonicalPlayerId: null }, select: { id: true, playerExternalId: true, playerName: true }, take: 50000, }); let metricsUpdated = 0; let metricsFailed = 0; for (const metric of metrics) { // Try to find canonical player const normalizedName = normalizePlayerName(metric.playerName || ''); let canonicalPlayer = await prisma.canonicalPlayer.findFirst({ where: { league, OR: [ { normalizedName }, { aliases: { some: { alias: { equals: metric.playerExternalId, mode: 'insensitive' } } } }, { sgoId: metric.playerExternalId }, { bdlId: metric.playerExternalId }, { espnId: metric.playerExternalId }, ], }, }); // If still not found, try parsing SGO ID if (!canonicalPlayer && metric.playerExternalId) { const parsed = parseSgoPlayerId(metric.playerExternalId); if (parsed) { canonicalPlayer = await prisma.canonicalPlayer.findUnique({ where: { league_normalizedName: { league, normalizedName: parsed.name } }, }); } } if (canonicalPlayer) { await prisma.playerGameMetric.update({ where: { id: metric.id }, data: { canonicalPlayerId: canonicalPlayer.id }, }); metricsUpdated++; } else { metricsFailed++; } } console.log(` PlayerGameMetric: ${metricsUpdated} updated, ${metricsFailed} unmatched`); // Backfill PlayerPropLine console.log(' Backfilling PlayerPropLine...'); const props = await prisma.playerPropLine.findMany({ where: { league, canonicalPlayerId: null }, select: { id: true, playerExternalId: true, gameId: true }, take: 50000, }); let propsUpdated = 0; let propsFailed = 0; for (const prop of props) { // Find canonical player let canonicalPlayer = await prisma.canonicalPlayer.findFirst({ where: { league, OR: [ { aliases: { some: { alias: { equals: prop.playerExternalId, mode: 'insensitive' } } } }, { sgoId: prop.playerExternalId }, { bdlId: prop.playerExternalId }, { espnId: prop.playerExternalId }, ], }, }); // Try parsing SGO ID if (!canonicalPlayer) { const parsed = parseSgoPlayerId(prop.playerExternalId); if (parsed) { canonicalPlayer = await prisma.canonicalPlayer.findUnique({ where: { league_normalizedName: { league, normalizedName: parsed.name } }, }); } } // Find canonical game (if gameId exists) let canonicalGame = null; if (prop.gameId) { const sportsGame = await prisma.sportsGame.findUnique({ where: { id: prop.gameId }, select: { externalGameId: true, gameDate: true, homeTeam: true, awayTeam: true, season: true }, }); if (sportsGame) { canonicalGame = await prisma.canonicalGame.findFirst({ where: { league, OR: [ { sgoEventId: sportsGame.externalGameId }, { espnEventId: sportsGame.externalGameId }, ], }, }); } } const updates: any = {}; if (canonicalPlayer) updates.canonicalPlayerId = canonicalPlayer.id; if (canonicalGame) updates.canonicalGameId = canonicalGame.id; if (Object.keys(updates).length > 0) { await prisma.playerPropLine.update({ where: { id: prop.id }, data: updates, }); propsUpdated++; } else { propsFailed++; } } console.log(` PlayerPropLine: ${propsUpdated} updated, ${propsFailed} unmatched`); // Backfill PlayerInjury console.log(' Backfilling PlayerInjury...'); const injuries = await prisma.playerInjury.findMany({ where: { league, canonicalPlayerId: null }, select: { id: true, playerExternalId: true, playerName: true }, take: 50000, }); let injuriesUpdated = 0; let injuriesFailed = 0; for (const injury of injuries) { const normalizedName = normalizePlayerName(injury.playerName); const canonicalPlayer = await prisma.canonicalPlayer.findFirst({ where: { league, OR: [ { normalizedName }, { aliases: { some: { alias: { equals: injury.playerExternalId, mode: 'insensitive' } } } }, ], }, }); if (canonicalPlayer) { await prisma.playerInjury.update({ where: { id: injury.id }, data: { canonicalPlayerId: canonicalPlayer.id }, }); injuriesUpdated++; } else { injuriesFailed++; } } console.log(` PlayerInjury: ${injuriesUpdated} updated, ${injuriesFailed} unmatched`); } } // ============================================================ // Main // ============================================================ async function main() { const args = process.argv.slice(2); const phaseArg = args.find(a => a.startsWith('--phase='))?.split('=')[1] || 'all'; const leagueArg = args.find(a => a.startsWith('--league='))?.split('=')[1]; const leagues = leagueArg ? [leagueArg] : ['nba', 'nfl', 'nhl', 'mlb']; const phases = phaseArg === 'all' ? ['teams', 'players', 'games', 'backfill'] : [phaseArg]; console.log('='.repeat(60)); console.log('CANONICAL ENTITY MIGRATION'); console.log('='.repeat(60)); console.log(`Leagues: ${leagues.join(', ').toUpperCase()}`); console.log(`Phases: ${phases.join(', ')}`); try { for (const phase of phases) { switch (phase) { case 'teams': await migrateTeams(leagues); break; case 'players': await migratePlayers(leagues); break; case 'games': await migrateGames(leagues); break; case 'backfill': await backfillCanonicalIds(leagues); break; default: console.error(`Unknown phase: ${phase}`); } } console.log('\n' + '='.repeat(60)); console.log('MIGRATION COMPLETE'); console.log('='.repeat(60)); // Print summary stats const teamCount = await prisma.canonicalTeam.count(); const playerCount = await prisma.canonicalPlayer.count(); const gameCount = await prisma.canonicalGame.count(); const teamAliasCount = await prisma.teamAlias.count(); const playerAliasCount = await prisma.playerAlias.count(); console.log(`\nSummary:`); console.log(` CanonicalTeam: ${teamCount} records`); console.log(` TeamAlias: ${teamAliasCount} records`); console.log(` CanonicalPlayer: ${playerCount} records`); console.log(` PlayerAlias: ${playerAliasCount} records`); console.log(` CanonicalGame: ${gameCount} records`); } finally { await prisma.$disconnect(); } } main().catch((e) => { console.error('Migration failed:', e); process.exit(1); });