<?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\JobBundle\Export;

use Chill\MainBundle\Export\ExportElementValidatedInterface;
use Chill\MainBundle\Export\FormatterInterface;
use Chill\MainBundle\Export\ListInterface;
use Chill\MainBundle\Form\Type\ChillDateType;
use Chill\PersonBundle\Entity\Person;
use Chill\JobBundle\Entity\ProjetProfessionnel;
use Chill\JobBundle\Security\Authorization\ExportsJobVoter;
use Doctrine\ORM\EntityManagerInterface;
use Doctrine\ORM\Query;
use Doctrine\ORM\QueryBuilder;
use Symfony\Component\Form\Extension\Core\Type\ChoiceType;
use Symfony\Component\Form\FormBuilderInterface;
use Symfony\Component\Security\Core\Role\Role;
use Symfony\Component\Validator\Constraints\Callback;
use Symfony\Component\Validator\Context\ExecutionContextInterface;

/**
 * Class ListProjetProfessionnel.
 *
 * @author Mathieu Jaumotte mathieu.jaumotte@champs-libres.coop
 */
class ListProjetProfessionnel implements ListInterface, ExportElementValidatedInterface
{
    use \Chill\MainBundle\Export\ExportDataNormalizerTrait;
    /**
     * @var array
     */
    public const PPROF = [
        'projet_prof__type_contrat__label' => ProjetProfessionnel::TYPE_CONTRAT,
        'projet_prof__volume_horaire__label' => ProjetProfessionnel::VOLUME_HORAIRES,
    ];

    /**
     * @var array
     */
    public const FIELDS = [
        'id' => 'integer',
        'firstname' => 'string',
        'lastname' => 'string',
        'gender' => 'string',
        'birthdate' => 'date',
        'placeofbirth' => 'string',
        'countryofbirth' => 'json',
        'projet_prof__souhait__code' => 'string',
        'projet_prof__type_contrat__label' => 'json',
        'projet_prof__type_contrat__note' => 'string',
        'projet_prof__volume_horaire__label' => 'json',
        'projet_prof__volume_horaire__note' => 'string',
        'projet_prof__idee' => 'string',
        'projet_prof__encoursdeconstruction' => 'string',
        'projet_prof__valide__note' => 'string',
        'projet_prof__valide__code' => 'string',
        'projet_prof__note' => 'string',
    ];

    /**
     * @var array
     */
    protected $personIds = [];

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

    /**
     * ListAcquisition constructor.
     */
    public function __construct(EntityManagerInterface $em)
    {
        $this->entityManager = $em;
    }

    /**
     * validate the form's data and, if required, build a contraint
     * violation on the data.
     *
     * @param mixed $data the data, as returned by the user
     */
    public function validateForm($data, ExecutionContextInterface $context): void {}

    /**
     * get a title, which will be used in UI (and translated).
     *
     * @return string
     */
    public function getTitle(): string|\Symfony\Contracts\Translation\TranslatableInterface
    {
        return 'Liste des projets professionnels par personne';
    }

    /**
     * Add a form to collect data from the user.
     */
    public function buildForm(FormBuilderInterface $builder)
    {
        $builder
            ->add('fields', ChoiceType::class, [
                'multiple' => true,
                'expanded' => true,
                //                'choices_as_values' => true,
                'label' => 'Fields to include in export',
                'choice_label' => fn ($key) => str_replace('__', '.', (string) $key),
                'choices' => array_combine($this->getFields(), $this->getFields()),
                'data' => array_combine($this->getFields(), $this->getFields()),
                'choice_attr' => [],
                'attr' => ['class' => ''],
                'constraints' => [new Callback([
                    'callback' => function ($selected, ExecutionContextInterface $context) {
                        if (0 === count($selected)) {
                            $context
                                ->buildViolation('You must select at least one element')
                                ->atPath('fields')
                                ->addViolation();
                        }
                    },
                ])],
            ])
            ->add('reportdate_min', ChillDateType::class, [
                'label' => 'Date du rapport après le',
                'required' => false,
                'label_attr' => [
                    'class' => 'reportdate_range',
                ],
                'attr' => [
                    'class' => 'reportdate_range',
                ],
            ])
            ->add('reportdate_max', ChillDateType::class, [
                'label' => 'Date du rapport avant le',
                'required' => false,
                'label_attr' => [
                    'class' => 'report_date_range',
                ],
                'attr' => [
                    'class' => 'report_date_range',
                ],
            ])
        ;
    }

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

