<?php
/**
 * @author 1902 Software
 * @copyright Copyright 2023 1902 Software (https://1902software.com/magento/)
 * @package WriteTextAI_WriteTextAI
 */

namespace WriteTextAI\WriteTextAI\Plugin;

use Magento\Eav\Api\AttributeRepositoryInterface;
use Magento\Framework\App\RequestInterface;
use Magento\Framework\App\ProductMetadataInterface;
use Magento\Framework\View\Element\UiComponent\DataProvider\SearchResult;
use WriteTextAI\WriteTextAI\Helper\Data;
use WriteTextAI\WriteTextAI\Ui\DataProvider\Products\ListingDataProvider;

class AddAttributesToUiDataProvider
{
    /** @var AttributeRepositoryInterface */
    protected $attributeRepository;

    /** @var ProductMetadataInterface */
    protected $productMetadata;

    /** @var Data */
    protected $helper;

    /** @var RequestInterface */
    protected $request;

    /**
     * Constructor
     *
     * @param AttributeRepositoryInterface $attributeRepository
     * @param ProductMetadataInterface $productMetadata
     * @param Data $helper
     * @param RequestInterface $request
     */
    public function __construct(
        AttributeRepositoryInterface $attributeRepository,
        ProductMetadataInterface $productMetadata,
        Data $helper,
        RequestInterface $request
    ) {
        $this->attributeRepository = $attributeRepository;
        $this->productMetadata = $productMetadata;
        $this->helper = $helper;
        $this->request = $request;
    }

    /**
     * Get Search Result after plugin
     *
     * @param ListingDataProvider $subject
     * @param SearchResult $result
     * @return SearchResult
     */
    public function afterGetSearchResult(ListingDataProvider $subject, SearchResult $result)
    {
        if ($result->isLoaded()) {
            return $result;
        }

        $edition = $this->productMetadata->getEdition();

        $column = in_array($edition, ['Enterprise', 'B2B']) ? 'row_id' : 'entity_id';

        // join Magento stores
        // this should go first before joining attributes
        $result->getSelect()->join(
            ['store' => 'store'],
            '',
            ['store_id' => 'store.store_id']
        );

        $mappingSettings = $this->helper->getMappingSettings();
        $attributes = [
            'name' => 'product_name',
            'visibility' => 'visibility',
            'status' => 'status',
            $mappingSettings['page_title'] => 'mg_page_title',
            $mappingSettings['page_description'] => 'mg_page_description',
            $mappingSettings['product_description'] => 'mg_product_description',
            $mappingSettings['short_product_description'] => 'mg_short_product_description',
            'thumbnail' => 'thumbnail'
        ];
        $customOpenGraph = $this->helper->getCustomOpenGraph();
        if ($customOpenGraph) {
            $attributes[$mappingSettings['open_graph']] = 'mg_open_graph';
        }

        $defaultStore = \Magento\Store\Model\Store::DEFAULT_STORE_ID;

        $filters = (array)$this->request->getParam('filters');
        $storeId = isset($filters['store_id']) ? (int)$filters['store_id'] : $defaultStore;

        foreach ($attributes as $attributeCode => $tableAlias) {
            $attribute = $this->attributeRepository->get('catalog_product', $attributeCode);
            $backendTable = $attribute->getBackendTable();
            $attributeId = $attribute->getAttributeId();

            // join attribute value for current store
            $result->getSelect()->joinLeft(
                [$tableAlias => $backendTable],
                "{$tableAlias}.{$column} = main_table.{$column} AND 
                    {$tableAlias}.attribute_id = {$attributeId} AND 
                    store.store_id = {$tableAlias}.store_id",
                [$tableAlias => "{$tableAlias}.value"]
            );

            // join attributes value for default store
            $result->getSelect()->joinLeft(
                ["default_{$tableAlias}" => $backendTable],
                "default_{$tableAlias}.{$column} = main_table.{$column} AND 
                    default_{$tableAlias}.attribute_id = {$attributeId} AND 
                    default_{$tableAlias}.store_id = {$defaultStore}",
                ["default_{$tableAlias}" => "default_{$tableAlias}.value"]
            );
        }
        
        // join Write Ai Products table
        $result->getSelect()->joinLeft(
            ['wtai_products' => 'writetextai_writetextai_products_store'],
            'wtai_products.product_id = main_table.' . $column . ' AND store.store_id = wtai_products.store_id',
            [
                'edited_at' => 'wtai_products.edited_at',
                'transferred_at' => 'wtai_products.transferred_at'
            ]
        );

        // join product websites
        $websiteSelect = '(
            SELECT
                GROUP_CONCAT(cpw.website_id) as websites,
                cpw.product_id 
            FROM catalog_product_website cpw
            GROUP BY cpw.product_id
        )';
        $result->getSelect()->joinLeft(
            ['websites' => new \Zend_Db_Expr($websiteSelect)],
            'websites.product_id = main_table.' . $column,
            ['websites']
        );

        // join product categories
        $categorySelect = '(
            SELECT
                ccp.product_id,
                GROUP_CONCAT(DISTINCT ccp.category_id) as category_ids,
                GROUP_CONCAT(
                    DISTINCT COALESCE(category_name_store.value, category_name_default.value)
                    SEPARATOR ", "
                ) as category_names
            FROM catalog_category_product ccp
            INNER JOIN eav_entity_type category_entity_type
                ON category_entity_type.entity_type_code = "catalog_category"
            INNER JOIN eav_attribute category_name_attribute
                ON category_name_attribute.entity_type_id = category_entity_type.entity_type_id
                AND category_name_attribute.attribute_code = "name"
            LEFT JOIN catalog_category_entity_varchar category_name_store
                ON category_name_store.entity_id = ccp.category_id
                AND category_name_store.attribute_id = category_name_attribute.attribute_id
                AND category_name_store.store_id = ' . $storeId . '
            LEFT JOIN catalog_category_entity_varchar category_name_default
                ON category_name_default.entity_id = ccp.category_id
                AND category_name_default.attribute_id = category_name_attribute.attribute_id
                AND category_name_default.store_id = 0
            GROUP BY ccp.product_id
        )';
        $result->getSelect()->joinLeft(
            ['categories' => new \Zend_Db_Expr($categorySelect)],
            'categories.product_id = main_table.' . $column,
            [
                'category_ids' => 'categories.category_ids',
                'category_names' => 'categories.category_names'
            ]
        );

        $result->getSelect()
            ->distinct(true)
            ->group('main_table.entity_id');

        return $result;
    }
}
