<?php

namespace Modules\Administration\Repository;

use Entity\Model\OxalinkUser;
use Helpers\QueryBuilder\AbsRepository;
use Modules\GlobalApplication\Repository\Societe;
use PDO;

// TODO : most of this SQL can be turn into QueryBuilder
class GestionUtilisateurOxalink extends AbsRepository
{
    /**
     * @param array<mixed> $post
     * @return string[][]
     */
    public function listeCompte($post)
    {
        $arrayReq = [];
        
        $req = "select * from ( select listeUser.*, FOURNISS.RS from (select *,
                    CASE (SELECT count(*) NB_FRS from CONTFOUR with (nolock) where upper(CONTFOUR.EMAIL)= upper(OXALINK_USER.EMAIL)) 
                    	   WHEN 0 THEN '0'
                           WHEN 1 THEN (SELECT CODE_FOU from CONTFOUR with (nolock) where upper(CONTFOUR.EMAIL)= upper(OXALINK_USER.EMAIL))
                           ELSE 'multi'
                    END FRS
                    from OXALINK_USER with (nolock) ) listeUser left join FOURNISS with (nolock) on FOURNISS.CODE = listeUser.FRS ) sousRequete";
        
        $req = "select *, 
                    CASE (SELECT count(*) NB_FRS from CONTFOUR with (nolock) where upper(CONTFOUR.OXALINK_ID)= upper(OXALINK_USER.USER_ID)) 
                        WHEN 0 THEN '0' 
                        WHEN 1 THEN (SELECT CODE_FOU /*CONCAT(CODE_FOU,'|splite-Sql|',RS)*/ from CONTFOUR with (nolock) left join FOURNISS with(nolock) on FOURNISS.CODE = CONTFOUR.CODE_FOU where upper(CONTFOUR.OXALINK_ID)= upper(OXALINK_USER.USER_ID))  
                        ELSE 'multi' 
                    END FRS 
                from OXALINK_USER with (nolock)
                where 1=1";
                
        if (isset($post['filter_FRSdebut-----finRS']) && $post['filter_FRSdebut-----finRS'] != '') {
            $req .= "and OXALINK_USER.USER_ID in (select DISTINCT(CONTFOUR.OXALINK_ID) from FOURNISS with (nolock)
            left join CONTFOUR with (nolock) on CONTFOUR.CODE_FOU = FOURNISS.CODE
            where  ( UPPER(RS) like :rsFrs OR UPPER(CODE) like :codeFrs))";

            $arrayReq[':rsFrs'] = '%'.$post['filter_FRSdebut-----finRS'].'%';
            $arrayReq[':codeFrs'] = '%'.$post['filter_FRSdebut-----finRS'].'%';
            unset($post['filter_FRSdebut-----finRS']);
        }

        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1);
        $filtreAvance = new \Helpers\Filter\FiltreAvance\Generator($post);
        $req .= $filtreAvance->generateSql(1);
        $filtreAvance->pushArray($arrayReq);

        if (count($whereArray)>  0) {
            $req .= $whereArray[0].$whereArray['tri'];
            
            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }

        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
     * @param string $user
     * @return array<mixed>
     */
    public function detailUser($user)
    {
        return (new OxalinkUser($this->pdo))->find(['USER_ID' => $user]) ?? [];
    }
        
    /**
     * detlisteFrsUser
     *
     * @param  string $idUser
     * @return array<mixed>
     */
    public function detlisteFrsUser(string $idUser) : array
    {
        return $this->fetchAssocAll(
            "SELECT FOURNISS.CODE,FOURNISS.RS 
            FROM CONTFOUR with (nolock) 
            JOIN FOURNISS with (nolock) on FOURNISS.CODE = CONTFOUR.CODE_FOU
            WHERE CONTFOUR.OXALINK_ID = :idUser",
            [':idUser' => $idUser]
        );
    }
    
    /**
     * @param string $mode
     */
    public function countDemandeAdmin($mode) :int
    {
        $req = "select count(*) COUNT from UTILCREAT with (nolock)
                inner join SOCIETE with (nolock) on UTILCREAT.CODE_STE = SOCIETE.CODE_STE
                left join DILISPEC with (nolock) on UTILCREAT.CODE = DILISPEC.CODEDICO
    			where CREATMOD=:mode";
        
        $arrayReq[':mode']=$mode;

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return intval($requete->fetch($this->pdo::FETCH_ASSOC)['COUNT']);
    }
    
    /**
     * @param string $profil
     * @param string $login
     */
    public function countCompteBloque($profil, $login) :int
    {
        $isMulti = Societe::hasMultiSoc();
        $societe = $_SESSION['Session_SocUsr'];

        $sql = 'SELECT count(*) as Count
            FROM UTILBLOQUE with(nolock)
            LEFT JOIN HASTUTIL with(nolock) on UTILBLOQUE.CODE = HASTUTIL.CODE
            LEFT JOIN SOCIETE with(nolock) on HASTUTIL.CODE_STE = SOCIETE.CODE_STE
            WHERE 1=1 ';
        
        if (! $isMulti) {
            $sql .= " and HASTUTIL.CODE_STE='$societe'";
        }
        if ($profil == 'locale') {
            $sql .= "and HASTUTIL.CTR_ADR in
				(
					select distinct ctr.CODE_CTR from CENTRE as ctr with(nolock), SOCIETE  as soc with(nolock)
					where ctr.EMISSION=0x54 and ctr.CODE_STE=soc.CODE_STE
					and (ctr.CODE_CTR in (select COD_CTR from ADML_CTR with(nolock) where COD_USR='$login')
					or
					soc.CODE_STE in (select SOC_COD from ADML_SOC with(nolock) where COD_USR='$login' and TTCTR=0x54)
					or
					soc.GROUPE in (select COD_GRPS from ADMLGRSO with(nolock) where COD_USR='$login' and TTSOC=0x54))";
            
                    // Si l'utilisateur est monosociété alors on ajoute un filtre sur la société
            if (! $isMulti) {
                $sql .= " and ctr.CODE_STE='$societe'";
            }
                
            $sql .= ')';
        }

        return intval($this->fetchAssocOne($sql, [])['Count'] ?? 0);
    }
    
    /**
     * @param string $idUser
     */
    public function suppresionUser($idUser) :void
    {
        /* Utilisateur OXA.LINK */
        $req = "delete from OXALINK_USER where USER_ID = :idUser";
        
        $arrayReq[':idUser']=$idUser;
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        /* Données liées */
        $req = "delete from OXALINK_UTIL_TENT_CNX where USER_ID = :idUser";
        
        $arrayReq[':idUser']=$idUser;
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        $req = "delete from OXALINK_UTILHISTCNX where USER_ID = :idUser";
        
        $arrayReq[':idUser']=$idUser;
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        $req = "delete from OXALINK_UTILBLOQUE where USER_ID = :idUser";
        
        $arrayReq[':idUser']=$idUser;
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        $req = "update CONTFOUR set OXALINK_ID='' where OXALINK_ID = :idUser";
        
        $arrayReq[':idUser']=$idUser;
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
    }
    
    /**
     * @param string $idUser
     * @return array<mixed>
     */
    public function getInfoUser($idUser)
    {
        return (new OxalinkUser($this->pdo))->find(['USER_ID' => $idUser]) ?? [];
    }
    
    /**
     * @param string $idUser
     * @param string $mail
     * @param array<mixed> $array
     */
    public function updateUser($idUser, $mail, $array) :void
    {
        $req = "UPDATE OXALINK_USER SET NOM = :nom, FONCTION = :fonction, TEL = :tel, TELPORT = :telport WHERE USER_ID=:idUser";
        
        $arrayReq = [
            ':idUser' => $idUser,
            ':nom' => $array['NOM'],
            ':fonction' => $array['FONCTION'],
            ':tel' => $array['TEL'],
            ':telport' => $array['TELPORT'],
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        $req = "UPDATE CONTFOUR SET NOM = :nom, FONCTION = :fonction, TEL = :tel, TELPORT = :telport WHERE EMAIL=:mail";
        
        $arrayReq = [
            ':mail' => $mail,
            ':nom' => $array['NOM'],
            ':fonction' => $array['FONCTION'],
            ':tel' => $array['TEL'],
            ':telport' => $array['TELPORT'],
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
    }
    
    /**
     * @param string $idUser
     * @return void
     */
    public function setActifUser($idUser) :void
    {
        $req = "update OXALINK_USER SET STATUT='A' where USER_ID=:idUser";
        
        $arrayReq[':idUser']=$idUser;
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
    }
    
    /**
     * Summary of updateContFour
     * @param string $idUser
     * @param string $email
     */
    public function updateContFour($idUser, $email) :void
    {
        $req = "update CONTFOUR SET OXALINK_ID= :idUser where EMAIL=:email";
        
        $arrayReq[':idUser']=$idUser;
        $arrayReq[':email']=$email;
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
    }
    
    /**
     * getEventsUserOxalink
     *
     * @param  string $userId
     * @param  array<mixed> $post
     * @return array<mixed>
     */
    public function getEventsUserOxalink(string $userId = '', array $post = []): array
    {
        $query = "SELECT cf.NOM AS NOM_UTILISATEUR, evt.DTE_EVE, evt.HEU_EVE, h.NOM, evt.COMMENTAIRE, typ.LIBELLE, evt.USER_ID_INTERNE
        FROM OXALINK_USER_EVE evt WITH (NOLOCK)
            /* Lecture du code évènement dans la liste des évènements gérés */
            LEFT JOIN OXALINK_USER_TYP_EVE typ WITH(NOLOCK) 
                ON typ.COD_EVE = evt.COD_EVE 
            LEFT JOIN HASTUTIL h WITH (NOLOCK)
                ON h.CODE = evt.USER_ID_INTERNE
            LEFT JOIN CONTFOUR cf with(nolock)
                ON cf.OXALINK_ID = evt.USER_ID
        WHERE 1 = 1 
        ";

        $params = [];

        if ($userId != '') {
            $query .= " AND evt.USER_ID = :user ";
            $params = [
                ':user'   => [
                    'value'     => $userId,
                    'option'    => \PDO::PARAM_STR
                ],
            ];
        }

        // Colnne ambigu
        $post['tri_cf.NOM'] = $post['tri_NOM_UTILISATEUR'] ?? null;
        unset($post['tri_NOM_UTILISATEUR']);

        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1);
        $filtreAvance = new \Helpers\Filter\FiltreAvance\Generator($post);
        $query .= $filtreAvance->generateSql(1);
        $filtreAvance->pushArray($params);

        if (count($whereArray)>  0) {
            $query .= $whereArray[0].$whereArray['tri'];
            
            if (count($whereArray[1]) != 0) {
                $params = array_merge($params, $whereArray[1]);
            }
        }

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

        foreach ($params as $key => $param) {
            if (\strpos($key, ':filter') === 0) {
                $reporting->bindParam($key, $param, PDO::PARAM_STR);
            } else {
                $reporting->bindParam($key, $param['value'], $param['option']);
            }
        }
        
        $reporting->execute();

        $return =  $reporting->fetchAll(PDO::FETCH_ASSOC);
        if ($return) {
            return $return;
        } else {
            return [];
        }
    }
    
    /**
     * insertEvent
     *
     * @param  string $userId
     * @param  int $orderNumber
     * @param  string $eventCode
     * @param  string $comment
     * @return bool
     */
    public function insertEvent(string $userId, int $orderNumber, string $eventCode, string $comment): bool
    {
        $query = "INSERT INTO OXALINK_USER_EVE (USER_ID, NUM_ORDR, DTE_EVE, HEU_EVE, USER_ID_INTERNE, COD_EVE, COMMENTAIRE)
        VALUES (:USER_ID, :NUM_ORDR, :DTE_EVE, :HEU_EVE, :ID_USER, :COD_EVE, :COMMENTAIRE) ";

        $params = [
            ':USER_ID' => [
                'option' => \PDO::PARAM_STR,
                'value' => $userId,
            ],
            ':NUM_ORDR' => [
                'option' => \PDO::PARAM_INT,
                'value' => $orderNumber,
            ],
            ':DTE_EVE' => [
                'option' => \PDO::PARAM_STR,
                'value' => date('Ymd'),
            ],
            ':HEU_EVE' => [
                'option' => \PDO::PARAM_STR,
                'value' => date('His'),
            ],
            ':ID_USER' => [
                'option' => \PDO::PARAM_STR,
                'value' => $_SESSION['Session_Login'],
            ],
            ':COD_EVE' => [
                'option' => \PDO::PARAM_STR,
                'value' => $eventCode,
            ],
            ':COMMENTAIRE' => [
                'option' => \PDO::PARAM_STR,
                'value' => $comment
            ],
        ];

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

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

        return $insert->execute();
    }

    /**
     * getMaxOrder
     * retourne le numéro d'ordre maximum d'une entité donnée
     *
     * @param  string $table la table concernée
     * @param  string $designation le nom de la colonne numéro d'ordre
     * @param  array<mixed> $whereClause (optional) une éventuelle clause 'WHERE'
     * @return int
     */
    public function getMaxOrder(string $table, string $designation, array $whereClause = [])
    {
        $query = 'SELECT CASE 
                            WHEN ISNULL(MAX(' . $designation . '), 0) = 0
                            THEN 0
                            ELSE MAX(' . $designation . ')
                        END AS max_order
                    FROM ' . $table . ' WITH(NOLOCK)
                    WHERE 1 = 1 ';

        if (is_array($whereClause) && count($whereClause) > 0) {
            foreach ($whereClause as $key => $clause) {
                $query .= ' AND ' . $key . ' = ' . $clause;
            }
        }

        $maxOrder = $this->pdo->prepare($query);
        $maxOrder->execute();

        return $maxOrder->fetch(PDO::FETCH_ASSOC)['max_order'];
    }
    
    /**
     * getSupplierInfo
     *
     * @param  string $userId
     * @return array<mixed>
     */
    public function getSupplierInfo(string $userId): array
    {
        $query = "SELECT TOP 1 f.CODE, f.RS
        FROM FOURNISS f WITH(NOLOCK)
            LEFT JOIN CONTFOUR cf with(nolock)
                ON cf.CODE_FOU = f.CODE
        WHERE cf.OXALINK_ID = :id";

        $params = [
            ':id'   => [
                'option'    => \PDO::PARAM_STR,
                'value'     => $userId,
            ]
        ];

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

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

        $data->execute();

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