Saisonprognose der Tabelle

Führe diesen SQL-Code einmalig im SQL Editor deines Supabase-Projekts aus. Er legt die Saisonprognose an und zählt ihre Punkte nach Saisonende in allen Ranglisten mit.
SQL-Code
-- ---------------------------------------------------------------------------
-- Saisonprognose der Tabelle
--
-- Führe diesen Code einmalig im SQL Editor deines Supabase-Projekts aus.
-- Er legt die Tabelle season_predictions an und erweitert die drei
-- Ranglisten um die Prognose-Punkte (1 Punkt je richtigem Tabellenplatz,
-- gewertet sobald alle Spiele der Saison beendet sind).
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS public.season_predictions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  league text NOT NULL,
  season int NOT NULL,
  ranking jsonb NOT NULL,
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (user_id, league, season)
);

GRANT SELECT, INSERT, UPDATE, DELETE ON public.season_predictions TO authenticated;
GRANT ALL ON public.season_predictions TO service_role;
ALTER TABLE public.season_predictions ENABLE ROW LEVEL SECURITY;

-- Maßgeblicher Spieltag für den Abgabeschluss: 2026/27 der 8., sonst der 1.
CREATE OR REPLACE FUNCTION public.season_prediction_open(_league text, _season int)
RETURNS boolean
LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public AS $$
  SELECT coalesce(
    now() < (
      SELECT min(kickoff) FROM public.matches
      WHERE league = _league AND season = _season
        AND matchday = CASE WHEN _season = 2026 THEN 8 ELSE 1 END
    ),
    true
  );
$$;
GRANT EXECUTE ON FUNCTION public.season_prediction_open(text, int) TO authenticated;

DROP POLICY IF EXISTS "Eigene Saisonprognose lesen" ON public.season_predictions;
DROP POLICY IF EXISTS "Eigene Saisonprognose anlegen" ON public.season_predictions;
DROP POLICY IF EXISTS "Eigene Saisonprognose ändern" ON public.season_predictions;

CREATE POLICY "Eigene Saisonprognose lesen" ON public.season_predictions
  FOR SELECT TO authenticated USING (auth.uid() = user_id);
CREATE POLICY "Eigene Saisonprognose anlegen" ON public.season_predictions
  FOR INSERT TO authenticated
  WITH CHECK (auth.uid() = user_id AND public.season_prediction_open(league, season));
CREATE POLICY "Eigene Saisonprognose ändern" ON public.season_predictions
  FOR UPDATE TO authenticated
  USING (auth.uid() = user_id AND public.season_prediction_open(league, season))
  WITH CHECK (auth.uid() = user_id AND public.season_prediction_open(league, season));

-- Prognose-Punkte je Nutzer, nur bei vollständig beendeter Saison.
CREATE OR REPLACE FUNCTION public.season_prediction_points(_league text, _season int)
RETURNS TABLE (user_id uuid, league text, season int, pts bigint)
LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public AS $$
  WITH comp AS (
    SELECT m.league, m.season
    FROM public.matches m
    WHERE (_league IS NULL OR m.league = _league)
      AND (_season IS NULL OR m.season = _season)
    GROUP BY m.league, m.season
    HAVING count(*) > 0 AND bool_and(m.finished)
  ),
  rows AS (
    SELECT m.league, m.season, m.home_team AS team, m.home_goals AS gf, m.away_goals AS ga
    FROM public.matches m JOIN comp c ON c.league = m.league AND c.season = m.season
    UNION ALL
    SELECT m.league, m.season, m.away_team, m.away_goals, m.home_goals
    FROM public.matches m JOIN comp c ON c.league = m.league AND c.season = m.season
  ),
  tbl AS (
    SELECT league, season, team,
           sum(CASE WHEN gf > ga THEN 3 WHEN gf = ga THEN 1 ELSE 0 END) AS p,
           sum(gf - ga) AS gd, sum(gf) AS g
    FROM rows WHERE gf IS NOT NULL AND ga IS NOT NULL
    GROUP BY league, season, team
  ),
  ranked AS (
    SELECT league, season, team,
           row_number() OVER (PARTITION BY league, season ORDER BY p DESC, gd DESC, g DESC, team) AS pos
    FROM tbl
  )
  SELECT sp.user_id, sp.league, sp.season, count(r.team)::bigint
  FROM public.season_predictions sp
  JOIN comp c ON c.league = sp.league AND c.season = sp.season
  CROSS JOIN LATERAL jsonb_array_elements_text(sp.ranking) WITH ORDINALITY AS e(team, pos)
  LEFT JOIN ranked r
    ON r.league = sp.league AND r.season = sp.season AND r.team = e.team AND r.pos = e.pos
  GROUP BY sp.user_id, sp.league, sp.season;
