--- clients

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

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

ALTER TABLE clients
  RENAME COLUMN last_seen_at TO seen_at;
DROP INDEX clients_online_last_seen_at;

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_ix
  ON clients (project_id, seen_at);
CREATE INDEX clients_online_seen_at_ix
  ON clients (seen_at)
  WHERE online = TRUE;
