<?php

namespace AppBundle\Repository;

use \PDO;

use AppBundle\Entity\Preference;
use AppBundle\Entity\User;
use AppBundle\Enum\OperationITDLCState;
use AppBundle\Enum\OperationITDLCType;
use AppBundle\Enum\PreferenceKm;
use AppBundle\Enum\UserType;
use AppBundle\Helper\UserHelper;
use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\Query;
use Doctrine\ORM\QueryBuilder;
use Doctrine\ORM\Query\ResultSetMapping;
use Doctrine\ORM\NativeQuery;
use Doctrine\DBAL\Connection;
use Symfony\Component\HttpFoundation\Request;

/**
 * UserRepository
 *
 * This class was generated by the Doctrine ORM. Add your own custom
 * repository methods below.
 */
class UserRepository extends EntityRepository
{
    /**
     * @DEPRECATED
     * Retourne la liste des académies des professeurs
     * La liste est ordonnée alphabétiquement
     *
     * @param int $hydratation Mode d'hydratation de Doctrine
     *
     * @return array Liste des académies
     */
    public function findAcademies(int $hydratation = Query::HYDRATE_OBJECT): array
    {
        // $qb = $this->getEntityManager()->createQueryBuilder();
        // $qb
        //     ->select('u.academy')
        //     ->from('AppBundle:User', 'u')
        //     ->where('u.type = :u_type')
        //     ->andWhere($qb->expr()->isNotNull('u.academy'))
        //     ->groupBy('u.academy')
        //     ->orderBy('u.academy', 'ASC')
        //     ->setParameter('u_type', UserType::TEACHER);

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

        return UserHelper::getAcademyList();
    }

    /**
     * Find the Users according to the given parameters from a Symfony Request object:
     * - User type is retrieved from the attributes
     * - Registration dates (min & max) and academy are retrived from the POST then GET parameters
     *
     * @param Request $request
     * @param bool $returnResults Desired return type
     * @param bool $count Indicates if we want the list of results of their number
     *
     * @return QueryBuilder|User[]|int The QueryBuilder object (if you want to paginate the results later for example), or the list of users as an array
     * @throws \Doctrine\ORM\NonUniqueResultException
     */
    public function findFromRequest(Request $request, bool $returnResults = true, bool $count = false)
    {
        $academy = $request->request->get('academy') ?? $request->query->get('academy');
        if ($dateMin = $request->query->get('datemin')) {
            $dateMin = \DateTime::createFromFormat('d/m/Y', $dateMin);
        }
        if ($dateMax = $request->query->get('datemax')) {
            $dateMax = \DateTime::createFromFormat('d/m/Y', $dateMax);
        }
        $type = $request->attributes->get('type');

        // Create base query
        $qb = $this->getEntityManager()->createQueryBuilder();
        $qb
            ->select($count ? $qb->expr()->count('u') : 'u')
            ->from(User::class, 'u')
            ->leftJoin('u.establishment', 'e')
        ;

        if (!$count) {
            $qb
                ->addSelect('e')
                ->orderBy('u.created', 'DESC')
            ;
        }

        if ($type) {
            $qb->andWhere('u.type = :u_type')
                ->setParameter('u_type', $type);
        }
        if ($dateMin) {
            $qb->andWhere('u.created >= :u_datemin')
                ->setParameter('u_datemin', $dateMin);
        }
        if ($dateMax) {
            $qb->andWhere('u.created <= :u_datemax')
                ->setParameter('u_datemax', $dateMax);
        }

        if (UserType::TEACHER === $type && $academy) {
            $qb->andWhere('u.academy = :u_academy')
                ->setParameter('u_academy', $academy);
        }

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

            $qb = $count ? $query->getSingleScalarResult() : $query->getResult();
        }

