<?php

declare(strict_types=1);

namespace FiloBlu\Refilo\Remote\Entity\Provider;

use Exception;
use FiloBlu\Refilo\Helper\Url;
use FiloBlu\Refilo\Remote\Entity\AbstractEntity;
use FiloBlu\Refilo\Remote\Entity\Provider\Filter\DisabledEntityFilterInterface;
use Magento\Catalog\Model\Category;
use Magento\Eav\Model\ResourceModel\Entity\Attribute;
use FiloBlu\Refilo\Remote\Entity\{UrlRewriteFactory, UrlRewrite};
use Magento\Catalog\Api\Data\CategoryInterface;
use Magento\Framework\App\Config\ScopeConfigInterface;
use Magento\Framework\App\ResourceConnection;
use Magento\Framework\DB\Select;
use Magento\Framework\EntityManager\MetadataPool;

/**
 * @class CategoryUrlProvider
 * @package FiloBlu\Refilo\Remote\Entity\Provider
 */
class CategoryUrlProvider extends AbstractUrlProvider
{

    /** @var string */
    public const CATEGORY_IDENTIFIER_ATTRIBUTE_CODE = 'refilo_category_identifier';

    /**
     * @var MetadataPool
     */
    private $metadataPool;
    /**
     * @var Attribute
     */
    private $eavAttribute;

    /**
     * @param ResourceConnection $resourceConnection
     * @param ScopeConfigInterface $scopeConfig
     * @param Url $urlHelper
     * @param UrlRewriteFactory $urlRewriteFactory
     * @param DisabledEntityFilterInterface $disabledEntityFilter
     * @param MetadataPool $metadataPool
     * @param Attribute $eavAttribute
     */
    public function __construct(
        ResourceConnection            $resourceConnection,
        ScopeConfigInterface          $scopeConfig,
        Url                           $urlHelper,
        UrlRewriteFactory             $urlRewriteFactory,
        DisabledEntityFilterInterface $disabledEntityFilter,
        MetadataPool                  $metadataPool,
        Attribute                     $eavAttribute
    ) {
        $this->metadataPool = $metadataPool;
        $this->eavAttribute = $eavAttribute;
        parent::__construct($resourceConnection, $scopeConfig, $urlHelper, $urlRewriteFactory, $disabledEntityFilter);
    }

    /**
     * @return Select
     * @throws Exception
     */
    public function getSelect(): Select
    {
        $connection = $this->getResourceConnection()->getConnection();
        $categoryEntityTable = $connection->getTableName('catalog_category_entity');
        $categoryEntityVarcharTable = $connection->getTableName('catalog_category_entity_varchar');
        $urlRewriteTable = $connection->getTableName('url_rewrite');
        $storeTable = $connection->getTableName('store');
        $linkField = $this->metadataPool->getMetadata(CategoryInterface::class)->getLinkField();
        $attributeId = $this->eavAttribute->getIdByCode(Category::ENTITY, self::CATEGORY_IDENTIFIER_ATTRIBUTE_CODE);

        $storeCondition = '!=0';

        if ($this->hasStore()) {
            $storeId = $this->getStore()->getId();
            $storeCondition = "= $storeId";
        }

        $disabledEntityIds = $this->getDisabledEntityFilter()->getIds($this->getStore());

        $select = $connection->select()->from(
            [
                'u' => $urlRewriteTable
            ], [
                AbstractEntity::ID => 'u.url_rewrite_id',
                UrlRewrite::URL_ID => 'u.url_rewrite_id',
                UrlRewrite::IS_REDIRECT => 'u.redirect_type',
                UrlRewrite::TYPE => 'u.entity_type',
                UrlRewrite::ENTITY_ID => 'u.entity_id',
                UrlRewrite::URL => 'u.request_path',
                UrlRewrite::TARGET_URL => 'u.target_path'
            ]
        )->joinInner(
            [
                's' => $storeTable
            ], "s.store_id = u.store_id AND u.store_id $storeCondition AND entity_type = 'category'",
            [
                UrlRewrite::STORE => 's.code'
            ]
        )->joinInner(
            [
                'c' => $categoryEntityTable
            ],
            'c.entity_id = u.entity_id ',
            [
                (string)($linkField) => "c.{$linkField}"
            ]
        )->joinLeft(
            [
                'ccev' => $categoryEntityVarcharTable
            ],
            "ccev.{$linkField} = c.{$linkField} AND ccev.attribute_id = {$attributeId} AND ccev.store_id = u.store_id",
            [
                UrlRewrite::CATEGORY_IDENTIFIER => 'ccev.value'
            ]
        );

        if (!empty($this->ids)) {
            $select->where(
                'u.url_rewrite_id IN (?)',
                $this->ids
            );
        }

        if (!empty($disabledEntityIds)) {
            $select->where('u.entity_id NOT IN (?)', $disabledEntityIds);
        }

        $select->order('u.target_path ASC');

        return $select;
    }
}
