<?php
/**
 * Script de diagnóstico y corrección de roles
 * Muestra exactamente qué hay en la BD y luego corrige
 */

require_once 'config.php';
require_once 'auth.php';

checkLogin();
if (($_SESSION['role'] ?? '') !== 'admin') {
    die('Solo el administrador puede ejecutar este script');
}

try {
    $pdo = getDBConnection();
    if (!$pdo) {
        die('Error de conexión a la base de datos');
    }

    echo "<h2>Diagnóstico y Corrección de Roles</h2>";
    
    // Primero, mostrar TODOS los usuarios y sus roles exactos
    echo "<h3>Estado actual de TODOS los usuarios:</h3>";
    $stmt = $pdo->query("SELECT id, username, role, ship_id, status, 
                        CASE 
                            WHEN role IS NULL THEN 'NULL'
                            WHEN role = '' THEN 'VACIO'
                            ELSE role
                        END as role_display,
                        LENGTH(role) as role_length,
                        HEX(role) as role_hex
                        FROM users ORDER BY id");
    $allUsers = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    echo "<table border='1' cellpadding='5' style='border-collapse: collapse; margin-bottom: 20px;'>";
    echo "<tr><th>ID</th><th>Usuario</th><th>Rol (raw)</th><th>Rol (display)</th><th>Longitud</th><th>HEX</th><th>Ship ID</th><th>Status</th></tr>";
    foreach ($allUsers as $user) {
        $bgColor = '';
        if ($user['role'] === null || $user['role'] === '' || trim($user['role']) === '') {
            $bgColor = 'background-color: #fff3cd;';
        }
        echo "<tr style='{$bgColor}'>";
        echo "<td>{$user['id']}</td>";
        echo "<td><strong>{$user['username']}</strong></td>";
        echo "<td>" . var_export($user['role'], true) . "</td>";
        echo "<td>{$user['role_display']}</td>";
        echo "<td>{$user['role_length']}</td>";
        echo "<td>{$user['role_hex']}</td>";
        echo "<td>" . ($user['ship_id'] ?? 'NULL') . "</td>";
        echo "<td>{$user['status']}</td>";
        echo "</tr>";
    }
    echo "</table>";
    
    // Intentar actualizar de diferentes formas
    echo "<h3>Intentando corregir...</h3>";
    
    $methods = [
        "Método 1: WHERE role IS NULL" => "UPDATE users SET role = CASE WHEN (ship_id IS NOT NULL AND ship_id != '') THEN 'sub-adm' ELSE 'user' END WHERE role IS NULL AND username != 'admin'",
        "Método 2: WHERE role = ''" => "UPDATE users SET role = CASE WHEN (ship_id IS NOT NULL AND ship_id != '') THEN 'sub-adm' ELSE 'user' END WHERE role = '' AND username != 'admin'",
        "Método 3: WHERE role IS NULL OR role = ''" => "UPDATE users SET role = CASE WHEN (ship_id IS NOT NULL AND ship_id != '') THEN 'sub-adm' ELSE 'user' END WHERE (role IS NULL OR role = '') AND username != 'admin'",
        "Método 4: WHERE COALESCE(role, '') = ''" => "UPDATE users SET role = CASE WHEN (ship_id IS NOT NULL AND ship_id != '') THEN 'sub-adm' ELSE 'user' END WHERE COALESCE(role, '') = '' AND username != 'admin'",
        "Método 5: Actualizar por ID específico" => null // Se hará individualmente
    ];
    
    $totalUpdated = 0;
    
    foreach ($methods as $methodName => $sql) {
        if ($sql === null) {
            // Método 5: Actualizar por ID
            echo "<p><strong>{$methodName}:</strong> ";
            $updated = 0;
            foreach ($allUsers as $user) {
                if ($user['username'] === 'admin') continue;
                if ($user['role'] === null || $user['role'] === '' || trim($user['role']) === '') {
                    $newRole = (!empty($user['ship_id']) && trim($user['ship_id']) !== '') ? 'sub-adm' : 'user';
                    try {
                        $stmt = $pdo->prepare("UPDATE users SET role = :role WHERE id = :id");
                        $stmt->execute([':role' => $newRole, ':id' => $user['id']]);
                        if ($stmt->rowCount() > 0) {
                            $updated++;
                            $totalUpdated++;
                        }
                    } catch (Exception $e) {
                        echo "Error en ID {$user['id']}: " . $e->getMessage() . " ";
                    }
                }
            }
            echo "Actualizados: {$updated}</p>";
        } else {
            echo "<p><strong>{$methodName}:</strong> ";
            try {
                $stmt = $pdo->prepare($sql);
                $stmt->execute();
                $rows = $stmt->rowCount();
                echo "Filas afectadas: {$rows}</p>";
                $totalUpdated += $rows;
            } catch (Exception $e) {
                echo "Error: " . htmlspecialchars($e->getMessage()) . "</p>";
            }
        }
    }
    
    echo "<h3 style='color: " . ($totalUpdated > 0 ? 'green' : 'red') . ";'>Total actualizado: {$totalUpdated} usuarios</h3>";
    
    // Verificar resultados finales
    echo "<h3>Estado final después de la corrección:</h3>";
    $stmt = $pdo->query("SELECT id, username, role, ship_id, status FROM users ORDER BY id");
    $finalUsers = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    echo "<table border='1' cellpadding='5' style='border-collapse: collapse;'>";
    echo "<tr><th>ID</th><th>Usuario</th><th>Rol</th><th>Ship ID</th><th>Status</th></tr>";
    foreach ($finalUsers as $user) {
        $bgColor = '';
        if ($user['role'] === null || $user['role'] === '' || trim($user['role']) === '') {
            $bgColor = 'background-color: #f8d7da;';
        } elseif ($user['role'] === 'sub-adm') {
            $bgColor = 'background-color: #d4edda;';
        }
        echo "<tr style='{$bgColor}'>";
        echo "<td>{$user['id']}</td>";
        echo "<td><strong>{$user['username']}</strong></td>";
        echo "<td>" . ($user['role'] ?? 'NULL') . "</td>";
        echo "<td>" . ($user['ship_id'] ?? 'NULL') . "</td>";
        echo "<td>{$user['status']}</td>";
        echo "</tr>";
    }
    echo "</table>";
    
    // Resumen final
    echo "<h3>Resumen final de roles:</h3>";
    $stmt = $pdo->query("SELECT role, COUNT(*) as count FROM users GROUP BY role ORDER BY role");
    $roles = $stmt->fetchAll(PDO::FETCH_ASSOC);
    echo "<table border='1' cellpadding='5' style='border-collapse: collapse;'>";
    echo "<tr><th>Rol</th><th>Cantidad</th></tr>";
    foreach ($roles as $role) {
        $roleName = $role['role'] ?? 'NULL';
        echo "<tr>";
        echo "<td><strong>{$roleName}</strong></td>";
        echo "<td>{$role['count']}</td>";
        echo "</tr>";
    }
    echo "</table>";
    
    echo "<br><p style='color: red;'><strong>IMPORTANTE:</strong> Elimina este archivo después de ejecutarlo.</p>";
    
} catch (Exception $e) {
    echo "<p style='color: red;'>Error: " . htmlspecialchars($e->getMessage()) . "</p>";
    if (isset($pdo)) {
        echo "<p>Error Info: " . json_encode($pdo->errorInfo()) . "</p>";
    }
}
?>

