<?php

namespace Modules\Commande\Repository;

use Entity\Model\BrRecep;
use Entity\Model\CaEntet;
use Entity\Model\Fourniss;
use Entity\Model\Hastutil;
use Helpers\Outil\ArrayHelper;
use Helpers\Outil\Sql;
use Helpers\QueryBuilder\AbsRepository;
use Modules\Reception\Repository\WorkflowBonRecepRepo;
use Modules\DemandeAchat\Repository\WorkflowDemandeAchatRepo;
use Modules\Facture\Outil\ImageFacture;
use Modules\Facture\Repository\WorkflowBapRepo;

class WorkflowCommandeRepo extends AbsRepository
{

    /**
     * @return array<string,string>
     */
    public function getWorkflowState(string $commandeId) :array
    {
        return $this->fetchAssocOne(
            'SELECT 
                CA_ENTET.NO_CDE,
                CA_ENTET.DTE_VALID,
                CA_ENTET.ETAT_CDE,
                CA_ENTET.ETAT_FIN,
                CA_ENTET.REFUSEE,
                CA_ENTET.SIGNEE,
                CA_ENTET.CDE_EXP,
                FOURNISS.RS as Fournisseur_RS,
                FOURNISS.CODE as Fournisseur_CODE,
                CASE WHEN EXISTS(
                    SELECT CA_LIGNE.NO_DA 
                    FROM CA_LIGNE with(nolock) 
                    WHERE CA_LIGNE.NO_CDE = CA_ENTET.NO_CDE
                        AND CA_LIGNE.NO_DA != 0
                ) THEN 1 ELSE 0 
                END as hasDemAchat,
                '.$this->sqlDaExists('DA_ENTET.VALIDITE = 0x46').' as hasDemAchatInvalid,
                '.$this->sqlDaExists('DA_ENTET.CHIF_EXEC = \'E\'').' as hasDemAchatExec,
                '.$this->sqlDaExists('DA_ENTET.REFUSEE = 0x54').' as hasDemAchatRefus,
                '.$this->sqlDaExists('DA_ENTET.SIGNEE = 0x46').' as hasDemAchatUnsigned,
                CASE WHEN EXISTS(
                    SELECT 1 as toto FROM FAC_LIG with (nolock)
                    WHERE FAC_LIG.NUM_CDE = CA_ENTET.NO_CDE

                    UNION

                    SELECT 1 as toto FROM FAC_LIG with (nolock)
                    JOIN DETCOLIS with (nolock) ON DETCOLIS.NUM_EXP = FAC_LIG.NUM_EXP
                    WHERE DETCOLIS.NO_CDE = CA_ENTET.NO_CDE
                ) THEN 1 ELSE 0 
                END as hasFacture,
                CASE WHEN EXISTS(
                    SELECT DETCOLIS.*
                    FROM DETCOLIS with(nolock)
                    JOIN EXPENTET with(nolock) 
                        ON EXPENTET.NUM_EXP = DETCOLIS.NUM_EXP
                        AND EXPENTET.ETATEXP != \'S\'
                    WHERE DETCOLIS.NO_CDE = CA_ENTET.NO_CDE
                ) THEN 1 ELSE 0 
                END as hasExpNonSold,
                CASE WHEN EXISTS(
                        SELECT 1 as toto FROM FAC_LIG with(nolock)
                        WHERE FAC_LIG.NUM_CDE = CA_ENTET.NO_CDE
                    UNION
                        SELECT 1 as toto FROM DETCOLIS with(nolock)
                        WHERE DETCOLIS.NO_CDE = CA_ENTET.NO_CDE
                ) THEN 1 ELSE 0 
                END as hasBap,
                CASE WHEN EXISTS(
                    SELECT *
                    FROM (
                        SELECT FAC_LIG.NUM_FACT FROM FAC_LIG with(nolock)
                        WHERE FAC_LIG.NUM_CDE = CA_ENTET.NO_CDE

                        UNION

                        SELECT FAC_LIG.NUM_FACT FROM DETCOLIS with(nolock)
                        JOIN FAC_LIG with(nolock) ON FAC_LIG.NUM_EXP = DETCOLIS.NUM_EXP 
                        WHERE DETCOLIS.NO_CDE = CA_ENTET.NO_CDE
                    ) as toto
                    JOIN FAC_ENT with(nolock) 
                        ON FAC_ENT.NUM_FACT = toto.NUM_FACT
                        AND (
                            FAC_ENT.BON_A_PAY = 0x46
                            OR FAC_ENT.DATE_BAP = \'00000000\'
                        )
                ) THEN 1 ELSE 0 
                END as hasBapNonValide,
                CASE WHEN EXISTS(
                    SELECT NUM_BR FROM BR_RECEP with(nolock)
                    WHERE BR_RECEP.NUM_CDE = CA_ENTET.NO_CDE
                ) THEN 1 ELSE 0 
                END as hasBonRecep,
                CASE WHEN EXISTS(
                    SELECT NUM_FACT
                    FROM FAC_LIG with(nolock)
                    join CA_LIGNE with(nolock) 
                        on CA_LIGNE.CODE_STE = FAC_LIG.CODE_STE
                        AND CA_LIGNE.NO_CDE = FAC_LIG.NUM_CDE
                        AND CA_LIGNE.NO_PO = FAC_LIG.NUM_LICDE
                        AND CA_LIGNE.SAISIEREC = 0x46
                    WHERE FAC_LIG.NUM_CDE = CA_ENTET.NO_CDE
                ) THEN 1 ELSE 0 
                END as hasFacLigneNonRecept,
                CASE WHEN EXISTS(
                    select DETCOLIS.NUM_EXP
                    from DETCOLIS with(nolock)
                    where DETCOLIS.NO_CDE = CA_ENTET.NO_CDE
                ) THEN 1 ELSE 0 
                END as hasExp,
                CASE WHEN EXISTS(
                    select DETCOLIS.NUM_EXP
                    FROM DETCOLIS with(nolock)
                    JOIN EXPENTET with(nolock) 
                        ON EXPENTET.NUM_EXP = DETCOLIS.NUM_EXP
                        AND EXPENTET.ETATEXP not in (\'A\', \'S\')
                    WHERE DETCOLIS.NO_CDE = CA_ENTET.NO_CDE
                ) THEN 1 ELSE 0 
                END as hasExpNonArrive,
                CASE WHEN EXISTS(
                    SELECT 
                        CA_EVENT.CODE
                    FROM CA_EVENT with(nolock)
                    WHERE CA_EVENT.NO_CDE = CA_ENTET.NO_CDE
                        AND CA_EVENT.CODE = \'IMA\'
                ) THEN 1 ELSE 0 
                END as hasEventIMA,
                CASE WHEN EXISTS(
                    SELECT 
                        CA_EVENT.CODE
                    FROM CA_EVENT with(nolock)
                    WHERE CA_EVENT.NO_CDE = CA_ENTET.NO_CDE
                        AND CA_EVENT.CODE IN (\'IMP\',\'MEL\',\'FAX\',\'EDI\',\'IMA\')
                ) THEN 1 ELSE 0 
                END as hasEventEnvoi

                
            FROM CA_ENTET with (nolock)
            JOIN FOURNISS with (nolock) ON FOURNISS.CODE = CA_ENTET.CODE_FOU
            WHERE CA_ENTET.NO_CDE = :commandeId',
            ['commandeId' => $commandeId]
        );
    }

    private function sqlDaExists(string $cond) :string
    {
        return 'CASE WHEN EXISTS(
            SELECT CA_LIGNE.NO_DA 
            FROM CA_LIGNE with(nolock)
            JOIN DA_ENTET with(nolock)
                ON DA_ENTET.NO_DA = CA_LIGNE.NO_DA
                AND ' . $cond
            . 'WHERE CA_LIGNE.NO_CDE = CA_ENTET.NO_CDE
        ) THEN 1 ELSE 0 
        END';
    }


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

