<?php
declare(strict_types=1);

namespace Celena\Plugin\Analytics\Admin;

use Celena\Core\Database\Connection;
use Celena\Core\Http\Request;
use Celena\Core\Http\Response;
use Celena\Core\Security\Auth;
use Celena\Core\Template\Engine;
use Celena\Module\AdminBase\AdminController;
use Celena\Module\Settings\Options;
use Celena\Plugin\Analytics\Support\GoalsRepo;
use Celena\Plugin\Analytics\Support\Geo;
use Celena\Plugin\Analytics\Support\Url;

/**
 * Визуальная панель аналитики посетителей: /admin/analytics
 * Источники, цели/конверсии, кампании (UTM), гео, реалтайм, drill-down.
 *
 * ВАЖНО (кросс-СУБД): прод — MySQL, локалка — PostgreSQL. Поэтому никакого
 * MySQL-only SQL (SUBSTRING_INDEX/GROUP_CONCAT/SUM(bool)/DATE()). Простые COUNT —
 * портируемым SQL; группировки/парсинг строк — агрегацией в PHP.
 */
final class AnalyticsAdminController extends AdminController
{
    private const SRC = [
        'ads'      => ['Реклама (Google Ads)', '#1797F0'],
        'organic'  => ['Поиск (organic)',      '#1CE2A6'],
        'social'   => ['Соцсети',              '#F5A065'],
        'referral' => ['Переходы по ссылкам',  '#8B9AAB'],
        'direct'   => ['Прямые заходы',        '#5C6A7D'],
    ];

    private GoalsRepo $goals;

    public function __construct(Engine $engine, Auth $auth, Options $options, private readonly Connection $conn)
    {
        parent::__construct($engine, $auth, $options);
        $this->goals = new GoalsRepo($conn);
    }

