backend-php/models/CommodityModel.php

<?php

namespace Models;

class CommodityModel
{
    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 create(string $name, string $flag = 'a'): array
    {
        $stmt = $this->pdo->prepare('INSERT INTO "commodities" (commodities_name, commodities_flag) VALUES (:name, :flag) RETURNING commodities_id');
        $stmt->execute(['name' => $name, 'flag' => $flag]);
        $id = (int)$stmt->fetchColumn();
        return [
            'commodities_id' => $id,
            'commodities_name' => $name,
            'commodities_flag' => $flag,
        ];
    }

    public function update(int $id, ?string $name = null, ?string $flag = null): ?array
    {
        $fields = [];
        $params = ['id' => $id];
        if ($name !== null) { $fields[] = 'commodities_name = :name'; $params['name'] = $name; }
        if ($flag !== null) { $fields[] = 'commodities_flag = :flag'; $params['flag'] = $flag; }
        if (!$fields) { return null; }

        $sql = 'UPDATE "commodities" SET ' . implode(', ', $fields) . ' WHERE commodities_id = :id';
        $stmt = $this->pdo->prepare($sql);
        $ok = $stmt->execute($params);
        if (!$ok) { return null; }

        $get = $this->pdo->prepare('SELECT commodities_id, commodities_name, commodities_flag FROM "commodities" WHERE commodities_id = :id');
        $get->execute(['id' => $id]);
        return $get->fetch(\PDO::FETCH_ASSOC) ?: null;
    }

    public function delete(int $id): bool
    {
        $stmt = $this->pdo->prepare('DELETE FROM "commodities" WHERE commodities_id = :id');
        $stmt->execute(['id' => $id]);
        return $stmt->rowCount() > 0;
    }

    public function getAll(?string $flag = 'a'): array
    {
        $columns = 'commodities_id, commodities_name, commodities_class, commodities_flag';

        if ($flag === null) {
            $stmt = $this->pdo->query('SELECT ' . $columns . ' FROM "commodities" ORDER BY commodities_id');
            return $stmt->fetchAll(\PDO::FETCH_ASSOC);
        }
        $stmt = $this->pdo->prepare('SELECT ' . $columns . ' FROM "commodities" WHERE commodities_flag = :flag ORDER BY commodities_id');
        $stmt->execute(['flag' => $flag]);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    public function getIdByName(string $name): ?int
    {
        $stmt = $this->pdo->prepare('SELECT commodities_id FROM "commodities" WHERE LOWER(TRIM(commodities_name)) = LOWER(TRIM(:name)) LIMIT 1');
        $stmt->execute(['name' => $name]);
        $id = $stmt->fetchColumn();

        return $id !== false ? (int)$id : null;
    }
}