<?php

/*
 * This file is part of the VITICORE2 package.
 *
 * (c) CP Creation <web@cpcreation.fr>
 *
 * For the full copyright and license information, please view the LICENSE
 * file that was distributed with this source code.
 */

namespace CpCreation\VitiCore\Cart\Repository;

use CpCreation\VitiCore\Cart\Model\Cart;
use CpCreation\VitiCore\Cart\Model\Promo;
use CpCreation\VitiCore\Marketing\Model\AbandonedCartReminder;
use CpCreation\VitiCore\Marketing\Model\ReviewInvitation;
use CpCreation\VitiCore\Product\Model\ProductVariant;
use CpCreation\VitiCore\Repository\BaseRepository;
use CpCreation\VitiCore\User\Model\User;
use Doctrine\DBAL\ArrayParameterType;
use Doctrine\ORM\Query\Expr\Join;
use Doctrine\ORM\QueryBuilder;
use Doctrine\Persistence\ManagerRegistry;
use Symfony\Component\Security\Core\User\UserInterface;

class CartRepository extends BaseRepository
{
    private const OFFLINE_CHECKOUT_METHODS = [
        'check',
        'banktransfert',
    ];

    /**
     * @var array<string, array<int, array<string, mixed>>>
     */
    private array $stockSaleSinceStockAtCache = [];

    /**
     * @var array<string, array<int, array<string, mixed>>>
     */
    private array $stockReservedSinceStockAtCache = [];

