-- ============================================================================
-- EduAdvise CRM — Phase 6 RLS + marketing storage bucket
-- Marketing/tutorial content: read by any authenticated user; write requires
-- promotional.update. Client mail: read+write require promotional.update.
-- ============================================================================

-- Read-all, write promotional.update
do $$
declare t text;
begin
  foreach t in array array[
    'promotional_materials','promotional_tutorials','social_media_posts','crm_tutorials'
  ]
  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('promotional','update'))
      with check (public.has_permission('promotional','update'))$p$, t);
  end loop;
end $$;

-- Client mail: categories + templates + sends — managed by promotional.update
do $$
declare t text;
begin
  foreach t in array array['client_mail_categories','client_mail_templates','client_mail_sends']
  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 (public.has_permission('promotional','read') or public.has_permission('promotional','update'))$p$, t);
    execute format($p$create policy "manage %1$s" on public.%1$I for all to authenticated
      using (public.has_permission('promotional','update'))
      with check (public.has_permission('promotional','update'))$p$, t);
  end loop;
end $$;

-- ---- Storage: private 'marketing' bucket ----------------------------------
insert into storage.buckets (id, name, public)
values ('marketing','marketing', false)
on conflict (id) do nothing;

drop policy if exists "marketing read" on storage.objects;
create policy "marketing read" on storage.objects
  for select to authenticated using (bucket_id = 'marketing');
drop policy if exists "marketing insert" on storage.objects;
create policy "marketing insert" on storage.objects
  for insert to authenticated with check (bucket_id = 'marketing');
drop policy if exists "marketing update" on storage.objects;
create policy "marketing update" on storage.objects
  for update to authenticated using (bucket_id = 'marketing');
drop policy if exists "marketing delete" on storage.objects;
create policy "marketing delete" on storage.objects
  for delete to authenticated using (bucket_id = 'marketing');