    public function normalizeFormData(array $formData): array
    {
        return ['fields' => $formData['fields'], 'reportdate_min' => $this->normalizeDate($formData['reportdate_min']), 'reportdate_max' => $this->normalizeDate($formData['reportdate_max'])];
    }

    public function denormalizeFormData(array $formData, int $fromVersion): array
    {
        return ['fields' => $formData['fields'], 'reportdate_min' => $this->denormalizeDate($formData['reportdate_min']), 'reportdate_max' => $this->denormalizeDate($formData['reportdate_max'])];
    }

    /**
     * Return the Export's type. This will inform _on what_ export will apply.
     * Most of the type, it will be a string which references an entity.
     *
     * Example of types : Chill\PersonBundle\Export\Declarations::PERSON_TYPE
     */
    public function getType(): string
    {
        return Person::class;
    }

    /**
     * A description, which will be used in the UI to explain what the export does.
     * This description will be translated.
     *
     * @return string
     */
    public function getDescription()
    {
        return 'Crée une liste des personnes et de leur projet professionnel en fonction de différents paramètres.';
    }

    public function initiateQuery(array $requiredModifiers, array $acl, array $data, \Chill\MainBundle\Export\ExportGenerationContext $context): QueryBuilder
    {
        return $this->entityManager->createQueryBuilder()
            ->from('ChillPersonBundle:Person', 'person');
    }

    /**
     * Inform which ModifiersInterface (i.e. AggregatorInterface, FilterInterface)
     * are allowed. The modifiers should be an array of types the _modifier_ apply on
     * (@see ModifiersInterface::applyOn()).
     *
     * @return string[]
     */
    public function supportsModifiers()
    {
        return ['projetprofessionnel', 'person'];
    }

    /**
     * Return the required Role to execute the Export.
     */
    public function requiredRole(): string
    {
        return ExportsJobVoter::EXPORT;
    }

    /**
     * Return which formatter type is allowed for this report.
     *
     * @return string[]
     */
    public function getAllowedFormattersTypes()
    {
        return [FormatterInterface::TYPE_LIST];
    }

    /**
     * Return array FIELDS keys only.
     *
     * @return array
     */
    private function getFields()
    {
        return array_keys(self::FIELDS);
    }

    /**
     * give the list of keys the current export added to the queryBuilder in
     * self::initiateQuery.
     *
     * Example: if your query builder will contains `SELECT count(id) AS count_id ...`,
     * this function will return `array('count_id')`.
     *
     * @param mixed[] $data the data from the export's form (added by self::buildForm)
     */
    public function getQueryKeys($data): array
    {
        $projet_professionnel = self::PPROF;

        $fields = [];
        foreach ($data['fields'] as $key) {
            switch ($key) {
                case 'projet_prof__type_contrat__label':
                case 'projet_prof__volume_horaire__label':
                    foreach ($projet_professionnel[$key] as $item) {
                        $this->translationCompatKey($item, $key);
                        $fields[] = $item;
                    }
                    break;

                case 'projet_prof__souhait__code':
                case 'projet_prof__type_contrat__note':
                case 'projet_prof__volume_horaire__note':
                case 'projet_prof__idee':
                case 'projet_prof__encoursdeconstruction':
                case 'projet_prof__valide__note':
                case 'projet_prof__valide__code':
                case 'projet_prof__note':
                    $key = str_replace('__', '.', (string) $key);

                    // no break
                default:
                    $fields[] = $key;
            }
        }

        return $fields;
    }

    /**
     * Make item compatible with YAML messages ids
     * for fields that are splitted in columns (with field key to replace).
     *
     * AVANT
     *     key:  projet_prof__volume_horaire__label
     *     item: temps_plein
     * APRES
     *     item: projet_prof.volume_horaire.temps_plein
     *
     * @param string $item (&) passed by reference
     * @param string $key
     */
    private function translationCompatKey(&$item, $key)
    {
        $key = str_replace('label', $item, $key);
        $key = str_replace('__', '.', $key);
        $item = $key;
    }

