Temp_MSSPLASHPage/DIAGNOSTIC_QUERIES_PASSERSBY.sql

-- ============================================================================
-- DIAGNOSTIC QUERIES FOR PASSERSBY (TRANSEÚNTES) TROUBLESHOOTING
-- ============================================================================
-- Purpose: Diagnose why passersby data is showing as 0 in the dashboard
-- Database: MySQL/PostgreSQL (adjust syntax if needed)
-- ============================================================================

-- ============================================================================
-- MASTER DIAGNOSTIC QUERY
-- ============================================================================
-- This query provides a comprehensive overview of all networks and their
-- scanning data status. Run this FIRST to identify the problem.
-- ============================================================================

WITH raw_data AS (
    SELECT
        NetworkId,
        COUNT(*) as total_records,
        COUNT(DISTINCT ClientMac) as unique_devices,
        COUNT(CASE WHEN ManufacturerIsExcluded THEN 1 END) as excluded_records,
        COUNT(CASE WHEN SSID IS NULL OR SSID = '' THEN 1 END) as no_ssid_records,
        COUNT(CASE WHEN NearestApRssi < -60 THEN 1 END) as weak_signal_count,
        MIN(CreationTime) as first_record,
        MAX(CreationTime) as last_record
    FROM SplashWiFiScanningData
    WHERE CreationTime >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
    GROUP BY NetworkId
),
scanning_config AS (
    SELECT
        NetworkId,
        IsEnabled,
        SyncStatus,
        LastSyncedAt,
        PostUrl,
        ErrorMessage
    FROM SplashLocationScanningConfigs
)
SELECT
    n.Name as network_name,
    n.Id as network_id,
    c.IsEnabled as scanning_enabled,
    c.SyncStatus,
    c.LastSyncedAt,
    c.PostUrl as webhook_url,
    c.ErrorMessage as sync_error,
    COALESCE(r.total_records, 0) as total_records_24h,
    COALESCE(r.unique_devices, 0) as unique_devices_24h,
    COALESCE(r.no_ssid_records, 0) as passersby_candidates,
    COALESCE(r.weak_signal_count, 0) as passersby_weak_signal,
    r.last_record as last_data_received,
    TIMESTAMPDIFF(MINUTE, r.last_record, NOW()) as minutes_since_last_data,
    CASE
        WHEN c.IsEnabled = 0 THEN '❌ PROBLEMA: Scanning Deshabilitado - Habilitar en configuración'
        WHEN c.SyncStatus IS NULL THEN '❌ PROBLEMA: No existe configuración - Crear config de Location Scanning'
        WHEN c.SyncStatus != 'Active' THEN CONCAT('❌ PROBLEMA: Sync No Activo - Status: ', c.SyncStatus)
        WHEN r.last_record IS NULL THEN '❌ PROBLEMA: No hay datos - Verificar webhook en Meraki Dashboard'
        WHEN TIMESTAMPDIFF(MINUTE, r.last_record, NOW()) > 60 THEN '⚠️ ADVERTENCIA: Datos obsoletos (>1h) - Revisar webhook'
        WHEN r.no_ssid_records = 0 THEN '⚠️ ADVERTENCIA: Todos los dispositivos tienen SSID - No hay transeúntes detectados'
        WHEN r.weak_signal_count = 0 THEN '⚠️ ADVERTENCIA: No hay señales débiles - Todos tienen RSSI >= -60'
        WHEN r.excluded_records = r.total_records THEN '❌ PROBLEMA: Todos los manufacturers excluidos'
        ELSE '✅ OK - Debería haber datos de transeúntes'
    END as diagnosis,
    CASE
        WHEN c.IsEnabled = 0 THEN 'Ir a Settings > Location Scanning > Habilitar network'
        WHEN c.SyncStatus IS NULL THEN 'Crear configuración de Location Scanning para este network'
        WHEN c.SyncStatus != 'Active' THEN 'Revisar ErrorMessage y volver a sincronizar'
        WHEN r.last_record IS NULL THEN 'Configurar webhook en Meraki Dashboard con URL correcta'
        WHEN TIMESTAMPDIFF(MINUTE, r.last_record, NOW()) > 60 THEN 'Verificar que webhook esté activo en Meraki'
        WHEN r.excluded_records = r.total_records THEN 'Revisar lista de manufacturers excluidos'
        ELSE 'Ejecutar queries adicionales para más detalles'
    END as recommended_action
FROM SplashMerakiNetworks n
LEFT JOIN scanning_config c ON c.NetworkId = n.Id
LEFT JOIN raw_data r ON r.NetworkId = n.Id
WHERE n.IsDeleted = 0
ORDER BY
    CASE c.IsEnabled WHEN 1 THEN 0 ELSE 1 END,
    n.Name;


