<?php

namespace Modules\Parametrage\Oxalink\Repository;

use Helpers\Outil\Sql;
use Helpers\QueryBuilder\AbsRepository;

class ParametrageOxalink extends AbsRepository
{
    
    /**
     * @return array<mixed>
     */
    public function listePageClient() : ?array
    {
        $req = "select * from OXALINK_PAGES_CLIENT with (nolock) where CODE_LNG = ''";
        
        return $this->fetchAssocAll($req, []);
    }
    
    /**
     * @return array<mixed>
     */
    public function listePageClientPublic() : ?array
    {
        $req = "select * from OXALINK_PAGES_CLIENT with (nolock) where CODE_LNG = '' and PAGE_PUBLIC=0x54";
        
        return $this->fetchAssocAll($req, []);
    }
    
    /**
     * @return array<mixed>
     */
    public function listePageClientLangue(string $codePage) : ?array
    {
        $codePage = trim(addslashes($codePage));
        
        $req = "select LIBELLE, COD_LNG CODE_LNG, TITRE, FIC_OU_URL,TYPE_URL , '".$codePage."' CODE from LANGUES with (nolock)
                left join OXALINK_PAGES_CLIENT with (nolock) on LANGUES.COD_LNG = OXALINK_PAGES_CLIENT.CODE_LNG and OXALINK_PAGES_CLIENT.CODE='".$codePage."'";

        return $this->fetchAssocAll($req, []);
    }
    
    /**
     * @return array<mixed>
     */
    public function listeCGULangue() : ?array
    {
        $req = "select LANGUES.LIBELLE LANGUES, COD_LNG CODE_LNG, PARAMDICO.* from LANGUES with (nolock)
                left join PARAMDICO with (nolock) on PARAMDICO.ID2= COD_LNG and TYPEDICO='PF_OXALINK' and ID1='CGU'";
        
        return $this->fetchAssocAll($req, []);
    }

    /**
     * @param array<mixed> $array
     */
    public function insertPageClient(array $array) : void
    {
        if (!$array['CODE']) {
            $array['CODE'] = $this->maxCodePageClient();
        }
        
        $req = "INSERT INTO OXALINK_PAGES_CLIENT (CODE, CODE_LNG, TITRE, TYPE_URL, FIC_OU_URL, PAGE_PUBLIC) VALUES (:code, :codeLng, :titre, ".Sql::setBoolSql($array['TYPE_URL']).", :FIC_OU_URL, ".Sql::setBoolSql($array['PAGE_PUBLIC']).")";
        
        $arrayReq = [
            ':code' => $array['CODE'],
            ':codeLng' => $array['CODE_LNG'],
            ':titre' => $array['TITRE'],
            ':FIC_OU_URL' => $array['FIC_OU_URL'],
        ];

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
    }
    
    public function maxCodePageClient(): int
    {
        $req = "select MAX( CAST(CODE AS int)) MAX_CODE from OXALINK_PAGES_CLIENT with (nolock)";
        
        $requete = $this->pdo->prepare($req);
        $requete->execute();
        
        $maxCode =  (int)$requete->fetch()['MAX_CODE'];
        
        if ($maxCode) {
            return $maxCode+1;
        } else {
            return 1;
        }
    }

    /**
     * @return array<mixed>
     */
    public function detailPageClient(?string $codePage = null, ?string $codeLng = null): ?array
    {
        $req =
        "SELECT *,
        (select count(*) from PARAVRAC with (nolock) where TYPEPARV='PF_OXALINK' and (CODEPARV='PAGE_ACCUEIL' or CODEPARV='PAGE_REFERENCEMENT') and LIB01PARV = '$codePage') pageParametree
        from OXALINK_PAGES_CLIENT with(nolock) where CODE=:code";
        $arrayReq[':code'] = $codePage;
        
        if ($codeLng) {
            $req .= "  and CODE_LNG = :codeLng";
            $arrayReq[':codeLng'] = $codeLng;
        }

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetch();
    }
    
    /**
     * @param array<mixed> $array
     */
    public function updatePageClient(array $array) : void
    {
        $req = "UPDATE OXALINK_PAGES_CLIENT SET TITRE = :titre, TYPE_URL = ".sql::setBoolSql($array['TYPE_URL']).", FIC_OU_URL= :FIC_OU_URL, PAGE_PUBLIC = ".sql::setBoolSql($array['PAGE_PUBLIC'])." where CODE=:code and CODE_LNG = :codeLng";
        
        $arrayReq = [
            ':code' => $array['CODE'],
            ':codeLng' => $array['CODE_LNG'],
            ':titre' => $array['TITRE'],
            ':FIC_OU_URL' => $array['FIC_OU_URL'],
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
    }
    
    public function deletePageClient(string $code, ?string $codeLng = null): void
    {
        $req = "delete from OXALINK_PAGES_CLIENT where CODE=:code";
        $arrayReq[':code'] = $code;
        
        if ($codeLng) {
            $req .= " and CODE_LNG=:codeLng";
            $arrayReq[':codeLng'] = $codeLng;
        }
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
    }
    
    /**
     * @return array<mixed>
    */
    public function listeMailSociete()
    {
        $req = "select CODE_STE, RAIS_SOC, LIBELLE from SOCIETE with (nolock) left join PARAMDICO with (nolock) on PARAMDICO.ID2 = SOCIETE.CODE_STE and TYPEDICO='PF_OXALINK'";

        $requete = $this->pdo->prepare($req);
        $requete->execute();
        
        return $this->fetchAssocAll($req, []);
    }

    /**
     * @return array<mixed>
     */
    public function listeParamChpsFrs(): ?array
    {
        $req = "select * from PARAMDICO with (nolock) where TYPEDICO='OXALINK_FOURN_CHP' or TYPEDICO='OXALINK_FOURN_STR'";
        
        return $this->fetchAssocAll($req, []);
    }

    public function insertParamOxalink(string $table, string $colonne, string $value1, string $value2) : void
    {
        $req = "INSERT INTO PARAMDICO (TYPEDICO, ID1, ID2, ALPHA1, ALPHA2) VALUES ('OXALINK_FOURN_CHP',:ID1, :ID2, :ALPHA1, :ALPHA2);";

        
        $arrayReq[':ALPHA2'] = $value2;
        $arrayReq[':ALPHA1'] = $value1;
        $arrayReq[':ID2'] = $colonne;
        $arrayReq[':ID1'] = $table;
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
    }
    
    public function updateParamOxalink(string $table, string $colonne, string $value):void
    {
        $req = "UPDATE PARAMDICO SET LOGIQUE1 = ".($value? '0x46':'0x54')." WHERE TYPEDICO = 'OXALINK_FOURN_CHP' AND ID1 ='$table' AND ID2 = '$colonne'";

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

    /**
     * @return array<mixed>
     */
    public function listeAccesOxalink(): ?array
    {
        $req = "select * from PARAMDICO with (nolock) where TYPEDICO='OXALINK_FONCTIONS_CACHEES'";
        
        return $this->fetchAssocAll($req, []);
    }
}
