-- 005-channels


CREATE TABLE channels (
  id                 NUMBER(19) PRIMARY KEY,
  project_id         NUMBER(19)               NOT NULL REFERENCES projects (id),
  type               VARCHAR2(64)             NOT NULL,
  name               VARCHAR2(255 CHAR)       NOT NULL,
  title              VARCHAR2(255 CHAR)       NOT NULL,
  description        VARCHAR2(255 CHAR)       NOT NULL,
  deleted            NUMBER(1)                NOT NULL,

  total_clients      NUMBER(10)               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
);

CREATE SEQUENCE channels_id_seq;

CREATE INDEX channels_project_id_ix
  ON channels (project_id);

CREATE UNIQUE INDEX channels_name_uniq
  ON channels (
    CASE WHEN deleted = 0
      THEN project_id END,
    CASE WHEN deleted = 0
      THEN lower(name) END
  );

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