<?php

namespace Modules\Delegation\Repository;

use Helpers\QueryBuilder\AbsRepository;

class Delegation extends AbsRepository
{
    /**
     * getAbsenceDelegations
     *
     * @param  array<mixed> $post
     * @return array<mixed>
     */
    public function getAbsenceDelegations(array $post = ['tri_DTE_DEB' => 'desc']): array
    {
        $query = "SELECT SIG_DELEG.SIGNAT, SIG_DELEG.DELEG, SIG_DELEG.DTE_DEB,
        SIG_DELEG.DTE_FIN, HASTUTIL.NOM
		FROM SIG_DELEG WITH (NOLOCK)
            LEFT JOIN HASTUTIL WITH (NOLOCK) ON HASTUTIL.CODE = SIG_DELEG.DELEG
		WHERE SIGNAT = :SIGNAT";

        $params = [
            ':SIGNAT' => $_SESSION['Session_Login']
        ];

        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1);
        if (count($whereArray) >  0) {
            $query .= $whereArray[0] . $whereArray['tri'];

            if (count($whereArray[1]) != 0) {
                $params = array_merge($params, $whereArray[1]);
            }
        }

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

    /**
     * getPermanentDelegations
     *
     * @param  array<mixed> $post
     * @return array<mixed>
     */
    public function getPermanentDelegations(array $post = []): array
    {
        $query = "SELECT  WF_DEL_ENT.CODE_SIGN, 
            WF_DEL_ENT.CODE_DEL, 
            ISNULL(WF_DEL_ENT.CODE_STE, '') AS CODE_STE, 
            WF_DEL_ENT.MONTANT, 
            WF_DEL_ENT.TYPE_DEL, 
            WF_DEL_ENT.TOUS,
            a.NOM NOM_DEL,
            b.NOM NOM_SIGN,
            SOCIETE.RAIS_SOC
        FROM WF_DEL_ENT WITH(NOLOCK) 
            LEFT OUTER JOIN HASTUTIL a WITH(NOLOCK) ON a.CODE = WF_DEL_ENT.CODE_DEL
            LEFT OUTER JOIN HASTUTIL b WITH(NOLOCK) ON b.CODE = WF_DEL_ENT.CODE_SIGN
            LEFT OUTER JOIN SOCIETE    WITH(NOLOCK) ON SOCIETE.CODE_STE = WF_DEL_ENT.CODE_STE
        WHERE CODE_SIGN = :SIGNAT";

        $params = [
            ':SIGNAT' => $_SESSION['Session_Login']
        ];

        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1);
        if (count($whereArray) >  0) {
            $query .= $whereArray[0] . $whereArray['tri'];

            if (count($whereArray[1]) != 0) {
                $params = array_merge($params, $whereArray[1]);
            }
        }

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

