<?php
require_once 'config.php';

// Simple security check: set a password or use a known API key
if (!isset($_GET['key']) || $_GET['key'] !== 'TEC_MIGRATE_2026') {
    die("Unauthorized access");
}

$pdo = getDBConnection();
if (!$pdo) {
    die("Error connecting to DB");
}

header('Content-Type: text/plain');

try {
    $pdo->beginTransaction();

    echo "Starting migration to BOUCIÑA-01 on LIVE environment...\n\n";

    // 1. Update historical measurements
    $stmt1 = $pdo->prepare("UPDATE mediciones SET buque_id = 'BOUCIÑA-01' WHERE buque_id = 'BUQUE-01'");
    $stmt1->execute();
    echo "1. MEDICIONES: " . $stmt1->rowCount() . " records migrated from BUQUE-01.\n";

    // 2. Unify sensor positions
    // First, clear any placeholder sensors for the new ID to avoid duplicates
    $pdo->prepare("DELETE FROM sensor_positions WHERE buque_id = 'BOUCIÑA-01'")->execute();

    $stmt2 = $pdo->prepare("UPDATE sensor_positions SET buque_id = 'BOUCIÑA-01' WHERE buque_id IN ('BUQUE-01', 'Bouciña', 'BOUCIÑA')");
    $stmt2->execute();
    echo "2. SENSOR_POSITIONS: " . $stmt2->rowCount() . " records updated (placeholders cleared).\n";

    // 3. Unify sensor configuration
    // First, clear any placeholder configs for the new ID to avoid duplicates
    $pdo->prepare("DELETE FROM sensor_temperature_config WHERE ship_id = 'BOUCIÑA-01'")->execute();

    $stmt3 = $pdo->prepare("UPDATE sensor_temperature_config SET ship_id = 'BOUCIÑA-01' WHERE ship_id IN ('BUQUE-01', 'Bouciña', 'BOUCIÑA')");
    $stmt3->execute();
    echo "3. SENSOR_CONFIG: " . $stmt3->rowCount() . " records updated (placeholders cleared).\n";

    // 4. Update legacy ship_id in users table
    $stmt4 = $pdo->prepare("UPDATE users SET ship_id = 'BOUCIÑA-01' WHERE ship_id = 'BUQUE-01'");
    $stmt4->execute();
    echo "4. USERS (legacy col): " . $stmt4->rowCount() . " assignments updated.\n";

    // 5. Update many-to-many user_ships table
    // Handle potential duplicate assignments in user_ships
    $userShips = $pdo->query("SELECT user_id FROM user_ships WHERE ship_id = 'BOUCIÑA-01'")->fetchAll(PDO::FETCH_COLUMN);
    if (!empty($userShips)) {
        $inSet = implode(',', $userShips);
        $pdo->prepare("DELETE FROM user_ships WHERE ship_id = 'BUQUE-01' AND user_id IN ($inSet)")->execute();
    }

    $stmt5 = $pdo->prepare("UPDATE user_ships SET ship_id = 'BOUCIÑA-01' WHERE ship_id = 'BUQUE-01'");
    $stmt5->execute();
    echo "5. USER_SHIPS: " . $stmt5->rowCount() . " assignments updated.\n";

    // 6. Explicitly set Alejandro's default ship if it's null (as seen in dump)
    $stmt6 = $pdo->prepare("UPDATE users SET ship_id = 'BOUCIÑA-01' WHERE username = 'Alejandro' AND ship_id IS NULL");
    $stmt6->execute();
    echo "6. USER ALEJANDRO: " . $stmt6->rowCount() . " default ID set.\n";

    $pdo->commit();
    echo "\nSUCCESS: Migration completed on LIVE database.\n";

} catch (Exception $e) {
    if ($pdo->inTransaction())
        $pdo->rollBack();
    echo "ERROR: Migration failed: " . $e->getMessage() . "\n";
}
