ALTER TABLE public.service_requests
  ADD COLUMN IF NOT EXISTS attachments jsonb NOT NULL DEFAULT '[]'::jsonb;

-- Storage policies for the request-attachments bucket.
-- Path convention: {user_id}/{request_or_uuid}/{filename}

CREATE POLICY "Users upload own request attachments"
  ON storage.objects FOR INSERT TO authenticated
  WITH CHECK (
    bucket_id = 'request-attachments'
    AND auth.uid()::text = (storage.foldername(name))[1]
  );

CREATE POLICY "Users read own request attachments"
  ON storage.objects FOR SELECT TO authenticated
  USING (
    bucket_id = 'request-attachments'
    AND (
      auth.uid()::text = (storage.foldername(name))[1]
      OR public.has_role(auth.uid(), 'admin')
    )
  );

CREATE POLICY "Admins manage request attachments"
  ON storage.objects FOR ALL TO authenticated
  USING (
    bucket_id = 'request-attachments'
    AND public.has_role(auth.uid(), 'admin')
  )
  WITH CHECK (
    bucket_id = 'request-attachments'
    AND public.has_role(auth.uid(), 'admin')
  );