-- Hourly opponent backfill for PlayerGameMetric (recent 7 days, all sports)

-- ============================================================
-- NBA: PGM uses abbreviations, SportsGame(nba) uses full names
-- ============================================================
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'),
  ('GS', 'Golden State Warriors'), ('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'), ('NO', 'New Orleans Pelicans'),
  ('NOP', 'New Orleans Pelicans'), ('NY', 'New York Knicks'), ('NYK', 'New York Knicks'),
  ('OKC', 'Oklahoma City Thunder'), ('ORL', 'Orlando Magic'),
  ('PHI', 'Philadelphia 76ers'), ('PHX', 'Phoenix Suns'),
  ('POR', 'Portland Trail Blazers'), ('SA', 'San Antonio Spurs'), ('SAS', 'San Antonio Spurs'),
  ('SAC', 'Sacramento Kings'), ('TOR', 'Toronto Raptors'),
  ('UTA', 'Utah Jazz'), ('UTAH', 'Utah Jazz'),
  ('WAS', 'Washington Wizards'), ('WSH', 'Washington Wizards')
),
nba_reverse(full_name, abbr) AS (VALUES
  ('Atlanta Hawks', 'ATL'), ('Brooklyn Nets', 'BKN'), ('Boston Celtics', 'BOS'),
  ('Charlotte Hornets', 'CHA'), ('Chicago Bulls', 'CHI'), ('Cleveland Cavaliers', 'CLE'),
  ('Dallas Mavericks', 'DAL'), ('Denver Nuggets', 'DEN'), ('Detroit Pistons', 'DET'),
  ('Golden State Warriors', 'GSW'), ('Houston Rockets', 'HOU'), ('Indiana Pacers', 'IND'),
  ('Los Angeles Clippers', 'LAC'), ('Los Angeles Lakers', 'LAL'),
  ('Memphis Grizzlies', 'MEM'), ('Miami Heat', 'MIA'), ('Milwaukee Bucks', 'MIL'),
  ('Minnesota Timberwolves', 'MIN'), ('New Orleans Pelicans', 'NOP'),
  ('New York Knicks', 'NYK'), ('Oklahoma City Thunder', 'OKC'), ('Orlando Magic', 'ORL'),
  ('Philadelphia 76ers', 'PHI'), ('Phoenix Suns', 'PHX'),
  ('Portland Trail Blazers', 'POR'), ('San Antonio Spurs', 'SAS'),
  ('Sacramento Kings', 'SAC'), ('Toronto Raptors', 'TOR'),
  ('Utah Jazz', 'UTA'), ('Washington Wizards', 'WAS')
),
nba_matched AS (
  SELECT DISTINCT ON (pgm.id)
    pgm.id,
    CASE
      WHEN tm.full_name = sg."homeTeam" THEN COALESCE(rm.abbr, sg."awayTeam")
      WHEN tm.full_name = sg."awayTeam" THEN COALESCE(rm2.abbr, sg."homeTeam")
    END as opponent_val
  FROM "PlayerGameMetric" pgm
  JOIN nba_map tm ON tm.abbr = pgm.team
  JOIN "SportsGame" sg ON sg.league = 'nba'
    AND sg."gameDate"::date BETWEEN pgm."gameDate"::date AND pgm."gameDate"::date + 1
    AND (tm.full_name = sg."homeTeam" OR tm.full_name = sg."awayTeam")
  LEFT JOIN nba_reverse rm ON rm.full_name = sg."awayTeam"
  LEFT JOIN nba_reverse rm2 ON rm2.full_name = sg."homeTeam"
  WHERE pgm.league = 'nba'
    AND (pgm.opponent IS NULL OR pgm.opponent = '')
    AND pgm."gameDate" >= CURRENT_DATE - interval '7 days'
)
UPDATE "PlayerGameMetric" pgm
SET opponent = m.opponent_val
FROM nba_matched m
WHERE pgm.id = m.id AND m.opponent_val IS NOT NULL AND m.opponent_val != '';

