-- 017-tickets

CREATE TABLE tickets (
  id             NUMBER(19) PRIMARY KEY,
  project_id     NUMBER(19)               NOT NULL REFERENCES projects (id),
  client_id      NUMBER(19)               NOT NULL REFERENCES clients (id),
  company_id     NUMBER(19) REFERENCES companies (id),

  title          VARCHAR2(255 CHAR),
  open           NUMBER(1)                NOT NULL,
  state          VARCHAR2(32 CHAR)        NOT NULL,

  category_id    NUMBER(19) REFERENCES categories (id),
  channel_id     NUMBER(19) REFERENCES channels (id),
  chat_id        NUMBER(19),

  group_id       NUMBER(19) REFERENCES groups (id),
  assignee_id    NUMBER(19) REFERENCES users (id),
  message_id     NUMBER(19),

  total_messages NUMBER(10)               NOT NULL,
  total_members  NUMBER(10)               NOT NULL,

  created_at     TIMESTAMP WITH TIME ZONE NOT NULL,
  updated_at     TIMESTAMP WITH TIME ZONE NOT NULL,
  opened_at      TIMESTAMP WITH TIME ZONE NOT NULL,
  closed_at      TIMESTAMP WITH TIME ZONE,
  assigned_at    TIMESTAMP WITH TIME ZONE,
  responded_at   TIMESTAMP WITH TIME ZONE,

  assign_time    NUMBER(10),
  response_time  NUMBER(10),
  total_time     NUMBER(10)
);

CREATE SEQUENCE tickets_id_seq;

CREATE INDEX tickets_assignee_id_ix
  ON tickets (
    CASE WHEN assignee_id IS NOT NULL
      THEN assignee_id END,
    CASE WHEN assignee_id IS NOT NULL
      THEN created_at END
  );

CREATE INDEX tickets_category_id_ix
  ON tickets (
    CASE WHEN category_id IS NOT NULL
      THEN category_id END,
    CASE WHEN category_id IS NOT NULL
      THEN created_at END
  );

CREATE INDEX tickets_company_id_ix
  ON tickets (
    CASE WHEN company_id IS NOT NULL
      THEN company_id END,
    CASE WHEN company_id IS NOT NULL
      THEN created_at END
  );

CREATE INDEX tickets_group_id_ix
  ON tickets (
    CASE WHEN group_id IS NOT NULL
      THEN group_id END,
    CASE WHEN group_id IS NOT NULL
      THEN created_at END
  );

CREATE INDEX tickets_project_id_ix
  ON tickets (project_id, created_at);

CREATE INDEX tickets_project_id_state_ix
  ON tickets (project_id, state, created_at);

-- ticket-members

CREATE TABLE ticket_members (
  id            NUMBER(19) PRIMARY KEY,
  ticket_id     NUMBER(19)               NOT NULL REFERENCES tickets (id),
  user_id       NUMBER(19)               NOT NULL REFERENCES users (id),
  deleted       NUMBER(1)                NOT NULL,
  created_at    TIMESTAMP WITH TIME ZONE NOT NULL,
  updated_at    TIMESTAMP WITH TIME ZONE NOT NULL,
  assigned_at   TIMESTAMP WITH TIME ZONE,
  unassigned_at TIMESTAMP WITH TIME ZONE,
  deleted_at    TIMESTAMP WITH TIME ZONE
);

CREATE SEQUENCE ticket_members_id_seq;

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

-- ticket-messages

CREATE TABLE ticket_messages (
  id           NUMBER(19) PRIMARY KEY,
  "uid"        VARCHAR2(40 CHAR)        NOT NULL,
  ticket_id    NUMBER(19)               NOT NULL REFERENCES tickets (id),
  local_id     NUMBER(19)               NOT NULL,

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

  type         VARCHAR2(32 CHAR)        NOT NULL,
  text         VARCHAR2(4000 CHAR),
  file_id      VARCHAR2(255) REFERENCES files (id),
  action_id    NUMBER(19),
  autoreply_id NUMBER(19) REFERENCES autoreplies (id),

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

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

CREATE SEQUENCE ticket_messages_id_seq;

CREATE INDEX ticket_messages_ticket_id_ix
  ON ticket_messages (ticket_id);

-- ticket-actions

CREATE TABLE ticket_actions (
  id         NUMBER(19) PRIMARY KEY,
  ticket_id  NUMBER(19)               NOT NULL REFERENCES tickets (id),
  type       VARCHAR2(32 CHAR)        NOT NULL,

  actor_id   NUMBER(19) REFERENCES users (id),
  user_id    NUMBER(19) REFERENCES users (id),
  group_id   NUMBER(19) REFERENCES groups (id),

  created_at TIMESTAMP WITH TIME ZONE NOT NULL
);

CREATE SEQUENCE ticket_actions_id_seq;

CREATE INDEX ticket_actions_ticket_id_ix
  ON ticket_actions (ticket_id);

ALTER TABLE tickets
  ADD CONSTRAINT tickets_message_id_fk FOREIGN KEY (message_id) REFERENCES ticket_messages (id);

-- tickets-export-view

CREATE VIEW tickets_export_view AS
  SELECT
    t.id,
    t.open,

    t.project_id,
    projects.name    AS project,

    t.channel_id,
    channels.title   AS channel,

    t.category_id,
    categories.title AS category,

    t.client_id,
    clients.name     AS client,

    t.assignee_id,
    assignees.name   AS assignee,
    assignees.email  AS assignee_email,

    t.assign_time,
    t.response_time,
    t.total_time,

    t.total_messages,
    t.total_members,

    t.created_at,
    t.closed_at

  FROM tickets t
    JOIN projects ON projects.id = t.project_id
    LEFT OUTER JOIN channels ON channels.id = t.channel_id
    LEFT OUTER JOIN categories ON categories.id = t.category_id
    JOIN clients ON clients.id = t.client_id
    LEFT OUTER JOIN users assignees ON assignees.id = t.assignee_id;

-- ticket-messages-export-view

CREATE VIEW ticket_messages_export_view AS
  SELECT
    m.id,
    m.ticket_id,

    projects.name    AS project,
    channels.title   AS channel,
    categories.title AS category,

    m.client_id,
    clients.name     AS client,
    users.name       AS "user",
    users.email      AS user_email,

    m.type,
    m.text,

    m.received,
    m.read,

    m.created_at,
    m.received_at,
    m.read_at

  FROM ticket_messages m
    LEFT JOIN tickets ON tickets.id = m.ticket_id
    LEFT OUTER JOIN projects ON projects.id = tickets.project_id
    LEFT OUTER JOIN channels ON channels.id = tickets.channel_id
    LEFT OUTER JOIN categories ON categories.id = tickets.category_id

    LEFT OUTER JOIN files ON files.id = m.file_id
    LEFT OUTER JOIN clients ON clients.id = m.client_id
    LEFT OUTER JOIN users ON users.id = m.user_id;
