<?php

declare(strict_types=1);

namespace Plugin\tnt_mcp\src\Mcp\Commerce;

use JTL\DB\DbInterface;
use stdClass;

/**
 * The shipping-method domain object, assembled from the six tables it actually lives in.
 *
 * A shipping method is never one row. `tversandart` carries the base configuration, and four
 * dependent tables carry the parts a caller reliably forgets: `tversandartsprache` (per-language
 * name, delivery time, notes), `tversandartstaffel` (the weight/cart-value/item-count tiers),
 * `tversandartzahlungsart` (which payment methods it is offered with, and their surcharge) and
 * `tversandzuschlag` + `tversandzuschlagplz` (per-country ZIP surcharges and their **ZIP-code
 * increments** — single codes and from/to ranges side by side). A `run_sql_read` of `tversandart`
 * alone silently reports a method as having no tiers, no ZIP surcharges and no payment
 * restrictions, which is why this reader exists at all.
 *
 * Three columns are packed strings, and each is decoded here rather than handed on raw, since
 * every one of them has a different separator and a different "means everything" sentinel:
 *
 * | column | separator | "all" |
 * |---|---|---|
 * | `cLaender` | space | — (a method always names its countries) |
 * | `cKundengruppen` | `;` | `-1` |
 * | `cVersandklassen` | space, each entry itself `-`-joined | `-1` |
 *
 * (`Shipping/DomainObjects/ShippingDTO.php:287-290` writes them,
 * `Shipping/Repositories/DatabaseRepository.php:152-157,245-248` reads them back.)
 *
 * On the shop's primary connection on purpose, like {@see
 * \Plugin\tnt_mcp\src\Mcp\Analytics\OrderReader}: fixed statements with bound parameters and no
 * caller-supplied SQL, so the read-only connection would buy nothing.
 */
final class ShippingMethodReader
{
    /** `cKundengruppen` / `cVersandklassen` sentinel for "no restriction". */
    private const ALL = '-1';

    /** A method with more tiers than this is a data problem, not a configuration. */
    private const MAX_TIERS = 200;

    /** ZIP rows per surcharge. The admin UI pages at far less than this. */
    private const MAX_ZIP_ROWS = 500;

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

    /**
     * Every shipping method, one compact row each — enough to pick one, not enough to configure it.
     *
     * @return array<string,mixed>
     */
    public function methods(): array
    {
        $rows = $this->db->getObjects(
            'SELECT va.kVersandart, va.cName, va.kVersandberechnung, vb.cName AS calculationName,
                    vb.cModulId AS calculationModule, va.cLaender, va.cKundengruppen,
                    va.cVersandklassen, va.cAnzeigen, va.eSteuer, va.nSort, va.fPreis,
                    va.fVersandkostenfreiAbX, va.fDeckelung, va.minValidValue,
                    va.nMinLiefertage, va.nMaxLiefertage, va.cNurAbhaengigeVersandart,
                    (SELECT COUNT(*) FROM tversandartstaffel AS s
                      WHERE s.kVersandart = va.kVersandart) AS tiers,
                    (SELECT COUNT(*) FROM tversandzuschlag AS z
                      WHERE z.kVersandart = va.kVersandart) AS surcharges,
                    (SELECT COUNT(*) FROM tversandartzahlungsart AS p
                      WHERE p.kVersandart = va.kVersandart) AS paymentMethods
               FROM tversandart AS va
               LEFT JOIN tversandberechnung AS vb ON vb.kVersandberechnung = va.kVersandberechnung
              ORDER BY va.nSort, va.kVersandart'
        );

        $methods = [];
        foreach ($rows as $row) {
            $methods[] = [
                'shippingMethodId' => (int)$row->kVersandart,
                'name'             => (string)$row->cName,
                'calculation'      => $this->calculation($row),
                'countries'        => self::countries($row->cLaender),
                'customerGroupIds' => self::customerGroups($row->cKundengruppen),
                'shippingClasses'  => self::shippingClasses($row->cVersandklassen),
                'sort'             => (int)$row->nSort,
                'tiers'            => (int)$row->tiers,
                'zipSurcharges'    => (int)$row->surcharges,
                'paymentMethods'   => (int)$row->paymentMethods,
            ];
        }

        return [
            'total'   => \count($methods),
            'methods' => $methods,
            // Countable proof that the list is the whole picture and a method is not.
            'note'    => 'Counts only. Call get_shipping_method for tiers, ZIP increments, '
                . 'per-language texts and payment surcharges.',
        ];
    }

