--- inbox

CREATE TABLE inboxes (
  id         BIGINT PRIMARY KEY,
  user_id    BIGINT      NOT NULL REFERENCES users (id),
  event_id   BIGINT,
  total_open INTEGER     NOT NULL  DEFAULT 0,
  created_at TIMESTAMPTZ NOT NULL  DEFAULT now(),
  updated_at TIMESTAMPTZ
);

CREATE SEQUENCE inboxes_id_seq;

CREATE UNIQUE INDEX inboxes_user_id_uniq
  ON inboxes (user_id);

-- inbox-thread

CREATE TABLE inbox_threads (
  id                BIGSERIAL PRIMARY KEY,
  inbox_id          BIGINT      NOT NULL REFERENCES inboxes (id),
  channel_thread_id BIGINT      NOT NULL REFERENCES channel_threads (id),
  event_id          BIGINT,
  closed            BOOLEAN     NOT NULL DEFAULT TRUE,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at        TIMESTAMPTZ
);

CREATE SEQUENCE inboxe_threads_id_seq;

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 = FALSE;
