-- =============================================================================
-- Vista: splash_wifi_scanning_metrics_by_network
-- Descripción: Pre-calcula métricas de visitantes agregadas por NetworkId
-- Autor: Sistema
-- Fecha: 2025-10-24
-- Optimización: Eliminados JOINs innecesarios (datos ya en scanning_report_daily_full)
-- =============================================================================

-- Vista Simple: Agregación por Network (sin filtro de fechas)
-- OPTIMIZADA: Sin JOINs, usa solo scanning_report_daily_full que ya tiene NetworkName y OrganizationName
CREATE OR REPLACE VIEW public.splash_wifi_scanning_metrics_by_network AS
SELECT
    s."NetworkId",
    MAX(s."NetworkName") AS "NetworkName",  -- Usa MAX para agregación (todos son iguales por NetworkId)
    MAX(s."OrganizationName") AS "OrganizationName",

    -- Rango de fechas
    MIN(s."DetectionDate") AS "FirstDetection",
    MAX(s."DetectionDate") AS "LastDetection",

    -- Métricas de visitantes (exactas al método CalculateVisitorMetrics)
    COUNT(*) AS "TotalPersons",
    COUNT(*) FILTER (WHERE s."PersonType" = 'Visitor') AS "Visitors",
    COUNT(*) FILTER (WHERE s."PersonType" != 'Visitor') AS "NonVisitors",

    -- VisitorRate: Porcentaje de visitantes
    CASE
        WHEN COUNT(*) > 0
        THEN ROUND((COUNT(*) FILTER (WHERE s."PersonType" = 'Visitor')::numeric / COUNT(*)::numeric * 100), 1)
        ELSE 0
    END AS "VisitorRate",

    -- AverageDurationMinutes
    CASE
        WHEN COUNT(*) > 0
        THEN ROUND(AVG(s."DurationInMinutes")::numeric, 0)::integer
        ELSE 0
    END AS "AverageDurationMinutes",

    -- Métricas adicionales útiles
    MIN(s."DurationInMinutes") AS "MinDurationMinutes",
    MAX(s."DurationInMinutes") AS "MaxDurationMinutes",
    COUNT(DISTINCT s."MacAddress") AS "UniqueDevices",
    COUNT(DISTINCT CASE WHEN s."IsRegisteredUser" THEN s."MacAddress" END) AS "RegisteredUsers",

    -- Distribución por categoría de presencia
    COUNT(*) FILTER (WHERE s."PresenceCategory" IS NOT NULL) AS "CategorizedDetections",

    -- Métricas de señal
    ROUND(AVG(s."AverageRssi")::numeric, 0)::integer AS "AvgSignalStrength"

FROM public.scanning_report_daily_full s
GROUP BY s."NetworkId"
ORDER BY s."NetworkId";

-- =============================================================================
-- Vista Materializada: splash_wifi_scanning_metrics_daily
-- Descripción: Métricas agregadas por NetworkId y Fecha (para mejor performance)
-- Uso: Consultas con filtros de fecha frecuentes en dashboards
-- Mantenimiento: Refrescar diariamente con REFRESH MATERIALIZED VIEW
-- Optimización: Eliminados JOINs innecesarios (datos ya en scanning_report_daily_full)
-- =============================================================================

CREATE MATERIALIZED VIEW IF NOT EXISTS public.splash_wifi_scanning_metrics_daily AS
SELECT
    s."NetworkId",
    MAX(s."NetworkName") AS "NetworkName",  -- Usa MAX para agregación
    MAX(s."OrganizationName") AS "OrganizationName",
    s."DetectionDate"::date AS "Date",
    MAX(s."Year") AS "Year",
    MAX(s."MonthNumber") AS "MonthNumber",
    MAX(s."MonthName") AS "MonthName",
    MAX(s."WeekNumber") AS "WeekNumber",
    MAX(s."DayOfWeek") AS "DayOfWeek",
    MAX(s."DayName") AS "DayName",

    -- Métricas exactas de CalculateVisitorMetrics
    COUNT(*) AS "TotalPersons",
    COUNT(*) FILTER (WHERE s."PersonType" = 'Visitor') AS "Visitors",
    COUNT(*) FILTER (WHERE s."PersonType" != 'Visitor') AS "NonVisitors",

    -- VisitorRate: Porcentaje de visitantes
    CASE
        WHEN COUNT(*) > 0
        THEN ROUND((COUNT(*) FILTER (WHERE s."PersonType" = 'Visitor')::numeric / COUNT(*)::numeric * 100), 1)
        ELSE 0
    END AS "VisitorRate",

    -- AverageDurationMinutes
    CASE
        WHEN COUNT(*) > 0
        THEN ROUND(AVG(s."DurationInMinutes")::numeric, 0)::integer
        ELSE 0
    END AS "AverageDurationMinutes",

    -- Métricas adicionales
    MIN(s."DurationInMinutes") AS "MinDurationMinutes",
    MAX(s."DurationInMinutes") AS "MaxDurationMinutes",
    COUNT(DISTINCT s."MacAddress") AS "UniqueDevices",
    COUNT(DISTINCT CASE WHEN s."IsRegisteredUser" THEN s."MacAddress" END) AS "RegisteredUsers",

    -- Métricas de señal
    ROUND(AVG(s."AverageRssi")::numeric, 0)::integer AS "AvgSignalStrength",
    MIN(s."MinimumRssi") AS "MinRssi",
    MAX(s."MaximumRssi") AS "MaxRssi"

