acmcc/supabase/migrations/20260421171653_c47e91a2-34be-4891-ad95-2f4f7e40c95b.sql

-- Add request_type and attachments columns to client_requests
ALTER TABLE public.client_requests
  ADD COLUMN IF NOT EXISTS request_type text,
  ADD COLUMN IF NOT EXISTS attachments jsonb NOT NULL DEFAULT '[]'::jsonb;

-- Allow authenticated users (clients/homeowners) to insert their own requests
DROP POLICY IF EXISTS "Authenticated users can submit client requests" ON public.client_requests;
CREATE POLICY "Authenticated users can submit client requests"
ON public.client_requests
FOR INSERT
TO authenticated
WITH CHECK (true);

-- Allow users to view their own submitted requests by email
DROP POLICY IF EXISTS "Users can view their own submitted requests" ON public.client_requests;
CREATE POLICY "Users can view their own submitted requests"
ON public.client_requests
FOR SELECT
TO authenticated
USING (
  has_role(auth.uid(), 'admin'::app_role)
  OR has_role(auth.uid(), 'manager'::app_role)
  OR requester_email = (SELECT email FROM auth.users WHERE id = auth.uid())
);

-- Trigger function: notify all admin/manager users when a new client request is submitted
CREATE OR REPLACE FUNCTION public.notify_staff_on_client_request()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path TO 'public'
AS $$
DECLARE
  staff_user RECORD;
  v_message text;
BEGIN
  v_message := COALESCE(NEW.requester_name, 'A client') 
    || ' submitted a ' 
    || COALESCE(NEW.request_type, 'request') 
    || ': ' || NEW.title;

  FOR staff_user IN
    SELECT DISTINCT user_id 
    FROM public.user_roles 
    WHERE role IN ('admin'::app_role, 'manager'::app_role)
  LOOP
    INSERT INTO public.in_app_notifications 
      (user_id, type, title, message, related_item_id, related_item_type, link)
    VALUES (
      staff_user.user_id,
      'client_request_submitted',
      'New Client Request',
      v_message,
      NEW.id,
      'client_request',
      '/dashboard/client-requests'
    );
  END LOOP;

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_notify_staff_on_client_request ON public.client_requests;
CREATE TRIGGER trg_notify_staff_on_client_request
AFTER INSERT ON public.client_requests
FOR EACH ROW
EXECUTE FUNCTION public.notify_staff_on_client_request();