bartender/models/ProductModel.php

<?php

namespace Models;

class ProductModel
{
    private \PDO $pdo;

    public function __construct()
    {
        $dbFile = $_ENV['DB_FILE'];
        $dbPath = __DIR__ . '/../' . $dbFile;
        $this->pdo = new \PDO("sqlite:" . $dbPath);
        $this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
    }

    public function getProducts(int $companyId): array
    {
        $stmt = $this->pdo->prepare("SELECT * FROM product WHERE company_id = :company_id AND product_flag = 'a'");
        $stmt->execute(['company_id' => $companyId]);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    public function createProduct(string $name, float $price, int $categoryId, int $companyId): bool
    {
        $stmt = $this->pdo->prepare("INSERT INTO product (product_name, product_price, category_id, company_id, product_flag)
                                    VALUES (:name, :price, :category_id, :company_id, 'a')");
        return $stmt->execute([
            'name' => $name,
            'price' => $price,
            'category_id' => $categoryId,
            'company_id' => $companyId
        ]);
    }

    public function updateProduct(int $productId, int $companyId, ?string $productName = null, ?float $productPrice = null): bool
    {
        $sql = "UPDATE product SET ";
        $params = [];
        if ($productName !== null) {
            $sql .= "product_name = :product_name";
            $params['product_name'] = $productName;
        }
        if ($productPrice !== null) {
            if ($productName !== null) {
                $sql .= ", ";
            }
            $sql .= "product_price = :product_price";
            $params['product_price'] = $productPrice;
        }

        if (empty($params)) {
            return false; // Nothing to update
        }

        $sql .= " WHERE product_id = :product_id AND company_id = :company_id AND product_flag = 'a'";
        $params['product_id'] = $productId;
        $params['company_id'] = $companyId;

        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute($params);
    }

    public function deleteProduct(int $productId, int $companyId): bool
    {
        $stmt = $this->pdo->prepare("UPDATE product SET product_flag = 'd'
                                    WHERE product_id = :product_id AND company_id = :company_id AND product_flag = 'a'");
        return $stmt->execute(['product_id' => $productId, 'company_id' => $companyId]);
    }
}