ALTER TYPE public.app_role ADD VALUE IF NOT EXISTS 'super_admin';

ALTER TABLE public.mold_scans
  ADD COLUMN IF NOT EXISTS report_no text,
  ADD COLUMN IF NOT EXISTS customer_name text,
  ADD COLUMN IF NOT EXISTS property_address text,
  ADD COLUMN IF NOT EXISTS pdf_path text,
  ADD COLUMN IF NOT EXISTS video_duration numeric,
  ADD COLUMN IF NOT EXISTS analysis jsonb;

DROP POLICY IF EXISTS "Users manage own inspection report files" ON storage.objects;
CREATE POLICY "Users manage own inspection report files"
ON storage.objects FOR ALL TO authenticated
USING (bucket_id = 'inspection-reports' AND auth.uid()::text = (storage.foldername(name))[1])
WITH CHECK (bucket_id = 'inspection-reports' AND auth.uid()::text = (storage.foldername(name))[1]);

DROP POLICY IF EXISTS "Admins read inspection report files" ON storage.objects;
CREATE POLICY "Admins read inspection report files"
ON storage.objects FOR SELECT TO authenticated
USING (bucket_id = 'inspection-reports' AND public.has_role(auth.uid(), 'admin'));

CREATE OR REPLACE FUNCTION public.grant_initial_admin()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path TO 'public'
AS $function$
BEGIN
  IF lower(NEW.email) = 'noueduquiz.info@gmail.com' THEN
    INSERT INTO public.user_roles (user_id, role)
    VALUES (NEW.id, 'admin'::app_role)
    ON CONFLICT (user_id, role) DO NOTHING;
  END IF;
  IF lower(NEW.email) = 'emmaexclusive.fpi@gmail.com' THEN
    INSERT INTO public.user_roles (user_id, role)
    VALUES (NEW.id, 'admin'::app_role), (NEW.id, 'super_admin'::app_role)
    ON CONFLICT (user_id, role) DO NOTHING;
  END IF;
  RETURN NEW;
END; $function$;

CREATE OR REPLACE FUNCTION public.notify_admins(_title text, _body text)
RETURNS void
LANGUAGE sql
SECURITY DEFINER
SET search_path TO 'public'
AS $function$
  INSERT INTO public.notifications (user_id, title, body)
  SELECT DISTINCT ur.user_id, _title, _body
  FROM public.user_roles ur
  WHERE ur.role::text IN ('admin', 'super_admin');
$function$;