<commit_before><?php

namespace Oro\Bundle\TranslationBundle\Command;

use Doctrine\DBAL\Platforms\PostgreSqlPlatform;
use Doctrine\ORM\EntityManager;
use Doctrine\ORM\EntityRepository;
use Symfony\Bundle\FrameworkBundle\Command\ContainerAwareCommand;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Input\InputOption;
use Symfony\Component\Console\Output\OutputInterface;

use Oro\Bundle\TranslationBundle\Entity\Language;
use Oro\Bundle\TranslationBundle\Entity\Translation;
use Oro\Bundle\TranslationBundle\Entity\TranslationKey;
use Oro\Bundle\TranslationBundle\Provider\LanguageProvider;
use Oro\Bundle\TranslationBundle\Translation\EmptyArrayLoader;
use Oro\Bundle\TranslationBundle\Translation\Translator;

class OroTranslationLoadCommand extends ContainerAwareCommand
{
    const BATCH_INSERT_ROWS_COUNT = 50;

    /**
     * {@inheritdoc}
     */
    protected function configure()
    {
        $this
            ->setName('oro:translation:load')
            ->setDescription('Load translations into DB')
            ->addOption(
                'languages',
                'l',
                InputOption::VALUE_OPTIONAL | InputOption::VALUE_IS_ARRAY,
                'Languages to load.'
            )->addOption('rebuild-cache', 'rc', InputOption::VALUE_OPTIONAL, 'Rebuild translation cache');
    }

    /**
     * {@inheritdoc}
     */
    protected function execute(InputInterface $input, OutputInterface $output)
    {
        /** @var $languageProvider LanguageProvider */
        $languageProvider = $this->getContainer()->get('oro_translation.provider.language');
        $availableLocales = array_keys($languageProvider->getAvailableLanguages());

        $locales = $input->getOption('languages') ?: $availableLocales;

        $output->writeln(
            sprintf(
                '<info>Available locales</info>: %s. <info>Should be processed:</info> %s.',
                implode(', ', $availableLocales),
                implode(', ', $locales)
            )
        );

        // backup DB loader
        $translationLoader = $this->getContainer()->get('oro_translation.database_translation.loader');

        // disable DB loader to not get translations from database
        $this->getContainer()->set('oro_translation.database_translation.loader', new EmptyArrayLoader());

        if ($input->getOption('rebuild-cache')) {
            $this->getTranslator()->rebuildCache();
        }

        $start = time();
        $this->processLocales($locales, $output);
        $output->writeln(sprintf('<info>All messages successfully loaded.</info>'));

        // restore DB loader
        $this->getContainer()->set('oro_translation.database_translation.loader', $translationLoader);

        if ($input->getOption('rebuild-cache')) {
            $output->write(sprintf('<info>Rebuilding cache ... </info>'));
            $this->getTranslator()->rebuildCache();
        }

        $output->writeln(sprintf('<info>Done.</info>'));
        echo time() - $start;
    }

    /**
     * @return array
     */
    protected function getTranslationKeysData()
    {
        $repository = $this->getEntityManager(TranslationKey::class)->getRepository(TranslationKey::class);
        $translationKeysData = $repository->createQueryBuilder('tk')
            ->select('tk.id, tk.domain, tk.key')
            ->getQuery()
            ->getArrayResult();
        $translationKeys = [];
        foreach ($translationKeysData as $item) {
            $translationKeys[$item['domain']][$item['key']] = $item['id'];
        }

        return $translationKeys;
    }

