-- Schema de Recomendações de Bids Automáticas — CDP Edge Quantum Tier
-- Versão: 1.0
-- Data: 9 de Abril de 2026
-- Complementa: schema-segmentation.sql

-- TABELA PRINCIPAL: Recomendações de Bids
CREATE TABLE IF NOT EXISTS bid_recommendations (
  id               INTEGER PRIMARY KEY AUTOINCREMENT,
  generated_at     TEXT NOT NULL DEFAULT (datetime('now')),
  vertical         TEXT NOT NULL,                          -- 'infoproduto', 'ecommerce', 'saas'
  platform         TEXT NOT NULL,                          -- 'meta', 'google', 'tiktok', 'all'
  period_days      INTEGER NOT NULL DEFAULT 30,            -- Janela de análise
  target_roi       REAL NOT NULL DEFAULT 3.5,              -- ROI alvo

  -- Segmento ML de origem (FK para ml_segments)
  segment_id       INTEGER,
  segment_name     TEXT,

  -- Dados analisados
  leads_analyzed   INTEGER NOT NULL DEFAULT 0,
  conversions_found INTEGER NOT NULL DEFAULT 0,
  avg_ltv          REAL,                                   -- LTV médio do segmento
  cpa_target       REAL,                                   -- CPA alvo calculado: avg_ltv / target_roi

  -- Output: O Bid em si
  recommended_bid  REAL,                                   -- Bid recomendado em BRL
  bid_currency     TEXT NOT NULL DEFAULT 'BRL',
  confidence       REAL NOT NULL DEFAULT 0,                -- 0-1 (base: conversions / 100)
  expected_roi     REAL,                                   -- ROI esperado com este bid

  -- Rastreabilidade
  reasoning        TEXT,                                   -- Explicação detalhada da lógica
  ai_used          INTEGER DEFAULT 0,                      -- 1 = Workers AI usado
  alert_message    TEXT,                                   -- Ex: "Dados insuficientes (< 30 conversões)"

  -- Fatores internos de cálculo (para auditoria)
  platform_factor  REAL,                                   -- 0.75–0.90 por plataforma
  confidence_adjustment REAL,                              -- 0.70–1.00 por nível de confiança
  segment_multiplier REAL,                                 -- 0.60–1.40 por tipo de segmento

  -- Ciclo de vida
  is_active        INTEGER NOT NULL DEFAULT 1,
  applied_at       TEXT,                                   -- Quando o usuário aplicou o bid
  applied_campaign TEXT,                                   -- ID da campanha onde foi aplicado
  applied_result   TEXT                                    -- JSON: {clicks, conversions, real_roi}
);

-- Índices
CREATE INDEX IF NOT EXISTS idx_bid_recs_vertical   ON bid_recommendations(vertical);
CREATE INDEX IF NOT EXISTS idx_bid_recs_platform   ON bid_recommendations(platform);
CREATE INDEX IF NOT EXISTS idx_bid_recs_generated  ON bid_recommendations(generated_at);
CREATE INDEX IF NOT EXISTS idx_bid_recs_active     ON bid_recommendations(is_active);
CREATE INDEX IF NOT EXISTS idx_bid_recs_segment    ON bid_recommendations(segment_id);
CREATE INDEX IF NOT EXISTS idx_bid_recs_confidence ON bid_recommendations(confidence);

-- VIEW: Recomendações Ativas (com dados dos segmentos)
CREATE VIEW IF NOT EXISTS v_active_bid_recommendations AS
SELECT
  br.id,
  br.generated_at,
  br.vertical,
  br.platform,
  br.period_days,
  br.target_roi,
  br.segment_name,
  br.leads_analyzed,
  br.conversions_found,
  br.avg_ltv,
  br.cpa_target,
  br.recommended_bid,
  br.bid_currency,
  br.confidence,
  br.expected_roi,
  br.reasoning,
  br.alert_message,
  br.ai_used,
  -- Dados do segmento ML relacionado
  ms.cluster_name    AS ml_cluster_name,
  ms.avg_behavior_score,
  ms.avg_engagement_score,
  ms.silhouette_score,
  ms.size            AS segment_size
FROM bid_recommendations br
LEFT JOIN ml_segments ms ON br.segment_id = ms.id
WHERE br.is_active = 1
ORDER BY br.generated_at DESC;
