<?php
declare(strict_types=1);

require_once __DIR__ . '/../../security/auth_guard.php';
require_once __DIR__ . '/../../config/database.php';
require_once __DIR__ . '/../../core/Response.php';

$db = Database::getInstance();

/**
 * Query params
 */
$page   = max(1, (int)($_GET['page'] ?? 1));
$limit  = min(50, max(1, (int)($_GET['limit'] ?? 10)));
$offset = ($page - 1) * $limit;

$context = $_GET['context'] ?? 'backend'; // frontend | backend

$conditions = [];
$params     = [];

/**
 * Frontend visibility rules
 */
if ($context === 'frontend') {
    $conditions[] = "b.status = 'Active'";
    $conditions[] = "b.approval_status = 'Approved'";
    $conditions[] = "b.published_at <= NOW()";
}

/**
 * Filters
 */
if (!empty($_GET['status'])) {
    $conditions[] = "b.status = :status";
    $params['status'] = $_GET['status'];
}

if (!empty($_GET['approval_status'])) {
    $conditions[] = "b.approval_status = :approval_status";
    $params['approval_status'] = $_GET['approval_status'];
}

if (!empty($_GET['search'])) {
    $conditions[] = "(b.title LIKE :search OR b.excerpt LIKE :search)";
    $params['search'] = '%' . $_GET['search'] . '%';
}

if (!empty($_GET['from_date'])) {
    $conditions[] = "DATE(b.created_at) >= :from_date";
    $params['from_date'] = $_GET['from_date'];
}

if (!empty($_GET['to_date'])) {
    $conditions[] = "DATE(b.created_at) <= :to_date";
    $params['to_date'] = $_GET['to_date'];
}

if (!empty($_GET['category_id'])) {
    $conditions[] = "b.id IN (
        SELECT blog_id FROM blog_category_map WHERE category_id = :category_id
    )";
    $params['category_id'] = (int)$_GET['category_id'];
}

if (!empty($_GET['tag_id'])) {
    $conditions[] = "b.id IN (
        SELECT blog_id FROM blog_tag_map WHERE tag_id = :tag_id
    )";
    $params['tag_id'] = (int)$_GET['tag_id'];
}

/**
 * WHERE clause
 */
$where = $conditions ? 'WHERE ' . implode(' AND ', $conditions) : '';

/**
 * Total count
 */
$countSql = "
    SELECT COUNT(*) 
    FROM blogs b
    $where
";
$stmt = $db->prepare($countSql);
$stmt->execute($params);
$total = (int)$stmt->fetchColumn();

/**
 * Fetch blogs
 */
$sql = "
SELECT
    b.id,
    b.title,
    b.slug,
    b.excerpt,
    b.author_name,
    b.status,
    b.approval_status,
    b.published_at,
    b.created_at,

    (
        SELECT image_path
        FROM blog_images
        WHERE blog_id = b.id AND is_primary = 1 AND status = 'Active'
        LIMIT 1
    ) AS featured_image

FROM blogs b
$where
ORDER BY 
    CASE 
        WHEN b.published_at IS NULL THEN b.created_at
        ELSE b.published_at
    END DESC
LIMIT :limit OFFSET :offset
";

$stmt = $db->prepare($sql);

foreach ($params as $key => $val) {
    $stmt->bindValue(':' . $key, $val);
}

$stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);

$stmt->execute();

$blogs = $stmt->fetchAll();

Response::success('Blog list fetched successfully', [
    'pagination' => [
        'page'  => $page,
        'limit'=> $limit,
        'total'=> $total,
        'pages'=> ceil($total / $limit)
    ],
    'blogs' => $blogs
]);
