<?php
session_start();

// Database connection
$host = 'localhost';
$dbname = 'jv_overseas';
$username = 'root';
$password = '';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}

// Get post slug
$slug = $_GET['slug'] ?? null;

if (!$slug) {
    die("Post slug is required.");
}

// Check if blog_posts table exists
$table_exists = $pdo->query("SHOW TABLES LIKE 'blog_posts'")->rowCount() > 0;

if (!$table_exists) {
    die("<h2>Error: Blog tables not found</h2>
    <p>The blog system requires specific database tables to function properly.</p>
    <p>Please create the necessary tables first.</p>");
}

// Get post data
try {
    $stmt = $pdo->prepare("SELECT bp.*, c.name as category_name
                          FROM blog_posts bp
                          JOIN categories c ON bp.category_id = c.id
                          WHERE bp.slug = ? AND bp.status = 'published'");
    $stmt->execute([$slug]);
    $post = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$post) {
        die("Blog post not found.");
    }

    // Increment view count
    $pdo->prepare("UPDATE blog_posts SET view_count = view_count + 1 WHERE id = ?")
        ->execute([$post['id']]);

    // Get tags for this post
    $stmt = $pdo->prepare("SELECT bt.tag 
                          FROM blog_tags bt
                          JOIN post_tags pt ON bt.id = pt.tag_id
                          WHERE pt.post_id = ?");
    $stmt->execute([$post['id']]);
    $tags = $stmt->fetchAll(PDO::FETCH_COLUMN);

    // Get related posts (same category, excluding current post)
    $stmt = $pdo->prepare("SELECT bp.title, bp.slug, bp.created_at, bp.excerpt, bp.featured_image
                          FROM blog_posts bp
                          WHERE bp.category_id = ? AND bp.id != ? AND bp.status = 'published'
                          ORDER BY bp.created_at DESC
                          LIMIT 3");
    $stmt->execute([$post['category_id'], $post['id']]);
    $related_posts = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Get recent posts
    $stmt = $pdo->prepare("SELECT bp.title, bp.slug, bp.created_at
                          FROM blog_posts bp
                          WHERE bp.id != ? AND bp.status = 'published'
                          ORDER BY bp.created_at DESC
                          LIMIT 5");
    $stmt->execute([$post['id']]);
    $recent_posts = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch(PDOException $e) {
    die("<h2>Error: Database query failed</h2>
    <p>The blog tables exist but there was an error retrieving data.</p>
    <p>Error: " . $e->getMessage() . "</p>");
}
?>

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="utf-8">
    <title><?= htmlspecialchars($post['title']) ?> - JV Overseas Education</title>
    <meta content="width=device-width, initial-scale=1.0" name="viewport">
    <meta name="description" content="<?= htmlspecialchars($post['excerpt'] ?: $post['title']) ?>">
    <meta name="keywords" content="<?= implode(', ', $tags) ?>, study abroad, international education">

    <!-- Favicon -->
    <link href="img/favicon.ico" rel="icon">

    <!-- Google Web Fonts -->
    <link rel="preconnect" href="https://fonts.gstatic.com">
    <link href="https://fonts.googleapis.com/css2?family=Jost:wght@500;600;700&family=Open+Sans:wght@400;600&display=swap" rel="stylesheet"> 

    <!-- Font Awesome -->
    <link href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/5.10.0/css/all.min.css" rel="stylesheet">

    <!-- Libraries Stylesheet -->
    <link href="lib/owlcarousel/assets/owl.carousel.min.css" rel="stylesheet">

    <!-- Customized Bootstrap Stylesheet -->
    <link href="css/style.css" rel="stylesheet">
    
    <style>
        .post-meta {
            color: #666;
            font-size: 14px;
            margin-bottom: 20px;
        }
        .post-meta a {
            color: #666;
            text-decoration: none;
        }
        .post-meta a:hover {
            color: #3FB7FE;
        }
        .post-content img {
            max-width: 100%;
            height: auto;
            margin: 20px 0;
            border-radius: 5px;
        }
        .post-tags {
            margin: 20px 0;
            padding: 15px;
            background: #f8f9fa;
            border-radius: 5px;
        }
        .post-tags a {
            display: inline-block;
            margin: 5px;
            padding: 5px 15px;
            background: #3FB7FE;
            color: white;
            border-radius: 20px;
            text-decoration: none;
            font-size: 14px;
        }
        .post-tags a:hover {
            background: #2a9bd8;
        }
        .related-post {
            margin-bottom: 20px;
            padding-bottom: 20px;
            border-bottom: 1px solid #eee;
        }
        .related-post:last-child {
            border-bottom: none;
            margin-bottom: 0;
            padding-bottom: 0;
        }
        
        /* Fix for white text on white background */
        .page-header {
            background: linear-gradient(rgba(0, 0, 0, 0.7), rgba(0, 0, 0, 0.7)), url('img/header-bg.jpg') center center no-repeat;
            background-size: cover;
        }
        
        .page-header * {
            color: #fff !important;
        }
        
        .bg-light {
            background-color: #f8f9fa !important;
        }
        
        .text-white {
            color: #333 !important;
        }
        
        .card {
            background-color: #fff;
        }
    </style>
</head>

<body>
    <?php include 'navbar.php'; ?>

    <!-- Header Start -->
    <div class="container-fluid page-header" style="margin-bottom: 90px;">
        <div class="container">
            <div class="d-flex flex-column justify-content-center" style="min-height: 300px">
                <h3 class="display-4 text-white text-uppercase"><?= htmlspecialchars($post['title']) ?></h3>
                <div class="d-inline-flex text-white">
                    <p class="m-0 text-uppercase"><a class="text-white" href="index.php">Home</a></p>
                    <i class="fa fa-angle-double-right pt-1 px-3"></i>
                    <p class="m-0 text-uppercase"><a class="text-white" href="blog.php">Blog</a></p>
                    <i class="fa fa-angle-double-right pt-1 px-3"></i>
                    <p class="m-0 text-uppercase"><?= htmlspecialchars($post['title']) ?></p>
                </div>
            </div>
        </div>
    </div>
    <!-- Header End -->

    <!-- Blog Detail Start -->
    <div class="container-fluid py-5">
        <div class="container py-5">
            <div class="row">
                <div class="col-lg-8">
                    <!-- Featured Image -->
                    <?php if ($post['featured_image']): ?>
                    <div class="mb-4">
                        <img class="img-fluid w-100" src="<?= htmlspecialchars($post['featured_image']) ?>" alt="<?= htmlspecialchars($post['title']) ?>">
                    </div>
                    <?php endif; ?>
                    
                    <!-- Post Meta -->
                    <div class="post-meta mb-4">
                        <span><i class="far fa-calendar-alt mr-2"></i><?= date('F j, Y', strtotime($post['created_at'])) ?></span>
                        <span class="mx-2">|</span>
                        <span><i class="far fa-folder-open mr-2"></i><a href="category.php?name=<?= urlencode($post['category_name']) ?>"><?= htmlspecialchars($post['category_name']) ?></a></span>
                        <span class="mx-2">|</span>
                        <span><i class="far fa-eye mr-2"></i><?= $post['view_count'] ?> Views</span>
                    </div>
                    
                    <!-- Post Content -->
                    <div class="post-content">
                        <?= htmlspecialchars_decode($post['content']) ?>
                    </div>
                    
                    <!-- Post Tags -->
                    <?php if (!empty($tags)): ?>
                    <div class="post-tags">
                        <h6 class="mb-3">Tags:</h6>
                        <?php foreach ($tags as $tag): ?>
                        <a href="tag.php?tag=<?= urlencode($tag) ?>"><?= htmlspecialchars($tag) ?></a>
                        <?php endforeach; ?>
                    </div>
                    <?php endif; ?>
                    
                    <!-- Related Posts -->
                    <?php if (!empty($related_posts)): ?>
                    <div class="mt-5">
                        <h4 class="mb-4">You Might Also Like</h4>
                        <div class="row">
                            <?php foreach ($related_posts as $related): ?>
                            <div class="col-md-4 mb-4">
                                <div class="bg-light p-3">
                                    <img class="img-fluid mb-3" src="<?= htmlspecialchars($related['featured_image'] ?: 'img/blog-default.jpg') ?>" alt="<?= htmlspecialchars($related['title']) ?>" style="height: 100px; object-fit: cover;">
                                    <h6><a href="blog-post.php?slug=<?= urlencode($related['slug']) ?>" class="text-dark"><?= htmlspecialchars($related['title']) ?></a></h6>
                                    <small class="text-muted"><?= date('M j, Y', strtotime($related['created_at'])) ?></small>
                                </div>
                            </div>
                            <?php endforeach; ?>
                        </div>
                    </div>
                    <?php endif; ?>
                </div>
                
                <!-- Sidebar -->
                <div class="col-lg-4 mt-5 mt-lg-0">
                    <!-- Recent Posts -->
                    <div class="mb-5">
                        <div class="bg-light p-4">
                            <h4 class="mb-4">Recent Posts</h4>
                            <?php foreach ($recent_posts as $recent): ?>
                            <div class="media mb-4">
                                <img src="img/blog-thumb-default.jpg" alt="<?= htmlspecialchars($recent['title']) ?>" class="img-fluid mr-3" style="width: 60px; height: 60px; object-fit: cover;">
                                <div class="media-body">
                                    <a class="text-dark" href="blog-post.php?slug=<?= urlencode($recent['slug']) ?>">
                                        <h6 class="mb-1"><?= htmlspecialchars($recent['title']) ?></h6>
                                    </a>
                                    <small><?= date('F j, Y', strtotime($recent['created_at'])) ?></small>
                                </div>
                            </div>
                            <?php endforeach; ?>
                        </div>
                    </div>
                    
                    <!-- Categories -->
                    <div class="mb-5">
                        <div class="bg-light p-4">
                            <h4 class="mb-4">Categories</h4>
                            <ul class="list-group list-group-flush">
                                <?php 
                                $stmt = $pdo->query("SELECT c.name, COUNT(bp.id) as post_count 
                                            FROM categories c 
                                            LEFT JOIN blog_posts bp ON c.id = bp.category_id AND bp.status = 'published'
                                            GROUP BY c.id, c.name
                                            ORDER BY post_count DESC");
                                $categories = $stmt->fetchAll(PDO::FETCH_ASSOC);
                                ?>
                                <?php foreach ($categories as $category): ?>
                                <li class="list-group-item d-flex justify-content-between align-items-center px-0">
                                    <a href="category.php?name=<?= urlencode($category['name']) ?>" class="text-dark">
                                        <?= htmlspecialchars($category['name']) ?>
                                    </a>
                                    <span class="badge badge-primary badge-pill"><?= $category['post_count'] ?></span>
                                </li>
                                <?php endforeach; ?>
                            </ul>
                        </div>
                    </div>
                    
                    <!-- Tags -->
                    <div class="mb-5">
                        <div class="bg-light p-4">
                            <h4 class="mb-4">Popular Tags</h4>
                            <div class="d-flex flex-wrap m-n1">
                                <?php 
                                $stmt = $pdo->query("SELECT bt.tag, COUNT(pt.post_id) as count 
                                            FROM blog_tags bt
                                            JOIN post_tags pt ON bt.id = pt.tag_id
                                            GROUP BY bt.id, bt.tag
                                            ORDER BY count DESC
                                            LIMIT 8");
                                $popular_tags = $stmt->fetchAll(PDO::FETCH_ASSOC);
                                ?>
                                <?php foreach ($popular_tags as $tag): ?>
                                <a href="tag.php?tag=<?= urlencode($tag['tag']) ?>" class="btn btn-outline-primary m-1"><?= htmlspecialchars($tag['tag']) ?></a>
                                <?php endforeach; ?>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
    <!-- Blog Detail End -->

    <?php include 'footer.php'; ?>

    <!-- Back to Top -->
    <a href="#" class="btn btn-lg btn-primary rounded-0 btn-lg-square back-to-top"><i class="fa fa-angle-double-up"></i></a>

    <!-- JavaScript Libraries -->
    <script src="https://code.jquery.com/jquery-3.4.1.min.js"></script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.bundle.min.js"></script>
    <script src="lib/easing/easing.min.js"></script>
    <script src="lib/waypoints/waypoints.min.js"></script>
    <script src="lib/counterup/counterup.min.js"></script>
    <script src="lib/owlcarousel/owl.carousel.min.js"></script>

    <!-- Template Javascript -->
    <script src="js/main.js"></script>
</body>
</html>