<?php declare(strict_types=1);

namespace Newland\Toubiz\Sync\Neos\Command;

use Doctrine\ORM\EntityManagerInterface;
use Neos\Flow\Annotations as Flow;
use Newland\Toubiz\Sync\Neos\Domain\Model\Category;
use Newland\Toubiz\Sync\Neos\Domain\Repository\CategoryRepository;
use Symfony\Component\Console\Output\NullOutput;

class CategoryCommandController extends AbstractCommandController
{

    /**
     * @var CategoryRepository
     * @Flow\Inject()
     */
    protected $categoryRepository;

    /**
     * @var EntityManagerInterface
     * @Flow\Inject()
     */
    protected $entityManager;

    /**
     * Removes Categories that are used for neither articles nor events.
     */
    public function removeUnusedCommand(bool $quiet = false): void
    {
        if ($quiet) {
            $this->output->setOutput(new NullOutput());
        }

        $removed = 0;
        $this->categoryRepository->withoutLanguageHandling(
            function () use (&$removed) {
                $identifiers = $this->categoryRepository->getAllIdentifiers();
                $this->output->progressStart(\count($identifiers));

                /** @var Category $category */
                foreach ($identifiers as $identifier) {
                    $category = $this->categoryRepository->findByIdentifier($identifier);
                    if ($this->hasNoEvents($identifier)
                        && $this->hasNoArticles($identifier)
                        && $this->notUsedInRelatedList($identifier)
                        && $this->notUsedInRelatedList((string) $category->getOriginalId())
                    ) {
                        $this->categoryRepository->remove($category);
                        $this->persistenceManager->persistAll();
                        $removed++;
                    }

                    $this->output->progressAdvance();
                }

                $this->output->progressFinish();
            }
        );

        $this->outputLine(sprintf('Removed %d categories', $removed));
    }

    private function hasNoArticles(string $identifier): bool
    {
        $sql = sprintf(
            'SELECT COUNT(*) AS count 
            FROM newland_toubiz_sync_neos_domain_model_article_categories_join AS join_table
            INNER JOIN newland_toubiz_sync_neos_domain_model_article article 
                ON join_table.neos_article = article.persistence_object_identifier
            WHERE neos_category="%s"',
            $identifier
        );
        $result = $this->entityManager->getConnection()->executeQuery($sql)->fetch();
        $count = (int) ($result['count'] ?? 0);
        return $count === 0;
    }

    private function hasNoEvents(string $identifier): bool
    {
        $sql = sprintf(
            'SELECT COUNT(*) AS count 
            FROM newland_toubiz_sync_neos_domain_model_event_categories_join join_table
            INNER JOIN newland_toubiz_sync_neos_domain_model_event event
                ON join_table.neos_event = event.persistence_object_identifier
            WHERE neos_category="%s"',
            $identifier
        );
        $result = $this->entityManager->getConnection()->executeQuery($sql)->fetch();
        $count = (int) ($result['count'] ?? 0);
        return $count === 0;
    }

    private function notUsedInRelatedList(string $identifier): bool
    {
        $sql = sprintf(
            'SELECT COUNT(*) AS count
            FROM newland_toubiz_sync_neos_domain_model_article
            WHERE relatedlists LIKE "%%%s%%"',
            $identifier
        );
        $result = $this->entityManager->getConnection()->executeQuery($sql)->fetch();
        $count = (int) ($result['count'] ?? 0);
        return $count === 0;
    }
}
