Temp_MSSPLASHPage/SQL/analysis_recovered_users.sql

-- ============================================================================
-- ANÁLISIS DE USUARIOS RECUPERADOS - Comparación de 3 conceptos diferentes
-- ============================================================================
-- Este SQL ayuda a entender las diferencias entre los 3 widgets:
-- 1. Usuarios Recuperados (LoyaltyType = 'Recurrent')
-- 2. Top 5 Usuarios Recuperados (IsRecoveredUser = true)
-- 3. Tasa de Recuperación (usuarios inactivos que regresaron)
-- ============================================================================

-- Parámetros de fechas (ajustar según necesidad)
-- DECLARE @StartDate DATE = '2025-01-01';
-- DECLARE @EndDate DATE = '2025-01-22';
-- Para PostgreSQL usar:
DO $$
DECLARE
    start_date DATE := '2025-01-01';
    end_date DATE := CURRENT_DATE;
BEGIN

-- ============================================================================
-- CONCEPTO 1: LoyaltyType = 'Recurrent' (Widget: Usuarios Recuperados)
-- ============================================================================
RAISE NOTICE '=== CONCEPTO 1: LoyaltyType = Recurrent ===';
RAISE NOTICE 'Usuarios con loyalty_type = Recurrent en el período';

SELECT
    'LoyaltyType Recurrent' as concepto,
    COUNT(DISTINCT client_mac) as total_usuarios_unicos,
    COUNT(*) as total_conexiones,
    MIN(connection_date) as primera_fecha,
    MAX(connection_date) as ultima_fecha
FROM splash_wifi_connection_report
WHERE loyalty_type = 'Recurrent'
  AND connection_date BETWEEN start_date AND end_date
  AND NOT is_deleted;

-- Detalle de usuarios Recurrent
SELECT
    client_mac,
    user_name,
    email,
    loyalty_type,
    COUNT(*) as total_conexiones,
    MIN(connection_date) as primera_conexion,
    MAX(connection_date) as ultima_conexion,
    MAX(connection_date_time) as ultima_conexion_datetime
FROM splash_wifi_connection_report
WHERE loyalty_type = 'Recurrent'
  AND connection_date BETWEEN start_date AND end_date
  AND NOT is_deleted
GROUP BY client_mac, user_name, email, loyalty_type
ORDER BY total_conexiones DESC
LIMIT 10;

-- ============================================================================
-- CONCEPTO 2: IsRecoveredUser = true (Widget: Top 5 Usuarios Recuperados)
-- ============================================================================
RAISE NOTICE '=== CONCEPTO 2: IsRecoveredUser = true ===';
RAISE NOTICE 'Usuarios marcados explícitamente como recuperados';

SELECT
    'IsRecoveredUser = true' as concepto,
    COUNT(DISTINCT client_mac) as total_usuarios_unicos,
    COUNT(*) as total_conexiones,
    COUNT(DISTINCT CASE WHEN user_id > 0 THEN user_id END) as usuarios_con_id
FROM splash_wifi_connection_report
WHERE is_recovered_user = true
  AND connection_date BETWEEN start_date AND end_date
  AND NOT is_deleted;

-- Detalle de usuarios recuperados
SELECT
    user_id,
    client_mac,
    user_name,
    email,
    loyalty_type,
    is_recovered_user,
    COUNT(*) as total_conexiones,
    MIN(connection_date) as primera_conexion,
    MAX(connection_date) as ultima_conexion,
    days_inactive
FROM splash_wifi_connection_report
WHERE is_recovered_user = true
  AND connection_date BETWEEN start_date AND end_date
  AND NOT is_deleted
  AND user_id > 0  -- El widget TopRetrievedUsers filtra por user_id > 0
GROUP BY user_id, client_mac, user_name, email, loyalty_type, is_recovered_user, days_inactive
ORDER BY total_conexiones DESC
LIMIT 10;

-- ============================================================================
-- CONCEPTO 3: Recovery Rate (usuarios inactivos que regresaron)
-- ============================================================================
RAISE NOTICE '=== CONCEPTO 3: Recovery Rate - Usuarios Inactivos que Regresaron ===';
RAISE NOTICE 'Lógica compleja: usuarios que estuvieron inactivos y luego regresaron';

-- Usuarios que tienen recovery_connection_date (fecha de recuperación)
SELECT
    'Usuarios con recovery_connection_date' as concepto,
    COUNT(DISTINCT client_mac) as total_usuarios_unicos,
    COUNT(*) as total_conexiones,
    AVG(days_inactive) as promedio_dias_inactivos
FROM splash_wifi_connection_report
WHERE recovery_connection_date IS NOT NULL
  AND connection_date BETWEEN start_date AND end_date
  AND NOT is_deleted;

-- Detalle de usuarios con recovery_connection_date
SELECT
    client_mac,
    user_name,
    email,
    loyalty_type,
    recovery_connection_date,
    days_inactive,
    COUNT(*) as total_conexiones,
    MIN(connection_date) as primera_conexion,
    MAX(connection_date) as ultima_conexion
