--- channel-events

CREATE TABLE channel_events (
  id          BIGINT IDENTITY (1,1) PRIMARY KEY,
  type        VARCHAR(64)     NOT NULL,
  channel_id  BIGINT      NOT NULL REFERENCES channels (id),
  member_id   BIGINT REFERENCES channel_members (id),

  actor_id    BIGINT      NOT NULL REFERENCES users (id),
  actor_ip    VARCHAR(45),
  actor_ua_id BIGINT      NOT NULL REFERENCES user_agents (id),

  created_at  DATETIMEOFFSET NOT NULL  DEFAULT SYSDATETIMEOFFSET()
);

CREATE INDEX channel_events_channel_id_ix
  ON channel_events (channel_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 = 'channels' AND KCU.COLUMN_NAME = 'event_id'
EXEC('alter table channels drop  CONSTRAINT ' + @constraint);

ALTER TABLE channels
  DROP COLUMN event_id;

ALTER TABLE channels
  ADD event_id BIGINT REFERENCES channel_events (id);

ALTER TABLE channels
  ADD total_members INTEGER NOT NULL DEFAULT 0;
