<!DOCTYPE html>
<html><head><title>Diagnóstico Notificaciones</title>
<style>body{font-family:monospace;padding:20px;background:#1e293b;color:#e2e8f0}pre{margin:0}.ok{color:#22c55e}.warn{color:#f59e0b}.err{color:#ef4444}.info{color:#38bdf8}hr{border-color:#334155}</style>
</head><body>
<h2>🔍 Diagnóstico de Notificaciones</h2>
<hr>
<?php
declare(strict_types=1);

require_once __DIR__ . '/../vendor/autoload.php';

use Dotenv\Dotenv;

$dotenv = Dotenv::createImmutable(dirname(__DIR__));
$dotenv->safeLoad();

define('BASE_PATH', dirname(__DIR__));
define('STORAGE_PATH', BASE_PATH . '/storage');
define('APP_ENV', $_ENV['APP_ENV'] ?? 'development');

date_default_timezone_set('America/Lima');

echo "<h3>📋 Configuración</h3>";
echo "<pre>BASE_PATH: <span class='ok'>" . BASE_PATH . "</span></pre>";
echo "<pre>Zona horaria: <span class='info'>" . date_default_timezone_get() . "</span></pre>";
echo "<pre>Hora actual: <span class='info'>" . date('Y-m-d H:i:s') . "</span></pre>";
echo "<pre>Hora +1h: <span class='info'>" . date('Y-m-d H:i:s', strtotime('+1 hour')) . "</span></pre>";

// Mail config
$mailConfig = require BASE_PATH . '/config/mail.php';
echo "<pre>Mail host: <span class='info'>{$mailConfig['host']}:{$mailConfig['port']}</span></pre>";
echo "<pre>Mail user: <span class='info'>{$mailConfig['user']}</span></pre>";
echo "<pre>Mail enc: <span class='info'>{$mailConfig['encryption']}</span></pre>";

echo "<hr><h3>📊 Tareas en Base de Datos</h3>";

try {
    $dbConfig = require BASE_PATH . '/config/database.php';
    $pdo = new PDO(
        "mysql:host={$dbConfig['host']};port={$dbConfig['port']};dbname={$dbConfig['name']};charset={$dbConfig['charset']}",
        $dbConfig['user'], $dbConfig['pass'],
        [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]
    );
    
    // Total tareas
    $total = $pdo->query("SELECT COUNT(*) as c FROM tasks")->fetch();
    $pendientes = $pdo->query("SELECT COUNT(*) as c FROM tasks WHERE status='pendiente'")->fetch();
    echo "<pre>Total tareas: <span class='info'>{$total['c']}</span></pre>";
    echo "<pre>Tareas pendientes: <span class='info'>{$pendientes['c']}</span></pre>";
    
    // Tareas pendientes con detalles
    $tasks = $pdo->query(
        "SELECT t.id, t.scheduled_date, t.scheduled_time, t.status, t.lead_id, t.assigned_to, t.type,
                CONCAT(t.scheduled_date, ' ', IFNULL(t.scheduled_time, '00:00')) as full_date
         FROM tasks t
         WHERE t.status = 'pendiente'
         ORDER BY t.scheduled_date, t.scheduled_time
         LIMIT 20"
    )->fetchAll();
    
    if (count($tasks) === 0) {
        echo "<pre class='warn'>⚠️ No hay tareas pendientes</pre>";
    } else {
        echo "<pre>Últimas tareas pendientes:</pre>";
        foreach ($tasks as $t) {
            $now = date('Y-m-d H:i:s');
            $later = date('Y-m-d H:i:s', strtotime('+1 hour'));
            $inRange = ($t['full_date'] >= $now && $t['full_date'] <= $later);
            $status = $inRange ? "<span class='ok'>✅ EN RANGO</span>" : "<span class='warn'>⏳ Fuera de rango</span>";
            echo "<pre>  ID {$t['id']}: {$t['full_date']} | lead={$t['lead_id']} | tipo={$t['type']} {$status}</pre>";
        }
    }
    
    // Verificar si los JOINs encuentran leads
    echo "<hr><h3>🔗 Verificación de JOINs</h3>";
    $joined = $pdo->query(
        "SELECT COUNT(*) as c FROM tasks t
         JOIN leads l ON t.lead_id = l.id
         JOIN users u ON t.assigned_to = u.id
         WHERE t.status = 'pendiente'"
    )->fetch();
    echo "<pre>Tareas pendientes con lead+user válidos: <span class='info'>{$joined['c']}</span></pre>";
    
    // Tareas con lead_id NULL
    $nullLead = $pdo->query("SELECT COUNT(*) as c FROM tasks WHERE lead_id IS NULL AND status='pendiente'")->fetch();
    if ($nullLead['c'] > 0) {
        echo "<pre class='warn'>⚠️ Tareas sin lead (lead_id IS NULL): {$nullLead['c']}</pre>";
        echo "<pre class='warn'>   Estas NO se notifican porque el JOIN con leads falla</pre>";
    }
    
    // Verificar notificaciones existentes
    echo "<hr><h3>📝 Notificaciones existentes</h3>";
    $notifCount = $pdo->query("SELECT COUNT(*) as c FROM notifications WHERE type='tarea_proxima'")->fetch();
    echo "<pre>Notificaciones 'tarea_proxima' enviadas: <span class='info'>{$notifCount['c']}</span></pre>";
    
    // Configuraciones SMTP en BD
    echo "<hr><h3>⚙️ Configuraciones SMTP en BD</h3>";
    $stmt = $pdo->query("SELECT `key`, `value` FROM configurations WHERE `key` LIKE 'smtp_%'");
    $cfgs = $stmt->fetchAll();
    if (count($cfgs) === 0) {
        echo "<pre class='ok'>✅ No hay configuraciones SMTP en BD. Se usa .env.</pre>";
    } else {
        echo "<pre class='err'>❌ HAY CONFIGURACIONES SMTP EN BD QUE SOBREESCRIBEN .env:</pre>";
        foreach ($cfgs as $c) {
            $val = str_contains($c['key'], 'pass') ? '******' : $c['value'];
            echo "<pre class='err'>  {$c['key']} = {$val}</pre>";
        }
        echo "<pre class='warn'>⚠️ Ejecuta en MySQL: DELETE FROM configurations WHERE `key` LIKE 'smtp_%';</pre>";
    }
    
} catch (Throwable $e) {
    echo "<pre class='err'>❌ Error de BD: " . htmlspecialchars($e->getMessage()) . "</pre>";
}

echo "<hr><p class='info'>Diagnóstico generado: " . date('Y-m-d H:i:s') . "</p>";
?>
</body></html>
