<?php

namespace Modules\Stock\Repository;

use Exception;
use Helpers\QueryBuilder\AbsRepository;
use Modules\Stock\Trait\Repo\ImputationRepoTrait;
use Modules\Stock\Trait\Repo\LigneRepoTrait;

class LigneDemandeServiceRepo extends AbsRepository
{
    use LigneRepoTrait;
    use ImputationRepoTrait;

    /**
     * @return array<int, array<string, mixed>>
     */
    public function findAllFromNoDs(string $noDs, bool $hasResteOnly = false): array
    {
        $shard = '';
        if ($hasResteOnly) {
            $shard .= " AND LIGNES.RESTE > 0";
        }

        $siSectorisationJoinValue = $this->getImputationSectoJoinValue('SISECTOR', "SDS.CODE_STE");

        $requete = "WITH LIGNES AS (
                        SELECT DISTINCT
                            SL.NO_DS AS NO_DS,
                            SL.NO_LIG_DS AS NO_LIG_DS,
                            SL.NO_DA AS NO_DA,
                            SL.NO_PO_DA AS NO_PO_DA,
                            SL.ART AS ART,
                            SA.DESIGN AS ART_DESIGN,
                            SL.QTE_DEM AS QTE_DEM,
                            SL.ETAT_LIG AS ETAT_LIG,
                            SL.ORIGINE AS ORIGINE,
                            STO.DESIGN AS ORIGINE_DESIGN,
                            SL.DESTINAT AS DESTINAT,
                            SL.RESTE AS RESTE,
                            SA.UNITE_STK AS UNITE_STK,
                            SL.DTE_SOUH AS DTE_SOUH,
                            SI.CODE_SI AS CODE_SI,
                            SI.LIBELLE AS SECTIMP_LIBELLE,
                            SL.CODGES AS CODGES,
                            SL.LIBGES AS LIBGES,
                            CC.CODE_CDC AS CODE_CDC,
                            CC.LIBELLE AS CODECOMP_LIBELLE,
                            SCA.LIBELLE AS SSCPTANA_LIBELLE,
                            SCA.CODE_SCA AS CODE_SCA,
                            CASE
                                WHEN SDS.TYPE_DEM = 'S' AND SBS.NO_DS = SL.NO_DS AND SBS.NO_LIG_DS = SL.NO_LIG_DS THEN 'SBS_LIGN'
                                WHEN SDS.TYPE_DEM = 'T' AND SBT.NO_DS = SL.NO_DS AND SBT.NO_LIG_DS = SL.NO_LIG_DS THEN 'SBT_LIGN'
                                ELSE ''
                            END AS IS_USED_BY,
                            T.RS AS DESTINAT_LIBELLE,
                            STO2.DESIGN AS DESTINAT_T_LIBELLE
                        FROM SDS_LIGN SL
                        LEFT JOIN SDS_ENTE SDS ON SL.NO_DS = SDS.NO_DS
                        LEFT JOIN SARTICLE SA ON SL.ART = SA.CODE_ART
                        LEFT JOIN SSITESTO STO ON SL.ORIGINE = STO.SITE
                        LEFT JOIN SSITESTO STO2 ON SL.DESTINAT = STO2.SITE
                        LEFT JOIN TIERS T ON SL.DESTINAT = T.CODE
                        LEFT JOIN SECTIMPU SI ON SL.SECTIMP = SI.CODE_SI AND SI.CODE_STE = ".$siSectorisationJoinValue."
                        LEFT JOIN CODECOMP CC ON SL.CODE_COMP = CC.CODE_CDC AND CC.CODE_STE = ".$this->getImputationSectoJoinValue('CDCSECTO', "SDS.CODE_STE")."
                        LEFT JOIN SSCPTANA SCA ON SL.SSCPTEANA = SCA.CODE_SCA AND SCA.CODE_STE = ".$this->getImputationSectoJoinValue('SCASECTO', "SDS.CODE_STE")."
                        LEFT JOIN SBS_LIGN SBS ON SDS.TYPE_DEM = 'S' AND SBS.NO_DS = SL.NO_DS AND SBS.NO_LIG_DS = SL.NO_LIG_DS
                        LEFT JOIN SBT_LIGN SBT ON SDS.TYPE_DEM = 'T' AND SBT.NO_DS = SL.NO_DS AND SBT.NO_LIG_DS = SL.NO_LIG_DS
                ) SELECT * FROM LIGNES WHERE LIGNES.NO_DS = :noDs ".$shard." ORDER BY LIGNES.NO_LIG_DS ASC 
        ";

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

    /**
     * @throws Exception
     */
    public function isDemandeServiceFullySolded(int $noDs): bool
    {
        $requete = "SELECT SDS.ETAT_LIG FROM SDS_LIGN SDS WHERE SDS.NO_DS =:noDs AND SDS.ETAT_LIG != 'S'";

        return empty($this->fetchAssocAll($requete, [':noDs' => $noDs]));
    }

