<?php

namespace Modules\Evaluation\Repository\Import;

use GuzzleHttp\Psr7\Response;
use Helpers\QueryBuilder\AbsRepository;
use PDO;

class ImportQuiz extends AbsRepository
{
    /**
     * importQuizFromModel
     *
     * @param  int $campaignId
     * @param  string $modelId
     * @return Response
     */
    public function importQuizFromModel(int $campaignId, string $modelId)
    {
        $query = "SELECT ''
        DECLARE 
        @p_i_Campaign_Id    INT, 
        @p_i_Model_Id       VARCHAR(10) = '', 
        @v_ID_QUEST         INT = 0,
        @v_NEW_ID_QUEST     INT = 0,
        @v_ID_QUEST_BIBLI   INT = 0,
        @v_LIB_QUEST        VARCHAR(120) = '',
        @v_NM_COM           INT = 0,
        @v_QUEST_FOUR       BINARY(1) = 0x46,
        @v_TYPE_REP         VARCHAR(1) = '',
        @v_NOTE_FAUX        INT = 0,
        @v_NOTE_VRAI        INT = 0,
        @v_NUM_FORMAT       VARCHAR(10) = '',
        @v_NUM_MIN          VARCHAR(18) = '',
        @v_NUM_MAX          VARCHAR(18) = '',
        @v_ID_THEME         VARCHAR(10) = '',
        @v_NM_ORDRE         INT = 0,
        @v_POIDS            INT = 0,
        @v_ID_MODEL         VARCHAR(10) = '',
        @v_PROFNOTEUR1      VARCHAR(30) = '',
        @v_PROFNOTEUR2      VARCHAR(30) = '',
        @v_AUTHCOMMENT      BINARY(1) = 0x46,
        @v_REGCOMOBLIG      VARCHAR(50) = '',
        @v_TYP_NOMBRE       VARCHAR(1) = '',
        @v_REPCALC_SELECT   VARCHAR(200) = '',
        @v_REPCALC_REQ      VARCHAR(3000) = '',
        @v_OUV_NOTEPAR      VARCHAR(1) = '',
        @v_OUV_NAPOSS       BINARY(1) = 0x46,
        @v_DESCRIPTION      VARCHAR(3000) = '',
        @v_QST_OBLIG        BINARY(1) = 0x54
        
        SET @p_i_Campaign_Id = :ID_CAMP
        SET @p_i_Model_Id = :ID_MODEL
        
        DELETE 
        FROM CAMP_QST_Q 
        WHERE ID_CAMP = @p_i_Campaign_Id
        
		DELETE 
        FROM CAMP_QST_R 
        WHERE ID_CAMP = @p_i_Campaign_Id

        DELETE 
        FROM CAMP_GRP_NOT
        WHERE ID_CAMP = @p_i_Campaign_Id

        -- on supprime les données de la table CAMP_QST_TH
        DELETE
        FROM CAMP_QST_TH 
        WHERE ID_CAMP = @p_i_Campaign_Id
        
        -- on crée une table temporaire chargée de récupérer l'ensemble des données du modèle
        CREATE TABLE #TEMP_CAMP_QST_TH(ID_CAMP INT, ID_THEME VARCHAR(10), ID_PERE VARCHAR(10), LIB_THEME VARCHAR(60), NM_ORDRE INT, POIDS INT, THEME_LIE VARCHAR(10), DESCRIPTION VARCHAR(3000))

        -- on insère dans la table temporaire les données du modèle en les adaptant à la table de la campagne
        INSERT INTO #TEMP_CAMP_QST_TH (ID_THEME, ID_PERE, LIB_THEME, NM_ORDRE, POIDS, THEME_LIE, DESCRIPTION)
            (SELECT 'C' + RIGHT(ID_THEME, LEN(ID_THEME) - 1), 
                CASE
                    WHEN ID_PERE <> ''
                    THEN 'C' + RIGHT(ID_PERE, LEN(ID_PERE) - 1)
                    ELSE ''
                END, LIB_THEME, NM_ORDRE, POIDS, ID_THEME, 
                DESCRIPTION
            FROM GRIL_THEME T
            WHERE ID_MODEL = @p_i_Model_Id)
        
        -- on définit l'id de la campagne dans la table temporaire
        UPDATE #TEMP_CAMP_QST_TH 
            SET ID_CAMP = @p_i_Campaign_Id
        
        -- on copie dans la table des thèmes de campagne l'ensemble des données de la table temporaire
        INSERT INTO CAMP_QST_TH (ID_CAMP, ID_THEME, ID_PERE, LIB_THEME, NM_ORDRE, POIDS, THEME_LIE, DESCRIPTION)
            SELECT ID_CAMP, ID_THEME, ID_PERE, LIB_THEME, NM_ORDRE, POIDS, THEME_LIE, DESCRIPTION FROM #TEMP_CAMP_QST_TH
        
        -- on drop la table une fois l'opération terminée
        DROP TABLE #TEMP_CAMP_QST_TH
        
        -- on démarre une boucle sur l'ensemble des questions du modèle
        DECLARE questions_list 
            CURSOR FOR 
                SELECT ID_QUEST, ID_QUEST_BIBLI, LIB_QUEST, QUEST_FOUR, TYPE_REP, NOTE_FAUX, NOTE_VRAI, NUM_FORMAT, NUM_MIN, NUM_MAX, ID_THEME, NM_ORDRE, POIDS, ID_MODEL, PROFNOTEUR1, PROFNOTEUR2, AUTHCOMMENT, REGCOMOBLIG, TYP_NOMBRE, REPCALC_SELECT, REPCALC_REQ, OUV_NOTEPAR, OUV_NAPOSS, DESCRIPTION, QST_OBLIG 
                FROM GRIL_QST_Q 
                WHERE ID_MODEL = @p_i_Model_Id
        
        OPEN questions_list  
            FETCH NEXT FROM questions_list 
                INTO @v_ID_QUEST, @v_ID_QUEST_BIBLI, @v_LIB_QUEST, @v_QUEST_FOUR,  @v_TYPE_REP,  @v_NOTE_FAUX,  @v_NOTE_VRAI,  @v_NUM_FORMAT,  @v_NUM_MIN,  @v_NUM_MAX,  @v_ID_THEME,  @v_NM_ORDRE,  @v_POIDS,  @v_ID_MODEL,  @v_PROFNOTEUR1,  @v_PROFNOTEUR2,  @v_AUTHCOMMENT,  @v_REGCOMOBLIG,  @v_TYP_NOMBRE,  @v_REPCALC_SELECT,  @v_REPCALC_REQ,  @v_OUV_NOTEPAR, @v_OUV_NAPOSS, @v_DESCRIPTION, @v_QST_OBLIG  
            WHILE @@FETCH_STATUS = 0
            BEGIN
                -- on insère les données des groupes de noteurs du modèle si les questions importées ont pour origine de noteur un groupe (GRIL_QST_Q.PROFNOTEUR1 == 'G' && GRIL_QST_Q.PROFNOTEUR2 <> '')
                IF (@v_PROFNOTEUR1 = 'G' and @v_PROFNOTEUR2 <> '')
                    BEGIN 
                        IF (NOT EXISTS (SELECT GROUPE FROM CAMP_GRP_NOT WHERE ID_CAMP = @p_i_Campaign_Id AND GROUPE = @v_PROFNOTEUR2) )
                            BEGIN 
                                INSERT INTO CAMP_GRP_NOT (ID_CAMP, GROUPE)
                                VALUES (@p_i_Campaign_Id, @v_PROFNOTEUR2)
                            END
                    END

                -- on insère les données des questions dans les tables de la campagne
                INSERT INTO CAMP_QST_Q (ID_CAMP, ID_THEME, ID_QUEST, G_B_QST_LIE, LIB_QUEST, QUEST_FOUR, TYPE_REP, NOTE_FAUX, NOTE_VRAI, NUM_FORMAT, NUM_MIN, NUM_MAX, POIDS, NM_ORDRE, ID_MODEL, PROFNOTEUR1, PROFNOTEUR2, AUTHCOMMENT, REGCOMOBLIG, TYP_NOMBRE, REPCALC_SELECT, REPCALC_REQ, OUV_NOTEPAR, OUV_NAPOSS, DESCRIPTION, QST_OBLIG)
                VALUES (@p_i_Campaign_Id, 'C' + RIGHT(@v_ID_THEME, LEN(@v_ID_THEME) - 1), (SELECT ISNULL(MAX(ID_QUEST), 0) + 1 FROM CAMP_QST_Q WITH(NOLOCK) WHERE ID_THEME = ('C' + RIGHT(@v_ID_THEME, LEN(@v_ID_THEME) - 1)) AND ID_CAMP = @p_i_Campaign_Id), 'G' + cast(@v_ID_QUEST as varchar), @v_LIB_QUEST, @v_QUEST_FOUR, @v_TYPE_REP, @v_NOTE_FAUX, @v_NOTE_VRAI, @v_NUM_FORMAT, @v_NUM_MIN, @v_NUM_MAX, @v_POIDS, @v_NM_ORDRE, @v_ID_MODEL, @v_PROFNOTEUR1, @v_PROFNOTEUR2, @v_AUTHCOMMENT, @v_REGCOMOBLIG, @v_TYP_NOMBRE, @v_REPCALC_SELECT, @v_REPCALC_REQ, @v_OUV_NOTEPAR, @v_OUV_NAPOSS, @v_DESCRIPTION, @v_QST_OBLIG)
        
                -- on passe alors à la question suivante
                FETCH NEXT FROM questions_list 
                    INTO @v_ID_QUEST, @v_ID_QUEST_BIBLI, @v_LIB_QUEST, @v_QUEST_FOUR,  @v_TYPE_REP,  @v_NOTE_FAUX,  @v_NOTE_VRAI,  @v_NUM_FORMAT,  @v_NUM_MIN,  @v_NUM_MAX,  @v_ID_THEME,  @v_NM_ORDRE,  @v_POIDS,  @v_ID_MODEL,  @v_PROFNOTEUR1,  @v_PROFNOTEUR2,  @v_AUTHCOMMENT,  @v_REGCOMOBLIG,  @v_TYP_NOMBRE,  @v_REPCALC_SELECT,  @v_REPCALC_REQ,  @v_OUV_NOTEPAR, @v_OUV_NAPOSS, @v_DESCRIPTION, @v_QST_OBLIG 
            END
        CLOSE questions_list
        DEALLOCATE questions_list

        -- on insère les tables de réponse
        INSERT INTO CAMP_QST_R (ID_CAMP, ID_THEME, ID_QUEST, ID_REP, TXT_REP, COM_MANUEL, NOTE, COM_OBLIG, DESCRIPTION)
            SELECT @p_i_Campaign_Id, 'C' + RIGHT(q.ID_THEME, LEN(q.ID_THEME) - 1), cq.ID_QUEST, r.ID_REP, r.TXT_REP, r.COM_MANUEL, r.NOTE, r.COM_OBLIG, r.DESCRIPTION 
            FROM GRIL_QST_R r
                LEFT JOIN GRIL_QST_Q q
                    ON q.ID_QUEST = r.ID_QUEST
                LEFT JOIN CAMP_QST_Q cq
                    ON CAST(RIGHT(cq.G_B_QST_LIE, LEN(cq.G_B_QST_LIE) - 1) AS INT) = q.ID_QUEST
                        AND cq.ID_CAMP = @p_i_Campaign_Id
            WHERE q.ID_MODEL = @p_i_Model_Id
        ";

        $params = [
            ':ID_CAMP' => [
                'value' => $campaignId,
                'option' => PDO::PARAM_INT
            ],
            ':ID_MODEL' => [
                'value' => $modelId,
                'option' => PDO::PARAM_STR
            ],
        ];

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

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

        $data->execute();

        return new Response();
    }

