bartender/models/MenuModel.php

<?php

namespace Models;

class MenuModel
{
    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 getMenu(int $companyId): array
    {
        $sql = "
            SELECT 
                c.category_name, 
                p.product_name, 
                p.product_price,
                d.description_text
            FROM product p
            JOIN category c ON c.category_id = p.category_id
            LEFT JOIN description d ON d.product_id = p.product_id AND d.company_id = p.company_id
            WHERE p.company_id = :company_id 
              AND p.product_flag != 'd' 
              AND c.category_flag != 'd'
            ORDER BY c.category_name, p.product_name
        ";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['company_id' => $companyId]);

        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }
}