<?php
require_once 'database/connection.php';

echo "<h2>Verificación de estructura de base de datos</h2>";

// 1. Verificar si las tablas existen
echo "<h3>1. Verificar existencia de tablas:</h3>";
$tables = ['leads', 'users', 'desks'];
foreach ($tables as $table) {
    try {
        $result = $db->select("SHOW TABLES LIKE '$table'");
        if ($result) {
            echo "✅ Tabla '$table' existe<br>";
        } else {
            echo "❌ Tabla '$table' NO existe<br>";
        }
    } catch (Exception $e) {
        echo "❌ Error verificando tabla '$table': " . $e->getMessage() . "<br>";
    }
}

// 2. Verificar columnas específicas
echo "<h3>2. Verificar columnas en tabla leads:</h3>";
try {
    $columns = $db->select("SHOW COLUMNS FROM leads");
    $column_names = array_column($columns, 'Field');
    
    $required_columns = ['id', 'assigned_to', 'desk_id'];
    foreach ($required_columns as $col) {
        if (in_array($col, $column_names)) {
            echo "✅ Columna 'leads.$col' existe<br>";
        } else {
            echo "❌ Columna 'leads.$col' NO existe<br>";
        }
    }
    
    echo "<br>Todas las columnas de leads:<br>";
    foreach ($columns as $col) {
        echo "- " . $col['Field'] . " (" . $col['Type'] . ")<br>";
    }
} catch (Exception $e) {
    echo "❌ Error verificando columnas de leads: " . $e->getMessage() . "<br>";
}

// 3. Verificar columnas en tabla users
echo "<h3>3. Verificar columnas en tabla users:</h3>";
try {
    $columns = $db->select("SHOW COLUMNS FROM users");
    $column_names = array_column($columns, 'Field');
    
    $required_columns = ['id', 'username', 'first_name', 'last_name'];
    foreach ($required_columns as $col) {
        if (in_array($col, $column_names)) {
            echo "✅ Columna 'users.$col' existe<br>";
        } else {
            echo "❌ Columna 'users.$col' NO existe<br>";
        }
    }
} catch (Exception $e) {
    echo "❌ Error verificando columnas de users: " . $e->getMessage() . "<br>";
}

// 4. Verificar columnas en tabla desks
echo "<h3>4. Verificar columnas en tabla desks:</h3>";
try {
    $columns = $db->select("SHOW COLUMNS FROM desks");
    $column_names = array_column($columns, 'Field');
    
    $required_columns = ['id', 'name', 'color'];
    foreach ($required_columns as $col) {
        if (in_array($col, $column_names)) {
            echo "✅ Columna 'desks.$col' existe<br>";
        } else {
            echo "❌ Columna 'desks.$col' NO existe<br>";
        }
    }
} catch (Exception $e) {
    echo "❌ Error verificando columnas de desks: " . $e->getMessage() . "<br>";
}

// 5. Probar la consulta JOIN manualmente
echo "<h3>5. Probar consulta JOIN manualmente:</h3>";
try {
    $query = "SELECT l.*, 
                     l.job_title AS position,
                     u.username AS assigned_user,
                     CONCAT(u.first_name, ' ', u.last_name) AS assigned_name,
                     d.name AS desk_name,
                     d.color AS desk_color
              FROM leads l
              LEFT JOIN users u ON l.assigned_to = u.id
              LEFT JOIN desks d ON l.desk_id = d.id
              WHERE l.id = 10292";
    
    $result = $db->select($query);
    if ($result) {
        echo "✅ Consulta JOIN ejecutada exitosamente<br>";
        echo "<pre>";
        print_r($result[0]);
        echo "</pre>";
    } else {
        echo "❌ Consulta JOIN no devolvió resultados<br>";
    }
} catch (Exception $e) {
    echo "❌ Error en consulta JOIN: " . $e->getMessage() . "<br>";
    echo "Detalles del error:<br>";
    echo "<pre>" . $e->getTraceAsString() . "</pre>";
}

// 6. Verificar datos específicos del lead 10292
echo "<h3>6. Datos específicos del lead 10292:</h3>";
try {
    $lead = $db->select("SELECT assigned_to, desk_id FROM leads WHERE id = 10292");
    if ($lead) {
        echo "assigned_to: " . ($lead[0]['assigned_to'] ?? 'NULL') . "<br>";
        echo "desk_id: " . ($lead[0]['desk_id'] ?? 'NULL') . "<br>";
        
        // Verificar si el usuario existe
        if ($lead[0]['assigned_to']) {
            $user = $db->select("SELECT username, first_name, last_name FROM users WHERE id = ?", [$lead[0]['assigned_to']]);
            if ($user) {
                echo "Usuario asignado encontrado: " . $user[0]['first_name'] . " " . $user[0]['last_name'] . "<br>";
            } else {
                echo "❌ Usuario con ID " . $lead[0]['assigned_to'] . " no encontrado<br>";
            }
        }
        
        // Verificar si el desk existe
        if ($lead[0]['desk_id']) {
            $desk = $db->select("SELECT name FROM desks WHERE id = ?", [$lead[0]['desk_id']]);
            if ($desk) {
                echo "Desk asignado encontrado: " . $desk[0]['name'] . "<br>";
            } else {
                echo "❌ Desk con ID " . $lead[0]['desk_id'] . " no encontrado<br>";
            }
        }
    }
} catch (Exception $e) {
    echo "❌ Error verificando datos del lead: " . $e->getMessage() . "<br>";
}
?>