-- ============================================================================
-- EduAdvise CRM — Phase 8 RLS + seed 'student' role
-- Students (auth users with role 'student') see ONLY their own application and
-- its children, can upload documents and exchange messages. All gated by
-- student_user_id = auth.uid(). Staff policies are unchanged (RLS is OR-additive).
-- ============================================================================

-- Seed the student role (no staff permissions; portal access is via RLS).
insert into public.roles (name, slug, description, is_system)
values ('Student', 'student', 'Self-serve student portal access', true)
on conflict (slug) do nothing;

-- Student can read their own application (adds to existing staff select policies)
create policy "student reads own application" on public.student_applications
  for select to authenticated
  using (student_user_id = auth.uid());

-- Student can upload (insert) documents for their own application.
-- Existing read policy already exposes docs of any visible application, so the
-- student will see their own docs once the application is visible (above).
create policy "student uploads own documents" on public.application_documents
  for insert to authenticated
  with check (
    exists (
      select 1 from public.student_applications a
      where a.id = application_documents.student_application_id
        and a.student_user_id = auth.uid()
    )
  );

-- Student can read fee payments for their own application.
create policy "student reads own fees" on public.fee_payments
  for select to authenticated
  using (
    exists (
      select 1 from public.student_applications a
      where a.id = fee_payments.student_application_id
        and a.student_user_id = auth.uid()
    )
  );

-- Messages: readable to anyone who can see the parent application (staff via
-- their policies, student via the policy above); insert as yourself.
alter table public.student_messages enable row level security;
create policy "read student_messages" on public.student_messages
  for select to authenticated
  using (
    exists (select 1 from public.student_applications a where a.id = student_messages.student_application_id)
  );
create policy "send student_messages" on public.student_messages
  for insert to authenticated
  with check (
    sender_user_id = auth.uid()
    and exists (select 1 from public.student_applications a where a.id = student_messages.student_application_id)
  );

-- visa_tracking already has a read policy gated on application visibility, so
-- students inherit read access to their own visa row with no extra policy.