--- routes

CREATE TABLE routes (
  id           BIGINT PRIMARY KEY,
  project_id   BIGINT       NOT NULL REFERENCES projects (id),
  title        VARCHAR(255) NOT NULL,
  group_id     BIGINT       NOT NULL REFERENCES groups (id),
  "order"      INTEGER      NOT NULL,
  enabled      BOOLEAN      NOT NULL,
  deleted      BOOLEAN      NOT NULL,

  online       BOOLEAN      NOT NULL,
  channel_ids  VARCHAR      NOT NULL,
  category_ids VARCHAR      NOT NULL,

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

CREATE SEQUENCE routes_id_seq;

CREATE INDEX routes_project_id_ix
  ON routes (project_id);
CREATE INDEX routes_group_id_ix
  ON routes (group_id);

CREATE UNIQUE INDEX routes_project_id_title_not_deleted_uniq
  ON routes (project_id, lower(title))
  WHERE deleted = FALSE;

CREATE UNIQUE INDEX routes_project_id_order_not_deleted_uniq
  ON routes (project_id, "order")
  WHERE deleted = FALSE;

-- users

ALTER TABLE users
  ADD assigned_at TIMESTAMPTZ,
  ADD total_assigned_tickets INTEGER;

UPDATE users
SET total_assigned_tickets = (
  SELECT count(*)
  FROM tickets
  WHERE
    tickets.assignee_id = users.id
    AND tickets.open
);

ALTER TABLE users
  ALTER COLUMN total_assigned_tickets SET NOT NULL;