    public function index(Request $request): Response
    {
        if ($this->auth->user() === null) {
            return Response::redirect('/admin/login');
        }

        $range = (string) $request->query('range', '7d');
        if (!in_array($range, ['today', '7d', '30d'], true)) $range = '7d';
        [$from, $to, $prevFrom, $prevTo] = $this->window($range);

        $T = $this->conn->table('analytics_events');
        $scalar = fn (string $sql, array $p = []): int => (int) $this->conn->run($sql, $p)->fetchColumn();

        // --- Цели ---
        $goals = $this->goals->all();
        $labelMap = [];
        $valueMap = [];
        $formSubmitSlugs = [];
        $allGoalSlugs = [];
        foreach ($goals as $g) {
            $slug = (string) $g['slug'];
            $labelMap[$slug] = (string) $g['name'];
            $valueMap[$slug] = (float) ($g['value_eur'] ?? 0);
            $allGoalSlugs[] = $slug;
            if ((string) $g['type'] === 'form_submit') $formSubmitSlugs[] = $slug;
        }

        // --- KPI (портируемо: COUNT) для текущего и предыдущего периодов ---
        $kpi = fn (string $f, string $t): array => $this->kpis($T, $scalar, $f, $t, $formSubmitSlugs, $allGoalSlugs);
        $cur  = $kpi($from, $to);
        $prev = $kpi($prevFrom, $prevTo);
        $deltas = [];
        foreach ($cur as $k => $v) $deltas[$k] = $this->delta((int) $v, (int) ($prev[$k] ?? 0));

        // --- Один портируемый выбор строк окна → агрегации в PHP ---
        $ev = $this->conn->builder('analytics_events')
            ->select('session_id', 'visitor_id', 'type', 'event_name', 'url', 'referrer',
                     'source', 'device', 'duration', 'utm_campaign', 'utm_term', 'gclid', 'country', 'created_at')
            ->where('created_at', '>=', $from)->where('created_at', '<', $to)
            ->orderBy('session_id', 'asc')->orderBy('created_at', 'asc')
            ->get();

        // Сессии (первый источник, устройство, pv/ev/dur, страница входа)
        $sess = [];
        $pages = [];
        $refs = [];
        $evtCounts = [];
        $utmCamp = [];
        $utmTerm = [];
        $geoCnt = [];
        $gclidCount = 0;
        foreach ($ev as $r) {
            $sid = (string) $r['session_id'];
            if (!isset($sess[$sid])) {
                $sess[$sid] = ['sid' => $sid, 'started' => $r['created_at'], 'source' => null,
                               'device' => null, 'pv' => 0, 'ev' => 0, 'dur' => 0, 'landing' => null];
            }
            $S =& $sess[$sid];
            if ($S['source'] === null && !empty($r['source'])) $S['source'] = (string) $r['source'];
            if (!empty($r['device'])) $S['device'] = (string) $r['device'];
            $S['dur'] += (int) ($r['duration'] ?? 0);
            if ($r['type'] === 'pageview') {
                $S['pv']++;
                if ($S['landing'] === null) $S['landing'] = (string) $r['url'];
                $path = explode('?', (string) $r['url'])[0] ?: '/';
                $pages[$path] = ($pages[$path] ?? 0) + 1;
                $ref = (string) ($r['referrer'] ?? '');
                if ($ref !== '' && !Url::isInternal($ref)) {
                    $host = Url::host($ref);
                    if ($host !== '') $refs[$host] = ($refs[$host] ?? 0) + 1;
                }
            } elseif ($r['type'] === 'event') {
                $S['ev']++;
                $name = (string) ($r['event_name'] ?? '');
                if ($name !== '') $evtCounts[$name] = ($evtCounts[$name] ?? 0) + 1;
            }
            if (!empty($r['utm_campaign'])) $utmCamp[(string) $r['utm_campaign']] = ($utmCamp[(string) $r['utm_campaign']] ?? 0) + 1;
            if (!empty($r['utm_term']))     $utmTerm[(string) $r['utm_term']] = ($utmTerm[(string) $r['utm_term']] ?? 0) + 1;
            if (!empty($r['gclid']))        $gclidCount++;
            if (!empty($r['country']))      $geoCnt[(string) $r['country']] = ($geoCnt[(string) $r['country']] ?? 0) + 1;
            unset($S);
        }

        // Источники по сессиям
        $srcMap = [];
        foreach ($sess as $s) {
            $key = $s['source'] ?: 'direct';
            $srcMap[$key] = ($srcMap[$key] ?? 0) + 1;
        }
        $srcTotal = array_sum($srcMap) ?: 1;
        $sources = [];
        foreach (self::SRC as $key => [$label, $color]) {
            $c = $srcMap[$key] ?? 0;
            $sources[] = ['label' => $label, 'count' => $c, 'pct' => (int) round($c * 100 / $srcTotal),
                          'color' => $color, 'is_ads' => $key === 'ads' ? '1' : ''];
        }

        // Топ-страницы
        arsort($pages);
        $topMax = $pages === [] ? 1 : max($pages);
        $topPages = [];
        foreach (array_slice($pages, 0, 8, true) as $url => $c) {
            $topPages[] = ['url' => $url, 'count' => $c, 'pct' => (int) round($c * 100 / $topMax)];
        }

        // Реферреры (внешние сайты)
        arsort($refs);
        $refMax = $refs === [] ? 1 : max($refs);
        $referrers = [];
        foreach (array_slice($refs, 0, 10, true) as $host => $c) {
            $referrers[] = ['host' => $host, 'count' => $c, 'pct' => (int) round($c * 100 / $refMax)];
        }

        // Клики/события (подписи из таблицы целей)
        arsort($evtCounts);
        $events = [];
        foreach ($evtCounts as $name => $c) {
            $events[] = ['label' => $labelMap[$name] ?? $name, 'count' => $c];
        }

        // Конверсии по целям: CR% (от сессий) + ценность €
        $sessionsCount = count($sess);
        $goalsPerf = [];
        $totalValue = 0.0;
        foreach ($goals as $g) {
            $slug = (string) $g['slug'];
            $c = $evtCounts[$slug] ?? 0;
            $val = $c * $valueMap[$slug];
            $totalValue += $val;
            $goalsPerf[] = [
                'name'  => (string) $g['name'],
                'count' => $c,
                'cr'    => $sessionsCount > 0 ? round($c * 100 / $sessionsCount, 1) : 0,
                'value' => $val > 0 ? '€' . number_format($val, 0, '.', ' ') : '—',
                'active' => (int) $g['active'] === 1 ? '1' : '',
            ];
        }

        // Воронка визит → заявка
        $sessWithPv = 0; $sessWithLead = 0;
        foreach ($sess as $s) {
            if ($s['pv'] > 0) $sessWithPv++;
        }
        // сессии с form_submit-целью
        $leadSessions = [];
        foreach ($ev as $r) {
            if ($r['type'] === 'event' && in_array((string) ($r['event_name'] ?? ''), $formSubmitSlugs, true)) {
                $leadSessions[(string) $r['session_id']] = true;
            }
        }
        $sessWithLead = count($leadSessions);
        $funnel = [
            ['label' => 'Сессии', 'count' => $sessionsCount, 'pct' => 100],
            ['label' => 'С просмотром', 'count' => $sessWithPv, 'pct' => $sessionsCount > 0 ? (int) round($sessWithPv * 100 / $sessionsCount) : 0],
            ['label' => 'С заявкой', 'count' => $sessWithLead, 'pct' => $sessionsCount > 0 ? (int) round($sessWithLead * 100 / $sessionsCount) : 0],
        ];

        // UTM-кампании / ключи
        arsort($utmCamp); arsort($utmTerm);
        $campaigns = [];
        foreach (array_slice($utmCamp, 0, 8, true) as $name => $c) $campaigns[] = ['name' => $name, 'count' => $c];
        $keywords = [];
        foreach (array_slice($utmTerm, 0, 8, true) as $name => $c) $keywords[] = ['name' => $name, 'count' => $c];

        // Гео
        arsort($geoCnt);
        $geoMax = $geoCnt === [] ? 1 : max($geoCnt);
        $geo = [];
        foreach (array_slice($geoCnt, 0, 10, true) as $code => $c) {
            $geo[] = ['code' => $code, 'name' => Geo::name($code), 'flag' => Geo::flag($code), 'count' => $c, 'pct' => (int) round($c * 100 / $geoMax)];
        }

        // Сессии (последние 25)
        uasort($sess, fn ($a, $b) => strcmp((string) $b['started'], (string) $a['started']));
        $sessList = [];
        foreach (array_slice($sess, 0, 25, true) as $s) {
            $sk = $s['source'] ?: 'direct';
            $sessList[] = [
                'sid'          => $s['sid'],
                'time'         => date('d.m H:i', strtotime((string) $s['started'])),
                'source_label' => self::SRC[$sk][0] ?? $sk,
                'source_color' => self::SRC[$sk][1] ?? '#5C6A7D',
                'device'       => $s['device'] ?: '—',
                'landing'      => $s['landing'] ?: '/',
                'pv'           => $s['pv'],
                'ev'           => $s['ev'],
                'dur'          => $this->fmtTime((int) $s['dur']),
            ];
        }

        // Спарклайн: просмотры за 14 дней (PHP-бакеты)
        $spark = $this->sparkline();

        return $this->view('plugins/analytics/admin/index', [
            'range'      => $range,
            'is_today'   => $range === 'today' ? '1' : '',
            'is_7d'      => $range === '7d' ? '1' : '',
            'is_30d'     => $range === '30d' ? '1' : '',
            'visitors'   => $cur['visitors'],
            'sessions'   => $cur['sessions'],
            'pageviews'  => $cur['pageviews'],
            'avg_time'   => $this->fmtTime($cur['avg_sec']),
            'leads'      => $cur['leads'],
            'goal_actions' => $cur['goal_actions'],
            'd_visitors' => $deltas['visitors'],
            'd_sessions' => $deltas['sessions'],
            'd_pageviews'=> $deltas['pageviews'],
            'd_leads'    => $deltas['leads'],
            'd_goal_actions' => $deltas['goal_actions'],
            'sources'    => $sources,
            'top_pages'  => $topPages,
            'referrers'  => $referrers,
            'events'     => $events,
            'goals_perf' => $goalsPerf,
            'total_value'=> $totalValue > 0 ? '€' . number_format($totalValue, 0, '.', ' ') : '€0',
            'funnel'     => $funnel,
            'campaigns'  => $campaigns,
            'keywords'   => $keywords,
            'gclid_count'=> $gclidCount,
            'geo'        => $geo,
            'sessions_list' => $sessList,
            'spark_svg'  => $spark,
            'has_data'   => $cur['pageviews'] > 0 ? '1' : '',
        ], 'analytics', 'Аналитика');
    }

