--- push-notifications

CREATE TABLE pushes (
  id           BIGINT                  IDENTITY (1, 1) PRIMARY KEY,
  state        VARCHAR(16)    NOT NULL,
  channel_id   BIGINT         NOT NULL REFERENCES channels (id),
  client_id    BIGINT         NOT NULL REFERENCES clients (id),
  token_id     BIGINT         NOT NULL REFERENCES push_tokens (id),
  message_id   BIGINT         NOT NULL REFERENCES chat_messages (id),
  created_at   DATETIMEOFFSET NOT NULL DEFAULT sysdatetimeoffset(),
  enqueued_at  DATETIMEOFFSET,
  sent_at      DATETIMEOFFSET,
  cancelled_at DATETIMEOFFSET
);

CREATE INDEX pushes_channel_id_state_created_at_ix
  ON pushes (channel_id, state, created_at);
CREATE INDEX pushes_client_id_state_created_at_ix
  ON pushes (client_id, state, created_at);
