<?php

declare(strict_types=1);

namespace External\HannahBundle\SAP\Util;

use External\HannahBundle\SAP\Exception\SAPException;
use External\HannahBundle\SAP\Synchronizer\POSOrderSynchronizer;
use External\HannahBundle\Util\FTP\SFTPClient;
use KupShop\AdminBundle\Util\ActivityLog;
use KupShop\KupShopBundle\Util\Functional\Mapping;
use KupShop\KupShopBundle\Util\Logging\SentryLogger;
use KupShop\KupShopBundle\Util\System\PathFinder;
use KupShop\SynchronizationBundle\Logger\SynchronizationLogger;
use KupShop\SynchronizationBundle\Util\SynchronizationLoggerFactory;
use Query\Operator;

class SAPUtil
{
    private ?SynchronizationLogger $logger = null;

    public function __construct(
        private SAPLocator $sapLocator,
        private SFTPClient $sftpClient,
        private ActivityLog $activityLog,
        private SentryLogger $sentryLogger,
        private SynchronizationLoggerFactory $loggerFactory,
    ) {
    }

    public function getOrderCreatedCoupon(\Order $order): array
    {
        $coupons = [];

        foreach ($order->fetchItems() as $item) {
            if (!empty($item['note']['created_coupons'])) {
                $coupons = array_merge($coupons, $item['note']['created_coupons']);
            }
        }

        return array_unique($coupons);
    }

    /**
     * Koukne, zda jsou data v JSON souboru a pripadne ho naloaduje a vrati jeho data. V opacnem pripade vrati stejna
     * data, ktera prisly.
     */
    public function loadSAPData(array $data): ?array
    {
        ini_set('memory_limit', '4096M');

        $convertToItemsField = ['Catalog', 'Dispo'];

        // kouknu jestli je to zmena, ktera vyzaduje nacteni souboru
        if ($filename = $this->getAdditionalFieldValue($data['Additionals'] ?? [], 'filename')) {
            try {
                // nactu soubor z FTP
                $file = $this->sftpClient->getRemoteJSONFile($filename);
            } catch (\Throwable $e) {
                throw new SAPException(sprintf('Download of JSON file from FTP failed: %s', $e->getMessage()));
            }

            if (!file_exists($file)) {
                throw new SAPException(sprintf('Missing JSON file: "%s"', $file));
            }

            $data = json_decode(file_get_contents($file), true);
            // poladim strukturu, aby odpovidala tomu, co zpracovavame standartne
            foreach ($convertToItemsField as $field) {
                if (isset($data[$field])) {
                    $data['Items']['item'] = $data[$field];
                    unset($data[$field]);
                }
            }

            $data['isFileImport'] = true;
            $data['filename'] = $filename;

            if (!isset($data['Additionals'])) {
                $data['Additionals'] = [];
            }

            $filesize = filesize($file) / (1024 * 1024);

            $this->addActivityLog(
                sprintf(sprintf('Úspěšně načten JSON: %s', $filename)),
                [
                    'path' => $file,
                    'size' => $filesize.' MB',
                ],
                ActivityLog::SEVERITY_SUCCESS
            );
        }

        return $data;
    }

    public function updateJSONFileAsDone(string $filename): void
    {
        try {
            $this->sftpClient->renameRemoteFile($filename, $filename.'.DONE');
        } catch (\Throwable $e) {
            $this->sentryLogger->captureException($e);
        }
    }

    public function getAdditionalFieldValue(array $data, string $field): ?string
    {
        foreach ($data['item'] ?? [] as $item) {
            if (($item['Fieldname'] ?? '') === $field || ($item['FIELDNAME'] ?? '') === $field) {
                return $item['Fieldvalue'] ?? $item['FIELDVALUE'] ?? null;
            }
        }

        return null;
    }

    public function getMappingData(string $type, int $id): array
    {
        $data = sqlQueryBuilder()
            ->select('data')
            ->from('sap_'.$type.'s')
            ->where(
                Operator::equals(
                    [
                        'id_'.$type => $id,
                    ]
                )
            )
            ->execute()->fetchColumn();

        if (!$data) {
            return [];
        }

        return json_decode($data, true) ?? [];
    }

