-- Cross-fill scores from lowercase → uppercase SportsGame records
-- Covers NBA, NHL, NFL, MLB

-- ============================================================
-- NBA (already had this, keeping it)
-- ============================================================
WITH nba_map(abbr, full_name) AS (VALUES
  ('ATL', 'Atlanta Hawks'), ('BKN', 'Brooklyn Nets'), ('BOS', 'Boston Celtics'),
  ('CHA', 'Charlotte Hornets'), ('CHI', 'Chicago Bulls'), ('CLE', 'Cleveland Cavaliers'),
  ('DAL', 'Dallas Mavericks'), ('DEN', 'Denver Nuggets'), ('DET', 'Detroit Pistons'),
  ('GSW', 'Golden State Warriors'), ('HOU', 'Houston Rockets'), ('IND', 'Indiana Pacers'),
  ('LAC', 'Los Angeles Clippers'), ('LAL', 'Los Angeles Lakers'),
  ('MEM', 'Memphis Grizzlies'), ('MIA', 'Miami Heat'), ('MIL', 'Milwaukee Bucks'),
  ('MIN', 'Minnesota Timberwolves'), ('NOP', 'New Orleans Pelicans'),
  ('NYK', 'New York Knicks'), ('OKC', 'Oklahoma City Thunder'), ('ORL', 'Orlando Magic'),
  ('PHI', 'Philadelphia 76ers'), ('PHX', 'Phoenix Suns'),
  ('POR', 'Portland Trail Blazers'), ('SAS', 'San Antonio Spurs'),
  ('SAC', 'Sacramento Kings'), ('TOR', 'Toronto Raptors'),
  ('UTA', 'Utah Jazz'), ('WAS', 'Washington Wizards')
),
nba_matched AS (
  SELECT DISTINCT ON (sgo.id)
    sgo.id, src."homeScore", src."awayScore", src.status,
    src."homeQ1", src."homeQ2", src."homeQ3", src."homeQ4", src."homeOT",
    src."awayQ1", src."awayQ2", src."awayQ3", src."awayQ4", src."awayOT"
  FROM "SportsGame" sgo
  JOIN nba_map hm ON hm.abbr = sgo."homeTeam"
  JOIN nba_map am ON am.abbr = sgo."awayTeam"
  JOIN "SportsGame" src ON src.league = 'nba'
    AND src."homeTeam" = hm.full_name AND src."awayTeam" = am.full_name
    AND src."gameDate"::date BETWEEN sgo."gameDate"::date - 1 AND sgo."gameDate"::date + 1
    AND src."homeScore" > 0
  WHERE sgo.league = 'NBA' AND (sgo."homeScore" IS NULL OR sgo."homeScore" = 0)
)
UPDATE "SportsGame" sg
SET "homeScore" = m."homeScore", "awayScore" = m."awayScore",
    status = CASE WHEN m.status = 'final' THEN 'final' ELSE sg.status END,
    "homeQ1" = m."homeQ1", "homeQ2" = m."homeQ2", "homeQ3" = m."homeQ3", "homeQ4" = m."homeQ4", "homeOT" = m."homeOT",
    "awayQ1" = m."awayQ1", "awayQ2" = m."awayQ2", "awayQ3" = m."awayQ3", "awayQ4" = m."awayQ4", "awayOT" = m."awayOT"
FROM nba_matched m WHERE sg.id = m.id;

