irdb/api/src/Infrastructure/ManualBlock/ManualBlockRepository.php

<?php

declare(strict_types=1);

namespace App\Infrastructure\ManualBlock;

use App\Domain\Ip\Cidr;
use App\Domain\Ip\IpAddress;
use App\Domain\ManualBlock\ManualBlock;
use App\Infrastructure\Db\RepositoryBase;
use DateTimeImmutable;
use DateTimeZone;
use Doctrine\DBAL\ParameterType;

/**
 * DBAL gateway for `manual_blocks`. The table holds both single-IP and CIDR
 * subnet entries in one shape (per SPEC §4); `kind` decides which set of
 * columns is populated.
 *
 * No update path: an admin removes and re-adds. Soft-delete is unnecessary
 * — manual_blocks is itself the override layer, not a long-lived audit
 * surface (audit lives in `audit_log`, M12).
 */
final class ManualBlockRepository extends RepositoryBase
{
    /**
     * Find a single-IP manual block by exact `ip_bin` match. Used by the
     * admin IP-detail endpoint to render the manual-block panel.
     */
    public function findByIpBin(string $ipBin): ?ManualBlock
    {
        $row = $this->fetchByIpBin('manual_blocks', $ipBin);
        if ($row === null) {
            return null;
        }
        // The base helper matches WHERE ip_bin = ?, but `manual_blocks`
        // stores network entries with `ip_bin = NULL` and a separate
        // `network_bin`, so we additionally filter to kind=ip rows.
        if (($row['kind'] ?? '') !== ManualBlock::KIND_IP) {
            return null;
        }

        return self::hydrate($row);
    }

    public function findById(int $id): ?ManualBlock
    {
        /** @var array<string, mixed>|false $row */
        $row = $this->connection()->fetchAssociative(
            'SELECT id, kind, ip_bin, network_bin, prefix_length, reason, expires_at, created_at, created_by_user_id '
            . 'FROM manual_blocks WHERE id = :id',
            ['id' => $id]
        );

        return $row === false ? null : self::hydrate($row);
    }

    /**
     * @param array{kind?: ?string} $filters
     * @return list<ManualBlock>
     */
    public function list(?int $limit, ?int $offset, array $filters = []): array
    {
        $sql = 'SELECT id, kind, ip_bin, network_bin, prefix_length, reason, expires_at, created_at, created_by_user_id '
            . 'FROM manual_blocks';
        $params = [];
        $types = [];
        $where = [];

        if (isset($filters['kind']) && $filters['kind'] !== null) {
            $where[] = 'kind = :kind';
            $params['kind'] = $filters['kind'];
        }
        if ($where !== []) {
            $sql .= ' WHERE ' . implode(' AND ', $where);
        }
        $sql .= ' ORDER BY id DESC';

        if ($limit !== null) {
            $sql .= ' LIMIT :limit';
            $params['limit'] = $limit;
            $types['limit'] = ParameterType::INTEGER;
            if ($offset !== null) {
                $sql .= ' OFFSET :offset';
                $params['offset'] = $offset;
                $types['offset'] = ParameterType::INTEGER;
            }
        }

        /** @var list<array<string, mixed>> $rows */
        $rows = $this->connection()->fetchAllAssociative($sql, $params, $types);

        return array_map(self::hydrate(...), $rows);
    }

    public function count(?string $kindFilter = null): int
    {
        if ($kindFilter !== null) {
            return (int) $this->connection()->fetchOne(
                'SELECT COUNT(*) FROM manual_blocks WHERE kind = :kind',
                ['kind' => $kindFilter]
            );
        }

        return (int) $this->connection()->fetchOne('SELECT COUNT(*) FROM manual_blocks');
    }

    public function createIp(
        IpAddress $ip,
        ?string $reason,
        ?DateTimeImmutable $expiresAt,
        ?int $createdByUserId,
    ): int {
        $this->insertRow('manual_blocks', [
            'kind' => ManualBlock::KIND_IP,
            'ip_bin' => $ip->binary(),
            'network_bin' => null,
            'prefix_length' => null,
            'reason' => $reason,
            'expires_at' => $expiresAt?->format('Y-m-d H:i:s'),
            'created_by_user_id' => $createdByUserId,
        ], ['ip_bin' => ParameterType::LARGE_OBJECT]);

        return (int) $this->connection()->lastInsertId();
    }

