--- company-members

CREATE TABLE company_members (
  id         NUMBER(19) PRIMARY KEY,
  company_id NUMBER(19)               NOT NULL REFERENCES companies (id),
  client_id  NUMBER(19)               NOT NULL REFERENCES clients (id),
  deleted    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 company_members_id_seq;

CREATE UNIQUE INDEX company_members_uniq
  ON company_members (company_id, client_id);

CREATE INDEX company_members_client_id_ix
  ON company_members (client_id);

-- clients

ALTER TABLE clients
  ADD (
  crm_id NUMBER(19) REFERENCES crms (id),
  crm_client_id VARCHAR2(1024)
  );

CREATE UNIQUE INDEX clients_crm_uniq
  ON clients (
    CASE WHEN crm_id IS NOT NULL
      THEN crm_id
    ELSE NULL END,
    CASE WHEN crm_client_id IS NOT NULL
      THEN crm_client_id
    ELSE NULL END
  );

-- migrate existing clients

INSERT INTO company_members (
  id,
  company_id,
  client_id,
  deleted,
  created_at,
  updated_at
) (
  SELECT
    company_members_id_seq.nextval,
    c.company_id,
    c.id,
    0,
    c.created_at,
    c.updated_at
  FROM clients c
  WHERE c.company_id IS NOT NULL
);