FROM splash_wifi_connection_report
WHERE recovery_connection_date IS NOT NULL
  AND connection_date BETWEEN start_date AND end_date
  AND NOT is_deleted
GROUP BY client_mac, user_name, email, loyalty_type, recovery_connection_date, days_inactive
ORDER BY days_inactive DESC
LIMIT 10;

-- ============================================================================
-- RESUMEN COMPARATIVO DE LOS 3 CONCEPTOS
-- ============================================================================
RAISE NOTICE '=== RESUMEN COMPARATIVO ===';

SELECT
    'LoyaltyType Recurrent' as concepto,
    COUNT(DISTINCT client_mac) as usuarios_unicos,
    COUNT(*) as total_conexiones
FROM splash_wifi_connection_report
WHERE loyalty_type = 'Recurrent'
  AND connection_date BETWEEN start_date AND end_date
  AND NOT is_deleted

UNION ALL

SELECT
    'IsRecoveredUser = true' as concepto,
    COUNT(DISTINCT client_mac) as usuarios_unicos,
    COUNT(*) as total_conexiones
FROM splash_wifi_connection_report
WHERE is_recovered_user = true
  AND connection_date BETWEEN start_date AND end_date
  AND NOT is_deleted

UNION ALL

SELECT
    'Recovery Date NOT NULL' as concepto,
    COUNT(DISTINCT client_mac) as usuarios_unicos,
    COUNT(*) as total_conexiones
FROM splash_wifi_connection_report
WHERE recovery_connection_date IS NOT NULL
  AND connection_date BETWEEN start_date AND end_date
  AND NOT is_deleted;

-- ============================================================================
-- INTERSECCIÓN: ¿Cuántos usuarios cumplen MÚLTIPLES conceptos?
-- ============================================================================
RAISE NOTICE '=== INTERSECCIÓN DE CONCEPTOS ===';

SELECT
    CASE
        WHEN loyalty_type = 'Recurrent' AND is_recovered_user = true AND recovery_connection_date IS NOT NULL
            THEN 'Los 3 conceptos'
        WHEN loyalty_type = 'Recurrent' AND is_recovered_user = true
            THEN 'Recurrent + IsRecovered'
        WHEN loyalty_type = 'Recurrent' AND recovery_connection_date IS NOT NULL
            THEN 'Recurrent + RecoveryDate'
        WHEN is_recovered_user = true AND recovery_connection_date IS NOT NULL
            THEN 'IsRecovered + RecoveryDate'
        WHEN loyalty_type = 'Recurrent'
            THEN 'Solo Recurrent'
        WHEN is_recovered_user = true
            THEN 'Solo IsRecovered'
        WHEN recovery_connection_date IS NOT NULL
            THEN 'Solo RecoveryDate'
        ELSE 'Ninguno'
    END as categoria,
    COUNT(DISTINCT client_mac) as usuarios_unicos,
    COUNT(*) as total_conexiones
FROM splash_wifi_connection_report
WHERE connection_date BETWEEN start_date AND end_date
  AND NOT is_deleted
  AND (
      loyalty_type = 'Recurrent'
      OR is_recovered_user = true
      OR recovery_connection_date IS NOT NULL
  )
GROUP BY categoria
ORDER BY usuarios_unicos DESC;

-- ============================================================================
-- RECOMENDACIÓN: ¿Qué campo usar para cada widget?
-- ============================================================================
RAISE NOTICE '=== RECOMENDACIONES ===';
RAISE NOTICE '';
RAISE NOTICE '1. Widget "Usuarios Recuperados" (contador simple):';
RAISE NOTICE '   → Usar: loyalty_type = ''Recurrent''';
RAISE NOTICE '   → Motivo: Campo estándar de clasificación de usuarios';
RAISE NOTICE '';
RAISE NOTICE '2. Widget "Top 5 Usuarios Recuperados" (ranking):';
RAISE NOTICE '   → Usar: is_recovered_user = true AND user_id > 0';
RAISE NOTICE '   → Motivo: Marca explícita + necesita user_id para agrupar';
RAISE NOTICE '';
RAISE NOTICE '3. Widget "Tasa de Recuperación" (métrica compleja):';
RAISE NOTICE '   → Usar: recovery_connection_date IS NOT NULL';
RAISE NOTICE '   → Motivo: Calcula % de usuarios inactivos que regresaron';

END $$;

-- ============================================================================
-- QUERY RÁPIDO PARA DEBUGGING
-- ============================================================================
-- Ver ejemplo de un usuario que cumple múltiples conceptos
SELECT
    client_mac,
    user_name,
    email,
    loyalty_type,
    is_recovered_user,
    recovery_connection_date,
    days_inactive,
    connection_date,
    connection_date_time
FROM splash_wifi_connection_report
WHERE (loyalty_type = 'Recurrent'
       OR is_recovered_user = true
       OR recovery_connection_date IS NOT NULL)
  AND NOT is_deleted
ORDER BY connection_date DESC
LIMIT 20;