<?php

namespace Modules\Evaluation\Service;

use Entity\Model\CampQstR;
use Helpers\Fichier\Fichier;
use Helpers\Log\LogSysteme;
use Helpers\Outil\Date;
use Helpers\Outil\Sql;
use Modules\Evaluation\Repository\BibliothequeDeQuestions;
use Modules\Evaluation\Repository\Export;
use Helpers\Trad\ITrad;
use Modules\Evaluation\Repository\Evaluation;
use PDO;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

class ExportService
{
    private PDO $pdo;
    private ITrad $trad;
    private Export $repository;

    /**
     * __construct
     *
     * @param  PDO $pdo
     * @param  ITrad $trad
     */
    public function __construct(PDO $pdo, ITrad $trad)
    {
        $this->pdo = $pdo;
        $this->trad = $trad;
        $this->repository = new Export($this->pdo);
    }

    /**
     * exportResults
     *
     * @param  string $campaignId
     * @param  string $supplier
     * @return string
     */
    public function exportResults(string $campaignId, string $supplier = ''): string
    {
        $obj = new Spreadsheet();
        $obj->getProperties()->setTitle($this->trad->t('Export résultats'))->setDescription("none");

        $obj->setActiveSheetIndex(0);
        $obj->getActiveSheet()->setTitle($this->trad->t('Entête de campagne'));


        // Récupération des noms des colonnes de l'entête
        $headingColumns = $this->getExportCol('entete');
        $col = 'A';
        foreach ($headingColumns as $key) {
            $obj->getActiveSheet()->setCellValue($col . '1', $key);
            $col++;
        }

        // Récupération des données de l'entête
        $headingData = $this->repository->getExportHeadingValues($campaignId);
        // si les données de l'entête concernent toutes les sociétés, il n'y a qu'une seule ligne qui contient le chps TOUTESTE,
        // sinon c'est un array multidimensionnel et il faut extraire la liste des sociétés
        if (!isset($headingData['TOUTESTE'])) {
            $tempData = $headingData[0];
            $companiesList = '';
            foreach ($headingData as $data) {
                $companiesList .= $data['CODE_STE'] . '/';
            }
            $companiesList = \rtrim($companiesList, '/');
            $tempData['CODE_STE'] = $companiesList;

            $headingData = $tempData;
        }

        $headingData = $this->formatValues($headingData, 'entete');

        // Insertion des données
        $col = 'A';
        foreach ($headingData as $data) {
            $obj->getActiveSheet()->setCellValue($col . '2', trim($data));
            $col++;
        }

        $obj->createSheet(1);
        $obj->setActiveSheetIndex(1);
        $obj->getActiveSheet()->setTitle($this->trad->t('Questionnaire'));

        // Récupération des noms des colonnes du questionnaire
        $headingColumns = $this->getExportCol('questionnaire');
        $col = 'A';
        foreach ($headingColumns as $key) {
            $obj->getActiveSheet()->setCellValue($col . '1', $key);
            $col++;
        }

        // Récupération des données du questionnaire
        $themes = (new Evaluation($this->pdo))->getArboQuestion($campaignId);
        $questions = (new BibliothequeDeQuestions($this->pdo))->listeBibliotheque($campaignId, '', 'campagne', '');
        $tableReponses = (new CampQstR($this->pdo))->findAll(['ID_CAMP' => $campaignId]);

        $questionnaireData = [];

        foreach ($themes as $key => $value) {
            $questionnaireData[$value['ID_THEME']]['THEME'] = $value['LIB_THEME'];
            $questionnaireData[$value['ID_THEME']]['ARBO'] = $value['ordretri'];
        }

        foreach ($questions as $key => $value) {
            $questionnaireData[$value['ID_THEME']]['QUESTIONS'][$value['ID_QUEST']] = $value;
        }

        foreach ($tableReponses as $key => $value) {
            $questionnaireData[$value['ID_THEME']]['QUESTIONS'][$value['ID_QUEST']]['REPONSES'][$value['ID_REP']] = $value;
        }

        $questionTypes = [
            'O' => $this->trad->t('Texte'),
            'N' => $this->trad->t('Matrice numérique'),
            'L' => $this->trad->t('Réponse oui/non'),
            'T' => $this->trad->t('Matrice texte'),
            'C' => $this->trad->t('Issue de la base de données')
        ];

        $numberTypes = [
            'N' => $this->trad->t('Normal'),
            'P' => $this->trad->t('Pourcentage'),
            'M' => $this->trad->t('Montant'),
        ];

        // Insertion des données
        $line = '2';
        foreach ($questionnaireData as $data) {
            $obj->getActiveSheet()->setCellValue('A' . $line, trim($data['ARBO']));
            $obj->getActiveSheet()->setCellValue('B' . $line, trim($data['THEME']));
            if (isset($data['QUESTIONS'])) {
                foreach ($data['QUESTIONS'] as $question) {
                    $obj->getActiveSheet()->setCellValue('A' . $line, trim($data['ARBO']));
                    $obj->getActiveSheet()->setCellValue('B' . $line, trim($data['THEME']));
                    $obj->getActiveSheet()->setCellValue('C' . $line, $questionTypes[trim($question['TYPE_REP'])]);
                    $obj->getActiveSheet()->setCellValue('D' . $line, trim($question['LIB_QUEST']));
                    $obj->getActiveSheet()->setCellValue('E' . $line, trim($question['COMMENTAIRE']));
                    $obj->getActiveSheet()->setCellValue('F' . $line, trim($question['POIDS']));
                    $obj->getActiveSheet()->setCellValue('G' . $line, (
                        ! Sql::toBool($question['QUEST_FOUR'])
                            ? $this->trad->t('Non')
                            : $this->trad->t('Oui')
                    ));
                    $obj->getActiveSheet()->setCellValue('H' . $line, (
                        trim($question['QUESTION_PARENT']) != ''
                        ? $this->trad->t('Oui')
                        : $this->trad->t('Non')
                    ));
                    $obj->getActiveSheet()->setCellValue('I' . $line, trim($question['QUESTION_PARENT']));
                    $obj->getActiveSheet()->setCellValue('K' . $line, (
                        trim($question['TYPE_REP']) == 'N'
                        ? $numberTypes[trim($question['TYP_NOMBRE'])]
                        : ''
                    ));
                    if (trim($question['TYPE_REP']) == 'L') {
                        $obj->getActiveSheet()->setCellValue('L' . $line, trim($question['NOTE_VRAI']));
                        $obj->getActiveSheet()->setCellValue('M' . $line, trim($question['NOTE_FAUX']));
                    } else {
                        $obj->getActiveSheet()->setCellValue('L' . $line, '');
                        $obj->getActiveSheet()->setCellValue('M' . $line, '');
                    }
                    if (isset($question['REPONSES'])) {
                        $listeReponses = '';
                        foreach ($question['REPONSES'] as $reponse) {
                            $listeReponses .= ($reponse['TXT_REP'] != ''
                                ? $reponse['TXT_REP']
                                : '0') . ' (' . $reponse['NOTE'] . ');';
                        }
                        $obj->getActiveSheet()->setCellValue('J' . $line, $listeReponses);
                    } else {
                        $obj->getActiveSheet()->setCellValue('J' . $line, '');
                    }
                    $line++;
                }
            } else {
                $line++;
            }
        }

        $obj->createSheet(2);
        $obj->setActiveSheetIndex(2);
        $obj->getActiveSheet()->setTitle($this->trad->t('Réponses'));


        // Récupération des noms des colonnes des réponses
        $headingColumns = $this->getExportCol('reponse');
        $col = 'A';
        foreach ($headingColumns as $key) {
            $obj->getActiveSheet()->setCellValue($col . '1', $key);
            $col++;
        }

        // Récupération des données de l'entête
        $answersData = $this->repository->getAnswersForExport($campaignId, $supplier);
        $answersData = $this->formatValues($answersData, 'reponse');

        // Insertion des données
        $line = 2;
        foreach ($answersData as $value) {
            $col = 'A';
            foreach ($value as $v) {
                $obj->getActiveSheet()->setCellValue($col . $line, trim($v));
                $col++;
            }
            $line++;
        }

        $obj->createSheet(3);
        $obj->setActiveSheetIndex(3);
        $obj->getActiveSheet()->setTitle($this->trad->t('Commentaires'));


        // Récupération des noms des colonnes des réponses
        $headingColumns = $this->getExportCol('commentaire');
        $col = 'A';
        foreach ($headingColumns as $key) {
            $obj->getActiveSheet()->setCellValue($col . '1', $key);
            $col++;
        }

        // Récupération des données de l'entête
        $commentsData = $this->repository->getCommentsForExport($campaignId, $supplier);
        // $commentsData = $this->formatValues($commentsData, 'reponse');

        // Insertion des données
        $line = 2;
        foreach ($commentsData as $value) {
            $col = 'A';
            foreach ($value as $v) {
                $obj->getActiveSheet()->setCellValue($col . $line, trim($v));
                $col++;
            }
            $line++;
        }

        foreach ($obj->getWorksheetIterator() as $worksheet) {
            $obj->setActiveSheetIndex($obj->getIndex($worksheet));
            $sheet = $obj->getActiveSheet();
            $cellIterator = $sheet->getRowIterator()->current()->getCellIterator();
            $cellIterator->setIterateOnlyExistingCells(true);
            foreach ($cellIterator as $cell) {
                $sheet->getColumnDimension($cell->getColumn())->setAutoSize(true);
            }
        }

        $timerCreationFichier = "_" . date('Ymd') . "_" . date('His');

        $nomFichier = $_SESSION['NomFichier'] = LogSysteme::getServerName() . '.' . $campaignId . '_result_export' . $timerCreationFichier . '.xlsx';

        // @phpstan-ignore-next-line
        $urlExcel = Fichier::URLDownload($nomFichier);

        $objWriter = IOFactory::createWriter($obj, 'Xlsx');
        $objWriter->save($urlExcel);

        return $nomFichier;
    }

