<?php

declare(strict_types=1);

namespace External\HannahBundle\Import;

use Doctrine\DBAL\Exception\UniqueConstraintViolationException;
use External\HannahBundle\SAP\Util\SAPUtil;
use External\HannahBundle\Util\FTP\FTPClient;
use KupShop\CatalogBundle\Parameters\ParameterFinder;
use KupShop\KupShopBundle\Query\JsonOperator;
use KupShop\KupShopBundle\Util\FileUtil;
use KupShop\KupShopBundle\Util\Functional\Mapping;
use KupShop\KupShopBundle\Util\StringUtil;
use KupShop\KupShopBundle\Util\System\PathFinder;
use Query\Operator;

class ImportData
{
    private $pathFinder;

    private $parameterFinder;

    private $sapUtil;

    private $fileUtil;

    public function __construct(
        PathFinder $pathFinder,
        ParameterFinder $parameterFinder,
        SAPUtil $sapUtil,
        FTPClient $fileUtil,
    ) {
        $this->pathFinder = $pathFinder;
        $this->parameterFinder = $parameterFinder;
        $this->sapUtil = $sapUtil;
        $this->fileUtil = $fileUtil;
    }

    public function createProductsAndVariations(string $file, bool $shoesForce = false): void
    {
        $data = [];

        sqlGetConnection()->getConfiguration()->setSQLLogger();

        foreach ($this->loadCSV($this->pathFinder->dataPath($file)) as $item) {
            $productInfo = $item['produkt-info'] ?? '';
            $shoes = false;
            if ($shoesForce || strpos($productInfo, 'boty') !== false) {
                $shoes = true;
            }

            if ($shoes) {
                $color = null;
                $variationName = null;
                if (preg_match('/(?<variant>US \d+(,\d+)?)/', $item['kratky-text-varianty'], $matches)) {
                    $variationName = $matches['variant'];
                }

                $parts = explode(', ', $item['kratky-text-varianty']);
                $color = count($parts) >= 3 ? $parts[0] : null;
            } else {
                $totalParts = count(explode(',', $item['kratky-text-varianty']));

                $variant = trim(trim(str_replace($item['kratky-text'], '', $item['kratky-text-varianty']), ','));
                $parts = array_filter(explode(',', $variant));

                if ($totalParts < 3) {
                    $variationName = null;
                } else {
                    $variationName = trim(end($parts) ?: '');
                }

                $color = null;
                if ($item['kratky-text'] != $item['kratky-text-varianty'] || $item['genericky-kod'] == $item['kod-zbozi-s-variantou']) {
                    if ($variationName === null && count($parts) > 0) {
                        $color = trim($parts[0]);
                    } elseif (count($parts) > 1) {
                        $color = trim($parts[0]);
                    } else {
                        $colorParts = explode(', ', $item['kratky-text']);
                        $color = end($colorParts);
                    }
                }
            }

            $data[$item['genericky-kod']][$color][$item['kod-zbozi-s-variantou']] = [
                'title' => $item['kratky-text'],
                'variation' => $variationName,
                'color' => $color,
                'shoes' => $shoes,
            ];
        }

        $photosInserted = [];
        foreach ($data as $groupCode => $colors) {
            foreach ($colors as $color => $variations) {
                foreach ($variations as $code => $item) {
                    $masterProductCode = $this->getMasterProductCode($groupCode, $code);

                    $exists = sqlQueryBuilder()
                        ->select('id')
                        ->from('products')
                        ->where(
                            Operator::orX(
                                Operator::equals(['code' => $masterProductCode]),
                                Operator::equals(['code' => $groupCode])
                            )
                        )
                        ->execute()->fetchOne();

                    if ($exists) {
                        continue;
                    }

                    $productId = $this->createMasterProduct(
                        $groupCode,
                        $masterProductCode,
                        $item['title'],
                        ''/* (string) $color */
                    );

                    if (!empty($item['variation'])) {
                        // for rockpoint
                        $labelId = 15;
                        if ($item['shoes'] ?? false) {
                            $labelId = 16;
                        }

                        $variationId = sqlGetConnection()->transactional(function () use ($labelId, $productId, $item) {
                            return \Variations::createProductVariation($productId, [
                                $labelId => $item['variation'],
                            ]);
                        });

                        sqlQueryBuilder()
                            ->update('products_variations')
                            ->directValues(
                                [
                                    'code' => $code,
                                ]
                            )
                            ->where(Operator::equals(['id' => $variationId]))
                            ->execute();

                        $this->sapUtil->createItemMapping($code, $productId, (int) $variationId);
                    }

                    // photos
                    if (!($photosInserted[$masterProductCode] ?? false)) {
                        $this->importProductPhotos($productId, $masterProductCode);
                        $photosInserted[$masterProductCode] = true;
                    }
                }
            }
        }
    }