    /**
     * Some fields values are arrays that have to be splitted in columns.
     * This function split theses fields.
     *
     * @param array $rows
     *
     * @return array|\Closure
     */
    private function splitArrayToColumns($rows)
    {
        $projet_professionnel = self::PPROF;

        $results = [];

        foreach ($rows as $row) {
            /**
             * @var string $key
             */
            $res = [];
            foreach ($row as $key => $value) {
                switch ($key) {
                    case 'projet_prof__type_contrat__label':
                    case 'projet_prof__volume_horaire__label':
                        foreach ($projet_professionnel[$key] as $item) {
                            $this->translationCompatKey($item, $key);

                            if (0 === count($value)) {
                                $res[$item] = '';
                            } else {
                                foreach ($value as $v) {
                                    $this->translationCompatKey($v, $key);

                                    if ($item === $v) {
                                        $res[$item] = 'x';
                                        break;
                                    }
                                    $res[$item] = '';
                                }
                            }
                        }
                        break;

                    case 'projet_prof__souhait__code':
                    case 'projet_prof__type_contrat__note':
                    case 'projet_prof__volume_horaire__note':
                    case 'projet_prof__idee':
                    case 'projet_prof__encoursdeconstruction':
                    case 'projet_prof__valide__note':
                    case 'projet_prof__valide__code':
                    case 'projet_prof__note':
                        $key = str_replace('__', '.', (string) $key);

                        // no break
                    default:
                        $res[$key] = $value;
                }
            }
            $results[] = $res;
        }

        return $results;
    }

    /**
     * Return the results of the query builder.
     *
     * @param QueryBuilder|\Doctrine\ORM\NativeQuery $qb
     * @param mixed[]                                $data the data from the export's form (added by self::buildForm)
     */
    public function getResult($qb, $data, \Chill\MainBundle\Export\ExportGenerationContext $context): array
    {
        $qb->select('person.id');

        $ids = $qb->getQuery()->getResult(Query::HYDRATE_SCALAR);
        $this->personIds = array_map(fn ($e) => $e['id'], $ids);
        $personIdsParameters = '?'.\str_repeat(', ?', count($this->personIds) - 1);
        $query = \str_replace('%person_ids%', $personIdsParameters, self::QUERY);

        $rsm = new Query\ResultSetMapping();

        foreach (self::FIELDS as $name => $type) {
            if (null !== $data['fields'][$name]) {
                $rsm->addScalarResult($name, $name, $type);
            }
        }

        $nq = $this->entityManager->createNativeQuery($query, $rsm);

        $idx = 1;
        for ($i = 1; $i <= count($this->personIds); ++$i) {
            ++$idx;
            $nq->setParameter($i, $this->personIds[$i - 1]);
        }
        $nq->setParameter($idx++, $data['reportdate_min'], 'date');
        $nq->setParameter($idx, $data['reportdate_max'], 'date');

        return $this->splitArrayToColumns(
            $nq->getResult()
        );
    }

