<?php

namespace Modules\RechercheArticles\Repository;

use Helpers\Fichier\Fichier;
use Helpers\QueryBuilder\AbsRepository;
use Helpers\Session\ParseBase1;

abstract class Requete extends AbsRepository
{
    protected \PDO $pdoOxaFT;
    protected string $user;
    protected string $centre;
    protected string $langue;
    protected string $whereHistoCde;
    protected string $recherche;
    protected string $wherePannel;
    protected string $listeColonne;
    protected string $wherePannelGenerique;
    protected string $listeColonneGenerique;
    protected string $siteStock;
    protected int $disabledZoneGeo = 0;
    protected string $table; // HISTOCDE ou CATAPROC
    protected string $tri;

    /**
     *
     * @param \PDO $pdoOxaFT
     * @param string $user
     * @param string $centre
     * @param string $siteStock
     */
    public function __construct(\PDO $pdoOxaFT, $user, $centre, $siteStock)
    {
        Fichier::createFichierTmp('debug_req_FT'.date('Ymd').'.txt', "***************** ".date('m/d/Y h:i:s').' *****************'."\r\n", 'a+');

        parent::__construct($pdoOxaFT);
        $this->pdoOxaFT = $pdoOxaFT;

        $this->table = '';
        $this->user = $user;
        $this->centre = $centre;
        $this->langue = $_SESSION['Session_Langue'];
        $this->siteStock = $siteStock;
        $this->whereHistoCde = " and SOCLST like '%".$_SESSION["Session_SocUsr"]."%' ";
    }

    /**
     *
     * @param string $table
     */
    public function setTable($table) : void
    {
        $this->table = $table;
    }

    /**
     * @param string|null|bool $desactiveZoneGeo
     */
    public function setDesactiveZoneGeo($desactiveZoneGeo) : void
    {
        $this->disabledZoneGeo = $desactiveZoneGeo? 1:0;
    }

    /**
     *
     * @param string $recherche /!\ il faut penser a bien echaper ce champs pour que les procedure fonctionne "test..."
     * @param string $wherePannel
     * @param string $listeColonne
     * @param string $tri
     */
    public function setRecherche($recherche, $wherePannel = '', $listeColonne = '', $tri = '') : void
    {
        $this->recherche = $recherche;
        $this->wherePannel = $wherePannel;
        $this->listeColonne = $listeColonne;
        $this->tri = $tri;
    }
    /**
     *
     * @param string $recherche
     * @param string $wherePannel
     * @param string $listeColonne
     */
    public function setRechercheGenerique($recherche, $wherePannel = '', $listeColonne = '') : void
    {
        $this->recherche = $recherche;
        $this->wherePannelGenerique = $wherePannel;
        $this->listeColonneGenerique = $listeColonne;
    }