    /**
     * importThemeFromLibrary
     *
     * @param  string $mode
     * @param  string $modeId
     * @param  string $theme
     * @param  string $targetedTheme
     * @return Response
     */
    public function importThemeFromLibrary(string $mode, $modeId, string $theme, $targetedTheme = '')
    {
        switch ($mode) {
            case 'campagne':
                $this->importLibraryIntoCampaign($modeId, $theme, $targetedTheme);
                return new Response();

            case 'modele':
                $this->importLibraryIntoModel($modeId, $theme, $targetedTheme);
                return new Response();

            default:
                $tableName = '';
                $tableId = '';
                break;
        }

        return new Response();
    }

    /**
     * importLibraryIntoCampaign
     *
     * @param  string $campaignId
     * @param  string $libraryTheme
     * @param  string $targetedTheme
     * @return Response
     */
    public function importLibraryIntoCampaign($campaignId, string $libraryTheme, string $targetedTheme = '')
    {
        $query = "SELECT ''
        DECLARE 
        @p_i_Campaign_Id        INT, 
        @p_i_Theme_Id           VARCHAR(10) = '', 
        @p_i_Theme_Id_Cible     VARCHAR(10) = '', 
        @v_ID_THEME             VARCHAR(10),
        @v_ID_PERE              VARCHAR(10),
        @v_LIB_THEME            VARCHAR(60),
        @v_NM_COM               INT = 0,
        @v_NM_ORDRE             INT = 0,
        @v_POIDS                INT = 0,
        @v_THEME_LIE            VARCHAR(10),
        @v_DESCRIPTION          VARCHAR(3000),
        @v_ID_CAMP              INT = 0,
        @v_QUESTION_ID_THEME    VARCHAR(10),
        @v_ID_QUEST             INT = 0,
        @v_G_B_QST_LIE          VARCHAR(11),
        @v_LIB_QUEST            VARCHAR(120),
        @v_QUEST_FOUR           BINARY(1),
        @v_TYPE_REP             VARCHAR(1),
        @v_NOTE_FAUX            INT = 0,
        @v_NOTE_VRAI            INT = 0,
        @v_NUM_FORMAT           VARCHAR(10),
        @v_NUM_MIN              VARCHAR(18),
        @v_NUM_MAX              VARCHAR(18),
        @v_QUESTION_POIDS       INT = 0,
        @v_QUESTION_NM_ORDRE    INT = 0,
        @v_ID_MODEL             VARCHAR(10),
        @v_PROFNOTEUR1          VARCHAR(30),
        @v_PROFNOTEUR2          VARCHAR(30),
        @v_PROFNOTEUR1_theme    VARCHAR(30),
        @v_PROFNOTEUR2_theme    VARCHAR(30),
        @v_AUTHCOMMENT          BINARY(1),
        @v_REGCOMOBLIG          VARCHAR(50),
        @v_TYP_NOMBRE           VARCHAR(1),
        @v_REPCALC_SELECT       VARCHAR(200),
        @v_REPCALC_REQ          VARCHAR(3000),
        @v_OUV_NOTEPAR          VARCHAR(1),
        @v_OUV_NAPOSS           BINARY(1),
        @v_QUESTION_DESCRIPTION VARCHAR(3000),
        @v_QST_OBLIG            BINARY(1)
        
        SET @p_i_Theme_Id = :THEME_ID
        SET @p_i_Campaign_Id = :CAMPAIGN_ID
        SET @p_i_Theme_Id_Cible = :THEME_CIBLE;

        SELECT @v_PROFNOTEUR1_theme = CASE
                WHEN @p_i_Theme_Id_Cible <> '' 
                THEN PROFNOTEUR1
                ELSE ''
            END, @v_PROFNOTEUR2_theme = CASE
                WHEN @p_i_Theme_Id_Cible <> '' 
                THEN PROFNOTEUR2
                ELSE ''
            END FROM CAMP_QST_TH WHERE ID_CAMP = @p_i_Campaign_Id AND ID_THEME = @p_i_Theme_Id_Cible;
        
        CREATE TABLE #TEMP_TABLE (ID_THEME varchar (10), ID_PERE varchar (10), LIB_THEME varchar (60), NM_COM int, POIDS int, NM_ORDRE int, PROFNOTEUR1 varchar (30), PROFNOTEUR2 varchar (30));
        
        INSERT INTO #TEMP_TABLE (ID_THEME, ID_PERE, LIB_THEME, NM_COM, POIDS, NM_ORDRE, PROFNOTEUR1, PROFNOTEUR2)
        (SELECT t1.ID_THEME, '', t1.LIB_THEME, t1.NM_COM, t1.POIDS, t1.NM_ORDRE, t1.PROFNOTEUR1, t1.PROFNOTEUR2  
            FROM BIB_QST_TH t1
            WHERE t1.ID_THEME = @p_i_Theme_Id);
        
        WITH first_theme_data (ID_THEME, ID_PERE, LIB_THEME, NM_COM, POIDS, NM_ORDRE, PROFNOTEUR1, PROFNOTEUR2)
        AS
        (
            SELECT t1.ID_THEME, t1.ID_PERE, t1.LIB_THEME, t1.NM_COM, t1.POIDS, t1.NM_ORDRE, t1.PROFNOTEUR1, t1.PROFNOTEUR2 
            FROM BIB_QST_TH t1
            WHERE t1.ID_PERE = @p_i_Theme_Id
                UNION ALL
            SELECT t1.ID_THEME, t1.ID_PERE, t1.LIB_THEME, t1.NM_COM, t1.POIDS, t1.NM_ORDRE, t1.PROFNOTEUR1, t1.PROFNOTEUR2 
            FROM BIB_QST_TH t1
                INNER JOIN first_theme_data 
                ON first_theme_data.ID_THEME = t1.ID_PERE
        )
        -- references expression name
        INSERT INTO #TEMP_TABLE
        (ID_THEME, ID_PERE, LIB_THEME, NM_COM, POIDS, NM_ORDRE, PROFNOTEUR1, PROFNOTEUR2)
        
        SELECT *
        FROM first_theme_data
        ORDER BY first_theme_data.ID_THEME 
                    
        DECLARE themes_info CURSOR FOR
            SELECT * 
            FROM #TEMP_TABLE
        
        OPEN themes_info  
            FETCH NEXT FROM themes_info 
                INTO @v_ID_THEME, @v_ID_PERE, @v_LIB_THEME, @v_NM_COM, @v_POIDS, @v_NM_ORDRE, @v_PROFNOTEUR1, @v_PROFNOTEUR2 
            WHILE @@FETCH_STATUS = 0
            BEGIN
                INSERT INTO CAMP_QST_TH (ID_CAMP, ID_THEME, ID_PERE, LIB_THEME, NM_ORDRE, POIDS, THEME_LIE, DESCRIPTION, PROFNOTEUR1, PROFNOTEUR2)
                    VALUES (@p_i_Campaign_Id, 'C' + RIGHT(@v_ID_THEME, LEN(@v_ID_THEME) - 1), 
                        CASE
                            WHEN @v_ID_PERE <> ''
                            THEN 'C' + RIGHT(@v_ID_PERE, LEN(@v_ID_PERE) - 1)
                            ELSE CASE
                                    WHEN @p_i_Theme_Id_Cible <> ''
                                    THEN @p_i_Theme_Id_Cible
                                    ELSE ''
                                END 
                        END, @v_LIB_THEME, (SELECT ISNULL(MAX(NM_ORDRE), 0) + 1 FROM CAMP_QST_TH WITH(NOLOCK) WHERE ID_CAMP = @p_i_Campaign_Id), @v_POIDS, @v_ID_THEME, 
                        (SELECT
                            CASE 
                                WHEN @v_NM_COM <> 0
                                THEN ISNULL(M.CONTMEMO, '')
                                ELSE ''
                            END 
                        FROM BIB_QST_TH T
                        LEFT JOIN MEMOS M on M.NODEMEMO = T.NM_COM
                        WHERE ID_THEME = @v_ID_THEME), 
                        CASE 
                            WHEN @v_PROFNOTEUR1_theme <> ''
                            THEN @v_PROFNOTEUR1_theme
                            ELSE @v_PROFNOTEUR1
                        END,
                        CASE 
                            WHEN @v_PROFNOTEUR1_theme <> ''
                            THEN @v_PROFNOTEUR2_theme
                            ELSE @v_PROFNOTEUR2
                        END)
        
                DECLARE questions_list CURSOR FOR
                    SELECT ID_QUEST, LIB_QUEST, NM_COM, QUEST_FOUR, TYPE_REP, NOTE_FAUX, NOTE_VRAI, NUM_FORMAT, NUM_MIN, NUM_MAX, ID_THEME, POIDS, PROFNOTEUR1, 
                        PROFNOTEUR2, AUTHCOMMENT, REGCOMOBLIG, TYP_NOMBRE, REPCALC_SELECT, REPCALC_REQ, OUV_NOTEPAR, OUV_NAPOSS, NM_ORDRE, QST_OBLIG 
                    FROM BIB_QST_Q WHERE ID_THEME = @v_ID_THEME
                
                OPEN questions_list  
                    FETCH NEXT FROM questions_list 
                        INTO @v_ID_QUEST, @v_LIB_QUEST, @v_NM_COM, @v_QUEST_FOUR, @v_TYPE_REP, @v_NOTE_FAUX, @v_NOTE_VRAI, @v_NUM_FORMAT, @v_NUM_MIN, @v_NUM_MAX, @v_ID_THEME, @v_POIDS, @v_PROFNOTEUR1, @v_PROFNOTEUR2, @v_AUTHCOMMENT, @v_REGCOMOBLIG, @v_TYP_NOMBRE, @v_REPCALC_SELECT, @v_REPCALC_REQ, @v_OUV_NOTEPAR, @v_OUV_NAPOSS, @v_NM_ORDRE, @v_QST_OBLIG	
                    WHILE @@FETCH_STATUS = 0
                    BEGIN
                        INSERT INTO CAMP_QST_Q (ID_CAMP, ID_THEME, ID_QUEST, G_B_QST_LIE, LIB_QUEST, QUEST_FOUR, TYPE_REP, NOTE_FAUX, NOTE_VRAI, NUM_FORMAT, NUM_MIN, NUM_MAX, POIDS, NM_ORDRE, ID_MODEL, PROFNOTEUR1, PROFNOTEUR2, AUTHCOMMENT, REGCOMOBLIG, TYP_NOMBRE, REPCALC_SELECT, REPCALC_REQ, OUV_NOTEPAR, OUV_NAPOSS, DESCRIPTION, QST_OBLIG)
                            VALUES (
                            @p_i_Campaign_Id, 
                            'C' + RIGHT(@v_ID_THEME, LEN(@v_ID_THEME) - 1), 
                            (SELECT ISNULL(MAX(ID_QUEST), 0) + 1 FROM CAMP_QST_Q WITH(NOLOCK) WHERE ID_CAMP = @p_i_Campaign_Id AND ID_THEME = 'C' + RIGHT(@v_ID_THEME, LEN(@v_ID_THEME) - 1)), 
                            'B' + CAST(@v_ID_QUEST AS VARCHAR), 
                            @v_LIB_QUEST, 
                            CASE 
                                WHEN @v_PROFNOTEUR1_theme <> ''
                                THEN 0x46
                                ELSE @v_QUEST_FOUR
                            END, 
                            @v_TYPE_REP, 
                            @v_NOTE_FAUX, 
                            @v_NOTE_VRAI, 
                            @v_NUM_FORMAT, 
                            @v_NUM_MIN, 
                            @v_NUM_MAX, 
                            @v_POIDS, 
                            @v_NM_ORDRE,
                            '',
                            CASE 
                                WHEN @v_PROFNOTEUR1_theme <> ''
                                THEN @v_PROFNOTEUR1_theme
                                ELSE @v_PROFNOTEUR1
                            END,
                            CASE 
                                WHEN @v_PROFNOTEUR1_theme <> ''
                                THEN @v_PROFNOTEUR2_theme
                                ELSE @v_PROFNOTEUR2
                            END, 
                            @v_AUTHCOMMENT, 
                            @v_REGCOMOBLIG, 
                            @v_TYP_NOMBRE, 
                            @v_REPCALC_SELECT, 
                             @v_REPCALC_REQ,
                            CASE
                                WHEN @v_QUEST_FOUR = 0x54 AND @v_TYPE_REP = 'O'
                                THEN 'C'
                                ELSE @v_OUV_NOTEPAR
                            END, 
                            @v_OUV_NAPOSS, 
                            CASE 
                                WHEN @v_NM_COM <> 0
                                THEN (SELECT CONTMEMO FROM MEMOS WHERE NODEMEMO = @v_NM_COM)
                                ELSE ''
                            END,
                            @v_QST_OBLIG)

						-- si la question est de type 'T' (table de réponses) ou 'C' (calculée), on insère dans CAMP_QST_R les valeurs de GRIL_QST_R
						IF @v_TYPE_REP = 'C' OR @v_TYPE_REP = 'T' OR @v_TYPE_REP = 'N'
							BEGIN
								CREATE TABLE #TEMP_CAMP_QST_R(ID_CAMP int, ID_THEME VARCHAR(10), ID_QUEST int, ID_REP int, TXT_REP VARCHAR(120), COM_MANUEL binary(1), NOTE int, COM_OBLIG binary (1),  DESCRIPTION VArCHAR(3000))
        
								INSERT INTO #TEMP_CAMP_QST_R (ID_REP, TXT_REP, COM_MANUEL, NOTE, COM_OBLIG, DESCRIPTION)
									(SELECT ID_REP, TXT_REP, COM_MANUEL, NOTE, COM_OBLIG, 
										CASE 
											WHEN @v_NM_COM <> 0
											THEN (SELECT CONTMEMO FROM MEMOS WHERE NODEMEMO = @v_NM_COM)
											ELSE ''
										END 
									FROM BIB_QST_R WHERE ID_QUEST = @v_ID_QUEST)
								UPDATE #TEMP_CAMP_QST_R 
									SET ID_CAMP = @p_i_Campaign_Id, ID_THEME = 'C' + RIGHT(@v_ID_THEME, LEN(@v_ID_THEME) - 1), ID_QUEST = (SELECT ID_QUEST FROM CAMP_QST_Q WHERE ID_CAMP = @p_i_Campaign_Id AND ID_THEME = 'C' + RIGHT(@v_ID_THEME, LEN(@v_ID_THEME) - 1) AND G_B_QST_LIE = 'B' + cast(@v_ID_QUEST as varchar))
        
								INSERT INTO CAMP_QST_R (ID_CAMP, ID_THEME, ID_QUEST, ID_REP, TXT_REP, COM_MANUEL, NOTE, COM_OBLIG, DESCRIPTION)
									SELECT ID_CAMP, ID_THEME, ID_QUEST, ID_REP, TXT_REP, COM_MANUEL, NOTE, COM_OBLIG, DESCRIPTION FROM #TEMP_CAMP_QST_R
        
								DROP TABLE #TEMP_CAMP_QST_R
							END

                        FETCH NEXT FROM questions_list 
                            INTO @v_ID_QUEST, @v_LIB_QUEST, @v_NM_COM, @v_QUEST_FOUR, @v_TYPE_REP, @v_NOTE_FAUX, @v_NOTE_VRAI, @v_NUM_FORMAT, @v_NUM_MIN, @v_NUM_MAX, @v_ID_THEME, @v_POIDS, @v_PROFNOTEUR1, @v_PROFNOTEUR2, @v_AUTHCOMMENT, @v_REGCOMOBLIG, @v_TYP_NOMBRE, @v_REPCALC_SELECT, @v_REPCALC_REQ, @v_OUV_NOTEPAR, @v_OUV_NAPOSS, @v_NM_ORDRE, @v_QST_OBLIG
                    END
                CLOSE questions_list
                DEALLOCATE questions_list 
        
                    
        
                -- on passe alors à la question suivante
                FETCH NEXT FROM themes_info 
                    INTO @v_ID_THEME, @v_ID_PERE, @v_LIB_THEME, @v_NM_COM, @v_POIDS, @v_NM_ORDRE, @v_PROFNOTEUR1, @v_PROFNOTEUR2
            END
        CLOSE themes_info
        DEALLOCATE themes_info
        DROP TABLE #TEMP_TABLE";

        $params = [
            ':THEME_ID' => [
                'value' => $libraryTheme,
                'option' => PDO::PARAM_STR
            ],
            ':CAMPAIGN_ID' => [
                'value' => $campaignId,
                'option' => PDO::PARAM_INT
            ],
            ':THEME_CIBLE' => [
                'value' => $targetedTheme,
                'option' => PDO::PARAM_STR
            ],
        ];

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

        $data->execute();

        return new Response();
    }

