li7y/migrations/20240721221728_add_item_state.up.sql

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

-- This datbase design is inspired by the following two blog posts by Felix Geisendörfer and Raphael Medaer:
--
-- https://felixge.de/2017/07/27/implementing-state-machines-in-postgresql/
-- https://raphael.medaer.me/2019/06/12/pgfsm.html
--
-- I chose a design that makes a compromise between both designs.
-- To simplify modifying transitions, it uses a table for storing the transitions,
-- but it does not version it, as the design should not radically change.

CREATE TYPE item_state AS ENUM (
  'borrowed',
  'inactive',
  'loaned',
  'owned'
);

CREATE TYPE item_event AS ENUM (
  'acquire',
  'borrow',
  'buy',
  'dispose',
  'gift',
  'loan',
  'lose',
  'recieve_gift',
  'return_borrowed',
  'return_loaned',
  'sell',
  'use'
);

CREATE TABLE item_events_transitions (
  state item_state,
  event item_event,
  next item_state,
  PRIMARY KEY (state, event, next)
);

INSERT INTO item_events_transitions VALUES
  ('inactive', 'buy', 'owned'),
  ('inactive', 'recieve_gift', 'owned'),
  ('inactive', 'acquire', 'owned'),
  ('inactive', 'borrow', 'borrowed'),
  ('owned', 'sell', 'inactive'),
  ('owned', 'gift', 'inactive'),
  ('owned', 'lose', 'inactive'),
  ('owned', 'use', 'inactive'),
  ('owned', 'dispose', 'inactive'),
  ('owned', 'loan', 'loaned'),
  ('loaned', 'return_loaned', 'owned'),
  ('borrowed', 'return_borrowed', 'inactive');

CREATE FUNCTION item_events_transition(_state item_state, _event item_event)
RETURNS item_state AS $$
SELECT next
  FROM item_events_transitions
  WHERE state = _state AND event = _event;
$$ LANGUAGE sql STRICT;

CREATE AGGREGATE item_events_fsm(item_event) (
  SFUNC = item_events_transition,
  STYPE = item_state,
  INITCOND = 'inactive'
);

CREATE TABLE item_events (
  id SERIAL PRIMARY KEY,
  item UUID NOT NULL REFERENCES items(id),
  date DATE NOT NULL DEFAULT now(),
  event item_event NOT NULL,
  description VARCHAR NOT NULL DEFAULT ''
);

CREATE FUNCTION check_item_events_fsm()
RETURNS trigger AS $$
BEGIN
  IF (SELECT item_events_fsm(event ORDER BY id) FROM (
    SELECT id, event FROM item_events WHERE item = NEW.item
    UNION ALL
    SELECT NEW.id, NEW.event
  ) events) IS NULL THEN
    RAISE EXCEPTION 'Event not possible from current state';
  END IF;

  RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_item_events_fsm
  BEFORE INSERT ON item_events
  FOR EACH ROW
  EXECUTE PROCEDURE check_item_events_fsm();

CREATE FUNCTION check_item_events_delete()
RETURNS trigger AS $$
BEGIN
  IF (SELECT OLD.id <> max(id) FROM item_events WHERE item = OLD.item) THEN
    RAISE EXCEPTION 'Only the last event of an item can be deleted';
  END IF;

  RETURN OLD;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_item_events_delete
  BEFORE DELETE ON item_events
  FOR EACH ROW
  EXECUTE PROCEDURE check_item_events_delete();

CREATE VIEW item_states AS
  -- probably not the best query, but it works
  SELECT
      items.id AS "item",
      state,
      event AS "last_event",
      date AS "last_event_date",
      item_events.description AS "last_event_description"
    FROM item_events
    -- items without eny event must be included
    RIGHT JOIN items
      ON item_events.item = items.id
    JOIN (
      SELECT
          item_events_fsm(event ORDER BY item_events.id) AS "state",
          max(item_events.id) AS "id",
          items.id AS "item"
        FROM item_events
        -- see above
        RIGHT JOIN items
          ON item_events.item = items.id
        GROUP BY item_events.item, items.id
    ) last_event
      ON items.id = last_event.item AND (item_events.id = last_event.id OR last_event.id IS NULL);

CREATE FUNCTION add_default_item_event()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO item_events (item, event, description)
    VALUES (NEW.id, 'acquire', 'automatically added on item insert');

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER add_default_item_event
  AFTER INSERT ON items
  FOR EACH ROW
  EXECUTE FUNCTION add_default_item_event();

INSERT INTO item_events (item, event, description)
  SELECT id, 'acquire', 'automatically added on migration' FROM items;