<?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 the tag from URL parameter
$tag = $_GET['tag'] ?? '';
if (empty($tag)) {
    header('Location: blog.php');
    exit;
}

// Get posts with this tag
try {
    // Get tag ID first
    $stmt = $pdo->prepare("SELECT id FROM blog_tags WHERE tag = ?");
    $stmt->execute([$tag]);
    $tag_id = $stmt->fetchColumn();

    if (!$tag_id) {
        die("<h2>Tag not found</h2><p>The tag you requested doesn't exist.</p>");
    }

    // Get pagination parameters
    $page = $_GET['page'] ?? 1;
    $posts_per_page = 6;
    $offset = ($page - 1) * $posts_per_page;

    // Get total posts for this tag
    $total_posts = $pdo->prepare("SELECT COUNT(*) FROM post_tags pt 
                                JOIN blog_posts bp ON pt.post_id = bp.id 
                                WHERE pt.tag_id = ? AND bp.status = 'published'");
    $total_posts->execute([$tag_id]);
    $total_posts = $total_posts->fetchColumn();
    $total_pages = ceil($total_posts / $posts_per_page);

    // Get posts with this tag
    $stmt = $pdo->prepare("SELECT bp.*, c.name as category_name 
                          FROM blog_posts bp
                          JOIN categories c ON bp.category_id = c.id
                          JOIN post_tags pt ON bp.id = pt.post_id
                          WHERE pt.tag_id = ? AND bp.status = 'published'
                          ORDER BY bp.created_at DESC
                          LIMIT ? OFFSET ?");
    $stmt->bindValue(1, $tag_id, PDO::PARAM_INT);
    $stmt->bindValue(2, $posts_per_page, PDO::PARAM_INT);
    $stmt->bindValue(3, $offset, PDO::PARAM_INT);
    $stmt->execute();
    $posts = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Get recent posts for sidebar
    $stmt = $pdo->prepare("SELECT bp.title, bp.slug, bp.created_at 
                          FROM blog_posts bp
                          WHERE bp.status = 'published'
                          ORDER BY bp.created_at DESC
                          LIMIT 4");
    $stmt->execute();
    $recent_posts = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Get categories for sidebar
    $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);

    // Get all tags
    $stmt = $pdo->query("SELECT DISTINCT tag FROM blog_tags ORDER BY tag");
    $tags = $stmt->fetchAll(PDO::FETCH_COLUMN);

} catch(PDOException $e) {
    die("Database error: " . $e->getMessage());
}

// Helper function to get category color
function getCategoryColor($category_name) {
    $colors = [
        'Scholarships' => '#3FB7FE',
        'Visa' => '#6c757d',
        'UK' => '#6c757d',
        'USA' => '#17a2b8',
        'Germany' => '#28a745',
        'Test Preparation' => '#ffc107',
        'Guides' => '#28a745',
        'Tips' => '#ffc107',
        'Canada' => '#dc3545',
        'Student Experience' => '#dc3545'
    ];
    return $colors[$category_name] ?? '#3FB7FE';
}

// Helper function for image paths
function getFeaturedImagePath($image_path) {
    if (empty($image_path)) {
        return 'img/blog-default.jpg';
    }
    if (file_exists($image_path)) {
        return $image_path;
    }
    $base_path = $_SERVER['DOCUMENT_ROOT'] . '/';
    if (file_exists($base_path . $image_path)) {
        return $image_path;
    }
    return 'img/blog-default.jpg';
}
?>

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

<head>
    <meta charset="utf-8">
    <title>Posts Tagged "<?= htmlspecialchars($tag) ?>" - JV Overseas Education</title>
    <meta content="width=device-width, initial-scale=1.0" name="viewport">
    <meta content="<?= htmlspecialchars($tag) ?>, study abroad blog, international education" name="keywords">
    <meta content="Blog posts about <?= htmlspecialchars($tag) ?> from JV Overseas Education" name="description">

    <!-- 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>
        .blog-item {
            transition: all 0.3s;
            margin-bottom: 30px;
        }
        .blog-item:hover {
            transform: translateY(-5px);
            box-shadow: 0 10px 20px rgba(0,0,0,0.1);
        }
        .blog-date {
            position: absolute;
            bottom: -20px;
            left: 20px;
            width: 80px;
            height: 80px;
            display: flex;
            flex-direction: column;
            align-items: center;
            justify-content: center;
            background: #3FB7FE;
            color: white;
            border-radius: 10px;
            z-index: 1;
        }
        .blog-img {
            height: 250px;
            object-fit: cover;
        }
        .blog-category {
            display: inline-block;
            padding: 3px 10px;
            background: #3FB7FE;
            color: white;
            border-radius: 15px;
            font-size: 12px;
            margin-right: 10px;
        }
        .tag-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;
            padding: 100px 0;
            color: white;
            text-align: center;
            margin-bottom: 60px;
        }
    </style>
</head>

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

    <!-- Tag Header -->
    <div class="tag-header">
        <div class="container">
            <h1 class="display-4 text-white">Posts Tagged: <?= htmlspecialchars($tag) ?></h1>
            <p class="text-white"><?= $total_posts ?> article<?= $total_posts != 1 ? 's' : '' ?> found</p>
        </div>
    </div>

    <!-- Blog Start -->
    <div class="container-fluid py-5">
        <div class="container py-5">
            <div class="row">
                <!-- Blog Posts -->
                <div class="col-lg-8">
                    <div class="row">
                        <?php if (empty($posts)): ?>
                        <div class="col-12 text-center py-5">
                            <h4>No posts found with this tag</h4>
                            <p class="text-muted">Try browsing our other articles.</p>
                            <a href="blog.php" class="btn btn-primary">View All Posts</a>
                        </div>
                        <?php else: ?>
                        <?php foreach ($posts as $post): ?>
                        <div class="col-md-6 mb-4">
                            <div class="blog-item bg-white rounded">
                                <div class="position-relative">
                                    <img class="img-fluid w-100 rounded-top blog-img" 
                                         src="<?= getFeaturedImagePath($post['featured_image']) ?>" 
                                         alt="<?= htmlspecialchars($post['title']) ?>">
                                    <div class="blog-date">
                                        <h6 class="font-weight-bold mb-n1"><?= date('d', strtotime($post['created_at'])) ?></h6>
                                        <small class="text-white text-uppercase"><?= date('M', strtotime($post['created_at'])) ?></small>
                                    </div>
                                </div>
                                <div class="p-4">
                                    <div class="d-flex mb-3">
                                        <span class="blog-category" style="background: <?= getCategoryColor($post['category_name']) ?>;">
                                            <?= htmlspecialchars($post['category_name']) ?>
                                        </span>
                                    </div>
                                    <h5 class="mb-3"><?= htmlspecialchars($post['title']) ?></h5>
                                    <p class="mb-4"><?= htmlspecialchars(substr($post['excerpt'], 0, 100)) ?>...</p>
                                    <a class="btn btn-sm btn-primary" href="blog-post.php?slug=<?= urlencode($post['slug']) ?>">Read More</a>
                                </div>
                            </div>
                        </div>
                        <?php endforeach; ?>
                        <?php endif; ?>
                    </div>
                    
                    <!-- Pagination -->
                    <?php if ($total_pages > 1): ?>
                    <nav class="mt-5">
                        <ul class="pagination justify-content-center">
                            <li class="page-item <?= $page <= 1 ? 'disabled' : '' ?>">
                                <a class="page-link" href="<?= $page <= 1 ? '#' : '?tag=' . urlencode($tag) . '&page=' . ($page - 1) ?>">Previous</a>
                            </li>
                            <?php for ($i = 1; $i <= $total_pages; $i++): ?>
                            <li class="page-item <?= $i == $page ? 'active' : '' ?>">
                                <a class="page-link" href="<?= $i == $page ? '#' : '?tag=' . urlencode($tag) . '&page=' . $i ?>"><?= $i ?></a>
                            </li>
                            <?php endfor; ?>
                            <li class="page-item <?= $page >= $total_pages ? 'disabled' : '' ?>">
                                <a class="page-link" href="<?= $page >= $total_pages ? '#' : '?tag=' . urlencode($tag) . '&page=' . ($page + 1) ?>">Next</a>
                            </li>
                        </ul>
                    </nav>
                    <?php endif; ?>
                </div>
                
                <!-- Sidebar -->
                <div class="col-lg-4 mt-5 mt-lg-0">
                    <!-- Search -->
                    <div class="mb-5">
                        <div class="bg-light p-4">
                            <h4 class="mb-4">Search Blog</h4>
                            <form action="search.php" method="GET">
                                <div class="input-group">
                                    <input type="text" name="q" class="form-control form-control-lg" placeholder="Keyword">
                                    <div class="input-group-append">
                                        <button type="submit" class="input-group-text bg-transparent text-primary"><i class="fa fa-search"></i></button>
                                    </div>
                                </div>
                            </form>
                        </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 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>
                    
                    <!-- 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>
                    
                    <!-- Tags -->
                    <div class="mb-5">
                        <div class="bg-light p-4">
                            <h4 class="mb-4">Tags</h4>
                            <div class="d-flex flex-wrap m-n1">
                                <?php foreach ($tags as $t): ?>
                                <a href="tag.php?tag=<?= urlencode($t) ?>" class="btn btn-sm btn-outline-primary m-1 <?= $t == $tag ? 'active' : '' ?>">
                                    <?= htmlspecialchars($t) ?>
                                </a>
                                <?php endforeach; ?>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
    <!-- Blog 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>