    private function importProductPhotos(int $productId, string $masterCode): void
    {
        $photos = $this->fileUtil->getProductPhotos($masterCode);
        foreach ($photos as $index => $photo) {
            $dest = $this->pathFinder->dataPath('photos/old'.$photo);

            $pathInfo = pathinfo($dest);
            if (!file_exists($pathInfo['dirname'])) {
                mkdir($pathInfo['dirname'], 0777, true);
            }

            $photoSource = trim(str_replace('data/photos/', '', $pathInfo['dirname']), '/').'/';
            $photoName = $pathInfo['basename'];

            if (!isDevelopment() && $this->fileUtil->copyFile($photo, $dest)) {
                $photoId = sqlQueryBuilder()
                    ->select('id')
                    ->from('photos')
                    ->where(Operator::equals(
                        [
                            'filename' => $photoName,
                            'source' => $photoSource,
                        ]
                    ))
                    ->execute()->fetchColumn();

                if (!$photoId) {
                    $photoId = sqlGetConnection()->transactional(function () use ($photoName, $photoSource) {
                        sqlQueryBuilder()
                            ->insert('photos')
                            ->directValues(
                                [
                                    'filename' => $photoName,
                                    'source' => $photoSource,
                                    'image_2' => $photoName,
                                    'date' => (new \DateTime())->format('Y-m-d H:i:s'),
                                ]
                            )->execute();

                        return (int) sqlInsertId();
                    });
                }

                try {
                    sqlQueryBuilder()
                        ->insert('photos_products_relation')
                        ->directValues(
                            [
                                'id_photo' => $photoId,
                                'id_product' => $productId,
                                'id_variation' => null,
                                'show_in_lead' => $index === 0 ? 'Y' : 'N',
                                'position' => $index,
                                'date_added' => (new \DateTime())->format('Y-m-d H:i:s'),
                            ]
                        )->execute();
                } catch (UniqueConstraintViolationException $e) {
                }
            }
        }
    }

    private function createMasterProduct(string $groupCode, string $code, string $title, string $color): int
    {
        static $masterProductsCache = [];

        if ($masterProductsCache[$code] ?? false) {
            return $masterProductsCache[$code];
        }

        $productId = sqlGetConnection()->transactional(function () use ($groupCode, $code, $title, $color) {
            if (!empty($color) && strpos($title, $color) === false) {
                $title = implode(', ', array_filter([$title, $color]));
            }

            sqlQueryBuilder()
                ->insert('products')
                ->directValues(
                    [
                        'code' => $code,
                        'title' => $title,
                        'vat' => 1,
                        'date_added' => (new \DateTime())->format('Y-m-d H:i:s'),
                        'data' => json_encode(['groupCode' => $groupCode]),
                    ]
                )->execute();

            return (int) sqlInsertId();
        });

        if (!empty($color)) {
            $this->addProductParameter($productId, 'Barva', 'list', [$color]);
        }

        $this->sapUtil->createItemMapping($groupCode, $productId);

        return $masterProductsCache[$code] = $productId;
    }

