<?php
class Category extends Model {
    protected static string $table = 'categories';

    public static function active(): array {
        $stmt = db()->prepare("SELECT * FROM categories WHERE tenant_id=? ORDER BY name");
        $stmt->execute([static::tenantId()]);
        return $stmt->fetchAll();
    }

    public static function getAllWithParentNames(int $tenantId): array {
        $stmt = db()->prepare("
            SELECT c.*, p.name as parent_name 
            FROM categories c 
            LEFT JOIN categories p ON c.parent_id = p.id 
            WHERE c.tenant_id = ? 
            ORDER BY c.name
        ");
        $stmt->execute([$tenantId]);
        return $stmt->fetchAll();
    }

    public static function create(array $data): int {
        $stmt = db()->prepare(
            "INSERT INTO categories (tenant_id, name, parent_id, description) VALUES (?, ?, ?, ?)"
        );
        $stmt->execute([
            Auth::tenantId(),
            $data['name'],
            $data['parent_id'] ?? null,
            $data['description'] ?? null
        ]);
        return (int)db()->lastInsertId();
    }

    public static function update(int $id, array $data): bool {
        $stmt = db()->prepare(
            "UPDATE categories SET name=?, parent_id=?, description=? WHERE id=? AND tenant_id=?"
        );
        return $stmt->execute([
            $data['name'],
            $data['parent_id'] ?? null,
            $data['description'] ?? null,
            $id,
            Auth::tenantId()
        ]);
    }

    public static function delete(int $id): bool {
        // Check if category has products
        $stmt = db()->prepare("SELECT COUNT(*) FROM products WHERE tenant_id=? AND category_id=?");
        $stmt->execute([Auth::tenantId(), $id]);
        $productCount = $stmt->fetchColumn();
        
        // Check if category has subcategories
        $stmt = db()->prepare("SELECT COUNT(*) FROM categories WHERE tenant_id=? AND parent_id=?");
        $stmt->execute([Auth::tenantId(), $id]);
        $subCategoryCount = $stmt->fetchColumn();
        
        if ($productCount > 0 || $subCategoryCount > 0) {
            return false; // Cannot delete if has products or subcategories
        }
        
        $stmt = db()->prepare("DELETE FROM categories WHERE id=? AND tenant_id=?");
        return $stmt->execute([$id, Auth::tenantId()]);
    }
}
