<?php

namespace Modules\Stock\Service;

use DateMalformedStringException;
use DateTime;
use Entity\Model\CsChps;
use Entity\Model\SSiteSto;
use Exception;
use Helpers\ChampsSupplementaire\ChampSupp;
use Helpers\Trad\ITrad;
use Modules\Stock\Enum\LigneImportUniverselOrigineEnum;
use Modules\Stock\Enum\LigneImportUniverselTypeEnum;
use Modules\Stock\Enum\TypeEnteteEnum;
use Modules\Stock\Helper\CsHelper;
use Modules\Stock\Service\Form\DemandeServiceFormService;
use Modules\Stock\Service\Form\LigneDemandeServiceFormService;
use PDO;
use PhpOffice\PhpSpreadsheet\Cell\Cell;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class ImportDemandeService
{
    private CsChps $csChps;
    private Worksheet $sheet;

    /**
     * @var array<string,string>
     */
    public array $parametreNumerotation;
    private LigneDemandeServiceFormService $ligneDemandeServiceFormService;

    /**
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     */
    public function __construct(string $fileName, private readonly PDO $pdo, private readonly ITrad $trad)
    {
        $this->csChps = new CsChps($pdo);
        $this->sheet = IOFactory::load($fileName)->getSheet(0);
        $this->ligneDemandeServiceFormService = new LigneDemandeServiceFormService($pdo);
    }

    /**
     * @param array<string,mixed> $lignesImportUniversel
     * @param array<string,string> $csParameters
     * @return array<string,mixed>
     * @throws DateMalformedStringException
     * @throws Exception
     */
    public function createDemandeService(array $lignesImportUniversel, array &$csParameters): array
    {
        $lignesImportUniverselEntete = array_filter($lignesImportUniversel, fn ($ligneImportUniversel) => $ligneImportUniversel['ORIGINE'] === LigneImportUniverselOrigineEnum::E->name);
        $lignesImportUniverselCSEntete = array_filter($lignesImportUniversel, fn ($ligneImportUniversel) => $ligneImportUniversel['ORIGINE'] === LigneImportUniverselOrigineEnum::ES->name);

        $demandeService = [];
        foreach ($lignesImportUniverselEntete as $ligneImportUniverselEntete) {
            $cell = $this->sheet->getCell(Coordinate::stringFromColumnIndex($ligneImportUniverselEntete['COLONNE']) . $ligneImportUniverselEntete['LIGNE']);
            $demandeService[$ligneImportUniverselEntete['COD_INT']] = self::convertCellValue($ligneImportUniverselEntete, $cell->getFormattedValue());
        }

        $commentaire = $demandeService['COMMENT'];
        $siteStock = (new SSiteSto($this->pdo))->find(['SITE' => $demandeService['SITE']]);
        $this->parametreNumerotation = (new ParametrageService($this->pdo))->getParametreNumerotation(TypeEnteteEnum::DS, $siteStock['CODE_STE']);
        $demandeService['NO_DS'] = $demandeService['NO_DS'] === 0 ? "": $demandeService['NO_DS'];
        $demandeService = (new DemandeServiceFormService($this->pdo))->initEntity($demandeService, ['SITE_ORIGINE' => $siteStock, 'PARAMETRE_NUMEROTATION' => $this->parametreNumerotation]);
        $demandeService['COMMENTAIRE'] = $commentaire;

        foreach ($lignesImportUniverselCSEntete as $ligneImportUniverselCSEntete) {
            $cell = $this->sheet->getCell(Coordinate::stringFromColumnIndex($ligneImportUniverselCSEntete['COLONNE']) . $ligneImportUniverselCSEntete['LIGNE']);
            $this->addCsParameter($csParameters, $cell, $ligneImportUniverselCSEntete['COD_INT'], $demandeService['NO_DS'], 'SDS_ENTE');
        }

        return $demandeService;
    }

    /**
     * @param array<string,mixed> $lignesImportUniversel
     * @param string|int $noDs
     * @param int $noLigneCol
     * @param int $noLigneRow
     * @param int $noLigneDepart
     * @return array<string,mixed>
     * @throws DateMalformedStringException
     * @throws Exception
     */
    public function createLigneDemandeService(array $lignesImportUniversel, string|int $noDs, int &$noLigneCol, int &$noLigneRow, int &$noLigneDepart): array
    {
        $ligneDemandeService = ['NO_DS' => $noDs];
        foreach ($lignesImportUniversel as $ligneImportUniversel) {
            if ($ligneImportUniversel['COD_INT'] === 'NO_LIG_DS') {
                $noLigneCol = $ligneImportUniversel['COLONNE'];
                $noLigneRow = $ligneImportUniversel['LIGNE'];
            }

            $cell = $this->sheet->getCell(Coordinate::stringFromColumnIndex($ligneImportUniversel['COLONNE']) . ($noLigneDepart + $ligneImportUniversel['LIGNE'] - 1));
            $ligneDemandeService[$ligneImportUniversel['COD_INT']] = self::convertCellValue($ligneImportUniversel, $cell->getFormattedValue());
        }

        $this->ligneDemandeServiceFormService->gererCodgestPropriete($ligneDemandeService);

        return $ligneDemandeService;
    }

    /**
     * @param array<string,mixed> $demandeService
     * @param array<string,mixed> $lignesImportUniversel
     * @param array<string,mixed> $importUniversel
     * @param array<string,mixed> $csParameters
     * @return array<int, array<string, mixed>>
     * @throws DateMalformedStringException
     */
    public function createLignesDemandeService(array $demandeService, array $lignesImportUniversel, array $importUniversel, array &$csParameters): array
    {
        $nextNoLigDs = true;
        $lignesDemandeService = [];
        $noLigneDepart = $importUniversel['DEB_DET'];
        $lignesImportUniverselLigne = array_filter($lignesImportUniversel, fn ($ligneImportUniversel) => $ligneImportUniversel['ORIGINE'] === LigneImportUniverselOrigineEnum::D->name);
        $lignesImportUniverselCSLigne = array_filter($lignesImportUniversel, fn ($ligneImportUniversel) => $ligneImportUniversel['ORIGINE'] === LigneImportUniverselOrigineEnum::DS->name);

        while ($nextNoLigDs) {
            $noLigneCol = 0;
            $noLigneRow = 0;

            $ligneDemandeService = $this->createLigneDemandeService($lignesImportUniverselLigne, $demandeService['NO_DS'], $noLigneCol, $noLigneRow, $noLigneDepart);

            foreach ($lignesImportUniverselCSLigne as $ligneImportUniverselCSLigne) {
                $cell = $this->sheet->getCell(Coordinate::stringFromColumnIndex($ligneImportUniverselCSLigne['COLONNE']) . ($noLigneDepart + $ligneImportUniverselCSLigne['LIGNE'] - 1));
                $this->addCsParameter($csParameters, $cell, $ligneImportUniverselCSLigne['COD_INT'], $demandeService['NO_DS'], 'SDS_LIGN', $ligneDemandeService['NO_LIG_DS']);
            }

            $lignesDemandeService[] = $ligneDemandeService;
            $noLigneDepart += $importUniversel['NB_LDET'];
            $nextNoLigDs = $this->getNextNoLigne($noLigneCol, $noLigneDepart, $noLigneRow);
        }

        return $lignesDemandeService;
    }

    /**
     * @param array<string,mixed> $csParameters
     * @param Cell $cell
     * @param string $codInt
     * @param string|int $codeEnr
     * @param string $codeTab
     * @param int|null $ligneEnr
     * @return void
     * @throws DateMalformedStringException
     * @throws Exception
     */
    public function addCsParameter(array &$csParameters, Cell $cell, string $codInt, string|int $codeEnr, string $codeTab, ?int $ligneEnr = null): void
    {
        $codeEnr = ChampSupp::autoCompletCodeEnr((string) $codeEnr, $ligneEnr, $codeTab);
        $numFix = (int) substr($codInt, -2);
        $type = $this->csChps->find(['COD_TAB' => $codeTab, 'NUM_FIX' => $numFix])['TYPE'];

        if ($value = self::convertCsCellValue($type, $cell->getFormattedValue(), $this->trad)) {
            $csParameters[CsHelper::generateCsKey($codeTab, $numFix, $codeEnr)] = $value;
        }
    }

    public function getNextNoLigne(int $noLigneCol, int $noLigneDepart, int $noLigneRow): ?int
    {
        return (int) $this->sheet->getCell(Coordinate::stringFromColumnIndex($noLigneCol) . ($noLigneDepart + $noLigneRow - 1))->getValue();
    }

    /**
     * @param array<string,mixed> $ligneImportUniversel
     * @param mixed $formattedValue
     * @return mixed
     * @throws DateMalformedStringException
     */
    private static function convertCellValue(array $ligneImportUniversel, mixed $formattedValue): mixed
    {
        return match ($ligneImportUniversel['TYPE']) {
            LigneImportUniverselTypeEnum::D->name => (new DateTime($formattedValue))->format('d/m/Y'),
            LigneImportUniverselTypeEnum::A->name => (string) $formattedValue,
            LigneImportUniverselTypeEnum::N->name => str_contains($formattedValue, '.') ? (float) $formattedValue : (int) $formattedValue,
            default => $formattedValue
        };
    }

    /**
     * @throws DateMalformedStringException
     */
    private static function convertCsCellValue(string $type, mixed $formattedValue, ITrad $trad): mixed
    {
        return match ($type) {
            'DATE' => (new DateTime($formattedValue))->format('d/m/Y'),
            'LOGIQUE' => strtoupper($formattedValue) === ltrim(str_replace('t|', '', $trad->t('OUI'))),
            default => $formattedValue
        };
    }
}
