<?php

namespace Modules\Stock\Repository;

use Exception;
use Helpers\Outil\Roles;
use Helpers\Pagination\PaginatorGestion;
use Helpers\QueryBuilder\AbsRepository;
use Modules\Stock\Interface\EnteteRepoInterface;
use Modules\Stock\Service\RepoService;
use Modules\Stock\Trait\Repo\EnteteRepoTrait;

class DemandeServiceRepo extends AbsRepository implements EnteteRepoInterface
{
    use EnteteRepoTrait;

    /**
     * @param array<string,string> $post
     * @return array<int,array<string,string|int>>
     * @throws Exception
     */
    public function listDemandesServices(array $post): array
    {
        $arrayReq = [];

        $req = "
            SELECT DISTINCT
	            SE.NO_DS AS NO_DS,
	            SE.CODE_STE AS CODE_STE,
	            SE.DTE_CREAT AS DTE_CREAT,
	            SE.TYPE_DEM AS TYPE_DEM,
	            M.CONTMEMO AS COMMENTAIRE,
	            SE.SOLDE_DS AS SOLDE_DS,
	            SE.DTE_VALID AS DTE_VALID
            FROM SDS_ENTE SE with (nolock)
            LEFT JOIN SDS_LIGN SL ON SE.NO_DS = SL.NO_DS
            LEFT JOIN MEMOS M ON SE.NM_COMME = M.NODEMEMO
        ";

        $parameters = $this->buildQuery($post, $req, $arrayReq);
        $paginatorGestion = new PaginatorGestion();
        $paginatorGestion->injecterSqlCount($req, $parameters, $arrayReq, $this->pdo);

        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * @param array<string,string> $post
     * @return array<int,array<string,string|int>>
     * @throws Exception
     */
    public function listDemandesServicesExport(array $post, string $listeSelect): array
    {
        $arrayReq = [];
        $req = "SELECT DISTINCT "
            . $listeSelect
            . " FROM SDS_ENTE SE with (nolock) "
            . "LEFT JOIN SDS_LIGN SL ON SE.NO_DS = SL.NO_DS "
            . "LEFT JOIN MEMOS M ON SE.NM_COMME = M.NODEMEMO ";

        $this->buildQuery($post, $req, $arrayReq);

        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * @param int $noDs
     * @return array<string,mixed>
     */
    public function findDemandeService(int $noDs): array
    {
        $requete = "SELECT DISTINCT
                        SE.NO_DS AS NO_DS,
                        SE.DTE_CREAT AS DTE_CREAT,
                        SE.TYPE_DEM AS TYPE_DEM,
                        SE.TIERS AS TIERS,
                        SE.SITE AS SITE,
                        SE.NM_COMME AS NM_COMME,
                        SE.SOLDE_DS AS SOLDE_DS,
                        SE.DTE_VALID AS DTE_VALID,
                        SE.CODE_STE AS CODE_STE,
                        SO.DESIGN AS SO_DESIGN,
                        ME.CONTMEMO AS COMMENTAIRE,
                        DESTINATAIRES.DESIGN AS DEST_DESIGN,
                        SOC.RAIS_SOC AS SOC_DESIGN
                    FROM SDS_ENTE SE
                    INNER JOIN SSITESTO SO ON SE.SITE = SO.SITE
                    LEFT JOIN MEMOS ME ON ME.NODEMEMO = SE.NM_COMME
                    LEFT JOIN (
                        SELECT CODE, RS AS DESIGN FROM TIERS
                        UNION
                        SELECT SITE AS CODE, DESIGN FROM SSITESTO
                    ) AS DESTINATAIRES ON SE.TIERS = DESTINATAIRES.CODE
                    INNER JOIN SOCIETE SOC ON SE.CODE_STE = SOC.CODE_STE WHERE SE.NO_DS = :noDs
        ";

        return $this->fetchAssocOne($requete, [':noDs' => $noDs]);
    }

    /**
     * @param int|string $noDs
     * @return array<string,mixed>
     */
    public function findFromNoDs(int|string $noDs): array
    {
        $requete = "SELECT DISTINCT SE.*, HA.CODE AS DEM_CODE, HA.NOM AS DEM_NOM, SL.NO_DA AS NO_DA FROM SDS_ENTE SE with (nolock)
                    LEFT JOIN SDS_LIGN SL ON SE.NO_DS = SL.NO_DS
                    LEFT JOIN DA_ENTET DA ON SL.NO_DA = DA.NO_DA
                    LEFT JOIN HASTUTIL HA ON DA.NO_DEM = HA.CODE
                    WHERE SE.NO_DS = :noDs";

        return $this->fetchAssocOne($requete, [':noDs' => $noDs]);
    }

    /**
     * @param array<string,string> $post
     * @param array<string,string> $arrayReq
     * @return array<string,mixed>
     * @throws Exception
     */
    private function buildQuery(array &$post, string &$req, array &$arrayReq): array
    {
        $repoService = new RepoService();
        $repoService->addMotCleQueryShard($post, $req, $arrayReq);
        $req .= " WHERE 1=1 ";
        $req .= Roles::getClausePerimetreSociete('SE.CODE_STE', $arrayReq);
        $repoService->addEmptyTiersQueryShard($post, $req, 'SE.TIERS');

        return $repoService->buildQuery($post, $req, $arrayReq, 'SDS_ENTE');
    }
}
