-- RLS helpers and policies

ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE leagues ENABLE ROW LEVEL SECURITY;
ALTER TABLE league_rules ENABLE ROW LEVEL SECURITY;
ALTER TABLE competitions ENABLE ROW LEVEL SECURITY;
ALTER TABLE players ENABLE ROW LEVEL SECURITY;
ALTER TABLE teams ENABLE ROW LEVEL SECURITY;
ALTER TABLE team_members ENABLE ROW LEVEL SECURITY;
ALTER TABLE team_availability ENABLE ROW LEVEL SECURITY;
ALTER TABLE team_roster ENABLE ROW LEVEL SECURITY;
ALTER TABLE roster_events ENABLE ROW LEVEL SECURITY;
ALTER TABLE transfers ENABLE ROW LEVEL SECURITY;
ALTER TABLE competition_league_managers ENABLE ROW LEVEL SECURITY;
ALTER TABLE matches ENABLE ROW LEVEL SECURITY;
ALTER TABLE match_signatures ENABLE ROW LEVEL SECURITY;
ALTER TABLE match_result_submissions ENABLE ROW LEVEL SECURITY;
ALTER TABLE match_player_stats ENABLE ROW LEVEL SECURITY;

-- Helper: is competition participant
CREATE OR REPLACE FUNCTION is_competition_participant(p_competition_id UUID)
RETURNS BOOLEAN AS $$
  SELECT EXISTS (
    SELECT 1 FROM team_members tm
    JOIN teams t ON t.id = tm.team_id
    WHERE t.competition_id = p_competition_id AND tm.user_id = auth.uid()
  )
  OR EXISTS (
    SELECT 1 FROM competition_league_managers
    WHERE competition_id = p_competition_id AND user_id = auth.uid()
  )
  OR EXISTS (
    SELECT 1 FROM competitions c
    JOIN leagues l ON l.id = c.league_id
    WHERE c.id = p_competition_id AND (c.created_by = auth.uid() OR l.created_by = auth.uid())
  );
$$ LANGUAGE sql SECURITY DEFINER STABLE;

CREATE OR REPLACE FUNCTION is_league_organizer(p_league_id UUID)
RETURNS BOOLEAN AS $$
  SELECT EXISTS (
    SELECT 1 FROM leagues WHERE id = p_league_id AND created_by = auth.uid()
  );
$$ LANGUAGE sql SECURITY DEFINER STABLE;

CREATE OR REPLACE FUNCTION is_competition_league_manager(p_competition_id UUID)
RETURNS BOOLEAN AS $$
  SELECT EXISTS (
    SELECT 1 FROM competition_league_managers
    WHERE competition_id = p_competition_id AND user_id = auth.uid()
  );
$$ LANGUAGE sql SECURITY DEFINER STABLE;

CREATE OR REPLACE FUNCTION is_team_manager(p_team_id UUID)
RETURNS BOOLEAN AS $$
  SELECT EXISTS (
    SELECT 1 FROM team_members
    WHERE team_id = p_team_id AND user_id = auth.uid() AND role = 'manager'
  );
$$ LANGUAGE sql SECURITY DEFINER STABLE;

CREATE OR REPLACE FUNCTION is_team_captain_or_manager(p_team_id UUID)
RETURNS BOOLEAN AS $$
  SELECT EXISTS (
    SELECT 1 FROM team_members
    WHERE team_id = p_team_id AND user_id = auth.uid()
  );
$$ LANGUAGE sql SECURITY DEFINER STABLE;

-- Profiles
CREATE POLICY profiles_select ON profiles FOR SELECT TO authenticated USING (true);
CREATE POLICY profiles_update ON profiles FOR UPDATE TO authenticated USING (id = auth.uid());

-- Leagues
CREATE POLICY leagues_select ON leagues FOR SELECT TO authenticated USING (true);
CREATE POLICY leagues_insert ON leagues FOR INSERT TO authenticated WITH CHECK (created_by = auth.uid());
CREATE POLICY leagues_update ON leagues FOR UPDATE TO authenticated USING (created_by = auth.uid());

-- League rules
CREATE POLICY league_rules_select ON league_rules FOR SELECT TO authenticated USING (true);
CREATE POLICY league_rules_insert ON league_rules FOR INSERT TO authenticated
  WITH CHECK (is_league_organizer(league_id));

-- Competitions
CREATE POLICY competitions_select ON competitions FOR SELECT TO authenticated
  USING (is_competition_participant(id) OR is_league_organizer(league_id));
