Project 8: Product Catalogue with Categories

CREATE TABLE categories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(80) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_id INT UNSIGNED NOT NULL,
    name VARCHAR(150) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    image VARCHAR(80) NULL,
    FOREIGN KEY (category_id) REFERENCES categories(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

<?php

declare(strict_types=1);

final class CatalogRepository
{
    public function __construct(private PDO $pdo) {}

    public function categories(): array
    {
        return $this->pdo->query(
            "SELECT c.id, c.name, c.slug, COUNT(i.id) AS item_count
               FROM categories c
          LEFT JOIN items i ON i.category_id = c.id
           GROUP BY c.id, c.name, c.slug
           ORDER BY c.name"
        )->fetchAll();
    }

    public function items(?string $categorySlug = null): array
    {
        $sql = "SELECT i.id, i.name, i.price, i.stock, c.name AS category
                  FROM items i
                  JOIN categories c ON c.id = i.category_id";
        $params = [];

        if ($categorySlug !== null) {
            $sql .= " WHERE c.slug = :slug";
            $params["slug"] = $categorySlug;
        }

        $stmt = $this->pdo->prepare($sql . " ORDER BY i.name");
        $stmt->execute($params);

        return $stmt->fetchAll();
    }

    public function create(int $categoryId, string $name, float $price, int $stock): int
    {
        if (!$this->categoryExists($categoryId)) {
            throw new InvalidArgumentException("The category does not exist.");
        }

        if (trim($name) === "" || $price < 0 || $stock < 0) {
            throw new InvalidArgumentException("Check the name, price and stock.");
        }

        $this->pdo->prepare("INSERT INTO items (category_id, name, price, stock) VALUES (:category, :name, :price, :stock)")
            ->execute(["category" => $categoryId, "name" => trim($name), "price" => $price, "stock" => $stock]);

        return (int) $this->pdo->lastInsertId();
    }

    /** Takes stock away only if enough is available. Returns false when there is not. */
    public function sell(int $itemId, int $quantity): bool
    {
        if ($quantity < 1) {
            throw new InvalidArgumentException("Quantity must be at least 1.");
        }

        $stmt = $this->pdo->prepare("UPDATE items SET stock = stock - :qty WHERE id = :id AND stock >= :needed");
        $stmt->execute(["qty" => $quantity, "id" => $itemId, "needed" => $quantity]);

        return $stmt->rowCount() === 1;
    }

    public function lowStock(int $threshold = 5): array
    {
        $stmt = $this->pdo->prepare("SELECT name, stock FROM items WHERE stock <= :threshold ORDER BY stock, name");
        $stmt->bindValue(":threshold", $threshold, PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll();
    }

    public function inventoryValue(): float
    {
        return (float) $this->pdo->query("SELECT COALESCE(SUM(price * stock), 0) FROM items")->fetchColumn();
    }

    private function categoryExists(int $id): bool
    {
        $stmt = $this->pdo->prepare("SELECT COUNT(*) FROM categories WHERE id = :id");
        $stmt->execute(["id" => $id]);

        return (int) $stmt->fetchColumn() === 1;
    }
}

<?php
require __DIR__ . "/CatalogRepository.php";

$catalog = new CatalogRepository(require __DIR__ . "/db.php");

foreach ($catalog->categories() as $c) {
    echo $c["name"] . ": " . $c["item_count"] . " item(s)\n";
}

echo "--\n";
echo implode(", ", array_column($catalog->items("stationery"), "name")) . "\n";
echo implode(", ", array_map(fn($i) => $i["name"] . " (" . $i["category"] . ")", $catalog->items())) . "\n";

$id = $catalog->create(2, "SQL Basics", 299.0, 3);
echo "created item $id\n";

try {
    $catalog->create(99, "Ghost", 1, 1);
} catch (InvalidArgumentException $e) {
    echo $e->getMessage() . "\n";
}

var_dump($catalog->sell($id, 2));
var_dump($catalog->sell($id, 2));       // only 1 left
var_dump($catalog->sell(4, 1));         // the backpack has 0 in stock

echo implode(", ", array_map(fn($i) => $i["name"] . "=" . $i["stock"], $catalog->lowStock())) . "\n";
echo "inventory value: " . $catalog->inventoryValue();

Bags: 1 item(s)
Books: 1 item(s)
Stationery: 2 item(s)
--
Marker, Pen
Atlas (Books), Backpack (Bags), Marker (Stationery), Pen (Stationery)
created item 5
The category does not exist.
bool(true)
bool(false)
bool(false)
Backpack=0, SQL Basics=1
inventory value: 5649

<?php
declare(strict_types=1);

require __DIR__ . "/functions.php";
require __DIR__ . "/CatalogRepository.php";

$catalog = new CatalogRepository(require __DIR__ . "/db.php");

$categories = $catalog->categories();
$slugs = array_column($categories, "slug");
$slug = in_array($_GET["category"] ?? "", $slugs, true) ? $_GET["category"] : null;

$items = $catalog->items($slug);
?>
<h1>Catalogue</h1>

<nav aria-label="Categories">
  <a href="index.php">All</a>
  <?php foreach ($categories as $c): ?>
    | <a href="?category=<?= h($c["slug"]) ?>"><?= h($c["name"]) ?> (<?= (int) $c["item_count"] ?>)</a>
  <?php endforeach; ?>
</nav>

<ul>
  <?php foreach ($items as $i): ?>
    <li>
      <strong><?= h($i["name"]) ?></strong> in <?= h($i["category"]) ?>:
      <?= number_format((float) $i["price"], 2) ?>
      <?= (int) $i["stock"] > 0 ? "(" . (int) $i["stock"] . " in stock)" : "(sold out)" ?>
    </li>
  <?php endforeach; ?>
</ul>