<?php

namespace Modules\Evaluation\Repository;

use Helpers\Filter\FilterTable;
use Helpers\Pagination\PaginatorGestion;
use Helpers\QueryBuilder\AbsRepository;
use Helpers\User\User;
use PDO;

class Rater extends AbsRepository
{
    /**
     * @var resource $pdoStorage
     */
    private $pdoStorage;

    /**
     * __construct
     *
     * @param  \PDO $pdo
     * @param  resource $pdoStorage
     */
    public function __construct(PDO $pdo, $pdoStorage)
    {
        parent::__construct($pdo);
        $this->pdoStorage = $pdoStorage;
    }

    /**
     * getGroupsOfRatersByCampaign
     *
     * @param  int $campaignId
     * @return array<mixed>
     */
    public function getGroupsOfRatersByCampaign(int $campaignId): array
    {
        $req = "SELECT C.GROUPE, STRING_AGG(H.NOM + ' (' + C.NOTEUR + ')', ',') AS noteurs 
                FROM CAMP_LST_NOT_GRP C WITH (NOLOCK)
                    LEFT JOIN HASTUTIL H WITH (NOLOCK)
                        ON C.NOTEUR = H.CODE
                WHERE C.ID_CAMP = $campaignId
                GROUP BY GROUPE";

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

    /**
     * getRatersByCampaign returns the list of raters for a campaign
     *
     * @param  int $campaignId
     * @param  array<mixed> $post
     * @return array<mixed>
     */
    public function getRatersByCampaign(int $campaignId, array $post = []): array
    {
        $req = "SELECT DISTINCT C.ID_CAMP, C.NOTEUR, 
                (CASE 
                    WHEN EXISTS (SELECT H.NOM FROM HASTUTIL H WITH(NOLOCK) WHERE H.CODE = C.NOTEUR) 
                    THEN (SELECT H.NOM FROM HASTUTIL H WITH(NOLOCK) WHERE H.CODE = C.NOTEUR) 
                    ELSE E.NOM_NOTEUR
                END) AS NOM,
                (CASE 
                    WHEN EXISTS (SELECT H.CODE_STE FROM HASTUTIL H WITH(NOLOCK) WHERE H.CODE = C.NOTEUR) 
                    THEN (SELECT H.CODE_STE FROM HASTUTIL H WITH(NOLOCK) WHERE H.CODE = C.NOTEUR) 
                    ELSE E.SOC_NOTEUR
                END) AS CODE_STE,
                (CASE 
                    WHEN EXISTS (SELECT H.FONCTION FROM HASTUTIL H WITH(NOLOCK) WHERE H.CODE = C.NOTEUR) 
                    THEN (SELECT H.FONCTION FROM HASTUTIL H WITH(NOLOCK) WHERE H.CODE = C.NOTEUR) 
                    ELSE E.FCT_NOTEUR
                END) AS FONCTION,
                (CASE 
                    WHEN EXISTS (SELECT H.EMAIL FROM HASTUTIL H WITH(NOLOCK) WHERE H.CODE = C.NOTEUR) 
                    THEN (SELECT H.EMAIL FROM HASTUTIL H WITH(NOLOCK) WHERE H.CODE = C.NOTEUR) 
                    ELSE E.EMAIL_NOTEUR
                END) AS EMAIL,
                (CASE 
                    WHEN EXISTS (SELECT H.CODE FROM HASTUTIL H WITH(NOLOCK) WHERE H.CODE = C.NOTEUR) 
                    THEN 'I' 
                    ELSE 'E'
                END) AS ORIGIN, 
                (CASE 
                    WHEN EXISTS (SELECT G.NOTEUR FROM CAMP_LST_NOT_GRP G WITH(NOLOCK) WHERE G.ID_CAMP = C.ID_CAMP AND C.NOTEUR = G.NOTEUR) 
                    THEN 'T' 
                    ELSE 'F'
                END) AS IN_GROUP
            FROM CAMP_LST_NOT_CAMP C WITH (NOLOCK)
                LEFT JOIN HASTUTIL H WITH (NOLOCK)
                    ON C.NOTEUR = H.CODE
                LEFT JOIN CAMP_NOTEX E WITH (NOLOCK)
                    ON C.NOTEUR = E.ID_NOTEUR
                LEFT JOIN CAMP_LST_NOT_GRP G WITH (NOLOCK)
                    ON G.ID_CAMP = C.ID_CAMP 
                        AND C.NOTEUR = G.NOTEUR
            WHERE C.ID_CAMP = $campaignId";

        $arrayReq = [];
        $filter = FilterTable::splitFilter($post, 0, 0, 1);
        if (count($filter) > 0 && isset($filter[1])) {
            $req .= $filter[0] . $filter['tri'];

            if (count($filter[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $filter[1]);
            }
        }
        
        $paginatorManagement = new PaginatorGestion();
        $paginatorManagement->injecterSqlCount(
            $req,
            $post,
            $arrayReq,
            $this->pdo
        );

        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
     * getRatersForEmailByCampaign
     *
     * @param  int $campaignId
     * @return array<mixed>
     */
    public function getRatersForEmailByCampaign(int $campaignId)
    {
        $req = "SELECT DISTINCT C.ID_CAMP, C.COD_NOTEUR, 
                    (CASE 
                        WHEN C.NOTEUR_EXTERNE = 0x46
                        THEN H.NOM
                        ELSE E.NOM_NOTEUR
                    END) AS NOM,
                    (CASE 
                        WHEN C.NOTEUR_EXTERNE = 0x46
                        THEN H.CODE_STE
                        ELSE E.SOC_NOTEUR
                    END) AS CODE_STE,
                    (CASE 
                        WHEN C.NOTEUR_EXTERNE = 0x46
                        THEN H.FONCTION
                        ELSE E.FCT_NOTEUR
                    END) AS FONCTION,
                    (CASE 
                        WHEN C.NOTEUR_EXTERNE = 0x46
                        THEN H.EMAIL
                        ELSE E.EMAIL_NOTEUR
                    END) AS EMAIL,
                    (CASE 
                        WHEN C.NOTEUR_EXTERNE = 0x46
                        THEN 'I' 
                        ELSE 'E'
                    END) AS ORIGIN,
                    (CASE 
                        WHEN C.NOTEUR_EXTERNE = 0x46
                        THEN H.COD_LNG 
                        ELSE :COD_LANG_SESSION
                    END) AS COD_LNG
                FROM CAMP_REP_QST C WITH (NOLOCK)
                    LEFT JOIN HASTUTIL H WITH (NOLOCK) ON C.COD_NOTEUR = H.CODE
                    LEFT JOIN CAMP_NOTEX E WITH (NOLOCK) ON C.COD_NOTEUR = E.ID_NOTEUR
                    LEFT JOIN CAMP_LST_NOT_GRP G WITH (NOLOCK) ON G.ID_CAMP = C.ID_CAMP AND C.COD_NOTEUR = G.NOTEUR
                WHERE C.ID_CAMP = $campaignId
                    AND C.QUEST_ACTIF = 0x54 
                    AND C.COD_NOTEUR <> :USER
                    AND C.TERMINE <> 0x54
                    AND C.NOT_FOUR = 0x46";

        $params = [
            ':USER'     => $_SESSION['Session_Login'],
            ':COD_LANG_SESSION' => User::getUser()->getLangue()
        ];

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

    
    /**
     * getSupplierRatersForEmailByCampaign
     *
     * @param  int $campaignId
     * @return array<mixed>
     */
    public function getSupplierRatersForEmailByCampaign(int $campaignId)
    {
        $query = "SELECT DISTINCT crq.COD_NOTEUR, ISNULL(ou.NOM, '') NOM, '' CODE_STE, ou.FONCTION, ISNULL(ou.EMAIL, '') EMAIL, 'F' ORIGIN, f.RS, f.COD_LNG
            FROM CAMP_REP_QST crq WITH (NOLOCK)
                LEFT JOIN CONTFOUR cf WITH (NOLOCK)
                    ON cf.CODE_FOU = crq.COD_NOTEUR
                LEFT JOIN FOURNISS f WITH (NOLOCK)
                    ON f.CODE = crq.COD_NOTEUR
                INNER JOIN OXALINK_USER ou WITH (NOLOCK)
                    ON ou.USER_ID = cf.OXALINK_ID
                LEFT JOIN HASTUTIL h WITH (NOLOCK)
                    ON h.CODE = crq.COD_NOTEUR
            WHERE ID_CAMP = $campaignId
                AND crq.NOT_FOUR = 0x54 
                AND crq.QUEST_ACTIF = 0x54 
                AND crq.TERMINE <> 0x54 ";

        return $this->fetchAssocAll($query, []);
    }
    /**
     * getRatersByGroupByCampaign
     *
     * @param  int $campaignId
     * @param  string $group
     * @param  array<mixed> $post
     * @return array<mixed>
     */
    public function getRatersByGroupByCampaign(int $campaignId, string $group = '', array $post = []): array
    {
        $clause = '';
        $arrayReq = [];
        if ($group !== '') {
            $arrayReq = [':GROUPE' => $group];
            $clause = " AND GROUPE = :GROUPE ";
        }
        $req =
            $this->getRatersBaseSql($campaignId, true, $arrayReq)
            . $clause;

        
        $filter = FilterTable::splitFilter($post, 0, 0, 1);
        if (count($filter) > 0 && isset($filter[1])) {
            $req .= $filter[0] . $filter['tri'];

            if (count($filter[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $filter[1]);
            }
        }

        $paginatorManagement = new PaginatorGestion();
        $paginatorManagement->injecterSqlCount(
            $req,
            $post,
            $arrayReq,
            $this->pdo
        );

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

    /**
     * @param array<mixed> $param
     */
    private function getRatersBaseSql(int $campId, bool $isGroup, array &$param) :string
    {
        $param['campId'] = $campId;
        $select = $isGroup ? 'C.GROUPE' : 'C.CODE_FOU';
        // TODO : simplify
        return "SELECT DISTINCT C.NOTEUR, {$select},
            (CASE 
                WHEN EXISTS (SELECT H.NOM FROM HASTUTIL H WITH(NOLOCK) WHERE H.CODE = C.NOTEUR) 
                THEN (SELECT H.NOM FROM HASTUTIL H WITH(NOLOCK) WHERE H.CODE = C.NOTEUR) 
                ELSE E.NOM_NOTEUR
            END) AS NOM,
            (CASE 
                WHEN EXISTS (SELECT H.CODE FROM HASTUTIL H WITH(NOLOCK) WHERE H.CODE = C.NOTEUR) 
                THEN (SELECT H.CODE_STE FROM HASTUTIL H WITH(NOLOCK) WHERE H.CODE = C.NOTEUR) 
                ELSE E.SOC_NOTEUR
            END) AS CODE_STE,
            (CASE 
                WHEN EXISTS (SELECT H.EMAIL FROM HASTUTIL H WITH(NOLOCK) WHERE H.CODE = C.NOTEUR) 
                THEN (SELECT H.FONCTION FROM HASTUTIL H WITH(NOLOCK) WHERE H.CODE = C.NOTEUR) 
                ELSE E.FCT_NOTEUR
            END) AS FONCTION, C.NOTEUR_EXTERNE AS ORIGIN
        FROM ".($isGroup ? 'CAMP_LST_NOT_GRP' : 'CAMP_LST_NOT_FOUR')." C with(nolock)
            LEFT JOIN HASTUTIL H WITH (NOLOCK)
                ON C.NOTEUR = H.CODE
            LEFT JOIN CAMP_NOTEX E WITH (NOLOCK)
                ON E.ID_NOTEUR = C.NOTEUR
            LEFT JOIN CAMP_LST_NOT_CAMP L WITH (NOLOCK)
                ON L.ID_CAMP = C.ID_CAMP
        WHERE C.ID_CAMP = :campId";
    }
    
    /**
     * getRatersBySupplierByCampaign
     *
     * @param  int $campaign
     * @param  string $supplier
     * @param  array<mixed> $post
     * @return array<mixed>
     */
    public function getRatersBySupplierByCampaign(int $campaign, string $supplier, array $post = [])
    {
        $clause = '';
        $params = [];
        if ($supplier !== '') {
            $params = [':CODE_FOU' => $supplier];
            $clause = " AND C.CODE_FOU = :CODE_FOU ";
        }
        $query =
            $this->getRatersBaseSql($campaign, false, $params)
            . $clause;
        
        $filter = FilterTable::splitFilter($post, 0, 0, 1);
        if (count($filter) > 0 && isset($filter[1])) {
            $query .= $filter[0] . $filter['tri'];

            if (count($filter[1]) != 0) {
                $params = array_merge($params, $filter[1]);
            }
        }

        $paginatorManagement = new PaginatorGestion();
        $paginatorManagement->injecterSqlCount(
            $query,
            $post,
            $params,
            $this->pdo
        );

        return $this->fetchAssocAll($query, $params);
    }
    
    /**
     * getRatersNotInGroup
     *
     * @param  int $campaignId
     * @param  array<mixed> $post
     * @return array<mixed>
     */
    public function getRatersNotInCampaign(int $campaignId, array $post = []): array
    {
        $req = "SELECT H.CODE, H.NOM, H.EMAIL
                FROM HASTUTIL H WITH (NOLOCK)
                WHERE H.NOTEUR = 0x54
                    AND DTE_FERM = '00000000'
                    AND H.CODE NOT IN
                (SELECT H2.CODE
                FROM HASTUTIL H2 WITH (NOLOCK)
                    LEFT JOIN CAMP_LST_NOT_CAMP C WITH (NOLOCK)
                        ON C.NOTEUR = H.CODE
                WHERE C.ID_CAMP = $campaignId)";

        $arrayReq = [
        ];

        $filter = FilterTable::splitFilter($post, 0, 0, 1);
        if (! empty($filter)) {
            $req .= $filter[0] . $filter['tri'];
            if (! empty($filter[1])) {
                $arrayReq = array_merge($arrayReq, $filter[1]);
            }
        }
        
        $paginatorManagement = new PaginatorGestion();
        $paginatorManagement->injecterSqlCount(
            $req,
            $post,
            $arrayReq,
            $this->pdo
        );

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

    /**
     * getRatersNotInGroup
     *
     * @param  int $campaignId
     * @param  array<mixed> $post
     * @return array<mixed>
     */
    public function getExternalRatersNotInCampaign(int $campaignId, array $post = []): array
    {
        $req = "SELECT E.ID_NOTEUR, E.NOM_NOTEUR, E.FCT_NOTEUR, E.EMAIL_NOTEUR, E.SOC_NOTEUR
                FROM CAMP_NOTEX E
                WHERE E.DTE_FERM = '00000000'
                    AND E.ID_NOTEUR NOT IN
                        (SELECT E2.ID_NOTEUR
                        FROM CAMP_NOTEX E2 WITH (NOLOCK)
                            LEFT JOIN CAMP_LST_NOT_CAMP C WITH (NOLOCK)
                                ON C.NOTEUR = E2.ID_NOTEUR
                        WHERE C.ID_CAMP = $campaignId)";

        $arrayReq = [
        ];

        $filter = FilterTable::splitFilter($post, 0, 0, 1);
        if (count($filter) > 0 && isset($filter[1])) {
            $req .= $filter[0] . $filter['tri'];

            if (count($filter[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $filter[1]);
            }
        }
        
        $paginatorManagement = new PaginatorGestion();
        $paginatorManagement->injecterSqlCount(
            $req,
            $post,
            $arrayReq,
            $this->pdo
        );

        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
     * getCampaignRatersForGroup
     *
     * @param  int $campaignId
     * @param  string $group
     * @param  array<mixed> $post
     * @return array<mixed>
     */
    public function getCampaignRatersForGroup(int $campaignId, string $group, array $post = [])
    {
        $req = "SELECT 
                    (CASE 
                        WHEN EXISTS (SELECT H.NOM FROM HASTUTIL H WITH(NOLOCK) WHERE H.CODE = C.NOTEUR) 
                        THEN (SELECT H.NOM FROM HASTUTIL H WITH(NOLOCK) WHERE H.CODE = C.NOTEUR) 
                        ELSE E.NOM_NOTEUR
                    END) AS NOM,
                    (CASE 
                        WHEN EXISTS (SELECT H.CODE FROM HASTUTIL H WITH(NOLOCK) WHERE H.CODE = C.NOTEUR) 
                        THEN (SELECT H.CODE FROM HASTUTIL H WITH(NOLOCK) WHERE H.CODE = C.NOTEUR) 
                        ELSE E.ID_NOTEUR
                    END) AS CODE,
                    (CASE 
                        WHEN EXISTS (SELECT H.EMAIL FROM HASTUTIL H WITH(NOLOCK) WHERE H.CODE = C.NOTEUR) 
                        THEN (SELECT H.EMAIL FROM HASTUTIL H WITH(NOLOCK) WHERE H.CODE = C.NOTEUR) 
                        ELSE E.EMAIL_NOTEUR
                    END) AS EMAIL,
                    (CASE 
                        WHEN EXISTS (SELECT H.NOM FROM HASTUTIL H WITH(NOLOCK) WHERE H.CODE = C.NOTEUR) 
                        THEN 'I' 
                        ELSE 'E'
                    END) AS ORIGIN
        FROM CAMP_LST_NOT_CAMP C WITH (NOLOCK)
            LEFT JOIN HASTUTIL H WITH (NOLOCK)
                ON H.CODE = C.NOTEUR
            LEFT JOIN CAMP_NOTEX E WITH (NOLOCK)
                ON E.ID_NOTEUR = C.NOTEUR
        WHERE C.ID_CAMP = $campaignId
            AND C.NOTEUR NOT IN
                (SELECT C2.NOTEUR
                FROM CAMP_LST_NOT_CAMP C2
                    LEFT JOIN CAMP_LST_NOT_GRP G WITH (NOLOCK)
                        ON G.NOTEUR = C.NOTEUR 
                            AND G.ID_CAMP = C2.ID_CAMP
                WHERE C2.ID_CAMP = $campaignId 
                    AND G.GROUPE = :GROUPE)";

        $arrayReq = [
            ':GROUPE' => $group,
        ];

        if (isset($post['filter_NOM']) && $post['filter_NOM'] != '') {
            $req .= " AND (UPPER(H.NOM) LIKE UPPER(:NOM1) OR UPPER(E.NOM_NOTEUR) LIKE UPPER(:NOM2)) ";

            $arrayReq[':NOM1'] = '%' . $post['filter_NOM'] . '%';
            $arrayReq[':NOM2'] = '%' . $post['filter_NOM'] . '%';

            unset($post['filter_NOM']);
        }

        if (isset($post['ORIGIN']) && $post['ORIGIN'] != '') {
            if ($post['ORIGIN'] == 'I') {
                $not = '';
            } else {
                $not = ' NOT ';
            }
            $req .= " AND $not EXISTS (SELECT H.NOM FROM HASTUTIL H WITH(NOLOCK) WHERE H.CODE = C.NOTEUR) ";
        }

        if (isset($post['filter_EMAIL']) && $post['filter_EMAIL'] != '') {
            $req .= " AND (UPPER(H.EMAIL) LIKE UPPER(:EMAIL1) OR UPPER(E.EMAIL_NOTEUR) LIKE UPPER(:EMAIL2)) ";

            $arrayReq[':EMAIL1'] = '%' . $post['filter_EMAIL'] . '%';
            $arrayReq[':EMAIL2'] = '%' . $post['filter_EMAIL'] . '%';

            unset($post['filter_EMAIL']);
        }

        $filter = FilterTable::splitFilter($post, 0, 0, 1);
        if (count($filter) > 0 && isset($filter[1])) {
            $req .= $filter[0] . $filter['tri'];
            
            if (count($filter[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $filter[1]);
            }
        }

        $paginatorManagement = new PaginatorGestion();
        $paginatorManagement->injecterSqlCount(
            $req,
            $post,
            $arrayReq,
            $this->pdo
        );

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

    /**
     * getCampaignRatersForSupplier
     *
     * @param  int $campaignId
     * @param  string $supplier
     * @param  array<mixed> $post
     * @return array<mixed>
     */
    public function getCampaignRatersForSupplier(int $campaignId, string $supplier, array $post = [])
    {
        $req = "SELECT 
                    (CASE 
                        WHEN EXISTS (SELECT H.NOM FROM HASTUTIL H WITH(NOLOCK) WHERE H.CODE = C.NOTEUR) 
                        THEN (SELECT H.NOM FROM HASTUTIL H WITH(NOLOCK) WHERE H.CODE = C.NOTEUR) 
                        ELSE E.NOM_NOTEUR
                    END) AS NOM,
                    (CASE 
                        WHEN EXISTS (SELECT H.CODE FROM HASTUTIL H WITH(NOLOCK) WHERE H.CODE = C.NOTEUR) 
                        THEN (SELECT H.CODE FROM HASTUTIL H WITH(NOLOCK) WHERE H.CODE = C.NOTEUR) 
                        ELSE E.ID_NOTEUR
                    END) AS CODE,
                    (CASE 
                        WHEN EXISTS (SELECT H.FONCTION FROM HASTUTIL H WITH(NOLOCK) WHERE H.CODE = C.NOTEUR) 
                        THEN (SELECT H.FONCTION FROM HASTUTIL H WITH(NOLOCK) WHERE H.CODE = C.NOTEUR) 
                        ELSE E.FCT_NOTEUR
                    END) AS FONCTION,
                    (CASE 
                        WHEN EXISTS (SELECT H.EMAIL FROM HASTUTIL H WITH(NOLOCK) WHERE H.CODE = C.NOTEUR) 
                        THEN (SELECT H.EMAIL FROM HASTUTIL H WITH(NOLOCK) WHERE H.CODE = C.NOTEUR) 
                        ELSE E.EMAIL_NOTEUR
                    END) AS EMAIL,
                    (CASE 
                        WHEN EXISTS (SELECT H.NOM FROM HASTUTIL H WITH(NOLOCK) WHERE H.CODE = C.NOTEUR) 
                        THEN 'I' 
                        ELSE 'E'
                    END) AS ORIGIN
        FROM CAMP_LST_NOT_CAMP C WITH (NOLOCK)
            LEFT JOIN HASTUTIL H WITH (NOLOCK)
                ON H.CODE = C.NOTEUR
            LEFT JOIN CAMP_NOTEX E WITH (NOLOCK)
                ON E.ID_NOTEUR = C.NOTEUR
        WHERE C.ID_CAMP = $campaignId
            AND C.NOTEUR NOT IN
                (SELECT F.NOTEUR
                FROM CAMP_LST_NOT_FOUR F
                WHERE F.ID_CAMP = $campaignId 
                    AND F.CODE_FOU = :CODE_FOU)";

        $arrayReq = [
            ':CODE_FOU' => $supplier,
        ];

        $filter = FilterTable::splitFilter($post, 0, 0, 1);
        if (count($filter) > 0 && isset($filter[1])) {
            $req .= $filter[0] . $filter['tri'];
            
            if (count($filter[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $filter[1]);
            }
        }

        $paginatorManagement = new PaginatorGestion();
        $paginatorManagement->injecterSqlCount(
            $req,
            $post,
            $arrayReq,
            $this->pdo
        );

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

    /**
     * addRaterToGroup
     *
     * @param  int $id
     * @param  string $mode
     * @param  string $group
     * @param  array<mixed> $post
     * @return bool
     */
    public function addRaterToGroup(int $id, string $mode, string $group, array $post)
    {
        $req = '';
        $arrayReq = [];
        switch ($mode) {
            case 'campaign':
                $req = 'INSERT INTO CAMP_LST_NOT_GRP (ID_CAMP, GROUPE, NOTEUR, NOTEUR_EXTERNE)
                        VALUES (';
                $i = 1;
                foreach ($post as $rater => $external) {
                    $req .= ':ID_CAMP' . $i . ', :GROUPE' . $i . ', :NOTEUR' . $i . ', ' . $external . '), (';
                    $arrayReq[':ID_CAMP' . $i]          = $id;
                    $arrayReq[':GROUPE' . $i]           = $group;
                    $arrayReq[':NOTEUR' . $i]           = $rater;
                    $i++;
                }
                $req = substr($req, 0, -3);
                break;
            
            default:
                break;
        }

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

        return true;
    }
    
    /**
     * addExternalRaterToGroup
     *
     * @param  array<mixed> $post
     * @return bool
     */
    public function addExternalRaterToGroup(array $post)
    {
        $req = "INSERT INTO CAMP_LST_NOT_CAMP (ID_CAMP, NOTEUR, NOTEUR_EXTERNE) 
        VALUES (" . $post['ID_CAMP'] . ", :NOTEUR, 0x54)";
        $arrayReq = [
            ':NOTEUR'                => $post['NOTEUR'],
        ];

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

        return true;
    }
    
    /**
     * @param array<mixed> $groups
     * @return array<mixed> $groups
     */
    public function groupImport(array $groups): array
    {
        // on crée la table temporaire qui nous permettra de faire les contrôles sur l'insert initial
        $req =
            'CREATE TABLE #TEMP_BIB_GRP_NOT (GROUPE VARCHAR(30), STATUT CHAR(1));';

        // on réalise un premier insert dans la table temporaire
        $req .= "INSERT INTO #TEMP_BIB_GRP_NOT (GROUPE)
        VALUES ";

        foreach ($groups as $group) {
            $req .=
                "('" .
                str_replace('\'', '\'\'', $group) .
                "'), ";
        }
        $req = \trim($req, ', ') . '; ';

        // on update la table temporaire avec le champ 'statut' pour organiser les résultats
        $req .= "UPDATE T
                SET T.STATUT = 
                    CASE 
                        WHEN ISNULL (B.GROUPE, '') = ''
                        THEN 'O'
                        ELSE 'E' 
                    END
                FROM #TEMP_BIB_GRP_NOT T
                LEFT OUTER JOIN BIB_GRP_NOT B ON B.GROUPE = T.GROUPE; ";

        // on insère les catégories vérifiées et non-doublons dans la liste des catégories
        $req .= "INSERT INTO BIB_GRP_NOT (GROUPE)
                SELECT GROUPE FROM #TEMP_BIB_GRP_NOT T WHERE T.STATUT = 'O'; ";

        // on exécute la requête en connexion persistante
        $stmt = sqlsrv_query($this->pdoStorage, $req);

        // on récupère le compte de catégories importées et insérées
        $req =
            "SELECT COUNT(*) AS count FROM #TEMP_BIB_GRP_NOT T WHERE T.STATUT = 'O';";
        if ($stmt = sqlsrv_query($this->pdoStorage, $req)) {
            while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
                $count = $row['count'];
            }
        }

        // on récupère la liste des catégories qui existaient déjà
        $req = "SELECT T.GROUPE
                FROM #TEMP_BIB_GRP_NOT T 
                    LEFT JOIN BIB_GRP_NOT B 
                        ON B.GROUPE = T.GROUPE
                WHERE T.STATUT = 'E';";
        if ($stmt = sqlsrv_query($this->pdoStorage, $req)) {
            $i = 1;
            while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
                $existingGroups[$i] = $row['GROUPE'];
                $i++;
            }
        }

        return [
            'count' => $count ?? 0,
            'existingGroups' => $existingGroups ?? [],
        ];
    }
    
    /**
     * addRatersToCampaign
     *
     * @param  int $campaignId
     * @param  array<mixed> $raters
     * @param  string $origin
     * @return bool
     */
    public function addRatersToCampaign(int $campaignId, array $raters, string $origin)
    {
        $req = '';
        $arrayReq = [];

        if ($origin == 'internal') {
            $origin = '0x46';
        } else {
            $origin = '0x54';
        }


        $req = "INSERT INTO CAMP_LST_NOT_CAMP (ID_CAMP, NOTEUR, NOTEUR_EXTERNE)
        VALUES (";

        $i = 1;

        foreach ($raters as $rater) {
            $req .= "$campaignId, :NOTEUR$i, $origin), (";
            $arrayReq[":NOTEUR$i"] = $rater;
            $i++;
        }

        $req = \rtrim($req, ', (');

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

        return $insert->execute($arrayReq);
    }
        
    /**
     * addRatersToGroup
     *
     * @param  int $campaignId
     * @param  string $group
     * @param  array<mixed> $raters
     * @return bool
     */
    public function addRatersToGroup(int $campaignId, string $group, array $raters)
    {
        $req = "INSERT INTO CAMP_LST_NOT_GRP (ID_CAMP, GROUPE, NOTEUR, NOTEUR_EXTERNE)
        VALUES (";
        $arrayReq = [];
        $i = 1;
        foreach ($raters as $rater) {
            $req .= "$campaignId, :GROUPE$i, :NOTEUR$i, (SELECT NOTEUR_EXTERNE FROM CAMP_LST_NOT_CAMP WHERE ID_CAMP = $campaignId AND NOTEUR = :NOTEUR_BIS$i)), (";
            $arrayReq[":NOTEUR$i"] = $rater;
            $arrayReq[":NOTEUR_BIS$i"] = $rater;
            $arrayReq[":GROUPE$i"] = $group;
            $i++;
        }
        $req = \rtrim($req, ', (');

        $insert = $this->pdo->prepare($req);
        return $insert->execute($arrayReq);
    }
    
    /**
     * addRatersToSupplier
     *
     * @param  int $campaignId
     * @param  string $supplier
     * @param  array<mixed> $raters
     * @return bool
     */
    public function addRatersToSupplier(int $campaignId, string $supplier, array $raters)
    {
        $query = "INSERT INTO CAMP_LST_NOT_FOUR (ID_CAMP, CODE_FOU, NOTEUR, NOTEUR_EXTERNE)
        VALUES (";
        $params = [];
        $i = 1;
        foreach ($raters as $rater) {
            $query .= "$campaignId, :CODE_FOU$i, :NOTEUR$i, (SELECT NOTEUR_EXTERNE FROM CAMP_LST_NOT_CAMP WHERE ID_CAMP = $campaignId AND NOTEUR = :NOTEUR_BIS$i)), (";
            $params[":NOTEUR$i"] = $rater;
            $params[":NOTEUR_BIS$i"] = $rater;
            $params[":CODE_FOU$i"] = $supplier;
            $i++;
        }
        $query = \rtrim($query, ', (');

        $insert = $this->pdo->prepare($query);
        return $insert->execute($params);
    }

    /**
     * getExternalRatersMaxId
     *
     * @return int|string
     */
    public function getExternalRatersMaxId()
    {
        $req = "SELECT MAX(ID_NOTEUR) MAX_ID
        FROM CAMP_NOTEX WITH (NOLOCK) 
        WHERE 1=1";

        $maxId = $this->pdo->prepare($req);
        $maxId->execute();
        return $maxId->fetch(PDO::FETCH_ASSOC)['MAX_ID'] ?? '0';
    }

    /**
     * importCampaignRaters
     *
     * @param  int $campaignId
     * @param  array<mixed> $raters
     * @return array<mixed>
     */
    public function importCampaignRaters(int $campaignId, array $raters): array
    {
        // on crée la table temporaire qui nous permettra de faire les contrôles sur l'insert initial
        $query = "SELECT ID_CAMP, NOTEUR, '' AS STATUT
                    INTO #CAMP_LST_NOT_CAMP
                FROM CAMP_LST_NOT_CAMP
                WHERE ID_CAMP = 0; ";

        // on réalise un premier insert dans la table temporaire
        $query .= "INSERT INTO #CAMP_LST_NOT_CAMP (ID_CAMP, NOTEUR, STATUT)
        VALUES ";

        foreach ($raters as $rater) {
            $query .= '(' . $campaignId . ', \'' . $rater . '\', \'\'), ';
        }
        $query = \rtrim($query, ', ');

        // on update la table temporaire avec le champ 'statut' pour organiser les résultats
        $query .= "UPDATE T
                SET T.STATUT = 
                    CASE 
                        WHEN ISNULL (F.CODE, '') = ''
                        THEN 'E'
                        WHEN ISNULL (C.NOTEUR, '') = ''
                        THEN 'O'
                        ELSE 'D' 
                    END
                FROM #CAMP_LST_NOT_CAMP T
                    LEFT OUTER JOIN HASTUTIL F ON F.CODE = T.NOTEUR AND F.NOTEUR = 0x54
                    LEFT OUTER JOIN CAMP_LST_NOT_CAMP C ON C.ID_CAMP = $campaignId AND C.NOTEUR = T.NOTEUR;";

        // on insère les fournisseurs vérifiés et non-doublons dans la liste des fournisseurs de la campagne
        $query .= "INSERT INTO CAMP_LST_NOT_CAMP (ID_CAMP, NOTEUR)
                SELECT ID_CAMP, NOTEUR FROM #CAMP_LST_NOT_CAMP T WHERE T.STATUT = 'O';";

        // on exécute la requête en connexion persistante
        $stmt = sqlsrv_query($this->pdoStorage, $query);

        // on récupère le compte de fournisseurs importés et insérés
        $query = "SELECT COUNT(*) AS count FROM #CAMP_LST_NOT_CAMP T WHERE T.STATUT = 'O';";

        $stmt = sqlsrv_query($this->pdoStorage, $query);
        /** @phpstan-ignore-next-line */
        while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
            $count = $row['count'];
        }

        // on récupère la liste des fournisseurs inexistants
        $query = "SELECT T.NOTEUR,
                    CASE 
						WHEN F.NOM IS NULL
						THEN 'Code inexistant'
						ELSE F.NOM
					END AS NOM 
                FROM #CAMP_LST_NOT_CAMP T 
                    LEFT JOIN HASTUTIL F ON T.NOTEUR = F.CODE
                WHERE T.STATUT = 'E'";

        $stmt = sqlsrv_query($this->pdoStorage, $query);
        /** @phpstan-ignore-next-line */
        while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
            $missingRaters[$row['NOTEUR']] = $row['NOTEUR'];
        }

        // on récupère la liste des fournisseurs qui étaient déjà enregistrés dans la campagne
        $query = "SELECT T.NOTEUR, F.NOM
                FROM #CAMP_LST_NOT_CAMP T 
                    LEFT JOIN HASTUTIL F ON T.NOTEUR = F.CODE
                WHERE T.STATUT = 'D'";

        $stmt = sqlsrv_query($this->pdoStorage, $query);
        /** @phpstan-ignore-next-line */
        while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
            $alreadyRegisteredRaters[$row['NOTEUR']] = $row['NOTEUR'];
        }

        return [
            'count' => $count ?? 0,
            'missingRaters' => $missingRaters ?? [],
            'alreadyRegisteredRaters' => $alreadyRegisteredRaters ?? [],
        ];
    }
}
