<?php

require_once BASE_PATH . '/config/database.php';

class PostCategory
{
    private PDO $db;

    public function __construct()
    {
        $database = new Database();
        $this->db = $database->connect();
    }

    public function getAllForOwner(): array
    {
        $sql = "
            SELECT
                pc.*,
                (
                    SELECT COUNT(*)
                    FROM posts p
                    WHERE p.category_id = pc.id
                ) AS post_count
            FROM post_categories pc
            ORDER BY pc.name ASC
        ";

        return $this->db->query($sql)->fetchAll();
    }

    public function getActive(): array
    {
        $stmt = $this->db->query("
            SELECT *
            FROM post_categories
            WHERE is_active = 1
            ORDER BY name ASC
        ");

        return $stmt->fetchAll();
    }

    public function findById(int $id): ?array
    {
        $stmt = $this->db->prepare("
            SELECT *
            FROM post_categories
            WHERE id = :id
            LIMIT 1
        ");

        $stmt->execute([
            'id' => $id
        ]);

        $category = $stmt->fetch();

        return $category ?: null;
    }

    public function slugExists(
        string $slug,
        ?int $excludeId = null
    ): bool {

        $sql = "
            SELECT COUNT(*)
            FROM post_categories
            WHERE slug = :slug
        ";

        $params = [
            'slug' => $slug
        ];

        if ($excludeId !== null) {

            $sql .= "
                AND id != :exclude_id
            ";

            $params['exclude_id'] = $excludeId;
        }

        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);

        return (int) $stmt->fetchColumn() > 0;
    }

    public function create(array $data): int
    {
        $stmt = $this->db->prepare("
            INSERT INTO post_categories (
                name,
                slug,
                is_active
            )
            VALUES (
                :name,
                :slug,
                :is_active
            )
        ");

        $stmt->execute([
            'name' => $data['name'],
            'slug' => $data['slug'],
            'is_active' => $data['is_active']
        ]);

        return (int) $this->db->lastInsertId();
    }

    public function update(
        int $id,
        array $data
    ): void {

        $stmt = $this->db->prepare("
            UPDATE post_categories
            SET
                name = :name,
                slug = :slug,
                is_active = :is_active
            WHERE id = :id
        ");

        $stmt->execute([
            'name' => $data['name'],
            'slug' => $data['slug'],
            'is_active' => $data['is_active'],
            'id' => $id
        ]);
    }

    public function toggle(int $id): void
    {
        $stmt = $this->db->prepare("
            UPDATE post_categories
            SET is_active =
                CASE
                    WHEN is_active = 1 THEN 0
                    ELSE 1
                END
            WHERE id = :id
        ");

        $stmt->execute([
            'id' => $id
        ]);
    }
    public function getActiveWithPostCount(): array
{
    $stmt =
        $this->db->query("
            SELECT
                pc.*,

                (
                    SELECT COUNT(*)

                    FROM posts p

                    WHERE p.category_id = pc.id
                      AND p.status = 'published'
                      AND p.published_at IS NOT NULL
                      AND p.published_at <= NOW()
                ) AS post_count

            FROM post_categories pc

            WHERE pc.is_active = 1

            ORDER BY pc.name ASC
        ");


    return $stmt->fetchAll();
}
}