--- chat-user-threads

CREATE TABLE chat_user_threads (
  id           BIGINT PRIMARY KEY,
  user_id      BIGINT      NOT NULL REFERENCES users (id),
  chat_id      BIGINT      NOT NULL REFERENCES chats (id),
  change_id    BIGINT,

  open         BOOLEAN     NOT NULL  DEFAULT FALSE,
  message_id   BIGINT REFERENCES chat_messages (id),
  total_unread INTEGER     NOT NULL  DEFAULT 0,

  created_at   TIMESTAMPTZ NOT NULL  DEFAULT now(),
  changed_at   TIMESTAMPTZ NOT NULL  DEFAULT now()
);

CREATE SEQUENCE chat_user_threads_id_seq;

CREATE UNIQUE INDEX chat_user_threads_chat_id_user_id_uniq
  ON chat_user_threads (chat_id, user_id);
CREATE INDEX chat_user_threads_user_id_open_changed_at_desc_ix
  ON chat_user_threads (user_id, open, changed_at DESC);
CREATE INDEX chat_user_threads_chat_id_open_ix
  ON chat_user_threads (chat_id)
  WHERE open = TRUE;

INSERT INTO chat_user_threads (
  id,
  user_id,
  chat_id,
  open,
  total_unread,
  created_at,
  changed_at
) SELECT
    nextval('chat_user_threads_id_seq'),
    m.user_id,
    m.chat_id,
    m.present,
    0,
    m.created_at,
    m.added_at
  FROM chat_members m;

UPDATE chat_user_threads
SET message_id = (
  SELECT c.message_id
  FROM chats c
  WHERE c.id = chat_user_threads.chat_id
);

UPDATE chat_user_threads
SET total_unread = (
  SELECT c.user_unread
  FROM chats c
  WHERE c.id = chat_user_threads.chat_id
)
WHERE chat_user_threads.open = TRUE;

-- chat-user-thread-changes

CREATE TABLE chat_user_thread_changes (
  Id           BIGINT PRIMARY KEY,
  user_id      BIGINT      NOT NULL REFERENCES users (id),
  thread_id    BIGINT      NOT NULL REFERENCES chat_user_threads (id),
  chat_id      BIGINT      NOT NULL REFERENCES chats (id),

  open         BOOLEAN     NOT NULL DEFAULT FALSE,
  message_id   BIGINT REFERENCES chat_messages (id),
  total_unread INTEGER     NOT NULL DEFAULT 0,

  type         VARCHAR(32) NOT NULL,
  timestamp    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE SEQUENCE chat_user_thread_changes_id_seq;
CREATE INDEX chat_user_thread_changes_user_id_ix
  ON chat_user_thread_changes (user_id, id DESC);
CREATE INDEX chat_user_thread_changes_thread_id_ix
  ON chat_user_thread_changes (thread_id);
