<?php

declare(strict_types=1);

namespace Gls\GlsPoland\PrestaShop\Grid\Query;

use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Query\QueryBuilder;
use PrestaShop\PrestaShop\Core\Grid\Query\AbstractDoctrineQueryBuilder;
use PrestaShop\PrestaShop\Core\Grid\Query\DoctrineSearchCriteriaApplicatorInterface;
use PrestaShop\PrestaShop\Core\Grid\Search\SearchCriteriaInterface;

final class ConsignmentQueryBuilder extends AbstractDoctrineQueryBuilder
{
    private $searchCriteriaApplicator;

    public function __construct(Connection $connection, string $dbPrefix, DoctrineSearchCriteriaApplicatorInterface $searchCriteriaApplicator)
    {
        parent::__construct($connection, $dbPrefix);

        $this->searchCriteriaApplicator = $searchCriteriaApplicator;
    }

    public function getSearchQueryBuilder(SearchCriteriaInterface $searchCriteria): QueryBuilder
    {
        $consignmentTypeExpression = $this->getConsignmentTypeSqlExpression();

        $queryBuilder = $this
            ->getCommonQueryBuilder($searchCriteria)
            ->select("c.*, o.reference AS order_reference, CONCAT(COALESCE(a.firstname, ''), ' ', COALESCE(a.lastname, '')) AS recipient_name, {$consignmentTypeExpression} AS consignment_type, GROUP_CONCAT(p.number ORDER BY p.id) AS parcel_numbers")
            ->innerJoin('c', $this->dbPrefix . 'gls_poland_parcel', 'p', 'p.consignment_id = c.id')
            ->groupBy('c.id');

        $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
    {
        $queryBuilder = $this->connection
            ->createQueryBuilder()
            ->from($this->dbPrefix . 'gls_poland_consignment', 'c')
            ->innerJoin('c', $this->dbPrefix . 'orders', 'o', 'o.id_order = c.id_order')
            ->leftJoin('o', $this->dbPrefix . 'address', 'a', 'a.id_address = o.id_address_delivery')
            ->leftJoin('o', $this->dbPrefix . 'carrier', 'car', 'car.id_carrier = o.id_carrier')
            ->leftJoin('car', $this->dbPrefix . 'gls_poland_carrier', 'gc', 'gc.id_reference = car.id_reference');

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

        return $queryBuilder;
    }

    private function applyFilters(array $filters, QueryBuilder $qb): void
    {
        foreach ($filters as $filterName => $filterValue) {
            switch ($filterName) {
                case 'id':
                case 'id_order':
                    $qb->andWhere('c.`' . $filterName . '` = :' . $filterName);
                    $qb->setParameter($filterName, $filterValue);

                    break;
                case 'sandbox':
                    $qb->andWhere('c.`' . $filterName . '` = :' . $filterName);
                    $qb->setParameter($filterName, $filterValue ? 1 : 0);

                    break;
                case 'tracking_number':
                case 'reference':
                case 'email':
                case 'phone':
                case 'pickup_id':
                    $qb->andWhere('c.`' . $filterName . '` LIKE :' . $filterName);
                    $qb->setParameter($filterName, '%' . $filterValue . '%');

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

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

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

                    break;
                case 'recipient_name':
                    $qb->andWhere("CONCAT(COALESCE(a.firstname, ''), ' ', COALESCE(a.lastname, '')) LIKE :recipient_name");
                    $qb->setParameter('recipient_name', '%' . $filterValue . '%');

                    break;
                case 'consignment_type':
                    $qb->andWhere($this->getConsignmentTypeSqlExpression() . ' LIKE :consignment_type');
                    $qb->setParameter('consignment_type', '%' . $filterValue . '%');

                    break;
                case 'parcel_numbers':
                    $subQuery = $this->connection
                        ->createQueryBuilder()
                        ->select('1')
                        ->from($this->dbPrefix . 'gls_poland_parcel', 'tmp')
                        ->andWhere('tmp.consignment_id = c.id')
                        ->andWhere('tmp.number LIKE :' . $filterName);

                    $qb->andWhere('EXISTS (' . $subQuery . ')');
                    $qb->setParameter($filterName, '%' . $filterValue . '%');

                    break;
                default:
                    break;
            }
        }
    }

    private function getConsignmentTypeSqlExpression(): string
    {
                        return "CASE gc.type WHEN 'sds' THEN 'GLS ParcelShop' WHEN 'shop_delivery' THEN 'GLS ParcelShop' WHEN 'courier' THEN TRIM(CONCAT('Kurier GLS', CASE WHEN gc.cash_on_delivery = 1 THEN ' - Pobranie' ELSE '' END, CASE WHEN gc.saturday_delivery = 1 THEN ' - Sobota' ELSE '' END, CASE WHEN gc.delivery_before_10 = 1 THEN ' - do 10:00' ELSE '' END, CASE WHEN gc.delivery_before_12 = 1 THEN ' - do 12:00' ELSE '' END, CASE WHEN gc.document_return = 1 THEN ' - Zwrot dokumentów' ELSE '' END)) ELSE COALESCE(gc.type, '') END";
    }
}