    /**
     * One shipping method in full, or null when nothing matches.
     *
     * @param int|string $idOrName internal id, or the exact `tversandart.cName`
     *
     * @return array<string,mixed>|null
     */
    public function method(int|string $idOrName): ?array
    {
        $base = \is_int($idOrName)
            ? $this->db->getSingleObject(
                'SELECT va.*, vb.cName AS calculationName, vb.cModulId AS calculationModule
                   FROM tversandart AS va
                   LEFT JOIN tversandberechnung AS vb
                          ON vb.kVersandberechnung = va.kVersandberechnung
                  WHERE va.kVersandart = :id',
                ['id' => $idOrName]
            )
            : $this->db->getSingleObject(
                'SELECT va.*, vb.cName AS calculationName, vb.cModulId AS calculationModule
                   FROM tversandart AS va
                   LEFT JOIN tversandberechnung AS vb
                          ON vb.kVersandberechnung = va.kVersandberechnung
                  WHERE va.cName = :name
                  ORDER BY va.kVersandart
                  LIMIT 1',
                ['name' => $idOrName]
            );
        if ($base === null) {
            return null;
        }
        $id = (int)$base->kVersandart;

        return [
            'shippingMethodId' => $id,
            'name'             => (string)$base->cName,
            'calculation'      => $this->calculation($base),
            'countries'        => $this->countryDetails($base->cLaender),
            'customerGroups'   => $this->customerGroupDetails($base->cKundengruppen),
            'shippingClasses'  => $this->shippingClassDetails($base->cVersandklassen),
            'display'          => [
                'mode'             => (string)($base->cAnzeigen ?? ''),
                'always'           => ($base->cAnzeigen ?? '') === 'immer',
                'sort'             => (int)$base->nSort,
                'image'            => (string)($base->cBild ?? ''),
                'dependentOnly'    => ($base->cNurAbhaengigeVersandart ?? 'N') === 'Y',
                'confirmationMail' => ($base->cSendConfirmationMail ?? 'N') === 'Y',
                'ignoreProposal'   => ($base->cIgnoreShippingProposal ?? 'N') === 'Y',
            ],
            'pricing'          => [
                'flatPrice'      => self::money($base->fPreis),
                'freeFrom'       => self::money($base->fVersandkostenfreiAbX),
                'cap'            => self::money($base->fDeckelung),
                'minOrderValue'  => self::money($base->minValidValue),
                'tiers'          => $this->tiers($id),
            ],
            'delivery'         => [
                'minDays' => (int)$base->nMinLiefertage,
                'maxDays' => (int)$base->nMaxLiefertage,
            ],
            'tax'              => $this->tax($base),
            'paymentMethods'   => $this->paymentMethods($id),
            'zipSurcharges'    => $this->surcharges($id),
            'languages'        => $this->languages($id),
        ];
    }

    /**
     * The price scale. Only calculations 2/3/4 (weight, cart value, item count) have one; for a
     * flat rate an empty list is the correct answer, not a missing one — `createOrUpdate()` never
     * builds scales for `kVersandberechnung = 1`
     * (`Router/Controller/Backend/ShippingMethodsController.php:816-846`).
     *
     * @return list<array<string,mixed>>
     */
    private function tiers(int $id): array
    {
        $rows  = $this->db->getObjects(
            'SELECT kVersandartStaffel, fBis, fPreis
               FROM tversandartstaffel
              WHERE kVersandart = :id
              ORDER BY fBis
              LIMIT ' . self::MAX_TIERS,
            ['id' => $id]
        );
        $tiers = [];
        foreach ($rows as $row) {
            $tiers[] = [
                'tierId' => (int)$row->kVersandartStaffel,
                'upTo'   => self::money($row->fBis),
                'price'  => self::money($row->fPreis),
            ];
        }

        return $tiers;
    }

