<?php

namespace Modules\Stock\Repository;

use DateTime;
use Helpers\QueryBuilder\AbsRepository;

class CumulAnnuelRepo extends AbsRepository
{
    /**
     * @param string $codeArt
     * @param string $site
     * @return array<string>[]
     */
    public function getConso3DernieresAnnees(string $codeArt, string $site): array
    {
        $requete = "DECLARE @site nvarchar(50)      = :site;
                    DECLARE @codeArt nvarchar(50)   = :codeArt;
                    DECLARE @troisAnsAvant int      = :troisAnsAvant;
                    DECLARE @anneeCourante int      = :anneeCourante;
                    
                    WITH years AS (
                        SELECT @troisAnsAvant AS ANNEE
                        UNION ALL
                        SELECT ANNEE + 1
                        FROM years
                        WHERE ANNEE + 1 <= @anneeCourante
                    ),
                    data AS (
                        SELECT
                            ANNEE,
                            CONSO = SUM(QTE)
                        FROM CUM_ANNU
                        WHERE SITE = @site
                          AND ART  = @codeArt
                          AND ANNEE BETWEEN @troisAnsAvant AND @anneeCourante
                        GROUP BY ANNEE
                    )
                    SELECT
                        y.ANNEE,
                        CONSO = COALESCE(d.CONSO, 0)
                    FROM years y
                    LEFT JOIN data d ON d.ANNEE = y.ANNEE
                    ORDER BY y.ANNEE
                    OPTION (MAXRECURSION 10);";

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

        $arrayReq = [
            ':site' => $site,
            ':codeArt' => $codeArt,
            ':troisAnsAvant' => $annee - 2,
            ':anneeCourante' => $annee,
        ];

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