--- categories


CREATE TABLE categories (
  id         BIGINT PRIMARY KEY IDENTITY (1, 1),
  project_id BIGINT         NOT NULL REFERENCES projects (id),
  parent_id  BIGINT REFERENCES categories (id),

  title      NVARCHAR(255)  NOT NULL,
  title_lower                   AS LOWER(title),
  is_folder  BIT            NOT NULL,
  deleted    BIT            NOT NULL,

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

CREATE INDEX categories_project_id_ix
  ON categories (project_id);

CREATE UNIQUE INDEX categories_project_id_title_not_deleted_uniq
  ON categories (project_id, title_lower)
  WHERE deleted = 0;

-- tickets

ALTER TABLE tickets
  ADD category_id BIGINT REFERENCES categories (id);

CREATE INDEX tickets_category_id_ix
  ON tickets (category_id, created_at DESC)
  WHERE category_id IS NOT NULL;
