includes/dashboard/top_pages.php

<?php
declare(strict_types=1);

/*
|--------------------------------------------------------------------------
| Top pages pagination
|--------------------------------------------------------------------------
*/

$topPageNum = max(1, (int)($_GET['tp'] ?? 1));

$allowedPerPage = [20, 50, 100, 500, 1000];
$topPerPage = (int)($_GET['per'] ?? 20);

if (!in_array($topPerPage, $allowedPerPage, true)) {
    $topPerPage = 20;
}

$topPagesGrouped = [];

refreshPageLabelsForRange($db, $rangeSql, $rangeParams, 20);

$sqliteTopPages = fetchAll($db, "
    SELECT
        site,
        page_key,
        COALESCE(NULLIF(MAX(CASE WHEN page_resolved = 1 THEN title END), ''), MAX(title)) AS title,
        COALESCE(NULLIF(MAX(CASE WHEN page_resolved = 1 THEN url END), ''), MAX(url)) AS url,
        SUM(views) AS views,
        MAX(page_resolved) AS page_resolved
    FROM pages_daily
    WHERE $rangeSql
    GROUP BY site, page_key
", $rangeParams);

foreach ($sqliteTopPages as $row) {
    $site = (string)($row['site'] ?? '');
    $pageKey = (string)($row['page_key'] ?? '');

    if ($site === '' || $pageKey === '') {
        continue;
    }

    $key = $site . "\0" . $pageKey;

    $topPagesGrouped[$key] = [
        'site' => $site,
        'page_key' => $pageKey,
        'title' => (string)($row['title'] ?? ''),
        'url' => (string)($row['url'] ?? ''),
        'views' => (int)($row['views'] ?? 0),
        'page_resolved' => (int)($row['page_resolved'] ?? 0),
    ];
}

if ($includeArchives) {
    foreach (archivePages($rangeStart, $rangeEnd) as $row) {
        $site = (string)($row['site'] ?? '');
        $pageKey = (string)($row['page_key'] ?? '');

        if ($site === '' || $pageKey === '') {
            continue;
        }

        $key = $site . "\0" . $pageKey;

        $topPagesGrouped[$key] ??= [
            'site' => $site,
            'page_key' => $pageKey,
            'title' => '',
            'url' => '',
            'views' => 0,
            'page_resolved' => 0,
        ];

        $topPagesGrouped[$key]['views'] += (int)($row['views'] ?? 0);

        $resolved = (int)($row['page_resolved'] ?? 0);
        $title = (string)($row['title'] ?? '');
        $url = (string)($row['url'] ?? '');

        if ($resolved === 1 || $topPagesGrouped[$key]['title'] === '') {
            if ($title !== '') {
                $topPagesGrouped[$key]['title'] = $title;
            }

            if ($url !== '') {
                $topPagesGrouped[$key]['url'] = $url;
            }

            $topPagesGrouped[$key]['page_resolved'] = max(
                $topPagesGrouped[$key]['page_resolved'],
                $resolved
            );
        }
    }
}

$topPages = array_values($topPagesGrouped);

usort(
    $topPages,
    fn($a, $b) =>
        ($b['views'] <=> $a['views']) ?:
        strcmp((string)$a['title'], (string)$b['title'])
);

$topTotal = count($topPages);
$topLastPage = max(1, (int)ceil($topTotal / $topPerPage));
$topPageNum = min($topPageNum, $topLastPage);
$topOffset = ($topPageNum - 1) * $topPerPage;

$topPages = array_slice($topPages, $topOffset, $topPerPage);