<?php

namespace Modules\Stock\Repository;

use DateTime;
use Helpers\QueryBuilder\AbsRepository;

class CumMensRepo extends AbsRepository
{
    /**
     * @param string $codeArt
     * @param string $site
     * @return array<int,array<string,string>>
     */
    public function getConso12DerniersMoisGlissants(string $codeArt, string $site): array
    {
        $requete = "DECLARE @start_yyyymm int =:unAnAvant;
                    DECLARE @end_yyyymm   int =:unMoisAvant;
                    
                    WITH bounds AS (
                      SELECT
                        StartDate = DATEFROMPARTS(@start_yyyymm/100, @start_yyyymm%100, 1),
                        EndDate   = DATEFROMPARTS(@end_yyyymm/100,   @end_yyyymm%100,   1)
                    ),
                    months AS (
                      SELECT StartDate AS MonthStart
                      FROM bounds
                      UNION ALL
                      SELECT DATEADD(MONTH, 1, MonthStart)
                      FROM months
                      JOIN bounds b ON DATEADD(MONTH, 1, MonthStart) <= b.EndDate
                    ),
                    data AS (
                      SELECT
                        yyyymm = ANNEE*100 + MOIS,
                        conso  = SUM(QTE_CUMUL)
                      FROM CUM_MENS WITH (NOLOCK)
                      WHERE SITE=:site
                        AND ART=:codeArt
                        AND (ANNEE*100 + MOIS) BETWEEN @start_yyyymm AND @end_yyyymm
                      GROUP BY ANNEE*100 + MOIS
                    )
                    SELECT
                      DATEVALEUR = CONVERT(int, FORMAT(m.MonthStart, 'yyyyMM')),
                      CONSO      = COALESCE(d.conso, 0)
                    FROM months m
                    LEFT JOIN data d
                      ON d.yyyymm = CONVERT(int, FORMAT(m.MonthStart, 'yyyyMM'))
                    ORDER BY DATEVALEUR
                    OPTION (MAXRECURSION 366);
                    ";

        $aujourdhui = new DateTime('now');
        $annee = (int) $aujourdhui->format('Y');
        $mois = (int) $aujourdhui->format('n');

        $unAnAvant = (($annee - 1) * 100 + $mois);
        $unMoisAvant = ($annee * 100 + ($mois - 1));

        $arrayReq = [
            ':site' => $site,
            ':codeArt' => $codeArt,
            ':unAnAvant' => $unAnAvant,
            ':unMoisAvant' => $unMoisAvant,
        ];

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

    public function getConsoMoisEnCours(string $codeArt, string $site): int
    {
        $requete = "SELECT QTE_CUMUL FROM CUM_MENS WHERE SITE=:site AND ART=:codeArt and ANNEE=:annee AND MOIS=:mois";

        $aujourdhui = new DateTime('now');
        $annee = (int) $aujourdhui->format('Y');
        $mois = (int) $aujourdhui->format('n');

        $arrayReq = [':site' => $site, ':codeArt' => $codeArt, ':annee' => $annee, ':mois' => $mois];

        return (int) $this->fetchAssocOne($requete, $arrayReq)['QTE_CUMUL'];
    }
}