    /**
     * importLibraryIntoModel
     *
     * @param  string $modelId
     * @param  string $libraryTheme
     * @param  string $targetedTheme
     * @return Response
     */
    public function importLibraryIntoModel(string $modelId, string $libraryTheme, string $targetedTheme = '')
    {
        $query = "SELECT ''
        DECLARE 
        @p_i_Model_Id               VARCHAR(10) = '', 
        @p_i_Theme_Id               VARCHAR(10) = '', 
        @p_i_Theme_Id_Cible         VARCHAR(10) = '', 
        @v_ID_THEME                 VARCHAR(10),
        @v_ID_PERE                  VARCHAR(10),
        @v_LIB_THEME                VARCHAR(60),
        @v_NM_COM                   INT = 0,
        @v_NM_ORDRE                 INT = 0,
        @v_POIDS                    INT = 0,
        @v_THEME_LIE                VARCHAR(10),
        @v_DESCRIPTION              VARCHAR(3000),
        @v_ID_MODEL                 INT = 0,
        @v_QUESTION_ID_THEME        VARCHAR(10),
        @v_ID_QUEST                 INT = 0,
        @v_G_B_QST_LIE              VARCHAR(11),
        @v_LIB_QUEST                VARCHAR(120),
        @v_QUESTION_NM_COM          INT = 0,
        @v_QUEST_FOUR               BINARY(1),
        @v_TYPE_REP                 VARCHAR(1),
        @v_NOTE_FAUX                INT = 0,
        @v_NOTE_VRAI                INT = 0,
        @v_NUM_FORMAT               VARCHAR(10),
        @v_NUM_MIN                  VARCHAR(18),
        @v_NUM_MAX                  VARCHAR(18),
        @v_QUESTION_POIDS           INT = 0,
        @v_QUESTION_NM_ORDRE        INT = 0,
        @v_PROFNOTEUR1              VARCHAR(30),
        @v_PROFNOTEUR2              VARCHAR(30),
        @v_AUTHCOMMENT              BINARY(1),
        @v_REGCOMOBLIG              VARCHAR(50),
        @v_TYP_NOMBRE               VARCHAR(1),
        @v_REPCALC_SELECT           VARCHAR(200),
        @v_REPCALC_REQ              VARCHAR(3000),
        @v_OUV_NOTEPAR              VARCHAR(1),
        @v_OUV_NAPOSS               BINARY(1),
        @v_QUESTION_DESCRIPTION     VARCHAR(3000),
        @v_QST_OBLIG                BINARY(1)
        
        SET @p_i_Theme_Id = :THEME_ID
        SET @p_i_Model_Id = :MODEL_ID
        SET @p_i_Theme_Id_Cible = :TARGETED_THEME_ID;
        
        CREATE TABLE #TEMP_GRIL_THEME (ID_THEME VARCHAR (10), ID_PERE VARCHAR (10), LIB_THEME VARCHAR (60), NM_COM INT, POIDS INT, NM_ORDRE INT);

        CREATE TABLE #TEMP_GRIL_QST_R(ID_QUEST int, ID_REP int, TXT_REP VARCHAR(120), COM_MANUEL binary(1), NOTE int, COM_OBLIG binary (1), DESCRIPTION VArCHAR(3000));

        INSERT INTO #TEMP_GRIL_THEME (ID_THEME, ID_PERE, LIB_THEME, NM_COM, POIDS, NM_ORDRE)
        (SELECT t1.ID_THEME, '', t1.LIB_THEME, t1.NM_COM, t1.POIDS, t1.NM_ORDRE 
            FROM BIB_QST_TH t1
            WHERE t1.ID_THEME = @p_i_Theme_Id);

        WITH first_theme_data (ID_THEME, ID_PERE, LIB_THEME, NM_COM, POIDS, NM_ORDRE)
        AS
        (
            SELECT t1.ID_THEME, t1.ID_PERE, t1.LIB_THEME, t1.NM_COM, t1.POIDS, t1.NM_ORDRE 
            FROM BIB_QST_TH t1
            WHERE t1.ID_PERE = @p_i_Theme_Id
            UNION ALL
            SELECT t1.ID_THEME, t1.ID_PERE, t1.LIB_THEME, t1.NM_COM, t1.POIDS, t1.NM_ORDRE 
            FROM BIB_QST_TH t1
            INNER JOIN first_theme_data ON first_theme_data.ID_THEME = t1.ID_PERE
        )
        -- references expression name
        INSERT INTO #TEMP_GRIL_THEME
        (ID_THEME, ID_PERE, LIB_THEME, NM_COM, POIDS, NM_ORDRE)
            SELECT *
            FROM first_theme_data
            ORDER BY first_theme_data.ID_THEME 
            
        DECLARE themes_info CURSOR FOR
            SELECT * 
            FROM #TEMP_GRIL_THEME

        OPEN themes_info  
            FETCH NEXT FROM themes_info 
                INTO @v_ID_THEME, @v_ID_PERE, @v_LIB_THEME, @v_NM_COM, @v_POIDS, @v_NM_ORDRE  
            WHILE @@FETCH_STATUS = 0
            BEGIN
                INSERT INTO GRIL_THEME (ID_MODEL, ID_THEME, ID_PERE, LIB_THEME, NM_ORDRE, POIDS, THEME_LIE, DESCRIPTION)
                    VALUES (@p_i_Model_Id, 'G' + RIGHT(@v_ID_THEME, LEN(@v_ID_THEME) - 1), 
                        CASE
                            WHEN @v_ID_PERE <> ''
                            THEN 'G' + RIGHT(@v_ID_PERE, LEN(@v_ID_PERE) - 1)
                            ELSE CASE
                                    WHEN @p_i_Theme_Id_Cible <> ''
                                    THEN @p_i_Theme_Id_Cible
                                    ELSE ''
                                END 
                        END, @v_LIB_THEME, (SELECT ISNULL(MAX(NM_ORDRE), 0) + 1 FROM GRIL_THEME WITH(NOLOCK) WHERE ID_MODEL = @p_i_Model_Id), @v_POIDS, @v_ID_THEME, 
                        (SELECT
                            CASE 
                                WHEN @v_NM_COM <> 0
                                THEN ISNULL(M.CONTMEMO, '')
                                ELSE ''
                            END 
                        FROM BIB_QST_TH T
                        LEFT JOIN MEMOS M ON M.NODEMEMO = T.NM_COM
                        WHERE ID_THEME = @v_ID_THEME))

                DECLARE questions_list CURSOR FOR
                    SELECT ID_QUEST, LIB_QUEST, NM_COM, QUEST_FOUR, TYPE_REP, NOTE_FAUX, NOTE_VRAI, NUM_FORMAT, NUM_MIN, NUM_MAX, ID_THEME, POIDS, PROFNOTEUR1, 
                        PROFNOTEUR2, AUTHCOMMENT, REGCOMOBLIG, TYP_NOMBRE, REPCALC_SELECT, REPCALC_REQ, OUV_NOTEPAR, OUV_NAPOSS, NM_ORDRE 
                    FROM BIB_QST_Q WHERE ID_THEME = @v_ID_THEME
                        
                OPEN questions_list  
                    FETCH NEXT FROM questions_list 
                        INTO @v_ID_QUEST, @v_LIB_QUEST, @v_NM_COM, @v_QUEST_FOUR, @v_TYPE_REP, @v_NOTE_FAUX, @v_NOTE_VRAI, @v_NUM_FORMAT, @v_NUM_MIN, @v_NUM_MAX, @v_ID_THEME, @v_POIDS, @v_PROFNOTEUR1, @v_PROFNOTEUR2, @v_AUTHCOMMENT, @v_REGCOMOBLIG, @v_TYP_NOMBRE, @v_REPCALC_SELECT, @v_REPCALC_REQ, @v_OUV_NOTEPAR, @v_OUV_NAPOSS, @v_NM_ORDRE, @v_QST_OBLIG
                    WHILE @@FETCH_STATUS = 0
                    BEGIN
                        INSERT INTO GRIL_QST_Q (ID_QUEST, ID_QUEST_BIBLI, LIB_QUEST, QUEST_FOUR, TYPE_REP, NOTE_FAUX, NOTE_VRAI, NUM_FORMAT, NUM_MIN, NUM_MAX, ID_THEME, NM_ORDRE, POIDS, ID_MODEL, PROFNOTEUR1, PROFNOTEUR2, AUTHCOMMENT, REGCOMOBLIG, TYP_NOMBRE, REPCALC_SELECT, REPCALC_REQ, OUV_NOTEPAR, OUV_NAPOSS, DESCRIPTION, QST_OBLIG)
                            VALUES (
                            (SELECT ISNULL(MAX(ID_QUEST), 0) + 1 FROM GRIL_QST_Q WITH(NOLOCK)), 
                            @v_ID_QUEST, 
                            @v_LIB_QUEST, 
                            @v_QUEST_FOUR, 
                            @v_TYPE_REP, 
                            @v_NOTE_FAUX, 
                            @v_NOTE_VRAI, 
                            @v_NUM_FORMAT, 
                            @v_NUM_MIN, 
                            @v_NUM_MAX, 
                            'G' + RIGHT(@v_ID_THEME, LEN(@v_ID_THEME) - 1), 
                            @v_NM_ORDRE,
                            @v_POIDS, 
                            @p_i_Model_Id, 
                            @v_PROFNOTEUR1, 
                            @v_PROFNOTEUR2, 
                            @v_AUTHCOMMENT, 
                            @v_REGCOMOBLIG, 
                            @v_TYP_NOMBRE, 
                            @v_REPCALC_SELECT, 
                            @v_REPCALC_REQ, 
                            @v_OUV_NOTEPAR, 
                            @v_OUV_NAPOSS, 
                            CASE 
                                WHEN @v_NM_COM <> 0
                                THEN (SELECT CONTMEMO FROM MEMOS WHERE NODEMEMO = @v_NM_COM)
                                ELSE ''
                            END,
                            @v_QST_OBLIG
                            )
                        TRUNCATE TABLE #TEMP_GRIL_QST_R
                        -- si la question est de type 'T' (table de réponses) ou 'C' (calculée), on insère dans CAMP_QST_R les valeurs de GRIL_QST_R
                        IF @v_TYPE_REP = 'C' OR @v_TYPE_REP = 'T' OR @v_TYPE_REP = 'N'
                            BEGIN
                                INSERT INTO #TEMP_GRIL_QST_R (ID_REP, TXT_REP, COM_MANUEL, NOTE, COM_OBLIG, DESCRIPTION)
                                    (SELECT ID_REP, TXT_REP, COM_MANUEL, NOTE, COM_OBLIG, 
                                        CASE 
                                            WHEN @v_NM_COM <> 0
                                            THEN (SELECT CONTMEMO FROM MEMOS WHERE NODEMEMO = @v_NM_COM)
                                            ELSE ''
                                        END 
                                    FROM BIB_QST_R WHERE ID_QUEST = @v_ID_QUEST)
                                UPDATE #TEMP_GRIL_QST_R 
                                    SET ID_QUEST = (SELECT ISNULL(MAX(ID_QUEST), 0) FROM GRIL_QST_Q WITH(NOLOCK))

                                INSERT INTO GRIL_QST_R (ID_QUEST, ID_REP, TXT_REP, COM_MANUEL, NOTE, COM_OBLIG, DESCRIPTION)
                                    SELECT ID_QUEST, ID_REP, TXT_REP, COM_MANUEL, NOTE, COM_OBLIG, DESCRIPTION FROM #TEMP_GRIL_QST_R
                            END

                        FETCH NEXT FROM questions_list 
                            INTO @v_ID_QUEST, @v_LIB_QUEST, @v_NM_COM, @v_QUEST_FOUR, @v_TYPE_REP, @v_NOTE_FAUX, @v_NOTE_VRAI, @v_NUM_FORMAT, @v_NUM_MIN, @v_NUM_MAX, @v_ID_THEME, @v_POIDS, @v_PROFNOTEUR1, @v_PROFNOTEUR2, @v_AUTHCOMMENT, @v_REGCOMOBLIG, @v_TYP_NOMBRE, @v_REPCALC_SELECT, @v_REPCALC_REQ, @v_OUV_NOTEPAR, @v_OUV_NAPOSS, @v_NM_ORDRE, @v_QST_OBLIG	
                    END
                CLOSE questions_list
                DEALLOCATE questions_list 

                -- on passe au thème suivant
                FETCH NEXT FROM themes_info 
                    INTO @v_ID_THEME, @v_ID_PERE, @v_LIB_THEME, @v_NM_COM, @v_POIDS, @v_NM_ORDRE  
            END
        CLOSE themes_info
        DEALLOCATE themes_info
        DROP TABLE #TEMP_GRIL_THEME
        DROP TABLE #TEMP_GRIL_QST_R";

        $params = [
            ':THEME_ID' => [
                'value' => $libraryTheme,
                'option' => PDO::PARAM_STR
            ],
            ':MODEL_ID' => [
                'value' => $modelId,
                'option' => PDO::PARAM_STR
            ],
            ':TARGETED_THEME_ID' => [
                'value' => $targetedTheme,
                'option' => PDO::PARAM_STR
            ],
        ];

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

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

        $data->execute();

        return new Response();
    }

