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

DROP TRIGGER prevent_item_class_recursion ON item_classes;
DROP FUNCTION check_item_class_recursion_depth;

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

  IF NEW.id = NEW.parent THEN
    RAISE EXCEPTION 'Cycle detected';
  END IF;

  IF (WITH RECURSIVE cte AS (
    SELECT id, parent
    FROM item_classes
    WHERE id = NEW.parent

    UNION ALL

    SELECT item_classes.id, item_classes.parent
    FROM item_classes, cte
    WHERE item_classes.id = cte.parent
  )
  SELECT 1
  FROM cte
  WHERE parent = NEW.id
  LIMIT 1) THEN
    RAISE EXCEPTION 'Cycle detected';
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

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

CREATE RECURSIVE VIEW item_class_tree (id, parents) AS (
  SELECT id, ARRAY[]::UUID[] AS parents FROM item_classes WHERE parent IS NULL
  UNION ALL
  SELECT item_classes.id, item_class_tree.parents || item_classes.parent FROM item_classes, item_class_tree WHERE item_classes.parent = item_class_tree.id
);