-- E1: Índice de suspeita diário de fraude por compartilhamento de conta
-- Combina métricas de biometria (NOT_MATCH), cobertura biométrica e produtividade atípica
WITH pedidos_por_driver_dia AS (
  SELECT DATE(Order_Dt) AS dt,
    Driver_ID,
    COUNT(*) AS pedidos_no_dia
  FROM `ifood-prev-fraude-case.fraude_biometria.orders`
  WHERE Order_Dt IS NOT NULL
    AND Driver_ID IS NOT NULL
  GROUP BY dt,
    Driver_ID
),
-- Calcula o percentil 95 (P95) de pedidos por dia
p95_por_dia AS (
  SELECT dt,
    APPROX_QUANTILES(pedidos_no_dia, 100) [OFFSET(95)] AS p95_pedidos_no_dia
  FROM pedidos_por_driver_dia
  GROUP BY dt
),
-- Identificar % de entregadores com pedidos acima do P95 (outliers de produtividade)
drivers_acima_p95 AS (
  SELECT p.dt,
    COUNT(DISTINCT p.Driver_ID) AS drivers_ativos,
    SAFE_DIVIDE(
      COUNTIF(p.pedidos_no_dia > q.p95_pedidos_no_dia),
      COUNT(DISTINCT p.Driver_ID)
    ) AS pct_acima_p95
  FROM pedidos_por_driver_dia p
    JOIN p95_por_dia q USING (dt)
  GROUP BY p.dt
),
-- Métricas biométricas por driver/dia
sessoes_por_driver_dia AS (
  SELECT DATE(Event_Dt) AS dt,
    Driver_ID,
    COUNT(*) AS sessoes_bio_no_dia,
    COUNTIF(Final_Status = 'NOT_MATCH') AS not_match_bio_no_dia
  FROM `ifood-prev-fraude-case.fraude_biometria.vw_biometry_enriched`
  WHERE Event_Dt IS NOT NULL
    AND Driver_ID IS NOT NULL
  GROUP BY dt,
    Driver_ID
),
-- Agrega métricas diárias combinando operação e biometria
dia_agregado AS (
  SELECT p.dt,
    COUNT(DISTINCT p.Driver_ID) AS drivers_ativos_no_dia,
    COUNT(DISTINCT s.Driver_ID) AS drivers_biometria_no_dia,
    -- % de entregadores auditados via biometria
    SAFE_DIVIDE(
      COUNT(DISTINCT s.Driver_ID),
      COUNT(DISTINCT p.Driver_ID)
    ) AS taxa_cobertura_bio,
    -- taxa de NOT_MATCH diária
    SAFE_DIVIDE(
      SUM(s.not_match_bio_no_dia),
      NULLIF(SUM(s.sessoes_bio_no_dia), 0)
    ) AS taxa_not_match,
    -- % de entregadores com produtividade acima do P95 do dia
    da.pct_acima_p95
  FROM pedidos_por_driver_dia p
    LEFT JOIN sessoes_por_driver_dia s ON p.dt = s.dt
    AND p.Driver_ID = s.Driver_ID
    LEFT JOIN drivers_acima_p95 da ON p.dt = da.dt
  GROUP BY p.dt,
    da.pct_acima_p95
),
-- Estatísticas globais para z-scores
estat AS (
  SELECT AVG(taxa_cobertura_bio) AS media_bio,
    STDDEV_SAMP(taxa_cobertura_bio) AS sd_bio,
    AVG(taxa_not_match) AS media_not,
    STDDEV_SAMP(taxa_not_match) AS sd_not,
    AVG(pct_acima_p95) AS media_p95,
    STDDEV_SAMP(pct_acima_p95) AS sd_p95
  FROM dia_agregado
),
-- Calcula z-scores individuais e índice ponderado
final AS (
  SELECT d.dt,
    -- z-scores individuais
    (d.taxa_cobertura_bio - e.media_bio) / NULLIF(e.sd_bio, 0) AS z_bio,
    (d.taxa_not_match - e.media_not) / NULLIF(e.sd_not, 0) AS z_not,
    (d.pct_acima_p95 - e.media_p95) / NULLIF(e.sd_p95, 0) AS z_p95,
    -- índice ponderado bruto (antes da normalização)
    -- pesos: NOT_MATCH 0.5, biometria 0.3, produtividade outlier 0.2
    (
      0.5 * (
        (d.taxa_not_match - e.media_not) / NULLIF(e.sd_not, 0)
      ) + 0.3 * (
        (d.taxa_cobertura_bio - e.media_bio) / NULLIF(e.sd_bio, 0)
      ) + 0.2 * (
        (d.pct_acima_p95 - e.media_p95) / NULLIF(e.sd_p95, 0)
      )
    ) AS indice_bruto
  FROM dia_agregado d
    CROSS JOIN estat e
),
-- Normaliza o índice para 0–1
normalizado AS (
  SELECT dt,
    z_bio,
    z_not,
    z_p95,
    indice_bruto,
    -- 1) divide por 5 para reduzir escala (assume "5 desvios-padrão acima" como risco extremo)
    -- 2) desloca +0.5 para recentrar
    -- 3) trunca entre 0 e 1
    ROUND(
      LEAST(
        GREATEST(indice_bruto / 5 + 0.5, 0),
        1
      ),
      3
    ) AS indice_suspeita_normalizado,
    FROM final
)
SELECT dt,
  ROUND(z_not, 2) AS z_notmatch,
  ROUND(z_bio, 2) AS z_bio,
  ROUND(z_p95, 2) AS z_prod_outlier,
  ROUND(indice_bruto, 2) AS indice_bruto_ponderado,
  indice_suspeita_normalizado,
  FROM normalizado
ORDER BY dt;