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 = 1, int $limit = 10): array
    {
        $offset = ($page - 1) * $limit;
    
        $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;

        $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);

        $sqlOrders = "
            SELECT order_id,
                   order_finished_at,
                   table_id,
                   order_name,
                   order_flag
            FROM `order`
            NATURAL JOIN status
            WHERE company_id = :company_id
              AND status_status = 'Finalizada'
            ORDER BY order_finished_at DESC
            LIMIT :limit OFFSET :offset;
        ";
        $stmtOrders = $this->pdo->prepare($sqlOrders);
        $stmtOrders->bindValue(':company_id', $companyId, \PDO::PARAM_INT);
        $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' => []
            ];
        }

        $orderIds = array_column($ordersData, 'order_id');
        $inQuery = implode(',', array_fill(0, count($orderIds), '?'));

        $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);

        $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),
        ];
    }
}