HOLULA APEX

📋 SQL Migration (einmalig ausführen)

-- HOLULA APEX V9 — Training Tables Migration

CREATE TABLE IF NOT EXISTS training_sessions (
  id                 UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  club_id            UUID NOT NULL REFERENCES clubs(id) ON DELETE CASCADE,
  trainer_id         UUID REFERENCES auth.users(id),
  date               DATE NOT NULL,
  start_time         TEXT,
  treffzeit          TEXT,
  title              TEXT,
  notes              TEXT,
  planned_tests      TEXT[]   DEFAULT '{}',
  jahrgangsklassen   TEXT[]   DEFAULT '{}',
  ort                TEXT,
  trainer_names      TEXT,
  material           TEXT,
  phasen             JSONB    DEFAULT '[]',
  saison             TEXT,
  created_at         TIMESTAMPTZ DEFAULT now(),
  updated_at         TIMESTAMPTZ DEFAULT now()
);

-- Falls Tabelle bereits existiert (V9.0 → V9.1 Upgrade):
ALTER TABLE training_sessions ADD COLUMN IF NOT EXISTS treffzeit        TEXT;
ALTER TABLE training_sessions ADD COLUMN IF NOT EXISTS jahrgangsklassen TEXT[]   DEFAULT '{}';
ALTER TABLE training_sessions ADD COLUMN IF NOT EXISTS ort              TEXT;
ALTER TABLE training_sessions ADD COLUMN IF NOT EXISTS trainer_names    TEXT;
ALTER TABLE training_sessions ADD COLUMN IF NOT EXISTS material         TEXT;
ALTER TABLE training_sessions ADD COLUMN IF NOT EXISTS phasen           JSONB    DEFAULT '[]';
ALTER TABLE training_sessions ADD COLUMN IF NOT EXISTS practice_no      INTEGER;
ALTER TABLE training_sessions ADD COLUMN IF NOT EXISTS duration_min     INTEGER;

CREATE TABLE IF NOT EXISTS training_attendance (
  id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  session_id UUID NOT NULL REFERENCES training_sessions(id) ON DELETE CASCADE,
  player_id  UUID NOT NULL REFERENCES players(id) ON DELETE CASCADE,
  club_id    UUID NOT NULL,
  status     TEXT NOT NULL DEFAULT 'absent'
             CHECK (status IN ('present','absent','late','excused')),
  note       TEXT,
  UNIQUE(session_id, player_id)
);

-- RLS aktivieren
ALTER TABLE training_sessions  ENABLE ROW LEVEL SECURITY;
ALTER TABLE training_attendance ENABLE ROW LEVEL SECURITY;

-- Bestehende Policies entfernen (idempotent)
DROP POLICY IF EXISTS "sessions_club_access"       ON training_sessions;
DROP POLICY IF EXISTS "sessions_superadmin_access" ON training_sessions;
DROP POLICY IF EXISTS "sessions_service_role"      ON training_sessions;
DROP POLICY IF EXISTS "attendance_club_access"       ON training_attendance;
DROP POLICY IF EXISTS "attendance_superadmin_access" ON training_attendance;
DROP POLICY IF EXISTS "attendance_service_role"      ON training_attendance;

-- Lese- und Schreibzugriff für eigenen Verein
CREATE POLICY "sessions_club_access" ON training_sessions
  FOR ALL USING (
    club_id IN (SELECT club_id FROM user_club_memberships WHERE user_id = auth.uid())
  );

CREATE POLICY "attendance_club_access" ON training_attendance
  FOR ALL USING (
    club_id IN (SELECT club_id FROM user_club_memberships WHERE user_id = auth.uid())
  );

-- Superadmin: vereinsübergreifender Vollzugriff
CREATE POLICY "sessions_superadmin_access" ON training_sessions
  FOR ALL USING (
    EXISTS (SELECT 1 FROM user_profiles WHERE id = auth.uid() AND role = 'superadmin')
  );

CREATE POLICY "attendance_superadmin_access" ON training_attendance
  FOR ALL USING (
    EXISTS (SELECT 1 FROM user_profiles WHERE id = auth.uid() AND role = 'superadmin')
  );

-- Service Role Bypass (für Admin-Operationen)
CREATE POLICY "sessions_service_role" ON training_sessions
  FOR ALL TO service_role USING (true) WITH CHECK (true);

CREATE POLICY "attendance_service_role" ON training_attendance
  FOR ALL TO service_role USING (true) WITH CHECK (true);

📋 SQL Migration (einmalig ausführen)

-- MERC App – Optionales E-Mail-Feld für Trainer/Admin

ALTER TABLE user_profiles ADD COLUMN IF NOT EXISTS email TEXT;

📋 SQL (einmalig ausführen)

-- HOLULA APEX – Superadmin RLS Fix (Trainingsplan)
-- Ermöglicht vereinsübergreifenden Zugriff für Superadmin

DROP POLICY IF EXISTS "sessions_superadmin_access"   ON training_sessions;
DROP POLICY IF EXISTS "attendance_superadmin_access" ON training_attendance;

CREATE POLICY "sessions_superadmin_access" ON training_sessions
  FOR ALL USING (
    EXISTS (SELECT 1 FROM user_profiles WHERE id = auth.uid() AND role = 'superadmin')
  );

CREATE POLICY "attendance_superadmin_access" ON training_attendance
  FOR ALL USING (
    EXISTS (SELECT 1 FROM user_profiles WHERE id = auth.uid() AND role = 'superadmin')
  );

📋 SQL Migration (einmalig ausführen)

-- HOLULA APEX – Login-Protokoll Tabelle

CREATE TABLE IF NOT EXISTS login_logs (
  id             UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id        UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  username       TEXT,
  display_name   TEXT,
  role           TEXT,
  club_id        UUID,
  club_name      TEXT,
  logged_in_at   TIMESTAMPTZ DEFAULT now()
);

ALTER TABLE login_logs ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "login_logs_insert"     ON login_logs;
DROP POLICY IF EXISTS "login_logs_superadmin" ON login_logs;
DROP POLICY IF EXISTS "login_logs_service"    ON login_logs;

-- Jeder eingeloggte Nutzer darf seinen eigenen Login eintragen
CREATE POLICY "login_logs_insert" ON login_logs
  FOR INSERT WITH CHECK (user_id = auth.uid());

-- Nur Superadmin darf alle Einträge lesen
CREATE POLICY "login_logs_superadmin" ON login_logs
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM user_profiles
      WHERE id = auth.uid() AND role = 'superadmin'
    )
  );

-- Service Role Bypass
CREATE POLICY "login_logs_service" ON login_logs
  FOR ALL TO service_role USING (true) WITH CHECK (true);

📋 SQL Migration (einmalig ausführen)

-- HOLULA APEX – Spieler-Lesezugriff auf Trainingsplan-Daten

-- Spieler dürfen Einheiten ihres Vereins lesen
DROP POLICY IF EXISTS "sessions_player_access" ON training_sessions;
CREATE POLICY "sessions_player_access" ON training_sessions
  FOR SELECT USING (
    club_id IN (
      SELECT club_id FROM players WHERE auth_user_id = auth.uid()
    )
  );

-- Spieler dürfen ihre eigenen Anwesenheits-Einträge lesen
DROP POLICY IF EXISTS "attendance_player_access" ON training_attendance;
CREATE POLICY "attendance_player_access" ON training_attendance
  FOR SELECT USING (
    player_id IN (
      SELECT id FROM players WHERE auth_user_id = auth.uid()
    )
  );

📋 SQL Migration (einmalig ausführen)

-- HOLULA APEX – Eistraining & Turnier Migration

ALTER TABLE training_sessions ADD COLUMN IF NOT EXISTS typ      TEXT DEFAULT 'sommer' CHECK (typ IN ('sommer', 'eis', 'turnier'));
ALTER TABLE training_sessions ADD COLUMN IF NOT EXISTS gegner   TEXT;
ALTER TABLE training_sessions ADD COLUMN IF NOT EXISTS ergebnis TEXT;

SQL – Supabase SQL-Editor

CREATE TABLE IF NOT EXISTS bug_reports (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  description  TEXT NOT NULL,
  severity     TEXT DEFAULT 'normal',
  context_tab  TEXT,
  reporter     TEXT,
  reporter_role TEXT,
  browser      TEXT,
  app_version  TEXT,
  screenshot   TEXT,
  status       TEXT DEFAULT 'open' CHECK (status IN ('open','in_progress','resolved','wontfix')),
  created_at   TIMESTAMPTZ DEFAULT now()
);

ALTER TABLE bug_reports ENABLE ROW LEVEL SECURITY;

-- Jeder eingeloggte Nutzer darf Bug-Reports einreichen
DROP POLICY IF EXISTS "bug_reports_insert" ON bug_reports;
CREATE POLICY "bug_reports_insert" ON bug_reports
  FOR INSERT WITH CHECK (true);

-- Nur Superadmin darf lesen
DROP POLICY IF EXISTS "bug_reports_superadmin" ON bug_reports;
CREATE POLICY "bug_reports_superadmin" ON bug_reports
  FOR ALL USING (
    EXISTS (SELECT 1 FROM user_profiles WHERE id = auth.uid() AND role = 'superadmin')
  );

SQL – Supabase SQL-Editor

-- Schritt 1: Alten CHECK-Constraint entfernen
ALTER TABLE clubs DROP CONSTRAINT IF EXISTS clubs_plan_check;

-- Schritt 2: Spalte anlegen falls noch nicht vorhanden
ALTER TABLE clubs
  ADD COLUMN IF NOT EXISTS plan TEXT NOT NULL DEFAULT 'basic';

-- Schritt 3: Neuen Constraint setzen
ALTER TABLE clubs ADD CONSTRAINT clubs_plan_check
  CHECK (plan IN ('basic', 'pro'));

-- Schritt 4: Alte Werte migrieren
UPDATE clubs SET plan = 'basic' WHERE plan IN ('starter', 'club');
-- 'pro' bleibt 'pro', kein Update nötig