$$;
REVOKE ALL ON FUNCTION public.season_prediction_points(text, int) FROM PUBLIC, anon;


-- 1) Gruppenrangliste einer einzelnen Gruppe --------------------------------
DROP FUNCTION IF EXISTS public.group_leaderboard(uuid, text);
DROP FUNCTION IF EXISTS public.group_leaderboard(uuid);

CREATE FUNCTION public.group_leaderboard(_group_id uuid)
RETURNS TABLE (user_id uuid, nickname text, points bigint, exact_hits bigint, tips bigint)
LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public AS $$
  WITH g AS (
    SELECT id, league, coalesce(season, 2026) AS season
    FROM public.groups WHERE id = _group_id
  ),
  tips AS (
    SELECT gm.user_id,
           coalesce(sum(p.points) FILTER (WHERE m.id IS NOT NULL), 0)::bigint AS pts,
           count(*) FILTER (
             WHERE m.id IS NOT NULL
               AND p.points IS NOT NULL
               AND m.home_goals = p.home_goals
               AND m.away_goals = p.away_goals
           )::bigint AS hits,
           count(p.id) FILTER (WHERE m.id IS NOT NULL)::bigint AS cnt
    FROM public.group_members gm
    JOIN g ON true
    LEFT JOIN public.matches m
      ON m.finished AND m.league = g.league AND m.season = g.season
    LEFT JOIN public.predictions p
      ON p.match_id = m.id AND p.user_id = gm.user_id
    WHERE gm.group_id = _group_id
    GROUP BY gm.user_id
  ),
  quiz AS (
    SELECT a.user_id, coalesce(sum(a.points), 0)::bigint AS pts
    FROM public.matchday_answers a
    JOIN g ON a.league = g.league AND a.season = g.season
    GROUP BY a.user_id
  ),
  prog AS (
    SELECT sp.user_id, sp.pts
    FROM g, public.season_prediction_points(g.league, g.season) sp
  )
  SELECT pr.id,
         pr.nickname,
         (t.pts + coalesce(q.pts, 0) + coalesce(sp.pts, 0))::bigint,
         t.hits,
         t.cnt
  FROM tips t
  JOIN public.profiles pr ON pr.id = t.user_id
  LEFT JOIN quiz q ON q.user_id = t.user_id
  LEFT JOIN prog sp ON sp.user_id = t.user_id
  WHERE public.is_group_member(_group_id, auth.uid())
  ORDER BY 3 DESC, 4 DESC, pr.nickname;
$$;

REVOKE ALL ON FUNCTION public.group_leaderboard(uuid) FROM PUBLIC, anon;
GRANT EXECUTE ON FUNCTION public.group_leaderboard(uuid) TO authenticated;

-- 2) Gesamtrangliste aller Tippenden ----------------------------------------
DROP FUNCTION IF EXISTS public.global_leaderboard(text, int, int);
DROP FUNCTION IF EXISTS public.global_leaderboard(text, int, int, int);

CREATE FUNCTION public.global_leaderboard(
  _league text DEFAULT NULL,
  _season int DEFAULT NULL,
  _limit int DEFAULT 100,
  _offset int DEFAULT 0
)
RETURNS TABLE (user_id uuid, nickname text, points bigint, exact_hits bigint)
LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public AS $$
  WITH participants AS (
    SELECT DISTINCT gm.user_id
    FROM public.group_members gm
    JOIN public.groups g ON g.id = gm.group_id
    WHERE (_league IS NULL OR g.league = _league)
      AND (_season IS NULL OR coalesce(g.season, 2026) = _season)
    UNION
    SELECT DISTINCT p.user_id
    FROM public.predictions p
    JOIN public.matches m ON m.id = p.match_id
    WHERE (_league IS NULL OR m.league = _league)
      AND (_season IS NULL OR m.season = _season)
  ),
  quiz AS (
    SELECT a.user_id, coalesce(sum(a.points), 0)::bigint AS pts
    FROM public.matchday_answers a
    WHERE (_league IS NULL OR a.league = _league)
      AND (_season IS NULL OR a.season = _season)
    GROUP BY a.user_id
  ),
  prog AS (
    SELECT sp.user_id, sum(sp.pts)::bigint AS pts
    FROM public.season_prediction_points(_league, _season) sp
    GROUP BY sp.user_id
  )
  SELECT pr.id,
         pr.nickname,
         (coalesce(sum(p.points), 0) + coalesce(max(q.pts), 0) + coalesce(max(sp.pts), 0))::bigint,
         count(*) FILTER (
           WHERE p.points IS NOT NULL
             AND m.home_goals = p.home_goals
             AND m.away_goals = p.away_goals
         )::bigint
  FROM public.profiles pr
  JOIN participants pa ON pa.user_id = pr.id
  LEFT JOIN public.matches m
    ON m.finished
   AND (_league IS NULL OR m.league = _league)
   AND (_season IS NULL OR m.season = _season)
  LEFT JOIN public.predictions p
    ON p.match_id = m.id
   AND p.user_id = pr.id
  LEFT JOIN quiz q ON q.user_id = pr.id
  LEFT JOIN prog sp ON sp.user_id = pr.id
  WHERE auth.uid() IS NOT NULL
  GROUP BY pr.id, pr.nickname
  ORDER BY 3 DESC, 4 DESC, pr.nickname
  LIMIT greatest(1, least(_limit, 500)) OFFSET greatest(0, _offset);
