<?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\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\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();
    }

    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);

        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 ('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))');
        }

        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;
    }

    /**
     * @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();

        return 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
        )));
    }

    /**
     * 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 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);
    }
}
