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]);
    }

    public function create(array $data, int $statusId): array
    {
        $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 (!array_key_exists($column, $data)) {
                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);
            }

            $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'];
        }

        return $record;
    }

    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');
    }

    /**
     * @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;
    }
}