<?php

namespace AppBundle\Repository;

use AppBundle\Entity\DemandePEE;
use AppBundle\Entity\Interest;
use AppBundle\Entity\OperationPEE;
use AppBundle\Entity\User;
use AppBundle\Enum\DemandePEEState;
use AppBundle\Enum\DemandePEEType;
use AppBundle\Enum\OperationPEEState;
use AppBundle\Enum\PreferenceKm;
use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\Query;
use Doctrine\ORM\QueryBuilder;

/**
 * OperationPeeRepository
 */
class OperationPeeRepository extends EntityRepository
{
    /**
     * Retourne l'ensemble des opérations PEE (actives et pas fermées),
     * ainsi que les demandes PEE associées pour lesquelles les professeurs sont inscrits ou préinscrits.
     *
     * @param int $hydratation Doctrine hydratation mode
     *
     * @return array
     */
    public function findAllWithDemandesPEEInscrits($hydratation = Query::HYDRATE_OBJECT): array
    {
        $qbo = $this->getEntityManager()->createQueryBuilder();

        $qbo
            ->select('o, d, c')
            ->from(OperationPEE::class, 'o')
            ->leftJoin('o.demandepee', 'd', 'with', $qbo->expr()->in('d.state', ':d_states'))
            ->leftJoin('o.company', 'c')
            ->where('o.is_active = :o_is_active')
            ->andWhere('o.state != :o_state')
            ->orderBy('o.academy', 'ASC')
            ->addOrderBy('o.dateVisite', 'ASC')
            ->addOrderBy('o.startTime', 'ASC')
            ->addOrderBy('o.city', 'ASC')
            ->setParameters([
                'o_is_active' => true,
                'o_state' => OperationPEEState::CLOSED,
                'd_states' => [
                    DemandePEEState::REGISTERED,
                    DemandePEEState::PREREGISTERED,
                ],
            ]);

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

    /**
     * Retrieve operations that are open for applications.
     * Also retrieve, for each operation, the number of taken places.
     * 
     * @param \AppBundle\Entity\User $user Allows to also retrieve the applications (DemandePEE) from a specific user
     * @param array $filters Additional filters for the operations. Keys must be OperationPEE's properties, otherwise they will not be taken into account
     * 
     * @return array Format: [['details' => OperationPEE, 'placesTaken' => x], ['details' => OperationPEE, 'placesTaken' => y]]
     */
    public function findOpenForApplication(\AppBundle\Entity\User $user = null, array $filters = array()): array
    {
        $now = new \DateTime();
        
        $qb = $this->getEntityManager()->createQueryBuilder();

        // Retrieve the operations
        $qb
            ->select('o AS details, c')
            ->from(OperationPEE::class, 'o')
            ->leftJoin('o.company', 'c')
            ->where('o.is_active = :o_is_active')
            ->andWhere($qb->expr()->notIn('o.state', ':o_state'))
            //->andWhere('o.publish <= :date_now')
            // ->andWhere('o.unpublish >= :date_now') // Delete unpublish restrictions (demande client)
            ->orderBy('o.company', 'ASC')
            ->addOrderBy('o.site', 'ASC')
            ->addOrderBy('o.dateVisite', 'ASC')
            ->addOrderBy('o.startTime', 'ASC')
            ->groupBy('o, c')
            ->setParameters([
                'o_is_active' => true,
                'o_state' => [
                    OperationPEEState::CLOSED,
                    OperationPEEState::CANCELLED,
                ]
            ]);

        // Retrieve the number of taken seats for each operation
        // d2 is used not to conflict with the optional retrieving of the user aplications (see below)
        $qb
            ->addSelect(
                '(SELECT COUNT(d2)
                FROM AppBundle:DemandePEE d2
                WHERE d2.operationpee = o.id
                AND d2.state IN (:d2_state)) AS placesTaken
            ')
            ->setParameter('d2_state', [
                DemandePEEState::REGISTERED,
                DemandePEEState::PREREGISTERED,
                DemandePEEState::WAITING,
            ]);

        // Filter the operations for the given user
        // DOESN'T WORK... no filter demandePee => See OperationHelper
        if (null !== $user) {
            $qb
                ->addSelect('d')
                ->addGroupBy('d')
                ->leftJoin('o.demandepee', 'd', 'with', 'd.cuser = :d_cuser')
                ->setParameter('d_cuser', $user->getId())
            ;

            if (
                empty($filters) 
                || (
                    !isset($filters['search_from_origin_position'])
                )
            ) {
                // Add Geoloc
                $this->addDistanceConditions($qb, $user, false, false); // Delete limitation distance (demande client) // Distance into Etablishment and company (demande client)
            }
        }

        // Handle additional filters
        if (!empty($filters)) {
            foreach ($filters as $property => $value) {
                if (property_exists(OperationPEE::class, $property)) {
                    $qb
                        ->andWhere('o.' . $property . ' = :o_' . $property)
                        ->setParameter('o_' . $property, $value)
                    ;
                }
            }

            if (
                isset($filters['search_from_origin_position']) 
                && !empty($filters['search_from_origin_position']['latitude']) 
                && !empty($filters['search_from_origin_position']['latitude'])
            ) {
                // Add Geoloc
                $this->addDistanceConditions($qb, null, true, false, array("latitude" => $filters['search_from_origin_position']['latitude'], "longitude" => $filters['search_from_origin_position']['longitude']));
            }
        }
       
        return $qb->getQuery()->getResult();
    }

    /**
     * Find the operations for which a user has a registration
     *
     * @param User $user
     * @param  array $dStates Restrict the list of operations to ones the user registration has one of these states
     * @param  array $oStates Restrict the list of operations to ones with these states
     *
     * @return array
     */
    public function findUserRegistrations(User $user, array $dStates = array(), array $oStates = array()): array
    {
        $qb = $this->getEntityManager()->createQueryBuilder();

        $qb
            ->select('o, d')
            ->from(OperationPEE::class, 'o')
            ->leftJoin('o.demandepee', 'd')
            ->where('d.cuser = :dCuser')
            ->andWhere('d.state NOT IN(:dState)')
            ->orderBy('d.created', 'DESC')
            ->setParameters([
                'dCuser' => $user,
                'dState' => [DemandePEEState::WITHDRAWAL],
            ]);

        if ($dStates) {
            $qb
                ->andWhere('d.state IN(:dStateIn)')
                ->setParameter('dStateIn', $dStates)
            ;
        }

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

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



    /**
     * 
     * @param  User $user
     * @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, array $oStates = [OperationPEEState::OPEN], int $hydratation = Query::HYDRATE_OBJECT)
    {
        $qb = $this->createQueryBuilder('o')
                ->leftJoin(Interest::class, 'i', 'with', 'i.user = :uCurrent and i.operationpee = o')
                ->addSelect('COUNT(i) AS isInterested')
                ->setParameters(array(
                    'uCurrent' => $user,
                ))
                ->groupBy('o, i')
        ;

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

        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, array $oStates = [OperationPEEState::OPEN]): array
    {
        $qb = $this->createQueryBuilder('o')
            ->leftJoin('o.interest', 'i')
            ->where('i.user = :oUser')
            ->orderBy('o.created', 'DESC')
            ->setParameters([
                '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(o.latitude))
            * COS(RADIANS(o.longitude) - RADIANS(:uCurrentLongitude))
            + SIN(RADIANS(:uCurrentLatitude))
            * SIN(RADIANS(o.latitude))
        )';

        $qb->addGroupBy('o');

        $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_CARTO);
            }
            else {
                $qb->setParameter('distance', PreferenceKm::DISTANCE_MAX_CARTO);
            }
        }
    }

    /**
     * Retrieve operations that are open for applications.
     * Also retrieve, for each operation, the number of taken places.
     * 
     * @param \AppBundle\Entity\OperationPEE $user Allows to also retrieve the applications (DemandePEE) from a specific user
     * 
     * @return int
     */
    public function getPlacesTaken(\AppBundle\Entity\OperationPEE $ope): int
    {
        $qb = $this->getEntityManager()->createQueryBuilder();

        // Retrieve the operations
        $qb
            ->select('COUNT(d)')
            ->from(DemandePEE::class, 'd')
            ->where('d.operationpee = :o_id')
            ->andWhere('d.state IN (:d_state)')
            ->setParameters([
                'o_id' => $ope->getId(),
                'd_state' => [
                    DemandePEEState::REGISTERED,
                    DemandePEEState::PREREGISTERED,
                    DemandePEEState::WAITING,
                ],
            ]);

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


/**
 * Retrieve operations that are open for applications.
 * @param \AppBundle\Entity\User $user Allows to also retrieve the applications (DemandePEE) from a specific user
 * @param array $opeIds
 */
    public function findIsCniIsMandatory(\AppBundle\Entity\User $user = null, array $opeIds): array
    {
        $now = new \DateTime();

        $qb = $this->getEntityManager()->createQueryBuilder();

        // Retrieve the operations
        $qb
            ->select('o.id, o.dateVisite')
            ->from(OperationPEE::class, 'o')
            ->where('o.is_active = :o_is_active')
            ->andWhere($qb->expr()->notIn('o.state', ':o_state'))
            //->andWhere('o.publish <= :date_now')
            ->andWhere('o.is_identity_mandatory = true')
            ->andWhere('o.id IN (:opeIds)')
            ->orderBy('o.dateVisite', 'DESC')
            ->setParameters([
                'o_is_active' => true,
                'o_state' => [
                    OperationPEEState::CLOSED,
                    OperationPEEState::CANCELLED,
                ],
                'opeIds' => $opeIds
            ]);

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