<?php
/**
 * Copyright since 2025 InPost S.A.
 *
 * For the full license information, please view the LICENSE file bundled with the module.
 *
 * @author InPost S.A.
 * @copyright since 2025 InPost S.A.
 * @license MIT
 */

declare(strict_types=1);

namespace InPost\International\Shipment\Grid;

use Doctrine\DBAL\Connection;
use Doctrine\DBAL\ParameterType;
use Doctrine\DBAL\Query\QueryBuilder;
use InPost\International\Api\Shipment\ShipmentType;
use InPost\International\Carrier\CarrierType;
use InPost\International\Shipment\ShippingMethod;
use PrestaShop\PrestaShop\Adapter\Shop\Context;
use PrestaShop\PrestaShop\Core\Grid\Query\AbstractDoctrineQueryBuilder;
use PrestaShop\PrestaShop\Core\Grid\Query\DoctrineSearchCriteriaApplicatorInterface;
use PrestaShop\PrestaShop\Core\Grid\Search\SearchCriteriaInterface;

if (!defined('_PS_VERSION_')) {
    exit;
}

final class ShipmentQueryBuilder extends AbstractDoctrineQueryBuilder
{
    private const RECIPIENT_PHONE_EXPR = 'CONCAT(s.recipient_phone_prefix, " ", s.recipient_phone_number)';

    /**
     * @var DoctrineSearchCriteriaApplicatorInterface
     */
    private $searchCriteriaApplicator;

    /**
     * @var Context
     */
    private $shopContext;

    public function __construct(Connection $connection, string $dbPrefix, DoctrineSearchCriteriaApplicatorInterface $searchCriteriaApplicator, Context $shopContext)
    {
        parent::__construct($connection, $dbPrefix);
        $this->searchCriteriaApplicator = $searchCriteriaApplicator;
        $this->shopContext = $shopContext;
    }

    public function getSearchQueryBuilder(SearchCriteriaInterface $searchCriteria): QueryBuilder
    {
        $queryBuilder = $this
            ->getCommonQueryBuilder($searchCriteria)
            ->select('s.*')
            ->addSelect('o.reference AS order_reference, o.date_add AS order_date, o.total_shipping AS shipping_cost, cur.iso_code AS order_currency')
            ->addSelect(self::RECIPIENT_PHONE_EXPR . ' AS recipient_phone')
            ->leftJoin('o', $this->dbPrefix . 'currency', 'cur', 'o.id_currency = cur.id_currency');

        $this->searchCriteriaApplicator
            ->applyPagination($searchCriteria, $queryBuilder)
            ->applySorting($searchCriteria, $queryBuilder);

        return $queryBuilder;
    }

    public function getCountQueryBuilder(SearchCriteriaInterface $searchCriteria): QueryBuilder
    {
        return $this
            ->getCommonQueryBuilder($searchCriteria)
            ->select('COUNT(*)');
    }

    private function getCommonQueryBuilder(SearchCriteriaInterface $searchCriteria): QueryBuilder
    {
        $qb = $this->connection
            ->createQueryBuilder()
            ->from($this->dbPrefix . 'inpost_intl_shipment', 's')
            ->innerJoin('s', $this->dbPrefix . 'orders', 'o', 'o.id_order = s.id_order');

        $this->applyFilters($searchCriteria->getFilters(), $qb);

        if ($this->shopContext->isAllShopContext()) {
            return $qb;
        }

        return $qb
            ->andWhere('o.id_shop IN (:shop_ids)')
            ->setParameter('shop_ids', $this->shopContext->getContextListShopID(), Connection::PARAM_INT_ARRAY);
    }

    /**
     * @param array<string, mixed> $filters
     */
    private function applyFilters(array $filters, QueryBuilder $qb): void
    {
        foreach ($filters as $filterName => $filterValue) {
            switch ($filterName) {
                case 'id':
                case 'status':
                    $qb->andWhere('s.' . $filterName . ' = :' . $filterName);
                    $qb->setParameter($filterName, $filterValue);

                    break;
                case 'id_order':
                    if (!is_array($filterValue)) {
                        $filterValue = [$filterValue];
                    }

                    $qb->andWhere('s.id_order IN (:order_ids)');
                    $qb->setParameter('order_ids', $filterValue, Connection::PARAM_INT_ARRAY);

                    break;
                case 'tracking_number':
                case 'reference':
                case 'recipient_email':
                    $qb->andWhere('s.' . $filterName . ' LIKE :' . $filterName);
                    $qb->setParameter($filterName, '%' . $filterValue . '%');

                    break;
                case 'is_sandbox':
                    $qb->andWhere('s.is_sandbox = :sandbox');
                    $qb->setParameter('sandbox', $filterValue, ParameterType::BOOLEAN);

                    break;
                case 'recipient_phone':
                    $qb->andWhere(self::RECIPIENT_PHONE_EXPR . ' LIKE :phone');
                    $qb->setParameter('phone', '%' . $filterValue . '%');

                    break;
                case 'service':
                    if (null !== $service = CarrierType::tryFrom($filterValue)) {
                        $types = array_map(static function (ShipmentType $type) {
                            return $type->value;
                        }, $service->getPossibleShipmentTypes());

                        $qb->andWhere('s.type IN (:service)');
                        $qb->setParameter('service', $types, Connection::PARAM_STR_ARRAY);
                    }

                    break;
                case 'shipping_method':
                    if (null !== $shippingMethod = ShippingMethod::tryFrom($filterValue)) {
                        $types = array_map(static function (ShipmentType $type) {
                            return $type->value;
                        }, $shippingMethod->getPossibleShipmentTypes());

                        $qb->andWhere('s.type IN (:shipping_method)');
                        $qb->setParameter('shipping_method', $types, Connection::PARAM_STR_ARRAY);
                    }

                    break;
                case 'has_pickup_order':
                    $types = [ShipmentType::AddressToPoint()->value, ShipmentType::AddressToAddress()->value];

                    $qb->andWhere('s.type IN (:type)');
                    $qb->setParameter('type', $types, Connection::PARAM_STR_ARRAY);

                    if ($filterValue) {
                        $qb->andWhere('s.pickup_order_id IS NOT NULL');
                    } else {
                        $qb->andWhere('s.pickup_order_id IS NULL');
                    }
                    break;
                case 'created_at':
                    if (isset($filterValue['from'])) {
                        $qb->andWhere('s.' . $filterName . ' >= :' . $filterName . '_from');
                        $qb->setParameter($filterName . '_from', sprintf('%s 00:00:00', $filterValue['from']));
                    }

                    if (isset($filterValue['to'])) {
                        $qb->andWhere('s.' . $filterName . ' <= :' . $filterName . '_to');
                        $qb->setParameter($filterName . '_to', sprintf('%s 23:59:59', $filterValue['to']));
                    }

                    break;
                case 'order_date':
                    if (isset($filterValue['from'])) {
                        $qb->andWhere('o.date_add >= :oder_date_from');
                        $qb->setParameter('oder_date_from', sprintf('%s 00:00:00', $filterValue['from']));
                    }

                    if (isset($filterValue['to'])) {
                        $qb->andWhere('o.date_add <= :oder_date_to');
                        $qb->setParameter('oder_date_to', sprintf('%s 23:59:59', $filterValue['to']));
                    }

                    break;
                case 'order_reference':
                    $qb->andWhere('o.reference LIKE :order_reference');
                    $qb->setParameter('order_reference', '%' . $filterValue . '%');

                    break;
                default:
                    break;
            }
        }
    }
}
