<?php
declare(strict_types=1);

namespace App\Repository\Network;

use App\Entity\Network\NetworkOrganization;
use App\Entity\Organization\Organization;
use App\Enum\Organization\OrganizationIdsEnum;
use App\Enum\Organization\PrincipalTypeEnum;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\ORM\Query\ResultSetMapping;
use Doctrine\ORM\Query\ResultSetMappingBuilder;
use Doctrine\Persistence\ManagerRegistry;

/**
 * @method NetworkOrganization|null find($id, $lockMode = null, $lockVersion = null)
 * @method NetworkOrganization|null findOneBy(array $criteria, array $orderBy = null)
 * @method NetworkOrganization[]    findAll()
 * @method NetworkOrganization[]    findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
 */
class NetworkOrganizationRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, NetworkOrganization::class);
    }

    /**
     * Vérifie si l'organisation est un dernier parent : possède des enfants mais ces enfants ne possèdent pas d'enfant
     * @param Organization $organization
     * @return bool
     */
    public function isLastParent(Organization $organization): bool
    {
        $sql = sprintf("
        SELECT 
            IF( (SELECT o.id 
                    FROM Organization as o 
                    WHERE o.id=neto.organization_id
                    AND o.principalType IN('%s','%s','%s','%s','%s','%s')) IS NOT NULL ,0,1) AS is_last_parent
        FROM
            NetworkOrganization as neto
        WHERE
            neto.parent_id = %d
            and (neto.endDate is null or neto.endDate = \"0000-00-00\" or neto.endDate > CURDATE())
        GROUP BY is_last_parent
        ORDER BY is_last_parent DESC",
            PrincipalTypeEnum::NATIONAL_FEDERATION(),
            PrincipalTypeEnum::REGIONAL_FEDERATION(),
            PrincipalTypeEnum::LOCAL_FEDERATION(),
            PrincipalTypeEnum::GROUPMENT(),
            PrincipalTypeEnum::DEPARTEMENTAL_FEDERATION(),
            PrincipalTypeEnum::DELEGATION(),
            $organization->getId()
        );

        $rsm = new ResultSetMapping();
        $rsm->addScalarResult('is_last_parent', 'is_last_parent', 'integer');
        $query = $this->getEntityManager()->createNativeQuery($sql, $rsm);
        $result = $query->getResult(\Doctrine\ORM\Query::HYDRATE_ARRAY);

        foreach ($result as $a_tmp) {
            if ($a_tmp['is_last_parent']) {
                return true;
            }
        }
        return false;
    }

    /**
     * Retrouve tous les parents de la structure passée en paramètres
     *
     * @param Organization $organization.
     *
     * @return list<Organization> The organizations.
     */
    public function findAllParents(Organization $organization): array
    {
        $sql = sprintf("
            SELECT *
             FROM Organization o
             JOIN
               (
                 SELECT
                         GROUP_CONCAT(@target:=
                             (
                                 SELECT @source:=GROUP_CONCAT(parent_id)
                                 FROM
                                     NetworkOrganization
                                 WHERE
                                     FIND_IN_SET(organization_id, @source) AND parent_id NOT IN (%s, %s)
                                    AND (endDate is null or endDate = \"0000-00-00\" or endDate > CURDATE()) 
                             )
                         ) AS parents
                 FROM
                     (SELECT
                         @source:=%d,
                         @target:=0
                     ) vars,
                     NetworkOrganization
                 WHERE
                     @source IS NOT NULL
               ) tmp
               WHERE FIND_IN_SET(o.id, parents)
         ",
            OrganizationIdsEnum::_2IOS(),
            OrganizationIdsEnum::OPENTALENT_BASE(),
            $organization->getId()
        );

        $rsm = new ResultSetMappingBuilder($this->getEntityManager());
        $rsm->addRootEntityFromClassMetadata(Organization::class, 'o');
        $query = $this->getEntityManager()->createNativeQuery($sql, $rsm);

        return $query->getResult();
    }
}
