backend-php/models/OrderbookModel.php

<?php

namespace Models;

class OrderbookModel
{
    private \PDO $pdo;
    public const STATUS_OPEN = 0;
    public const STATUS_COMPLETED = 1;

    public function __construct()
    {
        if (isset($GLOBALS['pdo']) && $GLOBALS['pdo'] instanceof \PDO) {
            $this->pdo = $GLOBALS['pdo'];
            return;
        }

        throw new \RuntimeException('Global PDO connection not initialized');
    }

    /**
     * @param array{
     *   orderbook_flag:string,
     *   orderbook_ts:int,
     *   orderbook_is_token:bool,
     *   orderbook_amount:string,
     *   orderbook_state:string,
     *   orderbook_commodity_type:string,
     *   token_external_id:string,
     *   status_id:int,
     *   user_id:int,
     *   wallet_id:int,
     *   token_id:int,
     *   currency_id:?int,
     *   chain_id:int
     * } $data
     */
    public function create(array $data): array
    {
        $stmt = $this->pdo->prepare(
            'INSERT INTO "orderbook" (
                orderbook_flag,
                orderbook_ts,
                orderbook_is_token,
                orderbook_amount,
                orderbook_state,
                orderbook_commodity_type,
                token_external_id,
                status_id,
                user_id,
                wallet_id,
                token_id,
                currency_id,
                chain_id
            ) VALUES (
                :orderbook_flag,
                :orderbook_ts,
                :orderbook_is_token,
                :orderbook_amount,
                :orderbook_state,
                :orderbook_commodity_type,
                :token_external_id,
                :status_id,
                :user_id,
                :wallet_id,
                :token_id,
                :currency_id,
                :chain_id
            ) RETURNING orderbook_id'
        );

        $stmt->execute([
            'orderbook_flag' => $data['orderbook_flag'],
            'orderbook_ts' => $data['orderbook_ts'],
            'orderbook_is_token' => $data['orderbook_is_token'],
            'orderbook_amount' => $data['orderbook_amount'],
            'orderbook_state' => $data['orderbook_state'],
            'orderbook_commodity_type' => $data['orderbook_commodity_type'],
            'token_external_id' => $data['token_external_id'],
            'status_id' => $data['status_id'],
            'user_id' => $data['user_id'],
            'wallet_id' => $data['wallet_id'],
            'token_id' => $data['token_id'],
            'currency_id' => $data['currency_id'],
            'chain_id' => $data['chain_id'],
        ]);

        $orderbookId = (int)$stmt->fetchColumn();

        return [
            'orderbook_id' => $orderbookId,
            'orderbook_ts' => $data['orderbook_ts'],
        ];
    }

    public function findByStateAndCommodity(string $state, string $commodityType, int $statusId = self::STATUS_OPEN): array
    {
        $stmt = $this->pdo->prepare(
            'SELECT 
                o.orderbook_id,
                o.orderbook_flag,
                o.orderbook_ts,
                o.orderbook_is_token,
                o.orderbook_amount,
                o.orderbook_state,
                o.orderbook_commodity_type,
                o.token_external_id,
                o.status_id,
                o.user_id,
                o.wallet_id,
                o.token_id,
                o.currency_id,
                o.chain_id,
                t.token_commodities_value,
                t.token_commodities_amount,
                c."cpr_deliveryPlace_city_name" AS cityName,
                c.cpr_product_quantity AS cprProductQuantity,
                c.cpr_measure_unit_name AS measureUnitName
            FROM "orderbook" o
            LEFT JOIN "token" t ON t.token_id = o.token_id
            LEFT JOIN "cpr" c ON c.cpr_id = t.cpr_id
            WHERE UPPER(o.orderbook_state) = UPPER(:state)
              AND LOWER(o.orderbook_commodity_type) = LOWER(:commodity_type)
              AND o.status_id = :status_id
            ORDER BY o.orderbook_ts DESC'
        );

        $stmt->execute([
            'state' => $state,
            'commodity_type' => $commodityType,
            'status_id' => $statusId,
        ]);

        return $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];
    }

    public function findByIdWithToken(int $orderbookId): ?array
    {
        $stmt = $this->pdo->prepare(
            'SELECT 
                o.*, 
                t.token_commodities_value, 
                t.token_commodities_amount,
                t.token_external_id AS token_table_external_id,
                w.company_id,
                w.wallet_address
             FROM "orderbook" o
             LEFT JOIN "token" t ON t.token_id = o.token_id
             LEFT JOIN "wallet" w ON w.wallet_id = o.wallet_id
             WHERE o.orderbook_id = :orderbook_id
             LIMIT 1'
        );

        $stmt->execute(['orderbook_id' => $orderbookId]);
        $record = $stmt->fetch(\PDO::FETCH_ASSOC);

        return $record ?: null;
    }

    public function findByTokenExternalId(string $tokenExternalId): ?array
    {
        $stmt = $this->pdo->prepare(
            'SELECT 
                o.*, 
                t.token_commodities_value,
                t.token_commodities_amount,
                w.company_id,
                w.wallet_address
             FROM "orderbook" o
             LEFT JOIN "token" t ON t.token_id = o.token_id
             LEFT JOIN "wallet" w ON w.wallet_id = o.wallet_id
             WHERE o.token_external_id = :token_external_id
             ORDER BY o.orderbook_id DESC
             LIMIT 1'
        );

        $stmt->execute(['token_external_id' => $tokenExternalId]);
        $record = $stmt->fetch(\PDO::FETCH_ASSOC);

        return $record ?: null;
    }

    public function updateStatus(int $orderbookId, int $statusId): void
    {
        $stmt = $this->pdo->prepare(
            'UPDATE "orderbook"
             SET status_id = :status_id
             WHERE orderbook_id = :orderbook_id'
        );

        $stmt->execute([
            'status_id' => $statusId,
            'orderbook_id' => $orderbookId,
        ]);
    }
}