-- Fix client storage policies to use has_company_access() instead of get_my_company_id().
-- Previously, clients tied to multiple companies via company_members could not upload
-- or read files for their non-primary company.

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 has_company_access(p.company_id)
    )
  );

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 has_company_access(p.company_id)
    )
  );

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 has_company_access(p.company_id)
    )
  );