--- routes

CREATE TABLE routes (
  id           BIGINT PRIMARY KEY               IDENTITY (1, 1),
  project_id   BIGINT         NOT NULL REFERENCES projects (id),
  title        NVARCHAR(255)  NOT NULL,
  title_lower                                   AS lower(title),
  group_id     BIGINT         NOT NULL REFERENCES groups (id),
  [order]      INTEGER        NOT NULL,
  enabled      BIT            NOT NULL,
  deleted      BIT            NOT NULL,

  online       BIT            NOT NULL,
  channel_ids  NVARCHAR(4000) NOT NULL,
  category_ids NVARCHAR(4000) NOT NULL,

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


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, title_lower)
  WHERE deleted = 0;

CREATE UNIQUE INDEX routes_project_id_order_not_deleted_uniq
  ON routes (project_id, [order])
  WHERE deleted = 0;

-- users

ALTER TABLE users
  ADD assigned_at DATETIMEOFFSET,
  total_assigned_tickets INTEGER;

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

ALTER TABLE users
  ALTER COLUMN total_assigned_tickets INTEGER NOT NULL;