SQL – Supabase SQL-Editor

-- Ermöglicht Login-Fallback: E-Mail zu Username nachschlagen
-- SECURITY DEFINER: bypassed RLS, sicher weil nur eigene E-Mail zurückgegeben wird
CREATE OR REPLACE FUNCTION public.get_login_email_for_username(p_username TEXT)
RETURNS TEXT
LANGUAGE SQL
SECURITY DEFINER
SET search_path = public
AS $$
  SELECT email FROM user_profiles
  WHERE lower(username) = lower(p_username)
    AND email IS NOT NULL
    AND email NOT LIKE '%@merc.internal'
    AND email NOT LIKE '%.internal'
  LIMIT 1;
$$;

GRANT EXECUTE ON FUNCTION public.get_login_email_for_username TO anon, authenticated;

SQL – Supabase SQL-Editor

-- Spieler dürfen ihre eigene Anwesenheit eintragen / ändern
CREATE POLICY "player_rsvp_own_attendance"
ON public.training_attendance
FOR ALL
TO authenticated
USING (
  player_id IN (
    SELECT id FROM players WHERE auth_user_id = auth.uid()
  )
)
WITH CHECK (
  player_id IN (
    SELECT id FROM players WHERE auth_user_id = auth.uid()
  )
);

-- Sicherstellen dass RLS aktiviert ist
ALTER TABLE public.training_attendance ENABLE ROW LEVEL SECURITY;

SQL – Supabase SQL-Editor

-- Erlaubt Vereins-Admins, Spieler-Profile in ihrem Club zu löschen
DROP POLICY IF EXISTS "admin_delete_player_profiles" ON user_profiles;
CREATE POLICY "admin_delete_player_profiles" ON user_profiles
  FOR DELETE USING (
    role = 'player'
    AND id IN (
      SELECT auth_user_id FROM players
      WHERE auth_user_id IS NOT NULL
        AND club_id IN (
          SELECT club_id FROM user_club_memberships
          WHERE user_id = auth.uid()
        )
    )
  );

📋 SQL Migration (einmalig ausführen)

-- HOLULA APEX v13 – Spieleraufstellung & Test-Label

ALTER TABLE public.training_sessions ADD COLUMN IF NOT EXISTS lineup     JSONB DEFAULT NULL;
ALTER TABLE public.training_sessions ADD COLUMN IF NOT EXISTS test_label TEXT  DEFAULT NULL;

📋 SQL – Supabase SQL-Editor (einmalig)

-- Tabellen zur Supabase Realtime Publication hinzufügen
ALTER PUBLICATION supabase_realtime ADD TABLE public.training_sessions;
ALTER PUBLICATION supabase_realtime ADD TABLE public.training_attendance;

-- REPLICA IDENTITY FULL für vollständige UPDATE/DELETE Payloads
ALTER TABLE public.training_sessions   REPLICA IDENTITY FULL;
ALTER TABLE public.training_attendance REPLICA IDENTITY FULL;

📋 SQL Migration (einmalig ausführen)

-- HOLULA APEX v14.0 – Plan-Ansicht Tracking
ALTER TABLE public.training_attendance
  ADD COLUMN IF NOT EXISTS plan_viewed_at TIMESTAMPTZ DEFAULT NULL;

📋 SQL Migration (einmalig ausführen)

-- HOLULA APEX v16.0 – Globale Referenzwerte

CREATE TABLE IF NOT EXISTS global_reference_values (
  id         UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  category   TEXT NOT NULL,
  metric     TEXT NOT NULL UNIQUE,
  gender     TEXT,
  label      TEXT NOT NULL,
  source     TEXT,
  thresholds JSONB NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT now(),
  updated_by UUID REFERENCES auth.users ON DELETE SET NULL
);

ALTER TABLE global_reference_values ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "global_refs_read"  ON global_reference_values;
DROP POLICY IF EXISTS "global_refs_write" ON global_reference_values;

CREATE POLICY "global_refs_read" ON global_reference_values
  FOR SELECT TO authenticated USING (true);

CREATE POLICY "global_refs_write" ON global_reference_values
  FOR ALL TO authenticated
  USING  ((SELECT role FROM user_profiles WHERE id = auth.uid()) = 'superadmin')
  WITH CHECK ((SELECT role FROM user_profiles WHERE id = auth.uid()) = 'superadmin');

-- Seed: VBG Cardio Orientierungswerte
INSERT INTO global_reference_values (category, metric, gender, label, source, thresholds) VALUES
('vbg_cardio', 'yoyo_male', 'male', 'Yo-Yo IRT Level 1 – Männer', 'VBG / Souhail et al. 2010',
 '{"type":"levels","lower_is_better":false,"levels":[
   {"threshold":2400,"label":"Weit überdurchschnittlich","cls":"nc-badge-elite"},
   {"threshold":2000,"label":"Überdurchschnittlich","cls":"nc-badge-above"},
   {"threshold":1520,"label":"Durchschnittlich","cls":"nc-badge-avg"},
   {"threshold":960,"label":"Unterdurchschnittlich","cls":"nc-badge-below"},
   {"threshold":null,"label":"Weit unterdurchschnittlich","cls":"nc-badge-low"}
 ]}'::jsonb),

('vbg_cardio', 'yoyo_female', 'female', 'Yo-Yo IRT Level 1 – Frauen', 'VBG / Souhail et al. 2010',
 '{"type":"levels","lower_is_better":false,"levels":[
   {"threshold":1800,"label":"Weit überdurchschnittlich","cls":"nc-badge-elite"},
   {"threshold":1400,"label":"Überdurchschnittlich","cls":"nc-badge-above"},
   {"threshold":1000,"label":"Durchschnittlich","cls":"nc-badge-avg"},
   {"threshold":520,"label":"Unterdurchschnittlich","cls":"nc-badge-below"},
   {"threshold":null,"label":"Weit unterdurchschnittlich","cls":"nc-badge-low"}
 ]}'::jsonb),

('vbg_cardio', 'shuttle_off_male', 'male', '300m Shuttle off ice – Männer', 'VBG Diagnostik Eishockey',
 '{"type":"levels","lower_is_better":true,"levels":[
   {"threshold":60,"label":"Ziel erreicht (≤ 60 s)","cls":"nc-badge-elite"},
   {"threshold":64,"label":"Minimum (≤ 64 s)","cls":"nc-badge-avg"},
   {"threshold":null,"label":"Unter Minimum (> 64 s)","cls":"nc-badge-low"}
 ]}'::jsonb),

('vbg_cardio', 'shuttle_on_male', 'male', '300m Shuttle on ice – Männer', 'VBG Diagnostik Eishockey',
 '{"type":"levels","lower_is_better":true,"levels":[
   {"threshold":57,"label":"Ziel erreicht (≤ 57 s)","cls":"nc-badge-elite"},
   {"threshold":62,"label":"Minimum (≤ 62 s)","cls":"nc-badge-avg"},
   {"threshold":null,"label":"Unter Minimum (> 62 s)","cls":"nc-badge-low"}
 ]}'::jsonb),

('vbg_cardio', 'shuttle_on_female', 'female', '300m Shuttle on ice – Frauen', 'VBG Diagnostik Eishockey',
 '{"type":"levels","lower_is_better":true,"levels":[
   {"threshold":62,"label":"Ziel erreicht (≤ 62 s)","cls":"nc-badge-elite"},
   {"threshold":67,"label":"Minimum (≤ 67 s)","cls":"nc-badge-avg"},
   {"threshold":null,"label":"Unter Minimum (> 67 s)","cls":"nc-badge-low"}
 ]}'::jsonb),

-- Seed: NEXUS Speed ZVT
('nexus_speed', 'zvt', null, 'NEXUS Speed – Zeit pro Zahl (s)', 'NEXUS Speed Bewertung (intern)',
 '{"type":"cutoffs","lower_is_better":true,"levels":[
   {"threshold":0.5,"label":"Spitze","color":"#2E7D32"},
   {"threshold":0.7,"label":"Gut","color":"#558B2F"},
   {"threshold":1.0,"label":"Mittel","color":"#0277BD"},
   {"threshold":null,"label":"Trainingsbedarf","color":"#B58A00"}
 ]}'::jsonb),

-- Seed: NHL Draft Combine 2026
('nhl_combine', 'nhl_standweit', null, 'Standweit', 'NHL Scouting Combine 2026',
 '{"type":"reference","unit":"cm","lower_is_better":false,"top1":299.7,"top5":291.6,"app_key":"standweit","lbl":"Standweit"}'::jsonb),

('nhl_combine', 'nhl_cmj', null, 'Vertical Jump (CMJ)', 'NHL Scouting Combine 2026',
 '{"type":"reference","unit":"cm","lower_is_better":false,"top1":64.5,"top5":57.9,"app_key":"cmj","lbl":"Vertical Jump"}'::jsonb),

('nhl_combine', 'nhl_sprint10m', null, 'Sprint 10m', 'NHL Scouting Combine 2026',
 '{"type":"reference","unit":"s","lower_is_better":true,"top1":1.97,"top5":2.04,"app_key":"sprint10m","lbl":"Sprint 10m"}'::jsonb),

('nhl_combine', 'nhl_pullups', null, 'Pull-Ups', 'NHL Scouting Combine 2026',
 '{"type":"reference","unit":"","lower_is_better":false,"top1":18,"top5":15,"app_key":"pullups","lbl":"Pull-Ups"}'::jsonb),

('nhl_combine', 'nhl_yoyo_vo2', null, 'VO₂max', 'NHL Scouting Combine 2026',
 '{"type":"reference","unit":"ml/kg/min","lower_is_better":false,"top1":65,"top5":60,"app_key":"yoyoVo2","lbl":"VO₂max"}'::jsonb)

ON CONFLICT (metric) DO NOTHING;

📋 SQL Migration (einmalig ausführen)

