-- Schema de Segmentação Dinâmica ML — CDP Edge Quantum Tier
-- Versão: 1.0
-- Data: 9 de Abril de 2026

-- TABELA PRINCIPAL: Segmentos ML
CREATE TABLE IF NOT EXISTS ml_segments (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  cluster_id INTEGER NOT NULL,
  cluster_name TEXT NOT NULL,                      -- Nome descritivo gerado por ML
  clustering_algorithm TEXT NOT NULL,                 -- 'kmeans', 'dbscan', 'hierarchical'
  client_vertical TEXT,                                -- 'curso-online', 'ecommerce', 'saaS'
  created_at TEXT DEFAULT (datetime('now')),
  updated_at TEXT DEFAULT (datetime('now')),

  -- Estatísticas do cluster
  size INTEGER NOT NULL,                                 -- Número de leads no cluster
  percentage REAL NOT NULL,                              -- % do total (ex: 0.25 = 25%)

  -- Características médias (centróides)
  avg_ltv REAL,                                        -- LTV médio do cluster
  avg_ltv_class REAL,                                   -- 0=Low, 1=Medium, 2=High (média)
  avg_behavior_score REAL,                               -- Engajamento médio (0-100)
  avg_engagement_score REAL,                             -- Interações médias (0-100)
  avg_intention_level REAL,                               -- Intenção de compra média (0-100)
  avg_days_since_lead REAL,                              -- Recência média em dias
  avg_hour_of_day REAL,                                  -- Hora média do dia (0-23)
  avg_is_weekend REAL,                                   -- % que acessa no fim de semana (0-1)
  avg_is_business_hours REAL,                              -- % em horário comercial (0-1)
  avg_bot_score REAL,                                    -- % humano médio (0-100, 100=100% humano)

  -- Características dominantes (top features por cluster)
  dominant_countries TEXT,                                -- JSON array: ["BR", "US", "AR"]
  dominant_states TEXT,                                   -- JSON array: ["SP", "RJ", "MG"]
  dominant_cities TEXT,                                   -- JSON array: ["São Paulo", "Rio de Janeiro"]
  dominant_timezones TEXT,                                 -- JSON array: ["America/Sao_Paulo", "America/New_York"]
  dominant_utm_sources TEXT,                              -- JSON array: ["facebook", "google", "tiktok"]
  dominant_utm_mediums TEXT,                             -- JSON array: ["cpc", "organic", "social"]
  dominant_features TEXT,                                   -- JSON array: ["ltv", "behavior_score", "engagement_score"]

  -- Métricas de qualidade do clustering
  silhouette_score REAL,                                   -- Coesão vs separação (-1 a 1, > 0.5 = bom)
  cohesion REAL,                                        -- Similaridade média intra-cluster
  separation REAL,                                       -- Distância média inter-cluster
  inertia REAL,                                          -- Soma dos quadrados das distâncias (menor = melhor)

  -- Recomendações automáticas geradas por ML
  action_recommendations TEXT,                          -- JSON array: ["Priorizar remarketing", "Aumentar bid", ...]
  bid_recommendations TEXT,                              -- JSON array: [{"adset_id": "123", "recommended_bid": "R$ 18.50"}, ...]
  campaign_recommendations TEXT,                           -- JSON array: [{"creative": "VSL A", "audience": "Segment 0"}, ...]

  -- Metadados de controle
  is_active INTEGER DEFAULT 1,                            -- 0 = arquivado, 1 = ativo
  min_data_points INTEGER,                                -- Mínimo de leads para formar cluster
  epsilon REAL,                                         -- Para DBSCAN (distância máxima)
  min_samples INTEGER,                                     -- Para DBSCAN (pontos mínimos para cluster)
  max_depth INTEGER                                       -- Para Hierarchical (profundidade máxima)
);

