<?php

namespace Modules\Consultation\Repository;

use Helpers\QueryBuilder\AbsRepository;
use PDOStatement;

class Contributor extends AbsRepository
{
    /**
     * getContributorsByRfx
     * @param string $idAo
     * @param array<mixed> $filters
     * @return array<mixed>
     */
    public function getContributorsByRfx(string $idAo, array $filters = []): array
    {
        $query = "SELECT ac.CONTRIB, ha.NOM, ha.CODE_STE, ha.FONCTION
            FROM AO_CONTRIB ac with (nolock) 
                LEFT JOIN HASTUTIL ha with (nolock) ON ha.CODE = ac.CONTRIB
            WHERE ac.NO_AO = :NO_AO";

        $params = [
            ':NO_AO' => $idAo
        ];

        if (!empty($filters)) {
            if ($filters['filter_NOM'] != '') {
                $query .= " AND UPPER(ha.NOM) LIKE UPPER(:NOM) ";
                $params[':NOM'] = '%' . $filters['filter_NOM'] . '%';
            }
            if ($filters['filter_CODE_STE'] != '') {
                $query .= " AND UPPER(ha.CODE_STE) LIKE UPPER(:CODE_STE) ";
                $params[':CODE_STE'] = '%' . $filters['filter_CODE_STE'] . '%';
            }
            if ($filters['filter_FONCTION'] != '') {
                $query .= " AND UPPER(ha.FONCTION) LIKE UPPER(:FONCTION) ";
                $params[':FONCTION'] = '%' . $filters['filter_FONCTION'] . '%';
            }
        }

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

    /**
     * getContributorsByGroupByRfx
     * @param string $idAo
     * @return array<mixed>
     */
    public function getContributorsByGroupByRfx(string $idAo): array
    {
        $query = "SELECT acg.CONTRIB, acg.GROUPE_CONTRIB, ha.NOM, ha.CODE_STE
            FROM AO_CONTRIB_GRP acg with (nolock) 
                LEFT JOIN HASTUTIL ha with (nolock) ON ha.CODE = acg.CONTRIB
            WHERE acg.NO_AO = :NO_AO";

        $params = [
            ':NO_AO' => $idAo
        ];

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

    /**
     * getContributorsNotInRfx
     * @param string $idAo
     * @param array<mixed> $post
     * @return array<mixed>
     */
    public function getContributorsNotInRfx(string $idAo, array $post = []): array
    {
        $today = date('Ymd');
        $query = "SELECT H.CODE, H.NOM, H.FONCTION, H.EMAIL, H.CODE_STE
                FROM HASTUTIL H WITH (NOLOCK)
                    LEFT JOIN AO_ENTET ae WITH (NOLOCK) ON ae.NO_AO = :NO_AO
                WHERE DTE_FERM = '00000000'
                    AND H.CODE NOT IN
                        (SELECT H2.CODE
                        FROM HASTUTIL H2 WITH (NOLOCK)
                            LEFT JOIN AO_CONTRIB ac WITH (NOLOCK)
                                ON ac.CONTRIB = H.CODE
                        WHERE ac.NO_AO = ae.NO_AO)
                    AND (H.UTIMULTI = 0x54 OR EXISTS (
                        SELECT SOCIETE.CODE_STE
                        FROM SOCIETE WITH (NOLOCK)
                        LEFT JOIN SOC_USR WITH (NOLOCK) ON SOC_USR.COD_STE = SOCIETE.CODE_STE AND SOC_USR.COD_USR = H.CODE
                        LEFT JOIN HASTUTIL WITH (NOLOCK) ON HASTUTIL.CODE_STE = SOCIETE.CODE_STE AND HASTUTIL.CODE = H.CODE
                        WHERE (SOC_USR.COD_USR IS NOT NULL OR HASTUTIL.CODE IS NOT NULL)
                            AND (SOCIETE.DTEVALMIN = '00000000' OR SOCIETE.DTEVALMIN <= '{$today}')
                            AND (SOCIETE.DTEVALMAX = '00000000' OR SOCIETE.DTEVALMAX >= '{$today}')
                            AND SOCIETE.CODE_STE IN (
                        SELECT CODE_STE 
                        FROM SOCIETE WITH(NOLOCK) 
                        WHERE 
                            CASE
                                WHEN ae.TYPE_SG = 'S'
                                THEN CODE_STE
                                ELSE GROUPE
                            END = ae.CODE_STE
                        )
                    )
                )";

        $params = [
            ':NO_AO' => $idAo
        ];

        $query = $this->applyFilter($query, $params, $post);

        $paginatorGestion = new \Helpers\Pagination\PaginatorGestion();
        $paginatorGestion->injecterSqlCount($query, $post, $params, $this->pdo, false);

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

    /**
     * addContributorsToRfx
     * @param string $idAo
     * @param array<mixed> $contributors
     * @return PDOStatement
     */
    public function addContributorsToRfx(string $idAo, array $contributors): PDOStatement
    {
        $query = "INSERT INTO AO_CONTRIB (NO_AO, CONTRIB)
        VALUES (";
        
        $params = [];
        $i = 1;

        foreach ($contributors as $contributor) {
            $query .= ":NO_AO$i, :CONTRIB$i), (";
            $params[":CONTRIB$i"] = $contributor;
            $params[":NO_AO$i"] = $idAo;
            $i++;
        }
        
        $query = \rtrim($query, ', (');

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

    /**
     * addRaterToGroup
     * @param string $idAo
     * @param string $group
     * @param array<mixed> $contributors
     * @return PDOStatement
     */
    public function addRaterToGroup(string $idAo, string $group, array $contributors): PDOStatement
    {
        $query = "INSERT INTO AO_CONTRIB_GRP (NO_AO, GROUPE_CONTRIB, CONTRIB)
        VALUES (";
        $i = 1;
        $params = [];

        foreach ($contributors as $contributor) {
            $query .= ":NO_AO$i, :GROUPE_CONTRIB$i, :CONTRIB$i), (";
            $params[":GROUPE_CONTRIB$i"] = $group;
            $params[":CONTRIB$i"] = $contributor;
            $params[":NO_AO$i"] = $idAo;
            $i++;
        }
        
        $query = \rtrim($query, ', (');

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

    /**
     * getRfxContributorsForGroup
     * @param string $idAo
     * @param string $group
     * @return array<mixed>
     */
    public function getRfxContributorsForGroup(string $idAo, string $group): array
    {
        $query = "SELECT H.CODE, H.NOM, H.EMAIL
                FROM AO_CONTRIB ac WITH (NOLOCK)
                    LEFT JOIN HASTUTIL H WITH (NOLOCK)
                        ON H.CODE = ac.CONTRIB
                WHERE ac.NO_AO = :NO_AO1
                    AND DTE_FERM = '00000000'
                    AND H.CODE NOT IN
                (SELECT acg.CONTRIB
                FROM AO_CONTRIB_GRP acg WITH (NOLOCK)
                WHERE acg.NO_AO = :NO_AO2 AND acg.GROUPE_CONTRIB = :GROUP)";

        $params = [
            ':NO_AO1' => $idAo,
            ':NO_AO2' => $idAo,
            ':GROUP' => $group
        ];

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

    /**
     * getAllRfxContributors retourne la liste complète des contributeurs sollicités sur une consultation
     * @param string $idAo
     * @return array<mixed>
     */
    public function getAllRfxContributors(string $idAo): array
    {
        $query = "SELECT DISTINCT 
            CASE 
                WHEN aqt.PROFNOTEUR1 = 'G' 
                THEN acg.CONTRIB 
                WHEN aqt.PROFNOTEUR1 = 'N'
                THEN aqt.PROFNOTEUR2
                WHEN aqt.PROFNOTEUR1 = ''
                THEN ac.CONTRIB 
            END AS CONTRIB, ha.NOM, ha.FONCTION, ha.EMAIL, ha.COD_LNG, ha.TEL, ISNULL(arq.TERMINE, 0x46)
        FROM AO_QST_TH aqt WITH (NOLOCK)
            LEFT JOIN AO_CONTRIB_GRP acg WITH (NOLOCK) ON acg.NO_AO = aqt.NO_AO AND acg.GROUPE_CONTRIB = aqt.PROFNOTEUR2 AND aqt.PROFNOTEUR1 = 'G'
            LEFT JOIN AO_CONTRIB ac WITH (NOLOCK) ON ac.NO_AO = aqt.NO_AO AND aqt.PROFNOTEUR1 = ''
            LEFT JOIN AO_REP_QST arq WITH (NOLOCK) ON arq.NO_AO = aqt.NO_AO AND arq.ID_THEME = aqt.ID_THEME AND arq.ID_THEME = aqt.ID_THEME
                AND arq.CONTRIB = CASE 
                    WHEN aqt.PROFNOTEUR1 = 'G' 
                    THEN acg.CONTRIB 
                    WHEN aqt.PROFNOTEUR1 = 'N'
                    THEN aqt.PROFNOTEUR2
                    WHEN aqt.PROFNOTEUR1 = ''
                    THEN ac.CONTRIB 
                END
            LEFT JOIN HASTUTIL ha WITH (NOLOCK) ON ha.CODE = CASE 
                WHEN aqt.PROFNOTEUR1 = 'G' 
                THEN acg.CONTRIB 
                WHEN aqt.PROFNOTEUR1 = 'N'
                THEN aqt.PROFNOTEUR2
                WHEN aqt.PROFNOTEUR1 = ''
                THEN ac.CONTRIB 
            END
        WHERE aqt.ID_THEME LIKE 'I%' AND aqt.NO_AO = :NO_AO";

        $params = [
            ':NO_AO' => $idAo,
        ];

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

    /**
     * isContributor retourne la liste complète des contributeurs sollicités sur une consultation
     * @param string $user
     * @return bool
     */
    public function isContributor(string $user): bool
    {
        $query = "SELECT DISTINCT 
            CASE 
                WHEN aqt.PROFNOTEUR1 = 'G' 
                THEN acg.CONTRIB 
                WHEN aqt.PROFNOTEUR1 = 'N'
                THEN aqt.PROFNOTEUR2
                WHEN aqt.PROFNOTEUR1 = ''
                THEN ac.CONTRIB 
            END AS CONTRIB, ha.NOM, ha.FONCTION, ha.EMAIL, ha.COD_LNG, ha.TEL, ISNULL(arq.TERMINE, 0x46)
        FROM AO_QST_TH aqt WITH (NOLOCK)
            LEFT JOIN AO_CONTRIB_GRP acg WITH (NOLOCK) ON acg.NO_AO = aqt.NO_AO AND acg.GROUPE_CONTRIB = aqt.PROFNOTEUR2 AND aqt.PROFNOTEUR1 = 'G'
            LEFT JOIN AO_CONTRIB ac WITH (NOLOCK) ON ac.NO_AO = aqt.NO_AO AND aqt.PROFNOTEUR1 = ''
            LEFT JOIN AO_REP_QST arq WITH (NOLOCK) ON arq.NO_AO = aqt.NO_AO AND arq.ID_THEME = aqt.ID_THEME AND arq.ID_THEME = aqt.ID_THEME
                AND arq.CONTRIB = CASE 
                    WHEN aqt.PROFNOTEUR1 = 'G' 
                    THEN acg.CONTRIB 
                    WHEN aqt.PROFNOTEUR1 = 'N'
                    THEN aqt.PROFNOTEUR2
                    WHEN aqt.PROFNOTEUR1 = ''
                    THEN ac.CONTRIB 
                END
            LEFT JOIN HASTUTIL ha WITH (NOLOCK) ON ha.CODE = CASE 
                WHEN aqt.PROFNOTEUR1 = 'G' 
                THEN acg.CONTRIB 
                WHEN aqt.PROFNOTEUR1 = 'N'
                THEN aqt.PROFNOTEUR2
                WHEN aqt.PROFNOTEUR1 = ''
                THEN ac.CONTRIB 
            END
        WHERE aqt.ID_THEME LIKE 'I%' AND :user = CASE WHEN aqt.PROFNOTEUR1 = 'G' 
                THEN acg.CONTRIB 
                WHEN aqt.PROFNOTEUR1 = 'N'
                THEN aqt.PROFNOTEUR2
                WHEN aqt.PROFNOTEUR1 = ''
                THEN ac.CONTRIB 
            END";

        $params = [
            ':user' => $user,
        ];

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