<?php
declare(strict_types=1);

namespace Autogedal\Manufacturers\Service;

use Autogedal\Manufacturers\Model\Config;
use Magento\Catalog\Model\Product\Attribute\Source\Status;
use Magento\Catalog\Model\Product\Visibility;
use Magento\Catalog\Model\ResourceModel\Product\CollectionFactory as ProductCollectionFactory;
use Magento\CatalogInventory\Model\ResourceModel\Stock\Status as StockStatusResource;
use Magento\Store\Model\StoreManagerInterface;

class FeaturedSkuProvider
{
    private const LIMIT = 10;
    private const SKU_LIST_RESERVED_CHARACTERS = '[,;`|^&%]';

    public function __construct(
        private readonly ProductCollectionFactory $productCollectionFactory,
        private readonly Visibility $productVisibility,
        private readonly StockStatusResource $stockStatusResource,
        private readonly StoreManagerInterface $storeManager,
        private readonly Config $config
    ) {
    }

    public function getSkus(int $optionId): array
    {
        $store = $this->storeManager->getDefaultStoreView();

        $collection = $this->productCollectionFactory->create();
        $collection->addStoreFilter($store)
            ->setVisibility($this->productVisibility->getVisibleInCatalogIds())
            ->addAttributeToFilter('status', Status::STATUS_ENABLED)
            ->addAttributeToFilter($this->config->getProductAttributeId(), $optionId);
        $this->stockStatusResource->addStockDataToCollection($collection, false);

        $connection = $collection->getConnection();
        $soldQty = $connection->select()
            ->from(
                ['bestsellers' => $collection->getTable('sales_bestsellers_aggregated_yearly')],
                ['SUM(bestsellers.qty_ordered)']
            )
            ->where('bestsellers.product_id = e.entity_id')
            ->where('bestsellers.store_id = ?', (int)$store->getId());

        $collection->getSelect()
            ->where('e.sku NOT REGEXP ?', self::SKU_LIST_RESERVED_CHARACTERS)
            ->order('is_salable DESC')
            ->order(new \Zend_Db_Expr('(' . $soldQty . ') DESC'))
            ->order('e.entity_id DESC')
            ->limit(self::LIMIT);

        return array_column($connection->fetchAll($collection->getSelect()), 'sku');
    }
}