<?php
/**
 * Database Fix Script
 * Upload this to your server and run it via browser: https://yourdomain.com/fix-database.php
 * This will:
 * 1. Add missing location_description column
 * 2. Import all friend's boats
 */

ini_set('display_errors', 1);
error_reporting(E_ALL);
set_time_limit(300); // 5 minutes

echo "<h1>YOLO Charters - Database Fix Script</h1>";
echo "<pre>";

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

if (!file_exists($dbPath)) {
    die("❌ ERROR: Database not found at: $dbPath\n");
}

echo "✅ Found database at: $dbPath\n\n";

// Connect to database
try {
    $db = new SQLite3($dbPath);
    echo "✅ Connected to database\n\n";
} catch (Exception $e) {
    die("❌ ERROR: Could not connect to database: " . $e->getMessage() . "\n");
}

// Step 1: Check if location_description column exists
echo "=== STEP 1: Checking location_description column ===\n";
$result = $db->query("PRAGMA table_info(yachts)");
$hasLocationDescription = false;
$columns = [];

while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
    $columns[] = $row['name'];
    if ($row['name'] === 'location_description') {
        $hasLocationDescription = true;
    }
}

echo "Current columns in yachts table: " . implode(', ', $columns) . "\n\n";

if (!$hasLocationDescription) {
    echo "Adding location_description column...\n";
    try {
        $db->exec("ALTER TABLE yachts ADD COLUMN location_description TEXT");
        echo "✅ location_description column added successfully!\n\n";
    } catch (Exception $e) {
        echo "⚠️  Warning: Could not add column (might already exist): " . $e->getMessage() . "\n\n";
    }
} else {
    echo "✅ location_description column already exists\n\n";
}

// Step 2: Check for setting_type column in settings table
echo "=== STEP 2: Checking settings table ===\n";
$result = $db->query("PRAGMA table_info(settings)");
$hasSettingType = false;
$settingsColumns = [];

while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
    $settingsColumns[] = $row['name'];
    if ($row['name'] === 'setting_type') {
        $hasSettingType = true;
    }
}

echo "Current columns in settings table: " . implode(', ', $settingsColumns) . "\n\n";

if (!$hasSettingType) {
    echo "Adding setting_type column...\n";
    try {
        $db->exec("ALTER TABLE settings ADD COLUMN setting_type TEXT DEFAULT 'text'");
        echo "✅ setting_type column added successfully!\n\n";
    } catch (Exception $e) {
        echo "⚠️  Warning: Could not add column (might already exist): " . $e->getMessage() . "\n\n";
    }
} else {
    echo "✅ setting_type column already exists\n\n";
}

// Step 3: Import Friend's Boats
echo "=== STEP 3: Importing Friend's Boats ===\n";

// Include required files
require_once __DIR__ . '/database/Database.php';
require_once __DIR__ . '/api/services/BookingManagerService.php';

