<?php

declare(strict_types=1);

namespace Plugin\tnt_mcp\src\Mcp\Analytics;

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

/**
 * Every order- and revenue-shaped query the analytics tools ask, in one place.
 *
 * Three rules run through the class, and they are the reason these are tools rather than documented
 * `run_sql_read` snippets:
 *
 * **Nothing the caller typed reaches SQL as text.** Column and table names are literals in this
 * file; every value is a bound parameter, `LIMIT` included (clamped to an int by
 * {@see AnalyticsQuery} and then written as a literal, because MySQL will not take a placeholder in
 * `LIMIT` under emulated prepares — the same concession {@see \Plugin\tnt_mcp\src\Mcp\Log\LogReader}
 * makes).
 *
 * **Aggregate over the window, enumerate over the page.** `total`, `revenue` and `byStatus`
 * deliberately ignore the cursor, so paging never moves the numbers the model already read.
 *
 * **Money is named for what it is.** `tbestellung.fGesamtsumme` is gross, in the shop's default
 * currency (`fWaehrungsFaktor` is the display conversion, applied *away* from this number), while
 * `twarenkorbpos.fPreis` is net per unit. Line-item revenue is therefore `netRevenue` and order
 * revenue is `grossRevenue`; a single ambiguous `revenue` key would be the one field an LLM would
 * confidently misreport to a shop owner.
 *
 * On the shop's primary connection on purpose, for the same reason {@see
 * \Plugin\tnt_mcp\src\Mcp\Catalog\SchemaReader} is: fixed, parameter-bound queries with no
 * caller-supplied SQL, so the read-only user and the SQL guard would buy nothing.
 */
final class OrderReader
{
    /** `nPosTyp = 1` — the shop's `C_WARENKORBPOS_TYP_ARTIKEL`. Shipping and coupon rows are not products. */
    private const POS_TYPE_ARTICLE = 1;

    /** A time series longer than this is a wall of numbers, so the bucket widens instead. */
    private const MAX_BUCKETS = 62;

    /** Positions of one order. Beyond this the caller wants a report, not an order. */
    private const MAX_POSITIONS = 200;

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

    /**
     * One page of orders plus window-wide totals.
     *
     * @return array<string,mixed>
     */
    public function orders(AnalyticsQuery $query): array
    {
        [$where, $params] = $this->filter($query);

        $totals = $this->aggregate(
            'SELECT COUNT(*) AS total,
                    COALESCE(SUM(b.fGesamtsumme), 0) AS gross,
                    COALESCE(AVG(b.fGesamtsumme), 0) AS avgValue
               FROM tbestellung AS b
               LEFT JOIN tkunde AS k ON k.kKunde = b.kKunde
              WHERE ' . $where,
            $params
        );
        $total = (int)$totals->total;

        $rows = $this->db->getObjects(
            'SELECT b.kBestellung, b.cBestellNr, b.cStatus, b.fGesamtsumme, b.dErstellt,
                    b.dBezahltDatum, b.dVersandDatum, b.cZahlungsartName, b.cVersandartName,
                    b.kKunde, k.cVorname, k.cNachname, k.cFirma, k.cMail, k.cLand
               FROM tbestellung AS b
               LEFT JOIN tkunde AS k ON k.kKunde = b.kKunde
              WHERE ' . $where . '
              ORDER BY b.dErstellt DESC, b.kBestellung DESC
              LIMIT ' . $query->offset . ', ' . $query->limit,
            $params
        );

        $orders = [];
        foreach ($rows as $row) {
            $orders[] = [
                'orderId'      => (int)$row->kBestellung,
                'orderNumber'  => (string)$row->cBestellNr,
                'status'       => OrderStatus::name($row->cStatus),
                'grossTotal'   => self::money($row->fGesamtsumme),
                'createdAt'    => self::stamp($row->dErstellt),
                'paidAt'       => self::stamp($row->dBezahltDatum),
                'shippedAt'    => self::stamp($row->dVersandDatum),
                'paymentName'  => (string)$row->cZahlungsartName,
                'shippingName' => (string)$row->cVersandartName,
                'customer'     => self::customer($row),
            ];
        }

        $result = [
            'window'        => $query->window(),
            'total'         => $total,
            'grossRevenue'  => self::money($totals->gross ?? 0),
            'averageOrder'  => self::money($totals->avgValue ?? 0),
            'currency'      => $this->defaultCurrency(),
            'byStatus'      => $this->byStatus($where, $params),
            'returned'      => \count($orders),
            'offset'        => $query->offset,
            'orders'        => $orders,
        ];
        if ($query->offset + \count($orders) < $total) {
            $result['nextOffset'] = $query->offset + \count($orders);
        }

        return $result;
    }

