-- CDP Edge — Migration File
-- Aplica apenas as colunas e tabelas faltantes no banco existente
-- Gerado em: 2026-03-28

-- ── ALTER TABLE leads (colunas faltantes) ─────────────────────────────────────
ALTER TABLE leads ADD COLUMN utm_term      TEXT;
ALTER TABLE leads ADD COLUMN bot_score     INTEGER DEFAULT 0;
ALTER TABLE leads ADD COLUMN engagement_score REAL;
ALTER TABLE leads ADD COLUMN intention_level  TEXT;
ALTER TABLE leads ADD COLUMN utm_restored     INTEGER DEFAULT 0;

-- ── Índices novos em leads ────────────────────────────────────────────────────
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);

-- ── ALTER TABLE user_profiles (colunas faltantes) ─────────────────────────────
ALTER TABLE user_profiles ADD COLUMN msclkid             TEXT;
ALTER TABLE user_profiles ADD COLUMN li_fat_id           TEXT;
ALTER TABLE user_profiles ADD COLUMN wbraid              TEXT;
ALTER TABLE user_profiles ADD COLUMN gbraid              TEXT;
ALTER TABLE user_profiles ADD COLUMN cohort_label        TEXT;
ALTER TABLE user_profiles ADD COLUMN predicted_ltv_class TEXT;
ALTER TABLE user_profiles ADD COLUMN predicted_ltv_value REAL;

-- ── Índices novos em user_profiles ───────────────────────────────────────────
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 webhook_events (nova) ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS webhook_events (
  id             INTEGER PRIMARY KEY AUTOINCREMENT,
  created_at     TEXT    NOT NULL DEFAULT (datetime('now')),
  platform       TEXT    NOT NULL,
  transaction_id TEXT,
  email          TEXT,
  status         TEXT,
  raw_payload    TEXT
);
CREATE INDEX IF NOT EXISTS idx_webhook_transaction ON webhook_events(transaction_id);

-- ── Tabela api_failures (nova) ────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS api_failures (
  id             INTEGER PRIMARY KEY AUTOINCREMENT,
  created_at     TEXT    NOT NULL DEFAULT (datetime('now')),
  platform       TEXT    NOT NULL,
  event_name     TEXT,
  error_code     TEXT,
  error_message  TEXT,
  retry_count    INTEGER DEFAULT 0,
  final_status   TEXT,
  event_id       TEXT,
  raw_payload    TEXT
);
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 health_reports (nova) ──────────────────────────────────────────────
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,
  platform         TEXT    NOT NULL,
  events_sent      INTEGER DEFAULT 0,
  events_failed    INTEGER DEFAULT 0,
  success_rate     REAL,
  avg_latency_ms   INTEGER,
  errors_detected  TEXT,
  issues_detected  TEXT
);
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 edge_fingerprints (nova) ───────────────────────────────────────────
CREATE TABLE IF NOT EXISTS edge_fingerprints (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  created_at   TEXT    NOT NULL DEFAULT (datetime('now')),
  fingerprint  TEXT    NOT NULL,
  user_id      TEXT,
  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 intelligence_logs (nova) ───────────────────────────────────────────
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,
  platform        TEXT,
  check_type      TEXT    NOT NULL,
  status          TEXT    NOT NULL,
  current_value   TEXT,
  expected_value  TEXT,
  message         TEXT,
  alert_sent      INTEGER DEFAULT 0
);
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);
