<?php
require_once 'auth.php';
require_once 'config.php';

// Only allow admin to run the migration
if (($_SESSION['role'] ?? '') !== 'admin') {
    die("No autorizado. Debes iniciar sesión como administrador.");
}

try {
    $pdo = getDBConnection();
    if (!$pdo) {
        die("Error de conexión a la base de datos.");
    }
    echo "Conectado exitosamente a la base de datos.\n";

    // Check if columns exist before adding
    $stmt = $pdo->query("SHOW COLUMNS FROM users");
    $columns = $stmt->fetchAll(PDO::FETCH_COLUMN);

    if (!in_array('legal_name', $columns)) {
        $pdo->exec("ALTER TABLE users ADD COLUMN legal_name VARCHAR(255) DEFAULT NULL");
        echo "Added legal_name\n";
    }
    if (!in_array('address', $columns)) {
        $pdo->exec("ALTER TABLE users ADD COLUMN address VARCHAR(255) DEFAULT NULL");
        echo "Added address\n";
    }
    if (!in_array('responsible_name', $columns)) {
        $pdo->exec("ALTER TABLE users ADD COLUMN responsible_name VARCHAR(100) DEFAULT NULL");
        echo "Added responsible_name\n";
    }
    if (!in_array('cuit', $columns)) {
        $pdo->exec("ALTER TABLE users ADD COLUMN cuit VARCHAR(20) DEFAULT NULL");
        echo "Added cuit\n";
    }

    // Create user_ships mapping table
    $pdo->exec("CREATE TABLE IF NOT EXISTS user_ships (
        user_id INT NOT NULL,
        ship_id VARCHAR(50) NOT NULL,
        PRIMARY KEY (user_id, ship_id),
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (ship_id) REFERENCES ships(ship_id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci");
    echo "Created user_ships table\n";

    // Migrate existing assignments
    $pdo->exec("INSERT IGNORE INTO user_ships (user_id, ship_id) 
                SELECT id, ship_id FROM users WHERE ship_id IS NOT NULL AND ship_id != ''");
    echo "Migrated existing user assignments\n";

    echo "Migration successful\n";
} catch (Exception $e) {
    echo "Migration failed: " . $e->getMessage() . "\n";
}
