<?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\Domain\Time\Clock;
use App\Infrastructure\Db\RepositoryBase;
use DateTimeImmutable;
use DateTimeZone;
use Doctrine\DBAL\Connection;
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).
 *
 * SPEC §M14.5: every list/lookup filters out rows whose `expires_at` has
 * passed. The `CleanupExpiredManualBlocksJob` prunes them daily so the
 * table doesn't grow unbounded; the read-time filter guarantees correctness
 * between cleanup runs.
 */
final class ManualBlockRepository extends RepositoryBase
{
    /**
     * Optional clock for test injection (advancing "now" past an entry's
     * `expires_at` without sleeping). Production builds wire it via the
     * container; tests that construct the repo directly may leave it null
     * and the system clock takes over.
     */
    public function __construct(Connection $connection, private readonly ?Clock $clock = null)
    {
        parent::__construct($connection);
    }

    private function notExpiredFragment(string $alias = ''): string
    {
        $col = $alias === '' ? 'expires_at' : $alias . '.expires_at';

        return "({$col} IS NULL OR {$col} >= :now_filter)";
    }

    private function nowParamValue(): string
    {
        $now = $this->clock !== null
            ? $this->clock->now()
            : new DateTimeImmutable('now', new DateTimeZone('UTC'));

        return $now->format('Y-m-d H:i:s');
    }

    /**
     * Find a single-IP manual block by exact `ip_bin` match. Used by the
     * admin IP-detail endpoint to render the manual-block panel.
     *
     * Filters out rows whose `expires_at` has passed so an expired manual
     * block doesn't appear "active" to anything that consults it.
     */
    public function findByIpBin(string $ipBin): ?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 ip_bin = :ip_bin AND kind = :kind AND ' . $this->notExpiredFragment(),
            ['ip_bin' => $ipBin, 'kind' => ManualBlock::KIND_IP, 'now_filter' => $this->nowParamValue()],
            ['ip_bin' => ParameterType::LARGE_OBJECT]
        );

        return $row === false ? null : 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 = ['now_filter' => $this->nowParamValue()];
        $types = [];
        $where = [$this->notExpiredFragment()];

        if (isset($filters['kind']) && $filters['kind'] !== null) {
            $where[] = 'kind = :kind';
            $params['kind'] = $filters['kind'];
        }
        $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 AND ' . $this->notExpiredFragment(),
                ['kind' => $kindFilter, 'now_filter' => $this->nowParamValue()]
            );
        }

        return (int) $this->connection()->fetchOne(
            'SELECT COUNT(*) FROM manual_blocks WHERE ' . $this->notExpiredFragment(),
            ['now_filter' => $this->nowParamValue()]
        );
    }

    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 AND ' . $this->notExpiredFragment() . ' ORDER BY id',
            ['kind' => ManualBlock::KIND_SUBNET, 'now_filter' => $this->nowParamValue()]
        );

        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 AND ' . $this->notExpiredFragment() . ' ORDER BY id',
            ['kind' => ManualBlock::KIND_IP, 'now_filter' => $this->nowParamValue()]
        );

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

    /**
     * Hard-delete every block whose `expires_at` has passed. Returns the
     * count for the cleanup job's audit + run-history reporting.
     */
    public function deleteExpired(DateTimeImmutable $now): int
    {
        return (int) $this->connection()->executeStatement(
            'DELETE FROM manual_blocks WHERE expires_at IS NOT NULL AND expires_at < :now',
            ['now' => $now->format('Y-m-d H:i:s')]
        );
    }

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