-- Query 2 - FINAL: Handle duplicate team records in Baselight
WITH team_season_performance AS (
  SELECT 
    ms.team_id,
    -- Use DISTINCT to get unique team info (handles duplicate team records)
    MAX(t.name) as team_name,
    MAX(c.name) as league,
    MAX(c.country) as country,
    -- Get xG directly from match_stats (no team table join duplication)
    SUM(CAST(ms.value AS DOUBLE)) as total_xg,
    -- Calculate goals and points
    SUM(CASE 
      WHEN m.home_team_id = ms.team_id THEN COALESCE(m.home_score, 0)
      WHEN m.away_team_id = ms.team_id THEN COALESCE(m.away_score, 0)
      ELSE 0
    END) as total_goals,
    SUM(CASE 
      WHEN m.home_team_id = ms.team_id AND m.home_score > m.away_score THEN 3
      WHEN m.away_team_id = ms.team_id AND m.away_score > m.home_score THEN 3
      WHEN m.home_score = m.away_score THEN 1
      ELSE 0
    END) as total_points,
    SUM(CASE 
      WHEN m.home_team_id = ms.team_id THEN COALESCE(m.home_score, 0) - COALESCE(m.away_score, 0)
      WHEN m.away_team_id = ms.team_id THEN COALESCE(m.away_score, 0) - COALESCE(m.home_score, 0)
      ELSE 0
    END) as goal_difference,
    COUNT(DISTINCT ms.match_id) as games_played
  FROM "@blt.ultimate_soccer_dataset.match_stats" ms
  JOIN "@blt.ultimate_soccer_dataset.matches" m ON ms.match_id = m.match_id
  JOIN "@blt.ultimate_soccer_dataset.seasons" s ON m.season_id = s.season_id
  JOIN "@blt.ultimate_soccer_dataset.competitions" c ON s.competition_id = c.competition_id
  -- Use DISTINCT to avoid duplicate team issues
  JOIN (SELECT DISTINCT team_id, name, country FROM "@blt.ultimate_soccer_dataset.teams") t ON ms.team_id = t.team_id
  WHERE s.season_id IN (
    '8a5c7169-9664-5691-9297-6d41a5d72ec9', -- La Liga 2024/25
    '49d5dcd6-7a63-5354-8774-c6cdfa41d771', -- Ligue 1 2024/25
    'c9251047-c6a9-56bf-9861-60bcb289bf10', -- Premier League 2024/25
    '7b34e935-9086-5c7d-852b-a82ba18c31be', -- Primeira Liga 2024/25
  )
    AND c.name IN ('Premier League', 'La Liga', 'Serie A', 'Bundesliga', 'Ligue 1', 'Primeira Liga')
    AND m.status = 'Match Finished'
    AND ms.type = 'expected_goals'
  GROUP BY ms.team_id
)
SELECT 
  team_name,
  league,
  country,
  ROUND(total_xg, 2) as total_xg,
  total_points,
  goal_difference,
  total_goals,
  games_played,
  RANK() OVER (PARTITION BY league ORDER BY total_points DESC, goal_difference DESC) as current_position
FROM team_season_performance
WHERE games_played >= 2
ORDER BY total_xg DESC;