<?php
declare(strict_types=1);
namespace App\Repositories;
use App\Core\Repository;

final class CategoryRepository extends Repository {
    public function active(): array {
        return $this->all("SELECT * FROM categories WHERE is_active=1 ORDER BY sort_order ASC, name_ar ASC");
    }
    public function allWithCounts(): array {
        return $this->all(
            "SELECT c.*, COUNT(i.id) AS items_count,
                    GROUP_CONCAT(CONCAT(i.item_code, IF(i.item_name IS NULL OR i.item_name='', '', CONCAT(' - ', i.item_name))) ORDER BY i.item_code ASC SEPARATOR '، ') AS items_preview
             FROM categories c
             LEFT JOIN items i ON i.category_id = c.id AND i.is_active = 1
             GROUP BY c.id
             ORDER BY c.is_active DESC, c.sort_order ASC, c.name_ar ASC"
        );
    }
    public function find(int $id): ?array { return $this->one("SELECT * FROM categories WHERE id=? LIMIT 1", [$id]); }
    public function create(array $data): int {
        return $this->insert("INSERT INTO categories(name_ar, name_en, default_price, sort_order, is_active, created_at, updated_at) VALUES(?,?,0.000,?,1,NOW(),NOW())", [trim((string)$data['name_ar']), trim((string)($data['name_en'] ?? '')) ?: null, (int)($data['sort_order'] ?? 0)]);
    }
    public function updateCategory(int $id, array $data): int {
        return $this->exec("UPDATE categories SET name_ar=?, name_en=?, sort_order=?, updated_at=NOW() WHERE id=?", [trim((string)$data['name_ar']), trim((string)($data['name_en'] ?? '')) ?: null, (int)($data['sort_order'] ?? 0), $id]);
    }
    public function moveItems(int $fromCategoryId, int $toCategoryId): int {
        return $this->exec("UPDATE items SET category_id=?, updated_at=NOW() WHERE category_id=?", [$toCategoryId, $fromCategoryId]);
    }
    public function activeItemsCount(int $categoryId): int { return (int)($this->one("SELECT COUNT(*) c FROM items WHERE category_id=? AND is_active=1", [$categoryId])['c'] ?? 0); }
    public function deactivateIfEmpty(int $id): bool {
        if ($this->activeItemsCount($id) > 0) return false;
        $this->exec("UPDATE categories SET is_active=0, updated_at=NOW() WHERE id=?", [$id]);
        return true;
    }
}
