<?php

declare(strict_types=1);

namespace Plugin\tnt_mcp\src\Mcp\Analytics;

use JTL\DB\DbInterface;
use RuntimeException;
use stdClass;

/**
 * Traffic, on-site search and cart figures — the three shop statistics that do not come from
 * `tbestellung`.
 *
 * Each of the three has one trap that a hand-written `run_sql_read` query gets wrong, and handling
 * those traps honestly is the whole reason these are tools:
 *
 * - **Visitors live in two tables.** `tbesucher` is the current session table, `tbesucherarchiv`
 *   the history, and the shop's own cron moves rows from one to the other. Querying either alone
 *   silently loses today or loses everything before today, so both are counted and de-duplicated
 *   by visitor id.
 * - **Search counters are lifetime, not windowed.** `tsuchanfrage.nAnzahlGesuche` is a running
 *   total with only a `dZuletztGesucht` timestamp beside it — there is no per-day search history in
 *   the shop at all. The window therefore selects *which terms* are reported, never how often they
 *   were searched, and the response says so rather than letting a model read a lifetime counter as
 *   a weekly one.
 * - **Persistent carts carry no price.** `twarenkorbperspos` stores a quantity and a name; the value
 *   has to come from the article's current price, which is an estimate at today's price and is
 *   labelled that way.
 */
final class StatsReader
{
    /** Ranked lists (referrers, entry pages, terms) — a top-N, not a page. */
    private const TOP_N = 10;

    /** Bots are counted, then excluded from the human figures. */
    private const BOT = 'kBesucherBot > 0';

    public function __construct(private readonly DbInterface $db)
    {
    }

    /**
     * @return array<string,mixed>
     */
    public function visitors(AnalyticsQuery $query): array
    {
        $params  = ['since' => $query->sinceSql(), 'until' => $query->untilSql()];
        $totals  = $this->aggregate(
            'SELECT COUNT(*) AS visits,
                    COUNT(DISTINCT cIP) AS uniqueIps,
                    SUM(CASE WHEN ' . self::BOT . ' THEN 1 ELSE 0 END) AS bots,
                    SUM(CASE WHEN kKunde > 0 THEN 1 ELSE 0 END) AS knownCustomers,
                    SUM(CASE WHEN kBestellung > 0 THEN 1 ELSE 0 END) AS conversions,
                    COALESCE(AVG(NULLIF(nBesuchsdauer, 0)), 0) AS avgSeconds
               FROM (' . $this->visitorUnion() . ') AS v
              WHERE dZeit >= :since AND dZeit <= :until',
            $params
        );

        $visits  = (int)($totals->visits ?? 0);
        $bots    = (int)($totals->bots ?? 0);
        $human   = \max(0, $visits - $bots);
        $orders  = (int)($totals->conversions ?? 0);

        return [
            'window'         => $query->window(),
            'visits'         => $visits,
            'humanVisits'    => $human,
            'botVisits'      => $bots,
            'uniqueIps'      => (int)($totals->uniqueIps ?? 0),
            'knownCustomers' => (int)($totals->knownCustomers ?? 0),
            'conversions'    => $orders,
            'conversionRate' => $human > 0 ? \round($orders / $human * 100, 2) : 0.0,
            'avgVisitSeconds' => (int)\round((float)($totals->avgSeconds ?? 0)),
            'series'         => $this->visitorSeries($query, $params),
            'topReferrers'   => $this->visitorTop('cReferer', $params),
            'topEntryPages'  => $this->visitorTop('cEinstiegsseite', $params),
            'topBrowsers'    => $this->visitorTop('cBrowser', $params),
            'notes'          => [
                'Counts merge tbesucher (the live session table) with tbesucherarchiv (the history) '
                . 'and de-duplicate on kBesucher; the shop\'s cron moves rows between the two.',
                'conversionRate is conversions / humanVisits and only counts visits the shop itself '
                . 'linked to an order (tbesucherarchiv.kBestellung).',
            ],
        ];
    }

