-- Crear CAGG daily corregido con cálculos matemáticamente correctos
CREATE MATERIALIZED VIEW cagg_scanning_report_daily
WITH (timescaledb.continuous) AS (
SELECT 
    -- Bucket diario
    time_bucket('1 day', "CreationTime") AS "CreationDate",
    
    -- Pre-calcular fecha local para consultas más rápidas
    "LocalDate",
    
    -- Agrupación
    "ClientMac",
    "NetworkId", 
    "NearestApMac",
    
    -- Datos del dispositivo (tomar el primer valor del día)
    FIRST("Manufacturer", "CreationTime") as "Manufacturer",
    FIRST("OS", "CreationTime") as "OS", 
    FIRST("SSID", "CreationTime") as "SSID",
    
    -- CORRECCIÓN: Métricas agregadas correctamente
    MIN("MinimumRssi") AS "MinimumRssi",
    MAX("MaximumRssi") AS "MaximumRssi",
    
    -- CRÍTICO: Promedio ponderado correcto en lugar de promedio de promedios
    SUM("RssiSum") / SUM("DetectionsCount") AS "AverageRssi",
    
    -- CRÍTICO: Duración total del día, no suma de duraciones parciales
    EXTRACT(EPOCH FROM (MAX("LastDetection") - MIN("FirstDetection")))/60 AS "DurationInMinutes",
    
    MIN("FirstDetection") AS "FirstDetection",
    MAX("LastDetection") AS "LastDetection",
    MIN("LocalFirstDetection") AS "LocalFirstDetection",
    MAX("LocalLastDetection") AS "LocalLastDetection",
    SUM("DetectionsCount") AS "DetectionsCount",
    
    -- CRÍTICO: Clasificación basada en métricas correctas
    CASE 
        WHEN SUM("RssiSum") / SUM("DetectionsCount") > -60 
             AND EXTRACT(EPOCH FROM (MAX("LastDetection") - MIN("FirstDetection")))/60 >= 5 THEN 'Visitor'
        WHEN SUM("RssiSum") / SUM("DetectionsCount") <= -60 THEN 'PasserBy'
        ELSE 'Unclassified'
    END AS "PersonType",
    
    CASE 
        WHEN EXTRACT(EPOCH FROM (MAX("LastDetection") - MIN("FirstDetection")))/60 < 1 THEN 'Less than 1 minute'
        WHEN EXTRACT(EPOCH FROM (MAX("LastDetection") - MIN("FirstDetection")))/60 BETWEEN 1 AND 5 THEN '1-5 minutes'
        WHEN EXTRACT(EPOCH FROM (MAX("LastDetection") - MIN("FirstDetection")))/60 BETWEEN 5 AND 15 THEN '5-15 minutes'
        WHEN EXTRACT(EPOCH FROM (MAX("LastDetection") - MIN("FirstDetection")))/60 BETWEEN 15 AND 30 THEN '15-30 minutes'
        WHEN EXTRACT(EPOCH FROM (MAX("LastDetection") - MIN("FirstDetection")))/60 BETWEEN 30 AND 60 THEN '30-60 minutes'
        WHEN EXTRACT(EPOCH FROM (MAX("LastDetection") - MIN("FirstDetection")))/60 BETWEEN 60 AND 120 THEN '1-2 hours'
        WHEN EXTRACT(EPOCH FROM (MAX("LastDetection") - MIN("FirstDetection")))/60 > 120 THEN 'More than 2 hours'
    END AS "PresenceCategory",
    
    COUNT(*) AS "HourlyPeriodsDetected"
FROM cagg_scanning_report_hourly
WHERE "CreationTime" < date_trunc('day', now())
GROUP BY 
    1,
    "LocalDate",
    "ClientMac",
    "NetworkId",
    "NearestApMac"
ORDER BY 1
);

-- Aplicar política de refresh
SELECT add_continuous_aggregate_policy('cagg_scanning_report_daily',
    start_offset => INTERVAL '3 day',
    end_offset   => INTERVAL '1 day',
    schedule_interval => INTERVAL '15 minutes'
);