    /**
     * CartRepository constructor.
     *
     * @throws \ReflectionException
     */
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, Cart::class);
    }

    public function findOneCartInProgressById(string $uuid, bool $checkExpired = true): ?Cart
    {
        $qb = $this->createQueryBuilder('cart')
            ->leftJoin('cart.cartCheckout', 'checkout')
            ->addSelect('checkout')
            ->leftJoin('checkout.paymentMethod', 'payment_method')
            ->addSelect('payment_method')
            ->andWhere('cart.invoice is NULL')
            ->andWhere('(
                checkout.id IS NULL
                OR (
                    checkout.disabled = FALSE
                    AND checkout.payedAt IS NULL
                    AND checkout.initiatedAt IS NULL
                    AND (
                        checkout.orderedAt IS NULL
                        OR payment_method.id IS NULL
                        OR payment_method.method NOT IN (:offlineCheckoutMethods)
                    )
                )
            )')
            ->andWhere('cart.id = :id')
            ->setMaxResults(1)
            ->setParameter('id', $uuid)
            ->setParameter('offlineCheckoutMethods', self::OFFLINE_CHECKOUT_METHODS);
        if ($checkExpired) {
            $date = new \DateTime();
            $qb->andWhere('cart.expiresAt > :date')
                ->setParameter('date', $date);
        }

        return $qb->getQuery()->getOneOrNullResult();
    }

    /**
     * @return list<Cart>
     */
    public function findEligibleForReviewInvitation(
        \DateTimeImmutable $fulfilledBefore,
        \DateTimeImmutable $fulfilledAfter,
        int $limit = 100,
    ): array {
        return $this->createQueryBuilder('cart')
            ->addSelect('checkout', 'invoiceAddress', 'deliveryAddress', 'user', 'items', 'variant', 'product')
            ->innerJoin('cart.cartCheckout', 'checkout')
            ->leftJoin('cart.invoiceAddress', 'invoiceAddress')
            ->leftJoin('cart.deliveryAddress', 'deliveryAddress')
            ->leftJoin('cart.user', 'user')
            ->innerJoin('cart.items', 'items')
            ->innerJoin('items.variant', 'variant')
            ->innerJoin('variant.product', 'product')
            ->andWhere('checkout.payedAt IS NOT NULL')
            ->andWhere('cart.delivery = TRUE')
            ->andWhere('cart.fulfilledAt IS NOT NULL')
            ->andWhere('cart.fulfilledAt <= :fulfilledBefore')
            ->andWhere('cart.fulfilledAt >= :fulfilledAfter')
            ->andWhere('COALESCE(invoiceAddress.email, deliveryAddress.email, user.email) IS NOT NULL')
            ->andWhere(\sprintf(
                'NOT EXISTS (SELECT invitation.id FROM %s invitation WHERE invitation.cart = cart)',
                ReviewInvitation::class,
            ))
            ->setParameter('fulfilledBefore', $fulfilledBefore)
            ->setParameter('fulfilledAfter', $fulfilledAfter)
            ->orderBy('cart.fulfilledAt', 'ASC')
            ->setMaxResults($limit)
            ->getQuery()
            ->getResult();
    }

    public function findLatestAbandonedCart(): ?Cart
    {
        return $this->createQueryBuilder('cart')
            ->addSelect('checkout', 'invoiceAddress', 'deliveryAddress', 'user', 'items')
            ->leftJoin('cart.cartCheckout', 'checkout')
            ->leftJoin('cart.invoiceAddress', 'invoiceAddress')
            ->leftJoin('cart.deliveryAddress', 'deliveryAddress')
            ->leftJoin('cart.user', 'user')
            ->innerJoin('cart.items', 'items')
            ->andWhere('cart.abandonedAt IS NOT NULL')
            ->andWhere('cart.invoice IS NULL')
            ->andWhere('(
                checkout.id IS NULL
                OR (
                    checkout.disabled = FALSE
                    AND checkout.payedAt IS NULL
                    AND checkout.orderedAt IS NULL
                )
            )')
            ->orderBy('cart.abandonedAt', 'DESC')
            ->setMaxResults(1)
            ->getQuery()
            ->getOneOrNullResult();
    }

    public function findLatestCartForForcedAbandonedCartReminder(bool $requireEmail = true): ?Cart
    {
        $queryBuilder = $this->createQueryBuilder('cart')
            ->addSelect('checkout', 'invoiceAddress', 'deliveryAddress', 'user', 'items', 'variant', 'product')
            ->leftJoin('cart.cartCheckout', 'checkout')
            ->leftJoin('cart.invoiceAddress', 'invoiceAddress')
            ->leftJoin('cart.deliveryAddress', 'deliveryAddress')
            ->leftJoin('cart.user', 'user')
            ->innerJoin('cart.items', 'items')
            ->innerJoin('items.variant', 'variant')
            ->innerJoin('variant.product', 'product')
            ->andWhere('cart.invoice IS NULL')
            ->andWhere('(
                checkout.id IS NULL
                OR (
                    checkout.disabled = FALSE
                    AND checkout.payedAt IS NULL
                    AND checkout.orderedAt IS NULL
                )
            )')
            ->orderBy('cart.lastActivityAt', 'DESC')
            ->addOrderBy('cart.updatedAt', 'DESC')
            ->setMaxResults(1);

        if ($requireEmail) {
            $queryBuilder->andWhere('COALESCE(invoiceAddress.email, deliveryAddress.email, user.email) IS NOT NULL');
        }

        return $queryBuilder
            ->getQuery()
            ->getOneOrNullResult();
    }

    public function findLatestFulfilledCartWithoutReviewInvitation(): ?Cart
    {
        return $this->createQueryBuilder('cart')
            ->addSelect('checkout', 'invoiceAddress', 'deliveryAddress', 'user', 'items', 'variant', 'product')
            ->innerJoin('cart.cartCheckout', 'checkout')
            ->leftJoin('cart.invoiceAddress', 'invoiceAddress')
            ->leftJoin('cart.deliveryAddress', 'deliveryAddress')
            ->leftJoin('cart.user', 'user')
            ->innerJoin('cart.items', 'items')
            ->innerJoin('items.variant', 'variant')
            ->innerJoin('variant.product', 'product')
            ->andWhere('checkout.payedAt IS NOT NULL')
            ->andWhere('cart.delivery = TRUE')
            ->andWhere('cart.fulfilledAt IS NOT NULL')
            ->andWhere('COALESCE(invoiceAddress.email, deliveryAddress.email, user.email) IS NOT NULL')
            ->andWhere(\sprintf(
                'NOT EXISTS (SELECT invitation.id FROM %s invitation WHERE invitation.cart = cart)',
                ReviewInvitation::class,
            ))
            ->orderBy('cart.fulfilledAt', 'DESC')
            ->setMaxResults(1)
            ->getQuery()
            ->getOneOrNullResult();
    }

    /**
     * @return list<array{email: string, firstname: ?string, lastname: ?string}>
     */
    public function findGuestPurchaserContacts(): array
    {
        $rows = $this->createQueryBuilder('cart')
            ->select('LOWER(COALESCE(invoiceAddress.email, deliveryAddress.email)) AS email')
            ->addSelect('MAX(COALESCE(invoiceAddress.firstname, deliveryAddress.firstname)) AS firstname')
            ->addSelect('MAX(COALESCE(invoiceAddress.lastname, deliveryAddress.lastname)) AS lastname')
            ->innerJoin('cart.cartCheckout', 'checkout')
            ->leftJoin('cart.invoiceAddress', 'invoiceAddress')
            ->leftJoin('cart.deliveryAddress', 'deliveryAddress')
            ->andWhere('cart.user IS NULL')
            ->andWhere('(checkout.payedAt IS NOT NULL OR checkout.orderedAt IS NOT NULL)')
            ->andWhere('COALESCE(invoiceAddress.email, deliveryAddress.email) IS NOT NULL')
            ->groupBy('LOWER(COALESCE(invoiceAddress.email, deliveryAddress.email))')
            ->orderBy('email', 'ASC')
            ->getQuery()
            ->getArrayResult();

        return array_values(array_filter($rows, static fn (array $row): bool => \is_string($row['email'] ?? null)));
    }

    /**
     * @return list<array{
     *     email: string,
     *     firstname: ?string,
     *     lastname: ?string,
     *     order_count: int,
     *     first_order_at: \DateTimeImmutable,
     *     last_order_at: \DateTimeImmutable,
     *     representative_cart_id: string
     * }>
     */
    public function findUnregisteredCustomerOrderSummaries(): array
    {
        /** @var list<array<string, mixed>> $rows */
        $rows = $this->getEntityManager()->getConnection()->fetchAllAssociative(<<<'SQL'
WITH guest_orders AS (
    SELECT
        CAST(cart.id AS text) AS cart_id,
        LOWER(TRIM(COALESCE(
            NULLIF(TRIM(invoice_address.email), ''),
            NULLIF(TRIM(delivery_address.email), '')
        ))) AS email,
        COALESCE(
            NULLIF(TRIM(invoice_address.firstname), ''),
            NULLIF(TRIM(delivery_address.firstname), '')
        ) AS firstname,
        COALESCE(
            NULLIF(TRIM(invoice_address.lastname), ''),
            NULLIF(TRIM(delivery_address.lastname), '')
        ) AS lastname,
        COALESCE(checkout.ordered_at, checkout.payed_at) AS order_at
    FROM cart_cart cart
    INNER JOIN cart_cart_checkout checkout ON checkout.cart_cart_id = cart.id
    LEFT JOIN cart_invoice_address invoice_address ON invoice_address.id = cart.invoice_address_id
    LEFT JOIN cart_delivery_address delivery_address ON delivery_address.id = cart.delivery_address_id
    WHERE cart.user_id IS NULL
      AND cart.deleted_at IS NULL
      AND checkout.deleted_at IS NULL
      AND checkout.disabled = FALSE
      AND (checkout.payed_at IS NOT NULL OR checkout.ordered_at IS NOT NULL)
      AND EXISTS (
          SELECT 1
          FROM cart_cart_item item
          WHERE item.cart_cart_id = cart.id
      )
), unregistered_guest_orders AS (
    SELECT guest_orders.*
    FROM guest_orders
    WHERE guest_orders.email IS NOT NULL
      AND guest_orders.email <> ''
      AND NOT EXISTS (
          SELECT 1
          FROM user_user registered_user
          WHERE LOWER(TRIM(registered_user.email)) = guest_orders.email
      )
), ranked_guest_orders AS (
    SELECT
        unregistered_guest_orders.*,
        ROW_NUMBER() OVER (
            PARTITION BY unregistered_guest_orders.email
            ORDER BY unregistered_guest_orders.order_at DESC, unregistered_guest_orders.cart_id DESC
        ) AS customer_order_rank
    FROM unregistered_guest_orders
)
SELECT
    email,
    MAX(CASE WHEN customer_order_rank = 1 THEN cart_id END) AS representative_cart_id,
    MAX(CASE WHEN customer_order_rank = 1 THEN firstname END) AS firstname,
    MAX(CASE WHEN customer_order_rank = 1 THEN lastname END) AS lastname,
    COUNT(*) AS order_count,
    MIN(order_at) AS first_order_at,
    MAX(order_at) AS last_order_at
FROM ranked_guest_orders
GROUP BY email
ORDER BY order_count DESC, last_order_at DESC, email ASC
SQL);

        $customers = [];
        foreach ($rows as $row) {
            $email = $this->normalizedGuestCustomerString($row['email'] ?? null);
            $representativeCartId = $this->normalizedGuestCustomerString($row['representative_cart_id'] ?? null);
            $firstOrderAt = $this->guestCustomerDate($row['first_order_at'] ?? null);
            $lastOrderAt = $this->guestCustomerDate($row['last_order_at'] ?? null);

            if (null === $email || null === $representativeCartId || null === $firstOrderAt || null === $lastOrderAt) {
                continue;
            }

            $customers[] = [
                'email' => $email,
                'firstname' => $this->normalizedGuestCustomerString($row['firstname'] ?? null),
                'lastname' => $this->normalizedGuestCustomerString($row['lastname'] ?? null),
                'order_count' => (int) ($row['order_count'] ?? 0),
                'first_order_at' => $firstOrderAt,
                'last_order_at' => $lastOrderAt,
                'representative_cart_id' => $representativeCartId,
            ];
        }

        return $customers;
    }

    /**
     * @return array{
     *     customer: array{
     *         email: string,
     *         firstname: ?string,
     *         lastname: ?string,
     *         order_count: int,
     *         first_order_at: \DateTimeImmutable,
     *         last_order_at: \DateTimeImmutable,
     *         representative_cart_id: string
     *     },
     *     orders: list<Cart>
     * }|null
     */
    public function findUnregisteredCustomerOrderHistory(string $representativeCartId): ?array
    {
        $customer = null;
        foreach ($this->findUnregisteredCustomerOrderSummaries() as $candidate) {
            if ($candidate['representative_cart_id'] === $representativeCartId) {
                $customer = $candidate;
                break;
            }
        }

        if (null === $customer) {
            return null;
        }

        $orders = $this->createCompletedCustomerOrdersQueryBuilder()
            ->andWhere('cart.user IS NULL')
            ->andWhere($this->normalizedOrderEmailCondition())
            ->setParameter('customerEmail', $customer['email'])
            ->getQuery()
            ->getResult();

        return [
            'customer' => $customer,
            'orders' => array_values(array_filter($orders, static fn (mixed $order): bool => $order instanceof Cart)),
        ];
    }

    /**
     * Returns every completed order associated with the account. Historical guest
     * orders with the same normalized email are also included until the existing
     * account-linking workflow persists their user relation.
     *
     * @return list<Cart>
     */
    public function findCompletedAdminOrdersByUser(User $user): array
    {
        $qb = $this->createCompletedCustomerOrdersQueryBuilder();
        $email = mb_strtolower(mb_trim((string) $user->getEmail()));

        if ('' === $email) {
            $qb->andWhere('cart.user = :customerUser');
        } else {
            $qb->andWhere(
                $qb->expr()->orX(
                    'cart.user = :customerUser',
                    $qb->expr()->andX(
                        'cart.user IS NULL',
                        $this->normalizedOrderEmailCondition(),
                    ),
                ),
            );
            $qb->setParameter('customerEmail', $email);
        }

        $orders = $qb
            ->setParameter('customerUser', $user)
            ->getQuery()
            ->getResult();

        return array_values(array_filter($orders, static fn (mixed $order): bool => $order instanceof Cart));
    }

    private function createCompletedCustomerOrdersQueryBuilder(): QueryBuilder
    {
        return $this->createQueryBuilder('cart')
            ->innerJoin('cart.cartCheckout', 'checkout', Join::WITH, 'checkout.disabled = FALSE')
            ->addSelect('checkout')
            ->leftJoin('checkout.paymentMethod', 'payment_method')
            ->addSelect('payment_method')
            ->leftJoin('cart.deliveryMethod', 'delivery_method')
            ->addSelect('delivery_method')
            ->leftJoin('cart.invoiceAddress', 'invoice_address')
            ->addSelect('invoice_address')
            ->leftJoin('cart.deliveryAddress', 'delivery_address')
            ->addSelect('delivery_address')
            ->andWhere('cart.deletedAt IS NULL')
            ->andWhere('checkout.deletedAt IS NULL')
            ->andWhere('(checkout.payedAt IS NOT NULL OR checkout.orderedAt IS NOT NULL)')
            ->andWhere('EXISTS (
                SELECT 1
                FROM CpCreation\VitiCore\Cart\Model\CartItem item
                WHERE item.cart = cart
            )')
            ->orderBy('checkout.orderedAt', 'DESC')
            ->addOrderBy('checkout.payedAt', 'DESC')
            ->addOrderBy('cart.createdAt', 'DESC');
    }

    private function normalizedOrderEmailCondition(): string
    {
        return "(
            LOWER(TRIM(invoice_address.email)) = :customerEmail
            OR (
                (invoice_address.email IS NULL OR TRIM(invoice_address.email) = '')
                AND LOWER(TRIM(delivery_address.email)) = :customerEmail
            )
        )";
    }

    private function normalizedGuestCustomerString(mixed $value): ?string
    {
        if (!\is_string($value)) {
            return null;
        }

        $value = mb_trim($value);

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

    private function guestCustomerDate(mixed $value): ?\DateTimeImmutable
    {
        if (!\is_string($value) || '' === $value) {
            return null;
        }

        try {
            return new \DateTimeImmutable($value);
        } catch (\Exception) {
            return null;
        }
    }

    /**
     * @return list<Cart>
     */
    public function findEligibleForAbandonedCartReminder(
        \DateTimeImmutable $abandonedBefore,
        \DateTimeImmutable $abandonedAfter,
        int $limit = 100,
    ): array {
        return $this->createQueryBuilder('cart')
            ->addSelect('checkout', 'invoiceAddress', 'deliveryAddress', 'user', 'items', 'variant', 'product')
            ->leftJoin('cart.cartCheckout', 'checkout')
            ->leftJoin('cart.invoiceAddress', 'invoiceAddress')
            ->leftJoin('cart.deliveryAddress', 'deliveryAddress')
            ->leftJoin('cart.user', 'user')
            ->innerJoin('cart.items', 'items')
            ->innerJoin('items.variant', 'variant')
            ->innerJoin('variant.product', 'product')
            ->andWhere('cart.abandonedAt IS NOT NULL')
            ->andWhere('cart.abandonedAt <= :abandonedBefore')
            ->andWhere('cart.abandonedAt >= :abandonedAfter')
            ->andWhere('cart.invoice IS NULL')
            ->andWhere('(
                checkout.id IS NULL
                OR (
                    checkout.disabled = FALSE
                    AND checkout.payedAt IS NULL
                    AND checkout.orderedAt IS NULL
                )
            )')
            ->andWhere('COALESCE(invoiceAddress.email, deliveryAddress.email, user.email) IS NOT NULL')
            ->andWhere(\sprintf(
                'NOT EXISTS (
                    SELECT reminder.id
                    FROM %s reminder
                    WHERE reminder.cart = cart
                )',
                AbandonedCartReminder::class,
            ))
            ->setParameter('abandonedBefore', $abandonedBefore)
            ->setParameter('abandonedAfter', $abandonedAfter)
            ->orderBy('cart.abandonedAt', 'ASC')
            ->setMaxResults(max(1, min(500, $limit)))
            ->getQuery()
            ->getResult();
    }

    public function hasCompletedOrderForCustomer(Cart $cart): bool
    {
        foreach ($this->findPaidCartsForPromoContext($cart) as $completedCart) {
            if (!$completedCart->getId()->equals($cart->getId())) {
                return true;
            }
        }

        return false;
    }

    public function findOneCartInProgressByUser(UserInterface $user): ?Cart
    {
        return $this->findCartsInProgressByUser($user)[0] ?? null;
    }

    /**
     * @return list<Cart>
     */
    public function findCartsInProgressByUser(UserInterface $user, bool $checkExpired = true): array
    {
        $qb = $this->createQueryBuilder('cart')
            ->leftJoin('cart.cartCheckout', 'checkout')
            ->addSelect('checkout')
            ->leftJoin('checkout.paymentMethod', 'payment_method')
            ->addSelect('payment_method')
            ->andWhere('cart.invoice is NULL')
            ->andWhere('(
                checkout.id IS NULL
                OR (
                    checkout.disabled = FALSE
                    AND checkout.payedAt IS NULL
                    AND checkout.initiatedAt IS NULL
                    AND (
                        checkout.orderedAt IS NULL
                        OR payment_method.id IS NULL
                        OR payment_method.method NOT IN (:offlineCheckoutMethods)
                    )
                )
            )')
            ->andWhere('cart.user = :user')
            ->orderBy('cart.updatedAt', 'DESC')
            ->addOrderBy('cart.createdAt', 'DESC')
            ->setParameter('user', $user)
            ->setParameter('offlineCheckoutMethods', self::OFFLINE_CHECKOUT_METHODS);

        if ($checkExpired) {
            $qb
                ->andWhere('cart.expiresAt > :date')
                ->setParameter('date', new \DateTime());
        }

        return $qb->getQuery()->getResult();
    }

    /**
     * @return array<int, Cart>
     */
    public function findTrackingAdmin(string $sort = 'DESC'): array
    {
        return $this->findTrackingAdminFiltered(sort: $sort);
    }

    /**
     * @return array<int, Cart>
     */
    public function findTrackingAdminFiltered(
        ?string $status = null,
        ?string $paymentMethodId = null,
        ?\DateTimeImmutable $createdFrom = null,
        ?\DateTimeImmutable $createdTo = null,
        ?string $search = null,
        string $sort = 'DESC',
    ): array {
        $qb = $this->createQueryBuilder('cart')
            ->leftJoin('cart.cartCheckout', 'checkout')
            ->addSelect('checkout')
            ->leftJoin('checkout.paymentMethod', 'payment_method')
            ->addSelect('payment_method')
            ->leftJoin('cart.invoice', 'invoice')
            ->addSelect('invoice')
            ->leftJoin('cart.invoiceAddress', 'invoice_address')
            ->addSelect('invoice_address')
            ->leftJoin('cart.deliveryAddress', 'delivery_address')
            ->addSelect('delivery_address')
            ->leftJoin('cart.user', 'user')
            ->addSelect('user')
            ->andWhere('EXISTS (
                SELECT 1
                FROM CpCreation\VitiCore\Cart\Model\CartItem item
                WHERE item.cart = cart
            )')
            ->orderBy('cart.createdAt', $sort)
            ->addOrderBy('cart.id', $sort);

        $this->applyTrackingAdminStatusFilter($qb, $status);

        if (null !== $paymentMethodId) {
            $qb
                ->andWhere('payment_method.id = :paymentMethodId')
                ->setParameter('paymentMethodId', $paymentMethodId);
        }

        if (null !== $createdFrom) {
            $qb
                ->andWhere('cart.createdAt >= :createdFrom')
                ->setParameter('createdFrom', $createdFrom);
        }

        if (null !== $createdTo) {
            $qb
                ->andWhere('cart.createdAt < :createdTo')
                ->setParameter('createdTo', $createdTo);
        }

        if (null !== $search) {
            $searchCartIds = $this->findTrackingAdminIdsBySearch($search);

            if ([] === $searchCartIds) {
                return [];
            }

            $qb
                ->andWhere('cart.id IN (:searchIds)')
                ->setParameter('searchIds', $searchCartIds, ArrayParameterType::STRING);
        }

        $carts = $qb->getQuery()->getResult();

        if (\in_array($status, ['initiated', 'failed', 'canceled'], true)) {
            return array_values(array_filter(
                $carts,
                static fn (Cart $cart): bool => $cart->getCheckoutStatus() === $status
            ));
        }

        return $carts;
    }

    public function countOfflineOrdersAwaitingPayment(): int
    {
        return $this->countTrackingAdminForStatus('offline_pending');
    }

    public function countPaidOrdersAwaitingDelivery(): int
    {
        return $this->countTrackingAdminForStatus('paid_not_delivered');
    }

    private function countTrackingAdminForStatus(string $status): int
    {
        $qb = $this->createQueryBuilder('cart')
            ->select('COUNT(DISTINCT cart.id)')
            ->leftJoin('cart.cartCheckout', 'checkout')
            ->leftJoin('checkout.paymentMethod', 'payment_method')
            ->andWhere('EXISTS (
                SELECT 1
                FROM CpCreation\VitiCore\Cart\Model\CartItem item
                WHERE item.cart = cart
            )');

        $this->applyTrackingAdminStatusFilter($qb, $status);

        return (int) $qb->getQuery()->getSingleScalarResult();
    }

    private function applyTrackingAdminStatusFilter(QueryBuilder $qb, ?string $status): void
    {
        if ('paid' === $status) {
            $qb->andWhere('checkout.payedAt IS NOT NULL');
        } elseif ('delivered' === $status) {
            $qb
                ->andWhere('checkout.payedAt IS NOT NULL')
                ->andWhere('cart.delivery = TRUE');
        } elseif ('paid_not_delivered' === $status) {
            $qb
                ->andWhere('checkout.payedAt IS NOT NULL')
                ->andWhere('cart.delivery = FALSE');
        } elseif ('offline_pending' === $status) {
            $qb
                ->andWhere('cart.abandonedAt IS NULL')
                ->andWhere('checkout.disabled = FALSE')
                ->andWhere('checkout.orderedAt IS NOT NULL')
                ->andWhere('checkout.payedAt IS NULL')
                ->andWhere('payment_method.paymentOnline = FALSE');
        } elseif ('initiated' === $status) {
            $qb
                ->andWhere('cart.abandonedAt IS NULL')
                ->andWhere('checkout.payedAt IS NULL')
                ->andWhere('(payment_method.id IS NULL OR payment_method.method NOT IN (:offlineCheckoutMethods))')
                ->setParameter('offlineCheckoutMethods', self::OFFLINE_CHECKOUT_METHODS)
                ->andWhere('checkout.initiatedAt IS NOT NULL');
        } elseif ('failed' === $status || 'canceled' === $status) {
            $qb
                ->andWhere('checkout.payedAt IS NULL')
                ->andWhere('checkout.response IS NOT NULL');
        } elseif ('abandoned' === $status) {
            $qb
                ->andWhere('cart.abandonedAt IS NOT NULL')
                ->andWhere('(
                    checkout.id IS NULL
                    OR (
                        checkout.disabled = FALSE
                        AND checkout.payedAt IS NULL
                        AND checkout.orderedAt IS NULL
                        AND checkout.response IS NULL
                    )
                )');
        } elseif ('pending' === $status) {
            $qb
                ->andWhere('cart.abandonedAt IS NULL')
                ->andWhere('checkout.payedAt IS NULL')
                ->andWhere('(checkout.id IS NULL OR (checkout.initiatedAt IS NULL AND checkout.orderedAt IS NULL))');
        }
    }

    /**
     * @return list<string>
     */
    private function findTrackingAdminIdsBySearch(string $search): array
    {
        $normalizedSearch = $this->normalizeTextSearch($search);
        if (null === $normalizedSearch) {
            return [];
        }

        $connection = $this->getEntityManager()->getConnection();
        $term = '%'.$normalizedSearch.'%';

        /** @var list<mixed> $rawIds */
        $rawIds = $connection->fetchFirstColumn(<<<'SQL'
SELECT DISTINCT CAST(cart.id AS text) AS id
FROM cart_cart cart
LEFT JOIN cart_cart_checkout checkout ON checkout.cart_cart_id = cart.id
LEFT JOIN cart_payment_method payment_method ON payment_method.id = checkout.payment_method_id
LEFT JOIN cart_invoice invoice ON invoice.id = cart.invoice_id
LEFT JOIN cart_invoice_address invoice_address ON invoice_address.id = cart.invoice_address_id
LEFT JOIN cart_delivery_address delivery_address ON delivery_address.id = cart.delivery_address_id
LEFT JOIN user_user "user" ON "user".id = cart.user_id
WHERE EXISTS (
    SELECT 1
    FROM cart_cart_item item
    WHERE item.cart_cart_id = cart.id
)
AND (
    LOWER(COALESCE(cart.token, '')) LIKE :term
    OR LOWER(COALESCE(cart.group_code, '')) LIKE :term
    OR LOWER(COALESCE(cart.comment, '')) LIKE :term
    OR LOWER(COALESCE(cart.code_carrier, '')) LIKE :term
    OR LOWER(COALESCE(cart.url_de_suivi_carrier, '')) LIKE :term
    OR LOWER(COALESCE(invoice_address.firstname, '')) LIKE :term
    OR LOWER(COALESCE(invoice_address.lastname, '')) LIKE :term
    OR LOWER(COALESCE(invoice_address.company, '')) LIKE :term
    OR LOWER(COALESCE(invoice_address.email, '')) LIKE :term
    OR LOWER(COALESCE(invoice_address.phone, '')) LIKE :term
    OR LOWER(COALESCE(invoice_address.siret, '')) LIKE :term
    OR LOWER(COALESCE(invoice_address.vat_id, '')) LIKE :term
    OR LOWER(COALESCE(invoice_address.street, '')) LIKE :term
    OR LOWER(COALESCE(invoice_address.postal, '')) LIKE :term
    OR LOWER(COALESCE(invoice_address.city, '')) LIKE :term
    OR LOWER(COALESCE(delivery_address.firstname, '')) LIKE :term
    OR LOWER(COALESCE(delivery_address.lastname, '')) LIKE :term
    OR LOWER(COALESCE(delivery_address.company, '')) LIKE :term
    OR LOWER(COALESCE(delivery_address.email, '')) LIKE :term
    OR LOWER(COALESCE(delivery_address.phone, '')) LIKE :term
    OR LOWER(COALESCE(delivery_address.siret, '')) LIKE :term
    OR LOWER(COALESCE(delivery_address.vat_id, '')) LIKE :term
    OR LOWER(COALESCE(delivery_address.street, '')) LIKE :term
    OR LOWER(COALESCE(delivery_address.postal, '')) LIKE :term
    OR LOWER(COALESCE(delivery_address.city, '')) LIKE :term
    OR LOWER(COALESCE("user".email, '')) LIKE :term
    OR LOWER(COALESCE(payment_method.method, '')) LIKE :term
    OR LOWER(COALESCE(checkout.payed_token, '')) LIKE :term
    OR LOWER(COALESCE(checkout.response::text, '')) LIKE :term
    OR LOWER(COALESCE(invoice.number_debit::text, '')) LIKE :term
    OR LOWER(COALESCE(invoice.number_credit::text, '')) LIKE :term
    OR LOWER(COALESCE(invoice.year::text, '')) LIKE :term
    OR LOWER(COALESCE(invoice.year::text || '-' || LPAD(invoice.number_debit::text, 5, '0'), '')) LIKE :term
    OR LOWER(COALESCE(invoice.year::text || '-avoir-' || LPAD(invoice.number_credit::text, 5, '0'), '')) LIKE :term
    OR LOWER(CASE WHEN cart.document_type = 'invoice' THEN 'facture invoice' ELSE 'commande order' END) LIKE :term
    OR LOWER(CASE WHEN invoice.credit = TRUE THEN 'avoir credit' ELSE '' END) LIKE :term
)
SQL,
            ['term' => $term]
        );

        return array_values(array_filter(array_map(
            static fn (mixed $id): ?string => \is_string($id) && '' !== $id ? $id : null,
            $rawIds
        )));
    }

    private function normalizeTextSearch(string $search): ?string
    {
        $search = mb_strtolower(mb_trim($search));

        return '' !== $search ? $search : null;
    }

    /**
     * @return list<Cart>
     */
    public function findPaidForStatsFiltered(
        ?string $paymentMethodId = null,
        ?\DateTimeImmutable $paidFrom = null,
        ?\DateTimeImmutable $paidTo = null,
    ): array {
        $qb = $this->createQueryBuilder('cart')
            ->innerJoin(
                'cart.cartCheckout',
                'checkout',
                Join::WITH,
                'checkout.disabled = FALSE AND checkout.payedAt IS NOT NULL'
            )
            ->addSelect('checkout')
            ->leftJoin('checkout.paymentMethod', 'payment_method')
            ->addSelect('payment_method')
            ->leftJoin('cart.items', 'items')
            ->addSelect('items')
            ->leftJoin('items.variant', 'variant')
            ->addSelect('variant')
            ->leftJoin('variant.product', 'product')
            ->addSelect('product')
            ->leftJoin('variant.type', 'type')
            ->addSelect('type')
            ->andWhere('EXISTS (
                SELECT 1
                FROM CpCreation\VitiCore\Cart\Model\CartItem item
                WHERE item.cart = cart
            )')
            ->orderBy('checkout.payedAt', 'ASC')
            ->addOrderBy('cart.createdAt', 'ASC');

        if (null !== $paymentMethodId) {
            $qb
                ->andWhere('payment_method.id = :paymentMethodId')
                ->setParameter('paymentMethodId', $paymentMethodId);
        }

        if (null !== $paidFrom) {
            $qb
                ->andWhere('checkout.payedAt >= :paidFrom')
                ->setParameter('paidFrom', $paidFrom);
        }

        if (null !== $paidTo) {
            $qb
                ->andWhere('checkout.payedAt < :paidTo')
                ->setParameter('paidTo', $paidTo);
        }

        return $qb->getQuery()->getResult();
    }

    /**
     * @return list<Cart>
     */
    public function findAbandonableCarts(
        \DateTimeImmutable $pendingCutoff,
        \DateTimeImmutable $checkoutCutoff,
        int $limit = 100,
    ): array {
        $qb = $this->createQueryBuilder('cart')
            ->leftJoin('cart.cartCheckout', 'checkout')
            ->addSelect('checkout')
            ->andWhere('cart.abandonedAt IS NULL')
            ->andWhere('cart.invoice IS NULL')
            ->andWhere('EXISTS (
                SELECT 1
                FROM CpCreation\VitiCore\Cart\Model\CartItem item
                WHERE item.cart = cart
            )')
            ->andWhere('(
                checkout.id IS NULL
                OR (
                    checkout.disabled = FALSE
                    AND checkout.payedAt IS NULL
                    AND checkout.orderedAt IS NULL
                    AND checkout.response IS NULL
                )
            )')
            ->andWhere('(
                (
                    checkout.initiatedAt IS NOT NULL
                    AND (
                        cart.lastActivityAt <= :checkoutCutoff
                        OR (
                            cart.lastActivityAt IS NULL
                            AND cart.updatedAt <= :checkoutCutoff
                        )
                        OR (
                            cart.lastActivityAt IS NULL
                            AND cart.updatedAt IS NULL
                            AND cart.createdAt <= :checkoutCutoff
                        )
                    )
                )
                OR (
                    (checkout.id IS NULL OR checkout.initiatedAt IS NULL)
                    AND (
                        cart.lastActivityAt <= :pendingCutoff
                        OR (
                            cart.lastActivityAt IS NULL
                            AND cart.updatedAt <= :pendingCutoff
                        )
                        OR (
                            cart.lastActivityAt IS NULL
                            AND cart.updatedAt IS NULL
                            AND cart.createdAt <= :pendingCutoff
                        )
                    )
                )
            )')
            ->orderBy('cart.lastActivityAt', 'ASC')
            ->addOrderBy('cart.updatedAt', 'ASC')
            ->addOrderBy('cart.createdAt', 'ASC')
            ->setMaxResults($limit)
            ->setParameter('pendingCutoff', $pendingCutoff)
            ->setParameter('checkoutCutoff', $checkoutCutoff);

        return $qb->getQuery()->getResult();
    }

    public function findLastCartByUser(UserInterface $user): ?Cart
    {
        $qb = $this
            ->createQueryBuilder('cart')
            ->leftJoin('cart.cartCheckout', 'checkout')
            ->addSelect('checkout')
            ->leftJoin('checkout.paymentMethod', 'payment_method')
            ->addSelect('payment_method')
            ->andWhere('cart.invoice is NULL')
            ->andWhere('(
                checkout.id IS NULL
                OR (
                    checkout.disabled = FALSE
                    AND checkout.payedAt IS NULL
                    AND (
                        checkout.orderedAt IS NULL
                        OR payment_method.id IS NULL
                        OR payment_method.method NOT IN (:offlineCheckoutMethods)
                    )
                )
            )')
            ->andWhere('cart.user = :user')
            ->orderBy('cart.id', 'DESC')
            ->setMaxResults(1)
            ->setParameter('user', $user)
            ->setParameter('offlineCheckoutMethods', self::OFFLINE_CHECKOUT_METHODS);

        return $qb->getQuery()->getOneOrNullResult();
    }

    /**
     * @return array<int, Cart>
     */
    public function findLastPaidByUser(UserInterface $user, int $limit = 5): array
    {
        return $this->findLastAccountOrdersByUser($user, $limit);
    }

    /**
     * Vue admin "Détail client" :
     * liste les commandes du client sur le même périmètre que le suivi
     * admin, y compris les commandes encore en cours.
     *
     * @return array<int, Cart>
     */
    public function findRecentAdminOrdersByUser(UserInterface $user, ?int $limit = 10): array
    {
        $qb = $this
            ->createQueryBuilder('cart')
            ->leftJoin('cart.cartCheckout', 'checkout')
            ->addSelect('checkout')
            ->leftJoin('checkout.paymentMethod', 'payment_method')
            ->addSelect('payment_method')
            ->leftJoin('cart.deliveryMethod', 'delivery_method')
            ->addSelect('delivery_method')
            ->andWhere('cart.user = :user')
            ->andWhere('EXISTS (
                SELECT 1
                FROM CpCreation\VitiCore\Cart\Model\CartItem item
                WHERE item.cart = cart
            )')
            ->orderBy('cart.createdAt', 'DESC')
            ->addOrderBy('cart.updatedAt', 'DESC')
            ->addOrderBy('checkout.createdAt', 'DESC')
            ->setParameter('user', $user);

        if (null !== $limit) {
            $qb->setMaxResults(max(1, $limit));
        }

        return $qb
            ->getQuery()
            ->getResult();
    }

    /**
     * @param list<UserInterface> $users
     *
     * @return list<string>
     */
    public function findUserIdsWithOrders(array $users): array
    {
        if ([] === $users) {
            return [];
        }

        $results = $this
            ->createQueryBuilder('cart')
            ->select('DISTINCT IDENTITY(cart.user) AS user_id')
            ->leftJoin('cart.cartCheckout', 'checkout')
            ->andWhere('cart.user IN (:users)')
            ->andWhere('EXISTS (
                SELECT 1
                FROM CpCreation\VitiCore\Cart\Model\CartItem item
                WHERE item.cart = cart
            )')
            ->andWhere('
                checkout.payedAt IS NOT NULL
                OR checkout.orderedAt IS NOT NULL
            ')
            ->setParameter('users', $users)
            ->getQuery()
            ->getScalarResult();

        $userIds = array_values(array_filter(array_map(
            static fn (array $row): ?string => isset($row['user_id']) && \is_string($row['user_id']) ? $row['user_id'] : null,
            $results
        )));

        $userIdsByEmail = [];
        foreach ($users as $user) {
            if (!$user instanceof User) {
                continue;
            }

            $email = mb_strtolower(mb_trim((string) $user->getEmail()));
            if ('' !== $email) {
                $userIdsByEmail[$email] = $user->getId()->toString();
            }
        }

        if ([] === $userIdsByEmail) {
            return $userIds;
        }

        /** @var list<mixed> $guestEmails */
        $guestEmails = $this->getEntityManager()->getConnection()->fetchFirstColumn(<<<'SQL'
SELECT DISTINCT guest_order.email
FROM (
    SELECT LOWER(TRIM(COALESCE(
        NULLIF(TRIM(invoice_address.email), ''),
        NULLIF(TRIM(delivery_address.email), '')
    ))) AS email
    FROM cart_cart cart
    INNER JOIN cart_cart_checkout checkout ON checkout.cart_cart_id = cart.id
    LEFT JOIN cart_invoice_address invoice_address ON invoice_address.id = cart.invoice_address_id
    LEFT JOIN cart_delivery_address delivery_address ON delivery_address.id = cart.delivery_address_id
    WHERE cart.user_id IS NULL
      AND cart.deleted_at IS NULL
      AND checkout.deleted_at IS NULL
      AND checkout.disabled = FALSE
      AND (checkout.payed_at IS NOT NULL OR checkout.ordered_at IS NOT NULL)
      AND EXISTS (
          SELECT 1
          FROM cart_cart_item item
          WHERE item.cart_cart_id = cart.id
      )
) guest_order
WHERE guest_order.email IN (?)
SQL, [array_keys($userIdsByEmail)], [ArrayParameterType::STRING]);

        foreach ($guestEmails as $guestEmail) {
            if (!\is_string($guestEmail) || !isset($userIdsByEmail[$guestEmail])) {
                continue;
            }

            $userIds[] = $userIdsByEmail[$guestEmail];
        }

        return array_values(array_unique($userIds));
    }

    /**
     * Vue admin "Détail client" :
     * liste les commandes visibles dans le compte client, mais triées
     * par date de création décroissante.
     *
     * @return array<int, Cart>
     */
    public function findAccountOrdersByUserOrderByCreatedAt(UserInterface $user, ?int $limit = 10): array
    {
        $qb = $this
            ->createQueryBuilder('cart')
            ->innerJoin('cart.cartCheckout', 'checkout', Join::WITH, 'checkout.disabled = FALSE')
            ->addSelect('checkout')
            ->leftJoin('checkout.paymentMethod', 'payment_method')
            ->addSelect('payment_method')
            ->leftJoin('cart.deliveryMethod', 'delivery_method')
            ->addSelect('delivery_method')
            ->andWhere('cart.user = :user')
            ->andWhere('
                checkout.payedAt IS NOT NULL
                OR (
                    checkout.orderedAt IS NOT NULL
                    AND payment_method.method IN (:offlineCheckoutMethods)
                )
                OR (
                    checkout.initiatedAt IS NOT NULL
                    AND payment_method.method = :stripeMethod
                )
            ')
            ->orderBy('cart.createdAt', 'DESC')
            ->addOrderBy('checkout.createdAt', 'DESC')
            ->setParameter('user', $user)
            ->setParameter('offlineCheckoutMethods', self::OFFLINE_CHECKOUT_METHODS)
            ->setParameter('stripeMethod', 'stripe');

        if (null !== $limit) {
            $qb->setMaxResults(max(1, $limit));
        }

        return $qb
            ->getQuery()
            ->getResult();
    }

    /**
     * Règle métier "Mon compte" avec les données actuellement persistées :
     * - paiement en ligne => uniquement les commandes payées ;
     * - paiement hors ligne => uniquement les commandes effectivement envoyées.
     * Tri : date de création décroissante.
     *
     * @return array<int, Cart>
     */
    public function findLastAccountOrdersByUser(UserInterface $user, ?int $limit = 5): array
    {
        $qb = $this
            ->createQueryBuilder('cart')
            ->innerJoin('cart.cartCheckout', 'checkout', Join::WITH, 'checkout.disabled = FALSE')
            ->addSelect('checkout')
            ->leftJoin('checkout.paymentMethod', 'payment_method')
            ->addSelect('payment_method')
            ->leftJoin('cart.deliveryMethod', 'delivery_method')
            ->addSelect('delivery_method')
            ->andWhere('cart.user = :user')
            ->andWhere('
                checkout.payedAt IS NOT NULL
                OR (
                    checkout.orderedAt IS NOT NULL
                    AND payment_method.method IN (:offlineCheckoutMethods)
                )
                OR (
                    checkout.initiatedAt IS NOT NULL
                    AND payment_method.method = :stripeMethod
                )
            ')
            ->orderBy('cart.createdAt', 'DESC')
            ->addOrderBy('checkout.createdAt', 'DESC')
            ->addOrderBy('checkout.payedAt', 'DESC')
            ->addOrderBy('checkout.orderedAt', 'DESC')
            ->addOrderBy('checkout.updatedAt', 'DESC')
            ->addOrderBy('cart.updatedAt', 'DESC')
            ->setParameter('user', $user)
            ->setParameter('offlineCheckoutMethods', self::OFFLINE_CHECKOUT_METHODS)
            ->setParameter('stripeMethod', 'stripe');

        if (null !== $limit) {
            $qb->setMaxResults(max(1, $limit));
        }

        return $qb
            ->getQuery()
            ->getResult();
    }

    public function findCompletedOrderForWithdrawal(string $reference, string $email): ?Cart
    {
        $reference = mb_strtolower(mb_trim($reference, " \t\n\r\0\x0B#"));
        $email = mb_strtolower(mb_trim($email));

        if ('' === $reference || '' === $email) {
            return null;
        }

        $referenceCondition = 'LOWER(cart.token) = :reference';
        if (preg_match('/^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i', $reference)) {
            $referenceCondition = '('.$referenceCondition.' OR cart.id = :cartId)';
        }

        $qb = $this
            ->createQueryBuilder('cart')
            ->innerJoin('cart.cartCheckout', 'checkout', Join::WITH, 'checkout.disabled = FALSE')
            ->addSelect('checkout')
            ->leftJoin('checkout.paymentMethod', 'payment_method')
            ->addSelect('payment_method')
            ->leftJoin('cart.deliveryAddress', 'delivery_address')
            ->addSelect('delivery_address')
            ->leftJoin('cart.invoiceAddress', 'invoice_address')
            ->addSelect('invoice_address')
            ->leftJoin('cart.user', 'site_user')
            ->addSelect('site_user')
            ->leftJoin('cart.withdrawalRequest', 'withdrawal_request')
            ->addSelect('withdrawal_request')
            ->andWhere($referenceCondition)
            ->andWhere('(
                checkout.payedAt IS NOT NULL
                OR (
                    checkout.orderedAt IS NOT NULL
                    AND payment_method.method IN (:offlineCheckoutMethods)
                )
            )')
            ->andWhere('(
                LOWER(invoice_address.email) = :email
                OR LOWER(delivery_address.email) = :email
                OR LOWER(site_user.email) = :email
            )')
            ->setParameter('reference', $reference)
            ->setParameter('email', $email)
            ->setParameter('offlineCheckoutMethods', self::OFFLINE_CHECKOUT_METHODS)
            ->setMaxResults(1);

        if (preg_match('/^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i', $reference)) {
            $qb->setParameter('cartId', $reference);
        }

        return $qb->getQuery()->getOneOrNullResult();
    }

    public function attachGuestCompletedOrdersToUserByEmail(User $user): int
    {
        $email = mb_strtolower(mb_trim((string) $user->getEmail()));

        if ('' === $email) {
            return 0;
        }

        $carts = $this
            ->createQueryBuilder('cart')
            ->innerJoin('cart.cartCheckout', 'checkout', Join::WITH, 'checkout.disabled = FALSE')
            ->addSelect('checkout')
            ->leftJoin('checkout.paymentMethod', 'payment_method')
            ->addSelect('payment_method')
            ->leftJoin('cart.deliveryAddress', 'delivery_address')
            ->addSelect('delivery_address')
            ->leftJoin('cart.invoiceAddress', 'invoice_address')
            ->addSelect('invoice_address')
            ->andWhere('cart.user IS NULL')
            ->andWhere('
                checkout.payedAt IS NOT NULL
                OR (
                    checkout.orderedAt IS NOT NULL
                    AND payment_method.method IN (:offlineCheckoutMethods)
                )
            ')
            ->andWhere('
                LOWER(delivery_address.email) = :email
                OR LOWER(invoice_address.email) = :email
            ')
            ->setParameter('offlineCheckoutMethods', self::OFFLINE_CHECKOUT_METHODS)
            ->setParameter('email', $email)
            ->getQuery()
            ->getResult();

        $count = 0;

        foreach ($carts as $cart) {
            if (!$cart instanceof Cart) {
                continue;
            }

            $cart->setUser($user);
            $this->persist($cart);
            ++$count;
        }

        if ($count > 0) {
            $this->flush();
        }

        return $count;
    }

    /**
     * @return array<int, Cart>
     */
    public function countPromoByUser(Promo $promo, Cart $cart): array
    {
        $phone = $cart->getPhone();
        $email = $cart->getEmail();

        if ((null === $phone || '' === $phone) && (null === $email || '' === $email)) {
            return [];
        }

        $qb = $this->createQueryBuilder('c');
        $qb
            ->andWhere('c.promoId = :promo')
            ->andWhere(
                $qb->expr()->orX(
                    'cart_checkout.payedAt IS NOT NULL',
                    'cart_checkout.orderedAt IS NOT NULL'
                )
            );

        $matches = $qb->expr()->orX();

        if (null !== $phone && '' !== $phone) {
            $matches->add(
                $qb->expr()->eq(
                    $qb->expr()->substring('invoice_address.phone', -8),
                    $qb->expr()->literal(mb_substr($phone, -8))
                )
            );
        }

        if (null !== $email && '' !== $email) {
            $matches->add(
                $qb->expr()->eq(
                    'invoice_address.email',
                    $qb->expr()->literal($email)
                )
            );
        }

        $qb->andWhere($matches);

        $qb
            ->innerJoin('c.cartCheckout', 'cart_checkout', Join::WITH, 'cart_checkout.cart = c.id')
            ->innerJoin('c.invoiceAddress', 'invoice_address')
            ->setParameter('promo', $promo->getId()->toString());

        $query = $qb->getQuery();

        return $query->getResult();
    }

    public function countAppliedPromoForCartContext(Promo $promo, Cart $cart): int
    {
        $candidates = $this->findPaidCartsForPromoContext($cart);
        $count = 0;

        foreach ($candidates as $candidate) {
            foreach ($candidate->getPromos() as $appliedPromo) {
                if (
                    $appliedPromo->promoId === $promo->getId()->toString()
                    || (
                        null !== $appliedPromo->code
                        && null !== $promo->getTitle()
                        && mb_strtoupper($appliedPromo->code) === mb_strtoupper($promo->getTitle())
                    )
                ) {
                    ++$count;
                    break;
                }
            }
        }

        return $count;
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    public function stockSaleSinceStockAt(ProductVariant $variant): array
    {
        $cacheKey = \sprintf(
            '%s:%s',
            $variant->getId()->toString(),
            $variant->getStockAt()?->format(\DATE_ATOM) ?? 'null',
        );

        if (\array_key_exists($cacheKey, $this->stockSaleSinceStockAtCache)) {
            return $this->stockSaleSinceStockAtCache[$cacheKey];
        }

        $qb = $this->createQueryBuilder('c');

        $qb
            ->innerJoin('c.items', 'item', Join::WITH, 'item.variant = :variant')
            ->setParameter('variant', $variant->getId()->toString())
            ->innerJoin('c.cartCheckout', 'checkout')
            ->andWhere('checkout.payedAt IS NOT NULL')
            ->andWhere('c.createdAt > :stockAt')
            ->setParameter('stockAt', $variant->getStockAt())
            ->select('SUM(item.quantity) as quantitySale');

        $query = $qb->getQuery();

        return $this->stockSaleSinceStockAtCache[$cacheKey] = $query->execute();
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    public function stockReservedSinceStockAt(ProductVariant $variant, ?Cart $excludedCart = null): array
    {
        $cacheKey = \sprintf(
            '%s:%s:%s',
            $variant->getId()->toString(),
            $variant->getStockAt()?->format(\DATE_ATOM) ?? 'null',
            $excludedCart?->getId()->toString() ?? 'none',
        );

        if (\array_key_exists($cacheKey, $this->stockReservedSinceStockAtCache)) {
            return $this->stockReservedSinceStockAtCache[$cacheKey];
        }

        $qb = $this->createQueryBuilder('c');

        $qb
            ->innerJoin('c.items', 'item', Join::WITH, 'item.variant = :variant')
            ->setParameter('variant', $variant->getId()->toString())
            ->innerJoin('c.cartCheckout', 'checkout', Join::WITH, 'checkout.disabled = FALSE')
            ->andWhere('checkout.payedAt IS NULL')
            ->andWhere('(checkout.initiatedAt IS NOT NULL OR checkout.orderedAt IS NOT NULL)')
            ->andWhere('c.createdAt > :stockAt')
            ->andWhere('c.expiresAt > :now')
            ->setParameter('stockAt', $variant->getStockAt())
            ->setParameter('now', new \DateTimeImmutable())
            ->select('SUM(item.quantity) as quantityReserved');

        if ($excludedCart instanceof Cart) {
            $qb
                ->andWhere('c != :excludedCart')
                ->setParameter('excludedCart', $excludedCart);
        }

        $query = $qb->getQuery();

        return $this->stockReservedSinceStockAtCache[$cacheKey] = $query->execute();
    }

    /**
     * @return array<int, Cart>
     */
    private function findPaidCartsForPromoContext(Cart $cart): array
    {
        $qb = $this->createQueryBuilder('c');

        $qb
            ->innerJoin('c.cartCheckout', 'checkout', Join::WITH, 'checkout.disabled = FALSE')
            ->leftJoin('c.user', 'customer')
            ->leftJoin('c.invoiceAddress', 'invoice_address')
            ->leftJoin('c.deliveryAddress', 'delivery_address')
            ->andWhere(
                $qb->expr()->orX(
                    'checkout.payedAt IS NOT NULL',
                    'checkout.orderedAt IS NOT NULL'
                )
            );

        $matches = $qb->expr()->orX();
        $hasMatcher = false;

        if (null !== $cart->getUser()) {
            $matches->add('customer = :user');
            $qb->setParameter('user', $cart->getUser());
            $hasMatcher = true;
        }

        $emails = array_values(array_unique(array_filter([
            $cart->getInvoiceAddress()?->getEmail(),
            $cart->getDeliveryAddress()?->getEmail(),
        ], static fn (?string $value): bool => null !== $value && '' !== mb_trim($value))));

        if ([] !== $emails) {
            $matches->add($qb->expr()->in('invoice_address.email', ':emails'));
            $matches->add($qb->expr()->in('delivery_address.email', ':emails'));
            $qb->setParameter('emails', $emails);
            $hasMatcher = true;
        }

        $phones = array_values(array_unique(array_filter([
            $this->normalizePhoneSuffix($cart->getInvoiceAddress()?->getPhone()),
            $this->normalizePhoneSuffix($cart->getDeliveryAddress()?->getPhone()),
        ])));

        foreach ($phones as $index => $phone) {
            $parameter = 'phone_'.$index;
            $matches->add($qb->expr()->eq($qb->expr()->substring('invoice_address.phone', -8), ':'.$parameter));
            $matches->add($qb->expr()->eq($qb->expr()->substring('delivery_address.phone', -8), ':'.$parameter));
            $qb->setParameter($parameter, $phone);
            $hasMatcher = true;
        }

        if (!$hasMatcher) {
            return [];
        }

        $qb->andWhere($matches);

        return $qb->getQuery()->getResult();
    }

    private function normalizePhoneSuffix(?string $phone): ?string
    {
        if (null === $phone) {
            return null;
        }

        $digits = preg_replace('/\D+/', '', $phone);

        if (null === $digits || '' === $digits) {
            return null;
        }

        return mb_substr($digits, -8);
    }
}
