<?php

declare(strict_types=1);

namespace App\Infrastructure\Reputation;

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

/**
 * UPSERT into `ip_scores` for the synchronous-on-ingest path.
 *
 * SQLite uses `ON CONFLICT(ip_bin, category_id) DO UPDATE`; MySQL uses
 * `ON DUPLICATE KEY UPDATE`. Single-replica deployments don't need
 * row-level locking — `ip_scores` rows churn fast and the bulk recompute
 * (M05) is the authority anyway.
 */
class IpScoreRepository extends RepositoryBase
{
    public function upsert(
        string $ipBin,
        string $ipText,
        int $categoryId,
        float $score,
        int $reportCount30d,
        DateTimeImmutable $lastReportAt,
        DateTimeImmutable $recomputedAt,
    ): void {
        $platform = $this->connection()->getDatabasePlatform()::class;
        $isMysql = stripos($platform, 'mysql') !== false || stripos($platform, 'mariadb') !== false;

        if ($isMysql) {
            $sql = 'INSERT INTO ip_scores (ip_bin, ip_text, category_id, score, report_count_30d, last_report_at, recomputed_at) '
                . 'VALUES (:ip_bin, :ip_text, :cat, :score, :cnt, :last, :recomputed) '
                . 'ON DUPLICATE KEY UPDATE '
                . 'ip_text = VALUES(ip_text), score = VALUES(score), report_count_30d = VALUES(report_count_30d), '
                . 'last_report_at = VALUES(last_report_at), recomputed_at = VALUES(recomputed_at)';
        } else {
            $sql = 'INSERT INTO ip_scores (ip_bin, ip_text, category_id, score, report_count_30d, last_report_at, recomputed_at) '
                . 'VALUES (:ip_bin, :ip_text, :cat, :score, :cnt, :last, :recomputed) '
                . 'ON CONFLICT(ip_bin, category_id) DO UPDATE SET '
                . 'ip_text = excluded.ip_text, score = excluded.score, report_count_30d = excluded.report_count_30d, '
                . 'last_report_at = excluded.last_report_at, recomputed_at = excluded.recomputed_at';
        }

        $stmt = $this->connection()->prepare($sql);
        $stmt->bindValue('ip_bin', $ipBin, ParameterType::LARGE_OBJECT);
        $stmt->bindValue('ip_text', $ipText);
        $stmt->bindValue('cat', $categoryId, ParameterType::INTEGER);
        $stmt->bindValue('score', number_format($score, 4, '.', ''));
        $stmt->bindValue('cnt', $reportCount30d, ParameterType::INTEGER);
        $stmt->bindValue('last', $lastReportAt->format('Y-m-d H:i:s'));
        $stmt->bindValue('recomputed', $recomputedAt->format('Y-m-d H:i:s'));

        $stmt->executeStatement();
    }