    /**
     * The payment methods this shipping method is offered with, plus their surcharge.
     *
     * `cAufpreisTyp` decides how `fAufpreis` is meant: `prozent` is a percentage of the order,
     * anything else is an absolute amount. Reported as both the raw value and a resolved
     * `surchargeType` so a caller cannot read 3.5 as euros when it is percent.
     *
     * @return list<array<string,mixed>>
     */
    private function paymentMethods(int $id): array
    {
        $rows = $this->db->getObjects(
            'SELECT vz.kZahlungsart, vz.fAufpreis, vz.cAufpreisTyp,
                    za.cName, za.cModulId, za.nActive, za.nNutzbar
               FROM tversandartzahlungsart AS vz
               LEFT JOIN tzahlungsart AS za ON za.kZahlungsart = vz.kZahlungsart
              WHERE vz.kVersandart = :id
              ORDER BY za.nSort, vz.kZahlungsart',
            ['id' => $id]
        );

        $methods = [];
        foreach ($rows as $row) {
            $type      = (string)($row->cAufpreisTyp ?? '');
            $methods[] = [
                'paymentMethodId' => (int)$row->kZahlungsart,
                'name'            => (string)($row->cName ?? ''),
                'moduleId'        => (string)($row->cModulId ?? ''),
                'active'          => (int)($row->nActive ?? 0) === 1,
                'usable'          => (int)($row->nNutzbar ?? 0) === 1,
                'surcharge'       => self::money($row->fAufpreis),
                'surchargeType'   => $type === 'prozent' ? 'percent' : 'absolute',
                'surchargeTypeRaw' => $type,
            ];
        }

        return $methods;
    }

    /**
     * Per-country ZIP surcharges with their **increments**: every `tversandzuschlagplz` row is
     * either a single ZIP (`cPLZ`) or a from/to range (`cPLZAb`/`cPLZBis`), and both shapes live in
     * the same table. Reported as one `zipCodes` list of typed entries so the two are not conflated.
     *
     * @return list<array<string,mixed>>
     */
    private function surcharges(int $id): array
    {
        $rows = $this->db->getObjects(
            'SELECT z.kVersandzuschlag, z.cISO, z.cName, z.fZuschlag, l.cDeutsch, l.cEnglisch
               FROM tversandzuschlag AS z
               LEFT JOIN tland AS l ON l.cISO = z.cISO
              WHERE z.kVersandart = :id
              ORDER BY z.cISO, z.kVersandzuschlag',
            ['id' => $id]
        );

        $surcharges = [];
        foreach ($rows as $row) {
            $surchargeID  = (int)$row->kVersandzuschlag;
            $surcharges[] = [
                'surchargeId' => $surchargeID,
                'country'     => [
                    'iso'     => (string)$row->cISO,
                    'nameDe'  => (string)($row->cDeutsch ?? ''),
                    'nameEn'  => (string)($row->cEnglisch ?? ''),
                ],
                'name'        => (string)($row->cName ?? ''),
                'surcharge'   => self::money($row->fZuschlag),
                'zipCodes'    => $this->zipCodes($surchargeID),
                'languages'   => $this->surchargeLanguages($surchargeID),
            ];
        }

        return $surcharges;
    }

    /**
     * @return list<array<string,mixed>>
     */
    private function zipCodes(int $surchargeID): array
    {
        $rows = $this->db->getObjects(
            'SELECT kVersandzuschlagPlz, cPLZ, cPLZAb, cPLZBis
               FROM tversandzuschlagplz
              WHERE kVersandzuschlag = :id
              ORDER BY kVersandzuschlagPlz
              LIMIT ' . self::MAX_ZIP_ROWS,
            ['id' => $surchargeID]
        );

        $codes = [];
        foreach ($rows as $row) {
            $single = (string)($row->cPLZ ?? '');
            $from   = (string)($row->cPLZAb ?? '');
            $to     = (string)($row->cPLZBis ?? '');
            $codes[] = $single !== ''
                ? ['zipEntryId' => (int)$row->kVersandzuschlagPlz, 'type' => 'single', 'zip' => $single]
                : [
                    'zipEntryId' => (int)$row->kVersandzuschlagPlz,
                    'type'       => 'range',
                    'from'       => $from,
                    'to'         => $to,
                ];
        }

        return $codes;
    }

