-- ============================================================================
-- EduAdvise CRM — Phase 8: Self-serve student portal
-- Link a portal login to its application(s) + a student↔counselor message thread.
-- ============================================================================

-- A portal account (auth user with role 'student') linked to its application.
alter table public.student_applications
  add column if not exists student_user_id uuid references auth.users(id);
create index if not exists student_applications_student_user_idx
  on public.student_applications (student_user_id);

-- Message thread between a student and staff, per application.
create table public.student_messages (
  id                     uuid primary key default gen_random_uuid(),
  student_application_id uuid not null references public.student_applications(id) on delete cascade,
  sender                 text not null,   -- 'student' | 'staff'
  sender_user_id         uuid references public.users(id),
  body                   text not null,
  created_at             timestamptz not null default now()
);
create index student_messages_app_idx on public.student_messages (student_application_id);