--- company-members

CREATE TABLE company_members (
  id         BIGINT PRIMARY KEY IDENTITY (1, 1),
  company_id BIGINT         NOT NULL REFERENCES companies (id),
  client_id  BIGINT         NOT NULL REFERENCES clients (id),
  deleted    BIT            NOT NULL,

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

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 BIGINT REFERENCES crms (id),
  crm_client_id NVARCHAR(1024);

CREATE UNIQUE INDEX clients_crm_id_crm_client_id_uniq
  ON clients (crm_id, crm_client_id)
  WHERE crm_id IS NOT NULL AND crm_client_id IS NOT NULL;

-- migrate existing clients

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