Temp_MSSPLASHPage/SQL/Scanning/01_CREATE_INDEXES_PHASE1.sql

-- =====================================================================
-- FASE 1: Índices Críticos para Optimización Inmediata
-- Propósito: Mejorar rendimiento de scanning_report_daily_full
-- Tiempo estimado: 1-2 horas (dependiendo del tamaño de datos)
-- Mejora esperada: 40-60% más rápido
-- =====================================================================

-- IMPORTANTE: Todos los índices usan CONCURRENTLY para no bloquear producción
-- Esto permite crear índices sin downtime, pero toma más tiempo

SET statement_timeout = 0;
SET lock_timeout = 0;

-- =====================================================================
-- 1. Índice para JOIN de SplashUserConnections
-- =====================================================================
-- Problema: El LEFT JOIN con DISTINCT es muy lento
-- Solución: Índice filtrado solo en registros no eliminados

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_splash_user_connections_mac_notdeleted
ON "SplashUserConnections" ("MacAddress")
WHERE "IsDeleted" = false;

COMMENT ON INDEX idx_splash_user_connections_mac_notdeleted IS
'Índice parcial para JOIN rápido en scanning_report_daily_full. Solo indexa registros activos.';

-- =====================================================================
-- 2. Índice compuesto principal para queries comunes
-- =====================================================================
-- Optimiza: WHERE NetworkId IN (...) AND DetectionDate BETWEEN ... AND ...
-- Este es el patrón más común en los dashboards

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_scanning_report_network_date_person
ON scanning_report_daily_full ("NetworkId", "DetectionDate" DESC, "PersonType")
INCLUDE ("MacAddress", "DurationInMinutes", "IsRegisteredUser", "DetectionsCount");

COMMENT ON INDEX idx_scanning_report_network_date_person IS
'Índice compuesto para filtros por red + fecha + tipo. INCLUDE permite index-only scans.';

-- =====================================================================
-- 3. Índice para filtros de rango de fechas
-- =====================================================================
-- Optimiza: WHERE DetectionDate BETWEEN ... AND ...
-- Útil para queries de métricas sin filtro de red

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_scanning_report_detection_date_network
ON scanning_report_daily_full ("DetectionDate" DESC, "NetworkId");

COMMENT ON INDEX idx_scanning_report_detection_date_network IS
'Índice para queries de rango de fechas. Orden DESC optimiza ORDER BY recientes.';

-- =====================================================================
-- 4. Índice para filtros por tipo de persona (PasserBy/Visitor)
-- =====================================================================
-- Optimiza: WHERE PersonType = 'Visitor'
-- Usado en análisis de conversión

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_scanning_report_person_type
ON scanning_report_daily_full ("PersonType")
WHERE "PersonType" IS NOT NULL;

COMMENT ON INDEX idx_scanning_report_person_type IS
'Índice filtrado para análisis por tipo de persona (Visitor/PasserBy).';

-- =====================================================================
-- 5. Índice para búsquedas por MAC Address
-- =====================================================================
-- Optimiza: WHERE MacAddress = '...'
-- Usado en reportes individuales y seguimiento de dispositivos

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_scanning_report_mac_address
ON scanning_report_daily_full ("MacAddress");

COMMENT ON INDEX idx_scanning_report_mac_address IS
'Índice para búsquedas por dispositivo específico (MAC address).';

-- =====================================================================
-- 6. Índice para análisis por Access Point
-- =====================================================================
-- Optimiza: JOIN con SplashAccessPoints
-- Mejora queries de distribución por ubicación

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_splash_access_points_mac
ON "SplashAccessPoints" ("Mac")
WHERE "IsDeleted" = false;

COMMENT ON INDEX idx_splash_access_points_mac IS
'Índice para JOIN rápido con AccessPoints en scanning reports.';

-- =====================================================================
-- 7. Índice para JOIN con Networks
-- =====================================================================
-- Optimiza: LEFT JOIN "SplashMerakiNetworks"
-- Mejora enriquecimiento de datos

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_splash_networks_id_org
ON "SplashMerakiNetworks" ("Id", "OrganizationId")
WHERE "IsDeleted" = false;

COMMENT ON INDEX idx_splash_networks_id_org IS
'Índice para JOINs de Networks con Organizations en scanning reports.';

-- =====================================================================
-- 8. Índice compuesto para widget "Comparativa entre Sucursales"
-- =====================================================================
-- Optimiza la query específica del widget más lento

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_scanning_report_branch_comparison
ON scanning_report_daily_full ("NetworkId", "DetectionDate" DESC, "PersonType", "IsRegisteredUser")
WHERE "DurationInMinutes" > 1;

COMMENT ON INDEX idx_scanning_report_branch_comparison IS
'Índice especializado para widget Comparativa entre Sucursales. Filtrado por duración > 1min.';

-- =====================================================================
-- 9. Actualizar estadísticas del optimizador
-- =====================================================================
-- Crítico: PostgreSQL necesita estadísticas actualizadas para elegir el índice correcto

ANALYZE "SplashUserConnections";
ANALYZE "SplashAccessPoints";
ANALYZE "SplashMerakiNetworks";
ANALYZE "SplashMerakiOrganizations";

-- Si scanning_report_daily_full es materialized view:
-- ANALYZE scanning_report_daily_full;

-- Si es vista regular, analizar la tabla base:
-- ANALYZE "SplashWiFiScanningData";

-- =====================================================================
-- 10. Verificar que los índices se crearon correctamente
-- =====================================================================
SELECT
    schemaname,
    tablename,
    indexname,
    indexdef
FROM pg_indexes
WHERE indexname LIKE 'idx_scanning%' OR indexname LIKE 'idx_splash%'
ORDER BY tablename, indexname;

-- =====================================================================
-- 11. Verificar tamaño de índices
-- =====================================================================
SELECT
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(schemaname||'.'||indexname)) as index_size
FROM pg_indexes
WHERE indexname LIKE 'idx_scanning%' OR indexname LIKE 'idx_splash%'
ORDER BY pg_relation_size(schemaname||'.'||indexname) DESC;

-- =====================================================================
-- 12. Monitoreo: Queries lentas relacionadas (requiere pg_stat_statements)
-- =====================================================================
-- Descomentar si pg_stat_statements está habilitado:

/*
SELECT
    query,
    calls,
    mean_exec_time,
    max_exec_time,
    total_exec_time
FROM pg_stat_statements
WHERE query ILIKE '%scanning_report_daily_full%'
ORDER BY mean_exec_time DESC
LIMIT 10;
*/

-- =====================================================================
-- NOTAS POST-IMPLEMENTACIÓN
-- =====================================================================
/*
1. Monitorear pg_stat_user_indexes para verificar uso:
   SELECT * FROM pg_stat_user_indexes WHERE indexrelname LIKE 'idx_scanning%';

2. Si un índice no se usa después de 1 semana, considerar eliminarlo

3. Ejecutar REINDEX periódicamente si hay muchos UPDATEs/DELETEs:
   REINDEX INDEX CONCURRENTLY idx_scanning_report_network_date_person;

4. Próximo paso: Implementar Fase 2 (pre-calcular timezone fields)
*/

-- =====================================================================
-- FIN DE FASE 1
-- =====================================================================