--- sys-queues


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

CREATE SEQUENCE sys_queues_id_seq;

CREATE UNIQUE INDEX sys_queues_name_uniq
  ON sys_queues (name);

-- sys-queue-consumer

CREATE TABLE sys_queue_consumers (
  id             BIGINT PRIMARY KEY,
  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     TIMESTAMPTZ   NOT NULL,
  seen_at        TIMESTAMPTZ   NOT NULL,
  stopped_at     TIMESTAMPTZ,
  collected_at   TIMESTAMPTZ
);

CREATE SEQUENCE sys_queue_consumers_id_seq;

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,
  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  TIMESTAMPTZ NOT NULL,
  run_at      TIMESTAMPTZ,
  started_at  TIMESTAMPTZ,
  consumed_at TIMESTAMPTZ,
  failed_at   TIMESTAMPTZ,
  error       VARCHAR(255)
);

CREATE SEQUENCE sys_queue_items_id_seq;

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_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';
