--- drop previous tables
DROP TABLE inbox_events;
DROP TABLE inbox_threads;
DROP TABLE inboxes;

--- inboxes


CREATE TABLE inboxes (
  user_id               BIGINT PRIMARY KEY REFERENCES users (id),

  total_open_threads    INTEGER        NOT NULL,
  total_unread_messages INTEGER        NOT NULL,

  created_at            DATETIMEOFFSET NOT NULL,
  updated_at            DATETIMEOFFSET NOT NULL
);

--

CREATE TABLE inbox_threads (
  id           BIGINT PRIMARY KEY IDENTITY (1, 1),
  user_id      BIGINT         NOT NULL  REFERENCES inboxes (user_id),
  ticket_id    BIGINT         NOT NULL REFERENCES tickets (id),
  message_id   BIGINT REFERENCES ticket_messages (id),

  [left]       BIT            NOT NULL,
  archived     BIT            NOT NULL,
  total_unread INTEGER        NOT NULL,

  created_at   DATETIMEOFFSET NOT NULL,
  updated_at   DATETIMEOFFSET NOT NULL,
  sorted_at    DATETIMEOFFSET NOT NULL
);

CREATE UNIQUE INDEX inbox_threads_user_id_ticket_id_uniq
  ON inbox_threads (user_id, ticket_id);

CREATE INDEX inbox_threads_user_id_sorted_at_ix
  ON inbox_threads (user_id, sorted_at DESC);

CREATE INDEX inbox_threads_ticket_id_sorted_at_ix
  ON inbox_threads (ticket_id, sorted_at DESC);