-- 1. Beta-Disclaimer Spalte
ALTER TABLE user_profiles
  ADD COLUMN IF NOT EXISTS show_beta_disclaimer BOOLEAN DEFAULT TRUE;

-- 2. SA darf beliebige user_profiles updaten (behebt auch Rollen-Toggle)
DROP POLICY IF EXISTS up_write_sa ON user_profiles;
CREATE POLICY up_write_sa ON user_profiles FOR UPDATE TO authenticated
  USING     ((SELECT role FROM user_profiles WHERE id = auth.uid()) = 'superadmin')
  WITH CHECK ((SELECT role FROM user_profiles WHERE id = auth.uid()) = 'superadmin');

📋 SQL Migration (einmalig ausführen)

-- game_rsvp: RSVP-Tabelle für Spieleinladungen (Spieltag/Turnier)
CREATE TABLE IF NOT EXISTS game_rsvp (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  session_id  UUID NOT NULL REFERENCES training_sessions(id) ON DELETE CASCADE,
  player_id   UUID NOT NULL REFERENCES players(id) ON DELETE CASCADE,
  club_id     UUID NOT NULL,
  status      TEXT NOT NULL CHECK (status IN ('accepted', 'declined')),
  created_at  TIMESTAMPTZ DEFAULT NOW(),
  updated_at  TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE (session_id, player_id)
);
ALTER TABLE game_rsvp ENABLE ROW LEVEL SECURITY;

-- Spieler: eigene Zeile lesen + schreiben
CREATE POLICY "player_own_rsvp" ON game_rsvp
  FOR ALL
  USING (player_id IN (SELECT id FROM players WHERE auth_user_id = auth.uid()))
  WITH CHECK (player_id IN (SELECT id FROM players WHERE auth_user_id = auth.uid()));

-- Trainer/Admin: alle Einträge des eigenen Vereins lesen
CREATE POLICY "trainer_read_rsvp" ON game_rsvp
  FOR SELECT
  USING (
    club_id IN (SELECT club_id FROM user_profiles WHERE id = auth.uid())
    OR EXISTS (SELECT 1 FROM user_profiles WHERE id = auth.uid() AND role = 'superadmin')
  );

📋 SQL Migration (einmalig ausführen)

-- Absagefrist-Spalte für Spieltag-/Turnier-RSVPs
ALTER TABLE training_sessions
  ADD COLUMN IF NOT EXISTS rsvp_deadline TIMESTAMPTZ DEFAULT NULL;

📋 SQL Migration (einmalig ausführen)

ALTER TABLE user_profiles
  ADD COLUMN IF NOT EXISTS plan_override TEXT DEFAULT NULL
  CHECK (plan_override IN ('basic', 'pro'));

📋 SQL Migration (einmalig ausführen)

ALTER TABLE training_sessions
  ADD COLUMN IF NOT EXISTS nexus_assignments JSONB DEFAULT NULL;

📋 SQL Migration (einmalig ausführen)

ALTER TABLE cognitive_tests
  DROP CONSTRAINT IF EXISTS cognitive_tests_test_type_check;

ALTER TABLE cognitive_tests
  ADD CONSTRAINT cognitive_tests_test_type_check
  CHECK (test_type IN ('zvt', 'd2r', 'nexus_search'));

📋 SQL Migration (einmalig ausführen)

CREATE OR REPLACE FUNCTION public.get_player_team_ranking(p_player_id UUID)
RETURNS JSONB LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_club_id UUID; v_klasse TEXT; v_ids UUID[]; v_count INT; v_result JSONB;
BEGIN
  SELECT club_id, jahrgangsklasse INTO v_club_id, v_klasse
    FROM players WHERE id = p_player_id;
  IF v_club_id IS NULL THEN RETURN '{"error":"no_club"}'::JSONB; END IF;

  SELECT ARRAY_AGG(id) INTO v_ids
    FROM players WHERE club_id = v_club_id AND jahrgangsklasse = v_klasse;
  v_count := COALESCE(ARRAY_LENGTH(v_ids, 1), 0);
  IF v_count < 3 THEN
    RETURN jsonb_build_object('error','not_enough_teammates','count',v_count);
  END IF;

  WITH latest_phys AS (
    SELECT DISTINCT ON (player_id) player_id, data
    FROM player_tests WHERE player_id = ANY(v_ids)
    ORDER BY player_id, date DESC
  ),
  latest_zvt AS (
    SELECT DISTINCT ON (player_id) player_id,
           (data->>'perNum')::numeric AS per_num
    FROM cognitive_tests
    WHERE test_type = 'zvt' AND player_id = ANY(v_ids)
    ORDER BY player_id, ts DESC
  ),
  combined AS (
    SELECT l.player_id,
      (l.data->>'cmj')::numeric         AS cmj,
      (l.data->>'squatMax')::numeric     AS squat_max,
      (l.data->>'sprint10m')::numeric    AS sprint,
      (l.data->>'iftMaxSpeed')::numeric  AS ift,
      (l.data->>'yoyoDist')::numeric     AS yoyo,
      z.per_num
    FROM latest_phys l LEFT JOIN latest_zvt z USING (player_id)
  ),
  ranked AS (
    SELECT player_id,
      RANK() OVER (ORDER BY cmj       DESC NULLS LAST) AS r_cmj,  COUNT(cmj)       OVER () AS n_cmj,
      RANK() OVER (ORDER BY squat_max DESC NULLS LAST) AS r_sq,   COUNT(squat_max) OVER () AS n_sq,
      RANK() OVER (ORDER BY sprint    ASC  NULLS LAST) AS r_sp,   COUNT(sprint)    OVER () AS n_sp,
      RANK() OVER (ORDER BY ift       DESC NULLS LAST) AS r_ift,  COUNT(ift)       OVER () AS n_ift,
      RANK() OVER (ORDER BY yoyo      DESC NULLS LAST) AS r_yo,   COUNT(yoyo)      OVER () AS n_yo,
      RANK() OVER (ORDER BY per_num   ASC  NULLS LAST) AS r_zvt,  COUNT(per_num)   OVER () AS n_zvt
    FROM combined
  )
  SELECT jsonb_build_object(
    'total',       v_count,
    'cmj',         jsonb_build_object('rank', r_cmj, 'of', n_cmj),
    'squatMax',    jsonb_build_object('rank', r_sq,  'of', n_sq),
    'sprint10m',   jsonb_build_object('rank', r_sp,  'of', n_sp),
    'iftMaxSpeed', jsonb_build_object('rank', r_ift, 'of', n_ift),
    'yoyoDist',    jsonb_build_object('rank', r_yo,  'of', n_yo),
    'perNum',      jsonb_build_object('rank', r_zvt, 'of', n_zvt)
  ) INTO v_result FROM ranked WHERE player_id = p_player_id;

  RETURN COALESCE(v_result, '{"error":"no_tests"}'::JSONB);
END;
$$;

GRANT EXECUTE ON FUNCTION public.get_player_team_ranking(UUID) TO authenticated;

📋 SQL Migration (einmalig ausführen)

