<?php

declare(strict_types=1);

namespace External\ZNZBundle\Synchronizers;

use KupShop\AdminBundle\Util\ActivityLog;
use KupShop\KupShopBundle\Context\DomainContext;
use KupShop\KupShopBundle\Context\LanguageContext;
use KupShop\KupShopBundle\Util\Contexts;
use KupShop\KupShopBundle\Util\Database\QueryHint;
use KupShop\KupShopBundle\Util\System\PathFinder;
use Query\Operator;
use Query\QueryBuilder;
use Symfony\Contracts\Service\Attribute\Required;

class ObjectValidationSynchronizer extends BaseSynchronizer
{
    protected static string $type = 'object_validation';

    #[Required]
    public ProductSynchronizer $productSynchronizer;
    #[Required]
    public PathFinder $pathFinder;

    private static bool $isDebugMode = false;

    public static function getHandledTables(): array
    {
        return [
            'PlatneEntity' => 'processObjectValidation',
        ];
    }

    public function processObjectValidation(array $item): void
    {
        if (!($method = $this->getObjectHandlers()[$item['Tabulka']] ?? null)) {
            return;
        }

        if (!$this->isAllowed()) {
            return;
        }

        QueryHint::withRouteToMaster(fn () => $this->{$method}($item['IDs:'] ?? [], $item['Tabulka']));
    }

    protected function preprocessItems(array $items): array
    {
        return [$items];
    }

    protected function getObjectHandlers(): array
    {
        return [
            'ProduktWebsite' => 'handleProductWebsiteValidation',
            'ProduktAtributy1' => 'handleProductValidation',
            'ProduktAtributOptions' => 'handleParameterValuesValidation',
            //            'ProduktAtributOptions1' => 'handleParameterValuesValidation',
            //            'ProduktAtributOptions2' => 'handleParameterValuesValidation',
            'ProduktAtributy2' => 'handleProductParameterValidation',
            'ProduktObrazek' => 'handleProductPhotosValidation',
            'ZakaznickaSkupina' => 'handleUserGroupsValidation',
        ];
    }

    /**
     * Validace vazby produkt <-> website. Pokud nejakou odebrali, tak ji smazneme.
     */
    private function handleProductWebsiteValidation(array $validIds, string $objectName): void
    {
        $values = [];
        foreach ($validIds as $validId) {
            $parts = explode('-', $validId);
            $id = array_shift($parts);
            $website = implode('-', $parts);

            if (empty($this->configuration->getSupportedWebsites()[$website])) {
                continue;
            }

            $values[] = [
                'id_znz' => $id,
                'type' => 'product_website',
                'data' => $website,
            ];
        }

        $this->withValidItems($values, 'product_website', function (QueryBuilder $validIdsQb) use ($objectName) {
            $validIdsQb->addSelect('data as id_website');

            $invalidIds = sqlQueryBuilder()
                ->select('id_znz, id_website')
                ->from('znz_products_website')
                ->where(Operator::not(Operator::inSubQuery('(id_znz, id_website)', $validIdsQb)));

            $deleteQb = sqlQueryBuilder()
                ->delete('znz_products_website', 'zpw')
                ->joinSubQuery('zpw', $invalidIds, 't', 't.id_znz = zpw.id_znz AND t.id_website = zpw.id_website');

            if (!$this->isSafeToExecute($deleteQb, $objectName, selectIdField: 'id_znz')) {
                return;
            }

            $deleteQb->execute();
        });
    }

    /**
     * Validace a mazani produktu, ktere v Heliosu uz nejsou.
     */
    private function handleProductValidation(array $validIds, string $objectName): void
    {
        $values = [];
        foreach ($validIds as $id) {
            $values[] = [
                'id_znz' => $id,
                'type' => ProductSynchronizer::getType(),
                'data' => null,
            ];
        }

        $this->withValidItems($values, ProductSynchronizer::getType(), function (QueryBuilder $validIdsQb) use ($objectName) {
            $invalidProductsSubquery = sqlQueryBuilder()
                ->select('p.id')
                ->from('products', 'p')
                ->join('p', 'znz_products', 'zp', 'zp.id_product = p.id AND zp.id_variation IS NULL')
                ->andWhere(Operator::not(Operator::inSubQuery('zp.id_znz', $validIdsQb)));

            $deleteProductsQb = sqlQueryBuilder()
                ->delete('products', 'p')
                ->joinSubQuery('p', $invalidProductsSubquery, 't', 't.id = p.id');

            if ($this->isSafeToExecute($deleteProductsQb, "{$objectName} (products)")) {
                $deleteProductsQb->execute();
            }

            $invalidVariationsSubquery = sqlQueryBuilder()
                ->select('pv.id')
                ->from('products_variations', 'pv')
                ->join('pv', 'znz_products', 'zp', 'zp.id_product = pv.id_product AND zp.id_variation = pv.id')
                ->andWhere(Operator::not(Operator::inSubQuery('zp.id_znz', $validIdsQb)));

            $deleteVariationsQb = sqlQueryBuilder()
                ->delete('products_variations', 'pv')
                ->joinSubQuery('pv', $invalidVariationsSubquery, 't', 't.id = pv.id');

            if ($this->isSafeToExecute($deleteVariationsQb, "{$objectName} (variations)")) {
                $deleteVariationsQb->execute();
            }
        });
    }