    private function getMasterProductCode(string $groupCode, $variationCode): string
    {
        if ($groupCode === $variationCode) {
            return $groupCode;
        }

        $code = str_replace($groupCode, '', $variationCode);

        return $groupCode.$code[0].$code[1];
    }

    public function importProductsDataFromFiles(array $files): int
    {
        $collections = [];

        $count = 0;

        // product data
        foreach ($this->getFiles() as $file) {
            echo $file.PHP_EOL;
            $parametersDefinition = $this->getParametersDefinition($file);
            $parameterNames = $this->getParameterNames($file);
            foreach ($this->loadCSV($file, 1) as $item) {
                $sapCode = $item['kod-sap'] ?? null;
                if (empty($sapCode)) {
                    $sapCode = $item['sap-kod'] ?? null;
                }

                if ($sapCode) {
                    $sapCode = trim(trim($sapCode, ' '));
                }

                if (empty($item['kod'])) {
                    $item['kod'] = $sapCode;
                }

                if (empty($sapCode)) {
                    continue;
                }

                $qb = sqlQueryBuilder()
                    ->select('id')
                    ->from('products')
                    ->where(Operator::equals(
                        [
                            'code' => $sapCode,
                            JsonOperator::value('data', 'groupCode') => $sapCode,
                        ],
                        'OR'
                    ));

                $products = [];
                foreach ($qb->execute() as $product) {
                    $products[] = ['id' => $product['id']];
                }

                foreach ($products as $p) {
                    $productId = (int) $p['id'];

                    //                    $collections[$item['kod']][] = $productId;

                    $update = [
                        'short_descr' => $item['anotace'] ?? '',
                        'long_descr' => $item['popis-zbozi'] ?? '',
                        'parameters' => $item['rozsirujici-popis'] ?? '',
                    ];

                    if (findModule(\Modules::PRODUCERS) && !empty($item['znacka'])) {
                        $update['producer'] = $this->getProducer($item['znacka']);
                    }

                    sqlQueryBuilder()
                        ->update('products')
                        ->set('data', 'JSON_SET(COALESCE(data, \'{}\'), \'$.codeOld\', "'.$item['kod'].'")')
                        ->directValues($update)
                        ->where(Operator::equals(['id' => $productId]))
                        ->execute();

                    foreach ($parametersDefinition as $key => $type) {
                        $value = $item[$key] ?? null;
                        if (!empty($value)) {
                            $this->addProductParameter($productId, $parameterNames[$key] ?? $key, $type, $this->getParameterValues($value));
                        }
                    }

                    // video link
                    if ($item['video'] ?? false) {
                        preg_match('/src=\"(?<src>[^"]*)\"/', $item['video'], $matches);
                        if ($matches) {
                            sqlQueryBuilder()
                                ->insert('links')
                                ->directValues(
                                    [
                                        'id_product' => $productId,
                                        'link' => $matches['src'],
                                        'type' => 'youtube',
                                    ]
                                )->execute();
                        }
                    }

                    $count++;
                }
            }
        }

        foreach ($collections as $key => $products) {
            $masterProductId = array_shift($products);
            foreach ($products as $productId) {
                try {
                    sqlQueryBuilder()
                        ->insert('products_collections')
                        ->directValues(
                            [
                                'id_product' => $masterProductId,
                                'id_product_related' => $productId,
                            ]
                        )->execute();
                } catch (UniqueConstraintViolationException $e) {
                }
            }
        }

        return $count;
    }

    private function getFiles(): array
    {
        $files = [];
        foreach (scandir($this->pathFinder->dataPath('files/product-data/')) as $file) {
            if (preg_match('/RP_(.+).csv$/i', $file)) {
                $files[] = $this->pathFinder->dataPath('files/product-data/'.$file);
            }
        }

        return $files;
    }