    /** KPI за окно [from,to) — портируемые COUNT-запросы. */
    private function kpis(string $T, callable $scalar, string $from, string $to, array $formSubmitSlugs, array $allGoalSlugs): array
    {
        $w = ' created_at>=? AND created_at<?';
        $visitors  = $scalar("SELECT COUNT(DISTINCT visitor_id) FROM $T WHERE$w", [$from, $to]);
        $sessions  = $scalar("SELECT COUNT(DISTINCT session_id) FROM $T WHERE$w", [$from, $to]);
        $pageviews = $scalar("SELECT COUNT(*) FROM $T WHERE type='pageview' AND$w", [$from, $to]);
        $avgRow    = $this->conn->run("SELECT AVG(s) a FROM (SELECT SUM(duration) s FROM $T WHERE type='leave' AND$w GROUP BY session_id) x", [$from, $to])->fetchColumn();
        $avgSec    = (int) round((float) ($avgRow ?: 0));
        $leads        = $this->countEvents($T, $formSubmitSlugs, $from, $to);
        $goalActions  = $this->countEvents($T, $allGoalSlugs, $from, $to);
        return [
            'visitors' => $visitors, 'sessions' => $sessions, 'pageviews' => $pageviews,
            'avg_sec' => $avgSec, 'leads' => $leads, 'goal_actions' => $goalActions,
        ];
    }

