-- 1) has_role: switch to SECURITY INVOKER and lock down EXECUTE
ALTER FUNCTION public.has_role(uuid, app_role) SECURITY INVOKER;
REVOKE EXECUTE ON FUNCTION public.has_role(uuid, app_role) FROM PUBLIC, anon;
GRANT EXECUTE ON FUNCTION public.has_role(uuid, app_role) TO authenticated, service_role;

-- 2) Replace overly-permissive contact submissions insert policy
DROP POLICY IF EXISTS "Anyone submits contact" ON public.contact_submissions;
CREATE POLICY "Anyone submits contact"
  ON public.contact_submissions
  FOR INSERT
  TO anon, authenticated
  WITH CHECK (
    char_length(btrim(name)) BETWEEN 1 AND 200
    AND char_length(btrim(email)) BETWEEN 3 AND 320
    AND email ~ '^[^@\s]+@[^@\s]+\.[^@\s]+$'
    AND char_length(btrim(message)) BETWEEN 1 AND 5000
  );

-- 3) Remove anonymous insert path for service_requests
DROP POLICY IF EXISTS "Anon insert request" ON public.service_requests;
DROP POLICY IF EXISTS "Users insert own request" ON public.service_requests;
CREATE POLICY "Users insert own request"
  ON public.service_requests
  FOR INSERT
  TO authenticated
  WITH CHECK (auth.uid() = user_id);

-- 4) Allow users to delete their own pending requests
CREATE POLICY "Users delete own pending requests"
  ON public.service_requests
  FOR DELETE
  TO authenticated
  USING (auth.uid() = user_id AND status = 'pending');