-- Índices para performance
CREATE INDEX IF NOT EXISTS idx_ml_segments_id ON ml_segments(id);
CREATE INDEX IF NOT EXISTS idx_ml_segments_cluster ON ml_segments(cluster_id);
CREATE INDEX IF NOT EXISTS idx_ml_segments_algorithm ON ml_segments(clustering_algorithm);
CREATE INDEX IF NOT EXISTS idx_ml_segments_created ON ml_segments(created_at);
CREATE INDEX IF NOT EXISTS idx_ml_segments_active ON ml_segments(is_active);
CREATE INDEX IF NOT EXISTS idx_ml_segments_vertical ON ml_segments(client_vertical);

-- TABELA DE ASSOCIAÇÃO: Segment Members (Quem está em cada segmento)
CREATE TABLE IF NOT EXISTS ml_segment_members (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  lead_id TEXT NOT NULL,                                  -- ID da tabela leads
  cluster_id INTEGER NOT NULL,                              -- ID do cluster (ml_segments.id)
  clustering_algorithm TEXT NOT NULL,                 -- Mesmo algoritmo usado para criar o cluster
  confidence REAL NOT NULL,                               -- 0-1 (quão perto do centroide)
  distance_to_centroid REAL,                              -- Distância Euclidiana ao centroide
  updated_at TEXT DEFAULT (datetime('now')),

  -- Metadados para rastreamento
  assigned_at TEXT DEFAULT (datetime('now')),              -- Quando foi atribuído ao segmento
  is_outlier INTEGER DEFAULT 0,                              -- 0 = normal, 1 = outlier (DBSCAN)
  outlier_reason TEXT,                                     -- "behavior_score too high", "unusual geo", etc.

  -- Características do lead no momento da atribuição
  lead_ltv REAL,                                         -- LTV do lead no momento
  lead_ltv_class REAL,                                   -- Classe de LTV do lead
  lead_behavior_score REAL,                               -- Behavior score no momento
  lead_engagement_score REAL,                             -- Engagement score no momento
  lead_intention_level REAL,                               -- Intention level no momento
  lead_days_since_lead REAL,                              -- Dias desde lead no momento
  lead_hour_of_day REAL,                                  -- Hora do dia no momento
  lead_is_weekend INTEGER,                                -- Se era fim de semana (0/1)
  lead_is_business_hours INTEGER,                          -- Se era horário comercial (0/1)
  lead_country TEXT,                                      -- País do lead
  lead_state TEXT,                                       -- Estado do lead
  lead_city TEXT,                                        -- Cidade do lead
  lead_utm_source TEXT,                                  -- UTM source do lead
  lead_utm_medium TEXT,                                   -- UTM medium do lead

  -- Chave composta para evitar duplicatas
  UNIQUE(lead_id, cluster_id, clustering_algorithm)
);

-- Índices para performance
CREATE INDEX IF NOT EXISTS idx_ml_segment_members_lead ON ml_segment_members(lead_id);
CREATE INDEX IF NOT EXISTS idx_ml_segment_members_cluster ON ml_segment_members(cluster_id);
CREATE INDEX IF NOT EXISTS idx_ml_segment_members_algorithm ON ml_segment_members(clustering_algorithm);
CREATE INDEX IF NOT EXISTS idx_ml_segment_members_confidence ON ml_segment_members(confidence);
CREATE INDEX IF NOT EXISTS idx_ml_segment_members_outlier ON ml_segment_members(is_outlier);
CREATE INDEX IF NOT EXISTS idx_ml_segment_members_assigned ON ml_segment_members(assigned_at);

-- TABELA DE HISTÓRICO DE CLUSTERING (Auditoria)
CREATE TABLE IF NOT EXISTS ml_clustering_history (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  clustering_id INTEGER NOT NULL,                          -- ID em ml_segments
  started_at TEXT NOT NULL,                               -- Quando iniciou o clustering
  completed_at TEXT,                                     -- Quando terminou
  algorithm TEXT NOT NULL,                                 -- 'kmeans', 'dbscan', 'hierarchical'
  n_leads_processed INTEGER NOT NULL,                        -- Quantos leads foram processados
  n_clusters_created INTEGER NOT NULL,                        -- Quantos clusters foram criados
  total_duration_ms INTEGER NOT NULL,                      -- Duração total em milissegundos
  workers_ai_neurons_used INTEGER,                         -- Neurônios usados (para billing)
  status TEXT NOT NULL,                                    -- 'completed', 'failed', 'timeout'
  error_message TEXT,                                       -- Se falhou, motivo do erro
  parameters TEXT NOT NULL,                                 -- Parâmetros usados (JSON)
  results_summary TEXT,                                    -- Resumo dos resultados (JSON)
  created_at TEXT DEFAULT (datetime('now'))
);