    protected function checkTableExist() : bool
    {
        if ($_SESSION['STATUT_RECH_ART'] != -1) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * @param array<mixed> $arrayReq
     */
    protected function debug(string $req, $arrayReq) : void
    {
        $backLigne = "\r\n";

        $requeteCompiler = $req;
        foreach ($arrayReq as $key => $ff) {
            $requeteCompiler = str_replace($key.',', "'".$ff."',", $requeteCompiler);
            $requeteCompiler = str_replace($key.'', "'".$ff."'", $requeteCompiler);
        }

        $arrayLigne = [
            'Requete : '.$requeteCompiler,
            '',
        ];

        $contenu = implode($backLigne, $arrayLigne);

        Fichier::createFichierTmp('debug_req_FT'.date('Ymd').'.txt', $contenu.$backLigne, 'a+');
    }

    /**
     *
     * @param string|int $page
     * @param string|int $articleParPage
     * @return array<mixed>
     */
    public function articleByRank($page, $articleParPage)
    {
        $req = "exec ".$this->getRequeteArticleByRank()." 
                :user, 
                :center,
                :siteStock, 
                $this->disabledZoneGeo, 
                :table, 
                :recherche, 
                :where,
                '$this->listeColonne',
                '$this->tri',
                :articleParPage,
                :page";

                
        if ($this->table == 'HISTOCDE') {
            $where = $this->wherePannel.$this->whereHistoCde;
        } else {
            $where = $this->wherePannel;
        }

        $arrayReq = [
            ':user'             => $this->user,
            ':center'           => $this->centre,
            ':siteStock'        => $this->siteStock,
            ':table'            => $this->table,
            ':recherche'        => str_replace('*', '', $this->recherche),
            ':where'            => $where,
            ':articleParPage'   => $articleParPage,
            ':page'             => $page,
        ];
        
        if ($this->checkTableExist()) {
            $this->debug($req, $arrayReq);
            return $this->fetchAssocAll($req, $arrayReq);
        } else {
            return [];
        }
    }

 /**
     *
     * @return array<mixed>
     */
    public function listePannel(bool $rayonSelect = false)
    {

        // BBAPERF : $req = "exec ".((trim($this->recherche)!= '' || $rayonSelect)?'Oxaproc_ChercheCatArtContNav':'Oxaproc_ChercheCatArtContNavBBA')."
        $req = "exec ".$this->getRequeteListePannel()." 
        :user, 
        :center,
        :siteStock, 
        $this->disabledZoneGeo, 
        :table, 
        :recherche, 
        :where, 
        '$this->listeColonne'";

        if ($this->table == 'HISTOCDE') {
            $where = $this->wherePannel.$this->whereHistoCde;
        } else {
            $where = $this->wherePannel;
        }

        $arrayReq = [
            ':user'             => $this->user,
            ':center'           => $this->centre,
            ':siteStock'        => $this->siteStock,
            ':table'            => $this->table,
            ':recherche'        => $this->recherche,
            ':where'            => $where,
        ];
        
        if ($this->checkTableExist()) {
            $this->debug($req, $arrayReq);
            return $this->fetchAssocAll($req, $arrayReq);
        } else {
            return [];
        }
    }

    /**
     *
     * @return array<mixed>
     */
    public function listeCarSelect()
    {
        $req = "exec ".$this->getRequeteListeCarSelect()." 
                :user, 
                :center,
                :siteStock, 
                $this->disabledZoneGeo, 
                :table, 
                :recherche, 
                :where, 
                '$this->listeColonne'";

        if ($this->table == 'HISTOCDE') {
            $where = $this->wherePannel.$this->whereHistoCde;
        } else {
            $where = $this->wherePannel;
        }

        $arrayReq = [
            ':user'             => $this->user,
            ':center'           => $this->centre,
            ':siteStock'        => $this->siteStock,
            ':table'            => $this->table,
            ':recherche'        => $this->recherche,
            ':where'            => $where,
        ];
        
        if ($this->checkTableExist()) {
            $this->debug($req, $arrayReq);
            return $this->fetchAssocAll($req, $arrayReq);
        } else {
            return [];
        }
    }

    /**
     *
     * @param string $table
     * @return array<mixed>
     */
    public function countArticle($table)
    {
        $req = "exec ".$this->getRequeteCountArticle()." 
                :user,
                :center,
                :siteStock, 
                $this->disabledZoneGeo, 
                :table, 
                :recherche, 
                :where, 
                '$this->listeColonne'";

        if ($table == 'HISTOCDE') {
            $where = $this->wherePannel." and SOCLST like '%".$_SESSION["Session_SocUsr"]."%' ";
        } else {
            $where = $this->wherePannel;
        }

        $arrayReq = [
            ':user'             => $this->user,
            ':center'           => $this->centre,
            ':siteStock'        => $this->siteStock,
            ':table'            => $table,
            ':recherche'        => $this->recherche,
            ':where'            => $where,
        ];
        if ($this->checkTableExist()) {
            $this->debug($req, $arrayReq);
            return $this->fetchAssocOne($req, $arrayReq);
        } else {
            return [];
        }
    }
    /**
     *
     * @param string $table
     * @return array<mixed>
     */
    public function countArticleTOP($table)
    {
       //return [];
        // BBAPERF : $req = "exec Oxaproc_CountCatArtContRankDesBBATOP
        $req = "exec Oxaproc_CountCatArtContRankDes 
                :user,
                :center,
                :siteStock, 
                $this->disabledZoneGeo, 
                :table, 
                :recherche, 
                :where, 
                '$this->listeColonne'";

        if ($table == 'HISTOCDE') {
            $where = $this->wherePannel." and SOCLST like '%".$_SESSION["Session_SocUsr"]."%' ";
        } else {
            $where = $this->wherePannel;
        }

        $arrayReq = [
            ':user'             => $this->user,
            ':center'           => $this->centre,
            ':siteStock'        => $this->siteStock,
            ':table'            => $table,
            ':recherche'        => $this->recherche,
            ':where'            => $where,
        ];
        if ($this->checkTableExist()) {
            $this->debug($req, $arrayReq);
            return $this->fetchAssocOne($req, $arrayReq);
        } else {
            return [];
        }
    }

    private function decoupageExist() : string
    {
        $req = "select COUNT(1) count from PROFILCTR  with (nolock)  WHERE CODE_CTR = :centre AND PROFIL <> ''";

        $arrayReq = [
            ':centre' => $this->centre
        ];

        return $this->fetchAssocOne($req, $arrayReq)['count']??'0';
    }

    /**
     * @return array<mixed>
     */
    public function getFullRequeteCountArticleWithTop1000(string $table): array
    {
        $zoneGeo = '';
        
        if ($this->siteStock) {
            $whereSiteStock = " ((COD_FOURN = 'RESERVESTOCK' AND EXISTS (SELECT * FROM STRING_SPLIT(LSTSITSTK, ';') WHERE value IN ('".$this->siteStock."'))) OR (COD_FOURN <> 'RESERVESTOCK')) AND ";
        } else {
            $whereSiteStock = " COD_FOURN <> 'RESERVESTOCK' AND ";
        }

        if ($this->disabledZoneGeo) {
            $zoneGeo = '';
        } else {
            $decoupageExist = $this->decoupageExist();

            if ($decoupageExist) {
                $zoneGeo = "OUTER APPLY string_split(a.DCOUPLST, ';') g
                            LEFT JOIN PROFILCTR p  with (nolock)  on p.CODE_CTR = '{$this->centre}' AND (p.PROFIL = g.value)";
            }
        }

        if ($table == 'HISTOCDE') {
            $where = $this->wherePannel." and SOCLST like '%".$_SESSION["Session_SocUsr"]."%' ";
        } else {
            $where = $this->wherePannel;
        }

        $requete = "SELECT COUNT(*) AS 'LIGNES' FROM $table a  with (nolock) 
                    INNER JOIN (
                        SELECT DISTINCT top 1000 a.ROW_COMPTEUR FROM $table a  with (nolock) 
                        CROSS APPLY string_split(a.CATPRLST, ';') s
                        INNER JOIN PROFILPUB c  with (nolock)  on c.CODE_USER = '{$this->user}' AND c.PROFIL = s.value 
                        ".$zoneGeo."
                        WHERE 
                        ".$whereSiteStock."
                        (DATEPUB   <= '".date('Ymd')."' OR DATEPUB = '00000000')    
                        AND (VALIDCATA >= '".date('Ymd')."' OR VALIDCATA = '00000000') 
                        AND ARTFRAISPORT = 'N'
                        ".$where."
                    ) b
                    on a.ROW_COMPTEUR = b.ROW_COMPTEUR";

        if ($this->recherche) {
            $requete .= $this->getJoinFilter($table);
        }

        if ($this->checkTableExist()) {
            $this->debug($requete, []);
            return $this->fetchAssocOne($requete, []);
        } else {
            return [];
        }
    }

    /**
     *
     * @param string $art_sf
     * @param string|int $page
     * @param string|int $articleParPage
     * @return array<mixed>
     */
    public function articleGenerique($art_sf, $page, $articleParPage)
    {
        $req = "exec ".$this->getRequeteArticleGenerique()." 
                :user, 
                :langue, 
                :art_sf, 
                :recherche, 
                :where,
                '$this->listeColonneGenerique',
                :articleParPage,
                :page";

        $arrayReq = [
            ':user'             => $this->user,
            ':langue'           => $this->langue,
            ':art_sf'           => $art_sf,
            ':recherche'        => $this->recherche,
            ':page'             => $page,
            ':articleParPage'   => $articleParPage,
            ':where'            => $this->wherePannelGenerique,
        ];

        if ($this->checkTableExist()) {
            $this->debug($req, $arrayReq);
            return $this->fetchAssocAll($req, $arrayReq);
        } else {
            return [];
        }
    }

    /**
     *
     * @param string $art_sf
     * @return array<mixed>
     */
    public function countArticleGenerique($art_sf)
    {
        $req = "exec ".$this->getRequeteCountArticleGenerique()." 
                :user,
                :langue,
                :art_sf, 
                :recherche, 
                :where, 
                '$this->listeColonneGenerique'";

        $arrayReq = [
            ':user'             => $this->user,
            ':langue'           => $this->langue,
            ':art_sf'           => $art_sf,
            ':recherche'        => $this->recherche,
            ':where'            => $this->wherePannelGenerique,
        ];
        
        $requete = $this->pdoOxaFT->prepare($req);
        $requete->execute($arrayReq);
        
        if ($this->checkTableExist()) {
            $this->debug($req, $arrayReq);
            return $this->fetchAssocOne($req, $arrayReq);
        } else {
            return [];
        }
    }


    abstract protected function getRequeteArticleByRank():string;
    abstract protected function getRequeteListePannel():string;
    abstract protected function getRequeteListeCarSelect():string;
    abstract protected function getRequeteCountArticle():string;
    abstract protected function getRequeteArticleGenerique():string;
    abstract protected function getRequeteCountArticleGenerique():string;
    abstract protected function getJoinFilter(string $table):string;
}
