-- ============================================================================
-- DIAGNÓSTICO: ¿Por qué "Tasa de Recuperación" está vacía?
-- ============================================================================
-- Este SQL diagnostica por qué el widget no muestra datos

-- Parámetros (ajustar según tu dashboard)
DO $$
DECLARE
    start_date DATE := '2025-01-01';
    end_date DATE := CURRENT_DATE;
BEGIN

RAISE NOTICE '=== DIAGNÓSTICO: Widget Tasa de Recuperación ===';
RAISE NOTICE '';

-- ============================================================================
-- PASO 1: ¿Hay usuarios con is_recovered_user = true?
-- ============================================================================
RAISE NOTICE '--- PASO 1: Usuarios con is_recovered_user = true ---';

SELECT
    COUNT(*) as total_registros,
    COUNT(DISTINCT client_mac) as usuarios_unicos,
    COUNT(DISTINCT CASE WHEN user_id > 0 THEN user_id END) as usuarios_con_cuenta
FROM splash_wifi_connection_report
WHERE is_recovered_user = true
  AND connection_date BETWEEN start_date AND end_date
  AND NOT is_deleted;

-- ============================================================================
-- PASO 2: ¿Cuántos tienen RecoveryConnectionDate?
-- ============================================================================
RAISE NOTICE '';
RAISE NOTICE '--- PASO 2: Usuarios con recovery_connection_date ---';

SELECT
    'Con RecoveryConnectionDate' as categoria,
    COUNT(DISTINCT client_mac) as usuarios_unicos,
    COUNT(DISTINCT CASE WHEN user_id > 0 THEN user_id END) as usuarios_con_cuenta
FROM splash_wifi_connection_report
WHERE is_recovered_user = true
  AND recovery_connection_date IS NOT NULL
  AND connection_date BETWEEN start_date AND end_date
  AND NOT is_deleted

UNION ALL

SELECT
    'SIN RecoveryConnectionDate' as categoria,
    COUNT(DISTINCT client_mac) as usuarios_unicos,
    COUNT(DISTINCT CASE WHEN user_id > 0 THEN user_id END) as usuarios_con_cuenta
FROM splash_wifi_connection_report
WHERE is_recovered_user = true
  AND recovery_connection_date IS NULL
  AND connection_date BETWEEN start_date AND end_date
  AND NOT is_deleted;

-- ============================================================================
-- PASO 3: ¿Cuántos cumplen TODAS las condiciones del widget?
-- ============================================================================
RAISE NOTICE '';
RAISE NOTICE '--- PASO 3: Usuarios que cumplen TODAS las condiciones ---';

-- Condiciones del widget RecoveryRate:
-- 1. user_id > 0
-- 2. is_recovered_user = true
-- 3. recovery_connection_date IS NOT NULL

SELECT
    COUNT(DISTINCT user_id) as usuarios_validos_para_recovery_rate
FROM splash_wifi_connection_report
WHERE user_id > 0
  AND is_recovered_user = true
  AND recovery_connection_date IS NOT NULL
  AND connection_date BETWEEN start_date AND end_date
  AND NOT is_deleted;

-- ============================================================================
-- PASO 4: ¿Hay usuarios inactivos (denominador)?
-- ============================================================================
RAISE NOTICE '';
RAISE NOTICE '--- PASO 4: Usuarios inactivos (más de 30 días) ---';

SELECT
    COUNT(DISTINCT user_id) as total_usuarios_inactivos_30dias
FROM splash_wifi_connection_report
WHERE user_id > 0
  AND days_inactive > 30
  AND connection_date BETWEEN start_date AND end_date
  AND NOT is_deleted;

-- ============================================================================
-- PASO 5: Detalle de los usuarios recuperados (si los hay)
-- ============================================================================
RAISE NOTICE '';
RAISE NOTICE '--- PASO 5: Detalle de usuarios recuperados (Top 10) ---';

SELECT
    user_id,
    client_mac,
    user_name,
    email,
    is_recovered_user,
    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 user_id > 0
  AND is_recovered_user = true
  AND connection_date BETWEEN start_date AND end_date
  AND NOT is_deleted
GROUP BY user_id, client_mac, user_name, email, is_recovered_user, recovery_connection_date, days_inactive
ORDER BY recovery_connection_date DESC NULLS LAST
LIMIT 10;

-- ============================================================================
-- RESUMEN DIAGNÓSTICO
-- ============================================================================
RAISE NOTICE '';
RAISE NOTICE '=== RESUMEN DIAGNÓSTICO ===';
RAISE NOTICE '';
RAISE NOTICE 'El widget "Tasa de Recuperación" requiere:';
RAISE NOTICE '  1. user_id > 0 (usuario con cuenta)';
RAISE NOTICE '  2. is_recovered_user = true';
RAISE NOTICE '  3. recovery_connection_date IS NOT NULL ⚠️ CRÍTICO';
RAISE NOTICE '  4. days_inactive > 30 (para calcular denominador)';
RAISE NOTICE '';
RAISE NOTICE 'Si el resultado del PASO 3 es 0:';
RAISE NOTICE '  → Falta el campo recovery_connection_date';
RAISE NOTICE '  → Solución: Verificar cómo se calcula este campo en la vista';
RAISE NOTICE '';
RAISE NOTICE 'Si el resultado del PASO 4 es 0:';
RAISE NOTICE '  → No hay usuarios inactivos suficientes';
RAISE NOTICE '  → Normal si el período es corto o todos son activos';

END $$;

-- ============================================================================
-- QUERY RÁPIDO DE VERIFICACIÓN
-- ============================================================================
-- Ver un ejemplo de registro recuperado
SELECT
    user_id,
    client_mac,
    is_recovered_user,
    recovery_connection_date,
    days_inactive,
    loyalty_type,
    connection_date,
    connection_date_time
FROM splash_wifi_connection_report
WHERE is_recovered_user = true
  AND NOT is_deleted
ORDER BY connection_date DESC
LIMIT 5;