<?php

namespace Modules\ProjetAchat\Repository;

class Hierarchy
{
    
    private $pdo;
    
    public function __construct(\PDO $pdo)
    {
        $this->pdo = $pdo;
    }

    /**
     * @return array<mixed>
    */
    public function getRightOrder(int $idParent, string $typeObject, int $idProjet)
    {
        $sql = "select max(NUM_ORDRE) from DOA_TACHES_H where ID_PARENT = :idParent and TYPE_OBJET = :typeObject and ID_OBJET = :idObjet";

        $arrayReq = [
            ':typeObject' => $typeObject,
            ':idObjet' => $idProjet,
            ':idParent' => $idParent
        ];

        $requete = $this->pdo->prepare($sql);

        $requete->execute($arrayReq);

        return $requete->fetch()[0];
    }
    
    /**
     * @return array<mixed>
    */
    public function getAllChilds(int $idHierarchy)
    {
        $req = "select * from DOA_TACHES_H with (nolock) where ID_PARENT=:id";
        
        $arrayReq = [
            ':id' => $idHierarchy,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return  $requete->fetchAll($this->pdo::FETCH_ASSOC);
    }

    /**
     * @return array<mixed>
    */
    public function hasChilds(string $typeObject, int $idObject, int $idHierarchy)
    {
        $req = "select * from DOA_TACHES_H with (nolock) where TYPE_OBJET=:typeObjet and ID_OBJET=:idObjet and ID_PARENT=:id";

        $arrayReq = [
            ':id' => $idHierarchy,
            ':typeObjet' => $typeObject,
            ':idObjet' => $idObject
        ];

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);

        return  $requete->fetch($this->pdo::FETCH_ASSOC);
    }
    
