-- 003-projects

CREATE TABLE projects (
  id                 NUMBER(19) PRIMARY KEY,
  name               VARCHAR2(64 CHAR)        NOT NULL,
  description        VARCHAR2(255 CHAR)       NOT NULL,
  state              VARCHAR2(32 CHAR)        NOT NULL,

  total_tickets      NUMBER(10)               NOT NULL,
  total_open_tickets NUMBER(10)               NOT NULL,

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

CREATE SEQUENCE projects_id_seq;

CREATE UNIQUE INDEX projects_name_uniq
  ON projects (
    CASE WHEN state = 'active'
      THEN lower(name)
    END
  );
