<?php

namespace Modules\ProjetAchat\Repository;

use Helpers\Outil\Sql;
use Helpers\QueryBuilder\AbsRepository;

class Task extends AbsRepository
{

    /**
     * @return array<mixed>
    */
    public function getTask(int $idTask)
    {
        $req = "SELECT dt.TYPE_OBJET, dt.LIB, dt.DESCR, dt.RESPONSABLE, dt.DUREE_PREV, dt.DATE_DEB, dt.DATE_FIN, dt.SOUMISE_ALERTE, dt.DELAI_PREAVIS, dt.TYPE_ALERTE, dea.LIB as ETAT_AV_LIB, dt.CHAP_PROC, dp.LIB as PROC_LIB
            FROM DOA_TACHES dt with (nolock)
            LEFT OUTER JOIN DOAR_ETAT_AV dea with (nolock) ON dea.ID = dt.ID_ETAT_AV
            LEFT OUTER JOIN DOAR_PROC dp with (nolock) ON dp.ID = dt.ID_PROC
            WHERE dt.ID_TACHE = :idTask";

        $requete = $this->pdo->prepare($req);
        $requete->execute([
            ':idTask' => $idTask
        ]);

        return $requete->fetch();
    }

    public function createTask(array $payload): void
    {
        $req =
            "INSERT INTO DOA_TACHES(
                TYPE_OBJET,
                ID_OBJET,
                ID_TACHE_H,
                LIB,
                DESCR,
                RESPONSABLE,
                DATE_DEB, DATE_FIN,
                TERMINEE,
                DUREE_PREV,
                SOUMISE_ALERTE,
                DELAI_PREAVIS,
                TYPE_ALERTE,
                ID_PROC,
                CHAP_PROC,
                ID_ETAT_AV,
                NUM_ORDRE
            ) values(
                :typeObjet,
                :idObjet,
                :IdTacheH,
                :lib,
                :descr,
                :owner,
                :startDate, 
                :endDate,
                ".sql::setBoolSql($payload['TERMINEE']).", 
                :dureePrev, 
                ".sql::setBoolSql($payload['SOUMISE_ALERTE']).", 
                :delaiPreavis,
                :alertType,
                :idProc,
                :chapProc,
                :idProgressState,
                :numOrder
            )";

        $arrayReq = [
            ':typeObjet'        => $payload['TYPE_OBJET'],
            ':idObjet'          => $payload['ID_OBJET'],
            ':IdTacheH'         => $payload['ID_TACHE_H'],
            ':lib'              => $payload['LIB'],
            ':descr'            => $payload['DESCR'],
            ':owner'            => $payload['RESPONSABLE'],
            ':startDate'        => $payload['DATE_DEB'],
            ':endDate'          => $payload['DATE_FIN'],
            ':dureePrev'        => $payload['DUREE_PREV'],
            ':delaiPreavis'     => $payload['DELAI_PREAVIS'],
            ':alertType'        => $payload['TYPE_ALERTE'],
            ':idProc'           => $payload['ID_PROC']??'',
            ':chapProc'         => $payload['CHAP_PROC'],
            ':idProgressState'  => $payload['ID_ETAT_AV'],
            ':numOrder'         => $payload['NUM_ORDRE'],
        ];

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

