-- ============================================================================
-- EduAdvise CRM — Phase 2 RLS fix: break leads <-> lead_assignments recursion
-- The leads SELECT/UPDATE policies referenced lead_assignments, whose own policy
-- referenced leads -> infinite recursion (42P17). Replace the cross-reference
-- with a SECURITY DEFINER helper that reads lead_assignments without RLS.
-- ============================================================================

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

drop policy if exists "read leads" on public.leads;
create policy "read leads" on public.leads
  for select to authenticated
  using (
    public.is_super_admin()
    or branch_id in (select public.current_user_branch_ids())
    or created_by = auth.uid()
    or id in (select public.current_user_assigned_lead_ids())
  );

drop policy if exists "update leads" on public.leads;
create policy "update leads" on public.leads
  for update to authenticated
  using (
    public.has_permission('leads','update')
    and (
      public.is_super_admin()
      or branch_id in (select public.current_user_branch_ids())
      or created_by = auth.uid()
      or id in (select public.current_user_assigned_lead_ids())
    )
  )
  with check (public.has_permission('leads','update'));