SWAMPy

✎ CARVE FLESH

EDITING: ProductsController.php

<?php
/**
 * Flora and Creations — Public Products Controller
 *
 * Handles storefront product listing with filtering and product detail.
 */

namespace App\Controllers\PublicApi;

use App\Database;
use App\Utils\Response;
use App\Utils\Request;

class ProductsController
{
    /**
     * GET /api/products
     * List products with optional filters: category, occasion, price range, search, sort.
     */
    public function index(array $params): void
    {
        $db = Database::getConnection();
        $query = Request::query();

$sql = "SELECT p.id, p.name, p.slug, p.description, p.base_price, p.status, p.featured,
                       p.created_at, p.updated_at
                FROM products p
                WHERE p.status = 'active'";

$conditions = [];
        $bindings   = [];

// Filter by product type (category slug)
        if (!empty($query['category'])) {
            $sql .= " AND p.id IN (SELECT pc.product_id FROM product_categories pc
                        JOIN categories c ON c.id = pc.category_id
                        WHERE c.slug = :category AND c.type = 'product_type')";
            $conditions[':category'] = $query['category'];
        }

// Filter by occasion
        if (!empty($query['occasion'])) {
            $sql .= " AND p.id IN (SELECT pc.product_id FROM product_categories pc
                        JOIN categories c ON c.id = pc.category_id
                        WHERE c.slug = :occasion AND c.type = 'occasion')";
            $conditions[':occasion'] = $query['occasion'];
        }

// Price range
        if (!empty($query['min_price'])) {
            $sql .= " AND p.base_price >= :min_price";
            $conditions[':min_price'] = (float) $query['min_price'];
        }
        if (!empty($query['max_price'])) {
            $sql .= " AND p.base_price <= :max_price";
            $conditions[':max_price'] = (float) $query['max_price'];
        }

// Featured
        if (isset($query['featured'])) {
            $featured = filter_var($query['featured'], FILTER_VALIDATE_BOOLEAN);
            $sql .= " AND p.featured = :featured";
            $conditions[':featured'] = $featured ? 1 : 0;
        }

// Search
        if (!empty($query['search'])) {
            $sql .= " AND (p.name LIKE :search OR p.description LIKE :search)";
            $conditions[':search'] = '%' . $query['search'] . '%';
        }

// Sorting
        // Default sort field is created_at; default direction is now ASC
        // (oldest first) instead of DESC, so newly added products land at
        // the bottom of the shop page rather than jumping to the top.
        // Explicitly passing ?order=desc still sorts newest-first on request.
        $allowedSorts = ['base_price', 'created_at', 'name'];
        $sortField    = in_array($query['sort'] ?? '', $allowedSorts) ? $query['sort'] : 'created_at';
        $sortDir      = strtoupper($query['order'] ?? '') === 'DESC' ? 'DESC' : 'ASC';
        $sql .= " ORDER BY p.$sortField $sortDir";

// Pagination
        $page    = max(1, (int) ($query['page'] ?? 1));
        $perPage = min(50, max(1, (int) ($query['per_page'] ?? 12)));
        $offset  = ($page - 1) * $perPage;

// Get total count
        $countSql = "SELECT COUNT(*) as total FROM ($sql) as count_query";
        $countStmt = $db->prepare($countSql);
        foreach ($conditions as $key => $value) {
            $countStmt->bindValue($key, $value, is_int($value) ? \PDO::PARAM_INT : \PDO::PARAM_STR);
        }
        $countStmt->execute();
        $total = $countStmt->fetch()['total'];

$sql .= " LIMIT :offset, :per_page";

$stmt = $db->prepare($sql);
        foreach ($conditions as $key => $value) {
            $stmt->bindValue($key, $value, is_int($value) ? \PDO::PARAM_INT : \PDO::PARAM_STR);
        }
        $stmt->bindValue(':offset', $offset, \PDO::PARAM_INT);
        $stmt->bindValue(':per_page', $perPage, \PDO::PARAM_INT);
        $stmt->execute();

$products = $stmt->fetchAll();

// Attach categories (product_type + occasion) and thumbnail image to each product
        foreach ($products as &$product) {
            $catStmt = $db->prepare("
                SELECT c.name, c.type, c.slug FROM categories c
                JOIN product_categories pc ON pc.category_id = c.id
                WHERE pc.product_id = :pid
            ");
            $catStmt->execute([':pid' => $product['id']]);
            $cats = $catStmt->fetchAll();
            $product['product_type'] = array_values(array_filter($cats, fn($c) => $c['type'] === 'product_type'));
            $product['occasions']    = array_values(array_filter($cats, fn($c) => $c['type'] === 'occasion'));

// Thumbnail
            $imgStmt = $db->prepare("SELECT image_url FROM product_images WHERE product_id = :pid ORDER BY sort_order ASC LIMIT 1");
            $imgStmt->execute([':pid' => $product['id']]);
            $product['thumbnail'] = $imgStmt->fetchColumn() ?: null;
        }

Response::paginated($products, (int) $total, $page, $perPage);
    }

/**
     * GET /api/products/:slug
     * Product detail with variants, add-ons, images.
     */
    public function show(array $params): void
    {
        $db = Database::getConnection();
        $slug = $params['slug'];

$stmt = $db->prepare("SELECT * FROM products WHERE slug = :slug LIMIT 1");
        $stmt->execute([':slug' => $slug]);
        $product = $stmt->fetch();

if (!$product) {
            Response::notFound('Product not found.');
        }

if ($product['status'] !== 'active') {
            Response::notFound('Product not available.');
        }

// Categories
        $catStmt = $db->prepare("
            SELECT c.name, c.type, c.slug FROM categories c
            JOIN product_categories pc ON pc.category_id = c.id
            WHERE pc.product_id = :pid
        ");
        $catStmt->execute([':pid' => $product['id']]);
        $cats = $catStmt->fetchAll();
        $product['product_type'] = array_values(array_filter($cats, fn($c) => $c['type'] === 'product_type'));
        $product['occasions']    = array_values(array_filter($cats, fn($c) => $c['type'] === 'occasion'));

// Images (sorted)
        $imgStmt = $db->prepare("SELECT id, image_url, sort_order FROM product_images WHERE product_id = :pid ORDER BY sort_order ASC");
        $imgStmt->execute([':pid' => $product['id']]);
        $product['images'] = $imgStmt->fetchAll();

// Variants
        $varStmt = $db->prepare("SELECT id, size, color, price, stock_status, sku FROM product_variants WHERE product_id = :pid");
        $varStmt->execute([':pid' => $product['id']]);
        $product['variants'] = $varStmt->fetchAll();

// Available add-ons
        $addonStmt = $db->query("SELECT id, name, price, type, active FROM add_ons WHERE active = 1");
        $product['add_ons'] = $addonStmt->fetchAll();

Response::ok($product);
    }
}

CANCEL

Name	Type	Size	Modified	Actions
↩ ..	DIR	—	—
📄 CartController.php	PHP	5.4 KB	2026-07-15 09:17	EDIT
📄 CategoriesController.php	PHP	775 B	2026-07-15 09:17	EDIT
📄 ContactController.php	PHP	2.7 KB	2026-11-04 12:42	EDIT
📄 DeliveryZonesController.php	PHP	579 B	2026-07-15 09:17	EDIT
📄 OrdersController.php	PHP	18 KB	2026-07-15 09:17	EDIT
📄 ProductsController.php	PHP	7 KB	2026-07-19 07:28	EDIT
📄 SettingsController.php	PHP	890 B	2026-07-15 09:17	EDIT