<?php

namespace Modules\Stock\Repository;

use Entity\Model\SempInv;
use Exception;
use Helpers\Outil\Roles;
use Helpers\Pagination\PaginatorGestion;
use Helpers\QueryBuilder\AbsRepository;
use Modules\Stock\Service\ParametrageService;
use Modules\Stock\Service\RepoService;
use Modules\Stock\Trait\Repo\ImputationRepoTrait;

class StockLocalRepo extends AbsRepository
{
    use ImputationRepoTrait;

    /**
     * @param array<string, mixed> $post
     * @return array<string, mixed>
     * @throws Exception
     */
    public function listStocksLocaux(array $post): array
    {
        $arrayReq = [];
        $qteDispoQueryShard = $this->getQteDispoQueryShard();

        $requete = "WITH STATS AS (
                        SELECT STO.SITE AS SITE, STO.CODE_ART AS CODE_ART, $qteDispoQueryShard FROM SSTOCKLO STO
                    ),
                    NBDOCS AS (
                        SELECT
                            COD_ENR,
                            COUNT(DA.COD_ENR) AS NB_DOC FROM DOC_ATT DA 
                        LEFT JOIN SSTOCKLO STO ON DA.COD_ENR = CONCAT(STO.SITE,STO.CODE_ART)
                        WHERE DA.NO_DA = 'SSTOCKLO'
                        GROUP BY COD_ENR
                    )
                    SELECT DISTINCT
                        STO.SITE AS SITE,
                        STO.EMPLAC AS EMPLAC,
                        SI.DESIGN AS SITE_DESIGN,
                        STO.CODE_ART AS CODE_ART,
                        SA.DESIGN AS ART_DESIGN,
                        STO.QTE_PHYS AS QTE_PHYS,
                        STO.QTE_RES AS QTE_RES,
                        STO.QTE_INDIS AS QTE_INDIS,
                        SA.UNITE_STK AS UNITE_STK,
                        S.QTE_DISPO AS QTE_DISPO,
                        NB.NB_DOC AS NB_DOC
                    FROM SSTOCKLO STO
                    LEFT JOIN SSITESTO SI ON STO.SITE=SI.SITE
                    LEFT JOIN SARTICLE SA ON STO.CODE_ART=SA.CODE_ART
                    LEFT JOIN STATS S ON STO.SITE=S.SITE AND STO.CODE_ART=S.CODE_ART
                    LEFT JOIN NBDOCS NB ON NB.COD_ENR=CONCAT(STO.SITE,STO.CODE_ART)
                    LEFT JOIN TRAD_ART TA ON STO.CODE_ART = TA.COD_ART WHERE 1=1 "
            . Roles::getClausePerimetreSociete('STO.CODE_STE', $arrayReq);


        $this->addMultilingueFilter($post);
        $parameters =  (new RepoService())->buildQuery($post, $requete, $arrayReq, 'SSTOCKLO');

        (new PaginatorGestion())->injecterSqlCount($requete, $parameters, $arrayReq, $this->pdo);

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

