<?php

// Define ACCESS constant for included files
define('ACCESS', true);

// Get blog categories
$blogCategories = $connect->query("SELECT * FROM blog_categories WHERE blog_cat_status = 1 ORDER BY blog_cat_name")->fetchAll(PDO::FETCH_ASSOC);

// Get statistics
$stats = [
    'total' => $connect->query("SELECT COUNT(*) FROM blog_posts")->fetchColumn(),
    'published' => $connect->query("SELECT COUNT(*) FROM blog_posts WHERE post_status = 'published'")->fetchColumn(),
    'draft' => $connect->query("SELECT COUNT(*) FROM blog_posts WHERE post_status = 'draft'")->fetchColumn()
];

// Pagination
$itemsPerPage = 20;
$currentPage = isset($_GET['page']) ? intval($_GET['page']) : 1;
$offset = ($currentPage - 1) * $itemsPerPage;

// Build query with filters
$where = [];
$params = [];

if (isset($_GET['search']) && !empty($_GET['search'])) {
    $where[] = "(post_title LIKE :search OR post_content LIKE :search)";
    $params[':search'] = '%' . $_GET['search'] . '%';
}

if (isset($_GET['category']) && !empty($_GET['category'])) {
    $where[] = "post_category = :category";
    $params[':category'] = $_GET['category'];
}

$whereSQL = !empty($where) ? 'WHERE ' . implode(' AND ', $where) : '';

// Get total count
$countQuery = "SELECT COUNT(*) FROM blog_posts $whereSQL";
$stmt = $connect->prepare($countQuery);
$stmt->execute($params);
$totalPosts = $stmt->fetchColumn();
$totalPages = ceil($totalPosts / $itemsPerPage);

// Get posts
$query = "SELECT bp.*, bc.blog_cat_name, u.user_name 
          FROM blog_posts bp
          LEFT JOIN blog_categories bc ON bp.post_category = bc.blog_cat_id
          LEFT JOIN users u ON bp.post_author = u.user_id
          $whereSQL
          ORDER BY bp.created_at DESC
          LIMIT $itemsPerPage OFFSET $offset";

$stmt = $connect->prepare($query);
$stmt->execute($params);
$posts = $stmt->fetchAll(PDO::FETCH_ASSOC);

require '../views/blog.posts.view.php';
?>