    private function getProducer(string $producer): ?int
    {
        static $producers = [];

        $key = StringUtil::slugify(mb_strtolower($producer));

        if (empty($producer)) {
            return null;
        }

        if (!$producers) {
            $producers = Mapping::mapKeys(
                sqlQueryBuilder()
                    ->select('id, name')
                    ->from('producers')
                    ->execute()->fetchAll(),
                function ($k, $v) {
                    return [StringUtil::slugify(mb_strtolower($v['name'])), (int) $v['id']];
                }
            );
        }

        if (!($producers[$key] ?? false)) {
            $producers[$key] = sqlGetConnection()->transactional(function () use ($producer) {
                sqlQueryBuilder()
                    ->insert('producers')
                    ->directValues(['name' => $producer])
                    ->execute();

                return (int) sqlInsertId();
            });
        }

        return $producers[$key];
    }

    private function getParameterNames(string $file): array
    {
        $header = [];
        foreach (FileUtil::loadCSV($file, ';') as $i => $item) {
            if ($i === 1) {
                $header = $item;
                break;
            }
        }

        $parameterNames = [];
        foreach ($header as $parameterName) {
            $parameterNames[StringUtil::slugify($parameterName)] = $parameterName;
        }

        return $parameterNames;
    }

    private function getParametersDefinition(string $file): array
    {
        $tmpData = [];

        foreach (FileUtil::loadCSV($file, ';') as $i => $item) {
            $tmpData[] = array_map(function ($x) {
                return StringUtil::slugify($x);
            }, $item);

            if ($i > 0) {
                break;
            }
        }

        $parameters = [];
        foreach (array_combine($tmpData[1], $tmpData[0]) as $key => $type) {
            if (empty($type)) {
                continue;
            }

            if (strpos($type, 'seznam') !== false) {
                $type = 'list';
            } elseif (strpos($type, 'cislo') !== false) {
                $type = 'float';
            } elseif (strpos($type, 'text') !== false) {
                $type = 'char';
            } else {
                continue;
            }

            $parameters[$key] = $type;
        }

        unset($parameters['anotace'], $parameters['popis-zbozi'], $parameters['rozsirujici-popis']);

        // rok sezóny	sezóna	Název	pohlaví	značka	info o produktu
        $additional = [
            'rok-sezony' => 'list',
            'sezona' => 'list',
            'pohlavi' => 'list',
            'info-o-produktu' => 'list',
        ];

        return array_merge($additional, $parameters);
    }