    /**
     * Validace a mazani hodnot parametru, ktere v Heliosu uz nejsou.
     */
    private function handleParameterValuesValidation(array $validIds, string $objectName): void
    {
        $values = [];
        foreach ($validIds as $validId) {
            $parsed = explode('-', $validId);

            $id = (int) $parsed[0];
            $lang = $parsed[2] ?? '';

            if (!($language = $this->getLanguageByLocale($lang))) {
                continue;
            }

            $values[] = [
                'id_znz' => $id,
                'type' => ParameterSynchronizer::$typeValues,
                'data' => json_encode([
                    'id_parameter' => $this->znzUtil->getParameterByKey($parsed[1]),
                    'id_language' => $language,
                ]),
            ];
        }

        $generator = function () {
            foreach ($this->getLanguageContext()->getAll() as $lang) {
                yield [$lang->getId(), Operator::equals(['JSON_VALUE(data, "$.id_language")' => $lang->getId()])];
            }
        };

        $this->withValidItems($values, ParameterSynchronizer::$typeValues, function (QueryBuilder $validIdsQb, string $language) use ($objectName) {
            $validIdsQb
                ->select('CAST(id_znz AS DECIMAL) as id_znz')
                ->addSelect('CAST(JSON_VALUE(data, "$.id_parameter") AS UNSIGNED) as id_parameter');

            $invalidIds = sqlQueryBuilder()
                ->select('pl.id')
                ->from('parameters_list', 'pl')
                ->join('pl', 'znz_parameters_values', 'zpv', 'zpv.id_parameters_value = pl.id')
                ->andWhere(Operator::not(Operator::inSubQuery('(zpv.id_znz, zpv.id_parameter)', $validIdsQb)));

            $isTranslation = $language !== $this->getLanguageContext()->getDefaultId();

            $deleteQb = sqlQueryBuilder()
                ->delete($isTranslation ? 'parameters_list_translations' : 'parameters_list', 'pt')
                ->joinSubQuery('pt', $invalidIds, 't', 't.id = '.($isTranslation ? 'pt.id_parameters_list' : 'pt.id'));

            // keep empty translations
            if ($isTranslation) {
                $deleteQb->andWhere('value != ""');
            }

            if ($isTranslation) {
                $deleteQb->andWhere(Operator::equals(['id_language' => $language]));
            }

            if (!$this->isSafeToExecute($deleteQb, $objectName, ['id_language' => $language])) {
                return;
            }

            $deleteQb->execute();
        }, $generator());
    }

    /**
     * Validace parametru produktu. Smaze prirazene hodnoty parametru od produktu, ktere uz nejsou v Heliosu.
     */
    public function handleProductParameterValidation(array $validIds, string $objectName): void
    {
        $values = [];
        foreach ($validIds as $id) {
            $parsed = explode('-', $id);

            $values[] = [
                // parameters_list id
                'id_znz' => $parsed[2],
                'type' => 'parameters_products',
                'data' => json_encode([
                    'id_parameter' => $this->znzUtil->getParameterByKey($parsed[1]),
                    'id_product' => $parsed[0],
                ]),
            ];
        }

        $this->withValidItems($values, 'parameters_products', function (QueryBuilder $validIdsQb) use ($objectName) {
            $validIdsQb->select('CAST(id_znz AS DECIMAL) as id_znz')
                ->addSelect('CAST(JSON_VALUE(data, "$.id_parameter") AS INT) as id_parameter')
                ->addSelect('CAST(JSON_VALUE(data, "$.id_product") AS INT) as id_product');

            $invalidIds = sqlQueryBuilder()
                ->select('pp.id')
                ->from('parameters_products', 'pp')
                ->join('pp', 'parameters_list', 'pl', 'pl.id_parameter = pp.id_parameter AND pl.id = pp.value_list')
                ->join('pp', 'znz_parameters_values', 'zpv', 'zpv.id_parameter = pp.id_parameter AND zpv.id_parameters_value = pl.id')
                ->join('zpv', 'znz_parameters', 'zp', 'zp.id_parameter = zpv.id_parameter')
                ->andWhere(Operator::not(Operator::inStringArray($this->productSynchronizer->getProduktAtributy1Parameters(), 'zp.id_znz')))
                ->andWhere(Operator::not(Operator::inSubQuery('(zpv.id_znz, zpv.id_parameter, pp.id_product)', $validIdsQb)));

            $deleteQb = sqlQueryBuilder()
                ->delete('parameters_products', 'pp')
                ->joinSubQuery('pp', $invalidIds, 't', 'pp.id = t.id');

            if (!$this->isSafeToExecute($deleteQb, $objectName)) {
                return;
            }

            $deleteQb->execute();
        });
    }

