-- Channels

CREATE TABLE channels (
  id          BIGSERIAL PRIMARY KEY,
  org_id      BIGINT      NOT NULL  REFERENCES orgs (id),
  name        VARCHAR     NOT NULL,
  title       VARCHAR     NOT NULL,
  description VARCHAR     NOT NULL,
  deleted     BOOLEAN              DEFAULT FALSE,
  event_id    BIGINT,

  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX channels_org_id_ix
  ON channels (org_id);
CREATE UNIQUE INDEX channels_org_id_name_nondeleted_uniq
  ON channels (org_id, lower(name))
  WHERE deleted = FALSE;

-- Threads

CREATE TABLE channel_threads (
  id            BIGSERIAL 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    TIMESTAMPTZ NOT NULL  DEFAULT now(),
  updated_at    TIMESTAMPTZ NOT NULL  DEFAULT now()
);

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          BIGSERIAL PRIMARY KEY,
  thread_id   BIGINT      NOT NULL REFERENCES channel_threads (id),
  local_id   BIGINT,

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

  payload     VARCHAR     NOT NULL,
  text        VARCHAR,
  event_id    BIGINT,

  received    BOOLEAN     NOT NULL  DEFAULT FALSE,
  read        BOOLEAN     NOT NULL  DEFAULT FALSE,

  created_at  TIMESTAMPTZ NOT NULL  DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL  DEFAULT now(),
  received_at TIMESTAMPTZ NULL,
  read_at     TIMESTAMPTZ 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         BIGSERIAL PRIMARY KEY,
  type       VARCHAR     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 TIMESTAMPTZ NOT NULL DEFAULT now()
);

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);