    public function setMappingData(string $type, int $id, array $data): void
    {
        $data = array_merge(
            $this->getMappingData($type, $id),
            $data
        );

        sqlQueryBuilder()
            ->update('sap_'.$type.'s')
            ->directValues(
                [
                    'data' => json_encode($data),
                ]
            )
            ->where(
                Operator::equals(
                    [
                        'id_'.$type => $id,
                    ]
                )
            )
            ->execute();
    }

    public function createMapping(string $type, $sapId, int $id): void
    {
        sqlQueryBuilder()
            ->insert('sap_'.$type.'s')
            ->directValues(
                [
                    'id_sap' => $sapId,
                    'id_'.$type => $id,
                ]
            )
            ->execute();
    }

    public function getMapping(string $type, $sapId): ?int
    {
        $id = sqlQueryBuilder()
            ->select('id_'.$type)
            ->from('sap_'.$type.'s')
            ->where(Operator::equals(['id_sap' => $sapId]))
            ->execute()->fetchColumn();

        if (!$id) {
            return null;
        }

        return (int) $id;
    }

    public function getSapId(string $type, int $id): ?string
    {
        $sapId = sqlQueryBuilder()
            ->select('id_sap')
            ->from('sap_'.$type.'s')
            ->where(Operator::equals(['id_'.$type => $id]))
            ->execute()->fetchOne();

        if (!$sapId) {
            return null;
        }

        return $sapId;
    }

    public function deleteMapping(string $type, int $id): void
    {
        sqlQueryBuilder()
            ->delete('sap_'.$type.'s')
            ->where(
                Operator::equals(
                    [
                        'id_'.$type => $id,
                    ]
                )
            )
            ->execute();
    }

    public function deleteItemMapping($sapId, int $productId, ?int $variationId = null): void
    {
        sqlQueryBuilder()
            ->delete('sap_products')
            ->where(
                Operator::equals(
                    [
                        'id_sap' => $sapId,
                        'id_product' => $productId,
                        'id_variation' => $variationId,
                    ]
                )
            )->execute();
    }

    public function createItemMapping($sapId, int $productId, ?int $variationId = null): void
    {
        sqlQueryBuilder()
            ->insert('sap_products')
            ->directValues(
                [
                    'id_sap' => $sapId,
                    'id_product' => $productId,
                    'id_variation' => $variationId,
                ]
            )->execute();
    }

    public function getItemMapping($sapId): ?array
    {
        static $itemMappingCache;

        // cache reset
        if ($itemMappingCache !== null && $itemMappingCache['timestamp'] < time()) {
            $itemMappingCache = null;
        }

        // init cache
        if ($itemMappingCache === null) {
            $itemMappingCache = [
                'data' => null,
                'timestamp' => time() + 1800,
            ];
        }

        if (($itemMappingCache['data'] ?? null) === null) {
            $itemMappingCache['data'] = Mapping::mapKeys(sqlQueryBuilder()
                ->select('id_sap, id_product, id_variation')
                ->from('sap_products')
                ->sendToMaster()
                ->execute(), fn ($k, $v) => [$v['id_sap'], [$v['id_product'], $v['id_variation']]]);
        }

        // fetch data and cache it
        if (!($itemMappingCache['data'][$sapId] ?? null)) {
            $data = sqlQueryBuilder()
                ->select('id_product, id_variation')
                ->from('sap_products')
                ->where(Operator::equals(['id_sap' => $sapId]))
                ->orderBy('id_variation', 'DESC')
                ->sendToMaster()
                ->execute()->fetchAssociative();

            if ($data) {
                $itemMappingCache['data'][$sapId] = [(int) $data['id_product'], $data['id_variation'] ? (int) $data['id_variation'] : null];
            }
        }

        return $itemMappingCache['data'][$sapId] ?? null;
    }

