<?php

namespace Modules\Facture\Repository;

use Entity\Model\FactEnt;
use Entity\Model\Fourniss;
use Helpers\Outil\Sql;
use Helpers\QueryBuilder\AbsRepository;
use Modules\Reception\Repository\WorkflowBonRecepRepo;
use Modules\Commande\Repository\WorkflowCommandeRepo;
use Modules\DemandeAchat\Repository\WorkflowDemandeAchatRepo;
use Modules\Facture\Outil\ImageFacture;

class WorkflowBapRepo extends AbsRepository
{

    /**
     * @return array<string,string>
     */
    public function getWorkflowState(string $factureId) :array
    {
        return $this->fetchAssocOne(
            'SELECT 
                FAC_ENT.NUM_FACT,
                FAC_ENT.LITIGE,
                FAC_ENT.DATE_BAP,

                CASE WHEN EXISTS(
                    SELECT distinct CA_LIGNE.NO_DA
                    FROM CA_LIGNE with(nolock)
                    JOIN FAC_LIG with(nolock) 
                        ON FAC_LIG.NUM_CDE = CA_LIGNE.NO_CDE
                        AND FAC_LIG.NUM_FACT = FAC_ENT.NUM_FACT
                    WHERE CA_LIGNE.NO_DA != 0
                ) THEN 1 ELSE 0 
                END as hasDemAchat,
                CASE WHEN EXISTS(
                    SELECT distinct DA_ENTET.NO_DA
                    FROM DA_ENTET with(nolock)
                    JOIN CA_LIGNE with(nolock) ON CA_LIGNE.NO_DA = DA_ENTET.NO_DA
                    JOIN FAC_LIG with(nolock) 
                        ON FAC_LIG.NUM_CDE = CA_LIGNE.NO_CDE
                        AND FAC_LIG.NUM_FACT = FAC_ENT.NUM_FACT
                    WHERE DA_ENTET.VALIDITE = 0x46
                ) THEN 1 ELSE 0 
                END as hasDemAchatNonValide,
                CASE WHEN EXISTS(
                    SELECT distinct DA_ENTET.NO_DA
                    FROM DA_ENTET with(nolock)
                    JOIN CA_LIGNE with(nolock) ON CA_LIGNE.NO_DA = DA_ENTET.NO_DA
                    JOIN FAC_LIG with(nolock) 
                        ON FAC_LIG.NUM_CDE = CA_LIGNE.NO_CDE
                        AND FAC_LIG.NUM_FACT = FAC_ENT.NUM_FACT
                    WHERE DA_ENTET.REFUSEE = 0x54
                ) THEN 1 ELSE 0 
                END as hasDemAchatRefuse,
                CASE WHEN EXISTS(
                    SELECT distinct DA_ENTET.NO_DA
                    FROM DA_ENTET with(nolock)
                    JOIN CA_LIGNE with(nolock) ON CA_LIGNE.NO_DA = DA_ENTET.NO_DA
                    JOIN FAC_LIG with(nolock) 
                        ON FAC_LIG.NUM_CDE = CA_LIGNE.NO_CDE
                        AND FAC_LIG.NUM_FACT = FAC_ENT.NUM_FACT
                    WHERE DA_ENTET.SIGNEE = 0x46
                ) THEN 1 ELSE 0 
                END as hasDemAchatNonSigne,
                CASE WHEN EXISTS(
                    SELECT distinct DA_ENTET.NO_DA
                    FROM DA_ENTET with(nolock)
                    JOIN CA_LIGNE with(nolock) ON CA_LIGNE.NO_DA = DA_ENTET.NO_DA
                    JOIN FAC_LIG with(nolock) 
                        ON FAC_LIG.NUM_CDE = CA_LIGNE.NO_CDE
                        AND FAC_LIG.NUM_FACT = FAC_ENT.NUM_FACT
                    WHERE DA_ENTET.CHIF_EXEC = \'E\'
                ) THEN 1 ELSE 0 
                END as hasDemAchatExec,
                CASE WHEN EXISTS(
                    SELECT distinct CA_LIGNE.NO_CDE
                    FROM CA_LIGNE with(nolock)
                    JOIN FAC_LIG with(nolock) 
                        ON FAC_LIG.NUM_CDE = CA_LIGNE.NO_CDE
                        AND FAC_LIG.NUM_FACT = FAC_ENT.NUM_FACT
                ) THEN 1 ELSE 0 
                END as hasCommande,
                CASE WHEN EXISTS(
                    SELECT distinct CA_ENTET.NO_CDE
                    FROM CA_ENTET with(nolock)
                    JOIN CA_LIGNE with(nolock) ON CA_LIGNE.NO_CDE = CA_ENTET.NO_CDE
                    JOIN FAC_LIG with(nolock) 
                        ON FAC_LIG.NUM_CDE = CA_LIGNE.NO_CDE
                        AND FAC_LIG.NUM_FACT = FAC_ENT.NUM_FACT
                    WHERE CA_ENTET.DTE_VALID = \'00000000\'
                ) THEN 1 ELSE 0 
                END as hasCommandeNonValide,
                CASE WHEN EXISTS(
                    SELECT distinct CA_ENTET.NO_CDE
                    FROM CA_ENTET with(nolock)
                    JOIN CA_LIGNE with(nolock) ON CA_LIGNE.NO_CDE = CA_ENTET.NO_CDE
                    JOIN FAC_LIG with(nolock) 
                        ON FAC_LIG.NUM_CDE = CA_LIGNE.NO_CDE
                        AND FAC_LIG.NUM_FACT = FAC_ENT.NUM_FACT
                    WHERE CA_ENTET.REFUSEE = 0x54
                ) THEN 1 ELSE 0 
                END as hasCommandeRefuse,
                CASE WHEN EXISTS(
                    SELECT distinct CA_ENTET.NO_CDE
                    FROM CA_ENTET with(nolock)
                    JOIN CA_LIGNE with(nolock) ON CA_LIGNE.NO_CDE = CA_ENTET.NO_CDE
                    JOIN FAC_LIG with(nolock) 
                        ON FAC_LIG.NUM_CDE = CA_LIGNE.NO_CDE
                        AND FAC_LIG.NUM_FACT = FAC_ENT.NUM_FACT
                    WHERE CA_ENTET.ETAT_CDE != \'S\'
                ) THEN 1 ELSE 0 
                END as hasCommandeNonSolde,
                CASE WHEN EXISTS(
                    SELECT distinct CA_ENTET.NO_CDE
                    FROM CA_ENTET with(nolock)
                    JOIN CA_LIGNE with(nolock) ON CA_LIGNE.NO_CDE = CA_ENTET.NO_CDE
                    JOIN FAC_LIG with(nolock) 
                        ON FAC_LIG.NUM_CDE = CA_LIGNE.NO_CDE
                        AND FAC_LIG.NUM_FACT = FAC_ENT.NUM_FACT
                    WHERE CA_ENTET.ETAT_CDE IN (\'E\', \'S\')
                ) THEN 1 ELSE 0 
                END as hasCommandeEnCour,

                CASE WHEN EXISTS(
                    SELECT distinct BR_RECEP.NUM_CDE
                    FROM BR_RECEP with(nolock)
                    JOIN CA_LIGNE with(nolock) ON CA_LIGNE.NO_CDE = BR_RECEP.NUM_CDE
                    JOIN FAC_LIG with(nolock) 
                        ON FAC_LIG.NUM_CDE = CA_LIGNE.NO_CDE
                        AND FAC_LIG.NUM_FACT = FAC_ENT.NUM_FACT
                ) THEN 1 ELSE 0 
                END as hasBonRecep,
                CASE WHEN EXISTS(
                    SELECT distinct CA_ENTET.NO_CDE
                    FROM CA_ENTET with(nolock)
                    JOIN CA_LIGNE with(nolock) ON CA_LIGNE.NO_CDE = CA_ENTET.NO_CDE
                    JOIN FAC_LIG with(nolock) 
                        ON FAC_LIG.NUM_CDE = CA_LIGNE.NO_CDE
                        AND FAC_LIG.NUM_FACT = FAC_ENT.NUM_FACT
                    WHERE CA_ENTET.CDE_EXP = 0x54
                ) THEN 1 ELSE 0 
                END as hasCommandeExp,
                CASE WHEN EXISTS(
                    SELECT distinct DETCOLIS.NUM_EXP
                    FROM DETCOLIS with(nolock)
                    JOIN CA_LIGNE with(nolock) ON CA_LIGNE.NO_CDE = DETCOLIS.NO_CDE
                    JOIN FAC_LIG with(nolock) 
                        ON FAC_LIG.NUM_CDE = CA_LIGNE.NO_CDE
                        AND FAC_LIG.NUM_FACT = FAC_ENT.NUM_FACT
                ) THEN 1 ELSE 0 
                END as hasExp,
                CASE WHEN EXISTS(
                    SELECT distinct EXPENTET.NUM_EXP
                    FROM EXPENTET with(nolock)
                    JOIN DETCOLIS with(nolock) ON DETCOLIS.NUM_EXP = EXPENTET.NUM_EXP
                    JOIN CA_LIGNE with(nolock) ON CA_LIGNE.NO_CDE = DETCOLIS.NO_CDE
                    JOIN FAC_LIG with(nolock) 
                        ON FAC_LIG.NUM_CDE = CA_LIGNE.NO_CDE
                        AND FAC_LIG.NUM_FACT = FAC_ENT.NUM_FACT
                    WHERE EXPENTET.ETATEXP NOT IN (\'A\',\'S\')
                ) THEN 1 ELSE 0 
                END as hasExpNonArrive
            FROM FAC_ENT with (nolock)
            WHERE FAC_ENT.NUM_FACT = :factureId',
            ['factureId' => $factureId]
        );
    }

    /**
     * Load:
     * - DemAchat.*
     * - DemAchat.listEvent[].*
     * @return array<array<string>>
     */
    public function listDemAchatWithListEvent(string $factureId) :array
    {
        $listDemAchat = $this->listAssociatedDemAchat($factureId);
        if (empty($listDemAchat)) {
            return [];
        }

        $this->arrayJoinManyToMany(
            $listDemAchat,
            (new WorkflowDemandeAchatRepo($this->pdo))
                ->listDemAchatEvent(
                    array_column($listDemAchat, 'NO_DA'),
                    false
                ),
            'NO_DA',
            'listEvent'
        );

        return $listDemAchat;
    }

    /**
     * Load:
     * - DemAchat.*
     * - DemAchat.listEvent[].*
     * @return array<array<string>>
     */
    public function listDemAchatApproWithListEvent(string $commandeId) :array
    {

        $listDemAchat = $this->listAssociatedDemAchat($commandeId);
        if (empty($listDemAchat)) {
            return [];
        }
        $listDemAchatId = array_column($listDemAchat, 'NO_DA');
        
        //___________________

        $this->arrayJoinManyToMany(
            $listDemAchat,
            (new WorkflowDemandeAchatRepo($this->pdo))
                ->listDemAchatEvent($listDemAchatId, true),
            'NO_DA',
            'listEvent'
        );

        //___________________

        $this->arrayJoinManyToMany(
            $listDemAchat,
            (new WorkflowDemandeAchatRepo($this->pdo))
                ->listSignat($listDemAchatId),
            'NO_DA',
            'listSignat'
        );

        return $listDemAchat;
    }

    /**
     * @return array<mixed>
     */
    private function listAssociatedDemAchat(string $factureId) :array
    {
        return $this->fetchAssocAll(
            'SELECT DISTINCT DA_ENTET.*, 
                Demandeur.NOM as Demandeur_NOM
            FROM DA_ENTET with(nolock)
            JOIN CA_LIGNE with(nolock) ON CA_LIGNE.NO_DA = DA_ENTET.NO_DA
            JOIN FAC_LIG with(nolock) 
                ON FAC_LIG.NUM_CDE = CA_LIGNE.NO_CDE
                AND FAC_LIG.NUM_FACT = :factureId
            LEFT JOIN HASTUTIL as Demandeur with(nolock) 
                ON Demandeur.CODE = DA_ENTET.NO_DEM',
            ['factureId' => $factureId]
        );
    }

    /**
     * @return array<mixed>
     */
    public function listCommandeWithListEvent(string $demandeId) :array
    {
        $listCommande = $this->listCommandeAssociated($demandeId);

        if (empty($listCommande)) {
            return [];
        }

        // Load commande.listEvent[]
        $this->arrayJoinManyToMany(
            $listCommande,
            (new WorkflowCommandeRepo($this->pdo))
                ->listCommandeEvent(
                    array_column($listCommande, 'NO_CDE'),
                    'not_signature'
                ),
            'NO_CDE',
            'listEvent'
        );
        
        return $listCommande;
    }

    /**
     * @return array<mixed>
     */
    public function listCommandeApproWithListEvent(string $demandeId) :array
    {
        $listCommande = $this->listCommandeAssociated($demandeId);
        if (empty($listCommande)) {
            return [];
        }
        
        // Load commande.listEvent[]
        $this->arrayJoinManyToMany(
            $listCommande,
            (new WorkflowCommandeRepo($this->pdo))
                ->listCommandeEvent(
                    array_column($listCommande, 'NO_CDE'),
                    'not_signature'
                ),
            'NO_CDE',
            'listEvent'
        );

        // Load commande.listSignat[]
        $this->arrayJoinManyToMany(
            $listCommande,
            (new WorkflowCommandeRepo($this->pdo))
                ->listCommandeSignat(array_column($listCommande, 'NO_CDE')),
            'NO_CDE',
            'listSignat'
        );
            
        return $listCommande;
    }

    /**
     * @return array<mixed>
     */
    public function listCommandeEnvoiWithListEvent(string $demandeId) :array
    {
        $listCommande = $this->listCommandeAssociated($demandeId);
        if (empty($listCommande)) {
            return [];
        }

        // Load commande.listEvent[]
        $this->arrayJoinManyToMany(
            $listCommande,
            (new WorkflowCommandeRepo($this->pdo))
                ->listCommandeEvent(
                    array_column($listCommande, 'NO_CDE'),
                    'envoi'
                ),
            'NO_CDE',
            'listEvent'
        );
        
        return $listCommande;
    }

    /**
     * Load :
     * - Commande[].Fournisseur_CODE
     * - Commande[].Fournisseur_RS
     * - Commande[].Acheteur_CODE
     * - Commande[].Acheteur_NOM
     * @return array<array<mixed>>
     */
    private function listCommandeAssociated(string $factureId) :array
    {
        return $this->fetchAssocAll(
            'SELECT DISTINCT 
                CA_ENTET.*,
                FOURNISS.CODE as Fournisseur_CODE,
                FOURNISS.RS as Fournisseur_RS,
                Acheteur.CODE as Acheteur_CODE,
                Acheteur.NOM as Acheteur_NOM

            FROM CA_ENTET with(nolock)
            JOIN CA_LIGNE with(nolock) ON CA_LIGNE.NO_CDE = CA_ENTET.NO_CDE
            JOIN FAC_LIG with(nolock) 
                ON FAC_LIG.NUM_CDE = CA_LIGNE.NO_CDE
                AND FAC_LIG.NUM_FACT = :factureId
            
            JOIN FOURNISS with(nolock) ON FOURNISS.CODE = CA_ENTET.CODE_FOU
            JOIN HASTUTIL as Acheteur with(nolock) ON Acheteur.CODE = CA_ENTET.ACH
            ORDER BY CA_ENTET.NO_CDE',
            ['factureId' => $factureId]
        );
    }

    /**
     * @return array<array<mixed>>
     */
    public function listBonRecepWithListEvent(string $demandeId) :array
    {
        $listBonRecep = $this->listBonRecepAssociated($demandeId);
        
        if (empty($listBonRecep)) {
            return [];
        }
        $this->arrayJoinManyToMany(
            $listBonRecep,
            (new WorkflowBonRecepRepo($this->pdo))
                ->listBonRecepEvent(array_column($listBonRecep, 'NUM_BR')),
            'NUM_BR',
            'listEvent'
        );
            
        return $listBonRecep;
    }

    /**
     * @return array<array<mixed>>
     */
    private function listBonRecepAssociated(string $factureId) :array
    {
        return $this->fetchAssocAll(
            'SELECT DISTINCT BR_RECEP.*
            FROM BR_RECEP with(nolock)
            JOIN CA_LIGNE with(nolock) ON CA_LIGNE.NO_CDE = BR_RECEP.NUM_CDE
            JOIN FAC_LIG with(nolock) 
                ON FAC_LIG.NUM_CDE = CA_LIGNE.NO_CDE
                AND FAC_LIG.NUM_FACT = :factureId',
            ['factureId' => $factureId]
        );
    }

    /**
     * @return array<array<mixed>>
     */
    public function listExpedition(string $factureId) :array
    {
        return $this->fetchAssocAll(
            'SELECT DISTINCT EXPENTET.* 
            FROM EXPENTET with(nolock)
            JOIN DETCOLIS with(nolock) ON DETCOLIS.NUM_EXP = EXPENTET.NUM_EXP
            JOIN CA_LIGNE with(nolock) ON CA_LIGNE.NO_CDE = DETCOLIS.NO_CDE
            JOIN FAC_LIG with(nolock) 
                ON FAC_LIG.NUM_CDE = CA_LIGNE.NO_CDE
                AND FAC_LIG.NUM_FACT = :factureId',
            ['factureId' => $factureId]
        );
    }

    /**
     * @return array<mixed>
     */
    public function getFactureWithEvent(string $factureId) :array
    {
        $facture = (new FactEnt($this->pdo))->find($factureId);
        $facture['Fournisseur'] = (new Fourniss($this->pdo))->find($facture['CODE_FOUR']);
        $facture['listEvent'] = $this->listFactureEvent([$facture['NUM_FACT']]);
        $facture['urlImageFacture'] = ImageFacture::getUrlImageFacture($facture);
        
        return $facture;
    }

    /**
     * @return array<array<mixed>>
     */
    public function getFactureApproWithEvent(string $factureId) :array
    {
        $facture = (new FactEnt($this->pdo))->find($factureId);
        $facture['Fournisseur'] = (new Fourniss($this->pdo))->find($facture['CODE_FOUR']);
        $facture['listEvent'] = $this->listFactureEvent([$facture['NUM_FACT']], true);
        $facture['listSignat'] =  $this->listFactureSignat([$facture['NUM_FACT']]);
        $facture['urlImageFacture'] = ImageFacture::getUrlImageFacture($facture);
        return $facture;
    }

    /**
     * @param array<string> $listFactureId
     * @return array<array<mixed>>
     */
    public function listFactureEvent(array $listFactureId, bool $isSignat = false)
    {
        $param = [];
        return $this->fetchAssocAll(
            'SELECT 
                FA_EVENT.NUM_FACT,
                FA_EVENT.DATE_CRE as DTE,
                FA_EVENT.HEURE_CRE as HEURE,'
                .$this->selectTrad('SUIFADAT.LIBELLE').',
                Signataire.NOM as Signataire_NOM,
                MEMOS.CONTMEMO as Comment_CONTMEMO,
                Auteur.NOM as Auteur_NOM
            FROM FA_EVENT with(nolock)
            JOIN SUIFADAT with(nolock) ON SUIFADAT.CODE_SFAC = FA_EVENT.CODE_EVE '
            . $this->joinTrad('SUIFADAT.LIBELLE', $param)
            . ' LEFT JOIN HASTUTIL as Auteur with(nolock) on Auteur.CODE = FA_EVENT.UTIL
            LEFT JOIN HASTUTIL as Signataire with(nolock) on Signataire.CODE = FA_EVENT.SIGNAT
            LEFT JOIN MEMOS with(nolock)  on FA_EVENT.NM_COMME	=	MEMOS.NODEMEMO 
            WHERE '
            . $this->makeWhereIn('FA_EVENT.NUM_FACT', $listFactureId, $param)
            . ' AND ' . ($isSignat ?
                " FA_EVENT.CODE_EVE IN ('SCH','SIB','REF','ESI','ESO','ELI','LIT','DEL','INF','ERS')" : // TODO : why ERS ?
                " FA_EVENT.CODE_EVE NOT IN ('SCH','SIB','REF','ESI','ESO','ELI','LIT','DEL','INF') "
            )
            . ' ORDER BY FA_EVENT.NUM_ORD desc ',
            $param
        );
    }

    /**
     * @param array<mixed> $listFactureId
     * @return array<array<mixed>>
     */
    public function listFactureSignat(array $listFactureId) :array
    {
        $param = [];
        return $this->fetchAssocAll(
            'SELECT 
                HASTUTIL.CODE,
                HASTUTIL.NOM,
                SIG_BAP.ORDRE,
                SIG_BAP.DTE_MISE,
                FAROLES as ROLES,
                INFORME,
                SIG_BAP.NO_FAC AS NUM_FACT
            FROM SIG_BAP with (nolock)
            LEFT JOIN HASTUTIL with (nolock) on SIG_BAP.SIGNAT = HASTUTIL.CODE
            WHERE '.$this->makeWhereIn('SIG_BAP.NO_FAC', $listFactureId, $param).'
            ORDER BY SIG_BAP.ORDRE',
            $param
        );
    }
}
