<?php

namespace Modules\Evaluation\Repository;

use Entity\Model\BibQstTh;
use Entity\Model\BibQstQ;
use Entity\Model\CampQstTh;
use Entity\Model\CampQstQ;
use Entity\Model\AoQstTh;
use Entity\Model\AoQstQ;
use Entity\Model\GrilQstQ;
use Entity\Model\GrilTheme;
use Exception;
use Helpers\Log\LogSysteme;
use Helpers\Outil\Sql;
use PDO;
use Helpers\QueryBuilder\AbsRepository;
use PDOStatement;

class BibliothequeDeQuestions extends AbsRepository
{

    /**
     * getInfoTheme
     *
     * @param  string $mode
     * @param  string $modeId
     * @param  string $themeId
     * @return array<mixed>
     */
    public function getInfoTheme(string $mode, string $modeId, string $themeId)
    {
        if ($mode == 'question') {
            $table = 'BIB_QST_TH';
            $whereIfModeCamp = '';
        } else {
            $table = 'CAMP_QST_TH';
            $whereIfModeCamp = 'AND ID_CAMP = :ID_CAMP';
            $params[':ID_CAMP'] = [
                'value'     => $modeId,
                'option'    => PDO::PARAM_INT
            ];
        }

        $query = "SELECT ID_THEME, ID_PERE, LIB_THEME, POIDS
        FROM {$table}
        WHERE ID_THEME = :ID_THEME
            {$whereIfModeCamp}";

        $params[':ID_THEME'] = [
            'value'     => $themeId,
            'option'    => PDO::PARAM_STR
        ];

        $data = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $data->bindParam($key, $param['value'], $param['option']);
        }

        $data->execute();

