<?php

namespace Metal\CompaniesBundle\Command;

use Doctrine\DBAL\Connection;
use Metal\CompaniesBundle\Helper\DefaultHelper;
use Metal\ProjectBundle\Helper\TextHelper;
use Metal\ProjectBundle\Repository\SiteRepository;
use Symfony\Bundle\FrameworkBundle\Command\ContainerAwareCommand;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Input\InputOption;
use Symfony\Component\Console\Output\OutputInterface;

class GenerateSlugsCommand extends  ContainerAwareCommand
{
    protected function configure()
    {
        $this->setName('metal:companies:generate-slugs');
        $this->addOption('truncate', null, InputOption::VALUE_NONE);
    }

    protected function execute(InputInterface $input, OutputInterface $output)
    {
        $output->writeln(sprintf('%s: Start command "%s"', date('d.m.Y H:i:s'), $this->getName()));
        $textHelper = $this->getContainer()->get('brouzie.helper_factory')->get('MetalProjectBundle:Text');
        /* @var $textHelper TextHelper */

        $companiesHelper = $this->getContainer()->get('brouzie.helper_factory')->get('MetalCompaniesBundle');
        /* @var $companiesHelper DefaultHelper */

        $em = $this->getContainer()->get('doctrine');
        $siteRepository =  $em->getRepository('MetalProjectBundle:Site');
        /* @var $siteRepository SiteRepository */

        $siteRepository->disableLogging();
        $i = 0;
        $conn = $em->getConnection();
        /* @var $conn Connection  */
        $conn->getConfiguration()->setSQLLogger(null);

        if ($input->getOption('truncate')) {
            $conn->executeQuery('UPDATE Message75 SET slug = null');
        }

        $lastId = $conn->fetchColumn('SELECT MAX(Message_ID) FROM Message75');
        $companies = $conn->fetchAll('
          SELECT c.Message_ID, c.Keyword, cr.Keyword as city_slug, c.title as title, cc.base_host as host
          FROM Message75 c
          JOIN Classificator_Country cc ON c.country_id = cc.Country_ID
          JOIN Classificator_Region cr on c.company_city = cr.Region_ID
          WHERE c.slug IS NULL OR c.slug = c.Message_ID OR c.slug = ""
          ORDER BY c.Message_ID'
        );

        foreach ($companies as $company) {
            $i++;
            $companyId = $company['Message_ID'];

            if (!empty($company['Keyword']) && strlen($company['Keyword']) <= 15 && !is_numeric($company['Keyword'])) {
                $baseSlug = $textHelper->slugifyCompanyTitle($company['Keyword']);
            } else {
                $baseSlug = $textHelper->slugifyCompanyTitle($company['title']);
            }
            $slug = $baseSlug;
            $available = $companiesHelper->isSlugAvailable($slug);

            if (!$available && !empty($company['city_slug'])) {
                $slug = $baseSlug.'-'.$company['city_slug'];
            }
            $available = $companiesHelper->isSlugAvailable($slug);

            if (!$available) {
                $slug = $baseSlug.'-'.$companyId;
            }

            $conn->executeUpdate('UPDATE Message75 SET slug = :slug WHERE Message_ID = :id',
                array(
                    'slug' => $slug,
                    'id' => $companyId
                )
            );

            $output->writeln(sprintf('Generate Slug: %s for company %s', $slug, $companyId));

            if ($i % 50 == 0) {
                $output->writeln($companyId . '/' . $lastId);
            }
        }

        $companiesCountBadSlug = $conn->fetchColumn('
          SELECT count(c.Message_ID) AS count
          FROM Message75 c
          WHERE c.slug IS NULL OR c.slug = Message_ID OR c.slug = ""
          '
        );

        $output->writeln(sprintf('The number of companies with poor slugs : %s', $companiesCountBadSlug));

        $conn->executeUpdate("
            UPDATE Message75 c
            JOIN Classificator_Country cc ON c.country_id = cc.Country_ID
            SET domain = CONCAT(c.slug, '.', cc.base_host)");

        $siteRepository->restoreLogging();
        $output->writeln(sprintf('End command %s at %s', $this->getName(), date('Y-m-d H:i')));
    }
}