-- 012-routes

CREATE TABLE routes (
  id              NUMBER(19) PRIMARY KEY,
  project_id      NUMBER(19)               NOT NULL REFERENCES projects (id),
  title           VARCHAR2(255 CHAR)       NOT NULL,
  group_id        NUMBER(19)               NOT NULL,
  "order"         NUMBER(10)               NOT NULL,
  enabled         NUMBER(1)                NOT NULL,
  deleted         NUMBER(1)                NOT NULL,

  "online"        NUMBER(1)                NOT NULL,
  channel_ids     VARCHAR2(4000 CHAR),
  channel_filter  NUMBER(1)                NOT NULL,
  category_ids    VARCHAR2(4000 CHAR),
  category_filter 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 routes_id_seq;

CREATE INDEX routes_group_id_ix
  ON routes (group_id);

CREATE INDEX routes_project_id_ix
  ON routes (project_id);

CREATE INDEX routes_project_id_order_ix
  ON routes (
    CASE WHEN deleted = 0
      THEN project_id END,
    CASE WHEN deleted = 0
      THEN "order" END);

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