--- clients

ALTER TABLE clients
  ADD
  project_id BIGINT REFERENCES projects (id),
  customer_id BIGINT REFERENCES crm_customers (id),
  channel_id BIGINT REFERENCES channels (id),
  messaged_at DATETIMEOFFSET;

UPDATE clients
SET [type] =
CASE
WHEN (integration_id IS NOT NULL)
  THEN 'crm'
WHEN [type] = 'anonymous'
  THEN 'internal'
ELSE 'external'
END;

DROP INDEX clients.clients_online_last_seen_at;
EXEC sp_RENAME 'clients.last_seen_at', 'seen_at', 'COLUMN';

CREATE UNIQUE INDEX clients_project_customer_uniq
  ON clients (project_id, customer_id)
  WHERE customer_id IS NOT NULL;
CREATE UNIQUE INDEX clients_project_telegram_uniq
  ON clients (project_id, telegram_id)
  WHERE telegram_id IS NOT NULL;
CREATE UNIQUE INDEX clients_project_facebook_uniq
  ON clients (project_id, facebook_id)
  WHERE facebook_id IS NOT NULL;
CREATE UNIQUE INDEX clients_project_vk_uniq
  ON clients (project_id, vk_id)
  WHERE vk_id IS NOT NULL;

CREATE INDEX clients_project_id_name_ix
  ON clients (project_id, name);
CREATE INDEX clients_project_id_seen_at_ix
  ON clients (project_id, seen_at);
CREATE INDEX clients_online_seen_at_ix
  ON clients (seen_at)
  WHERE online = 1;
