-- Standings view (league mode)
CREATE OR REPLACE VIEW competition_standings AS
SELECT
  t.competition_id,
  t.id AS team_id,
  t.name AS team_name,
  t.logo_path,
  COUNT(m.id) FILTER (WHERE m.status = 'completed') AS played,
  COUNT(m.id) FILTER (
    WHERE m.status = 'completed'
    AND (
      (m.home_team_id = t.id AND m.home_score > m.away_score)
      OR (m.away_team_id = t.id AND m.away_score > m.home_score)
    )
  ) AS won,
  COUNT(m.id) FILTER (
    WHERE m.status = 'completed' AND m.home_score = m.away_score
  ) AS drawn,
  COUNT(m.id) FILTER (
    WHERE m.status = 'completed'
    AND (
      (m.home_team_id = t.id AND m.home_score < m.away_score)
      OR (m.away_team_id = t.id AND m.away_score < m.home_score)
    )
  ) AS lost,
  COALESCE(SUM(
    CASE
      WHEN m.status = 'completed' AND m.home_team_id = t.id THEN m.home_score
      WHEN m.status = 'completed' AND m.away_team_id = t.id THEN m.away_score
      ELSE 0
    END
  ), 0)::INT AS goals_for,
  COALESCE(SUM(
    CASE
      WHEN m.status = 'completed' AND m.home_team_id = t.id THEN m.away_score
      WHEN m.status = 'completed' AND m.away_team_id = t.id THEN m.home_score
      ELSE 0
    END
  ), 0)::INT AS goals_against,
  COALESCE(SUM(
    CASE
      WHEN m.status = 'completed' AND m.home_team_id = t.id THEN m.home_score - m.away_score
      WHEN m.status = 'completed' AND m.away_team_id = t.id THEN m.away_score - m.home_score
      ELSE 0
    END
  ), 0)::INT AS goal_difference,
  COALESCE(SUM(
    CASE
      WHEN m.status <> 'completed' THEN 0
      WHEN m.home_score = m.away_score THEN 1
      WHEN m.home_team_id = t.id AND m.home_score > m.away_score THEN 3
      WHEN m.away_team_id = t.id AND m.away_score > m.home_score THEN 3
      ELSE 0
    END
  ), 0)::INT AS points
FROM teams t
LEFT JOIN matches m ON m.competition_id = t.competition_id
  AND (m.home_team_id = t.id OR m.away_team_id = t.id)
GROUP BY t.competition_id, t.id, t.name, t.logo_path;

-- Player competition stats
CREATE OR REPLACE VIEW player_competition_stats AS
SELECT
  mps.player_id,
  p.display_name AS player_name,
  m.competition_id,
  mps.team_id,
  COUNT(DISTINCT mps.match_id) AS appearances,
  COALESCE(SUM(mps.goals), 0)::INT AS goals,
  COALESCE(SUM(mps.assists), 0)::INT AS assists
FROM match_player_stats mps
JOIN matches m ON m.id = mps.match_id AND m.status = 'completed'
JOIN players p ON p.id = mps.player_id
GROUP BY mps.player_id, p.display_name, m.competition_id, mps.team_id;

-- Team match results for charts
CREATE OR REPLACE VIEW team_match_results AS
SELECT
  t.id AS team_id,
  t.competition_id,
  m.id AS match_id,
  m.matchday,
  m.scheduled_at,
  CASE WHEN m.home_team_id = t.id THEN ht.name ELSE at.name END AS opponent_name,
  CASE WHEN m.home_team_id = t.id THEN at.logo_path ELSE ht.logo_path END AS opponent_logo,
  CASE WHEN m.home_team_id = t.id THEN m.home_score ELSE m.away_score END AS goals_for,
  CASE WHEN m.home_team_id = t.id THEN m.away_score ELSE m.home_score END AS goals_against,
  CASE
    WHEN m.home_score = m.away_score THEN 'D'
    WHEN (m.home_team_id = t.id AND m.home_score > m.away_score)
      OR (m.away_team_id = t.id AND m.away_score > m.home_score) THEN 'W'
    ELSE 'L'
  END AS result,
  CASE
    WHEN m.home_score = m.away_score THEN 1
    WHEN (m.home_team_id = t.id AND m.home_score > m.away_score)
      OR (m.away_team_id = t.id AND m.away_score > m.home_score) THEN 3
    ELSE 0
  END AS points_earned
FROM teams t
JOIN matches m ON m.competition_id = t.competition_id
  AND m.status = 'completed'
  AND (m.home_team_id = t.id OR m.away_team_id = t.id)
JOIN teams ht ON ht.id = m.home_team_id
JOIN teams at ON at.id = m.away_team_id;