    /** COUNT событий с event_name в наборе slug'ов (портируемо, через placeholders). */
    private function countEvents(string $T, array $slugs, string $from, string $to): int
    {
        if ($slugs === []) return 0;
        $ph = implode(',', array_fill(0, count($slugs), '?'));
        $params = array_merge($slugs, [$from, $to]);
        return (int) $this->conn->run(
            "SELECT COUNT(*) FROM $T WHERE type='event' AND event_name IN ($ph) AND created_at>=? AND created_at<?",
            $params
        )->fetchColumn();
    }

    /** Дельта текущего vs предыдущего периода: %, направление. */
    private function delta(int $cur, int $prev): array
    {
        if ($prev <= 0) {
            $pct = $cur > 0 ? 100 : 0;
        } else {
            $pct = (int) round(($cur - $prev) * 100 / $prev);
        }
        $dir = $pct > 0 ? 'up' : ($pct < 0 ? 'down' : 'flat');
        return ['pct' => abs($pct), 'dir' => $dir, 'has' => $cur !== $prev ? '1' : ''];
    }

    /** Границы окна: [from, to, prevFrom, prevTo]. */
    private function window(string $range): array
    {
        $now = time();
        $fromTs = match ($range) {
            'today' => strtotime(date('Y-m-d 00:00:00')),
            '30d'   => $now - 30 * 86400,
            default => $now - 7 * 86400,
        };
        $len = max(1, $now - $fromTs);
        return [
            date('Y-m-d H:i:s', $fromTs),
            date('Y-m-d H:i:s', $now + 1),
            date('Y-m-d H:i:s', $fromTs - $len),
            date('Y-m-d H:i:s', $fromTs),
        ];
    }

    private function fmtTime(int $sec): string
    {
        if ($sec <= 0) return '0с';
        $m = intdiv($sec, 60); $s = $sec % 60;
        return $m > 0 ? "{$m}м {$s}с" : "{$s}с";
    }

    /** Спарклайн просмотров за 14 дней (PHP-бакеты по дате — без DATE()/GROUP BY). */
    private function sparkline(): string
    {
        $sparkFrom = date('Y-m-d H:i:s', time() - 13 * 86400);
        $rows = $this->conn->builder('analytics_events')
            ->select('created_at')
            ->where('type', '=', 'pageview')->where('created_at', '>=', $sparkFrom)->get();
        $raw = [];
        foreach ($rows as $r) {
            $d = substr((string) $r['created_at'], 0, 10);
            $raw[$d] = ($raw[$d] ?? 0) + 1;
        }
        $vals = [];
        for ($i = 13; $i >= 0; $i--) $vals[] = $raw[date('Y-m-d', time() - $i * 86400)] ?? 0;
        $max = max(1, max($vals));
        $W = 560; $H = 70; $n = count($vals); $step = $W / ($n - 1);
        $pts = [];
        foreach ($vals as $i => $v) {
            $x = round($i * $step, 1);
            $y = round($H - 6 - ($v / $max) * ($H - 14), 1);
            $pts[] = "$x,$y";
        }
        $poly = implode(' ', $pts);
        $area = "0,$H " . $poly . ",$W,$H";
        return '<svg viewBox="0 0 ' . $W . ' ' . $H . '" preserveAspectRatio="none" style="width:100%;height:70px">'
            . '<polygon points="' . $area . '" fill="rgba(23,151,240,.10)"/>'
            . '<polyline points="' . $poly . '" fill="none" stroke="#1797F0" stroke-width="2.5" stroke-linejoin="round" stroke-linecap="round"/>'
            . '</svg>';
    }

