<?php

namespace Modules\Fournisseur\Outil;

use Entity\Model\FouLstFourn;
use Entity\Model\Fourniss;
use Entity\Model\ShortList;
use Helpers\Outil\Date;
use Helpers\Trad\ITrad;
use Helpers\Trad\Trad;
use PhpOffice\PhpSpreadsheet\IOFactory;

class ImportSLFournisseur
{
    const MAX_LINE = 1000;

    const MAX_COLUMN = 3;

    /** @var \PDO $pdo */
    private \PDO $pdo;

    /** @var string $file */
    private string $file;

    /** @var array<mixed> $content */
    private array $content;

    /** @var array<mixed> $shortList */
    private array $shortList = [];

    /** @var array<mixed> $fourniss */
    private array $fourniss = [];

    /** @var array<mixed> $existed */
    private array $existed = [];

    /** @var int $nbTotalLine */
    private int $nbTotalLine = 0;

    /** @var int $nbLineTreated */
    private int $nbLineTreated = 0;

    /** @var int $nbLineDeleted */
    private int $nbLineDeleted = 0;

    /** @var int $nbLineCreated */
    private int $nbLineCreated = 0;

    /** @var array<mixed> $errors */
    public array $errors = [];

    /** @var array<mixed> $ignoredLine */
    private array $ignoredLine = [];

    /** @var array<mixed> $toInsert */
    private array $toInsert = [];

    /** @var array<mixed> $toDelete */
    private array $toDelete = [];

    /** @var array<mixed> $log */
    private array $log = [];

    /** @var bool $valid */
    private bool $valid = true;

    /** @var array<mixed> $dataSQL */
    private array $dataSQL = [];

    private ITrad $trad;

    /**
     * @param \PDO $pdo
     * @param string $file
     */
    public function __construct(\PDO $pdo, string $file, ITrad $trad)
    {
        $this->pdo  = $pdo;
        $this->file = $file;

        $this->initData();
        $this->trad = $trad;
    }

    /**
     * @return void
     */
    private function initData(): void
    {
        $stmt = "
            SELECT CONCAT('fourniss_', FOURNISS.CODE, '_libelle_', FOURNISS.RS) AS valeur FROM FOURNISS with(nolock)
            UNION ALL
            SELECT CONCAT('shortlist_', LST_FOURN.CODLST, '_libelle_', LST_FOURN.LIBELLE) AS valeur FROM LST_FOURN with(nolock)
            UNION ALL
            SELECT CONCAT('fourniss_', FOU_LSTFOURN.CODFOURN, '_shortlist_', FOU_LSTFOURN.CODLST) as valeur FROM FOU_LSTFOURN with(nolock);
        ";

        $req = $this->pdo->prepare($stmt);
        $req->execute();

        $data = $req->fetchAll($this->pdo::FETCH_ASSOC);
        if ($data) {
            $this->hydrateData($data);
        }
    }

    /**
     * @param array<mixed> $data
     * @return void
     */
    private function hydrateData(array $data)
    {
        $findRattachment    = '_shortlist_';
        $findFourniss       = 'fourniss_';
        $findShortList      = 'shortlist_';

        foreach ($data as $row => $value) {
            $value = $value['valeur'];
            $matchRattachment = (stripos($value, $findRattachment) !== false);
            $matchFourniss = stripos($value, $findFourniss) !== false;
            $matchShortList = stripos($value, $findShortList) !== false;

            $realValue = str_replace([
                $findRattachment, $findFourniss, $findShortList
            ], [
                '', '', ''
            ], $value);

            list($code, $libelle) = explode('_libelle_', $realValue);

            if ($matchRattachment) {
                $this->existed[] = $value;
            } elseif ($matchFourniss) {
                if (!isset($this->fourniss[$code])) {
                    $this->fourniss[trim($code)] = trim($libelle);
                }
            } elseif ($matchShortList) {
                if (!isset($this->shortList[$code])) {
                    $this->shortList[trim($code)] = trim($libelle);
                }
            }
        }
    }

    /**
     * @return void
     * @throws \PhpOffice\PhpSpreadsheet\Calculation\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     */
    private function extract(): void
    {
        $ext = pathinfo($this->file, PATHINFO_EXTENSION);

        $this->content = ($ext === 'csv') ? $this->extractFromCsv() : $this->extractFromExcel();

        $this->nbTotalLine = count($this->content);

        if ($this->nbTotalLine >= self::MAX_LINE) {
            $this->errors[] = Trad::strBuild(
                $this->trad->t('Le fichier dépasse le nombre de lignes autorisées. @1@ lignes lues pour @2@ lignes autorisées.'),
                $this->nbTotalLine,
                self::MAX_LINE
            );
            $this->valid = false;
        }
    }

