--- categories

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

  title      VARCHAR(255) NOT NULL,
  is_folder  BOOLEAN      NOT NULL,
  deleted    BOOLEAN      NOT NULL,

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

CREATE SEQUENCE categories_id_seq;

CREATE INDEX categories_project_id_ix
  ON categories (project_id);

CREATE UNIQUE INDEX categories_project_id_title_not_deleted_uniq
  ON categories (project_id, lower(title))
  WHERE deleted = FALSE;

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