<?php

namespace Modules\ProjetAchat\Repository;

use Helpers\Outil\Sql;

class Donnee
{
    
    private $pdo;

    const STRUCT_TABLE = "DOA_STRUCT";
    const DATA_TABLE = "DOA_DATA";

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


    /**
     * @return array<mixed>
    */
    public function findFinalData(Int $idData): ?array
    {
        $req = "select * from DOA_STRUCT with (nolock)
                where DOA_STRUCT.ID_DONNEE=:idData";

        $arrayReq = [
            ':idData'   =>  $idData,
        ];

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetch($this->pdo::FETCH_ASSOC);
    }

    public function create(array $payload): void
    {
        $this->createStruct($payload);

        if ($payload['LIE_A_OBJET']) {
            $this->createData((int) $this->findStruct($payload['ID_CAT'], $payload['NUM_ORDRE'])[0], $payload);
        }
    }

    /**
     * @param array $payload
     */
    private function createStruct(array $payload): void
    {
        $req = "INSERT INTO " . self::STRUCT_TABLE . "(TYPE_OBJET, ID_OBJET, NOM_DONNEE, DESCR,
        MULTIPLE, ID_CAT, LIE_A_OBJET, OBJET, NUM_ORDRE, NOMSQLDW) values (:typeObject, :idObject,
        :nomDonnee, :descr, " . Sql::setBoolSql($payload['MULTIPLE']) . ", :idCat, " . Sql::setBoolSql($payload['LIE_A_OBJET']) . ", :object, :numOrdre, :nomSqlDW)";

        $arrayReq = [
            ':typeObject'       => $payload['TYPE_OBJET'],
            ':idObject'         => $payload['ID_OBJET'],
            ':nomDonnee'        => $payload['NOM_DATA'],
            ':descr'            => $payload['DESCR'],
            ':idCat'            => $payload['ID_CAT'],
            ':object'           => $payload['OBJET'],
            ':numOrdre'         => $payload['NUM_ORDRE'],
            ':nomSqlDW'         => $payload['NOMSQLDW'],
        ];

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

    /**
     * @param Int $idData
     * @param array $payload
     */
    private function createData(Int $idData, array $payload): void
    {
        $flagIdObjet = $payload['LIE_A_OBJET'];

        $req = "INSERT INTO " . self::DATA_TABLE . "(TYPE_OBJET, ID_OBJET, ID_DONNEE, NUM_ORDRE, NOM_DATA,
        LIE_A_OBJET, OBJET, TAG_MERGE, NOMSQLDW, FLAG_ID_OBJET, OBJET_DATA, CLE_UNIQUE, DATA_LIE_A_OBJ) values (:typeObject, :idObject, :idData,
        :numOrdre, :nomDonnee, " . Sql::setBoolSql($payload['LIE_A_OBJET']) . ", :object, :tagMerge, :nomSqlDW, ". Sql::setBoolSql($flagIdObjet) .", :objectData, 0x54, 0x54)";

        $arrayReq = [
            ':typeObject'       => $payload['TYPE_OBJET'],
            ':idObject'         => $payload['ID_OBJET'],
            ':idData'           => $idData,
            ':numOrdre'         => $payload['NUM_ORDRE'],
            ':nomDonnee'        => $payload['NOM_DATA_ID'],
            ':object'           => $payload['OBJET_DATA'],
            ':tagMerge'         => $payload['TAG_MERGE'],
            ':nomSqlDW'         => $payload['CHAMP_NOMSQLDW'],
            ':objectData'       => $payload['OBJET']
        ];

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

    /**
     * @return array<mixed>
    */
    public function findStruct(Int $idCat, Int $numOrder): ?array
    {
        $req = "select ID_DONNEE from DOA_STRUCT with (nolock) where ID_CAT = :idCat AND NUM_ORDRE = :numOrder";

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

        return $requete->fetch();
    }

