<?php

namespace Modules\Consultation\Repository;

use Entity\Model\AoDaReP;
use Entity\Model\AoRep;
use Entity\Model\Bdd;
use Entity\Model\DccArticles;
use Helpers\Outil\ArrayHelper;
use Helpers\Outil\Sql;
use Helpers\QueryBuilder\AbsRepository;
use Modules\GlobalApplication\Service\ConvertionDeviseService;

class ImportExcelRepo extends AbsRepository
{

    /**
     * @return array<mixed>
     */
    public function getInfoImport(string $importId) :array
    {
        if (!ctype_digit($importId)) {
            return [];
        }
        // TODO : CHAMPS SUP
        return $this->fetchAssocOne(
            "SELECT
                AOEXCENV.*,
                AO_ENTET.DTE_VALID_AO as Entet_DTE_VALID_AO,
                AO_ENTET.VALCHXFOU as Entet_VALCHXFOU,
                AO_ENTET.VARIANTE as Entet_VARIANTE,
                AO_ENTET.TYPEDEL as Entet_TYPEDEL,
                AO_ENTET.CODE_STE as Entet_CODE_STE,
                SOCIETE.DEVISE as Societe_DEVISE,
                DeviseSoc.VALEUR as DeviseSoc_VALEUR,
                AoExcEnvCompl.TYPE_FG as FournisseurType,
                AOEXCENTE.LIGUNAO as Form_LIGUNAO,
                AOEXCENTE.COLUNAO as Form_COLUNAO,
                AOEXCENTE.NBLIGAO as Form_NBLIGAO,
                AOEXCENTE.NLIG_ST as Form_NLIG_ST,
                COALESCE(FOURNISS.COD_LNG, GRPFOU.COD_LNG) as Fournisseur_COD_LNG,
                CASE WHEN AOEXCENTE.CODEFORM IS NULL THEN 0 ELSE 1 END as hasForm,
                (
                    SELECT COUNT(*)
                    FROM AO_DAREP with(nolock)
                    WHERE AO_DAREP.NO_AO = AOEXCENV.NOAO
                        AND AO_DAREP.CODE_FOU = AOEXCENV.CODE_FOU
                        AND AO_DAREP.TYPE_FG = AoExcEnvCompl.TYPE_FG
                ) as countResponse,
                (
                    SELECT COUNT(*) 
                    FROM AO_QST_TH with(nolock)
                    WHERE AO_QST_TH.NO_AO = AO_ENTET.NO_AO
                        AND AO_QST_TH.ID_THEME != 'APRIX'
                ) as countTheme,
                (
                    SELECT MAX(NO_DCC)
                    FROM DCC_LIEN with(nolock)
                    WHERE TYPEOBJ = 'AO_ENTET'
                        AND COD_ENR = RIGHT('000000000'+cast(AO_ENTET.NO_AO as varchar), 9)
                ) as DossierConsoli_NO_DCC
            FROM AOEXCENV with(nolock)
            JOIN AO_ENTET with(nolock) ON AO_ENTET.NO_AO = AOEXCENV.NOAO
            JOIN SOCIETE with(nolock) ON SOCIETE.CODE_STE = AO_ENTET.CODE_STE
            JOIN DEVISES as DeviseSoc with(nolock) ON DeviseSoc.CODE_DEVI = SOCIETE.DEVISE
            LEFT JOIN FOURNISS with(nolock) ON FOURNISS.CODE = AOEXCENV.CODE_FOU
            LEFT JOIN GRPFOU with(nolock) ON GRPFOU.CODE = AOEXCENV.CODE_FOU
            JOIN (
                SELECT LIB01DICO as TYPE_FG, CODEDICO
                FROM DILISPEC with(nolock)
                WHERE DILISPEC.TYPEDICO = 'AOEXENV921'
            ) as AoExcEnvCompl 
                ON AoExcEnvCompl.CODEDICO = RIGHT('000000000'+cast(AOEXCENV.NOIDENT as varchar), 9)
            LEFT JOIN AOEXCENTE 
                ON AOEXCENTE.CODEFORM = AOEXCENV.FORMULAIR 
                AND AOEXCENTE.VERSFORM = AOEXCENV.VERSI
            WHERE AOEXCENV.NOIDENT = :importId",
            [':importId' => $importId]
        );
    }

