<?php
declare(strict_types=1);

namespace Autogedal\BlackFriday\Model;

use Magento\Framework\App\ResourceConnection;
use Magento\Framework\Exception\LocalizedException;
use Magento\Framework\FlagManager;

/** The Black Friday product list: import from the uploaded CSV, product ids and bestseller rank. */
class SkuList
{
    public const TABLE = 'autogedal_blackfriday_sku';
    public const MAX_SKUS = 50000;
    public const MAX_SKU_LENGTH = 64;   // Magento SKU limit, also the size of the column
    private const FLAG = 'autogedal_blackfriday_import';

    public function __construct(
        private readonly ResourceConnection $resource,
        private readonly Config $config,
        private readonly FlagManager $flagManager,
        private readonly CacheCleaner $cacheCleaner
    ) {
    }

    /**
     * Replaces the list with the SKUs of a one-column file (an optional "sku" header is skipped).
     *
     * @return array{imported: int, missing: string[], invalid: string[]}
     * @throws LocalizedException more than MAX_SKUS in the file
     */
    public function importCsv(string $content, string $fileName): array
    {
        $content = preg_replace('/^\xEF\xBB\xBF/', '', $content) ?? $content;
        $skus = [];
        $invalid = [];
        foreach (preg_split('/\r\n|\r|\n/', $content) ?: [] as $line) {
            $sku = trim((string)(preg_split('/[,;\t]/', $line)[0] ?? ''), " \t\"'");
            if ($sku === '' || strcasecmp($sku, 'sku') === 0) {
                continue;
            }
            if (mb_strlen($sku) > self::MAX_SKU_LENGTH) {
                $invalid[] = mb_substr($sku, 0, 30) . '...';   // cannot be a SKU of the catalog
                continue;
            }
            $skus[mb_strtoupper($sku)] = $sku;
            if (count($skus) > self::MAX_SKUS) {
                throw new LocalizedException(__('Fisierul are mai mult de %1 de SKU-uri. Lista nu a fost modificata.', self::MAX_SKUS));
            }
        }

        $connection = $this->resource->getConnection();
        $table = $this->resource->getTableName(self::TABLE);
        $connection->beginTransaction();
        try {
            $connection->delete($table);
            foreach (array_chunk(array_values($skus), 1000) as $chunk) {
                $connection->insertMultiple($table, array_map(fn ($sku) => ['sku' => $sku], $chunk));
            }
            $connection->commit();
        } catch (\Throwable $e) {
            $connection->rollBack();
            throw $e;
        }
        $this->refresh();

        $missing = $connection->fetchCol($connection->select()->from($table, ['sku'])->where('product_id IS NULL'));
        $result = ['imported' => count($skus) - count($missing), 'missing' => $missing, 'invalid' => $invalid];
        $this->flagManager->saveFlag(self::FLAG, $result + ['file' => $fileName, 'date' => date('Y-m-d H:i:s'), 'total' => count($skus)]);
        $this->cacheCleaner->clean();
        return $result;
    }

    /** Resolves the product ids (products created after the upload) and recomputes the bestseller rank. */
    public function refresh(): void
    {
        $connection = $this->resource->getConnection();
        $table = $this->resource->getTableName(self::TABLE);
        $connection->query(sprintf(
            'UPDATE %s s LEFT JOIN %s e ON e.sku = s.sku SET s.product_id = e.entity_id',
            $table,
            $this->resource->getTableName('catalog_product_entity')
        ));
        // Two steps on purpose: the aggregate alone uses the range on sales_order.created_at (EXPLAIN: range on
        // SALES_ORDER_CREATED_AT, ~26k rows locally), while the same query as a derived table of an UPDATE made MySQL
        // scan the whole store_id/state/created_at index (~216k rows). The ranks are then written by primary key.
        $sales = $connection->select()
            ->from(['i' => $this->resource->getTableName('sales_order_item')], ['product_id', 'qty' => new \Zend_Db_Expr('SUM(i.qty_ordered)')])
            ->join(['o' => $this->resource->getTableName('sales_order')], 'o.entity_id = i.order_id', [])
            ->where('o.state <> ?', 'canceled')
            ->where('o.created_at >= ?', date('Y-m-d H:i:s', time() - $this->config->getBestsellerDays() * 86400))
            ->group('i.product_id');
        $qtyByProduct = $connection->fetchPairs($sales);

        $rows = [];
        foreach ($connection->fetchPairs($connection->select()->from($table, ['sku', 'product_id'])) as $sku => $productId) {
            $rows[] = ['sku' => $sku, 'sales_rank' => (float)($qtyByProduct[$productId] ?? 0)];
        }
        foreach (array_chunk($rows, 1000) as $chunk) {
            $connection->insertOnDuplicate($table, $chunk, ['sales_rank']);
        }
        $this->cacheCleaner->clean();
    }

    /** @return array{file?: string, date?: string, total?: int, imported?: int, missing?: string[]} */
    public function getLastImport(): array
    {
        return (array)($this->flagManager->getFlagData(self::FLAG) ?: []);
    }

    /**
     * SKU => product id of the list, without the excluded SKUs.
     *
     * @return array<string, int>
     */
    public function getProductIds(): array
    {
        $connection = $this->resource->getConnection();
        $select = $connection->select()
            ->from($this->resource->getTableName(self::TABLE), ['sku', 'product_id'])
            ->where('product_id IS NOT NULL');
        $excluded = $this->config->getExcludedSkus();
        if ($excluded) {
            $select->where('UPPER(sku) NOT IN (?)', $excluded);
        }
        return array_map('intval', $connection->fetchPairs($select));
    }

    /** @return array<int, float> product id => qty sold in the bestseller period */
    public function getRanks(): array
    {
        $connection = $this->resource->getConnection();
        return array_map('floatval', $connection->fetchPairs(
            $connection->select()->from($this->resource->getTableName(self::TABLE), ['product_id', 'sales_rank'])->where('product_id IS NOT NULL')
        ));
    }
}