<?php
declare(strict_types=1);

namespace FiloBlu\Flow\Helper;

use Magento\Catalog\Model\Product\Type;
use Magento\Framework\App\ResourceConnection;

/**
 *
 */
class ProductLookupHelper
{
    /**
     * @var ResourceConnection
     */
    protected $resource;

    /**
     * @var
     */
    protected $connection;

    /**
     * @var array
     */
    protected $lookupBy = [];

    /**
     * @param ResourceConnection $resource
     */
    public function __construct(
        ResourceConnection $resource
    )
    {
        $this->resource = $resource;
    }

    /**
     * @param string $attributeCode in example ean, barcode
     * @param string $attributeValue
     * @return mixed|null
     */
    public function getSkuFromAttributeCode($attributeCode, $attributeValue)
    {
        if (!isset($this->lookupBy[$attributeCode])) {

            $this->lookupBy[$attributeCode] = [];
            $type_simple = Type::TYPE_SIMPLE;
            $catalog_product_type_id = 4;

            $sql = "SELECT  CPEV.value AS barcode, CPE.sku AS sku FROM catalog_product_entity CPE
                LEFT JOIN catalog_product_entity_varchar CPEV ON CPE.row_id = CPEV.row_id AND CPEV.attribute_id IN (
                    SELECT attribute_id
                    FROM eav_attribute
                    WHERE attribute_code = '{$attributeCode}' AND entity_type_id = {$catalog_product_type_id}
                    )
                WHERE CPE.type_id = '{$type_simple}'
                AND CPEV.value IS NOT NULL";

            $this->lookupBy[$attributeCode] = $this->resource->getConnection()->fetchPairs($sql) ?? [];
        }

        if (isset($this->lookupBy[$attributeCode][$attributeValue])) {
            return $this->lookupBy[$attributeCode][$attributeValue];
        }

        return null;
    }
}
