<?php

namespace OfficeBrain\Bundle\SeoBundle\Command;

use Symfony\Bundle\FrameworkBundle\Command\ContainerAwareCommand;

use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Output\OutputInterface;
use Symfony\Component\Console\Input\InputOption;

use Symfony\Component\HttpKernel\Event\GetResponseEvent;
use Symfony\Component\HttpKernel\HttpKernelInterface;

use OfficeBrain\Bundle\SeoBundle\Entity\SeoDetail;
use OfficeBrain\Bundle\SeoBundle\Entity\SeoCategory;
use PHPExcel_IOFactory;

/**
 * Sync product Category data command
 *
 * @author OfficeBrain 4220 <info@officebrain.com>
 */
class SyncSeoProductCommand extends ContainerAwareCommand
{
    CONST COMMAND_NAME = 'office_brain:seo:sync:product';
    /**
     * {@inheritdoc}
     */
    protected function configure()
    {
        $this
            ->setName(self::COMMAND_NAME)
            ->setDescription('Command to sync product data')
            ->addOption('filename', null, InputOption::VALUE_REQUIRED, 'filename in .xlsx');
    }
    
    public function init() {
    	$this->entityManager = $this->getContainer()->get('doctrine.orm.entity_manager');
    }

    /**
     * {@inheritdoc}
     */
    protected function execute(InputInterface $input, OutputInterface $output)
    {
    	$this->init();
        $file = $input->getOption('filename', null);
        
        $filePath = $this->getContainer()->getParameter('kernel.root_dir') . '/../web/uploads/'.$file;

        $objReader= PHPExcel_IOFactory::createReader('Excel2007');
        $objReader->setReadDataOnly(true);
        $objPHPExcel=$objReader->load($filePath);
        $totalrows=$objPHPExcel->setActiveSheetIndex(0)->getHighestRow();
        $objWorksheet=$objPHPExcel->setActiveSheetIndex(0);
        if($totalrows>1){
            for($i=2; $i<=$totalrows; $i++)
            {
                $url = $objWorksheet->getCellByColumnAndRow(0,$i)->getValue();
                $path = parse_url($url, PHP_URL_PATH);

                // Step 2: Split the path by "/"
                $parts = explode('/', trim($path, '/'));
               
                // Step 3: Get the last part (slug)
                $type = $parts[1];
                $slug = end($parts);
                echo $metaTag = $this->sanitizeForMysql($objWorksheet->getCellByColumnAndRow(1,$i)->getValue());
                echo "\n";
                echo $metaDesc = $this->sanitizeForMysql($objWorksheet->getCellByColumnAndRow(2,$i)->getValue());
                
                
                
                if($type=='product'){
                    $this->syncMetaInProduct($slug,$metaTag,$metaDesc,$output,$type);
                }else{
                    $this->syncMetaInCategory($slug,$type,$metaTag,$metaDesc,$output);
                }
               
            }
        }
		$output->writeln('=======>sync complete<=======');
		return;
    }

	private function sanitizeForMysql($text) {
		// Remove 4-byte UTF-8 chars (emoji, narrow nb space, fancy unicode)
		$text = preg_replace('/[\xF0-\xF7][\x80-\xBF]{3}/', '', $text);

		// Normalize whitespace
		$text = preg_replace('/\s+/u', ' ', $text);
		
		$text = preg_replace('/[^\P{C}]+/u', '', $text);
		
		$text =  preg_replace('/[\x{202F}\x{2011}\x{2010}\x{00A0}\x{2007}\x{2009}\x{2060}\x{FEFF}]/u', ' ', $text);
		
		// remove any remaining invalid UTF-8 sequences
		$text= mb_convert_encoding($text, 'UTF-8', 'UTF-8');

		return trim($text);
	}

    private function syncMetaInProduct($slug,$metaTag,$metaDesc,$output,$type)
    {
        
        $em = $this->getContainer()->get('doctrine')->getManager();
        if ($em->getConnection()->ping() === false) {
            $em->getConnection()->close();
            $em->getConnection()->connect();
        }   

        $seoManager = $this->getContainer()->get('office_brain_seo_manager');
        $productObj = $seoManager->checkDublicateSlug($slug);
        if($productObj!=NULL){
            $obj = $productObj;
            $output->writeln("=======>Update for slug: ".$slug."<=====Type:===".$type."==\n");
        }else{
            $obj = new SeoDetail();
            $output->writeln("=======>Insert for slug: ".$slug."<=====Type:===".$type."==\n");
        }
        $obj->setMetaTitle($metaTag);
        $obj->setMetaDescription($metaDesc);
        $obj->setSlug($slug);
        $obj->setUserId(1);
        $obj->setInstanceType('distributor');
        $obj->setInstanceId(1);
        $obj->setProductSku('');
        $em->persist($obj);
        $em->flush();
         $output->writeln("=======>End for slug: ".$slug."<=====Type:===".$type."==== Done\n");
    }
    
    private function syncMetaInCategory($slug,$type,$metaTag,$metaDesc,$output)
    {
        $em = $this->getContainer()->get('doctrine')->getManager();
        if ($em->getConnection()->ping() === false) {
            $em->getConnection()->close();
            $em->getConnection()->connect();
        }   

        $seoManager = $this->getContainer()->get('office_brain_seo_manager');
        $categoryObj = $seoManager->checkDublicateCategorySlug($slug,$type);
        if($categoryObj!=NULL){
            $obj = $categoryObj;
            $output->writeln("=======>Update for slug: ".$slug."<=====Type:===".$type."==\n");
        }else{
            $obj = new SeoCategory();
            $output->writeln("=======>Insert for slug: ".$slug."<=====Type:===".$type."==\n");
        }
        $obj->setMetaTitle($metaTag);
        $obj->setMetaDescription($metaDesc);
        $obj->setProductSlug($slug);
        $obj->setUrlType($type);
        
        $em->persist($obj);
        $em->flush();
    }
}