<?php
/**
 * _             _       _
 * | |____      _| | __ _| |__
 * | '_ \ \ /\ / / |/ _` | '_ \
 * | |_) \ V  V /| | (_| | |_) |
 * |_.__/ \_/\_/ |_|\__,_|_.__/.
 *
 * @author    Bwlab di Luigi Massa <info@bwlab.it>
 * @copyright since 2014 Bwlab
 * @license   proprietary
 */

declare(strict_types=1);

namespace Bwlab\Withdrawal\Grid\Query;

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

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

class WithdrawalRequestQueryBuilder extends AbstractDoctrineQueryBuilder
{
    public function getSearchQueryBuilder(SearchCriteriaInterface $searchCriteria): QueryBuilder
    {
        $qb = $this->getBaseQuery($searchCriteria);
        $qb->select(
            'wr.id_withdrawal_request',
            'wr.order_reference',
            'wr.customer_name',
            'wr.customer_email',
            'wr.status',
            'wr.date_add',
            'wr.withdrawal_deadline',
            "CONCAT(e.firstname, ' ', e.lastname) AS employee_name"
        );

        if ($searchCriteria->getOrderBy()) {
            $qb->orderBy($searchCriteria->getOrderBy(), $searchCriteria->getOrderWay());
        }

        $qb->setFirstResult($searchCriteria->getOffset())
           ->setMaxResults($searchCriteria->getLimit());

        return $qb;
    }

    public function getCountQueryBuilder(SearchCriteriaInterface $searchCriteria): QueryBuilder
    {
        return $this->getBaseQuery($searchCriteria)
            ->select('COUNT(wr.id_withdrawal_request)');
    }

    private function getBaseQuery(SearchCriteriaInterface $searchCriteria): QueryBuilder
    {
        $qb = $this->connection
            ->createQueryBuilder()
            ->from($this->dbPrefix . 'withdrawal_request', 'wr')
            ->leftJoin('wr', $this->dbPrefix . 'employee', 'e', 'e.id_employee = wr.id_employee');

        $filters = $searchCriteria->getFilters();

        if (!empty($filters['status'])) {
            $qb->andWhere('wr.status = :status')
               ->setParameter('status', $filters['status']);
        }

        if (!empty($filters['date_from'])) {
            $qb->andWhere('wr.date_add >= :date_from')
               ->setParameter('date_from', $filters['date_from']);
        }

        if (!empty($filters['date_to'])) {
            $qb->andWhere('wr.date_add <= :date_to')
               ->setParameter('date_to', $filters['date_to']);
        }

        if (!empty($filters['employee_name'])) {
            $qb->andWhere("CONCAT(e.firstname, ' ', e.lastname) LIKE :employee_name")
               ->setParameter('employee_name', '%' . $filters['employee_name'] . '%');
        }

        return $qb;
    }
}
