<?php

namespace Modules\Commande\Repository;

use Helpers\Outil\Sql;
use Entity\Model\XtEve;
use Helpers\QueryBuilder\AbsRepository;

class CommandeEventRepo extends AbsRepository
{
    /**
     * @param mixed $code
     * @return array<mixed>
     */
    public function commandeEntete($code)
    {
        $req = "Select * from CA_ENTET with (nolock)
                LEFT JOIN FOURNISS with (nolock) on CA_ENTET.CODE_FOU = FOURNISS.CODE
                WHERE NO_CDE = :value";
        
        $arrayReq[':value']=$code;
        
        $commande = $this->pdo->prepare($req);
        $commande->execute($arrayReq);
        
        return $commande->fetch();
    }

    private function getMainQuery() :string
    {
        return "SELECT 
                CA_EVENT.*, MEMOS.*, SUICADAT.*,
                Signataire.NOM as Signataire_NOM,
                Utili.NOM as Utili_NOM
            FROM CA_EVENT with (nolock)
            LEFT JOIN MEMOS with (nolock) ON CA_EVENT.NM_COMME = MEMOS.NODEMEMO
            LEFT JOIN SUICADAT with (nolock) ON SUICADAT.CODE_SCD = CA_EVENT.CODE
            LEFT JOIN HASTUTIL as Signataire with (nolock) ON Signataire.CODE = CA_EVENT.SIGNAT
            LEFT JOIN HASTUTIL as Utili with (nolock) ON Utili.CODE = CA_EVENT.UTIL
            WHERE NO_CDE=:value";
    }

    /**
     * @param array<mixed> $post
     */
    private function getFilter(array $post) :string
    {
        $where = '';
        if (!empty($post['CA_EVENT.CODE'])) {
            $where .= " AND CA_EVENT.CODE = 'SIG' "; // TODO : parametrize
        }
        return $where;
    }
    
    /**
     * @param array<mixed> $post
     * @return array<array<string>>
     */
    public function listeEventCommande(string $code, array $post)
    {
        return $this->fetchAssocAll(
            $this->getMainQuery()
            . $this->getFilter($post)
            . " ORDER BY DTE desc, HEURE desc ",
            [':value' => $code]
        );
    }

    /**
     * @param array<mixed> $post
     */
    public function export(string $code, array $post) :\PDOStatement
    {
        return $this->createStatement(
            $this->getMainQuery()
            . $this->getFilter($post)
            . " ORDER BY DTE desc, HEURE desc ",
            [':value' => $code]
        );
    }
    
    /**
     * @return string[][]
     */
    public function listeEvent()
    {
        $param = [];
        $req = "SELECT 
                CODE_SCD,
                " . $this->selectTrad('SUICADAT.LIBELLE') . ",
                GERE_HAP, TYP_EVE, MOD_ECH 
            FROM SUICADAT WITH (NOLOCK) 
            " . $this->joinTrad('SUICADAT.LIBELLE', $param) . "
            WHERE GERE_HAP = 0x54";
        
        return $this->fetchAssocAll($req, $param);
    }
    
    /**
     * @param array<mixed> $post
     * @return bool
     */
    public function ajoutEvenement($post)
    {
        $this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
        
        $req = "SELECT MAX(NUM_ORD) NUM_ORD FROM CA_EVENT with (nolock) WHERE NO_CDE=:numCde";
        $arrayReq = [':numCde' => $post['NUM_CDE']];
        
        $commande = $this->pdo->prepare($req);
        $commande->execute($arrayReq);
        $result = $commande->fetch();

        $maxOrd = (($result['NUM_ORD'])? $result['NUM_ORD']+1:0);
        
        $req = "INSERT INTO CA_EVENT (NO_CDE, NUM_ORD, AVNT, DTE, HEURE, CODE, UTIL, SIGNAT, HAPLUS, NM_COMME, TYP_EVE, MOD_ECH, COORD, ID_USER_OXALINK)
                VALUES (:numCde, :numOrd, :avnt, :dateCre, :heureCre, :codeEve, :Util, '', 0x46, :memos, :typEve, :modEch, :coord, :idUserOxalink)";
        
        $arrayReq = [
            ':numCde' => $post['NUM_CDE'],
            ':numOrd' => $maxOrd,
            ':avnt' => $post['AVNT'] ?? '',
            ':dateCre' => $post['DTE'],
            ':heureCre' => $post['HEURE'],
            ':codeEve' => $post['CODE'],
            ':Util' => $post['UTIL'],
            ':memos' => $post['NM_COMME'],
            ':typEve' => $post['TYP_EVE'] ?? '',
            ':modEch' => $post['MOD_ECH'] ?? '',
            ':coord' => $post['COORD'] ?? '',
            ':idUserOxalink' => '',
        ];
        
        $commande = $this->pdo->prepare($req);
        $retourSQL = $commande->execute($arrayReq);

        // Si le paramétrage des flux d'enregistrements évènements Commandes est activé et que le code évènement est lui aussi paramétré, alors on crée un évènement dans XT_EVE
        $INT_EVT = sql::paravrac($this->pdo, 'INT_EVT', 'CA_ENTET');
        $CONSTDCDE = sql::paravrac($this->pdo, 'CONSTDCDE', $post['CODE']);
        if ($retourSQL && $INT_EVT=='O' && !empty($CONSTDCDE)) {
            $XtEve = new XtEve($this->pdo);
            $array = [
                'DATEEVT' => $post['DTE'],
                'HEUREEVT' => $post['HEURE'],
                'TYPEOBJ' => 'CA_ENTET',
                'ID_1' => str_pad($post['NUM_CDE'], 9, '0', STR_PAD_LEFT),
            ];
            $retourSQL = $XtEve->insert($array);
        }
        
        return $retourSQL;
    }
    
