<?php

namespace Modules\Commande\Repository;

use Entity\Model\CaCritere;
use Entity\Model\CdeCrit;
use Entity\Model\CdeNot;
use Entity\Model\CdeThem;
use Entity\Model\FourCrit;
use Entity\Model\Paravrac;
use Helpers\Log\LogSysteme;
use Helpers\Outil\ArrayHelper;
use Helpers\QueryBuilder\AbsRepository;
use Helpers\Outil\Sql;

class NotationCommande extends AbsRepository
{
    /**
     * @param array<mixed> $caEntet
     */
    public function onCdeCreationInit(array $caEntet) :void
    {
        if (Sql::toBool($caEntet['Fournisseur']['NOT_MANU']) === true) {
            $this->initialize($caEntet);
            return;
        }
        $notMax = (new Paravrac($this->pdo))->getValue('NOTFOURN', 'NOTEMAXI');
        if ($notMax == 'O') {
            $this->initialize($caEntet, false);
            $this->setNoteMax($caEntet['NO_CDE']);
            $this->updateNoteAverage($caEntet['NO_CDE']);
            try {
                $this->validationCommande($caEntet['NO_CDE']);
            } catch (\Exception $e) {
                LogSysteme::prefixErrorLog($e); // Silence error
            }
            return;
        }
    }

