<?php

namespace Modules\Consultation\Repository;

use Helpers\QueryBuilder\AbsRepository;
use PDOStatement;

class Contributor extends AbsRepository
{
    /**
     * getContributorsByRfx
     * @param string $idAo
     * @return array<mixed>
     */
    public function getContributorsByRfx(string $idAo): 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
        ];

        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
    {
        $query = "SELECT H.CODE, H.NOM, H.FONCTION, H.EMAIL
                FROM HASTUTIL H WITH (NOLOCK)
                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 = :NO_AO)";

        $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);
    }
}
