--- sys-queues


CREATE TABLE sys_queues (
  id              BIGINT PRIMARY KEY IDENTITY (1, 1),
  name            VARCHAR(255)   NOT NULL,
  total_consumers INTEGER        NOT NULL,
  created_at      DATETIMEOFFSET NOT NULL
);

CREATE UNIQUE INDEX sys_queues_name_uniq
  ON sys_queues (name);

-- sys-queue-consumers

CREATE TABLE sys_queue_consumers (
  id           BIGINT PRIMARY KEY IDENTITY (1, 1),
  queue_id     BIGINT         NOT NULL REFERENCES sys_queues (id),
  state        VARCHAR(16)    NOT NULL,

  process      VARCHAR(1024)  NOT NULL,
  pid          INTEGER,
  hostname     VARCHAR(255),

  started_at   DATETIMEOFFSET NOT NULL,
  seen_at      DATETIMEOFFSET NOT NULL,
  stopped_at   DATETIMEOFFSET,
  collected_at DATETIMEOFFSET
);

CREATE INDEX sys_queue_consumers_queue_id_state_seen_at_ix
  ON sys_queue_consumers (queue_id, state, seen_at);

-- sys-queue-items

CREATE TABLE sys_queue_items (
  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(16)    NOT NULL,
  attempts    INTEGER        NOT NULL,
  payload     BIGINT         NOT NULL,

  created_at  DATETIMEOFFSET NOT NULL,
  run_at      DATETIMEOFFSET,
  started_at  DATETIMEOFFSET,
  consumed_at DATETIMEOFFSET,
  failed_at   DATETIMEOFFSET,
  error       NVARCHAR(255)
);

CREATE INDEX sys_queue_items_queue_id_enqueued_ix
  ON sys_queue_items (queue_id, id)
  WHERE state = 'enqueued';

CREATE INDEX sys_queue_items_queue_id_scheduled_run_at_ix
  ON sys_queue_items (queue_id, run_at)
  WHERE state = 'scheduled';

CREATE INDEX sys_queue_items_consumer_id_started_ix
  ON sys_queue_items (consumer_id, id)
  WHERE consumer_id IS NOT NULL AND state = 'started';