    /**
     * @param array<mixed> $commande
     */
    public function initialize(array $commande, bool $isNoteManuel = true) :void
    {
        $bTransacActive = $this->pdo->inTransaction();
        if (!$bTransacActive) {
            $this->pdo->beginTransaction();
        }
        

        // Création entête de notation
        (new CdeNot($this->pdo))->insert([
            'NO_CDE' => $commande['NO_CDE'],
            'COD_FOUR' => $commande['CODE_FOU'],
            'DTEDEMAND' => date('Ymd'),
            'DTEVALID' => '00000000',
            'INNOTAT' => $_SESSION['Session_Login'],
            'NOTMANU' => $isNoteManuel,
            'NOTMOYEN' => 0,
            'CODSOCCD' => $commande['CODE_STE'],
        ]);

        // Création des thème de notation
        $stmt = $this->pdo->prepare("
            INSERT INTO CDE_THEM (NO_CDE, COD_THEM)  
            SELECT distinct :commande, COD_THEM 
            FROM CRITERES with(nolock)  
            WHERE DTEDEBVAL <= '".date('Ymd')."' 
                AND (
                    DTEFINVAL = '00000000' 
                    OR DTEFINVAL >= '".date('Ymd')."'
                )  
        ");
        $stmt->execute(['commande' => $commande['NO_CDE']]);

        // Création des critères de notation
        $stmt = $this->pdo->prepare("
            INSERT INTO CDE__CRIT( NO_CDE, COD_THEM, COD_CRIT )  
            SELECT DISTINCT :commande, COD_THEM, COD_CRIT 
            FROM CRITERES with(nolock)  
            WHERE DTEDEBVAL <= '".date('Ymd')."'
                AND (
                    DTEFINVAL = '00000000' 
                    OR DTEFINVAL >= '".date('Ymd')."'
                )
        ");
        $stmt->execute(['commande' => $commande['NO_CDE']]);
        

        if (!$bTransacActive) {
            $this->pdo->commit();
        }
    }

    public function setNoteMax(string $commandeId) :void
    {
        // initialisation réponse max par défaut
        // TODO : simplify query
        $stmt = $this->pdo->prepare("
            UPDATE CDE__CRIT  
            SET CDE__CRIT.NOTLIB = grill.LIBNOT 
            FROM CDE__CRIT
            LEFT JOIN (
                SELECT g.COD_THEM, g.COD_CRIT, g.LIBNOT, g.NOTE 
                FROM GRILLES g
                JOIN (
                    SELECT DISTINCT COD_THEM, COD_CRIT, MAX(NOTE) NOTE 
                    FROM GRILLES 
                    GROUP BY COD_THEM, COD_CRIT
                ) n ON n.COD_THEM = g.COD_THEM 
                    AND n.COD_CRIT = g.COD_CRIT 
                    AND n.NOTE = g.NOTE
            ) grill ON grill.COD_THEM = CDE__CRIT.COD_THEM 
                AND grill.COD_CRIT = CDE__CRIT.COD_CRIT
            WHERE CDE__CRIT.NO_CDE = :commande
        ");
        $stmt->execute(['commande' => $commandeId]);
    }
    
    /**
     * retourne la liste des notations commande
     * @param array<mixed> $post
     * @return array<mixed>
     */
    public function listeCde($post)
    {
        $arrayReq = [];
        $req = "select * from
        (
        select
            CDE_NOT.NO_CDE,
            COD_FOUR,
            RS,
            INNOTAT,
            DTEDEMAND,
            NOM,
            NOTMANU,
            DTEVALID,
            NOTMOYEN,
            OBJET_CA,
            CA_ENTET.CODE_STE,
            ETAT_CDE,
            ETAT_FIN,
            CONTMEMO
        from CDE_NOT with (nolock)
        left join FOURNISS with (nolock) on FOURNISS.CODE = CDE_NOT.COD_FOUR
        left join HASTUTIL with (nolock) on  HASTUTIL.CODE = INNOTAT
        left join CA_ENTET with (nolock) on CA_ENTET.NO_CDE = CDE_NOT.NO_CDE
        left join MEMOS with (nolock) on MEMOS.NODEMEMO = CA_ENTET.NM_TITRE 
        ) CDE";
        
        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 0);
        
        if (count($whereArray)>  0) {
            $req .= $whereArray[0].$whereArray['tri'];
            
            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }
        
        return $this->fetchAssocAll($req, $arrayReq);
    }


    /**
     * Load *:
     * - Commande.Fourniss
     * - Commande.mapNoteTheme[].Theme.listCritere[].listChoix[]
     * - Commande.listCaCritere[]
     * Filter: commande has a notation
     * @return \stdClass|null
     */
    public function getCommandeNotation(string $commandeId) :?object
    {

        $row = $this->fetchAssocOne(
            'SELECT 
                '.$this->generateFieldList([
                    'CA_ENTET' => ['NO_CDE', 'ACH', 'DEMANDEUR'],
                    'FOURNISS' => ['CODE', 'RS'],
                ]).'
            FROM CA_ENTET with(nolock)
            JOIN CDE_NOT with(nolock) ON CDE_NOT.NO_CDE = CA_ENTET.NO_CDE
            JOIN FOURNISS with(nolock) ON FOURNISS.CODE = CA_ENTET.CODE_FOU
            WHERE CA_ENTET.NO_CDE = :commandeId',
            ['commandeId' => $commandeId]
        );

        if (empty($row)) {
            return null;
        }

        $split = $this->splitRowByTable($row);

        $commande = (object) (
            $split['CA_ENTET']
            + [
                'Fourniss' => (object) $split['FOURNISS'],
                'mapNoteTheme' => $this->listFormNotationCommande($commandeId),
                'listCaCritere' => (new CaCritere($this->pdo))->findAll([
                    'NO_CDE' => $commandeId,
                ]),
            ]
        );

        // Calc Critere.mapSelectOption
        foreach ($commande->mapNoteTheme as $noteTheme) {
            foreach ($noteTheme->Theme->listCritere as $critere) {
                $critere->mapSelectOption = array_column(
                    $critere->listChoix,
                    'LIBNOT',
                    'LIBNOT'
                );
                $critere->commandeCurrentValue = ArrayHelper::findFirst(
                    $commande->listCaCritere,
                    function ($caCritere) use ($critere) {
                        return $caCritere['COD_THEM'] == $critere->COD_THEM
                            && $caCritere['COD_CRIT'] == $critere->COD_CRIT;
                    }
                )['NOTLIB'] ?? null;
            }
        }

        return $commande;
    }