        $listDemAchat =
            (new Commande($this->pdo))
                ->getAssociatedDemAchat($commandeId);
        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 listAssociatedDemAchatApproWithListEvent(string $commandeId) :array
    {

        $listDemAchat =
            (new Commande($this->pdo))
                ->getAssociatedDemAchat($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>
     */
    public function getCommandeWithListEvent(string $commandeId) :array
    {
        $commande = (new CaEntet($this->pdo))->find($commandeId);
        $commande['Fournisseur'] = (new Fourniss($this->pdo))->find($commande['CODE_FOU']);
        $commande['Acheteur'] = (new Hastutil($this->pdo))->find($commande['ACH']);
        $commande['listEvent'] = $this->listCommandeEvent([$commandeId], 'not_signature');
        return $commande;
    }

    /**
     * @return array<mixed>
     */
    public function getCommandeEnvoiWithListEvent(string $commandeId) :array
    {
        $commande = (new CaEntet($this->pdo))->find($commandeId);
        $commande['Fournisseur'] = (new Fourniss($this->pdo))->find($commande['CODE_FOU']);
        $commande['Acheteur'] = (new Hastutil($this->pdo))->find($commande['ACH']);
        $commande['listEvent'] = $this->listCommandeEvent([$commandeId], 'envoi');
        return $commande;
    }

    /**
     * @return array<mixed>
     */
    public function getCommandeApproWithListEvent(string $commandeId) :array
    {
        $commande = (new CaEntet($this->pdo))->find($commandeId);
        $commande['Fournisseur'] = (new Fourniss($this->pdo))->find($commande['CODE_FOU']);
        $commande['Acheteur'] = (new Hastutil($this->pdo))->find($commande['ACH']);
        $commande['listEvent'] = $this->listCommandeEvent([$commandeId], 'signature');
        $commande['listSignat'] = $this->listCommandeSignat([$commandeId]);
        return $commande;
    }

    /**
     * @param array<string> $listCommandeId
     * @return array<array<mixed>>
     */
    public function listCommandeSignat(array $listCommandeId) :array
    {
        if (empty($listCommandeId)) {
            return [];
        }
        $param = [];
        return $this->fetchAssocAll(
            'SELECT 
                SIG_CDE.NO_CDE,
                HASTUTIL.CODE,
                HASTUTIL.NOM,
                SIG_CDE.ORDRE,
                SIG_CDE.DTE_MISE,
                CAROLES as ROLES,
                INFORME
            FROM SIG_CDE with (nolock)
            LEFT JOIN HASTUTIL with (nolock) ON SIG_CDE.SIGNAT = HASTUTIL.CODE
            WHERE ' . $this->makeWhereIn('SIG_CDE.NO_CDE', $listCommandeId, $param) . '
            ORDER BY SIG_CDE.ORDRE',
            $param
        );
    }

    /**
     * @param array<string> $listCommandeId
     * @return array<array<string>>
     */
    public function listCommandeEvent(array $listCommandeId, string $typeEvent) :array
    {
        if (empty($listCommandeId)) {
            return [];
        }
        switch ($typeEvent) {
            // Sélection de tous les évènements non liés aux signatures
            case 'not_signature':
                $clause = " CA_EVENT.CODE NOT IN ('ESI','ESO','ESR','SCH','SIG','SPC','SOT','REF','INF','IMP','MEL','FAX','EDI','ERS')";
                break;
            
            // Sélection des évènements exclusivement liés aux signatures
            case 'signature':
                $clause = " CA_EVENT.CODE IN ('ESI','ESO','ESR','SCH','SIG','SPC','SOT','REF','INF','ERS','DAM','CAM')";
                break;
            
            // Sélection des évènements de type impression
            case 'envoi':
                $clause = " CA_EVENT.CODE IN ('IMP','MEL','FAX','EDI','IMA')";
                break;
            default:
                throw new \Exception('!!');
        }
        $param = [];
        return $this->fetchAssocAll(
            'SELECT 
                CA_EVENT.*,'
                .$this->selectTrad('SUICADAT.LIBELLE', 'LIBELLE').',
                Signataire.NOM as Signataire_NOM,
                MEMOS.CONTMEMO as Comment_CONTMEMO,
                (CASE 
                    WHEN Auteur.NOM IS NULL
                    THEN AuteurOxalink.NOM 
                    ELSE Auteur.NOM
                END) AS Auteur_NOM 	
            FROM CA_EVENT with(nolock) 
            LEFT JOIN SUICADAT with(nolock) ON CA_EVENT.CODE = SUICADAT.CODE_SCD '
            . $this->joinTrad('SUICADAT.LIBELLE', $param)
            ." LEFT JOIN HASTUTIL as Auteur with(nolock) ON Auteur.CODE = CA_EVENT.UTIL
            LEFT JOIN HASTUTIL as Signataire with(nolock) ON Signataire.CODE = CA_EVENT.SIGNAT
            LEFT JOIN MEMOS with(nolock) ON MEMOS.NODEMEMO = CA_EVENT.NM_COMME
            LEFT JOIN OXALINK_USER as AuteurOxalink with(nolock) 
                ON AuteurOxalink.USER_ID = CA_EVENT.ID_USER_OXALINK
            WHERE ".$this->makeWhereIn('CA_EVENT.NO_CDE', $listCommandeId, $param)."
                AND ".$clause."
            ORDER BY CA_EVENT.NUM_ORD DESC",
            $param
        );
    }

    /**
     * @return array<array<mixed>>
     */
    public function listBonRecepWithListEvent(string $commandeId) :array
    {
        $listBonRecep =
            (new BrRecep($this->pdo))->findAll(['NUM_CDE' => $commandeId]);
        
        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>>
     */
    public function listExpedition(string $commandeId)
    {

        return $this->fetchAssocAll(
            'SELECT distinct(EXPENTET.NUM_EXP),
                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
                AND CA_LIGNE.NO_CDE = :commandeId
                ',
            ['commandeId' => $commandeId]
        );
    }

    /**
     * @return array<array<mixed>>
     */
    public function listFactureExpWithEvent(string $commandeId) :array
    {
        $listFactureExp = $this->listFactureExpFromCommande($commandeId);
        if (empty($listFactureExp)) {
            return [];
        }

        $this->arrayJoinManyToMany(
            $listFactureExp,
            (new WorkflowBapRepo($this->pdo))
                ->listFactureEvent(array_column($listFactureExp, 'NUM_FACT')),
            'NUM_FACT',
            'listEvent'
        );
        foreach ($listFactureExp as &$facture) {
            $facture['urlImageFacture'] = ImageFacture::getUrlImageFacture($facture);
        }
        
        return $listFactureExp;
    }

    /**
     * @return array<array<mixed>>
     */
    public function listFactureExpApproWithEvent(string $commandeId) :array
    {
        $listFactureExp = $this->listFactureExpFromCommande($commandeId);
        if (empty($listFactureExp)) {
            return [];
        }
        $listFactureExpId = array_column($listFactureExp, 'NUM_FACT');

        $this->arrayJoinManyToMany(
            $listFactureExp,
            (new WorkflowBapRepo($this->pdo))
                ->listFactureEvent($listFactureExpId, true),
            'NUM_FACT',
            'listEvent'
        );
        $this->arrayJoinManyToMany(
            $listFactureExp,
            (new WorkflowBapRepo($this->pdo))
                ->listFactureSignat($listFactureExpId),
            'NUM_FACT',
            'listSignat'
        );

        foreach ($listFactureExp as &$facture) {
            $facture['urlImageFacture'] = ImageFacture::getUrlImageFacture($facture);
        }
        
        return $listFactureExp;
    }

    /**
     * @return array<array<mixed>>
     */
    private function listFactureExpFromCommande(string $commandeId) :array
    {
        return $this->fetchAssocAll(
            'SELECT distinct 
                FAC_LIG.NUM_EXP,
                FAC_ENT.*,
                FOURNISS.CODE as Fournisseur_CODE,
                FOURNISS.RS as Fournisseur_RS
            FROM (
                SELECT FAC_LIG.NUM_FACT, FAC_LIG.NUM_EXP
                FROM FAC_LIG with(nolock) 
                WHERE FAC_LIG.NUM_CDE = :commandeId_0

                UNION

                SELECT FAC_LIG.NUM_FACT, FAC_LIG.NUM_EXP
                FROM FAC_LIG with(nolock)
                JOIN EXPENTET with(nolock) ON EXPENTET.NUM_EXP = FAC_LIG.NUM_EXP
                JOIN DETCOLIS with(nolock) 
                    ON DETCOLIS.NUM_EXP = EXPENTET.NUM_EXP
                    AND DETCOLIS.NO_CDE = :commandeId_1
            ) as FAC_LIG
            JOIN FAC_ENT with(nolock) ON FAC_ENT.NUM_FACT = FAC_LIG.NUM_FACT
            LEFT JOIN FOURNISS with(nolock) ON FOURNISS.CODE = FAC_ENT.CODE_FOUR',
            [
                'commandeId_0' => $commandeId,
                'commandeId_1' => $commandeId,
            ]
        );
    }
}
