<?php

namespace FiloBlu\Flow\Model\Channel\In;

use Exception;
use FiloBlu\Flow\Helper\LoggerProvider;
use FiloBlu\Flow\Model\Channel\ConfigFactory;
use FiloBlu\Flow\Model\Imagesflow;
use FiloBlu\Flow\Model\ImagesflowFactory;
use FiloBlu\Flow\Model\Manager\ProductImageManager;
use Magento\Catalog\Model\Product;
use Magento\Catalog\Model\Product\Image\CacheFactory;
use Magento\Catalog\Model\ProductRepository;
use Magento\Eav\Model\ResourceModel\Entity\Attribute\Collection;
use Magento\Framework\App\Filesystem\DirectoryList;
use Magento\Framework\App\ObjectManager;
use Magento\Framework\App\ProductMetadataInterface;
use Magento\Framework\App\ResourceConnection;
use Magento\Framework\DataObject;
use Magento\Framework\DB\Adapter\AdapterInterface;
use Magento\Framework\Event\Manager;
use Magento\Framework\Exception\FileSystemException;
use Magento\Framework\Filesystem;
use Magento\Framework\Filesystem\Directory\WriteInterface;
use Magento\Framework\Model\ResourceModel\Iterator;
use Magento\Framework\Model\ResourceModel\IteratorFactory;
use Magento\Framework\ObjectManagerInterface;
use Magento\MediaStorage\Model\File\Uploader;
use Throwable;
use Zend_Db;
use Zend_Db_Expr;
use Zend_Db_Statement_Exception;
use function array_key_exists;

/**
 * Class Images
 * @package FiloBlu\Flow\Model\Channel\In
 * @method getVideoToProcessCount()
 * @method getProcessedVideoErrorCount()
 */
class Images extends AbstractModel
{
    /**
     * @var string
     */
    const STATUS_RECEIVED = 'received';

    /**
     * @var string
     */
    const STATUS_PROCESSED = 'processed';

    /**
     * @var string
     */
    const STATUS_PARSED = 'parsed';

    /**
     * @var string
     */
    const STATUS_ERROR = 'error';

    /**
     * @var \FiloBlu\Flow\Helper\Images
     */
    protected $imagesHelper;

    /**
     * @var ResourceConnection
     */
    protected $resourceConnection;

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

    /**
     * @var ProductRepository
     */
    protected $productRepository;

    /**
     * @var Filesystem
     */
    protected $filesystem;

    /**
     * @var WriteInterface
     */
    protected $mediaDirectory;

    /**
     * @var WriteInterface
     */
    protected $varDirectory;

    /**
     * @var  CacheFactory
     */
    protected $imageCacheFactory;

    /**
     * @var Product
     */
    protected $catalogModelProduct;

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

    /**
     * @var
     */
    protected $channelPhotoConfig;

    /**
     * @var
     */
    protected $collectionOfImagesToParse;

    /**
     * @var
     */
    protected $arrayOfDataForCsv;

    /**
     * @var string
     */
    protected $filenameSeparator = '_';

    /**
     * @var string
     */
    protected $imageAltSeparator = ' ';

    /**
     * @var Manager
     */
    protected $eventManager;

    /**
     * @var int
     */
    protected $errorsProcessingImages = 0;

    /**
     * @var int
     */
    protected $imagesToProcessCount = 0;

    /**
     * @var string
     */
    protected $uniqueImageCodePattern = 'unq';

    /**
     * @var string
     */
    protected $cacheFolder = BP . '/pub/media/catalog/product/cache/';

    /**
     * Product entity link field
     *
     * @var string
     */
    protected $productEntityLinkField;

    /**
     * @var ImagesflowFactory
     */
    protected $imageFlowFactory;

    /**
     * @var IteratorFactory
     */
    protected $iteratorFactory;

    /**
     * @var ProductMetadataInterface
     */
    protected $productMetadata;

    /**
     * @var string
     */
    protected $entityIdFieldName;

    /**
     * @var array
     * ANTMRGEST-1485 feature
     * $assignTagOnlyIfMissing is an array of 'types' where this logic will happen:
     * Allow to assign image tags only if they are free (not already assigned to another image)
     * (check the image channel json map, under the 'types' node)
     * BUG: We call them 'types' but most of the time we handle them only as integer 'positions',
     * to fix this a heavy refactor is needed
     */
    protected $assignTagOnlyIfMissing = [];

    /**
     *
     * TODO: check locks on import
     * TODO: vendor/magento/module-catalog-import-export/Model/Import/Product.php see how to use this class
     */
    /**
     * Images constructor.
     * @param LoggerProvider $loggerProvider
     * @param ConfigFactory $channelConfigFactory
     * @param \FiloBlu\Flow\Helper\Images $imagesHelper
     * @param Manager $eventManager
     * @param ResourceConnection $resourceConnection
     * @param ProductRepository $productRepository
     * @param Filesystem $filesystem
     * @param CacheFactory $imageCacheFactory
     * @param Product $catalogModelProduct
     * @param ImagesflowFactory $imageFlowFactory
     * @param IteratorFactory $iteratorFactory
     * @param ProductMetadataInterface $productMetadata
     * @param ObjectManagerInterface $objectManager
     * @throws FileSystemException|Zend_Db_Statement_Exception
     */
    public function __construct(
        LoggerProvider              $loggerProvider,
        ConfigFactory               $channelConfigFactory,
        \FiloBlu\Flow\Helper\Images $imagesHelper,
        Manager                     $eventManager,
        ResourceConnection          $resourceConnection,
        ProductRepository           $productRepository,
        Filesystem                  $filesystem,
        CacheFactory                $imageCacheFactory,
        Product                     $catalogModelProduct,
        ImagesflowFactory           $imageFlowFactory,
        IteratorFactory             $iteratorFactory,
        ProductMetadataInterface    $productMetadata,
        ObjectManagerInterface      $objectManager

    )
    {
        parent::__construct($loggerProvider, $channelConfigFactory, $objectManager);
        $this->imagesHelper = $imagesHelper;
        $this->eventManager = $eventManager;
        $this->resourceConnection = $resourceConnection;
        $this->productRepository = $productRepository;
        $this->filesystem = $filesystem;
        $this->mediaDirectory = $filesystem->getDirectoryWrite(DirectoryList::MEDIA);
        $this->varDirectory = $filesystem->getDirectoryWrite(DirectoryList::VAR_DIR);
        $this->imageCacheFactory = $imageCacheFactory;
        $this->catalogModelProduct = $catalogModelProduct;
        $this->imageFlowFactory = $imageFlowFactory;
        $this->iteratorFactory = $iteratorFactory;
        $this->productMetadata = $productMetadata;
        $this->loadLookupTables();
    }