$$;

REVOKE ALL ON FUNCTION public.global_leaderboard(text, int, int, int) FROM PUBLIC, anon;
GRANT EXECUTE ON FUNCTION public.global_leaderboard(text, int, int, int) TO authenticated;

-- 3) Rangliste aller Gruppen einer Liga/Saison --------------------------------
DROP FUNCTION IF EXISTS public.groups_leaderboard(text);
DROP FUNCTION IF EXISTS public.groups_leaderboard(text, int);

CREATE FUNCTION public.groups_leaderboard(_league text DEFAULT NULL, _season int DEFAULT NULL)
RETURNS TABLE (
  group_id uuid,
  name text,
  league text,
  season int,
  members bigint,
  points bigint,
  avg_points numeric
)
LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public AS $$
  WITH sel AS (
    SELECT g.id, g.name, g.league, coalesce(g.season, 2026) AS season
    FROM public.groups g
    WHERE (_league IS NULL OR g.league = _league)
      AND (_season IS NULL OR coalesce(g.season, 2026) = _season)
  ),
  member_points AS (
    SELECT gm.group_id,
           gm.user_id,
           coalesce(sum(p.points), 0)::bigint AS pts
    FROM public.group_members gm
    JOIN sel s ON s.id = gm.group_id
    LEFT JOIN public.matches m
      ON m.finished AND m.league = s.league AND m.season = s.season
    LEFT JOIN public.predictions p
      ON p.match_id = m.id AND p.user_id = gm.user_id
    GROUP BY gm.group_id, gm.user_id
  ),
  quiz_points AS (
    SELECT gm.group_id,
           gm.user_id,
           coalesce(sum(a.points), 0)::bigint AS pts
    FROM public.group_members gm
    JOIN sel s ON s.id = gm.group_id
    LEFT JOIN public.matchday_answers a
      ON a.user_id = gm.user_id AND a.league = s.league AND a.season = s.season
    GROUP BY gm.group_id, gm.user_id
  ),
  prog AS (
    SELECT * FROM public.season_prediction_points(_league, _season)
  ),
  total AS (
    SELECT mp.group_id,
           mp.user_id,
           mp.pts + coalesce(qp.pts, 0) + coalesce(pg.pts, 0) AS pts
    FROM member_points mp
    JOIN sel s ON s.id = mp.group_id
    LEFT JOIN quiz_points qp
      ON qp.group_id = mp.group_id AND qp.user_id = mp.user_id
    LEFT JOIN prog pg
      ON pg.user_id = mp.user_id AND pg.league = s.league AND pg.season = s.season
  )
  SELECT s.id,
         s.name,
         s.league,
         s.season,
         count(t.user_id)::bigint,
         coalesce(sum(t.pts), 0)::bigint,
         round(coalesce(avg(t.pts), 0), 1)
  FROM sel s
  LEFT JOIN total t ON t.group_id = s.id
  WHERE auth.uid() IS NOT NULL
  GROUP BY s.id, s.name, s.league, s.season
  ORDER BY 7 DESC, 6 DESC, s.name;
$$;

REVOKE ALL ON FUNCTION public.groups_leaderboard(text, int) FROM PUBLIC, anon;
GRANT EXECUTE ON FUNCTION public.groups_leaderboard(text, int) TO authenticated;