<?php
/**
 * SQLite to MySQL Migration Script
 * 
 * STEPS:
 * 1. Create a new MySQL database in cPanel (e.g., mytest_yolo)
 * 2. Update the MySQL connection settings below
 * 3. Upload this file to your server
 * 4. Run it: https://yourdomain.com/migrate-to-mysql.php
 * 5. Update config.php to use MySQL instead of SQLite
 */

ini_set('display_errors', 1);
error_reporting(E_ALL);
set_time_limit(600);

echo "<h1>SQLite to MySQL Migration</h1>";
echo "<pre>";

// ============================================
// CONFIGURATION - UPDATE THESE!
// ============================================
$mysql_host = 'localhost';
$mysql_user = 'mytest_yolo';  // Your MySQL username
$mysql_pass = 'YOUR_MYSQL_PASSWORD';  // Set a strong password
$mysql_db   = 'mytest_yolo';  // Your MySQL database name

$sqlite_db = __DIR__ . '/data/yolo_charters.db';

// ============================================
// START MIGRATION
// ============================================

echo "=== SQLite to MySQL Migration Script ===\n\n";

// Check if SQLite database exists
if (!file_exists($sqlite_db)) {
    die("❌ ERROR: SQLite database not found at: $sqlite_db\n");
}

echo "✅ Found SQLite database\n\n";

// Connect to SQLite
try {
    $sqlite = new SQLite3($sqlite_db);
    echo "✅ Connected to SQLite\n";
} catch (Exception $e) {
    die("❌ ERROR connecting to SQLite: " . $e->getMessage() . "\n");
}