    /**
     * @param int $noBon
     * @param string $codeTab
     * @return null|int
     * @throws Exception
     */
    public function getNoDsFromNoBon(int $noBon, string $codeTab): ?int
    {
        $requete = "SELECT SDS.NO_DS FROM SDS_LIGN SDS
                    LEFT JOIN " .$codeTab. " SB ON SDS.NO_DS = SB.NO_DS AND SDS.NO_LIG_DS = SB.NO_LIG_DS
                    WHERE SB." .$this->getColNoFromCodeTab($codeTab). "=:noBon";

        $results = $this->fetchAssocAll($requete, [':noBon' => $noBon]);

        return !empty($results) ? (int) $results[0]['NO_DS']: null;
    }

    /**
     * @param int $noBs
     * @return bool
     */
    public function updateEtatLignesFromNoBs(int $noBs): bool
    {
        $requete = "WITH NO_TABLE AS (
                        SELECT DISTINCT SDS.NO_DS AS NO_DS FROM SDS_LIGN SDS
                        LEFT JOIN SBS_LIGN SB ON SB.NO_DS = SDS.NO_DS AND SB.NO_LIG_DS = SDS.NO_LIG_DS
                        WHERE SB.NO_BS=:noBs 
                    )
                    UPDATE SDS2
                        SET SDS2.ETAT_LIG = 'S'
                        FROM SDS_LIGN SDS2 
                        JOIN NO_TABLE NT ON NT.NO_DS = SDS2.NO_DS
                        LEFT JOIN SBS_LIGN SB ON SB.NO_DS = SDS2.NO_DS AND SB.NO_LIG_DS = SDS2.NO_LIG_DS
                    WHERE SDS2.RESTE = 0 AND SDS2.ETAT_LIG='E' AND SDS2.NO_LIG_DS NOT IN (
                        SELECT SDS.NO_LIG_DS FROM SDS_LIGN SDS
                        LEFT JOIN SBS_LIGN SB ON SB.NO_DS = SDS.NO_DS AND SB.NO_LIG_DS = SDS.NO_LIG_DS
                        LEFT JOIN SBS_ENTE ENT ON SB.NO_BS = ENT.NO_BS
                        JOIN NO_TABLE NT ON NT.NO_DS = SDS.NO_DS
                        WHERE ENT.DTE_VALID = '00000000'
                    )";

        $arrayReq = [':noBs' => $noBs];
        $query = $this->pdo->prepare($requete);

        return $query->execute($arrayReq);
    }

    public function updateEtatLignesFromNoBt(int $noBt): bool
    {
        $requete = "WITH NO_TABLE AS (
                        SELECT DISTINCT SDS.NO_DS AS NO_DS FROM SDS_LIGN SDS
                        LEFT JOIN SBT_LIGN SB ON SB.NO_DS = SDS.NO_DS AND SB.NO_LIG_DS = SDS.NO_LIG_DS
                        WHERE SB.NO_BT=:noBt 
                    )
                    UPDATE SDS2
                        SET SDS2.ETAT_LIG = 'S'
                        FROM SDS_LIGN SDS2 
                        JOIN NO_TABLE NT ON NT.NO_DS = SDS2.NO_DS
                        LEFT JOIN SBT_LIGN SB ON SB.NO_DS = SDS2.NO_DS AND SB.NO_LIG_DS = SDS2.NO_LIG_DS
                    WHERE SDS2.RESTE = 0 AND SDS2.ETAT_LIG='E' AND SDS2.NO_LIG_DS NOT IN (
                        SELECT SDS.NO_LIG_DS FROM SDS_LIGN SDS
                        LEFT JOIN SBT_LIGN SB ON SB.NO_DS = SDS.NO_DS AND SB.NO_LIG_DS = SDS.NO_LIG_DS
                        LEFT JOIN SBT_ENTE ENT ON SB.NO_BT = ENT.NO_BT
                        JOIN NO_TABLE NT ON NT.NO_DS = SDS.NO_DS
                        WHERE ENT.DTE_VAL_E = '00000000' AND ENT.DTE_VAL_S = '00000000'
                    )";

        $arrayReq = [':noBt' => $noBt];
        $query = $this->pdo->prepare($requete);

        return $query->execute($arrayReq);
    }

    /**
     * @param string $codeTab
     * @return string
     * @throws Exception
     */
    private function getColNoFromCodeTab(string $codeTab): string
    {
        return match ($codeTab) {
            'SBS_LIGN' => 'NO_BS',
            'SBT_LIGN' => 'NO_BT',
            default => throw new Exception("Code tab '$codeTab' not found"),
        };
    }
}
