<?php
namespace App\Models;
use App\Models\BaseModel;
/**
* Category Model
* Handles hierarchical product categories with nested structure support
*/
class Category extends BaseModel
{
protected string $table = 'categories';
protected array $fillable = [
'name', 'slug', 'parent_id', 'description', 'image',
'sort_order', 'is_active', 'meta_title', 'meta_description'
];
protected array $casts = [
'parent_id' => 'integer',
'sort_order' => 'integer',
'is_active' => 'boolean'
];
/**
* Get category with children
*/
public function getWithChildren(int $id): ?array
{
$category = $this->find($id);
if (!$category) return null;
$category['children'] = $this->getChildren($id);
$category['parent'] = $this->getParent($category['parent_id']);
$category['product_count'] = $this->getProductCount($id);
return $category;
}
/**
* Get child categories
*/
public function getChildren(int $parentId): array
{
$children = $this->db->query(
"SELECT * FROM categories
WHERE parent_id = ? AND is_active = 1
ORDER BY sort_order ASC, name ASC",
[$parentId]
);
// Recursively get children for each child
foreach ($children as &$child) {
$child = $this->castAttributes($child);
$child['children'] = $this->getChildren($child['id']);
$child['product_count'] = $this->getProductCount($child['id']);
}
return $children;
}
/**
* Get parent category
*/
public function getParent(?int $parentId): ?array
{
if (!$parentId) return null;
$parent = $this->find($parentId);
return $parent ? $this->castAttributes($parent) : null;
}
/**
* Get all root categories (no parent)
*/
public function getRootCategories(): array
{
$categories = $this->db->query(
"SELECT * FROM categories
WHERE parent_id IS NULL AND is_active = 1
ORDER BY sort_order ASC, name ASC"
);
foreach ($categories as &$category) {
$category = $this->castAttributes($category);
$category['children'] = $this->getChildren($category['id']);
$category['product_count'] = $this->getProductCount($category['id'], true);
}
return $categories;
}
/**
* Get category tree (hierarchical structure)
*/
public function getCategoryTree(): array
{
return $this->getRootCategories();
}
/**
* Get flat list of all categories
*/
public function getFlatList(bool $activeOnly = true): array
{
$sql = "SELECT * FROM categories";
$params = [];
if ($activeOnly) {
$sql .= " WHERE is_active = 1";
}
$sql .= " ORDER BY sort_order ASC, name ASC";
$categories = $this->db->query($sql, $params);
foreach ($categories as &$category) {
$category = $this->castAttributes($category);
$category['product_count'] = $this->getProductCount($category['id']);
// Add breadcrumb path
$category['breadcrumb'] = $this->getBreadcrumb($category['id']);
}
return $categories;
}
/**
* Get category by slug
*/
public function getBySlug(string $slug): ?array
{
$category = $this->db->queryOne(
"SELECT * FROM categories WHERE slug = ? AND is_active = 1",
[$slug]
);
if (!$category) return null;
return $this->getWithChildren($category['id']);
}
/**
* Get product count for category
*/
public function getProductCount(int $categoryId, bool $includeChildren = false): int
{
if ($includeChildren) {
// Get all descendant category IDs
$categoryIds = $this->getAllDescendantIds($categoryId);
$categoryIds[] = $categoryId;
$placeholders = str_repeat('?,', count($categoryIds) - 1) . '?';
$result = $this->db->queryOne(
"SELECT COUNT(*) as count
FROM products
WHERE category_id IN ({$placeholders}) AND status = 'active'",
$categoryIds
);
} else {
$result = $this->db->queryOne(
"SELECT COUNT(*) as count
FROM products
WHERE category_id = ? AND status = 'active'",
[$categoryId]
);
}
return (int)($result['count'] ?? 0);
}
/**
* Get all descendant category IDs
*/
public function getAllDescendantIds(int $categoryId): array
{
$descendants = [];
$children = $this->db->query(
"SELECT id FROM categories WHERE parent_id = ?",
[$categoryId]
);
foreach ($children as $child) {
$descendants[] = $child['id'];
$descendants = array_merge($descendants, $this->getAllDescendantIds($child['id']));
}
return $descendants;
}
/**
* Get breadcrumb path for category
*/
public function getBreadcrumb(int $categoryId): array
{
$breadcrumb = [];
$category = $this->find($categoryId);
while ($category) {
array_unshift($breadcrumb, [
'id' => $category['id'],
'name' => $category['name'],
'slug' => $category['slug']
]);
if ($category['parent_id']) {
$category = $this->find($category['parent_id']);
} else {
break;
}
}
return $breadcrumb;
}
/**
* Create category with auto-generated slug
*/
public function createCategory(array $data): int
{
// Generate slug if not provided
if (empty($data['slug'])) {
$data['slug'] = $this->generateSlug($data['name']);
} else {
$data['slug'] = $this->generateSlug($data['slug']);
}
// Set default sort order
if (!isset($data['sort_order'])) {
$data['sort_order'] = $this->getNextSortOrder($data['parent_id'] ?? null);
}
return $this->create($data);
}
/**
* Update category
*/
public function updateCategory(int $id, array $data): bool
{
// Generate new slug if name changed
if (!empty($data['name']) && empty($data['slug'])) {
$data['slug'] = $this->generateSlug($data['name'], $id);
} elseif (!empty($data['slug'])) {
$data['slug'] = $this->generateSlug($data['slug'], $id);
}
return $this->updateById($id, $data) > 0;
}
/**
* Delete category (with children handling)
*/
public function deleteCategory(int $id): bool
{
// Check if category has children
$children = $this->db->query("SELECT id FROM categories WHERE parent_id = ?", [$id]);
if (!empty($children)) {
throw new \Exception("Cannot delete category with child categories");
}
// Check if category has products
$productCount = $this->getProductCount($id);
if ($productCount > 0) {
throw new \Exception("Cannot delete category with products");
}
return $this->deleteById($id) > 0;
}
/**
* Move category to new parent
*/
public function moveCategory(int $categoryId, ?int $newParentId): bool
{
// Prevent moving category to its own descendant
if ($newParentId && $this->isDescendant($categoryId, $newParentId)) {
throw new \Exception("Cannot move category to its own descendant");
}
return $this->updateById($categoryId, ['parent_id' => $newParentId]) > 0;
}
/**
* Check if target is descendant of source
*/
private function isDescendant(int $sourceId, int $targetId): bool
{
$descendants = $this->getAllDescendantIds($sourceId);
return in_array($targetId, $descendants);
}
/**
* Reorder categories
*/
public function reorderCategories(array $categoryOrders): bool
{
return $this->db->transaction(function() use ($categoryOrders) {
foreach ($categoryOrders as $order) {
$this->updateById($order['id'], ['sort_order' => $order['sort_order']]);
}
return true;
});
}
/**
* Generate unique slug
*/
private function generateSlug(string $name, ?int $excludeId = null): string
{
$slug = strtolower(trim(preg_replace('/[^A-Za-z0-9-]+/', '-', $name)));
$originalSlug = $slug;
$counter = 1;
do {
$query = "SELECT id FROM categories WHERE slug = ?";
$params = [$slug];
if ($excludeId) {
$query .= " AND id != ?";
$params[] = $excludeId;
}
$exists = $this->db->queryOne($query, $params);
if ($exists) {
$slug = $originalSlug . '-' . $counter;
$counter++;
}
} while ($exists);
return $slug;
}
/**
* Get next sort order for parent
*/
private function getNextSortOrder(?int $parentId): int
{
$result = $this->db->queryOne(
"SELECT MAX(sort_order) as max_order FROM categories WHERE parent_id " .
($parentId ? "= ?" : "IS NULL"),
$parentId ? [$parentId] : []
);
return ((int)($result['max_order'] ?? 0)) + 1;
}
/**
* Get categories for admin with statistics
*/
public function getAdminCategories(): array
{
$categories = $this->db->query(
"SELECT c.*,
COUNT(p.id) as product_count,
parent.name as parent_name
FROM categories c
LEFT JOIN products p ON c.id = p.category_id AND p.status = 'active'
LEFT JOIN categories parent ON c.parent_id = parent.id
GROUP BY c.id
ORDER BY c.sort_order ASC, c.name ASC"
);
foreach ($categories as &$category) {
$category = $this->castAttributes($category);
$category['product_count'] = (int)$category['product_count'];
// Get children count
$childrenCount = $this->db->queryOne(
"SELECT COUNT(*) as count FROM categories WHERE parent_id = ?",
[$category['id']]
);
$category['children_count'] = (int)($childrenCount['count'] ?? 0);
}
return $categories;
}
/**
* Get categories with optional filters
*/
public function getCategories(array $filters = []): array
{
$conditions = [];
$params = [];
// Apply filters
if (!empty($filters['search'])) {
$conditions[] = "(c.name LIKE ? OR c.description LIKE ?)";
$params[] = "%{$filters['search']}%";
$params[] = "%{$filters['search']}%";
}
if (!empty($filters['status'])) {
if ($filters['status'] === 'active') {
$conditions[] = "c.is_active = 1";
} elseif ($filters['status'] === 'inactive') {
$conditions[] = "c.is_active = 0";
}
}
if (isset($filters['parent_id'])) {
if ($filters['parent_id'] === null || $filters['parent_id'] === '') {
$conditions[] = "c.parent_id IS NULL";
} else {
$conditions[] = "c.parent_id = ?";
$params[] = (int)$filters['parent_id'];
}
}
$whereClause = '';
if (!empty($conditions)) {
$whereClause = 'WHERE ' . implode(' AND ', $conditions);
}
$categories = $this->db->query(
"SELECT c.*,
COUNT(p.id) as product_count,
parent.name as parent_name
FROM categories c
LEFT JOIN products p ON c.id = p.category_id AND p.status = 'active'
LEFT JOIN categories parent ON c.parent_id = parent.id
{$whereClause}
GROUP BY c.id
ORDER BY c.sort_order ASC, c.name ASC",
$params
);
foreach ($categories as &$category) {
$category = $this->castAttributes($category);
$category['product_count'] = (int)$category['product_count'];
// Get children count
$childrenCount = $this->db->queryOne(
"SELECT COUNT(*) as count FROM categories WHERE parent_id = ?",
[$category['id']]
);
$category['children_count'] = (int)($childrenCount['count'] ?? 0);
}
return $categories;
}
/**
* Count subcategories of a category
*/
public function countSubcategories(int $categoryId): int
{
$result = $this->db->queryOne(
"SELECT COUNT(*) as count FROM categories WHERE parent_id = ?",
[$categoryId]
);
return (int)($result['count'] ?? 0);
}
/**
* Search categories
*/
public function searchCategories(string $query): array
{
$categories = $this->db->query(
"SELECT * FROM categories
WHERE (name LIKE ? OR description LIKE ?) AND is_active = 1
ORDER BY name ASC",
["%{$query}%", "%{$query}%"]
);
foreach ($categories as &$category) {
$category = $this->castAttributes($category);
$category['breadcrumb'] = $this->getBreadcrumb($category['id']);
}
return $categories;
}
}