bartender/models/OrderModel.php

<?php

namespace Models;

class OrderModel
{
    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 createOrder(
        int $tableId,
        int $userId,
        int $companyId,
        string $orderName,
        string $orderPhone,
        int $statusId
    ): int|false {
        if (!$this->tableExists($tableId, $companyId) || !$this->userExists($userId, $companyId) || !$this->companyExists($companyId) || !$this->statusExists($statusId)) {
            error_log("Tentativa de criar pedido com IDs inválidos: table_id={$tableId}, user_id={$userId}, company_id={$companyId}, status_id={$statusId}");
            return false;
        }

        $stmt = $this->pdo->prepare("
            INSERT INTO `order` (table_id, user_id, company_id, order_name, order_phone, status_id, order_created_at, order_finished_at, order_flag)
            VALUES (:table_id, :user_id, :company_id, :order_name, :order_phone, :status_id, :order_created_at, :order_finished_at, 'a')
        ");

        $currentTime = date('Y-m-d H:i:s');

        try {
            $executed = $stmt->execute([
                'table_id' => $tableId,
                'user_id' => $userId,
                'company_id' => $companyId,
                'order_name' => $orderName,
                'order_phone' => $orderPhone,
                'status_id' => $statusId,
                'order_created_at' => $currentTime,
                'order_finished_at' => ' '
            ]);
            return $executed ? (int)$this->pdo->lastInsertId() : false;
        } catch (\PDOException $e) {
            error_log("PDO Exception during order creation: " . $e->getMessage());
            return false;
        }
    }

    public function updateOrderStatus(int $orderId, int $companyId, int $statusId, ?string $orderFlag = null): bool|string
    {
        $stmtStatus = $this->pdo->prepare("
            SELECT status_status 
            FROM status 
            WHERE status_id = :status_id
        ");
        $stmtStatus->execute(['status_id' => $statusId]);
        $statusName = $stmtStatus->fetchColumn();

        if (strtolower($statusName) === 'finalizada') {
            $stmtCheck = $this->pdo->prepare("
                SELECT COUNT(*)
                FROM order_item
                NATURAL JOIN product
                NATURAL JOIN 'order'
                WHERE order_id = :order_id
                  AND company_id = :company_id
                  AND product_is_kitchen = 1
                  AND order_flag = 'a';
            ");
            $stmtCheck->execute([
                'order_id'   => $orderId,
                'company_id' => $companyId
            ]);

            $pendingKitchen = (int)$stmtCheck->fetchColumn();

            if ($pendingKitchen > 0) {
                return "Comanda possui itens de cozinha não finalizados.";
            }
        }

        $sql = "
            UPDATE `order`
            SET status_id = :status_id
        ";

        if (strtolower($statusName) === 'finalizada') {
            $sql .= ", order_finished_at = :order_finished_at";
        }

        if ($orderFlag !== null) {
            $sql .= ", order_flag = :order_flag";
        }

        $sql .= "
            WHERE order_id = :order_id 
              AND company_id = :company_id
        ";

        $stmt = $this->pdo->prepare($sql);

        $params = [
            'status_id'  => $statusId,
            'order_id'   => $orderId,
            'company_id' => $companyId
        ];

        if (strtolower($statusName) === 'finalizada') {
            $params['order_finished_at'] = date('Y-m-d H:i:s');
        }

        if ($orderFlag !== null) {
            $params['order_flag'] = $orderFlag;
        }

        $executed = $stmt->execute($params);
        return $executed && $stmt->rowCount() > 0;
    }

        public function deleteOrder(int $orderId, int $companyId, bool $hardDelete = false): bool
    {
        if ($hardDelete) {
            $stmtItems = $this->pdo->prepare("DELETE FROM order_item WHERE order_id = :order_id");
            $stmtItems->execute(['order_id' => $orderId]);

            $stmtOrder = $this->pdo->prepare("DELETE FROM `order` WHERE order_id = :order_id AND company_id = :company_id");
            $stmtOrder->execute(['order_id' => $orderId, 'company_id' => $companyId]);

            return $stmtOrder->rowCount() > 0;
        } else {
            $currentTime = date('Y-m-d H:i:s');

            $stmt = $this->pdo->prepare("
                UPDATE `order`
                SET order_flag = 'd', order_finished_at = :order_finished_at
                WHERE order_id = :order_id AND company_id = :company_id AND order_flag = 'a'
            ");

            $executed = $stmt->execute([
                'order_finished_at' => $currentTime,
                'order_id' => $orderId,
                'company_id' => $companyId
            ]);

            return $executed && $stmt->rowCount() > 0;
        }
    }


    public function getOrders(int $companyId, ?int $statusId = null): array
{
    $sql = "SELECT * 
            FROM `order` 
            WHERE company_id = :company_id 
              AND order_flag IN ('a', 'p')";
    $params = ['company_id' => $companyId];

    if ($statusId !== null) {
        $sql .= " AND status_id = :status_id";
        $params['status_id'] = $statusId;
    }

    $stmt = $this->pdo->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll(\PDO::FETCH_ASSOC);
}

public function getOrdersByTable(int $tableId, int $companyId, ?int $statusId = null): array
{
    if (!$this->tableExists($tableId, $companyId)) {
        error_log("Tentativa de obter pedidos de mesa inválida: table_id={$tableId}, company_id={$companyId}");
        return [];
    }

    $sql = "SELECT * 
            FROM `order` 
            WHERE table_id = :table_id 
              AND company_id = :company_id 
              AND order_flag IN ('a', 'p')";
    $params = [
        'table_id' => $tableId,
        'company_id' => $companyId
    ];

    if ($statusId !== null) {
        $sql .= " AND status_id = :status_id";
        $params['status_id'] = $statusId;
    }

    $stmt = $this->pdo->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll(\PDO::FETCH_ASSOC);
}


    public function getOrderById(int $orderId, int $companyId, bool $onlyActive = true): ?array
    {
        $sql = "SELECT * FROM `order` WHERE order_id = :order_id AND company_id = :company_id";
        if ($onlyActive) {
            $sql .= " AND order_flag = 'a'";
        }
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['order_id' => $orderId, 'company_id' => $companyId]);
        $result = $stmt->fetch(\PDO::FETCH_ASSOC);
        return $result ?: null;
    }

    public function getStatusNameById(int $statusId): ?string
    {
        $stmt = $this->pdo->prepare("SELECT status_status FROM status WHERE status_id = :status_id");
        $stmt->execute(['status_id' => $statusId]);
        $result = $stmt->fetchColumn();
        return $result ?: null;
    }

    public function getStatusIdByName(string $statusName): ?int
    {
        $stmt = $this->pdo->prepare("SELECT status_id FROM status WHERE status_status = :status_name");
        $stmt->execute(['status_name' => $statusName]);
        $result = $stmt->fetch(\PDO::FETCH_ASSOC);
        return $result ? (int)$result['status_id'] : null;
    }

    private function companyExists(int $companyId): bool
    {
        $stmt = $this->pdo->prepare("SELECT 1 FROM company WHERE company_id = :id AND company_flag = 'a'");
        $stmt->execute(['id' => $companyId]);
        return (bool) $stmt->fetch();
    }

    private function tableExists(int $tableId, int $companyId): bool
    {
        $stmt = $this->pdo->prepare("SELECT 1 FROM `table` WHERE table_id = :table_id AND company_id = :company_id AND table_flag = 'a'");
        $stmt->execute(['table_id' => $tableId, 'company_id' => $companyId]);
        return (bool) $stmt->fetch();
    }

    private function userExists(int $userId, int $companyId): bool
    {
        $stmt = $this->pdo->prepare("SELECT 1 FROM user WHERE user_id = :user_id AND company_id = :company_id AND user_flag = 'a'");
        $stmt->execute(['user_id' => $userId, 'company_id' => $companyId]);
        return (bool) $stmt->fetch();
    }

    private function statusExists(int $statusId): bool
    {
        $stmt = $this->pdo->prepare("SELECT 1 FROM status WHERE status_id = :id");
        $stmt->execute(['id' => $statusId]);
        return (bool) $stmt->fetch();
    }
}