bartender/models/KitchenModel.php

<?php

namespace Models;

class KitchenModel
{
    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 getKitchenOrders(int $companyId): array
    {
        $sql = "
            SELECT o.order_id,
                o.table_id,
                o.order_created_at,
                p.product_name,
                oi.kitchen_note
            FROM 'order' o
            NATURAL JOIN order_item oi
            NATURAL JOIN product p
            WHERE o.company_id = :company_id
              AND o.order_flag = 'a'
              AND p.product_is_kitchen = 1
            ORDER BY o.order_created_at ASC;   
        ";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['company_id' => $companyId]);
        $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC);

        $orders = [];
        foreach ($rows as $row) {
            $orderId = $row['order_id'];

            if (!isset($orders[$orderId])) {
                $orders[$orderId] = [
                    'order_id'        => $row['order_id'],
                    'table_id'        => $row['table_id'],
                    'order_created_at'=> $row['order_created_at'],
                    'items'           => []
                ];
            }

            $orders[$orderId]['items'][] = [
                'product_name' => $row['product_name'],
                'kitchen_note' => $row['kitchen_note'] ?? ''
            ];
        }

        return array_values($orders);
    }
}