    /**
     * Load the lookups tables to speed up the whole process
     * @throws Zend_Db_Statement_Exception
     */
    protected function loadLookupTables()
    {
        $stmt = $this->getConnection()->query(
            'SELECT a.attribute_set_id, b.* FROM eav_attribute_set AS a LEFT JOIN eav_entity_type AS b ON b.entity_type_id = a.entity_type_id WHERE attribute_set_id IN ( SELECT DISTINCT attribute_set_id FROM catalog_product_entity )'
        );

        $results = $stmt->fetchAll(Zend_Db::FETCH_OBJ);

        foreach ($results as $item) {
            $item->attributesSet = $this->getAttributeSet($item->entity_type_id);
            $this->lookupAttributeSet[$item->attribute_set_id] = $item;
        }
    }

    /**
     * @return AdapterInterface
     */
    protected function getConnection(): AdapterInterface
    {
        if ($this->connection) {
            return $this->connection;
        }

        return ($this->connection = $this->resourceConnection->getConnection());
    }

    /**
     * Retrieve the attribute set available from the entity type
     *
     * @param $entityTypeId
     * @return array
     * @throws Zend_Db_Statement_Exception
     */
    protected function getAttributeSet($entityTypeId): array
    {
        $attributeSet = [];

        $stmt = $this->getConnection()
            ->query('SELECT * FROM eav_attribute WHERE entity_type_id = ?', [$entityTypeId]);

        $results = $stmt->fetchAll(Zend_Db::FETCH_OBJ);

        foreach ($results as $item) {
            $attributeSet[$item->attribute_code] = $item;
        }

        return $attributeSet;
    }

    /**
     * @return int
     */
    public function getProcessedImageErrorCount()
    {
        return $this->errorsProcessingImages;
    }

    /**
     * @return int
     */
    public function getImageToProcessCount()
    {
        return $this->imagesToProcessCount;
    }

    /**
     * @param $file
     * @return bool
     */
    public function insertData($file)
    {
        $metaFileId = $file->getId();

        // Loading channel config for 'assign_only_if_missing' feature (ANTMRGEST-1485)
        $this->loadImageAssignTagOnlyIfMissing($file->getChannel());

        /** @var Imagesflow $model */
        $model = $this->imageFlowFactory->create();

        $catalogProductEntity = $this->getConnection()->getTableName('catalog_product_entity');

        // Loading images SKUs to process
        $collection = $model->getCollection();
        $collection->addFieldToSelect(['id', 'image_sku']);
        //$collection->addFieldToFilter('meta_processed', 0);
        $collection->addFieldToFilter('meta_file', $metaFileId);
        $collection->addFieldToFilter('image_status', ['in' => [Imagesflow::STATUS_PARSED, Imagesflow::STATUS_ERROR]]);
        $collection->addFieldToFilter('image_sku', ['in' => new Zend_Db_Expr("SELECT sku FROM `{$catalogProductEntity}`")]);
        //$collection->getSelect()->group('image_sku');
        $collection->load();

        $this->imagesToProcessCount = $collection->count();

        if ($this->imagesToProcessCount) {
            /** @var Iterator $iterator */
            $iterator = $this->iteratorFactory->create();
            $iterator->walk($collection->getSelect(), [[$this, 'fastImageProcess']], ['meta_file_id' => $metaFileId]);
        } else {
            $this->errorsProcessingImages = 1;
        }

        return !($this->errorsProcessingImages > 0);
    }