    /**
     * One order in full: header, addresses, positions.
     *
     * @return array<string,mixed>|null null when no such order exists — the tool turns that into a
     *                                  caller-facing message rather than an empty shell
     */
    public function order(int|string $idOrNumber): ?array
    {
        $row = \is_int($idOrNumber) || \ctype_digit((string)$idOrNumber)
            ? $this->db->getSingleObject(
                'SELECT * FROM tbestellung WHERE kBestellung = :id',
                ['id' => (int)$idOrNumber]
            )
            : $this->db->getSingleObject(
                'SELECT * FROM tbestellung WHERE cBestellNr = :nr',
                ['nr' => (string)$idOrNumber]
            );
        if ($row === null) {
            return null;
        }

        $orderId   = (int)$row->kBestellung;
        $positions = $this->positions((int)$row->kWarenkorb);

        return [
            'orderId'        => $orderId,
            'orderNumber'    => (string)$row->cBestellNr,
            'status'         => OrderStatus::name($row->cStatus),
            'grossTotal'     => self::money($row->fGesamtsumme),
            'credit'         => self::money($row->fGuthaben),
            'currency'       => $this->orderCurrency($row),
            'createdAt'      => self::stamp($row->dErstellt),
            'paidAt'         => self::stamp($row->dBezahltDatum),
            'shippedAt'      => self::stamp($row->dVersandDatum),
            'paymentName'    => (string)$row->cZahlungsartName,
            'shippingName'   => (string)$row->cVersandartName,
            'shippingInfo'   => self::text($row->cVersandInfo ?? null),
            'tracking'       => self::text($row->cTracking ?? null),
            'trackingUrl'    => self::text($row->cTrackingURL ?? null),
            'carrier'        => self::text($row->cLogistiker ?? null),
            'comment'        => self::text($row->cKommentar ?? null),
            'anonymised'     => (int)($row->nAnonymisiert ?? 0) > 0,
            'customer'       => $this->customerOf((int)$row->kKunde),
            'billingAddress' => $this->address('trechnungsadresse', 'kRechnungsadresse', (int)$row->kRechnungsadresse),
            'deliveryAddress' => $this->address('tlieferadresse', 'kLieferadresse', (int)$row->kLieferadresse),
            'positions'      => $positions['rows'],
            'positionCount'  => $positions['count'],
            'notes'          => $positions['notes'],
        ];
    }

    /**
     * Revenue over the window plus a time series, bucketed so the series never runs past
     * {@see MAX_BUCKETS} rows.
     *
     * @return array<string,mixed>
     */
    public function salesSummary(AnalyticsQuery $query): array
    {
        [$where, $params] = $this->filter($query);
        $bucket           = $this->bucket($query);

        $totals = $this->aggregate(
            'SELECT COUNT(*) AS orders,
                    COUNT(DISTINCT b.kKunde) AS customers,
                    COALESCE(SUM(b.fGesamtsumme), 0) AS gross,
                    COALESCE(AVG(b.fGesamtsumme), 0) AS avgValue,
                    COALESCE(MAX(b.fGesamtsumme), 0) AS largestValue
               FROM tbestellung AS b
               LEFT JOIN tkunde AS k ON k.kKunde = b.kKunde
              WHERE ' . $where,
            $params
        );

        $series = [];
        foreach ($this->db->getObjects(
            'SELECT DATE_FORMAT(b.dErstellt, :fmt) AS bucket,
                    COUNT(*) AS orders,
                    COALESCE(SUM(b.fGesamtsumme), 0) AS gross
               FROM tbestellung AS b
               LEFT JOIN tkunde AS k ON k.kKunde = b.kKunde
              WHERE ' . $where . '
              GROUP BY bucket
              ORDER BY bucket ASC
              LIMIT ' . self::MAX_BUCKETS,
            $params + ['fmt' => $bucket['format']]
        ) as $row) {
            $series[] = [
                'bucket'       => (string)$row->bucket,
                'orders'       => (int)$row->orders,
                'grossRevenue' => self::money($row->gross),
            ];
        }

        $orders = (int)($totals->orders ?? 0);

        return [
            'window'          => $query->window(),
            'granularity'     => $bucket['name'],
            'orders'          => $orders,
            'customers'       => (int)($totals->customers ?? 0),
            'grossRevenue'    => self::money($totals->gross ?? 0),
            'averageOrder'    => self::money($totals->avgValue ?? 0),
            // Alias `largestValue`, never `maxValue`: MariaDB reserves MAXVALUE (table
            // partitioning), so `MAX(...) AS maxValue` is a 1064 syntax error — and NiceDB swallows
            // it into a null result rather than an exception, so the whole summary silently reported
            // zero orders. Found by the live smoke run, not by any unit test.
            'largestOrder'    => self::money($totals->largestValue ?? 0),
            'currency'        => $this->defaultCurrency(),
            'byStatus'        => $this->byStatus($where, $params),
            'byPaymentMethod' => $this->groupBy('b.cZahlungsartName', $where, $params),
            'series'          => $series,
            'notes'           => [
                'grossRevenue is the sum of tbestellung.fGesamtsumme in the shop default currency, '
                . 'gross of tax and shipping, and includes cancelled orders unless you filter status.',
            ],
        ];
    }

