CREATE OR REPLACE VIEW `ifood-prev-fraude-case.fraude_biometria.vw_biometry_base` AS
SELECT b.Session_ID,
    b.Driver_ID,
    b.Status AS Final_Status,
    b.Action,
    b.Event_Dt,
    -- Status por provedor
    MAX(IF(e.Provider = 'A', e.Status, NULL)) AS Status_A,
    MAX(IF(e.Provider = 'B', e.Status, NULL)) AS Status_B,
    MAX(IF(e.Provider = 'C', e.Status, NULL)) AS Status_C,
    -- Similaridade por provedor
    MAX(IF(e.Provider = 'A', e.Similarity, NULL)) AS Sim_A,
    MAX(IF(e.Provider = 'B', e.Similarity, NULL)) AS Sim_B,
    MAX(IF(e.Provider = 'C', e.Similarity, NULL)) AS Sim_C,
    -- Maior similaridade na sessão (para simular threshold)
    COALESCE(
        SAFE_CAST(MAX(e.Similarity) AS FLOAT64),
        SAFE_CAST(REPLACE(MAX(e.Similarity), ',', '.') AS FLOAT64)
    ) AS Max_Service_Similarity
FROM `ifood-prev-fraude-case.fraude_biometria.biometry_typed` b
    LEFT JOIN `ifood-prev-fraude-case.fraude_biometria.biometry_execution_typed` e ON b.Session_ID = e.Session_ID
GROUP BY b.Session_ID,
    b.Driver_ID,
    b.Status,
    b.Action,
    b.Event_Dt;