<?php

namespace AppBundle\Repository;

use AppBundle\Entity\Interest;
use AppBundle\Entity\OperationITDLC;
use AppBundle\Entity\User;
use AppBundle\Enum\OperationITDLCState;
use AppBundle\Enum\OperationITDLCType;
use AppBundle\Enum\OperationPEEState;
use AppBundle\Enum\PreferenceKm;
use AppBundle\Enum\UserType;
use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\Query;
use Doctrine\ORM\QueryBuilder;

/**
 * OperationItdlcRepository
 */
class OperationItdlcRepository extends EntityRepository
{
    /**
     *
     * @param int $operationId
     * @param int $hydratation Doctrine hydratation mode
     *
     * @return OperationITDLC
     * @throws \Doctrine\ORM\NoResultException
     * @throws \Doctrine\ORM\NonUniqueResultException
     */
    public function findAllWithAvailabilities(int $operationId, int $hydratation = Query::HYDRATE_OBJECT): OperationITDLC
    {
        $qb = $this->createQueryBuilder('o')
            ->leftJoin('o.availability', 'a')
            ->addSelect('a')
            ->where('o.id = :o_id')
            ->setParameters(array(
                'o_id' => $operationId,
            ))
        ;

        return $qb->getQuery()->getSingleResult($hydratation);
    }

