--- sys-queue-messages

ALTER TABLE sys_queues
  DROP COLUMN total_consumers;

ALTER TABLE sys_queue_consumers
  ADD died_at DATETIMEOFFSET;


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

  state       VARCHAR(32)    NOT NULL,
  locked      BIT            NOT NULL,
  attempt     INTEGER        NOT NULL,
  payload     NVARCHAR(MAX)  NOT NULL,
  error       NVARCHAR(4000),

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

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 = 0;

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