    /**
     * getExportCol
     *
     * @param  string $sheet
     * @return array<mixed>
     */
    public function getExportCol(string $sheet): array
    {
        switch ($sheet) {
            case 'gestion':
                $columns = [
                    'ID_CAMP' => $this->trad->t('Id de campagne'),
                    'LIBELLE' => $this->trad->t('Libellé'),
                    'DESCRIPT' => $this->trad->t('Description'),
                    'STATUT' => $this->trad->t('Statut'),
                    'TOUTESTE' => $this->trad->t('Toute société ?'),
                    'DTECREAT' => $this->trad->t('Date de validation'),
                    'DTEFIN' => $this->trad->t('Date de clôture'),
                    'DTEFIN_PREVUE' => $this->trad->t('Date de fin prévue'),
                    'LIBELLEPER' => $this->trad->t('Période'),
                    'CREATOR' => $this->trad->t('Créateur'),
                ];
                break;

            case 'entete':
                $columns = [
                    'LIBELLE' => $this->trad->t('Libellé'),
                    'LIB_GRILLE' => $this->trad->t('Catégorie'),
                    'DESCRIPT' => $this->trad->t('Description'),
                    'DTECREAT' => $this->trad->t('Date de validation'),
                    'DTEFIN' => $this->trad->t('Date de clôture'),
                    'PERIODE' => $this->trad->t('Période'),
                    'CODE_STE' => $this->trad->t('Sociétés'),
                    'NOM' => $this->trad->t('Créateur'),
                ];
                break;

            case 'questionnaire':
                $columns = [
                    'ARBO' => $this->trad->t('Arborescence des thèmes'),
                    'THEME' => $this->trad->t('Thèmes et sous-thèmes'),
                    'TYPE' => $this->trad->t('Type de question'),
                    'LIBELLE' => $this->trad->t('Libellé question'),
                    'DESCRIPT' => $this->trad->t('Commentaire'),
                    'POIDS' => $this->trad->t('Poids'),
                    'QUEST_FOUR' => $this->trad->t('Pour fournisseur'),
                    'DEP' => $this->trad->t('Question dépendante'),
                    'PARENT' => $this->trad->t('Question parent'),
                    'TBL_REP' => $this->trad->t('Tableau de réponses'),
                    'TYP_NBR' => $this->trad->t('Type de nombre'),
                    'NOTE_VRAI' => $this->trad->t('Note pour oui'),
                    'NOTE_FAUX' => $this->trad->t('Note pour non'),
                ];
                break;

            case 'reponse':
                $columns = [
                    'ARBO' => $this->trad->t('Fournisseur'),
                    'NOTEUR' => $this->trad->t('Evaluateur'),
                    'THEME' => $this->trad->t('Thème'),
                    'LIBELLE' => $this->trad->t('Libellé question'),
                    'PARENT' => $this->trad->t('Question parent'),
                    'NOTE' => $this->trad->t('Note'),
                    'REPONSE' => $this->trad->t('Réponse'),
                    'COMMENTAIRE' => $this->trad->t('Commentaire'),
                    'TYPE' => $this->trad->t('Type de question'),
                    'POIDS' => $this->trad->t('Poids'),
                    'ATTRIBUE_PAR' => $this->trad->t('Note attribuée par'),
                ];
                break;

            case 'commentaire':
                $columns = [
                    'FOURNISS' => $this->trad->t('Fournisseur'),
                    'NOTE' => $this->trad->t('Note'),
                    'COMMENT' => $this->trad->t('Commentaire'),
                    'PLAN' => $this->trad->t('Appréciation globale'),
                ];
                break;

            default:
                $columns = [];
                break;
        }
        return $columns;
    }

