drop policy if exists "Auth users upload to submissions" on storage.objects;
drop policy if exists "Auth users read submissions" on storage.objects;
drop policy if exists "Team upload deliveries" on storage.objects;
drop policy if exists "Auth users read deliveries" on storage.objects;

drop policy if exists "Team reads submissions storage" on storage.objects;
create policy "Team reads submissions storage" on storage.objects
  for select to authenticated
  using (bucket_id = 'submissions' and get_my_role() = 'team');

drop policy if exists "Client reads submissions storage" on storage.objects;
create policy "Client reads submissions storage" on storage.objects
  for select to authenticated
  using (
    bucket_id = 'submissions'
    and get_my_role() = 'client'
    and split_part(name, '/', 1) in (
      select t.id::text
      from public.tasks t
      join public.projects p on p.id = t.project_id
      where p.company_id = get_my_company_id()
    )
  );

drop policy if exists "External reads submissions storage" on storage.objects;
create policy "External reads submissions storage" on storage.objects
  for select to authenticated
  using (
    bucket_id = 'submissions'
    and get_my_role() = 'external'
    and split_part(name, '/', 1) in (
      select t.id::text
      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 "Team inserts submissions storage" on storage.objects;
create policy "Team inserts submissions storage" on storage.objects
  for insert to authenticated
  with check (bucket_id = 'submissions' and get_my_role() = 'team');

drop policy if exists "Client inserts submissions storage" on storage.objects;
create policy "Client inserts submissions storage" on storage.objects
  for insert to authenticated
  with check (
    bucket_id = 'submissions'
    and get_my_role() = 'client'
    and split_part(name, '/', 1) in (
      select t.id::text
      from public.tasks t
      join public.projects p on p.id = t.project_id
      where p.company_id = get_my_company_id()
    )
  );

drop policy if exists "External inserts submissions storage" on storage.objects;
create policy "External inserts submissions storage" on storage.objects
  for insert to authenticated
  with check (
    bucket_id = 'submissions'
    and get_my_role() = 'external'
    and split_part(name, '/', 1) in (
      select t.id::text
      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 "Team deletes submissions storage" on storage.objects;
create policy "Team deletes submissions storage" on storage.objects
  for delete to authenticated
  using (bucket_id = 'submissions' and get_my_role() = 'team');

drop policy if exists "Team reads deliveries storage" on storage.objects;
create policy "Team reads deliveries storage" on storage.objects
  for select to authenticated
  using (bucket_id = 'deliveries' and get_my_role() = 'team');

drop policy if exists "Client reads deliveries storage" on storage.objects;
create policy "Client reads deliveries storage" on storage.objects
  for select to authenticated
  using (
    bucket_id = 'deliveries'
    and get_my_role() = 'client'
    and split_part(name, '/', 1) in (
      select t.id::text
      from public.tasks t
      join public.projects p on p.id = t.project_id
      where p.company_id = get_my_company_id()
    )
  );

drop policy if exists "External reads deliveries storage" on storage.objects;
create policy "External reads deliveries storage" on storage.objects
  for select to authenticated
  using (
    bucket_id = 'deliveries'
    and get_my_role() = 'external'
    and split_part(name, '/', 1) in (
      select t.id::text
      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 "Team inserts deliveries storage" on storage.objects;
create policy "Team inserts deliveries storage" on storage.objects
  for insert to authenticated
  with check (bucket_id = 'deliveries' and get_my_role() = 'team');

drop policy if exists "Team deletes deliveries storage" on storage.objects;
create policy "Team deletes deliveries storage" on storage.objects
  for delete to authenticated
  using (bucket_id = 'deliveries' and get_my_role() = 'team');