<?php

namespace Modules\Stock\Repository;

use DateTime;
use Exception;
use Helpers\Outil\Roles;
use Helpers\Pagination\PaginatorGestion;
use Helpers\QueryBuilder\AbsRepository;
use Modules\Stock\Service\RepoService;
use Modules\Stock\Trait\Repo\EnteteRepoTrait;

class StockArticleRepo extends AbsRepository
{
    use EnteteRepoTrait;

    /**
     * @param array<string,mixed> $post
     * @return array<int, array<string,string>>
     * @throws Exception
     */
    public function listStocksArticles(array $post): array
    {
        $arrayReq = [];
        $req = "SELECT DISTINCT 
                    SA.CODE_ART AS CODE_ART,
                    SA.DESIGN AS DESIGN,
                    SA.QTE_PHY AS QTE_PHY,
                    SA.TYPE_VALO AS TYPE_VALO,
                    SA.VALEUR AS VALEUR,
                    SA.STRATEG AS STRATEG,
                    SA.UNITE_STK AS UNITE_STK
                FROM SARTICLE SA with (nolock)
                LEFT JOIN ARTICLES ART ON SA.CODE_ART = ART.CODE_ART
                WHERE 1=1";

        $parameters = (new RepoService())->buildQuery($post, $req, $arrayReq, 'SARTICLE');

        $paginatorGestion = new PaginatorGestion();
        $paginatorGestion->injecterSqlCount($req, $parameters, $arrayReq, $this->pdo);

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

    /**
     * @param array<string,string> $post
     * @return array<int,array<string,string|int>>
     * @throws Exception
     */
    public function listStocksArticlesExport(array $post, string $listeSelect): array
    {
        $arrayReq = [];
        $req = "SELECT DISTINCT "
            . $listeSelect
            . " FROM SARTICLE SA with (nolock) "
            . "LEFT JOIN ARTICLES ART ON SA.CODE_ART = ART.CODE_ART "
            . "LEFT JOIN MEMOS ME ON SA.NM_DESCR = ME.NODEMEMO "
            . "WHERE 1=1 "
        ;

        (new RepoService())->buildQuery($post, $req, $arrayReq, 'SARTICLE');

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

    /**
     * @param string $codeArt
     * @return array<string,string>
     */
    public function findFromCodeArt(string $codeArt): array
    {
        $requete = "SELECT 
                        SA.*,
                        ME.CONTMEMO AS DESCRIPTION,
                        ME.NODEMEMO AS NODEMEMO,
                        SSF.DESIGNAT AS SSF_DESIGNAT,
                        FAB.RS AS FAB_LIBELLE,
                        GM.DESIGN AS GM_DESIGN,
                        ART.CODEFAB AS CODEFAB,
                        ART.CODEGAM AS CODEGAM,
                        ART.REFARTFAB AS REFARTFAB
                    FROM SARTICLE SA
                    LEFT JOIN ARTICLES ART ON SA.CODE_ART=ART.CODE_ART
                    LEFT JOIN MEMOS ME ON SA.NM_DESCR = ME.NODEMEMO
                    LEFT JOIN SSFAMILL SSF ON SA.SSFAM = SSF.CODE_SFA
                    LEFT JOIN FABRICAN FAB ON ART.CODEFAB = FAB.CODE
                    LEFT JOIN GAMMES GM ON GM.CODEGAMME = ART.CODEGAM
                    WHERE SA.CODE_ART = :codeArt";

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

    /**
     * @param array<string,mixed> $post
     * @param bool $paginate
     * @return array<int,array<string,mixed>>
     */
    public function listStocksArticlesSelectables(array $post, bool $paginate = true): array
    {
        $arrayReq = [];
        $requete = "SELECT DISTINCT 
                        SA.CODE_ART AS ART,
                        SF.CODE_SFA AS SSFAM,
                        SF.CODE_FAM AS FAM,
                        SA.DESIGN AS DESIGN 
                    FROM SARTICLE SA
                    INNER JOIN SSTOCKLO STO ON SA.CODE_ART = STO.CODE_ART
                    LEFT JOIN SSFAMILL SF ON SF.CODE_SFA = SA.SSFAM
                    LEFT JOIN SBE_LIGN SL ON SA.CODE_ART=SL.CODE_ART
                    LEFT JOIN SBE_ENTE SE ON SE.NO_BE = SL.NO_BE
                    WHERE 1=1  
                    " . Roles::getClausePerimetreSociete('STO.CODE_STE', $arrayReq);

        if (array_key_exists('SELECT_ALL', $post) && $post['SELECT_ALL'] === 'false') {
            return [];
        }
        if (!empty($post['FAMILLE'])) {
            $codeFams = array_keys(array_column($post['FAMILLE'], null, 'FAM'));
            $codeSfas = array_merge(...array_values(array_map(fn (array $famille) => array_keys(array_column($famille['SOUS_FAMILLES'] ?? [], null, 'CODE_SFA')), $post['FAMILLE'])));
            $codeFamParams = implode(',', array_map(fn (string $codeFam, int $key): string => ':codeFam' . $key, $codeFams, array_keys($codeFams)));
            $codeSfaParams = implode(',', array_map(fn (string $codeSfas, int $key): string => ':codeSfa' . $key, $codeSfas, array_keys($codeSfas)));

            $requete .= " AND SF.CODE_FAM IN ($codeFamParams) AND SA.SSFAM IN ($codeSfaParams) ";

            foreach ($codeFams as $key => $codeFam) {
                $arrayReq[':codeFam' . $key] = $codeFam;
            }

            foreach ($codeSfas as $key => $codeSfa) {
                $arrayReq[':codeSfa' . $key] = $codeSfa;
            }
        }
        if (!empty($post['SITE'])) {
            (new RepoService())->addSiteQueryShard($post, $requete, $arrayReq);
        }
        if (!empty($post['ARTICLE'])) {
            /** @phpstan-ignore-next-line */
            $codeArtParams = implode(',', array_map(fn (string $codeArt, int $key): string => ':codeArt' . $key, $post['ARTICLE'], array_keys($post['ARTICLE'])));
            $requete .= " AND STO.CODE_ART IN ($codeArtParams) ";

            foreach ($post['ARTICLE'] as $key => $codeArt) {
                $arrayReq[':codeArt' . $key] = $codeArt;
            }
        }
        if (!empty($post['DATE_BE']) && $dateBe = DateTime::createFromFormat('d/m/Y', $post['DATE_BE'])) {
            $requete .= " AND SE.DTE_CREAT=:dteCreate";
            $arrayReq[':dteCreate'] = $dateBe->format('Ymd');
        }
        if (!empty($post['NEXT_INV_EXCEEDED']) && $post['NEXT_INV_EXCEEDED'] === 'true') {
            $requete .= " AND STO.DTE_INV <= :today2";
            $arrayReq[':today2'] = date('Ymd');
        }
        if (!empty($post['EMPLAC'])) {
            /** @phpstan-ignore-next-line */
            $emplacParams = implode(',', array_map(fn (string $emplac, int $key): string => ':emplac' . $key, $post['EMPLAC'], array_keys($post['EMPLAC'])));
            $requete .= " AND STO.EMPLAC IN ($emplacParams) ";

            foreach ($post['EMPLAC'] as $key => $emplac) {
                $arrayReq[':emplac' . $key] = $emplac;
            }
        }
        if (!empty($post['DATEFERM']) && $post['DATEFERM'] !== '' && $dateFerm = DateTime::createFromFormat('d/m/Y', $post['DATEFERM'])) {
            $requete .= " AND (STO.DTE_FERM = '00000000' OR STO.DTE_FERM >= :dateFerm)";
            $arrayReq[':dateFerm'] = $dateFerm->format('Ymd');
        }

        $requete = $this->applyFilter($requete, $arrayReq, $post);

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

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