    /**
     * importQuestionsFromLibrary
     *
     * @param  string $mode
     * @param  string $modeId
     * @param  array<mixed> $questionsList
     * @param  string $idThemeCible
     * @return Response
     */
    public function importQuestionsFromLibrary(string $mode, $modeId, string $idThemeCible, array $questionsList)
    {
        $questionsIds = " ( ";
        foreach ($questionsList as $question) {
            $questionsIds .= " $question, ";
        }
        $questionsIds = \rtrim($questionsIds, ', ') . ') ';

        switch ($mode) {
            case 'campagne':
                $this->importQuestionsIntoCampaign($modeId, $questionsIds, $idThemeCible);
                break;

            case 'modele':
                $this->importQuestionsIntoModel($modeId, $questionsIds, $idThemeCible);
                break;

            default:
                break;
        }

        return new Response();
    }

    /**
     * importQuestionsIntoCampaign
     *
     * @param  string $campaignId
     * @param  string $questionsIds
     * @param  string $targetedThemeId
     * @return Response
     */
    public function importQuestionsIntoCampaign($campaignId, string $questionsIds, string $targetedThemeId)
    {
        $query = "SELECT ''
        DECLARE 
        @p_i_Campaign_Id            INT, 
        @p_i_Theme_Id_Cible         VARCHAR(10) = '', 
        @v_ID_THEME                 VARCHAR(10),
        @v_NM_COM                   INT = 0,
        @v_NM_ORDRE                 INT = 0,
        @v_POIDS                    INT = 0,
        @v_THEME_LIE                VARCHAR(10),
        @v_DESCRIPTION              VARCHAR(3000),
        @v_ID_MODEL                 INT = 0,
        @v_QUESTION_ID_THEME        VARCHAR(10),
        @v_ID_QUEST                 INT = 0,
        @v_G_B_QST_LIE              VARCHAR(11),
        @v_LIB_QUEST                VARCHAR(120),
        @v_QUESTION_NM_COM          INT = 0,
        @v_QUEST_FOUR               BINARY(1),
        @v_TYPE_REP                 VARCHAR(1),
        @v_NOTE_FAUX                INT = 0,
        @v_NOTE_VRAI                INT = 0,
        @v_NUM_FORMAT               VARCHAR(10),
        @v_NUM_MIN                  VARCHAR(18),
        @v_NUM_MAX                  VARCHAR(18),
        @v_QUESTION_POIDS           INT = 0,
        @v_QUESTION_NM_ORDRE        INT = 0,
        @v_PROFNOTEUR1              VARCHAR(30),
        @v_PROFNOTEUR2              VARCHAR(30),
        @v_PROFNOTEUR1_theme        VARCHAR(30),
        @v_PROFNOTEUR2_theme        VARCHAR(30),
        @v_AUTHCOMMENT              BINARY(1),
        @v_REGCOMOBLIG              VARCHAR(50),
        @v_TYP_NOMBRE               VARCHAR(1),
        @v_REPCALC_SELECT           VARCHAR(200),
        @v_REPCALC_REQ              VARCHAR(3000),
        @v_OUV_NOTEPAR              VARCHAR(1),
        @v_OUV_NAPOSS               BINARY(1),
        @v_QUESTION_DESCRIPTION     VARCHAR(3000), 
        @v_ID_REP					INT,
        @v_TXT_REP					VARCHAR(120),
        @v_COM_MANUEL				BINARY(1),
        @v_NOTE						INT,
        @v_COM_OBLIG				BINARY(1),
        @v_QST_OBLIG                BINARY(1)
        
        SET @p_i_Campaign_Id = :CAMPAIGN_ID
        SET @p_i_Theme_Id_Cible = :TARGETED_THEME_ID;

        SELECT @v_PROFNOTEUR1_theme = PROFNOTEUR1, @v_PROFNOTEUR2_theme = PROFNOTEUR2 
        FROM CAMP_QST_TH
        WHERE ID_CAMP = @p_i_Campaign_Id AND ID_THEME = @p_i_Theme_Id_Cible;
        
        DECLARE questions_info CURSOR FOR
            SELECT ID_QUEST, LIB_QUEST, NM_COM, QUEST_FOUR, TYPE_REP, NOTE_FAUX, NOTE_VRAI, NUM_FORMAT, NUM_MIN, NUM_MAX, ID_THEME, POIDS, PROFNOTEUR1, 
                PROFNOTEUR2, AUTHCOMMENT, REGCOMOBLIG, TYP_NOMBRE, REPCALC_SELECT, REPCALC_REQ, OUV_NOTEPAR, OUV_NAPOSS, NM_ORDRE 
            FROM BIB_QST_Q WHERE ID_QUEST IN " . $questionsIds . "
            
            -- on ouvre une première boucle sur les questions de la bibliothèque qu'on souhaite importer
        OPEN questions_info  
            FETCH NEXT FROM questions_info 
                INTO @v_ID_QUEST, @v_LIB_QUEST, @v_NM_COM, @v_QUEST_FOUR, @v_TYPE_REP, @v_NOTE_FAUX, @v_NOTE_VRAI, @v_NUM_FORMAT, @v_NUM_MIN, @v_NUM_MAX, @v_ID_THEME, @v_POIDS, @v_PROFNOTEUR1, @v_PROFNOTEUR2, @v_AUTHCOMMENT, @v_REGCOMOBLIG, @v_TYP_NOMBRE, @v_REPCALC_SELECT, @v_REPCALC_REQ, @v_OUV_NOTEPAR, @v_OUV_NAPOSS, @v_NM_ORDRE, @v_QST_OBLIG  
            WHILE @@FETCH_STATUS = 0
            BEGIN
                -- pour chacune de ces questions, on insère dans la table des modèles en changeant les colonnes ID_QUEST, NM_ORDRE
                INSERT INTO CAMP_QST_Q 
                                (
                                    ID_CAMP,
                                    ID_THEME,
                                    ID_QUEST,
                                    G_B_QST_LIE,
                                    LIB_QUEST,
                                    QUEST_FOUR,
                                    TYPE_REP,
                                    NOTE_FAUX,
                                    NOTE_VRAI,
                                    NUM_FORMAT,
                                    NUM_MIN,
                                    NUM_MAX,
                                    POIDS,
                                    NM_ORDRE,
                                    ID_MODEL,
                                    PROFNOTEUR1,
                                    PROFNOTEUR2,
                                    AUTHCOMMENT,
                                    REGCOMOBLIG,
                                    TYP_NOMBRE,
                                    REPCALC_SELECT,
                                    REPCALC_REQ,
                                    OUV_NOTEPAR,
                                    OUV_NAPOSS,
                                    DESCRIPTION,
                                    QST_OBLIG
                                )
                    VALUES 
                        (
                            @p_i_Campaign_Id,
                            @p_i_Theme_Id_Cible, 
                            (SELECT ISNULL(MAX(ID_QUEST), 0) + 1 FROM CAMP_QST_Q WITH(NOLOCK) WHERE ID_CAMP = @p_i_Campaign_Id AND ID_THEME = @p_i_Theme_Id_Cible),
                            'B' + CAST(@v_ID_QUEST AS VARCHAR), 
                            @v_LIB_QUEST, 
                            CASE 
                                WHEN @v_PROFNOTEUR1_theme <> ''
                                THEN 0x46
                                ELSE @v_QUEST_FOUR
                            END,  
                            @v_TYPE_REP, 
                            @v_NOTE_FAUX, 
                            @v_NOTE_VRAI, 
                            @v_NUM_FORMAT, 
                            @v_NUM_MIN, 
                            @v_NUM_MAX, 
                            @v_POIDS, 
                            (SELECT ISNULL(MAX(NM_ORDRE), 0) + 1 FROM CAMP_QST_Q WITH(NOLOCK) WHERE ID_CAMP = @p_i_Campaign_Id AND ID_THEME = @p_i_Theme_Id_Cible), 
                            '', 
                            CASE 
                                WHEN @v_PROFNOTEUR1_theme <> ''
                                THEN @v_PROFNOTEUR1_theme
                                ELSE @v_PROFNOTEUR1
                            END, 
                            CASE 
                                WHEN @v_PROFNOTEUR1_theme <> ''
                                THEN @v_PROFNOTEUR2_theme
                                ELSE @v_PROFNOTEUR2
                            END, 
                            @v_AUTHCOMMENT, 
                            @v_REGCOMOBLIG, 
                            @v_TYP_NOMBRE, 
                            @v_REPCALC_SELECT, 
                            @v_REPCALC_REQ, 
                            @v_OUV_NOTEPAR, 
                            @v_OUV_NAPOSS, 
                            CASE 
                                WHEN @v_NM_COM <> 0
                                THEN (SELECT CONTMEMO FROM MEMOS WHERE NODEMEMO = @v_NM_COM)
                                ELSE ''
                            END,
                            @v_QST_OBLIG
                        )
        
                        -- on ouvre un second curseur, pour parcourir les éventuelles réponses associées à la question qu'on souhaite importer
                    DECLARE answers_list CURSOR FOR 
                        SELECT * FROM BIB_QST_R WHERE ID_QUEST = @v_ID_QUEST
        
                    OPEN answers_list  
                        FETCH NEXT FROM answers_list 
                            INTO @v_ID_QUEST, @v_ID_REP, @v_TXT_REP, @v_NM_COM, @v_COM_MANUEL, @v_NOTE, @v_COM_OBLIG
                        WHILE @@FETCH_STATUS = 0
                        BEGIN
                            -- on insère les valeurs trouvées dans la table de réponses dans le modèle, en changeant l'ID de la question et le NM_COM
                            INSERT INTO CAMP_QST_R 
                                                (
                                                    ID_CAMP,
                                                    ID_THEME,
                                                    ID_QUEST,
                                                    ID_REP,
                                                    TXT_REP,
                                                    COM_MANUEL,
                                                    NOTE,
                                                    COM_OBLIG,
                                                    DESCRIPTION
                                                )
                                VALUES 
                                    (
                                        @p_i_Campaign_Id,
                                        @p_i_Theme_Id_Cible,
                                        (SELECT MAX(ID_QUEST) FROM CAMP_QST_Q WITH(NOLOCK) WHERE ID_CAMP = @p_i_Campaign_Id AND ID_THEME = @p_i_Theme_Id_Cible), 
                                        @v_ID_REP, 
                                        @v_TXT_REP, 
                                        @v_COM_MANUEL, 
                                        @v_NOTE, 
                                        @v_COM_OBLIG, 
                                        CASE 
                                            WHEN @v_NM_COM <> 0
                                            THEN (SELECT CONTMEMO FROM MEMOS WHERE NODEMEMO = @v_NM_COM)
                                            ELSE ''
                                        END
                                    )
                        
                            -- on passe à la réponse suivante
                            FETCH NEXT FROM answers_list 
                                INTO @v_ID_QUEST, @v_ID_REP, @v_TXT_REP, @v_NM_COM, @v_COM_MANUEL, @v_NOTE, @v_COM_OBLIG 
                        END
                    CLOSE answers_list
                    DEALLOCATE answers_list
        
                -- on passe à la question suivante
                FETCH NEXT FROM questions_info 
                    INTO @v_ID_QUEST, @v_LIB_QUEST, @v_NM_COM, @v_QUEST_FOUR, @v_TYPE_REP, @v_NOTE_FAUX, @v_NOTE_VRAI, @v_NUM_FORMAT, @v_NUM_MIN, @v_NUM_MAX, @v_ID_THEME, @v_POIDS, @v_PROFNOTEUR1, @v_PROFNOTEUR2, @v_AUTHCOMMENT, @v_REGCOMOBLIG, @v_TYP_NOMBRE, @v_REPCALC_SELECT, @v_REPCALC_REQ, @v_OUV_NOTEPAR, @v_OUV_NAPOSS, @v_NM_ORDRE, @v_QST_OBLIG
            END
        CLOSE questions_info
        DEALLOCATE questions_info";

        $params = [
            ':CAMPAIGN_ID' => [
                'value' => $campaignId,
                'option' => PDO::PARAM_INT
            ],
            ':TARGETED_THEME_ID' => [
                'value' => $targetedThemeId,
                'option' => PDO::PARAM_STR
            ],
        ];

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

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

        $data->execute();

        return new Response();
    }

