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

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

CREATE POLICY "Users can view their own incident attachments"
ON storage.objects FOR SELECT TO authenticated
USING (
  bucket_id = 'incident-attachments'
  AND (storage.foldername(name))[1] = auth.uid()::text
);

CREATE POLICY "Supervisors can view all incident attachments"
ON storage.objects FOR SELECT TO authenticated
USING (
  bucket_id = 'incident-attachments'
  AND public.has_any_role(auth.uid(), ARRAY['super_admin','admin','company_admin','ops_manager','supervisor','security_officer']::app_role[])
);

CREATE POLICY "Users can delete their own incident attachments"
ON storage.objects FOR DELETE TO authenticated
USING (
  bucket_id = 'incident-attachments'
  AND (storage.foldername(name))[1] = auth.uid()::text
);