<?php

namespace Modules\TermeDePaiement\Repository;

use Helpers\Outil\Sql;
use Helpers\QueryBuilder\AbsRepository;

class TermPaieRepo extends AbsRepository
{
    /**
     * Le calcul de l'avancement des termes de paiement doit être fait par une procédure stockée.
     * Elle devra être appelée à chaque fois que le statut "Facturable" d'un terme de paiement change.
     */
    public function recalc(string $commandeId, string $typeobj): void
    {
        $isInCalleeTransac = $this->pdo->inTransaction();
        if (!$isInCalleeTransac) {
            $this->pdo->beginTransaction();
        }

        // Mis à jour des lignes events manuels (listEvent.HAPLUS = 0x46) où les seuils sont à 0 (p.POURCBUT = 0) pour que LIGN_OK soit = 0x54
        $stmt = $this->pdo->prepare(
            'UPDATE p
                    SET p.LIGN_OK = 0x54 
                    FROM TP_PEVT p
                    LEFT JOIN TP_EVT e
                        ON p.NOCOMMAND = e.NUM_CDE
                        AND p.NOTP = e.NUM_TP
                        AND p.NOLIGTP = e.NOL_TP
                        AND p.TYPEOBJ = e.TYPEOBJ
                    LEFT JOIN TYEVTSTP listEvent
                        ON listEvent.CODE_EVT = e.EVENEMENT
                    WHERE p.NOCOMMAND =  :commandeId
                        AND p.TYPEOBJ = :typeobj
                        AND p.POURCBUT = 0
                        AND listEvent.HAPLUS = 0x46
                        '
        );
        $stmt->execute([
            'commandeId' => $commandeId,
            'typeobj' => $typeobj
        ]);

        // Recalcul le le statut de chaque terme de paiement pour voir s'il est facturable,
        // en vérifiant le statut des postes de commande LIGN_OK
        // Si un poste de commande est à false (TP_PEVT.LIGN_OK) alors que l'event est à true (TP_EVT.LIGN_OK), cela n'est pas génant
        // En revanche, il faut que l'ensemble des seuils (POURCBUT) soient atteint (LIGN_OK)
        // Si on souhaite qu'un TP soit facturable lorsque (TP_ENT.TP_FACT)
        $stmt = $this->pdo->prepare(
            'UPDATE Entete
                    SET Entete.TP_FACT = (
                        SELECT 
                            CASE 
                                WHEN ( (MIN(p.LIGN_OK) = 0x54) OR MIN(p.LIGN_OK) IS NULL) AND (MIN(e.EVT_OK) = 0x54) 
                                THEN 0x54
                                ELSE 0x46
                            END
                        FROM TP_EVT e
                        LEFT JOIN TP_PEVT p WITH (NOLOCK) 
                            ON p.NOCOMMAND = e.NUM_CDE
                            AND p.NOTP = e.NUM_TP
                            AND p.NOLIGTP = e.NOL_TP
                            AND p.TYPEOBJ = e.TYPEOBJ
                        WHERE e.NUM_CDE = Entete.NUM_CDE
                        AND e.NUM_TP = Entete.NUM_TP
                        AND e.TYPEOBJ = Entete.TYPEOBJ
                    ) 
                    FROM TP_ENT Entete
                    WHERE Entete.NUM_CDE =  :commandeId
                        AND Entete.TYPEOBJ = :typeobj
                        AND Entete.TP_FACT = 0x46'
        );
        $stmt->execute([
            'commandeId' => $commandeId,
            'typeobj' => $typeobj
        ]);

        // calcule le délai de facturation en cas de facturation à terme
        $stmt = $this->pdo->prepare(
            "UPDATE t
            SET t.DATELIBFACT = (
                    CASE
                        WHEN t.TYPEDELAIFACT = '1' THEN CONVERT(
                            char(8), DATEADD(day, t.DELAIFACT, getdate()), 112
                        )
                        WHEN t.TYPEDELAIFACT = '2' THEN CONVERT(
                            char(8),
                            DATEADD(month, t.DELAIFACT, getdate()),
                            112
                        )
                        WHEN t.TYPEDELAIFACT = '3' then CONVERT(char(8),
                            DATEADD(year, t.DELAIFACT, getdate()),
                            112)
                        ELSE CONVERT(char(8),
                            getdate(),
                            112)
                    END
                )
            FROM TP_ENT as t
            WHERE t.NUM_CDE = :commandeId
                AND t.TYPEOBJ = :typeobj
                AND t.TP_FACT = 0x54
            "
        );
        $stmt->execute([
            'commandeId' => $commandeId,
            'typeobj' => $typeobj
        ]);

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

    /**
     * Summary of getNumTp
     * @param string $numCde
     * @return array<string, mixed>
     */
    public function getNumTp(string $numCde) : array
    {
        $req = "SELECT NUM_TP FROM TP_ENT with(nolock)
                WHERE NUM_CDE = :numCde";
        $stmt = $this->pdo->prepare($req);
        $stmt->execute(['numCde' => $numCde]);
        $rows = $stmt->fetchAll(\PDO::FETCH_COLUMN);
        
        return ['NUM_TP' => $rows[0] ?? null];
    }

    /**
     * Summary of getDetailSuiviTp
     * @param string $numCde
     * @return string[][]
     */
    public function getDetailSuiviTp(string $numCde): array
    {
        $req = <<<SQL
                        SELECT tpEvt.EVENEMENT, listTpEvt.CODE_EVT, tpEnt.* 
                        FROM TP_ENT tpEnt with (nolock)
                        LEFT JOIN TP_EVT tpEvt with (nolock) 
                            ON tpEvt.NUM_CDE = tpEnt.NUM_CDE 
                            AND tpEvt.NUM_TP = tpEnt.NUM_TP
                        LEFT JOIN TYEVTSTP listTpEvt with (nolock)
                            ON listTpEvt.CODE_EVT = tpEvt.EVENEMENT
                        WHERE tpEnt.NUM_CDE = :numCde
        SQL;

        return $this->fetchAssocAll($req, ['numCde' => $numCde]);
    }

    /**
     * Summary of getNumModele
     * @param string $libelle
     * @return array<string, mixed>
     */
    public function getNumModele(string $libelle) : array
    {
        $req = "SELECT ID_MODELE FROM TP_MODELES with(nolock)
                WHERE LIB_MODELE = :libelle";
        $stmt = $this->pdo->prepare($req);
        $stmt->execute(['libelle' => $libelle]);
        $rows = $stmt->fetchAll(\PDO::FETCH_COLUMN);
        
        return ['ID_MODELE' => $rows[0] ?? null];
    }

    /**
     * Summary of getNextNumTp
     * @return array<string, mixed>
     */
    public function getNextNumTp(): array
    {
        $req = " SELECT ISNULL(MAX(NUM_TP), 0) + 1 AS NEXT_NUM_TP
                 FROM TP_ENT ";
    
        return $this->fetchAssocOne($req, []);
    }

        /**
     * Summary of getNextNumTp
     * @return array<string, mixed>
     */
    public function getLastNoLigneTpEvt(
        string|int $cde,
        string|int $tp
    ): array {
        $req = " SELECT ISNULL(MAX(NOL_TP), 0) + 1 AS NEXT_NOL_TP
                 FROM TP_EVT 
                 WHERE NUM_CDE = :cde AND NUM_TP = :tp
                 ";
    
        return $this->fetchAssocOne($req, [
            'cde' => (int) $cde,
            'tp' => (int) $tp
        ]);
    }

    /**
     * Récupère le dernier TP_ENT inséré pour un NUM_CDE donné (le plus grand NUM_TP).
     * @param string $numCde
     * @return array<string, mixed>
     */
    public function getLastTp(string $numCde): array
    {
        $req = "SELECT TOP 1 * FROM TP_ENT WITH (NOLOCK)
                WHERE NUM_CDE = :numCde
                ORDER BY NUM_TP DESC";
        $stmt = $this->pdo->prepare($req);
        $stmt->execute(['numCde' => $numCde]);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        
        return $row ?: [];
    }

    /**
     * Summary of getEvtOk
     * @param string $codeEvt
     * @return array<string, mixed>
     */
    public function getEvtOk(string $codeEvt) : array
    {
        $req = "SELECT HAPLUS FROM TYEVTSTP with(nolock)
                WHERE CODE_EVT = :codeEvt";
        $stmt = $this->pdo->prepare($req);
        $stmt->execute(['codeEvt' => $codeEvt]);
        $rows = $stmt->fetchAll(\PDO::FETCH_COLUMN);
        
        return ['HAPLUS' => $rows[0] ?? null];
    }

    /**
     * Summary of getMntBase
     * @param string $module
     * @param string $id
     * @return array<string, mixed>
     */
    public function getMntBase(string $module, string $id): array
    {
        if ($module === 'DemandeAchat') {
            $req = "SELECT TOTAL_DA AS MNT_BASE FROM DA_ENTET WITH (NOLOCK) WHERE NO_DA = :id";
        } else {
            $req = "SELECT TOTAL_CDE AS MNT_BASE FROM CA_ENTET WITH (NOLOCK) WHERE NO_CDE = :id";
        }
        $stmt = $this->pdo->prepare($req);
        $stmt->execute(['id' => $id]);
        $rows = $stmt->fetchAll(\PDO::FETCH_COLUMN);
        
        return ['MNT_BASE' => $rows[0] ?? null];
    }

    /**
     * Summary of getLastNoPo
     * @param string|int $cde
     * @param string|int $tp
     * @return array<string, mixed>
     */
    public function getLastNoPo(
        string|int $cde,
        string|int $tp
    ): array {
        $req = "SELECT ISNULL(MAX(NUM_PO_CD), 0) + 1 AS NEXT_NO_PO
                FROM TP_POS
                WHERE NUM_CDE = :cde AND NUM_TP = :tp";
        return $this->fetchAssocOne($req, [
            'cde' => (int) $cde,
            'tp' => (int) $tp
        ]);
    }

    /**
     * Summary of verifPourcTp
     * @param mixed $id
     * @param mixed $numTP
     * @param mixed $module
     * @param mixed $noPoCde
     * @param string $critereRegroup
     * @return string[][]
     */
    public function verifPourcTp($id, $numTP, $module, $noPoCde, $critereRegroup = ''): array
    {
        $req = "SELECT p.NUM_CDE AS NO_CDE, p.NUM_PO_CD AS NO_PO,
            p.TYPEOBJ AS TYPEOBJ,
            SUM(e.POURC_TP) AS total_pourc_tp,
            100 - SUM(e.POURC_TP) AS pourcentage_manquant
            FROM TP_POS p
            INNER JOIN TP_ENT e
                ON e.NUM_TP = p.NUM_TP
                AND e.NUM_TP = :numTP
                AND e.NUM_CDE = p.NUM_CDE
                AND e.TYPEOBJ = p.TYPEOBJ
                AND e.CRITERE_REGROUP = p.CRITERE_REGROUP
            WHERE p.NUM_CDE = :idCommande
            AND p.NUM_PO_CD = :noPoCde
            AND p.TYPEOBJ = :module
            AND p.CRITERE_REGROUP = :critereRegroup
            GROUP BY p.NUM_CDE, p.NUM_PO_CD, p.TYPEOBJ;";

        return $this->fetchAssocAll($req, [
            ':idCommande' => (int) $id,
            ':numTP' => (int)$numTP,
            ':module' => (string)$module,
            ':noPoCde' => (int) $noPoCde,
            ':critereRegroup' => $critereRegroup
        ]);
    }

    /**
     * Vérifie que le POURC_TP atteint 100% pour un produit donné (NUM_PO_CD) d'un TP.
     * @param mixed $numCde
     * @param mixed $typeObj
     * @param mixed $numPoCde
     * @param mixed $critereRegroup
     * @return bool
     */
    public function verifTermesDePaiement($numCde, $typeObj, $numPoCde, $critereRegroup = ''): bool
    {
        $req  = "SELECT
                    p.NUM_PO_CD,
                    SUM(e.POURC_TP) as POURC_TP
                FROM TP_POS p
                INNER JOIN TP_ENT e
                    ON e.NUM_TP = p.NUM_TP
                    AND e.NUM_CDE = p.NUM_CDE
                    AND e.TYPEOBJ = p.TYPEOBJ
                    AND e.CRITERE_REGROUP = p.CRITERE_REGROUP
                WHERE p.NUM_CDE = :numCde
                AND p.TYPEOBJ = :typeObj
                AND p.NUM_PO_CD = :numPoCde
                AND p.CRITERE_REGROUP = :critere_regroup
                GROUP BY p.NUM_CDE, p.NUM_PO_CD, p.TYPEOBJ
                ";
        $stmt = $this->pdo->prepare($req);
        $stmt->execute([
            'numCde' => $numCde,
            'typeObj' => $typeObj,
            'numPoCde' => $numPoCde,
            'critere_regroup' => $critereRegroup
        ]);
        $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC);
 
        if (count($rows) == 0) {
            return false;
        }
 
        foreach ($rows as $row) {
            if ((int) $row['POURC_TP'] != 100) {
                return false;
            }
        }
        return true;
    }
}