    /**
     * Best-selling products over the window, ranked by net line revenue.
     *
     * @return array<string,mixed>
     */
    public function topProducts(AnalyticsQuery $query, string $rankBy): array
    {
        [$where, $params] = $this->filter($query);
        $order            = $rankBy === 'quantity' ? 'quantity DESC, netRevenue DESC' : 'netRevenue DESC, quantity DESC';

        $rows = $this->db->getObjects(
            'SELECT p.kArtikel,
                    MAX(p.cArtNr) AS cArtNr,
                    MAX(p.cName) AS cName,
                    SUM(p.nAnzahl) AS quantity,
                    SUM(p.nAnzahl * p.fPreis) AS netRevenue,
                    COUNT(DISTINCT b.kBestellung) AS orders
               FROM tbestellung AS b
               INNER JOIN twarenkorbpos AS p ON p.kWarenkorb = b.kWarenkorb
               LEFT JOIN tkunde AS k ON k.kKunde = b.kKunde
              WHERE ' . $where . ' AND p.nPosTyp = :posTyp
              GROUP BY p.kArtikel
              ORDER BY ' . $order . '
              LIMIT ' . $query->limit,
            $params + ['posTyp' => self::POS_TYPE_ARTICLE]
        );

        $products = [];
        foreach ($rows as $row) {
            $products[] = [
                'productId'   => (int)$row->kArtikel,
                'articleNo'   => (string)$row->cArtNr,
                'name'        => (string)$row->cName,
                'quantity'    => self::money($row->quantity),
                'netRevenue'  => self::money($row->netRevenue),
                'orders'      => (int)$row->orders,
            ];
        }

        return [
            'window'   => $query->window(),
            'rankedBy' => $rankBy,
            'currency' => $this->defaultCurrency(),
            'products' => $products,
            'notes'    => [
                'netRevenue is quantity x twarenkorbpos.fPreis — net of tax, and not comparable with '
                . 'the grossRevenue of sales_summary. productId is 0 for free-text positions that '
                . 'never referenced an article.',
            ],
        ];
    }

    /**
     * An aggregate that must always produce exactly one row.
     *
     * {@see \JTL\DB\NiceDB::getSingleObject()} returns null both for "no rows" and for "the
     * statement failed" — it catches the PDOException, logs it and hands back null. For a COUNT/SUM
     * query the first case cannot happen, so a null here is always a broken statement, and letting
     * it fall through to `?? 0` would report a shop with orders as a shop with none. Ask any
     * merchant which they would rather see: an error, or a confident 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;
    }

    /**
     * @param array<string,mixed> $params
     *
     * @return list<array{status:string,orders:int,grossRevenue:float}>
     */
    private function byStatus(string $where, array $params): array
    {
        $out = [];
        foreach ($this->db->getObjects(
            'SELECT b.cStatus, COUNT(*) AS orders, COALESCE(SUM(b.fGesamtsumme), 0) AS gross
               FROM tbestellung AS b
               LEFT JOIN tkunde AS k ON k.kKunde = b.kKunde
              WHERE ' . $where . '
              GROUP BY b.cStatus
              ORDER BY orders DESC',
            $params
        ) as $row) {
            $out[] = [
                'status'       => OrderStatus::name($row->cStatus),
                'orders'       => (int)$row->orders,
                'grossRevenue' => self::money($row->gross),
            ];
        }

        return $out;
    }