    /**
     * @param array $locales
     * @param OutputInterface $output
     * @return array
     */
    protected function processLocales(array $locales, OutputInterface $output)
    {
        $repoLanguage = $this->getEntityRepository(Language::class);
        $connection = $this->getEntityManager(Translation::class)->getConnection();

        $translationKeys = $this->getTranslationKeysData();
        $seqName = $connection->getDatabasePlatform() instanceof PostgreSqlPlatform
            ? 'oro_translation_key_id_seq'
            : null;

        $sqlData = [];
        foreach ($locales as $locale) {
            $language = $repoLanguage->findOneBy(['code' => $locale]);
            $domains = $this->getTranslator()->getCatalogue($locale)->all();

            $output->writeln(sprintf('<info>Loading translations [%s] (%d) ...</info>', $locale, count($domains)));

            $translations = $this->getTranslationsData($language->getId());

            foreach ($domains as $domain => $messages) {
                $output->write(sprintf('  > loading [%s] (%d) ... ', $domain, count($messages)));

                foreach ($messages as $key => $value) {
                    if (!isset($translationKeys[$domain][$key])) {
                        $connection->insert('oro_translation_key', [
                            'domain' => $domain,
                            $connection->quoteIdentifier('key') => $key,
                        ]);
                        $translationKeys[$domain][$key] = $connection->lastInsertId($seqName);
                    }

                    if (isset($translations[$translationKeys[$domain][$key]])) {
                        if ($translations[$translationKeys[$domain][$key]] === Translation::SCOPE_SYSTEM) {
                            $connection->update(
                                'oro_translation',
                                ['value' => $value],
                                [
                                    'translation_key_id' => $translationKeys[$domain][$key],
                                    'language_id' => $language->getId()
                                ]
                            );
                        }
                    } else {
                        $sqlData[] = sprintf(
                            '(%d, %d, %s, %s)',
                            $translationKeys[$domain][$key],
                            $language->getId(),
                            $connection->quote($value),
                            Translation::SCOPE_SYSTEM
                        );

                        if (self::BATCH_INSERT_ROWS_COUNT === count($sqlData)) {
                            $this->executeBatchTranslationInsert($sqlData);
                            $sqlData = [];
                        }
                    }
                }
                $output->writeln(sprintf('processed %d records.', count($messages)));
            }
        }

        $this->executeBatchTranslationInsert($sqlData);
    }

    /**
     * @param array $sqlData<commit_msg>BAP-12213: Installation performance problem after merge new functional of Translations to master - optimize inserting key<commit_after><?php

namespace Oro\Bundle\TranslationBundle\Command;

use Doctrine\ORM\EntityManager;
use Doctrine\ORM\EntityRepository;

use Symfony\Bundle\FrameworkBundle\Command\ContainerAwareCommand;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Input\InputOption;
use Symfony\Component\Console\Output\OutputInterface;

use Oro\Bundle\TranslationBundle\Entity\Language;
use Oro\Bundle\TranslationBundle\Entity\Translation;
use Oro\Bundle\TranslationBundle\Entity\TranslationKey;
use Oro\Bundle\TranslationBundle\Provider\LanguageProvider;
use Oro\Bundle\TranslationBundle\Translation\EmptyArrayLoader;
use Oro\Bundle\TranslationBundle\Translation\Translator;

class OroTranslationLoadCommand extends ContainerAwareCommand
{
    const BATCH_INSERT_ROWS_COUNT = 50;

    /**
     * {@inheritdoc}
     */
    protected function configure()
    {
        $this
            ->setName('oro:translation:load')
            ->setDescription('Load translations into DB')
            ->addOption(
                'languages',
                'l',
                InputOption::VALUE_OPTIONAL | InputOption::VALUE_IS_ARRAY,
                'Languages to load.'
            )->addOption('rebuild-cache', 'rc', InputOption::VALUE_OPTIONAL, 'Rebuild translation cache');
    }

    /**
     * {@inheritdoc}
     */
    protected function execute(InputInterface $input, OutputInterface $output)
    {
        /** @var $languageProvider LanguageProvider */
        $languageProvider = $this->getContainer()->get('oro_translation.provider.language');
        $availableLocales = array_keys($languageProvider->getAvailableLanguages());

        $locales = $input->getOption('languages') ?: $availableLocales;

        $output->writeln(
            sprintf(
                '<info>Available locales</info>: %s. <info>Should be processed:</info> %s.',
                implode(', ', $availableLocales),
                implode(', ', $locales)
            )
        );

        // backup DB loader
        $translationLoader = $this->getContainer()->get('oro_translation.database_translation.loader');

        // disable DB loader to not get translations from database
        $this->getContainer()->set('oro_translation.database_translation.loader', new EmptyArrayLoader());

        if ($input->getOption('rebuild-cache')) {
            $this->getTranslator()->rebuildCache();
        }

        $start = time();
        $this->processLocales($locales, $output);
        $output->writeln(sprintf('<info>All messages successfully loaded.</info>'));

        // restore DB loader
        $this->getContainer()->set('oro_translation.database_translation.loader', $translationLoader);

        if ($input->getOption('rebuild-cache')) {
            $output->write(sprintf('<info>Rebuilding cache ... </info>'));
            $this->getTranslator()->rebuildCache();
        }

        $output->writeln(sprintf('<info>Done.</info>'));
        echo time() - $start;
    }