    /**
     * Process image import directly query the db layer
     * @param $args
     * @return bool
     * @throws Zend_Db_Statement_Exception
     */
    public function fastImageProcess($args)
    {
        $this->getConnection()
            ->update(
                $this->resourceConnection->getTableName('flow_inboundflow'),
                ['last_activity' => date('Y-m-d H:i:s')],
                ['id = ?' => $args['meta_file_id']]
            );
        // TODO : find best place
        /** @var ProductImageManager $imageManager */
        $imageManager = $this->objectManager->get(ProductImageManager::class);

        $entityIdFieldName = $this->getEntityField();

        // Where Magento 2 looks for images
        $catalogRootDirectory = $this->mediaDirectory->getAbsolutePath();

        // Retieve sku
        $sku = $args['row']['image_sku'];
        $metaFileId = $args['meta_file_id'];

        // Find product by sku
        $results = $this->getConnection()->query('SELECT * FROM catalog_product_entity WHERE sku = ?', [$sku]);

        $product = $results->fetch(Zend_Db::FETCH_OBJ);

        if ($product === null || $product === false) {
            $this->getLogger()->error("Unable to find product with SKU {$sku}");
            $this->errorsProcessingImages++;
            return true;
        }

        // TODO : use direct query instead. See https://blackfire.io/profiles/0b7ca6a2-ac40-41c1-b41d-1bfee4e7a8ac/graph
        // Find incoming product images

        $model = $this->imageFlowFactory->create();

        $collection = $model->getCollection();
        // $collection->addFieldToFilter('meta_processed', 0);
        $collection->addFieldToFilter('meta_file', $metaFileId);
        $collection->addFieldToFilter('image_status', ['in' => [Imagesflow::STATUS_PARSED, Imagesflow::STATUS_ERROR]]);
        $collection->addFieldToFilter('image_sku', $sku);
        //$collection->setOrder('image_position', 'ASC');
        $collection->load();

        $incomingImages = [];
        foreach ($collection as $imageFlow) {
            $incomingImages[$this->imagesHelper->getCorrectFileName($imageFlow->getImageName())] = $imageFlow;
        }

        if (empty($this->lookupAttributeSet)) {
            $this->loadLookupTables();
        }

        $id = (int)$product->attribute_set_id;
        $attributeSet = $this->lookupAttributeSet[$id];

        $imageMediaGalleryAttributeCode = $this->getChannelConfig()->getImageMediaGallery() ?? 'media_gallery';
        $mediaGalleryAttribute = $attributeSet->attributesSet[$imageMediaGalleryAttributeCode];
        $mediaGalleryAttributeId = $mediaGalleryAttribute->attribute_id;

        // Fix $catalogRootDirectory

        $catalogRootDirectory .= str_replace('_', DIRECTORY_SEPARATOR, $attributeSet->entity_type_code);

        // Find existing images in gallery
        $existingImages = $this->getProductGalleryBySku($sku, $mediaGalleryAttributeId);

        foreach ($incomingImages as $name => $imageFlow) {
            try {

                if (!empty($this->assignTagOnlyIfMissing)){
                    // Reaload existing images, I need most up-to-date data here
                    $existingImages = $this->getProductGalleryBySku($sku, $mediaGalleryAttributeId, $product->{$entityIdFieldName});
                    $this->excludeImageTypes($imageFlow,$existingImages);
                }

                $relativePath = Uploader::getDispretionPath($name) . DIRECTORY_SEPARATOR . $name;
                $to = $catalogRootDirectory . $relativePath;
                $from = $this->getImportImagePath($imageFlow);

                $folder = dirname($to) . DIRECTORY_SEPARATOR;

                if (is_dir($folder) === false) {
                    if (mkdir($folder, 0777, true) || is_dir($folder)) {
                        $this->getLogger()->error("Unable to create directory {$folder}");
                        $this->setProcecessedStatus($imageFlow, Imagesflow::STATUS_ERROR, "Unable to create directory {$folder}");
                        $this->errorsProcessingImages++;
                        return true;
                    }
                }

                if (copy($from, $to) === false) {
                    $this->setProcecessedStatus($imageFlow, Imagesflow::STATUS_ERROR, "Unable to move {$from} -> {$to}");
                    $this->errorsProcessingImages++;
                    continue;
                }

                /* If already exists image in gallery just copy/move the image file and update metas */
                if (array_key_exists($name, $existingImages)) {
                    $row = $existingImages[$name];
                    $this->updateGalleryValues($product->{$entityIdFieldName}, $row->value_id, $imageFlow);
                    $this->updateAttributes($product->{$entityIdFieldName}, $relativePath, $imageFlow, $attributeSet->attributesSet);
                } else {
                    $this->addImageToGallery($relativePath, $product->{$entityIdFieldName}, $mediaGalleryAttributeId, $imageFlow);
                    $this->addAttributes($product->{$entityIdFieldName}, $relativePath, $imageFlow, $attributeSet->attributesSet);
                }

                try {
                    $imageManager->cleanCache($name);
                } catch (Throwable $throwable) {
                    $this->getLogger()->error($throwable->getMessage());
                }

                $this->setProcecessedStatus($imageFlow);
            } catch (Exception $e) {
                $this->getLogger()->info("Importing image {$name} for product with SKU {$sku} failed. : " . $e->getMessage() . PHP_EOL . $e->getTraceAsString());
                $this->setProcecessedStatus($imageFlow, Imagesflow::STATUS_ERROR, $e->getMessage());
                $this->errorsProcessingImages++;
            }
        }

        return true;
    }

    protected function excludeImageTypes($imageFlow,$existingImages){
        // Do I have a ImageType?
        if (!$imageFlow->getImageType()){
            return;
        }
        // I'm inside a position I need to validate?
        if (!in_array($imageFlow->getData('image_position'),$this->assignTagOnlyIfMissing)){
            return;
        }
        $tags_already_present = [];
        foreach ($existingImages as $existingImage) {
            $image_tags_attribute_codes = explode(',', $existingImage->tags_attribute_codes);
            $tags_already_present = array_merge($tags_already_present, $image_tags_attribute_codes);
        }
        $tags_already_present = array_unique(array_filter($tags_already_present));
        sort($tags_already_present);
        $tags_of_current_image = array_unique(explode('|', $imageFlow->getImageType()));
        sort($tags_of_current_image);
        $allowed_tags = array_diff($tags_of_current_image,$tags_already_present);
        if ($tags_of_current_image !== $allowed_tags) {
            $allowed_tags_string = implode('|',$allowed_tags);
            $imageFlow->setImageType($allowed_tags_string);
            $msg = "{$imageFlow->getImageName()} flow image import, changed tags to: '{$allowed_tags_string}' because of 'assignTagOnlyIfMissing' json map parameter";
            $this->getLogger()->info($msg);
        }

    }

    /**
     * @return string
     */
    protected function getEntityField()
    {
        if ($this->entityIdFieldName) {
            return $this->entityIdFieldName;
        }

        $edition = $this->productMetadata->getEdition();
        $this->entityIdFieldName = ($edition === 'Enterprise' || $edition === 'B2B') ? 'row_id' : 'entity_id';

        return $this->entityIdFieldName;
    }

