<?php

declare(strict_types=1);

namespace App\Infrastructure\Reputation;

use App\Infrastructure\Db\RepositoryBase;
use DateTimeImmutable;
use DateTimeZone;
use Doctrine\DBAL\ParameterType;

/**
 * Append-only writer + reader for the `reports` table.
 *
 * `insert()` returns the new id and writes binary `ip_bin` correctly on
 * both SQLite and MySQL via RepositoryBase's binding helper. `forScoring()`
 * loads the (received_at, weight_at_report) pairs needed by PairScorer.
 */
final class ReportRepository extends RepositoryBase
{
    public function insert(
        string $ipBin,
        string $ipText,
        int $categoryId,
        int $reporterId,
        float $weightAtReport,
        ?string $metadataJson,
        DateTimeImmutable $receivedAt,
    ): int {
        $this->insertRow(
            'reports',
            [
                'ip_bin' => $ipBin,
                'ip_text' => $ipText,
                'category_id' => $categoryId,
                'reporter_id' => $reporterId,
                'weight_at_report' => number_format($weightAtReport, 2, '.', ''),
                'metadata_json' => $metadataJson,
                'received_at' => $receivedAt->format('Y-m-d H:i:s'),
            ],
            ['ip_bin' => ParameterType::LARGE_OBJECT]
        );

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

    /**
     * Returns the (received_at, weight_at_report) pairs for an ip+category
     * within the cutoff window, newest first. PairScorer iterates them.
     *
     * @return list<array{received_at: DateTimeImmutable, weight: float}>
     */
    public function forScoring(string $ipBin, int $categoryId, DateTimeImmutable $cutoff): array
    {
        $sql = 'SELECT received_at, weight_at_report '
            . 'FROM reports WHERE ip_bin = :ip AND category_id = :cat AND received_at >= :cutoff '
            . 'ORDER BY received_at DESC';

        $stmt = $this->connection()->prepare($sql);
        $stmt->bindValue('ip', $ipBin, ParameterType::LARGE_OBJECT);
        $stmt->bindValue('cat', $categoryId, ParameterType::INTEGER);
        $stmt->bindValue('cutoff', $cutoff->format('Y-m-d H:i:s'));

        /** @var list<array<string, mixed>> $rows */
        $rows = $stmt->executeQuery()->fetchAllAssociative();
        $out = [];
        foreach ($rows as $row) {
            $out[] = [
                'received_at' => new DateTimeImmutable((string) $row['received_at'], new DateTimeZone('UTC')),
                'weight' => (float) $row['weight_at_report'],
            ];
        }

        return $out;
    }

    /**
     * Count of reports in the last 30 days for (ip, category) — used to
     * keep `ip_scores.report_count_30d` in sync on the synchronous path.
     */
    public function recentCount(string $ipBin, int $categoryId, DateTimeImmutable $since): int
    {
        $stmt = $this->connection()->prepare(
            'SELECT COUNT(*) FROM reports WHERE ip_bin = :ip AND category_id = :cat AND received_at >= :since'
        );
        $stmt->bindValue('ip', $ipBin, ParameterType::LARGE_OBJECT);
        $stmt->bindValue('cat', $categoryId, ParameterType::INTEGER);
        $stmt->bindValue('since', $since->format('Y-m-d H:i:s'));

        return (int) $stmt->executeQuery()->fetchOne();
    }

    /**
     * Most-recent `received_at` for a pair, ignoring the cutoff window.
     * Used by RecomputeScoresJob to set `ip_scores.last_report_at`
     * accurately so the drop-stale rule (score < threshold AND
     * last_report_at < now − 90 days) fires on truly dead pairs.
     */
    public function lastReceivedAt(string $ipBin, int $categoryId): ?DateTimeImmutable
    {
        $stmt = $this->connection()->prepare(
            'SELECT MAX(received_at) FROM reports WHERE ip_bin = :ip AND category_id = :cat'
        );
        $stmt->bindValue('ip', $ipBin, ParameterType::LARGE_OBJECT);
        $stmt->bindValue('cat', $categoryId, ParameterType::INTEGER);
        $value = $stmt->executeQuery()->fetchOne();
        if ($value === false || $value === null) {
            return null;
        }

        return new DateTimeImmutable((string) $value, new DateTimeZone('UTC'));
    }

    /**
     * Distinct (ip_bin, ip_text, category_id) triplets from `reports` whose
     * `received_at` is at or after `$since`. Used by RecomputeScoresJob in
     * incremental mode (recently-touched pairs) and full mode (entire
     * table; pass the epoch).
     *
     * @return list<array{ip_bin: string, ip_text: string, category_id: int}>
     */
    public function distinctPairsSince(DateTimeImmutable $since, ?int $limit = null): array
    {
        $sql = 'SELECT DISTINCT ip_bin, ip_text, category_id FROM reports '
            . 'WHERE received_at >= :since ORDER BY ip_bin, category_id';
        if ($limit !== null) {
            $sql .= ' LIMIT ' . (int) $limit;
        }

        $stmt = $this->connection()->prepare($sql);
        $stmt->bindValue('since', $since->format('Y-m-d H:i:s'));

        /** @var list<array<string, mixed>> $rows */
        $rows = $stmt->executeQuery()->fetchAllAssociative();
        $out = [];
        foreach ($rows as $row) {
            $out[] = [
                'ip_bin' => (string) $row['ip_bin'],
                'ip_text' => (string) $row['ip_text'],
                'category_id' => (int) $row['category_id'],
            ];
        }

        return $out;
    }
}