    public function findScore(string $ipBin, int $categoryId): ?float
    {
        $stmt = $this->connection()->prepare(
            'SELECT score FROM ip_scores 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();

        return $value === false ? null : (float) $value;
    }

    /**
     * True if any (ip_bin, category_id) row exists with a non-zero score.
     * The single-IP "is this scored?" check for the admin UI.
     */
    public function hasAnyScore(string $ipBin): bool
    {
        $stmt = $this->connection()->prepare(
            'SELECT 1 FROM ip_scores WHERE ip_bin = :ip AND score > 0 LIMIT 1'
        );
        $stmt->bindValue('ip', $ipBin, ParameterType::LARGE_OBJECT);

        return $stmt->executeQuery()->fetchOne() !== false;
    }

    /**
     * All score rows for one IP — used by the admin IP-detail page to
     * show the score-per-category breakdown.
     *
     * @return list<array{category_id: int, score: float, last_report_at: ?string, report_count_30d: int, recomputed_at: ?string}>
     */
    public function scoresForIp(string $ipBin): array
    {
        $stmt = $this->connection()->prepare(
            'SELECT category_id, score, last_report_at, report_count_30d, recomputed_at '
            . 'FROM ip_scores WHERE ip_bin = :ip ORDER BY score DESC'
        );
        $stmt->bindValue('ip', $ipBin, ParameterType::LARGE_OBJECT);

        /** @var list<array<string, mixed>> $rows */
        $rows = $stmt->executeQuery()->fetchAllAssociative();
        $out = [];
        foreach ($rows as $row) {
            $out[] = [
                'category_id' => (int) $row['category_id'],
                'score' => (float) $row['score'],
                'last_report_at' => $row['last_report_at'] !== null ? (string) $row['last_report_at'] : null,
                'report_count_30d' => (int) $row['report_count_30d'],
                'recomputed_at' => $row['recomputed_at'] !== null ? (string) $row['recomputed_at'] : null,
            ];
        }

        return $out;
    }

    /**
     * Paginated IP search.
     *
     * Filters:
     *  - q: ip_text **prefix** (LIKE 'q%'). The controller validates that
     *    `q` only contains IP-shaped characters, so a non-anchored
     *    `%q%` is never produced (SEC_REVIEW F30). Defence-in-depth: this
     *    method also rejects any `q` with non-IP characters before
     *    building the LIKE.
     *  - categoryId: only count IPs with a row in this category (score > 0)
     *  - minScore / maxScore: filter MAX(score) per IP via HAVING
     *  - countryCode / asn: filter via LEFT JOIN on ip_enrichment
     *  - statusManualBins / statusAllowBins / statusScored / statusClean:
     *    pre-resolved sets for the status filter; the controller maps the
     *    `status=` query value to one of these.
     *
     * Returns paged rows with aggregate columns and a total count.
     *
     * @param array{
     *     q?: ?string,
     *     category_id?: ?int,
     *     min_score?: ?float,
     *     max_score?: ?float,
     *     country?: ?string,
     *     asn?: ?int,
     *     status?: ?string,
     *     manual_bins?: list<string>,
     *     allow_bins?: list<string>,
     * } $filters
     * @return array{
     *     items: list<array{ip_bin: string, ip_text: string, max_score: float, last_report_at: ?string, pair_count: int}>,
     *     total: int,
     * }
     */
    public function searchIps(array $filters, int $limit, int $offset): array
    {
        $where = [];
        $params = [];

        $q = $filters['q'] ?? null;
        if (is_string($q) && $q !== '' && preg_match('/^[\da-fA-F:.]+$/', $q) === 1) {
            // Anchored prefix only — never `%q%`. The controller already
            // validates this shape, but the repo enforces it again so a
            // future caller cannot accidentally drive a full-table scan.
            $where[] = 's.ip_text LIKE :q';
            $params['q'] = $q . '%';
        }

        $catId = $filters['category_id'] ?? null;
        if (is_int($catId) && $catId > 0) {
            $where[] = 's.category_id = :catId';
            $where[] = 's.score > 0';
            $params['catId'] = $catId;
        }

        $country = $filters['country'] ?? null;
        $asn = $filters['asn'] ?? null;
        $joinEnrichment = (is_string($country) && $country !== '') || (is_int($asn) && $asn > 0);
        if (is_string($country) && $country !== '') {
            $where[] = 'e.country_code = :country';
            $params['country'] = strtoupper($country);
        }
        if (is_int($asn) && $asn > 0) {
            $where[] = 'e.asn = :asn';
            $params['asn'] = $asn;
        }

        $manualBins = $filters['manual_bins'] ?? [];
        $allowBins = $filters['allow_bins'] ?? [];
        $status = $filters['status'] ?? null;
        if (is_string($status)) {
            switch ($status) {
                case 'manual':
                    if ($manualBins === []) {
                        // No matches possible — short-circuit.
                        return ['items' => [], 'total' => 0];
                    }
                    [$inExpr, $inParams] = $this->buildBinIn($manualBins, 'mb');
                    $where[] = 's.ip_bin IN (' . $inExpr . ')';
                    $params = array_merge($params, $inParams);

                    break;
                case 'allowlisted':
                    if ($allowBins === []) {
                        return ['items' => [], 'total' => 0];
                    }
                    [$inExpr, $inParams] = $this->buildBinIn($allowBins, 'ab');
                    $where[] = 's.ip_bin IN (' . $inExpr . ')';
                    $params = array_merge($params, $inParams);

                    break;
                case 'scored':
                    $where[] = 's.score > 0';

                    break;
                case 'clean':
                    if ($manualBins !== []) {
                        [$inExpr, $inParams] = $this->buildBinIn($manualBins, 'mb');
                        $where[] = 's.ip_bin NOT IN (' . $inExpr . ')';
                        $params = array_merge($params, $inParams);
                    }
                    if ($allowBins !== []) {
                        [$inExpr, $inParams] = $this->buildBinIn($allowBins, 'ab');
                        $where[] = 's.ip_bin NOT IN (' . $inExpr . ')';
                        $params = array_merge($params, $inParams);
                    }

                    break;
            }
        }

        $minScore = $filters['min_score'] ?? null;
        $maxScore = $filters['max_score'] ?? null;
        $having = [];
        if ($minScore !== null) {
            $having[] = 'MAX(s.score) >= :minScore';
            $params['minScore'] = number_format($minScore, 4, '.', '');
        }
        if ($maxScore !== null) {
            $having[] = 'MAX(s.score) <= :maxScore';
            $params['maxScore'] = number_format($maxScore, 4, '.', '');
        }

        $sql = 'SELECT s.ip_bin, s.ip_text, '
            . 'MAX(s.score) AS max_score, '
            . 'MAX(s.last_report_at) AS last_report_at, '
            . 'COUNT(*) AS pair_count '
            . 'FROM ip_scores s ';
        if ($joinEnrichment) {
            $sql .= 'LEFT JOIN ip_enrichment e ON e.ip_bin = s.ip_bin ';
        }
        if ($where !== []) {
            $sql .= 'WHERE ' . implode(' AND ', $where) . ' ';
        }
        $sql .= 'GROUP BY s.ip_bin, s.ip_text ';
        if ($having !== []) {
            $sql .= 'HAVING ' . implode(' AND ', $having) . ' ';
        }
        $sql .= 'ORDER BY max_score DESC, s.ip_bin ASC ';
        $sql .= 'LIMIT :limit OFFSET :offset';

        // Threading binary IN-list params through: DBAL needs LARGE_OBJECT
        // type for `ip_bin` blob comparisons on MySQL; SQLite is happy
        // with strings either way. Mark all parameters whose names start
        // with "mb" (manual_bins) or "ab" (allow_bins).
        $itemsParams = $params;
        $itemsParams['limit'] = $limit;
        $itemsParams['offset'] = $offset;
        $itemTypes = ['limit' => ParameterType::INTEGER, 'offset' => ParameterType::INTEGER];
        foreach (array_keys($params) as $name) {
            if (str_starts_with($name, 'mb') || str_starts_with($name, 'ab')) {
                $itemTypes[$name] = ParameterType::LARGE_OBJECT;
            }
        }

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

        // Count: wrap the SELECT (without ORDER/LIMIT) in a subquery.
        $countSql = 'SELECT COUNT(*) FROM (SELECT s.ip_bin FROM ip_scores s ';
        if ($joinEnrichment) {
            $countSql .= 'LEFT JOIN ip_enrichment e ON e.ip_bin = s.ip_bin ';
        }
        if ($where !== []) {
            $countSql .= 'WHERE ' . implode(' AND ', $where) . ' ';
        }
        $countSql .= 'GROUP BY s.ip_bin';
        if ($having !== []) {
            $countSql .= ' HAVING ' . implode(' AND ', $having);
        }
        $countSql .= ') t';
        $countTypes = [];
        foreach (array_keys($params) as $name) {
            if (str_starts_with($name, 'mb') || str_starts_with($name, 'ab')) {
                $countTypes[$name] = ParameterType::LARGE_OBJECT;
            }
        }
        $total = (int) $this->connection()->fetchOne($countSql, $params, $countTypes);

        $items = [];
        foreach ($rows as $row) {
            $items[] = [
                'ip_bin' => (string) $row['ip_bin'],
                'ip_text' => (string) $row['ip_text'],
                'max_score' => (float) $row['max_score'],
                'last_report_at' => $row['last_report_at'] !== null ? (string) $row['last_report_at'] : null,
                'pair_count' => (int) $row['pair_count'],
            ];
        }

        return ['items' => $items, 'total' => $total];
    }

    /**
     * Batched "top scoring category" lookup keyed by ip_bin. For each input
     * bin, returns the `category_id` with the highest `score > 0` row, or
     * absent if no such row exists.
     *
     * SEC_REVIEW F32: the admin IPs list previously called
     * `scoresForIp` per page row; at `page_size=200` that's 200
     * round-trips just to pick the top category slug. One query keyed on
     * the bin set replaces all of them.
     *
     * @param list<string> $ipBins
     * @return array<string, int> map of ip_bin → top category_id (only IPs
     *     with at least one score > 0 row are present)
     */
    public function topCategoryByIpBins(array $ipBins): array
    {
        if ($ipBins === []) {
            return [];
        }

        $names = [];
        $params = [];
        $types = [];
        foreach ($ipBins as $i => $bin) {
            $name = 'b' . $i;
            $names[] = ':' . $name;
            $params[$name] = $bin;
            $types[$name] = ParameterType::LARGE_OBJECT;
        }
        // Order by ip_bin then score DESC; the first row encountered per
        // bin in PHP is its top category.
        $sql = 'SELECT ip_bin, category_id, score FROM ip_scores '
            . 'WHERE score > 0 AND ip_bin IN (' . implode(', ', $names) . ') '
            . 'ORDER BY ip_bin ASC, score DESC, category_id ASC';

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

        $out = [];
        foreach ($rows as $row) {
            $bin = (string) $row['ip_bin'];
            if (!isset($out[$bin])) {
                $out[$bin] = (int) $row['category_id'];
            }
        }

        return $out;
    }

    /**
     * Histogram of IPs by their max score across all categories, bucketed in
     * `$bucketSize`-wide bins (e.g. 5 → 0..5, 5..10, …). Only IPs with score
     * > 0 are counted. Returns one row per bucket that has at least one IP,
     * plus the overall max score so callers can size axes without a second
     * query.
     *
     * @return array{
     *     buckets: list<array{start: float, count: int}>,
     *     max_score: float,
     *     bucket_size: float,
     * }
     */
    public function scoreDistribution(float $bucketSize): array
    {
        if ($bucketSize <= 0) {
            $bucketSize = 5.0;
        }
        $sql = 'SELECT CAST(max_score / :bucket AS INTEGER) AS bucket_idx, COUNT(*) AS cnt '
            . 'FROM (SELECT ip_bin, MAX(score) AS max_score FROM ip_scores '
            . 'WHERE score > 0 GROUP BY ip_bin) t '
            . 'GROUP BY bucket_idx ORDER BY bucket_idx';
        /** @var list<array<string, mixed>> $rows */
        $rows = $this->connection()->fetchAllAssociative($sql, [
            'bucket' => number_format($bucketSize, 4, '.', ''),
        ]);

        $buckets = [];
        foreach ($rows as $row) {
            $buckets[] = [
                'start' => (float) $row['bucket_idx'] * $bucketSize,
                'count' => (int) $row['cnt'],
            ];
        }

        $maxScore = (float) ($this->connection()->fetchOne(
            'SELECT MAX(score) FROM ip_scores WHERE score > 0'
        ) ?: 0);

        return [
            'buckets' => $buckets,
            'max_score' => $maxScore,
            'bucket_size' => $bucketSize,
        ];
    }

    /**
     * Per-category histogram of (ip, category) score pairs, bucketed in
     * `$bucketSize`-wide bins. One series per `category_id` that has any
     * scored IP — empty categories are simply absent. Used by the policy
     * editor to draw one line per category instead of an aggregate.
     *
     * @return array{
     *     categories: array<int, array{buckets: list<array{start: float, count: int}>, max_score: float}>,
     *     bucket_size: float,
     *     overall_max_score: float,
     * }
     */
    public function scoreDistributionByCategory(float $bucketSize): array
    {
        if ($bucketSize <= 0) {
            $bucketSize = 5.0;
        }
        $sql = 'SELECT category_id, CAST(score / :bucket AS INTEGER) AS bucket_idx, '
            . 'COUNT(*) AS cnt, MAX(score) AS max_score '
            . 'FROM ip_scores WHERE score > 0 '
            . 'GROUP BY category_id, bucket_idx ORDER BY category_id, bucket_idx';
        /** @var list<array<string, mixed>> $rows */
        $rows = $this->connection()->fetchAllAssociative($sql, [
            'bucket' => number_format($bucketSize, 4, '.', ''),
        ]);

        /** @var array<int, array{buckets: list<array{start: float, count: int}>, max_score: float}> $byCategory */
        $byCategory = [];
        $overallMax = 0.0;
        foreach ($rows as $row) {
            $catId = (int) $row['category_id'];
            $start = (float) $row['bucket_idx'] * $bucketSize;
            $count = (int) $row['cnt'];
            $max = (float) $row['max_score'];

            if (!isset($byCategory[$catId])) {
                $byCategory[$catId] = ['buckets' => [], 'max_score' => 0.0];
            }
            $byCategory[$catId]['buckets'][] = ['start' => $start, 'count' => $count];
            if ($max > $byCategory[$catId]['max_score']) {
                $byCategory[$catId]['max_score'] = $max;
            }
            if ($max > $overallMax) {
                $overallMax = $max;
            }
        }

        return [
            'categories' => $byCategory,
            'bucket_size' => $bucketSize,
            'overall_max_score' => $overallMax,
        ];
    }

    /**
     * Build a parametrised `(:p0, :p1, …)` clause + matching params map for
     * a list of binary IPs. Used by the search's status filter.
     *
     * @param list<string> $bins
     * @return array{0: string, 1: array<string, string>}
     */
    private function buildBinIn(array $bins, string $prefix): array
    {
        $names = [];
        $params = [];
        foreach ($bins as $i => $bin) {
            $name = $prefix . $i;
            $names[] = ':' . $name;
            $params[$name] = $bin;
        }

        return [implode(', ', $names), $params];
    }

    /**
     * All (ip_bin, ip_text, category_id) currently in `ip_scores` whose
     * `recomputed_at` is older than `$staleBefore` (NULLs counted as stale
     * — defensive for older rows). Used by RecomputeScoresJob to refresh
     * decay on pairs that haven't been touched lately.
     *
     * @return list<array{ip_bin: string, ip_text: string, category_id: int}>
     */
    public function pairsStaleBefore(DateTimeImmutable $staleBefore): array
    {
        $stmt = $this->connection()->prepare(
            'SELECT ip_bin, ip_text, category_id FROM ip_scores '
            . 'WHERE recomputed_at IS NULL OR recomputed_at < :before '
            . 'ORDER BY ip_bin, category_id'
        );
        $stmt->bindValue('before', $staleBefore->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;
    }

    /**
     * Every pair in `ip_scores` — used by full recompute to sweep rows
     * whose underlying reports may all be older than the cutoff (so they
     * wouldn't appear in `reports.distinctPairsSince` anymore).
     *
     * @return list<array{ip_bin: string, ip_text: string, category_id: int}>
     */
    public function allPairs(): array
    {
        /** @var list<array<string, mixed>> $rows */
        $rows = $this->connection()->fetchAllAssociative(
            'SELECT ip_bin, ip_text, category_id FROM ip_scores ORDER BY ip_bin, category_id'
        );
        $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;
    }

    /**
     * Drops rows whose score is below `$minScore` AND whose
     * `last_report_at` is older than `$staleBefore`. SPEC §5: prune dead
     * pairs but keep recently-active ones even if their score temporarily
     * dipped.
     *
     * Returns the number of rows deleted.
     */
    public function dropStale(float $minScore, DateTimeImmutable $staleBefore): int
    {
        return (int) $this->connection()->executeStatement(
            'DELETE FROM ip_scores WHERE score < :min AND last_report_at < :before',
            [
                'min' => number_format($minScore, 4, '.', ''),
                'before' => $staleBefore->format('Y-m-d H:i:s'),
            ],
        );
    }

    /**
     * Fetch (ip_bin, ip_text, category_id, score) rows whose category is in
     * `$categoryIds` AND whose score meets that category's `$thresholds`
     * entry. Single SQL query with a per-category WHERE clause UNION; the
     * caller groups results per-IP.
     *
     * Returns an empty list when `$categoryIds` is empty.
     *
     * Performance note: at 50k rows we skip the per-row PHP-side type
     * normalisation that the rest of the repo does — the caller treats
     * the strings as opaque bytes (ip_bin) or casts on demand. Saves
     * ~25 % of build time on the SPEC's perf budget.
     *
     * @param list<int>         $categoryIds
     * @param array<int, float> $thresholds category_id => threshold
     * @return list<array<string, mixed>>
     */
    public function findExceedingThresholds(array $categoryIds, array $thresholds): array
    {
        if ($categoryIds === [] || $thresholds === []) {
            return [];
        }

        $clauses = [];
        $params = [];
        $i = 0;
        foreach ($categoryIds as $categoryId) {
            if (!isset($thresholds[$categoryId])) {
                continue;
            }
            $key = 't' . $i;
            $catKey = 'c' . $i;
            $clauses[] = "(category_id = :{$catKey} AND score >= :{$key})";
            $params[$catKey] = $categoryId;
            $params[$key] = number_format($thresholds[$categoryId], 4, '.', '');
            ++$i;
        }
        if ($clauses === []) {
            return [];
        }

        $sql = 'SELECT ip_bin, ip_text, category_id, score FROM ip_scores WHERE '
            . implode(' OR ', $clauses);

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

        return $rows;
    }
}
