<?php

namespace Autogedal\InternalLinks\Block;

use Magento\Framework\View\Element\Template;
use Autogedal\InternalLinks\Model\Config;
use Autogedal\InternalLinks\Model\SlugHandler;
use Magento\Eav\Model\ResourceModel\Entity\Attribute\Option\CollectionFactory as OptionCollectionFactory;
use Magento\Catalog\Model\ResourceModel\Category\CollectionFactory as CategoryCollectionFactory;
use Magento\Catalog\Model\ResourceModel\Product\CollectionFactory as ProductCollectionFactory;
use Magento\Framework\UrlInterface;
use Magento\Store\Model\StoreManagerInterface;
use Amasty\Shopby\Helper\UrlBuilder;
use Magento\Framework\App\RequestInterface;
use Magento\Framework\App\ResourceConnection;
use Autogedal\InternalLinks\Model\Brand;

class BrandDetails extends Template
{
    private $config;
    private $optionCollectionFactory;
    private $categoryCollectionFactory;
    private $productCollectionFactory;
    private $storeManager;
    private $urlBuilder;
    private $request;
    private $resourceConnection;
    private $slugHandler;
    private $brand;

    private $catImages = [
        'Carlige de remorcare' => 'carlige.jpeg',
        'Covorase auto' => 'covorase.jpeg',
        'Tavite portbagaj' => 'tavite.jpeg',
        'Scuturi Metalice Auto' => 'scuturi.jpeg',
        'Bare transversale Menabo' => 'bare_menabo.jpeg',
        'Bare Transversale Green Valley' => 'bare_green_valley.jpeg',
    ];

    public function __construct(
        Template\Context $context,
        Config $config,
        SlugHandler $slugHandler,
        OptionCollectionFactory $optionCollectionFactory,
        CategoryCollectionFactory $categoryCollectionFactory,
        ProductCollectionFactory $productCollectionFactory,
        StoreManagerInterface $storeManager,
        UrlBuilder $urlBuilder,
        RequestInterface $request,
        ResourceConnection $resourceConnection,
        Brand $brand,
        array $data = []
    ) {
        $this->config = $config;
        $this->slugHandler = $slugHandler;
        $this->optionCollectionFactory = $optionCollectionFactory;
        $this->categoryCollectionFactory = $categoryCollectionFactory;
        $this->productCollectionFactory = $productCollectionFactory;
        $this->storeManager = $storeManager;
        $this->urlBuilder = $urlBuilder;
        $this->request = $request;
        $this->resourceConnection = $resourceConnection;
        $this->brand = $brand;
        parent::__construct($context, $data);
    }

    public function getBrandData()
    {
        $brandSlug = $this->request->getParam('brand');
        $brandAttributeId = $this->config->getBrandAttributeId();

        if (!$brandSlug || !$brandAttributeId) {
            return null;
        }

        $brandOption = $this->brand->getBySlug($brandSlug);
        if (!$brandOption) {
            return null;
        }

        $data = [
            'name' => $brandOption->value,
            'slug' => strtolower(str_replace('-', '_', $brandSlug)),
            'image' => $this->getBrandImageUrl($brandSlug),
            'categories' => []
        ];

        $categories = $this->getCategoriesWithBrandProducts($brandOption->option_id);
        $modelAttributeId = $this->config->getModelAttributeId();
        $modelSecondaryAttributeId = $this->config->getModelSecondaryAttributeId();

        foreach ($categories as $category) {
            $allModels = $this->getModelsInCategory($category->getId(), $brandAttributeId, $brandOption->option_id, $modelAttributeId, $modelSecondaryAttributeId);

            $data['categories'][] = [
                'id' => $category->getId(),
                'name' => $category->getName(),
                'url_key' => $category->getUrlKey(),
                'models' => $allModels,
                'has_more_models' => (count($allModels) && $category->getName() == 'Bare transversale Menabo') ? count($allModels) > $this->config->getModelLimit() : false
            ];
        }

        return $data;
    }

    private function getBrandImageUrl($slug)
    {
        $mediaUrl = $this->storeManager->getStore()->getBaseUrl(UrlInterface::URL_TYPE_MEDIA);
        $imagePath = 'wysiwyg/auto_brands/' . $slug . '.jpg';
        return $mediaUrl . $imagePath;
    }