try {
    $database = Database::getInstance()->getConnection();
    $bookingManager = new BookingManagerService();
    
    // Get friend companies
    $result = $database->query("SELECT company_id, company_name FROM friend_companies WHERE active = 1");
    $friendCompanies = [];
    while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
        $friendCompanies[] = $row;
    }
    
    echo "Found " . count($friendCompanies) . " friend companies\n\n";
    
    $imported = 0;
    $updated = 0;
    $skipped = 0;
    
    foreach ($friendCompanies as $company) {
        echo "Processing: {$company['company_name']} (ID: {$company['company_id']})\n";
        
        // Get all yachts from this company
        $yachts = $bookingManager->getYachts(['companyId' => $company['company_id']]);
        
        if (isset($yachts['error'])) {
            echo "  ❌ Error: " . json_encode($yachts['error']) . "\n";
            continue;
        }
        
        if (!is_array($yachts)) {
            echo "  No yachts found\n";
            continue;
        }
        
        echo "  Found " . count($yachts) . " yachts\n";
        
        foreach ($yachts as $yacht) {
            $name = $yacht['name'] ?? 'Unknown';
            $model = $yacht['model'] ?? '';
            $yachtId = $yacht['id'] ?? null;
            
            if (!$yachtId) {
                echo "    ⚠️  Skipping yacht with no ID: $name\n";
                $skipped++;
                continue;
            }
            
            // Generate slug
            $slug = strtolower(trim(preg_replace('/[^A-Za-z0-9-]+/', '-', $name . '-' . $model)));
            $slug = preg_replace('/-+/', '-', $slug);
            $slug = trim($slug, '-');
            
            // Check if yacht already exists
            $stmt = $database->prepare("SELECT id FROM yachts WHERE booking_manager_id = ?");
            $stmt->bindValue(1, $yachtId, SQLITE3_TEXT);
            $existing = $stmt->execute()->fetchArray(SQLITE3_ASSOC);
            
            // Prepare data
            $length = isset($yacht['length']) ? floatval($yacht['length']) : (isset($yacht['LOA']) ? floatval($yacht['LOA']) : 0);
            $lengthFeet = $length > 0 ? round($length * 3.28084) : 0;
            $cabins = $yacht['cabins'] ?? 0;
            $bathrooms = $yacht['wc'] ?? $yacht['bathrooms'] ?? 0;
            $year = $yacht['year'] ?? 0;
            $mainImage = $yacht['mainImage'] ?? '';
            $homePort = $yacht['homeBase'] ?? '';
            $berths = $yacht['berths'] ?? 0;
            
            // Create description
            $description = "<p>{$name} is a {$year} {$model} available for charter.</p>";
            $description .= "<p>This yacht features {$cabins} cabins and {$bathrooms} bathrooms, and can accommodate up to {$berths} guests.</p>";
            if ($lengthFeet > 0) {
                $description .= "<p>With a length of {$lengthFeet} feet, this vessel offers ample space and comfort.</p>";
            }
            
            // Prepare gallery JSON
            $galleryImages = [];
            if (isset($yacht['images']) && is_array($yacht['images'])) {
                foreach ($yacht['images'] as $img) {
                    if (isset($img['url'])) {
                        $galleryImages[] = $img['url'];
                    }
                }
            }
            $gallery = json_encode($galleryImages);
            
            if ($existing) {
                echo "    📝 Updating: $name ($model) - $slug\n";
                
                $stmt = $database->prepare("
                    UPDATE yachts SET
                        name = ?,
                        slug = ?,
                        model = ?,
                        length_ft = ?,
                        cabins = ?,
                        bathrooms = ?,
                        year_built = ?,
                        main_image = ?,
                        base_port = ?,
                        description = ?,
                        gallery = ?,
                        updated_at = datetime('now')
                    WHERE booking_manager_id = ?
                ");
                
                $stmt->bindValue(1, $name, SQLITE3_TEXT);
                $stmt->bindValue(2, $slug, SQLITE3_TEXT);
                $stmt->bindValue(3, $model, SQLITE3_TEXT);
                $stmt->bindValue(4, $lengthFeet, SQLITE3_INTEGER);
                $stmt->bindValue(5, $cabins, SQLITE3_INTEGER);
                $stmt->bindValue(6, $bathrooms, SQLITE3_INTEGER);
                $stmt->bindValue(7, $year, SQLITE3_INTEGER);
                $stmt->bindValue(8, $mainImage, SQLITE3_TEXT);
                $stmt->bindValue(9, $homePort, SQLITE3_TEXT);
                $stmt->bindValue(10, $description, SQLITE3_TEXT);
                $stmt->bindValue(11, $gallery, SQLITE3_TEXT);
                $stmt->bindValue(12, $yachtId, SQLITE3_TEXT);
                
                $stmt->execute();
                $updated++;
            } else {
                echo "    ➕ Importing: $name ($model) - $slug\n";
                
                $stmt = $database->prepare("
                    INSERT INTO yachts (
                        name, slug, model, length_ft, cabins, bathrooms, year_built,
                        main_image, base_port, description, gallery,
                        booking_manager_id,
                        published, created_at, updated_at
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1, datetime('now'), datetime('now'))
                ");
                
                $stmt->bindValue(1, $name, SQLITE3_TEXT);
                $stmt->bindValue(2, $slug, SQLITE3_TEXT);
                $stmt->bindValue(3, $model, SQLITE3_TEXT);
                $stmt->bindValue(4, $lengthFeet, SQLITE3_INTEGER);
                $stmt->bindValue(5, $cabins, SQLITE3_INTEGER);
                $stmt->bindValue(6, $bathrooms, SQLITE3_INTEGER);
                $stmt->bindValue(7, $year, SQLITE3_INTEGER);
                $stmt->bindValue(8, $mainImage, SQLITE3_TEXT);
                $stmt->bindValue(9, $homePort, SQLITE3_TEXT);
                $stmt->bindValue(10, $description, SQLITE3_TEXT);
                $stmt->bindValue(11, $gallery, SQLITE3_TEXT);
                $stmt->bindValue(12, $yachtId, SQLITE3_TEXT);
                
                $stmt->execute();
                $imported++;
            }
        }
        
        echo "\n";
    }
    
    echo "\n========================================\n";
    echo "✅ Import Complete!\n";
    echo "  ➕ Imported: $imported new yachts\n";
    echo "  📝 Updated: $updated existing yachts\n";
    echo "  ⚠️  Skipped: $skipped yachts\n";
    echo "========================================\n\n";
    
} catch (Exception $e) {
    echo "❌ ERROR during import: " . $e->getMessage() . "\n";
    echo "Stack trace:\n" . $e->getTraceAsString() . "\n";
}

echo "\n=== DONE! ===\n";
echo "Your database has been updated successfully.\n";
echo "You can now delete this file for security.\n\n";

echo "</pre>";
echo "<p><strong>✅ ALL DONE! Delete this file now: fix-database.php</strong></p>";
?>