    public function getItemSapId(int $productId, ?int $variationId = null): ?string
    {
        $sapId = sqlQueryBuilder()
            ->select('id_sap')
            ->from('sap_products')
            ->where(
                Operator::equalsNullable(
                    [
                        'id_product' => $productId,
                        'id_variation' => $variationId,
                    ]
                )
            )
            ->execute()->fetchColumn();

        if (!$sapId) {
            return null;
        }

        return $sapId;
    }

    public function formatZip(string $zip, string $country): string
    {
        $formatZipPL = function (string $zip): string {
            $parts = explode('-', $zip);
            $zip = implode('', $parts);

            return substr($zip, 0, 2).'-'.substr($zip, 2);
        };

        $formatZipNL = function (string $zip): string {
            $zip = str_replace(' ', '', $zip);

            // nizozemskej zip na konci muze/nemusi mit dve pismena
            $zipLetters = is_numeric(substr($zip, -2)) ? '  ' : substr($zip, -2);

            return substr($zip, 0, 4).' '.$zipLetters;
        };

        $countryFormatZip = match ($country) {
            'DE', 'RO', 'AT', 'IT', 'FR', 'ES', 'BE', 'HR', 'HU' => str_replace(' ', '', $zip),
            'PL' => $formatZipPL($zip),
            'NL' => $formatZipNL($zip),
            default => null,
        };

        if ($countryFormatZip) {
            return $countryFormatZip;
        }

        if (strlen($zip) !== 6) {
            $zip = substr($zip, 0, 3).' '.substr($zip, 3);
        }

        return $zip;
    }

    public function formatPhone(string $phone): string
    {
        return str_replace(['+420', '+421'], ['', ''], $phone);
    }

    public function formatCity(string $city): string
    {
        return mb_substr($city, 0, 40);
    }