CREATE POLICY competitions_insert ON competitions FOR INSERT TO authenticated
  WITH CHECK (is_league_organizer(league_id));
CREATE POLICY competitions_update ON competitions FOR UPDATE TO authenticated
  USING (is_league_organizer(league_id));

-- Players (global registry)
CREATE POLICY players_select ON players FOR SELECT TO authenticated USING (true);
CREATE POLICY players_insert ON players FOR INSERT TO authenticated WITH CHECK (true);
CREATE POLICY players_update ON players FOR UPDATE TO authenticated USING (true);

-- Teams
CREATE POLICY teams_select ON teams FOR SELECT TO authenticated
  USING (is_competition_participant(competition_id));
CREATE POLICY teams_insert ON teams FOR INSERT TO authenticated
  WITH CHECK (
    EXISTS (
      SELECT 1 FROM competitions c
      WHERE c.id = competition_id AND is_league_organizer(c.league_id)
    )
  );
CREATE POLICY teams_update ON teams FOR UPDATE TO authenticated
  USING (is_team_manager(id) OR EXISTS (
    SELECT 1 FROM competitions c WHERE c.id = competition_id AND is_league_organizer(c.league_id)
  ));

-- Team members
CREATE POLICY team_members_select ON team_members FOR SELECT TO authenticated
  USING (EXISTS (SELECT 1 FROM teams t WHERE t.id = team_id AND is_competition_participant(t.competition_id)));
CREATE POLICY team_members_insert ON team_members FOR INSERT TO authenticated
  WITH CHECK (EXISTS (
    SELECT 1 FROM teams t JOIN competitions c ON c.id = t.competition_id
    WHERE t.id = team_id AND is_league_organizer(c.league_id)
  ));

-- Team availability
CREATE POLICY team_availability_all ON team_availability FOR ALL TO authenticated
  USING (is_team_manager(team_id))
  WITH CHECK (is_team_manager(team_id));

-- Team roster / events / transfers - read for participants
CREATE POLICY team_roster_select ON team_roster FOR SELECT TO authenticated
  USING (is_competition_participant(competition_id));

CREATE POLICY roster_events_select ON roster_events FOR SELECT TO authenticated
  USING (is_competition_participant(competition_id));
CREATE POLICY roster_events_insert ON roster_events FOR INSERT TO authenticated
  WITH CHECK (is_team_manager(team_id));

CREATE POLICY transfers_select ON transfers FOR SELECT TO authenticated
  USING (is_competition_participant(competition_id));
CREATE POLICY transfers_insert ON transfers FOR INSERT TO authenticated
  WITH CHECK (is_team_manager(from_team_id));

-- Competition league managers
CREATE POLICY clm_select ON competition_league_managers FOR SELECT TO authenticated
  USING (is_competition_participant(competition_id));
CREATE POLICY clm_insert ON competition_league_managers FOR INSERT TO authenticated
  WITH CHECK (EXISTS (
    SELECT 1 FROM competitions c WHERE c.id = competition_id AND is_league_organizer(c.league_id)
  ));

-- Matches
CREATE POLICY matches_select ON matches FOR SELECT TO authenticated
  USING (is_competition_participant(competition_id));
CREATE POLICY matches_update ON matches FOR UPDATE TO authenticated
  USING (is_competition_league_manager(competition_id));

-- Match signatures
CREATE POLICY match_signatures_select ON match_signatures FOR SELECT TO authenticated
  USING (EXISTS (SELECT 1 FROM matches m WHERE m.id = match_id AND is_competition_participant(m.competition_id)));
CREATE POLICY match_signatures_insert ON match_signatures FOR INSERT TO authenticated
  WITH CHECK (is_team_captain_or_manager(team_id));

-- Result submissions
CREATE POLICY result_submissions_select ON match_result_submissions FOR SELECT TO authenticated
  USING (EXISTS (SELECT 1 FROM matches m WHERE m.id = match_id AND is_competition_participant(m.competition_id)));
CREATE POLICY result_submissions_insert ON match_result_submissions FOR INSERT TO authenticated
  WITH CHECK (is_team_captain_or_manager(team_id));

-- Player stats
CREATE POLICY match_player_stats_select ON match_player_stats FOR SELECT TO authenticated
  USING (EXISTS (SELECT 1 FROM matches m WHERE m.id = match_id AND is_competition_participant(m.competition_id)));
CREATE POLICY match_player_stats_insert ON match_player_stats FOR INSERT TO authenticated
  WITH CHECK (is_team_captain_or_manager(team_id));