-- ============================================================================
-- EduAdvise CRM — B2B partner applications + student profile completion
-- Partners submit applications (no student email); admin adds email and invites;
-- students complete profile before full portal tracking.
-- ============================================================================

-- Partner attribution + student onboarding fields
alter table public.student_applications
  add column if not exists partner_id uuid references public.users(id),
  add column if not exists profile_completed_at timestamptz,
  add column if not exists invite_sent_at timestamptz,
  add column if not exists student_date_of_birth date,
  add column if not exists student_gender text,
  add column if not exists student_city text,
  add column if not exists student_address text;

create index if not exists student_applications_partner_idx
  on public.student_applications (partner_id);

-- B2B workflow statuses (global masters)
insert into public.application_statuses (branch_id, name, is_active)
select null, s.name, true
from (values
  ('Draft'),
  ('Submitted'),
  ('Under Review'),
  ('Awaiting Student Email'),
  ('Invite Sent'),
  ('Profile Pending'),
  ('Profile Complete')
) as s(name)
where not exists (
  select 1 from public.application_statuses x
  where x.branch_id is null and x.name = s.name
);

-- B2B partner: create + read own applications
insert into public.role_permissions (role_id, permission_id)
select r.id, p.id
from public.roles r
join public.permissions p on true
where r.slug = 'b2b-partner'
  and (p.resource, p.action) in (
    ('applications','read'),
    ('applications','create')
  )
on conflict do nothing;

-- ---------------------------------------------------------------------------
-- student_applications: partner insert/update constraints
-- ---------------------------------------------------------------------------
drop policy if exists "insert student_applications" on public.student_applications;
create policy "insert student_applications" on public.student_applications
  for insert to authenticated
  with check (
    public.has_permission('applications','create')
    and (
      not public.is_b2b_partner()
      or (
        partner_id = auth.uid()
        and created_by = auth.uid()
        and email is null
        and status in ('Draft', 'Submitted')
      )
    )
  );

drop policy if exists "update student_applications" on public.student_applications;
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()
      )
    )
    or (
      public.is_b2b_partner()
      and partner_id = auth.uid()
      and status in ('Draft', 'Submitted')
    )
    or (
      student_user_id = auth.uid()
      and profile_completed_at is null
    )
  )
  with check (
    (
      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()
      )
    )
    or (
      public.is_b2b_partner()
      and partner_id = auth.uid()
      and email is null
      and status in ('Draft', 'Submitted')
    )
    or (
      student_user_id = auth.uid()
    )
  );

-- Partner read via partner_id (created_by already covered in read policy)
drop policy if exists "read student_applications" on public.student_applications;
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 partner_id = auth.uid()
    or counselor_id = auth.uid()
    or student_user_id = auth.uid()
  );

-- ---------------------------------------------------------------------------
-- application_documents: partner upload for own applications
-- ---------------------------------------------------------------------------
create policy "partner uploads application_documents" on public.application_documents
  for insert to authenticated
  with check (
    public.is_b2b_partner()
    and exists (
      select 1 from public.student_applications a
      where a.id = application_documents.student_application_id
        and a.partner_id = auth.uid()
    )
  );