    /**
     * Validace fotek. Smaze fotky, ktere uz nejsou v Heliosu.
     */
    public function handleProductPhotosValidation(array $validIds, string $objectName): void
    {
        $values = [];
        foreach ($validIds as $id) {
            $values[] = [
                'id_znz' => explode('-', $id)[0],
                'type' => 'photo',
                'data' => null,
            ];
        }

        $this->withValidItems($values, 'photo', function (QueryBuilder $validIdsQb) use ($objectName) {
            $invalidIds = sqlQueryBuilder()
                ->select('ph.id')
                ->from('photos', 'ph')
                ->join('ph', 'znz_photos', 'zph', 'zph.id_photo = ph.id')
                ->andWhere(Operator::not(Operator::inSubQuery('zph.id_znz', $validIdsQb)));

            $deleteQb = sqlQueryBuilder()
                ->delete('photos', 'ph')
                ->joinSubQuery('ph', $invalidIds, 't', 'ph.id = t.id');

            if (!$this->isSafeToExecute($deleteQb, $objectName)) {
                return;
            }

            $deleteQb->execute();
        });
    }

    /**
     * Validace skupin uzivatelu. Smaze skupiny, ktere uz nejsou v Heliosu.
     */
    public function handleUserGroupsValidation(array $validIds, string $objectName): void
    {
        $values = [];
        foreach ($validIds as $id) {
            $values[] = [
                'id_znz' => $id,
                'type' => UserSynchronizer::$typeGroups,
                'data' => null,
            ];
        }

        $this->withValidItems($values, UserSynchronizer::$typeGroups, function (QueryBuilder $validIdsQb) use ($objectName) {
            $invalidIds = sqlQueryBuilder()
                ->select('ug.id')
                ->from('users_groups', 'ug')
                ->join('ug', 'znz_users_groups', 'zug', 'zug.id_users_group = ug.id')
                ->andWhere(Operator::not(Operator::inSubQuery('zug.id_znz', $validIdsQb)));

            $deleteQb = sqlQueryBuilder()
                ->delete('users_groups')
                ->where(Operator::inSubQuery('id', $invalidIds));

            if (!$this->isSafeToExecute($deleteQb, $objectName)) {
                return;
            }

            $deleteQb->execute();
        });
    }

    /**
     * @param iterable<array{0: string, 1: callable}>|null $iterator
     */
    private function withValidItems(array $validItems, string $type, callable $fn, ?iterable $iterator = null): void
    {
        // smaznout vsechny valid objekty
        sqlQuery('TRUNCATE TABLE znz_valid_objects');

        // ted zacnu insertovat nove valid objekty z $validItems
        if (!empty($validItems)) {
            $baseQb = sqlQueryBuilder()
                ->insert('znz_valid_objects');

            foreach (array_chunk($validItems, 1000) as $chunk) {
                $qb = clone $baseQb;
                foreach ($chunk as $item) {
                    $qb->multiDirectValues($item);
                }
                $qb->execute();
            }
        }

        $validIdsBaseQb = sqlQueryBuilder()
            ->select('CAST(id_znz AS INT) as id_znz')
            ->from('znz_valid_objects')
            ->andWhere(Operator::equals(['type' => $type]));

        // pokud nemam iterator tak poslu rovnou validIdBaseQb
        if (!$iterator) {
            $fn($validIdsBaseQb);

            return;
        }

        // pokud mam iterator, tak pro kazdy iterator generuju specificky query builder
        foreach ($iterator as [$value, $spec]) {
            $validIdsQb = (clone $validIdsBaseQb);

            if ($spec) {
                $validIdsQb->andWhere($spec);
            }

            $fn($validIdsQb, $value);
        }
    }

