-- 006-crms


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

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

CREATE SEQUENCE crms_id_seq;

CREATE INDEX crms_project_ix
  ON crms (project_id);

CREATE UNIQUE INDEX crms_uniq
  ON crms (
    CASE WHEN deleted = 0
      THEN project_id
    END
  );

-- crm-customers

CREATE TABLE crm_customers (
  id          NUMBER(19) PRIMARY KEY,
  crm_id      NUMBER(19)               NOT NULL REFERENCES crms (id),
  external_id VARCHAR2(1024 CHAR)      NOT NULL,

  name        VARCHAR2(1024 CHAR)      NOT NULL,
  first_name  VARCHAR2(1024 CHAR),
  middle_name VARCHAR2(1024 CHAR),
  last_name   VARCHAR2(1024 CHAR),

  sex         VARCHAR2(255 CHAR),
  birthday    VARCHAR2(255 CHAR),
  cellphone   VARCHAR2(255 CHAR),
  email       VARCHAR2(255 CHAR),

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

CREATE SEQUENCE crm_customers_id_seq;

CREATE UNIQUE INDEX crm_customers_uniq
  ON crm_customers (crm_id, external_id);
