-- 021-sys-queues

CREATE TABLE sys_queues (
  id         NUMBER(19) PRIMARY KEY,
  name       VARCHAR2(255 CHAR)       NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL
);

CREATE SEQUENCE sys_queues_id_seq;

CREATE UNIQUE INDEX sys_queues_name_uniq
  ON sys_queues (name);

-- sys-queue-consumers

CREATE TABLE sys_queue_consumers (
  id           NUMBER(19) PRIMARY KEY,
  queue_id     NUMBER(19)               NOT NULL REFERENCES sys_queues (id),
  state        VARCHAR2(16 CHAR)        NOT NULL,

  process      VARCHAR2(1024 CHAR)      NOT NULL,
  pid          NUMBER(10)               NOT NULL,
  hostname     VARCHAR2(255 CHAR),

  started_at   TIMESTAMP WITH TIME ZONE NOT NULL,
  seen_at      TIMESTAMP WITH TIME ZONE NOT NULL,
  stopped_at   TIMESTAMP WITH TIME ZONE,
  died_at      TIMESTAMP WITH TIME ZONE,

  collected_at TIMESTAMP WITH TIME ZONE
);

CREATE SEQUENCE sys_queue_consumers_id_seq;

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

-- sys-queue-messages

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

  state       VARCHAR2(32 CHAR)        NOT NULL,
  locked      NUMBER(1)                NOT NULL,
  attempt     NUMBER(10)               NOT NULL,
  payload     VARCHAR2(4000 CHAR)      NOT NULL,
  error       VARCHAR2(4000 CHAR),

  created_at  TIMESTAMP WITH TIME ZONE NOT NULL,
  timeout_at  TIMESTAMP WITH TIME ZONE,
  acked_at    TIMESTAMP WITH TIME ZONE,
  failed_at   TIMESTAMP WITH TIME ZONE
);

CREATE SEQUENCE sys_queue_messages_id_seq;

CREATE INDEX sys_queue_messages_cpending_ix
  ON sys_queue_messages (
    CASE WHEN state = 'pending'
      THEN consumer_id END
  );

CREATE INDEX sys_queue_messages_qpending_ix
  ON sys_queue_messages (
    CASE WHEN state = 'pending' AND locked = 0
      THEN queue_id END
  );

CREATE INDEX sys_queue_messages_qtimeout_ix
  ON sys_queue_messages (
    CASE WHEN timeout_at IS NOT NULL
      THEN queue_id END,
    CASE WHEN timeout_at IS NOT NULL
      THEN timeout_at END
  );