    public function createSubnet(
        Cidr $cidr,
        ?string $reason,
        ?DateTimeImmutable $expiresAt,
        ?int $createdByUserId,
    ): int {
        $this->insertRow('manual_blocks', [
            'kind' => ManualBlock::KIND_SUBNET,
            'ip_bin' => null,
            'network_bin' => $cidr->network(),
            'prefix_length' => $cidr->prefixLength(),
            'reason' => $reason,
            'expires_at' => $expiresAt?->format('Y-m-d H:i:s'),
            'created_by_user_id' => $createdByUserId,
        ], ['network_bin' => ParameterType::LARGE_OBJECT]);

        return (int) $this->connection()->lastInsertId();
    }

    public function delete(int $id): void
    {
        $this->connection()->executeStatement('DELETE FROM manual_blocks WHERE id = :id', ['id' => $id]);
    }

    /**
     * IDs of blocks whose `expires_at` has passed. Used by a future cleanup
     * job; the caller decides whether to delete or just flag.
     *
     * @return list<int>
     */
    public function findExpired(DateTimeImmutable $now): array
    {
        /** @var list<array<string, mixed>> $rows */
        $rows = $this->connection()->fetchAllAssociative(
            'SELECT id FROM manual_blocks WHERE expires_at IS NOT NULL AND expires_at < :now',
            ['now' => $now->format('Y-m-d H:i:s')]
        );

        return array_map(static fn (array $r): int => (int) $r['id'], $rows);
    }

    /**
     * @return list<ManualBlock>
     */
    public function listSubnets(): array
    {
        /** @var list<array<string, mixed>> $rows */
        $rows = $this->connection()->fetchAllAssociative(
            'SELECT id, kind, ip_bin, network_bin, prefix_length, reason, expires_at, created_at, created_by_user_id '
            . 'FROM manual_blocks WHERE kind = :kind ORDER BY id',
            ['kind' => ManualBlock::KIND_SUBNET]
        );

        return array_map(self::hydrate(...), $rows);
    }

    /**
     * @return list<ManualBlock>
     */
    public function listIps(): array
    {
        /** @var list<array<string, mixed>> $rows */
        $rows = $this->connection()->fetchAllAssociative(
            'SELECT id, kind, ip_bin, network_bin, prefix_length, reason, expires_at, created_at, created_by_user_id '
            . 'FROM manual_blocks WHERE kind = :kind ORDER BY id',
            ['kind' => ManualBlock::KIND_IP]
        );

        return array_map(self::hydrate(...), $rows);
    }

    /**
     * @param array<string, mixed> $row
     */
    private static function hydrate(array $row): ManualBlock
    {
        $tz = new DateTimeZone('UTC');
        $createdAt = isset($row['created_at']) && $row['created_at'] !== null
            ? new DateTimeImmutable((string) $row['created_at'], $tz)
            : new DateTimeImmutable('now', $tz);
        $expiresAt = isset($row['expires_at']) && $row['expires_at'] !== null
            ? new DateTimeImmutable((string) $row['expires_at'], $tz)
            : null;

        $kind = (string) $row['kind'];
        $ip = null;
        $cidr = null;
        if ($kind === ManualBlock::KIND_IP && $row['ip_bin'] !== null) {
            $ip = IpAddress::fromBinary((string) $row['ip_bin']);
        } elseif ($kind === ManualBlock::KIND_SUBNET && $row['network_bin'] !== null) {
            $cidr = Cidr::fromBinary((string) $row['network_bin'], (int) $row['prefix_length']);
        }

        return new ManualBlock(
            id: (int) $row['id'],
            kind: $kind,
            ip: $ip,
            cidr: $cidr,
            reason: $row['reason'] !== null ? (string) $row['reason'] : null,
            expiresAt: $expiresAt,
            createdAt: $createdAt,
            createdByUserId: $row['created_by_user_id'] !== null ? (int) $row['created_by_user_id'] : null,
        );
    }
}