<?php

namespace Modules\Reception\Repository;

use Helpers\QueryBuilder\AbsRepository;
use PDO;

class Incident extends AbsRepository
{
    /**
     * __construct
     */
    public function __construct(PDO $pdo)
    {
        parent::__construct($pdo);
    }

    /**
     * saveIncident
     *
     * @param  array<mixed> $data
     * @return bool
     */
    public function saveIncident(array $data): bool
    {
        // on enregistre dans oxa_br_ligne avec no_ligne = 0 pour signifier qu'il s'agit de l'incident global du BR
        $query = "INSERT INTO oxa_br_ligne (login, no_ligne_cde, no_ligne_br, code_inc, comment_inc, is_incident)
        VALUES (:login, 0, 0, :code_inc, :comment_inc, 0x54) ";

        $params = [
            ':login'    => [
                'value'   => $_SESSION['Session_Login'],
                'option'  => PDO::PARAM_STR
            ],
            ':code_inc' => [
                'value'   => $data['TYPE'],
                'option'  => PDO::PARAM_STR
            ],
            ':comment_inc' => [
                'value'   => $data['COMMENTAIRE_INCIDENT'],
                'option'  => PDO::PARAM_STR
            ],
        ];

        $insert = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $insert->bindParam($key, $param['value'], $param['option']);
        }

