<?php

namespace AppBundle\Command;

use AppBundle\Entity\Company;
use AppBundle\Entity\OperationPEE;
use Doctrine\ORM\EntityManagerInterface;
use Doctrine\ORM\Query;
use Symfony\Bundle\FrameworkBundle\Command\ContainerAwareCommand;
use Symfony\Component\Console\Helper\ProgressBar;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Output\OutputInterface;
use AppBundle\Entity\User;
use AppBundle\Enum\UserType;

/**
 * Create companies entities from PEE operations and user profiles,
 * then update users and PEE operations
 */
class MigrateCompaniesCommand extends ContainerAwareCommand
{
    /**
     * 
     */
    protected function configure()
    {
        $this
            ->setName('cgenial:companies:migrate')
            ->setDescription("Create companies entities from PEE operations and user profiles, then update users and PEE operations.");
    }

    /**
     *
     * @param InputInterface $input
     * @param OutputInterface $output
     * @throws \Doctrine\ORM\ORMException
     * @throws \Doctrine\ORM\OptimisticLockException
     */
    protected function execute(InputInterface $input, OutputInterface $output)
    {
        $em = $this->getContainer()->get("doctrine.orm.entity_manager");

        // Retrieve the distinct companies names from the PEE operations and engineers (users)
        $peeOperationsCompanies = $this->getPeeOperationsCompanies($em);
        $userCompanies = $this->getUserCompanies($em);

        // Merge companies. As the keys are string (companies names),
        // we are sure that the resulting array will have unique values.
        // @see http://php.net/manual/en/function.array-merge.php
        // (If the input arrays have the same string keys, then the later value for that key will overwrite the previous one.)
        $companiesNames = array_merge($peeOperationsCompanies, $userCompanies);

        // Retrieve all the PEE operations and engineers
        $peeOperations = $em->getRepository(OperationPEE::class)->findAll();
        $engineers = $em->getRepository(User::class)->findByType(UserType::ENGINEER);

        // Add companies to DB
        $progress = new ProgressBar($output, count($companiesNames));
        $progress->start();
        foreach ($companiesNames as $companyNameLower => $companyName) {
            $company = new Company();
            $company->setName($companyName);

            if (isset($userCompanies[$companyNameLower])) {
                $company->setAvailableForUsers(true);
            }

            // Update PEE operations that share the same company
            $matchingPos = array_filter($peeOperations, function($po) use ($companyNameLower) {
                return $companyNameLower === mb_strtolower($po->getCompanyText());
            });
            foreach ($matchingPos as $po) {
                $po->setCompany($company);
                $em->persist($po);
            }

            // Update engineers who share the same company
            $matchingEngineers = array_filter($engineers, function($e) use ($companyNameLower) {
                return $companyNameLower === mb_strtolower($e->getEstablishmentName());
            });
            foreach ($matchingEngineers as $e) {
                $e->setEstablishment($company);
                $em->persist($e);
            }

            $em->persist($company);
            $progress->advance();
        }
        $em->flush();
        $progress->finish();
    }

    /**
     * Retrieve distinct companies from PEE operations.
     *
     * @param EntityManagerInterface $em
     * @return array List of company names as values. Keys are the lowercase version.
     */
    private function getPeeOperationsCompanies(EntityManagerInterface $em): array
    {
        $qb = $em->createQueryBuilder();
        $qb
            ->select('DISTINCT o.companyText AS company, LOWER(o.companyText) AS companyLower')
            ->from(OperationPEE::class, 'o')
            ->where('o.companyText IS NOT NULL')
        ;
        $peeOperations = $qb->getQuery()->getResult(Query::HYDRATE_ARRAY);
        
        return array_column($peeOperations, 'company', 'companyLower');
    }

    /**
     * Retrieve distinct companies from engineers.
     *
     * @param EntityManagerInterface $em
     * @return array List of company names as values. Keys are the lowercase version.
     */
    private function getUserCompanies(EntityManagerInterface $em): array
    {
        $query = $em->createQuery("
                SELECT
                    CASE
                        WHEN u.establishmentName IS NOT NULL THEN u.establishmentName
                        ELSE u.establishmentNameOther
                    END AS company,
                    LOWER(
                        CASE
                            WHEN u.establishmentName IS NOT NULL THEN u.establishmentName
                            ELSE u.establishmentNameOther
                        END
                    ) AS companyLower
                FROM " . User::class . " u
                WHERE
                    u.type = :u_type
                    AND (u.establishmentName IS NOT NULL OR u.establishmentNameOther IS NOT NULL)
                GROUP BY company
            ")
            ->setParameter('u_type', UserType::ENGINEER)
        ;
        $users = $query->getResult(Query::HYDRATE_ARRAY);
        
        return array_column($users, 'company', 'companyLower');
    }
}
