START TRANSACTION;

 CREATE TABLE status_type (
    id serial not null primary key,
    key varchar(50) not null,
    initial boolean not null default false,
    unique (id, initial)
  );

  CREATE TABLE transitions (
    status_id_from int not null references status_type,
    status_id_to int not null references status_type,
    primary key (status_id_from, status_id_to)
  );

  CREATE TABLE status (
    id serial not null,
    story_id int not null,

    story_status_number int not null,
    status_type_id int not null references status_type,
    status_type_initial boolean GENERATED ALWAYS AS (nullif(story_status_number = 1, false)) STORED,

    story_status_previous_number int generated always as (nullif(story_status_number - 1, 0)) stored,
    status_type_previous_id int references status_type,

    PRIMARY KEY (story_id, story_status_number),

    FOREIGN KEY (status_type_id, status_type_initial) REFERENCES status_type (id, initial),

    UNIQUE (story_id, story_status_number, status_type_id),
    FOREIGN KEY (status_type_previous_id, status_type_id) REFERENCES transitions,
    FOREIGN KEY (story_id, story_status_previous_number, status_type_previous_id) REFERENCES status (story_id, story_status_number, status_type_id),
    CHECK (CASE WHEN status_type_initial THEN status_type_previous_id IS NULL ELSE TRUE END)
  );

  DO $$
    DECLARE
      born int;
      married int;
      divorced int;
      dead int;
    BEGIN
      
      INSERT INTO status_type (key, initial) VALUES ('Born', true) RETURNING id INTO born;
      INSERT INTO status_type (key) VALUES ('Married') RETURNING id INTO married;
      INSERT INTO status_type (key) VALUES ('Divorced') RETURNING id INTO divorced;
      INSERT INTO status_type (key) VALUES ('Dead') RETURNING id INTO dead;

      INSERT INTO transitions (status_id_from, status_id_to) VALUES
      (born, married),
      (born, dead),
      (married, divorced),
      (married, dead),
      (divorced, married),
      (divorced, dead);

      INSERT INTO status (story_id, story_status_number, status_type_previous_id, status_type_id) VALUES
      (1, 1, null, born),
      (1, 2, born, married),
      (1, 3, married, divorced),
      (1, 4, divorced, married);

      BEGIN
        INSERT INTO status (story_id, story_status_number, status_type_previous_id, status_type_id) VALUES
        (2, 1, born, born);
      EXCEPTION WHEN check_violation THEN RAISE NOTICE 'Correctly failing when before-first status is not null.'; END;

      BEGIN
        INSERT INTO status (story_id, story_status_number, status_type_previous_id, status_type_id) VALUES
        (3, 1, null, married);
      EXCEPTION WHEN foreign_key_violation THEN RAISE NOTICE 'Correctly failing when inserting non-initial status.'; END;

      BEGIN
        INSERT INTO status (story_id, story_status_number, status_type_previous_id, status_type_id) VALUES
        (5, 1, null, born),
        (5, 2, born, divorced);
      EXCEPTION WHEN foreign_key_violation THEN RAISE NOTICE 'Correctly failing when using an non-existent transition.'; END;

      BEGIN
        INSERT INTO status (story_id, story_status_number, status_type_previous_id, status_type_id) VALUES
        (6, 1, null, born),
        (6, 2, married, divorced);
      EXCEPTION WHEN foreign_key_violation THEN RAISE NOTICE 'Correctly failing when not respecting the previous state.'; END;

      BEGIN
        INSERT INTO status (story_id, story_status_number, status_type_previous_id, status_type_id) VALUES
        (7, 1, null, born),
        (7, 2, born, married),
        (7, 2, born, dead);
      EXCEPTION WHEN unique_violation THEN RAISE NOTICE 'Correctly failing when transitioning to two states.'; END;

      BEGIN
        INSERT INTO status (story_id, story_status_number, status_type_previous_id, status_type_id) VALUES
        (8, 2, born, married);
      EXCEPTION WHEN foreign_key_violation THEN RAISE NOTICE 'Correctly failing when missing status number 1.'; END;

      BEGIN
        INSERT INTO status (story_id, story_status_number, status_type_previous_id, status_type_id) VALUES
        (9, 0, null, born);
      EXCEPTION WHEN foreign_key_violation THEN RAISE NOTICE 'Correctly failing when inserting non-positive status number.'; END;

      BEGIN
        INSERT INTO status (story_id, story_status_number, status_type_previous_id, status_type_id) VALUES
        (10, 1, null, born),
        (10, 2, born, (SELECT MAX(id) FROM status_type) + 1);
      EXCEPTION WHEN foreign_key_violation THEN RAISE NOTICE 'Correctly failing when inserting a non-existent status type.'; END;

    END;
  $$ LANGUAGE plpgsql;

ROLLBACK;