    /**
     * Retrieve the whole gallery for the given product sku
     * @param $sku
     * @param $mediaGalleryAttributeId
     *
     * @return array
     * @throws Zend_Db_Statement_Exception
     */
    protected function getProductGalleryBySku($sku, $mediaGalleryAttributeId)
    {
        $gallery = [];
        $entityIdFieldName = $this->getEntityField();

        $results = $this->getConnection()->query(
            "SELECT CPEMGON.*, CPEMGVTE.{$entityIdFieldName} FROM catalog_product_entity AS CPE
             LEFT JOIN catalog_product_entity_media_gallery_value_to_entity AS CPEMGVTE ON CPE.{$entityIdFieldName} = CPEMGVTE.{$entityIdFieldName}
             LEFT JOIN catalog_product_entity_media_gallery AS CPEMGON ON CPEMGVTE.value_id = CPEMGON.value_id
             WHERE CPE.sku = ? AND CPEMGON.attribute_id = ?",
            [$sku, $mediaGalleryAttributeId]
        );

        $images = $results->fetchAll(Zend_Db::FETCH_OBJ);

        foreach ($images as $image) {
            // Integrate attribute codes list of image tags
            // NOTE: Only default admin store is supported for now
            if ($image->value){
                $tags_sql = "SELECT GROUP_CONCAT(DISTINCT EA.attribute_code) AS 'tags_attribute_codes'
                         FROM `catalog_product_entity_varchar` CPEV
                         LEFT JOIN eav_attribute EA ON EA.attribute_id = CPEV.attribute_id
                         LEFT JOIN catalog_product_entity CPE ON CPE.{$entityIdFieldName} = CPEV.{$entityIdFieldName}
                         WHERE CPE.sku = '{$sku}'
                         AND CPEV.`store_id` = 0
                         AND CPEV.`value` IS NOT NULL
                         AND CPEV.`value` LIKE '{$image->value}'";
                $image->tags_attribute_codes = (string)$this->getConnection()->query($tags_sql)->fetchColumn(0);
             }

            /* Remove the dispersion prefix */
            $gallery[basename($image->value)] = $image;
        }

        return $gallery;
    }

    /**
     * @param Imagesflow $imagesflow
     * @return string
     */
    protected function getImportImagePath(Imagesflow $imagesflow)
    {
        return $this->varDirectory->getAbsolutePath() . DIRECTORY_SEPARATOR . $imagesflow->getImagePath() . DIRECTORY_SEPARATOR . $imagesflow->getImageName();
    }

    /**
     * @param Imagesflow $image
     * @param string $status
     * @param string $reason
     */
    protected function setProcecessedStatus(Imagesflow $image, string $status = Imagesflow::STATUS_PROCESSED, string $reason = null)
    {
        $connection = $this->getConnection();
        $connection->update(
            $connection->getTableName('flow_imagesflow'),
            [
                'image_process_time' => date('Y-m-d H:i:s'),
                'meta_processed' => 1,
                'image_status' => $status,
                'log' => $reason
            ],
            $connection->quoteInto('id = ?', $image->getId())
        );
    }

    /**
     * @param $rowId
     * @param $valueId
     * @param Imagesflow $imageFlow
     */
    protected function updateGalleryValues($rowId, $valueId, Imagesflow $imageFlow)
    {
        $entityIdFieldName = $this->getEntityField();
        $connection = $this->getConnection();

        $valueTable = $connection->getTableName('catalog_product_entity_media_gallery_value');

        $where = $connection->quoteInto("value_id = ? AND {$entityIdFieldName} = ?", $valueId, $rowId);

        $connection->update(
            $valueTable,
            [
                'store_id' => 0,
                'label' => $imageFlow->getImageAlt(),
                'position' => $imageFlow->getImagePosition(),
                'disabled' => $imageFlow->getImageHide()
            ],
            $where
        );
    }

    /**
     * @param string | int $position
     * @return int
     */
    protected function getImageHide($position)
    {
        $config = $this->channelPhotoConfig;

        if (isset($config['types'][$position]['hide']) && $config['types'][$position]['hide'] == 'true') {
            return 1;
        }

        return 0;
    }

    /**
     * Update attributes. Allowed attributes to be set are defined in @param $rowId
     *
     * @param $value
     * @param Imagesflow $imageFlow
     * @param array $attributesSet
     * @throws Zend_Db_Statement_Exception
     * @see \FiloBlu\Flow\Model\Channel\In\Images::$_imageAttributeCodes
     *
     */
    protected function updateAttributes($rowId, $value, Imagesflow $imageFlow, array $attributesSet)
    {
        $entityIdFieldName = $this->getEntityField();
        $connection = $this->getConnection();

        $assignableAttributes = explode('|', $imageFlow->getImageType());

        if (empty($assignableAttributes)) {
            return;
        }

        $attributesTable = $connection->getTableName('catalog_product_entity_varchar');

        $attributeIds = [];
        $toUpdate = [];
        $lookup = [];

        foreach ($assignableAttributes as $attributeCode) {
            if (!isset($attributesSet[$attributeCode])) {
                continue;
            }

            $id = $attributesSet[$attributeCode]->attribute_id;
            $attributeIds[] = (int)$id;
            $lookup[$id] = $attributeCode;
        }

        $stmt = $connection->query(
            $this->connection
                ->select()
                ->from($attributesTable)
                ->where("{$entityIdFieldName} = ?", $rowId)
                ->where('store_id = ?', 0)
                ->where('attribute_id IN (?)', $attributeIds)
        );

        $existingAttributes = $stmt->fetchAll(Zend_Db::FETCH_OBJ);

        /* Find attributes to update and attributes to insert */

        foreach ($existingAttributes as $attribute) {
            $id = $attribute->attribute_id;
            $toUpdate[] = $lookup[$id];

            $where = $connection->quoteInto('value_id = ?', $attribute->value_id);

            $connection->update(
                $attributesTable,
                [
                    'store_id' => 0,
                    'attribute_id' => $id,
                    $entityIdFieldName => $rowId,
                    'value' => $value
                ],
                $where
            );
        }

        // Add missing attributes

        foreach (array_diff($assignableAttributes, $toUpdate) as $attributeCode) {
            if (!isset($attributesSet[$attributeCode])) {
                continue;
            }

            $attributeId = $attributesSet[$attributeCode]->attribute_id;

            $connection->insert($attributesTable, [
                'store_id' => 0,
                'attribute_id' => $attributeId,
                $entityIdFieldName => $rowId,
                'value' => $value
            ]);
        }
    }

    /**
     * This will look into the json config of the image channels
     * searching which type of image is, based on the $position key inside the "types" node of the json
     *
     * @param $position
     *
     * @return array
     */
    protected function getImageType($position)
    {
        $position = trim($position ?? '');

        $config = $this->channelPhotoConfig;

        if (isset($config['types'][$position]['type'])) {
            return $config['types'][$position]['type'];
        }

        // Check if position is a number (can also be a string)
        // If is a number handle it as a number
        // This will handle positions as "001" "01" "1" (usually human errors)
        if ((int)$position > 0 && isset($config['types'][(int)$position]['type'])) {
            return $config['types'][(int)$position]['type'];
        }

        // Check again if maybe we have uppercases (usually human errors)
        $position = strtolower($position);
        return $config['types'][$position]['type'] ?? [];
    }