    /**
     * @return array<string,mixed>
     */
    public function searches(AnalyticsQuery $query): array
    {
        $params = ['since' => $query->sinceSql(), 'until' => $query->untilSql()];

        $successful = [];
        foreach ($this->db->getObjects(
            'SELECT cSuche, nAnzahlGesuche, nAnzahlTreffer, dZuletztGesucht
               FROM tsuchanfrage
              WHERE dZuletztGesucht >= :since AND dZuletztGesucht <= :until
              ORDER BY nAnzahlGesuche DESC, cSuche ASC
              LIMIT ' . $query->limit,
            $params
        ) as $row) {
            $successful[] = [
                'term'            => (string)$row->cSuche,
                'searchesAllTime' => (int)$row->nAnzahlGesuche,
                'results'         => (int)$row->nAnzahlTreffer,
                'lastSearchedAt'  => OrderReader::stamp($row->dZuletztGesucht),
            ];
        }

        $empty = [];
        foreach ($this->db->getObjects(
            'SELECT cSuche, nAnzahlGesuche, dZuletztGesucht
               FROM tsuchanfrageerfolglos
              WHERE dZuletztGesucht >= :since AND dZuletztGesucht <= :until
              ORDER BY nAnzahlGesuche DESC, cSuche ASC
              LIMIT ' . $query->limit,
            $params
        ) as $row) {
            $empty[] = [
                'term'            => (string)$row->cSuche,
                'searchesAllTime' => (int)$row->nAnzahlGesuche,
                'results'         => 0,
                'lastSearchedAt'  => OrderReader::stamp($row->dZuletztGesucht),
            ];
        }

        return [
            'window'         => $query->window(),
            'topSearches'    => $successful,
            'withoutResults' => $empty,
            'notes'          => [
                'searchesAllTime is a LIFETIME counter (tsuchanfrage.nAnzahlGesuche); the shop keeps '
                . 'no per-day search history. The window only decides which terms appear — a term '
                . 'searched once inside it still reports every search it ever had.',
                'withoutResults comes from tsuchanfrageerfolglos and is the list worth acting on: '
                . 'demand the catalogue did not answer.',
            ],
        ];
    }

    /**
     * @return array<string,mixed>
     */
    public function carts(AnalyticsQuery $query): array
    {
        $params = ['since' => $query->sinceSql(), 'until' => $query->untilSql()];

        $totals = $this->aggregate(
            'SELECT COUNT(*) AS carts, COUNT(DISTINCT kKunde) AS customers
               FROM twarenkorbpers
              WHERE dErstellt >= :since AND dErstellt <= :until',
            $params
        );

        $value = $this->aggregate(
            'SELECT COUNT(DISTINCT w.kWarenkorbPers) AS filledCarts,
                    COALESCE(SUM(p.fAnzahl), 0) AS items,
                    COALESCE(SUM(p.fAnzahl * COALESCE(a.fStandardpreisNetto, 0)), 0) AS netValue
               FROM twarenkorbpers AS w
               INNER JOIN twarenkorbperspos AS p ON p.kWarenkorbPers = w.kWarenkorbPers
               LEFT JOIN tartikel AS a ON a.kArtikel = p.kArtikel
              WHERE w.dErstellt >= :since AND w.dErstellt <= :until',
            $params
        );

        $abandoned = $this->aggregate(
            'SELECT COUNT(*) AS carts
               FROM twarenkorbpers AS w
              WHERE w.dErstellt >= :since AND w.dErstellt <= :until
                AND NOT EXISTS (
                    SELECT 1 FROM tbestellung AS b
                     WHERE b.kKunde = w.kKunde AND b.dErstellt >= w.dErstellt
                )',
            $params
        );

        $top = [];
        foreach ($this->db->getObjects(
            'SELECT p.kArtikel, MAX(p.cArtikelName) AS cArtikelName,
                    SUM(p.fAnzahl) AS quantity, COUNT(DISTINCT p.kWarenkorbPers) AS carts
               FROM twarenkorbpers AS w
               INNER JOIN twarenkorbperspos AS p ON p.kWarenkorbPers = w.kWarenkorbPers
              WHERE w.dErstellt >= :since AND w.dErstellt <= :until
              GROUP BY p.kArtikel
              ORDER BY quantity DESC
              LIMIT ' . self::TOP_N,
            $params
        ) as $row) {
            $top[] = [
                'productId' => (int)$row->kArtikel,
                'name'      => (string)$row->cArtikelName,
                'quantity'  => OrderReader::money($row->quantity),
                'carts'     => (int)$row->carts,
            ];
        }

        $carts       = (int)($totals->carts ?? 0);
        $filled      = (int)($value->filledCarts ?? 0);
        $abandonedNo = (int)($abandoned->carts ?? 0);

        return [
            'window'          => $query->window(),
            'carts'           => $carts,
            'cartsWithItems'  => $filled,
            'emptyCarts'      => \max(0, $carts - $filled),
            'customers'       => (int)($totals->customers ?? 0),
            'items'           => OrderReader::money($value->items ?? 0),
            'estimatedNetValue' => OrderReader::money($value->netValue ?? 0),
            'abandonedCarts'  => $abandonedNo,
            'abandonRate'     => $carts > 0 ? \round($abandonedNo / $carts * 100, 2) : 0.0,
            'topCartProducts' => $top,
            'notes'           => [
                'twarenkorbpers is the SAVED cart of a logged-in customer, not every session cart — '
                . 'guest carts live only in the session and are not counted anywhere.',
                'estimatedNetValue prices the cart at the article\'s CURRENT tartikel.fStandardpreisNetto; '
                . 'the cart rows themselves store no price, so this is an estimate at today\'s price.',
                'abandonedCarts counts saved carts whose customer placed no order at all afterwards; '
                . 'the cart contents are not compared with the order.',
            ],
        ];
    }