    /**
     * @return array<array<mixed>>
     */
    public function getForm(string $form, int $version) :array
    {
        // TODO : cache
        return $this->fetchAssocAll(
            "SELECT 
                AOEXCLIGN.*,
                AOEXCENTE.NBLIGAO as Entet_NBLIGAO,
                AOEXCDICC.ENTET as Dict_ENTET
            FROM AOEXCENTE with(nolock)
            JOIN AOEXCLIGN with(nolock) 
                ON AOEXCLIGN.CODEFORM = AOEXCENTE.CODEFORM
                AND AOEXCLIGN.VERSI = AOEXCENTE.VERSFORM
                AND (AOEXCLIGN.LUE = 0x54 OR AOEXCLIGN.CODEINTZ IN ('POSTE', 'CODART', 'DESART'))
            LEFT JOIN AOEXCDICC with(nolock)
                ON AOEXCDICC.CODEINTZ = AOEXCLIGN.CODEINTZ
            WHERE AOEXCENTE.CODEFORM = :form
                AND AOEXCENTE.VERSFORM = :version",
            [
                'form' => $form,
                'version' => $version,
            ]
        );
    }

    /**
     * @param array<mixed> $importInfo
     * @param array<mixed> $formDataEntet
     * @param array<array<mixed>> $formDataListLigne
     */
    public function insertAoReponse(
        array $importInfo,
        array $formDataEntet,
        array $formDataListLigne,
        bool $isVariante = false,
        bool $ignoreBdd99 = true
    ) :void {

        $idAO = $importInfo['NOAO'];
        $repType = ($isVariante ? 'V' : 'C');
        $qbAoDaRep = new AoDaRep($this->pdo);
        $qbAoRep = new AoRep($this->pdo);

        // Case : too many response -> Erase previous
        if (intval($importInfo['countResponse']) > 3) {
            // Get most recent rep
            $deletedId = $qbAoDaRep->find(
                [
                    'NO_AO' => $idAO,
                    'CODE_FOU' => $importInfo['CODE_FOU'],
                    'TYPE_FG' => $importInfo['FournisseurType'],
                    'C_V' => $repType,
                ],
                ['NO_AO', 'DTE_REP', 'CODE_FOU', 'TYPE_FG', 'C_V'],
                ['tri_DTE_REP' => 'DESC']
            );
            $qbAoDaRep->delete($deletedId);
            $qbAoRep->delete($deletedId);
        }

        // Delete duplicate response
        $repId = [
            'NO_AO' => $idAO,
            'DTE_REP' => $formDataEntet['DATREP'],
            'CODE_FOU' => $importInfo['CODE_FOU'],
            'TYPE_FG' => $importInfo['FournisseurType'],
            'C_V' => $repType,
        ];
        $qbAoDaRep->delete($repId);
        $qbAoRep->delete($repId);

        $typeDelaiLivr = $importInfo['Entet_TYPEDEL'];
        $deviseConv = new ConvertionDeviseService(
            $this->pdo,
            $importInfo['Entet_CODE_STE']
        );

        $qbAoDaRep->insert([
            'NO_AO' => $idAO,
            'CODE_FOU' => $importInfo['CODE_FOU'],
            'DTE_REP' => $formDataEntet['DATREP'],
            'REFRPFOU' => $formDataEntet['REFREPFOU'],
            'DEVISE' => $formDataEntet['DEVREP'],
            'DTELIMIT' => $formDataEntet['DATLIMVAL'],
            'COMMENT1' => Sql::updateMemo($this->pdo, '', $formDataEntet['COMFOU']),
            'C_V' => $repType,
            'TYPE_FG' => $importInfo['FournisseurType'],
            'VAL_DEVIS' => $deviseConv->getTauxConvDevPivotToDevDest(
                $formDataEntet['DEVREP']
            ),
            'VAL_DEVSTE' => $deviseConv->getTauxConvDevSrcToDevDest(
                $formDataEntet['DEVREP'],
                $importInfo['Societe_DEVISE']
            ),
        ]);

        $listEntity = [];
        foreach ($formDataListLigne as $ligne) {
            $listEntity[] = [
                'NO_AO' => $idAO,
                'NO_PO_AO' => $ligne['AoLigne']['NO_PO'],
                'CODE_FOU' => $importInfo['CODE_FOU'],
                'DTE_REP' => $formDataEntet['DATREP'],
                'QTE' => $ligne['QTE'] ?? $ligne['AoLigne']['QTE'],
                'UNITE' => $ligne['AoLigne']['UNITE'],
                'PU' => $ligne['TARIF'],
                'DELAI' =>
                    $typeDelaiLivr == 'D' ?
                        $ligne['DELAILIVR'] : '00000000',
                'CODE_STE' => $importInfo['Entet_CODE_STE'],
                'DESCOMPL' => Sql::updateMemo($this->pdo, '', $ligne['DESARTCOMP']),
                'QTECOND' => $ligne['QTECOND'],
                'C_V' => $repType,
                'COMFOURN' => $ligne['COMREPFO'] ?? '',
                'CODE_ART' => $ligne['AoLigne']['CODE_ART'],
                'DES_ART' => $ligne['AoLigne']['DES'],
                'DELAIJ' =>
                    $typeDelaiLivr == 'J' ?
                        $ligne['DELAILIVR'] : '0',
                'DELAIT' =>
                    $typeDelaiLivr == 'T' ?
                        $ligne['DELAILIVR'] : '',
                'TYPE_FG' => $importInfo['FournisseurType'],
                'REF_FOUR' => $ligne['REFARTFOU'] ?? '',
                'DES_FOUR' => $ligne['DESARTFOU'] ?? '',
                'COD_FAB' => $ligne['CODEFABR'] ?? '',
                'COD_GAM' => $ligne['CODEGAM'] ?? '',
                'REF_FAB' => $ligne['REFFABR'] ?? '',
                'PAY_ORI' => $ligne['PAYSORI'] ?? '',
                'PAY_PROV' => $ligne['PAYSPROV'] ?? '',
            ];
        }
        $this->multiInsert('AO_REP', $listEntity);

        if (! $ignoreBdd99) {
            $qbBdd = new Bdd($this->pdo);
            $mapBdd99 = array_column(
                $qbBdd->findAll([
                    'CODE_FOU' => $importInfo['CODE_FOU'],
                    'NO_PAGE' => 99,
                ]),
                null,
                'CODE_ART'
            );

            $listEntity = [];
            foreach ($formDataListLigne as $ligne) {
                $artId = $ligne['AoLigne']['CODE_ART'];
                if (empty($artId)) {
                    continue;
                }

                $bddart = $mapBdd99[ $artId ] ?? [
                    'CODE_ART' => $artId,
                    'CODE_FOU' => $importInfo['CODE_FOU'],
                    'NO_PAGE' => 99,
                    'NM_TEXTE' => 0,
                ];
                $id = Sql::updateMemo(
                    $this->pdo,
                    $bddart['NM_TEXTE'],
                    $ligne['BDDART99']
                );

                if ($bddart['NM_TEXTE'] == 0) {
                    $bddart['NM_TEXTE'] = $id;
                    $qbBdd->merge($bddart);
                }
            }
        }
    }

