bartender/models/ReportsModel.php

<?php

namespace Models;

class ReportsModel
{
    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);
        $this->pdo->exec('PRAGMA journal_mode = WAL;');
        $this->pdo->exec('PRAGMA busy_timeout = 5000;');
    }

    public function getReports(int $companyId, ?int $page = null, ?int $limit = null): array
    {
        // Total de vendas
        $sqlTotalValue = "
            SELECT SUM(CAST(product_price AS REAL)) AS total_sales
            FROM `order`
            NATURAL JOIN order_item
            NATURAL JOIN product
            NATURAL JOIN status
            WHERE company_id = :company_id
              AND status_status = 'Finalizada'
        ";
        $stmtTotal = $this->pdo->prepare($sqlTotalValue);
        $stmtTotal->execute(['company_id' => $companyId]);
        $totalSales = $stmtTotal->fetchColumn() ?: 0;

        // Top 3 itens mais vendidos
        $sqlTopItems = "
            SELECT product_name, COUNT(*) AS sold_quantity
            FROM `order`
            NATURAL JOIN order_item
            NATURAL JOIN product
            NATURAL JOIN status
            WHERE company_id = :company_id
              AND status_status = 'Finalizada'
            GROUP BY product_id
            ORDER BY sold_quantity DESC
            LIMIT 3
        ";
        $stmtTopItems = $this->pdo->prepare($sqlTopItems);
        $stmtTopItems->execute(['company_id' => $companyId]);
        $topItems = $stmtTopItems->fetchAll(\PDO::FETCH_ASSOC);

        // Lista de pedidos
        $sqlOrders = "
            SELECT  o.order_id,
                    o.order_finished_at,
                    t.table_number,
                    o.order_name,
                    o.order_flag
            FROM `order` o
            NATURAL JOIN status
            JOIN `table` t USING(table_id)
            WHERE o.company_id = :company_id
                AND status_status = 'Finalizada'
            ORDER BY o.order_finished_at DESC
        ";

        $offset = 0;
        if ($page !== null && $limit !== null) {
            $offset = ($page - 1) * $limit;
            $sqlOrders .= " LIMIT :limit OFFSET :offset";
        }

        $stmtOrders = $this->pdo->prepare($sqlOrders);
        $stmtOrders->bindValue(':company_id', $companyId, \PDO::PARAM_INT);

        if ($page !== null && $limit !== null) {
            $stmtOrders->bindValue(':limit', $limit, \PDO::PARAM_INT);
            $stmtOrders->bindValue(':offset', $offset, \PDO::PARAM_INT);
        }

        $stmtOrders->execute();
        $ordersData = $stmtOrders->fetchAll(\PDO::FETCH_ASSOC);

        if (empty($ordersData)) {
            return [
                'total_sales' => (float) $totalSales,
                'top_items'   => $topItems,
                'orders'      => []
            ];
        }

        // Pega os IDs dos pedidos encontrados
        $orderIds = array_column($ordersData, 'order_id');
        $inQuery = implode(',', array_fill(0, count($orderIds), '?'));

        // Busca itens dos pedidos
        $sqlItems = "
            SELECT order_id,
                   product_name,
                   product_price
            FROM order_item
            NATURAL JOIN product
            WHERE order_id IN ($inQuery)
        ";
        $stmtItems = $this->pdo->prepare($sqlItems);
        foreach ($orderIds as $k => $id) {
            $stmtItems->bindValue($k + 1, $id, \PDO::PARAM_INT);
        }
        $stmtItems->execute();
        $itemsData = $stmtItems->fetchAll(\PDO::FETCH_ASSOC);

        // Monta estrutura final de pedidos + itens
        $orders = [];
        foreach ($ordersData as $order) {
            $orders[$order['order_id']] = $order + ['items' => []];
        }
        foreach ($itemsData as $item) {
            $orders[$item['order_id']]['items'][] = [
                'product_name'  => $item['product_name'],
                'product_price' => $item['product_price']
            ];
        }

        return [
            'total_sales' => (float) $totalSales,
            'top_items'   => $topItems,
            'orders'      => array_values($orders),
        ];
    }
}