--- inbox

CREATE TABLE inboxes (
  id         BIGINT IDENTITY (1,1) PRIMARY KEY,
  user_id    BIGINT      NOT NULL REFERENCES users (id),
  event_id   BIGINT,
  total_open INTEGER     NOT NULL  DEFAULT 0,
  created_at DATETIMEOFFSET NOT NULL  DEFAULT SYSDATETIMEOFFSET(),
  updated_at DATETIMEOFFSET
);

CREATE UNIQUE INDEX inboxes_user_id_uniq
  ON inboxes (user_id);

-- inbox-thread

CREATE TABLE inbox_threads (
  id                BIGINT IDENTITY (1,1) PRIMARY KEY,
  inbox_id          BIGINT      NOT NULL REFERENCES inboxes (id),
  channel_thread_id BIGINT      NOT NULL REFERENCES channel_threads (id),
  event_id          BIGINT,
  closed            BIT     NOT NULL DEFAULT 1,
  created_at        DATETIMEOFFSET NOT NULL DEFAULT SYSDATETIMEOFFSET(),
  updated_at        DATETIMEOFFSET
);

CREATE UNIQUE INDEX inbox_threads_inbox_id_channel_thread_id_uniq
  ON inbox_threads (inbox_id, channel_thread_id);

CREATE INDEX inbox_threads_inbox_id_event_id_ix
  ON inbox_threads (inbox_id, event_id DESC);

CREATE INDEX inbox_threads_channel_thread_id_open_ix
  ON inbox_threads (channel_thread_id)
  WHERE closed = 0;