// Connect to MySQL
try {
    $mysql = new PDO(
        "mysql:host=$mysql_host;charset=utf8mb4",
        $mysql_user,
        $mysql_pass,
        [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
    );
    echo "✅ Connected to MySQL\n\n";
} catch (Exception $e) {
    die("❌ ERROR connecting to MySQL: " . $e->getMessage() . "\n");
}

// Create database if it doesn't exist
try {
    $mysql->exec("CREATE DATABASE IF NOT EXISTS `$mysql_db` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
    $mysql->exec("USE `$mysql_db`");
    echo "✅ Database '$mysql_db' ready\n\n";
} catch (Exception $e) {
    die("❌ ERROR creating database: " . $e->getMessage() . "\n");
}

// Get all tables from SQLite
$tables = [];
$result = $sqlite->query("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'");
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
    $tables[] = $row['name'];
}

echo "Found " . count($tables) . " tables to migrate: " . implode(', ', $tables) . "\n\n";

// Table schemas for MySQL
$mysql_schemas = [
    'yachts' => "CREATE TABLE IF NOT EXISTS yachts (
        id INT AUTO_INCREMENT PRIMARY KEY,
        slug VARCHAR(255) NOT NULL UNIQUE,
        name VARCHAR(255) NOT NULL,
        model VARCHAR(255) NOT NULL,
        booking_manager_id VARCHAR(255),
        base_port VARCHAR(255),
        price_from DECIMAL(10,2),
        year_built INT,
        year_refit INT,
        length_ft INT,
        cabins INT,
        bathrooms INT,
        draught_ft DECIMAL(10,2),
        engine_hp INT,
        water_capacity_l DECIMAL(10,2),
        fuel_capacity_l DECIMAL(10,2),
        beam_ft DECIMAL(10,2),
        displacement_kg DECIMAL(10,2),
        main_sail VARCHAR(255),
        genoa VARCHAR(255),
        description TEXT,
        equipment TEXT,
        main_image TEXT,
        gallery TEXT,
        layout_image TEXT,
        location_description TEXT,
        featured TINYINT DEFAULT 0,
        published TINYINT DEFAULT 1,
        sort_order INT DEFAULT 0,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_slug (slug),
        INDEX idx_published (published),
        INDEX idx_booking_manager (booking_manager_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
    
    'users' => "CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(255) NOT NULL UNIQUE,
        password VARCHAR(255) NOT NULL,
        email VARCHAR(255),
        first_name VARCHAR(255),
        last_name VARCHAR(255),
        phone VARCHAR(50),
        role VARCHAR(50) DEFAULT 'guest',
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_username (username),
        INDEX idx_email (email)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
    
    'settings' => "CREATE TABLE IF NOT EXISTS settings (
        id INT AUTO_INCREMENT PRIMARY KEY,
        setting_key VARCHAR(255) NOT NULL UNIQUE,
        setting_value TEXT,
        setting_type VARCHAR(50) DEFAULT 'text',
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_key (setting_key)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
    
    'friend_companies' => "CREATE TABLE IF NOT EXISTS friend_companies (
        id INT AUTO_INCREMENT PRIMARY KEY,
        company_name VARCHAR(255) NOT NULL,
        company_id VARCHAR(255) NOT NULL,
        active TINYINT DEFAULT 1,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_company_id (company_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
    
    'quote_requests' => "CREATE TABLE IF NOT EXISTS quote_requests (
        id INT AUTO_INCREMENT PRIMARY KEY,
        yacht_id VARCHAR(255) NOT NULL,
        yacht_name VARCHAR(255) NOT NULL,
        yacht_model VARCHAR(255),
        pickup_date DATE,
        dropoff_date DATE,
        flexible_dates TINYINT DEFAULT 0,
        first_name VARCHAR(255) NOT NULL,
        last_name VARCHAR(255) NOT NULL,
        email VARCHAR(255) NOT NULL,
        country_code VARCHAR(10) DEFAULT '+30',
        phone VARCHAR(50) NOT NULL,
        message TEXT,
        status VARCHAR(50) DEFAULT 'pending',
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_status (status),
        INDEX idx_created (created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
    
    'contacts' => "CREATE TABLE IF NOT EXISTS contacts (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        email VARCHAR(255) NOT NULL,
        phone VARCHAR(50),
        message TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_created (created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
    
    'posts' => "CREATE TABLE IF NOT EXISTS posts (
        id INT AUTO_INCREMENT PRIMARY KEY,
        slug VARCHAR(255) NOT NULL UNIQUE,
        title VARCHAR(255) NOT NULL,
        content TEXT,
        excerpt TEXT,
        featured_image TEXT,
        published TINYINT DEFAULT 0,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_slug (slug),
        INDEX idx_published (published)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"
];

// Migrate each table
foreach ($tables as $table) {
    echo "=== Migrating table: $table ===\n";
    
    // Create table in MySQL
    if (isset($mysql_schemas[$table])) {
        try {
            $mysql->exec($mysql_schemas[$table]);
            echo "  ✅ Created table structure\n";
        } catch (Exception $e) {
            echo "  ⚠️ Warning: " . $e->getMessage() . "\n";
        }
    } else {
        echo "  ⚠️ No schema defined for $table, skipping...\n\n";
        continue;
    }
    
    // Get all data from SQLite
    $result = $sqlite->query("SELECT * FROM $table");
    $rows = [];
    while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
        $rows[] = $row;
    }
    
    if (empty($rows)) {
        echo "  No data to migrate\n\n";
        continue;
    }
    
    echo "  Found " . count($rows) . " rows\n";
    
    // Get column names
    $firstRow = $rows[0];
    $columns = array_keys($firstRow);
    
    // Remove 'id' from columns for INSERT
    $columnsForInsert = array_filter($columns, function($col) {
        return $col !== 'id';
    });
    
    // Prepare INSERT statement
    $columnList = implode(', ', array_map(function($col) {
        return "`$col`";
    }, $columnsForInsert));
    
    $placeholders = implode(', ', array_fill(0, count($columnsForInsert), '?'));
    
    $insertSQL = "INSERT INTO `$table` ($columnList) VALUES ($placeholders)";
    $stmt = $mysql->prepare($insertSQL);
    
    // Insert each row
    $inserted = 0;
    foreach ($rows as $row) {
        $values = [];
        foreach ($columnsForInsert as $col) {
            $values[] = $row[$col];
        }
        
        try {
            $stmt->execute($values);
            $inserted++;
        } catch (Exception $e) {
            echo "  ⚠️ Error inserting row: " . $e->getMessage() . "\n";
        }
    }
    
    echo "  ✅ Inserted $inserted rows\n\n";
}

echo "\n========================================\n";
echo "✅ MIGRATION COMPLETE!\n";
echo "========================================\n\n";

echo "NEXT STEPS:\n";
echo "1. Update config.php to use MySQL connection\n";
echo "2. Test the website thoroughly\n";
echo "3. Backup the SQLite database\n";
echo "4. Delete this migration script\n\n";

echo "MySQL Connection Details:\n";
echo "Host: $mysql_host\n";
echo "Database: $mysql_db\n";
echo "Username: $mysql_user\n";
echo "Password: [hidden]\n\n";

echo "</pre>";
?>