<?php

declare(strict_types=1);

namespace App\Infrastructure\Reputation;

use App\Infrastructure\Db\RepositoryBase;
use DateTimeImmutable;
use DateTimeZone;

/**
 * Aggregates for the admin dashboard. Each method returns the smallest
 * shape the controller needs; the controller assembles the response.
 *
 * No caching here — the controller wraps the whole thing in a 30 s
 * in-memory cache (SPEC §M09.1).
 */
final class DashboardStatsRepository extends RepositoryBase
{
    public function manualBlocksCount(): int
    {
        return (int) $this->connection()->fetchOne('SELECT COUNT(*) FROM manual_blocks');
    }

    public function allowlistCount(): int
    {
        return (int) $this->connection()->fetchOne('SELECT COUNT(*) FROM allowlist');
    }

    public function reportsSince(DateTimeImmutable $since): int
    {
        return (int) $this->connection()->fetchOne(
            'SELECT COUNT(*) FROM reports WHERE received_at >= :since',
            ['since' => $since->format('Y-m-d H:i:s')]
        );
    }

    /**
     * @return list<array{hour: string, count: int}>
     */
    public function reportsByHourSince(DateTimeImmutable $since): array
    {
        $platform = $this->connection()->getDatabasePlatform()::class;
        $isMysql = stripos($platform, 'mysql') !== false || stripos($platform, 'mariadb') !== false;

        // Group by truncated-to-hour timestamp. Both adapters have their
        // own date functions; substr() works on ISO 8601 strings on
        // SQLite, and MySQL has DATE_FORMAT.
        $sql = $isMysql
            ? "SELECT DATE_FORMAT(received_at, '%Y-%m-%dT%H:00:00Z') AS hour, COUNT(*) AS c "
                . 'FROM reports WHERE received_at >= :since GROUP BY hour ORDER BY hour ASC'
            : "SELECT substr(replace(received_at, ' ', 'T'), 1, 13) || ':00:00Z' AS hour, COUNT(*) AS c "
                . 'FROM reports WHERE received_at >= :since GROUP BY hour ORDER BY hour ASC';

        /** @var list<array<string, mixed>> $rows */
        $rows = $this->connection()->fetchAllAssociative($sql, ['since' => $since->format('Y-m-d H:i:s')]);
        $out = [];
        foreach ($rows as $row) {
            $out[] = ['hour' => (string) $row['hour'], 'count' => (int) $row['c']];
        }

        return $out;
    }

    /**
     * @return list<array{name: string, count: int}>
     */
    public function topReportersSince(DateTimeImmutable $since, int $limit = 10): array
    {
        $sql = 'SELECT rep.name AS name, COUNT(*) AS c '
            . 'FROM reports r JOIN reporters rep ON rep.id = r.reporter_id '
            . 'WHERE r.received_at >= :since '
            . 'GROUP BY rep.name '
            . 'ORDER BY c DESC '
            . 'LIMIT ' . max(1, $limit);

        /** @var list<array<string, mixed>> $rows */
        $rows = $this->connection()->fetchAllAssociative($sql, ['since' => $since->format('Y-m-d H:i:s')]);
        $out = [];
        foreach ($rows as $row) {
            $out[] = ['name' => (string) $row['name'], 'count' => (int) $row['c']];
        }

        return $out;
    }

    /**
     * Distinct blocked IPs per UTC calendar day per category across the
     * window `[since, now]`. "Blocked" here is approximated by "received
     * at least one report on that day in that category" — every such IP
     * is contributing to its score for the category, so the histogram is
     * a faithful proxy for "which categories saw activity each day".
     *
     * Days/categories with zero IPs are filled in by the caller — the
     * SQL only emits (day, category) pairs that have at least one row.
     *
     * @return list<array{day: string, category: string, count: int}>
     */
    public function blockedIpsByDayCategorySince(DateTimeImmutable $since): array
    {
        $platform = $this->connection()->getDatabasePlatform()::class;
        $isMysql = stripos($platform, 'mysql') !== false || stripos($platform, 'mariadb') !== false;

        $dayExpr = $isMysql
            ? "DATE_FORMAT(r.received_at, '%Y-%m-%d')"
            : "substr(replace(r.received_at, ' ', 'T'), 1, 10)";

        $sql = 'SELECT ' . $dayExpr . ' AS d, c.slug AS slug, COUNT(DISTINCT r.ip_bin) AS cnt '
            . 'FROM reports r JOIN categories c ON c.id = r.category_id '
            . 'WHERE r.received_at >= :since '
            . 'GROUP BY d, c.slug '
            . 'ORDER BY d ASC, c.slug ASC';

        /** @var list<array<string, mixed>> $rows */
        $rows = $this->connection()->fetchAllAssociative($sql, ['since' => $since->format('Y-m-d H:i:s')]);
        $out = [];
        foreach ($rows as $row) {
            $out[] = [
                'day' => (string) $row['d'],
                'category' => (string) $row['slug'],
                'count' => (int) $row['cnt'],
            ];
        }

        return $out;
    }