-- ============================================================================
-- QUERY 1: Check Raw Scanning Data (Last 24 Hours)
-- ============================================================================
-- Verifica si hay datos crudos en la tabla principal
-- Si esta query retorna 0 registros, el problema es que no llegan datos de Meraki
-- ============================================================================

SELECT
    'Last 24 Hours' as time_period,
    COUNT(*) as total_records,
    COUNT(DISTINCT ClientMac) as unique_devices,
    MIN(CreationTime) as first_record,
    MAX(CreationTime) as last_record,
    TIMESTAMPDIFF(MINUTE, MAX(CreationTime), NOW()) as minutes_since_last
FROM SplashWiFiScanningData
WHERE CreationTime >= DATE_SUB(NOW(), INTERVAL 24 HOUR);


-- ============================================================================
-- QUERY 2: Check Real-Time Window (Last 30 Minutes)
-- ============================================================================
-- El widget de tiempo real solo mira los últimos 30 minutos
-- Esta es la query más importante para el widget PassersRealTimeWidget
-- ============================================================================

SELECT
    'Last 30 Minutes (Real-Time Window)' as time_period,
    COUNT(*) as total_records,
    COUNT(DISTINCT ClientMac) as unique_devices,
    COUNT(CASE WHEN ManufacturerIsExcluded = 0 THEN 1 END) as valid_records,
    COUNT(CASE WHEN SSID IS NULL OR SSID = '' THEN 1 END) as no_ssid_count,
    COUNT(CASE WHEN (SSID IS NULL OR SSID = '') AND ManufacturerIsExcluded = 0 THEN 1 END) as passersby_eligible,
    MIN(CreationTime) as first_record,
    MAX(CreationTime) as last_record
FROM SplashWiFiScanningData
WHERE CreationTime >= DATE_SUB(NOW(), INTERVAL 30 MINUTE);


-- ============================================================================
-- QUERY 3: Simulate Real-Time PassersBy Calculation
-- ============================================================================
-- Esta query simula exactamente lo que hace el backend en RealTimeStats()
-- Muestra cuántos transeúntes deberían aparecer en el widget
-- ============================================================================

SELECT
    ClientMac,
    MIN(NearestApTime) as first_seen,
    MAX(NearestApTime) as last_seen,
    TIMESTAMPDIFF(SECOND, MIN(NearestApTime), MAX(NearestApTime)) as dwell_time_seconds,
    AVG(NearestApRssi) as average_rssi,
    COUNT(*) as detections_count,
    CASE
        WHEN AVG(NearestApRssi) < -60 THEN 'PasserBy'
        ELSE 'Visitor'
    END as person_type
FROM SplashWiFiScanningData
WHERE CreationTime >= DATE_SUB(NOW(), INTERVAL 30 MINUTE)
  AND ManufacturerIsExcluded = 0
  AND Manufacturer IS NOT NULL
  AND (SSID IS NULL OR SSID = '')
GROUP BY ClientMac
HAVING COUNT(*) > 0
ORDER BY average_rssi ASC;

-- Count PassersBy vs Visitors
SELECT
    CASE
        WHEN AVG(NearestApRssi) < -60 THEN 'PasserBy'
        ELSE 'Visitor'
    END as person_type,
    COUNT(DISTINCT ClientMac) as count
FROM SplashWiFiScanningData
WHERE CreationTime >= DATE_SUB(NOW(), INTERVAL 30 MINUTE)
  AND ManufacturerIsExcluded = 0
  AND Manufacturer IS NOT NULL
  AND (SSID IS NULL OR SSID = '')
GROUP BY
    CASE
        WHEN AVG(NearestApRssi) < -60 THEN 'PasserBy'
        ELSE 'Visitor'
    END;


-- ============================================================================
-- QUERY 4: Check Location Scanning Configuration
-- ============================================================================
-- Verifica la configuración de Location Scanning para cada network
-- IsEnabled debe ser 1 y SyncStatus debe ser 'Active'
-- ============================================================================

SELECT
    n.Name as network_name,
    n.NetworkId as meraki_network_id,
    c.IsEnabled,
    c.SyncStatus,
    c.ApiVersion,
    c.ValidatorSecret,
    c.PostUrl as webhook_url,
    c.LastSyncedAt,
    c.ErrorMessage,
    c.FailedAttempts,
    CASE
        WHEN c.Id IS NULL THEN '❌ No configurado'
        WHEN c.IsEnabled = 0 THEN '❌ Deshabilitado'
        WHEN c.SyncStatus != 'Active' THEN CONCAT('❌ Status: ', c.SyncStatus)
        ELSE '✅ Configurado correctamente'
    END as status