    public function updateHierarchiefunction(int $id, int $idParent): void
    {
        $req = "select ID_TACHE_H, ID_PARENT, NUM_ORDRE from DOA_TACHES_H with (nolock) where ID_TACHE_H = :idParent";
      
        $arrayReq = [
            ':idParent' => $idParent
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        $resultParent = $requete->fetch($this->pdo::FETCH_ASSOC);
        
        

        $req = "select ID_TACHE_H, ID_PARENT, NUM_ORDRE from DOA_TACHES_H with (nolock) where ID_TACHE_H = :id";
        
        $arrayReq = [
            ':id' => $id
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        $resultID = $requete->fetch($this->pdo::FETCH_ASSOC);

        $req = "update DOA_TACHES_H set ID_PARENT = :idParent, NUM_ORDRE = :newNum where ID_TACHE_H = :id";
        
        $arrayReq = [
            ':id' => $id,
            ':idParent' => ($resultParent['ID_PARENT'] == $id)?$resultID['ID_PARENT']:$resultParent['ID_PARENT'],
            ':newNum' => $resultParent['NUM_ORDRE'],
        ];
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);

        $req ="update DOA_TACHES_H set NUM_ORDRE =TMP from DOA_TACHES_H
            INNER JOIN (
            SELECT ID_PARENT,ID_TACHE_H,NUM_ORDRE,
            ROW_NUMBER() OVER (ORDER BY ID_PARENT,NUM_ORDRE) AS TMP
            FROM DOA_TACHES_H
            where  ID_PARENT = :ID_PARENT
            ) TMPTable ON TMPTable.ID_TACHE_H = DOA_TACHES_H.ID_TACHE_H
            where DOA_TACHES_H.ID_PARENT = :ID_PARENT1";

        $arrayReq = [
            ':ID_PARENT' =>$resultParent['ID_PARENT'],
            ':ID_PARENT1' => $resultParent['ID_PARENT'],
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);

        $req ="update DOA_TACHES_H set NUM_ORDRE =TMP from DOA_TACHES_H
        INNER JOIN (
        SELECT ID_PARENT,ID_TACHE_H,NUM_ORDRE,
        ROW_NUMBER() OVER (ORDER BY ID_PARENT,NUM_ORDRE) AS TMP
        FROM DOA_TACHES_H
        where ID_PARENT = :ID_PARENT
        ) TMPTable ON TMPTable.ID_TACHE_H = DOA_TACHES_H.ID_TACHE_H
        where DOA_TACHES_H.ID_PARENT = :ID_PARENT1";

        $arrayReq = [
        ':ID_PARENT' =>$resultID['ID_PARENT'],
        ':ID_PARENT1' => $resultID['ID_PARENT'],
        ];
    
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
    }

    /**
     * @return array<mixed>
    */
    public function getErrorOrderHierarchie(string $typeObjet, int $idObjet, int $idHierarchy)
    {
        $req = "select * from (
                select NUM_ORDRE, count(NUM_ORDRE) count_Order
                from DOA_TACHES with (nolock)
                where TYPE_OBJET = :typeObjet and ID_OBJET = :idObjet and ID_TACHE_H = :idHierarchy
                group by NUM_ORDRE ) testOrder where count_Order > 1 ";
        
        $arrayReq = [
            ':typeObjet'    => $typeObjet,
            ':idObjet'      => $idObjet,
            ':idHierarchy'  => $idHierarchy
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return  $requete->fetch($this->pdo::FETCH_ASSOC);
    }
    
    public function reorderTasks(string $typeObjet, int $idObjet, int $idParent, int $numOrdre): void
    {
        $sql = 'UPDATE DOA_TACHES_H set NUM_ORDRE = NUM_ORDRE-1 where TYPE_OBJET = :typeObjet and ID_OBJET = :idObjet and ID_PARENT = :idParent AND NUM_ORDRE > :numOrdre';
        
        $arrayReq = [
            ':typeObjet' => $typeObjet,
            ':idObjet' => $idObjet,
            ':idParent' => $idParent,
            ':numOrdre' => $numOrdre,
        ];
        
        $requete = $this->pdo->prepare($sql);
        $requete->execute($arrayReq);
    }

    /**
     * @return array<mixed>
    */
    public function orderIsError(string $typeObjet, int $idObjet, int $idParent)
    {
        $req = "select MAX(NUM_ORDRE) maxNumOrdre, count(NUM_ORDRE) countNumOrdre
        from DOA_TACHES_H with (nolock)
        where TYPE_OBJET = :typeObjet and ID_OBJET = :idObjet and ID_PARENT = :idParent";
        
        $arrayReq = [
            ':typeObjet' => $typeObjet,
            ':idObjet' => $idObjet,
            ':idParent' => $idParent,
        ];
        
        $requete = $this->pdo->prepare($req);
        
        $requete->execute($arrayReq);
        return $requete->fetch();
    }
    
    public function reOrderOtherParent(string $typeObjet, int $idObjet, int $idHierarchy, int $oldParent, int $parent, int $oldPosition, int $position): void
    {
        $req = "update DOA_TACHES_H set NUM_ORDRE = NUM_ORDRE +1 where TYPE_OBJET = :typeObjet and ID_OBJET = :idObjet and ID_PARENT = :parent AND NUM_ORDRE >= :position";
        
        $arrayReq = [
            ':typeObjet' => $typeObjet,
            ':idObjet'   => $idObjet,
            ':parent' => $parent,
            ':position' => $position,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        $req = "update DOA_TACHES_H set NUM_ORDRE = :position, ID_PARENT = :parent where TYPE_OBJET = :typeObjet and ID_OBJET = :idObjet and ID_TACHE_H = :idHierarchy";
        
        $arrayReq = [
            ':typeObjet' => $typeObjet,
            ':idObjet'   => $idObjet,
            ':idHierarchy' => $idHierarchy,
            ':parent' => $parent,
            ':position' => $position,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
    }
    
    public function reOrderSameParent(string $typeObjet, int $idObjet, int $idHierarchy, int $parent, int $oldPosition, int $position): void
    {
        if ($oldPosition<$position) {
            $req = "update DOA_TACHES_H set NUM_ORDRE = NUM_ORDRE - 1 where TYPE_OBJET = :typeObjet and ID_OBJET = :idObjet and ID_PARENT = :parent and NUM_ORDRE > :oldPosition and NUM_ORDRE <= :position";
        } else {
            $req = "update DOA_TACHES_H set NUM_ORDRE = NUM_ORDRE + 1 where TYPE_OBJET = :typeObjet and ID_OBJET = :idObjet and ID_PARENT = :parent and NUM_ORDRE >= :position and NUM_ORDRE < :oldPosition";
        }
        
        $arrayReq = [
            ':typeObjet' => $typeObjet,
            ':idObjet'   => $idObjet,
            ':parent' => $parent,
            ':oldPosition' => $oldPosition,
            ':position' => $position,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        $req = "update DOA_TACHES_H set NUM_ORDRE = :position where TYPE_OBJET = :typeObjet and ID_OBJET = :idObjet and ID_PARENT = :parent and ID_TACHE_H = :idHierarchy";
        
        $arrayReq = [
            ':typeObjet' => $typeObjet,
            ':idObjet'   => $idObjet,
            ':idHierarchy' => $idHierarchy,
            ':parent' => $parent,
            ':position' => $position,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
    }
}