    /**
     * importQuestionsIntoModel
     *
     * @param  string $modelId
     * @param  string $questionsIds
     * @param  string $targetedThemeId
     * @return Response
     */
    public function importQuestionsIntoModel(string $modelId, string $questionsIds, string $targetedThemeId)
    {
        $query = "SELECT ''
        DECLARE 
        @p_i_Model_Id               VARCHAR(10) = '', 
        @p_i_Theme_Id_Cible         VARCHAR(10) = '', 
        @v_ID_THEME                 VARCHAR(10),
        @v_NM_COM                   INT = 0,
        @v_NM_ORDRE                 INT = 0,
        @v_POIDS                    INT = 0,
        @v_THEME_LIE                VARCHAR(10),
        @v_DESCRIPTION              VARCHAR(3000),
        @v_ID_MODEL                 INT = 0,
        @v_QUESTION_ID_THEME        VARCHAR(10),
        @v_ID_QUEST                 INT = 0,
        @v_G_B_QST_LIE              VARCHAR(11),
        @v_LIB_QUEST                VARCHAR(120),
        @v_QUESTION_NM_COM          INT = 0,
        @v_QUEST_FOUR               BINARY(1),
        @v_TYPE_REP                 VARCHAR(1),
        @v_NOTE_FAUX                INT = 0,
        @v_NOTE_VRAI                INT = 0,
        @v_NUM_FORMAT               VARCHAR(10),
        @v_NUM_MIN                  VARCHAR(18),
        @v_NUM_MAX                  VARCHAR(18),
        @v_QUESTION_POIDS           INT = 0,
        @v_QUESTION_NM_ORDRE        INT = 0,
        @v_PROFNOTEUR1              VARCHAR(30),
        @v_PROFNOTEUR2              VARCHAR(30),
        @v_AUTHCOMMENT              BINARY(1),
        @v_REGCOMOBLIG              VARCHAR(50),
        @v_TYP_NOMBRE               VARCHAR(1),
        @v_REPCALC_SELECT           VARCHAR(200),
        @v_REPCALC_REQ              VARCHAR(3000),
        @v_OUV_NOTEPAR              VARCHAR(1),
        @v_OUV_NAPOSS               BINARY(1),
        @v_QUESTION_DESCRIPTION     VARCHAR(3000), 
        @v_ID_REP					INT,
        @v_TXT_REP					VARCHAR(120),
        @v_COM_MANUEL				BINARY(1),
        @v_NOTE						INT,
        @v_COM_OBLIG				BINARY(1),
        @v_QST_OBLIG				BINARY(1)
        
        SET @p_i_Model_Id = :MODEL_ID
        SET @p_i_Theme_Id_Cible = :TARGETED_THEME_ID;
        
        DECLARE questions_info CURSOR FOR
            SELECT ID_QUEST, LIB_QUEST, NM_COM, QUEST_FOUR, TYPE_REP, NOTE_FAUX, NOTE_VRAI, NUM_FORMAT, NUM_MIN, NUM_MAX, ID_THEME, POIDS, PROFNOTEUR1, 
                PROFNOTEUR2, AUTHCOMMENT, REGCOMOBLIG, TYP_NOMBRE, REPCALC_SELECT, REPCALC_REQ, OUV_NOTEPAR, OUV_NAPOSS, NM_ORDRE 
            FROM BIB_QST_Q WHERE ID_QUEST IN " . $questionsIds . "
            
            -- on ouvre une première boucle sur les questions de la bibliothèque qu'on souhaite importer
        OPEN questions_info  
            FETCH NEXT FROM questions_info 
                INTO @v_ID_QUEST, @v_LIB_QUEST, @v_NM_COM, @v_QUEST_FOUR, @v_TYPE_REP, @v_NOTE_FAUX, @v_NOTE_VRAI, @v_NUM_FORMAT, @v_NUM_MIN, @v_NUM_MAX, @v_ID_THEME, @v_POIDS, @v_PROFNOTEUR1, @v_PROFNOTEUR2, @v_AUTHCOMMENT, @v_REGCOMOBLIG, @v_TYP_NOMBRE, @v_REPCALC_SELECT, @v_REPCALC_REQ, @v_OUV_NOTEPAR, @v_OUV_NAPOSS, @v_NM_ORDRE, @v_QST_OBLIG  
            WHILE @@FETCH_STATUS = 0
            BEGIN
                -- pour chacune de ces questions, on insère dans la table des modèles en changeant les colonnes ID_QUEST, NM_ORDRE
                INSERT INTO GRIL_QST_Q 
                                (
                                    ID_QUEST, 
                                    ID_QUEST_BIBLI, 
                                    LIB_QUEST, 
                                    QUEST_FOUR, 
                                    TYPE_REP, 
                                    NOTE_FAUX, 
                                    NOTE_VRAI, 
                                    NUM_FORMAT, 
                                    NUM_MIN, 
                                    NUM_MAX, 
                                    ID_THEME, 
                                    NM_ORDRE, 
                                    POIDS, 
                                    ID_MODEL, 
                                    PROFNOTEUR1, 
                                    PROFNOTEUR2, 
                                    AUTHCOMMENT, 
                                    REGCOMOBLIG, 
                                    TYP_NOMBRE, 
                                    REPCALC_SELECT, 
                                    REPCALC_REQ, 
                                    OUV_NOTEPAR, 
                                    OUV_NAPOSS, 
                                    DESCRIPTION,
                                    QST_OBLIG
                                )
                    VALUES (
                        (
                            SELECT ISNULL(MAX(ID_QUEST), 0) + 1 FROM GRIL_QST_Q WITH(NOLOCK)), 
                            @v_ID_QUEST, 
                            @v_LIB_QUEST,  
                            @v_QUEST_FOUR, 
                            @v_TYPE_REP, 
                            @v_NOTE_FAUX, 
                            @v_NOTE_VRAI, 
                            @v_NUM_FORMAT, 
                            @v_NUM_MIN, 
                            @v_NUM_MAX, 
                            @p_i_Theme_Id_Cible, 
                            (SELECT ISNULL(MAX(NM_ORDRE), 0) + 1 FROM GRIL_QST_Q WITH(NOLOCK) WHERE ID_THEME = @p_i_Theme_Id_Cible), 
                            @v_POIDS, 
                            @p_i_Model_Id, 
                            @v_PROFNOTEUR1, 
                            @v_PROFNOTEUR2, 
                            @v_AUTHCOMMENT, 
                            @v_REGCOMOBLIG, 
                            @v_TYP_NOMBRE, 
                            @v_REPCALC_SELECT, 
                            @v_REPCALC_REQ, 
                            @v_OUV_NOTEPAR, 
                            @v_OUV_NAPOSS, 
                            CASE 
                                WHEN @v_NM_COM <> 0
                                THEN (SELECT CONTMEMO FROM MEMOS WHERE NODEMEMO = @v_NM_COM)
                                ELSE ''
                            END,
                            @v_QST_OBLIG
                        )
        
                        -- on ouvre un second curseur, pour parcourir les éventuelles réponses associées à la question qu'on souhaite importer
                    DECLARE answers_list CURSOR FOR 
                        SELECT * FROM BIB_QST_R WHERE ID_QUEST = @v_ID_QUEST
        
                    OPEN answers_list  
                        FETCH NEXT FROM answers_list 
                            INTO @v_ID_QUEST, @v_ID_REP, @v_TXT_REP, @v_NM_COM, @v_COM_MANUEL, @v_NOTE, @v_COM_OBLIG
                        WHILE @@FETCH_STATUS = 0
                        BEGIN
                            -- on insère les valeurs trouvées dans la table de réponses dans le modèle, en changeant l'ID de la question et le NM_COM
                            INSERT INTO GRIL_QST_R (ID_QUEST, ID_REP, TXT_REP, COM_MANUEL, NOTE, COM_OBLIG, DESCRIPTION)
                                VALUES (
                                    (SELECT MAX(ID_QUEST) FROM GRIL_QST_Q WITH(NOLOCK)), 
                                    @v_ID_REP, 
                                    @v_TXT_REP, 
                                    @v_COM_MANUEL, 
                                    @v_NOTE, 
                                    @v_COM_OBLIG, 
                                    CASE 
                                        WHEN @v_NM_COM <> 0
                                        THEN (SELECT CONTMEMO FROM MEMOS WHERE NODEMEMO = @v_NM_COM)
                                        ELSE ''
                                    END)
                        
                            -- on passe à la réponse suivante
                            FETCH NEXT FROM answers_list 
                                INTO @v_ID_QUEST, @v_ID_REP, @v_TXT_REP, @v_NM_COM, @v_COM_MANUEL, @v_NOTE, @v_COM_OBLIG
                        END
                    CLOSE answers_list
                    DEALLOCATE answers_list
        
                -- on passe à la question suivante
                FETCH NEXT FROM questions_info 
                    INTO @v_ID_QUEST, @v_LIB_QUEST, @v_NM_COM, @v_QUEST_FOUR, @v_TYPE_REP, @v_NOTE_FAUX, @v_NOTE_VRAI, @v_NUM_FORMAT, @v_NUM_MIN, @v_NUM_MAX, @v_ID_THEME, @v_POIDS, @v_PROFNOTEUR1, @v_PROFNOTEUR2, @v_AUTHCOMMENT, @v_REGCOMOBLIG, @v_TYP_NOMBRE, @v_REPCALC_SELECT, @v_REPCALC_REQ, @v_OUV_NOTEPAR, @v_OUV_NAPOSS, @v_NM_ORDRE, @v_QST_OBLIG  
            END
        CLOSE questions_info
        DEALLOCATE questions_info";

        $params = [
            ':MODEL_ID' => [
                'value' => $modelId,
                'option' => PDO::PARAM_STR
            ],
            ':TARGETED_THEME_ID' => [
                'value' => $targetedThemeId,
                'option' => PDO::PARAM_STR
            ],
        ];

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

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

        $data->execute();

        return new Response();
    }

