irdb/api/src/Infrastructure/Policy/PolicyRepository.php

<?php

declare(strict_types=1);

namespace App\Infrastructure\Policy;

use App\Domain\Policy\Policy;
use App\Infrastructure\Db\RepositoryBase;
use DateTimeImmutable;
use DateTimeZone;
use Doctrine\DBAL\Connection;

/**
 * DBAL gateway for `policies` and `policy_category_thresholds`.
 *
 * Threshold rows live in a separate join table but the policy makes no
 * sense without them: every read loads a policy together with all its
 * thresholds in a small two-query fetch. Writes that touch thresholds
 * happen inside a single transaction (see `replaceThresholds`).
 */
final class PolicyRepository extends RepositoryBase
{
    public function findById(int $id): ?Policy
    {
        /** @var array<string, mixed>|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<string, mixed>|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<Policy>
     */
    public function listAll(): array
    {
        /** @var list<array<string, mixed>> $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<int, float> $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<string, mixed> $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<int, float> $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<array{id: int, name: string}>
     */
    public function consumersUsing(int $policyId): array
    {
        /** @var list<array<string, mixed>> $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<int, float> category_id => threshold
     */
    private function loadThresholds(int $policyId): array
    {
        /** @var list<array<string, mixed>> $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<int, array<int, float>> policy_id => (category_id => threshold)
     */
    private function loadAllThresholds(): array
    {
        /** @var list<array<string, mixed>> $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<string, mixed> $row
     * @param array<int, float>    $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,
        );
    }
}