    /**
     * Load mapNoteTheme[].Theme.listCritere[].listChoix[]
     * @return array<mixed>
     */
    public function listFormNotationCommande(string $commandeId) :array
    {
        $fieldList = [
            'CDE_THEM' => [
                'NO_CDE',
                'COD_THEM',
            ],
            'THEMES' => [
                'COD_THEM',
                'LIBELLE',
                'TH_ACHET',
                'TH_DEM',
                'TH_AUT',
            ],
            'CRITERES' => [
                'COD_THEM',
                'COD_CRIT',
                'LIBELLE',
            ],
            'GRILLES' => [
                'COD_THEM',
                'COD_CRIT',
                'LIBNOT',
                'NOTE',
            ],
        ];
        $listRow = $this->fetchAssocAll(
            "SELECT ".$this->generateFieldList($fieldList)."
            FROM CDE_THEM with (nolock)
            JOIN THEMES with (nolock) ON THEMES.COD_THEM = CDE_THEM.COD_THEM
            JOIN CRITERES with (nolock)
                ON CRITERES.COD_THEM = THEMES.COD_THEM
            JOIN GRILLES with (nolock) 
                ON GRILLES.COD_THEM = CRITERES.COD_THEM
                AND GRILLES.COD_CRIT = CRITERES.COD_CRIT
            WHERE CDE_THEM.NO_CDE = :commande
            ",
            [ 'commande' => $commandeId ]
        );

        $mapNoteTheme = [];
        foreach ($listRow as $row) {
            $a = $this->splitRowByTable($row);
            $noteTheme = (object) $a['CDE_THEM'];
            if (!isset($mapNoteTheme[ $noteTheme->COD_THEM ])) {
                $mapNoteTheme[ $noteTheme->COD_THEM ] = $noteTheme;
            } else {
                $noteTheme = $mapNoteTheme[ $noteTheme->COD_THEM ];
            }
            
            if (! isset($noteTheme->Theme)) {
                $noteTheme->Theme = (object) $a['THEMES'];
            }
            
            $critere = (object) $a['CRITERES'];
            if (!isset($noteTheme->Theme->listCritere[ $critere->COD_CRIT ])) {
                $noteTheme->Theme->listCritere[ $critere->COD_CRIT ] = $critere;
            } else {
                $critere = $noteTheme->Theme->listCritere[ $critere->COD_CRIT ];
            }
            
            $critere->listChoix[] = (object) $a['GRILLES'];
        }

        return $mapNoteTheme;
    }

