-- Function to recalculate league standings
CREATE OR REPLACE FUNCTION recalc_league_standings()
RETURNS TRIGGER AS $$
DECLARE
  v_league_id TEXT;
  v_home_club_id TEXT;
  v_away_club_id TEXT;
  v_rank INT;
  club_record RECORD;
BEGIN
  -- Get match details from matches table
  SELECT league_id, home_club_id, away_club_id
  INTO v_league_id, v_home_club_id, v_away_club_id
  FROM matches
  WHERE id = NEW.match_id;
  
  -- Update home club stats
  UPDATE league_clubs
  SET 
    matches_played = (
      SELECT COUNT(DISTINCT m.id)
      FROM matches m
      JOIN match_results mr ON mr.match_id = m.id
      WHERE (m.home_club_id = v_home_club_id OR m.away_club_id = v_home_club_id)
        AND m.league_id = v_league_id
        AND mr.home_score IS NOT NULL
        AND mr.away_score IS NOT NULL
    ),
    wins = (
      SELECT COUNT(*)
      FROM matches m
      JOIN match_results mr ON mr.match_id = m.id
      WHERE m.league_id = v_league_id
        AND mr.home_score IS NOT NULL
        AND mr.away_score IS NOT NULL
        AND (
          (m.home_club_id = v_home_club_id AND mr.home_score > mr.away_score)
          OR (m.away_club_id = v_home_club_id AND mr.away_score > mr.home_score)
        )
    ),
    draws = (
      SELECT COUNT(*)
      FROM matches m
      JOIN match_results mr ON mr.match_id = m.id
      WHERE m.league_id = v_league_id
        AND mr.home_score IS NOT NULL
        AND mr.away_score IS NOT NULL
        AND mr.home_score = mr.away_score
        AND (m.home_club_id = v_home_club_id OR m.away_club_id = v_home_club_id)
    ),
    losses = (
      SELECT COUNT(*)
      FROM matches m
      JOIN match_results mr ON mr.match_id = m.id
      WHERE m.league_id = v_league_id
        AND mr.home_score IS NOT NULL
        AND mr.away_score IS NOT NULL
        AND (
          (m.home_club_id = v_home_club_id AND mr.home_score < mr.away_score)
          OR (m.away_club_id = v_home_club_id AND mr.away_score < mr.home_score)
        )
    ),
    goals_for = (
      SELECT COALESCE(SUM(
        CASE 
          WHEN m.home_club_id = v_home_club_id THEN mr.home_score
          WHEN m.away_club_id = v_home_club_id THEN mr.away_score
          ELSE 0
        END
      ), 0)
      FROM matches m
      JOIN match_results mr ON mr.match_id = m.id
      WHERE (m.home_club_id = v_home_club_id OR m.away_club_id = v_home_club_id)
        AND m.league_id = v_league_id
        AND mr.home_score IS NOT NULL
        AND mr.away_score IS NOT NULL
    ),
    goals_against = (
      SELECT COALESCE(SUM(
        CASE 
          WHEN m.home_club_id = v_home_club_id THEN mr.away_score
          WHEN m.away_club_id = v_home_club_id THEN mr.home_score
          ELSE 0
        END
      ), 0)
      FROM matches m
      JOIN match_results mr ON mr.match_id = m.id
      WHERE (m.home_club_id = v_home_club_id OR m.away_club_id = v_home_club_id)
        AND m.league_id = v_league_id
        AND mr.home_score IS NOT NULL
        AND mr.away_score IS NOT NULL
    ),
    points = (
      SELECT COALESCE(SUM(
        CASE 
          WHEN mr.home_score > mr.away_score AND m.home_club_id = v_home_club_id THEN 3
          WHEN mr.away_score > mr.home_score AND m.away_club_id = v_home_club_id THEN 3
          WHEN mr.home_score = mr.away_score AND (m.home_club_id = v_home_club_id OR m.away_club_id = v_home_club_id) THEN 1
          ELSE 0
        END
      ), 0)
      FROM matches m
      JOIN match_results mr ON mr.match_id = m.id
      WHERE (m.home_club_id = v_home_club_id OR m.away_club_id = v_home_club_id)
        AND m.league_id = v_league_id
        AND mr.home_score IS NOT NULL
        AND mr.away_score IS NOT NULL
    )
  WHERE league_id = v_league_id AND club_id = v_home_club_id;

  -- Update away club stats
  UPDATE league_clubs
  SET 
    matches_played = (
      SELECT COUNT(DISTINCT m.id)
      FROM matches m
      JOIN match_results mr ON mr.match_id = m.id
      WHERE (m.home_club_id = v_away_club_id OR m.away_club_id = v_away_club_id)
        AND m.league_id = v_league_id
        AND mr.home_score IS NOT NULL
        AND mr.away_score IS NOT NULL
    ),
    wins = (
      SELECT COUNT(*)
      FROM matches m
      JOIN match_results mr ON mr.match_id = m.id
      WHERE m.league_id = v_league_id
        AND mr.home_score IS NOT NULL
        AND mr.away_score IS NOT NULL
        AND (
          (m.home_club_id = v_away_club_id AND mr.home_score > mr.away_score)
          OR (m.away_club_id = v_away_club_id AND mr.away_score > mr.home_score)
        )
    ),
    draws = (
      SELECT COUNT(*)
      FROM matches m
      JOIN match_results mr ON mr.match_id = m.id
      WHERE m.league_id = v_league_id
        AND mr.home_score IS NOT NULL
        AND mr.away_score IS NOT NULL
        AND mr.home_score = mr.away_score
        AND (m.home_club_id = v_away_club_id OR m.away_club_id = v_away_club_id)
    ),
    losses = (
      SELECT COUNT(*)
      FROM matches m
      JOIN match_results mr ON mr.match_id = m.id
      WHERE m.league_id = v_league_id
        AND mr.home_score IS NOT NULL
        AND mr.away_score IS NOT NULL
        AND (
          (m.home_club_id = v_away_club_id AND mr.home_score < mr.away_score)
          OR (m.away_club_id = v_away_club_id AND mr.away_score < mr.home_score)
        )
    ),
    goals_for = (
      SELECT COALESCE(SUM(
        CASE 
          WHEN m.home_club_id = v_away_club_id THEN mr.home_score
          WHEN m.away_club_id = v_away_club_id THEN mr.away_score
          ELSE 0
        END
      ), 0)
      FROM matches m
      JOIN match_results mr ON mr.match_id = m.id
      WHERE (m.home_club_id = v_away_club_id OR m.away_club_id = v_away_club_id)
        AND m.league_id = v_league_id
        AND mr.home_score IS NOT NULL
        AND mr.away_score IS NOT NULL
    ),
    goals_against = (
      SELECT COALESCE(SUM(
        CASE 
          WHEN m.home_club_id = v_away_club_id THEN mr.away_score
          WHEN m.away_club_id = v_away_club_id THEN mr.home_score
          ELSE 0
        END
      ), 0)
      FROM matches m
      JOIN match_results mr ON mr.match_id = m.id
      WHERE (m.home_club_id = v_away_club_id OR m.away_club_id = v_away_club_id)
        AND m.league_id = v_league_id
        AND mr.home_score IS NOT NULL
        AND mr.away_score IS NOT NULL
    ),
    points = (
      SELECT COALESCE(SUM(
        CASE 
          WHEN mr.home_score > mr.away_score AND m.home_club_id = v_away_club_id THEN 3
          WHEN mr.away_score > mr.home_score AND m.away_club_id = v_away_club_id THEN 3
          WHEN mr.home_score = mr.away_score AND (m.home_club_id = v_away_club_id OR m.away_club_id = v_away_club_id) THEN 1
          ELSE 0
        END
      ), 0)
      FROM matches m
      JOIN match_results mr ON mr.match_id = m.id
      WHERE (m.home_club_id = v_away_club_id OR m.away_club_id = v_away_club_id)
        AND m.league_id = v_league_id
        AND mr.home_score IS NOT NULL
        AND mr.away_score IS NOT NULL
    )
  WHERE league_id = v_league_id AND club_id = v_away_club_id;

  -- Recalculate positions for all clubs in the league
  v_rank := 1;
  FOR club_record IN 
    SELECT id
    FROM league_clubs
    WHERE league_id = v_league_id
    ORDER BY points DESC, (goals_for - goals_against) DESC, goals_for DESC, id ASC
  LOOP
    UPDATE league_clubs
    SET position = v_rank
    WHERE id = club_record.id;
    
    v_rank := v_rank + 1;
  END LOOP;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger on match_results table
CREATE TRIGGER update_standings_after_result
AFTER INSERT OR UPDATE ON match_results
FOR EACH ROW
EXECUTE FUNCTION recalc_league_standings();