-- Channels

CREATE TABLE channels (
  id          BIGINT IDENTITY(1,1) PRIMARY KEY,
  org_id      BIGINT      NOT NULL  REFERENCES orgs (id),
  name        NVARCHAR(255)     NOT NULL,
  name_lower  AS LOWER(name),
  title       NVARCHAR(255)     NOT NULL,
  description NVARCHAR(255)     NOT NULL,
  deleted     BIT              DEFAULT 0,
  event_id    BIGINT,

  created_at  DATETIMEOFFSET NOT NULL DEFAULT SYSDATETIMEOFFSET(),
  updated_at  DATETIMEOFFSET NOT NULL DEFAULT SYSDATETIMEOFFSET()
);

CREATE INDEX channels_org_id_ix
  ON channels (org_id);
CREATE UNIQUE INDEX channels_org_id_name_nondeleted_uniq
  ON channels (org_id, name_lower)
  WHERE deleted = 0;

-- Threads

CREATE TABLE channel_threads (
  id            BIGINT IDENTITY(1,1) PRIMARY KEY,
  channel_id    BIGINT      NOT NULL REFERENCES channels (id),
  client_id     BIGINT      NOT NULL REFERENCES clients (id),
  event_id      BIGINT,

  client_unread INTEGER     NOT NULL  DEFAULT 0,
  user_unread   INTEGER     NOT NULL  DEFAULT 0,

  created_at    DATETIMEOFFSET NOT NULL  DEFAULT SYSDATETIMEOFFSET(),
  updated_at    DATETIMEOFFSET NOT NULL  DEFAULT SYSDATETIMEOFFSET()
);

CREATE INDEX channel_threads_channel_id_ix
  ON channel_threads (channel_id);
CREATE UNIQUE INDEX channel_threads_channel_id_client_id_uniq
  ON channel_threads (channel_id, client_id);

-- Messages

CREATE TABLE channel_messages (
  id          BIGINT IDENTITY(1,1) PRIMARY KEY,
  thread_id   BIGINT      NOT NULL REFERENCES channel_threads (id),
  local_id   BIGINT,

  author      NVARCHAR(255)     NOT NULL,
  client_id   BIGINT REFERENCES clients (id),
  user_id     BIGINT REFERENCES users (id),

  payload     VARCHAR(32)     NOT NULL,
  text        NVARCHAR(1024),
  event_id    BIGINT,

  received    BIT     NOT NULL  DEFAULT 0,
  "read"        BIT     NOT NULL  DEFAULT 0,

  created_at  DATETIMEOFFSET NOT NULL  DEFAULT SYSDATETIMEOFFSET(),
  updated_at  DATETIMEOFFSET NOT NULL  DEFAULT SYSDATETIMEOFFSET(),
  received_at DATETIMEOFFSET NULL,
  read_at     DATETIMEOFFSET NULL
);

CREATE INDEX channel_messages_thread_id_id_desc
  ON channel_messages (thread_id, id DESC);
CREATE UNIQUE INDEX channel_messages_thread_id_user_id_local_id
  ON channel_messages (thread_id, user_id, local_id)
  WHERE user_id IS NOT NULL;
CREATE UNIQUE INDEX channel_messages_thread_id_client_id_local_id
  ON channel_messages (thread_id, client_id, local_id)
  WHERE client_id IS NOT NULL;

-- 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),
  thread_id  BIGINT      NOT NULL REFERENCES channel_threads (id),
  message_id BIGINT REFERENCES channel_messages (id),

  client_id  BIGINT REFERENCES clients (id),
  user_id    BIGINT REFERENCES users (id),
  created_at DATETIMEOFFSET NOT NULL DEFAULT SYSDATETIMEOFFSET()
);

CREATE INDEX channel_events_channel_id_id_desc_ix
  ON channel_events (channel_id, id DESC);
CREATE INDEX channel_events_thread_id_id_desc_ix
  ON channel_events (thread_id, id DESC);

ALTER TABLE channels
  ADD FOREIGN KEY (event_id) REFERENCES channel_events (id);
ALTER TABLE channel_threads
  ADD FOREIGN KEY (event_id) REFERENCES channel_events (id);
ALTER TABLE channel_messages
  ADD FOREIGN KEY (event_id) REFERENCES channel_events (id);