    /**
     * @param array $locales
     * @param OutputInterface $output
     * @return array
     */
    protected function processLocales(array $locales, OutputInterface $output)
    {
        $repoLanguage = $this->getEntityRepository(Language::class);
        $connection = $this->getEntityManager(Translation::class)->getConnection();

        foreach ($locales as $locale) {
            $language = $repoLanguage->findOneBy(['code' => $locale]);
            $domains = $this->getTranslator()->getCatalogue($locale)->all();

            $output->writeln(sprintf('<info>Loading translations [%s] (%d) ...</info>', $locale, count($domains)));

            $translationKeys = $this->processTranslationKeys($domains);
            $translations = $this->getTranslationsData($language->getId());
            $sqlData = [];
            foreach ($domains as $domain => $messages) {
                $output->write(sprintf('  > loading [%s] (%d) ... ', $domain, count($messages)));
                foreach ($messages as $key => $value) {
                    if (isset($translations[$translationKeys[$domain][$key]])) {
                        if ($translations[$translationKeys[$domain][$key]] === Translation::SCOPE_SYSTEM) {
                            $connection->update(
                                'oro_translation',
                                ['value' => $value],
                                [
                                    'translation_key_id' => $translationKeys[$domain][$key],
                                    'language_id' => $language->getId()
                                ]
                            );
                        }
                    } else {
                        $sqlData[] = sprintf(
                            '(%d, %d, %s, %s)',
                            $translationKeys[$domain][$key],
                            $language->getId(),
                            $connection->quote($value),
                            Translation::SCOPE_SYSTEM
                        );

                        if (self::BATCH_INSERT_ROWS_COUNT === count($sqlData)) {
                            $this->executeBatchTranslationInsert($sqlData);
                            $sqlData = [];
                        }
                    }
                }
                $output->writeln(sprintf('processed %d records.', count($messages)));
            }
            $this->executeBatchTranslationInsert($sqlData);
        }
    }

    /**
     * @param array $domains
     *
     * @return array
     */
    protected function processTranslationKeys(array $domains)
    {
        $connection = $this->getEntityManager(TranslationKey::class)->getConnection();
        $translationKeys = $this->getTranslationKeysData();
        $sql = sprintf(
            'INSERT INTO oro_translation_key (%s, %s) VALUES ',
            $connection->quoteIdentifier('domain'),
            $connection->quoteIdentifier('key')
        );
        $sqlData = [];
        $needUpdate = false;
        foreach ($domains as $domain => $messages) {
            foreach ($messages as $key => $value) {
                if (!isset($translationKeys[$domain][$key])) {
                    $sqlData[] = sprintf('(%s, %s)', $connection->quote($domain), $connection->quote($key));

                    if (self::BATCH_INSERT_ROWS_COUNT === count($sqlData)) {
                        $this->getEntityManager(Translation::class)
                            ->getConnection()
                            ->executeQuery($sql . implode(', ', $sqlData));
                        $translationKeys[$domain][$key] = 1;
                        $needUpdate = true;
                        $sqlData = [];
                    }
                }
            }
        }
        if (0 !== count($sqlData)) {
            $this->getEntityManager(Translation::class)->getConnection()->executeQuery($sql . implode(', ', $sqlData));
        }
        if ($needUpdate) {
            $translationKeys = $this->getTranslationKeysData();
        }

        return $translationKeys;
    }

    /**
     * @return array
     */
    protected function getTranslationKeysData()
    {
        $repository = $this->getEntityManager(TranslationKey::class)->getRepository(TranslationKey::class);
        $translationKeysData = $repository->createQueryBuilder('tk')
            ->select('tk.id, tk.domain, tk.key')
            ->getQuery()
            ->getArrayResult();
        $translationKeys = [];
        foreach ($translationKeysData as $item) {
            $translationKeys[$item['domain']][$item['key']] = $item['id'];
        }

        return $translationKeys;
    }

    /**
     * @param array $sqlData
{'fraction_non_alphanumeric': 0.13904814533397802, 'fraction_numerical': 0.0007598259307867653, 'mean_word_length': 1.9975155279503105, 'pattern_counts': {'":': 0, '<': 29, '<?xml version=': 0, '>': 163, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2371489', 'n_tokens_mistral': 3668, 'n_tokens_neox': 3549, 'n_words': 590}