    private function isSafeToExecute(QueryBuilder $deleteQb, ?string $objectName = null, array $data = [], string $selectIdField = 'id'): bool
    {
        if ($deleteQb->getQueryPart('from')['alias']) {
            $selectIdField = "{$deleteQb->getQueryPart('from')['alias']}.{$selectIdField}";
        }

        $selectQb = sqlQueryBuilder()
            ->select($selectIdField)
            ->from($deleteQb->getQueryPart('from')['table'], $deleteQb->getQueryPart('from')['alias'])
            ->setParameters($deleteQb->getParameters(), $deleteQb->getParameterTypes());

        if ($deleteQb->getQueryPart('where')) {
            $selectQb->where($deleteQb->getQueryPart('where'));
        }

        if ($deleteQb->getQueryPart('join')) {
            foreach ($deleteQb->getQueryPart('join') as $alias => $joins) {
                foreach ($joins as $join) {
                    $selectQb->add('join', [$alias => $join], true);
                }
            }
        }

        $count = $selectQb->execute()->rowCount();

        // zbytecny spoustet pokud neni co smazat
        if ($count <= 0) {
            return false;
        }

        // data pro activity log
        $activityLogData = [
            ...['entity' => $objectName, 'deleteCount' => $count],
            ...$data,
        ];

        // vygeneruju delete report, ktery obsahuje IDcka zaznamu, ktere chceme smaznout
        if ($this->isDebugMode() && ($report = $this->generateDebugReport($selectQb, $objectName, $data))) {
            $activityLogData['delete_report'] = Contexts::get(DomainContext::class)->getActiveWithScheme().'/'.$report;
        }

        $prefix = '';
        if ($data['id_language'] ?? false) {
            $prefix = "[{$data['id_language']}] ";
        }

        // pokud se toho chce smazat vic jak N, tak to nedovolim a lognu do Activity logu jako chybu
        if ($count > ($safeLimit = $this->getSafeLimitByObjectName($objectName))) {
            $this->logger->activity(
                "{$prefix}PlatneEntity: U objektu \"{$objectName}\" nebylo spuštěné smazání neplatných objektů, protože jich bylo vybráno více jak {$safeLimit} ke smazání: {$count}",
                $activityLogData,
            );

            return false;
        }

        $this->logger->activity(
            "{$prefix}PlatneEntity: U objektu \"{$objectName}\" bylo vybráno \"{$count}\" objektů ke smazání",
            $activityLogData,
            ActivityLog::SEVERITY_SUCCESS
        );

        // pokud je to debug mod, tak vracim vzdy false
        if ($this->isDebugMode()) {
            return false;
        }

        return true;
    }

