<?php

namespace Leadlion\AutoRelated\Cron;

use Magento\Framework\App\ResourceConnection;
use Leadlion\AutoRelated\Helper\Data;

class ReorderLinkedProds
{
    const LINK_TYPE_UPSELL = 4;
    const CATEGORY_ID = 2118;

    protected $connection;
    protected $customHelper;
    protected $storeManager;

    public function __construct(
        ResourceConnection $connection,
        Data $customHelper,
        \Magento\Store\Model\StoreManagerInterface $storeManager
    ) {
        $this->connection = $connection;
        $this->customHelper = $customHelper;
        $this->storeManager = $storeManager;
    }

    public function execute()
    {
        $connection = $this->connection->getConnection();
        $linkTable = $connection->getTableName('catalog_product_link');
        $categoryTable = $connection->getTableName('catalog_category_product');
        $data = [];

        $query = 'select
            linkProd.link_id,
            linkProd.product_id,
            linkProd.linked_product_id,
            linkProd.link_type_id,
            GROUP_CONCAT(catProdLinked.category_id SEPARATOR ",") as linkedCategories
        from '.$linkTable.' as linkProd
        inner join '.$categoryTable.' as catProd 
            on linkProd.product_id = catProd.product_id and 
            catProd.category_id = '.self::CATEGORY_ID.'
        inner join '.$categoryTable.' as catProdLinked
            on linkProd.linked_product_id = catProdLinked.product_id
        where linkProd.link_type_id = '.self::LINK_TYPE_UPSELL.'
        group by linkProd.product_id, linkProd.linked_product_id';
        $result = $connection->fetchAll($query);

        $productUpsells = [];
        foreach ($result as $key => $info) {
            $productUpsells[$info['product_id']][] = $info;
        }

        $sortOrderUpsellCatIds = $this->getSortProdConf(self::CATEGORY_ID);
        foreach ($productUpsells as $productId => $upsellProducts) {
            $upsellPositions = [];
            $productByCategory = [];

            foreach ($upsellProducts as $info) {
                $catIds = explode(',', $info['linkedCategories']);
                $lastCategoryId = end($catIds);
                if ($lastCategoryId == $this->getRootCategoryId()) {
                    $lastCategoryId = prev($catIds);
                }
                $productByCategory[$lastCategoryId][] = $info;
            }

            if ($sortOrderUpsellCatIds) {
                $upsellPositions = $this->upsellSortedProds($sortOrderUpsellCatIds, $productByCategory, $upsellPositions);
            }

            foreach ($upsellPositions as $key => $info) {
                $data[] = [
                    'product_link_attribute_id' => self::LINK_TYPE_UPSELL,
                    'link_id' => $info['link_id'],
                    'value' => $key
                ];
            }
        }
        $linkAttributeIntTable = $connection->getTableName('catalog_product_link_attribute_int');
        $connection->insertOnDuplicate($linkAttributeIntTable, $data);
    }

    protected function upsellSortedProds($sortOrderUpsellCatIds, $productByCategory, $upsellPositions)
    {
        $returnProducts = true;
        foreach ($sortOrderUpsellCatIds as $sortCatUpsellId) {
            if (isset($productByCategory[$sortCatUpsellId])) {
                $returnProducts = false;
                $products = $productByCategory[$sortCatUpsellId];
                if (empty($products)) {
                    unset($productByCategory[$sortCatUpsellId]);
                }
                $counter = 0;
                foreach ($products as $key => $prod) {
                    if ($counter == 1) {
                        break;
                    }
                    
                    $upsellPositions[] = $prod;
                    $counter++;
                    unset($productByCategory[$sortCatUpsellId][$key]);
                }
            }
        }
        if (!$returnProducts) {
            return $this->upsellSortedProds($sortOrderUpsellCatIds, $productByCategory, $upsellPositions);
        } elseif (!empty($productByCategory)) {
            foreach ($productByCategory as $catId => $products) {
                if (!empty($products)) {
                    foreach ($products as $prod) {
                        $upsellPositions[] = $prod;
                    }
                }
            }
        }
        return $upsellPositions;
    }

    protected function getSortProdConf($categoryId)
    {
        $configuration = $this->customHelper->getUpsellSortOrder();

        foreach ($configuration as $key => $info) {
            if ($info['category'] == $categoryId) {
                return explode(',', $info['categories_ids_sort']);
            }
        }
        return null;
    }
    
    protected function getRootCategoryId()
    {
        return $this->storeManager->getStore()->getRootCategoryId();
    }
}