    /**
     * @param array<string,mixed> $params
     *
     * @return list<array{value:string,orders:int,grossRevenue:float}>
     */
    private function groupBy(string $column, string $where, array $params): array
    {
        $out = [];
        foreach ($this->db->getObjects(
            'SELECT ' . $column . ' AS value, COUNT(*) AS orders, COALESCE(SUM(b.fGesamtsumme), 0) AS gross
               FROM tbestellung AS b
               LEFT JOIN tkunde AS k ON k.kKunde = b.kKunde
              WHERE ' . $where . '
              GROUP BY value
              ORDER BY orders DESC
              LIMIT 10',
            $params
        ) as $row) {
            $out[] = [
                'value'        => (string)($row->value ?? ''),
                'orders'       => (int)$row->orders,
                'grossRevenue' => self::money($row->gross),
            ];
        }

        return $out;
    }

    /**
     * @return array{rows:list<array<string,mixed>>,count:int,notes:list<string>}
     */
    private function positions(int $cartId): array
    {
        if ($cartId <= 0) {
            return ['rows' => [], 'count' => 0, 'notes' => ['This order has no cart row; positions are unavailable.']];
        }
        $count = (int)($this->db->getSingleObject(
            'SELECT COUNT(*) AS c FROM twarenkorbpos WHERE kWarenkorb = :id',
            ['id' => $cartId]
        )->c ?? 0);

        $rows = [];
        foreach ($this->db->getObjects(
            'SELECT kWarenkorbPos, kArtikel, cName, cArtNr, nAnzahl, fPreis, fMwSt, nPosTyp, cHinweis
               FROM twarenkorbpos
              WHERE kWarenkorb = :id
              ORDER BY kWarenkorbPos ASC
              LIMIT ' . self::MAX_POSITIONS,
            ['id' => $cartId]
        ) as $row) {
            $rows[] = [
                'positionId'   => (int)$row->kWarenkorbPos,
                'productId'    => (int)$row->kArtikel,
                'articleNo'    => (string)$row->cArtNr,
                'name'         => (string)$row->cName,
                'quantity'     => self::money($row->nAnzahl),
                'unitNetPrice' => self::money($row->fPreis),
                'vatPercent'   => self::money($row->fMwSt),
                'lineNetTotal' => self::money((float)$row->nAnzahl * (float)$row->fPreis),
                'type'         => (int)$row->nPosTyp === self::POS_TYPE_ARTICLE ? 'article' : 'other',
                'note'         => self::text($row->cHinweis ?? null),
            ];
        }

        $notes = ['Position prices are net per unit (twarenkorbpos.fPreis); grossTotal is gross.'];
        if ($count > self::MAX_POSITIONS) {
            $notes[] = \sprintf(
                'This order has %d positions; only the first %d are listed.',
                $count,
                self::MAX_POSITIONS
            );
        }

        return ['rows' => $rows, 'count' => $count, 'notes' => $notes];
    }

    /** @return array<string,mixed>|null */
    private function customerOf(int $customerId): ?array
    {
        if ($customerId <= 0) {
            return null; // guest order — the addresses carry the identity instead
        }
        $row = $this->db->getSingleObject(
            'SELECT kKunde, cKundenNr, cVorname, cNachname, cFirma, cMail, cLand, cPLZ, cOrt,
                    kKundengruppe, dErstellt
               FROM tkunde WHERE kKunde = :id',
            ['id' => $customerId]
        );
        if ($row === null) {
            return null;
        }

        return [
            'customerId'    => (int)$row->kKunde,
            'customerNo'    => (string)$row->cKundenNr,
            'name'          => \trim($row->cVorname . ' ' . $row->cNachname),
            'company'       => self::text($row->cFirma),
            'email'         => self::text($row->cMail),
            'country'       => self::text($row->cLand),
            'postcode'      => self::text($row->cPLZ),
            'city'          => self::text($row->cOrt),
            'customerGroup' => (int)$row->kKundengruppe,
            'registeredAt'  => self::stamp($row->dErstellt),
        ];
    }

    /** @return array<string,mixed>|null */
    private function address(string $table, string $key, int $id): ?array
    {
        if ($id <= 0) {
            return null;
        }
        $row = $this->db->getSingleObject(
            'SELECT cAnrede, cVorname, cNachname, cFirma, cStrasse, cHausnummer, cAdressZusatz,
                    cPLZ, cOrt, cBundesland, cLand, cMail, cTel
               FROM ' . $table . ' WHERE ' . $key . ' = :id',
            ['id' => $id]
        );
        if ($row === null) {
            return null;
        }

        return [
            'salutation' => self::text($row->cAnrede),
            'name'       => \trim($row->cVorname . ' ' . $row->cNachname),
            'company'    => self::text($row->cFirma),
            'street'     => \trim($row->cStrasse . ' ' . $row->cHausnummer),
            'extra'      => self::text($row->cAdressZusatz),
            'postcode'   => self::text($row->cPLZ),
            'city'       => self::text($row->cOrt),
            'state'      => self::text($row->cBundesland),
            'country'    => self::text($row->cLand),
            'email'      => self::text($row->cMail),
            'phone'      => self::text($row->cTel),
        ];
    }

