--- companies

CREATE TABLE companies (
  id            BIGINT PRIMARY KEY,
  project_id    BIGINT        NOT NULL REFERENCES projects (id),
  name          VARCHAR(1024) NOT NULL,
  crm_id        BIGINT REFERENCES crms (id),
  ext_ref       VARCHAR(255),
  total_members INTEGER       NOT NULL,

  created_at    TIMESTAMPTZ   NOT NULL,
  updated_at    TIMESTAMPTZ   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 (crm_id, ext_ref)
  WHERE crm_id IS NOT NULL AND ext_ref IS NOT NULL;

-- clients

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

CREATE INDEX clients_company_id_name_ix
  ON clients (company_id, lower(name));

-- 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);
