-- ============================================================================
-- EduAdvise CRM — Phase 2: RLS for the lead pipeline
-- ============================================================================

-- Masters: read by any authenticated user; write requires master.update
do $$
declare t text;
begin
  foreach t in array array['inquiries','exams','degrees','followup_types','lead_sources','interested_courses']
  loop
    execute format('alter table public.%I enable row level security', t);
    execute format($p$create policy "read %1$s" on public.%1$I for select to authenticated using (true)$p$, t);
    execute format($p$create policy "manage %1$s" on public.%1$I for all to authenticated
      using (public.has_permission('master','update'))
      with check (public.has_permission('master','update'))$p$, t);
  end loop;
end $$;

-- ---------------------------------------------------------------------------
-- Leads: visible if super admin, in my branch, created by me, or assigned to me
-- ---------------------------------------------------------------------------
alter table public.leads enable row level security;

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 exists (
      select 1 from public.lead_assignments la
      where la.lead_id = leads.id and la.user_id = auth.uid()
    )
  );

create policy "insert leads" on public.leads
  for insert to authenticated
  with check (public.has_permission('leads','create'));

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 exists (
        select 1 from public.lead_assignments la
        where la.lead_id = leads.id and la.user_id = auth.uid()
      )
    )
  )
  with check (public.has_permission('leads','update'));

create policy "delete leads" on public.leads
  for delete to authenticated
  using (public.has_permission('leads','delete'));

-- ---------------------------------------------------------------------------
-- Child tables: scoped via visibility of the parent lead (leads RLS applies in
-- the EXISTS subquery, so only visible leads expose their children)
-- ---------------------------------------------------------------------------
alter table public.lead_assignments enable row level security;
create policy "read lead_assignments" on public.lead_assignments
  for select to authenticated
  using (exists (select 1 from public.leads l where l.id = lead_assignments.lead_id));
create policy "manage lead_assignments" on public.lead_assignments
  for all to authenticated
  using (public.has_permission('leads','assign')
    and exists (select 1 from public.leads l where l.id = lead_assignments.lead_id))
  with check (public.has_permission('leads','assign'));

alter table public.followups enable row level security;
create policy "read followups" on public.followups
  for select to authenticated
  using (exists (select 1 from public.leads l where l.id = followups.lead_id));
create policy "manage followups" on public.followups
  for all to authenticated
  using (public.has_permission('followups','update')
    and exists (select 1 from public.leads l where l.id = followups.lead_id))
  with check (public.has_permission('followups','update'));

alter table public.lead_notes enable row level security;
create policy "read lead_notes" on public.lead_notes
  for select to authenticated
  using (exists (select 1 from public.leads l where l.id = lead_notes.lead_id));
create policy "manage lead_notes" on public.lead_notes
  for all to authenticated
  using (public.has_permission('leads','update')
    and exists (select 1 from public.leads l where l.id = lead_notes.lead_id))
  with check (public.has_permission('leads','update'));