<?php
// Start session for navbar
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 blog posts from database
$page = $_GET['page'] ?? 1;
$posts_per_page = 4;
$offset = ($page - 1) * $posts_per_page;

// 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 run the following SQL to create the necessary tables:</p>
    <pre>
-- Create categories table
CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Create blog_posts table
CREATE TABLE blog_posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL UNIQUE,
    excerpt TEXT,
    content TEXT NOT NULL,
    category_id INT NOT NULL,
    author_id INT NOT NULL,
    featured_image VARCHAR(255),
    status ENUM('draft', 'published', 'scheduled') DEFAULT 'draft',
    is_featured BOOLEAN DEFAULT FALSE,
    view_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
);

-- Create blog_tags table
CREATE TABLE blog_tags (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tag VARCHAR(50) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create post_tags junction table
CREATE TABLE post_tags (
    id INT AUTO_INCREMENT PRIMARY KEY,
    post_id INT NOT NULL,
    tag_id INT NOT NULL,
    UNIQUE KEY unique_post_tag (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES blog_posts(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES blog_tags(id) ON DELETE CASCADE
);
    </pre>
    <p>After running this SQL, refresh the page.</p>");
}

// Get total posts for pagination
try {
    $total_posts = $pdo->query("SELECT COUNT(*) FROM blog_posts WHERE status = 'published'")->fetchColumn();
    $total_pages = ceil($total_posts / $posts_per_page);

    // Get current page posts - FIXED: Removed author join and author fields
    $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.status = 'published'
                          ORDER BY bp.created_at DESC
                          LIMIT ? OFFSET ?");
    $stmt->bindValue(1, $posts_per_page, PDO::PARAM_INT);
    $stmt->bindValue(2, $offset, PDO::PARAM_INT);
    $stmt->execute();
    $posts = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Get featured post (most recent)
    $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.status = 'published' AND bp.is_featured = 1
                          ORDER BY bp.created_at DESC
                          LIMIT 1");
    $stmt->execute();
    $featured_post = $stmt->fetch(PDO::FETCH_ASSOC);

    // If no featured post, get the most recent post
    if (!$featured_post && !empty($posts)) {
        $featured_post = $posts[0];
    }

    // 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 recent posts for sidebar - FIXED: Removed author fields
    $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 all tags
    $stmt = $pdo->query("SELECT DISTINCT tag FROM blog_tags ORDER BY tag");
    $tags = $stmt->fetchAll(PDO::FETCH_COLUMN);
} 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>
    <p>The issue was with missing columns in the database. This version has been simplified to work without author information.</p>");
}
?>

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

<head>
    <meta charset="utf-8">
    <title>Study Abroad Blog - JV Overseas Education</title>
    <meta content="width=device-width, initial-scale=1.0" name="viewport">
    <meta content="study abroad blog, international education tips, student visa updates, scholarship information" name="keywords">
    <meta content="Latest news and articles about studying abroad, visa updates, scholarship opportunities and student experiences 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;
        }
        .blog-meta {
            font-size: 14px;
            color: #666;
            margin-bottom: 10px;
        }
        .blog-meta a {
            color: #666;
            text-decoration: none;
        }
        .blog-meta a:hover {
            color: #3FB7FE;
        }
        
        /* 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">Blog</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">Blog</p>
                </div>
            </div>
        </div>
    </div>
    <!-- Header End -->

    <!-- Blog Start -->
    <div class="container-fluid py-5">
        <div class="container py-5">
            <div class="row">
                <!-- Blog Posts -->
                <div class="col-lg-8">
                    <!-- Featured Post -->
                    <?php if ($featured_post): ?>
                    <div class="mb-5">
                        <div class="position-relative">
                            <img class="img-fluid w-100 rounded" src="<?= htmlspecialchars($featured_post['featured_image'] ?: 'img/blog-default.jpg') ?>" alt="<?= htmlspecialchars($featured_post['title']) ?>">
                            <div class="blog-date">
                                <h4 class="font-weight-bold mb-n1"><?= date('d', strtotime($featured_post['created_at'])) ?></h4>
                                <small class="text-white text-uppercase"><?= date('M', strtotime($featured_post['created_at'])) ?></small>
                            </div>
                        </div>
                        <div class="bg-white p-4">
                            <div class="d-flex mb-3">
                                <span class="blog-category" style="background: <?= getCategoryColor($featured_post['category_name']) ?>;">
                                    <?= htmlspecialchars($featured_post['category_name']) ?>
                                </span>
                                <small class="text-muted ml-3">
                                    <i class="far fa-eye mr-2"></i><?= $featured_post['view_count'] ?> Views
                                </small>
                            </div>
                            <h3 class="mb-3"><?= htmlspecialchars($featured_post['title']) ?></h3>
                            <p><?= htmlspecialchars(substr($featured_post['excerpt'], 0, 200)) ?>...</p>
                            <a class="btn btn-primary" href="blog-post.php?slug=<?= urlencode($featured_post['slug']) ?>">Read More</a>
                        </div>
                    </div>
                    <?php endif; ?>
                    
                    <!-- Blog List -->
                    <div class="row">
                        <?php if (empty($posts)): ?>
                        <div class="col-12 text-center py-5">
                            <h4>No blog posts available</h4>
                            <p class="text-muted">Check back later for new articles about studying abroad.</p>
                        </div>
                        <?php else: ?>
                        <?php foreach ($posts as $post): ?>
                        <!-- Skip featured post if it's already displayed -->
                        <?php if ($featured_post && $post['id'] == $featured_post['id']) continue; ?>
                        
                        <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="<?= htmlspecialchars($post['featured_image'] ?: 'img/blog-default.jpg') ?>" 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 ? '#' : '?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 ? '#' : '?page=' . $i ?>"><?= $i ?></a>
                            </li>
                            <?php endfor; ?>
                            <li class="page-item <?= $page >= $total_pages ? 'disabled' : '' ?>">
                                <a class="page-link" href="<?= $page >= $total_pages ? '#' : '?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" value="<?= htmlspecialchars($_GET['q'] ?? '') ?>">
                                    <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 $tag): ?>
                                <a href="tag.php?tag=<?= urlencode($tag) ?>" class="btn btn-outline-primary m-1"><?= htmlspecialchars($tag) ?></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>
    
    <script>
        // Helper function to get category color
        function getCategoryColor(category) {
            const colors = {
                'Scholarships': '#3FB7FE',
                'Visa': '#6c757d',
                'UK': '#6c757d',
                'USA': '#17a2b8',
                'Germany': '#28a745',
                'Test Preparation': '#ffc107',
                'Guide': '#28a745',
                'Tips': '#ffc107',
                'Canada': '#dc3545',
                'Experience': '#dc3545'
            };
            return colors[category] || '#3FB7FE';
        }
    </script>
</body>
</html>

<?php
// 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';
}
?>