backend-php/models/TokenModel.php

<?php

namespace Models;

class TokenModel
{
    private \PDO $pdo;

    public function __construct()
    {
        if (isset($GLOBALS['pdo']) && $GLOBALS['pdo'] instanceof \PDO) {
            $this->pdo = $GLOBALS['pdo'];
            return;
        }
        throw new \RuntimeException('Global PDO connection not initialized');
    }

    public function getByUf(string $uf): array
    {
        $stmt = $this->pdo->prepare('SELECT token_id, token_external_id, token_commodities_amount, token_commodities_value, token_uf, token_city, token_content, token_flag, wallet_id, chain_id, commodities_id, cpr_id, user_id FROM "token" WHERE token_uf = :uf ORDER BY token_id');
        $stmt->execute(['uf' => $uf]);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    public function getByUfAndCommodityName(string $uf, string $commoditiesName): array
    {
        $sql = 'SELECT t.token_id, t.token_external_id, t.token_commodities_amount, t.token_commodities_value, t.token_uf, t.token_city, t.token_content, t.token_flag, t.wallet_id, t.chain_id, t.commodities_id, t.cpr_id, t.user_id
                FROM "token" t
                JOIN "commodities" c ON c.commodities_id = t.commodities_id
                WHERE t.token_uf = :uf AND LOWER(TRIM(c.commodities_name)) = LOWER(TRIM(:name))
                ORDER BY t.token_id';
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['uf' => $uf, 'name' => $commoditiesName]);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    public function getByCompanyId(int $companyId): array
    {
        $sql = 'SELECT t.token_id,
                       t.token_external_id,
                       t.token_commodities_amount,
                       t.token_commodities_value,
                       t.token_uf,
                       t.token_city,
                       t.token_content,
                       t.token_flag,
                       t.wallet_id,
                       t.chain_id,
                       t.commodities_id,
                       t.cpr_id,
                       t.user_id,
                       c.cpr_product_name,
                       c."cpr_deliveryPlace_state_acronym",
                       c."cpr_deliveryPlace_city_name",
                       c.cpr_issue_value,
                       c.cpr_packaging_way_name,
                       c.cpr_product_quantity,
                       c.cpr_measure_unit_name,
                       c.cpr_production_place_name,
                       c.cpr_maturity_date
                FROM "token" t
                INNER JOIN "wallet" w ON w.wallet_id = t.wallet_id
                LEFT JOIN "cpr" c ON c.cpr_id = t.cpr_id
                LEFT JOIN "orderbook" o ON o.token_id = t.token_id AND o.status_id = 0
                WHERE w.company_id = :company_id
                  AND o.orderbook_id IS NULL
                ORDER BY t.token_id';
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['company_id' => $companyId]);

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

    public function findByCprId(int $cprId, bool $forUpdate = false): ?array
    {
        $sql = 'SELECT token_id,
                    token_external_id,
                    token_commodities_amount,
                    token_commodities_value,
                    token_uf,
                    token_city,
                    token_content,
                    token_flag,
                    wallet_id,
                    chain_id,
                    commodities_id,
                    cpr_id,
                    user_id
             FROM "token"
             WHERE cpr_id = :cpr_id
            ORDER BY token_id DESC
            LIMIT 1';

        if ($forUpdate) {
            $sql .= ' FOR UPDATE';
        }

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['cpr_id' => $cprId]);
        $record = $stmt->fetch(\PDO::FETCH_ASSOC);

        return $record ?: null;
    }

    public function findById(int $tokenId): ?array
    {
        $stmt = $this->pdo->prepare(
            'SELECT token_id,
                    token_external_id,
                    token_commodities_amount,
                    token_commodities_value,
                    token_uf,
                    token_city,
                    token_content,
                    token_flag,
                    wallet_id,
                    chain_id,
                    commodities_id,
                    cpr_id,
                    user_id
             FROM "token"
             WHERE token_id = :token_id
             LIMIT 1'
        );

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

        return $record ?: null;
    }

    public function updateCommoditiesValue(int $tokenId, float $value): void
    {
        $stmt = $this->pdo->prepare(
            'UPDATE "token"
             SET token_commodities_value = :value
             WHERE token_id = :token_id'
        );
        $stmt->execute([
            'value' => $value,
            'token_id' => $tokenId,
        ]);
    }

    public function updateWalletId(int $tokenId, int $walletId): void
    {
        if ($tokenId <= 0) {
            throw new \InvalidArgumentException('Invalid token id provided');
        }

        if ($walletId <= 0) {
            throw new \InvalidArgumentException('Invalid wallet id provided');
        }

        $stmt = $this->pdo->prepare(
            'UPDATE "token"
             SET wallet_id = :wallet_id
             WHERE token_id = :token_id'
        );

        $stmt->execute([
            'wallet_id' => $walletId,
            'token_id' => $tokenId,
        ]);

        if ($stmt->rowCount() === 0) {
            throw new \RuntimeException('Token record not found for wallet update');
        }
    }
}