    /**
     * The WHERE clause every order query shares, plus its bound parameters.
     *
     * @return array{0:string,1:array<string,mixed>}
     */
    private function filter(AnalyticsQuery $query): array
    {
        $where  = ['b.dErstellt >= :since', 'b.dErstellt <= :until'];
        $params = ['since' => $query->sinceSql(), 'until' => $query->untilSql()];

        if ($query->status !== []) {
            $placeholders = [];
            foreach ($query->status as $i => $code) {
                $placeholders[]      = ':st' . $i;
                $params['st' . $i]   = (string)$code; // cStatus is char(2), not an int column
            }
            $where[] = 'b.cStatus IN (' . \implode(', ', $placeholders) . ')';
        }
        if ($query->customerId !== null) {
            $where[]              = 'b.kKunde = :customer';
            $params['customer']   = $query->customerId;
        }
        if ($query->search !== null) {
            // Literal text: the caller's "%" and "_" are escaped, so a search for "50%" is a search
            // for "50%" and not for everything.
            $where[]          = '(b.cBestellNr LIKE :q OR k.cMail LIKE :q OR k.cFirma LIKE :q '
                . "OR CONCAT_WS(' ', k.cVorname, k.cNachname) LIKE :q)";
            $params['q']      = '%' . self::escapeLike($query->search) . '%';
        }

        return [\implode(' AND ', $where), $params];
    }

    /** @return array{name:string,format:string} */
    private function bucket(AnalyticsQuery $query): array
    {
        $days = (int)\ceil(($query->until->getTimestamp() - $query->since->getTimestamp()) / 86400);
        if ($days <= 2) {
            return ['name' => 'hour', 'format' => '%Y-%m-%dT%H:00'];
        }
        if ($days <= self::MAX_BUCKETS) {
            return ['name' => 'day', 'format' => '%Y-%m-%d'];
        }
        if ($days <= self::MAX_BUCKETS * 7) {
            return ['name' => 'week', 'format' => '%x-W%v'];
        }

        return ['name' => 'month', 'format' => '%Y-%m'];
    }

    private function defaultCurrency(): string
    {
        $row = $this->db->getSingleObject("SELECT cISO FROM twaehrung WHERE cStandard = 'Y' LIMIT 1");

        return (string)($row->cISO ?? '');
    }

    private function orderCurrency(object $row): string
    {
        $currency = $this->db->getSingleObject(
            'SELECT cISO FROM twaehrung WHERE kWaehrung = :id',
            ['id' => (int)($row->kWaehrung ?? 0)]
        );

        return (string)($currency->cISO ?? $this->defaultCurrency());
    }

    /** @return array<string,mixed>|null null for a guest order — the addresses carry the identity */
    private static function customer(object $row): ?array
    {
        if ((int)($row->kKunde ?? 0) <= 0) {
            return null;
        }

        return [
            'customerId' => (int)($row->kKunde ?? 0),
            'name'       => \trim(($row->cVorname ?? '') . ' ' . ($row->cNachname ?? '')),
            'company'    => self::text($row->cFirma ?? null),
            'email'      => self::text($row->cMail ?? null),
            'country'    => self::text($row->cLand ?? null),
        ];
    }

    /** Two decimals, so a double's 19.989999999999998 does not become a shop owner's revenue figure. */
    public static function money(mixed $value): float
    {
        return \round((float)$value, 2);
    }

    public static function stamp(mixed $value): ?string
    {
        if (!\is_string($value) || \trim($value) === '' || \str_starts_with($value, '0000')) {
            return null;
        }

        return \str_replace(' ', 'T', $value);
    }

    public static function text(mixed $value): ?string
    {
        if (!\is_string($value)) {
            return null;
        }
        $value = \trim($value);

        return $value === '' ? null : $value;
    }

    public static function escapeLike(string $value): string
    {
        return \str_replace(['\\', '%', '_'], ['\\\\', '\%', '\_'], $value);
    }
}
