bartender/models/CategoryModel.php

<?php

namespace Models;

class CategoryModel
{
    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 getCategories(int $companyId): array
    {
        $stmt = $this->pdo->prepare("SELECT * FROM category WHERE company_id = :company_id AND category_flag = 'a'");
        $stmt->execute(['company_id' => $companyId]);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    public function createCategory(string $name, int $companyId): bool
    {
        $stmt = $this->pdo->prepare("INSERT INTO category (category_name, category_flag, company_id)
            VALUES (:name, 'a', :company_id)");
        return $stmt->execute(['name' => $name, 'company_id' => $companyId]);
    }

    public function deleteByName(string $name, int $companyId): bool
    {
        $stmt = $this->pdo->prepare("UPDATE category SET category_flag = 'd'
            WHERE category_name = :name AND company_id = :company_id AND category_flag = 'a'");
        return $stmt->execute(['name' => $name, 'company_id' => $companyId]);
    }

    public function addProductToCategory(string $productName, float $price, string $categoryName, int $companyId): bool
    {
        $stmt = $this->pdo->prepare("SELECT category_id FROM category WHERE category_name = :name AND company_id = :company_id AND category_flag = 'a'");
        $stmt->execute(['name' => $categoryName, 'company_id' => $companyId]);
        $category = $stmt->fetch(\PDO::FETCH_ASSOC);

        if (!$category) {
            return false;
        }

        $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' => $productName, 'price' => $price, 'category_id' => $category['category_id'], 'company_id' => $companyId]);
    }
}