-- ============================================================================
-- SCHEMA UTM OBFUSCATION — Fase de Segmentação de Valor
-- ============================================================================
-- Este schema estende o schema.sql existente com colunas para UTMs obfuscadas
-- e uma tabela de mapeamento para de-obfuscação no runtime.
--
-- Como funciona:
-- 1. utm_mappings: mapeia hash obfuscado → valor real (configuração)
-- 2. leads: adiciona colunas faixa_real, faixa_category (segmentação)
-- 3. dispatch: payload enriquecido com faixa de-obfuscada para Meta CAPI
--
-- ============================================================================
-- TABELA: utm_mappings (configuração de segmentação)
-- ============================================================================
CREATE TABLE IF NOT EXISTS utm_mappings (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  obfuscated_hash TEXT NOT NULL UNIQUE,           -- ex: "8a3f1d2b" (hash truncado de 8 chars)
  original_value TEXT NOT NULL,                    -- ex: "700k-1M" (valor real)
  category TEXT NOT NULL,                         -- ex: "imovel", "automotivo", "curso"
  pixel_audience TEXT,                            -- ex: "AUDIENCE_MID" (Meta custom audience)
  platform_specific TEXT,                          -- JSON com IDs específicos por plataforma
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================================
-- ÍNDICES: utm_mappings
-- ============================================================================
CREATE INDEX IF NOT EXISTS idx_utm_obfuscated ON utm_mappings(obfuscated_hash);
CREATE INDEX IF NOT EXISTS idx_utm_category ON utm_mappings(category);

-- ============================================================================
-- ALTER: leads (adicionar colunas de segmentação)
-- ============================================================================
-- Nota: D1/SQLite não suporta ADD COLUMN IF NOT EXISTS. Executar uma vez.
-- Se as colunas já existirem, o erro "duplicate column name" é inofensivo — banco continua OK.
ALTER TABLE leads ADD COLUMN faixa_obfuscada TEXT;       -- Hash da faixa de valor (vem da URL)
ALTER TABLE leads ADD COLUMN faixa_real TEXT;            -- Valor real de-obfuscado (ex: "700k-1M")
ALTER TABLE leads ADD COLUMN faixa_category TEXT;        -- Categoria do produto (ex: "imovel")

-- ============================================================================
-- ÍNDICES: leads (novas colunas para segmentação)
-- ============================================================================
CREATE INDEX IF NOT EXISTS idx_leads_faixa_real ON leads(faixa_real);
CREATE INDEX IF NOT EXISTS idx_leads_faixa_category ON leads(faixa_category);
CREATE INDEX IF NOT EXISTS idx_leads_faixa_obfuscada ON leads(faixa_obfuscada);

-- ============================================================================
-- VIEW: leads_segmented (para queries de segmentação)
-- ============================================================================
CREATE VIEW IF NOT EXISTS leads_segmented AS
SELECT
  l.id,
  l.user_id,
  l.event,
  l.event_id,
  l.email,
  l.phone,
  l.city,
  l.state,
  l.faixa_obfuscada,
  l.faixa_real,
  l.faixa_category,
  l.created_at,
  l.value,
  l.intent_score,
  l.ltv_class,
  -- Meta CAPI: custom audience de-obfuscada
  u.pixel_audience AS meta_custom_audience
FROM leads l
LEFT JOIN utm_mappings u ON l.faixa_obfuscada = u.obfuscated_hash;

-- ============================================================================
-- EXEMPLO: Query para exportar leads por faixa de valor (para Meta Custom Audience)
-- ============================================================================
-- SELECT email, phone, city, state, faixa_real, meta_custom_audience
-- FROM leads_segmented
-- WHERE faixa_category = 'imovel' AND faixa_real = '700k-1M'
--   AND created_at >= datetime('now', '-30 days');
--
-- Isso gera um CSV pronto para upload como Custom Audience na Meta.
-- ============================================================================