    /**
     * An aggregate that must always produce exactly one row — see
     * {@see OrderReader::aggregate()} for why a null is an error and not a zero.
     *
     * @param array<string,mixed> $params
     */
    private function aggregate(string $sql, array $params): stdClass
    {
        $row = $this->db->getSingleObject($sql, $params);
        if ($row === null) {
            throw new RuntimeException(
                'The shop database rejected an analytics aggregate; the shop log (read_logs, '
                . 'source "system") holds the SQL error. No figures were produced.'
            );
        }

        return $row;
    }

    /**
     * `tbesucher` and `tbesucherarchiv` overlap while the cron is mid-flight, so the union is
     * de-duplicated on the visitor id rather than simply added up.
     */
    private function visitorUnion(): string
    {
        return 'SELECT kBesucher, cIP, kKunde, kBestellung, cReferer, cEinstiegsseite, cBrowser,
                       nBesuchsdauer, kBesucherBot, dZeit
                  FROM tbesucherarchiv
                 UNION
                SELECT kBesucher, cIP, kKunde, kBestellung, cReferer, cEinstiegsseite,
                       cBrowser, 0 AS nBesuchsdauer, kBesucherBot, dZeit
                  FROM tbesucher';
    }

    /**
     * @param array<string,mixed> $params
     *
     * @return list<array{bucket:string,visits:int,conversions:int}>
     */
    private function visitorSeries(AnalyticsQuery $query, array $params): array
    {
        $days   = (int)\ceil(($query->until->getTimestamp() - $query->since->getTimestamp()) / 86400);
        $format = $days <= 2 ? '%Y-%m-%dT%H:00' : ($days <= 62 ? '%Y-%m-%d' : '%Y-%m');

        $out = [];
        foreach ($this->db->getObjects(
            'SELECT DATE_FORMAT(dZeit, :fmt) AS bucket,
                    COUNT(*) AS visits,
                    SUM(CASE WHEN kBestellung > 0 THEN 1 ELSE 0 END) AS conversions
               FROM (' . $this->visitorUnion() . ') AS v
              WHERE dZeit >= :since AND dZeit <= :until AND NOT (' . self::BOT . ')
              GROUP BY bucket
              ORDER BY bucket ASC
              LIMIT 62',
            $params + ['fmt' => $format]
        ) as $row) {
            $out[] = [
                'bucket'      => (string)$row->bucket,
                'visits'      => (int)$row->visits,
                'conversions' => (int)$row->conversions,
            ];
        }

        return $out;
    }

    /**
     * @param array<string,mixed> $params
     *
     * @return list<array{value:string,visits:int}>
     */
    private function visitorTop(string $column, array $params): array
    {
        $out = [];
        foreach ($this->db->getObjects(
            'SELECT ' . $column . ' AS value, COUNT(*) AS visits
               FROM (' . $this->visitorUnion() . ') AS v
              WHERE dZeit >= :since AND dZeit <= :until
                AND NOT (' . self::BOT . ')
                AND ' . $column . " IS NOT NULL AND " . $column . " <> ''
              GROUP BY value
              ORDER BY visits DESC
              LIMIT " . self::TOP_N,
            $params
        ) as $row) {
            $out[] = [
                'value'  => (string)$row->value,
                'visits' => (int)$row->visits,
            ];
        }

        return $out;
    }
}
