<?php

namespace Autogedal\CategoryReviews\Model\Indexer;

use Magento\Framework\Indexer\ActionInterface;
use Magento\Framework\Mview\ActionInterface as MviewActionInterface;
use Magento\Framework\App\ResourceConnection;
use Magento\Store\Model\StoreManagerInterface;
use Magento\Catalog\Model\ResourceModel\Category\CollectionFactory as CategoryCollectionFactory;
use Magento\Framework\Indexer\CacheContext;
use Magento\Framework\Event\ManagerInterface as EventManagerInterface;

class CategoryReviewsIndexer implements ActionInterface, MviewActionInterface
{
    private $resource;
    private $storeManager;
    private $categoryCollectionFactory;
    private $cacheContext;
    private $eventManager;

    public function __construct(
        ResourceConnection $resource,
        StoreManagerInterface $storeManager,
        CategoryCollectionFactory $categoryCollectionFactory,
        CacheContext $cacheContext,
        EventManagerInterface $eventManager
    ) {
        $this->resource = $resource;
        $this->storeManager = $storeManager;
        $this->categoryCollectionFactory = $categoryCollectionFactory;
        $this->cacheContext = $cacheContext;
        $this->eventManager = $eventManager;
    }

    public function execute($ids = null)
    {
        $this->executeList($ids);
    }

    public function executeFull()
    {
        $connection = $this->resource->getConnection();
        $tableName = $this->resource->getTableName('category_reviews_aggregate');
        
        $connection->truncateTable($tableName);
        
        foreach ($this->storeManager->getStores() as $store) {
            $this->processStore($store->getId());
        }

        $this->cacheContext->registerTags(['cat_c']);
        $this->eventManager->dispatch('clean_cache_by_tags', ['object' => $this->cacheContext]);
    }

    public function executeList(array $ids)
    {
        if (empty($ids)) {
            return;
        }

        $categoryIds = $this->getCategoryIdsFromChangeIds($ids);

        foreach ($this->storeManager->getStores() as $store) {
            $this->processCategories($categoryIds, $store->getId());
        }

        $this->cacheContext->registerEntities('category', $categoryIds);
        $this->eventManager->dispatch('clean_cache_by_tags', ['object' => $this->cacheContext]);
    }

    public function executeRow($id)
    {
        $this->executeList([$id]);
    }

    private function getCategoryIdsFromChangeIds(array $ids)
    {
        $connection = $this->resource->getConnection();
        $categoryIds = [];

        $select = $connection->select()
            ->from(
                ['r' => $this->resource->getTableName('review')],
                []
            )
            ->joinInner(
                ['ccp' => $this->resource->getTableName('catalog_category_product')],
                'r.entity_pk_value = ccp.product_id',
                ['category_id']
            )
            ->where('r.review_id IN (?)', $ids)
            ->distinct();

        $reviewCategoryIds = $connection->fetchCol($select);
        $categoryIds = array_merge($categoryIds, $reviewCategoryIds);

        $directCategoryIds = array_filter($ids, function($id) {
            return is_numeric($id) && $id > 0;
        });
        $categoryIds = array_merge($categoryIds, $directCategoryIds);

        return array_unique($categoryIds);
    }

    private function processStore($storeId)
    {
        $categories = $this->categoryCollectionFactory->create()
            ->setStoreId($storeId)
            ->addAttributeToSelect('entity_id');

        $categoryIds = $categories->getAllIds();
        $this->processCategories($categoryIds, $storeId);
    }

    private function processCategories(array $categoryIds, $storeId)
    {
        $connection = $this->resource->getConnection();
        $tableName = $this->resource->getTableName('category_reviews_aggregate');

        foreach ($categoryIds as $categoryId) {
            $connection->delete($tableName, [
                'category_id = ?' => $categoryId,
                'store_id = ?' => $storeId
            ]);

            $data = $this->calculateCategoryReviewData($categoryId, $storeId);
            
            if ($data) {
                $connection->insert($tableName, $data);
            }
        }
    }

    private function calculateCategoryReviewData($categoryId, $storeId)
    {
        $connection = $this->resource->getConnection();
        
        $select = $connection->select()
            ->from(
                ['ccp' => $this->resource->getTableName('catalog_category_product')],
                []
            )
            ->joinInner(
                ['cpe' => $this->resource->getTableName('catalog_product_entity')],
                'ccp.product_id = cpe.entity_id',
                []
            )
            ->joinInner(
                ['r' => $this->resource->getTableName('review')],
                'cpe.entity_id = r.entity_pk_value AND r.entity_id = 1', // 1 = product entity
                []
            )
            ->joinInner(
                ['rs' => $this->resource->getTableName('review_store')],
                'r.review_id = rs.review_id',
                []
            )
            ->joinLeft(
                ['rov' => $this->resource->getTableName('rating_option_vote')],
                'r.review_id = rov.review_id',
                []
            )
            ->columns([
                'review_count' => 'COUNT(DISTINCT r.review_id)',
                'rating_sum' => 'SUM(COALESCE(rov.percent, 0) / 20)',
                'avg_rating' => 'AVG(COALESCE(rov.percent, 0) / 20)'
            ])
            ->where('ccp.category_id = ?', $categoryId)
            ->where('rs.store_id = ?', $storeId)
            ->where('r.status_id = ?', \Magento\Review\Model\Review::STATUS_APPROVED)
            ->group(['ccp.category_id', 'rs.store_id']);

        $result = $connection->fetchRow($select);

        if ($result && $result['review_count'] > 0) {
            return [
                'category_id' => $categoryId,
                'store_id' => $storeId,
                'review_count' => (int)$result['review_count'],
                'rating_sum' => round((float)$result['rating_sum'], 2),
                'avg_rating' => round((float)$result['avg_rating'], 2),
                'updated_at' => date('Y-m-d H:i:s')
            ];
        }

        return null;
    }
}