-- ── Eisplan-Tabellen ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS eisplaene (
  id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  club_id          UUID NOT NULL REFERENCES clubs(id) ON DELETE CASCADE,
  name             TEXT NOT NULL,
  saison           TEXT,
  sichtbar_trainer BOOLEAN DEFAULT false,
  sichtbar_spieler BOOLEAN DEFAULT false,
  is_active        BOOLEAN DEFAULT true,
  created_at       TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS eisplan_flaechen (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  plan_id      UUID NOT NULL REFERENCES eisplaene(id) ON DELETE CASCADE,
  name         TEXT NOT NULL,
  reihenfolge  INT DEFAULT 0,
  farbe        TEXT DEFAULT '#3b82f6'
);

CREATE TABLE IF NOT EXISTS eisplan_slots (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  plan_id     UUID NOT NULL REFERENCES eisplaene(id) ON DELETE CASCADE,
  flaeche_id  UUID REFERENCES eisplan_flaechen(id) ON DELETE SET NULL,
  weekday     INT NOT NULL CHECK (weekday BETWEEN 0 AND 6),
  start_time  TIME NOT NULL,
  end_time    TIME NOT NULL,
  kabinen     TEXT,
  team_label  TEXT,
  typ         TEXT DEFAULT 'training'
              CHECK (typ IN ('training','spiel','eisbereitung','schule','sonstiges')),
  farbe       TEXT,
  notiz       TEXT,
  ist_intern  BOOLEAN DEFAULT true
);

CREATE INDEX IF NOT EXISTS idx_eisplan_slots_plan ON eisplan_slots(plan_id);
CREATE INDEX IF NOT EXISTS idx_eisplan_flaechen_plan ON eisplan_flaechen(plan_id);

ALTER TABLE eisplaene        ENABLE ROW LEVEL SECURITY;
ALTER TABLE eisplan_flaechen ENABLE ROW LEVEL SECURITY;
ALTER TABLE eisplan_slots    ENABLE ROW LEVEL SECURITY;

-- ── eisplaene ────────────────────────────────────────────────────────────
-- Club-Mitglieder dürfen Pläne ihres Clubs lesen und schreiben
-- (Admin-Einschränkung erfolgt auf Anwendungsebene)
DROP POLICY IF EXISTS "admin_eisplaene"        ON eisplaene;
DROP POLICY IF EXISTS "trainer_read_eisplaene" ON eisplaene;
DROP POLICY IF EXISTS "player_read_eisplaene"  ON eisplaene;
DROP POLICY IF EXISTS "club_member_eisplaene"  ON eisplaene;
DROP POLICY IF EXISTS "player_eisplaene"       ON eisplaene;

-- Trainer + Admin: voller Zugriff auf Pläne des eigenen Clubs
CREATE POLICY "club_member_eisplaene" ON eisplaene FOR ALL
  USING (club_id IN (
    SELECT club_id FROM user_club_memberships WHERE user_id = auth.uid()
  ))
  WITH CHECK (club_id IN (
    SELECT club_id FROM user_club_memberships WHERE user_id = auth.uid()
  ));

-- Spieler: nur lesen wenn sichtbar_spieler = true
CREATE POLICY "player_eisplaene" ON eisplaene FOR SELECT
  USING (sichtbar_spieler = true AND club_id IN (
    SELECT club_id FROM players WHERE auth_user_id = auth.uid()
  ));

-- Superadmin: Vollzugriff (SA hat keine user_club_memberships → sonst 0 Pläne sichtbar).
-- Kind-Tabellen erben automatisch über plan_id IN (SELECT id FROM eisplaene).
DROP POLICY IF EXISTS "superadmin_eisplaene" ON eisplaene;
CREATE POLICY "superadmin_eisplaene" ON eisplaene FOR ALL
  USING (is_superadmin()) WITH CHECK (is_superadmin());

-- ── eisplan_flaechen ─────────────────────────────────────────────────────
DROP POLICY IF EXISTS "read_eisplan_flaechen"  ON eisplan_flaechen;
DROP POLICY IF EXISTS "admin_eisplan_flaechen" ON eisplan_flaechen;
DROP POLICY IF EXISTS "club_eisplan_flaechen"  ON eisplan_flaechen;

CREATE POLICY "club_eisplan_flaechen" ON eisplan_flaechen FOR ALL
  USING (plan_id IN (SELECT id FROM eisplaene))
  WITH CHECK (plan_id IN (SELECT id FROM eisplaene));

-- ── eisplan_slots ────────────────────────────────────────────────────────
DROP POLICY IF EXISTS "read_eisplan_slots"  ON eisplan_slots;
DROP POLICY IF EXISTS "admin_eisplan_slots" ON eisplan_slots;
DROP POLICY IF EXISTS "club_eisplan_slots"  ON eisplan_slots;

CREATE POLICY "club_eisplan_slots" ON eisplan_slots FOR ALL
  USING (plan_id IN (SELECT id FROM eisplaene))
  WITH CHECK (plan_id IN (SELECT id FROM eisplaene));

📋 SQL Migration (einmalig ausführen)

CREATE TABLE IF NOT EXISTS tournaments (
  id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  club_id    UUID REFERENCES clubs(id) ON DELETE CASCADE,
  name       TEXT NOT NULL,
  datum      DATE,
  modus      TEXT NOT NULL,
  config     JSONB DEFAULT '{}'::jsonb,
  status     TEXT DEFAULT 'running',
  created_at TIMESTAMPTZ DEFAULT now()
);
CREATE TABLE IF NOT EXISTS tournament_teams (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tournament_id UUID NOT NULL REFERENCES tournaments(id) ON DELETE CASCADE,
  name TEXT NOT NULL, seed INT DEFAULT 0
);
CREATE TABLE IF NOT EXISTS tournament_phases (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tournament_id UUID NOT NULL REFERENCES tournaments(id) ON DELETE CASCADE,
  phase_order INT NOT NULL,
  type TEXT NOT NULL CHECK (type IN ('group','knockout','league')),
  name TEXT, config JSONB DEFAULT '{}'::jsonb
);
CREATE TABLE IF NOT EXISTS tournament_groups (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tournament_id UUID NOT NULL REFERENCES tournaments(id) ON DELETE CASCADE,
  phase_id UUID NOT NULL REFERENCES tournament_phases(id) ON DELETE CASCADE,
  name TEXT NOT NULL, group_order INT DEFAULT 0
);
CREATE TABLE IF NOT EXISTS tournament_group_teams (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tournament_id UUID NOT NULL REFERENCES tournaments(id) ON DELETE CASCADE,
  group_id UUID NOT NULL REFERENCES tournament_groups(id) ON DELETE CASCADE,
  team_id  UUID NOT NULL REFERENCES tournament_teams(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS tournament_matches (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tournament_id UUID NOT NULL REFERENCES tournaments(id) ON DELETE CASCADE,
  phase_id UUID NOT NULL REFERENCES tournament_phases(id) ON DELETE CASCADE,
  group_id UUID REFERENCES tournament_groups(id) ON DELETE CASCADE,
  round INT DEFAULT 1, position INT DEFAULT 0, station TEXT, label TEXT,
  team_home_id UUID REFERENCES tournament_teams(id) ON DELETE CASCADE,
  team_away_id UUID REFERENCES tournament_teams(id) ON DELETE CASCADE,
  score_home INT, score_away INT, created_at TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_tour_teams_t   ON tournament_teams(tournament_id);
CREATE INDEX IF NOT EXISTS idx_tour_phases_t  ON tournament_phases(tournament_id);
CREATE INDEX IF NOT EXISTS idx_tour_groups_t  ON tournament_groups(tournament_id);
CREATE INDEX IF NOT EXISTS idx_tour_gt_group  ON tournament_group_teams(group_id);
CREATE INDEX IF NOT EXISTS idx_tour_matches_p ON tournament_matches(phase_id);

ALTER TABLE tournaments            ENABLE ROW LEVEL SECURITY;
ALTER TABLE tournament_teams       ENABLE ROW LEVEL SECURITY;
ALTER TABLE tournament_phases      ENABLE ROW LEVEL SECURITY;
ALTER TABLE tournament_groups      ENABLE ROW LEVEL SECURITY;
ALTER TABLE tournament_group_teams ENABLE ROW LEVEL SECURITY;
ALTER TABLE tournament_matches     ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "tour_club_all" ON tournaments;
DROP POLICY IF EXISTS "tour_sa_all"   ON tournaments;
DROP POLICY IF EXISTS "tour_service"  ON tournaments;
CREATE POLICY "tour_club_all" ON tournaments FOR ALL
  USING (club_id IN (SELECT my_club_ids())) WITH CHECK (club_id IN (SELECT my_club_ids()));
CREATE POLICY "tour_sa_all" ON tournaments FOR ALL
  USING (is_superadmin()) WITH CHECK (is_superadmin());
CREATE POLICY "tour_service" ON tournaments FOR ALL
  USING (auth.role() = 'service_role') WITH CHECK (auth.role() = 'service_role');

DROP POLICY IF EXISTS "tteams_via_tournament" ON tournament_teams;
CREATE POLICY "tteams_via_tournament" ON tournament_teams FOR ALL
  USING (tournament_id IN (SELECT id FROM tournaments)) WITH CHECK (tournament_id IN (SELECT id FROM tournaments));
DROP POLICY IF EXISTS "tphases_via_tournament" ON tournament_phases;
CREATE POLICY "tphases_via_tournament" ON tournament_phases FOR ALL
  USING (tournament_id IN (SELECT id FROM tournaments)) WITH CHECK (tournament_id IN (SELECT id FROM tournaments));
DROP POLICY IF EXISTS "tgroups_via_tournament" ON tournament_groups;
CREATE POLICY "tgroups_via_tournament" ON tournament_groups FOR ALL
  USING (tournament_id IN (SELECT id FROM tournaments)) WITH CHECK (tournament_id IN (SELECT id FROM tournaments));
DROP POLICY IF EXISTS "tgt_via_tournament" ON tournament_group_teams;
CREATE POLICY "tgt_via_tournament" ON tournament_group_teams FOR ALL
  USING (tournament_id IN (SELECT id FROM tournaments)) WITH CHECK (tournament_id IN (SELECT id FROM tournaments));
DROP POLICY IF EXISTS "tmatches_via_tournament" ON tournament_matches;
CREATE POLICY "tmatches_via_tournament" ON tournament_matches FOR ALL
  USING (tournament_id IN (SELECT id FROM tournaments)) WITH CHECK (tournament_id IN (SELECT id FROM tournaments));

📋 SQL Migration (einmalig ausführen)

ALTER TABLE tournaments        ADD COLUMN IF NOT EXISTS datum_bis  DATE;
ALTER TABLE tournament_matches ADD COLUMN IF NOT EXISTS match_date DATE;
ALTER TABLE tournament_matches ADD COLUMN IF NOT EXISTS start_time TIME;

📋 SQL Migration (einmalig ausführen)

⚠ In 2 Blöcken ausführen — erst Block 1, dann Block 2.

-- ── BLOCK 1: Tabellen anlegen ──────────────────────────────
CREATE TABLE IF NOT EXISTS verbands (
  id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name       TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS verband_clubs (
  id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  verband_id UUID NOT NULL REFERENCES verbands(id) ON DELETE CASCADE,
  club_id    UUID NOT NULL REFERENCES clubs(id) ON DELETE CASCADE,
  UNIQUE (verband_id, club_id)
);

-- user_id ohne FK auf auth.users (Supabase-Einschränkung)
CREATE TABLE IF NOT EXISTS verband_memberships (
  id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  verband_id UUID NOT NULL REFERENCES verbands(id) ON DELETE CASCADE,
  user_id    UUID NOT NULL,
  role       TEXT NOT NULL CHECK (role IN ('verband_admin', 'verband_trainer')),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE (verband_id, user_id)
);

-- ── BLOCK 2: RLS + Constraints (nach Block 1 ausführen) ────
ALTER TABLE verbands ENABLE ROW LEVEL SECURITY;
ALTER TABLE verband_clubs ENABLE ROW LEVEL SECURITY;
ALTER TABLE verband_memberships ENABLE ROW LEVEL SECURITY;

CREATE POLICY "sa_manage_verbands" ON verbands FOR ALL
  USING (EXISTS (SELECT 1 FROM user_profiles WHERE id = auth.uid() AND role = 'superadmin'));
CREATE POLICY "verband_member_read" ON verbands FOR SELECT
  USING (id IN (SELECT verband_id FROM verband_memberships WHERE user_id = auth.uid()));

CREATE POLICY "sa_manage_verband_clubs" ON verband_clubs FOR ALL
  USING (EXISTS (SELECT 1 FROM user_profiles WHERE id = auth.uid() AND role = 'superadmin'));
CREATE POLICY "verband_member_read_clubs" ON verband_clubs FOR SELECT
  USING (verband_id IN (SELECT verband_id FROM verband_memberships WHERE user_id = auth.uid()));

CREATE POLICY "sa_manage_verband_memberships" ON verband_memberships FOR ALL
  USING (EXISTS (SELECT 1 FROM user_profiles WHERE id = auth.uid() AND role = 'superadmin'));
CREATE POLICY "verband_member_read_memberships" ON verband_memberships FOR SELECT
  USING (user_id = auth.uid());

ALTER TABLE user_profiles DROP CONSTRAINT IF EXISTS user_profiles_role_check;
ALTER TABLE user_profiles ADD CONSTRAINT user_profiles_role_check
  CHECK (role IN ('superadmin','admin','trainer','player','verband_admin','verband_trainer'));

📋 Block 3: Verbands-Sessions in training_sessions (einmalig)

Fügt verband_id zu training_sessions hinzu und legt RLS-Policies an.

-- ── Verbands-Sessions Spalte ────────────────────────────────
-- club_id nullable machen (Verbands-Sessions haben kein club_id)
ALTER TABLE training_sessions ALTER COLUMN club_id DROP NOT NULL;

-- typ-Constraint um Verbands-Typen erweitern
ALTER TABLE training_sessions DROP CONSTRAINT IF EXISTS training_sessions_typ_check;
ALTER TABLE training_sessions ADD CONSTRAINT training_sessions_typ_check
  CHECK (typ IN ('sommer', 'eis', 'turnier', 'lehrgang', 'sonstiges'));

ALTER TABLE training_sessions
  ADD COLUMN IF NOT EXISTS verband_id UUID NULL REFERENCES verbands(id) ON DELETE SET NULL;

CREATE INDEX IF NOT EXISTS idx_training_sessions_verband_id
  ON training_sessions(verband_id);

-- ── RLS-Policies für Verbands-Sessions ──────────────────────
DROP POLICY IF EXISTS "verband_member_read_sessions"    ON training_sessions;
DROP POLICY IF EXISTS "verband_admin_manage_sessions"   ON training_sessions;
DROP POLICY IF EXISTS "player_read_verband_sessions"    ON training_sessions;

-- Verband-Mitglieder (Admin/Trainer) dürfen Verbands-Sessions lesen
CREATE POLICY "verband_member_read_sessions" ON training_sessions FOR SELECT
  USING (
    verband_id IS NOT NULL AND
    verband_id IN (SELECT verband_id FROM verband_memberships WHERE user_id = auth.uid())
  );

-- Verband-Admins dürfen Verbands-Sessions erstellen / bearbeiten / löschen
CREATE POLICY "verband_admin_manage_sessions" ON training_sessions
  FOR ALL
  USING (
    verband_id IS NOT NULL AND
    verband_id IN (
      SELECT verband_id FROM verband_memberships
      WHERE user_id = auth.uid() AND role = 'verband_admin'
    )
  )
  WITH CHECK (
    verband_id IS NOT NULL AND
    verband_id IN (
      SELECT verband_id FROM verband_memberships
      WHERE user_id = auth.uid() AND role = 'verband_admin'
    )
  );

-- Spieler dürfen Verbands-Sessions ihres Verbands lesen (Einladungs-Anzeige)
-- (Filterung auf tatsächlich eingeladene Spieler erfolgt client-seitig)
CREATE POLICY "player_read_verband_sessions" ON training_sessions FOR SELECT
  USING (
    verband_id IS NOT NULL AND
    verband_id IN (
      SELECT vc.verband_id FROM verband_clubs vc
      JOIN players p ON p.club_id = vc.club_id
      WHERE p.auth_user_id = auth.uid()
    )
  );

-- Spieler dürfen verband_clubs für ihren Club lesen (um Verband-ID zu ermitteln)
DROP POLICY IF EXISTS "player_read_own_verband_clubs" ON verband_clubs;
CREATE POLICY "player_read_own_verband_clubs" ON verband_clubs FOR SELECT
  USING (
    club_id IN (
      SELECT club_id FROM players WHERE auth_user_id = auth.uid()
    )
  );

-- Verband-Mitglieder dürfen RSVPs ihrer Verbands-Sessions in game_rsvp lesen
DROP POLICY IF EXISTS "verband_member_read_rsvp" ON game_rsvp;
CREATE POLICY "verband_member_read_rsvp" ON game_rsvp FOR SELECT
  USING (
    session_id IN (
      SELECT id FROM training_sessions
      WHERE verband_id IN (
        SELECT verband_id FROM verband_memberships WHERE user_id = auth.uid()
      )
    )
  );

📋 Block 4: Tests in Verbands-Sessions (einmalig)

Fügt verband_id + session_id zu player_tests und cognitive_tests hinzu und setzt RLS-Policies für Verband-Mitglieder.

-- ── Verbands-Tests Spalten ──────────────────────────────────
ALTER TABLE player_tests
  ADD COLUMN IF NOT EXISTS verband_id UUID NULL REFERENCES verbands(id) ON DELETE SET NULL,
  ADD COLUMN IF NOT EXISTS session_id UUID NULL REFERENCES training_sessions(id) ON DELETE SET NULL;

ALTER TABLE cognitive_tests
  ADD COLUMN IF NOT EXISTS verband_id UUID NULL REFERENCES verbands(id) ON DELETE SET NULL,
  ADD COLUMN IF NOT EXISTS session_id UUID NULL REFERENCES training_sessions(id) ON DELETE SET NULL;

CREATE INDEX IF NOT EXISTS idx_player_tests_session_id    ON player_tests(session_id);
CREATE INDEX IF NOT EXISTS idx_cognitive_tests_session_id ON cognitive_tests(session_id);
CREATE INDEX IF NOT EXISTS idx_player_tests_verband_id    ON player_tests(verband_id);
CREATE INDEX IF NOT EXISTS idx_cognitive_tests_verband_id ON cognitive_tests(verband_id);

-- ── RLS-Policies ─────────────────────────────────────────────
DROP POLICY IF EXISTS "verband_member_read_player_tests"    ON player_tests;
DROP POLICY IF EXISTS "verband_admin_insert_player_tests"   ON player_tests;
DROP POLICY IF EXISTS "verband_member_read_cognitive_tests" ON cognitive_tests;
DROP POLICY IF EXISTS "verband_admin_insert_cognitive_tests" ON cognitive_tests;

-- Verband-Mitglieder lesen Tests mit verband_id
CREATE POLICY "verband_member_read_player_tests" ON player_tests FOR SELECT
  USING (verband_id IS NOT NULL AND verband_id IN (
    SELECT verband_id FROM verband_memberships WHERE user_id = auth.uid()
  ));

-- Verband-Admins dürfen Tests anlegen/aktualisieren
CREATE POLICY "verband_admin_insert_player_tests" ON player_tests FOR INSERT
  WITH CHECK (verband_id IS NOT NULL AND verband_id IN (
    SELECT verband_id FROM verband_memberships WHERE user_id = auth.uid() AND role = 'verband_admin'
  ));
CREATE POLICY "verband_admin_update_player_tests" ON player_tests FOR UPDATE
  USING (verband_id IS NOT NULL AND verband_id IN (
    SELECT verband_id FROM verband_memberships WHERE user_id = auth.uid() AND role = 'verband_admin'
  ));

CREATE POLICY "verband_member_read_cognitive_tests" ON cognitive_tests FOR SELECT
  USING (verband_id IS NOT NULL AND verband_id IN (
    SELECT verband_id FROM verband_memberships WHERE user_id = auth.uid()
  ));
CREATE POLICY "verband_admin_insert_cognitive_tests" ON cognitive_tests FOR INSERT
  WITH CHECK (verband_id IS NOT NULL AND verband_id IN (
    SELECT verband_id FROM verband_memberships WHERE user_id = auth.uid() AND role = 'verband_admin'
  ));
CREATE POLICY "verband_admin_update_cognitive_tests" ON cognitive_tests FOR UPDATE
  USING (verband_id IS NOT NULL AND verband_id IN (
    SELECT verband_id FROM verband_memberships WHERE user_id = auth.uid() AND role = 'verband_admin'
  ));

📋 SQL Migration (einmalig ausführen)

CREATE TABLE IF NOT EXISTS wellness_checkins (
  id           UUID     PRIMARY KEY DEFAULT gen_random_uuid(),
  player_id    UUID     NOT NULL REFERENCES players(id) ON DELETE CASCADE,
  club_id      UUID     NOT NULL,
  date         DATE     NOT NULL,
  schlaf       SMALLINT CHECK (schlaf BETWEEN 1 AND 5),
  muskelkater  SMALLINT CHECK (muskelkater BETWEEN 1 AND 5),
  energie      SMALLINT CHECK (energie BETWEEN 1 AND 5),
  stress       SMALLINT CHECK (stress BETWEEN 1 AND 5),
  motivation   SMALLINT CHECK (motivation BETWEEN 1 AND 5),
  rpe          SMALLINT CHECK (rpe BETWEEN 1 AND 10),
  session_id   UUID     REFERENCES training_sessions(id),
  created_at   TIMESTAMPTZ DEFAULT now(),
  UNIQUE(player_id, date)
);
ALTER TABLE wellness_checkins ENABLE ROW LEVEL SECURITY;
CREATE POLICY "player_own_wellness" ON wellness_checkins FOR ALL TO authenticated
  USING (player_id IN (SELECT id FROM players WHERE auth_user_id = auth.uid()))
  WITH CHECK (player_id IN (SELECT id FROM players WHERE auth_user_id = auth.uid()));
CREATE POLICY "trainer_read_wellness" ON wellness_checkins FOR SELECT TO authenticated
  USING (club_id IN (SELECT club_id FROM user_club_memberships WHERE user_id = auth.uid()));
CREATE INDEX IF NOT EXISTS idx_wellness_player_id ON wellness_checkins(player_id);
CREATE INDEX IF NOT EXISTS idx_wellness_date ON wellness_checkins(date);

📋 SQL Migration (einmalig ausführen)

CREATE TABLE IF NOT EXISTS skill_ratings (
  id                 UUID     PRIMARY KEY DEFAULT gen_random_uuid(),
  player_id          UUID     NOT NULL REFERENCES players(id) ON DELETE CASCADE,
  club_id            UUID     NOT NULL,
  rater_id           UUID     REFERENCES user_profiles(id),
  date               DATE     NOT NULL,
  saison             TEXT,
  schlittschuhlaufen INT      CHECK (schlittschuhlaufen BETWEEN 1 AND 10),
  puckhandling       INT      CHECK (puckhandling BETWEEN 1 AND 10),
  passen             INT      CHECK (passen BETWEEN 1 AND 10),
  schiessen          INT      CHECK (schiessen BETWEEN 1 AND 10),
  spielverstaendnis  INT      CHECK (spielverstaendnis BETWEEN 1 AND 10),
  zweikampf          INT      CHECK (zweikampf BETWEEN 1 AND 10),
  notiz              TEXT,
  created_at         TIMESTAMPTZ DEFAULT now()
);
ALTER TABLE skill_ratings ENABLE ROW LEVEL SECURITY;
CREATE POLICY "trainer_manage_skill_ratings" ON skill_ratings FOR ALL TO authenticated
  USING (
    club_id IN (SELECT club_id FROM user_club_memberships WHERE user_id = auth.uid())
    OR EXISTS (SELECT 1 FROM user_profiles WHERE id = auth.uid() AND role = 'superadmin')
  )
  WITH CHECK (
    club_id IN (SELECT club_id FROM user_club_memberships WHERE user_id = auth.uid())
    OR EXISTS (SELECT 1 FROM user_profiles WHERE id = auth.uid() AND role = 'superadmin')
  );
CREATE INDEX IF NOT EXISTS idx_skill_ratings_player_id ON skill_ratings(player_id);
CREATE INDEX IF NOT EXISTS idx_skill_ratings_club_id   ON skill_ratings(club_id);
CREATE INDEX IF NOT EXISTS idx_skill_ratings_date      ON skill_ratings(date);

🔧 RLS-Fix (falls Tabelle bereits angelegt, aber Policy falsch)

DROP POLICY IF EXISTS "trainer_manage_skill_ratings" ON skill_ratings;
CREATE POLICY "trainer_manage_skill_ratings" ON skill_ratings FOR ALL TO authenticated
  USING (
    club_id IN (SELECT club_id FROM user_club_memberships WHERE user_id = auth.uid())
    OR EXISTS (SELECT 1 FROM user_profiles WHERE id = auth.uid() AND role = 'superadmin')
  )
  WITH CHECK (
    club_id IN (SELECT club_id FROM user_club_memberships WHERE user_id = auth.uid())
    OR EXISTS (SELECT 1 FROM user_profiles WHERE id = auth.uid() AND role = 'superadmin')
  );

📋 SQL Migration (einmalig ausführen)

CREATE TABLE IF NOT EXISTS public.drills (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  club_id      UUID NOT NULL REFERENCES clubs(id) ON DELETE CASCADE,
  title        TEXT NOT NULL,
  category     TEXT NOT NULL DEFAULT 'sonstiges',
  surface      TEXT NOT NULL DEFAULT 'eis',
  duration_min INT,
  description  TEXT,
  keypoints    TEXT,
  equipment    TEXT,
  tags         TEXT[] DEFAULT '{}',
  scene        JSONB,
  sketch       TEXT,
  created_by   UUID,
  created_at   TIMESTAMPTZ DEFAULT now(),
  updated_at   TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_drills_club     ON public.drills(club_id);
CREATE INDEX IF NOT EXISTS idx_drills_category ON public.drills(club_id, category);

CREATE OR REPLACE FUNCTION public.drills_touch_updated_at()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN NEW.updated_at := now(); RETURN NEW; END; $$;
DROP TRIGGER IF EXISTS trg_drills_updated_at ON public.drills;
CREATE TRIGGER trg_drills_updated_at BEFORE UPDATE ON public.drills
  FOR EACH ROW EXECUTE FUNCTION public.drills_touch_updated_at();

ALTER TABLE public.drills ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "drills_club_all" ON public.drills;
DROP POLICY IF EXISTS "drills_sa_all"   ON public.drills;
DROP POLICY IF EXISTS "drills_service"  ON public.drills;
CREATE POLICY "drills_club_all" ON public.drills FOR ALL
  USING (club_id IN (SELECT my_club_ids())) WITH CHECK (club_id IN (SELECT my_club_ids()));
CREATE POLICY "drills_sa_all" ON public.drills FOR ALL
  USING (is_superadmin()) WITH CHECK (is_superadmin());
CREATE POLICY "drills_service" ON public.drills FOR ALL
  USING (auth.role() = 'service_role') WITH CHECK (auth.role() = 'service_role');

📋 SQL Migration (einmalig ausführen)

ALTER TABLE public.drills
  ADD COLUMN IF NOT EXISTS visibility TEXT NOT NULL DEFAULT 'club'
    CHECK (visibility IN ('club', 'private'));

DROP POLICY IF EXISTS "drills_club_all" ON public.drills;
CREATE POLICY "drills_club_all" ON public.drills FOR ALL
  USING (
    club_id IN (SELECT my_club_ids())
    AND (visibility = 'club' OR created_by = auth.uid())
  )
  WITH CHECK (
    club_id IN (SELECT my_club_ids())
    AND (visibility = 'club' OR created_by = auth.uid())
  );

📋 SQL Migration (einmalig ausführen)

CREATE OR REPLACE FUNCTION public.drills_guard_created_by()
RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
BEGIN
  IF public.is_superadmin() OR auth.role() = 'service_role' THEN
    RETURN NEW;
  END IF;
  IF TG_OP = 'INSERT' THEN
    NEW.created_by := auth.uid();
  ELSIF NEW.created_by IS DISTINCT FROM OLD.created_by THEN
    RAISE EXCEPTION 'created_by ist unveraenderlich';
  END IF;
  RETURN NEW;
END; $$;

DROP TRIGGER IF EXISTS trg_drills_guard_created_by ON public.drills;
CREATE TRIGGER trg_drills_guard_created_by
  BEFORE INSERT OR UPDATE ON public.drills
  FOR EACH ROW EXECUTE FUNCTION public.drills_guard_created_by();

📋 SQL Migration (einmalig ausführen)

ALTER TABLE public.drills REPLICA IDENTITY FULL;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_publication_tables
    WHERE pubname = 'supabase_realtime' AND schemaname = 'public' AND tablename = 'drills'
  ) THEN
    ALTER PUBLICATION supabase_realtime ADD TABLE public.drills;
  END IF;
END $$;

📋 SQL Migration (einmalig ausführen)

-- Orga (Mitbringsel/Dienste/Aufgaben) — Phase 1, Konzept KONZEPT_ORGA_DIENSTE.md
-- event_title/event_date sind denormalisiert: Spieler haben keine memberships
-- und können tournaments nicht lesen — die Spieler-Ansicht braucht sie so nicht.

CREATE TABLE IF NOT EXISTS event_tasks (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  club_id       UUID NOT NULL REFERENCES clubs(id) ON DELETE CASCADE,
  tournament_id UUID REFERENCES tournaments(id)       ON DELETE CASCADE,
  session_id    UUID REFERENCES training_sessions(id) ON DELETE CASCADE,
  kind          TEXT NOT NULL CHECK (kind IN ('mitbringsel','dienst','aufgabe')),
  title         TEXT NOT NULL,
  beschreibung  TEXT,
  menge_soll    NUMERIC CHECK (menge_soll IS NULL OR (menge_soll > 0 AND menge_soll <= 1000)),
  einheit       TEXT,
  event_title   TEXT,
  event_date    DATE,
  sort_order    INT DEFAULT 0,
  created_by    UUID REFERENCES user_profiles(id) ON DELETE SET NULL,
  created_at    TIMESTAMPTZ DEFAULT now(),
  CHECK (num_nonnulls(tournament_id, session_id) = 1)
);

CREATE TABLE IF NOT EXISTS event_task_slots (
  id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  task_id    UUID NOT NULL REFERENCES event_tasks(id) ON DELETE CASCADE,
  club_id    UUID NOT NULL REFERENCES clubs(id) ON DELETE CASCADE,
  start_ts   TIMESTAMPTZ NOT NULL,
  end_ts     TIMESTAMPTZ NOT NULL,
  kapazitaet INT NOT NULL DEFAULT 1 CHECK (kapazitaet BETWEEN 1 AND 50),
  CHECK (end_ts > start_ts)
);

CREATE TABLE IF NOT EXISTS event_task_signups (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  task_id     UUID NOT NULL REFERENCES event_tasks(id)      ON DELETE CASCADE,
  slot_id     UUID REFERENCES event_task_slots(id)          ON DELETE CASCADE,
  club_id     UUID NOT NULL REFERENCES clubs(id)            ON DELETE CASCADE,
  player_id   UUID REFERENCES players(id)                   ON DELETE CASCADE,
  name        TEXT,
  menge       NUMERIC NOT NULL DEFAULT 1 CHECK (menge > 0 AND menge <= 100),
  notiz       TEXT,
  created_at  TIMESTAMPTZ DEFAULT now(),
  CHECK (player_id IS NOT NULL OR name IS NOT NULL)
);

-- edit_secret entfernt (Row-Level-RLS schützt Spalten nicht — Phase 2 nutzt
-- eine separate service-role-only Tabelle); Drop für Erst-Version-Instanzen:
ALTER TABLE event_task_signups DROP COLUMN IF EXISTS edit_secret;

CREATE INDEX IF NOT EXISTS idx_etasks_tour   ON event_tasks(tournament_id);
CREATE INDEX IF NOT EXISTS idx_etasks_sess   ON event_tasks(session_id);
CREATE INDEX IF NOT EXISTS idx_etasks_club   ON event_tasks(club_id);
CREATE INDEX IF NOT EXISTS idx_etasks_date   ON event_tasks(event_date);
CREATE INDEX IF NOT EXISTS idx_eslots_task   ON event_task_slots(task_id);
CREATE INDEX IF NOT EXISTS idx_eslots_start  ON event_task_slots(start_ts);
CREATE INDEX IF NOT EXISTS idx_esign_task    ON event_task_signups(task_id);
CREATE INDEX IF NOT EXISTS idx_esign_slot    ON event_task_signups(slot_id);
CREATE INDEX IF NOT EXISTS idx_esign_player  ON event_task_signups(player_id);

ALTER TABLE event_tasks        ENABLE ROW LEVEL SECURITY;
ALTER TABLE event_task_slots   ENABLE ROW LEVEL SECURITY;
ALTER TABLE event_task_signups ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "etasks_club_all"    ON event_tasks;
DROP POLICY IF EXISTS "etasks_player_read" ON event_tasks;
DROP POLICY IF EXISTS "etasks_sa_all"      ON event_tasks;
DROP POLICY IF EXISTS "etasks_service"     ON event_tasks;
CREATE POLICY "etasks_club_all" ON event_tasks FOR ALL
  USING (club_id IN (SELECT my_club_ids()))
  WITH CHECK (club_id IN (SELECT my_club_ids()));
CREATE POLICY "etasks_player_read" ON event_tasks FOR SELECT
  USING (club_id IN (SELECT club_id FROM players WHERE auth_user_id = auth.uid()));
CREATE POLICY "etasks_sa_all" ON event_tasks FOR ALL
  USING (is_superadmin()) WITH CHECK (is_superadmin());
CREATE POLICY "etasks_service" ON event_tasks FOR ALL
  USING (auth.role() = 'service_role') WITH CHECK (auth.role() = 'service_role');

DROP POLICY IF EXISTS "eslots_club_all"    ON event_task_slots;
DROP POLICY IF EXISTS "eslots_player_read" ON event_task_slots;
DROP POLICY IF EXISTS "eslots_sa_all"      ON event_task_slots;
DROP POLICY IF EXISTS "eslots_service"     ON event_task_slots;
CREATE POLICY "eslots_club_all" ON event_task_slots FOR ALL
  USING (club_id IN (SELECT my_club_ids()))
  WITH CHECK (club_id IN (SELECT my_club_ids()));
CREATE POLICY "eslots_player_read" ON event_task_slots FOR SELECT
  USING (club_id IN (SELECT club_id FROM players WHERE auth_user_id = auth.uid()));
CREATE POLICY "eslots_sa_all" ON event_task_slots FOR ALL
  USING (is_superadmin()) WITH CHECK (is_superadmin());
CREATE POLICY "eslots_service" ON event_task_slots FOR ALL
  USING (auth.role() = 'service_role') WITH CHECK (auth.role() = 'service_role');

DROP POLICY IF EXISTS "esign_club_all"    ON event_task_signups;
DROP POLICY IF EXISTS "esign_player_read" ON event_task_signups;
DROP POLICY IF EXISTS "esign_player_own"  ON event_task_signups;
DROP POLICY IF EXISTS "esign_sa_all"      ON event_task_signups;
DROP POLICY IF EXISTS "esign_service"     ON event_task_signups;
CREATE POLICY "esign_club_all" ON event_task_signups FOR ALL
  USING (club_id IN (SELECT my_club_ids()))
  WITH CHECK (club_id IN (SELECT my_club_ids()));
CREATE POLICY "esign_player_read" ON event_task_signups FOR SELECT
  USING (club_id IN (SELECT club_id FROM players WHERE auth_user_id = auth.uid()));
CREATE POLICY "esign_player_own" ON event_task_signups FOR ALL
  USING (player_id IN (SELECT id FROM players WHERE auth_user_id = auth.uid()))
  WITH CHECK (player_id IN (SELECT id FROM players WHERE auth_user_id = auth.uid()));
CREATE POLICY "esign_sa_all" ON event_task_signups FOR ALL
  USING (is_superadmin()) WITH CHECK (is_superadmin());
CREATE POLICY "esign_service" ON event_task_signups FOR ALL
  USING (auth.role() = 'service_role') WITH CHECK (auth.role() = 'service_role');

CREATE OR REPLACE FUNCTION public.orga_signup(
  p_task_id uuid,
  p_slot_id uuid    DEFAULT NULL,
  p_name    text    DEFAULT NULL,
  p_menge   numeric DEFAULT 1,
  p_notiz   text    DEFAULT NULL
) RETURNS jsonb
LANGUAGE plpgsql SECURITY DEFINER
SET search_path = public AS $$
DECLARE
  v_task     event_tasks%ROWTYPE;
  v_slot     event_task_slots%ROWTYPE;
  v_pid      uuid;
  v_is_staff boolean;
  v_cnt      numeric;
  v_id       uuid;
BEGIN
  IF auth.uid() IS NULL THEN
    RETURN jsonb_build_object('ok', false, 'error', 'FORBIDDEN');
  END IF;

  SELECT * INTO v_task FROM event_tasks WHERE id = p_task_id;
  IF NOT FOUND THEN
    RETURN jsonb_build_object('ok', false, 'error', 'NOT_FOUND');
  END IF;

  SELECT id INTO v_pid FROM players
    WHERE auth_user_id = auth.uid() AND club_id = v_task.club_id LIMIT 1;
  v_is_staff := v_task.club_id IN (SELECT my_club_ids()) OR is_superadmin();
  IF v_pid IS NULL AND NOT v_is_staff THEN
    RETURN jsonb_build_object('ok', false, 'error', 'FORBIDDEN');
  END IF;

  p_menge := COALESCE(p_menge, 1);
  IF p_menge <= 0 OR p_menge > 100 THEN
    RETURN jsonb_build_object('ok', false, 'error', 'BAD_MENGE');
  END IF;
  IF v_task.kind <> 'mitbringsel' THEN p_menge := 1; END IF;
  p_name  := NULLIF(left(btrim(COALESCE(p_name,  '')),  80), '');
  p_notiz := NULLIF(left(btrim(COALESCE(p_notiz, '')), 200), '');

  IF v_pid IS NULL AND p_name IS NULL THEN
    RETURN jsonb_build_object('ok', false, 'error', 'NAME_REQUIRED');
  END IF;

  IF p_slot_id IS NOT NULL THEN
    SELECT * INTO v_slot FROM event_task_slots
      WHERE id = p_slot_id AND task_id = p_task_id FOR UPDATE;
    IF NOT FOUND THEN
      RETURN jsonb_build_object('ok', false, 'error', 'NOT_FOUND');
    END IF;
    SELECT count(*) INTO v_cnt FROM event_task_signups WHERE slot_id = p_slot_id;
    IF v_cnt >= v_slot.kapazitaet THEN
      RETURN jsonb_build_object('ok', false, 'error', 'FULL');
    END IF;
  ELSIF v_task.kind = 'dienst' THEN
    RETURN jsonb_build_object('ok', false, 'error', 'SLOT_REQUIRED');
  ELSIF v_task.kind = 'aufgabe' THEN
    PERFORM 1 FROM event_tasks WHERE id = p_task_id FOR UPDATE;
    SELECT count(*) INTO v_cnt FROM event_task_signups WHERE task_id = p_task_id;
    IF v_cnt >= 1 THEN
      RETURN jsonb_build_object('ok', false, 'error', 'FULL');
    END IF;
  END IF;

  INSERT INTO event_task_signups (task_id, slot_id, club_id, player_id, name, menge, notiz)
  VALUES (p_task_id, p_slot_id, v_task.club_id, v_pid, p_name, p_menge, p_notiz)
  RETURNING id INTO v_id;

  RETURN jsonb_build_object('ok', true, 'signup_id', v_id);
END; $$;

GRANT EXECUTE ON FUNCTION public.orga_signup(uuid, uuid, text, numeric, text) TO authenticated;

📋 SQL Migration (einmalig ausführen)

ALTER TABLE public.event_tasks        REPLICA IDENTITY FULL;
ALTER TABLE public.event_task_slots   REPLICA IDENTITY FULL;
ALTER TABLE public.event_task_signups REPLICA IDENTITY FULL;

DO $$
DECLARE t TEXT;
BEGIN
  FOREACH t IN ARRAY ARRAY['event_tasks','event_task_slots','event_task_signups'] LOOP
    IF NOT EXISTS (
      SELECT 1 FROM pg_publication_tables
      WHERE pubname = 'supabase_realtime' AND schemaname = 'public' AND tablename = t
    ) THEN
      EXECUTE format('ALTER PUBLICATION supabase_realtime ADD TABLE public.%I', t);
    END IF;
  END LOOP;
END $$;

📋 SQL Migration (einmalig ausführen)

ALTER TABLE public.club_announcements REPLICA IDENTITY FULL;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_publication_tables
    WHERE pubname = 'supabase_realtime' AND schemaname = 'public' AND tablename = 'club_announcements'
  ) THEN
    ALTER PUBLICATION supabase_realtime ADD TABLE public.club_announcements;
  END IF;
END $$;

📋 SQL Migration (einmalig ausführen)

ALTER TABLE public.wellness_checkins REPLICA IDENTITY FULL;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_publication_tables
    WHERE pubname = 'supabase_realtime' AND schemaname = 'public' AND tablename = 'wellness_checkins'
  ) THEN
    ALTER PUBLICATION supabase_realtime ADD TABLE public.wellness_checkins;
  END IF;
END $$;

📋 SQL Migration (einmalig ausführen)

-- 1) Tabellen + Indizes
CREATE TABLE IF NOT EXISTS public.session_templates (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  club_id      UUID NOT NULL REFERENCES clubs(id) ON DELETE CASCADE,
  title        TEXT NOT NULL,
  focus        TEXT,
  visibility   TEXT NOT NULL DEFAULT 'club' CHECK (visibility IN ('club', 'private')),
  created_by   UUID,
  created_at   TIMESTAMPTZ DEFAULT now(),
  updated_at   TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_session_templates_club ON public.session_templates(club_id);

CREATE TABLE IF NOT EXISTS public.phase_catalog (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  club_id      UUID NOT NULL REFERENCES clubs(id) ON DELETE CASCADE,
  duration_min INT,
  name         TEXT NOT NULL,
  content      TEXT,
  hint         TEXT,
  visibility   TEXT NOT NULL DEFAULT 'club' CHECK (visibility IN ('club', 'private')),
  created_by   UUID,
  created_at   TIMESTAMPTZ DEFAULT now(),
  updated_at   TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_phase_catalog_club ON public.phase_catalog(club_id);

-- 2) updated_at-Trigger
CREATE OR REPLACE FUNCTION public.training_reuse_touch_updated_at()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$;
DROP TRIGGER IF EXISTS trg_session_templates_updated_at ON public.session_templates;
CREATE TRIGGER trg_session_templates_updated_at
  BEFORE UPDATE ON public.session_templates
  FOR EACH ROW EXECUTE FUNCTION public.training_reuse_touch_updated_at();
DROP TRIGGER IF EXISTS trg_phase_catalog_updated_at ON public.phase_catalog;
CREATE TRIGGER trg_phase_catalog_updated_at
  BEFORE UPDATE ON public.phase_catalog
  FOR EACH ROW EXECUTE FUNCTION public.training_reuse_touch_updated_at();

-- 3) created_by-Guard (F-216-Muster)
CREATE OR REPLACE FUNCTION public.training_reuse_guard_created_by()
RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
BEGIN
  IF public.is_superadmin() OR auth.role() = 'service_role' THEN
    RETURN NEW;
  END IF;
  IF TG_OP = 'INSERT' THEN
    NEW.created_by := auth.uid();
  ELSIF NEW.created_by IS DISTINCT FROM OLD.created_by THEN
    RAISE EXCEPTION 'created_by ist unveraenderlich';
  END IF;
  RETURN NEW;
END;
$$;
DROP TRIGGER IF EXISTS trg_session_templates_guard_created_by ON public.session_templates;
CREATE TRIGGER trg_session_templates_guard_created_by
  BEFORE INSERT OR UPDATE ON public.session_templates
  FOR EACH ROW EXECUTE FUNCTION public.training_reuse_guard_created_by();
DROP TRIGGER IF EXISTS trg_phase_catalog_guard_created_by ON public.phase_catalog;
CREATE TRIGGER trg_phase_catalog_guard_created_by
  BEFORE INSERT OR UPDATE ON public.phase_catalog
  FOR EACH ROW EXECUTE FUNCTION public.training_reuse_guard_created_by();

-- 4) RLS (P0-Muster + Sichtbarkeit; kein user_profiles-Zugriff, Fallstrick #112)
ALTER TABLE public.session_templates ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.phase_catalog     ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "session_templates_club_all" ON public.session_templates;
DROP POLICY IF EXISTS "session_templates_sa_all"   ON public.session_templates;
DROP POLICY IF EXISTS "session_templates_service"  ON public.session_templates;
CREATE POLICY "session_templates_club_all" ON public.session_templates FOR ALL
  USING (club_id IN (SELECT my_club_ids())
    AND (visibility = 'club' OR created_by = auth.uid()))
  WITH CHECK (club_id IN (SELECT my_club_ids())
    AND (visibility = 'club' OR created_by = auth.uid()));
CREATE POLICY "session_templates_sa_all" ON public.session_templates FOR ALL
  USING (is_superadmin()) WITH CHECK (is_superadmin());
CREATE POLICY "session_templates_service" ON public.session_templates FOR ALL
  USING (auth.role() = 'service_role') WITH CHECK (auth.role() = 'service_role');

DROP POLICY IF EXISTS "phase_catalog_club_all" ON public.phase_catalog;
DROP POLICY IF EXISTS "phase_catalog_sa_all"   ON public.phase_catalog;
DROP POLICY IF EXISTS "phase_catalog_service"  ON public.phase_catalog;
CREATE POLICY "phase_catalog_club_all" ON public.phase_catalog FOR ALL
  USING (club_id IN (SELECT my_club_ids())
    AND (visibility = 'club' OR created_by = auth.uid()))
  WITH CHECK (club_id IN (SELECT my_club_ids())
    AND (visibility = 'club' OR created_by = auth.uid()));
CREATE POLICY "phase_catalog_sa_all" ON public.phase_catalog FOR ALL
  USING (is_superadmin()) WITH CHECK (is_superadmin());
CREATE POLICY "phase_catalog_service" ON public.phase_catalog FOR ALL
  USING (auth.role() = 'service_role') WITH CHECK (auth.role() = 'service_role');

📋 SQL Migration (einmalig ausführen)

-- 1) Tabellen + Indizes
CREATE TABLE IF NOT EXISTS public.tournament_team_players (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tournament_id UUID NOT NULL REFERENCES tournaments(id) ON DELETE CASCADE,
  team_id       UUID NOT NULL REFERENCES tournament_teams(id) ON DELETE CASCADE,
  jersey_no     INT,
  name          TEXT NOT NULL,
  position      TEXT,
  player_id     UUID REFERENCES players(id) ON DELETE SET NULL,
  created_at    TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_ttp_tournament ON public.tournament_team_players(tournament_id);
CREATE INDEX IF NOT EXISTS idx_ttp_team       ON public.tournament_team_players(team_id);

CREATE TABLE IF NOT EXISTS public.tournament_match_events (
  id             UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tournament_id  UUID NOT NULL REFERENCES tournaments(id) ON DELETE CASCADE,
  match_id       UUID NOT NULL REFERENCES tournament_matches(id) ON DELETE CASCADE,
  team_id        UUID NOT NULL REFERENCES tournament_teams(id) ON DELETE CASCADE,
  type           TEXT NOT NULL CHECK (type IN ('goal','penalty')),
  period         INT,
  game_time      TEXT,
  player_id      UUID REFERENCES tournament_team_players(id) ON DELETE SET NULL,
  assist1_id     UUID REFERENCES tournament_team_players(id) ON DELETE SET NULL,
  assist2_id     UUID REFERENCES tournament_team_players(id) ON DELETE SET NULL,
  penalty_min    INT,
  penalty_reason TEXT,
  created_at     TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_tme_tournament ON public.tournament_match_events(tournament_id);
CREATE INDEX IF NOT EXISTS idx_tme_match      ON public.tournament_match_events(match_id);

-- Torschüsse pro Drittel (Stufe 3): aggregierte Zahlen am Spiel, kein Personenbezug
ALTER TABLE public.tournament_matches ADD COLUMN IF NOT EXISTS stats JSONB DEFAULT NULL;

-- 2) RLS — Kind-Tabellen-Erbmuster (wie tournament_teams etc.)
ALTER TABLE public.tournament_team_players ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.tournament_match_events ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "ttp_via_tournament" ON public.tournament_team_players;
CREATE POLICY "ttp_via_tournament" ON public.tournament_team_players FOR ALL
  USING (tournament_id IN (SELECT id FROM tournaments))
  WITH CHECK (tournament_id IN (SELECT id FROM tournaments));

DROP POLICY IF EXISTS "tme_via_tournament" ON public.tournament_match_events;
CREATE POLICY "tme_via_tournament" ON public.tournament_match_events FOR ALL
  USING (tournament_id IN (SELECT id FROM tournaments))
  WITH CHECK (tournament_id IN (SELECT id FROM tournaments));

📋 SQL Migration (einmalig ausführen)

-- 1) Spalten
ALTER TABLE public.clubs ADD COLUMN IF NOT EXISTS modules JSONB DEFAULT NULL;
ALTER TABLE public.clubs ADD COLUMN IF NOT EXISTS trial_until DATE DEFAULT NULL;

-- 2) Schutz-Trigger (P0-Muster wie protect_club_plan)
CREATE OR REPLACE FUNCTION public.protect_club_modules()
RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER
SET search_path = public AS $$
BEGIN
  IF public.is_superadmin() OR current_setting('role', true) = 'service_role' THEN
    RETURN NEW;
  END IF;
  IF NEW.modules IS DISTINCT FROM OLD.modules THEN
    RAISE EXCEPTION 'clubs.modules darf nur vom Superadmin geändert werden';
  END IF;
  IF NEW.trial_until IS DISTINCT FROM OLD.trial_until THEN
    RAISE EXCEPTION 'clubs.trial_until darf nur vom Superadmin geändert werden';
  END IF;
  RETURN NEW;
