<?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
 */
$context = $_GET['context'] ?? 'backend'; // backend | frontend
$page    = max(1, (int)($_GET['page'] ?? 1));
$limit   = max(1, min(50, (int)($_GET['limit'] ?? 10)));
$offset  = ($page - 1) * $limit;

$pageType       = $_GET['page_type'] ?? null;
$status         = $_GET['status'] ?? null;
$approvalStatus = $_GET['approval_status'] ?? null;
$showInMenu     = isset($_GET['show_in_menu']) && $_GET['show_in_menu'] !== ''
    ? (int)$_GET['show_in_menu']
    : null;
$search         = $_GET['search'] ?? null;

$where  = [];
$params = [];

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

/**
 * Filters
 */
if ($pageType) {
    $where[] = "p.page_type = :page_type";
    $params['page_type'] = $pageType;
}

if ($status) {
    $where[] = "p.status = :status";
    $params['status'] = $status;
}

if ($approvalStatus) {
    $where[] = "p.approval_status = :approval_status";
    $params['approval_status'] = $approvalStatus;
}

if ($showInMenu !== null) {
    $where[] = "p.show_in_menu = :show_in_menu";
    $params['show_in_menu'] = $showInMenu;
}

if ($search) {
    $where[] = "p.title LIKE :search";
    $params['search'] = '%' . trim($search) . '%';
}

$whereSql = $where ? 'WHERE ' . implode(' AND ', $where) : '';

/**
 * Count total
 */
$stmt = $db->prepare("SELECT COUNT(*) FROM pages p $whereSql");
$stmt->execute($params);
$total = (int)$stmt->fetchColumn();

/**
 * Ordering
 */
$orderBy = ($context === 'frontend')
    ? "ORDER BY p.menu_order ASC, p.created_at DESC"
    : "ORDER BY p.created_at DESC";

/**
 * Fetch pages
 */
$sql = "
SELECT
    p.id,
    p.title,
    p.slug,
    p.page_type,
    p.template,
    p.show_in_menu,
    p.status,
    p.approval_status,
    p.published_at,
    p.created_at,

    (
        SELECT image_path
        FROM page_images
        WHERE page_id = p.id AND status = 'Active'
        ORDER BY is_primary DESC
        LIMIT 1
    ) AS featured_image

FROM pages p
$whereSql
$orderBy
LIMIT :limit OFFSET :offset
";

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

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

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

$stmt->execute();
$pages = $stmt->fetchAll();

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