    /**
     * @return array<string,array<string,string>>
     */
    private function surchargeLanguages(int $surchargeID): array
    {
        $rows = $this->db->getObjects(
            'SELECT cISOSprache, cName
               FROM tversandzuschlagsprache
              WHERE kVersandzuschlag = :id',
            ['id' => $surchargeID]
        );

        $languages = [];
        foreach ($rows as $row) {
            $languages[(string)$row->cISOSprache] = ['name' => (string)($row->cName ?? '')];
        }

        return $languages;
    }

    /**
     * The four per-language fields, keyed by language code — the same `{code}` that
     * `shipping.method.save` templates its `cName_{code}` arguments on.
     *
     * @return array<string,array<string,string>>
     */
    private function languages(int $id): array
    {
        $rows = $this->db->getObjects(
            'SELECT cISOSprache, cName, cLieferdauer, cHinweistext, cHinweistextShop
               FROM tversandartsprache
              WHERE kVersandart = :id',
            ['id' => $id]
        );

        $languages = [];
        foreach ($rows as $row) {
            $languages[(string)$row->cISOSprache] = [
                'name'         => (string)($row->cName ?? ''),
                'deliveryTime' => (string)($row->cLieferdauer ?? ''),
                'note'         => (string)($row->cHinweistext ?? ''),
                'noteShop'     => (string)($row->cHinweistextShop ?? ''),
            ];
        }

        return $languages;
    }

    /**
     * Tax, honestly. A shipping method has **no tax class of its own** — `tversandart.eSteuer` only
     * says whether the configured prices are net or gross, and the rate applied at checkout comes
     * from the shop-wide `bestellvorgang_versand_steuersatz` setting (`HS` = the highest rate in the
     * cart; otherwise a `tsteuerklasse` id). Reporting a per-method tax class would be an invention.
     *
     * @return array<string,mixed>
     */
    private function tax(stdClass $base): array
    {
        $configured = $this->db->getSingleObject(
            "SELECT cWert FROM teinstellungen WHERE cName = 'bestellvorgang_versand_steuersatz'"
        );
        $value      = $configured === null ? '' : (string)$configured->cWert;
        $class      = null;
        if ($value !== '' && $value !== 'HS' && \ctype_digit($value)) {
            $row   = $this->db->getSingleObject(
                'SELECT kSteuerklasse, cName FROM tsteuerklasse WHERE kSteuerklasse = :id',
                ['id' => (int)$value]
            );
            $class = $row === null
                ? null
                : ['taxClassId' => (int)$row->kSteuerklasse, 'name' => (string)$row->cName];
        }

        return [
            'priceBasis'   => (string)($base->eSteuer ?? ''),
            'rateSource'   => $value === 'HS' ? 'highestRateInCart' : 'taxClass',
            'rateSetting'  => 'bestellvorgang_versand_steuersatz',
            'taxClass'     => $class,
            'note'         => 'A shipping method has no tax class column; the rate is shop-wide.',
        ];
    }

    /**
     * @return array<string,mixed>
     */
    private function calculation(stdClass $row): array
    {
        return [
            'id'        => (int)$row->kVersandberechnung,
            'name'      => (string)($row->calculationName ?? ''),
            'moduleId'  => (string)($row->calculationModule ?? ''),
            'usesTiers' => (int)$row->kVersandberechnung !== 1,
        ];
    }

    /**
     * @return list<array<string,mixed>>
     */
    private function countryDetails(?string $packed): array
    {
        $isos = self::countries($packed);
        if ($isos === []) {
            return [];
        }
        $rows = $this->db->getObjects(
            'SELECT cISO, cDeutsch, cEnglisch, nEU, bPermitRegistration
               FROM tland
              WHERE cISO IN (' . self::placeholders($isos) . ')',
            self::bind($isos)
        );

        $known = [];
        foreach ($rows as $row) {
            $known[(string)$row->cISO] = [
                'iso'                => (string)$row->cISO,
                'nameDe'             => (string)$row->cDeutsch,
                'nameEn'             => (string)$row->cEnglisch,
                'eu'                 => (int)$row->nEU === 1,
                // The shipping guide's side effect, visible without having to read the guide.
                'permitRegistration' => (int)$row->bPermitRegistration === 1,
            ];
        }

        $countries = [];
        foreach ($isos as $iso) {
            $countries[] = $known[$iso] ?? ['iso' => $iso, 'unknown' => true];
        }

        return $countries;
    }

