<?php

namespace App\Models;

use App\Core\Model;

class Category extends Model
{
    protected string $table = 'categories';

    public function all(): array
    {
        return $this->db->query(
            "SELECT c.*, COUNT(p.id) as topic_count 
             FROM categories c 
             LEFT JOIN posts p ON p.category_id = c.id AND p.status = 'active'
             GROUP BY c.id 
             ORDER BY c.sort_order DESC"
        )->fetchAll();
    }

    public function findBySlug(string $slug): ?array
    {
        $stmt = $this->db->prepare("SELECT * FROM categories WHERE slug = ?");
        $stmt->execute([$slug]);
        return $stmt->fetch() ?: null;
    }

    public function create(array $data): bool
    {
        $stmt = $this->db->prepare(
            "INSERT INTO categories (name, slug, description, icon) VALUES (?, ?, ?, ?)"
        );
        return $stmt->execute([$data['name'], $data['slug'], $data['description'] ?? '', $data['icon'] ?? 'folder']);
    }

    public function update(int $id, array $data): bool
    {
        $stmt = $this->db->prepare(
            "UPDATE categories SET name = ?, description = ?, icon = ? WHERE id = ?"
        );
        return $stmt->execute([$data['name'], $data['description'] ?? '', $data['icon'] ?? 'folder', $id]);
    }
}