    /**
     * @param array<string,string> $post
     * @return array<int,array<string,string|int>>
     * @throws Exception
     */
    public function listeStocksLocauxExport(array $post, string $listeSelect): array
    {
        $arrayReq = [];
        $qteDispoQueryShard = $this->getQteDispoQueryShard();

        $req = "WITH STATS AS (SELECT STO.SITE AS SITE, STO.CODE_ART AS CODE_ART, $qteDispoQueryShard FROM SSTOCKLO STO)
                SELECT DISTINCT $listeSelect FROM SSTOCKLO STO
                LEFT JOIN SSITESTO SI ON STO.SITE=SI.SITE
                LEFT JOIN SARTICLE SA ON STO.CODE_ART=SA.CODE_ART
                LEFT JOIN STATS S ON STO.SITE=S.SITE AND STO.CODE_ART=S.CODE_ART
                LEFT JOIN TRAD_ART TA ON STO.CODE_ART = TA.COD_ART WHERE 1=1 "
                . Roles::getClausePerimetreSociete('STO.CODE_STE', $arrayReq);

        $this->addMultilingueFilter($post);
        (new RepoService())->buildQuery($post, $req, $arrayReq, 'SSTOCKLO');

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

    /**
     * @return array<string,mixed>
     * @throws Exception
     */
    public function findStockLocal(string $codeArt, string $site): array
    {
        $qteDispoQueryShard = $this->getQteDispoQueryShard();

        $requete = "SELECT
                        STO.QTE_PHYS AS QTE_PHYS,
                        STO.QTE_RES AS QTE_RES,
                        STO.QTE_PREV AS QTE_PREV,
                        STO.QTE_INDIS AS QTE_INDIS,
                        STO.QTE_TRF AS QTE_TRF,
                        STO.QTE_REAP AS QTE_REAP,
                        STO.QTE_LIV AS QTE_LIV,
                        STO.QTE_ACHEM AS QTE_ACHEM,
                        STO.VAL_LOCAL AS VAL_LOCAL,
                        $qteDispoQueryShard,
                        STO.VAL_LOCAL AS VAL_LOCAL,
                        STO.SITE AS SITE,
                        STO.CODE_ART AS CODE_ART,
                        SA.UNITE_STK AS UNITE_STK
                    FROM SSTOCKLO STO
                    LEFT JOIN SARTICLE SA ON STO.CODE_ART=SA.CODE_ART
                    WHERE STO.CODE_ART=:codeArt AND STO.SITE=:site
        ";

        $arrayReq = [':codeArt' => $codeArt, ':site' => $site];

        return $this->fetchAssocOne($requete, $arrayReq);
    }

    /**
     * @param string $site
     * @param string $codeArt
     * @return array<string,mixed>
     * @throws Exception
     */
    public function findFromId(string $site, string $codeArt): array
    {
        $qteDispoQueryShard = $this->getQteDispoQueryShard();
        $siSectorisationJoinValue = $this->getImputationSectoJoinValue('SISECTOR', "STO.CODE_STE");

        $requete = "SELECT 
                        STO.*,
                        $qteDispoQueryShard,
                        SE.LIBELLE AS SI_LIBELLE,
                        CDC.LIBELLE AS CDC_LIBELLE,
                        SCA.LIBELLE AS SCA_LIBELLE,
                        SA.TRACABLE AS TRACABLE,
                        SA.UNITE_STK AS UNITE_STK
                    FROM SSTOCKLO STO
                    LEFT JOIN SECTIMPU SE ON SE.CODE_SI=STO.STKLO_SI AND SE.CODE_STE = ".$siSectorisationJoinValue."
                    LEFT JOIN CODECOMP CDC ON CDC.CODE_CDC=STO.STKLO_CDC AND CDC.CODE_STE = ".$this->getImputationSectoJoinValue('CDCSECTO', "STO.CODE_STE")."
                    LEFT JOIN SSCPTANA SCA ON SCA.CODE_SCA=STO.STKLO_SCA AND SCA.CODE_STE = ".$this->getImputationSectoJoinValue('SCASECTO', "STO.CODE_STE")."
                    LEFT JOIN SARTICLE SA ON STO.CODE_ART=SA.CODE_ART
                    WHERE STO.SITE=:site AND STO.CODE_ART=:codeArt";

        $arrayReq = [':codeArt' => $codeArt, ':site' => $site];

        return $this->fetchAssocOne($requete, $arrayReq);
    }

    /**
     * @param string $codeArt
     * @return array<string,string>
     */
    public function findGlobal(string $codeArt): array
    {
        $qteDispoQueryShard = $this->getGlobalQteDispoQueryShard();

        $requete = "SELECT 
                        SUM(QTE_PREV) AS QTE_PREV,
                        SUM(QTE_STSUR) AS QTE_STSUR,
                        SUM(QTE_PHYS) AS QTE_PHYS,
                        SUM(QTE_RES) AS QTE_RES,
                        SUM(QTE_INDIS) AS QTE_INDIS,
                        SUM(QTE_TRF) AS QTE_TRF,
                        $qteDispoQueryShard
                    FROM SSTOCKLO 
                    WHERE CODE_ART=:codeArt";

        return $this->fetchAssocOne($requete, [':codeArt' => $codeArt]);
    }

    /**
     * @param array<string,mixed> $post
     * @return array<int,array<string,string>>
     * @throws Exception
     */
    public function listSites(array $post): array
    {
        $qteDispoQueryShard = $this->getQteDispoQueryShard();

        $requete = "SELECT 
                        STO.CODE_ART AS CODE_ART,
                        STO.SITE AS SITE,
                        SSI.DESIGN AS SITE_DESIGN,
                        STO.CODE_STE AS CODE_STE,
                        STO.QTE_PHYS AS QTE_PHYS,
                        STO.QTE_INDIS AS QTE_INDIS,
                        SA.UNITE_STK AS UNITE_STK,
                        SO.RAIS_SOC AS RAIS_SOC,
                        $qteDispoQueryShard
                    FROM SSTOCKLO STO
                    LEFT JOIN SSITESTO SSI ON STO.SITE = SSI.SITE
                    LEFT JOIN SARTICLE SA ON STO.CODE_ART = SA.CODE_ART
                    LEFT JOIN SOCIETE SO ON STO.CODE_STE = SO.CODE_STE
                    WHERE 1=1 ";

        $arrayReq = [];
        $parameters =  (new RepoService())->buildQuery($post, $requete, $arrayReq, 'SSTOCKLO');
        (new PaginatorGestion())->injecterSqlCount($requete, $parameters, $arrayReq, $this->pdo);

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

    /**
     * @param array<string,mixed> $post
     * @return array<int,array<string,mixed>>
     */
    public function getEmplacements(array $post, bool $paginate = true): array
    {
        $arrayReq = [];
        $requete = "SELECT DISTINCT SSTOCKLO.SITE, SSTOCKLO.EMPLAC FROM SSTOCKLO WHERE EMPLAC != '' AND (DTE_FERM = '00000000' OR DTE_FERM >= :today) "
            . Roles::getClausePerimetreSociete('SSTOCKLO.CODE_STE', $arrayReq);

        if (!empty($post['SITE'])) {
            (new RepoService())->addSiteQueryShard($post, $requete, $arrayReq, 'SSTOCKLO');
        }
        if (array_key_exists('SELECT_ALL', $post) && $post['SELECT_ALL'] === 'false') {
            return [];
        }

        $arrayReq[':today'] = date('Ymd');
        $requete = $this->applyFilter($requete, $arrayReq, $post);

        if ($paginate) {
            $paginatorGestion = new PaginatorGestion();
            $paginatorGestion->injecterSqlCount($requete, $post, $arrayReq, $this->pdo);
        }

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

    /**
     * @param string $noInv
     * @param string|null $dateFermeture
     * @return array<int,array<string,mixed>>
     * @throws Exception
     */
    public function findAllFromNoInv(string $noInv, ?string $dateFermeture = null): array
    {
        $requete = "SELECT STO.*, SA.FAM AS FAM, SA.SSFAM AS SSFAM FROM SSTOCKLO STO
                    INNER JOIN SARTINVE SA ON STO.CODE_ART = SA.ART 
                    INNER JOIN SCINVENT SC ON STO.SITE = SC.SITE
                    ";

        $hasSempInv = !empty((new SempInv($this->pdo))->findAll(['NO_INV' => $noInv]));
        if ($hasSempInv) {
            $requete .= "INNER JOIN SEMP_INV SI ON SA.NO_INV = SI.NO_INV";
        }

        $requete .= " WHERE SA.NO_INV = :noInv0 AND SC.NO_INV = :noInv1 ";

        if ($hasSempInv) {
            $requete .= "AND STO.EMPLAC LIKE SI.EMPLACE + '%'";
        }

        $arrayReq = [':noInv0' => $noInv, ':noInv1' => $noInv];

        if ($dateFermeture) {
            $requete .= " AND (STO.DTE_FERM = '00000000' OR STO.DTE_FERM > :dateFermeture)";
            $arrayReq[':dateFermeture'] = $dateFermeture;
        }

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

    public function libererArticlesBloques(string $noInv): void
    {
        $req = "UPDATE S SET S.MVT_VERR=0x46 FROM SSTOCKLO S INNER JOIN SCIINVLI I ON I.NO_INV=:noInv AND I.SITE=S.SITE AND I.CODE_ART=S.CODE_ART;";

        $arrayReq = [':noInv' => $noInv];

        $this->execute($req, $arrayReq);
    }

    /**
     * @param array<string,mixed> $post
     * @return void
     */
    private function addMultilingueFilter(array &$post): void
    {
        if (array_key_exists('MULTILINGUE', $post)) {
            $post['filter_TA#point#DES_ART'] = $post['filter_SA#point#DESIGN'];
            unset($post['filter_SA#point#DESIGN']);
        }
    }

    /**
     * @return string
     * @throws Exception
     */
    private function getQteDispoQueryShard(): string
    {
        $qteRes = (new ParametrageService($this->pdo))->isQteDispoComputableWithQteReservee() ? "- STO.QTE_RES":" ";

        return "STO.QTE_PHYS " . $qteRes. " - STO.QTE_INDIS - STO.QTE_TRF - STO.QTE_PREV AS QTE_DISPO";
    }

    private function getGlobalQteDispoQueryShard(): string
    {
        $qteRes = (new ParametrageService($this->pdo))->isQteDispoComputableWithQteReservee() ? "- SUM(QTE_RES)":" ";

        return "SUM(QTE_PHYS) " . $qteRes. " - SUM(QTE_INDIS) - SUM(QTE_TRF) - SUM(QTE_PREV) AS QTE_DISPO";
    }
}