    // ───────────────────────── CSV-экспорт ─────────────────────────

    public function export(Request $request): Response
    {
        if ($this->auth->user() === null) {
            return Response::redirect('/admin/login');
        }
        $range = (string) $request->query('range', '7d');
        if (!in_array($range, ['today', '7d', '30d'], true)) $range = '7d';
        [$from, $to] = $this->window($range);
        $labelMap = $this->goals->labelMap();

        $rows = $this->conn->builder('analytics_events')
            ->select('created_at', 'type', 'event_name', 'url', 'source', 'utm_campaign', 'utm_term',
                     'gclid', 'device', 'country', 'duration', 'session_id', 'visitor_id')
            ->where('created_at', '>=', $from)->where('created_at', '<', $to)
            ->orderBy('id', 'asc')->get();

        $fh = fopen('php://temp', 'r+');
        fwrite($fh, "\xEF\xBB\xBF"); // BOM для Excel
        fputcsv($fh, ['Дата', 'Тип', 'Событие', 'URL', 'Источник', 'Кампания', 'Ключ', 'gclid', 'Устройство', 'Страна', 'Сек', 'Сессия', 'Посетитель'], ',', '"', '');
        foreach ($rows as $r) {
            $name = (string) ($r['event_name'] ?? '');
            fputcsv($fh, [
                (string) $r['created_at'],
                (string) $r['type'],
                $name !== '' ? ($labelMap[$name] ?? $name) : '',
                (string) ($r['url'] ?? ''),
                (string) ($r['source'] ?? ''),
                (string) ($r['utm_campaign'] ?? ''),
                (string) ($r['utm_term'] ?? ''),
                (string) ($r['gclid'] ?? ''),
                (string) ($r['device'] ?? ''),
                (string) ($r['country'] ?? ''),
                (string) ($r['duration'] ?? ''),
                (string) $r['session_id'],
                (string) $r['visitor_id'],
            ], ',', '"', '');
        }
        rewind($fh);
        $csv = stream_get_contents($fh) ?: '';
        fclose($fh);

        return new Response($csv, 200, [
            'Content-Type'        => 'text/csv; charset=UTF-8',
            'Content-Disposition' => 'attachment; filename="analytics-' . $range . '-' . date('Ymd') . '.csv"',
        ]);
    }

    // ───────────────────────── Реалтайм ─────────────────────────

    public function realtime(Request $request): Response
    {
        if ($this->auth->user() === null) {
            return Response::redirect('/admin/login');
        }
        return $this->view('plugins/analytics/admin/realtime', [], 'analytics-realtime', 'Realtime');
    }

    public function realtimeData(Request $request): Response
    {
        if ($this->auth->user() === null) {
            return Response::json(['ok' => false], 401);
        }
        $T = $this->conn->table('analytics_events');
        $since = date('Y-m-d H:i:s', time() - 300); // 5 минут
        $online = (int) $this->conn->run("SELECT COUNT(DISTINCT session_id) FROM $T WHERE created_at>=?", [$since])->fetchColumn();
        $labelMap = $this->goals->labelMap();

        $rows = $this->conn->builder('analytics_events')
            ->select('created_at', 'type', 'event_name', 'url', 'source', 'device', 'country', 'city', 'duration', 'visitor_id')
            ->orderBy('id', 'desc')->limit(40)->get();
        $feed = [];
        foreach ($rows as $r) {
            $name = (string) ($r['event_name'] ?? '');
            $sk = (string) ($r['source'] ?? 'direct') ?: 'direct';
            $vid = (string) ($r['visitor_id'] ?? '');
            $feed[] = [
                'time'   => date('H:i:s', strtotime((string) $r['created_at'])),
                'type'   => (string) $r['type'],
                'label'  => $r['type'] === 'event' ? ($labelMap[$name] ?? $name) : ($r['type'] === 'leave' ? 'Ушёл со страницы' : 'Открыл страницу'),
                'url'    => (string) ($r['url'] ?? ''),
                'source' => self::SRC[$sk][0] ?? $sk,
                'device' => (string) ($r['device'] ?? ''),
                'country'=> (string) ($r['country'] ?? ''),
                'city'   => (string) ($r['city'] ?? ''),
                'dur'    => $r['type'] === 'leave' ? $this->fmtTime((int) ($r['duration'] ?? 0)) : '',
                'vid'    => $vid !== '' ? substr($vid, -4) : '—',
            ];
        }
        return Response::json(['ok' => true, 'online' => $online, 'feed' => $feed]);
    }

