Temp_MSSPLASHPage/SQL/OptimizeScanningReportView.sql

-- =====================================================================
-- Script de Optimización para scanning_report_daily_full
-- Propósito: Mejorar el rendimiento del widget "Comparativa entre Sucursales"
-- Fecha: 2025-10-21
-- =====================================================================

-- IMPORTANTE: Este script asume que scanning_report_daily_full es una VISTA MATERIALIZADA
-- Si es una vista regular, se deben crear índices en las tablas subyacentes

-- =====================================================================
-- Paso 1: Verificar si la vista es materializada
-- =====================================================================
SELECT
    schemaname,
    matviewname,
    definition
FROM pg_matviews
WHERE matviewname = 'scanning_report_daily_full';

-- Si el resultado está vacío, es una vista regular, no materializada
-- En ese caso, usar el script alternativo al final de este archivo

-- =====================================================================
-- Paso 2: Crear índices en la vista materializada (si existe)
-- =====================================================================

-- Índice compuesto principal: NetworkId + DetectionDate
-- Este índice optimiza las queries más comunes del widget
CREATE INDEX IF NOT EXISTS idx_scanning_report_network_date
ON scanning_report_daily_full (network_id, detection_date DESC);

-- Índice para filtros por rango de fechas
CREATE INDEX IF NOT EXISTS idx_scanning_report_detection_date
ON scanning_report_daily_full (detection_date DESC);

-- Índice para filtro por tipo de persona (PasserBy, Visitor)
CREATE INDEX IF NOT EXISTS idx_scanning_report_person_type
ON scanning_report_daily_full (person_type);

-- Índice compuesto para análisis por red y tipo de persona
CREATE INDEX IF NOT EXISTS idx_scanning_report_network_person
ON scanning_report_daily_full (network_id, person_type, detection_date DESC);

-- Índice para búsquedas por MAC address (reportes individuales)
CREATE INDEX IF NOT EXISTS idx_scanning_report_mac
ON scanning_report_daily_full (mac_address);

-- =====================================================================
-- Paso 3: Configurar auto-refresh de la vista materializada (OPCIONAL)
-- =====================================================================
-- NOTA: PostgreSQL no tiene auto-refresh nativo. Esto requiere un job externo.
-- Ejemplo usando pg_cron (si está instalado):

/*
-- Instalar pg_cron primero:
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- Programar refresh cada hora
SELECT cron.schedule(
    'refresh-scanning-report',
    '0 * * * *', -- Cada hora en punto
    $$REFRESH MATERIALIZED VIEW CONCURRENTLY scanning_report_daily_full$$
);
*/

-- Alternativa: Refresh manual (ejecutar según necesidad)
-- REFRESH MATERIALIZED VIEW CONCURRENTLY scanning_report_daily_full;

-- =====================================================================
-- Paso 4: Analizar estadísticas para el optimizador de queries
-- =====================================================================
ANALYZE scanning_report_daily_full;

-- =====================================================================
-- Paso 5: Verificar que los índices se crearon correctamente
-- =====================================================================
SELECT
    indexname,
    indexdef
FROM pg_indexes
WHERE tablename = 'scanning_report_daily_full'
ORDER BY indexname;

-- =====================================================================
-- SCRIPT ALTERNATIVO: Si scanning_report_daily_full es una VISTA REGULAR
-- =====================================================================
/*
-- Si es una vista regular, necesitamos crear índices en las tablas base
-- Asumiendo que la vista consulta la tabla SplashWiFiScanningData:

-- Índice compuesto principal
CREATE INDEX IF NOT EXISTS idx_wifi_scanning_network_date
ON "SplashWiFiScanningData" ("NetworkId", "CreationTime" DESC);

-- Índice para filtros por fecha
CREATE INDEX IF NOT EXISTS idx_wifi_scanning_creation_time
ON "SplashWiFiScanningData" ("CreationTime" DESC);

-- Índice para MAC address
CREATE INDEX IF NOT EXISTS idx_wifi_scanning_mac
ON "SplashWiFiScanningData" ("ClientMac");

-- Índice para RSSI (usado en clasificación PasserBy/Visitor)
CREATE INDEX IF NOT EXISTS idx_wifi_scanning_rssi
ON "SplashWiFiScanningData" ("NearestApRssi");

-- Índice compuesto para queries complejas
CREATE INDEX IF NOT EXISTS idx_wifi_scanning_network_time_rssi
ON "SplashWiFiScanningData" ("NetworkId", "CreationTime" DESC, "NearestApRssi");

-- Analizar tabla base
ANALYZE "SplashWiFiScanningData";
*/

-- =====================================================================
-- Paso 6: Monitoreo de rendimiento (queries útiles)
-- =====================================================================

-- Ver tamaño de la vista/tabla
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;

-- Ver queries lentas relacionadas con la vista (requiere pg_stat_statements)
/*
SELECT
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    max_exec_time
FROM pg_stat_statements
WHERE query ILIKE '%scanning_report_daily_full%'
ORDER BY mean_exec_time DESC
LIMIT 10;
*/

-- =====================================================================
-- NOTAS IMPORTANTES
-- =====================================================================
/*
1. Si scanning_report_daily_full es una VISTA MATERIALIZADA:
   - Los índices mejoran las queries de lectura
   - Pero hacen más lento el REFRESH
   - Considerar refresh durante horas de bajo tráfico

2. Si es una VISTA REGULAR:
   - Usar el script alternativo para indexar tablas base
   - Los cambios se reflejan inmediatamente
   - No hay overhead de refresh

3. Monitoreo post-implementación:
   - Verificar plan de ejecución con EXPLAIN ANALYZE
   - Monitorear pg_stat_user_indexes para uso de índices
   - Ajustar según patrones de uso real

4. Mantenimiento:
   - REINDEX periódico si hay muchos UPDATEs/DELETEs
   - VACUUM ANALYZE regular
   - Considerar particionado si la tabla crece mucho (>10M rows)
*/

-- =====================================================================
-- FIN DEL SCRIPT
-- =====================================================================