END; $$;

DROP TRIGGER IF EXISTS trg_protect_club_modules ON public.clubs;
CREATE TRIGGER trg_protect_club_modules
  BEFORE UPDATE ON public.clubs
  FOR EACH ROW EXECUTE FUNCTION public.protect_club_modules();

📋 SQL Migration (einmalig ausführen)

CREATE TABLE IF NOT EXISTS public.push_tokens (
  id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  player_id  UUID NOT NULL REFERENCES public.players(id) ON DELETE CASCADE,
  club_id    UUID NOT NULL REFERENCES public.clubs(id)   ON DELETE CASCADE,
  platform   TEXT NOT NULL CHECK (platform IN ('ios', 'android')),
  token      TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE (player_id, platform, token)
);

CREATE INDEX IF NOT EXISTS idx_push_tokens_club_id   ON public.push_tokens(club_id);
CREATE INDEX IF NOT EXISTS idx_push_tokens_player_id ON public.push_tokens(player_id);

ALTER TABLE public.push_tokens ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "player_manage_own_push_token" ON public.push_tokens;
CREATE POLICY "player_manage_own_push_token" ON public.push_tokens
  FOR ALL TO authenticated
  USING     (player_id IN (SELECT id FROM public.players WHERE auth_user_id = auth.uid()))
  WITH CHECK (player_id IN (SELECT id FROM public.players WHERE auth_user_id = auth.uid()));

DROP POLICY IF EXISTS "sa_read_push_tokens" ON public.push_tokens;
CREATE POLICY "sa_read_push_tokens" ON public.push_tokens
  FOR SELECT TO authenticated
  USING (public.is_superadmin());