backend-php/models/CprModel.php

<?php

namespace Models;

class CprModel
{
    private \PDO $pdo;
    private static ?array $columnsMeta = null;

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

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

    /**
     * @return array<string, array{nullable: bool, data_type: string}>
     */
    private function getColumnsMeta(): array
    {
        if (self::$columnsMeta !== null) {
            return self::$columnsMeta;
        }

        $stmt = $this->pdo->prepare(
            'SELECT column_name, is_nullable, data_type
             FROM information_schema.columns
             WHERE table_schema = current_schema()
               AND table_name = :table
             ORDER BY ordinal_position'
        );
        $stmt->execute(['table' => 'cpr']);
        $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC);

        if (!$rows) {
            throw new \RuntimeException('Unable to load CPR table metadata');
        }

        $meta = [];
        foreach ($rows as $row) {
            $meta[$row['column_name']] = [
                'nullable' => strtoupper((string)$row['is_nullable']) === 'YES',
                'data_type' => (string)$row['data_type'],
            ];
        }

        self::$columnsMeta = $meta;

        return self::$columnsMeta;
    }

    /**
     * @return array<string, array{nullable: bool, data_type: string}>
     */
    public function getUserColumns(): array
    {
        $meta = $this->getColumnsMeta();
        unset($meta['cpr_id']);

        return array_diff_key($meta, ['status_id' => true, 'payment_id' => true, 'user_id' => true, 'company_id' => true]);
    }

    public function create(array $data, int $statusId, int $paymentId, int $userId, int $companyId): array
    {
        $data = $this->flattenB3Arrays($data);

        $meta = $this->getColumnsMeta();

        $columns = [];
        $placeholders = [];
        $params = [];

        foreach ($meta as $column => $info) {
            if ($column === 'cpr_id') {
                continue;
            }

            if ($column === 'status_id') {
                $columns[] = '"status_id"';
                $placeholders[] = ':status_id';
                $params['status_id'] = $statusId;
                continue;
            }

            if ($column === 'payment_id') {
                $columns[] = '"payment_id"';
                $placeholders[] = ':payment_id';
                $params['payment_id'] = $paymentId;
                continue;
            }

            if ($column === 'user_id') {
                $columns[] = '"user_id"';
                $placeholders[] = ':user_id';
                $params['user_id'] = $userId;
                continue;
            }

            if ($column === 'company_id') {
                $columns[] = '"company_id"';
                $placeholders[] = ':company_id';
                $params['company_id'] = $companyId;
                continue;
            }

            if (!array_key_exists($column, $data)) {
                if ($column === 'cpr_ticker') {
                    $columns[] = '"' . $column . '"';
                    $placeholders[] = ':' . $column;
                    $params[$column] = '';
                    continue;
                }

                if ($info['nullable']) {
                    $columns[] = '"' . $column . '"';
                    $placeholders[] = ':' . $column;
                    $params[$column] = null;
                    continue;
                }

                throw new \InvalidArgumentException("Missing field: {$column}");
            }

            $value = $data[$column];
            if ($column === 'cpr_children_codes') {
                $value = $this->normalizeChildrenCodes($value);
            }

            if (in_array($column, $this->getSemicolonListColumns(), true)) {
                $value = $this->normalizeSemicolonList($value, $column);
            }

            $columns[] = '"' . $column . '"';
            $placeholders[] = ':' . $column;
            $params[$column] = $value;
        }

        $sql = 'INSERT INTO "cpr" (' . implode(', ', $columns) . ')
                VALUES (' . implode(', ', $placeholders) . ')
                RETURNING cpr_id';
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        $cprId = (int)$stmt->fetchColumn();

        $record = $this->fetchById($cprId);
        if (!$record) {
            throw new \RuntimeException('Failed to load created CPR record');
        }

        if (isset($record['cpr_children_codes'])) {
            $record['cpr_children_codes'] = $this->decodeChildrenCodes((string)$record['cpr_children_codes']);
        }

        $record['cpr_id'] = (int)$record['cpr_id'];
        if (isset($record['status_id'])) {
            $record['status_id'] = (int)$record['status_id'];
        }
        if (isset($record['payment_id'])) {
            $record['payment_id'] = (int)$record['payment_id'];
        }
        if (isset($record['user_id'])) {
            $record['user_id'] = (int)$record['user_id'];
        }
        if (isset($record['company_id'])) {
            $record['company_id'] = (int)$record['company_id'];
        }

        return $record;
    }

    public function updateTokenId(int $cprId, int $tokenId): void
    {
        $stmt = $this->pdo->prepare('UPDATE "cpr" SET token_id = :token_id WHERE cpr_id = :cpr_id');
        $stmt->execute([
            'token_id' => $tokenId,
            'cpr_id' => $cprId,
        ]);
    }

    public function updateCompanyId(int $cprId, int $companyId): void
    {
        if ($cprId <= 0) {
            throw new \InvalidArgumentException('Invalid CPR id provided');
        }

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

        $stmt = $this->pdo->prepare('UPDATE "cpr" SET company_id = :company_id WHERE cpr_id = :cpr_id');
        $stmt->execute([
            'company_id' => $companyId,
            'cpr_id' => $cprId,
        ]);

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

    public function updateTicker(int $cprId, string $ticker): void
    {
        if ($cprId <= 0) {
            throw new \InvalidArgumentException('Invalid CPR id provided');
        }

        $normalizedTicker = trim($ticker);

        $stmt = $this->pdo->prepare('UPDATE "cpr" SET cpr_ticker = :cpr_ticker WHERE cpr_id = :cpr_id');
        $stmt->execute([
            'cpr_ticker' => $normalizedTicker,
            'cpr_id' => $cprId,
        ]);
    }

    private function normalizeChildrenCodes($value): string
    {
        if (is_array($value)) {
            $value = array_map('strval', array_values($value));
            if (!$value) {
                throw new \InvalidArgumentException('cpr_children_codes must not be empty');
            }

            $encoded = json_encode($value, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
            if ($encoded === false) {
                throw new \InvalidArgumentException('Invalid cpr_children_codes payload');
            }

            return $encoded;
        }

        if (is_string($value) && trim($value) !== '') {
            return $value;
        }

        throw new \InvalidArgumentException('cpr_children_codes must be a non-empty string or array of strings');
    }

    private function normalizeSemicolonList($value, string $field): string
    {
        if (is_array($value)) {
            $items = array_map(static fn($v) => trim((string)$v), array_values($value));
            $items = array_values(array_filter($items, static fn($v) => $v !== ''));
            if (!$items) {
                throw new \InvalidArgumentException("{$field} must not be empty");
            }
            return implode('; ', $items);
        }

        if (is_string($value)) {
            $trimmed = trim($value);
            if ($trimmed === '') {
                throw new \InvalidArgumentException("{$field} must not be empty");
            }

            $parts = preg_split('/\s*;\s*/', $trimmed) ?: [];
            $parts = array_map(static fn($v) => trim((string)$v), $parts);
            $parts = array_values(array_filter($parts, static fn($v) => $v !== ''));
            return implode('; ', $parts);
        }

        throw new \InvalidArgumentException("{$field} must be a non-empty string or array of strings");
    }

    private function getSemicolonListColumns(): array
    {
        return [
            'cpr_collateral_type_code',
            'cpr_collateral_type_name',
            'cpr_constitution_process_indicator',
            'cpr_otc_bondsman_account_code',
            'cpr_issuer_name',
            'cpr_issuers_document_number',
            'cpr_issuers_person_type_acronym',
            'cpr_issuer_legal_nature_code',
            'cpr_issuers_state_acronym',
            'cpr_issuers_city_name',
            'cpr_production_place_name',
            'cpr_property_registration_number',
            'cpr_notary_name',
            'cpr_total_production_area_in_hectares_number',
            'cpr_total_area_in_hectares_number',
            'cpr_car_code',
            'cpr_latitude_code',
            'cpr_longitude_code',
            'cpr_zip_code',
        ];
    }

    private function flattenB3Arrays(array $data): array
    {
        if (!array_key_exists('collaterals', $data) && !array_key_exists('issuers', $data) && !array_key_exists('productionPlaces', $data)) {
            return $data;
        }

        if (array_key_exists('collaterals', $data) && !array_key_exists('cpr_collateral_type_code', $data)) {
            $collaterals = $data['collaterals'];
            if (is_array($collaterals) && $collaterals && $this->isAssoc($collaterals)) {
                $collaterals = [$collaterals];
            }

            if (is_array($collaterals)) {
                $data['cpr_collateral_type_code'] = array_map(static fn($c) => $c['collateralTypeCode'] ?? null, $collaterals);
                $data['cpr_collateral_type_name'] = array_map(static fn($c) => $c['collateralTypeName'] ?? null, $collaterals);
                $data['cpr_constitution_process_indicator'] = array_map(static fn($c) => $c['constitutionProcessIndicator'] ?? null, $collaterals);
                $data['cpr_otc_bondsman_account_code'] = array_map(static fn($c) => $c['otcBondsmanAccountCode'] ?? null, $collaterals);
            }
        }

        if (array_key_exists('issuers', $data) && !array_key_exists('cpr_issuer_name', $data)) {
            $issuers = $data['issuers'];
            if (is_array($issuers) && $issuers && $this->isAssoc($issuers)) {
                $issuers = [$issuers];
            }

            if (is_array($issuers)) {
                $data['cpr_issuer_name'] = array_map(static fn($i) => $i['cprIssuerName'] ?? null, $issuers);
                $data['cpr_issuers_document_number'] = array_map(static fn($i) => $i['documentNumber'] ?? null, $issuers);
                $data['cpr_issuers_person_type_acronym'] = array_map(static fn($i) => $i['personTypeAcronym'] ?? null, $issuers);
                $data['cpr_issuer_legal_nature_code'] = array_map(static fn($i) => $i['issuerLegalNatureCode'] ?? null, $issuers);
                $data['cpr_issuers_state_acronym'] = array_map(static fn($i) => $i['stateAcronym'] ?? null, $issuers);
                $data['cpr_issuers_city_name'] = array_map(static fn($i) => $i['cityName'] ?? null, $issuers);
            }
        }

        if (array_key_exists('productionPlaces', $data) && !array_key_exists('cpr_production_place_name', $data)) {
            $productionPlaces = $data['productionPlaces'];
            if (is_array($productionPlaces) && $productionPlaces && $this->isAssoc($productionPlaces)) {
                $productionPlaces = [$productionPlaces];
            }

            if (is_array($productionPlaces)) {
                $data['cpr_production_place_name'] = array_map(static fn($p) => $p['productionPlaceName'] ?? null, $productionPlaces);
                $data['cpr_property_registration_number'] = array_map(static fn($p) => $p['propertyRegistrationNumber'] ?? null, $productionPlaces);
                $data['cpr_notary_name'] = array_map(static fn($p) => $p['notaryName'] ?? null, $productionPlaces);
                $data['cpr_total_production_area_in_hectares_number'] = array_map(static fn($p) => $p['totalProductionAreaInHectaresNumber'] ?? null, $productionPlaces);
                $data['cpr_total_area_in_hectares_number'] = array_map(static fn($p) => $p['totalAreaInHectaresNumber'] ?? null, $productionPlaces);
                $data['cpr_car_code'] = array_map(static fn($p) => $p['carCode'] ?? null, $productionPlaces);
                $data['cpr_latitude_code'] = array_map(static fn($p) => $p['latitudeCode'] ?? null, $productionPlaces);
                $data['cpr_longitude_code'] = array_map(static fn($p) => $p['longitudeCode'] ?? null, $productionPlaces);
                $data['cpr_zip_code'] = array_map(static fn($p) => $p['zipCode'] ?? null, $productionPlaces);
            }
        }

        return $data;
    }

    private function isAssoc(array $arr): bool
    {
        return array_keys($arr) !== range(0, count($arr) - 1);
    }

    /**
     * @return array<int, string>|string
     */
    private function decodeChildrenCodes(string $stored)
    {
        $decoded = json_decode($stored, true);
        if (json_last_error() === JSON_ERROR_NONE && is_array($decoded)) {
            return $decoded;
        }

        return $stored;
    }

    private function fetchById(int $id): ?array
    {
        $stmt = $this->pdo->prepare('SELECT * FROM "cpr" WHERE cpr_id = :id');
        $stmt->execute(['id' => $id]);
        $record = $stmt->fetch(\PDO::FETCH_ASSOC);

        return $record ?: null;
    }

    public function findByPaymentId(int $paymentId): ?array
    {
        $stmt = $this->pdo->prepare('SELECT * FROM "cpr" WHERE payment_id = :payment_id ORDER BY cpr_id DESC LIMIT 1');
        $stmt->execute(['payment_id' => $paymentId]);
        $record = $stmt->fetch(\PDO::FETCH_ASSOC);

        if (!$record) {
            return null;
        }

        if (isset($record['cpr_children_codes'])) {
            $record['cpr_children_codes'] = $this->decodeChildrenCodes((string)$record['cpr_children_codes']);
        }

        return $record;
    }
}