FROM public.scanning_report_daily_full s
GROUP BY
    s."NetworkId",
    s."DetectionDate"::date;

-- =============================================================================
-- Índices para Vista Materializada
-- =============================================================================

-- Índices para mejorar performance en consultas con filtros
CREATE INDEX IF NOT EXISTS idx_scanning_metrics_daily_date
    ON public.splash_wifi_scanning_metrics_daily("Date", "NetworkId");

CREATE INDEX IF NOT EXISTS idx_scanning_metrics_daily_network
    ON public.splash_wifi_scanning_metrics_daily("NetworkId", "Date");

CREATE INDEX IF NOT EXISTS idx_scanning_metrics_daily_year_month
    ON public.splash_wifi_scanning_metrics_daily("Year", "MonthNumber", "NetworkId");

-- =============================================================================
-- CRÍTICO: Índices en la tabla/vista base scanning_report_daily_full
-- NOTA: Estos índices son ESENCIALES para mejorar performance
-- Si scanning_report_daily_full es una VISTA, crear MATERIALIZED VIEW con índices
-- Si es una TABLA, estos índices mejorarán drasticamente las consultas
-- =============================================================================

-- Índice compuesto para filtros de fecha y network (más usado)
CREATE INDEX IF NOT EXISTS idx_scanning_daily_networkid_date
    ON public.scanning_report_daily_full("NetworkId", "DetectionDate");

-- Índice solo por fecha (para queries que filtran todas las networks)
CREATE INDEX IF NOT EXISTS idx_scanning_daily_date
    ON public.scanning_report_daily_full("DetectionDate");

-- Índice por PersonType para filtros específicos
CREATE INDEX IF NOT EXISTS idx_scanning_daily_persontype
    ON public.scanning_report_daily_full("PersonType");

-- Índice compuesto para queries complejas (NetworkId, Date, PersonType)
CREATE INDEX IF NOT EXISTS idx_scanning_daily_composite
    ON public.scanning_report_daily_full("NetworkId", "DetectionDate", "PersonType");

-- =============================================================================
-- Comandos de Mantenimiento
-- =============================================================================

-- Refrescar la vista materializada (ejecutar diariamente o según necesidad)
-- REFRESH MATERIALIZED VIEW public.splash_wifi_scanning_metrics_daily;

-- Refrescar sin bloquear lecturas (requiere índice único, PostgreSQL 9.4+)
-- REFRESH MATERIALIZED VIEW CONCURRENTLY public.splash_wifi_scanning_metrics_daily;

-- Eliminar vista materializada (si necesitas recrearla)
-- DROP MATERIALIZED VIEW IF EXISTS public.splash_wifi_scanning_metrics_daily;

-- =============================================================================
-- Ejemplos de Uso
-- =============================================================================

-- =============================================================================
-- CONSULTA OPTIMIZADA: Equivalente a tu consulta original pero MUCHO MÁS RÁPIDA
-- Esta consulta debería ser más rápida que C# porque:
-- 1. No hace JOINs innecesarios
-- 2. Usa índices apropiados
-- 3. Filtra ANTES de agregar
-- =============================================================================

-- Consulta directa optimizada (sin vista pre-agregada)
-- Usa esta para consultas ad-hoc con filtros específicos
-- DEBE tardar MENOS de 7.63 segundos con índices apropiados
/*
SELECT
    "NetworkId",
    MAX("NetworkName") AS "NetworkName",
    MAX("OrganizationName") AS "OrganizationName",

    -- Métricas exactas de CalculateVisitorMetrics
    COUNT(*) AS "TotalPersons",
    COUNT(*) FILTER (WHERE "PersonType" = 'Visitor') AS "Visitors",

    CASE
        WHEN COUNT(*) > 0
        THEN ROUND((COUNT(*) FILTER (WHERE "PersonType" = 'Visitor')::numeric / COUNT(*)::numeric * 100), 1)
        ELSE 0
    END AS "VisitorRate",

    CASE
        WHEN COUNT(*) > 0
        THEN ROUND(AVG("DurationInMinutes")::numeric, 0)::integer
        ELSE 0
    END AS "AverageDurationMinutes"

FROM public.scanning_report_daily_full
WHERE "NetworkId" IN (17, 372, 82, 93, 95)
  AND "DetectionDate" >= '2025-10-18'
  AND "DetectionDate" <= '2025-10-25'
GROUP BY "NetworkId"
ORDER BY "NetworkId";
*/

-- =============================================================================
-- DIAGNÓSTICO DE PERFORMANCE
-- =============================================================================

