--- chat-event-tree

CREATE TABLE chat_event_tree (
  id         BIGSERIAL PRIMARY KEY,
  channel_id BIGINT REFERENCES channels (id),
  chat_id    BIGINT REFERENCES chats (id),
  client_id  BIGINT REFERENCES clients (id),
  user_id    BIGINT REFERENCES users (id),
  event_id   BIGINT NOT NULL REFERENCES chat_events (id)
);

CREATE INDEX chat_event_tree_channel_id_ix
  ON chat_event_tree (channel_id, event_id)
  WHERE channel_id IS NOT NULL;

CREATE INDEX chat_event_tree_chat_id_ix
  ON chat_event_tree (chat_id, event_id)
  WHERE chat_id IS NOT NULL;

CREATE INDEX chat_event_tree_client_id_ix
  ON chat_event_tree (client_id, event_id)
  WHERE client_id IS NOT NULL;

CREATE INDEX chat_event_tree_user_id_ix
  ON chat_event_tree (user_id, event_id)
  WHERE user_id IS NOT NULL;

-- chat_events

ALTER TABLE chat_events
  DROP COLUMN public;
ALTER TABLE chat_events
  DROP COLUMN transitive;
ALTER TABLE chat_events
  DROP COLUMN session_id;

ALTER TABLE chat_events
  ALTER COLUMN chat_id DROP NOT NULL;
ALTER TABLE chat_events
  ADD inbox_id BIGINT REFERENCES chat_user_inboxes (id);
ALTER TABLE chat_events
  ADD thread_id BIGINT REFERENCES chat_user_threads (id);

-- chat_inboxes

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

-- chat_inbox_threads

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