-- ============================================================
-- NHL: PGM uses abbreviations, SportsGame(nhl) uses full names
-- ============================================================
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'),
  ('VAN', 'Vancouver Canucks'), ('VGK', 'Vegas Golden Knights'),
  ('WPG', 'Winnipeg Jets'), ('WSH', 'Washington Capitals')
),
nhl_reverse(full_name, abbr) AS (VALUES
  ('Anaheim Ducks', 'ANA'), ('Boston Bruins', 'BOS'), ('Buffalo Sabres', 'BUF'),
  ('Carolina Hurricanes', 'CAR'), ('Columbus Blue Jackets', 'CBJ'), ('Calgary Flames', 'CGY'),
  ('Chicago Blackhawks', 'CHI'), ('Colorado Avalanche', 'COL'), ('Dallas Stars', 'DAL'),
  ('Detroit Red Wings', 'DET'), ('Edmonton Oilers', 'EDM'), ('Florida Panthers', 'FLA'),
  ('Los Angeles Kings', 'LAK'), ('Minnesota Wild', 'MIN'), ('Montreal Canadiens', 'MTL'),
  ('New Jersey Devils', 'NJD'), ('Nashville Predators', 'NSH'), ('New York Islanders', 'NYI'),
  ('New York Rangers', 'NYR'), ('Ottawa Senators', 'OTT'), ('Philadelphia Flyers', 'PHI'),
  ('Pittsburgh Penguins', 'PIT'), ('Seattle Kraken', 'SEA'), ('San Jose Sharks', 'SJS'),
  ('St. Louis Blues', 'STL'), ('Tampa Bay Lightning', 'TBL'), ('Toronto Maple Leafs', 'TOR'),
  ('Utah Hockey Club', 'UTA'), ('Vancouver Canucks', 'VAN'), ('Vegas Golden Knights', 'VGK'),
  ('Winnipeg Jets', 'WPG'), ('Washington Capitals', 'WSH')
),
nhl_matched AS (
  SELECT DISTINCT ON (pgm.id)
    pgm.id,
    CASE
      WHEN tm.full_name = sg."homeTeam" THEN COALESCE(rm.abbr, sg."awayTeam")
      WHEN tm.full_name = sg."awayTeam" THEN COALESCE(rm2.abbr, sg."homeTeam")
    END as opponent_val
  FROM "PlayerGameMetric" pgm
  JOIN nhl_map tm ON tm.abbr = pgm.team
  JOIN "SportsGame" sg ON sg.league = 'nhl'
    AND sg."gameDate"::date BETWEEN pgm."gameDate"::date AND pgm."gameDate"::date + 1
    AND (tm.full_name = sg."homeTeam" OR tm.full_name = sg."awayTeam")
  LEFT JOIN nhl_reverse rm ON rm.full_name = sg."awayTeam"
  LEFT JOIN nhl_reverse rm2 ON rm2.full_name = sg."homeTeam"
  WHERE pgm.league = 'nhl'
    AND (pgm.opponent IS NULL OR pgm.opponent = '')
    AND pgm."gameDate" >= CURRENT_DATE - interval '7 days'
)
UPDATE "PlayerGameMetric" pgm
SET opponent = m.opponent_val
FROM nhl_matched m
WHERE pgm.id = m.id AND m.opponent_val IS NOT NULL AND m.opponent_val != '';

-- ============================================================
-- MLB: PGM uses full names, SportsGame(mlb) uses full names
-- Direct match where both have full names
-- ============================================================
WITH mlb_matched AS (
  SELECT DISTINCT ON (pgm.id)
    pgm.id,
    CASE
      WHEN pgm.team = sg."homeTeam" THEN sg."awayTeam"
      WHEN pgm.team = sg."awayTeam" THEN sg."homeTeam"
    END as opponent_val
  FROM "PlayerGameMetric" pgm
  JOIN "SportsGame" sg ON sg.league = 'mlb'
    AND sg."gameDate"::date BETWEEN pgm."gameDate"::date AND pgm."gameDate"::date + 1
    AND (pgm.team = sg."homeTeam" OR pgm.team = sg."awayTeam")
    AND LENGTH(sg."homeTeam") > 4
  WHERE pgm.league = 'mlb'
    AND (pgm.opponent IS NULL OR pgm.opponent = '')
    AND pgm."gameDate" >= CURRENT_DATE - interval '7 days'
    AND LENGTH(pgm.team) > 4
)
UPDATE "PlayerGameMetric" pgm
SET opponent = m.opponent_val
FROM mlb_matched m
WHERE pgm.id = m.id AND m.opponent_val IS NOT NULL AND m.opponent_val != '';

