--- chat-user-changes

CREATE TABLE chat_user_changes (
  id         BIGINT PRIMARY KEY      IDENTITY (1, 1),

  user_id    BIGINT         NOT NULL REFERENCES users (id),
  type       VARCHAR(32)    NOT NULL,

  inbox_id   BIGINT REFERENCES chat_user_inboxes (id),
  thread_id  BIGINT REFERENCES chat_user_threads (id),
  message_id BIGINT REFERENCES chat_messages (id),

  timestamp  DATETIMEOFFSET NOT NULL DEFAULT sysdatetimeoffset()
);

CREATE INDEX chat_user_changes_user_id_ix
  ON chat_user_changes (user_id, id);

-- chat_user_inboxes

ALTER TABLE chat_user_inboxes
  DROP COLUMN change_id;
ALTER TABLE chat_user_inboxes
  ADD change_id BIGINT REFERENCES chat_user_changes (id);

-- chat_user_threads

ALTER TABLE chat_user_threads
  DROP COLUMN change_id;
ALTER TABLE chat_user_threads
  ADD change_id BIGINT REFERENCES chat_user_changes (id);
