-- CDP Edge — Schema D1
-- Projeto: server-edge-tracker | SEU_DOMINIO
-- Criação: wrangler d1 execute cdp-edge-db --file=schema.sql

-- ── Tabela de Leads ───────────────────────────────────────────────────────────
-- Armazena cada evento de conversão recebido (Lead, Purchase, Contact, etc.)
CREATE TABLE IF NOT EXISTS leads (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  created_at   TEXT    NOT NULL DEFAULT (datetime('now')),
  event_name   TEXT    NOT NULL,           -- Lead | Purchase | Contact | etc.
  event_id     TEXT,                       -- ID único (deduplicação browser↔servidor)
  email        TEXT,                       -- email bruto (não hasheado — fica no banco)
  phone        TEXT,                       -- telefone normalizado (55119...)
  first_name   TEXT,
  last_name    TEXT,
  city         TEXT,                       -- cidade bruta (normalização feita no envio)
  state        TEXT,
  country      TEXT,
  fbp          TEXT,                       -- cookie _fbp do Meta Pixel
  fbc          TEXT,                       -- cookie _fbc (fbclid)
  user_id      TEXT,                       -- ID first-party (_cdp_uid)
  utm_source   TEXT,
  utm_medium   TEXT,
  utm_campaign TEXT,
  utm_content  TEXT,
  utm_term     TEXT,                       -- palavra-chave da campanha (capturado no browser)
  page_url     TEXT,
  value        REAL,
  currency     TEXT    DEFAULT 'BRL',
  ip_address       TEXT,
  platform         TEXT    DEFAULT 'website',  -- website | hotmart | kiwify | whatsapp
  bot_score        INTEGER DEFAULT 0,          -- 0 = humano, 1 = suspeito, 2 = bot
  engagement_score REAL,                       -- Score de engajamento browser (0-5 ou 0-100)
  intention_level  TEXT,                       -- curioso | interessado | comprador | high_intent
  utm_restored     INTEGER DEFAULT 0,          -- 1 = UTM recuperada via Edge Fingerprint
  crm_status       TEXT    DEFAULT 'cold',     -- cold | engaging | closing | sold (CRM Dashboard)
  raw_payload      TEXT                        -- JSON original (para debug)
);

CREATE INDEX IF NOT EXISTS idx_leads_email    ON leads(email);
CREATE INDEX IF NOT EXISTS idx_leads_event    ON leads(event_name);
CREATE INDEX IF NOT EXISTS idx_leads_created  ON leads(created_at);
CREATE INDEX IF NOT EXISTS idx_leads_user_id  ON leads(user_id);
CREATE INDEX IF NOT EXISTS idx_leads_platform   ON leads(platform);
CREATE INDEX IF NOT EXISTS idx_leads_crm_status ON leads(crm_status);

