<?php

namespace AppBundle\Repository;

use AppBundle\Entity\DemandePEE;
use AppBundle\Enum\DemandePEEState;
use AppBundle\Enum\OperationPEEState;
use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\Query;
use Doctrine\ORM\QueryBuilder;
use Symfony\Component\HttpFoundation\Request;
use AppBundle\Entity\User;

/**
 * DemandePeeRepository
 */
class DemandePeeRepository extends EntityRepository
{
    /**
     * Count the number of DemandePEE for each different state and the given OperationPEE
     *
     * @param int $operationPeeId
     *
     * @return array ['state1' => x, 'state2' => y ...] with x, y numbers
     * @throws \ReflectionException
     */
    public function countByState(int $operationPeeId): array
    {
        $qb = $this->getEntityManager()->createQueryBuilder();
        $states = DemandePEEState::getConstants(); // All the possible states

        $qb->select('d.state, count(d.id) as qty')
            ->from(DemandePEE::class, 'd')
            ->leftJoin('d.operationpee', 'o')
            ->groupBy('d.state')
            ->where('o.id = :o_id')
            ->setParameters(array(
                'o_id' => $operationPeeId,
            ));
       
        $results = $qb->getQuery()->getArrayResult();
        $resultsQty = count($results);

        // $results are under the form [0 => ['state' => 'state1', 'qty' => x], 1 => ['state' => 'state2', 'qty' => y]],
        // so we need to change this organization in order to have the one we desire (see the @return method comment)

        foreach ($states as $stateName => $stateValue) {
            $i = 0;
          
            while (isset($results[$i]) && $stateValue !== $results[$i]['state']) {
                $i++;
            }
           
            // $resultsQty === $i means that the state was not found by the query
            $states[$stateValue] = $resultsQty === $i ? 0 : $results[$i]['qty'];
            
            unset($states[$stateName]);
        }
        
        return $states;
    }

    /**
     * Find the first DemandePEE that is waiting for the given OperationPEE
     *
     * @param int $operationPeeId
     *
     * @return DemandePEE|null
     * @throws \Doctrine\ORM\NonUniqueResultException
     */
    public function findWaitingFirst(int $operationPeeId): ?DemandePEE
    {
        $qb = $this->createQueryBuilder('d')
            ->leftJoin('d.operationpee', 'o')
            ->where('o.id = :o_id')
            ->andWhere('d.state = :d_state')
            ->andWhere('o.state = :o_state')
            ->orderBy('d.created', 'asc')
            ->addOrderBy('d.id', 'asc')
            ->setFirstResult(0)
            ->setMaxResults(1)
            ->setParameters([
                'o_id' => $operationPeeId,
                'o_state' => OperationPEEState::OPEN,
                'd_state' => DemandePEEState::WAITING,
            ])
        ;

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

    /**
     * Count the number of DemandePEE not yet validated the user did.
     * Not yet validated means that the status have to be different from WITHDRAWAL and REGISTERED.
     *
     * @param  \AppBundle\Entity\User $user
     * @param  bool $countResults Indicates if the result should be the list of DemandePEE or their quantity
     *
     * @return array|int
     * @throws \Doctrine\ORM\NonUniqueResultException
     */
    public function waitingForValidation(User $user, bool $countResults = false)
    {
        $qb = $this->getEntityManager()->createQueryBuilder();

        if ($countResults) {
            $qb->select('COUNT(d)');
        } else {
            $qb->select('d');
        }

        $qb
            ->from(DemandePEE::class, 'd')
            ->leftJoin('d.operationpee', 'o')
            ->where('d.cuser = :dCuser')
            ->andWhere('d.state NOT IN(:dState)')
            ->andWhere('o.state = :oState')
            ->setParameters([
                'dCuser' => $user,
                'dState' => [DemandePEEState::WITHDRAWAL, DemandePEEState::REGISTERED],
                'oState' => OperationPEEState::OPEN,
            ]);

        return $countResults
            ? $qb->getQuery()->getSingleScalarResult()
            : $qb->getQuery()->getResult()
        ;
    }

    /**
     * Find the DemandePEEs according to the GET parameters of the given Symfony Request object:
     * 
     * @param Request $request 
     * @param bool $returnResults Desired return type
     * 
     * @return QueryBuilder|array The QueryBuilder object (if you want to paginate the results later for example), or the list of results as an array
     */
    public function findFromRequest(Request $request, bool $returnResults = true)
    {
        $qb = $this->getEntityManager()->createQueryBuilder();

        $academy = $request->query->get('academy');
        if ($date = $request->query->get('date')) {
            $date = \DateTime::createFromFormat('d/m/Y', $date);
        }
        $lastname = $request->query->get('lastname');
        $email = $request->query->get('email');
        $dState = $request->query->get('dState'); // State of the DemandePEE
        $oState = $request->query->get('oState'); // State of the OperationPEE

        $qb
            ->select('d, o, u')
            ->from(DemandePEE::class, 'd')
            ->leftJoin('d.operationpee', 'o')
            ->leftJoin('d.cuser', 'u')
            ->orderBy('d.created', 'desc')
            ->addOrderBy('d.id', 'desc');

        if (!empty($oState)) {
            $qb
                ->andWhere('o.state = :oState')
                ->setParameter('oState', $oState);
        }

        if (!empty($lastname)) {
            $qb
                ->andWhere($qb->expr()->orX(
                    $qb->expr()->like(
                        $qb->expr()->upper('u.lastname'),
                        $qb->expr()->upper(':u_lastname')
                    ),
                    $qb->expr()->like(
                        $qb->expr()->upper('u.firstname'),
                        $qb->expr()->upper(':u_firstname')
                    )
                ))
                ->setParameter('u_lastname', "%$lastname%")
                ->setParameter('u_firstname', "%$lastname%");
        }

        if (!empty($email)) {
            $qb
                ->andWhere(
                    $qb->expr()->like(
                        $qb->expr()->upper('u.email'),
                        $qb->expr()->upper(':u_email')
                    )
                )
                ->setParameter('u_email', "%$email%");
        }

        if (!empty($academy)) {
            $qb
                ->andWhere(
                    $qb->expr()->like(
                        $qb->expr()->upper('u.academy'),
                        $qb->expr()->upper(':u_academy')
                    )
                )
                ->setParameter('u_academy', "%$academy%");
        }

        if (!empty($dState)) {
            $qb
                ->andWhere('d.state = :d_state')
                ->setParameter('d_state', $dState);
        }

        if ($date) {
            $qb
                ->andWhere('d.created = :d_created')
                ->setParameter('d_created', $date);
        }

        if ($returnResults) {
            $qb = $qb->getQuery()->getResult();
        }

        return $qb;
    }

    /**
     * Find the PEE applications of an operation. Also retrieve the user who did the registration.
     * @param int $operationPeeId
     * @param array $allowedStates
     * @param  int $hydratationMode Doctrine hydratation mode
     * @return array
     */
    public function findByOperationAndStates(int $operationPeeId, array $allowedStates, int $hydratationMode = Query::HYDRATE_ARRAY): array
    {
        $qb = $this->createQueryBuilder('d');

        $qb
            ->where('d.operationpee = :d_operationpee')
            ->andWhere($qb->expr()->in('d.state', ':d_state'))
            ->orderBy('d.state', 'ASC')
            ->leftJoin('d.cuser', 'u')
            ->addSelect('u')
            ->leftJoin('u.establishment', 'e')
            ->addSelect('e')
            ->setParameters([
                'd_operationpee' => $operationPeeId,
                'd_state' => $allowedStates,
            ])
        ;

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