        return $qb;
    }

    /**
     * Find the closest user of the opposite type:
     * - List of engineers, if the user is a teacher
     * - List of teachers, if the user is an engineer
     * The maximum distance allowed is taken from the system.
     * The query also retrieve the fact that the user is a preferred one or not.
     *
     * @param User $user
     * @param array $filters Additional filters for the user. Keys must be User's properties, otherwise they will not be taken into account
     * @param bool $usePersonalCoordinates
     * @param int $limit Max users return
     * @return array Format: [[0 => User, 'isPreferred' => 1, 'distance' => 123.456], [0 => User, 'isPreferred' => 0, 'distance' => 9.8765]]
     */
    public function findClosestWithPartners(User $user, array $filters = [], bool $usePersonalCoordinates = false, int $limit = 0): array
    {
        $conn = $this->getEntityManager()->getConnection();

        $fieldsNames = [
            'latitude' => $usePersonalCoordinates ? 'personalLatitude' : 'latitude',
            'longitude' => $usePersonalCoordinates ? 'personalLongitude' : 'longitude',
        ];
        
        // Create raw query
        
        $sql = "SELECT u.id, COUNT(p0_.id) AS isPreferred, (select 1 where sp1_.partner_soundex = SOUNDEX(u.establishmentName) or sp1_.partner_soundex = SOUNDEX(u.establishmentNameOther)) as priority, ";
        
        $sql .='6371 * ACOS(COS(RADIANS(:uCurrentLatitude)) * COS(RADIANS('. $fieldsNames['latitude'] .')) * COS(RADIANS('. $fieldsNames['longitude'] .') - RADIANS(:uCurrentLongitude)) + SIN(RADIANS(:uCurrentLatitude)) * SIN(RADIANS('. $fieldsNames['latitude'] .'))) AS distance, ';

        $sql .="u.establishmentName,
       u.establishmentNameOther, ";

        $sql .= "sp1_.partner_soundex,
        SOUNDEX(u.establishmentName) AS soundex_entreprise_name,
        SOUNDEX(u.establishmentNameOther) AS soundex_entreprise_name_other, ";
        
        $sql .= "u.username, u.username_canonical, u.email, u.email_canonical, u.enabled, u.type, u.firstname, u.lastname, u.classroom, u.personalLongitude, u.personalLatitude, u.nbContribution, u.transportKm, u.photo, u.latitude, u.longitude, u.jobOther, u.establishmentName, u.establishmentCity ";
    
        $sql .=" FROM fos_user u ";
    
        $sql .=" LEFT JOIN preference p0_ ON (p0_.mainUser=:uCurrent AND p0_.selectedUser=u.id) ";
    
        $sql .=" LEFT JOIN establishment e2_ ON u.establishment_id = e2_.id AND e2_.type IN ('company', 'partner') ";
    
        $sql .=" LEFT JOIN (SELECT SOUNDEX(value) partner_soundex from config where domain=:domain and key=:key) AS sp1_ ON  sp1_.partner_soundex  = SOUNDEX(u.establishmentName) or sp1_.partner_soundex = SOUNDEX(u.establishmentNameOther) ";
    
         $sql .=" WHERE u.enabled = :uEnabled AND u.is_active = :uIsActive AND u.is_available = :uIsAvailable AND u.type = :uType AND ((u.nbContribution > 0 OR u.nbContribution IS NULL)) ";
    
        
         // Add special conditions according to the user type
        if (UserType::TEACHER === $user->getType()) {
            $sql .= ' AND (u.nbContribution > :uNbContribution OR u.nbContribution IS NULL)';
        } else {
            $sql .= ' AND u.id IN (SELECT IDENTITY(o.prof) from operationitdlc where o.state=:oState AND o.type=:oType) AS o';
        }

        // Add filters if any
        if ($filters) {
            if (isset($filters['classroom'])) {
                $sql .= ' AND u.classroom LIKE %:uClassroom%)';
            }
        }

        $sql .=" GROUP BY sp1_.partner_soundex, u.username, u.username_canonical, u.email, u.email_canonical, u.enabled, u.salt, u.password, u.last_login, u.confirmation_token, u.password_requested_at, u.roles, u.id, u.import_id_professor, u.import_id_enginner, u.type, u.clearpassword, u.firstname, u.lastname, u.created, u.is_active, u.birthday, u.phone, u.mobile, u.classroom, u.jobcategory, u.job, u.jobOther, u.bossEmail, u.establishmentType, u.establishmentEducation, u.establishmentSectorOther, u.establishmentName, u.establishmentNameOther, u.establishmentEmail, u.establishmentAddress, u.establishmentZipcode, u.establishmentCity, u.personalAddress, u.personalZipcode, u.personalCity, u.personalLatitude, u.personalLongitude, u.briefing, u.note, u.newsletter, u.nbContribution, u.formation, u.formationType, u.detailFormation, u.aboutMe, u.publicTransport, u.professionalDiscover, u.transportTime, u.transportKm, u.academy, u.rep, u.isValideInfo, u.sendCard, u.identityCard1, u.identityCard2, u.identitycardvalidity, u.photograph, u.photo, u.shoeSize, u.latitude, u.longitude, u.honorific, u.expectations, u.is_available, u.number_no_answer, u.availabilities, u.establishment_id, u.establishment_sector_id, p0_.id, p0_.mainUser, p0_.selectedUser, e2_.id, e2_.name, e2_.description, e2_.website, e2_.position, e2_.logoPath, e2_.logoSize, e2_.created_at, e2_.updated_at ";
    
        $sql .=" HAVING 6371 * ACOS(COS(RADIANS(:uCurrentLatitude)) * COS(RADIANS(" . $fieldsNames['latitude'] . ")) * COS(RADIANS(" . $fieldsNames['longitude'] . ") - RADIANS(:uCurrentLongitude)) + SIN(RADIANS(:uCurrentLatitude)) * SIN(RADIANS( " . $fieldsNames['latitude'] . "))) <= :uDistance ";
    
        $sql .=" ORDER BY priority asc, distance ASC";
        if (!empty($limit)) {
            $sql .=" LIMIT " . $limit;
        }

        $stmt = $conn->prepare($sql);
        $stmt->bindValue('domain', 'partenaire_itdlc');
        $stmt->bindValue('key', 'name');
        $stmt->bindValue('uCurrent', $user->getId());
        $stmt->bindValue('uEnabled', true);
        $stmt->bindValue('uIsActive', true);
        $stmt->bindValue('uIsAvailable', true);
        $stmt->bindValue('uType', UserType::TEACHER === $user->getType() ? UserType::ENGINEER : UserType::TEACHER);
        $stmt->bindValue('uCurrentLatitude', $user->getLatitude());
        $stmt->bindValue('uCurrentLongitude', $user->getLongitude());
        $stmt->bindValue('uDistance', $user->getTransportKm() ?? PreferenceKm::DISTANCE_MAX_INTERVENANTS);
       

        
        if (UserType::TEACHER === $user->getType()) {
            $stmt->bindValue('uNbContribution', 0);
        } else {
            $stmt->bindValue('oState', OperationITDLCState::NEW);
            $stmt->bindValue('oType', OperationITDLCType::DEMANDE);
        }

        // Add filters if any
        if ($filters) {
            if (isset($filters['classroom'])) {
                $stmt->bindValue('uClassroom', '%' . $filters['classroom'] . '%');
            }
        }
        
       //echo "<!-- " . $sql . " -->";

        $stmt->execute();
        return $stmt->fetchAll();
    }

     /**
     * Find the closest user of the opposite type:
     * - List of engineers, if the user is a teacher
     * - List of teachers, if the user is an engineer
     * The maximum distance allowed is taken from the system.
     * The query also retrieve the fact that the user is a preferred one or not.
     *
     * @param User $user
     * @param array $filters Additional filters for the user. Keys must be User's properties, otherwise they will not be taken into account
     * @param bool $usePersonalCoordinates
     * @param int $limit Max users return
     * @return array Format: [[0 => User, 'isPreferred' => 1, 'distance' => 123.456], [0 => User, 'isPreferred' => 0, 'distance' => 9.8765]]
     */
    public function findClosestOthers(User $user, array $filters = [], bool $usePersonalCoordinates = false, int $limit = 0): array
    {
        // Create base query
        $qb = $this->createQueryBuilder('u')
            ->leftJoin(Preference::class, 'p', 'with', 'p.mainUser = :uCurrent and p.selectedUser = u')
            ->addSelect('COUNT(p) AS isPreferred')
            ->leftJoin('u.establishment', 'e')
            ->addSelect('e')
            ->where('u.enabled = :uEnabled')
            ->andWhere('u.is_active = :uIsActive')
            ->andWhere('u.is_available = :uIsAvailable')
            ->andWhere('u.type = :uType')
            ->groupBy('u, p, e')
            ->setParameters([
                'uCurrent' => $user,
                'uEnabled' => true,
                'uIsActive' => true,
                'uIsAvailable' => true,
                'uType' => UserType::TEACHER === $user->getType() ? UserType::ENGINEER : UserType::TEACHER,
            ])
        ;

        if (!empty($limit)) {
            $qb->setFirstResult(0)
                ->setMaxResults($limit);
        }
        
        // Add distance conditions
        
        self::addDistanceConditions($qb, $user, $filters['distance'] ?? true, $usePersonalCoordinates);

        // Add special conditions according to the user type
        if (UserType::TEACHER === $user->getType()) {
            $qb
                ->andWhere('(u.nbContribution > :uNbContribution OR u.nbContribution IS NULL)')
                ->setParameter('uNbContribution', 0)
            ;
        } else {
            $qbSub = $this->getEntityManager()->createQueryBuilder();
            $qbSub
                ->select('IDENTITY(o.prof)')
                ->from('AppBundle:OperationITDLC', 'o')
                ->where('o.state = :oState')
                ->andWhere('o.type = :oType')
            ;

            $qb
                ->andWhere($qb->expr()->in('u.id', $qbSub->getDQL()))
                ->setParameter('oState', OperationITDLCState::NEW)
                ->setParameter('oType', OperationITDLCType::DEMANDE)
            ;
        }

        // Add filters if any
        if ($filters) {
            if (isset($filters['classroom'])) {
                $qb
                    ->andWhere($qb->expr()->like('u.classroom', ':uClassroom'))
                    ->setParameter('uClassroom', '%' . $filters['classroom'] . '%');
            }
        }

        /*
        $sql = $qb->getQuery()->getSql();
        echo "<!--" . $sql . "-->";
        $param_values = '';
        foreach ($qb->getQuery()->getParameters() as $index => $param) {
            $param_values .= $param->getName() . ": " . $param->getValue().', ';
        }
        echo "<!--";

        echo rtrim($param_values, ', ');
        echo "-->";
        */
        return $qb->getQuery()->getResult();
    }

    /**
     * Add query conditions related to the distance between 2 users
     * @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
     */
    public static function addDistanceConditions(QueryBuilder &$qb, User $user, bool $limitDistance = true, bool $usePersonalCoordinates = false): void
    {
        $fieldsNames = [
            'latitude' => $usePersonalCoordinates ? 'personalLatitude' : 'latitude',
            'longitude' => $usePersonalCoordinates ? 'personalLongitude' : 'longitude',
        ];

        // Calculation of the dustance between 2 points on the planet
        $distanceCalculation = '6371 * ACOS(
            COS(RADIANS(:uCurrentLatitude))
            * COS(RADIANS(u.'. $fieldsNames['latitude'] .'))
            * COS(RADIANS(u.'. $fieldsNames['longitude'] .') - RADIANS(:uCurrentLongitude))
            + SIN(RADIANS(:uCurrentLatitude))
            * SIN(RADIANS(u.'. $fieldsNames['latitude'] .'))
        )';

        
        $qb->addSelect($distanceCalculation . ' AS distance')
            ->orderBy('distance', 'ASC')
            ->setParameter('uCurrentLatitude', $user->getLatitude())
            ->setParameter('uCurrentLongitude', $user->getLongitude());

        if ($limitDistance) {
            $qb->having($distanceCalculation . ' <= :distance')
                ->setParameter('distance', $user->getTransportKm() ?? PreferenceKm::DISTANCE_MAX_INTERVENANTS);
        }
        else
        {
            $qb->having($distanceCalculation . ' <= :distance')
            ->setParameter('distance', PreferenceKm::DISTANCE_MAX_CARTO);
        }
    }


    /**
     * Return all engineers without operations OR MER
     * @param integer $days
     * @return array user's emails
     */
    public function findAllWithoutSolicitation($days = 30): array
    {
        $timeWithoutSolicitations = date('Y-m-d', strtotime("-".$days."days"));

         $qb = $this->getEntityManager()->createQueryBuilder();
         $qb
             ->select('u')
             ->from('AppBundle:User', 'u')
             ->leftJoin('u.merITDLC', 'm')
             ->leftJoin('u.ingeitdlc', 'o')
             ->where('u.type = :u_type')
             ->andWhere('o.id is NULL')
             ->andWhere('m.id is NULL')
             ->andWhere('u.created = :timeWithoutSolicitations')
             ->groupBy('u.id')
             ->setFirstResult(0)
             ->setMaxResults(5)
         ;

        $qb->setParameter("timeWithoutSolicitations", $timeWithoutSolicitations)
            ->setParameter('u_type', UserType::ENGINEER);
//
         return $qb->getQuery()->getResult();
    }


    /**
     * Return all engineers to de desactivate
     *
     * @return array of users
     */
    public function findAllUsersToBeDesactivate(): array
    {
         $qb = $this->getEntityManager()->createQueryBuilder()
             ->select('u.id, u.firstname, u.lastname')
             ->from('AppBundle:User', 'u')
             ->where('u.type = :u_type')
             ->andWhere('u.numberNoAnswer = :uNumberNoAnswer')
             ->andWhere('u.is_active = :uIsActive')
         ;

        $qb->setParameter('u_type', UserType::ENGINEER)
            ->setParameter('uNumberNoAnswer', 2)
            ->setParameter('uIsActive', true);
//
         return $qb->getQuery()->getResult();
    }

    /**
     * Desactivate all engineers to de desactivate because they have 2 no answer
     *
     * @return integer
     */
        public function desactivateEngineerWith2NoAnswer()
        {
            return $this->getEntityManager()->createQueryBuilder()
                ->update('AppBundle:User', 'u')
                ->set('u.numberNoAnswer', ':reinitNumberNoAnswer')
                ->set('u.is_active', ':desactivateUser')
                ->where('u.type = :u_type')
                ->andWhere('u.numberNoAnswer = :uNumberNoAnswer')
                ->andWhere('u.is_active = :uIsActive')
                ->setParameter('u_type', UserType::ENGINEER)
                ->setParameter('uNumberNoAnswer', 2)
                ->setParameter('uIsActive', true)
                ->setParameter('desactivateUser', false)
                ->setParameter('reinitNumberNoAnswer', 0)
                ->getQuery()
                ->execute();
        }

        /**
         * Return all engineers
         *
         * @return array of users
         */
            public function searchUsers($search): array
            {
                $qb = $this->getEntityManager()->createQueryBuilder()
                    ->select('u.id, CONCAT(upper(u.lastname), \' \', u.firstname) as value, CONCAT(upper(u.lastname), \' \', u.firstname) as label')
                    ->from('AppBundle:User', 'u');

                $qb->where('u.type = :u_type');
                $qb->andWhere(
                    $qb->expr()->orX(
                        $qb->expr()->like('u.email', ':search'),
                        $qb->expr()->like(
                            $qb->expr()->concat($qb->expr()->concat('upper(u.lastname)', $qb->expr()->literal(' ')), 'upper(u.firstname)'),
                            ":search"
                        ),
                        $qb->expr()->like(
                            $qb->expr()->concat($qb->expr()->concat('upper(u.firstname)', $qb->expr()->literal(' ')), 'upper(u.lastname)'),
                                ":search"
                        )
                    )
                );
                $qb->setParameter('u_type', UserType::TEACHER);
                $qb->setParameter('search', strtoupper($search));
                $qb->orderBy('u.lastname', 'ASC');


                $qb->setFirstResult(0)->setMaxResults(10);
        //
                return $qb->getQuery()->getResult();
            }
}