-- ============================================================
-- NHL: uppercase uses abbreviations, lowercase has mix
-- Match uppercase abbr → lowercase (try both abbr and full name)
-- ============================================================
WITH nhl_map(abbr, full_name) AS (VALUES
  ('ANA', 'Anaheim Ducks'), ('BOS', 'Boston Bruins'), ('BUF', 'Buffalo Sabres'),
  ('CAR', 'Carolina Hurricanes'), ('CBJ', 'Columbus Blue Jackets'), ('CGY', 'Calgary Flames'),
  ('CHI', 'Chicago Blackhawks'), ('COL', 'Colorado Avalanche'), ('DAL', 'Dallas Stars'),
  ('DET', 'Detroit Red Wings'), ('EDM', 'Edmonton Oilers'), ('FLA', 'Florida Panthers'),
  ('LA', 'Los Angeles Kings'), ('LAK', 'Los Angeles Kings'),
  ('MIN', 'Minnesota Wild'), ('MTL', 'Montreal Canadiens'),
  ('NJ', 'New Jersey Devils'), ('NJD', 'New Jersey Devils'),
  ('NSH', 'Nashville Predators'), ('NYI', 'New York Islanders'), ('NYR', 'New York Rangers'),
  ('OTT', 'Ottawa Senators'), ('PHI', 'Philadelphia Flyers'), ('PIT', 'Pittsburgh Penguins'),
  ('SEA', 'Seattle Kraken'), ('SJ', 'San Jose Sharks'), ('SJS', 'San Jose Sharks'),
  ('STL', 'St. Louis Blues'), ('TB', 'Tampa Bay Lightning'), ('TBL', 'Tampa Bay Lightning'),
  ('TOR', 'Toronto Maple Leafs'), ('UTA', 'Utah Hockey Club'), ('UMA', 'Utah Mammoth'),
  ('VAN', 'Vancouver Canucks'), ('VGK', 'Vegas Golden Knights'),
  ('WPG', 'Winnipeg Jets'), ('WSH', 'Washington Capitals')
),
nhl_matched AS (
  SELECT DISTINCT ON (sgo.id)
    sgo.id, src."homeScore", src."awayScore", src.status
  FROM "SportsGame" sgo
  JOIN nhl_map hm ON hm.abbr = sgo."homeTeam"
  JOIN nhl_map am ON am.abbr = sgo."awayTeam"
  JOIN "SportsGame" src ON src.league = 'nhl'
    AND (src."homeTeam" = hm.full_name OR src."homeTeam" = sgo."homeTeam")
    AND (src."awayTeam" = am.full_name OR src."awayTeam" = sgo."awayTeam")
    AND src."gameDate"::date BETWEEN sgo."gameDate"::date - 1 AND sgo."gameDate"::date + 1
    AND src."homeScore" > 0
  WHERE sgo.league = 'NHL' AND (sgo."homeScore" IS NULL OR sgo."homeScore" = 0)
)
UPDATE "SportsGame" sg
SET "homeScore" = m."homeScore", "awayScore" = m."awayScore",
    status = CASE WHEN m.status = 'final' THEN 'final' ELSE sg.status END
FROM nhl_matched m WHERE sg.id = m.id;

-- ============================================================
-- NFL: uppercase uses abbreviations, lowercase uses full names
-- ============================================================
WITH nfl_map(abbr, full_name) AS (VALUES
  ('ARI', 'Arizona Cardinals'), ('ATL', 'Atlanta Falcons'), ('BAL', 'Baltimore Ravens'),
  ('BUF', 'Buffalo Bills'), ('CAR', 'Carolina Panthers'), ('CHI', 'Chicago Bears'),
  ('CIN', 'Cincinnati Bengals'), ('CLE', 'Cleveland Browns'), ('DAL', 'Dallas Cowboys'),
  ('DEN', 'Denver Broncos'), ('DET', 'Detroit Lions'), ('GB', 'Green Bay Packers'),
  ('HOU', 'Houston Texans'), ('IND', 'Indianapolis Colts'), ('JAX', 'Jacksonville Jaguars'),
  ('KC', 'Kansas City Chiefs'), ('LV', 'Las Vegas Raiders'), ('LAC', 'Los Angeles Chargers'),
  ('LAR', 'Los Angeles Rams'), ('MIA', 'Miami Dolphins'), ('MIN', 'Minnesota Vikings'),
  ('NE', 'New England Patriots'), ('NO', 'New Orleans Saints'), ('NYG', 'New York Giants'),
  ('NYJ', 'New York Jets'), ('PHI', 'Philadelphia Eagles'), ('PIT', 'Pittsburgh Steelers'),
  ('SF', 'San Francisco 49ers'), ('SEA', 'Seattle Seahawks'), ('TB', 'Tampa Bay Buccaneers'),
  ('TEN', 'Tennessee Titans'), ('WSH', 'Washington Commanders')
),
nfl_matched AS (
  SELECT DISTINCT ON (sgo.id)
    sgo.id, src."homeScore", src."awayScore", src.status
  FROM "SportsGame" sgo
  JOIN nfl_map hm ON hm.abbr = sgo."homeTeam"
  JOIN nfl_map am ON am.abbr = sgo."awayTeam"
  JOIN "SportsGame" src ON src.league = 'nfl'
    AND (src."homeTeam" = hm.full_name OR src."homeTeam" = sgo."homeTeam")
    AND (src."awayTeam" = am.full_name OR src."awayTeam" = sgo."awayTeam")
    AND src."gameDate"::date BETWEEN sgo."gameDate"::date - 1 AND sgo."gameDate"::date + 1
    AND src."homeScore" > 0
  WHERE sgo.league = 'NFL' AND (sgo."homeScore" IS NULL OR sgo."homeScore" = 0)
)
UPDATE "SportsGame" sg
SET "homeScore" = m."homeScore", "awayScore" = m."awayScore",
    status = CASE WHEN m.status = 'final' THEN 'final' ELSE sg.status END
