<?php

namespace Modules\Reception\Repository;

use Entity\Model\BrRecep;
use Helpers\QueryBuilder\AbsRepository;

class WorkflowBonRecepRepo extends AbsRepository
{

    /**
     * @return array<string,string>
     * @see \Modules\Commande\Repository\WorkflowCommandeRepo::getWorkflowState
     */
    public function getWorkflowState(string $bonRecepId) :array
    {
        return $this->fetchAssocOne(
            'SELECT 
                BR_RECEP.NUM_BR,
                CA_ENTET.NO_CDE as Commande_NO_CDE,
                FOURNISS.RS as Fournisseur_RS,
                FOURNISS.CODE as Fournisseur_CODE,

                CA_ENTET.DTE_VALID as Commande_DTE_VALID,
                CA_ENTET.REFUSEE as Commande_REFUSEE,
                CA_ENTET.SIGNEE as Commande_SIGNEE,
                CA_ENTET.ETAT_CDE as Commande_ETAT_CDE,
                CA_ENTET.CDE_EXP as Commande_CDE_EXP,
                CASE WHEN EXISTS(
                    SELECT CA_LIGNE.NO_DA 
                    FROM CA_LIGNE with(nolock) 
                    JOIN BR_LIGNE with(nolock) 
                        ON BR_LIGNE.NUM_CDE = CA_LIGNE.NO_CDE
                        AND BR_LIGNE.NUM_BR = BR_RECEP.NUM_BR
                    WHERE 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_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 BR_RECEP with (nolock)
            JOIN CA_ENTET with (nolock) ON CA_ENTET.NO_CDE = BR_RECEP.NUM_CDE
            JOIN FOURNISS with (nolock) ON FOURNISS.CODE = CA_ENTET.CODE_FOU
            WHERE BR_RECEP.NUM_BR = :bonRecepId',
            ['bonRecepId' => $bonRecepId]
        );
    }

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

    /**
     * @return array<array<mixed>>
     */
    public function getBonRecepWithListEvent(string $bonRecepId) :array
    {
        $bonRecep = (new BrRecep($this->pdo))->find($bonRecepId);
        $bonRecep['listEvent'] = $this->listBonRecepEvent([$bonRecepId]);
            
        return $bonRecep;
    }


    /**
     * @param array<string> $listBonRecepId
     * @return array<array<mixed>>
     */
    public function listBonRecepEvent(array $listBonRecepId) :array
    {
        if (empty($listBonRecepId)) {
            return [];
        }
        $param = [];
        return $this->fetchAssocAll(
            'SELECT 
                BR_EVENT.*, 
                BR_EVENT.DATE_CRE as DTE, 
                BR_EVENT.HEURE_CRE as HEURE, 
                Auteur.NOM as Auteur_NOM,
                BR_EVENT.COMMENT as Comment_CONTMEMO, '
                . $this->selectTrad('SUIBRDAT.LIBELLE', 'LIBELLE')
            .' FROM BR_EVENT WITH (NOLOCK) 
            LEFT JOIN SUIBRDAT WITH (NOLOCK) 
                ON BR_EVENT.CODE_EVE = SUIBRDAT.CODE_SBD '
            . $this->joinTrad('SUIBRDAT.LIBELLE', $param)
            .' LEFT JOIN HASTUTIL as Auteur with(nolock) 
                ON Auteur.CODE = BR_EVENT.UTIL
            WHERE '.$this->makeWhereIn('BR_EVENT.NUM_BR', $listBonRecepId, $param)
            .' ORDER BY BR_EVENT.NUM_ORD DESC ',
            $param
        );
    }
}
