-- SPDX-FileCopyrightText: 2024 Simon Bruder <simon@sbruder.de>
--
-- SPDX-License-Identifier: AGPL-3.0-or-later

DROP VIEW item_class_tree;

DROP TRIGGER prevent_item_class_cycle ON item_classes;
DROP FUNCTION check_item_class_cycle;

CREATE FUNCTION check_item_class_recursion_depth()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.parent IS NULL THEN
    RETURN NEW;
  END IF;

  IF (SELECT parent FROM item_classes WHERE id = NEW.parent) IS NULL THEN
    RETURN NEW;
  END IF;

  RAISE EXCEPTION 'Item classes may only be nested one level deep';
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER prevent_item_class_recursion
BEFORE INSERT OR UPDATE ON item_classes
FOR EACH ROW
EXECUTE FUNCTION check_item_class_recursion_depth();