FROM nfl_matched m WHERE sg.id = m.id;

-- ============================================================
-- MLB: both use abbreviations (uppercase MLB and lowercase mlb)
-- ============================================================
WITH mlb_map(abbr, full_name) AS (VALUES
  ('ARI', 'Arizona Diamondbacks'), ('ATL', 'Atlanta Braves'), ('BAL', 'Baltimore Orioles'),
  ('BOS', 'Boston Red Sox'), ('CHC', 'Chicago Cubs'), ('CWS', 'Chicago White Sox'),
  ('CIN', 'Cincinnati Reds'), ('CLE', 'Cleveland Guardians'), ('COL', 'Colorado Rockies'),
  ('DET', 'Detroit Tigers'), ('HOU', 'Houston Astros'), ('KC', 'Kansas City Royals'),
  ('LAA', 'Los Angeles Angels'), ('LAD', 'Los Angeles Dodgers'), ('MIA', 'Miami Marlins'),
  ('MIL', 'Milwaukee Brewers'), ('MIN', 'Minnesota Twins'), ('NYM', 'New York Mets'),
  ('NYY', 'New York Yankees'), ('OAK', 'Oakland Athletics'),
  ('PHI', 'Philadelphia Phillies'), ('PIT', 'Pittsburgh Pirates'), ('SD', 'San Diego Padres'),
  ('SF', 'San Francisco Giants'), ('SEA', 'Seattle Mariners'), ('STL', 'St. Louis Cardinals'),
  ('TB', 'Tampa Bay Rays'), ('TEX', 'Texas Rangers'), ('TOR', 'Toronto Blue Jays'),
  ('WSH', 'Washington Nationals')
),
mlb_matched AS (
  SELECT DISTINCT ON (sgo.id)
    sgo.id, src."homeScore", src."awayScore", src.status
  FROM "SportsGame" sgo
  JOIN "SportsGame" src ON src.league = 'mlb'
    AND (src."homeTeam" = sgo."homeTeam"
         OR src."homeTeam" IN (SELECT full_name FROM mlb_map WHERE abbr = sgo."homeTeam"))
    AND (src."awayTeam" = sgo."awayTeam"
         OR src."awayTeam" IN (SELECT full_name FROM mlb_map WHERE abbr = sgo."awayTeam"))
    AND src."gameDate"::date BETWEEN sgo."gameDate"::date - 1 AND sgo."gameDate"::date + 1
    AND src."homeScore" > 0
  WHERE sgo.league = 'MLB' AND (sgo."homeScore" IS NULL OR sgo."homeScore" = 0)
)
UPDATE "SportsGame" sg
SET "homeScore" = m."homeScore", "awayScore" = m."awayScore",
    status = CASE WHEN m.status = 'final' THEN 'final' ELSE sg.status END
FROM mlb_matched m WHERE sg.id = m.id;
