--- tickets

CREATE TABLE tickets (
  id             BIGINT PRIMARY KEY,
  project_id     BIGINT       NOT NULL REFERENCES projects (id),
  client_id      BIGINT       NOT NULL REFERENCES clients (id),

  title          VARCHAR(255) NOT NULL,
  open           BOOLEAN      NOT NULL,

  chat_id        BIGINT REFERENCES chats (id),
  channel_id     BIGINT REFERENCES channels (id),
  assignee_id    BIGINT REFERENCES users (id),
  message_id     BIGINT,

  total_messages INTEGER      NOT NULL,
  total_members  INTEGER      NOT NULL,

  created_at     TIMESTAMPTZ  NOT NULL,
  updated_at     TIMESTAMPTZ  NOT NULL,
  opened_at      TIMESTAMPTZ,
  closed_at      TIMESTAMPTZ,

  assigned_at    TIMESTAMPTZ,
  responded_at   TIMESTAMPTZ,

  assign_time    INTEGER      NULL,
  response_time  INTEGER      NULL,
  total_time     INTEGER      NULL
);

CREATE SEQUENCE tickets_id_seq;

CREATE INDEX tickets_project_id_updated_at_ix
  ON tickets (project_id, updated_at DESC);
CREATE INDEX tickets_assignee_id_updated_at_ix
  ON tickets (assignee_id, updated_at DESC)
  WHERE assignee_id IS NOT NULL;

-- ticket-members

CREATE TABLE ticket_members (
  id            BIGINT PRIMARY KEY,
  ticket_id     BIGINT      NOT NULL REFERENCES tickets (id),
  user_id       BIGINT      NOT NULL REFERENCES users (id),
  deleted       BOOLEAN     NOT NULL,

  created_at    TIMESTAMPTZ NOT NULL,
  updated_at    TIMESTAMPTZ NOT NULL,
  assigned_at   TIMESTAMPTZ,
  unassigned_at TIMESTAMPTZ,
  deleted_at    TIMESTAMPTZ
);

CREATE SEQUENCE ticket_members_id_seq;

CREATE UNIQUE INDEX ticket_members_uniq
  ON ticket_members (user_id, ticket_id);

-- ticket-actions

CREATE TABLE ticket_actions (
  id         BIGINT PRIMARY KEY,
  ticket_id  BIGINT      NOT NULL REFERENCES tickets (id),
  type       VARCHAR(32) NOT NULL,

  actor_id   BIGINT REFERENCES users (id),
  user_id    BIGINT REFERENCES users (id),

  created_at TIMESTAMPTZ NOT NULL
);

CREATE SEQUENCE ticket_actions_id_seq;

CREATE INDEX ticket_actions_ticket_id_ix
  ON ticket_actions (ticket_id);

-- ticket-messages

CREATE TABLE ticket_messages (
  id           BIGINT PRIMARY KEY,
  uid          VARCHAR(40) NOT NULL,
  ticket_id    BIGINT      NOT NULL REFERENCES tickets (id),
  local_id     BIGINT      NOT NULL,

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

  type         VARCHAR(32) NOT NULL,
  text         VARCHAR     NOT NULL,
  file_id      VARCHAR(255) REFERENCES files (id),
  action_id    BIGINT REFERENCES ticket_actions (id),
  autoreply_id BIGINT REFERENCES autoreplies (id),

  received     BOOLEAN     NOT NULL,
  read         BOOLEAN     NOT NULL,

  created_at   TIMESTAMPTZ NOT NULL,
  updated_at   TIMESTAMPTZ NOT NULL,
  received_at  TIMESTAMPTZ,
  read_at      TIMESTAMPTZ
);

CREATE SEQUENCE ticket_messages_id_seq;

CREATE INDEX ticket_messages_ticket_id_ix
  ON ticket_messages (ticket_id);

-- chats

ALTER TABLE chats
  ADD ticket_id BIGINT REFERENCES tickets (id);

-- chat-messages

ALTER TABLE chat_messages
  ADD ticket_message_id BIGINT REFERENCES ticket_messages (id);
ALTER TABLE chat_messages
  ALTER COLUMN session_id DROP NOT NULL;

DROP INDEX chat_messages_uid_uniq;
ALTER TABLE chat_messages
  ALTER COLUMN uid DROP NOT NULL;

-- chat-events

UPDATE chat_events
SET chat_id = (
  SELECT m.chat_id
  FROM chat_messages m
  WHERE m.id = chat_events.message_id)
WHERE message_id IS NOT NULL;

UPDATE chat_events
SET chat_id = (
  SELECT m.chat_id
  FROM chat_members m
  WHERE m.id = chat_events.member_id
)
WHERE member_id IS NOT NULL;