    /**
     * formatValues
     *
     * @param  array<mixed> $data
     * @param  string $sheet
     * @return array<mixed>
     */
    public function formatValues(array $data, string $sheet): array
    {
        $questionTypes = [
            'O' => $this->trad->t('Texte'),
            'N' => $this->trad->t('Matrice numérique'),
            'L' => $this->trad->t('Réponse oui/non'),
            'T' => $this->trad->t('Matrice texte'),
            'C' => $this->trad->t('Issue de la base de données')
        ];

        $statusTypes = [
            'T' => $this->trad->t('Terminée'),
            'P' => $this->trad->t('En préparation'),
            'E' => $this->trad->t('En cours'),
            'A' => $this->trad->t('En attente de clôture'),
        ];

        $listeNoteur = [
            'N' => $this->trad->t('Evaluateur'),
            'D' => $this->trad->t('Demandeur'),
            'C' => $this->trad->t('Dépouilleur'),
        ];

        switch ($sheet) {
            case 'entete':
                $return = [
                    'LIBELLE' => $data['LIBELLE'],
                    'LIB_GRILLE' => $data['LIB_GRILLE'],
                    'DESCRIPT' => $data['DESCRIPT'],
                    'DTECREAT' => Date::dateConverte($data['DTECREAT']),
                    'DTEFIN' => Date::dateConverte($data['DTEFIN']),
                    'PERIODE' => $data['PERIODE'],
                    'CODE_STE' => ($data['CODE_STE'] == '' ? $this->trad->t('Toutes') : $data['CODE_STE']),
                    'NOM' => $data['NOM'],
                ];
                break;

            case 'gestion':
                foreach ($data as &$value) {
                    $value = [
                        'ID_CAMP' => $value['ID_CAMP'],
                        'LIBELLE' => $value['LIBELLE'],
                        'DESCRIPT' => $value['DESCRIPT'],
                        'STATUT' => $statusTypes[$value['STATUT']],
                        'TOUTESTE' => (Sql::toBool($value['TOUTESTE']))
                            ? $this->trad->t('Oui')
                            : $this->trad->t('Non'),
                        'DTECREAT' => Date::dateConverte($value['DTECREAT']),
                        'DTEFIN' => Date::dateConverte($value['DTEFIN']),
                        'DTEFIN_PREVUE' => Date::dateConverte($value['DTEFIN_PREVUE']),
                        'LIBELLEPER' => $value['LIBELLEPER'],
                        'CREATOR' => $value['CREATOR'] . ' (' . $value['CREEEPAR'] . ')',
                    ];
                }
                $return = $data;
                break;

            case 'reponse':
                foreach ($data as &$value) {
                    unset($value['CODE_FOU']);
                    if ($value['TYPE_REP'] == 'L') {
                        $value['reponse_evaluateur'] = (Sql::toBool($value['reponse_evaluateur']))
                            ? $this->trad->t('Oui')
                            : $this->trad->t('Non');
                    }

                    if ($value['TYPE_REP'] == 'O') {
                        $value['note_attribuee_par'] = $listeNoteur[$value['note_attribuee_par']];
                    }

                    $value['TYPE_REP'] = $questionTypes[$value['TYPE_REP']];
                }
                $return = $data;
                break;

            default:
                $return = [];
                break;
        }
        return $return;
    }
}
