--- tickets

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

  title          NVARCHAR(255)  NOT NULL,
  [open]         BIT            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     DATETIMEOFFSET NOT NULL,
  updated_at     DATETIMEOFFSET NOT NULL,
  opened_at      DATETIMEOFFSET,
  closed_at      DATETIMEOFFSET,

  assigned_at    DATETIMEOFFSET,
  responded_at   DATETIMEOFFSET,

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


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 IDENTITY (1, 1),
  ticket_id     BIGINT         NOT NULL REFERENCES tickets (id),
  user_id       BIGINT         NOT NULL REFERENCES users (id),
  deleted       BIT            NOT NULL,

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

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

-- ticket-actions

CREATE TABLE ticket_actions (
  id         BIGINT PRIMARY KEY IDENTITY (1, 1),
  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 DATETIMEOFFSET NOT NULL
);

CREATE INDEX ticket_actions_ticket_id_ix
  ON ticket_actions (ticket_id);

-- ticket-messages

CREATE TABLE ticket_messages (
  id           BIGINT PRIMARY KEY IDENTITY (1, 1),
  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         NVARCHAR(MAX)  NOT NULL,
  file_id      VARCHAR(255) REFERENCES files (id),
  action_id    BIGINT REFERENCES ticket_actions (id),
  autoreply_id BIGINT REFERENCES autoreplies (id),

  received     BIT            NOT NULL,
  [read]       BIT            NOT NULL,

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

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 BIGINT NULL;

DROP INDEX chat_messages.chat_messages_uid_uniq;
ALTER TABLE chat_messages
  ALTER COLUMN uid VARCHAR(40) 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;