    /**
     * importGroupsFromModel
     *
     * @param  int $campaignId
     * @param  string $model
     * @return Response
     */
    public function importGroupsFromModel(int $campaignId, string $model)
    {
        $query = "INSERT INTO CAMP_GRP_NOT(ID_CAMP, GROUPE)
            SELECT :ID_CAMP, PROFNOTEUR2
            FROM GRIL_QST_Q WHERE PROFNOTEUR1 = 'G'
                AND ID_MODEL = :ID_MODEL";

        $import = $this->pdo->prepare($query);
        $import->bindParam(':ID_CAMP', $campaignId, PDO::PARAM_INT);
        $import->bindParam(':ID_MODEL', $model, PDO::PARAM_STR);
        $import->execute();

        return new Response();
    }

    /**
     * getChildrenThemes
     *
     * @param  string $theme
     * @return array<mixed>
     */
    public function getChildrenThemes(string $theme): array
    {
        $query = "WITH ArboThemes (ID_THEME)  
            AS   
            (  
                SELECT ID_THEME
                FROM BIB_QST_TH 
                WHERE ID_PERE = :theme
                    UNION ALL
                SELECT e.ID_THEME 
                FROM BIB_QST_TH e
                    JOIN ArboThemes d ON e.ID_PERE = d.ID_THEME
            )   
            SELECT ID_THEME
            FROM ArboThemes ";

        return $this->fetchAssocAll($query, [':theme' => $theme]);
    }

