<?php
declare(strict_types=1);

namespace Autogedal\EanTools\Model\ResourceModel;

use Magento\Catalog\Model\Product;
use Magento\Catalog\Model\Product\Attribute\Source\Status;
use Magento\Eav\Model\Config as EavConfig;
use Magento\Framework\App\ResourceConnection;
use Magento\Framework\DB\Adapter\AdapterInterface;
use Magento\Framework\DB\Select;
use Magento\Framework\Exception\LocalizedException;

class Ean
{
    public const ATTRIBUTE_CODE = 'emkp_ean';

    private const STORE_ID = 0;
    private const CHUNK_SIZE = 2000;

    private const NAME_ATTRIBUTE = 'name';
    private const STATUS_ATTRIBUTE = 'status';
    private const SKU_FZ_ATTRIBUTE = 'sku_fz';
    private const MANUFACTURER_ATTRIBUTE = 'manufacturer';

    private const PRODUCT_TABLE = 'catalog_product_entity';
    private const VARCHAR_TABLE = 'catalog_product_entity_varchar';
    private const INT_TABLE = 'catalog_product_entity_int';
    private const OPTION_VALUE_TABLE = 'eav_attribute_option_value';

    private array $attributeIds = [];
    private array $tableNames = [];

    public function __construct(
        private readonly ResourceConnection $resource,
        private readonly EavConfig $eavConfig
    ) {}

    public function getEanAttributeCode(): string
    {
        return self::ATTRIBUTE_CODE;
    }

    public function getEanAttributeId(): int
    {
        if (!isset($this->attributeIds[self::ATTRIBUTE_CODE])) {
            $attribute = $this->eavConfig->getAttribute(Product::ENTITY, self::ATTRIBUTE_CODE);
            if (!$attribute || !$attribute->getId()) {
                throw new LocalizedException(
                    __(
                        'The "%1" product attribute does not exist.',
                        self::ATTRIBUTE_CODE
                    )
                );
            }
            if ($attribute->getBackendType() !== 'varchar') {
                throw new LocalizedException(
                    __(
                        'The "%1" attribute must use the text (varchar) backend type; its current type is "%2".',
                        self::ATTRIBUTE_CODE,
                        $attribute->getBackendType()
                    )
                );
            }
            $this->attributeIds[self::ATTRIBUTE_CODE] = (int)$attribute->getId();
        }

        return $this->attributeIds[self::ATTRIBUTE_CODE];
    }

    public function getEanTable(): string
    {
        return $this->tableName(self::VARCHAR_TABLE);
    }

    public function findUsedEans(array $eans): array
    {
        if ($eans === []) {
            return [];
        }

        $connection = $this->getConnection();
        $baseSelect = $this->baseEanSelect();
        $used = [];

        foreach (array_chunk(array_values(array_unique($eans)), self::CHUNK_SIZE) as $chunk) {
            $select = (clone $baseSelect)->where('v.value IN (?)', $chunk);

            $statement = $connection->query($select);
            while ($row = $statement->fetch()) {
                $used[$row['value']][] = [
                    'sku' => (string)$row['sku'],
                    'sku_fz' => (string)($row['sku_fz'] ?? ''),
                    'entity_id' => (int)$row['entity_id'],
                    'name' => (string)($row['name'] ?? ''),
                    'type_id' => (string)$row['type_id'],
                    'status' => $row['status'] ?? null,
                    'manufacturer' => (string)($row['manufacturer'] ?? ''),
                ];
            }
            $statement->closeCursor();
        }

        return $used;
    }

    public function streamUsedEans(callable $callback): int
    {
        return $this->stream($this->baseEanSelect()->order('v.value ASC'), $callback);
    }

    public function streamEanValues(callable $callback): int
    {
        $select = $this->getConnection()->select()
            ->from($this->getEanTable(), ['value'])
            ->where('attribute_id = ?', $this->getEanAttributeId())
            ->where('store_id = ?', self::STORE_ID)
            ->where('value IS NOT NULL')
            ->where("value != ''");

        return $this->stream($select, static function (array $row) use ($callback): void {
            $callback((string)$row['value']);
        });
    }

    public function streamDuplicateEans(callable $callback): int
    {
        $select = $this->baseEanSelect()
            ->join(['dup' => $this->duplicateEanSelect()], 'dup.value = v.value', [])
            ->order(['v.value ASC', 'e.sku ASC']);

        return $this->stream($select, $callback);
    }

    public function getMaxProductsPerDuplicateEan(): int
    {
        $connection = $this->getConnection();
        $select = $connection->select()
            ->from(['t' => $this->duplicateEanSelect()], ['mx' => 'MAX(t.cnt)']);

        return (int)$connection->fetchOne($select);
    }

    public function countDuplicateEanValues(): int
    {
        $connection = $this->getConnection();
        $select = $connection->select()
            ->from(['t' => $this->duplicateEanSelect()], ['cnt' => 'COUNT(*)']);

        return (int)$connection->fetchOne($select);
    }

    public function countUsedEans(): int
    {
        $connection = $this->getConnection();
        $select = $connection->select()
            ->from($this->getEanTable(), ['cnt' => 'COUNT(*)'])
            ->where('attribute_id = ?', $this->getEanAttributeId())
            ->where('store_id = ?', self::STORE_ID)
            ->where('value IS NOT NULL')
            ->where("value != ''");

        return (int)$connection->fetchOne($select);
    }

