-- Fix: the `members_co_member_select` policy added in 0003 had a recursive
-- subquery (organization_members referencing itself in its USING clause).
-- Postgres silently returned no rows, which broke the orgs_member_select
-- subquery and made the app think Rian wasn't a member of Bowden Works.
--
-- Replace the recursion with a SECURITY DEFINER helper that bypasses RLS
-- internally (same pattern as current_user_is_super_admin).

create or replace function public.current_user_org_ids()
returns setof uuid
language sql
stable
security definer
set search_path = public
as $$
  select organization_id
  from public.organization_members
  where user_id = auth.uid()
$$;

-- Rewrite co-member visibility on organization_members using the helper.
drop policy if exists "members_co_member_select" on public.organization_members;
create policy "members_co_member_select"
  on public.organization_members for select
  to authenticated
  using (organization_id in (select public.current_user_org_ids()));

-- Defensively rewrite the orgs membership policy too — its subquery used to
-- be safe (organization_members only had a non-recursive self policy), but
-- now that we've added more policies, route everything through the helper.
drop policy if exists "orgs_member_select" on public.organizations;
create policy "orgs_member_select"
  on public.organizations for select
  to authenticated
  using (id in (select public.current_user_org_ids()));