    /**
     * Load
     * - Commande.listTheme[]
     * - Commande.Acheteur
     * - Commande.Demandeur
     * - Commande.NoteGlobal
     * - Commande.listDaDemandeur[]
     * - Commande.listSoldeurPhysique[]
     * - Commande.listSoldeurFinance[]
     * - Commande.listAuthorBonRecep[] list of User who created a BonRecp associated to given Commande
     * - Commande.listAuthorFacture[] list of User who created a Facture associated to given Commande
     * @return array<mixed>
     */
    public function getCommandeForActivation(string $commandeId) :array
    {
        // Load Commande.Acheteur & Commande.Demandeur
        $listRowCommande = $this->fetchAssocAll(
            "SELECT ".$this->generateFieldList([
                'CA_ENTET' => [
                    'NO_CDE',
                    'CODE_FOU',
                    'CODE_STE',
                    'ETAT_CDE',
                    'ETAT_FIN',
                    'OBJET_CA',
                ],
                'Acheteur' => ['CODE','NOM','EMAIL','COD_LNG'],
                'Demandeur' => ['CODE','NOM','EMAIL','COD_LNG'],
                'NoteGlobal' => ['NO_CDE'],
                'FOURNISS' => ['CODE','RS'],
            ])."
            FROM CA_ENTET with (nolock)
            LEFT JOIN HASTUTIL as Demandeur with(nolock) ON Demandeur.CODE = CA_ENTET.DEMANDEUR
            JOIN HASTUTIL as Acheteur with(nolock) ON Acheteur.CODE = CA_ENTET.ACH
            LEFT JOIN FOURNISS with(nolock) ON FOURNISS.CODE = CA_ENTET.CODE_FOU
            LEFT JOIN CDE_NOT as NoteGlobal with(nolock) ON NoteGlobal.NO_CDE = CA_ENTET.NO_CDE

            WHERE CA_ENTET.NO_CDE = :commandeId
            ",
            [ 'commandeId' => $commandeId ]
        );

        if (empty($listRowCommande)) {
            throw new \Exception('Invalid commande #'.$commandeId);
        }

        $split = $this->splitRowByTable($listRowCommande[0]);
        $commande = $split['CA_ENTET'] + [
            'Acheteur' => $split['Acheteur'],
            'Demandeur' => empty($split['Demandeur']['CODE']) ?
                null : $split['Demandeur'],
            'NoteGlobal' => empty($split['NoteGlobal']['NO_CDE']) ?
                null : $split['NoteGlobal'],
            'Fourniss' => empty($split['FOURNISS']['CODE']) ?
                null : $split['FOURNISS'],
        ];

        //___________________

        // Load Commande.listTheme
        $listRowTheme = $this->fetchAssocAll(
            "SELECT ".$this->generateFieldList([
                'THEMES' => ['COD_THEM','LIBELLE','TH_ACHET','TH_DEM','TH_AUT'],
            ])."
            FROM CA_ENTET with (nolock)
            JOIN CDE_THEM with (nolock) ON CDE_THEM.NO_CDE = CA_ENTET.NO_CDE
            JOIN THEMES with (nolock) ON THEMES.COD_THEM = CDE_THEM.COD_THEM
            WHERE CA_ENTET.NO_CDE = :commandeId
            ",
            [ 'commandeId' => $commandeId ]
        );
        
        $commande['listTheme'] = array_map(function ($row) {
            return $this->splitRowByTable($row)['THEMES'];
        }, $listRowTheme);

        //___________________


        $listHastutilField = 'HASTUTIL.CODE, HASTUTIL.NOM, HASTUTIL.EMAIL, HASTUTIL.COD_LNG';

        // Load :
        // - Commande.listDaDemandeur[]
        // - Commande.listSoldeurPhysique[]
        // - Commande.listSoldeurFinance[]
        // - Commande.listAuthorBonRecep[]
        // - Commande.listAuthorFacture[]
        $listRowUser = $this->fetchAssocAll(
            "SELECT DISTINCT 'listDaDemandeur' as listType, $listHastutilField
            FROM HASTUTIL with (nolock)
            JOIN DA_ENTET with (nolock) ON DA_ENTET.NO_DEM = HASTUTIL.CODE
            JOIN CA_LIGNE with (nolock) ON CA_LIGNE.NO_DA = DA_ENTET.NO_DA
            WHERE CA_LIGNE.NO_CDE = :commandeId0

            UNION
            
            SELECT DISTINCT 'listSoldeurPhysique' as listType, $listHastutilField
            FROM CA_ENTET with (nolock)
            JOIN CA_EVENT with (nolock) 
                ON CA_EVENT.NO_CDE = CA_ENTET.NO_CDE
                AND CA_ENTET.AVENANT = CA_EVENT.AVNT
            JOIN HASTUTIL with (nolock) ON HASTUTIL.CODE = CA_EVENT.UTIL
            WHERE CA_ENTET.NO_CDE = :commandeId1
                AND CA_EVENT.CODE IN ('SPA','SPM')

            UNION

            SELECT DISTINCT 'listSoldeurFinance' as listType, $listHastutilField
            FROM CA_ENTET with (nolock)
            JOIN CA_EVENT with (nolock) 
                ON CA_EVENT.NO_CDE = CA_ENTET.NO_CDE
                AND CA_ENTET.AVENANT = CA_EVENT.AVNT
            JOIN HASTUTIL with (nolock) ON HASTUTIL.CODE = CA_EVENT.UTIL
            WHERE CA_ENTET.NO_CDE = :commandeId2
                AND CA_EVENT.CODE IN ('SFA','SFM')

            UNION

            SELECT DISTINCT 'listAuthorBonRecep' as listType, $listHastutilField
            FROM CA_ENTET with (nolock)
            JOIN CA_EVENT with (nolock) 
                ON CA_EVENT.NO_CDE = CA_ENTET.NO_CDE
                AND CA_ENTET.AVENANT = CA_EVENT.AVNT
            JOIN HASTUTIL with (nolock) ON HASTUTIL.CODE = CA_EVENT.UTIL
            WHERE CA_ENTET.NO_CDE = :commandeId3
                AND CA_EVENT.CODE IN ('BRC')

            UNION

            SELECT DISTINCT 'listAuthorFacture' as listType, $listHastutilField
            FROM CA_ENTET with (nolock)
            JOIN FAC_ENT with (nolock) ON FAC_ENT.NUM_CDE = CA_ENTET.NO_CDE
            JOIN FA_EVENT with (nolock) ON FA_EVENT.NUM_FACT = FAC_ENT.NUM_FACT
            JOIN HASTUTIL with (nolock) ON HASTUTIL.CODE = FA_EVENT.UTIL
            WHERE CA_ENTET.NO_CDE = :commandeId4
                AND FA_EVENT.CODE_EVE IN ('CRE')
            ",
            [
                'commandeId0' => $commandeId,
                'commandeId1' => $commandeId,
                'commandeId2' => $commandeId,
                'commandeId3' => $commandeId,
                'commandeId4' => $commandeId,
            ]
        );

        $commande += ArrayHelper::indexBy($listRowUser, 'listType') + [
            'listDaDemandeur' => [],
            'listSoldeurPhysique' => [],
            'listSoldeurFinance' => [],
            'listAuthorBonRecep' => [],
            'listAuthorFacture' => [],
        ];

        return $commande;
    }

