<?php

namespace Modules\Evaluation\Repository;

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

class Association extends AbsRepository
{
    
    /**
     * getAssociationsById returns the list of associations of an evaluation campaign
     *
     * @param  int $campaignId
     * @return array<mixed>
     */
    public function getAssociationsById(int $campaignId)
    {
        $param = [];
        $req = "SELECT 
                    C.TYPE_OBJ,
                    C.VAL_OBJ,
                    C.CODE_STE,
                    S.RAIS_SOC,
                    " . $this->selectTrad('T.LIBELLE') . "
                FROM CAMP_ASSO C WITH(NOLOCK)
                LEFT JOIN SOCIETE S WITH(NOLOCK)
                    ON S.CODE_STE = C.CODE_STE
                LEFT JOIN CS_TYPES T WITH(NOLOCK)
                    ON T.TYPE = C.TYPE_OBJ
                " . $this->joinTrad('T.LIBELLE', $param) . "
                WHERE ID_CAMP = $campaignId
                ORDER BY C.TYPE_OBJ";
        return $this->fetchAssocAll($req, $param);
    }
    
    /**
     * getAssociationsByQuestions
     * retourne la liste des associations liées aux questions d'une campagne
     *
     * @param  int $campaignId
     * @return array<mixed>
     */
    public function getAssociationsByQuestions(int $campaignId)
    {
        $param = [
            ':ID_CAMP'  => $campaignId,
            ':ID_CAMP2' => $campaignId,
        ];
        $query = "SELECT DISTINCT 
            SUBSTRING(Q.PROFNOTEUR1, 5, 30) as ASSO,
            " . $this->selectTrad('T.LIBELLE') . "
        FROM CAMP_QST_Q Q WITH(NOLOCK)
        LEFT JOIN CS_TYPES T WITH(NOLOCK)
            ON T.TYPE = SUBSTRING(Q.PROFNOTEUR1,5,30)
        " . $this->joinTrad('T.LIBELLE', $param) . "
        WHERE Q.ID_CAMP = :ID_CAMP 
            AND PROFNOTEUR1 LIKE 'OBJ|%'
            AND SUBSTRING(Q.PROFNOTEUR1, 5, 30) NOT IN (
                SELECT C.TYPE_OBJ
                FROM CAMP_ASSO C WITH(NOLOCK)
                WHERE ID_CAMP = :ID_CAMP2 
            )";
        return $this->fetchAssocAll($query, $param);
    }
    
    /**
     * associationExistsInCampaign
     *
     * @param  int $campaignId
     * @param  string $type
     * @param  string $value
     * @param  string $company
     * @return array<mixed>
     */
    public function associationExistsInCampaign(int $campaignId, string $type, string $value, string $company = '') : array
    {
        $req = "SELECT ID_CAMP 
                FROM CAMP_ASSO WITH (NOLOCK) 
                WHERE ID_CAMP = $campaignId 
                    AND TYPE_OBJ = :TYPE_OBJ 
                    AND VAL_OBJ = :VAL_OBJ";
        
        $arrayReq = [
            ':TYPE_OBJ' => $type,
            ':VAL_OBJ' => $value,
        ];

        if ($company != '') {
            $req .= " AND CODE_STE = :CODE_STE";
            $arrayReq[':CODE_STE'] = $company;
        }
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetch();
    }
    
    /**
     * associationExistsInModel
     *
     * @param  string $modelId
     * @param  string $type
     * @param  string $value
     * @param  string $company
     * @return array<mixed>
     */
    public function associationExistsInModel(string $modelId, string $type, string $value, string $company = '') : array
    {
        $req = "SELECT ID_MODEL 
                FROM GRIL_ASSO WITH (NOLOCK) 
                WHERE ID_MODEL = :ID_MODEL 
                    AND TYPE_OBJ = :TYPE_OBJ 
                    AND VAL_OBJ = :VAL_OBJ";

        $arrayReq = [
            ':TYPE_OBJ' => $type,
            ':VAL_OBJ'  => $value,
            ':ID_MODEL' => $modelId,
        ];

        if ($company != '') {
            $req .= " AND CODE_STE = :CODE_STE";
            $arrayReq[':CODE_STE'] = $company;
        }

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetch();
    }
    
    /**
     * deleteAssociation
     *
     * @param  mixed $id
     * @param  string $mode
     * @param  string $type
     * @param  string $value
     * @param  string $company
     * @return Response
     */
    public function deleteAssociation($id, $mode, $type, $value, $company = '')
    {
        $req = '';
        switch ($mode) {
            case 'campaign':
                $req = "DELETE FROM CAMP_ASSO 
                WHERE ID_CAMP = $id ";
                break;
            
            case 'modele':
                $req = "DELETE FROM GRIL_ASSO 
                WHERE ID_MODEL = '$id' ";
                break;
        }
        $req .= "AND TYPE_OBJ = :TYPE_OBJ 
        AND VAL_OBJ = :VAL_OBJ";

        $arrayReq = [
            ':TYPE_OBJ' => $type,
            ':VAL_OBJ' => $value,
        ];

        if ($company != '') {
            $req .= " AND CODE_STE = :CODE_STE";
            $arrayReq[':CODE_STE'] = $company;
        }

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

        return new Response();
    }
    
    /**
     * getAssociationsByModel
     *
     * @param  string $modelId
     * @return array<mixed>
     */
    public function getAssociationsByModel($modelId)
    {
        $arrayReq = [
            ':ID_MODEL' => $modelId,
        ];
        $req = "SELECT 
                A.TYPE_OBJ,
                A.VAL_OBJ,
                A.CODE_STE,
                S.RAIS_SOC,
                " . $this->selectTrad('T.LIBELLE') . "
            FROM GRIL_ASSO A WITH(NOLOCK)
            LEFT JOIN SOCIETE S WITH(NOLOCK)
                ON S.CODE_STE = A.CODE_STE
            LEFT JOIN CS_TYPES T WITH(NOLOCK)
                ON T.TYPE = A.TYPE_OBJ
            " . $this->joinTrad('T.LIBELLE', $arrayReq) . "
            WHERE ID_MODEL = :ID_MODEL
            ORDER BY A.TYPE_OBJ";

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