<?php

declare(strict_types=1);

/*
 * Chill is a software for social workers
 *
 * For the full copyright and license information, please view
 * the LICENSE file that was distributed with this source code.
 */

namespace Chill\PersonBundle\Export\Export;

use Chill\MainBundle\Export\DirectExportInterface;
use Chill\MainBundle\Export\ExportElementValidatedInterface;
use Chill\MainBundle\Export\FormattedExportGeneration;
use Chill\MainBundle\Export\GroupedExportInterface;
use Chill\PersonBundle\Security\Authorization\PersonVoter;
use Doctrine\ORM\EntityManagerInterface;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Symfony\Component\Form\Extension\Core\Type\NumberType;
use Symfony\Component\Form\FormBuilderInterface;
use Symfony\Component\Routing\Generator\UrlGeneratorInterface;
use Symfony\Component\Validator\Context\ExecutionContextInterface;
use Symfony\Contracts\Translation\TranslatorInterface;

/**
 * Render a list of duplicate peoples.
 */
class ListPersonDuplicate implements DirectExportInterface, ExportElementValidatedInterface, GroupedExportInterface
{
    /**
     * @var float
     */
    private const PRECISION_DEFAULT_VALUE = 0.7;

    /**
     * @var string
     */
    protected $baseUrl;

    /**
     * @var EntityManagerInterface
     */
    protected $entityManager;

    public function __construct(
        EntityManagerInterface $em,
        private readonly TranslatorInterface $translator,
        private readonly UrlGeneratorInterface $router,
        $routeParameters,
    ) {
        $this->entityManager = $em;
        $this->baseUrl = $routeParameters['scheme'].
            '://'.$routeParameters['host'];
    }

    public function buildForm(FormBuilderInterface $builder)
    {
        $builder->add('precision', NumberType::class, [
            'label' => 'Precision',
        ]);
    }

    public function getNormalizationVersion(): int
    {
        return 1;
    }

    public function normalizeFormData(array $formData): array
    {
        return ['precision' => $formData['precision']];
    }

    public function denormalizeFormData(array $formData, int $fromVersion): array
    {
        return ['precision' => $formData['precision']];
    }

    public function getFormDefaultData(): array
    {
        return ['precision' => self::PRECISION_DEFAULT_VALUE];
    }

    public function generate(array $acl, array $data, \Chill\MainBundle\Export\ExportGenerationContext $context): FormattedExportGeneration
    {
        $values = [];
        $values[] = $this->getHeaders();

        $result = $this->getResult($data);

        foreach ($result as $row) {
            $values[] = [
                $row['id1'],
                $row['firstname1'],
                $row['lastname1'],
                $this->baseUrl.$this->router->generate('chill_person_view', ['person_id' => $row['id1']]),
                $row['id2'],
                $row['firstname2'],
                $row['lastname2'],
                $this->baseUrl.$this->router->generate('chill_person_view', ['person_id' => $row['id2']]),
            ];
        }

        $spreadsheet = new Spreadsheet();
        $spreadsheet->getActiveSheet()->fromArray($values);

        // Make links clickable
        for ($i = 1; $spreadsheet->getActiveSheet()->getHighestDataRow() >= $i; ++$i) {
            $spreadsheet->getActiveSheet()->getCell('D'.$i)->getHyperlink()
                ->setUrl($spreadsheet->getActiveSheet()->getCell('D'.$i)->getValue());
            $spreadsheet->getActiveSheet()->getCell('H'.$i)->getHyperlink()
                ->setUrl($spreadsheet->getActiveSheet()->getCell('H'.$i)->getValue());
        }

        $writer = new Xlsx($spreadsheet);
        $temp_file = sys_get_temp_dir().'/'.uniqid('export_').'.xlsx';
        $writer->save($temp_file);

        $formatted = new FormattedExportGeneration(file_get_contents($temp_file), 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        unlink($temp_file);

        return $formatted;
    }

    public function getDescription(): string
    {
        return 'Create a list of duplicate people';
    }

    public function getGroup(): string
    {
        return 'Exports of persons';
    }

    /**
     * @return string
     */
    public function getTitle(): string|\Symfony\Contracts\Translation\TranslatableInterface
    {
        return 'List duplicates';
    }

    public function requiredRole(): string
    {
        return PersonVoter::DUPLICATE;
    }

    public function validateForm($data, ExecutionContextInterface $context): void {}

    protected function getHeaders(): array
    {
        return [
            $this->translator->trans('Departure folder number'),
            $this->translator->trans('Last name'),
            $this->translator->trans('First name'),
            $this->translator->trans('Link'),
            $this->translator->trans('Arrival folder number'),
            $this->translator->trans('Last name'),
            $this->translator->trans('First name'),
            $this->translator->trans('Link'),
        ];
    }

    protected function getResult($data = [])
    {
        $precision = $data['precision'] ?? self::PRECISION_DEFAULT_VALUE;

        $sql = 'SELECT
                p.id as id1, p.firstname as firstname1, p.lastname as lastname1, p.fullnamecanonical as fullnamecanonical1,
                p2.id as id2, p2.firstname as firstname2, p2.lastname as lastname2, p2.fullnamecanonical as fullnamecanonical2,
                SIMILARITY(p.fullnamecanonical, p2.fullnamecanonical) AS "similarity nom + prenom",
                SIMILARITY(p.lastname, p2.lastname) AS "similarity nom",
                SIMILARITY(p.firstname, p2.firstname) AS "similarity prenom"
            FROM chill_person_person AS p
            JOIN chill_person_person AS p2
                ON p.id != p2.id
                    AND (SIMILARITY(p.fullnamecanonical, p2.fullnamecanonical) > :precision
                    AND p.id < p2.id)
                OR (UNACCENT(LOWER(p.firstname)) = UNACCENT(LOWER(p2.lastname))
                    AND UNACCENT(LOWER(p.lastname))  = UNACCENT(LOWER(p2.firstname)))
            JOIN centers AS p1center
                ON p1center.id = p.center_id
            JOIN centers AS p2center
                ON p2center.id = p2.center_id
            WHERE NOT EXISTS (
                SELECT id
                FROM chill_person_not_duplicate as pnd
                WHERE (pnd.person1_id = p.id
                    AND pnd.person2_id = p2.id)
                OR (pnd.person2_id = p.id
                    AND pnd.person1_id = p2.id)
            )
            ORDER BY p.fullnamecanonical, p.id, p2.id';

        $statement = $this->entityManager->getConnection()->prepare($sql);
        $statement->bindValue('precision', $precision);
        $result = $statement->executeQuery();

        return $result->fetchAllAssociative();
    }
}
