--- companies

CREATE TABLE companies (
  id            BIGINT PRIMARY KEY  IDENTITY (1, 1),
  project_id    BIGINT         NOT NULL REFERENCES projects (id),
  name          NVARCHAR(1024) NOT NULL,
  name_lower                        AS lower(name),
  crm_id        BIGINT REFERENCES crms (id),
  ext_ref       NVARCHAR(255),
  total_members INTEGER        NOT NULL,

  created_at    DATETIMEOFFSET NOT NULL,
  updated_at    DATETIMEOFFSET NOT NULL
);

CREATE INDEX companies_project_id_name_lower_ix
  ON companies (project_id, name_lower);

CREATE UNIQUE INDEX companies_crm_id_ext_ref_uniq
  ON companies (crm_id, ext_ref)
  WHERE crm_id IS NOT NULL AND ext_ref IS NOT NULL;

-- clients

ALTER TABLE clients
  ADD name_lower AS lower(name);
ALTER TABLE clients
  ADD company_id BIGINT REFERENCES companies (id);

CREATE INDEX clients_company_id_name_lower_ix
  ON clients (company_id, name_lower);

-- tickets

ALTER TABLE tickets
  ADD company_id BIGINT REFERENCES companies (id);

CREATE INDEX tickets_company_id_created_at_ix
  ON tickets (company_id, created_at);