    private function getParameterName(string $parameterKey): string
    {
        $parameters = [
            'info-o-produktu' => 'Info o produktu',
            'delka-zipu' => 'Délka zipu',
            'eko-produkt' => 'EKO produkt',
            'konstrukce-spaciho-pytle' => 'Konstrukce spacího pytle',
            'material-vnejsi' => 'Materiál vnější',
            'material-vnitrni' => 'Materiál vnitřní',
            'moznost-spojeni' => 'Možnost spojení',
            'technicke-detaily' => 'Technické detaily',
            'tvar-spaciho-pytle' => 'Tvar spacího pytle',
            'vhodne-pro-aktivity' => 'Vhodné pro aktivity',
            'typ-produktu' => 'Typ produktu',
            'aktivity-hannah' => 'Aktivity hannah',
            'integrovana-plastenka' => 'Integrovaná pláštěnka',
            'kapsa-na-laptop' => 'Kapsa na laptop',
            'vodni-rezervoar' => 'Vodní rezervoár',
            'doporucene-pouziti' => 'Doporučené použití',
            'typ-brasny' => 'Typ brašny',
            'madlo-rukojet' => 'Madlo rukojeť',
            'odpruzeni-antishock' => 'Odpružení antishock',
            'ochrana-hrotu' => 'Ochrana hrotu',
            'odepinaci-poutko' => 'Odepínací poutko',
            'prodlouzena-rukojet' => 'Prodloužená rukojeť',
            'vymenne-hroty' => 'Výměnné hroty',
            'vymenne-talirky' => 'Výměnné talířky',
            'teplotni-rozsah-tmax-tcom' => 'Teplotní rozsah tmax tcom',
            'rozmer-sbaleneho-pytle' => 'Rozměr sbaleného pytle',
            'teplotni-rozsah-tcom-tlim' => 'Teplotní rozsah tcom tlim',
            'rozmer-plachty' => 'Rozměr plachty',
            'vyska-batohu' => 'Výška batohu',
            'vnejsi-material' => 'Vnější materiál',
            'vnitrni-material' => 'Vnitřní materiál',
            'vyska-postavy' => 'Výška postavy',
            'rozsah-delky-cm' => 'Rozsah délky cm',
            'uprava-povrchu' => 'Úprava povrchu',
            'delka-zad-batohu' => 'Délka zad batohu',
            'pocet-bocnich-kapes' => 'Počet bočních kapes',
            'pocet-komor' => 'Počet komor',
            'pocet-dilu' => 'Počet dílů',
            'mezipodesev' => 'Mezipodešev',
            'keen-technology' => 'KEEN technology',
            'podrazka' => 'Podrážka',
            'podsivka' => 'Podšívka',
            'svrsek' => 'Svršek',
            'typ-tlumeni' => 'Typ tlumení',
            'vlozka' => 'Vložka',
            'kolekce' => 'Kolekce',
        ];

        return $parameters[$parameterKey] ?? $parameterKey;
    }

    public function importProductsData(): void
    {
        // common data
        foreach ($this->loadCSV($this->pathFinder->dataPath('files/products.csv')) as $item) {
            sqlQueryBuilder()
                ->update('products')
                ->set('data', 'JSON_SET(COALESCE(data, \'{}\'), \'$.codeOld\', "'.$item['kod-produktu'].'")')
               ->where(Operator::equals(['code' => $item['sap-kod']]))
                ->execute();
        }

        // cisleniky - vhodne pro aktivity
        $vhodneProAktivityMapping = [];
        $vhodneProAktivityId = $this->parameterFinder->findParameter('Vhodné pro aktivity');
        foreach ($this->loadCSV($this->pathFinder->dataPath('files/ciselnik_vhodne_pro_aktivity.csv')) as $item) {
            $valueId = $this->parameterFinder->findParameterValue($vhodneProAktivityId, $item['nazev']);
            $vhodneProAktivityMapping[$item['id']] = $valueId;
            sqlQueryBuilder()
                ->update('parameters_list')
                ->directValues(
                    [
                        'description' => $item['image'],
                    ]
                )
                ->where(Operator::equals(['id' => $valueId]))
                ->execute();
        }

        // cisleniky - material a technologie
        $materialATechnologieMapping = [];
        $materialATechnologieId = $this->parameterFinder->findParameter('Materiál a technologie');
        foreach ($this->loadCSV($this->pathFinder->dataPath('files/ciselnik_material_a_technolgie.csv')) as $item) {
            $valueId = $this->parameterFinder->findParameterValue($materialATechnologieId, $item['nazev']);
            $materialATechnologieMapping[$item['id']] = $valueId;
            sqlQueryBuilder()
                ->update('parameters_list')
                ->directValues(
                    [
                        'description' => strip_tags($item['popis']),
                    ]
                )
                ->where(Operator::equals(['id' => $valueId]))
                ->execute();
        }

        $multiParameters = [
            'vhodne-pro-aktivity' => ['Vhodné pro aktivity', $vhodneProAktivityMapping],
            'material' => ['Materiál a technologie', $vhodneProAktivityMapping],
        ];

        $staticParameters = [
            'vnejsi-material' => 'Vnější materiál',
            'vnitrni-material' => 'Vnitřní materiál',
            'vypln' => 'Výplň',
            'zater' => 'Zátěr',
            'rozmer' => 'Rozměr',
            'slozeni' => 'Složení',
        ];

        // product data
        foreach ($this->loadCSV($this->pathFinder->dataPath('files/product_parameters.csv')) as $item) {
            if (empty($item['kod'])) {
                continue;
            }

            if (!($productId = $this->getProductIdByOldCode($item['kod']))) {
                continue;
            }

            sqlQueryBuilder()
                ->update('products')
                ->directValues(
                    [
                        'short_descr' => $item['anotace'],
                        'long_descr' => $item['popis'],
                    ]
                )
                ->where(Operator::equals(['id' => $productId]))
                ->execute();

            // static parameters
            foreach ($staticParameters as $key => $name) {
                if (!empty($item[$key])) {
                    $this->addProductParameter($productId, $name, 'list', [$item[$key]]);
                }
            }

            // multi parameters
            foreach ($multiParameters as $key => [$name, $values]) {
                if (!empty($item[$name])) {
                    $v = array_filter(explode('|', $item[$key]));
                    $valueIds = [];
                    foreach ($v as $i) {
                        $valueIds[] = $values[$i] ?? null;
                    }
                    $this->addProductParameter($productId, $name, 'list', array_filter($valueIds));
                }
            }

            // video link
            preg_match('/src=\"(?<src>[^"]*)\"/', $item['video'], $matches);
            if ($matches) {
                sqlQueryBuilder()
                    ->insert('link')
                    ->directValues(
                        [
                            'id_product' => $productId,
                            'link' => $matches['src'],
                            'type' => 'youtube',
                        ]
                    )->execute();
            }
        }
    }

