<?php
header('Content-Type: application/json');
session_start();

include_once __DIR__ . '/../../config/dbcon.php'; // PDO connection
include_once __DIR__ . '/../../helpers/api_helper.php';

try {

    $stmt = $conn->prepare("
        SELECT 
            c.*,
            COUNT(j.id) AS job_count,
            CASE 
                WHEN COUNT(j.id) > 50 THEN 1
                ELSE 0
            END AS is_popular
        FROM categories c
        LEFT JOIN jobs j 
            ON j.category_id = c.id
            AND j.deleted_at IS NULL
            AND j.is_published = 1
        WHERE c.deleted_at IS NULL
        GROUP BY c.id
        ORDER BY c.category_name ASC
    ");

    $stmt->execute();
    $categories = $stmt->fetchAll(PDO::FETCH_ASSOC);

    sendResponse(
        200,
        "Categories fetched successfully.",
        ['categories' => $categories]
    );

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