api/export.php

<?php
declare(strict_types=1);

require_once __DIR__ . '/../includes/core.php';

loadTranslations();
require __DIR__ . '/../includes/dashboard/_init.php';

$format = strtolower((string)($_GET['format'] ?? 'json'));

if (!in_array($format, ['csv', 'json', 'txt'], true)) {
    http_response_code(400);
    exit('Invalid export format');
}

$isAll = $view === 'all';
$rangeStart = null;
$rangeEnd = null;

if (!$isAll) {
    $rangeStart = (string)($rangeParams[0] ?? '');
    $rangeEnd = (string)($rangeParams[1] ?? $rangeStart);
}

function exportSiteMatches(array $row, string $currentSite): bool {
    return $currentSite === '' || (string)($row['site'] ?? '') === $currentSite;
}

function exportInRange(string $day, ?string $start, ?string $end): bool {
    return $start === null || $end === null || ($day >= $start && $day <= $end);
}

function exportAddTotals(array &$total, array $row): void {
    foreach ($total as $key => $_) {
        $total[$key] += (int)($row[$key] ?? 0);
    }
}

function exportGroupedRows(array $rows, string $keyName): array {
    $grouped = [];

    foreach ($rows as $row) {
        $key = (string)($row[$keyName] ?? '');
        if ($key === '') continue;

        $grouped[$key] ??= [$keyName => $key, 'views' => 0];
        $grouped[$key]['views'] += (int)($row['views'] ?? 0);
    }

    usort($grouped, fn($a, $b) => $b['views'] <=> $a['views']);
    return $grouped;
}

