--- company-members

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

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

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 BIGINT REFERENCES crms (id),
  ADD crm_client_id VARCHAR(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 (
  id,
  company_id,
  client_id,
  deleted,
  created_at,
  updated_at
) (
  SELECT
    nextval('company_members_id_seq'),
    c.company_id,
    c.id,
    FALSE,
    c.created_at,
    c.updated_at
  FROM clients c
  WHERE c.company_id IS NOT NULL
);
