-- Admin-only function to change any user's role.
-- Uses auth.uid() for the admin check (not a parameter).
CREATE OR REPLACE FUNCTION admin_set_user_role(
  p_target_user_id UUID,
  p_new_role TEXT
)
RETURNS JSON
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  v_old_role TEXT;
BEGIN
  -- Only admins may call this
  IF NOT EXISTS (
    SELECT 1 FROM profiles WHERE id = auth.uid() AND role = 'admin'
  ) THEN
    RAISE EXCEPTION 'Unauthorized: admin access required';
  END IF;
 
  -- Validate role
  IF p_new_role NOT IN ('user', 'provider', 'admin') THEN
    RAISE EXCEPTION 'Invalid role: must be user, provider, or admin';
  END IF;
 
  -- Prevent self-demotion (optional safeguard)
  IF p_target_user_id = auth.uid() AND p_new_role != 'admin' THEN
    RAISE EXCEPTION 'Cannot demote yourself from admin';
  END IF;
 
  SELECT role INTO v_old_role FROM profiles WHERE id = p_target_user_id;
 
  UPDATE profiles
    SET role = p_new_role, updated_at = NOW()
  WHERE id = p_target_user_id;
 
  RETURN json_build_object(
    'success', true,
    'old_role', v_old_role,
    'new_role', p_new_role
  );
END;
$$;
 
GRANT EXECUTE ON FUNCTION admin_set_user_role(UUID, TEXT) TO authenticated;