function exportAddPage(array &$pages, array $row): void {
    $site = (string)($row['site'] ?? '');
    $pageKey = (string)($row['page_key'] ?? '');

    if ($site === '' || $pageKey === '') return;

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

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

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

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

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

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

$summary = [
    'unique_visitors' => 0,
    'visits' => 0,
    'pageviews' => 0,
    'bots' => 0,
];

$sqliteSummary = one($db, "
    SELECT
        COALESCE(SUM(unique_visitors), 0) AS unique_visitors,
        COALESCE(SUM(visits), 0) AS visits,
        COALESCE(SUM(pageviews), 0) AS pageviews,
        COALESCE(SUM(bots), 0) AS bots
    FROM hits_daily
    WHERE $rangeSql
", $rangeParams);

exportAddTotals($summary, $sqliteSummary);

$countriesRaw = $showCountries ? fetchAll($db, "
    SELECT country, SUM(views) AS views
    FROM countries_daily
    WHERE $rangeSql
    GROUP BY country
", $rangeParams) : [];

$referrersRaw = fetchAll($db, "
    SELECT referrer, SUM(views) AS views
    FROM referrers_daily
    WHERE $rangeSql
    GROUP BY referrer
", $rangeParams);

$pagesRaw = fetchAll($db, "
    SELECT site, page_key, title, url, views, page_resolved
    FROM pages_daily
    WHERE $rangeSql
", $rangeParams);

$pagesGrouped = [];

foreach ($pagesRaw as $row) {
    exportAddPage($pagesGrouped, $row);
}

foreach (glob(archiveDir() . '/*.json') ?: [] as $file) {
    $json = json_decode((string)file_get_contents($file), true);

    if (!is_array($json)) continue;

    foreach (($json['days'] ?? []) as $row) {
        $archiveDay = (string)($row['day'] ?? '');
        if (exportInRange($archiveDay, $rangeStart, $rangeEnd) && exportSiteMatches($row, $currentSite)) {
            exportAddTotals($summary, $row);
        }
    }

    if ($showCountries) {
        foreach (($json['countries'] ?? []) as $row) {
            $archiveDay = (string)($row['day'] ?? '');
            if (exportInRange($archiveDay, $rangeStart, $rangeEnd) && exportSiteMatches($row, $currentSite)) {
                $countriesRaw[] = $row;
            }
        }
    }

    foreach (($json['referrers'] ?? []) as $row) {
        $archiveDay = (string)($row['day'] ?? '');
        if (exportInRange($archiveDay, $rangeStart, $rangeEnd) && exportSiteMatches($row, $currentSite)) {
            $referrersRaw[] = $row;
        }
    }

    foreach (($json['pages'] ?? []) as $row) {
        $archiveDay = (string)($row['day'] ?? '');
        if (exportInRange($archiveDay, $rangeStart, $rangeEnd) && exportSiteMatches($row, $currentSite)) {
            exportAddPage($pagesGrouped, $row);
        }
    }
}

$countries = $showCountries ? exportGroupedRows($countriesRaw, 'country') : [];

$referrersTmp = [];

foreach ($referrersRaw as $row) {
    $referrer = (string)($row['referrer'] ?? '');

    if (strcasecmp($referrer, BRIVACIA_UNKNOWN) === 0) {
        $canonical = BRIVACIA_UNKNOWN;
        $label = t('ui.unknown');
    } else {
        $canonical = referrerCanonical($referrer);
        $label = referrerLabel($referrer);
    }

    $referrersTmp[$canonical] ??= [
        'referrer' => $canonical,
        'label' => $label,
        'views' => 0,
    ];

    $referrersTmp[$canonical]['views'] += (int)($row['views'] ?? 0);
}

$referrers = array_values($referrersTmp);
usort($referrers, fn($a, $b) => $b['views'] <=> $a['views']);

$pages = array_values($pagesGrouped);

foreach ($pages as &$page) {
    $page['label'] = cleanPageTitle(
        (string)$page['title'],
        (string)$page['site'],
        (string)$page['page_key'],
        (string)$page['url']
    );

    $page['url'] = pageUrl(
        (string)$page['site'],
        (string)$page['page_key'],
        (string)$page['url']
    );

    unset($page['page_resolved']);
}

unset($page);

usort($pages, fn($a, $b) => $b['views'] <=> $a['views']);

$export = [
    'meta' => [
        'generated_at' => date('c'),
        'instance' => brivacia_setting('dashboard.instance_name', 'Brivacia'),
        'view' => $view,
        'range_start' => $rangeStart,
        'range_end' => $rangeEnd,
    ],
    'summary' => $summary,
    'countries' => $countries,
    'referrers' => $referrers,
    'top_pages' => $pages,
];

$filename = 'brivacia-' . $view . '-' . date('Ymd-His') . '.' . $format;

if ($format === 'json') {
    header('Content-Type: application/json; charset=utf-8');
    header('Content-Disposition: attachment; filename="' . $filename . '"');

    echo json_encode(
        $export,
        JSON_PRETTY_PRINT |
        JSON_UNESCAPED_UNICODE |
        JSON_UNESCAPED_SLASHES
    );

    exit;
}

if ($format === 'txt') {
    header('Content-Type: text/plain; charset=utf-8');
    header('Content-Disposition: attachment; filename="' . $filename . '"');

    echo "Brivacia export\n";
    echo "Generated at: {$export['meta']['generated_at']}\n";
    echo "Instance: {$export['meta']['instance']}\n";
    echo "View: {$export['meta']['view']}\n";
    echo "Range start: " . ($rangeStart ?? 'all') . "\n";
    echo "Range end: " . ($rangeEnd ?? 'all') . "\n\n";

    echo "Summary\n";
    foreach ($summary as $key => $value) {
        echo "- $key: $value\n";
    }

    echo "\nCountries\n";
    foreach ($countries as $row) {
        echo "- {$row['country']}: {$row['views']}\n";
    }

    echo "\nReferrers\n";
    foreach ($referrers as $row) {
        echo "- {$row['label']} ({$row['referrer']}): {$row['views']}\n";
    }

    echo "\nTop pages\n";
    foreach ($pages as $row) {
        echo "- {$row['label']} — {$row['views']} views\n";
        echo "  {$row['url']}\n";
    }

    exit;
}

header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename="' . $filename . '"');

$out = fopen('php://output', 'w');

/* UTF-8 BOM for Excel/LibreOffice comfort */
fwrite($out, "\xEF\xBB\xBF");

fputcsv($out, ['section', 'key', 'label', 'value', 'site', 'url']);

foreach ($summary as $key => $value) {
    fputcsv($out, ['summary', $key, $key, $value, '', '']);
}

foreach ($countries as $row) {
    fputcsv($out, [
        'country',
        $row['country'],
        $row['country'],
        $row['views'],
        '',
        '',
    ]);
}

foreach ($referrers as $row) {
    fputcsv($out, [
        'referrer',
        $row['referrer'],
        $row['label'],
        $row['views'],
        '',
        '',
    ]);
}

foreach ($pages as $row) {
    fputcsv($out, [
        'top_page',
        $row['page_key'],
        $row['label'],
        $row['views'],
        $row['site'],
        $row['url'],
    ]);
}

fclose($out);