= 0) {
$query .= " AND p.price >= ?";
$params[] = $min_price;
}
if ($max_price > 0 && $max_price > $min_price) {
$query .= " AND p.price <= ?";
$params[] = $max_price;
}
if (isset($_GET['in_stock']) && $_GET['in_stock'] === '1') {
$query .= " AND p.stock > 0";
}
if (!empty($size)) {
$query .= " AND FIND_IN_SET(?, p.sizes) > 0";
$params[] = $size;
}
if (!empty($color)) {
$query .= " AND FIND_IN_SET(?, p.colors) > 0";
$params[] = $color;
}
// Sorting logic
switch ($sort) {
case 'price_asc':
$query .= " ORDER BY p.price ASC";
break;
case 'price_desc':
$query .= " ORDER BY p.price DESC";
break;
case 'best_selling':
// Sum total quantities sold in order items
$query .= " ORDER BY (SELECT IFNULL(SUM(oi.quantity), 0) FROM order_items oi WHERE oi.product_id = p.id) DESC";
break;
case 'newest':
default:
$query .= " ORDER BY p.created_at DESC";
break;
}
$stmt = $pdo->prepare($query);
$stmt->execute($params);
$products = $stmt->fetchAll();
// Get Best Selling / Featured for Flash Sale block
$flashStmt = $pdo->query("SELECT * FROM products WHERE status = 'approved' AND stock > 0 ORDER BY stock DESC LIMIT 4");
$flashProducts = $flashStmt->fetchAll();
renderHeader(__('index_page_title'));
?>