-- Sync scores from nba (ESPN/TheSportsDB) records to NBA (SGO) records
-- Run periodically to keep SGO game records updated with final scores

WITH team_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')
),
matched AS (
  SELECT DISTINCT ON (sgo.id)
    sgo.id,
    src."homeScore",
    src."awayScore",
    src.status as 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 team_map hm ON hm.abbr = sgo."homeTeam"
  JOIN team_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.src_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 matched m
WHERE sg.id = m.id;
