do $$
declare
  cname text;
begin
  select constraint_name into cname
  from information_schema.table_constraints
  where table_schema = 'public'
    and table_name = 'profiles'
    and constraint_type = 'CHECK'
    and constraint_name = 'profiles_role_check';

  if cname is not null then
    execute 'alter table public.profiles drop constraint ' || quote_ident(cname);
  end if;
end;
$$;

alter table public.profiles
  add constraint profiles_role_check check (role in ('team', 'client', 'external'));

create table if not exists public.project_members (
  id uuid default gen_random_uuid() primary key,
  project_id uuid references public.projects(id) on delete cascade not null,
  profile_id uuid references public.profiles(id) on delete cascade not null,
  created_at timestamptz default now() not null,
  unique(project_id, profile_id)
);

alter table public.project_members enable row level security;

create or replace function public.is_external()
returns boolean as $$
  select get_my_role() = 'external';
$$ language sql security definer stable;

drop policy if exists "Team all project_members" on public.project_members;
create policy "Team all project_members" on public.project_members
  for all using (get_my_role() = 'team');

drop policy if exists "External reads own memberships" on public.project_members;
create policy "External reads own memberships" on public.project_members
  for select using (profile_id = auth.uid());

drop policy if exists "External reads assigned projects" on public.projects;
create policy "External reads assigned projects" on public.projects
  for select using (
    get_my_role() = 'external' and
    id in (select project_id from public.project_members where profile_id = auth.uid())
  );

drop policy if exists "External reads assigned tasks" on public.tasks;
create policy "External reads assigned tasks" on public.tasks
  for select using (
    get_my_role() = 'external' and
    project_id in (select project_id from public.project_members where profile_id = auth.uid())
  );

drop policy if exists "External updates assigned tasks" on public.tasks;
create policy "External updates assigned tasks" on public.tasks
  for update using (
    get_my_role() = 'external' and
    project_id in (select project_id from public.project_members where profile_id = auth.uid())
  );

drop policy if exists "External reads assigned submissions" on public.submissions;
create policy "External reads assigned submissions" on public.submissions
  for select using (
    get_my_role() = 'external' and
    task_id in (
      select t.id from public.tasks t
      join public.project_members pm on pm.project_id = t.project_id
      where pm.profile_id = auth.uid()
    )
  );

drop policy if exists "External inserts submissions" on public.submissions;
create policy "External inserts submissions" on public.submissions
  for insert with check (
    get_my_role() = 'external' and submitted_by = auth.uid()
  );

drop policy if exists "External reads assigned submission_files" on public.submission_files;
create policy "External reads assigned submission_files" on public.submission_files
  for select using (
    get_my_role() = 'external' and
    submission_id in (
      select s.id from public.submissions s
      join public.tasks t on t.id = s.task_id
      join public.project_members pm on pm.project_id = t.project_id
      where pm.profile_id = auth.uid()
    )
  );

drop policy if exists "External inserts submission_files" on public.submission_files;
create policy "External inserts submission_files" on public.submission_files
  for insert with check (get_my_role() = 'external');

drop policy if exists "External reads assigned deliveries" on public.deliveries;
create policy "External reads assigned deliveries" on public.deliveries
  for select using (
    get_my_role() = 'external' and
    submission_id in (
      select s.id from public.submissions s
      join public.tasks t on t.id = s.task_id
      join public.project_members pm on pm.project_id = t.project_id
      where pm.profile_id = auth.uid()
    )
  );

drop policy if exists "External reads assigned delivery_files" on public.delivery_files;
create policy "External reads assigned delivery_files" on public.delivery_files
  for select using (
    get_my_role() = 'external' and
    delivery_id in (
      select d.id from public.deliveries d
      join public.submissions s on s.id = d.submission_id
      join public.tasks t on t.id = s.task_id
      join public.project_members pm on pm.project_id = t.project_id
      where pm.profile_id = auth.uid()
    )
  );