<?php
/**
 * RECONCILE_V5.PHP - Versión Definitiva
 * Basada en el SCHEMA real verificado por check_columns.php
 */

require_once 'config.php';

// Simple security check
if (!isset($_GET['key']) || $_GET['key'] !== 'TEC_V5_RECONCILE') {
    die("Error: Key de seguridad inválida.");
}

$pdo = getDBConnection();
if (!$pdo)
    die("Error: No se pudo conectar a la base de datos.");

header('Content-Type: text/html; charset=utf-8');

echo "<html><body style='font-family: monospace; background: #050a10; color: #fff; padding: 20px;'>";
echo "<h2>--- RECONCILIACIÓN DE DATOS V5 ---</h2>";

try {
    $pdo->beginTransaction();

    $targetId = 'BOUCIÑA-01';
    $oldIds = ['BUQUE-01', 'Bouciña', 'BOUCIÑA', 'Artica', 'BRICEL', 'Buque 02', 'Buque-02', 'FRIGORIFICO-01'];

    // --- PASO 1: TABLA `ships` (Usa ship_id) ---
    echo "<p>1. Actualizando nombre en tabla <b>ships</b> (columna ship_id)... ";
    $stmt = $pdo->prepare("UPDATE ships SET name = :name WHERE ship_id = :target");
    $stmt->execute([':name' => 'BOUCIÑA-01', ':target' => $targetId]);
    echo "OK (" . $stmt->rowCount() . " filas)</p>";

    // --- PASO 2: TABLA `mediciones` (Usa buque_id) ---
    echo "<p>2. Migrando <b>mediciones</b> (columna buque_id)... ";
    $totalMed = 0;
    foreach ($oldIds as $old) {
        $stmt = $pdo->prepare("UPDATE mediciones SET buque_id = :target WHERE buque_id = :old");
        $stmt->execute([':target' => $targetId, ':old' => $old]);
        $totalMed += $stmt->rowCount();
    }
    echo "OK ($totalMed filas)</p>";

    // --- PASO 3: TABLA `sensor_positions` (Usa buque_id) ---
    echo "<p>3. Limpiando <b>sensor_positions</b> (columna buque_id)... ";
    $stmt = $pdo->prepare("DELETE FROM sensor_positions WHERE buque_id IN ('" . implode("','", $oldIds) . "')");
    $stmt->execute();
    echo "OK (" . $stmt->rowCount() . " eliminadas)</p>";

    // --- PASO 4: TABLA `sensor_temperature_config` (Usa buque_id) ---
    echo "<p>4. Limpiando <b>sensor_temperature_config</b> (columna buque_id)... ";
    $stmt = $pdo->prepare("DELETE FROM sensor_temperature_config WHERE buque_id IN ('" . implode("','", $oldIds) . "')");
    $stmt->execute();
    echo "OK (" . $stmt->rowCount() . " eliminadas)</p>";

    // --- PASO 5: TABLA `users` (Usa ship_id) ---
    echo "<p>5. Actualizando tabla <b>users</b> (columna ship_id)... ";
    $stmt = $pdo->prepare("UPDATE users SET ship_id = :target WHERE ship_id IN ('" . implode("','", $oldIds) . "') OR (username = 'Alejandro' AND ship_id IS NULL)");
    $stmt->execute([':target' => $targetId]);
    echo "OK (" . $stmt->rowCount() . " usuarios)</p>";

    // --- PASO 6: TABLA `user_ships` (Usa ship_id) ---
    echo "<p>6. Actualizando tabla <b>user_ships</b> (columna ship_id)... ";
    $stmt = $pdo->prepare("UPDATE user_ships SET ship_id = :target WHERE ship_id IN ('" . implode("','", $oldIds) . "')");
    $stmt->execute([':target' => $targetId]);
    echo "OK (" . $stmt->rowCount() . " asignaciones)</p>";

    // --- PASO 7: LIMPIEZA FINAL DE RESIDUOS ---
    echo "<p>7. Limpieza final de configuraciones no activas... ";
    $activeShips = ['BOUCIÑA-01', 'Heladera-01'];
    $placeholders = implode(',', array_fill(0, count($activeShips), '?'));

    $stmt1 = $pdo->prepare("DELETE FROM sensor_positions WHERE buque_id NOT IN ($placeholders)");
    $stmt1->execute($activeShips);

    $stmt2 = $pdo->prepare("DELETE FROM sensor_temperature_config WHERE buque_id NOT IN ($placeholders)");
    $stmt2->execute($activeShips);
    echo "OK</p>";

    $pdo->commit();
    echo "<h3 style='color: #00ffcc;'>¡ÉXITO TOTAL! Los datos han sido unificados.</h3>";
    echo "<p><b>IMPORTANTE:</b> Debes cerrar sesión y volver a entrar para que los cambios surtan efecto en tu usuario.</p>";

} catch (Exception $e) {
    if ($pdo->inTransaction())
        $pdo->rollBack();
    echo "<p style='color: #ff4d4d;'><b>ERROR:</b> " . $e->getMessage() . "</p>";
}

echo "</body></html>";
?>