    /**
     * importGroupsFromLibrary
     *
     * @param  string $mode
     * @param  mixed $modeId
     * @return Response
     */
    public function importGroupsFromLibrary(string $mode, $modeId, string $theme)
    {
        $query  = "";
        $import = '';
        switch ($mode) {
            case 'campagne':
                $query = "INSERT INTO CAMP_GRP_NOT(ID_CAMP, GROUPE)
                    SELECT DISTINCT :ID_CAMP, PROFNOTEUR2
                    FROM BIB_QST_Q WITH(NOLOCK)
                        LEFT JOIN CAMP_GRP_NOT cgn WITH(NOLOCK) ON cgn.GROUPE = PROFNOTEUR2 AND cgn.ID_CAMP = :ID_CAMP2
                    WHERE PROFNOTEUR1 = 'G'
                        AND ID_THEME IN ('{$theme}') 
                        AND cgn.GROUPE IS NULL";

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

                $import->bindParam(':ID_CAMP', $modeId, PDO::PARAM_INT);
                $import->bindParam(':ID_CAMP2', $modeId, PDO::PARAM_INT);
                break;

            case 'modele':
                $query = "INSERT INTO GRIL_GRP_NOT(ID_MODEL, GROUPE)
                SELECT DISTINCT :ID_MODEL, PROFNOTEUR2
                FROM BIB_QST_Q WITH(NOLOCK) 
                    LEFT JOIN GRIL_GRP_NOT ggn WITH(NOLOCK) ON ggn.GROUPE = PROFNOTEUR2 AND ggn.ID_MODEL = :ID_MODEL2
                WHERE PROFNOTEUR1 = 'G' 
                    AND ID_THEME IN ('{$theme}') 
                    AND ggn.GROUPE IS NULL ";

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

                $import->bindParam(':ID_MODEL', $modeId, PDO::PARAM_STR);
                $import->bindParam(':ID_MODEL2', $modeId, PDO::PARAM_STR);
                break;
        }

        /** @phpstan-ignore-next-line */
        $import->execute();

        return new Response();
    }

