-- 018-inboxes

CREATE TABLE inboxes (
  user_id               NUMBER(19) PRIMARY KEY REFERENCES users (id),
  total_open_threads    NUMBER(10)               NOT NULL,
  total_unread_messages NUMBER(10)               NOT NULL,
  created_at            TIMESTAMP WITH TIME ZONE NOT NULL,
  updated_at            TIMESTAMP WITH TIME ZONE NOT NULL
);

-- inbox-threads

CREATE TABLE inbox_threads (
  id           NUMBER(19) PRIMARY KEY,
  user_id      NUMBER(19)               NOT NULL REFERENCES users (id),
  ticket_id    NUMBER(19)               NOT NULL REFERENCES tickets (id),

  "left"       NUMBER(1)                NOT NULL,
  archived     NUMBER(1)                NOT NULL,
  total_unread NUMBER(10)               NOT NULL,

  created_at   TIMESTAMP WITH TIME ZONE NOT NULL,
  updated_at   TIMESTAMP WITH TIME ZONE NOT NULL,
  sorted_at    TIMESTAMP WITH TIME ZONE NOT NULL
);

CREATE SEQUENCE inbox_threads_id_seq;

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

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

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