<?php
/**
 * Plumrocket Inc.
 *
 * NOTICE OF LICENSE
 *
 * This source file is subject to the End-user License Agreement
 * that is available through the world-wide-web at this URL:
 * http://wiki.plumrocket.net/wiki/EULA
 * If you are unable to obtain it through the world-wide-web, please
 * send an email to support@plumrocket.com so we can send you a copy immediately.
 *
 * @package     Plumrocket Product Filter v3.x.x
 * @copyright   Copyright (c) 2016 Plumrocket Inc. (http://www.plumrocket.com)
 * @license     http://wiki.plumrocket.net/wiki/EULA  End-user License Agreement
 */

namespace Plumrocket\ProductFilter\Model\ResourceModel\Layer\Filter;

class CustomOption extends \Magento\Framework\Model\ResourceModel\Db\AbstractDb
{
    /**
     * @var \Magento\Store\Model\StoreManagerInterface
     */
    private $storeManager;

    /**
     * CustomOption constructor.
     *
     * @param \Magento\Framework\Model\ResourceModel\Db\Context $context
     * @param \Magento\Store\Model\StoreManagerInterface        $storeManager
     * @param null                                              $connectionName
     */
    public function __construct(
        \Magento\Framework\Model\ResourceModel\Db\Context $context,
        \Magento\Store\Model\StoreManagerInterface $storeManager,
        $connectionName = null
    ) {
        parent::__construct($context, $connectionName);
        $this->storeManager = $storeManager;
    }

    /**
     * Initialize connection and define main table name
     *
     * @return void
     */
    protected function _construct()
    {
        $this->_init('catalog_product_option_title', 'title');
    }

    /**
     * Apply custom option filter to product collection
     *
     * @param \Magento\Catalog\Model\Layer\Filter\FilterInterface $filter
     * @param  string $var
     * @param string $value
     * @return $this
     */
    public function applyFilterToCollection(\Magento\Catalog\Model\Layer\Filter\FilterInterface $filter, $var, $value)
    {
        $additionalValue = str_replace("_", " ", $value);

        $collection = $filter->getLayer()->getProductCollection();
        $select = $collection->getSelect();
        $select->where('type_title_s.title IN ' . "('".  $value . "', '".  $additionalValue . "')");
        $select->where('option_title_s.title = ?', $var);

        try {
            if ($collection->getSelect()->getPart('option_main_s')) {
                return $this;
            }
        } catch (\Zend_Db_Select_Exception $e) {
            $select
                ->joinLeft(
                    ['option_main_s' => $this->getTable('catalog_product_option')],
                    'e.entity_id = option_main_s.product_id',
                    ['product_option_sku' => 'sku']
                )
                ->joinLeft(
                    ['option_title_s' => $this->getTable('catalog_product_option_title')],
                    'option_main_s.option_id = option_title_s.option_id'
                )
                ->joinLeft(
                    ['type_value_s' => $this->getTable('catalog_product_option_type_value')],
                    'option_title_s.option_id = type_value_s.option_id',
                    ['option_type_value_sku' => 'sku']
                )
                ->joinLeft(
                    ['type_title_s' => $this->getTable('catalog_product_option_type_title')],
                    'type_title_s.option_type_id = type_value_s.option_type_id'
                );

            $select->where('type_title_s.title IN ' . "('".  $value . "', '".  $additionalValue . "')");
            $select->where('option_title_s.title = ?', $var);
            $select->group('e.entity_id');
        }

        $data = $this->getConnection()->fetchAll($select);

        return $this;
    }

    /**
     * @param \Magento\Catalog\Model\Layer\Filter\FilterInterface $filter
     * @return array
     */
    public function loadFilterItemsData(\Magento\Catalog\Model\Layer\Filter\FilterInterface $filter)
    {
        // Clone select from collection with filters
        $select = clone $filter->getLayer()->getProductCollection()->getSelect();
        $storeId = $this->storeManager->getStore()->getId();
        $productOptionTitleTable = $this->getTable('catalog_product_option_type_title');
        $connection = $this->getConnection();

        // Reset columns, order and limitation conditions
        $select->reset(\Magento\Framework\DB\Select::COLUMNS);
        $select->reset(\Magento\Framework\DB\Select::ORDER);
        $select->reset(\Magento\Framework\DB\Select::LIMIT_COUNT);
        $select->reset(\Magento\Framework\DB\Select::LIMIT_OFFSET);
        $select->reset(\Magento\Framework\DB\Select::GROUP);

        $titleExpr = $connection->getCheckSql(
            'store_option_title.title IS NULL',
            'default_option_title.title',
            'store_option_title.title'
        );

        $select
            ->joinLeft(
                ['option_main'=> $this->getTable('catalog_product_option')],
                'e.entity_id = option_main.product_id',
                []
            )
            ->joinLeft(
                ['option_title'=> $this->getTable('catalog_product_option_title')],
                'option_main.option_id = option_title.option_id',
                []
            )
            ->joinLeft(
                ['type_value'=> $this->getTable('catalog_product_option_type_value')],
                'option_title.option_id = type_value.option_id',
                []
            )
            ->joinLeft(
                ['type_title'=> $this->getTable('catalog_product_option_type_title')],
                'type_title.option_type_id = type_value.option_type_id',
                []
            )->join(
                ['default_option_title' => $productOptionTitleTable],
                'default_option_title.option_type_id = type_value.option_type_id',
                ['default_title' => 'title']
            )->joinLeft(
                ['store_option_title' => $productOptionTitleTable],
                'store_option_title.option_type_id = type_value.option_type_id AND ' . $connection->quoteInto(
                    'store_option_title.store_id = ?',
                    $storeId
                ),
                ['store_title' => 'title', 'title' => $titleExpr]
            );

        $select->where('option_main.product_id = e.entity_id')
            ->where('option_title.title = ?', $filter->getName())
            ->where(
                'default_option_title.store_id = ?',
                \Magento\Store\Model\Store::DEFAULT_STORE_ID
            );

        $select->group('title');
        $select->columns(
            ['product_count' => new \Zend_Db_Expr('COUNT(DISTINCT e.entity_id)')]
        );

        return $connection->fetchAll($select);
    }

    /**
     * @deprecated since 3.2.6
     * @see loadFilterItemsData
     *
     * Retrieve array with stock status data of products
     *
     * @param \Magento\Catalog\Model\Layer\Filter\FilterInterface $filter
     * @throws \Magento\Framework\Exception\LocalizedException
     * @return array
     */
    public function getCount(\Magento\Catalog\Model\Layer\Filter\FilterInterface $filter)
    {
        return $this->loadFilterItemsData($filter);
    }
}
