-- 007-companies

CREATE TABLE companies (
  id            NUMBER(19) PRIMARY KEY,
  project_id    NUMBER(19)               NOT NULL REFERENCES projects (id),
  name          VARCHAR2(1024 CHAR)      NOT NULL,
  crm_id        NUMBER(19) REFERENCES crms (id),
  ext_ref       VARCHAR2(255 CHAR),
  total_members NUMBER(10)               NOT NULL,

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

CREATE SEQUENCE companies_id_seq;

CREATE INDEX companies_project_id_name_ix
  ON companies (project_id, name);

CREATE UNIQUE INDEX companies_crm_id_ext_ref_uniq
  ON companies (
    CASE WHEN crm_id IS NOT NULL AND ext_ref IS NOT NULL
      THEN crm_id END,
    CASE WHEN crm_id IS NOT NULL AND ext_ref IS NOT NULL
      THEN ext_ref END
  );
