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): bool
    {

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

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

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

    public function deleteOrder(int $orderId, int $companyId): bool
    {
        $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 = 'a'";
        $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 = 'a'";
        $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): ?array
    {
        $stmt = $this->pdo->prepare("SELECT * FROM `order` WHERE order_id = :order_id AND company_id = :company_id AND order_flag = 'a'");
        $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();
    }
}