    private function getParameterValues(string $value): array
    {
        return array_filter(preg_split('/(;|\|)/', $value));
    }

    private function addProductParameter(int $productId, string $parameterName, string $type, array $values): void
    {
        $parameterId = $this->parameterFinder->findParameter($parameterName, $type);

        if ($type === 'list') {
            $valueIds = [];
            foreach ($values as $value) {
                $valueIds[] = $this->parameterFinder->findParameterValue($parameterId, $value);
            }
        } else {
            $valueIds = $values;
        }

        $this->parameterFinder->setProductParameters($productId, $parameterId, $valueIds);
    }

    private function getProductIdByOldCode(string $codeOld): ?int
    {
        $productId = sqlQueryBuilder()
            ->select('id')
            ->from('products')
            ->where(Operator::equals([JsonOperator::value('data', 'codeOld') => $codeOld]))
            ->execute()->fetchColumn();

        if (!$productId) {
            return null;
        }

        return (int) $productId;
    }

    private function loadCSV($file, $headerLine = 0, $mergeHeader = true, $slugifyHeader = true, $cut = null, $delimeter = ';'): array
    {
        $return = [];
        $header = null;

        $index = 0;
        if (($handle = fopen($file, 'r')) !== false) {
            while (($data = fgetcsv($handle, 999999, $delimeter)) !== false) {
                if ($cut) {
                    $data = array_slice($data, 0, $cut);
                }

                if ($index < $headerLine) {
                    $index++;
                    continue;
                }

                if ($index == $headerLine) {
                    $header = $data;
                    if ($slugifyHeader) {
                        $exists = [];
                        foreach ($header as &$headerItem) {
                            $headerItem = StringUtil::slugify($headerItem);

                            if ($exists[$headerItem] ?? false) {
                                $headerItem .= '-2';
                            }

                            $exists[$headerItem] = true;
                        }
                    }
                    $index++;
                    continue;
                }

                if ($mergeHeader) {
                    $headerOriginal = $header;
                    if (count($header) > count($data)) {
                        $header = array_splice($header, 0, -(count($header) - count($data)));
                    }

                    $return[] = array_combine($header, $data);

                    $header = $headerOriginal;
                } else {
                    $return[] = $data;
                }
            }
            fclose($handle);
        }

        return $return;
    }
}
