<?php

declare(strict_types=1);

namespace App\Models;

use Core\Database;

final class CategoryModel
{
    private Database $db;

    public function __construct()
    {
        $this->db = Database::getInstance();
    }

    /** @return array<int, array<string,mixed>> */
    public function all(int $page = 1, int $perPage = ITEMS_PER_PAGE): array
    {
        $offset = ($page - 1) * $perPage;
        return $this->db->fetchAll(
            "SELECT c.*, u.name AS created_by_name,
                    (SELECT COUNT(*) FROM menu_items mi WHERE mi.category_id = c.id) AS item_count
               FROM categories c
               LEFT JOIN users u ON u.id = c.created_by
              ORDER BY c.sort_order ASC, c.id DESC
              LIMIT ? OFFSET ?",
            [$perPage, $offset]
        );
    }

    public function count(): int
    {
        return (int) $this->db->fetchValue("SELECT COUNT(*) FROM categories");
    }

    /** @return array<string,mixed>|null */
    public function find(int $id): ?array
    {
        return $this->db->fetchOne(
            "SELECT * FROM categories WHERE id = ?", [$id]
        );
    }

    public function slugExists(string $slug, int $excludeId = 0): bool
    {
        return (int) $this->db->fetchValue(
            "SELECT COUNT(*) FROM categories WHERE slug = ? AND id != ?",
            [$slug, $excludeId]
        ) > 0;
    }

    public function create(array $data): int
    {
        return $this->db->insert('categories', $data);
    }

    public function update(int $id, array $data): int
    {
        return $this->db->update('categories', $data, ['id' => $id]);
    }

    public function delete(int $id): int
    {
        return $this->db->delete('categories', ['id' => $id]);
    }

    public function toggle(int $id): bool
    {
        $current = (int) $this->db->fetchValue(
            "SELECT is_active FROM categories WHERE id = ?", [$id]
        );
        $this->db->update('categories', ['is_active' => $current ? 0 : 1], ['id' => $id]);
        return !$current;
    }

    /** All active categories for dropdowns */
    public function activeList(): array
    {
        return $this->db->fetchAll(
            "SELECT id, name FROM categories WHERE is_active = 1 ORDER BY sort_order ASC, name ASC"
        );
    }
}
