-- ============================================================================
-- EduAdvise CRM — Phase 3 RLS
-- Catalogue is global (shared across branches): any authenticated user may read;
-- writing requires master.update (geography/masters) or course_finder.update
-- (institutes, campuses, courses). Shortlist is scoped via the parent lead.
-- ============================================================================

-- Simple + geography masters: read all, write requires master.update
do $$
declare t text;
begin
  foreach t in array array[
    'program_levels','qualifications','streams','requirements','course_tags',
    'intakes','countries','states'
  ]
  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 $$;

-- Institutes, campuses, courses: read all, write requires course_finder.update
do $$
declare t text;
begin
  foreach t in array array['institutes','campuses','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('course_finder','update'))
      with check (public.has_permission('course_finder','update'))$p$, t);
  end loop;
end $$;

-- Course shortlist: visible/manageable only through a visible lead
alter table public.course_shortlist enable row level security;
create policy "read course_shortlist" on public.course_shortlist
  for select to authenticated
  using (exists (select 1 from public.leads l where l.id = course_shortlist.lead_id));
create policy "manage course_shortlist" on public.course_shortlist
  for all to authenticated
  using (public.has_permission('leads','update')
    and exists (select 1 from public.leads l where l.id = course_shortlist.lead_id))
  with check (public.has_permission('leads','update'));