        return $data->fetch(PDO::FETCH_ASSOC);
    }

    public function prefixThemeId(string $mode, string $idTheme) :string
    {
        if ($mode == 'AO') {
            return 'A' . $idTheme;
        } elseif ($mode == 'AO_CONTRIB') {
            return 'I' . $idTheme;
        } elseif ($mode == 'question') {
            return 'B' . $idTheme;
        } elseif ($mode == 'modele') {
            return 'G' . $idTheme;
        } elseif ($mode == 'campagne') {
            return 'C' . $idTheme;
        }
        LogSysteme::prefixErrorLog('Invalid mode "'.$mode.'"');
        return $idTheme;
    }

    /**
     * @param array<mixed> $theme Entity AoQstTh|BibQstTh|GrilTheme|CampQstTh
     */
    public function getProfNoteurType(array $theme) :?string
    {
        if (array_key_exists('PROFNOTEUR1', $theme)) {
            if (strpos($theme['PROFNOTEUR1'], 'OBJ|') === 0) {
                return 'O';
            }
            if (in_array($theme['PROFNOTEUR1'], ['G','N'])) {
                return $theme['PROFNOTEUR1'];
            }
            return 'P';
        }
        return null;
    }

    /**
     * @return ?array<string>
     */
    public function getTheme(string $mode, string $idTheme, ?string $idCamp = null)
    {
        if (in_array($mode, ['AO', 'AO_CONTRIB'])) {
            return (new AoQstTh($this->pdo))->find([
                'NO_AO' => $idCamp,
                'ID_THEME' => $idTheme,
            ]);
        } elseif ($mode == 'question') {
            return (new BibQstTh($this->pdo))->find([
                'ID_THEME' => $idTheme,
            ]);
        } elseif ($mode == 'modele') {
            return (new GrilTheme($this->pdo))->find([
                'ID_MODEL' => $idCamp,
                'ID_THEME' => $idTheme,
            ]);
        } elseif ($mode == 'campagne') {
            return (new CampQstTh($this->pdo))->find([
                'ID_CAMP'   => $idCamp,
                'ID_THEME'  => $idTheme,
            ]);
        }
        LogSysteme::prefixErrorLog('Invalid mode "'.$mode.'"');
        return null;
    }

    public function listeAssociation($id_camp = null)
    {
        $req = "SELECT TYPE_OBJ, VAL_OBJ, CODE_STE
            FROM BIB_QST_U WITH(NOLOCK) 
            WHERE ID_QUEST = :idCamp";

        $arrayReq = [':idCamp' => $id_camp];
        return $this->fetchAssocAll($req, $arrayReq);
    }

    public function getMaxOrderTheme(string $tab, ?string $idCamp = null, ?string $idPere = null) :int
    {
        $array_req = [];
        $tabSql = '';
        $id = '';
        switch ($tab) {
            case 'question':
                $tabSql = 'BIB_QST_TH';
                $id = '';
                break;

            case 'campagne':
                $tabSql = 'CAMP_QST_TH';
                $id = 'ID_CAMP';
                break;

            case 'AO':
                $tabSql = 'AO_QST_TH';
                $id = 'NO_AO';
                break;

            case 'AO_CONTRIB':
                $tabSql = 'AO_QST_TH';
                $id = 'NO_AO';
                break;

            case 'modele':
                $tabSql = 'GRIL_THEME';
                $id = 'ID_MODEL';
                break;

            default:
                throw new \Exception('Invalid $tab "'.$tab.'"');
        }

        // TODO : replace with QueryBuilder::setAutoincrement
        $req = "SELECT MAX(NM_ORDRE) maxOrder 
        FROM $tabSql WITH (NOLOCK) 
        WHERE 1=1 ";

        if ($idCamp && !empty($id)) {
            $req .= " AND $id = :idCamp";
            $array_req[':idCamp'] = $idCamp;
        }

        if ($idPere) {
            $req .= " AND ID_PERE = :idPere";
            $array_req[':idPere'] = $idPere;
        }

        return intval($this->fetchAssocOne($req, $array_req)['maxOrder'] ?? 0);
    }

    public function getMaxOrderQuestion($tab, $champs, $idCamp = null, $idTheme = null)
    {
        $array_req = [];
        $tabSql = '';
        $id = '';

        if ($tab == 'question') {
            $tabSql = 'BIB_QST_Q';
            $id = '';
        } elseif ($tab == 'campagne') {
            $tabSql = 'CAMP_QST_Q';
            $id = 'ID_CAMP';
        } elseif (in_array($tab, ['AO', 'AO_CONTRIB'])) {
            $tabSql = 'AO_QST_Q';
            $id = 'NO_AO';
        } elseif ($tab == 'modele') {
            $tabSql = 'GRIL_QST_Q';
            $id = 'ID_MODEL';
        }

        $req = "SELECT MAX($champs) maxOrder 
        FROM $tabSql WITH (NOLOCK)
        WHERE 1=1";

        if ($idCamp) {
            $req .= " AND $id = :idCamp";
            $array_req[':idCamp'] = $idCamp;
        }

        if ($idTheme) {
            $req .= " AND ID_THEME = :idTheme";
            $array_req[':idTheme'] = $idTheme;
        }

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

        $acc->execute($array_req);
        return $acc->fetch(PDO::FETCH_ASSOC)['maxOrder'] ?? '0';
    }

    public function listeBibliotheque($id_camp = '', $id_theme = '', $mode = null, $id_question = null)
    {
        // QUESTION
        if ($mode == 'question' && $id_question != null) {
            $req = "SELECT ID_QUEST, mem.CONTMEMO AS COMMENTAIRE, LIB_QUEST, QUEST_FOUR, TYPE_REP, NOTE_FAUX, NOTE_VRAI, NUM_FORMAT, NUM_MIN,NUM_MAX,  ID_THEME, POIDS, PROFNOTEUR1, PROFNOTEUR2, AUTHCOMMENT, REGCOMOBLIG, TYP_NOMBRE, REPCALC_REQ, REPCALC_SELECT, OUV_NOTEPAR, OUV_NAPOSS, NM_ORDRE, QST_OBLIG
            FROM BIB_QST_Q q WITH (NOLOCK)
                LEFT OUTER JOIN MEMOS mem WITH (NOLOCK)
                    ON q.NM_COM = mem.NODEMEMO 
            WHERE ID_QUEST = :id_question 
            ORDER BY NM_ORDRE";
            $array_req = [':id_question' => $id_question];

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

            $acc->execute($array_req);
            return $acc->fetch(PDO::FETCH_ASSOC);
        }
        if ($id_theme == '' && $mode == 'question' && $id_question == null) {
            $req = "SELECT ID_QUEST, mem.CONTMEMO AS COMMENTAIRE, LIB_QUEST, QUEST_FOUR, TYPE_REP, NOTE_FAUX, NOTE_VRAI, NUM_FORMAT, NUM_MIN,NUM_MAX,  ID_THEME, POIDS, PROFNOTEUR1, PROFNOTEUR2, AUTHCOMMENT, REGCOMOBLIG, TYP_NOMBRE, REPCALC_REQ, REPCALC_SELECT, OUV_NOTEPAR, OUV_NAPOSS, NM_ORDRE, QST_OBLIG
            FROM BIB_QST_Q q WITH (NOLOCK)
                LEFT OUTER JOIN MEMOS mem WITH (NOLOCK)
                    ON q.NM_COM = mem.NODEMEMO 
            ORDER BY NM_ORDRE";
            $array_req = [];

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

        if ($id_theme != null && $mode == 'question') {
            $req = "SELECT ID_QUEST, mem.CONTMEMO AS COMMENTAIRE, LIB_QUEST,  QUEST_FOUR, TYPE_REP, NOTE_FAUX, NOTE_VRAI, NUM_FORMAT, NUM_MIN,NUM_MAX,  ID_THEME, POIDS, PROFNOTEUR1, PROFNOTEUR2, AUTHCOMMENT, REGCOMOBLIG, TYP_NOMBRE, REPCALC_REQ, REPCALC_SELECT, OUV_NOTEPAR, OUV_NAPOSS, NM_ORDRE, QST_OBLIG
            FROM BIB_QST_Q q WITH (NOLOCK)
                LEFT OUTER JOIN MEMOS mem WITH (NOLOCK)
                    ON q.NM_COM = mem.NODEMEMO  
            WHERE ID_THEME = :id_theme 
            ORDER BY NM_ORDRE";

            $array_req = [':id_theme' => $id_theme];

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

        // CAMPAGNE

        // Detail pour une question en fonction de l'id de la campagne
        if ($id_camp != '' && $id_question != '' && $id_theme != '' && $mode == 'campagne') {
            $req = "SELECT ID_CAMP, ID_QUEST, DESCRIPTION AS COMMENTAIRE, LIB_QUEST, QUEST_FOUR, TYPE_REP, NOTE_FAUX, NOTE_VRAI, NUM_FORMAT, NUM_MIN,NUM_MAX,  ID_THEME, POIDS, PROFNOTEUR1, PROFNOTEUR2, AUTHCOMMENT, REGCOMOBLIG, TYP_NOMBRE, REPCALC_REQ, REPCALC_SELECT, OUV_NOTEPAR, OUV_NAPOSS, NM_ORDRE, QST_OBLIG
            FROM CAMP_QST_Q q WITH (NOLOCK)
            WHERE ID_CAMP = :id_camp 
                AND ID_QUEST = :id_question 
                AND ID_THEME = :id_theme
            ORDER BY NM_ORDRE";
            $array_req = [':id_camp' => $id_camp, ':id_question' => $id_question, ':id_theme' => $id_theme];

            return $this->fetchAssocOne($req, $array_req);
        }
        if ($id_camp != '' && $id_theme != '' && $mode == 'campagne') {
            $req = "SELECT DISTINCT Q.ID_CAMP, Q.ID_QUEST, DESCRIPTION AS COMMENTAIRE, LIB_QUEST, QUEST_FOUR, TYPE_REP, NOTE_FAUX, NOTE_VRAI, NUM_FORMAT, NUM_MIN,NUM_MAX,  ID_THEME, POIDS, PROFNOTEUR1, PROFNOTEUR2, AUTHCOMMENT, REGCOMOBLIG, TYP_NOMBRE, REPCALC_REQ, REPCALC_SELECT, OUV_NOTEPAR, OUV_NAPOSS, NM_ORDRE, PARENT.ID_THEME_PARENT AS PARENT, ENFANT.ID_THEME_DEP AS ENFANT, QST_OBLIG
            FROM CAMP_QST_Q Q WITH (NOLOCK)
                LEFT JOIN CAMP_QST_DEP PARENT WITH (NOLOCK)
                    ON PARENT.ID_CAMP = Q.ID_CAMP 
                    AND PARENT.ID_THEME_PARENT = Q.ID_THEME 
                    AND PARENT.ID_QST_PARENT = Q.ID_QUEST 
                LEFT JOIN CAMP_QST_DEP ENFANT WITH (NOLOCK)
                    ON ENFANT.ID_CAMP = Q.ID_CAMP 
                    AND ENFANT.ID_THEME_DEP = Q.ID_THEME 
                    AND ENFANT.ID_QST_DEP = Q.ID_QUEST 
            WHERE Q.ID_CAMP = :id_camp 
                AND Q.ID_THEME = :idTheme 
            ORDER BY NM_ORDRE";
            $array_req = [':id_camp' => $id_camp, ':idTheme' => $id_theme];

            return $this->fetchAssocAll($req, $array_req);
        }
        if ($id_camp != '' && $id_theme == '' && $mode == 'campagne') {
            $req = "SELECT DISTINCT Q.ID_CAMP, ID_QUEST, DESCRIPTION AS COMMENTAIRE, LIB_QUEST, QUEST_FOUR, TYPE_REP, NOTE_FAUX, NOTE_VRAI, NUM_FORMAT, NUM_MIN,NUM_MAX,  ID_THEME, POIDS, PROFNOTEUR1, PROFNOTEUR2, AUTHCOMMENT, REGCOMOBLIG, TYP_NOMBRE, REPCALC_REQ, REPCALC_SELECT, OUV_NOTEPAR, OUV_NAPOSS, NM_ORDRE, PARENT.ID_THEME_PARENT AS THEME_ENFANT, PARENT.ID_QST_DEP AS QUESTION_ENFANT, ENFANT.ID_QST_PARENT AS QUESTION_PARENT, ENFANT.ID_THEME_PARENT AS THEME_PARENT, QST_OBLIG
            FROM CAMP_QST_Q Q WITH (NOLOCK)
                LEFT JOIN CAMP_QST_DEP PARENT WITH (NOLOCK)
                    ON PARENT.ID_CAMP = Q.ID_CAMP 
                    AND PARENT.ID_THEME_PARENT = Q.ID_THEME 
                    AND PARENT.ID_QST_PARENT = Q.ID_QUEST 
                LEFT JOIN CAMP_QST_DEP ENFANT WITH (NOLOCK)
                    ON ENFANT.ID_CAMP = Q.ID_CAMP 
                    AND ENFANT.ID_THEME_DEP = Q.ID_THEME 
                    AND ENFANT.ID_QST_DEP = Q.ID_QUEST 
            WHERE Q.ID_CAMP = :id_camp 
            ORDER BY NM_ORDRE";
            $array_req = [':id_camp' => $id_camp];

            return $this->fetchAssocAll($req, $array_req);
        }
        if ($id_camp == null && $id_theme != null && $mode == 'campagne') {
            $req = "SELECT ID_CAMP, ID_QUEST, G_B_QST_LIE, DESCRIPTION AS COMMENTAIRE, LIB_QUEST,  QUEST_FOUR, TYPE_REP, NOTE_FAUX, NOTE_VRAI, NUM_FORMAT, NUM_MIN,NUM_MAX,  ID_THEME, POIDS, PROFNOTEUR1, PROFNOTEUR2, AUTHCOMMENT, REGCOMOBLIG, TYP_NOMBRE, REPCALC_REQ, REPCALC_SELECT, OUV_NOTEPAR, OUV_NAPOSS, NM_ORDRE, QST_OBLIG
            FROM CAMP_QST_Q q WITH (NOLOCK)
            WHERE ID_THEME = :id_theme 
            ORDER BY NM_ORDRE";

            $array_req = [':id_theme' => $id_theme];

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

        // MODELE

        // Detail pour une question en fonction de l'id du modèle
        if ($id_camp != '' && $id_question != '' && $mode == 'modele') {
            $req = "SELECT ID_MODEL, ID_QUEST, DESCRIPTION AS COMMENTAIRE, LIB_QUEST, QUEST_FOUR, TYPE_REP, NOTE_FAUX, NOTE_VRAI, NUM_FORMAT, NUM_MIN, NUM_MAX, ID_THEME, POIDS, PROFNOTEUR1, PROFNOTEUR2, AUTHCOMMENT, REGCOMOBLIG, TYP_NOMBRE, REPCALC_REQ, REPCALC_SELECT, OUV_NOTEPAR, OUV_NAPOSS, NM_ORDRE, QST_OBLIG
            FROM GRIL_QST_Q q WITH (NOLOCK)
            WHERE ID_MODEL = :id_camp 
                AND ID_QUEST = :id_question 
            ORDER BY NM_ORDRE";
            $array_req = [':id_camp' => $id_camp, ':id_question' => $id_question];

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

            $acc->execute($array_req);

            return $acc->fetch(PDO::FETCH_ASSOC);
        }
        if ($id_camp != '' && $id_theme != '' && $mode == 'modele') {
            $req = "SELECT ID_MODEL,ID_QUEST, DESCRIPTION AS COMMENTAIRE, LIB_QUEST, QUEST_FOUR, TYPE_REP, NOTE_FAUX, NOTE_VRAI, NUM_FORMAT, NUM_MIN,NUM_MAX, ID_THEME, POIDS, PROFNOTEUR1, PROFNOTEUR2, AUTHCOMMENT, REGCOMOBLIG, TYP_NOMBRE, REPCALC_REQ, REPCALC_SELECT, OUV_NOTEPAR, OUV_NAPOSS, NM_ORDRE, QST_OBLIG
            FROM GRIL_QST_Q q WITH (NOLOCK)
            WHERE ID_MODEL = :id_camp 
                AND ID_THEME = :idTheme 
            ORDER BY NM_ORDRE";
            $array_req = [':id_camp' => $id_camp, ':idTheme' => $id_theme];

            return $this->fetchAssocAll($req, $array_req);
        }
        if ($id_camp != '' && $id_theme == '' && $mode == 'modele') {
            $req = "SELECT ID_MODEL,ID_QUEST, DESCRIPTION AS COMMENTAIRE, LIB_QUEST, QUEST_FOUR, TYPE_REP, NOTE_FAUX, NOTE_VRAI, NUM_FORMAT, NUM_MIN,NUM_MAX, ID_THEME, POIDS, PROFNOTEUR1, PROFNOTEUR2, AUTHCOMMENT, REGCOMOBLIG, TYP_NOMBRE, REPCALC_REQ, REPCALC_SELECT, OUV_NOTEPAR, OUV_NAPOSS, NM_ORDRE, QST_OBLIG
            FROM GRIL_QST_Q q WITH (NOLOCK)
            WHERE ID_MODEL = :id_camp 
            ORDER BY NM_ORDRE";
            $array_req = [':id_camp' => $id_camp];

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

        if ($id_camp == null && $id_theme != null && $mode == 'modele') {
            $req = "SELECT ID_MODEL, ID_QUEST, G_B_QST_LIE, DESCRIPTION AS COMMENTAIRE, LIB_QUEST,  QUEST_FOUR, TYPE_REP, NOTE_FAUX, NOTE_VRAI, NUM_FORMAT, NUM_MIN,NUM_MAX, ID_THEME, POIDS, PROFNOTEUR1, PROFNOTEUR2, AUTHCOMMENT, REGCOMOBLIG, TYP_NOMBRE, REPCALC_REQ, REPCALC_SELECT, OUV_NOTEPAR, OUV_NAPOSS, NM_ORDRE, QST_OBLIG
            FROM GRIL_QST_Q q WITH (NOLOCK)
            WHERE ID_THEME = :id_theme 
            ORDER BY NM_ORDRE";

            $array_req = [':id_theme' => $id_theme];

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

        //AO
        if (in_array($mode, ['AO', 'AO_CONTRIB'])) {
            $req = "SELECT * 
            FROM AO_QST_Q WITH (NOLOCK)
                LEFT OUTER JOIN MEMOS mem WITH (NOLOCK)
                    ON NM_COM = NODEMEMO 
                WHERE NO_AO = :idAo 
                    AND ID_THEME = :idTheme";
            $array_req = [':idAo' => $id_camp, ':idTheme' => $id_theme];


            if ($id_question) {
                $req .= " AND ID_QUEST = :idQuestion";
                $array_req[':idQuestion'] = $id_question;

                $req .= " ORDER BY NM_ORDRE";

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


                $acc->execute($array_req);
                return $acc->fetch(PDO::FETCH_ASSOC);
            } else {
                $req .= " ORDER BY NM_ORDRE";
                $acc = $this->pdo->prepare($req);

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

    public function updateMemoQuestion($idMemo, $id_question)
    {
        $req = "UPDATE BIB_QST_Q
        SET NM_COM =:NM_COM WHERE ID_QUEST = :ID_QUEST";

        $arrayReq = [':NM_COM' => $idMemo, ':ID_QUEST' => $id_question];

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

        return true;
    }

    public function listeTreeQuestion($mode, $id_camp = null)
    {
        if ($mode == 'question') {
            $req = "WITH ArboThemes (arbre,ID_PERE,ID_THEME,LIB_THEME, niveau,ordretri)  
            AS   
            (  
                SELECT CONVERT(VARCHAR(255),ID_THEME) arbre, ID_PERE,ID_THEME,LIB_THEME, 0 AS niveau, CONVERT(VARCHAR(255),ID_THEME+'|') ordretri 
                FROM BIB_QST_TH 
                WHERE ID_PERE=''
                UNION ALL
                SELECT CONVERT(VARCHAR(255),''+e.ID_THEME),e.ID_PERE,e.ID_THEME,e.LIB_THEME, niveau+1, CONVERT(VARCHAR(255),ordretri+e.ID_THEME) FROM BIB_QST_TH e
                JOIN ArboThemes d ON e.ID_PERE=d.ID_THEME
            )   
            SELECT arbre,ID_PERE,ID_THEME,LIB_THEME, niveau,ordretri,'0' COUNT_QUEST
            FROM ArboThemes  
            ORDER BY ordretri";

            $arrayReq = [];
        }

        if ($mode == 'AO_CONTRIB') {
            $req = "SELECT aqt.NO_AO, aqt.ID_THEME, aqt.ID_PERE, aqt.LIB_THEME, aqt.NM_COM, aqt.NM_ORDRE, aqt.POIDS, aqt.THEME_LIE, '0' COUNT_QUEST 
                    FROM AO_QST_TH aqt WITH (NOLOCK) 
                    WHERE aqt.NO_AO = :idAO AND aqt.ID_THEME LIKE 'I%'
                    ORDER BY ID_THEME, NM_ORDRE";
            $arrayReq = [':idAO' => $id_camp];
        }

        if ($mode == 'AO') {
            $req = "SELECT *, 
                        (SELECT COUNT(*) 
                        FROM AO_QST_Q WITH (NOLOCK) 
                        WHERE AO_QST_Q.NO_AO = AO_QST_TH.NO_AO 
                            AND AO_QST_Q.ID_THEME = AO_QST_TH.ID_THEME ) COUNT_QUEST 
                    FROM AO_QST_TH WITH (NOLOCK) 
                    WHERE NO_AO = :idAO AND ID_THEME != 'APRIX' AND ID_THEME LIKE 'A%' ORDER BY ID_THEME, NM_ORDRE";
            $arrayReq = [':idAO' => $id_camp];
        }

        if ($mode == 'campagne') {
            $req = "SELECT ID_PERE, ID_THEME, LIB_THEME, POIDS, THEME_LIE, '0' COUNT_QUEST 
                    FROM CAMP_QST_TH WITH (NOLOCK)
                    WHERE ID_CAMP = :id_camp";
            $arrayReq = [':id_camp' => $id_camp];
        }

        if ($mode == 'modele') {
            $req = "SELECT ID_MODEL, ID_THEME, ID_PERE, LIB_THEME, DESCRIPTION, NM_ORDRE, POIDS, THEME_LIE, '0' COUNT_QUEST 
                    FROM GRIL_THEME WITH (NOLOCK)
                    WHERE ID_MODEL = :id_camp";

            $arrayReq = [
                ':id_camp' => $id_camp,
            ];
        }

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

    /**
     * listeTreeQuestionReponseQuestionnaire
     *
     * @param  string $idAo
     * @return array<mixed>
     */
    public function listeTreeQuestionReponseQuestionnaire(string $idAo, string $type): array
    {
        $themeLike = $type == 'contrib'
            ? 'I%'
            : 'A%';
        $req = "SELECT *, 
                        (SELECT COUNT(*) 
                        FROM AO_QST_Q WITH (NOLOCK) 
                        WHERE AO_QST_Q.NO_AO = AO_QST_TH.NO_AO 
                            AND AO_QST_Q.ID_THEME = AO_QST_TH.ID_THEME ) COUNT_QUEST 
                FROM AO_QST_TH WITH (NOLOCK) 
                WHERE NO_AO = :idAO 
                    AND ID_THEME != 'APRIX'
                    AND ID_THEME LIKE :themeLike
                    AND EXISTS (SELECT * FROM AO_QST_Q with(nolock) WHERE NO_AO = AO_QST_TH.NO_AO AND ID_THEME = AO_QST_TH.ID_THEME)
                ORDER BY ID_THEME, NM_ORDRE";

        $arrayReq = [
            ':idAO' => $idAo,
            ':themeLike' => $themeLike,
        ];

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

    public function nbrCs($code_tab)
    {
        $req = "SELECT COD_TAB, TYPE FROM CS_CHPS with(nolock) 
        where COD_TAB =:code_tab and ( TYPE='SIGCDE'	or TYPE='DEMANDEU' or TYPE='ACHETEUR' or TYPE='SIGDA' or TYPE='SIGREF'  	)";

        $arrayReq = ['code_tab' => $code_tab];
        return $this->fetchAssocAll($req, $arrayReq);
    }
    public function popupListe($code_tab, $entity, $libelle = null)
    {

        $req = '';
        $alias = '';
        $i = 1;


        foreach ($this->nbrCs($code_tab) as $valeur) {
            $req .= " LEFT JOIN CS_CHPS CSCHPS" . $i . " with(nolock) ON CSCHPS" . $i . ".COD_TAB = '" . $valeur['COD_TAB'] . "' AND CSCHPS" . $i . ".TYPE='" . $valeur['TYPE'] . "'	
	            LEFT JOIN CS_DON CS" . $i . " with(nolock) ON CS" . $i . ".COD_ENR = " . $code_tab . "." . $entity . " AND CS" . $i . ".COD_TAB = CSCHPS" . $i . ".COD_TAB AND CS" . $i . ".NUM_FIX = CSCHPS" . $i . ".NUM_FIX";
            $alias .= ', CS' . $i . '.VALEUR ' . $valeur['TYPE'] . "";
            $i++;
        }
        $req_base = "SELECT * FROM (SELECT $entity , $libelle $alias FROM $code_tab $req ) CS";

        $arrayReq = [];
        return $this->fetchAssocAll($req_base, $arrayReq);
    }

    public function getListeThemeFils($mode, $idmode, $idTheme)
    {
        $tab = '';
        if ($mode == 'question') {
            $tab = 'BIB_QST_TH';
        } elseif ($mode == 'campagne') {
            $tab = 'CAMP_QST_TH';
        } elseif (in_array($mode, ['AO', 'AO_CONTRIB'])) {
            $tab = 'AO_QST_TH';
        } elseif ($mode == 'modele') {
            $tab = 'GRIL_THEME';
        }

        $req = "Select * from $tab with (nolock) where ID_PERE = :idTheme";

        if (in_array($mode, ['AO', 'AO_CONTRIB'])) {
            $req .= ' and NO_AO = :idMode';
            $arrayReq[':idMode'] = $idmode;
        } elseif ($mode == 'campagne') {
            $req .= ' and ID_CAMP = :idMode';
            $arrayReq[':idMode'] = $idmode;
        } elseif ($mode == 'modele') {
            $req .= ' and ID_MODEL = :idMode';
            $arrayReq[':idMode'] = $idmode;
        }

        $arrayReq[':idTheme'] = $idTheme;

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

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

    public function deleteTheme($mode, $idmode, $idTheme)
    {
        //On récupere la liste des fils (si il en a)
        $listeFils = $this->getListeThemeFils($mode, $idmode, $idTheme);

        //Si il a des fils, alors on éxécute la fonction deleteTheme afin de vérifié qu'il n'ai pas de fils de non plus et on supprime ces question, liste de réponse et le theme.
        foreach ($listeFils as $fils) {
            $this->deleteTheme($mode, $idmode, $fils['ID_THEME']);
        }

        //On delete le theme et on récupere la liste des questions si il en a
        $infoTheme = [];
        $listeQuestion = [];
        if ($mode == 'question') {
            $bibQstTh = new BibQstTh($this->pdo);
            $infoTheme = $bibQstTh->find($idTheme);
            $bibQstTh->delete($idTheme);

            $bibQstQ = new BibQstQ($this->pdo);
            $listeQuestion = $bibQstQ->fetchAll([], ['filter_egal_ID_THEME' => $idTheme]);
        } elseif ($mode == 'campagne') {
            $campQstTh = new CampQstTh($this->pdo);
            $infoTheme = $campQstTh->find(['ID_CAMP' => $idmode, 'ID_THEME' => $idTheme]);
            $campQstTh->delete(['ID_CAMP' => $idmode, 'ID_THEME' => $idTheme]);

            $campQstQ = new CampQstQ($this->pdo);
            $listeQuestion = $campQstQ->fetchAll([], ['filter_egal_ID_THEME' => $idTheme, 'filter_egal_ID_CAMP' => $idmode]);
        } elseif (in_array($mode, ['AO', 'AO_CONTRIB'])) {
            $aoQstTh = new AoQstTh($this->pdo);
            $infoTheme = $aoQstTh->find(['NO_AO' => $idmode, 'ID_THEME' => $idTheme]);
            $aoQstTh->find(['NO_AO' => $idmode, 'ID_THEME' => $idTheme]);
            $aoQstTh->delete(['NO_AO' => $idmode, 'ID_THEME' => $idTheme]);

            $aoQstQ = new AoQstQ($this->pdo);
            $listeQuestion = $aoQstQ->fetchAll([], ['filter_egal_ID_THEME' => $idTheme, 'filter_egal_NO_AO' => $idmode]);
        } elseif ($mode == 'modele') {
            $modelQstTheme = new GrilTheme($this->pdo);
            $infoTheme = $modelQstTheme->find(['ID_MODEL' => $idmode, 'ID_THEME' => $idTheme]);
            $modelQstTheme->find(['ID_MODEL' => $idmode, 'ID_THEME' => $idTheme]);
            $modelQstTheme->delete(['ID_MODEL' => $idmode, 'ID_THEME' => $idTheme]);

            $modelQstQ = new GrilQstQ($this->pdo);
            $listeQuestion = $modelQstQ->fetchAll([], ['filter_egal_ID_THEME' => $idTheme, 'filter_egal_ID_MODEL' => $idmode]);
        }

        // on delete le memo du theme
        if ($mode != 'modele' && $mode != 'campagne') {
            Sql::deleteMemos($this->pdo, $infoTheme['NM_COM']);
        }

        // on delete la liste des question (et la liste des réponse possible pour les TYPE "T" ainsi que les memo des questions
        $questionRepo = new Question($this->pdo);
        foreach ($listeQuestion as $question) {
            $questionRepo->deleteQuestion($mode, $idmode, $idTheme, $question['ID_QUEST']);
        }
    }

    /**
     * isDependentQuestion
     *
     * @param  int $campaignId
     * @param  string $theme
     * @param  int $questionId
     * @return array<mixed>
     */
    public function isDependentQuestion(int $campaignId, string $theme, int $questionId)
    {
        $req = "SELECT DISTINCT DEPENDENCY.ID_QUEST AS dependentId, DEPENDENCY.LIB_QUEST AS dependentLabel, PARENT.ID_QUEST AS parentId, PARENT.LIB_QUEST AS parentLabel, PARENT_THEME.LIB_THEME AS parentThemeLabel, DEP_THEME.LIB_THEME as dependentThemeLabel, 'D' AS BOND, d.ID_THEME_PARENT, d.ID_THEME_DEP, d.ID_QST_PARENT, d.ID_QST_DEP 
        FROM CAMP_QST_DEP d WITH (NOLOCK)
        LEFT JOIN CAMP_QST_Q DEPENDENCY WITH (NOLOCK)
            ON  d.ID_CAMP = DEPENDENCY.ID_CAMP 
            AND (d.ID_THEME_DEP = DEPENDENCY.ID_THEME 
            AND d.ID_QST_DEP = DEPENDENCY.ID_QUEST)
        LEFT JOIN CAMP_QST_Q PARENT WITH (NOLOCK)
            ON d.ID_CAMP = PARENT.ID_CAMP 
            AND (d.ID_THEME_PARENT = PARENT.ID_THEME 
            AND d.ID_QST_PARENT = PARENT.ID_QUEST)
        LEFT JOIN CAMP_QST_TH DEP_THEME WITH (NOLOCK)
            ON d.ID_CAMP = DEP_THEME.ID_CAMP 
            AND DEP_THEME.ID_THEME = d.ID_THEME_DEP
        LEFT JOIN CAMP_QST_TH PARENT_THEME WITH (NOLOCK)
            ON d.ID_CAMP = PARENT_THEME.ID_CAMP 
            AND PARENT_THEME.ID_THEME = d.ID_THEME_PARENT
        WHERE d.ID_CAMP = $campaignId 
            AND d.ID_THEME_DEP = :ID_THEME 
            AND d.ID_QST_DEP = $questionId ";

        $arrayReq = [
            ':ID_THEME' => $theme,
        ];

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

    /**
     * isParentQuestion
     *
     * @param  int $campaignId
     * @param  string $theme
     * @param  int $questionId
     * @return array<mixed>
     */
    public function isParentQuestion(int $campaignId, string $theme, int $questionId)
    {
        $req = "SELECT DISTINCT DEPENDENCY.ID_QUEST AS dependentId, DEPENDENCY.LIB_QUEST AS dependentLabel, PARENT.ID_QUEST AS parentId, PARENT.LIB_QUEST AS parentLabel, PARENT_THEME.LIB_THEME AS parentThemeLabel, DEP_THEME.LIB_THEME as dependentThemeLabel, 'P' AS BOND, d.ID_THEME_PARENT, d.ID_THEME_DEP, d.ID_QST_PARENT, d.ID_QST_DEP 
        FROM CAMP_QST_DEP d WITH (NOLOCK)
            LEFT JOIN CAMP_QST_Q DEPENDENCY 
                ON  d.ID_CAMP = DEPENDENCY.ID_CAMP 
                    AND (d.ID_THEME_DEP = DEPENDENCY.ID_THEME 
                    AND d.ID_QST_DEP = DEPENDENCY.ID_QUEST)
            LEFT JOIN CAMP_QST_Q PARENT 
                ON d.ID_CAMP = PARENT.ID_CAMP 
                    AND (d.ID_THEME_PARENT = PARENT.ID_THEME 
                    AND d.ID_QST_PARENT = PARENT.ID_QUEST)
            LEFT JOIN CAMP_QST_TH DEP_THEME 
                ON d.ID_CAMP = DEP_THEME.ID_CAMP 
                    AND DEP_THEME.ID_THEME = d.ID_THEME_DEP
            LEFT JOIN CAMP_QST_TH PARENT_THEME 
                ON d.ID_CAMP = PARENT_THEME.ID_CAMP 
                    AND PARENT_THEME.ID_THEME = d.ID_THEME_PARENT
        WHERE d.ID_CAMP = $campaignId AND PARENT.ID_THEME = :ID_THEME 
            AND PARENT.ID_QUEST = $questionId ";

        $arrayReq = [
            ':ID_THEME' => $theme,
        ];

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

    /**
     * resetQuiz
     *
     * @param  string $themeTable
     * @param  string $questionsTable
     * @param  string $answersTable
     * @param  string $key
     * @param  string|int $modeId
     * @param  string $dependenciesTable
     * @return bool
     */
    public function resetQuiz(string $themeTable, string $questionsTable, string $answersTable, string $key, $modeId, string $dependenciesTable = '')
    {
        if ($key == 'ID_MODEL') {
            $query = "DELETE FROM $answersTable
                WHERE ID_QUEST IN
                    (SELECT ID_QUEST 
                    FROM $questionsTable WITH (NOLOCK)
                    WHERE $key = :key3)
                ";
        } else {
            $query = "DELETE FROM $answersTable
                WHERE $key = :key3
                ";
        }

        $query .= "DELETE FROM $themeTable 
            WHERE $key = :key
            
            DELETE FROM $questionsTable
            WHERE $key = :key2
            ";

        /** @var array<mixed> $params */
        $params = [
            ':key'  => $modeId,
            ':key2' => $modeId,
            ':key3' => $modeId
        ];

        if ($dependenciesTable != '') {
            $query .= "DELETE FROM $dependenciesTable
                WHERE $key = :key4 
                ";
            $params[':key4'] = $modeId;
        }

        $reset = $this->pdo->prepare($query);

        try {
            if ($key == 'ID_MODEL') {
                $reset->bindParam(':key', $params[':key'], PDO::PARAM_STR);
                $reset->bindParam(':key2', $params[':key2'], PDO::PARAM_STR);
                $reset->bindParam(':key3', $params[':key3'], PDO::PARAM_STR);
            } else {
                $reset->bindParam(':key', $params[':key'], PDO::PARAM_INT);
                $reset->bindParam(':key2', $params[':key2'], PDO::PARAM_INT);
                $reset->bindParam(':key3', $params[':key3'], PDO::PARAM_INT);
                if ($key == 'ID_CAMP') {
                    $reset->bindParam(':key4', $params[':key4'], PDO::PARAM_INT);
                }
            }
            $reset->execute();
            return true;
        } catch (Exception $e) {
            return false;
        }
    }

    /**
     * isQuizEditableOnRfx
     *
     * @param  int $id
     * @return string
     */
    public function isQuizEditableOnRfx(int $id)
    {
        $query = "SELECT gme.NON_MODIF_AO 
            FROM GRIL_THEME gt WITH (NOLOCK)
                LEFT JOIN GRIL_MOD_ENT gme WITH (NOLOCK)
                    ON gme.ID_MODEL = gt.ID_MODEL
                LEFT JOIN AO_QST_TH aqt WITH (NOLOCK)
                    ON aqt.THEME_LIE = gt.ID_THEME
            WHERE aqt.NO_AO = :NO_AO ";

        $params = [
            ':NO_AO'    => $id,
        ];

        $check = $this->pdo->prepare($query);
        $check->bindParam(':NO_AO', $params[':NO_AO'], PDO::PARAM_STR);

        $check->execute();

        $check = $check->fetch(PDO::FETCH_ASSOC);

        return $check['NON_MODIF_AO'] ?? 'F';
    }

    /**
     * questionsListForImportRfx
     *
     * @param  string $themeId
     * @return array<mixed>
     */
    public function questionsListForImportRfx(string $themeId)
    {
        $query = "SELECT * 
        FROM BIB_QST_Q WITH(NOLOCK)
        WHERE ID_THEME = :ID_THEME
            AND TYPE_REP <> 'C'
            AND (QUEST_FOUR = 0x54
                OR PROFNOTEUR1 = 'A')";

        $params = [
            ':ID_THEME' => $themeId
        ];

        $list = $this->pdo->prepare($query);
        $list->bindParam(':ID_THEME', $params[':ID_THEME'], PDO::PARAM_STR);

        $list->execute();

        $list = $list->fetchAll(PDO::FETCH_ASSOC);

        if ($list) {
            return $list;
        } else {
            return [];
        }
    }

    /**
     * checkIfSqlRequestWorksV2
     *
     * @param  string $select
     * @param  string $from
     * @return bool
     */
    public function checkIfSqlRequestWorks(string $select, string $from): bool
    {
        // on remplace les éventuels tags de la requête
        $from = \str_replace('<TAG_PERIODE>', '', $from);
        $from = \str_replace('<TAG_FOURN>', '', $from);
        $from = \str_replace('<TAG_USER>', '', $from);
        $from = \str_replace('<TAG_SOCIETE>', '', $from);

        // TODO : use PdoSkeletonV2::countTransacError instead
        // on crée une vue SQL pour tester la requête sans l'exécuter
        $query = "CREATE VIEW [Test Requete] AS
        SELECT {$select} AS TEST
        {$from}";

        $test = $this->pdo->prepare($query);

        if ($test->execute() === true) {
            // si l'exécution renvoie true c'est que la requête est ok, on supprime donc la vue créée en SQL
            $query = "DROP VIEW [Test Requete];";
            $test = $this->pdo->prepare($query);
            $test->execute();
            return true;
        } else {
            return false;
        }
    }

    /**
     * updateLibraryThemeOrigin
     *
     * @param  array<mixed> $post
     * @return PDOStatement
     */
    public function updateLibraryThemeOrigin(array $post): PDOStatement
    {
        // on construit la clause where échappée des thèmes en fonction du nombre de thèmes concernés
        $params = $this->_buildUpdateThemeParams($post);
        $where = $params['where'];
        return $this->execute(
            "UPDATE BIB_QST_Q SET PROFNOTEUR1 = :PROFNOTEUR1, PROFNOTEUR2 = :PROFNOTEUR2
            WHERE ID_THEME IN ({$where})
                AND TYPE_REP <> 'C' 
                AND QUEST_FOUR = 0x46",
            [
                ':PROFNOTEUR1' => $post['PROFNOTEUR1'],
                ':PROFNOTEUR2' => $post['PROFNOTEUR2'] ?? '',
            ] + $params['themes']
        );
    }

    /**
     * updateCampaignThemeOrigin
     *
     * @param  array<mixed> $post
     * @return PDOStatement
     */
    public function updateCampaignThemeOrigin(array $post): PDOStatement
    {
        // on construit la clause where échappée des thèmes en fonction du nombre de thèmes concernés
        $params = $this->_buildUpdateThemeParams($post);
        $where = $params['where'];
        return $this->execute(
            "UPDATE CAMP_QST_Q SET PROFNOTEUR1 = :PROFNOTEUR1, PROFNOTEUR2 = :PROFNOTEUR2
            WHERE ID_CAMP = :ID_CAMP
                AND ID_THEME IN ({$where})
                AND TYPE_REP <> 'C' 
                AND QUEST_FOUR = 0x46",
            [
                ':PROFNOTEUR1' => $post['PROFNOTEUR1'],
                ':PROFNOTEUR2' => $post['PROFNOTEUR2'] ?? '',
                ':ID_CAMP' => $post['idMode'],
            ] + $params['themes']
        );
    }

    /**
     * updateModelThemeOrigin
     *
     * @param  array<mixed> $post
     * @return PDOStatement
     */
    public function updateModelThemeOrigin(array $post): PDOStatement
    {
        // on construit la clause where échappée des thèmes en fonction du nombre de thèmes concernés
        $params = $this->_buildUpdateThemeParams($post);
        $where = $params['where'];
        return $this->execute(
            "UPDATE GRIL_QST_Q SET PROFNOTEUR1 = :PROFNOTEUR1, PROFNOTEUR2 = :PROFNOTEUR2
            WHERE ID_MODEL = :ID_MODEL
                AND ID_THEME IN ({$where})
                AND TYPE_REP <> 'C' 
                AND QUEST_FOUR = 0x46",
            [
                ':PROFNOTEUR1' => $post['PROFNOTEUR1'],
                ':PROFNOTEUR2' => $post['PROFNOTEUR2'] ?? '',
                ':ID_MODEL' => $post['idMode']
            ] + $params['themes']
        );
    }

    /**
     * _buildUpdateThemeParams
     *
     * @param  array<mixed> $post
     * @return array<mixed>
     */
    private function _buildUpdateThemeParams(array $post): array
    {
        $themes = [];
        $whereClause = '';
        if (is_array($post['idTheme'])) {
            $i = 1;
            foreach ($post['idTheme'] as $theme) {
                $whereClause .= ':ID_THEME' . $i . ', ';
                $themes[':ID_THEME' . $i] = $theme['ID_THEME'];
            }
            $whereClause = substr($whereClause, 0, -2);
        } else {
            $whereClause = ':ID_THEME ';
            $themes[':ID_THEME'] = $post['idTheme'];
        }

        return [
            'where' => $whereClause,
            'themes' => $themes
        ];
    }
}
