--- sys-queue-messages


ALTER TABLE sys_queues
  DROP COLUMN total_consumers;

ALTER TABLE sys_queue_consumers
  ADD died_at TIMESTAMPTZ;


CREATE TABLE sys_queue_messages (
  id          BIGINT PRIMARY KEY,
  queue_id    BIGINT      NOT NULL REFERENCES sys_queues (id),
  consumer_id BIGINT REFERENCES sys_queue_consumers (id),

  state       VARCHAR(32) NOT NULL,
  locked      BOOLEAN     NOT NULL,
  attempt     INTEGER     NOT NULL,
  payload     VARCHAR     NOT NULL,
  error       VARCHAR(4000),

  created_at  TIMESTAMPTZ NOT NULL,
  timeout_at  TIMESTAMPTZ,
  acked_at    TIMESTAMPTZ,
  failed_at   TIMESTAMPTZ
);

CREATE SEQUENCE sys_queue_messages_id_seq;

CREATE INDEX sys_queue_messages_queue_id_timeout_at_ix
  ON sys_queue_messages (queue_id, timeout_at)
  WHERE timeout_at IS NOT NULL;

CREATE INDEX sys_queue_messages_queue_id_pending_not_locked_ix
  ON sys_queue_messages (queue_id)
  WHERE
    state = 'pending'
    AND locked = FALSE;

CREATE INDEX sys_queue_messages_consumer_id_pending_ix
  ON sys_queue_messages (consumer_id)
  WHERE state = 'pending';