    /**
     * @return array<mixed>|string|null
    */
    public function getRightOrder(int $idCategory)
    {
        $sql = "select max(NUM_ORDRE) from DOA_STRUCT with (nolock) where ID_CAT = :idCategory";

        $arrayReq = [
            ':idCategory' => $idCategory
        ];

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

        $requete->execute($arrayReq);

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

    /*
     * EDIT PART
     */

    /**
     * @param array $payload
     * @param Int $idData
     *
     * Edit data with 'false' === $post['del_foldersInProgress'] && 'false' === $post['del_TemplatesOnly']
     */
    public function editRepercussionNoWhere(array $payload, Int $idData): void
    {
        
        $reqUpdate = "
        update ds
		SET ds.NOM_DONNEE = :nomDonnee, ds.DESCR = :descr, ds.NOMSQLDW = :nomSqlDw
		from DOA_STRUCT ds
		where ID_DONNEE = :idData";

        $requete = $this->pdo->prepare($reqUpdate);
        $requete->execute([
            ':nomDonnee'    => $payload['NOM_DATA'],
            ':descr'        => $payload['DESCR'],
            ':nomSqlDw'     => $payload['NOMSQLDW'],
            ':idData'       => $idData
        ]);

        $firstReqCutLink = "
        update dd
        SET dd.ID_STRUCT = 0
		from DOA_DATA dd
		left outer join DOA_STRUCT ds on dd.ID_DONNEE = ds.ID_DONNEE
		where ds.ID_STRUCT = :idData";

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

        $secondReqCutLink = "
        update ds
		SET ds.ID_STRUCT = 0
		from DOA_STRUCT ds
		where ds.ID_STRUCT = :idData";

        $requete = $this->pdo->prepare($secondReqCutLink);
        $requete->execute([
            ':idData'       => $idData
        ]);
    }

    /**
     * @param array $payload
     * @param Int $idData
     *
     * Edit data with 'true' === $post['del_TemplatesOnly']
     */
    public function editRepercussionFoldersInProgress(array $payload, Int $idData): void
    {
        $reqUpdate = "update ds
		SET NOM_DONNEE = :nomDonnee, DESCR = :descr, NOMSQLDW = :nomSqlDw
		from DOA_STRUCT ds
		left outer join DOA d on d.ID = ds.ID_OBJET
		where ((d.MODELE = 0x54 OR (d.MODELE = 0x46 AND d.STATUT IN ('I', 'P')))
		AND ds.ID_STRUCT = :idData) OR ds.ID_DONNEE = :idD";

        $requete = $this->pdo->prepare($reqUpdate);
        $requete->execute([
            ':nomDonnee'    => $payload['NOM_DATA'],
            ':descr'        => $payload['DESCR'],
            ':nomSqlDw'     => $payload['NOMSQLDW'],
            ':idData'       => $idData,
            ':idD'       => $idData
        ]);

        $firstReqCutLink = "
        update dd
		SET dd.ID_STRUCT = 0
		from DOA_DATA dd
		left outer join DOA_STRUCT ds on dd.ID_DONNEE = ds.ID_DONNEE
		left outer join DOA d on d.ID = ds.ID_OBJET
		where ((d.MODELE = 0x46 AND d.STATUT NOT IN ('I', 'P'))
		AND ds.ID_STRUCT = :idData)";

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

        $secondReqCutLink = "
        update ds
        SET ds.ID_STRUCT = 0
		from DOA_STRUCT ds
		left outer join DOA d on d.ID = ds.ID_OBJET
		where ((d.MODELE = 0x46 AND d.STATUT NOT IN ('I', 'P'))
		AND ds.ID_STRUCT = :idData)";

        $requete = $this->pdo->prepare($secondReqCutLink);
        $requete->execute([
            ':idData'       => $idData
        ]);
    }

    /**
     * @param array $payload
     * @param Int $idData
     *
     * Edit data with 'true' === $post['del_foldersInProgress']
     */
    public function editRepercussionTemplatesOnly(array $payload, Int $idData): void
    {
        $reqUpdate = "
		update ds
		SET NOM_DONNEE = :nomDonnee, DESCR = :descr, NOMSQLDW = :nomSqlDw
		from DOA_STRUCT ds
		left outer join DOA d on d.ID = ds.ID_OBJET
		where (d.MODELE=0x54 and ds.ID_STRUCT=:idData) 
		or ds.ID_DONNEE=:idD";

        $requete = $this->pdo->prepare($reqUpdate);
        $requete->execute([
            ':nomDonnee'    => $payload['NOM_DATA'],
            ':descr'        => $payload['DESCR'],
            ':nomSqlDw'     => $payload['NOMSQLDW'],
            ':idData'       => $idData,
            ':idD'          => $idData
        ]);

        $firstReqCutLink = "
        update dd
		SET dd.ID_STRUCT = 0
		left outer join DOA_STRUCT ds on dd.ID_DONNEE = ds.ID_DONNEE
		left outer join DOA d on d.ID = ds.ID_OBJET
		where (d.MODELE = 0x46
		AND ds.ID_STRUCT = :idData)";

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

        $secondReqCutLink = "
        update ds
        SET ds.ID_STRUCT = 0
		from DOA_STRUCT ds
		left outer join DOA d on d.ID = ds.ID_OBJET
		where (d.MODELE = 0x46
		AND ds.ID_STRUCT = :idData)";

        $requete = $this->pdo->prepare($secondReqCutLink);
        $requete->execute([
            ':idData'       => $idData
        ]);
    }

    /*
     * END EDIT PART
     */

    /**
     * @return array<mixed>
    */
    public function getInfosLinkedToData(Int $idData): ?array
    {
        $req = "select TYPE_OBJET, ID_OBJET, ID_CAT, NOM_DONNEE 
        from DOA_STRUCT with (nolock) 
        WHERE ID_DONNEE = :idData";

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

        return $requete->fetch();
    }

    /**
     * @return array<mixed>
    */
    public function getListeDonee(string $typeObjet, int $idObjet, ?int $idCategorie = null, ?int $idDonnee = null): ?array
    {
        $req = 'select * 
        from DOA_STRUCT with(nolock) 
        where TYPE_OBJET = :typeObjet 
            and   ID_OBJET = :idObjet ';
       
        $arrayReq = [
            ':typeObjet'    => $typeObjet,
            ':idObjet'      => $idObjet,
        ];

        if ($idCategorie) {
            $req .=' and ID_CAT = :idCategorie';
            $arrayReq[':idCategorie'] =$idCategorie;
        }

        if ($idDonnee) {
            $req .=' and ID_DONNEE = :idDonnee';
            $arrayReq[':idDonnee'] =$idDonnee;
        }

        $listeDonnee              = $this->pdo->prepare($req);
        $listeDonnee->execute($arrayReq);
        
        return  $listeDonnee->fetchAll();
    }

    /**
     * @return array<mixed>
    */
    public function donneeAvecProjet(int $idDonnee, ?string $typeObjet = null, ?int $idObjet = null): ?array
    {
              
        $req = 'select * 
        from DOA_STRUCT with (nolock)
            left join DOA on DOA.ID = DOA_STRUCT.ID_OBJET
        where DOA_STRUCT.ID_DONNEE = :idDonnee';
       
        $arrayReq = [
            ':idDonnee'    => $idDonnee,
        ];

        if ($typeObjet) {
            $req .=' and DOA_STRUCT.TYPE_OBJET = :typeObjet';
            $arrayReq[':typeObjet'] =$typeObjet;
        }

        if ($idObjet) {
            $req .=' and  DOA_STRUCT.ID_OBJET = :idObjet';
            $arrayReq[':idObjet'] =$idObjet;
        }

        $listeDonnee              = $this->pdo->prepare($req);
        $listeDonnee->execute($arrayReq);
        
        return  $listeDonnee->fetch();
    }

    /**
     * @return array<mixed>
    */
    public function donneesAvecProjet(int $idDonnee, ?string $typeObjet = null, ?int $idObjet = null): ?array
    {
              
        $req = 'select * 
                from DOA_STRUCT with (nolock)
                left join DOA on DOA.ID = DOA_STRUCT.ID_OBJET
                where DOA_STRUCT.ID_DONNEE = :idDonnee';
       
        $arrayReq = [
            ':idDonnee'    => $idDonnee,
        ];

        if ($typeObjet) {
            $req .=' and DOA_STRUCT.TYPE_OBJET = :typeObjet';
            $arrayReq[':typeObjet'] =$typeObjet;
        }

        if ($idObjet) {
            $req .=' and  DOA_STRUCT.ID_OBJET = :idObjet';
            $arrayReq[':idObjet'] =$idObjet;
        }

        $listeDonnee              = $this->pdo->prepare($req);
        $listeDonnee->execute($arrayReq);
        
        return  $listeDonnee->fetchAll();
    }

    /**
     * @return array<mixed>
    */
    public function dataDonneeAvecProjet(string $typeObjet, int $idObjet, int $idDonnee): ?array
    {
        $req = 'select * 
                from DOA_DATA with (nolock) 
                left join DOA with (nolock) on DOA.ID = DOA_DATA.ID_OBJET 
                where DOA_DATA.TYPE_OBJET =:typeObjet 
                and   DOA_DATA.ID_OBJET   =  :idObjet 
                and   DOA_DATA.ID_DONNEE  =  :idDonnee ';
       
        $arrayReq = [
            ':typeObjet'    => $typeObjet,
            ':idObjet'      => $idObjet,
            ':idDonnee'     => $idDonnee,
        ];

        $listeDonnee              = $this->pdo->prepare($req);
        $listeDonnee->execute($arrayReq);
        
        return  $listeDonnee->fetchAll();
    }

    public function donneeBriserHeritage(string $typeObjet, int $idObjet, int $idDonnee): void
    {
        $req = "update DOA_STRUCT set ID_STRUCT = '' where TYPE_OBJET = :typeObjet and ID_OBJET = :idObjet and ID_DONNEE = :idDonnee";
        
        $arrayReq = [
            ':typeObjet'    => $typeObjet,
            ':idObjet'      => $idObjet,
            ':idDonnee'     => $idDonnee,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
    }
    
    /**
     * @return array<mixed>
    */
    public function getIdCategoryByData(string $typeObjet, int $idObjet, int $idDonnee): ?array
    {
        $req = "select ID_CAT from DOA_DATA with (nolock)
			    left join DOA_STRUCT with (nolock) on DOA_STRUCT.TYPE_OBJET = DOA_DATA.TYPE_OBJET AND DOA_STRUCT.ID_OBJET = DOA_DATA.ID_OBJET and DOA_STRUCT.ID_DONNEE = DOA_DATA.ID_DONNEE
				where DOA_DATA.TYPE_OBJET = :typeObjet and DOA_DATA.ID_OBJET = :idObjet and DOA_DATA.ID_DONNEE = :idDonnee";
        
        $arrayReq = [
            ':typeObjet'    => $typeObjet,
            ':idObjet'      => $idObjet,
            ':idDonnee'     => $idDonnee,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        return  $requete->fetch();
    }
}