-- ── Tabela de Perfis de Usuário ───────────────────────────────────────────────
-- UPSERT por user_id — acumula cookies e dados geo ao longo das visitas.
-- Objetivo: quando o webhook de compra chega (sem cookies), buscar fbp/fbc aqui.
CREATE TABLE IF NOT EXISTS user_profiles (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id      TEXT    NOT NULL UNIQUE,    -- ID first-party (_cdp_uid)
  email        TEXT,
  phone        TEXT,
  fbp          TEXT,                       -- _fbp mais recente
  fbc          TEXT,                       -- _fbc mais recente
  ttp          TEXT,                       -- _ttp TikTok
  gclid        TEXT,                       -- Google Click ID
  ttclid       TEXT,                       -- TikTok Click ID
  ga_client_id TEXT,                       -- GA4 client_id (para Measurement Protocol)
  msclkid      TEXT,                       -- Microsoft Ads Click ID (bing/microsoft)
  li_fat_id    TEXT,                       -- LinkedIn Ads Click ID
  wbraid       TEXT,                       -- Google Ads (iOS web-to-app privacy-preserving)
  gbraid       TEXT,                       -- Google Ads (app campaigns privacy-preserving)
  city         TEXT,
  state        TEXT,
  country      TEXT,
  score              INTEGER DEFAULT 0,    -- Lead Scoring acumulado (LTV preditivo)
  cohort_label       TEXT,                 -- high_intent | nurture | lost | buyer_lookalike
  predicted_ltv_class TEXT,               -- High | Medium | Low (Fase 4)
  predicted_ltv_value REAL,               -- Valor preditivo em BRL (Fase 4)
  created_at   TEXT    NOT NULL DEFAULT (datetime('now')),
  updated_at   TEXT    NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_profiles_user_id  ON user_profiles(user_id);
CREATE INDEX IF NOT EXISTS idx_profiles_email    ON user_profiles(email);
CREATE INDEX IF NOT EXISTS idx_profiles_fbp      ON user_profiles(fbp);
CREATE INDEX IF NOT EXISTS idx_profiles_msclkid  ON user_profiles(msclkid);
CREATE INDEX IF NOT EXISTS idx_profiles_li_fat   ON user_profiles(li_fat_id);

-- ── Tabela de Eventos (Deduplicação + Label ML) ──────────────────────────────
-- Registra cada evento processado com sucesso.
-- Duplo uso: (1) deduplicação server-side via event_id UNIQUE;
--            (2) label de LTV para treinamento ML (lead comprou depois?)
CREATE TABLE IF NOT EXISTS events (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  event_id     TEXT    UNIQUE,              -- deduplicação: INSERT OR IGNORE descarta duplicatas
  event_name   TEXT    NOT NULL,
  user_id      TEXT,
  created_at   TEXT    NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_events_user_id    ON events(user_id);
CREATE INDEX IF NOT EXISTS idx_events_event_name ON events(event_name);
CREATE INDEX IF NOT EXISTS idx_events_created    ON events(created_at);

-- ── Tabela de LOG de Webhooks Offline ─────────────────────────────────────────
CREATE TABLE IF NOT EXISTS webhook_events (
  id             INTEGER PRIMARY KEY AUTOINCREMENT,
  created_at     TEXT    NOT NULL DEFAULT (datetime('now')),
  platform       TEXT    NOT NULL,           -- hotmart | kiwify | ticto
  transaction_id TEXT,
  email          TEXT,
  status         TEXT,                       -- processed | failed
  raw_payload    TEXT
);
CREATE INDEX IF NOT EXISTS idx_webhook_transaction ON webhook_events(transaction_id);

-- ── Tabela de Edge Fingerprints (UTM Resurrection) ──────────────────────────
-- Armazena o hash de borda (ASN + Accept-Language + UA base) associado às UTMs
-- da última visita com parâmetros. Usado para recuperar atribuição perdida.
CREATE TABLE IF NOT EXISTS edge_fingerprints (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  created_at   TEXT    NOT NULL DEFAULT (datetime('now')),
  fingerprint  TEXT    NOT NULL,               -- SHA256(asn|lang|ua_base)
  user_id      TEXT,                           -- _cdp_uid se disponível
  utm_source   TEXT,
  utm_medium   TEXT,
  utm_campaign TEXT,
  utm_content  TEXT,
  utm_term     TEXT
);

CREATE INDEX IF NOT EXISTS idx_fingerprint_hash    ON edge_fingerprints(fingerprint);
CREATE INDEX IF NOT EXISTS idx_fingerprint_created ON edge_fingerprints(created_at);
CREATE INDEX IF NOT EXISTS idx_fingerprint_user_id ON edge_fingerprints(user_id);

-- ── Tabela de Falhas de API (Feedback Loop) ───────────────────────────────
-- Registra cada falha de API para monitoramento e análise
CREATE TABLE IF NOT EXISTS api_failures (
  id             INTEGER PRIMARY KEY AUTOINCREMENT,
  created_at     TEXT    NOT NULL DEFAULT (datetime('now')),
  platform       TEXT    NOT NULL,           -- meta | ga4 | tiktok | pinterest | reddit
  event_name     TEXT,                       -- Purchase | Lead | InitiateCheckout, etc.
  error_code     TEXT,                       -- 400, 401, 500, etc.
  error_message  TEXT,
  retry_count    INTEGER DEFAULT 0,
  final_status   TEXT,                       -- success | failed | abandoned
  event_id       TEXT,                       -- event_id original para correlação
  raw_payload    TEXT                        -- JSON payload original (debug)
);
CREATE INDEX IF NOT EXISTS idx_api_failures_platform   ON api_failures(platform);
CREATE INDEX IF NOT EXISTS idx_api_failures_created   ON api_failures(created_at);
CREATE INDEX IF NOT EXISTS idx_api_failures_event_id  ON api_failures(event_id);

-- ── Tabela de Relatórios de Saúde (Feedback Loop) ──────────────────────────
-- Relatórios diários de saúde das APIs
CREATE TABLE IF NOT EXISTS health_reports (
  id               INTEGER PRIMARY KEY AUTOINCREMENT,
  created_at       TEXT    NOT NULL DEFAULT (datetime('now')),
  report_date      TEXT    NOT NULL,           -- '2026-03-28'
  platform         TEXT    NOT NULL,           -- meta | ga4 | tiktok | pinterest | reddit
  events_sent      INTEGER DEFAULT 0,         -- Total de eventos enviados
  events_failed    INTEGER DEFAULT 0,         -- Total de eventos com falha
  success_rate     REAL,                      -- Porcentagem de sucesso (0-100)
  avg_latency_ms   INTEGER,                   -- Latência média em milissegundos
  errors_detected  TEXT,                      -- JSON array de códigos de erro mais comuns
  issues_detected  TEXT                       -- JSON array de problemas detectados
);
CREATE INDEX IF NOT EXISTS idx_health_reports_date     ON health_reports(report_date);
CREATE INDEX IF NOT EXISTS idx_health_reports_platform ON health_reports(platform);

-- ── Tabela de Device Graph (Cross-Device Matching) ──────────────────────────
-- Quando o mesmo email/phone aparece em dois _cdp_uid diferentes,
-- registra o par como "mesmo usuário, dispositivos distintos".
-- O primary_user_id é o mais antigo (created_at mais baixo no user_profiles).
CREATE TABLE IF NOT EXISTS device_graph (
  id                INTEGER PRIMARY KEY AUTOINCREMENT,
  created_at        TEXT    NOT NULL DEFAULT (datetime('now')),
  primary_user_id   TEXT    NOT NULL,   -- _cdp_uid mais antigo (âncora)
  secondary_user_id TEXT    NOT NULL,   -- _cdp_uid novo detectado
  match_type        TEXT    NOT NULL,   -- 'email' | 'phone' | 'email+phone'
  match_confidence  REAL    NOT NULL    -- 0.0–1.0 (email=0.95, phone=0.85, ambos=0.99)
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_device_graph_pair
  ON device_graph(primary_user_id, secondary_user_id);
CREATE INDEX IF NOT EXISTS idx_device_graph_primary   ON device_graph(primary_user_id);
CREATE INDEX IF NOT EXISTS idx_device_graph_secondary ON device_graph(secondary_user_id);

-- ── Tabela de Logs do Intelligence Agent ─────────────────────────────────────
-- Histórico de execuções de cron: checks de versão, auditorias, alertas
CREATE TABLE IF NOT EXISTS intelligence_logs (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  created_at      TEXT    NOT NULL DEFAULT (datetime('now')),
  run_type        TEXT    NOT NULL,   -- 'weekly_check' | 'monthly_audit' | 'on_demand'
  platform        TEXT,               -- meta | ga4 | tiktok | all
  check_type      TEXT    NOT NULL,   -- 'api_version' | 'error_rate' | 'privacy_audit'
  status          TEXT    NOT NULL,   -- 'ok' | 'warning' | 'critical'
  current_value   TEXT,               -- versão atual ou taxa de erro detectada
  expected_value  TEXT,               -- versão esperada ou threshold
  message         TEXT,               -- descrição do resultado
  alert_sent      INTEGER DEFAULT 0   -- 1 = alerta WhatsApp enviado
);

CREATE INDEX IF NOT EXISTS idx_intel_logs_created   ON intelligence_logs(created_at);
CREATE INDEX IF NOT EXISTS idx_intel_logs_status    ON intelligence_logs(status);
CREATE INDEX IF NOT EXISTS idx_intel_logs_platform  ON intelligence_logs(platform);

-- ── Meta Ads Dashboard — Tabelas (conectadas ao mesmo D1) ────────────────────
-- Alimentadas pelo sync diário da Meta Marketing API v25.0

CREATE TABLE IF NOT EXISTS meta_account (
  account_id TEXT PRIMARY KEY,
  name       TEXT NOT NULL,
  currency   TEXT DEFAULT 'BRL',
  timezone   TEXT DEFAULT 'America/Sao_Paulo',
  updated_at TEXT DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS meta_ad_metrics (
  ad_id                  TEXT NOT NULL,
  ad_name                TEXT,
  adset_id               TEXT,
  adset_name             TEXT,
  campaign_id            TEXT NOT NULL,
  campaign_name          TEXT,
  account_id             TEXT,
  spend                  REAL    NOT NULL DEFAULT 0,
  impressions            INTEGER DEFAULT 0,
  cpm                    REAL    DEFAULT 0,
  clicks                 INTEGER DEFAULT 0,
  ctr                    REAL    DEFAULT 0,
  reach                  INTEGER DEFAULT 0,
  frequency              REAL    DEFAULT 0,
  actions_results        INTEGER DEFAULT 0,
  cost_per_result        REAL    DEFAULT 0,
  inline_link_clicks     INTEGER DEFAULT 0,
  inline_link_click_ctr  REAL    DEFAULT 0,
  date_ref               TEXT NOT NULL,
  synced_at              TEXT DEFAULT (datetime('now')),
  PRIMARY KEY (ad_id, date_ref)
);

CREATE INDEX IF NOT EXISTS idx_meta_metrics_date          ON meta_ad_metrics(date_ref);
CREATE INDEX IF NOT EXISTS idx_meta_metrics_campaign      ON meta_ad_metrics(campaign_id);
CREATE INDEX IF NOT EXISTS idx_meta_metrics_campaign_date ON meta_ad_metrics(campaign_id, date_ref);

CREATE TABLE IF NOT EXISTS meta_financeiro (
  account_id   TEXT NOT NULL,
  month_start  TEXT NOT NULL,
  month_end    TEXT NOT NULL,
  spend        REAL NOT NULL DEFAULT 0,
  tax          REAL NOT NULL DEFAULT 0,
  total        REAL NOT NULL DEFAULT 0,
  updated_at   TEXT DEFAULT (datetime('now')),
  PRIMARY KEY (account_id, month_start)
);

-- ── Mensageria — WhatsApp (Meta Cloud API) + Email (Resend) ──────────────────
CREATE TABLE IF NOT EXISTS messaging_history (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  sent_at    TEXT    NOT NULL DEFAULT (datetime('now')),
  lead_id    INTEGER NOT NULL,
  channel    TEXT    NOT NULL,  -- 'whatsapp' | 'email'
  recipient  TEXT    NOT NULL,  -- telefone E.164 ou endereço de email
  subject    TEXT,              -- assunto (só email)
  content    TEXT    NOT NULL,  -- corpo da mensagem
  status     TEXT    NOT NULL,  -- 'sent' | 'failed'
  meta       TEXT               -- message_id da API ou erro em JSON
);

CREATE INDEX IF NOT EXISTS idx_msg_history_lead    ON messaging_history(lead_id);
CREATE INDEX IF NOT EXISTS idx_msg_history_sent_at ON messaging_history(sent_at);
CREATE INDEX IF NOT EXISTS idx_msg_history_channel ON messaging_history(channel);

-- ── Automação de Mensagens ────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS automation_rules (
  id               INTEGER PRIMARY KEY AUTOINCREMENT,
  created_at       TEXT    NOT NULL DEFAULT (datetime('now')),
  name             TEXT    NOT NULL,
  trigger_event    TEXT    NOT NULL,           -- Lead | Purchase | InitiateCheckout | HighIntent
  channel          TEXT    NOT NULL,           -- whatsapp | email
  subject_template TEXT,
  message_template TEXT    NOT NULL,           -- suporta {{name}} {{email}} {{phone}} {{campaign}} {{intention}}
  delay_minutes    INTEGER NOT NULL DEFAULT 0,
  is_active        INTEGER NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_auto_rules_event  ON automation_rules(trigger_event);
CREATE INDEX IF NOT EXISTS idx_auto_rules_active ON automation_rules(is_active);