    private function generateDebugReport(QueryBuilder $invalidIdsQb, ?string $objectName = null, array $data = []): ?string
    {
        if (!($table = $invalidIdsQb->getQueryPart('from')[0]['table'] ?? null)) {
            return null;
        }

        switch ($table) {
            case 'products':
                $objectName = "{$objectName}_products";
                $dataQb = sqlQueryBuilder()
                    ->select('zp.id_znz, p.id, p.code, p.title')
                    ->from('products', 'p')
                    ->leftJoin('p', 'znz_products', 'zp', 'zp.id_product = p.id AND zp.id_variation IS NULL')
                    ->where(Operator::inSubQuery('p.id', $invalidIdsQb));
                break;
            case 'products_variations':
                $objectName = "{$objectName}_variations";
                $dataQb = sqlQueryBuilder()
                    ->select('zp.id_znz, pv.id, pv.code, p.title as product_title, pv.title as title')
                    ->from('products_variations', 'pv')
                    ->join('pv', 'products', 'p', 'p.id = pv.id_product')
                    ->leftJoin('pv', 'znz_products', 'zp', 'zp.id_variation = pv.id')
                    ->where(Operator::inSubQuery('pv.id', $invalidIdsQb));
                break;
            case 'znz_products_website':
                $dataQb = sqlQueryBuilder()
                    ->select('zpw.id_znz, zpw.id_website')
                    ->from('znz_products_website', 'zpw')
                    ->where(Operator::inSubQuery('(zpw.id_znz, zpw.id_website)', $invalidIdsQb->addSelect('id_website')));
                break;
            case 'photos':
                $dataQb = sqlQueryBuilder()
                    ->select('zph.id_znz, ph.id')
                    ->from('photos', 'ph')
                    ->join('ph', 'znz_photos', 'zph', 'ph.id = zph.id_photo')
                    ->where(Operator::inSubQuery('ph.id', $invalidIdsQb));
                break;
            case 'users_groups':
                $dataQb = sqlQueryBuilder()
                    ->select('zug.id_znz, ug.id, ug.name')
                    ->from('users_groups', 'ug')
                    ->join('ug', 'znz_users_groups', 'zug', 'ug.id = zug.id_users_group')
                    ->where(Operator::inSubQuery('ug.id', $invalidIdsQb));
                break;
            case 'parameters_list':
                $dataQb = sqlQueryBuilder()
                    ->select('zpl.id_znz, pl.id, pl.value')
                    ->from('parameters_list', 'pl')
                    ->join('pl', 'znz_parameters_values', 'zpl', 'zpl.id_parameter = pl.id_parameter AND zpl.id_parameters_value = pl.id')
                    ->where(Operator::inSubQuery('(pl.id, pl.id_parameter)', $invalidIdsQb->addSelect('id_parameter')));
                break;
            case 'parameters_list_translations':
                $objectName = "{$objectName}_{$data['id_language']}";
                $dataQb = sqlQueryBuilder()
                    ->select('zpl.id_znz, plt.id_parameters_list, plt.value, plt.id_language')
                    ->from('parameters_list_translations', 'plt')
                    ->join('plt', 'znz_parameters_values', 'zpl', 'zpl.id_parameters_value = plt.id_parameters_list')
                    ->where(Operator::inSubQuery('plt.id', $invalidIdsQb));

                if (!empty($data['id_language'])) {
                    $dataQb->andWhere(Operator::equals(['plt.id_language' => $data['id_language']]));
                }
                break;
            case 'parameters_products':
                $dataQb = sqlQueryBuilder()
                    ->select('zp.id_znz as id_znz_product, zpar.id_znz as id_znz_parameter, zpv.id_znz as id_znz_parameter_value, pl.value as parameter_value')
                    ->from('parameters_products', 'pp')
                    ->join('pp', 'znz_products', 'zp', 'zp.id_product = pp.id_product AND zp.id_variation IS NULL')
                    ->join('pp', 'znz_parameters', 'zpar', 'zpar.id_parameter = pp.id_parameter')
                    ->join('pp', 'parameters_list', 'pl', 'pl.id_parameter AND pp.id_parameter AND pl.id = pp.value_list')
                    ->join('pp', 'znz_parameters_values', 'zpv', 'zpv.id_parameter = pp.id_parameter AND zpv.id_parameters_value = pp.value_list')
                    ->where(Operator::inSubQuery('pp.id', $invalidIdsQb));
                break;
            default:
                return null;
        }

        $filepath = $this->pathFinder->tmpPath('znz/delete_report_'.$objectName.'_'.time().'.csv');
        if (!file_exists($this->pathFinder->tmpPath('znz'))) {
            mkdir($this->pathFinder->tmpPath('znz'));
        }

        $fp = fopen($filepath, 'w');
        foreach ($dataQb->execute() as $key => $item) {
            if ($key === 0) {
                fputcsv($fp, array_keys($item));
            }

            fputcsv($fp, $item);
        }

        return $filepath;
    }

    private function getSafeLimitByObjectName(?string $objectName): int
    {
        return match ($objectName) {
            // vazby produkt <-> parametr
            'ProduktAtributy2' => 100000,
            // hodnoty parametru
            'ProduktAtributOptions' => 10000,
            // fotky produktu
            'ProduktObrazek' => 5000,
            // skupiny zakazniku
            'ZakaznickaSkupina' => 10,
            // pro ostatni
            default => 2000,
        };
    }

    private function getLanguageByLocale(string $locale): ?string
    {
        $languageContext = $this->getLanguageContext();

        // find language
        $language = null;
        foreach ($languageContext->getAll() as $lang) {
            if ($lang->getLocale() === $locale) {
                $language = $lang->getId();
            }
        }

        return $language;
    }

    private function getLanguageContext(): LanguageContext
    {
        static $languageContext;

        return $languageContext ??= Contexts::get(LanguageContext::class);
    }

    private function isAllowed(): bool
    {
        return true;
    }

    private function isDebugMode(): bool
    {
        return static::$isDebugMode;
    }
}