    /**
     * @return array<mixed>
     */
    private function extractFromCsv(): array
    {
        $contents = [];

        if ($fp = fopen($this->file, 'r')) {
            while (($data = fgetcsv($fp, 0, ';')) !== false) {
                $row = array_map(function ($elem) {
                    return trim($elem);
                }, $data);
                $contents[] = $row;
            }
        }

        return $contents;
    }

    /**
     * @return array<mixed>
     * @throws \PhpOffice\PhpSpreadsheet\Calculation\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     */
    private function extractFromExcel(): array
    {
        $input = IOFactory::identify($this->file);
        $objReader = IOFactory::createReader($input);
        $objReader->setReadDataOnly(true);

        $excel = $objReader->load($this->file);

        $sheet = $excel->getSheet(0);
        $nbRow = $sheet->getHighestRow();

        $data = [];

        $rowCount = 0;
        //$maxColumn = 0;
        foreach ($sheet->getRowIterator(1, $nbRow) as $row) {
            $rowContent = [];
            $iC = 0;
            foreach ($row->getCellIterator() as $column) {
                if ($iC >= self::MAX_COLUMN) {
                    break 1;
                }

                $value = $column->getCalculatedValue();

                /*if ($rowCount > 0) {
                    if ($maxColumn !== 0 && $iC >= $maxColumn) {
                        continue;
                    }
                }*/

                $rowContent[] = trim($value);
                $iC++;
            }

            $dataLine   = $rowContent;
            $data[]     = $dataLine;
            $rowCount++;
        }

        return $data;
    }

    /**
     * @return bool
     */
    public function isValid()
    {
        return $this->valid;
    }

    /**
     * @param string $fourniss
     * @param int $index
     * @return void
     */
    private function checkFourniss(string $fourniss, int $index): void
    {
        if (!isset($this->fourniss[$fourniss])) {
            $this->log[]         = [
                'statut' => $this->trad->t('Erreur'),
                'message' => Trad::strBuild(
                    $this->trad->t('Le Fournisseur avec le code @1@ est introuvable dans la base'),
                    $fourniss,
                ),
                'indexFile' => $index + 1,
                'fournisseur' => $fourniss,
                'fournisseur_libelle' => $this->fourniss[$fourniss]
            ];
            $this->ignoredLine[]    = $index;
        }
    }

    /**
     * @param int $shortList
     * @param int $index
     * @return void
     */
    private function checkShortList(int $shortList, int $index): void
    {
        if (!isset($this->shortList[$shortList])) {
            $this->log[]         = [
                'statut' => $this->trad->t('Erreur'),
                'message' => Trad::strBuild(
                    $this->trad->t('La ShortList avec le code @1@ est introuvable dans la base'),
                    $shortList,
                ),
                'indexFile' => $index + 1,
                'shortlist' => $shortList,
                'shortlist_libelle' => $this->shortList[$shortList]
            ];
            $this->ignoredLine[]    = $index;
        }
    }

    /**
     * @param string $status
     * @param int $index
     * @return void
     */
    private function checkStatus(string $status, int $index): void
    {
        $statusAvailable = [
            'C',
            'D'
        ];

        if (!in_array(strtoupper($status), $statusAvailable)) {
            $this->log[]         = [
                'statut' => $this->trad->t('Erreur'),
                'message' => Trad::strBuild(
                    $this->trad->t('Le statut @1@ n\'est pas un statut attendu'),
                    $status,
                ),
                'indexFile' => $index + 1,
            ];
            $this->ignoredLine[]    = $index;
        }
    }

    /**
     * @param string $fourniss
     * @param int $shortList
     * @param string $status
     * @param int $index
     * @return void
     */
    private function isExist(string $fourniss, int $shortList, string $status, int $index): void
    {
        $key = 'fourniss_'.$fourniss.'_shortlist_'.$shortList;
        $exist = in_array($key, $this->existed);

        if ($exist && $status === 'C' && !in_array($index, $this->ignoredLine)) {
            $this->log[]         = [
                'statut' => $this->trad->t('Erreur'),
                'message' => $this->trad->t('Le rattachement Fournisseur Shortlist existe déjà'),
                'indexFile' => $index + 1,
                'fournisseur' => $fourniss,
                'fournisseur_libelle' => $this->fourniss[$fourniss],
                'shortlist' => $shortList,
                'shortlist_libelle' => $this->shortList[$shortList],
            ];
            $this->ignoredLine[]    = $index;
        }

        if (!$exist && $status === 'D' && !in_array($index, $this->ignoredLine)) {
            $this->log[]         = [
                'statut' => $this->trad->t('Erreur'),
                'message' => $this->trad->t('Le rattachement Fournisseur Shortlist n\'existe pas, suppression impossible'),
                'indexFile' => $index + 1,
                'fournisseur' => $fourniss,
                'fournisseur_libelle' => $this->fourniss[$fourniss],
                'shortlist' => $shortList,
                'shortlist_libelle' => $this->shortList[$shortList],
            ];
            $this->ignoredLine[]    = $index;
        }
    }

