<?php
namespace App\Models;
use App\Models\BaseModel;
use Exception;
/**
* Product Model
* Handles product data with variants, inventory tracking, and search functionality
*/
class Product extends BaseModel
{
protected string $table = 'products';
protected array $fillable = [
'sku', 'name', 'slug', 'description', 'short_description',
'category_id', 'brand', 'base_price', 'compare_price', 'cost_price',
'weight', 'dimensions', 'tags', 'meta_title', 'meta_description',
'status', 'featured', 'track_inventory', 'allow_backorder', 'min_stock_level'
];
protected array $casts = [
'base_price' => 'float',
'compare_price' => 'float',
'cost_price' => 'float',
'weight' => 'float',
'dimensions' => 'json',
'tags' => 'json',
'featured' => 'boolean',
'track_inventory' => 'boolean',
'allow_backorder' => 'boolean',
'min_stock_level' => 'integer'
];
/**
* Get product with all related data
*/
public function getWithDetails(int $id): ?array
{
$product = $this->find($id);
if (!$product) return null;
// Get variants
$product['variants'] = $this->getVariants($id);
// Get images
$product['images'] = $this->getImages($id);
// Get category
$product['category'] = $this->getCategory($product['category_id']);
// Calculate total stock
$product['total_stock'] = $this->getTotalStock($id);
// Check if in stock
$product['in_stock'] = $this->isInStock($id);
return $product;
}
/**
* Get product variants
*/
public function getVariants(int $productId): array
{
return $this->db->query(
"SELECT * FROM product_variants
WHERE product_id = ? AND is_active = 1
ORDER BY position ASC, id ASC",
[$productId]
);
}
/**
* Get single product variant
*/
public function getVariant(int $variantId): ?array
{
return $this->db->queryOne(
"SELECT * FROM product_variants
WHERE id = ? AND is_active = 1",
[$variantId]
);
}
/**
* Get product images
*/
public function getImages(int $productId): array
{
return $this->db->query(
"SELECT * FROM product_images
WHERE product_id = ?
ORDER BY is_primary DESC, position ASC, id ASC",
[$productId]
);
}
/**
* Get category information
*/
public function getCategory(int $categoryId): ?array
{
return $this->db->queryOne(
"SELECT * FROM categories WHERE id = ?",
[$categoryId]
);
}
/**
* Get total stock across all variants
*/
public function getTotalStock(int $productId): int
{
$result = $this->db->queryOne(
"SELECT SUM(inventory_quantity) as total_stock
FROM product_variants
WHERE product_id = ? AND is_active = 1",
[$productId]
);
return (int)($result['total_stock'] ?? 0);
}
/**
* Check if product is in stock
*/
public function isInStock(int $productId): bool
{
$product = $this->find($productId);
if (!$product) return false;
if (!$product['track_inventory']) {
return true; // Always in stock if not tracking inventory
}
$totalStock = $this->getTotalStock($productId);
return $totalStock > 0 || $product['allow_backorder'];
}
/**
* Check stock for specific variant
*/
public function checkVariantStock(int $productId, int $variantId, int $quantity = 1): bool
{
$variant = $this->db->queryOne(
"SELECT pv.inventory_quantity, p.track_inventory, p.allow_backorder
FROM product_variants pv
JOIN products p ON pv.product_id = p.id
WHERE pv.id = ? AND pv.product_id = ? AND pv.is_active = 1",
[$variantId, $productId]
);
if (!$variant) return false;
if (!$variant['track_inventory']) {
return true; // Always available if not tracking inventory
}
return $variant['inventory_quantity'] >= $quantity || $variant['allow_backorder'];
}
/**
* Reserve stock for order
*/
public function reserveStock(int $productId, int $variantId, int $quantity): bool
{
// Check if we can reserve the stock
if (!$this->checkVariantStock($productId, $variantId, $quantity)) {
return false;
}
// Reserve the stock
$updated = $this->db->execute(
"UPDATE product_variants
SET inventory_quantity = inventory_quantity - ?
WHERE id = ? AND product_id = ? AND inventory_quantity >= ?",
[$quantity, $variantId, $productId, $quantity]
);
return $updated > 0;
}
/**
* Release reserved stock (e.g., when order is cancelled)
*/
public function releaseStock(int $productId, int $variantId, int $quantity): bool
{
$updated = $this->db->execute(
"UPDATE product_variants
SET inventory_quantity = inventory_quantity + ?
WHERE id = ? AND product_id = ?",
[$quantity, $variantId, $productId]
);
return $updated > 0;
}
/**
* Search products with filters
*/
public function search(array $filters = [], int $page = 1, int $limit = 20): array
{
$offset = ($page - 1) * $limit;
$params = [];
// Base query
$sql = "SELECT p.*, c.name as category_name, c.slug as category_slug
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
WHERE p.status = 'active'";
// Search query
if (!empty($filters['q'])) {
$sql .= " AND (MATCH(p.name, p.description, p.short_description) AGAINST(? IN NATURAL LANGUAGE MODE)
OR p.name LIKE ? OR p.description LIKE ?)";
$searchTerm = $filters['q'];
$likeTerm = '%' . $searchTerm . '%';
$params[] = $searchTerm;
$params[] = $likeTerm;
$params[] = $likeTerm;
}
// Category filter
if (!empty($filters['category_id'])) {
$sql .= " AND p.category_id = ?";
$params[] = $filters['category_id'];
}
// Price range filter
if (!empty($filters['min_price'])) {
$sql .= " AND p.base_price >= ?";
$params[] = $filters['min_price'];
}
if (!empty($filters['max_price'])) {
$sql .= " AND p.base_price <= ?";
$params[] = $filters['max_price'];
}
// Brand filter
if (!empty($filters['brand'])) {
$sql .= " AND p.brand = ?";
$params[] = $filters['brand'];
}
// Featured filter
if (!empty($filters['featured'])) {
$sql .= " AND p.featured = 1";
}
// In stock filter
if (!empty($filters['in_stock'])) {
$sql .= " AND (
p.track_inventory = 0 OR
p.allow_backorder = 1 OR
EXISTS (
SELECT 1 FROM product_variants pv
WHERE pv.product_id = p.id AND pv.inventory_quantity > 0 AND pv.is_active = 1
)
)";
}
// Sorting
$sortBy = $filters['sort'] ?? 'relevance';
switch ($sortBy) {
case 'price_asc':
$sql .= " ORDER BY p.base_price ASC";
break;
case 'price_desc':
$sql .= " ORDER BY p.base_price DESC";
break;
case 'name':
$sql .= " ORDER BY p.name ASC";
break;
case 'newest':
$sql .= " ORDER BY p.created_at DESC";
break;
case 'featured':
$sql .= " ORDER BY p.featured DESC, p.created_at DESC";
break;
default: // relevance
if (!empty($filters['q'])) {
$sql .= " ORDER BY MATCH(p.name, p.description, p.short_description) AGAINST(? IN NATURAL LANGUAGE MODE) DESC";
$params[] = $filters['q'];
} else {
$sql .= " ORDER BY p.featured DESC, p.created_at DESC";
}
}
// Get total count for pagination (use separate params for count query)
$countSql = preg_replace('/SELECT.*?FROM/', 'SELECT COUNT(*) as total FROM', $sql);
$countSql = preg_replace('/ORDER BY.*$/', '', $countSql);
// Create count params without the extra search parameter for ORDER BY
$countParams = $params;
if (!empty($filters['q']) && ($filters['sort'] ?? 'relevance') === 'relevance') {
array_pop($countParams); // Remove the last search parameter used for ORDER BY
}
$totalResult = $this->db->queryOne($countSql, $countParams);
$total = (int)($totalResult['total'] ?? 0);
// Add pagination
$sql .= " LIMIT ? OFFSET ?";
$params[] = $limit;
$params[] = $offset;
// Execute query
$products = $this->db->query($sql, $params);
// Add stock information to each product
foreach ($products as &$product) {
$product = $this->castAttributes($product);
$product['total_stock'] = $this->getTotalStock($product['id']);
$product['in_stock'] = $this->isInStock($product['id']);
}
return [
'data' => $products,
'pagination' => [
'current_page' => $page,
'per_page' => $limit,
'total' => $total,
'last_page' => ceil($total / $limit),
'from' => $offset + 1,
'to' => min($offset + $limit, $total)
]
];
}
/**
* Get featured products
*/
public function getFeatured(int $limit = 8): array
{
$products = $this->db->query(
"SELECT p.*, c.name as category_name
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
WHERE p.status = 'active' AND p.featured = 1
ORDER BY p.created_at DESC
LIMIT ?",
[$limit]
);
// Add stock information
foreach ($products as &$product) {
$product = $this->castAttributes($product);
$product['total_stock'] = $this->getTotalStock($product['id']);
$product['in_stock'] = $this->isInStock($product['id']);
}
return $products;
}
/**
* Get related products
*/
public function getRelated(int $productId, int $limit = 4): array
{
$product = $this->find($productId);
if (!$product) return [];
$products = $this->db->query(
"SELECT p.*, c.name as category_name
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
WHERE p.status = 'active'
AND p.id != ?
AND (p.category_id = ? OR p.brand = ?)
ORDER BY
CASE WHEN p.category_id = ? THEN 1 ELSE 2 END,
p.featured DESC,
RAND()
LIMIT ?",
[$productId, $product['category_id'], $product['brand'], $product['category_id'], $limit]
);
// Add stock information
foreach ($products as &$relatedProduct) {
$relatedProduct = $this->castAttributes($relatedProduct);
$relatedProduct['total_stock'] = $this->getTotalStock($relatedProduct['id']);
$relatedProduct['in_stock'] = $this->isInStock($relatedProduct['id']);
}
return $products;
}
/**
* Get low stock products (for admin)
*/
public function getLowStock(): array
{
return $this->db->query(
"SELECT p.id, p.name, p.sku, p.min_stock_level,
SUM(pv.inventory_quantity) as total_stock,
COUNT(pv.id) as variant_count
FROM products p
LEFT JOIN product_variants pv ON p.id = pv.product_id AND pv.is_active = 1
WHERE p.status = 'active' AND p.track_inventory = 1
GROUP BY p.id
HAVING total_stock <= p.min_stock_level
ORDER BY total_stock ASC"
);
}
/**
* Update product stock
*/
public function updateStock(int $productId, int $variantId, int $newQuantity): bool
{
$updated = $this->db->execute(
"UPDATE product_variants
SET inventory_quantity = ?, updated_at = NOW()
WHERE id = ? AND product_id = ?",
[$newQuantity, $variantId, $productId]
);
return $updated > 0;
}
/**
* Generate unique SKU
*/
public function generateSku(string $prefix = 'PRD'): string
{
do {
$sku = $prefix . str_pad(mt_rand(1, 999999), 6, '0', STR_PAD_LEFT);
$exists = $this->db->queryOne("SELECT id FROM products WHERE sku = ?", [$sku]);
} while ($exists);
return $sku;
}
/**
* Generate unique slug
*/
public 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 products 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 products with advanced filtering (for ProductController)
*/
public function getProducts(array $filters = [], int $limit = 20, int $offset = 0): array
{
$page = ($offset / $limit) + 1;
$result = $this->search($filters, $page, $limit);
return [
'products' => $result['data'],
'total' => $result['pagination']['total']
];
}
/**
* Search products with suggestions (for ProductController)
*/
public function searchProducts(string $query, array $filters = [], int $limit = 20, int $offset = 0): array
{
$filters['q'] = $query;
$page = ($offset / $limit) + 1;
$result = $this->search($filters, $page, $limit);
// Get suggestions
$suggestions = $this->getSuggestions($query, 5);
return [
'products' => $result['data'],
'total' => $result['pagination']['total'],
'suggestions' => $suggestions
];
}
/**
* Get search suggestions
*/
public function getSuggestions(string $query, int $limit = 10): array
{
$suggestions = $this->db->query(
"SELECT DISTINCT name, slug
FROM products
WHERE status = 'active'
AND (name LIKE ? OR description LIKE ?)
ORDER BY
CASE
WHEN name LIKE ? THEN 1
WHEN name LIKE ? THEN 2
ELSE 3
END,
name ASC
LIMIT ?",
["%$query%", "%$query%", $query, "$query%", $limit]
);
return $suggestions;
}
/**
* Get product with variants (alias for getWithDetails)
*/
public function getWithVariants(int $id): ?array
{
return $this->getWithDetails($id);
}
/**
* Get featured products (alias for getFeatured)
*/
public function getFeaturedProducts(int $limit = 10): array
{
return $this->getFeatured($limit);
}
/**
* Get related products (alias for getRelated)
*/
public function getRelatedProducts(int $productId, int $limit = 8): array
{
return $this->getRelated($productId, $limit);
}
/**
* Get stock information for a product/variant
*/
public function getStockInfo(int $productId, ?int $variantId = null): ?array
{
if ($variantId) {
return $this->db->queryOne(
"SELECT inventory_quantity, sku
FROM product_variants
WHERE id = ? AND product_id = ? AND is_active = 1",
[$variantId, $productId]
);
} else {
return [
'total_stock' => $this->getTotalStock($productId),
'variant_count' => count($this->getVariants($productId))
];
}
}
/**
* Check if product has orders
*/
public function hasOrders(int $productId): bool
{
$result = $this->db->queryOne(
"SELECT COUNT(*) as order_count
FROM order_items
WHERE product_id = ?",
[$productId]
);
return (int)($result['order_count'] ?? 0) > 0;
}
/**
* Check stock (alias for checkVariantStock)
*/
public function checkStock(int $productId, ?int $variantId = null, int $quantity = 1): bool
{
if ($variantId) {
return $this->checkVariantStock($productId, $variantId, $quantity);
} else {
$totalStock = $this->getTotalStock($productId);
$product = $this->find($productId);
if (!$product || !$product['track_inventory']) {
return true;
}
return $totalStock >= $quantity || $product['allow_backorder'];
}
}
/**
* Count products by category
*/
public function countByCategory(int $categoryId): int
{
$result = $this->db->queryOne(
"SELECT COUNT(*) as count FROM products WHERE category_id = ?",
[$categoryId]
);
return (int)($result['count'] ?? 0);
}
}