    /**
     * @param mixed $id
     * @param mixed $ordre
     * @return array<mixed>z
     */
    public function findEvent($id, $ordre)
    {
        $req = "Select * from CA_EVENT with (nolock)
                LEFT JOIN MEMOS with (nolock) on CA_EVENT.NM_COMME = MEMOS.NODEMEMO
                where NO_CDE=:value and NUM_ORD=:value1";
        
        $arrayReq[':value']=$id;
        $arrayReq[':value1']=$ordre;
        
        $commande = $this->pdo->prepare($req);
        $commande->execute($arrayReq);
        
        return $commande->fetch();
    }
    
    /**
     * @param mixed $id
     * @param mixed $ordre
     * @return void
     */
    public function deleteEvenement($id, $ordre)
    {
        $req = "Delete CA_EVENT where NO_CDE=:value and NUM_ORD=:value1";
        
        $arrayReq[':value']=$id;
        $arrayReq[':value1']=$ordre;
        
        $commande = $this->pdo->prepare($req);
        $commande->execute($arrayReq);
    }
    
    
    /**
     * @param array<mixed> $post
     * @return void
     */
    public function modificationEvenement($post)
    {
        $req = "UPDATE CA_EVENT SET NM_COMME=:value2 where NO_CDE=:value and NUM_ORD=:value1";
        
        $arrayReq[':value']=$post['NUM_CDE'];
        $arrayReq[':value1']=$post['NO_ORD'];
        $arrayReq[':value2']=$post['NM_COMME'];
        
        $commande = $this->pdo->prepare($req);
        $commande->execute($arrayReq);
    }
    
    /**
     * insertEvent
     *
     * @param  array<mixed> $array
     * @return string
     */
    public function insertEvent(array $array) : string
    {
        $req = "insert into CA_EVENT (NO_CDE, NUM_ORD, AVNT, DTE, HEURE,CODE, UTIL, SIGNAT, NM_COMME, TYP_EVE) 
                VALUES  (:id, :noOrdre, :avnt, :date, :heure,:codeEvent, :user, :signa, :nmComme, :typeEve )";
        
        $maxOrdre = (string) $this->getMaxOrder($array['NO_CDE']);

        $arrayReq = [
            ':id'           => $array['NO_CDE']??'',
            ':noOrdre'      => $maxOrdre,
            ':avnt'         => $array['AVNT']??'',
            ':date'         => date('Ymd'),
            ':heure'        => date('His'),
            ':codeEvent'    => $array['CODE']??'',
            ':user'         => $_SESSION["Session_Login"],
            ':signa'        => $array['SIGNAT']??'',
            ':nmComme'      => Sql::updateMemo($this->pdo, '', ($array['NM_COMME']??'')),
            ':typeEve'      => $array['TYP_EVE']??'',
        ];
        
        $data = $this->pdo->prepare($req);
        $data->execute($arrayReq);

        return $maxOrdre;
    }

    public function getMaxOrder(string $noCde) : string
    {
        $req = "SELECT MAX(NUM_ORD) NUM_ORD FROM CA_EVENT with (nolock) WHERE NO_CDE=:numCde";
        $arrayReq = [':numCde' => $noCde];
        
        $commande = $this->pdo->prepare($req);
        $commande->execute($arrayReq);
        $numOrdre = $commande->fetch()['NUM_ORD']??null;
        $maxOrd = $numOrdre? $numOrdre+1:0;

        return (string) $maxOrd;
    }
}