    /**
     * getPermanentDelegationsByRoles
     *
     * @param  string $deleg
     * @return array<mixed>
     */
    public function getPermanentDelegationsByRoles(string $deleg): array
    {
        $query = "SELECT wdr.CODE_SIGN, 
                wdr.CODE_DEL, 
                wdr.NUM_WF, 
                wdr.NUM_ROLE, 
                wdr.TYPE_ENT, 
                wre.NOM_PAR,
                wrl.LIB_ROLE
			FROM WF_DEL_ROL wdr WITH (NOLOCK)
				LEFT JOIN WF_ROL_ENT wre WITH (NOLOCK) ON wre.ID_PAR = wdr.NUM_WF
				LEFT JOIN WF_ROL_LIG wrl WITH (NOLOCK) ON wrl.ID_ROLE = wdr.NUM_ROLE 
					AND wrl.ID_PAR = wre.ID_PAR
			WHERE CODE_SIGN = :SIGN
				AND wdr.CODE_DEL = :DELEG";

        $params = [
            ':SIGN' => $_SESSION['Session_Login'],
            ':DELEG' => $deleg,
        ];

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

    /**
     * getRolesByDeleg retourne la liste exhaustive des rôles d'une délégation permanente
     * en précisant lesquels sont activés pour le délégataire choisi
     *
     * @param  string $deleg
     * @return array<mixed>
     */
    public function getRolesByDeleg(string $deleg): array
    {
        $query = "SELECT ent.NOM_PAR, lig.ID_PAR, lig.ID_ROLE, del.CODE_SIGN, del.CODE_DEL, lig.LIB_ROLE,
                CASE
                    WHEN ISNULL(del.CODE_DEL, '') = ''
                    THEN 0
                    ELSE 1
                END AS IS_SELECTED
			FROM WF_ROL_LIG lig WITH (NOLOCK)
				LEFT JOIN WF_ROL_ENT ent WITH (NOLOCK) ON lig.ID_PAR = ent.ID_PAR
				LEFT JOIN WF_DEL_ROL del WITH (NOLOCK) ON del.NUM_WF = lig.ID_PAR 
					AND del.NUM_ROLE = lig.ID_ROLE
					AND del.CODE_SIGN = :SIGNAT 
                    AND del.CODE_DEL = :DELEG
            ORDER BY lig.ID_PAR, lig.ID_ROLE ";

        $params = [
            ':SIGNAT' => $_SESSION['Session_Login'],
            ':DELEG' => $deleg,
        ];

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

    /**
     * getPermanentDelegationsByDeclencheurs
     *
     * @param  string $deleg
     * @return array<mixed>
     */
    public function getPermanentDelegationsByDeclencheurs(string $deleg): array
    {
        $query = "SELECT CODE_SIGN, CODE_DEL, CS, NUM_CS, 
                CODE_TABLE, CODE_ENREG, OPERATEUR, TYPE_ENT
			FROM WF_DEL_DEC WITH(NOLOCK) 
			WHERE CODE_DEL = :DELEG 
				AND CODE_SIGN = :SIGN";

        $params = [
            ':SIGN' => $_SESSION['Session_Login'],
            ':DELEG' => $deleg,
        ];

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

    /**
     * getEarlierDelegationDebutDate
     *
     * @return array<mixed>
     */
    public function getEarlierDelegationDebutDate(): array
    {
        $query = "SELECT MAX(DTE_FIN) AS MAX
        FROM SIG_DELEG WITH(NOLOCK) 
        WHERE SIGNAT = :SIGNAT";

        $params = [
            ':SIGNAT' => $_SESSION['Session_Login'],
        ];

        return $this->fetchAssocOne($query, $params);
    }

    /**
     * checkforOverlap
     *
     * @param  array<mixed> $post
     * @param  string $origineDebut
     * @return array<mixed>
     */
    public function checkforOverlap(array $post, string $origineDebut): array
    {
        $query = "DECLARE 
            @v_dte_deb CHAR(8) = '00000000',
            @v_dte_fin CHAR(8) = '00000000'

        SET @v_dte_deb = :DTE_DEB
        SET @v_dte_fin = :DTE_FIN

        SELECT DTE_DEB, DELEG, ha.NOM + ' (' + DELEG + ')' AS DELEG_ERROR
        FROM SIG_DELEG WITH (NOLOCK) 
            LEFT JOIN HASTUTIL ha ON ha.CODE = SIG_DELEG.DELEG
        WHERE 
            (
                (@v_dte_deb <= DTE_DEB 
                    AND DTE_DEB <= (CASE WHEN @v_dte_fin = '00000000' 
                        THEN '99999999' ELSE @v_dte_fin END)
                ) OR (@v_dte_deb <= (CASE WHEN DTE_FIN = '00000000' 
                    THEN '99999999' ELSE DTE_FIN END) 
                    AND (CASE WHEN DTE_FIN = '00000000' 
                    THEN '99999999' ELSE DTE_FIN END) <= 
                    (CASE WHEN @v_dte_fin = '00000000' 
                    THEN '99999999' ELSE @v_dte_fin END)
                ) OR (DTE_DEB < @v_dte_deb AND (CASE WHEN @v_dte_fin = '00000000' 
                    THEN '99999999' ELSE @v_dte_fin END) < (CASE WHEN DTE_FIN = '00000000' 
                    THEN '99999999' ELSE DTE_FIN END)
                )
            ) AND SIGNAT = :SIGNAT";

        $params = [
            ':SIGNAT' => $_SESSION['Session_Login'],
            ':DTE_DEB' => $post['DTE_DEB'],
            ':DTE_FIN' => $post['DTE_FIN'],
        ];

        if ($origineDebut != '') {
            $query .= " AND DTE_DEB <> :DTE_DEB_ORI ";
            $params[':DTE_DEB_ORI'] = $origineDebut;
        }

        return $this->fetchAssocOne($query, $params);
    }

    /**
     * multiInsertDeclencheurs
     *
     * @param  array<mixed> $declencheurs
     * @param  string $delegataire
     * @return bool
     */
    public function multiInsertDeclencheurs(array $declencheurs, string $delegataire): bool
    {
        $query = "DECLARE 
            @v_user VARCHAR(30) = '',
            @v_deleg VARCHAR(30) = ''

        SET @v_user = :user
        SET @v_deleg = :deleg

        INSERT INTO WF_DEL_DEC (CODE_SIGN, CODE_DEL, CODE_TABLE, CODE_ENREG, OPERATEUR)
        VALUES ";

        foreach ($declencheurs as $declencheur) {
            $query .= '(@v_user, @v_deleg, \''
                . $declencheur['CODE_TABLE'] . '\', \'' . $declencheur['CODE_ENREG'] . '\', \''
                . $declencheur['OPERATEUR'] . '\'), ';
        }
        $query = \rtrim($query, ', ');

        $params = [
            ':user' => $_SESSION['Session_Login'],
            ':deleg' => $delegataire,
        ];

        $multiInsert = $this->pdo->prepare($query);
        return $multiInsert->execute($params);
    }

    /**
     * multiInsertRoles
     *
     * @param  array<mixed> $roles
     * @param  string $delegataire
     * @return bool
     */
    public function multiInsertRoles(array $roles, string $delegataire): bool
    {
        $query = "DECLARE 
            @v_user VARCHAR(30) = '',
            @v_deleg VARCHAR(30) = ''

        SET @v_user = :user
        SET @v_deleg = :deleg

        INSERT INTO WF_DEL_ROL (CODE_SIGN, CODE_DEL, NUM_WF, NUM_ROLE)
        VALUES ";

        foreach ($roles as $role) {
            $query .= '(@v_user, @v_deleg, \''
                . $role['NUM_WF'] . '\', \'' . $role['NUM_ROLE'] . '\'), ';
        }
        $query = \rtrim($query, ', ');

        $params = [
            ':user' => $_SESSION['Session_Login'],
            ':deleg' => $delegataire,
        ];

        $multiInsert = $this->pdo->prepare($query);
        return $multiInsert->execute($params);
    }
}