    /**
     * @param int $index
     * @param array<mixed> $line
     * @return void
     */
    private function checkLine(int $index, array $line)
    {
        $codeSL = (int) $line[1];

        $this->checkFourniss($line[0], $index);
        $this->checkShortList($codeSL, $index);
        $this->checkStatus($line[2], $index);
        $this->isExist($line[0], $codeSL, $line[2], $index);
    }

    /**
     * @return void
     */
    private function verifyEntries()
    {
        if ($this->content) {
            foreach ($this->content as $index => $line) {
                $this->checkLine($index, (array) $line);
            }
        }
    }

    /**
     * @return void
     * @throws \PhpOffice\PhpSpreadsheet\Calculation\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     */
    public function import()
    {
        $this->extract();
        if ($this->isValid()) {
            $this->verifyEntries();
            $this->treatment();
        }
    }

    /**
     * @return void
     */
    private function treatment()
    {
        foreach ($this->content as $index => $line) {
            if (!in_array($index, $this->ignoredLine)) {
                switch ($line[2]) {
                    default:
                        $this->addToInsertQuery($line, $index);
                        break;

                    case "D":
                        $this->addToDeleteQuery($line, $index);
                        break;
                }

                $this->nbLineTreated++;
            }
        }

        $this->buildAndExecuteQuery();
    }


    /**
     * @return void
     */
    private function buildAndExecuteQuery()
    {
        $counterToDelete = count($this->toDelete);
        $counterToInsert = count($this->toInsert);

        // S'il y a au moins une donnée à supprimer ou insérer on build les query.
        if ($counterToDelete > 0 || $counterToInsert > 0) {
            $datas = implode(', ', $this->dataSQL);

            // TODO : prepare $datas
            $query = "MERGE INTO FOU_LSTFOURN Target
                USING (
                    VALUES {$datas}
                ) as Entity(CODFOURN, CODLST)
                ON Target.CODFOURN = Entity.CODFOURN AND Target.CODLST = Entity.CODLST
                WHEN NOT MATCHED
                    THEN
                        INSERT (CODFOURN, CODLST)
                        VALUES (Entity.CODFOURN, Entity.CODLST)
                WHEN MATCHED
                    THEN
                        DELETE;
            ";

            $stmt = $this->pdo->prepare($query);
            $stmt->execute();
        }
    }

    /**
     * @param array<mixed> $line
     * @param int $index
     * @return void
     */
    private function addToInsertQuery(array $line, int $index)
    {
        $codeSL = (int) $line[1];

        $this->toInsert[] = "('{$line[0]}', '{$codeSL}')";
        $this->dataSQL[] = "('{$line[0]}', '{$codeSL}')";
        $this->nbLineCreated++;
        $this->log[] = [
            'statut' => $this->trad->t('Création'),
            'message' => $this->trad->t('Le rattachement a bien été créé'),
            'fournisseur' => $line[0],
            'fournisseur_libelle' => $this->fourniss[$line[0]],
            'shortlist' => $line[1],
            'shortlist_libelle' => $this->shortList[$codeSL],
            'indexFile' => $index + 1,
        ];
    }

    /**
     * @param array<mixed> $line
     * @param int $index
     * @return void
     */
    private function addToDeleteQuery(array $line, int $index)
    {
        $codeSL = (int) $line[1];

        $this->toDelete[] = "(CODFOURN = '{$line[0]}' AND CODLST = '{$codeSL}')";
        $this->dataSQL[] = "('{$line[0]}', '{$codeSL}')";
        $this->nbLineDeleted++;
        $this->log[] = [
            'statut' => $this->trad->t('Suppression'),
            'message' => $this->trad->t('Le rattachement a bien été supprimé'),
            'fournisseur' => $line[0],
            'fournisseur_libelle' => $this->fourniss[$line[0]],
            'shortlist' => $line[1],
            'shortlist_libelle' => $this->shortList[$codeSL],
            'indexFile' => $index + 1,
        ];
    }

    /**
     * @return array<mixed>
     */
    public function getErrors()
    {
        return $this->errors;
    }

    /**
     * @return array<mixed>
     */
    public function getResult()
    {
        usort($this->log, function ($a, $b) {
            return $a['indexFile'] <=> $b['indexFile'];
        });

        return [
            'totalLine'     => $this->nbTotalLine,
            'lineTreated'   => $this->nbLineTreated,
            'lineCreated'   => $this->nbLineCreated,
            'lineDeleted'   => $this->nbLineDeleted,
            'lineIgnored'   => count($this->ignoredLine),
            'errors'        => $this->getErrors(),
            'logs'          => $this->log,
        ];
    }
}
