--- chat-event-tree

CREATE TABLE chat_event_tree (
  id         BIGINT IDENTITY (1, 1) 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;

-- drop chat_events.public

DECLARE @ObjectName NVARCHAR(100)
SELECT @ObjectName = OBJECT_NAME([default_object_id])
FROM SYS.COLUMNS
WHERE [object_id] = OBJECT_ID('chat_events') AND [name] = 'public'
EXEC ('ALTER TABLE chat_events DROP CONSTRAINT ' + @ObjectName);

ALTER TABLE chat_events
  DROP COLUMN [public];

-- drop chat_events.transitive

DECLARE @ObjectName NVARCHAR(100)
SELECT @ObjectName = OBJECT_NAME([default_object_id])
FROM SYS.COLUMNS
WHERE [object_id] = OBJECT_ID('chat_events') AND [name] = 'transitive'
EXEC ('ALTER TABLE chat_events DROP CONSTRAINT ' + @ObjectName);

ALTER TABLE chat_events
  DROP COLUMN [transitive];

-- drop chat_events.session_id
DECLARE @constraint VARCHAR(100)
SELECT @constraint = KCU.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
  INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
    ON KCU.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
       AND KCU.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
       AND KCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
WHERE KCU.TABLE_NAME = 'chat_events' AND KCU.COLUMN_NAME = 'session_id'
EXEC ('ALTER TABLE chat_events drop CONSTRAINT ' + @constraint);

ALTER TABLE chat_events
  DROP COLUMN session_id;

-- alter chat_events
ALTER TABLE chat_events
  ALTER COLUMN chat_id BIGINT 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);

-- drop chat_user_inboxes.change_id
DECLARE @constraint VARCHAR(100)
SELECT @constraint = KCU.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
  INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
    ON KCU.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
       AND KCU.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
       AND KCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
WHERE KCU.TABLE_NAME = 'chat_user_inboxes' AND KCU.COLUMN_NAME = 'change_id'
EXEC ('ALTER TABLE chat_user_inboxes drop CONSTRAINT ' + @constraint);

ALTER TABLE chat_user_inboxes
  DROP COLUMN change_id;

-- alter chat_user_inboxes
ALTER TABLE chat_user_inboxes
  ADD event_id BIGINT REFERENCES chat_events (id);

-- drop chat_user_threads.change_id
DECLARE @constraint VARCHAR(100)
SELECT @constraint = KCU.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
  INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
    ON KCU.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
       AND KCU.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
       AND KCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
WHERE KCU.TABLE_NAME = 'chat_user_threads' AND KCU.COLUMN_NAME = 'change_id'
EXEC ('ALTER TABLE chat_user_threads drop CONSTRAINT ' + @constraint);

ALTER TABLE chat_user_threads
  DROP COLUMN change_id;

-- add chat_user_threads.event_id
ALTER TABLE chat_user_threads
  ADD event_id BIGINT REFERENCES chat_events (id);
