<?php

namespace Modules\GlobalApplication\Repository;

use Entity\Model\Incident;
use Helpers\Outil\Date;
use Helpers\Outil\Sql;
use Helpers\Additional\Specifique\Specifique;
use Helpers\Pagination\PaginatorGestion;
use Helpers\QueryBuilder\AbsRepository;
use Helpers\Validator\Validator;

class Incidents extends AbsRepository
{
    /**
     * listeIncident
     * @param string $code
     * @param array<string> $post
     * @param string $Chps_Filter
     * @return array<mixed>
     */
    public function listeIncident(string $code = '', array $post = [], string $Chps_Filter = '')
    {
        $arrayReq = [];

        // TODO : move trad somewhere else
        $req = "SELECT 
            FOURNINC.DTE_OCCUR as DATE, "
            . $this->selectTrad(
                'CATINCID.LIBELLE',
                'LIBELLE_CATINC',
                'CATINCID_Trad'
            )
            . ", "
            . $this->selectTrad(
                'INCIDENT.LIBELLE',
                'LIBELLE2_INC',
                'INCIDENT_Trad'
            )
            . ",
            FOURNINC.DESCRIPT, 
            FOURNINC.NIVEAU as NIVEAU_FOUR, 
            FOURNINC.CODE, 
            FOURNINC.IDUNIQUE, 
            FOURNINC.DTE_OCCUR, 
            FOURNINC.NO_CDE, 
            FOURNINC.PO_CDE, 
            FOURNINC.ART, 
            FOURNINC.NO_BR, 
            FOURNINC.PO_BR, 
            FOURNINC.INCF_USER, 
            FOURNINC.QTE_REFU,  
            FOURNINC.DTE_CREAT, 
            FOURNINC.DTE_LIV, 
            FOURNINC.FOU, 
            FOURNINC.DES_ART,
            FOURNINC.NO_FACT,
            CATINCID.CODE_CAT,
            MEMOS.CONTMEMO,
            Util.NOM as Util_NOM,
            FOURNINC.INC_FERME,
            FOURNISS.RS
        FROM FOURNINC with (nolock)
        LEFT JOIN INCIDENT with (nolock) on INCIDENT.COD_INC = FOURNINC.CODE
        LEFT JOIN CATINCID with (nolock) on CATINCID.CODE_CAT = INCIDENT.CODE_CAT
        " . $this->joinTrad('CATINCID.LIBELLE', $arrayReq, 'CATINCID_Trad') . "
        " . $this->joinTrad('INCIDENT.LIBELLE', $arrayReq, 'INCIDENT_Trad') . "
        LEFT JOIN MEMOS with (nolock) ON MEMOS.NODEMEMO = FOURNINC.NM_COM
        LEFT JOIN HASTUTIL as Util with (nolock) ON Util.CODE = FOURNINC.INCF_USER
        LEFT JOIN FOURNISS with(nolock) ON FOURNISS.CODE = FOURNINC.FOU 
        WHERE 1=1 ";

        if ($code != 'ALL') {
            $req .= " and " . $Chps_Filter . "=:value1";
            $arrayReq[':value1'] = $code;
        }

        $this->_listeIncidentClause($req, $arrayReq, $post);

        (new PaginatorGestion())->injecterSqlCount($req, $post, $arrayReq);

        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * @param array<mixed> $post
     * @return array<array<string>>
     */
    public function listeIncidentExport(
        string $code = '',
        array $post = [],
        string $Chps_Filter = ''
    ): array {
        $arrayReq = [];

        $req = "SELECT 
                DTE_OCCUR as DATE,"
            . $this->selectTrad(
                'CATINCID.LIBELLE',
                'CATEGORIE_INCIDENT',
                'CatIncid_Trad'
            ) . ",
                "
            . $this->selectTrad(
                'INCIDENT.LIBELLE',
                'INCIDENT',
                'Incident_Trad'
            )
            . ",
                DESCRIPT as DESCRIPTION, 
                FOURNINC.NIVEAU as NIVEAU, 
                NO_CDE as NUMERO_COMMANDE, 
                PO_CDE as NUM_LIGNE_COMMANDE, 
                ART as CODE_ARTICLE, 
                NO_BR as NUMERO_BR, 
                PO_BR as NUM_LIGNE_BR, 
                QTE_REFU as QTE_ANOMALIE, 
                DTE_CREAT as DATE_CREATION, 
                DTE_LIV as DATE_LIVRAISON, 
                DES_ART as LIBELLE_ARTLCE, 
                CONTMEMO as COMMENTAIRE,
                FOURNISS.CODE CODE_FOURNISSEUR,
                FOURNISS.RS LIBELLE_FOURNISSEUR
            FROM FOURNINC with (nolock)
            LEFT JOIN INCIDENT with (nolock) on INCIDENT.COD_INC = FOURNINC.CODE
            LEFT JOIN CATINCID with (nolock) on CATINCID.CODE_CAT = INCIDENT.CODE_CAT
            " . $this->joinTrad('CATINCID.LIBELLE', $arrayReq, 'CatIncid_Trad') . " 
            " . $this->joinTrad('INCIDENT.LIBELLE', $arrayReq, 'Incident_Trad') . " 
            LEFT JOIN MEMOS with (nolock) ON MEMOS.NODEMEMO = FOURNINC.NM_COM
            LEFT JOIN FOURNISS with (nolock) ON FOURNISS.CODE = FOURNINC.FOU
            WHERE 1=1 ";

        if ($code != 'ALL') {
            $req .= " and " . $Chps_Filter . "=:value1";
            $arrayReq[':value1'] = $code;
        }

        $this->_listeIncidentClause($req, $arrayReq, $post);
        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * @param array<mixed> $arrayReq
     * @param array<mixed> $post
     */
    private function _listeIncidentClause(
        string &$req,
        array &$arrayReq,
        array $post
    ): void {
        if (!$post) {
            return;
        }
        if (isset($post['filter_date_DATE']) && $post['filter_date_DATE']) {
            Date::dateToSql($post['filter_date_DATE']);
            $req .= " and FOURNINC.DTE_OCCUR  = :value2";
            $arrayReq[':value2'] = $post['filter_date_DATE'];
        }
        if (isset($post['filter_LIBELLE_CATINC']) && $post['filter_LIBELLE_CATINC']) {
            $req .= " and INCIDENT.CODE_CAT = :value3";
            $arrayReq[':value3'] = $post['filter_LIBELLE_CATINC'];
        }
        if (isset($post['filter_LIBELLE2_INC']) && $post['filter_LIBELLE2_INC']) {
            $req .= " and FOURNINC.CODE  = :value4";
            $arrayReq[':value4'] = $post['filter_LIBELLE2_INC'];
        }
        if (isset($post['filter_DESCRIPT']) && $post['filter_DESCRIPT']) {
            $req .= " and  UPPER(DESCRIPT) like UPPER(:value5)";
            $arrayReq[':value5'] = '%' . trim($post['filter_DESCRIPT']) . '%';
        }
        if (isset($post['filter_NIVEAU_FOUR']) && $post['filter_NIVEAU_FOUR']) {
            $req .= " and  FOURNINC.NIVEAU = :value6";
            $arrayReq[':value6'] = $post['filter_NIVEAU_FOUR'];
        }
        if (isset($post['filter_FOU']) && $post['filter_FOU']) {
            $req .= " and (upper(FOURNISS.CODE) LIKE :value7 or upper(FOURNISS.RS) LIKE :value8)";
            $arrayReq[':value7'] = '%' . trim(strtoupper($post['filter_FOU'])) . '%';
            $arrayReq[':value8'] = '%' . trim(strtoupper($post['filter_FOU'])) . '%';
        }

        if (isset($post['filter_FOUdebut-----finRS']) && $post['filter_FOUdebut-----finRS']) {
            $req .= " AND (UPPER(FOURNISS.CODE) LIKE :value9 OR UPPER(FOURNISS.RS) LIKE :value10)";
            $arrayReq[':value9'] = '%' . trim(strtoupper($post['filter_FOUdebut-----finRS'])) . '%';
            $arrayReq[':value10'] = '%' . trim(strtoupper($post['filter_FOUdebut-----finRS'])) . '%';
        }

        if (isset($post['BETWEEN_DTE_OCCUR']) && $post['BETWEEN_DTE_OCCUR']) {
            $req .= " and FOURNINC.DTE_OCCUR BETWEEN :value11 and :value12";
            $arrayReq[':value11'] = $post['BETWEEN_DTE_OCCUR'] . $post['mois'] . '01';
            $arrayReq[':value12'] = $post['BETWEEN_DTE_OCCUR'] . $post['mois'] . '31';
        }

        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 3, 0, 1);

        if (count($whereArray) >  0) {
            $req .= $whereArray['tri'];
        }

        $filtreAvance = new \Helpers\Filter\FiltreAvance\Generator($post);
        $req .= $filtreAvance->generateSql(1);

        $filtreAvance->pushArray($arrayReq);
    }

    /**
     * maxIdIncident
     * @param object $pdo
     * @return array<mixed>
     */
    public function maxIdIncident(Object $pdo): ?array
    {
        $req = "select max(IDUNIQUE) IDUNIQUE from FOURNINC with (nolock)";

        $incident = $pdo->prepare($req);
        $incident->execute();

        return $incident->fetch($pdo::FETCH_ASSOC);
    }

    /**
     * creationIncident
     * @param array<string> $post
     * @return void
     */
    public function creationIncident(array $post): void
    {
        $idMemo = Sql::updateMemo($this->pdo, '', $post['CONTMEMO']);

        $req = "INSERT INTO FOURNINC (FOU, NIVEAU, CODE, DTE_OCCUR, DESCRIPT, NM_COM, NO_CDE, PO_CDE, ART, DES_ART, NO_BR, PO_BR, QTE_REFU, DTE_LIV, DTE_CREAT, INCF_USER) VALUES 
                (:code, :value0, :value1, :value3, :value4, " . $idMemo . ", :value5, :value6, :value7, :value8, :value9, :value10, :value11, :value12, :value13, :value14)";

        $arrayReq = [
            ':code' => $post['FOU'],
            ':value0' => $post['NIVEAU'],
            ':value1' => $post['CODE'],
            ':value3' => $post['DTE_OCCUR'],
            ':value4' => $post['DESCRIPT'],
            ':value5' => $post['NO_CDE'],
            ':value6' => $post['PO_CDE'],
            ':value7' => $post['ART'],
            ':value8' => $post['DES_ART'],
            ':value9' => $post['NO_BR'] ?? '',
            ':value10' => $post['PO_BR'] ?? '',
            ':value11' => $post['QTE_REFU'] ?? '',
            ':value12' => $post['DTE_LIV'] ?? '',
            ':value13' => date('Ymd'),
            ':value14' => $_SESSION["Session_Login"],
        ];

        $incident = $this->pdo->prepare($req);
        $incident->execute($arrayReq);

        $this->createIncidentPostProcess(
            $post['NO_CDE'],
            $post['CODE'],
            $post['CONTMEMO']
        );
    }

    public function createIncident(
        string $caEntetId,
        string $caLigneId,
        string $description,
        string $memo,
        string $codInc,
        string $brNumber = '',
        string $brLigne = '',
        string $refusedQuantity = ''
    ): void {

        $idMemo = Sql::updateMemo($this->pdo, '', $memo);

        $param = [
            'DTE_OCCUR'   => date('Ymd'),
            'DESCRIPT'    => $description,
            'NM_COM'      => $idMemo,
            'INCF_USER'   => $_SESSION['Session_Login'],
            'NO_BR'       => $brNumber,
            'PO_BR'       => $brLigne,
            'QTE_REFU'    => $refusedQuantity,
        ];

        $sqlExpr = [
            'NO_CDE'    => 'CA_ENTET.NO_CDE',
            'PO_CDE'    => "COALESCE(CA_LIGNE.NO_PO, 0)",
            'FOU'       => 'CA_ENTET.CODE_FOU',
            'NIVEAU'    => 'INCIDENT.NIVEAU',
            'CODE'      => 'INCIDENT.COD_INC',
            'ART'       => "COALESCE(CA_LIGNE.CODE_ART, '')",
            'DES_ART'   => "COALESCE(CA_LIGNE.DES_SOMM,'')",
            'DTE_CREAT' => 'CA_ENTET.DTE_CREAT',
        ];

        $columns = implode(',', array_keys($param + $sqlExpr));
        $select = implode(
            ',',
            array_merge(
                array_map(function ($paramKey) {
                    return ':' . $paramKey;
                }, array_keys($param)),
                $sqlExpr
            )
        );

        $this->execute(
            "INSERT INTO FOURNINC ({$columns}) 
            SELECT {$select} 
            FROM CA_ENTET with(nolock)
            JOIN INCIDENT with(nolock) ON INCIDENT.COD_INC = :incId
            LEFT JOIN CA_LIGNE with(nolock) 
                ON CA_LIGNE.NO_CDE = CA_ENTET.NO_CDE
                AND CA_LIGNE.NO_PO = :caLigneId
            WHERE CA_ENTET.NO_CDE = :caEntetId",
            $param + [
                'caEntetId' => $caEntetId,
                'caLigneId' => intval($caLigneId), // Convert '' to 0
                'incId' => $codInc,
            ]
        );

        $this->createIncidentPostProcess(
            $caEntetId,
            $codInc,
            $memo
        );
    }

    private function createIncidentPostProcess(
        string $caEntetId,
        string $incTypeId,
        string $memoContent
    ): void {
        // Si le paramétrage des flux d'enregistrements évènements Incident fournisseur est activé , alors on crée un évènement dans XT_EVE
        $INT_EVT = sql::paravrac($this->pdo, 'INT_EVT', 'FOURNINC');
        if ($INT_EVT == 'O') {
            $this->insertXtEVE($this->pdo, [
                'DATEEVT' => date('Ymd'),
                'HEUREEVT' => date('His'),
                'TYPEOBJ' => 'FOURNINC',
                'ID' => str_pad($this->maxIdIncident($this->pdo)['IDUNIQUE'], 10, '0', STR_PAD_LEFT),
            ]);
        }

        // SPECIFIQUE : Initialisation CS Litige en cours
        $namespaceSpecifique = (new Specifique())
            ->specifiqueFunction('\\Modules\\Outil\\Incident');

        if (class_exists($namespaceSpecifique)) {
            Specifique::specifique(
                '\\Modules\\Outil\\Incident',
                'ouvertureCSLitige',
                $this->pdo,
                $caEntetId,
                $incTypeId,
                $memoContent
            );
        }
    }

    public function createIncidentReportLiv(
        string $caEntetId,
        string $caLigneId,
        string $description,
        string $memo
    ): void {
        $this->createIncident($caEntetId, $caLigneId, $description, $memo, 'REPORT');
    }

    /**
     * insertXtEVE
     * @param \PDO $pdo
     * @param array<string> $value
     * @return void
     */
    public function insertXtEVE(\PDO $pdo, array $value): void
    {

        $req = "INSERT INTO XT_EVE (DATEEVT, HEUREEVT, TYPEOBJ, ID_1) VALUES (:value0, :value1, :value2, :value3)";

        $arrayReq = [
            ':value0' => $value['DATEEVT'],
            ':value1' => $value['HEUREEVT'],
            ':value2' => $value['TYPEOBJ'],
            ':value3' => $value['ID'],
        ];

        $incident = $pdo->prepare($req);
        $incident->execute($arrayReq);
    }

    /**
     * Incident
     * @param string $idUnique
     * @return array<string>
     */
    // phpcs:ignore
    public function Incident(string $idUnique): ?array
    {
        $req = " SELECT FOURNINC.*, MEMOS.CONTMEMO, INCIDENT.LIBELLE libelleIncident FROM FOURNINC with (nolock)
                LEFT JOIN MEMOS with (nolock) ON MEMOS.NODEMEMO = FOURNINC.NM_COM
                LEFT JOIN INCIDENT with (nolock) ON INCIDENT.COD_INC = FOURNINC.CODE
                WHERE IDUNIQUE=" . trim($idUnique) . "";

        $incident = $this->pdo->prepare($req);
        $incident->execute();

        return $incident->fetch($this->pdo::FETCH_ASSOC);
    }

    /**
     * updateIncident
     * @param string $idUnique
     * @param string $idmemos
     * @param array<string> $post
     * @return void
     */
    public function updateIncident(string $idUnique, string $idmemos, array $post): void
    {
        $idmemos = sql::updateMemo($this->pdo, $idmemos, $post['CONTMEMO']);

        $req = "UPDATE FOURNINC SET CODE=:value1, DTE_OCCUR=:value2, DESCRIPT=:value3, NM_COM='" . $idmemos . "', 
                NO_CDE = :value5, PO_CDE = :value6, ART = :value7, DES_ART = :value8,
                NO_BR = :value9, PO_BR = :value10, QTE_REFU = :value11, DTE_LIV = :value12, NIVEAU = :value13
                where IDUNIQUE=" . $idUnique . "";

        $arrayReq = [
            ':value1' => $post['CODE'],
            ':value2' => $post['DTE_OCCUR'],
            ':value3' => $post['DESCRIPT'],
            ':value5' => $post['NO_CDE'],
            ':value6' => $post['PO_CDE'],
            ':value7' => $post['ART'],
            ':value8' => $post['DES_ART'],
            ':value9' => $post['NO_BR'] ?? 0,
            ':value10' => $post['PO_BR'] ?? 0,
            ':value11' => $post['QTE_REFU'],
            ':value12' => ($post['DTE_LIV']),
            ':value13' => $post['NIVEAU'],
        ];


        $incident = $this->pdo->prepare($req);
        $incident->execute($arrayReq);

        $this->createIncidentPostProcess(
            $post['NO_CDE'],
            $idUnique,
            $post['CONTMEMO']
        );
    }

    /**
     * listeTypeIncident
     * @TODO rename $true
     * @return array<array<mixed>>
     */
    public function listeTypeIncident(bool $true = false): ?array
    {
        $param = [];
        $req = "SELECT
                COD_INC, 
                " . $this->selectTrad('INCIDENT.LIBELLE') . ",
                NIVEAU, 
                CODE_CAT,
                GERE_HAP
            FROM INCIDENT with (nolock) 
            " . $this->joinTrad('INCIDENT.LIBELLE', $param) . "
        ";
        $true == false ? $req .= "WHERE GERE_HAP = " . sql::setBoolSql(0) : "";

        return $this->fetchAssocAll($req, $param);
    }

    /**
     * incidentCategoriesList
     *
     * @param  bool $gereHap
     * @return array<array<mixed>>
     */
    public function incidentCategoriesList(bool $gereHap = false): ?array
    {
        $param = [];
        $query = "SELECT
                CODE_CAT, 
                " . $this->selectTrad('CATINCID.LIBELLE') . ",
                GERE_HAP
            FROM CATINCID with (nolock) 
            " . $this->joinTrad('CATINCID.LIBELLE', $param) . "
        ";

        $gereHap === false ? $query .= "WHERE GERE_HAP = " . sql::setBoolSql(0) : "";

        return $this->fetchAssocAll($query, $param);
    }

    /**
     * deleteIncident
     * @param string $idUnique
     * @return void
     */
    public function deleteIncident(string $idUnique): void
    {
        $req = "delete from FOURNINC where IDUNIQUE=" . $idUnique . "";

        $incident = $this->pdo->prepare($req);
        $incident->execute();
    }

    /**
     * getIncident
     * @param string $type
     * @return array<array<mixed>>
     */
    public function getIncident(string $type): ?array
    {
        $param = [':type' => $type];
        $query = "SELECT 
                COD_INC, 
                NIVEAU,
                " . $this->selectTrad('i.LIBELLE') . "
            FROM INCIDENT i with (nolock)
            LEFT JOIN CATINCID c with (nolock) ON c.CODE_CAT=i.CODE_CAT
            " . $this->joinTrad('i.LIBELLE', $param) . "
            WHERE (i.CODE_CAT = :type OR c.GERE_HAP = 0x46) 
                AND i.GERE_HAP = 0x46";
        return $this->fetchAssocAll($query, $param);
    }

    /**
     * Summary of getIncidentsByCategory
     * @param string $categoryCode
     * @return string[][]
     */
    public function getIncidentsByCategory(string $categoryCode): array
    {

        $param = [':categoryCode' => $categoryCode];
        $query = "SELECT 
                i.COD_INC as code,
                i.MASQUER as masquer,
                " . $this->selectTrad('i.LIBELLE') . "
            FROM INCIDENT i with (nolock)
            " . $this->joinTrad('i.LIBELLE', $param) . "
            WHERE i.CODE_CAT = :categoryCode
                AND i.MASQUER = " . sql::setBoolSql(0) ."
            ORDER BY LIBELLE";

        return $this->fetchAssocAll($query, $param);
    }

    /**
     * @return ?array<mixed>
     */
    public function getInfoMail(string $id) :?array
    {
        return $this->fetchAssocOne(
            "SELECT 
                FOURNINC.*,
                FOURNISS.EMAIL as 'Four.EMAIL',
                FOURNISS.COD_LNG as 'Four.COD_LNG',
                FAC_ENT.NUM_FACT as 'Facture.NUM_FACT',
                FAC_ENT.FIC_LAD as 'Facture.FIC_LAD',
                FAC_ENT.FACT_FOU as 'Facture.FACT_FOU',
                FAC_ENT.DATE_FAFO as 'Facture.DATE_FAFO'
            FROM FOURNINC with(nolock)
            JOIN FOURNISS with(nolock) ON FOURNISS.CODE = FOURNINC.FOU
            /** TODO958 lien FACT<->FOURNINC */
            JOIN FAC_ENT with(nolock) ON FAC_ENT.NUM_CDE = FOURNINC.NO_CDE
            WHERE FOURNINC.IDUNIQUE = :id AND FOURNINC.NO_CDE != 0",
            ['id' => $id]
        );
    }
}
