--- channel-thread-sessions

CREATE TABLE channel_thread_sessions (
  id         BIGINT IDENTITY (1,1) PRIMARY KEY,
  thread_id  BIGINT      NOT NULL REFERENCES channel_threads (id),
  closed     BIT     NOT NULL  DEFAULT 1,
  created_at DATETIMEOFFSET NOT NULL  DEFAULT SYSDATETIMEOFFSET(),
  opened_at  DATETIMEOFFSET NOT NULL  DEFAULT SYSDATETIMEOFFSET(),
  closed_at  DATETIMEOFFSET
);

CREATE INDEX channel_thread_sessions_thread_id_ix
  ON channel_thread_sessions (thread_id);

-- channel-threads

ALTER TABLE channel_threads
  ADD closed BIT NOT NULL DEFAULT 1;

ALTER TABLE channel_threads
  ADD session_id BIGINT REFERENCES channel_thread_sessions (id);

CREATE INDEX channel_threads_channel_opened_ix
  ON channel_threads (channel_id, closed)
  WHERE closed = 0;

-- channel-thread-events

ALTER TABLE channel_thread_events
  ADD session_id BIGINT REFERENCES channel_thread_sessions (id);

-- channel-messages

ALTER TABLE channel_messages
  ADD session_id BIGINT REFERENCES channel_thread_sessions (id);

-- channels

ALTER TABLE channels
  ADD total_threads INTEGER NOT NULL DEFAULT 0;
ALTER TABLE channels
  ADD total_opened_threads INTEGER NOT NULL DEFAULT 0;
