-- Schema Fraud Detection — CDP Edge Quantum Tier
-- Versão: 1.0
-- Data: 9 de Abril de 2026
-- Fase 4 Enterprise-Level

-- TABELA: Sinais de Fraude por Evento
CREATE TABLE IF NOT EXISTS fraud_signals (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  detected_at     TEXT NOT NULL DEFAULT (datetime('now')),

  -- Identificadores do evento
  ip_address      TEXT,
  fingerprint     TEXT,
  user_id         TEXT,
  email_hash      TEXT,             -- SHA256, sem PII
  event_name      TEXT,
  event_id        TEXT,

  -- Score e decisão
  fraud_score     INTEGER NOT NULL, -- 0-100
  action_taken    TEXT NOT NULL,    -- 'allowed', 'flagged', 'dropped'
  reasons         TEXT NOT NULL,    -- JSON array: ["ip_velocity_high", "datacenter_ip", ...]

  -- Contexto
  ip_country      TEXT,
  ip_asn          TEXT,
  user_agent      TEXT,
  bot_score       INTEGER,
  velocity_1h     INTEGER,          -- Eventos deste IP na última 1h
  velocity_1m     INTEGER,          -- Eventos deste IP no último 1min

  -- Resultado (preenchido depois se lead converter)
  was_real_user   INTEGER           -- 1 = comprou depois (falso positivo), 0 = fraude confirmada
);

-- TABELA: Alertas Agregados (quando IP/fingerprint atinge threshold repetidamente)
CREATE TABLE IF NOT EXISTS fraud_alerts (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  created_at      TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at      TEXT NOT NULL DEFAULT (datetime('now')),

  alert_type      TEXT NOT NULL,    -- 'ip_attack', 'fingerprint_abuse', 'bot_network'
  entity_type     TEXT NOT NULL,    -- 'ip', 'fingerprint', 'asn'
  entity_value    TEXT NOT NULL,    -- O IP, fingerprint ou ASN em questão

  -- Métricas do ataque
  events_total    INTEGER NOT NULL DEFAULT 0,
  events_dropped  INTEGER NOT NULL DEFAULT 0,
  peak_score      INTEGER NOT NULL DEFAULT 0,
  first_seen      TEXT NOT NULL DEFAULT (datetime('now')),
  last_seen       TEXT NOT NULL DEFAULT (datetime('now')),

  -- Status
  is_blocked      INTEGER NOT NULL DEFAULT 0,  -- 1 = está no KV blocklist
  blocked_at      TEXT,
  block_expires   TEXT,                          -- NULL = permanente
  resolved_at     TEXT,
  resolved_by     TEXT,                          -- 'auto', 'manual'

  -- Contexto
  top_reasons     TEXT,             -- JSON: razões mais comuns
  sample_ips      TEXT              -- JSON: amostra de IPs relacionados (para redes de bots)
);

-- Índices
CREATE INDEX IF NOT EXISTS idx_fraud_signals_ip      ON fraud_signals(ip_address);
CREATE INDEX IF NOT EXISTS idx_fraud_signals_fp      ON fraud_signals(fingerprint);
CREATE INDEX IF NOT EXISTS idx_fraud_signals_score   ON fraud_signals(fraud_score);
CREATE INDEX IF NOT EXISTS idx_fraud_signals_action  ON fraud_signals(action_taken);
CREATE INDEX IF NOT EXISTS idx_fraud_signals_date    ON fraud_signals(detected_at);
CREATE INDEX IF NOT EXISTS idx_fraud_alerts_entity   ON fraud_alerts(entity_type, entity_value);
CREATE INDEX IF NOT EXISTS idx_fraud_alerts_blocked  ON fraud_alerts(is_blocked);
CREATE INDEX IF NOT EXISTS idx_fraud_alerts_date     ON fraud_alerts(created_at);

-- VIEW: Dashboard de fraude (últimas 24h)
CREATE VIEW IF NOT EXISTS v_fraud_dashboard AS
SELECT
  COUNT(*) AS total_events_checked,
  SUM(CASE WHEN action_taken = 'dropped' THEN 1 ELSE 0 END) AS events_dropped,
  SUM(CASE WHEN action_taken = 'flagged' THEN 1 ELSE 0 END) AS events_flagged,
  SUM(CASE WHEN action_taken = 'allowed' THEN 1 ELSE 0 END) AS events_allowed,
  ROUND(
    CAST(SUM(CASE WHEN action_taken = 'dropped' THEN 1 ELSE 0 END) AS REAL) /
    NULLIF(COUNT(*), 0) * 100
  , 2) AS fraud_rate_pct,
  AVG(fraud_score) AS avg_fraud_score,
  MAX(fraud_score) AS peak_fraud_score,
  COUNT(DISTINCT ip_address) AS unique_ips_flagged
FROM fraud_signals
WHERE detected_at >= datetime('now', '-24 hours');