    public function updateProductPricesByVariations(): void
    {
        // update product prices
        sqlQuery('UPDATE products p
            JOIN products_variations pv ON p.id = pv.id_product
            SET p.price = COALESCE((SELECT MIN(pv2.price)
            FROM products_variations pv2
            WHERE pv2.id_product = p.id AND pv2.price > 0 AND pv2.figure = "Y"), p.price);');
    }

    public function recalculateUpdatedProducts(): void
    {
        sqlGetConnection()->transactional(function () {
            $qb = sqlQueryBuilder()
                ->select('id, id_product')
                ->from('stores_items')
                ->where(Operator::equals(['sap_updated' => 1]))
                ->forUpdate();

            $doUpdateItems = function (array $updateItems) {
                $this->recalculateStores(array_unique(array_map(fn ($x) => $x['id_product'], $updateItems)));
                sqlQueryBuilder()
                    ->update('stores_items')
                    ->directValues(['sap_updated' => 0])
                    ->where(Operator::inIntArray(array_unique(array_map(fn ($x) => $x['id'], $updateItems)), 'id'))
                    ->execute();
            };

            $updateItems = [];
            foreach ($qb->execute() as $item) {
                $updateItems[] = $item;

                if (count($updateItems) >= 1000) {
                    $doUpdateItems($updateItems);
                    $updateItems = [];
                }
            }

            if (!empty($updateItems)) {
                $doUpdateItems($updateItems);
            }
        });
    }

    public function recalculateStores(array $productIds = []): void
    {
        sqlGetConnection()->transactional(function () use ($productIds) {
            $getQuantitySubQuery = function (bool $variations = false) {
                $qb = sqlQueryBuilder()
                    ->select('COALESCE(SUM(quantity), 0)')
                    ->from('stores_items', 'si')
                    ->join('si', 'stores', 's', 's.id = si.id_store')
                    ->andWhere(Operator::equals(['s.figure' => 'Y']));

                if ($variations) {
                    $qb->andWhere('si.id_product = pv.id_product AND si.id_variation = pv.id');
                } else {
                    $qb->andWhere('si.id_product = p.id AND si.id_variation IS NULL');
                }

                return $qb->getRunnableSQL();
            };

            $productsQuantitySubQuery = $getQuantitySubQuery();

            $productsQb = sqlQueryBuilder()
                ->update('products', 'p')
                ->join('p', 'sap_products', 'sp', 'sp.id_product = p.id AND sp.id_variation IS NULL')
                ->set('p.in_store', "({$productsQuantitySubQuery})")
                ->andWhere("in_store != ({$productsQuantitySubQuery})");

            if (!empty($productIds)) {
                $productsQb->andWhere(Operator::inIntArray($productIds, 'p.id'));
            }

            $productsQb->execute();

            $variationsQuantitySubQuery = $getQuantitySubQuery(true);

            $variationsQb = sqlQueryBuilder()
                ->update('products_variations', 'pv')
                ->join('pv', 'sap_products', 'sp', 'sp.id_product = pv.id_product AND sp.id_variation = pv.id')
                ->set('pv.in_store', "({$variationsQuantitySubQuery})")
                ->andWhere("pv.in_store != ({$variationsQuantitySubQuery})");

            if (!empty($productIds)) {
                $variationsQb->andWhere(Operator::inIntArray($productIds, 'pv.id_product'));
            }

            $variationsQb->execute();

            \Variations::recalcInStore();
        });
    }

    /**
     * Skrývání produktů, které se delší dobu neaktualizovaly.
     *
     * Pokud SAP produkt nebyl přes synchronizace aktualizován více jak X hodin, tak produkt automaticky skryjeme.
     * SAP každou noc posílá do synchronizace celý katalog a tímpádem jakmile něco není aktualizované, tak to bylo v SAPu
     * z katalogu odebrané.
     */
    public function updateProductsVisibility(int $hours = 50, ?array $deadlockedProducts = null): void
    {
        $productsToHide = sqlQueryBuilder()
            ->select('p.id, p.code, sp.date_updated as sap_date_updated')
            ->from('products', 'p')
            ->join('p', 'sap_products', 'sp', 'sp.id_product = p.id AND sp.id_variation IS NULL')
            ->andWhere(Operator::equals(['p.figure' => 'Y']))
            ->andWhere('TIMESTAMPDIFF(HOUR, sp.date_updated, NOW()) > '.$hours)
            ->andWhere('p.in_store > 0')
            // pokud nemam prázdný pole nacpu podmínku jinak '1'
            ->andWhere(!empty($deadlockedProducts) ? Operator::not(Operator::inIntArray($deadlockedProducts, 'p.id')) : '1')
            ->sendToMaster()
            ->execute()->fetchAllAssociative();
        // skryjeme produkty, ktere se vice jak X hodin neaktualizovaly
        sqlQueryBuilder()
            ->update('products', 'p')
            ->join('p', 'sap_products', 'sp', 'sp.id_product = p.id AND sp.id_variation IS NULL')
            ->directValues(
                [
                    'figure' => 'O',
                ]
            )
            ->andWhere(Operator::equals(['p.figure' => 'Y']))
            ->andWhere('TIMESTAMPDIFF(HOUR, sp.date_updated, NOW()) > '.$hours)
            ->andWhere('p.in_store > 0')
            // pokud nemam prázdný pole nacpu podmínku jinak '1'
            ->andWhere(!empty($deadlockedProducts) ? Operator::not(Operator::inIntArray($deadlockedProducts, 'p.id')) : '1')
            ->execute();
        //        // zobrazime produktu, ktere jsou aktualizovane a jsou v prodeji ukoncen
        //        sqlQueryBuilder()
        //            ->update('products', 'p')
        //            ->join('p', 'sap_products', 'sp', 'sp.id_product = p.id AND sp.id_variation IS NULL')
        //            ->directValues(
        //                [
        //                    'figure' => 'Y',
        //                ]
        //            )
        //            ->andWhere(Operator::equals(['p.figure' => 'O']))
        //            ->andWhere('TIMESTAMPDIFF(HOUR, sp.date_updated, NOW()) < '.$hours)
        //            ->andWhere('p.in_store > 0')
        //            ->execute();

        if (!empty($productsToHide)) {
            $this->addActivityLog(
                sprintf('Byly skryty produkty, které nebyly ve zpracovaném FULL katalogu: %s', count($productsToHide)),
                ['products' => $productsToHide],
                ActivityLog::SEVERITY_NOTICE
            );
        }
    }

    public function prepareSAPItems(object $data): array
    {
        $items = [];

        if (isset($data->item)) {
            if (!is_array($data->item)) {
                $items = [$data->item];
            } else {
                $items = $data->item ?? [];
            }
        }

        return $items;
    }

    public function synchronize(...$types): void
    {
        foreach ($types as $type) {
            $synchronizer = $this->sapLocator->get($type);

            try {
                $synchronizer->processToSAP();
            } catch (\Throwable $e) {
                if (isDevelopment()) {
                    throw $e;
                }

                if (!($e instanceof SAPException)) {
                    $this->sentryLogger->captureException($e);
                }

                $this->addActivityLog(
                    sprintf('Synchronizace typu %s nebyla úspěšná', $type),
                    [
                        'message' => $e->getMessage(),
                    ]
                );
            }
        }
    }

    public function synchronizeSales(?callable $processItemCallback = null): void
    {
        $synchronizer = $this->sapLocator->get(POSOrderSynchronizer::getType());

        if ($processItemCallback) {
            $synchronizer->setProcessItemCallback($processItemCallback);
        }

        $synchronizer->process([]);
    }

    public function updateUser(\User $user, object $customer): void
    {
        // nactu si karty uzivatele
        $cards = [];
        if (!empty($customer->PersonalData->PartnerCards)) {
            foreach ($this->prepareSAPItems($customer->PersonalData->PartnerCards) as $card) {
                $cards[] = $card->PartnerCard;
            }
        }

        if ($user->figure !== 'Y') {
            $userFields = [
                'figure' => 'Y',
            ];

            if (!isset($user->date_reg)) {
                $userFields['date_reg'] = (new \DateTime())->format('Y-m-d H:i:s');
            }

            sqlQueryBuilder()
                ->update('users')
                ->directValues($userFields)
                ->where(Operator::equals(['id' => $user->id]))
                ->execute();
        }

        $user->setCustomData('sap', [
            'discountIndex' => $customer->PersonalData->DiscountIndex,
            'cards' => $cards,
        ]);

        $this->updateUserDiscount($user, $customer->PersonalData->DiscountIndex);
    }

    public function updateUserDiscount(\User $user, string $discountIndex): void
    {
        // update user discount index
        $priceLevelId = sqlQueryBuilder()
            ->select('id')
            ->from('price_levels')
            ->where(Operator::equals(['descr' => $discountIndex]))
            ->execute()->fetchColumn();

        sqlGetConnection()->transactional(function () use ($user, $priceLevelId) {
            sqlQueryBuilder()
                ->delete('users_dealer_price_level')
                ->where(Operator::equals(
                    [
                        'id_user' => $user->id,
                    ]
                ))->execute();

            if ($priceLevelId) {
                sqlQueryBuilder()
                    ->insert('users_dealer_price_level')
                    ->directValues(
                        [
                            'id_user' => $user->id,
                            'id_price_level' => $priceLevelId,
                        ]
                    )->execute();
            }
        });
    }

    public function cleanupJsonFiles(): void
    {
        $pathFinder = PathFinder::getService();

        foreach (scandir($pathFinder->getTmpDir()) as $file) {
            if (preg_match('/(.*)_(.*)_FULL_(.*).json$/', $file)) {
                unlink($pathFinder->tmpPath($file));
            }
        }
    }

    public function addActivityLog(string $message, array $data = [], string $severity = ActivityLog::SEVERITY_ERROR): bool
    {
        $this->activityLog->addActivityLog($severity, ActivityLog::TYPE_SYNC, sprintf('[SAP] %s', $message), $data);

        return true;
    }

    public function getLogger(): SynchronizationLogger
    {
        if (!$this->logger) {
            $this->logger = $this->loggerFactory->create(
                [SAPException::class],
                'SAP'
            );
        }

        return $this->logger;
    }
}