    /**
     * @param array<mixed> $importInfo as return from getInfoImport
     * @see self::getInfoImport
     */
    public function updateDossierConsol(array $importInfo, string $dossierId) :void
    {
        $this->pdo->beginTransaction();


        $listDccArt = $this->fetchAssocAll(
            "SELECT 
                DCC_ARTICLES.*,
                CASE WHEN EXISTS(
                    SELECT AO_REP.NO_AO 
                    FROM AO_REP with(nolock) 
                    WHERE AO_REP.CODE_ART = DCC_ARTICLES.CODE_ART
                        AND AO_REP.PU > 0
                ) THEN 1 ELSE 0 
                END as hasTarif,
            FROM DCC_ARTICLES with(nolock)
            WHERE DCC_ARTICLES.NO_DCC = :dossierId",
            ['dossierId' => $dossierId]
        );

        $qbDccArt = new DccArticles($this->pdo);
        $listDccArt = $qbDccArt->findAll(
            ['NO_DCC' => $dossierId],
            ['NO_DCC', 'CODE_ART','CODE_FAB', 'REF_FAB', 'DESIGN', 'STATUS'],
        );
        
        $listUpdated = [];
        foreach ($listDccArt as &$dccArt) {
            if (empty($dccArt['CODE_ART'])) {
                continue;
            }

            //  Si DCC_ARTICLES.STATUS=’3’ et qu’aucun tarif n’est trouvé pour l’article dans la réponse,
            // STATUS prend la valeur ‘2’.
            if (intval($dccArt['STATUS']) == 3 && ! $dccArt['hasTarif']) {
                $listUpdated[] = ['STATUS' => 2] + $dccArt;
                continue;
            }

            //  Si DCC_ARTICLES.STATUS<’3’ et qu’un tarif est trouvé dans la réponse pour l’article (recherche via le code article interne),
            // alors STATUS prend la valeur ‘3’.
            if (intval($dccArt['STATUS']) < 3 && $dccArt['hasTarif']) {
                $listUpdated[] = ['STATUS' => 3] + $dccArt;
            }
        }
        
        foreach ($listUpdated as $entity) {
            $qbDccArt->updateEntity($entity);
        }
        
        $this->pdo->commit();
    }
}