    // ───────────────────────── Карта мира ─────────────────────────

    public function map(Request $request): Response
    {
        if ($this->auth->user() === null) {
            return Response::redirect('/admin/login');
        }
        $range = (string) $request->query('range', '30d');
        if (!in_array($range, ['today', '7d', '30d', 'all'], true)) $range = '30d';
        if ($range === 'all') {
            $from = '1970-01-01 00:00:00';
            $to   = date('Y-m-d H:i:s', time() + 1);
        } else {
            [$from, $to] = $this->window($range);
        }

        $rows = $this->conn->builder('analytics_events')
            ->select('country')
            ->where('created_at', '>=', $from)->where('created_at', '<', $to)
            ->get();
        $cnt = [];
        foreach ($rows as $r) {
            $cc = strtoupper(trim((string) ($r['country'] ?? '')));
            if (preg_match('/^[A-Z]{2}$/', $cc)) $cnt[$cc] = ($cnt[$cc] ?? 0) + 1;
        }
        arsort($cnt);
        $max = $cnt === [] ? 1 : max($cnt);
        $list = [];
        $jsCounts = [];
        foreach ($cnt as $cc => $c) {
            $low = strtolower($cc);
            $jsCounts[$low] = $c;
            $list[] = ['code' => $cc, 'name' => Geo::name($cc), 'flag' => Geo::flag($cc),
                       'count' => $c, 'pct' => (int) round($c * 100 / $max)];
        }

        return $this->view('plugins/analytics/admin/map', [
            'range'      => $range,
            'is_today'   => $range === 'today' ? '1' : '',
            'is_7d'      => $range === '7d' ? '1' : '',
            'is_30d'     => $range === '30d' ? '1' : '',
            'is_all'     => $range === 'all' ? '1' : '',
            'list'       => $list,
            'total'      => array_sum($cnt),
            'countries'  => count($cnt),
            'is_empty'   => $cnt === [] ? '1' : '',
            'map_counts' => json_encode($jsCounts, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES) ?: '{}',
            'map_names'  => json_encode(Geo::allNames(), JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES) ?: '{}',
            'map_max'    => $max,
        ], 'analytics-map', 'Карта мира');
    }

