|false $row */ $row = $this->connection()->fetchAssociative( 'SELECT id, name, description, include_manual_blocks, created_at FROM policies WHERE id = :id', ['id' => $id] ); if ($row === false) { return null; } return $this->hydrate($row, $this->loadThresholds((int) $row['id'])); } public function findByName(string $name): ?Policy { /** @var array|false $row */ $row = $this->connection()->fetchAssociative( 'SELECT id, name, description, include_manual_blocks, created_at FROM policies WHERE name = :name', ['name' => $name] ); if ($row === false) { return null; } return $this->hydrate($row, $this->loadThresholds((int) $row['id'])); } /** * @return list */ public function listAll(): array { /** @var list> $rows */ $rows = $this->connection()->fetchAllAssociative( 'SELECT id, name, description, include_manual_blocks, created_at FROM policies ORDER BY id ASC' ); if ($rows === []) { return []; } $thresholdsByPolicy = $this->loadAllThresholds(); return array_map( fn (array $row): Policy => $this->hydrate($row, $thresholdsByPolicy[(int) $row['id']] ?? []), $rows ); } /** * Insert a policy + its thresholds atomically. Returns the new id. * * @param array $thresholds category_id => threshold */ public function create(string $name, ?string $description, bool $includeManualBlocks, array $thresholds): int { return (int) $this->connection()->transactional(function (Connection $conn) use ($name, $description, $includeManualBlocks, $thresholds): int { $conn->insert('policies', [ 'name' => $name, 'description' => $description, 'include_manual_blocks' => $includeManualBlocks ? 1 : 0, ]); $policyId = (int) $conn->lastInsertId(); foreach ($thresholds as $categoryId => $threshold) { $conn->insert('policy_category_thresholds', [ 'policy_id' => $policyId, 'category_id' => $categoryId, 'threshold' => number_format($threshold, 4, '.', ''), ]); } return $policyId; }); } /** * Replace the policy's name/description/include_manual_blocks fields. * Only the keys present in `$fields` are updated. * * @param array $fields */ public function update(int $id, array $fields): void { if ($fields === []) { return; } $this->connection()->update('policies', $fields, ['id' => $id]); } /** * Atomic threshold replacement: deletes the old set and inserts the new * one inside a single transaction so concurrent updates can't observe a * half-written state. * * @param array $thresholds category_id => threshold */ public function replaceThresholds(int $policyId, array $thresholds): void { $this->connection()->transactional(function (Connection $conn) use ($policyId, $thresholds): void { $conn->executeStatement( 'DELETE FROM policy_category_thresholds WHERE policy_id = :pid', ['pid' => $policyId] ); foreach ($thresholds as $categoryId => $threshold) { $conn->insert('policy_category_thresholds', [ 'policy_id' => $policyId, 'category_id' => $categoryId, 'threshold' => number_format($threshold, 4, '.', ''), ]); } }); } public function delete(int $id): void { $this->connection()->executeStatement('DELETE FROM policies WHERE id = :id', ['id' => $id]); } /** * Returns active consumers (id + name) referencing this policy. The * admin DELETE endpoint uses this list to refuse deletion with a 409 * response (per SPEC §M07: cascade is wrong here). * * @return list */ public function consumersUsing(int $policyId): array { /** @var list> $rows */ $rows = $this->connection()->fetchAllAssociative( 'SELECT id, name FROM consumers WHERE policy_id = :pid ORDER BY id ASC', ['pid' => $policyId] ); return array_map( static fn (array $r): array => ['id' => (int) $r['id'], 'name' => (string) $r['name']], $rows ); } /** * @return array category_id => threshold */ private function loadThresholds(int $policyId): array { /** @var list> $rows */ $rows = $this->connection()->fetchAllAssociative( 'SELECT category_id, threshold FROM policy_category_thresholds WHERE policy_id = :pid', ['pid' => $policyId] ); $out = []; foreach ($rows as $row) { $out[(int) $row['category_id']] = (float) $row['threshold']; } return $out; } /** * @return array> policy_id => (category_id => threshold) */ private function loadAllThresholds(): array { /** @var list> $rows */ $rows = $this->connection()->fetchAllAssociative( 'SELECT policy_id, category_id, threshold FROM policy_category_thresholds' ); $out = []; foreach ($rows as $row) { $out[(int) $row['policy_id']][(int) $row['category_id']] = (float) $row['threshold']; } return $out; } /** * @param array $row * @param array $thresholds */ private function hydrate(array $row, array $thresholds): Policy { $createdAt = isset($row['created_at']) && $row['created_at'] !== null ? new DateTimeImmutable((string) $row['created_at'], new DateTimeZone('UTC')) : new DateTimeImmutable('now', new DateTimeZone('UTC')); return new Policy( id: (int) $row['id'], name: (string) $row['name'], description: $row['description'] !== null ? (string) $row['description'] : null, includeManualBlocks: (bool) $row['include_manual_blocks'], thresholds: $thresholds, createdAt: $createdAt, ); } }