--- crms

CREATE TABLE crms (
  id          BIGINT PRIMARY KEY,
  project_id  BIGINT      NOT NULL REFERENCES projects (id),
  name        VARCHAR(64) NOT NULL,
  type        VARCHAR(64) NOT NULL,
  deleted     BOOLEAN     NOT NULL,

  isimple_url VARCHAR(255),

  created_at  TIMESTAMPTZ NOT NULL,
  updated_at  TIMESTAMPTZ NOT NULL,
  deleted_at  TIMESTAMPTZ,
  restored_at TIMESTAMPTZ
);

CREATE SEQUENCE crms_id_seq;
CREATE UNIQUE INDEX crms_uniq
  ON crms (project_id)
  WHERE deleted = FALSE;
CREATE INDEX crms_project_ix
  ON crms (project_id);

-- customers

CREATE TABLE crm_customers (
  id          BIGINT PRIMARY KEY,
  crm_id      BIGINT        NOT NULL REFERENCES crms (id),
  external_id VARCHAR(1024) NOT NULL,

  name        VARCHAR(1024) NOT NULL,
  first_name  VARCHAR(1024) NOT NULL,
  middle_name VARCHAR(1024) NOT NULL,
  last_name   VARCHAR(1024) NOT NULL,

  sex         VARCHAR(255)  NOT NULL,
  birthday    VARCHAR(255)  NOT NULL,
  cellphone   VARCHAR(255)  NOT NULL,
  email       VARCHAR(1024) NOT NULL,

  created_at  TIMESTAMPTZ   NOT NULL,
  updated_at  TIMESTAMPTZ   NOT NULL
);

CREATE SEQUENCE crm_customers_id_seq;

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