    public function countProductsByType(): array
    {
        $connection = $this->getConnection();
        $select = $connection->select()
            ->from($this->tableName(self::PRODUCT_TABLE), ['type_id', 'cnt' => 'COUNT(*)'])
            ->group('type_id');

        return array_map('intval', $connection->fetchPairs($select));
    }

    public function streamProductsWithoutEan(array $filters, callable $callback): int
    {
        return $this->stream($this->missingEanSelect($filters, true), $callback);
    }

    public function countProductsWithoutEan(array $filters): int
    {
        $select = $this->missingEanSelect($filters, false)->columns(['cnt' => 'COUNT(*)']);

        return (int)$this->getConnection()->fetchOne($select);
    }

    private function missingEanSelect(array $filters, bool $withProductData): Select
    {
        $status = $filters['status'] ?? 'all';
        $filtersOnStatus = $status === 'enabled' || $status === 'disabled';

        $select = $this->getConnection()->select()
            ->from(
                ['e' => $this->tableName(self::PRODUCT_TABLE)],
                $withProductData ? ['entity_id', 'sku', 'type_id'] : []
            )
            ->joinLeft(
                ['v' => $this->getEanTable()],
                $this->attributeCondition('v', $this->getEanAttributeId()),
                $withProductData ? ['ean' => 'v.value'] : []
            )
            ->where("v.value IS NULL OR v.value = ''");

        if ($withProductData) {
            $select->joinLeft(
                ['n' => $this->getEanTable()],
                $this->attributeCondition('n', $this->attributeId(self::NAME_ATTRIBUTE)),
                ['name' => 'n.value']
            );
        }

        if ($withProductData || $filtersOnStatus) {
            $select->joinLeft(
                ['s' => $this->tableName(self::INT_TABLE)],
                $this->attributeCondition('s', $this->attributeId(self::STATUS_ATTRIBUTE)),
                $withProductData ? ['status' => 's.value'] : []
            );
        }

        if ($filtersOnStatus) {
            $select->where('s.value = ?', $status === 'enabled' ? Status::STATUS_ENABLED : Status::STATUS_DISABLED);
        }

        $types = $filters['types'] ?? [];
        if ($types !== []) {
            $select->where('e.type_id IN (?)', $types);
        }

        if ($withProductData) {
            $this->joinProductColumns($select);
            $select->order('e.entity_id ASC');
        }

        return $select;
    }

    private function baseEanSelect(): Select
    {
        $select = $this->getConnection()->select()
            ->from(['v' => $this->getEanTable()], ['value', 'entity_id'])
            ->join(
                ['e' => $this->tableName(self::PRODUCT_TABLE)],
                'e.entity_id = v.entity_id',
                ['sku', 'type_id']
            )
            ->joinLeft(
                ['n' => $this->getEanTable()],
                $this->attributeCondition('n', $this->attributeId(self::NAME_ATTRIBUTE)),
                ['name' => 'n.value']
            )
            ->joinLeft(
                ['s' => $this->tableName(self::INT_TABLE)],
                $this->attributeCondition('s', $this->attributeId(self::STATUS_ATTRIBUTE)),
                ['status' => 's.value']
            );

        $this->joinProductColumns($select);

        return $select
            ->where('v.attribute_id = ?', $this->getEanAttributeId())
            ->where('v.store_id = ?', self::STORE_ID)
            ->where('v.value IS NOT NULL')
            ->where("v.value != ''");
    }

    private function duplicateEanSelect(): Select
    {
        return $this->getConnection()->select()
            ->from($this->getEanTable(), ['value', 'cnt' => 'COUNT(*)'])
            ->where('attribute_id = ?', $this->getEanAttributeId())
            ->where('store_id = ?', self::STORE_ID)
            ->where('value IS NOT NULL')
            ->where("value != ''")
            ->group('value')
            ->having('COUNT(*) > 1');
    }

    private function joinProductColumns(Select $select): void
    {
        $select
            ->joinLeft(
                ['f' => $this->getEanTable()],
                $this->attributeCondition('f', $this->attributeId(self::SKU_FZ_ATTRIBUTE)),
                ['sku_fz' => 'f.value']
            )
            ->joinLeft(
                ['m' => $this->getEanTable()],
                $this->attributeCondition('m', $this->attributeId(self::MANUFACTURER_ATTRIBUTE)),
                []
            )
            ->joinLeft(
                ['mo' => $this->tableName(self::OPTION_VALUE_TABLE)],
                'mo.option_id = m.value AND mo.store_id = ' . self::STORE_ID,
                ['manufacturer' => 'mo.value']
            );
    }

    private function attributeCondition(string $alias, int $attributeId): string
    {
        return $this->getConnection()->quoteInto(
            sprintf(
                '%1$s.entity_id = e.entity_id AND %1$s.attribute_id = ? AND %1$s.store_id = %2$d',
                $alias,
                self::STORE_ID
            ),
            $attributeId
        );
    }

    private function stream(Select $select, callable $callback): int
    {
        $statement = $this->getConnection()->query($select);
        $count = 0;
        while ($row = $statement->fetch()) {
            $callback($row);
            $count++;
        }
        $statement->closeCursor();

        return $count;
    }

    private function attributeId(string $code): int
    {
        return $this->attributeIds[$code] ??= (int)$this->eavConfig
            ->getAttribute(Product::ENTITY, $code)
            ?->getId();
    }

    private function tableName(string $name): string
    {
        return $this->tableNames[$name] ??= $this->resource->getTableName($name);
    }

    private function getConnection(): AdapterInterface
    {
        return $this->resource->getConnection();
    }
}