-- 011-categories

CREATE TABLE categories (
  id         NUMBER(19) PRIMARY KEY,
  project_id NUMBER(19)               NOT NULL REFERENCES projects (id),
  parent_id  NUMBER(19) REFERENCES categories (id),
  title      VARCHAR2(255 CHAR)       NOT NULL,
  is_folder  NUMBER(1)                NOT NULL,
  deleted    NUMBER(1)                NOT NULL,

  created_at TIMESTAMP WITH TIME ZONE NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL,
  deleted_at TIMESTAMP WITH TIME ZONE
);

CREATE SEQUENCE categories_id_seq;

CREATE INDEX categories_project_id_ix
  ON categories (project_id);

CREATE UNIQUE INDEX categories_project_title_uniq
  ON categories (
    CASE WHEN deleted = 0
      THEN project_id END,
    CASE WHEN deleted = 0
      THEN lower(title) END
  );