-- Ver plan de ejecución de la consulta (busca "Seq Scan" - malo, "Index Scan" - bueno)
/*
EXPLAIN ANALYZE
SELECT
    "NetworkId",
    COUNT(*) AS "TotalPersons",
    COUNT(*) FILTER (WHERE "PersonType" = 'Visitor') AS "Visitors"
FROM public.scanning_report_daily_full
WHERE "NetworkId" IN (17, 372, 82, 93, 95)
  AND "DetectionDate" >= '2025-10-18'
  AND "DetectionDate" <= '2025-10-25'
GROUP BY "NetworkId";
*/

-- Ver estadísticas de índices utilizados
/*
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE tablename = 'scanning_report_daily_full'
ORDER BY idx_scan DESC;
*/

-- Ver tamaño de la tabla/vista
/*
SELECT
    pg_size_pretty(pg_total_relation_size('scanning_report_daily_full')) AS total_size,
    pg_size_pretty(pg_relation_size('scanning_report_daily_full')) AS table_size,
    pg_size_pretty(pg_indexes_size('scanning_report_daily_full')) AS indexes_size;
*/

-- =============================================================================
-- Ejemplos de Uso con Vistas Pre-agregadas
-- =============================================================================

-- Ejemplo 1: Obtener métricas de un network específico (sin filtro de fecha)
/*
SELECT
    "NetworkName",
    "TotalPersons",
    "Visitors",
    "VisitorRate",
    "AverageDurationMinutes"
FROM public.splash_wifi_scanning_metrics_by_network
WHERE "NetworkId" = 123;
*/

-- Ejemplo 2: Obtener métricas por rango de fechas (USA LA VISTA MATERIALIZADA)
/*
SELECT
    "NetworkName",
    "Date",
    "TotalPersons",
    "Visitors",
    "VisitorRate",
    "AverageDurationMinutes"
FROM public.splash_wifi_scanning_metrics_daily
WHERE "NetworkId" = 123
  AND "Date" BETWEEN '2025-01-01' AND '2025-01-31'
ORDER BY "Date";
*/

-- Ejemplo 3: Agregar métricas de múltiples días en un período (RECOMENDADO)
/*
SELECT
    "NetworkId",
    "NetworkName",
    SUM("TotalPersons") AS "TotalPersons",
    SUM("Visitors") AS "Visitors",
    CASE
        WHEN SUM("TotalPersons") > 0
        THEN ROUND((SUM("Visitors")::numeric / SUM("TotalPersons")::numeric * 100), 1)
        ELSE 0
    END AS "VisitorRate",
    ROUND(AVG("AverageDurationMinutes")::numeric, 0)::integer AS "AverageDurationMinutes"
FROM public.splash_wifi_scanning_metrics_daily
WHERE "Date" BETWEEN '2025-10-18' AND '2025-10-25'
  AND "NetworkId" IN (17, 372, 82, 93, 95)
GROUP BY "NetworkId", "NetworkName";
*/

-- Ejemplo 4: Top 10 Networks por VisitorRate en el último mes
/*
SELECT
    "NetworkName",
    SUM("TotalPersons") AS "TotalPersons",
    SUM("Visitors") AS "Visitors",
    CASE
        WHEN SUM("TotalPersons") > 0
        THEN ROUND((SUM("Visitors")::numeric / SUM("TotalPersons")::numeric * 100), 1)
        ELSE 0
    END AS "VisitorRate"
FROM public.splash_wifi_scanning_metrics_daily
WHERE "Date" >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY "NetworkId", "NetworkName"
ORDER BY "VisitorRate" DESC
LIMIT 10;
*/

-- =============================================================================
-- Permisos
-- =============================================================================
ALTER TABLE public.splash_wifi_scanning_metrics_by_network OWNER TO mysql;
ALTER TABLE public.splash_wifi_scanning_metrics_daily OWNER TO mysql;

-- =============================================================================
-- RECOMENDACIONES DE OPTIMIZACIÓN
-- =============================================================================
/*
Si la consulta sigue siendo lenta después de crear los índices:

1. VERIFICAR si scanning_report_daily_full es una VISTA o TABLA:
   - Si es VISTA: Considera convertirla a MATERIALIZED VIEW
   - Si es TABLA: Los índices deberían ayudar significativamente

2. ANALIZAR estadísticas de la tabla:
   ANALYZE public.scanning_report_daily_full;

3. VACUUM si la tabla tiene muchos registros eliminados:
   VACUUM ANALYZE public.scanning_report_daily_full;

4. CONSIDERAR particionamiento por fecha si la tabla es muy grande (>10M registros):
   CREATE TABLE scanning_report_daily_full_2025_01 PARTITION OF scanning_report_daily_full
   FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

5. MONITOREAR queries lentas:
   SELECT query, mean_exec_time, calls
   FROM pg_stat_statements
   WHERE query LIKE '%scanning_report_daily_full%'
   ORDER BY mean_exec_time DESC
   LIMIT 10;

6. AJUSTAR parámetros de PostgreSQL si es necesario:
   - shared_buffers (25% de RAM)
   - effective_cache_size (50-75% de RAM)
   - work_mem (para sorts y agregaciones grandes)
   - maintenance_work_mem (para CREATE INDEX)
*/