    /**
     * @param $value
     * @param $rowId
     * @param $attributeId
     * @param Imagesflow $imageFlow
     */
    protected function addImageToGallery($value, $rowId, $attributeId, Imagesflow $imageFlow)
    {
        $entityIdFieldName = $this->getEntityField();
        $connection = $this->getConnection();

        $galleryTable = $connection->getTableName('catalog_product_entity_media_gallery');
        $relationTable = $connection->getTableName('catalog_product_entity_media_gallery_value_to_entity');
        $valueTable = $connection->getTableName('catalog_product_entity_media_gallery_value');

        // Add new entry in the gallery

        $connection->insert($galleryTable, [
            'value' => $value,
            'attribute_id' => $attributeId,
            'media_type' => 'image',
            'disabled' => 0
        ]);

        // Create relationship
        $valueId = $connection->lastInsertId($galleryTable);

        $connection->insert($relationTable, [
            'value_id' => $valueId,
            $entityIdFieldName => $rowId
        ]);

        // Add new value entry

        $connection->insert($valueTable, [
            'value_id' => $valueId,
            $entityIdFieldName => $rowId,
            'store_id' => 0,
            'label' => $imageFlow->getImageAlt(),
            'position' => $imageFlow->getImagePosition(),
            'disabled' => $imageFlow->getImageHide()
        ]);
    }

    /**
     * Update attributes.
     *
     * @param $rowId
     * @param $value
     * @param Imagesflow $imageFlow
     * @param array $attributesSet
     */
    protected function addAttributes($rowId, $value, Imagesflow $imageFlow, array $attributesSet)
    {
        $connection = $this->getConnection();

        $entityIdFieldName = $this->getEntityField();

        $assignableAttributes = explode('|', $imageFlow->getImageType());

        if (empty($assignableAttributes)) {
            return;
        }

        $attributesTable = $connection->getTableName('catalog_product_entity_varchar');

        // Lookup for attribute_code => attribute_id

        foreach ($assignableAttributes as $attribute) {
            if (!isset($attributesSet[$attribute])) {
                continue;
            }

            $attributeId = $attributesSet[$attribute]->attribute_id;

            $connection->insertOnDuplicate($attributesTable, [
                'store_id' => 0,
                'attribute_id' => $attributeId,
                $entityIdFieldName => $rowId,
                'value' => $value
            ], ['value']);
        }
    }

    /**
     * @param $file
     * @return bool
     * @throws Exception
     */
    public function receiveFile($file)
    {
        if (!$this->getConnector()) {
            throw new Exception('no connector joined to channel');
        }

        $metaFileId = $file->getId();

        $local_dir = $this->getConnector()->getLocalDir() . '/' . $metaFileId;

        $this->getConnector()->setLocalDir($local_dir);

        $model = $this->imageFlowFactory->create();

        $collection = $model->getCollection();
        $collection->addFieldToFilter('meta_processed', 0);
        $collection->addFieldToFilter('meta_file', $metaFileId);
        $collection->addFieldToFilter('image_status', 'found');
        $collection->addFieldToFilter('image_queue_time', ['notnull' => true]);
        $collection->load();

        $result = true;

        foreach ($collection as $image) {
            try {

                // Is unique filename enabled?
                $uniqueFilenamePattern = null;
                if ($this->imagesHelper->isUniqueUploadImagesActive()) {
                    $uniqueFilenamePattern = "_{$this->uniqueImageCodePattern}" . $image->getId();
                }

                $received = $this->getConnector()->receiveResource($image->getData('image_name'),
                    null,
                    FTP_BINARY,
                    true,
                    $uniqueFilenamePattern);
                if ($received) {
                    $image->setData('image_receive_time', date('Y-m-d H:i:s'));
                    $image->setData('image_status', 'received');
                    if ($image->getData('image_name') !== $received) {
                        $log = $image->getData('log');
                        $log = trim("Image name fixed from '" . $image->getData('image_name') . "' to '{$received}'" . $log . "\n");
                        $image->setData('log', $log);
                        $image->setData('image_name', $received);
                    }
                    $image->save();
                } else {
                    $image->setData('image_status', 'found');
                    $log = $image->getData('log');
                    $log .= "Image not received\n";
                    $image->setData('log', $log);
                    $image->save();
                    $result = false;
                }
            } catch (Exception $e) {
                $log = $image->getData('log');
                $log .= $e->getMessage() . "\n";
                $image->setData('log', $log);
                $image->save();
                $result = false;
            }
        }

        return $result;
    }

