<?php
declare(strict_types=1);

final class ReportRepository {
    private const LIST_SORT = [
        'priority',
        'generated_at_ms',
        'received_at_ms',
        'crash_type',
        'device_model',
        'app_version',
        'fingerprint',
        'fingerprint_cnt',
        'rating',
    ];
    private const GROUP_SORT = [
        'cnt',
        'fingerprint',
        'crash_type',
        'last_generated_ms',
        'last_received_ms',
    ];

    public static function insert(array $payload): void {
        $pdo = Database::pdo();
        $device = is_array($payload['device'] ?? null) ? $payload['device'] : [];
        $app = is_array($payload['app'] ?? null) ? $payload['app'] : [];
        $json = json_encode($payload, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE);
        if ($json === false) {
            throw new RuntimeException('payload json_encode failed: ' . json_last_error_msg());
        }
        $tags = json_encode($payload['tags'] ?? [], JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE);
        $companyId = Rbac::defaultCompanyId();
        $deviceId = DeviceRepository::upsertFromPayload($companyId, $device, 'auto');
        $stmt = $pdo->prepare(
            'INSERT INTO reports (company_id, device_id, report_id, fingerprint, crash_type, generated_at_ms,
                received_at_ms, device_model, app_version, payload_json, tags_json)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
        );
        $stmt->execute([
            $companyId,
            $deviceId > 0 ? $deviceId : null,
            $payload['report_id'] ?? uniqid('rpt_', true),
            $payload['fingerprint'] ?? 'unknown',
            $payload['crash_type'] ?? 'java',
            (int) ($payload['generated_at_epoch_ms'] ?? 0),
            (int) round(microtime(true) * 1000),
            $device['model'] ?? null,
            $app['version_name'] ?? null,
            $json,
            $tags ?: '[]',
        ]);
    }

    public static function listReports(bool $grouped): array {
        return self::listPage($grouped, 1, 500, 'generated_at_ms', 'desc')['items'];
    }

    public static function listPage(
        bool $grouped,
        int $page,
        int $perPage,
        string $sort,
        string $dir,
        int $sinceMs = 0,
        array $tagFilters = [],
        string $tagMode = 'and'
    ): array {
        if ($tagFilters !== []) {
            return self::listFiltered(
                ['tags' => $tagFilters, 'tag_mode' => $tagMode],
                $page,
                $perPage,
                $sort,
                $dir,
                $sinceMs
            );
        }
        $pdo = Database::pdo();
        $page = max(1, $page);
        $perPage = max(1, min(200, $perPage));
        $dir = strtolower($dir) === 'asc' ? 'ASC' : 'DESC';
        $offset = ($page - 1) * $perPage;
        $userId = (int) (Auth::user()['id'] ?? 0);

        if ($grouped) {
            $sortCol = in_array($sort, self::GROUP_SORT, true) ? $sort : 'cnt';
            $gWhere = ['1=1'];
            $gParams = [];
            self::appendCompanyScope('company_id', $gWhere, $gParams);
            $gWhereSql = implode(' AND ', $gWhere);
            $countStmt = $pdo->prepare(
                "SELECT COUNT(*) FROM (
                    SELECT 1 FROM reports WHERE $gWhereSql GROUP BY fingerprint, crash_type
                 ) t"
            );
            $countStmt->execute($gParams);
            $total = (int) $countStmt->fetchColumn();
            $sql = "SELECT fingerprint, crash_type,
                           MIN(device_model) AS device_model,
                           MAX(app_version) AS app_version,
                           COUNT(*) AS cnt,
                           MAX(generated_at_ms) AS last_generated_ms,
                           MAX(received_at_ms) AS last_received_ms
                    FROM reports
                    WHERE $gWhereSql
                    GROUP BY fingerprint, crash_type
                    ORDER BY $sortCol $dir
                    LIMIT $perPage OFFSET $offset";
            $stmt = $pdo->prepare($sql);
            $stmt->execute($gParams);
            $items = $stmt->fetchAll();
            foreach ($items as &$row) {
                $row['fingerprint_cnt'] = (int) ($row['cnt'] ?? 0);
                $row['fingerprint_recent_cnt'] = $sinceMs > 0 && (int) ($row['last_received_ms'] ?? 0) > $sinceMs
                    ? (int) $row['cnt'] : 0;
                $row['rating'] = report_rating_score((int) $row['cnt'], (int) $row['fingerprint_recent_cnt'], (int) $row['cnt']);
                $row['os_name'] = '';
                $row['os_version'] = '';
                $row['brief'] = report_brief_lines($row, true);
            }
            unset($row);
            return [
                'items' => $items,
                'total' => $total,
                'page' => $page,
                'per_page' => $perPage,
                'sort' => $sortCol,
                'dir' => strtolower($dir),
                'grouped' => true,
            ];
        }

        $sortCol = in_array($sort, self::LIST_SORT, true) ? $sort : 'priority';
        if ($sortCol === 'rating') {
            $sortCol = 'fingerprint_cnt';
        }
        $orderBy = self::buildListOrder($sortCol === 'priority' ? 'priority' : $sortCol, $dir);
        $where = ['1=1'];
        $scopeParams = [];
        self::appendCompanyScope('r.company_id', $where, $scopeParams);
        $whereSql = implode(' AND ', $where);
        $countStmt = $pdo->prepare("SELECT COUNT(*) FROM reports r WHERE $whereSql");
        $countStmt->execute($scopeParams);
        $total = (int) $countStmt->fetchColumn();
        $sql = 'SELECT ' . self::listSelectColumns() . '
                FROM reports r
                ' . self::fingerprintStatsJoin() . '
                LEFT JOIN report_views v ON v.report_id = r.id AND v.user_id = ?
                WHERE ' . $whereSql . '
                ORDER BY ' . $orderBy . '
                LIMIT ' . $perPage . ' OFFSET ' . $offset;
        $stmt = $pdo->prepare($sql);
        $stmt->execute(array_merge([$sinceMs, $userId], $scopeParams));
        $items = $stmt->fetchAll();
        self::finishListRows($items, $sinceMs, false);

        return [
            'items' => $items,
            'total' => $total,
            'page' => $page,
            'per_page' => $perPage,
            'sort' => $sort === 'priority' ? 'priority' : $sortCol,
            'dir' => strtolower($dir),
            'grouped' => false,
        ];
    }

    /**
     * @param array{similar_to?:int,device?:string,abi?:string,os_sdk?:int,os_release?:string} $filters
     */
    public static function listFiltered(
        array $filters,
        int $page,
        int $perPage,
        string $sort,
        string $dir,
        int $sinceMs = 0
    ): array {
        $pdo = Database::pdo();
        $page = max(1, $page);
        $perPage = max(1, min(200, $perPage));
        $dir = strtolower($dir) === 'asc' ? 'ASC' : 'DESC';
        $userId = (int) (Auth::user()['id'] ?? 0);
        $where = ['1=1'];
        $params = [];
        self::appendCompanyScope('r.company_id', $where, $params);
        $device = trim((string) ($filters['device'] ?? ''));
        if ($device !== '') {
            $like = '%' . $device . '%';
            $where[] = '(r.device_model LIKE ? OR r.payload_json LIKE ?)';
            $params[] = $like;
            $params[] = $like;
        }
        $abi = trim((string) ($filters['abi'] ?? ''));
        if ($abi !== '') {
            $where[] = 'r.payload_json LIKE ?';
            $params[] = '%' . $abi . '%';
        }
        $sdk = (int) ($filters['os_sdk'] ?? 0);
        if ($sdk > 0) {
            $where[] = 'r.payload_json LIKE ?';
            $params[] = '%"sdk_int":' . $sdk . '%';
        }
        $release = trim((string) ($filters['os_release'] ?? ''));
        if ($release !== '') {
            $where[] = '(r.payload_json LIKE ? OR r.payload_json LIKE ?)';
            $params[] = '%"release":"' . $release . '"%';
            $params[] = '%"release": "' . $release . '"%';
        }
        $appVersion = trim((string) ($filters['app_version'] ?? ''));
        if ($appVersion !== '') {
            $like = '%' . $appVersion . '%';
            $where[] = '(r.app_version = ? OR r.app_version LIKE ? OR r.payload_json LIKE ?)';
            $params[] = $appVersion;
            $params[] = $like;
            $params[] = '%"version_name":"' . $appVersion . '"%';
        }
        $build = trim((string) ($filters['build'] ?? ''));
        if ($build !== '') {
            $where[] = 'r.payload_json LIKE ?';
            $params[] = '%' . $build . '%';
        }
        self::appendTagFilters($where, $params, $filters);
        $whereSql = implode(' AND ', $where);
        $countStmt = $pdo->prepare("SELECT COUNT(*) FROM reports r WHERE $whereSql");
        $countStmt->execute($params);
        $total = (int) $countStmt->fetchColumn();
        $sortCol = in_array($sort, self::LIST_SORT, true) ? $sort : 'priority';
        if ($sortCol === 'rating') {
            $sortCol = 'fingerprint_cnt';
        }
        $orderBy = self::buildListOrder($sortCol === 'priority' ? 'priority' : $sortCol, $dir);
        $offset = ($page - 1) * $perPage;
        $sql = 'SELECT ' . self::listSelectColumns() . '
                FROM reports r
                ' . self::fingerprintStatsJoin() . '
                LEFT JOIN report_views v ON v.report_id = r.id AND v.user_id = ?
                WHERE ' . $whereSql . '
                ORDER BY ' . $orderBy . '
                LIMIT ' . $perPage . ' OFFSET ' . $offset;
        $stmt = $pdo->prepare($sql);
        $stmt->execute(array_merge([$sinceMs, $userId], $params));
        $items = $stmt->fetchAll();
        self::finishListRows($items, $sinceMs, false);
        return [
            'items' => $items,
            'total' => $total,
            'page' => $page,
            'per_page' => $perPage,
            'sort' => $sort === 'priority' ? 'priority' : $sortCol,
            'dir' => strtolower($dir),
            'grouped' => false,
            'filter' => $filters,
        ];
    }

    public static function listSimilar(
        int $anchorId,
        int $page,
        int $perPage,
        int $sinceMs = 0
    ): array {
        $anchor = self::getById($anchorId);
        if ($anchor === null) {
            return [
                'items' => [],
                'total' => 0,
                'page' => 1,
                'per_page' => $perPage,
                'sort' => 'similarity',
                'dir' => 'desc',
                'grouped' => false,
                'similar_to' => $anchorId,
            ];
        }
        $pdo = Database::pdo();
        $page = max(1, $page);
        $perPage = max(1, min(200, $perPage));
        $userId = (int) (Auth::user()['id'] ?? 0);
        $payload = is_array($anchor['payload'] ?? null) ? $anchor['payload'] : [];
        $keys = report_similarity_keys($payload);
        $fp = $keys['fingerprint'];
        $ct = $keys['crash_type'];
        $familyLike = $keys['device_family'] !== '' ? '%' . $keys['device_family'] . '%' : null;
        $excLike = $keys['exception'] !== '' ? '%' . $keys['exception'] . '%' : null;
        $sigLike = $keys['signal'] !== '' ? '%' . $keys['signal'] . '%' : null;

        $where = ['r.id != ?'];
        $params = [$anchorId];
        self::appendCompanyScope('r.company_id', $where, $params);
        $or = [];
        if ($fp !== '') {
            $or[] = 'r.fingerprint = ?';
            $params[] = $fp;
        }
        if ($ct !== '') {
            $or[] = 'r.crash_type = ?';
            $params[] = $ct;
        }
        if ($familyLike !== null) {
            $or[] = '(r.device_model LIKE ? OR r.payload_json LIKE ?)';
            $params[] = $familyLike;
            $params[] = $familyLike;
        }
        if ($excLike !== null) {
            $or[] = 'r.payload_json LIKE ?';
            $params[] = $excLike;
        }
        if ($sigLike !== null) {
            $or[] = 'r.payload_json LIKE ?';
            $params[] = $sigLike;
        }
        if ($or === []) {
            $or[] = '1=1';
        }
        $where[] = '(' . implode(' OR ', $or) . ')';
        $whereSql = implode(' AND ', $where);

        $sql = 'SELECT ' . self::listSelectColumns() . '
                FROM reports r
                ' . self::fingerprintStatsJoin() . '
                LEFT JOIN report_views v ON v.report_id = r.id AND v.user_id = ?
                WHERE ' . $whereSql . '
                ORDER BY r.received_at_ms DESC
                LIMIT 1200';
        $stmt = $pdo->prepare($sql);
        $stmt->execute(array_merge([$sinceMs, $userId], $params));
        $rows = $stmt->fetchAll();
        $scored = [];
        foreach ($rows as $row) {
            $p = json_decode($row['payload_json'] ?? '', true);
            if (!is_array($p)) {
                $p = [];
            }
            $score = report_similarity_score($keys, $row, $p);
            if ($score <= 0) {
                continue;
            }
            $row['similarity_score'] = $score;
            $row['similarity_tier'] = $score >= 1000 ? 'exact' : ($score >= 200 ? 'strong' : ($score >= 80 ? 'likely' : 'guess'));
            $scored[] = $row;
        }
        usort($scored, static function (array $a, array $b): int {
            $sa = (int) ($a['similarity_score'] ?? 0);
            $sb = (int) ($b['similarity_score'] ?? 0);
            if ($sa !== $sb) {
                return $sb <=> $sa;
            }
            return ((int) ($b['received_at_ms'] ?? 0)) <=> ((int) ($a['received_at_ms'] ?? 0));
        });
        $total = count($scored);
        $offset = ($page - 1) * $perPage;
        $slice = array_values(array_slice($scored, $offset, $perPage));
        if ($page === 1) {
            $anchorRow = null;
            foreach ($scored as $row) {
                if ((int) $row['id'] === $anchorId) {
                    $anchorRow = $row;
                    break;
                }
            }
            if ($anchorRow === null) {
                $device = is_array($payload['device'] ?? null) ? $payload['device'] : [];
                $app = is_array($payload['app'] ?? null) ? $payload['app'] : [];
                $anchorRow = [
                    'id' => $anchorId,
                    'report_id' => $anchor['report_id'] ?? '',
                    'fingerprint' => $anchor['fingerprint'] ?? ($payload['fingerprint'] ?? ''),
                    'crash_type' => $anchor['crash_type'] ?? ($payload['crash_type'] ?? ''),
                    'generated_at_ms' => (int) ($anchor['generated_at_ms'] ?? 0),
                    'received_at_ms' => (int) ($anchor['received_at_ms'] ?? 0),
                    'device_model' => $anchor['device_model'] ?? ($device['model'] ?? null),
                    'app_version' => $anchor['app_version'] ?? ($app['version_name'] ?? null),
                    'payload_json' => json_encode($payload, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES) ?: '{}',
                    'tags_json' => json_encode($anchor['tags'] ?? [], JSON_UNESCAPED_UNICODE) ?: '[]',
                    'viewed' => !empty($anchor['viewed']) ? 1 : 0,
                    'fingerprint_cnt' => 1,
                    'fingerprint_recent_cnt' => 0,
                ];
            }
            $anchorRow['similarity_score'] = 10000;
            $anchorRow['similarity_tier'] = 'anchor';
            $slice = array_values(array_filter(
                $slice,
                static fn (array $r): bool => (int) ($r['id'] ?? 0) !== $anchorId
            ));
            array_unshift($slice, $anchorRow);
            if (count($slice) > $perPage) {
                $slice = array_slice($slice, 0, $perPage);
            }
        }
        self::finishListRows($slice, $sinceMs, false, true);
        return [
            'items' => $slice,
            'total' => $total + 1,
            'page' => $page,
            'per_page' => $perPage,
            'sort' => 'similarity',
            'dir' => 'desc',
            'grouped' => false,
            'similar_to' => $anchorId,
        ];
    }

    /** @return list<string> */
    public static function searchSuggestions(string $q, int $limit = 10): array {
        $tokens = search_query_tokens($q);
        $prefix = $tokens[0] ?? mb_strtolower(trim($q));
        if (strlen($prefix) < 2) {
            return [];
        }
        $pdo = Database::pdo();
        $where = ['1=1'];
        $params = [];
        self::appendCompanyScope('company_id', $where, $params);
        $whereSql = implode(' AND ', $where);
        $stmt = $pdo->prepare(
            "SELECT payload_json, device_model, app_version, crash_type FROM reports
             WHERE $whereSql ORDER BY received_at_ms DESC LIMIT 500"
        );
        $stmt->execute($params);
        $freq = [];
        while ($row = $stmt->fetch()) {
            $payload = json_decode($row['payload_json'] ?? '', true);
            if (!is_array($payload)) {
                $payload = [];
            }
            $java = is_array($payload['java'] ?? null) ? $payload['java'] : [];
            $device = is_array($payload['device'] ?? null) ? $payload['device'] : [];
            $candidates = array_filter([
                $java['exception'] ?? null,
                $row['crash_type'] ?? null,
                $row['device_model'] ?? null,
                $device['manufacturer'] ?? null,
                $device['model'] ?? null,
                $row['app_version'] ?? null,
            ]);
            foreach ($candidates as $term) {
                $term = trim((string) $term);
                if ($term === '' || strlen($term) < 3) {
                    continue;
                }
                $low = mb_strtolower($term);
                if (!str_contains($low, $prefix)) {
                    continue;
                }
                $freq[$term] = ($freq[$term] ?? 0) + 1;
            }
            if (!empty($java['message'])) {
                if (preg_match_all('/[A-Za-z][A-Za-z0-9_]{3,}(?:Exception|Error)/', (string) $java['message'], $m)) {
                    foreach ($m[0] as $term) {
                        $low = mb_strtolower($term);
                        if (str_contains($low, $prefix)) {
                            $freq[$term] = ($freq[$term] ?? 0) + 2;
                        }
                    }
                }
            }
        }
        arsort($freq);
        return array_slice(array_keys($freq), 0, max(1, min(20, $limit)));
    }

    public static function search(string $q, int $page, int $perPage, int $sinceMs = 0): array {
        $tokens = search_query_tokens($q);
        if ($tokens === []) {
            return [
                'items' => [],
                'total' => 0,
                'page' => 1,
                'per_page' => $perPage,
                'sort' => 'relevance',
                'dir' => 'desc',
                'grouped' => false,
                'search' => $q,
            ];
        }
        $pdo = Database::pdo();
        $page = max(1, $page);
        $perPage = max(1, min(200, $perPage));
        $userId = (int) (Auth::user()['id'] ?? 0);
        $where = ['1=1'];
        $params = [];
        self::appendCompanyScope('r.company_id', $where, $params);
        $orParts = [];
        foreach ($tokens as $token) {
            $like = '%' . $token . '%';
            $orParts[] = '(r.payload_json LIKE ? OR r.device_model LIKE ? OR r.app_version LIKE ?
                OR r.fingerprint LIKE ? OR r.crash_type LIKE ?)';
            array_push($params, $like, $like, $like, $like, $like);
        }
        $where[] = '(' . implode(' OR ', $orParts) . ')';
        $whereSql = implode(' AND ', $where);
        $sql = 'SELECT ' . self::listSelectColumns() . '
                FROM reports r
                ' . self::fingerprintStatsJoin() . '
                LEFT JOIN report_views v ON v.report_id = r.id AND v.user_id = ?
                WHERE ' . $whereSql . '
                ORDER BY r.received_at_ms DESC
                LIMIT 1500';
        $stmt = $pdo->prepare($sql);
        $stmt->execute(array_merge([$sinceMs, $userId], $params));
        $scored = [];
        while ($row = $stmt->fetch()) {
            $payload = json_decode($row['payload_json'] ?? '', true);
            if (!is_array($payload)) {
                $payload = [];
            }
            report_enrich_list_row($row, $sinceMs);
            $score = report_search_score($tokens, $row, $payload);
            if ($score <= 0) {
                continue;
            }
            $row['search_score'] = $score;
            $scored[] = $row;
        }
        usort($scored, static function (array $a, array $b): int {
            $sa = (int) ($a['search_score'] ?? 0);
            $sb = (int) ($b['search_score'] ?? 0);
            if ($sa !== $sb) {
                return $sb <=> $sa;
            }
            return ((int) ($b['received_at_ms'] ?? 0)) <=> ((int) ($a['received_at_ms'] ?? 0));
        });
        $total = count($scored);
        $offset = ($page - 1) * $perPage;
        $slice = array_slice($scored, $offset, $perPage);
        self::finishListRows($slice, $sinceMs, false, false, true);
        return [
            'items' => $slice,
            'total' => $total,
            'page' => $page,
            'per_page' => $perPage,
            'sort' => 'relevance',
            'dir' => 'desc',
            'grouped' => false,
            'search' => $q,
        ];
    }

    /** @param list<array<string,mixed>> $items */
    private static function finishListRows(
        array &$items,
        int $sinceMs,
        bool $grouped,
        bool $similar = false,
        bool $search = false
    ): void {
        foreach ($items as &$row) {
            $row['viewed'] = (bool) ($row['viewed'] ?? false);
            $row['tags'] = parse_report_tags($row['tags_json'] ?? '[]');
            report_enrich_list_row($row, $sinceMs);
            $brief = report_brief_lines($row, $grouped);
            if ($similar && !empty($row['similarity_tier'])) {
                $tier = (string) $row['similarity_tier'];
                $labels = [
                    'anchor' => 'This report',
                    'exact' => 'Same fingerprint',
                    'strong' => 'Strong match',
                    'likely' => 'Likely similar',
                    'guess' => 'Possible match',
                ];
                array_unshift($brief, ($labels[$tier] ?? $tier) . ' · score ' . (int) ($row['similarity_score'] ?? 0));
            }
            if ($search && !empty($row['search_score'])) {
                array_unshift($brief, 'Search match · relevance ' . (int) $row['search_score']);
            }
            $row['brief'] = $brief;
            unset($row['payload_json'], $row['tags_json']);
        }
        unset($row);
    }

    private static function buildListOrder(string $sort, string $dir): string {
        $dir = strtoupper($dir) === 'ASC' ? 'ASC' : 'DESC';
        $fpCnt = 'COALESCE(fp.fingerprint_cnt, 1)';
        if ($sort === 'priority') {
            return "$fpCnt DESC, r.received_at_ms DESC, r.app_version DESC, r.device_model ASC, r.crash_type ASC";
        }
        if ($sort === 'fingerprint_cnt') {
            return "$fpCnt $dir, r.received_at_ms DESC";
        }
        return "r.$sort $dir";
    }

    private static function listSelectColumns(): string {
        return 'r.id, r.report_id, r.fingerprint, r.crash_type, r.generated_at_ms, r.received_at_ms,
                r.device_model, r.app_version, r.payload_json, r.tags_json,
                CASE WHEN v.viewed_at_ms IS NOT NULL THEN 1 ELSE 0 END AS viewed,
                COALESCE(fp.fingerprint_cnt, 1) AS fingerprint_cnt,
                COALESCE(fp.fingerprint_recent_cnt, 0) AS fingerprint_recent_cnt';
    }

    /** One grouped scan for fingerprint counts (replaces per-row correlated subqueries). */
    private static function fingerprintStatsJoin(): string {
        return 'LEFT JOIN (
            SELECT company_id, fingerprint,
                   COUNT(*) AS fingerprint_cnt,
                   SUM(CASE WHEN received_at_ms > ? THEN 1 ELSE 0 END) AS fingerprint_recent_cnt
            FROM reports
            GROUP BY company_id, fingerprint
        ) fp ON fp.company_id = r.company_id AND fp.fingerprint = r.fingerprint';
    }

    public static function setCustomTags(int $reportId, array $tags): bool {
        if (self::getById($reportId) === null) {
            return false;
        }
        $pdo = Database::pdo();
        $normalized = normalize_report_tags($tags);
        $json = json_encode($normalized, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE);
        $stmt = $pdo->prepare('UPDATE reports SET tags_json = ? WHERE id = ?');
        $stmt->execute([$json ?: '[]', $reportId]);
        return true;
    }

    /** Distinct custom tags used across reports (for suggestions). */
    public static function listTagPresets(int $limit = 40): array {
        $pdo = Database::pdo();
        $where = ['tags_json IS NOT NULL', "tags_json != '[]'"];
        $params = [];
        self::appendCompanyScope('company_id', $where, $params);
        $whereSql = implode(' AND ', $where);
        $stmt = $pdo->prepare("SELECT tags_json FROM reports WHERE $whereSql LIMIT 500");
        $stmt->execute($params);
        $rows = $stmt->fetchAll();
        $map = [];
        foreach ($rows as $row) {
            foreach (parse_report_tags($row['tags_json'] ?? '[]') as $tag) {
                $map[$tag['id']] = $tag;
            }
        }
        return array_values($map);
    }

    public static function markViewed(int $reportId, int $userId): void {
        Database::upsertReportView(
            $userId,
            $reportId,
            (int) round(microtime(true) * 1000)
        );
    }

    public static function getById(int $id): ?array {
        $pdo = Database::pdo();
        $userId = (int) (Auth::user()['id'] ?? 0);
        $stmt = $pdo->prepare(
            'SELECT r.*, CASE WHEN v.viewed_at_ms IS NOT NULL THEN 1 ELSE 0 END AS viewed
             FROM reports r
             LEFT JOIN report_views v ON v.report_id = r.id AND v.user_id = ?
             WHERE r.id = ?'
        );
        $stmt->execute([$userId, $id]);
        $row = $stmt->fetch();
        if (!$row) {
            return null;
        }
        if (!Rbac::canAccessReport($row)) {
            return null;
        }
        $decoded = json_decode($row['payload_json'], true);
        $row['payload'] = is_array($decoded) ? $decoded : [];
        $row['tags'] = parse_report_tags($row['tags_json'] ?? '[]');
        $row['viewed'] = (bool) ($row['viewed'] ?? false);
        return $row;
    }

    /**
     * @param list<string> $where
     * @param list<mixed> $params
     */
    private static function appendCompanyScope(string $column, array &$where, array &$params): void {
        Rbac::appendCompanyScope($column, $where, $params);
    }

    /**
     * @param list<string> $where
     * @param list<mixed> $params
     * @param array<string, mixed> $filters
     */
    private static function appendTagFilters(array &$where, array &$params, array $filters): void {
        $tags = $filters['tags'] ?? [];
        if (!is_array($tags) || $tags === []) {
            return;
        }
        $ids = TagCatalog::normalizeFilterIds($tags);
        if ($ids === []) {
            return;
        }
        $mode = strtolower((string) ($filters['tag_mode'] ?? 'and')) === 'or' ? 'or' : 'and';
        $clauses = [];
        foreach ($ids as $id) {
            $clauses[] = '(r.tags_json LIKE ? OR r.tags_json LIKE ?)';
            $params[] = '%"id":"' . $id . '"%';
            $params[] = '%"id": "' . $id . '"%';
        }
        if ($mode === 'or') {
            $where[] = '(' . implode(' OR ', $clauses) . ')';
        } else {
            foreach ($clauses as $clause) {
                $where[] = $clause;
            }
        }
    }

}