-- Índices para auditoria
CREATE INDEX IF NOT EXISTS idx_ml_clustering_history_clustering ON ml_clustering_history(clustering_id);
CREATE INDEX IF NOT EXISTS idx_ml_clustering_history_started ON ml_clustering_history(started_at);
CREATE INDEX IF NOT EXISTS idx_ml_clustering_history_algorithm ON ml_clustering_history(algorithm);
CREATE INDEX IF NOT EXISTS idx_ml_clustering_history_status ON ml_clustering_history(status);

-- TABELA DE FEATURE IMPORTANCE (Otimização Futura)
CREATE TABLE IF NOT EXISTS ml_feature_importance (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  feature_name TEXT NOT NULL,                             -- 'ltv', 'behavior_score', etc.
  importance_score REAL NOT NULL,                           -- 0-1 (quão importante para clustering)
  clustering_algorithm TEXT NOT NULL,                 -- Algoritmo usado para calcular
  variance_explained REAL,                                 -- % da variância explicada
  created_at TEXT DEFAULT (datetime('now')),
  updated_at TEXT DEFAULT (datetime('now'))
);

-- Índices
CREATE INDEX IF NOT EXISTS idx_ml_feature_importance_feature ON ml_feature_importance(feature_name);
CREATE INDEX IF NOT EXISTS idx_ml_feature_importance_algorithm ON ml_feature_importance(clustering_algorithm);

-- VIEW: Segmentos Ativos com Estatísticas
CREATE VIEW IF NOT EXISTS v_active_segments AS
SELECT
  ms.id,
  ms.cluster_id,
  ms.cluster_name,
  ms.clustering_algorithm,
  ms.client_vertical,
  ms.size,
  ms.percentage,
  ms.avg_ltv,
  ms.avg_ltv_class,
  ms.avg_behavior_score,
  ms.avg_engagement_score,
  ms.avg_intention_level,
  ms.avg_days_since_lead,
  ms.dominant_countries,
  ms.dominant_states,
  ms.dominant_cities,
  ms.dominant_utm_sources,
  ms.dominant_features,
  ms.silhouette_score,
  ms.cohesion,
  ms.separation,
  ms.inertia,
  ms.action_recommendations,
  ms.bid_recommendations,
  ms.campaign_recommendations,
  COUNT(msm.id) as member_count
FROM ml_segments ms
LEFT JOIN ml_segment_members msm ON msm.cluster_id = ms.id
WHERE ms.is_active = 1
GROUP BY ms.id
ORDER BY ms.created_at DESC;


-- VIEW: Membros de Segmentos (enriquecidos com dados de leads)
CREATE VIEW IF NOT EXISTS v_segment_members_enriched AS
SELECT
  msm.id,
  msm.lead_id,
  msm.cluster_id,
  msm.clustering_algorithm,
  msm.confidence,
  msm.distance_to_centroid,
  msm.is_outlier,
  msm.outlier_reason,
  msm.assigned_at,
  msm.lead_ltv,
  msm.lead_ltv_class,
  msm.lead_behavior_score,
  msm.lead_engagement_score,
  msm.lead_intention_level,
  msm.lead_days_since_lead,
  msm.lead_hour_of_day,
  msm.lead_is_weekend,
  msm.lead_is_business_hours,
  l.email,
  l.first_name,
  l.last_name,
  l.city,
  l.state,
  l.country,
  l.utm_source,
  l.utm_medium,
  l.utm_campaign,
  l.created_at as lead_created_at,
  ms.cluster_name
FROM ml_segment_members msm
INNER JOIN leads l ON msm.lead_id = l.id
ORDER BY msm.confidence DESC, msm.assigned_at DESC;