    /**
     * @param $file
     * @return bool
     * @throws Exception
     */
    public function parse($file)
    {
        if (!$this->getConnector()) {
            throw new Exception('No connector assigned to channel');
        }

        $metaFileId = $file->getId();
        $channelConfig = $this->getConnector()->getConfig();
        $localDir = $channelConfig['local_dir'] . DIRECTORY_SEPARATOR . $metaFileId;
        $csvFilename = $file->getName();
        $channelName = $file->getChannel();
        $this->loadImageChannelConfig($channelName);
        $this->filenameSeparator = $this->getImageSeparator();

        if (!$this->channelPhotoConfig) {
            throw new Exception('Wrong images configuration');
        }

        $this->loadImagesListToParse($metaFileId);

        foreach ($this->collectionOfImagesToParse->getItems() as $data) {

            $imageFilename = $data->getImageName();
            // $image_filename_no_ext = $this->removeExtensionFromFilename($image_filename);
            $loadedData = $this->loadDataBasedOnFilename($imageFilename, $channelName);

            $productSku = $loadedData['sku'];
            $data->setData('image_sku', $productSku);
            $data->setData('image_path', $localDir);

            if ($loadedData['position'] && count($loadedData) > 0) {

                // Handling not numeric "L1", "L2", "L3", "L4" special positions (pattern inside filename)
                // Giving custom high order (order is mandatory, numeric and unique)

                switch ($loadedData['position']) {
                    case 'l1':
                    case 'L1':
                        $loadedData['position'] = 999;
                        break;
                    case 'l2':
                    case 'L2':
                        $loadedData['position'] = 998;
                        break;
                    case 'l3':
                    case 'L3':
                        $loadedData['position'] = 997;
                        break;
                    case 'l4':
                    case 'L4':
                        $loadedData['position'] = 996;
                        break;
                    default:
                        #nothing to do
                }

                $data->setData('image_position', (int)$loadedData['position']);
            }

            if (isset($loadedData['alt']) && $loadedData['alt']) {
                $data->setData('image_alt', $loadedData['alt']);
            }

            $data->setData('image_type', implode('|', array_unique($loadedData['image_type'])));
            $data->setData('image_hide', $loadedData['image_hide']);
            $data->setData('image_status', 'parsed');
            //$data->setData('image_path', $localDir);

            if (!$this->productExists($productSku)) {
                $data->setData('image_status', 'error');
                $log = $file->getData('log');
                $log .= "Can't parse image filename {$imageFilename} or SKU does not exists, wrong format! {$csvFilename}\n";
                $file->setData('log', $log);
                $file->save();
            }

            $data->save();
        }

        // Handling replicate images for same simple products
        $duplicateImageSuperAttribute = trim($this->imagesHelper->isDuplicateImagesActive() ?? '');

        if (!empty($duplicateImageSuperAttribute)) {

            // Reload Images
            $model = $this->imageFlowFactory->create();

            $collection = $model->getCollection();
            $collection->addFieldToFilter('meta_processed', 0);
            $collection->addFieldToFilter('meta_file', $metaFileId);
            $collection->addFieldToFilter('image_status', 'parsed');
            $collection->load();

            // Rerunning the same collection
            foreach ($collection as $data) {
                if ($data->getData('image_status') === self::STATUS_PARSED) {
                    try {

                        // TODO :  just a workaround

                        $position = (int)$this->getFieldPosition($duplicateImageSuperAttribute);
                        $image_filename_no_ext = $this->removeExtensionFromFilename($data->getImageName());
                        $filename_exploded = explode($this->filenameSeparator, $image_filename_no_ext);
                        $sku = $filename_exploded[$this->getFieldPosition('sku')];

                        /* END */

                        $childrenSkuArray = $this->getSimpleFromConfigurableHavingAttribute($sku,
                            $duplicateImageSuperAttribute,
                            $filename_exploded[$position]);

                        if ($childrenSkuArray === false) {
                            continue;
                        }

                        if (isset($filename_exploded[$this->getFieldPosition('default')])) {
                            $data->setData('image_sku', $sku);
                            $data->setData('log', 'Image set as default');
                            $data->save();
                        }

                        // For each child replicating the same image to be processed but on a different SKU

                        foreach ($childrenSkuArray as $childSku) {
                            $data->unsetData('id');
                            $data->setData('image_sku', $childSku);
                            $data->setData('log', 'Image replicated');
                            $data->save();
                        }


                    } catch (Exception $exception) {
                        $this->getLogger()->error('An error occurred while expanding: ' . $exception->getMessage());
                    }
                }
            }
        }

        return true;
    }

    /**
     * @param string $channelName
     */
    protected function loadImageChannelConfig($channelName)
    {
        $this->channelPhotoConfig = null;
        if ($this->getParser()->loadMap($channelName)) {
            $configMapping = $this->getParser()->map->getFullMappedData();
            $this->channelPhotoConfig = $configMapping;
        }
    }

    /**
     * @param $channelName
     * @return void
     */
    protected function loadImageAssignTagOnlyIfMissing($channelName){
        if (!$this->channelPhotoConfig){
            $this->loadImageChannelConfig($channelName);
        }
        if (!isset($this->channelPhotoConfig['types'])){
            // Types must exist for this to work
            return;
        }
        foreach ($this->channelPhotoConfig['types'] as $type_name => $type_data) {
            if (isset($type_data['assign_only_if_missing']) && in_array($type_data['assign_only_if_missing'],array("true","1",1,true))){
                $this->assignTagOnlyIfMissing[] = $type_name;
            }
        }
    }

    /**
     * @return string
     */
    protected function getImageSeparator()
    {
        $config = $this->channelPhotoConfig;

        $hasSeparator = (isset($config['options']['separator']) && trim($config['options']['separator'] ?? '') !== '');
        return $hasSeparator ? $config['options']['separator'] : '_';
    }

    /**
     * @param $file_id
     */
    protected function loadImagesListToParse($file_id)
    {
        $model = $this->imageFlowFactory->create();

        $collection = $model->getCollection();
        $collection->addFieldToFilter('meta_processed', 0);
        $collection->addFieldToFilter('meta_file', $file_id);
        $collection->addFieldToFilter('image_status', 'received');
        $collection->load();

        $this->collectionOfImagesToParse = $collection;
    }

