--- crms


CREATE TABLE crms (
  id          BIGINT PRIMARY KEY IDENTITY (1, 1),
  project_id  BIGINT         NOT NULL REFERENCES projects (id),
  name        NVARCHAR(64)   NOT NULL,
  type        NVARCHAR(64)   NOT NULL,
  deleted     BIT            NOT NULL,

  isimple_url NVARCHAR(255),

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

CREATE UNIQUE INDEX crms_uniq
  ON crms (project_id)
  WHERE deleted = 0;
CREATE INDEX crms_project_ix
  ON crms (project_id);

-- customers

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

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

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

  created_at  DATETIMEOFFSET NOT NULL,
  updated_at  DATETIMEOFFSET NOT NULL
);

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