                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
<?php
require_once __DIR__ . '/../config/config.php';

// Check if running in CLI or browser
$is_cli = (php_sapi_name() === 'cli');

if (!$is_cli) {
    echo "<pre>";
}

echo "Starting database updates...\n";

// Helper to check if a column exists
function columnExists($conn, $table, $column) {
    $result = $conn->query("SHOW COLUMNS FROM `$table` LIKE '$column'");
    return $result && $result->num_rows > 0;
}

// Alter bookings table
$table = 'bookings';
$columns_to_add = [
    'package_price' => "DECIMAL(12,2) DEFAULT 0.00 AFTER inspiration_image",
    'transport_cost' => "DECIMAL(12,2) DEFAULT 0.00 AFTER package_price",
    'distance' => "DECIMAL(8,2) DEFAULT 0.00 AFTER transport_cost",
    'latitude' => "DECIMAL(10,8) NULL AFTER distance",
    'longitude' => "DECIMAL(11,8) NULL AFTER latitude"
];

foreach ($columns_to_add as $col => $definition) {
    if (!columnExists($conn, $table, $col)) {
        $sql = "ALTER TABLE `$table` ADD COLUMN `$col` $definition";
        if ($conn->query($sql)) {
            echo "Added column `$col` to `$table` successfully.\n";
        } else {
            echo "Error adding column `$col`: " . $conn->error . "\n";
        }
    } else {
        echo "Column `$col` already exists in `$table`.\n";
    }
}

// Insert default settings if they don't exist
$settings = [
    'office_latitude' => '6.9271',
    'office_longitude' => '79.8612',
    'transport_cost_per_km' => '100.00'
];

foreach ($settings as $key => $val) {
    $stmt = $conn->prepare("INSERT INTO settings (setting_key, setting_value) VALUES (?, ?) ON DUPLICATE KEY UPDATE setting_value = IF(setting_value IS NULL OR setting_value = '', ?, setting_value)");
    $stmt->bind_param("sss", $key, $val, $val);
    if ($stmt->execute()) {
        echo "Setting `$key` initialized to `$val`.\n";
    } else {
        echo "Error setting `$key`: " . $stmt->error . "\n";
    }
}

echo "Database updates completed.\n";

if (!$is_cli) {
    echo "</pre>";
}
?>