    public function updateNoteAverage(string $commandeId) :void
    {
        $stmt = $this->pdo->prepare("
            UPDATE CDE_NOT  
            SET CDE_NOT.NOTMOYEN = ref.average 
            FROM CDE_NOT
            JOIN (
                SELECT
                    answer.NO_CDE,
                    AVG(choice.NOTE) as average 
                FROM CDE__CRIT as answer
                JOIN GRILLES as choice 
                    ON choice.COD_THEM = answer.COD_THEM
                    AND choice.COD_CRIT = answer.COD_CRIT
                    AND choice.LIBNOT = answer.NOTLIB
                WHERE answer.NO_CDE = :commande
                GROUP BY answer.NO_CDE
            ) ref ON ref.NO_CDE = CDE_NOT.NO_CDE
        ");
        $stmt->execute(['commande' => $commandeId]);

        $stmt = $this->pdo->prepare("
            UPDATE CDE_THEM
            SET CDE_THEM.NOTMOYTH = ref.average
            FROM CDE_THEM
            JOIN (
                SELECT
                    answer.NO_CDE,
                    answer.COD_THEM,
                    AVG(choice.NOTE) as average 
                FROM CDE__CRIT as answer
                JOIN GRILLES as choice 
                    ON choice.COD_THEM = answer.COD_THEM
                    AND choice.COD_CRIT = answer.COD_CRIT
                    AND choice.LIBNOT = answer.NOTLIB
                WHERE answer.NO_CDE = :commande
                GROUP BY answer.NO_CDE, answer.COD_THEM
            ) ref 
                ON ref.NO_CDE = CDE_THEM.NO_CDE
                AND ref.COD_THEM = CDE_THEM.COD_THEM
        ");
        $stmt->execute(['commande' => $commandeId]);
    }

    public function validationCommande(string $commandeId) :void
    {
        $wsdl = new \Helpers\Webservice\Wsdl(
            '',
            'OXAPRC_VALIDNOTATION',
            '<NotationValideIn LngUtilisation="">
                <User>' . htmlentities($_SESSION['Session_Login']) . '</User>
                <NumCde>' . htmlentities($commandeId) . '</NumCde>
            </NotationValideIn>',
            false
        );
        $response = new \SimpleXMLElement($wsdl->responseData);
        
        if ($response->Statut!='O') {
            // Sometime Status is 'A' with no error, why ?
            //$listError = $response->ListErreur->xpath('Erreur');
            throw new \Exception('OXAPRC_VALIDNOTATION failed : '.$wsdl->responseData);
        }
    }

    public function devalidationNotation(string $commandeId) :void
    {
        // Reset notation
        (new CdeNot($this->pdo))
            ->update([
                'DTEVALID' => '00000000',
                'NOTMANU' => true,
                'NOTMOYEN' => 0,
            ], $commandeId);

        (new CdeCrit($this->pdo))
            ->update(['NOTLIB' => ''], ['NO_CDE' => $commandeId]);
        (new CdeThem($this->pdo))
            ->update(['NOTMOYTH' => 0], ['NO_CDE' => $commandeId]);

        // Update calculated value
        $this->updateFouCrit($commandeId);
    }

    private function updateFouCrit(string $commandeId) :void
    {
        $this->execute(
            'UPDATE FOU_CRIT
            SET 
                FOU_CRIT.NOMOYCRIT = COALESCE(NoteAvr.average, 0),
                FOU_CRIT.NBNOTE = COALESCE(NoteCount.count, 0)
            FROM FOU_CRIT
            JOIN CA_ENTET with(nolock) 
                ON CA_ENTET.CODE_FOU = FOU_CRIT.COD_FOU
                AND CA_ENTET.NO_CDE = :commandeId
            
            JOIN (
                SELECT 
                    CDE_NOT.COD_FOUR,
                    CDE__CRIT.COD_THEM,
                    CDE__CRIT.COD_CRIT,
                    COUNT(*) as count
                FROM CDE__CRIT
                JOIN CDE_NOT 
                    ON CDE_NOT.NO_CDE = CDE__CRIT.NO_CDE
                    AND CDE_NOT.DTEVALID != \'00000000\'
                GROUP BY CDE_NOT.COD_FOUR, CDE__CRIT.COD_THEM, CDE__CRIT.COD_CRIT
            ) as NoteCount 
                ON NoteCount.COD_FOUR = FOU_CRIT.COD_FOU
                AND NoteCount.COD_THEM = FOU_CRIT.COD_THEM 
                AND NoteCount.COD_CRIT = FOU_CRIT.COD_CRIT
            
            JOIN (
                SELECT Commande.CODE_FOU, AVG(Choice.NOTE) as average 
                FROM CDE__CRIT as Answer
                JOIN CDE_NOT 
                    ON CDE_NOT.NO_CDE = Answer.NO_CDE
                    AND CDE_NOT.DTEVALID != \'00000000\'
                JOIN GRILLES as Choice with(nolock) 
                    ON Choice.COD_THEM = Answer.COD_THEM
                    AND Choice.COD_CRIT = Answer.COD_CRIT
                    AND Choice.LIBNOT = Answer.NOTLIB
                JOIN CA_ENTET as Commande with(nolock) 
                    ON Commande.NO_CDE = Answer.NO_CDE
                GROUP BY Commande.CODE_FOU
            ) as NoteAvr ON NoteAvr.CODE_FOU = FOU_CRIT.COD_FOU

            WHERE FOU_CRIT.COD_PER = SUBSTRING(CA_ENTET.DTE_PERIO, 1, 4)',
            [':commandeId' => $commandeId]
        );
    }
}
