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, bool $showDescription = false): array
    {
        if ($showDescription === false) {
            $stmt = $this->pdo->prepare("SELECT * FROM product WHERE company_id = :company_id AND product_flag = 'a'");
        } else {
            $stmt = $this->pdo->prepare("SELECT * FROM product NATURAL JOIN `description` WHERE company_id = :company_id AND product_flag = 'a'");
        }

        $stmt->execute(['company_id' => $companyId]);
        $products = $stmt->fetchAll(\PDO::FETCH_ASSOC);

        foreach ($products as &$product) {
            $product['product_is_kitchen'] = (bool) $product['product_is_kitchen'];
        }

        return $products;
    }

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

        return $executed ? (int)$this->pdo->lastInsertId() : false;
    }

    public function updateProduct(int $productId, int $companyId, ?string $productName = null, ?float $productPrice = null, ?bool $productIsKitchen = null): bool
    {
        $sql = "UPDATE product SET ";
        $params = [];
        $updates = [];

        if ($productName !== null) {
            $updates[] = "product_name = :product_name";
            $params['product_name'] = $productName;
        }

        if ($productPrice !== null) {
            $updates[] = "product_price = :product_price";
            $params['product_price'] = $productPrice;
        }

        if ($productIsKitchen !== null) {
            $updates[] = "product_is_kitchen = :product_is_kitchen";
            $params['product_is_kitchen'] = $productIsKitchen ? 1 : 0;
        }

        if (empty($updates)) {
            return false; // nada para atualizar
        }

        $sql .= implode(', ', $updates);
        $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]);
    }

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

        public function getProductById(int $productId, int $companyId): ?array
    {
        $stmt = $this->pdo->prepare("SELECT product_id, company_id, category_id, product_is_kitchen, product_name, product_price FROM product WHERE product_id = :product_id AND company_id = :company_id AND product_flag = 'a'");
        $stmt->execute(['product_id' => $productId, 'company_id' => $companyId]);
        $product = $stmt->fetch(\PDO::FETCH_ASSOC);
        if ($product) {
            $product['product_is_kitchen'] = (bool) $product['product_is_kitchen'];
        }
        return $product ?: null;
    }
}