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

DROP SEQUENCE inbox_events_id_seq;
DROP SEQUENCE inboxe_threads_id_seq;
DROP SEQUENCE inboxes_id_seq;

--- 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            TIMESTAMPTZ NOT NULL,
  updated_at            TIMESTAMPTZ NOT NULL
);

-- inbox_threads

CREATE TABLE inbox_threads (
  id           BIGINT PRIMARY KEY,
  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"       BOOLEAN     NOT NULL,
  archived     BOOLEAN     NOT NULL,
  total_unread INTEGER     NOT NULL,

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

CREATE SEQUENCE inbox_threads_id_seq;

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);