    /**
     * Live-данные для карты: кто откуда прямо сейчас.
     * Активность за последние 5 минут, агрегация по странам (distinct сессии) в PHP
     * (кросс-СУБД: прод MySQL, локалка PostgreSQL — без MySQL-only SQL).
     */
    public function mapLive(Request $request): Response
    {
        if ($this->auth->user() === null) {
            return Response::json(['ok' => false], 401);
        }

        $since = date('Y-m-d H:i:s', time() - 300); // «онлайн сейчас» = активность за 5 минут
        $rows = $this->conn->builder('analytics_events')
            ->select('session_id', 'country', 'city', 'lat', 'lon', 'source', 'device', 'created_at')
            ->where('created_at', '>=', $since)
            ->orderBy('id', 'desc')->limit(800)->get();

        $online     = [];   // distinct session_id (всего онлайн)
        $seen       = [];   // session_id => true (берём только первое — самое свежее — появление)
        $byCountry  = [];   // cc_low => сессий
        $citiesByCc = [];   // cc_low => [city => true] (для тултипа маркера)
        $points     = [];   // "lat|lon" => точная точка города
        $feed       = [];
        foreach ($rows as $r) {
            $sid = (string) $r['session_id'];
            if ($sid === '') continue;
            $online[$sid] = true;
            if (isset($seen[$sid])) continue;
            $seen[$sid] = true;

            $cc = strtoupper(trim((string) ($r['country'] ?? '')));
            $valid = (bool) preg_match('/^[A-Z]{2}$/', $cc);
            $cityName = trim((string) ($r['city'] ?? ''));
            $sk = (string) ($r['source'] ?? 'direct') ?: 'direct';

            if ($valid) {
                $low = strtolower($cc);
                $byCountry[$low] = ($byCountry[$low] ?? 0) + 1;
                if ($cityName !== '') $citiesByCc[$low][$cityName] = true;
            }

            $feed[] = [
                'cc'     => $valid ? $cc : '',
                'name'   => $valid ? Geo::name($cc) : '',
                'city'   => $cityName,
                'flag'   => $valid ? Geo::flag($cc) : '',
                'time'   => date('H:i:s', strtotime((string) $r['created_at'])),
                'source' => self::SRC[$sk][0] ?? $sk,
                'device' => (string) ($r['device'] ?? ''),
            ];

            // Точная точка по координатам города (если есть)
            $lat = $r['lat']; $lon = $r['lon'];
            if (is_numeric($lat) && is_numeric($lon)) {
                $latF = (float) $lat; $lonF = (float) $lon;
                $key = round($latF, 2) . '|' . round($lonF, 2);
                if (!isset($points[$key])) {
                    $points[$key] = [
                        'lat'  => $latF,
                        'lon'  => $lonF,
                        'city' => $cityName !== '' ? $cityName : ($valid ? Geo::name($cc) : ''),
                        'cc'   => $valid ? $cc : '',
                        'name' => $valid ? Geo::name($cc) : '',
                        'n'    => 0,
                    ];
                }
                $points[$key]['n']++;
            }
        }

        $counts = $byCountry;
        arsort($counts);

        $cities = [];
        foreach ($citiesByCc as $low => $set) {
            $cities[$low] = implode(', ', array_slice(array_keys($set), 0, 5));
        }

        $list = [];
        foreach ($counts as $low => $c) {
            $cc = strtoupper($low);
            $list[] = ['code' => $cc, 'name' => Geo::name($cc), 'flag' => Geo::flag($cc), 'count' => $c];
        }

        return Response::json([
            'ok'        => true,
            'online'    => count($online),
            'countries' => count($counts),
            'counts'    => (object) $counts,            // cc_low => сессий (для стран без координат / списка)
            'cities'    => (object) $cities,            // cc_low => "Дублин, Корк" (тултип маркера-страны)
            'points'    => array_values($points),       // точные точки городов (lat/lon)
            'list'      => array_slice($list, 0, 30),
            'feed'      => array_slice($feed, 0, 25),
        ]);
    }

    // ───────────────────────── Drill-down ─────────────────────────

    public function session(Request $request, array $params): Response
    {
        if ($this->auth->user() === null) {
            return Response::redirect('/admin/login');
        }
        $sid = preg_replace('/[^a-z0-9]/i', '', (string) ($params['sid'] ?? ''));
        return $this->timeline('session_id', $sid, 'Сессия ' . $sid);
    }

    public function visitor(Request $request, array $params): Response
    {
        if ($this->auth->user() === null) {
            return Response::redirect('/admin/login');
        }
        $vid = preg_replace('/[^a-z0-9]/i', '', (string) ($params['vid'] ?? ''));
        return $this->timeline('visitor_id', $vid, 'Посетитель ' . $vid);
    }

    private function timeline(string $column, string $id, string $title): Response
    {
        $labelMap = $this->goals->labelMap();
        $rows = $this->conn->builder('analytics_events')
            ->where($column, '=', $id)
            ->orderBy('created_at', 'asc')->limit(500)->get();
        $items = [];
        foreach ($rows as $r) {
            $name = (string) ($r['event_name'] ?? '');
            $sk = (string) ($r['source'] ?? 'direct') ?: 'direct';
            $items[] = [
                'time'    => date('d.m.Y H:i:s', strtotime((string) $r['created_at'])),
                'type'    => (string) $r['type'],
                'label'   => $r['type'] === 'event' ? ($labelMap[$name] ?? $name) : ($r['type'] === 'leave' ? 'Уход (' . (int) $r['duration'] . 'с)' : 'Просмотр страницы'),
                'url'     => (string) ($r['url'] ?? ''),
                'referrer'=> (string) ($r['referrer'] ?? ''),
                'source'  => self::SRC[$sk][0] ?? $sk,
                'device'  => (string) ($r['device'] ?? ''),
                'country' => (string) ($r['country'] ?? ''),
            ];
        }
        return $this->view('plugins/analytics/admin/timeline', [
            'title'    => $title,
            'items'    => $items,
            'is_empty' => $items === [] ? '1' : '',
        ], 'analytics', $title);
    }
}