FROM SplashMerakiNetworks n
LEFT JOIN SplashLocationScanningConfigs c ON c.NetworkId = n.Id
WHERE n.IsDeleted = 0
ORDER BY n.Name;


-- ============================================================================
-- QUERY 5: Check Excluded Manufacturers
-- ============================================================================
-- Verifica qué manufacturers están siendo excluidos
-- Si todos los manufacturers están excluidos, no habrá datos
-- ============================================================================

SELECT
    Manufacturer,
    COUNT(*) as total_records,
    SUM(CASE WHEN ManufacturerIsExcluded = 1 THEN 1 ELSE 0 END) as excluded_count,
    SUM(CASE WHEN ManufacturerIsExcluded = 0 THEN 1 ELSE 0 END) as included_count,
    ROUND(SUM(CASE WHEN ManufacturerIsExcluded = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) as excluded_percentage
FROM SplashWiFiScanningData
WHERE CreationTime >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY Manufacturer
ORDER BY total_records DESC
LIMIT 20;


-- ============================================================================
-- QUERY 6: Check SSID Distribution
-- ============================================================================
-- Los transeúntes (PassersBy) tienen SSID = NULL o vacío
-- Si todos tienen SSID, significa que están conectados a WiFi (no son transeúntes)
-- ============================================================================

SELECT
    CASE
        WHEN SSID IS NULL OR SSID = '' THEN 'Sin SSID (Transeúntes)'
        ELSE CONCAT('Con SSID: ', SSID)
    END as ssid_status,
    COUNT(*) as total_records,
    COUNT(DISTINCT ClientMac) as unique_devices,
    ROUND(AVG(NearestApRssi), 2) as avg_rssi
FROM SplashWiFiScanningData
WHERE CreationTime >= DATE_SUB(NOW(), INTERVAL 30 MINUTE)
GROUP BY
    CASE
        WHEN SSID IS NULL OR SSID = '' THEN 'Sin SSID (Transeúntes)'
        ELSE CONCAT('Con SSID: ', SSID)
    END
ORDER BY total_records DESC;


-- ============================================================================
-- QUERY 7: Check RSSI Distribution (Signal Strength)
-- ============================================================================
-- PassersBy tienen RSSI < -60 (señal débil, fuera del local)
-- Visitors tienen RSSI >= -60 (señal fuerte, dentro del local)
-- ============================================================================

SELECT
    CASE
        WHEN NearestApRssi < -80 THEN 'Muy Débil (< -80)'
        WHEN NearestApRssi < -70 THEN 'Débil (-70 to -80)'
        WHEN NearestApRssi < -60 THEN 'PasserBy (-60 to -70)'
        WHEN NearestApRssi < -50 THEN 'Visitor (-50 to -60)'
        ELSE 'Fuerte (>= -50)'
    END as rssi_range,
    COUNT(*) as total_records,
    COUNT(DISTINCT ClientMac) as unique_devices
FROM SplashWiFiScanningData
WHERE CreationTime >= DATE_SUB(NOW(), INTERVAL 30 MINUTE)
  AND ManufacturerIsExcluded = 0
  AND (SSID IS NULL OR SSID = '')
GROUP BY
    CASE
        WHEN NearestApRssi < -80 THEN 'Muy Débil (< -80)'
        WHEN NearestApRssi < -70 THEN 'Débil (-70 to -80)'
        WHEN NearestApRssi < -60 THEN 'PasserBy (-60 to -70)'
        WHEN NearestApRssi < -50 THEN 'Visitor (-50 to -60)'
        ELSE 'Fuerte (>= -50)'
    END
ORDER BY
    CASE
        WHEN NearestApRssi < -80 THEN 1
        WHEN NearestApRssi < -70 THEN 2
        WHEN NearestApRssi < -60 THEN 3
        WHEN NearestApRssi < -50 THEN 4
        ELSE 5
    END;


-- ============================================================================
-- QUERY 8: Check Database Views Existence
-- ============================================================================
-- Verifica si las vistas necesarias existen en la base de datos
-- Estas vistas son requeridas para los reportes históricos
-- ============================================================================

-- For MySQL:
SELECT
    TABLE_NAME as view_name,
    'EXISTS' as status
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME IN ('scanning_report_daily_unique', 'scanning_report_hourly_full')
  AND TABLE_TYPE = 'VIEW';

-- If this returns 0 rows, you need to create the views from:
-- SQL/scanning_report/scanning_report_daily_unique.sql


-- ============================================================================
-- QUERY 9: Check Historical Data (Daily View)
-- ============================================================================
-- Si la vista existe, verifica datos históricos de transeúntes
-- Esta query puede fallar si la vista no existe
-- ============================================================================

SELECT
    DetectionDate,
    PersonType,
    COUNT(*) as total_persons,
    COUNT(DISTINCT MacAddress) as unique_devices
FROM scanning_report_daily_unique
WHERE DetectionDate >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
GROUP BY DetectionDate, PersonType
ORDER BY DetectionDate DESC, PersonType;


-- ============================================================================
-- QUERY 10: Check Time Distribution (Last 24 Hours)
-- ============================================================================
-- Muestra la distribución de registros por hora
-- Útil para detectar si hay horas sin datos
-- ============================================================================

SELECT
    DATE_FORMAT(CreationTime, '%Y-%m-%d %H:00') as hour,
    COUNT(*) as total_records,
    COUNT(DISTINCT ClientMac) as unique_devices,
    COUNT(CASE WHEN SSID IS NULL OR SSID = '' THEN 1 END) as passersby_records,
    COUNT(CASE WHEN SSID IS NOT NULL AND SSID != '' THEN 1 END) as connected_records
FROM SplashWiFiScanningData
WHERE CreationTime >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
GROUP BY DATE_FORMAT(CreationTime, '%Y-%m-%d %H:00')
ORDER BY hour DESC;


-- ============================================================================
-- QUERY 11: Check Dashboard Network Selection
-- ============================================================================
-- Verifica qué networks están seleccionados en cada dashboard
-- El widget solo muestra datos de networks seleccionados
-- ============================================================================

SELECT
    d.Id as dashboard_id,
    d.Name as dashboard_name,
    d.UserId,
    ng.Id as network_group_id,
    ng.Name as network_group_name,
    n.Id as network_id,
    n.Name as network_name
FROM SplashDashboards d
LEFT JOIN SplashNetworkGroups ng ON FIND_IN_SET(ng.Id, REPLACE(REPLACE(d.SelectedNetworkGroups, '[', ''), ']', ''))
LEFT JOIN SplashNetworkGroupMembers m ON m.NetworkGroupId = ng.Id
LEFT JOIN SplashMerakiNetworks n ON n.Id = m.NetworkId
WHERE d.IsDeleted = 0
ORDER BY d.Name, n.Name;


-- ============================================================================
-- QUERY 12: Check Recent Webhook Activity
-- ============================================================================
-- Muestra los registros más recientes para verificar que el webhook funciona
-- ============================================================================

SELECT
    Id,
    NetworkId,
    ClientMac,
    Manufacturer,
    ManufacturerIsExcluded,
    SSID,
    NearestApRssi,
    NearestApTime,
    CreationTime,
    TIMESTAMPDIFF(MINUTE, CreationTime, NOW()) as minutes_ago
FROM SplashWiFiScanningData
ORDER BY CreationTime DESC
LIMIT 20;


-- ============================================================================
-- QUICK TROUBLESHOOTING CHECKLIST
-- ============================================================================
-- Run these queries in order and follow the actions based on results:
--
-- 1. MASTER DIAGNOSTIC QUERY - Run first to identify the problem
--    → Check 'diagnosis' and 'recommended_action' columns
--
-- 2. If "No hay datos":
--    → Run QUERY 4 (Configuration) - Verify IsEnabled=1 and SyncStatus='Active'
--    → Check Meraki Dashboard webhook configuration
--    → Verify webhook URL matches PostUrl in database
--
-- 3. If "Datos obsoletos":
--    → Check Meraki Dashboard webhook is active
--    → Review application logs for webhook errors
--    → Run QUERY 12 to see last records received
--
-- 4. If "Todos los manufacturers excluidos":
--    → Run QUERY 5 (Excluded Manufacturers)
--    → Review manufacturer exclusion logic
--
-- 5. If "No hay transeúntes detectados":
--    → Run QUERY 6 (SSID Distribution)
--    → Run QUERY 7 (RSSI Distribution)
--    → All devices may be connected to WiFi (have SSID)
--    → Or all devices may have strong signal (RSSI >= -60)
--
-- 6. If "Vista no existe":
--    → Run QUERY 8 to verify
--    → Create views from SQL/scanning_report/ directory
--    → Run database migrations
--
-- 7. If everything looks OK but widget shows 0:
--    → Run QUERY 11 (Dashboard Network Selection)
--    → Verify the correct networks are selected in dashboard filters
--    → Check frontend console for API errors
--    → Verify API endpoint /api/services/app/SplashMetricsService/RealTimeStats
--
-- ============================================================================