-- 020-pushes

CREATE TABLE push_tokens (
  id         NUMBER(19) PRIMARY KEY,
  "uid"      VARCHAR2(40 CHAR)        NOT NULL,
  channel_id NUMBER(19)               NOT NULL REFERENCES channels (id),
  client_id  NUMBER(19)               NOT NULL REFERENCES clients (id),
  type       VARCHAR2(16 CHAR)        NOT NULL,
  token      VARCHAR2(4000 CHAR)      NOT NULL,
  active     NUMBER(1)                NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL
);

CREATE SEQUENCE push_tokens_id_seq;

CREATE UNIQUE INDEX push_tokens_uid_uniq
  ON push_tokens ("uid");

CREATE INDEX push_tokens_client_id
  ON push_tokens (client_id, "uid");

-- pushes

CREATE TABLE pushes (
  id           NUMBER(19) PRIMARY KEY,
  token_id     NUMBER(19)               NOT NULL REFERENCES push_tokens (id),
  message_id   NUMBER(19)               NOT NULL REFERENCES chat_messages (id),
  state        VARCHAR2(16 CHAR)        NOT NULL,
  created_at   TIMESTAMP WITH TIME ZONE NOT NULL,
  sent_at      TIMESTAMP WITH TIME ZONE,
  cancelled_at TIMESTAMP WITH TIME ZONE
);

CREATE SEQUENCE pushes_id_seq;

CREATE UNIQUE INDEX pushes_token_message_uniq
  ON pushes (token_id, message_id);

CREATE INDEX pushes_message_id_enqueued_ix
  ON pushes (
    CASE WHEN state = 'enqueued'
      THEN message_id
    END
  );