-- MLB: PGM abbreviations → full name SportsGame match
WITH mlb_abbr(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_matched2 AS (
  SELECT DISTINCT ON (pgm.id)
    pgm.id,
    CASE
      WHEN am.full_name = sg."homeTeam" THEN sg."awayTeam"
      WHEN am.full_name = sg."awayTeam" THEN sg."homeTeam"
    END as opponent_val
  FROM "PlayerGameMetric" pgm
  JOIN mlb_abbr am ON am.abbr = pgm.team
  JOIN "SportsGame" sg ON sg.league = 'mlb'
    AND sg."gameDate"::date BETWEEN pgm."gameDate"::date AND pgm."gameDate"::date + 1
    AND (am.full_name = sg."homeTeam" OR am.full_name = sg."awayTeam")
  WHERE pgm.league = 'mlb'
    AND (pgm.opponent IS NULL OR pgm.opponent = '')
    AND pgm."gameDate" >= CURRENT_DATE - interval '7 days'
    AND LENGTH(pgm.team) <= 4
)
UPDATE "PlayerGameMetric" pgm
SET opponent = m.opponent_val
FROM mlb_matched2 m
WHERE pgm.id = m.id AND m.opponent_val IS NOT NULL AND m.opponent_val != '';

-- ============================================================
-- NFL: PGM uses abbreviations, SportsGame(nfl) 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_reverse(full_name, abbr) AS (VALUES
  ('Arizona Cardinals', 'ARI'), ('Atlanta Falcons', 'ATL'), ('Baltimore Ravens', 'BAL'),
  ('Buffalo Bills', 'BUF'), ('Carolina Panthers', 'CAR'), ('Chicago Bears', 'CHI'),
  ('Cincinnati Bengals', 'CIN'), ('Cleveland Browns', 'CLE'), ('Dallas Cowboys', 'DAL'),
  ('Denver Broncos', 'DEN'), ('Detroit Lions', 'DET'), ('Green Bay Packers', 'GB'),
  ('Houston Texans', 'HOU'), ('Indianapolis Colts', 'IND'), ('Jacksonville Jaguars', 'JAX'),
  ('Kansas City Chiefs', 'KC'), ('Las Vegas Raiders', 'LV'), ('Los Angeles Chargers', 'LAC'),
  ('Los Angeles Rams', 'LAR'), ('Miami Dolphins', 'MIA'), ('Minnesota Vikings', 'MIN'),
  ('New England Patriots', 'NE'), ('New Orleans Saints', 'NO'), ('New York Giants', 'NYG'),
  ('New York Jets', 'NYJ'), ('Philadelphia Eagles', 'PHI'), ('Pittsburgh Steelers', 'PIT'),
  ('San Francisco 49ers', 'SF'), ('Seattle Seahawks', 'SEA'), ('Tampa Bay Buccaneers', 'TB'),
  ('Tennessee Titans', 'TEN'), ('Washington Commanders', 'WSH')
),
nfl_matched AS (
  SELECT DISTINCT ON (pgm.id)
    pgm.id,
    CASE
      WHEN nm.full_name = sg."homeTeam" THEN COALESCE(rm.abbr, sg."awayTeam")
      WHEN nm.full_name = sg."awayTeam" THEN COALESCE(rm2.abbr, sg."homeTeam")
    END as opponent_val
  FROM "PlayerGameMetric" pgm
  JOIN nfl_map nm ON nm.abbr = pgm.team
  JOIN "SportsGame" sg ON sg.league = 'nfl'
    AND sg."gameDate"::date BETWEEN pgm."gameDate"::date AND pgm."gameDate"::date + 1
    AND (nm.full_name = sg."homeTeam" OR nm.full_name = sg."awayTeam")
  LEFT JOIN nfl_reverse rm ON rm.full_name = sg."awayTeam"
  LEFT JOIN nfl_reverse rm2 ON rm2.full_name = sg."homeTeam"
  WHERE pgm.league = 'nfl'
    AND (pgm.opponent IS NULL OR pgm.opponent = '')
    AND pgm."gameDate" >= CURRENT_DATE - interval '7 days'
)
UPDATE "PlayerGameMetric" pgm
SET opponent = m.opponent_val
FROM nfl_matched m
WHERE pgm.id = m.id AND m.opponent_val IS NOT NULL AND m.opponent_val != '';
