-- CDP Edge — Schema Migration
-- Migrado de pixel-tracker-db → cdp-edge-db

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 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,
  secondary_user_id TEXT    NOT NULL,
  match_type        TEXT    NOT NULL,
  match_confidence  REAL    NOT NULL
);

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 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 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 TABLE IF NOT EXISTS leads (
  id               INTEGER PRIMARY KEY AUTOINCREMENT,
  created_at       TEXT    NOT NULL DEFAULT (datetime('now')),
  event_name       TEXT    NOT NULL,
  event_id         TEXT,
  email            TEXT,
  phone            TEXT,
  first_name       TEXT,
  last_name        TEXT,
  city             TEXT,
  state            TEXT,
  country          TEXT,
  fbp              TEXT,
  fbc              TEXT,
  user_id          TEXT,
  utm_source       TEXT,
  utm_medium       TEXT,
  utm_campaign     TEXT,
  utm_content      TEXT,
  utm_term         TEXT,
  page_url         TEXT,
  value            REAL,
  currency         TEXT    DEFAULT 'BRL',
  ip_address       TEXT,
  client_ip        TEXT    DEFAULT '',
  platform         TEXT    DEFAULT 'website',
  zip              TEXT    DEFAULT '',
  ga_client_id     TEXT    DEFAULT '',
  status           TEXT    DEFAULT 'pending',
  retry_count      INTEGER DEFAULT 0,
  updated_at       TEXT,
  bot_score        INTEGER DEFAULT 0,
  engagement_score REAL,
  intention_level  TEXT,
  utm_restored     INTEGER DEFAULT 0,
  raw_payload      TEXT
);

CREATE TABLE IF NOT EXISTS user_profiles (
  id                  INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id             TEXT    NOT NULL UNIQUE,
  email               TEXT,
  phone               TEXT,
  fbp                 TEXT,
  fbc                 TEXT,
  ttp                 TEXT,
  gclid               TEXT,
  ttclid              TEXT,
  ga_client_id        TEXT,
  city                TEXT,
  state               TEXT,
  country             TEXT,
  score               INTEGER DEFAULT 0,
  last_seen           TEXT,
  msclkid             TEXT,
  li_fat_id           TEXT,
  wbraid              TEXT,
  gbraid              TEXT,
  cohort_label        TEXT,
  predicted_ltv_class TEXT,
  predicted_ltv_value REAL,
  created_at          TEXT    NOT NULL DEFAULT (datetime('now')),
  updated_at          TEXT    NOT NULL DEFAULT (datetime('now'))
);

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
);
