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

$results = [];

// 1. Create local_tour_categories table
$sql = "CREATE TABLE IF NOT EXISTS `local_tour_categories` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(150) NOT NULL,
    `slug` VARCHAR(150) NOT NULL,
    `description` TEXT,
    `sort_order` INT DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
if ($conn->query($sql)) {
    $results[] = ['status' => 'ok', 'msg' => '✅ Table <strong>local_tour_categories</strong> created/verified.'];
} else {
    $results[] = ['status' => 'error', 'msg' => '❌ local_tour_categories: ' . $conn->error];
}

// 2. Add category_id to local_tours
$check = $conn->query("SHOW COLUMNS FROM local_tours LIKE 'category_id'");
if ($check && $check->num_rows == 0) {
    if ($conn->query("ALTER TABLE local_tours ADD COLUMN category_id INT DEFAULT NULL AFTER id")) {
        $results[] = ['status' => 'ok', 'msg' => '✅ Column <strong>category_id</strong> added to local_tours.'];
    } else {
        $results[] = ['status' => 'error', 'msg' => '❌ category_id: ' . $conn->error];
    }
} else {
    $results[] = ['status' => 'info', 'msg' => 'ℹ️ Column <strong>category_id</strong> already exists.'];
}

// 3. Add duration to local_tours
$check = $conn->query("SHOW COLUMNS FROM local_tours LIKE 'duration'");
if ($check && $check->num_rows == 0) {
    if ($conn->query("ALTER TABLE local_tours ADD COLUMN duration VARCHAR(100) DEFAULT NULL")) {
        $results[] = ['status' => 'ok', 'msg' => '✅ Column <strong>duration</strong> added to local_tours.'];
    } else {
        $results[] = ['status' => 'error', 'msg' => '❌ duration: ' . $conn->error];
    }
} else {
    $results[] = ['status' => 'info', 'msg' => 'ℹ️ Column <strong>duration</strong> already exists.'];
}

// 4. Add highlights to local_tours
$check = $conn->query("SHOW COLUMNS FROM local_tours LIKE 'highlights'");
if ($check && $check->num_rows == 0) {
    if ($conn->query("ALTER TABLE local_tours ADD COLUMN highlights TEXT DEFAULT NULL")) {
        $results[] = ['status' => 'ok', 'msg' => '✅ Column <strong>highlights</strong> added to local_tours.'];
    } else {
        $results[] = ['status' => 'error', 'msg' => '❌ highlights: ' . $conn->error];
    }
} else {
    $results[] = ['status' => 'info', 'msg' => 'ℹ️ Column <strong>highlights</strong> already exists.'];
}

// 5. Add includes_list to local_tours
$check = $conn->query("SHOW COLUMNS FROM local_tours LIKE 'includes_list'");
if ($check && $check->num_rows == 0) {
    if ($conn->query("ALTER TABLE local_tours ADD COLUMN includes_list TEXT DEFAULT NULL")) {
        $results[] = ['status' => 'ok', 'msg' => '✅ Column <strong>includes_list</strong> added to local_tours.'];
    } else {
        $results[] = ['status' => 'error', 'msg' => '❌ includes_list: ' . $conn->error];
    }
} else {
    $results[] = ['status' => 'info', 'msg' => 'ℹ️ Column <strong>includes_list</strong> already exists.'];
}

// 6. Add tour_type to local_tours
$check = $conn->query("SHOW COLUMNS FROM local_tours LIKE 'tour_type'");
if ($check && $check->num_rows == 0) {
    if ($conn->query("ALTER TABLE local_tours ADD COLUMN tour_type VARCHAR(50) DEFAULT 'day-tour'")) {
        $results[] = ['status' => 'ok', 'msg' => '✅ Column <strong>tour_type</strong> added to local_tours.'];
    } else {
        $results[] = ['status' => 'error', 'msg' => '❌ tour_type: ' . $conn->error];
    }
} else {
    $results[] = ['status' => 'info', 'msg' => 'ℹ️ Column <strong>tour_type</strong> already exists.'];
}

// 7. Add sort_order to local_tours
$check = $conn->query("SHOW COLUMNS FROM local_tours LIKE 'sort_order'");
if ($check && $check->num_rows == 0) {
    if ($conn->query("ALTER TABLE local_tours ADD COLUMN sort_order INT DEFAULT 0")) {
        $results[] = ['status' => 'ok', 'msg' => '✅ Column <strong>sort_order</strong> added to local_tours.'];
    } else {
        $results[] = ['status' => 'error', 'msg' => '❌ sort_order: ' . $conn->error];
    }
} else {
    $results[] = ['status' => 'info', 'msg' => 'ℹ️ Column <strong>sort_order</strong> already exists.'];
}

// 8. Create google_reviews table
$sql = "CREATE TABLE IF NOT EXISTS `google_reviews` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `reviewer_name` VARCHAR(200) NOT NULL,
    `reviewer_photo` VARCHAR(500) DEFAULT NULL,
    `rating` TINYINT(1) DEFAULT 5,
    `review_text` TEXT NOT NULL,
    `review_date` VARCHAR(100) DEFAULT NULL,
    `is_active` TINYINT(1) DEFAULT 1,
    `sort_order` INT DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
if ($conn->query($sql)) {
    $results[] = ['status' => 'ok', 'msg' => '✅ Table <strong>google_reviews</strong> created/verified.'];
} else {
    $results[] = ['status' => 'error', 'msg' => '❌ google_reviews: ' . $conn->error];
}

?><!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Database Migration | Admin</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
    <link href="https://fonts.googleapis.com/css2?family=Inter:wght@400;600;700&display=swap" rel="stylesheet">
    <style>* { font-family: 'Inter', sans-serif; } body { background: #f0f2f5; padding: 40px; }</style>
</head>
<body>
<div class="container" style="max-width: 700px;">
    <div class="mb-4">
        <a href="index.php" class="text-decoration-none text-muted"><i class="fas fa-arrow-left me-2"></i>Back to Dashboard</a>
        <h3 class="fw-bold mt-2">Database Migration</h3>
        <p class="text-muted">Running required schema updates...</p>
    </div>
    <div class="card border-0 shadow-sm" style="border-radius: 16px;">
        <div class="card-body p-4">
            <?php foreach ($results as $r): ?>
            <div class="alert <?= $r['status'] === 'ok' ? 'alert-success' : ($r['status'] === 'error' ? 'alert-danger' : 'alert-info') ?> py-2 border-0 rounded-3 mb-2">
                <?= $r['msg'] ?>
            </div>
            <?php endforeach; ?>
            <hr>
            <div class="text-center mt-3">
                <a href="index.php" class="btn btn-primary rounded-pill px-5">
                    <i class="fas fa-home me-2"></i>Back to Dashboard
                </a>
            </div>
        </div>
    </div>
</div>
</body>
</html>
