-- Drop the old trigger
DROP TRIGGER IF EXISTS update_standings_after_result ON match_results;

-- Drop the old function
DROP FUNCTION IF EXISTS recalc_league_standings();

-- Create function with correct camelCase column names
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 using camelCase column names
  SELECT "leagueId", "homeClubId", "awayClubId"
  INTO v_league_id, v_home_club_id, v_away_club_id
  FROM matches
  WHERE id = NEW."matchId";

  -- Update home club stats
  UPDATE league_clubs
  SET
    "matchesPlayed" = (
      SELECT COUNT(DISTINCT m.id)
      FROM matches m
      JOIN match_results mr ON mr."matchId" = m.id
      WHERE (m."homeClubId" = v_home_club_id OR m."awayClubId" = v_home_club_id)
        AND m."leagueId" = v_league_id
        AND mr."homeScore" IS NOT NULL
        AND mr."awayScore" IS NOT NULL
    ),
    wins = (
      SELECT COUNT(*)
      FROM matches m
      JOIN match_results mr ON mr."matchId" = m.id
      WHERE m."leagueId" = v_league_id
        AND mr."homeScore" IS NOT NULL
        AND mr."awayScore" IS NOT NULL
        AND (
          (m."homeClubId" = v_home_club_id AND mr."homeScore" > mr."awayScore")
          OR (m."awayClubId" = v_home_club_id AND mr."awayScore" > mr."homeScore")
        )
    ),
    draws = (
      SELECT COUNT(*)
      FROM matches m
      JOIN match_results mr ON mr."matchId" = m.id
      WHERE m."leagueId" = v_league_id
        AND mr."homeScore" IS NOT NULL
        AND mr."awayScore" IS NOT NULL
        AND mr."homeScore" = mr."awayScore"
        AND (m."homeClubId" = v_home_club_id OR m."awayClubId" = v_home_club_id)
    ),
    losses = (
      SELECT COUNT(*)
      FROM matches m
      JOIN match_results mr ON mr."matchId" = m.id
      WHERE m."leagueId" = v_league_id
        AND mr."homeScore" IS NOT NULL
        AND mr."awayScore" IS NOT NULL
        AND (
          (m."homeClubId" = v_home_club_id AND mr."homeScore" < mr."awayScore")
          OR (m."awayClubId" = v_home_club_id AND mr."awayScore" < mr."homeScore")
        )
    ),
    "goalsFor" = (
      SELECT COALESCE(SUM(
        CASE
          WHEN m."homeClubId" = v_home_club_id THEN mr."homeScore"
          WHEN m."awayClubId" = v_home_club_id THEN mr."awayScore"
          ELSE 0
        END
      ), 0)
      FROM matches m
      JOIN match_results mr ON mr."matchId" = m.id
      WHERE (m."homeClubId" = v_home_club_id OR m."awayClubId" = v_home_club_id)
        AND m."leagueId" = v_league_id
        AND mr."homeScore" IS NOT NULL
        AND mr."awayScore" IS NOT NULL
    ),
    "goalsAgainst" = (
      SELECT COALESCE(SUM(
        CASE
          WHEN m."homeClubId" = v_home_club_id THEN mr."awayScore"
          WHEN m."awayClubId" = v_home_club_id THEN mr."homeScore"
          ELSE 0
        END
      ), 0)
      FROM matches m
      JOIN match_results mr ON mr."matchId" = m.id
      WHERE (m."homeClubId" = v_home_club_id OR m."awayClubId" = v_home_club_id)
        AND m."leagueId" = v_league_id
        AND mr."homeScore" IS NOT NULL
        AND mr."awayScore" IS NOT NULL
    ),
    points = (
      SELECT COALESCE(SUM(
        CASE
          WHEN mr."homeScore" > mr."awayScore" AND m."homeClubId" = v_home_club_id THEN 3
          WHEN mr."awayScore" > mr."homeScore" AND m."awayClubId" = v_home_club_id THEN 3
          WHEN mr."homeScore" = mr."awayScore" AND (m."homeClubId" = v_home_club_id OR m."awayClubId" = v_home_club_id) THEN 1
          ELSE 0
        END
      ), 0)
      FROM matches m
      JOIN match_results mr ON mr."matchId" = m.id
      WHERE (m."homeClubId" = v_home_club_id OR m."awayClubId" = v_home_club_id)
        AND m."leagueId" = v_league_id
        AND mr."homeScore" IS NOT NULL
        AND mr."awayScore" IS NOT NULL
    )
  WHERE "leagueId" = v_league_id AND "clubId" = v_home_club_id;

  -- Update away club stats
  UPDATE league_clubs
  SET
    "matchesPlayed" = (
      SELECT COUNT(DISTINCT m.id)
      FROM matches m
      JOIN match_results mr ON mr."matchId" = m.id
      WHERE (m."homeClubId" = v_away_club_id OR m."awayClubId" = v_away_club_id)
        AND m."leagueId" = v_league_id
        AND mr."homeScore" IS NOT NULL
        AND mr."awayScore" IS NOT NULL
    ),
    wins = (
      SELECT COUNT(*)
      FROM matches m
      JOIN match_results mr ON mr."matchId" = m.id
      WHERE m."leagueId" = v_league_id
        AND mr."homeScore" IS NOT NULL
        AND mr."awayScore" IS NOT NULL
        AND (
          (m."homeClubId" = v_away_club_id AND mr."homeScore" > mr."awayScore")
          OR (m."awayClubId" = v_away_club_id AND mr."awayScore" > mr."homeScore")
        )
    ),
    draws = (
      SELECT COUNT(*)
      FROM matches m
      JOIN match_results mr ON mr."matchId" = m.id
      WHERE m."leagueId" = v_league_id
        AND mr."homeScore" IS NOT NULL
        AND mr."awayScore" IS NOT NULL
        AND mr."homeScore" = mr."awayScore"
        AND (m."homeClubId" = v_away_club_id OR m."awayClubId" = v_away_club_id)
    ),
    losses = (
      SELECT COUNT(*)
      FROM matches m
      JOIN match_results mr ON mr."matchId" = m.id
      WHERE m."leagueId" = v_league_id
        AND mr."homeScore" IS NOT NULL
        AND mr."awayScore" IS NOT NULL
        AND (
          (m."homeClubId" = v_away_club_id AND mr."homeScore" < mr."awayScore")
          OR (m."awayClubId" = v_away_club_id AND mr."awayScore" < mr."homeScore")
        )
    ),
    "goalsFor" = (
      SELECT COALESCE(SUM(
        CASE
          WHEN m."homeClubId" = v_away_club_id THEN mr."homeScore"
          WHEN m."awayClubId" = v_away_club_id THEN mr."awayScore"
          ELSE 0
        END
      ), 0)
      FROM matches m
      JOIN match_results mr ON mr."matchId" = m.id
      WHERE (m."homeClubId" = v_away_club_id OR m."awayClubId" = v_away_club_id)
        AND m."leagueId" = v_league_id
        AND mr."homeScore" IS NOT NULL
        AND mr."awayScore" IS NOT NULL
    ),
    "goalsAgainst" = (
      SELECT COALESCE(SUM(
        CASE
          WHEN m."homeClubId" = v_away_club_id THEN mr."awayScore"
          WHEN m."awayClubId" = v_away_club_id THEN mr."homeScore"
          ELSE 0
        END
      ), 0)
      FROM matches m
      JOIN match_results mr ON mr."matchId" = m.id
      WHERE (m."homeClubId" = v_away_club_id OR m."awayClubId" = v_away_club_id)
        AND m."leagueId" = v_league_id
        AND mr."homeScore" IS NOT NULL
        AND mr."awayScore" IS NOT NULL
    ),
    points = (
      SELECT COALESCE(SUM(
        CASE
          WHEN mr."homeScore" > mr."awayScore" AND m."homeClubId" = v_away_club_id THEN 3
          WHEN mr."awayScore" > mr."homeScore" AND m."awayClubId" = v_away_club_id THEN 3
          WHEN mr."homeScore" = mr."awayScore" AND (m."homeClubId" = v_away_club_id OR m."awayClubId" = v_away_club_id) THEN 1
          ELSE 0
        END
      ), 0)
      FROM matches m
      JOIN match_results mr ON mr."matchId" = m.id
      WHERE (m."homeClubId" = v_away_club_id OR m."awayClubId" = v_away_club_id)
        AND m."leagueId" = v_league_id
        AND mr."homeScore" IS NOT NULL
        AND mr."awayScore" IS NOT NULL
    )
  WHERE "leagueId" = v_league_id AND "clubId" = v_away_club_id;

  -- Recalculate positions for all clubs in the league
  -- Tiebreaker criteria (in order):
  -- 1. Points (higher is better)
  -- 2. Goal difference (higher is better)
  -- 3. Goals scored (higher is better)
  -- 4. Club name alphabetically (A-Z)
  v_rank := 1;
  FOR club_record IN
    SELECT lc.id
    FROM league_clubs lc
    JOIN clubs c ON c.id = lc."clubId"
    WHERE lc."leagueId" = v_league_id
    ORDER BY
      lc.points DESC,
      (lc."goalsFor" - lc."goalsAgainst") DESC,
      lc."goalsFor" DESC,
      c.name 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;

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