    /**
     * @return array<string,mixed>
     */
    private function customerGroupDetails(?string $packed): array
    {
        $ids = self::customerGroups($packed);
        if ($ids === []) {
            return ['restricted' => false, 'groups' => []];
        }
        $rows   = $this->db->getObjects(
            'SELECT kKundengruppe, cName FROM tkundengruppe
              WHERE kKundengruppe IN (' . self::placeholders($ids) . ')',
            self::bind($ids)
        );
        $groups = [];
        foreach ($rows as $row) {
            $groups[] = ['customerGroupId' => (int)$row->kKundengruppe, 'name' => (string)$row->cName];
        }

        return ['restricted' => true, 'groups' => $groups];
    }

    /**
     * Shipping classes are stored as **combinations**: `"1 3-4"` means "class 1 alone, or classes
     * 3 and 4 together". Each combination is returned with its resolved class names rather than
     * flattened, because flattening changes what the method actually applies to.
     *
     * @return array<string,mixed>
     */
    private function shippingClassDetails(?string $packed): array
    {
        $combinations = self::shippingClasses($packed);
        if ($combinations === []) {
            return ['restricted' => false, 'combinations' => []];
        }
        $names = [];
        foreach ($this->db->getObjects('SELECT kVersandklasse, cName FROM tversandklasse') as $row) {
            $names[(int)$row->kVersandklasse] = (string)$row->cName;
        }

        $resolved = [];
        foreach ($combinations as $combination) {
            $classes = [];
            foreach ($combination as $classID) {
                $classes[] = [
                    'shippingClassId' => $classID,
                    'name'            => $names[$classID] ?? '',
                ];
            }
            $resolved[] = ['classes' => $classes];
        }

        return ['restricted' => true, 'combinations' => $resolved];
    }

    /** @return list<string> space-separated ISO codes (`ShippingDTO.php:287`) */
    private static function countries(?string $packed): array
    {
        $isos = \preg_split('/\s+/', \trim((string)$packed), -1, \PREG_SPLIT_NO_EMPTY);

        return $isos === false ? [] : $isos;
    }

    /** @return list<int> `;`-separated ids, empty when unrestricted (`ShippingDTO.php:290`) */
    private static function customerGroups(?string $packed): array
    {
        $raw = \trim((string)$packed);
        if ($raw === '' || $raw === self::ALL) {
            return [];
        }

        $ids = [];
        foreach (\preg_split('/[;,]/', $raw, -1, \PREG_SPLIT_NO_EMPTY) ?: [] as $part) {
            $part = \trim($part);
            if ($part !== '' && \ctype_digit($part)) {
                $ids[] = (int)$part;
            }
        }

        return $ids;
    }

    /**
     * @return list<list<int>> one inner list per combination, empty when unrestricted
     *                         (`ShippingMethodsController.php:221`)
     */
    private static function shippingClasses(?string $packed): array
    {
        $raw = \trim((string)$packed);
        if ($raw === '' || $raw === self::ALL) {
            return [];
        }

        $combinations = [];
        foreach (\preg_split('/\s+/', $raw, -1, \PREG_SPLIT_NO_EMPTY) ?: [] as $combination) {
            $classes = [];
            foreach (\explode('-', $combination) as $classID) {
                if (\ctype_digit($classID)) {
                    $classes[] = (int)$classID;
                }
            }
            if ($classes !== []) {
                $combinations[] = $classes;
            }
        }

        return $combinations;
    }

    /**
     * @param list<int|string> $values
     */
    private static function placeholders(array $values): string
    {
        return \implode(', ', \array_map(static fn (int $i): string => ':v' . $i, \array_keys($values)));
    }

    /**
     * @param list<int|string> $values
     *
     * @return array<string,int|string>
     */
    private static function bind(array $values): array
    {
        $params = [];
        foreach ($values as $i => $value) {
            $params['v' . $i] = $value;
        }

        return $params;
    }

    private static function money(mixed $value): float
    {
        return \round((float)$value, 2);
    }
}
