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

echo "<h2>Database Migration</h2>";

// 1. Local Tours Table
$sql1 = "CREATE TABLE IF NOT EXISTS local_tours (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    location VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    main_image VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";

if ($conn->query($sql1)) {
    echo "<p style='color: green;'>✓ Table 'local_tours' created successfully or already exists.</p>";
    
    // Seed if empty
    $check = $conn->query("SELECT id FROM local_tours LIMIT 1");
    if ($check->num_rows == 0) {
        $local_tours = [
            ['Sigiriya Rock Fortress', 'Matale District', 'The 8th Wonder of the World, an ancient rock fortress and palace built by King Kashyapa.', 7500.00],
            ['Polonnaruwa Ancient City', 'North Central Province', 'The second capital of Sri Lanka, a UNESCO World Heritage site with remarkably preserved ruins.', 6000.00],
            ['Anuradhapura Sacred City', 'North Central Province', 'The first capital of Sri Lanka, home to the sacred Sri Maha Bodhi tree and massive stupas.', 6500.00],
            ['Dambulla Cave Temple', 'Matale District', 'The largest and best-preserved cave temple complex in Sri Lanka, featuring stunning Buddhist murals.', 5000.00],
            ['Temple of the Tooth', 'Kandy', 'The sacred Buddhist temple in the heart of Kandy, housing the relic of the tooth of the Buddha.', 5500.00]
        ];
        foreach ($local_tours as $t) {
            $title = mysqli_real_escape_string($conn, $t[0]);
            $loc   = mysqli_real_escape_string($conn, $t[1]);
            $desc  = mysqli_real_escape_string($conn, $t[2]);
            $conn->query("INSERT INTO local_tours (title, location, description, price) VALUES ('$title', '$loc', '$desc', {$t[3]})");
        }
        echo "<p style='color: blue;'>✓ Seeded initial local tours.</p>";
    }
} else {
    echo "<p style='color: red;'>✗ Error creating 'local_tours': " . $conn->error . "</p>";
}

echo "<hr>";

// 2. Upgrade destinations table — add new columns for admin management
echo "<h3>Upgrading 'destinations' table...</h3>";

/**
 * Safely add a column to a table only if it doesn't already exist.
 * Works for both MySQL 5.7 (no IF NOT EXISTS for ALTER) and MySQL 8+.
 */
function add_column_if_missing($conn, $table, $column, $definition) {
    // Check via INFORMATION_SCHEMA
    $db = $conn->query("SELECT DATABASE()")->fetch_row()[0];
    $res = $conn->query("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
                         WHERE TABLE_SCHEMA='$db' AND TABLE_NAME='$table' AND COLUMN_NAME='$column'");
    if ($res && $res->num_rows > 0) {
        echo "<p style='color:#aaa;'>ℹ Column '<b>$column</b>' already exists — skipped.</p>";
        return;
    }
    if ($conn->query("ALTER TABLE `$table` ADD COLUMN `$column` $definition")) {
        echo "<p style='color:green;'>✓ Column '<b>$column</b>' added to '$table'.</p>";
    } else {
        echo "<p style='color:red;'>✗ Failed to add '$column': " . $conn->error . "</p>";
    }
}

add_column_if_missing($conn, 'destinations', 'country_code', "VARCHAR(5) DEFAULT ''");
add_column_if_missing($conn, 'destinations', 'description', "TEXT DEFAULT NULL");
add_column_if_missing($conn, 'destinations', 'show_home',   "TINYINT(1) NOT NULL DEFAULT 1");
add_column_if_missing($conn, 'destinations', 'image',       "VARCHAR(255) DEFAULT NULL");

// 3. Upgrade packages table — add sort_order column for sorting packages
echo "<h3>Upgrading 'packages' table...</h3>";
add_column_if_missing($conn, 'packages', 'sort_order', "INT DEFAULT 0");

// 4. Local Tour Gallery
echo "<h3>Creating 'local_tour_gallery' table...</h3>";
$gallery_sql = "CREATE TABLE IF NOT EXISTS local_tour_gallery (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tour_id INT NOT NULL,
    image VARCHAR(255) NOT NULL,
    FOREIGN KEY (tour_id) REFERENCES local_tours(id) ON DELETE CASCADE
)";
if ($conn->query($gallery_sql)) {
    echo "<p style='color: green;'>✓ Table 'local_tour_gallery' created successfully or already exists.</p>";
} else {
    echo "<p style='color: red;'>✗ Error creating 'local_tour_gallery': " . $conn->error . "</p>";
}

echo "<hr><p><a href='index.php'>Go to Homepage</a> | <a href='admin/login.php'>Go to Admin</a></p>";
?>
