-- ============================================================================
-- EduAdvise CRM — Phase 4 RLS + Storage
-- ============================================================================

-- Masters: read all, write requires master.update
do $$
declare t text;
begin
  foreach t in array array[
    'application_types','application_statuses','education_loan_statuses',
    'visa_statuses','coaching_levels','coaching_subjects','coaching_requirements',
    'register_for','coaching_faculty'
  ]
  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 $$;

-- Branch-scoped parents: student_applications, coaching_applications,
-- education_loan_inquiries. Visible to super admin / own branch / creator /
-- assigned counselor.
alter table public.student_applications enable row level security;
create policy "read student_applications" on public.student_applications
  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 counselor_id = auth.uid()
  );
create policy "insert student_applications" on public.student_applications
  for insert to authenticated with check (public.has_permission('applications','create'));
create policy "update student_applications" on public.student_applications
  for update to authenticated
  using (public.has_permission('applications','update') and (
    public.is_super_admin()
    or branch_id in (select public.current_user_branch_ids())
    or created_by = auth.uid()
    or counselor_id = auth.uid()))
  with check (public.has_permission('applications','update'));
create policy "delete student_applications" on public.student_applications
  for delete to authenticated using (public.has_permission('applications','delete'));

alter table public.coaching_applications enable row level security;
create policy "read coaching_applications" on public.coaching_applications
  for select to authenticated using (
    public.is_super_admin()
    or branch_id in (select public.current_user_branch_ids())
    or created_by = auth.uid());
create policy "manage coaching_applications" on public.coaching_applications
  for all to authenticated
  using (public.has_permission('coaching','update'))
  with check (public.has_permission('coaching','update'));

alter table public.education_loan_inquiries enable row level security;
create policy "read education_loan_inquiries" on public.education_loan_inquiries
  for select to authenticated using (
    public.is_super_admin()
    or branch_id in (select public.current_user_branch_ids())
    or created_by = auth.uid());
create policy "manage education_loan_inquiries" on public.education_loan_inquiries
  for all to authenticated
  using (public.has_permission('loan','update'))
  with check (public.has_permission('loan','update'));

-- Child tables: scoped via the visibility of their parent (parent RLS applies
-- in the EXISTS subquery, no recursion since parents don't reference children).
alter table public.application_documents enable row level security;
create policy "read application_documents" on public.application_documents
  for select to authenticated
  using (exists (select 1 from public.student_applications a where a.id = application_documents.student_application_id));
create policy "manage application_documents" on public.application_documents
  for all to authenticated
  using (public.has_permission('applications','update')
    and exists (select 1 from public.student_applications a where a.id = application_documents.student_application_id))
  with check (public.has_permission('applications','update'));

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

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

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

-- Any authenticated staff user may read/write objects in the documents bucket.
-- (App-level RLS on application_documents governs which rows reference them.)
drop policy if exists "documents read" on storage.objects;
create policy "documents read" on storage.objects
  for select to authenticated using (bucket_id = 'documents');
drop policy if exists "documents insert" on storage.objects;
create policy "documents insert" on storage.objects
  for insert to authenticated with check (bucket_id = 'documents');
drop policy if exists "documents update" on storage.objects;
create policy "documents update" on storage.objects
  for update to authenticated using (bucket_id = 'documents');
drop policy if exists "documents delete" on storage.objects;
create policy "documents delete" on storage.objects
  for delete to authenticated using (bucket_id = 'documents');