    /**
     * transform the results to viewable and understable string.
     *
     * The callable will have only one argument: the `value` to translate.
     *
     * The callable should also be able to return a key `_header`, which
     * will contains the header of the column.
     *
     * The string returned **must** be already translated if necessary,
     * **with an exception** for the string returned for `_header`.
     *
     * Example :
     *
     * ```
     * protected $translator;
     *
     * public function getLabels($key, array $values, $data)
     * {
     *      return function($value) {
     *          case $value
     *          {
     *              case '_header' :
     *                  return 'my header not translated';
     *              case true:
     *                  return $this->translator->trans('true');
     *              case false:
     *                  return $this->translator->trans('false');
     *              default:
     *                  // this should not happens !
     *                  throw new \LogicException();
     *          }
     * }
     * ```
     *
     * **Note:** Why each string must be translated with an exception for
     * the `_header` ? For performance reasons: most of the value will be number
     * which do not need to be translated, or value already translated in
     * database. But the header must be, in every case, translated.
     *
     * @param string  $key    The column key, as added in the query
     * @param mixed[] $values The values from the result. if there are duplicates, those might be given twice. Example: array('FR', 'BE', 'CZ', 'FR', 'BE', 'FR')
     * @param mixed   $data   The data from the export's form (as defined in `buildForm`
     */
    public function getLabels($key, array $values, $data)
    {
        return match ($key) {
            'birthdate' => function ($value) use ($key) {
                if ('_header' === $value) {
                    return $key;
                }
                if (null === $value || '' === $value) {
                    return '';
                }

                return $value->format('d-m-Y');
            },
            'countryofbirth' => function ($value) use ($key) {
                if ('_header' === $value) {
                    return $key;
                }

                return $value['fr'];
            },
            'projet_prof.valide.code', 'projet_prof.souhait.code' => function ($value) use ($key) {
                if ('_header' === $value) {
                    return $key;
                }
                if ('{NULL}' === $value) {
                    return '';
                }

                return str_replace(['{', '}'], '', $value);
            },
            'gender' => function ($value) use ($key) {
                $gend_array = ['man' => 'Homme', 'woman' => 'Femme', 'both' => 'Indéterminé'];
                if ('_header' === $value) {
                    return $key;
                }
                if (null === $value || '' === $value) {
                    return '';
                }

                return $gend_array[$value];
            },
            default => function ($value) use ($key) {
                if ('_header' === $value) {
                    return $key;
                }
                if (null === $value || '' === $value) {
                    return '';
                }

                return $value;
            },
        };
    }

    /**
     * Native Query SQL.
     */
    public const QUERY = <<<'SQL'

WITH projet_professionnel AS (

  SELECT
    pp.id,
    pp.reportdate,
    pp.typecontrat,
    pp.typecontratnotes,
    pp.volumehoraire,
    pp.volumehorairenotes,
    pp.idee,
    pp.encoursconstruction,
    pp.validenotes,
    pp.projetprofessionnelnote,
    ARRAY_AGG (DISTINCT rms.code) as rms_codes,
    ARRAY_AGG (DISTINCT rmv.code) as rmv_codes

  FROM chill_job.projet_professionnel AS pp

    LEFT OUTER JOIN chill_job.projetprofessionnel_souhait AS pps
      ON pp.id = pps.projetprofessionnel_id
    LEFT OUTER JOIN chill_job.rome_appellation AS ras
      ON pps.appellation_id = ras.id
    LEFT OUTER JOIN chill_job.rome_metier AS rms
      ON ras.metier_id = rms.id
    LEFT OUTER JOIN chill_job.projetprofessionnel_valide AS ppv
      ON pp.id = ppv.projetprofessionnel_id
    LEFT OUTER JOIN chill_job.rome_appellation AS rav
      ON ppv.appellation_id = rav.id
    LEFT OUTER JOIN chill_job.rome_metier AS rmv
      ON rav.metier_id = rmv.id

  GROUP BY pp.id
)

SELECT
  p.id,
  p.firstname,
  p.lastname,
  p.gender as gender,
  p.birthdate as birthdate,
  p.place_of_birth as placeofbirth,
  cn.name as countryofbirth,
  pp.rms_codes as projet_prof__souhait__code,
  pp.typecontrat as projet_prof__type_contrat__label,
  pp.typecontratnotes as projet_prof__type_contrat__note,
  pp.volumehoraire as projet_prof__volume_horaire__label,
  pp.volumehorairenotes as projet_prof__volume_horaire__note,
  pp.idee as projet_prof__idee,
  pp.encoursconstruction as projet_prof__encoursdeconstruction,
  pp.validenotes as projet_prof__valide__note,
  pp.rmv_codes as projet_prof__valide__code,
  pp.projetprofessionnelnote as projet_prof__note

FROM public.chill_person_person AS p

  LEFT JOIN projet_professionnel AS pp
    ON p.id = pp.id

  LEFT OUTER JOIN public.country AS cn
    ON p.countryofbirth_id = cn.id

-- condition 1
WHERE p.id IN (%person_ids%)

-- condition 2
AND (
  pp.reportdate
  BETWEEN COALESCE(?::date, '1900-01-01')
      AND COALESCE(?::date, '2100-01-01')
)

ORDER BY pp.reportdate DESC

SQL;

    public function getFormDefaultData(): array
    {
        return [];
    }
}