    /**
     * importFiltersFromModel
     * importe les filtres de campagne d'un modèle en excluant les valeurs qui existent déjà dans la campagne ciblée
     *
     * @param  int $campaignId
     * @param  string $modelId
     * @return Response
     */
    public function importFiltersFromModel(int $campaignId, string $modelId)
    {
        $query = "INSERT INTO CAMP_ASSO(ID_CAMP,TYPE_OBJ,VAL_OBJ,CODE_STE)
            SELECT DISTINCT :ID_CAMP, g.TYPE_OBJ, g.VAL_OBJ, g.CODE_STE
                FROM GRIL_ASSO g
                LEFT JOIN CAMP_ASSO c 
                    ON c.ID_CAMP = :ID_CAMP2
                WHERE ID_MODEL = :ID_MODEL
                    AND NOT EXISTS(SELECT *
                        FROM CAMP_ASSO c2
                        WHERE c2.ID_CAMP = :ID_CAMP3
                            AND c2.TYPE_OBJ = g.TYPE_OBJ
                            AND c2.VAL_OBJ = g.VAL_OBJ
                            AND c2.CODE_STE = g.CODE_STE)";

        $import = $this->pdo->prepare($query);
        $import->bindParam(':ID_CAMP', $campaignId, PDO::PARAM_INT);
        $import->bindParam(':ID_CAMP2', $campaignId, PDO::PARAM_INT);
        $import->bindParam(':ID_CAMP3', $campaignId, PDO::PARAM_INT);
        $import->bindParam(':ID_MODEL', $modelId, PDO::PARAM_STR);

        $import->execute();

        return new Response();
    }
}