    /**
     * Find the operations for which a user has a registration.
     * The operations only the ones that are more recent that last June.
     * @param User $user
     * @param  array $oStates Restrict the list of operations to ones with these states
     * @return array
     */
    public function findUserRegistrations(User $user, array $oStates = []): array
    {
        $lastJune = new \DateTime('first day of June ' . (date('n') >= 6 ? 'this' : 'last') . ' year');
        $userTypePropertyName = UserType::TEACHER === $user->getType() ? 'prof' : 'inge';

        $qb = $this->createQueryBuilder('o')
            ->leftJoin('o.availability', 'a')
            ->where('o.type = :oType')
            ->andWhere("o.$userTypePropertyName = :oUser")
            //->andWhere('a.dateVisite >= :aDateVisite')
            ->orderBy('o.id', 'DESC')
            ->groupBy('o')
            ->setParameters([
                'oType' => OperationITDLCType::OPERATION,
                'oUser' => $user,
                //'aDateVisite' => $lastJune,
            ])
        ;

        if ($oStates) {
            $qb
                ->andWhere('o.state IN(:oState)')
                ->setParameter('oState', $oStates);
        }

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

    /**
     * Count the number of OperationITDLC 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();

        $qb
            ->select($countResults ? 'COUNT(d)' : 'd')
            ->from('AppBundle:DemandePEE', 'd')
            ->leftJoin('d.operationpee', 'o')
            ->where('d.cuser = :dCuser')
            ->andWhere('d.state NOT IN(:dState)')
            ->andWhere('o.state = :oState')
            ->setParameters([
                'dCuser' => $user,
                'dState' => [OperationITDLCState::ONGOING],
                'oState' => OperationPEEState::OPEN,
            ])
        ;

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

    /**
     * 
     * Find the recent applications (not closed) for the given user.
     * @param  User   $user        
     * @param  int    $hydratation 
     * @return array
     */
    public function findRecentApplicationsByUser(User $user, int $hydratation = Query::HYDRATE_OBJECT): array
    {
        $qb = $this->createQueryBuilder('o')
            ->where('o.type = :o_type')
            ->andWhere('o.state != :o_state')
            ->andWhere('o.prof = :o_prof')
            ->orderBy('o.created', 'DESC')
            ->setParameters([
                'o_type' => OperationITDLCType::DEMANDE,
                'o_state' => OperationITDLCState::CLOSED,
                'o_prof' => $user,
            ])
        ;

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


    /**
     *
     * @param User $user
     * @param OperationITDLCType $type
     * @param  array $oStates Restrict the list of operations to ones with these states
     * @param int $hydratation Doctrine hydratation mode
     *
     * @return array
     */
    public function findAllWithUserInterests(User $user, $type = OperationITDLCType::DEMANDE, array $oStates = [OperationITDLCState::NEW], int $hydratation = Query::HYDRATE_OBJECT)
    {
        $qb = $this->createQueryBuilder('o')
            ->select('o AS demande, u')
            ->leftJoin('o.prof', 'u')
            ->leftJoin(Interest::class, 'i', 'with', 'i.user = :uCurrent and i.operationitdlc = o')
            ->addSelect('COUNT(i) AS isInterested')
            ->where('o.type = :oType')
            ->andWhere('u.enabled = :uEnabled')
            ->andWhere('u.is_active = :uIsActive')
            ->andWhere('u.is_available = :uIsAvailable')
            ->setParameters(array(
                'oType' => $type,
                'uCurrent' => $user,
                'uEnabled' => true,
                'uIsActive' => true,
                'uIsAvailable' => true,
            ))
            ->groupBy('o, u, i')
        ;

        if ($oStates) {
            $qb
                ->andWhere('o.state IN(:oState)')
                ->setParameter('oState', $oStates);
        }

        self::addDistanceConditions($qb, $user, true, false); // Distance into Etablishment and company (demande client)
        
        return $qb->getQuery()->getResult($hydratation);
    }

    /**
     * 
     * @param User $user
     * @param OperationITDLCType $type
     * @param  array $oStates Restrict the list of operations to ones with these states
     * @return array
     */
    public function findUserInterests(User $user, $type = OperationITDLCType::DEMANDE, array $oStates = [OperationITDLCState::NEW, OperationITDLCState::ONGOING]): array
    {
        $qb = $this->createQueryBuilder('o')
            ->leftJoin('o.interest', 'i')
            ->where('o.type = :oType')
            ->andWhere("i.user = :oUser")
            ->orderBy('o.created', 'DESC')
            ->setParameters([
                'oType' => $type,
                'oUser' => $user,
            ])
        ;

        if ($oStates) {
            $qb
                ->andWhere('o.state IN(:oState)')
                ->setParameter('oState', $oStates);
        }

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

     /**
      *
      * @param User $user
      * @param OperationITDLCType $type
      * @param  array $oStates Restrict the list of operations to ones with these states
      * @return array
      */
    public function findUserMER(User $user, $type = OperationITDLCType::DEMANDE, array $oStates = [OperationITDLCState::NEW, OperationITDLCState::ONGOING]): array
    {
        $qb = $this->createQueryBuilder('o')
            ->where('o.type = :oType')
            ->andWhere("o.inge = :oUser")
            ->orderBy('o.created', 'DESC')
            ->setParameters([
                'oType' => $type,
                'oUser' => $user,
            ])
        ;

        if ($oStates) {
            $qb
                ->andWhere('o.state IN(:oState)')
                ->setParameter('oState', $oStates);
        }

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


    /**
     * Add query conditions related to the distance between user and operation
     * @param QueryBuilder &$qb           
     * @param User &$user         
     * @param bool $limitDistance
     * @param bool $usePersonalCoordinates Indicates if the distance should be calculated from the work or personal location
     * @param array $useSpecificPosition
     */
    public static function addDistanceConditions(QueryBuilder &$qb, User $user = null, bool $limitDistance = true, bool $usePersonalCoordinates = false, array $useSpecificPosition = array("latitude" => null, "longitude" => null)): void
    {
        // Calculation of the dustance between 2 points on the planet
        $distanceCalculation = '6371 * ACOS(
            COS(RADIANS(:uCurrentLatitude))
            * COS(RADIANS(p.latitude))
            * COS(RADIANS(p.longitude) - RADIANS(:uCurrentLongitude))
            + SIN(RADIANS(:uCurrentLatitude))
            * SIN(RADIANS(p.latitude))
        )';

        $qb->leftJoin('o.prof', 'p')
            ->addGroupBy('p');

        $qb->addSelect($distanceCalculation . ' AS distance')
            ->orderBy('distance', 'ASC');

        if (!empty($useSpecificPosition['latitude']) && !empty($useSpecificPosition['longitude'])) {
            $qb->setParameter('uCurrentLatitude', $useSpecificPosition['latitude'])
                ->setParameter('uCurrentLongitude', $useSpecificPosition['longitude']);   
        }
        elseif ($usePersonalCoordinates) {
            $qb->setParameter('uCurrentLatitude', $user->getPersonalLatitude())
                ->setParameter('uCurrentLongitude', $user->getPersonalLongitude());
        }
        else {
            $qb->setParameter('uCurrentLatitude', $user->getLatitude())
                ->setParameter('uCurrentLongitude', $user->getLongitude());
        }

        if ($limitDistance) {
            $qb->having($distanceCalculation . ' <= :distance');

            if ($user instanceof User) {
                $qb->setParameter('distance', $user->getTransportKm() ?? PreferenceKm::DISTANCE_MAX_INTERVENANTS);
            }
            else {
                $qb->setParameter('distance', PreferenceKm::DISTANCE_MAX_INTERVENANTS);
            }
        }
    }


    /**
     * Return all operations to be closed
     */
    public function findAllOperationsToBeClosed()
    {
        $timeToBeClosed = new \DateTime('yesterday');

        $qb = $this->createQueryBuilder('o')
            ->select('o, p, i')
            ->join('o.availability', 'a')
            ->join('o.prof', 'p')
            ->join('o.inge', 'i')
            ->where('o.state = :oState')
            ->andWhere('o.type = :oType')
            ->andWhere('a.dateVisite <= :aDateVisite')
            ->setParameters(array(
                'oType' => OperationITDLCType::OPERATION,
                'oState' => OperationITDLCState::ONGOING,
                'aDateVisite' => $timeToBeClosed
            ))
        ;
       
        return $qb->getQuery()->getResult();
    }


    /**
     * Find all operations that will take place in 7 days
     * @return mixed
     */
    public function findComingSoonOperations()
    {
        $qb = $this->createQueryBuilder('o')
            ->select('o, p')
            ->join('o.prof', 'p')
            ->join('o.inge', 'i')
            ->join('o.availability', 'a')
            ->where('o.state = :oState')
            ->andWhere('o.type = :oType')
            ->andWhere('a.dateVisite = :aDateVisite')

        ;

        $j7 = date('Y-m-d', strtotime('+7days'));
        $qb->setParameters(array(
                'oType' => OperationITDLCType::OPERATION,
                'oState' => OperationITDLCState::ONGOING,
                'aDateVisite' => $j7
            ));

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

    /**
     * Find demandes without MER after 30 days
     * @return mixed
     */
    public function findDemandeWithoutMer()
    {
        $qb = $this->createQueryBuilder('o')
            ->select('o, p')
            ->join('o.prof', 'p')
            ->where('o.state = :oState')
            ->andWhere('o.type = :oType')
            ->andWhere('o.created = :oCreated')

        ;

        $j30 = date('Y-m-d', strtotime('-30days'));
        $qb->setParameters(array(
                'oType' => OperationITDLCType::DEMANDE,
                'oCreated' => $j30,
                'oState' => OperationITDLCState::NEW
            ));

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