-- 048 — Camada de Receita F0: fundação de identidade.
--
-- "Existir uma pessoa" na plataforma: global_contacts (uma linha por
-- conta+telefone canônico — a UNIQUE é o mecanismo atômico da fusão),
-- contact_events (imutável, append-only) e contact_attributes (editável,
-- precedência por fonte). Supressão promovida a fonte única cross-canal.
-- Índices parciais/BRIN vivem em migrations/always/ensure_indexes.sql.
-- (Aplicada em dev via db:push em 28/07/2026; este arquivo leva a prod.)

CREATE TABLE IF NOT EXISTS global_contacts (
  id varchar PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_user_id varchar NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  phone_number varchar(20) NOT NULL,        -- canônico normalizeBrPhone (55+DDD+9+8)
  name varchar(150),
  email_hash varchar(64),                   -- SHA-256 lowercase/trim; NUNCA e-mail em claro
  origem varchar(20) NOT NULL DEFAULT 'importacao',
  birth_date varchar(10),
  consent_status varchar(20) NOT NULL DEFAULT 'sem_registro',
  consent_source varchar(40),
  consent_at timestamptz,
  opt_out_channel varchar(20),
  opt_out_at timestamptz,
  last_click_at timestamptz,
  clicks_30d integer NOT NULL DEFAULT 0,
  last_purchase_at timestamptz,
  first_purchase_at timestamptz,
  total_spent numeric(14,2) NOT NULL DEFAULT 0,
  purchase_count integer NOT NULL DEFAULT 0,
  avg_ticket numeric(12,2),
  top_categories jsonb,
  engagement_state varchar(20) NOT NULL DEFAULT 'nunca_engajou',
  last_event_at timestamptz,
  tags jsonb,
  first_seen_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now(),
  CONSTRAINT global_contacts_owner_phone_uniq UNIQUE (owner_user_id, phone_number)
);
CREATE INDEX IF NOT EXISTS idx_global_contacts_owner ON global_contacts (owner_user_id);
CREATE INDEX IF NOT EXISTS idx_global_contacts_owner_engagement ON global_contacts (owner_user_id, engagement_state);
CREATE INDEX IF NOT EXISTS idx_global_contacts_owner_last_purchase ON global_contacts (owner_user_id, last_purchase_at);

CREATE TABLE IF NOT EXISTS contact_events (
  id bigserial PRIMARY KEY,
  owner_user_id varchar NOT NULL,
  contact_id varchar NOT NULL REFERENCES global_contacts(id) ON DELETE CASCADE,
  type varchar(30) NOT NULL,
  channel varchar(20),
  campaign_ref varchar(64),
  payload jsonb,
  occurred_at timestamptz NOT NULL DEFAULT now(),
  created_at timestamptz DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_contact_events_contact_occurred ON contact_events (contact_id, occurred_at);
CREATE INDEX IF NOT EXISTS idx_contact_events_owner_type_occurred ON contact_events (owner_user_id, type, occurred_at);

CREATE TABLE IF NOT EXISTS contact_attributes (
  id varchar PRIMARY KEY DEFAULT gen_random_uuid(),
  contact_id varchar NOT NULL REFERENCES global_contacts(id) ON DELETE CASCADE,
  key varchar(60) NOT NULL,
  value text,
  source varchar(20) NOT NULL,
  updated_at timestamptz DEFAULT now(),
  CONSTRAINT contact_attributes_contact_key_uniq UNIQUE (contact_id, key)
);

-- Supressão cross-canal: canal de origem + motivo na black-list existente.
ALTER TABLE client_blacklist_contacts ADD COLUMN IF NOT EXISTS channel_origin varchar(20);
ALTER TABLE client_blacklist_contacts ADD COLUMN IF NOT EXISTS reason varchar(200);

-- contacts vira "participação em campanha"; a pessoa vive em global_contacts.
ALTER TABLE contacts ADD COLUMN IF NOT EXISTS global_contact_id varchar;