-- Fix infinite recursion in members RLS.
--
-- The original "Admins can read all members" and "Admins can manage all members"
-- policies check role by querying members itself, which causes infinite recursion
-- when any authenticated user accesses the members table.
--
-- Solution: SECURITY DEFINER helper functions that bypass RLS to check the caller's
-- role, then reference those functions from the policies.

create or replace function public.current_user_role()
  returns text
  language sql
  security definer
  stable
  set search_path = public
as $$
  select role from public.members where user_id = auth.uid() limit 1;
$$;

create or replace function public.current_user_has_role(roles text[])
  returns boolean
  language sql
  security definer
  stable
  set search_path = public
as $$
  select exists (
    select 1 from public.members
    where user_id = auth.uid() and role = any(roles)
  );
$$;

-- Drop the recursive policies on members
drop policy if exists "Admins can read all members" on public.members;
drop policy if exists "Admins can manage all members" on public.members;

-- Replace with non-recursive equivalents using the SECURITY DEFINER helper
create policy "Admins can read all members" on public.members
  for select using (public.current_user_has_role(array['admin', 'boatswain', 'instructor']));

create policy "Admins can manage all members" on public.members
  for all using (public.current_user_has_role(array['admin']));

-- Also fix all other tables that query members inline (same recursion risk)
drop policy if exists "Admins can manage boats" on public.boats;
create policy "Admins can manage boats" on public.boats
  for all using (public.current_user_has_role(array['admin', 'boatswain']));

drop policy if exists "Admins can manage interval templates" on public.interval_templates;
create policy "Admins can manage interval templates" on public.interval_templates
  for all using (public.current_user_has_role(array['admin', 'boatswain']));

drop policy if exists "Admins can manage intervals" on public.intervals;
create policy "Admins can manage intervals" on public.intervals
  for all using (public.current_user_has_role(array['admin', 'boatswain']));

drop policy if exists "Admins can read all reservations" on public.reservations;
create policy "Admins can read all reservations" on public.reservations
  for select using (public.current_user_has_role(array['admin', 'boatswain']));

drop policy if exists "Admins can manage all reservations" on public.reservations;
create policy "Admins can manage all reservations" on public.reservations
  for all using (public.current_user_has_role(array['admin', 'boatswain']));

drop policy if exists "Admins can manage reference docs" on public.reference_docs;
create policy "Admins can manage reference docs" on public.reference_docs
  for all using (public.current_user_has_role(array['admin']));

-- booking_config and holidays policies (added in later migration, same pattern)
drop policy if exists "Admins can manage booking config" on public.booking_config;
create policy "Admins can manage booking config" on public.booking_config
  for all using (public.current_user_has_role(array['admin']));

drop policy if exists "Admins can manage holidays" on public.holidays;
create policy "Admins can manage holidays" on public.holidays
  for all using (public.current_user_has_role(array['admin']));