<?php

namespace Modules\Stock\Repository;

use Exception;
use Helpers\Outil\Sql;
use Helpers\Pagination\PaginatorGestion;
use Helpers\QueryBuilder\AbsRepository;
use Modules\Stock\Service\RepoService;

class LigneCahierInventaireRepo extends AbsRepository
{

    /**
     * @param array<string,string> $post
     * @return array<int,array<string,string|int>>
     * @throws Exception
     */
    public function listLignesCahierInventaire(array $post): array
    {
        $req = "WITH
                    STATS AS (
                        SELECT ART, SITE, SUM(QTE_REST) AS QTE_INDISPO FROM SINDISPO
                        WHERE QTE_REST > 0
                        GROUP BY ART,SITE
                    ),
                    ECARTS_SAISIS AS (
                        SELECT
                            CODE_ART,
                            QTE_REEL - QTE_THEO AS ECART_SAISI
                        FROM SCIINVLI
                        WHERE SITE = :site0 AND NO_INV = :noInv0
                    )
                SELECT DISTINCT
                    SCI.*,
                    ES.ECART_SAISI AS ECART_SAISI,
                    SA.DESIGN AS ART_DESIGN,
                    FA.DESIGNAT AS FAM_DESIGN,
                    SFA.DESIGNAT AS SSFAM_DESIGN,
                    STO.DESIGN AS SITE_DESIGN,
                    SSTO.EMPLAC AS EMPLAC,
                    SA.TRACABLE AS TRACABLE,
                    SA.TYPE_VALO AS TYPE_VALO,
                    SA.UNITE_STK AS UNITE_STK,
                    STO.DEVISE AS DEVISE,
                    STATS.QTE_INDISPO AS QTE_INDISPO
                FROM SCIINVLI SCI
                LEFT JOIN SARTICLE SA ON SCI.CODE_ART= SA.CODE_ART
                LEFT JOIN FAMILLE FA ON SCI.FAM = FA.CODE_FAM
                LEFT JOIN SSFAMILL SFA ON SCI.SSFAM = SFA.CODE_SFA
                LEFT JOIN SSITESTO STO ON SCI.SITE = STO.SITE
                LEFT JOIN SSTOCKLO SSTO ON SCI.CODE_ART = SSTO.CODE_ART
                LEFT JOIN STATS ON SCI.CODE_ART = STATS.ART AND SCI.SITE = STATS.SITE
                LEFT JOIN ECARTS_SAISIS ES ON SCI.CODE_ART = ES.CODE_ART
                WHERE SCI.SITE =:site1 AND SSTO.SITE =:site2 ";

        $arrayReq = [
            ':site0' => $post['SITE'],
            ':site1' => $post['SITE'],
            ':site2' => $post['SITE'],
            ':noInv0' => $post['filter_egal_SCI#point#NO_INV']
        ];

        if (Sql::toBool($post['SCIINVLI_REP_INCORRECTE'])) {
            $req .= " AND SA.TRACABLE=0x54 AND SCI.QTE_THEO != SCI.QTE_REEL";
        }

        $parameters = (new RepoService())->buildQuery($post, $req, $arrayReq, 'SCIINVLI');
        $paginatorGestion = new PaginatorGestion();
        $paginatorGestion->injecterSqlCount($req, $parameters, $arrayReq, $this->pdo);

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