-- 019-chats

CREATE TABLE chats (
  id            NUMBER(19) PRIMARY KEY,
  project_id    NUMBER(19)               NOT NULL REFERENCES projects (id),
  client_id     NUMBER(19)               NOT NULL  REFERENCES clients (id),
  channel_id    NUMBER(19)               NOT NULL REFERENCES channels (id),

  ticket_id     NUMBER(19) REFERENCES tickets (id),
  event_id      NUMBER(19),
  client_unread NUMBER(10)               NOT NULL,

  created_at    TIMESTAMP WITH TIME ZONE NOT NULL,
  changed_at    TIMESTAMP WITH TIME ZONE NOT NULL
);

CREATE SEQUENCE chats_id_seq;

ALTER TABLE tickets
  ADD CONSTRAINT tickets_chat_id_fk FOREIGN KEY (chat_id) REFERENCES chats (id);

CREATE UNIQUE INDEX chats_channel_client_uniq
  ON chats (channel_id, client_id);

CREATE INDEX chats_channel_id_ix
  ON chats (channel_id, changed_at DESC);

-- chat-messages

CREATE TABLE chat_messages (
  id                NUMBER(19) PRIMARY KEY,
  chat_id           NUMBER(19)               NOT NULL REFERENCES chats (id),
  local_id          NUMBER(19)               NOT NULL,
  event_id          NUMBER(19),
  ticket_message_id NUMBER(19) REFERENCES ticket_messages (id),

  author            VARCHAR2(32 CHAR)        NOT NULL,
  client_id         NUMBER(19) REFERENCES clients (id),
  user_id           NUMBER(19) REFERENCES users (id),

  payload           VARCHAR2(32 CHAR)        NOT NULL,
  text              VARCHAR2(4000 CHAR),
  file_id           VARCHAR2(255 CHAR) REFERENCES files (id),

  received          NUMBER(1)                NOT NULL,
  read              NUMBER(1)                NOT NULL,
  pushed            NUMBER(1)                NOT NULL,

  created_at        TIMESTAMP WITH TIME ZONE NOT NULL,
  received_at       TIMESTAMP WITH TIME ZONE,
  read_at           TIMESTAMP WITH TIME ZONE
);

CREATE SEQUENCE chat_messages_id_seq;

CREATE INDEX chat_messages_chat_id_ix
  ON chat_messages (chat_id, id DESC);

CREATE INDEX chat_messages_push_ix
  ON chat_messages (
    CASE WHEN received = 0 AND pushed = 0 AND author = 'user'
      THEN chat_id END,
    CASE WHEN received = 0 AND pushed = 0 AND author = 'user'
      THEN id END
  );

-- chat-events

CREATE TABLE chat_events (
  id         NUMBER(19) PRIMARY KEY,
  type       VARCHAR2(32 CHAR)        NOT NULL,
  chat_id    NUMBER(19)               NOT NULL REFERENCES chats (id),
  message_id NUMBER(19) REFERENCES chat_messages (id),

  actor      VARCHAR2(32 CHAR)        NOT NULL,
  client_id  NUMBER(19) REFERENCES clients (id),
  user_id    NUMBER(19) REFERENCES users (id),
  created_at TIMESTAMP WITH TIME ZONE NOT NULL
);

CREATE SEQUENCE chat_events_id_seq;

CREATE INDEX chat_events_chat_id_ix
  ON chat_events (chat_id, id DESC);