    /**
     * @param $imageFilename
     * @param null $channelName
     * @return mixed
     * @throws Exception
     * TODO: Refactor everything, this is a mess!
     */
    protected function loadDataBasedOnFilename($imageFilename, $channelName = null)
    {
        $alt = [];
        $originalFilename = $imageFilename;

        // Remove extension
        $imageFilename = $this->removeExtensionFromFilename($imageFilename);

        $filenameExploded = explode($this->filenameSeparator, $imageFilename);

        $sku_position = $this->getSkuFilenamePosition();

        $sku = $filenameExploded[$sku_position] ?? '_unknown_';

        $image_position_position = $this->getImagePositionPosition();

        $position = $filenameExploded[$image_position_position] ?? 1;

        $imageAltPositions = $this->getImageAltPositions();

        if (count($imageAltPositions) > 0) {
            foreach ($imageAltPositions as $pos) {
                // If ALT of image is equal to $this->uniqueImageCodePattern then we have an error in the naming and
                // we need to skip that ALT value

                if (isset($filenameExploded[$pos]) && strpos($filenameExploded[$pos], $this->uniqueImageCodePattern) !== 0) {
                    $alt[] = $filenameExploded[$pos];
                }
            }
            if (count($alt) > 0) {
                $alt = implode($this->imageAltSeparator, $alt);
            }
        }

        if (empty($alt)) {
            $alt = '';
        }

        if (false !== ($defaultListing = $this->getFieldPosition('default', false)) && isset($filenameExploded[$defaultListing])) {
            $image_type = $this->getImageType($filenameExploded[$defaultListing]);
        } else {
            $image_type = $this->getImageType($position);
        }

        $image_type = array_merge($image_type, $this->getImageType($position));

        $image_hide = $this->getImageHide($position);

        $this->arrayOfDataForCsv[$sku][$position]['filename'] = $originalFilename;
        $this->arrayOfDataForCsv[$sku][$position]['path'] = $this->getConnector()->getLocalDir();
        $this->arrayOfDataForCsv[$sku][$position]['position'] = $position;
        if ($alt) {
            $this->arrayOfDataForCsv[$sku][$position]['alt'] = $alt;
        }
        $this->arrayOfDataForCsv[$sku][$position]['image_type'] = $image_type;
        $this->arrayOfDataForCsv[$sku][$position]['image_hide'] = $image_hide;
        $this->arrayOfDataForCsv[$sku][$position]['sku'] = $sku;

        // Start dispatch event to handle change of this data if needed
        $event_dispatch_data = new DataObject();
        $event_dispatch_data->setData('channel_name', $channelName);
        $event_dispatch_data->setData('image_filename', $originalFilename);
        $event_dispatch_data->setData('image_filename_noext', $imageFilename);
        $event_dispatch_data->setData('filename_separator', $this->filenameSeparator);
        $event_dispatch_data->setData('channel_mapping', $this->channelPhotoConfig);
        $event_dispatch_data->setData('loaded_data', $this->arrayOfDataForCsv[$sku][$position]);
        $this->eventManager->dispatch('flow_images_parse_load_data_based_on_filename_after', ['event_dispatch_data' => $event_dispatch_data]);
        // If the value is changed then it will be updated here
        $this->arrayOfDataForCsv[$sku][$position] = $event_dispatch_data->getData('loaded_data');
        // End dispatch event, the data may have been changed here

        return $this->arrayOfDataForCsv[$sku][$position];
    }

    /**
     * @param $string
     * @return bool|string
     */
    public function removeExtensionFromFilename($string)
    {
        $p = strrpos($string, '.');
        if ($p !== false) {
            $string = substr($string, 0, $p);
        }

        return $string;
    }

    /**
     * @return int|string
     */
    protected function getSkuFilenamePosition()
    {
        $config = $this->channelPhotoConfig;
        foreach ($config['fields'] as $key => $value) {
            if ($value['type'] === 'sku') {
                return $key;
            }
        }

        //Handle error missing SKU on config here, block all flow
    }

    /**
     * @return int|string
     */
    protected function getImagePositionPosition()
    {
        $config = $this->channelPhotoConfig;
        foreach ($config['fields'] as $key => $value) {
            if ($value['type'] === 'position') {
                return $key;
            }
        }
    }

    /**
     * @return array
     */
    protected function getImageAltPositions()
    {
        $config = $this->channelPhotoConfig;
        $positions = [];
        foreach ($config['fields'] as $key => $value) {
            if ($value['type'] === 'alt') {
                $positions[] = $key;
            }
        }

        return $positions;
    }

    /**
     * @param $fieldName
     * @param bool $throw | integer
     * @return bool|int|string
     * @throws Exception
     */
    protected function getFieldPosition($fieldName, $throw = true)
    {
        $config = $this->channelPhotoConfig;
        foreach ($config['fields'] as $key => $value) {
            if ($value['type'] === $fieldName) {
                return $key;
            }
        }

        if ($throw) {
            throw new Exception("Mapped field '{$fieldName}' does not exist in channel map");
        }

        return false;
    }

    /**
     * @param string $sku
     * @return bool
     */
    public function productExists($sku)
    {
        if ($this->catalogModelProduct->getIdBySku($sku)) {
            return true;
        }
        return false;
    }