    public function editTask(array $payload, Int $idTask): void
    {
        $req = "UPDATE DOA_TACHES
                SET LIB = :lib,
                DESCR = :descr,
                RESPONSABLE = :owner,
                DUREE_PREV = :dureePrev,
                SOUMISE_ALERTE = ".sql::setBoolSql($payload['SOUMISE_ALERTE']).",
                DELAI_PREAVIS = :delaiPreavis,
                TYPE_ALERTE = :alertType,
                ID_PROC = :idProc,
                CHAP_PROC = :chapProc,
                ID_ETAT_AV = :idProgressState,
                DATE_DEB = :dateDeb,
                DATE_FIN = :dateFin
                WHERE ID_TACHE = :idTask";

        $arrayReq = [
            ':lib'              => $payload['LIB'],
            ':descr'            => $payload['DESCR'],
            ':owner'            => $payload['RESPONSABLE'],
            ':dureePrev'        => $payload['DUREE_PREV'],
            ':delaiPreavis'     => $payload['DELAI_PREAVIS'],
            ':alertType'        => $payload['TYPE_ALERTE'],
            ':idProc'           => $payload['ID_PROC']??'',
            ':chapProc'         => $payload['CHAP_PROC'],
            ':idProgressState'  => $payload['ID_ETAT_AV'],
            ':dateDeb'          => $payload['DATE_DEB'],
            ':dateFin'          => $payload['DATE_FIN'],
            ':idTask'           => $idTask,
        ];

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

    public function deleteTask(Int $idTask): void
    {
        $req = "DELETE FROM DOA_TACHES WHERE ID_TACHE = :idTask";

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

        $sql->execute([
            ":idTask" => $idTask
        ]);
    }

    public function countTasksInHierarchy(Int $idHierarchy): int
    {
        $req = "SELECT count(ID_TACHE) FROM DOA_TACHES with (nolock) WHERE ID_TACHE_H = :idHierarchy";

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

        $sql->execute([
            ":idHierarchy" => $idHierarchy
        ]);

        return (int) $sql->fetch()[0];
    }

    public function getLatestTaskEndDate(Int $idProject): string
    {
        $req = "SELECT max(DATE_FIN) FROM DOA_TACHES with (nolock) WHERE ID_OBJET = :idProject";

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

        $sql->execute([
            ":idProject" => $idProject
        ]);

        return $sql->fetch()[0];
    }

    /**
     * @return array<mixed>
    */
    public function getTasksLinkedToHierarchy(Int $idHierarchy)
    {
        $req = "select *
                from DOA_TACHES with (nolock)
                LEFT JOIN HASTUTIL util with (nolock) on util.CODE = RESPONSABLE
                WHERE ID_TACHE_H = :idTacheH
                ORDER BY NUM_ORDRE ASC";
        
        $arrayReq = [
            ':idTacheH' => $idHierarchy
        ];

        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * @return array<mixed>
    */
    public function getProcedures()
    {
        $req = "select * from DOAR_PROC with (nolock)";

        return $this->fetchAssocAll($req, []);
    }

    /**
     * @return array<mixed>
    */
    public function getProgressStates()
    {
        $req = "select * from DOAR_ETAT_AV with (nolock) order by NUM_ORDRE ASC";

        return $this->fetchAssocAll($req, []);
    }

    /**
     * @return array<mixed>
    */
    public function getRightOrder(Int $idTacheH, string $typeObject)
    {
        $sql = "select max(NUM_ORDRE) from DOA_TACHES with (nolock) where ID_TACHE_H = :idTacheH and TYPE_OBJET = :typeObject";

        $arrayReq = [
            ':typeObject' => $typeObject,
            ':idTacheH' => $idTacheH
        ];

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

        $requete->execute($arrayReq);

        return $requete->fetch()[0]??'0';
    }

    /**
     * @return array<mixed>
    */
    public function getActionRightOrder(int $idTasks)
    {
        $sql = "select max(NUM_ORDRE) from DOA_ACTIONS with (nolock) where ID_TACHE = :idTache";
        
        $arrayReq = [
            ':idTache' => $idTasks
        ];
        
        $requete = $this->pdo->prepare($sql);
        
        $requete->execute($arrayReq);
        
        return $requete->fetch()[0]??'0';
    }

    /**
     * @return array<mixed>
    */
    public function getListAction(int $idTasks)
    {
        $req = "select * from DOA_ACTIONS with (nolock) where ID_TACHE = :idTache order by NUM_ORDRE";
        
        $arrayReq = [
            ':idTache' => $idTasks
        ];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }

    public function isExecutable(int $idAction, string $userCode): bool
    {
        $req = "SELECT ID_DOA
            FROM DOA_ACTIONS_RACI with(nolock)
            WHERE UPPER(CODE_USER) = UPPER(:userCode)
            AND ID_ACTION = :idAction
            AND ((RACI IN ('R', 'A', 'C') and TERMINE = 0x46) or (RACI = 'R' and TERMINE = 0x54))";

        $request = $this->pdo->prepare($req);
        $request->execute([
            ':userCode'  => $userCode,
            ':idAction' => $idAction
        ]);

         return $request->fetch() ? true:false;
    }
    
    /**
     * @return array<mixed>
    */
    public function getLastIdAction(string $typeObjet, int $idObjet, int $idTasks)
    {
        $req = "select MAX(ID_ACTION) maxId from DOA_ACTIONS with (nolock) where TYPE_OBJET = :typeObjet and ID_OBJET = :idObjet and ID_TACHE = :idTache";
        
        
        $arrayReq = [
            ':typeObjet' => $typeObjet,
            ':idObjet' => $idObjet,
            ':idTache' => $idTasks
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetch();
    }
    
    /**
     * @param array<string> $post
     * @return array<mixed>
    */
    public function getListeTaches(string $typeObjet, int $idObjet, ?string $idHierarchy = null, array $post = [])
    {
        $req = "select DOA_TACHES.*, HASTUTIL.NOM, DOA_HISTO_PLANNING.DATE_DEB DATE_DEB_P1, DOA_HISTO_PLANNING.DATE_FIN DATE_FIN_P1 from DOA_TACHES with (nolock)
                left join HASTUTIL with (nolock) on HASTUTIL.CODE = DOA_TACHES.RESPONSABLE
                left join DOA_HISTO_PLANNING with (nolock) on DOA_HISTO_PLANNING.ID_DOA = ID_OBJET and DOA_HISTO_PLANNING.ID_TACHE = DOA_TACHES.ID_TACHE and ID_VERSION	 = 0
                where TYPE_OBJET = :typeObjet and ID_OBJET = :idObjet";
        
        $arrayReq = [
            ':typeObjet' => $typeObjet,
            ':idObjet' => $idObjet,
        ];
        
        if ($idHierarchy) {
            $req .=" and ID_TACHE_H = :idHierarchy";
            $arrayReq[':idHierarchy'] = $idHierarchy;
        }
        
        if (($post['STATUT']??'') != '') {
            $req .=" and TERMINEE = ".($post['STATUT']=='OUV'? '0x46':'0x54')."";
        }
        
        $req .= " order by DOA_TACHES.DATE_DEB";
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
     * @return array<mixed>
    */
    public function getListTasksForPlanning(string $typeObjet, int $idObjet)
    {
        //$req = "select * from DOA_TACHES  with(nolock)  where TYPE_OBJET = :typeObjet and ID_OBJET=:idObjet and TERMINEE=0x46 order by DATE_DEB";
        
        $req = "select DOA_TACHES.* from DOA_TACHES with (nolock)
                left join DOA_TACHES_H with (nolock) on DOA_TACHES_H.TYPE_OBJET = DOA_TACHES.TYPE_OBJET  and DOA_TACHES_H.ID_OBJET = DOA_TACHES.ID_OBJET  and DOA_TACHES_H.ID_TACHE_H = DOA_TACHES.ID_TACHE_H 
                where DOA_TACHES.TYPE_OBJET = :typeObjet and DOA_TACHES.ID_OBJET = :idObjet
                order by  DOA_TACHES_H.NUM_ORDRE, DOA_TACHES.NUM_ORDRE";
        
        $arrayReq = [
            ':typeObjet' => $typeObjet,
            ':idObjet' => $idObjet,
        ];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
     * @return array<mixed>
    */
    public function getGroupTaskPlanning(string $typeObjet, int $idObjet, int $idTask)
    {
        $req = "select distinct DOA_TACHES.* from ( 
                	select DOA_TACHES.TYPE_OBJET,DOA_TACHES.ID_OBJET,DOA_TACHES.ID_TACHE from DOA_TACHES with (nolock) 
                	where DOA_TACHES.TYPE_OBJET = :typeObjet1 AND DOA_TACHES.ID_OBJET = :idObjet1 AND DOA_TACHES.ID_TACHE = :idTask1
                union 
                	select DOA_TAC_CONTR.TYPE_OBJET, DOA_TAC_CONTR.ID_OBJET, DOA_TAC_CONTR.ID_TACHE_CIBLE ID_TACHE from DOA_TAC_CONTR,DOA_TACHES
                	where DOA_TAC_CONTR.TYPE_OBJET = :typeObjet2 AND DOA_TAC_CONTR.ID_OBJET = :idObjet2 AND DOA_TAC_CONTR.ID_TACHE = :idTask2
                union 
                	select DOA_TAC_CONTR.TYPE_OBJET, DOA_TAC_CONTR.ID_OBJET, DOA_TAC_CONTR.ID_TACHE from DOA_TAC_CONTR,DOA_TACHES 
                	where DOA_TAC_CONTR.TYPE_OBJET = :typeObjet3 AND DOA_TAC_CONTR.ID_OBJET = :idObjet3 AND DOA_TAC_CONTR.ID_TACHE_CIBLE = :idTask3
                ) tmp,DOA_TACHES with (nolock)
                where tmp.ID_TACHE = DOA_TACHES.ID_TACHE and   tmp.ID_OBJET = DOA_TACHES.ID_OBJET and   tmp.TYPE_OBJET = DOA_TACHES.TYPE_OBJET and   tmp.ID_TACHE <> :idTask4;";
        
        $arrayReq = [
            ':typeObjet1' => $typeObjet,
            ':idObjet1' => $idObjet,
            ':idTask1' => $idTask,
            
            ':typeObjet2' => $typeObjet,
            ':idObjet2' => $idObjet,
            ':idTask2' => $idTask,
            
            ':typeObjet3' => $typeObjet,
            ':idObjet3' => $idObjet,
            ':idTask3' => $idTask,
            
            ':idTask4' => $idTask,
        ];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * @param array<string> $payload
    */
    public function reorderTask(array $payload): void
    {
        $firstReq = "UPDATE DOA_TACHES
                    SET NUM_ORDRE = 0
                    WHERE NUM_ORDRE = :initialPos AND ID_TACHE_H = :idHierarchy";
        
        $firstExec = $this->pdo->prepare($firstReq);
        $firstExec->execute([
            ':initialPos' => $payload['initialPosition'],
            ':idHierarchy' => $payload['idHierarchy'],
        ]);
        
        if ("+" === $payload['sign']) {
            $secondReq = "UPDATE DOA_TACHES
                    SET NUM_ORDRE = NUM_ORDRE " . $payload['sign'] . " 1
                    WHERE NUM_ORDRE >= :finalPos AND  NUM_ORDRE <= :initialPos AND ID_TACHE_H = :idHierarchy";
        } else {
            $secondReq = "UPDATE DOA_TACHES
                    SET NUM_ORDRE = NUM_ORDRE " . $payload['sign'] . " 1
                    WHERE NUM_ORDRE >= :initialPos AND  NUM_ORDRE <= :finalPos AND ID_TACHE_H = :idHierarchy";
        }
        
        $secondExec = $this->pdo->prepare($secondReq);
        $secondExec->execute([
            ':finalPos' => $payload['newPosition'],
            ':initialPos' => $payload['initialPosition'],
            ':idHierarchy' => $payload['idHierarchy'],
        ]);
        
        $thirdReq = "UPDATE DOA_TACHES
                    SET NUM_ORDRE = :finalPos
                    WHERE NUM_ORDRE = 0 AND ID_TACHE_H = :idHierarchy";
        
        $thirdExec = $this->pdo->prepare($thirdReq);
        $thirdExec->execute([
            ':finalPos' => $payload['newPosition'],
            ':idHierarchy' => $payload['idHierarchy'],
        ]);
    }
    
    public function updateRaciByResponsable(string $typeObjet, int $idObjet, int $idTask, string $codeActeur): void
    {
        if (strpos($codeActeur, 'CS_') === 0) {
            $update = "CODE_ACTEUR = '', TYPE_ELEM = :codeActeur ";
        } else {
            $update = "CODE_ACTEUR = :codeActeur";
        }

        $req = "update DOA_ACTIONS_ACTEURS set TYPE_ELEM = '', $update
                where TYPE_OBJET = :typeObjet and ID_OBJET = :idObjet and ID_TACHE = :idTask AND TYPE_ELEM = 3";

        
        $arrayReq = [
            ':typeObjet'    => $typeObjet,
            ':idObjet'      => $idObjet,
            ':idTask'       => $idTask,
            ':codeActeur'   => $codeActeur,
        ];
        
        $sql = $this->pdo->prepare($req);
        $sql->execute($arrayReq);
    }
}
