backend-php/models/CprQueryModel.php

<?php

namespace Models;

class CprQueryModel
{
    private \PDO $pdo;

    public function __construct()
    {
        if (!isset($GLOBALS['pdo']) || !$GLOBALS['pdo'] instanceof \PDO) {
            throw new \RuntimeException('Global PDO not initialized');
        }

        $this->pdo = $GLOBALS['pdo'];
    }

    /**
     * Lista resumida
     */
    public function listByCompany(int $companyId): array
    {
        $sql = '
            SELECT
                cpr_id,
                cpr_product_class_name,
                cpr_issue_date,
                cpr_issuer_name,
                cpr_issue_financial_value
            FROM cpr
            WHERE company_id = :company_id
            ORDER BY cpr_issue_date DESC
        ';

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['company_id' => $companyId]);

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

    public function listAll(): array
    {
        $sql = '
            SELECT
                cpr_id,
                cpr_product_class_name,
                cpr_issue_date,
                cpr_issuer_name,
                cpr_issue_financial_value
            FROM cpr
            ORDER BY cpr_issue_date DESC
        ';

        return $this->pdo->query($sql)->fetchAll(\PDO::FETCH_ASSOC);
    }

    /**
     * Detalhe completo
     */
    public function getByIdAndCompany(int $cprId, int $companyId): ?array
    {
        $sql = '
            SELECT *
            FROM cpr
            WHERE cpr_id = :cpr_id
              AND company_id = :company_id
            LIMIT 1
        ';

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([
            'cpr_id' => $cprId,
            'company_id' => $companyId
        ]);

        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        if (!$row) {
            return null;
        }

        return $this->filterEmptyFields($row);
    }

    public function getById(int $cprId): ?array
    {
        $stmt = $this->pdo->prepare('SELECT * FROM cpr WHERE cpr_id = :cpr_id LIMIT 1');
        $stmt->execute(['cpr_id' => $cprId]);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);

        if (!$row) {
            return null;
        }

        return $this->filterEmptyFields($row);
    }

    /**
     * Remove NULL e "na"
     */
    private function filterEmptyFields(array $data): array
    {
        return array_filter(
            $data,
            static function ($value) {
                if ($value === null) {
                    return false;
                }

                if (is_string($value) && strtolower(trim($value)) === 'na') {
                    return false;
                }

                return true;
            }
        );
    }
}