    /**
     * Active categories — used to ensure the dashboard chart shows zeros
     * for categories that exist but had no activity in the window.
     *
     * @return list<string>
     */
    public function activeCategorySlugs(): array
    {
        $rows = $this->connection()->fetchAllAssociative(
            'SELECT slug FROM categories WHERE is_active = :a ORDER BY slug ASC',
            ['a' => 1],
        );

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

    /**
     * @return list<array{slug: string, count: int}>
     */
    public function topCategoriesSince(DateTimeImmutable $since, int $limit = 10): array
    {
        $sql = 'SELECT c.slug AS slug, COUNT(*) AS cnt '
            . 'FROM reports r JOIN categories c ON c.id = r.category_id '
            . 'WHERE r.received_at >= :since '
            . 'GROUP BY c.slug '
            . 'ORDER BY cnt DESC '
            . 'LIMIT ' . max(1, $limit);

        /** @var list<array<string, mixed>> $rows */
        $rows = $this->connection()->fetchAllAssociative($sql, ['since' => $since->format('Y-m-d H:i:s')]);
        $out = [];
        foreach ($rows as $row) {
            $out[] = ['slug' => (string) $row['slug'], 'count' => (int) $row['cnt']];
        }

        return $out;
    }

    /**
     * Last-finished status of each known job, keyed off the most-recent
     * `job_runs` row per `job_name`. Returns an empty list if the table
     * is empty (fresh deployment).
     *
     * @param array<string, int> $expectedIntervals job_name => seconds
     * @return list<array{name: string, last_finished_at: ?string, status: string, overdue: bool}>
     */
    public function jobsStatus(array $expectedIntervals, DateTimeImmutable $now): array
    {
        $sql = 'SELECT job_name, status, finished_at, started_at FROM job_runs r1 '
            . 'WHERE r1.id = ('
            . '  SELECT MAX(id) FROM job_runs r2 WHERE r2.job_name = r1.job_name'
            . ') '
            . 'ORDER BY job_name';

        /** @var list<array<string, mixed>> $rows */
        $rows = $this->connection()->fetchAllAssociative($sql);
        $out = [];
        foreach ($rows as $row) {
            $name = (string) $row['job_name'];
            $finishedAt = $row['finished_at'] !== null ? (string) $row['finished_at'] : null;
            $overdue = false;
            if ($finishedAt !== null && isset($expectedIntervals[$name])) {
                $finished = new DateTimeImmutable($finishedAt, new DateTimeZone('UTC'));
                $overdue = ($now->getTimestamp() - $finished->getTimestamp()) > $expectedIntervals[$name];
            }
            $out[] = [
                'name' => $name,
                'last_finished_at' => $finishedAt,
                'status' => (string) $row['status'],
                'overdue' => $overdue,
            ];
        }

        return $out;
    }

    /**
     * Crude "active blocks" estimate: count of distinct IPs in `ip_scores`
     * with at least one non-zero score, plus the count of single-IP
     * manual blocks. Subnet manual blocks are not expanded.
     *
     * SPEC §M09.1 says "count of IPs currently in any policy's blocklist
     * using `moderate` as default reference"; computing that exactly
     * means evaluating the BlocklistBuilder, which is too heavy for a
     * dashboard ping. The number here is a stable proxy.
     */
    public function activeBlocksApprox(): int
    {
        $scored = (int) $this->connection()->fetchOne(
            'SELECT COUNT(DISTINCT ip_bin) FROM ip_scores WHERE score > 0'
        );
        $manualSingles = (int) $this->connection()->fetchOne(
            "SELECT COUNT(*) FROM manual_blocks WHERE kind = 'ip'"
        );

        return $scored + $manualSingles;
    }
}