        return $insert->execute();
    }

    /**
     * updateIncident
     *
     * @param  array<mixed> $data
     * @return bool
     */
    public function updateIncident(array $data)
    {
        $query = "UPDATE oxa_br_ligne
        SET code_inc = :code_inc, comment_inc = :comment_inc, is_incident = 0x54
        WHERE login = :login
            AND no_ligne_cde = 0
            AND no_ligne_br = 0 ";

        $params = [
            ':login'    => [
                'value'   => $_SESSION['Session_Login'],
                'option'  => PDO::PARAM_STR
            ],
            ':code_inc' => [
                'value'   => $data['TYPE'],
                'option'  => PDO::PARAM_STR
            ],
            ':comment_inc' => [
                'value'   => $data['COMMENTAIRE_INCIDENT'],
                'option'  => PDO::PARAM_STR
            ],
        ];

        $insert = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $insert->bindParam($key, $param['value'], $param['option']);
        }

        return $insert->execute();
    }

    public function deleteIncident(): bool
    {
        $query = "DELETE FROM oxa_br_ligne
        WHERE login = :login
            AND no_ligne_br = 0
            AND no_ligne_cde = 0";
        $params = [
            ':login'    => [
                'value'   => $_SESSION['Session_Login'],
                'option'  => PDO::PARAM_STR
            ],
        ];

        $delete = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $delete->bindParam($key, $param['value'], $param['option']);
        }

        return $delete->execute();
    }

    /**
     * getIncident
     *
     * @return array<mixed>
     */
    public function getIncident(): array
    {
        $query = "SELECT code_inc AS TYPE, comment_inc AS COMMENTAIRE_INCIDENT
        FROM oxa_br_ligne WITH (NOLOCK)
        WHERE login = :login
            AND no_ligne_br = 0";

        $params = [
            ':login'   => [
                'value'     => $_SESSION['Session_Login'],
                'option'    => PDO::PARAM_STR
            ],
        ];

        $incident = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $incident->bindParam($key, $param['value'], $param['option']);
        }

        $incident->execute();

        $return = $incident->fetchAll($this->pdo::FETCH_ASSOC);
        if ($return) {
            return $return;
        } else {
            return [];
        }
    }

    /**
     * getIncidentLines
     *
     * @param  string $order
     * @return array<mixed>
     */
    public function getIncidentLines(string $order): array
    {
        $query = "SELECT obl.no_ligne_cde, obl.code_art, obl.design_sommaire, obl.is_incident, obl.code_inc, obl.comment_inc, u.LIBELLE AS unite_label, obl.unite, obl.qte_r - obl.qte_a AS ecart_quantite
        FROM oxa_br_ligne obl with(nolock)
            LEFT JOIN CA_LIGNE cal with(nolock) ON cal.NO_PO = obl.no_ligne_cde
            LEFT JOIN UNITES u with(nolock)
                ON u.CODE_UNI = obl.unite
        WHERE obl.login = :login
            AND cal.NO_CDE = :NO_CDE
            AND obl.qte_r - obl.qte_a > 0
			AND cal.CTRL_BR = ''
        ";

        $params = [
            ':login'   => [
                'value'     => $_SESSION['Session_Login'],
                'option'    => PDO::PARAM_STR
            ],
            ':NO_CDE'   => [
                'value'     => $order,
                'option'    => PDO::PARAM_INT
            ],
        ];

        $update = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $update->bindParam($key, $param['value'], $param['option']);
        }

        $update->execute();

        $return = $update->fetchAll($this->pdo::FETCH_ASSOC);
        if ($return) {
            return $return;
        } else {
            return [];
        }
    }

    /**
     * getIncidentLinesForValidation
     *
     * @return array<mixed>
     */
    public function getIncidentLinesForValidation(): array
    {
        $query = "SELECT obl.no_ligne_cde, obl.code_art, obl.design_sommaire, obl.is_incident, obl.code_inc, obl.comment_inc, u.LIBELLE AS unite_label, obl.unite, obl.qte_r - obl.qte_a AS ecart_quantite
        FROM oxa_br_ligne obl with(nolock)
            LEFT JOIN oxa_br_entete obe with(nolock) ON obe.login = obl.login
            LEFT JOIN CA_LIGNE cal with(nolock) ON cal.NO_PO = obl.no_ligne_cde AND cal.NO_CDE = obe.no_cde
            LEFT JOIN UNITES u with(nolock) ON u.CODE_UNI = obl.unite
        WHERE obl.login = :login
            AND obl.is_incident = 0x54
        ";

        $params = [
            ':login'   => [
                'value'     => $_SESSION['Session_Login'],
                'option'    => PDO::PARAM_STR
            ],
        ];

        $data = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $data->bindParam($key, $param['value'], $param['option']);
        }

        $data->execute();

        $return = $data->fetchAll($this->pdo::FETCH_ASSOC);
        if ($return) {
            return $return;
        } else {
            return [];
        }
    }

    /**
     * setSupplierIncident
     *
     */
    public function setSupplierIncident(): void
    {
        $query = "INSERT INTO FOURNINC (
            FOU, NIVEAU, DTE_OCCUR, CODE, NO_CDE, PO_CDE, NO_BR, 
            PO_BR, ART, DES_ART, QTE_REFU, DTE_LIV, DTE_CREAT, NM_COM, INCF_USER
        )
        SELECT  cae.CODE_FOU, 
            CASE 
				WHEN i.NIVEAU = null
				then i.NIVEAU
				else 0
			END as NIVEAU,
            obe.date_bl, obl.code_inc, obe.no_cde, obl.no_ligne_cde, obe.no_br, obl.no_ligne_br,
            CASE
                WHEN obl.no_ligne_br <> 0
                THEN obl.code_art
                ELSE ''
            END AS ART,
            CASE
                WHEN obl.no_ligne_br <> 0
                THEN obl.design_sommaire
                ELSE ''
            END AS DES_ART,
            CASE
                WHEN obl.no_ligne_br <> 0
                THEN
                    CASE
                        WHEN obl.is_ctrl_qualite = 0x54 AND obl.ctrl_qualite_done = 0x46
                        THEN (obl.qte_r - obl.qte_c)
                        ELSE (obl.qte_r - obl.qte_a)
                    END
                ELSE 0
            END AS QTE_REFU, obe.date_bl, cae.DTE_CREAT, 
            CASE
                WHEN obl.comment_inc <> ''
                THEN (SELECT TOP 1 ISNULL(NODEMEMO, 0) FROM MEMOS with(nolock) WHERE CONTMEMO = obl.comment_inc ORDER BY NODEMEMO DESC)
                ELSE 0
            END AS NM_COM, obl.login
        FROM oxa_br_ligne obl with(nolock)
            LEFT JOIN oxa_br_entete obe with(nolock)
                ON obe.login = obl.login
            LEFT JOIN CA_ENTET cae with(nolock)
                ON cae.NO_CDE = obe.no_cde
            LEFT JOIN INCIDENT i with(nolock)
                ON i.COD_INC = obl.code_inc
        WHERE obl.login = :login and is_incident = 0x54
        ";

        $this->execute($query, [':login' => $_SESSION['Session_Login']]);
    }

    /**
     * setXtEvent
     *
     * @param  int $id
     * @return bool
     */
    public function setXtEvent(int $id): bool
    {
        $query = "INSERT INTO XT_EVE (DATEEVT, HEUREEVT, TYPEOBJ, ID_1)
        SELECT :today, :hour, 'FOURNINC', IDUNIQUE
        FROM FOURNINC WITH (NOLOCK)
        WHERE NO_BR = :NO_BR";

        $insert = $this->pdo->prepare($query);

        $params = [
            ':today'   => [
                'option'    => PDO::PARAM_STR,
                'value'     => \date('Ymd')
            ],
            ':hour'   => [
                'option'    => PDO::PARAM_STR,
                'value'     => \date('His')
            ],
            ':NO_BR'   => [
                'option'    => PDO::PARAM_INT,
                'value'     => $id
            ]
        ];

        foreach ($params as $key => $param) {
            $insert->bindParam($key, $param['value'], $param['option']);
        }

        return $insert->execute();
    }

    /**
     * getIncMailInfo
     *
     * @return array<mixed>
     */
    public function getIncMailInfo(string $idBr = ''): array
    {
        $query = "SELECT FOURNINC.NO_CDE, FOU AS CODE_FOU, PO_CDE AS NO_PO_CDE, NO_BR AS NUM_BR, PO_BR AS NO_PO_BR, i.LIBELLE AS TYP_INC, cat.LIBELLE AS CAT_INC, QTE_REFU AS QTE_REFUS, DES_ART AS DESIGN_ART
		FROM FOURNINC WITH (NOLOCK)
			LEFT JOIN INCIDENT i with(nolock) ON i.COD_INC = CODE
			LEFT JOIN CATINCID cat with(nolock) ON cat.CODE_CAT = i.CODE_CAT
			LEFT JOIN oxa_br_entete obe with(nolock) ON obe.no_br = FOURNINC.NO_BR AND obe.no_cde = FOURNINC.NO_CDE
		WHERE obe.login = :login
        ";

        if ($idBr) {
        } else {
        }

        $params = [
            ':login' =>  [
                'value'     => $_SESSION['Session_Login'],
                'option'    => PDO::PARAM_STR
            ],
        ];

        $data = $this->pdo->prepare($query);

        foreach ($params as $key => $param) {
            $data->bindParam($key, $param['value'], $param['option']);
        }
        
        $data->execute();
        $data = $data->fetch(PDO::FETCH_ASSOC);
        return $data ?? [];
    }
}