    private function getModelsInCategory($categoryId, $brandAttributeId, $brandOptionId, $modelAttributeId, $modelSecondaryAttributeId)
    {
        $models = [];
        $connection = $this->resourceConnection->getConnection();

        $categoryProductTable = $connection->getTableName('catalog_category_product');
        $productTable = $connection->getTableName('catalog_product_entity');
        $productAttrTable = $connection->getTableName('catalog_product_entity_varchar');
        $productIntAttrTable = $connection->getTableName('catalog_product_entity_int');

        $select = $connection->select()
            ->distinct()
            ->from(['ccp' => $categoryProductTable], [])
            ->join(['cpe' => $productTable], 'cpe.entity_id = ccp.product_id', [])
            ->join(
                ['cpei' => $productIntAttrTable],
                'cpei.entity_id = ccp.product_id AND cpei.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = \'status\' AND entity_type_id = 4) AND cpei.value = 1',
                []
            )
            ->join(
                ['brand' => $productAttrTable],
                'brand.entity_id = cpe.entity_id AND brand.attribute_id = ' . (int)$brandAttributeId . ' AND brand.value = ' . (int)$brandOptionId,
                []
            )
            ->joinLeft(
                ['model' => $productAttrTable],
                'model.entity_id = cpe.entity_id AND model.attribute_id = ' . (int)$modelAttributeId . '',
                []
            )
            ->joinLeft(
                ['ea1' => 'eav_attribute'],
                'ea1.attribute_id = model.attribute_id',
                ['ea1.attribute_code']
            )
            ->joinLeft(
                ['model_secondary' => $productIntAttrTable],
                'model_secondary.entity_id = cpe.entity_id AND model_secondary.attribute_id = ' . (int)$modelSecondaryAttributeId . '',
                []
            )
            ->joinLeft(
                ['ea2' => 'eav_attribute'],
                'ea2.attribute_id = model_secondary.attribute_id',
                ['ea2.attribute_code']
            )
            ->join(
                ['opt' => 'eav_attribute_option'],
                'opt.option_id IN (model.value, model_secondary.value)',
                [])
            ->join(
                ['optval' => 'eav_attribute_option_value'],
                'optval.option_id = opt.option_id',
                [
                    'model_value' => 'optval.value',
                    'attribute_code' => new \Zend_Db_Expr('COALESCE(ea1.attribute_code, ea2.attribute_code)'),
                    'attribute_id' => new \Zend_Db_Expr('COALESCE(ea1.attribute_id, ea2.attribute_id)')
                ]
            )
            ->where('ccp.category_id = ?', $categoryId)
            ->order('optval.value ASC');

        $results = $connection->fetchAll($select);

        foreach ($results as $result) {
            $models[] = [
                'model' => $result['model_value'],
                'attribute_code' => $result['attribute_code'],
                'attribute_alias' => $this->getAttributeAlias($result['attribute_id']) ?: $result['attribute_code'],
                'slug' => $this->slugHandler->createSlug($result['model_value'], '_'),
            ];
        }

        return $models;
    }

    private function getAttributeAlias($attributeId)
    {
        $connection = $this->resourceConnection->getConnection();
        $eavAttrTable = $connection->getTableName('amasty_amshopby_filter_setting');

        $attributeAlias = $connection->fetchOne(
            $connection->select()
                ->from($eavAttrTable, 'attribute_url_alias')
                ->where('attribute_id = ?', $attributeId)
        );

        if ($attributeAlias && !empty($attributeAlias)) {
            $attributeAlias = json_decode($attributeAlias, true);

            if (isset($attributeAlias["1"])) {
                return $attributeAlias["1"];
            }
        }
        return null;
    }

    public function buildShopbyUrl($categorySlug, $brandSlug, $modelSlug, $attributeAlias)
    {
        $secureBaseUrl = $this->storeManager->getStore()->getBaseUrl(UrlInterface::URL_TYPE_WEB, true);
        $url = $secureBaseUrl . $categorySlug . '/marca-' . $brandSlug . '-' . $attributeAlias . '-' . $modelSlug . '.html';
        return $url;
    }

    public function buildCategoryUrl($categorySlug, $brandSlug)
    {
        $secureBaseUrl = $this->storeManager->getStore()->getBaseUrl(UrlInterface::URL_TYPE_WEB, true);
        return $secureBaseUrl . $categorySlug . '/marca-' . $brandSlug . '.html';
    }


    public function getModelLimit()
    {
        return (int)$this->config->getModelLimit();
    }

    private function getCategoriesWithBrandProducts($optionId)
    {
        $attributeCode = $this->getBrandAttributeCode();

        $categories = $this->categoryCollectionFactory->create()
            ->addAttributeToSelect('*')
            ->addFieldToFilter('is_active', 1)
            ->addFieldToFilter('level', 2)
            ->setOrder('position', 'ASC');

        $result = [];

        foreach ($categories as $category) {
            $productCollection = $this->productCollectionFactory->create();
            $productCollection->addAttributeToSelect($attributeCode)
                ->addCategoryFilter($category)
                ->addAttributeToFilter('status', 1)
                ->addAttributeToFilter($attributeCode, $optionId)
                ->setPageSize(1);

            if ($productCollection->getSize() > 0) {
                $result[] = $category;
            }
        }

        return $result;
    }

    private function getBrandAttributeCode()
    {
        $connection = $this->resourceConnection->getConnection();
        $eavAttrTable = $connection->getTableName('eav_attribute');
        $brandAttribute = $connection->fetchOne(
            $connection->select()
                ->from($eavAttrTable, 'attribute_code')
                ->where('attribute_id = ?', $this->config->getBrandAttributeId())
        );

        return $brandAttribute;
    }

    public function getCategoryImageUrl($categoryName)
    {
        $mediaUrl = $this->storeManager->getStore()->getBaseUrl(UrlInterface::URL_TYPE_MEDIA);
        $imagePath = 'wysiwyg/cat_images/' . $this->getCategoryImage($categoryName);
        return $mediaUrl . $imagePath;
    }


    public function categoryImageExists($categoryName)
    {
        $mediaPath = $this->storeManager->getStore()->getBaseMediaDir();
        $imagePath = $mediaPath . '/wysiwyg/cat_images/' . $this->getCategoryImage($categoryName);
        return file_exists($imagePath);
    }

    public function getCategoryImage($categoryName)
    {
        return $this->catImages[$categoryName] ?? null;
    }
}