    /**
     * @param $sku
     * @param $attributeId
     * @param $attributeValue
     * @return array|bool
     * @throws Exception
     */
    protected function getSimpleFromConfigurableHavingAttribute($sku, $attributeId, $attributeValue)
    {
        $connection = $this->getConnection();
        $entityIdFieldName = $this->getEntityField();

        $catalog = $connection->getTableName('catalog_product_entity');
        $superAttributesTable = $connection->getTableName('catalog_product_super_attribute');
        $eavAttributeTable = $connection->getTableName('eav_attribute');
        $eavAttributeOptionTable = $connection->getTableName('eav_attribute_option');
        $eavAttributeOptionValueTable = $connection->getTableName('eav_attribute_option_value');

        $superLinkTable = $connection->getTableName('catalog_product_super_link');

        // Step 1 - Find the configurable row_id

        $configurables = $connection->fetchAll(
            "SELECT {$entityIdFieldName} FROM {$catalog} WHERE sku = ? AND type_id = ?", [$sku, 'configurable'],
            Zend_Db::FETCH_OBJ
        );

        if (empty($configurables)) {
            return false;
        }

        $output = [];

        foreach ($configurables as $configurable) {
            $configurableId = $configurable->{$entityIdFieldName};

            // Step 2 - List super attributes

            $superAttributes = [];
            $simpleIds = [];

            $attributes = $connection->fetchAll(
                "SELECT EA.attribute_id,EA.backend_type,EA.attribute_code
FROM {$superAttributesTable} AS CPSA
LEFT JOIN {$eavAttributeTable} AS EA ON EA.attribute_id = CPSA.attribute_id
WHERE CPSA.product_id = ?",
                [$configurableId],
                Zend_Db::FETCH_OBJ
            );

            foreach ($attributes as $attribute) {
                $superAttributes[] = $attribute->attribute_id;
            }

            // Step 3 - Find children

            $simples = $connection->fetchAll("SELECT {$entityIdFieldName}  FROM {$superLinkTable} AS CPSL LEFT JOIN {$catalog} AS CPE ON CPSL.product_id = CPE.entity_id WHERE CPSL.parent_id = ?", [$configurableId], Zend_Db::FETCH_OBJ);

            foreach ($simples as $simple) {
                $simpleIds[] = $simple->{$entityIdFieldName};
            }

            // step 4 - Find children matching attribute value

            $sqlAttributesIds = implode(',', $superAttributes);
            $sqlSimpleIds = implode(',', $simpleIds);

            //
            //
            // value_id |attribute_id |store_id |row_id |value |sku            |
            // ---------|-------------|---------|-------|------|---------------|
            // 6516450  |93           |0        |41888  |233   |BSC6A85ECG80TU |
            //
            //

            $sql = "SELECT CPEAV.{$entityIdFieldName}, CPEAV.attribute_id, CPE.sku
FROM catalog_product_entity_int AS CPEAV
LEFT JOIN {$catalog} AS CPE ON CPEAV.{$entityIdFieldName} = CPE.{$entityIdFieldName}
WHERE CPEAV.attribute_id IN ({$sqlAttributesIds}) AND CPEAV.`value` IN
(
    SELECT EAO.option_id FROM {$eavAttributeOptionTable} AS EAO
    LEFT JOIN {$eavAttributeOptionValueTable} AS EAOV ON EAOV.option_id = EAO.option_id
    WHERE EAO.attribute_id IN ({$sqlAttributesIds}) AND (EAOV.`value` = ?)
)
AND CPEAV.{$entityIdFieldName} IN ({$sqlSimpleIds})";

            $matchedSimpleProducts = $connection->fetchAll($sql, [$attributeValue], Zend_Db::FETCH_OBJ);

            // Step 5 - check that matched simple products are all with same attribute
            $currentAttributeId = null;

            foreach ($matchedSimpleProducts as $simpleProduct) {
                $output[] = $simpleProduct->sku;
                if ($currentAttributeId === null) {
                    $currentAttributeId = $simpleProduct->attribute_id;
                    continue;
                }

                if ($currentAttributeId !== $simpleProduct->attribute_id) {
                    throw new Exception("The configurable product with sku '{$sku}' has simple products ({$sqlSimpleIds}) with the same attribute value '{$attributeValue}' for attributes with id ({$sqlAttributesIds}). Unable to resolve the ambiguity.");
                }
            }
        }

        return $output;
    }

    /**
     * @return Filesystem
     */
    public function getFilesystem(): Filesystem
    {
        return $this->filesystem;
    }

    /**
     * @param string $path
     * @return bool
     */
    protected function removeCatalogProductImageByPath($path)
    {
        $file = BP . "/pub/media/catalog/product{$path}";

        $connection = $this->getConnection();

        $query = "DELETE FROM catalog_product_entity_media_gallery WHERE value = '{$path}'";
        $result = $connection->query($query);
        $addWhere = '';

        if (!$result) {
            $this->getLogger()->info("INSERT IMAGES: NOT Removed from catalog_product_entity_media_gallery {$path}");
            return false;
        }

        $this->getLogger()->info("INSERT IMAGES: Removed from catalog_product_entity_media_gallery {$path}");

        $ProductMediaImageAttributeIds = $this->getProductMediaImageAttributeIds();
        if ($ProductMediaImageAttributeIds) {
            $addWhere = " AND attribute_id IN ({$ProductMediaImageAttributeIds}) ";
        }

        // Removing also the "photo tags" (base, small, thumbnail) or they will be left there
        $query = "DELETE FROM catalog_product_entity_varchar WHERE value = '{$path}' {$addWhere}";
        $result = $connection->query($query);

        if (!$result) {
            $this->getLogger()->info("INSERT IMAGES: NOT Removed from catalog_product_entity_varchar {$path}");
            return false;
        }

        $this->getLogger()->info("INSERT IMAGES: Removed TAG from catalog_product_entity_varchar {$path}");

        if (is_file($file)) {
            unlink($file);
            $this->getLogger()->info("INSERT IMAGES: Removed file from disk {$file}");
        } else {
            $this->getLogger()->info("INSERT IMAGES: Removing file from disk {$file} not found, skipping");
        }

        $this->removeImageCacheFromImageFileName($path);

        return true;
    }

    /**
     * @return string of ids comma separated
     */
    protected function getProductMediaImageAttributeIds()
    {
        /** @var $coll Collection */
        $coll = ObjectManager::getInstance()->create(Collection::class);
        $coll->addFieldToFilter('frontend_input', 'media_image');
        $attrAll = $coll->load()->getItems();
        $result = [];
        foreach ($attrAll as $attr) {
            $result[] = $attr->getAttributeId();
        }

        $return = false;
        if (count($result)) {
            $return = implode(',', $result);
        }

        return $return;
    }

    /**
     * @param string|null $filename
     * @return bool
     */
    protected function removeImageCacheFromImageFileName($filename = null)
    {
        if (!$filename || !$this->imagesHelper->isClearImageCacheActive()) {
            return true;
        }

        // Array of all cache main folders
        try {
            $paths = $this->getAllCacheImageMainPaths();
        } catch (Exception $e) {
            $this->getLogger()->info('ERROR IMAGES: removeImageCacheFromImageFileName ' . $e->getMessage());
            return false;
        }

        if (!isset($paths) || !$paths || count($paths) === 0) {
            return false;
        }

        foreach ($paths as $path) {

            // Safe check
            $path = rtrim($path, DIRECTORY_SEPARATOR);

            $fullPath = $this->cacheFolder . $path . $filename;

            if (file_exists($fullPath)) {
                // Try to delete the cache
                unlink($fullPath);
                $this->getLogger()->info("INSERT IMAGES: Removed cache image {$fullPath}");
            }
        }
    }

    /**
     * @return array
     */
    public function getAllCacheImageMainPaths()
    {
        // Give me an array of all the folders inside the cache folder
        // Going deep 3
        $foldersMap = $this->imagesHelper->foldersMap($this->cacheFolder, 3);

        // Transform the array paths into string path checking also the md5 of the last folder
        $result = [];
        $this->imagesHelper->collapse('', $foldersMap, $result);

        return $result;
    }
}
