<?php
declare(strict_types=1);

/**
 * This source file is available under the terms of the
 * Pimcore Open Core License (POCL)
 * Full copyright and license information is available in
 * LICENSE.md which is distributed with this source code.
 *
 *  @copyright  Copyright (c) Pimcore GmbH (https://www.pimcore.com)
 *  @license    Pimcore Open Core License (POCL)
 */

namespace Pimcore\Bundle\AdminBundle\Helper;

use Doctrine\DBAL\Query\QueryBuilder as DoctrineQueryBuilder;
use League\Flysystem\FilesystemException;
use League\Flysystem\FilesystemOperator;
use PhpOffice\PhpSpreadsheet\Reader\Csv;
use PhpOffice\PhpSpreadsheet\Writer\Exception;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Pimcore\Bundle\AdminBundle\Event\AdminEvents;
use Pimcore\Db;
use Pimcore\Logger;
use Pimcore\Model;
use Pimcore\Model\DataObject;
use Pimcore\Model\DataObject\ClassDefinition;
use Pimcore\Model\DataObject\Objectbrick;
use Pimcore\Model\Element\Service;
use Pimcore\Model\User;
use Symfony\Component\EventDispatcher\GenericEvent;
use Symfony\Component\HttpFoundation\BinaryFileResponse;
use Symfony\Component\HttpFoundation\ResponseHeaderBag;
use Symfony\Contracts\EventDispatcher\EventDispatcherInterface;

/**
 * @internal
 */
class GridHelperService
{
    public function __construct(
        private readonly EventDispatcherInterface $eventDispatcher
    ) {

    }

    public function getFeatureAndSlugFilters(string $filterJson, ClassDefinition $class, string $requestedLanguage): array
    {
        $featureJoins = [];
        $slugJoins = [];
        $slugConditions = [];
        $featureConditions = [];

        if ($filterJson) {
            $filters = json_decode($filterJson, true);
            foreach ($filters as $filter) {
                $operator = '=';

                $filterField = $filter['property'];
                $filterOperator = $filter['operator'];

                if ($filter['type'] == 'string') {
                    $operator = 'LIKE';
                } elseif ($filter['type'] == 'numeric' || $filter['type'] == 'quantityValue') {
                    if ($filterOperator == 'lt') {
                        $operator = '<';
                    } elseif ($filterOperator == 'gt') {
                        $operator = '>';
                    } elseif ($filterOperator == 'eq') {
                        $operator = '=';
                    }
                } elseif ($filter['type'] == 'date') {
                    if ($filterOperator == 'lt') {
                        $operator = '<';
                    } elseif ($filterOperator == 'gt') {
                        $operator = '>';
                    } elseif ($filterOperator == 'eq') {
                        $operator = '=';
                    }
                    $filter['value'] = strtotime($filter['value']);
                } elseif ($filter['type'] == 'list') {
                    $operator = '=';
                } elseif ($filter['type'] == 'boolean') {
                    $operator = '=';
                    $filter['value'] = (int)$filter['value'];
                }

                $keyParts = explode('~', $filterField);

                $slugFd = null;
                $field = null;
                $slugKey = null;
                $mappedKey = null;

                if (substr($filterField, 0, 1) == '~') {
                    $type = $keyParts[1];
                    if ($type != 'classificationstore') {
                        continue;
                    }

                    $fieldName = $keyParts[2];
                    $groupKeyId = explode('-', $keyParts[3]);

                    /** @var Model\DataObject\ClassDefinition\Data\Classificationstore $csFieldDefinition */
                    $csFieldDefinition = $class->getFieldDefinition($fieldName);

                    $language = $requestedLanguage;
                    if (!$csFieldDefinition->isLocalized()) {
                        $language = 'default';
                    }

                    $groupId = (int) $groupKeyId[0];
                    $keyid = (int) $groupKeyId[1];

                    $keyConfig = Model\DataObject\Classificationstore\KeyConfig::getById($keyid);
                    $type = $keyConfig->getType();
                    $definition = json_decode($keyConfig->getDefinition(), true);
                    $field = \Pimcore\Model\DataObject\Classificationstore\Service::getFieldDefinitionFromJson($definition, $type);

                    if ($field instanceof Model\DataObject\ClassDefinition\Data) {
                        $featureJoin = [
                            'fieldname' => $fieldName,
                            'groupId' => $groupId,
                            'keyId' => $keyid,
                            'language' => $language,
                        ];

                        // Some fields need a secondary value, e.g. the unit of a QuantityValue
                        if ($field instanceof Model\DataObject\ClassDefinition\Data\QuantityValue) {
                            $featureJoin['secondaryValue'] = $filter['value'][0][1] ?? null;
                        }
                        $featureJoins[] = $featureJoin;

                        $mappedKey = 'cskey_' . $fieldName . '_' . $groupId . '_' . $keyid;
                        if (isset($filter['value'])) {
                            $featureCondition = $field->getFilterConditionExt(
                                $filter['value'],
                                $operator,
                                [
                                    'name' => $mappedKey, ]
                            );

                            if (!empty($featureCondition)) {
                                // if both greater than and less than are used, we need to combine them
                                if (
                                    $field instanceof Model\DataObject\ClassDefinition\Data\QuantityValue &&
                                    isset($featureConditions[$mappedKey])
                                ) {
                                    $featureConditions[$mappedKey] =
                                        '(' . $featureConditions[$mappedKey] . ' AND ' . $featureCondition . ')';
                                } else {
                                    $featureConditions[$mappedKey] = $featureCondition;
                                }
                            }
                        }
                    }
                } elseif (count($keyParts) > 1) {
                    $brickType = $keyParts[0];
                    $brickKey = $keyParts[1];

                    if (strpos($brickType, '?') !== false) {
                        $brickDescriptor = substr($brickType, 1);
                        $brickDescriptor = json_decode($brickDescriptor, true);
                        $brickType = $brickDescriptor['containerKey'];
                    }

                    $brickDef = Objectbrick\Definition::getByKey($brickType);
                    if ($slugFd = $brickDef->getFieldDefinition($brickKey) instanceof ClassDefinition\Data\UrlSlug) {
                        $slugKey = $brickKey;
                        $slugJoins[] = ['fieldname' => $brickKey];
                    }
                } else {
                    if ($slugFd = $class->getFieldDefinition($filterField) instanceof ClassDefinition\Data\UrlSlug) {
                        $slugKey = $filterField;
                        $slugJoins[] = ['fieldname' => $filterField];
                    }
                }

                if ($field && $slugFd) {
                    $slugCondition = $field->getFilterConditionExt(
                        $filter['value'],
                        $operator,
                        [
                            'name' => $slugKey, ]
                    );

                    $slugConditions[$mappedKey] = $slugCondition;
                }
            }
        }

        $result = [
            'featureJoins' => $featureJoins,
            'slugJoins' => $slugJoins,
            'featureConditions' => $featureConditions,
            'slugConditions' => $slugConditions,
        ];

        return $result;
    }

    public function getFilterCondition(string $filterJson, ClassDefinition $class, ?string $tablePrefix = null): string
    {
        $systemFields = Model\DataObject\Service::getSystemFields();

        // create filter condition
        $conditionPartsFilters = [];

        if ($filterJson) {
            $db = Db::get();
            $filters = json_decode($filterJson, true);

            foreach ($filters as $filter) {
                if (isset($filter['value'])) {
                    $operator = '=';

                    $filterField = $filter['property'];
                    $filterOperator = $filter['operator'];

                    if ($filter['type'] == 'string' && preg_match('/^IN\((.*)\)$/i', $filter['value'], $matches)) {
                        $filter['value'] = str_getcsv($matches[1], ',');
                    } elseif ($filter['type'] == 'string') {
                        $operator = 'LIKE';
                    } elseif ($filter['type'] == 'date') {
                        if ($filterOperator == 'lt') {
                            $operator = '<';
                        } elseif ($filterOperator == 'gt') {
                            $operator = '>';
                        } elseif ($filterOperator == 'eq') {
                            $operator = '=';
                        }
                        $filter['value'] = strtotime($filter['value']);
                    } elseif ($filter['type'] == 'list') {
                        $operator = '=';
                    } elseif ($filter['type'] == 'boolean') {
                        $operator = '=';
                        $filter['value'] = (int)$filter['value'];
                    } elseif ($filterOperator == 'in' && is_array($filter['value'])) {
                        $operator = 'in';
                        $matches = preg_split('/[^0-9\.]+/', $filter['value'][0][0] ?? [], -1, PREG_SPLIT_NO_EMPTY);
                        if (is_array($matches) && count($matches) > 0) {
                            $uniqueIds = array_unique(array_map(floatval(...), $matches));
                            if (count($uniqueIds) > 1) {
                                $filter['value'][0][0] = implode(',', $uniqueIds);
                            } else {
                                $filter['value'][0][0] = $uniqueIds[0];
                                $operator = '=';
                            }
                        } else {
                            continue;
                        }
                    } elseif ($filterOperator == 'in' && !is_array($filter['value'])) {
                        $operator = 'in';
                        $matches = preg_split('/[^0-9\.]+/', $filter['value'], -1, PREG_SPLIT_NO_EMPTY);
                        if (is_array($matches) && count($matches) > 0) {
                            $filter['value'] = implode(',', array_unique(array_map(floatval(...), $matches)));
                        } else {
                            continue;
                        }
                    } else {
                        if ($filterOperator == 'lt') {
                            $operator = '<';
                        } elseif ($filterOperator == 'gt') {
                            $operator = '>';
                        } elseif ($filterOperator == 'eq') {
                            $operator = '=';
                        }
                    }

                    $field = $class->getFieldDefinition($filterField);
                    $brickField = null;
                    $brickKey = null;
                    $brickType = null;
                    $brickDescriptor = null;
                    $isLocalized = false;
                    if (!$field) {
                        // if the definition doesn't exist check for a localized field
                        $localized = $class->getFieldDefinition('localizedfields');
                        if ($localized instanceof ClassDefinition\Data\Localizedfields) {
                            $field = $localized->getFieldDefinition($filterField);
                        }

                        //if the definition doesn't exist check for object brick
                        $keyParts = explode('~', $filterField);

                        if (substr($filterField, 0, 1) === '~') {
                            // not needed for now
                            //                            $type = $keyParts[1];
                            //                            $field = $keyParts[2];
                            //                            $keyid = $keyParts[3];
                        } elseif (count($keyParts) > 1) {
                            $brickType = $keyParts[0];
                            $brickKey = $keyParts[1];

                            if (strpos($brickType, '?') !== false) {
                                $brickDescriptor = substr($brickType, 1);
                                $brickDescriptor = json_decode($brickDescriptor, true);
                                $brickType = $brickDescriptor['containerKey'];
                            }

                            $key = Model\DataObject\Service::getFieldForBrickType($class, $brickType);
                            $field = $class->getFieldDefinition($key);

                            $brickClass = Objectbrick\Definition::getByKey($brickType);

                            $brickFieldKey = $brickDescriptor ? $brickDescriptor['brickfield'] : $brickKey;

                            $brickClassDefinitions = $brickClass->getFieldDefinitions();
                            if (array_key_exists($brickFieldKey, $brickClassDefinitions)) {
                                $brickField = $brickClass->getFieldDefinition($brickFieldKey);
                            } else {
                                /** @var ClassDefinition\Data\Localizedfields|null $localizedFields */
                                $localizedFields = $brickClass->getFieldDefinition('localizedfields');
                                if ($localizedFields) {
                                    $brickField = $localizedFields->getFieldDefinition($brickFieldKey);
                                    $isLocalized = true;
                                }
                            }
                        }
                    }
                    if ($field instanceof ClassDefinition\Data\Objectbricks || $brickDescriptor) {
                        // custom field
                        if ($brickDescriptor) {
                            $brickFilterField = $brickDescriptor['fieldname'];
                        } else {
                            $brickFilterField = $field->getName();
                        }

                        $db = Db::get();

                        if ($isLocalized) {
                            $brickPrefix = $db->quoteIdentifier($brickType . '_localized') . '.';
                        } else {
                            if ($brickField instanceof ClassDefinition\Data\UrlSlug) {
                                $brickPrefix = $db->quoteIdentifier($brickKey) . '.';
                            } else {
                                $brickPrefix = $db->quoteIdentifier($brickType) . '.';
                            }
                        }

                        if (is_array($filter['value'])) {
                            $fieldConditions = [];
                            foreach ($filter['value'] as $filterValue) {
                                $brickCondition = '(' . $brickField->getFilterCondition($filterValue, $operator,
                                    ['brickPrefix' => $brickPrefix]
                                ) . ' AND ' . $brickType . '.fieldname = ' . $db->quote($brickFilterField) . ')';
                                $fieldConditions[] = $brickCondition;
                            }

                            if (!empty($fieldConditions)) {
                                $conditionPartsFilters[] = '(' . implode(' OR ', $fieldConditions) . ')';
                            }
                        } else {
                            $brickCondition = '(' . $brickField->getFilterCondition($filter['value'], $operator,
                                ['brickPrefix' => $brickPrefix]) . ' AND ' . $brickType . '.fieldname = ' . $db->quote($brickFilterField) . ')';
                            $conditionPartsFilters[] = $brickCondition;
                        }
                    } elseif ($field instanceof ClassDefinition\Data\UrlSlug) {
                        $conditionPartsFilters[] = $db->quoteIdentifier($field->getName()) . '.' . $field->getFilterCondition($filter['value'], $operator);
                    } elseif ($field instanceof ClassDefinition\Data) {
                        // custom field
                        if (is_array($filter['value'] ?? false)) {
                            $fieldConditions = [];
                            foreach ($filter['value'] as $filterValue) {
                                $fieldConditions[] = $field->getFilterCondition($filterValue, $operator, ['brickPrefix' => ($tablePrefix ? $tablePrefix . '.' : null)]);
                            }

                            if (!empty($fieldConditions)) {
                                $conditionPartsFilters[] = '(' . implode(' OR ', $fieldConditions) . ')';
                            }
                        } else {
                            $conditionPartsFilters[] = $field->getFilterCondition(
                                $filter['value'] ?? null,
                                $operator,
                                [
                                    'brickPrefix' => ($tablePrefix ? $tablePrefix . '.' : null),
                                    'tablePrefix' => ($tablePrefix ? $tablePrefix . '.' : null),
                                ]
                            );
                        }
                    } elseif (in_array($filterField, $systemFields)) {
                        // system fields
                        $filterValue = $filter['value'];
                        if (is_string($filterValue)) {
                            $filterValue = strtolower($filterValue); // lowercase for case-insensitive search
                            $filterValue = str_replace('*', '%', $filterValue); // replace wildcard
                        }
                        if ($filterField == 'fullpath') {
                            $conditionPartsFilters[] = 'concat(lower(`path`), lower(`key`)) ' . $operator . ' ' . $db->quote('%' . $filterValue . '%');
                        } elseif ($filterField == 'key') {
                            $conditionPartsFilters[] = 'lower(`key`) ' . $operator . ' ' . $db->quote('%' . $filterValue . '%');
                        } elseif ($filterField == 'id' && $operator !== 'in') {
                            $conditionPartsFilters[] = 'oo_id ' . $operator . ' ' . $filterValue;
                        } elseif ($filterField == 'id' && $operator === 'in') {
                            $conditionPartsFilters[] = 'oo_id ' . $operator . ' (' . $filterValue . ')';
                        } else {
                            $filterField = $db->quoteIdentifier($filterField);
                            if ($filter['type'] == 'date' && $operator == '=') {
                                //if the equal operator is chosen with the date type, condition has to be changed
                                $maxTime = $filterValue + (86400 - 1); //specifies the top point of the range used in the condition
                                $conditionPartsFilters[] = sprintf(
                                    '%s BETWEEN %s AND %s',
                                    $filterField,
                                    is_string($filterValue) ? $db->quote($filterValue) : $filterValue,
                                    $maxTime
                                );
                            } else {
                                // @see \Pimcore\Model\DataObject\ClassDefinition\Data\Checkbox::getFilterConditionExt()
                                if ($filter['type'] === 'boolean') {
                                    $filterField = 'IFNULL(' . $filterField . ', 0)';
                                }
                                $conditionPartsFilters[] = sprintf(
                                    '%s %s %s',
                                    $filterField,
                                    $operator,
                                    is_string($filterValue) ? $db->quote($filterValue) : $filterValue
                                );
                            }
                        }
                    }
                } elseif ($filter['property'] !== 'fullpath') {
                    $filterProperty = $filter['property'];

                    if (strpos($filterProperty, '~') !== false) {
                        $filterParts = explode('~', $filterProperty);
                        $filterParts = array_map(fn ($part) => $db->quoteIdentifier($part), $filterParts);
                        $filterProperty = implode('.', $filterParts);

                        $conditionPartsFilters[] = '( ' .
                            $filterProperty . ' IS NULL OR ' .
                            $filterProperty . " = '' )";
                    } else {
                        $conditionPartsFilters[] = '( ' .
                            $db->quoteIdentifier($filter['property']) . ' IS NULL OR ' .
                            $db->quoteIdentifier($filter['property']) . " = '' )";
                    }
                }
            }
        }

        $conditionFilters = '1 = 1';
        if (count($conditionPartsFilters) > 0) {
            $conditionFilters = '(' . implode(' AND ', $conditionPartsFilters) . ')';
        }
        Logger::log('DataObjectController filter condition:' . $conditionFilters);

        return $conditionFilters;
    }

    protected function extractBricks(array $fields): array
    {
        $bricks = [];
        if ($fields) {
            foreach ($fields as $f) {
                $fieldName = $f;
                $parts = explode('~', $f);
                if (substr($f, 0, 1) == '~') {
                    // key value, ignore for now
                } elseif (count($parts) > 1) {
                    $brickType = $parts[0];

                    if (strpos($brickType, '?') !== false) {
                        $brickDescriptor = substr($brickType, 1);
                        $brickDescriptor = json_decode($brickDescriptor, true);
                        $brickType = $brickDescriptor['containerKey'];
                    }

                    $bricks[$brickType] = $brickType;
                }
                $newFields[] = $fieldName;
            }
        }

        return $bricks;
    }

    /**
     * Adds all the query stuff that is needed for displaying, filtering and exporting the feature grid data.
     */
    public function addGridFeatureJoins(DataObject\Listing\Concrete $list, array $featureJoins, ClassDefinition $class, array $featureAndSlugFilters): void
    {
        if ($featureJoins) {
            $me = $list;

            $list->onCreateQueryBuilder(function (DoctrineQueryBuilder $select) use (
                $featureJoins,
                $class,
                $featureAndSlugFilters,
                $me
            ) {
                $db = Db::get();

                $alreadyJoined = [];

                foreach ($featureJoins as $featureJoin) {
                    $fieldname = $featureJoin['fieldname'];
                    $mappedKey = 'cskey_' . $fieldname . '_' . $featureJoin['groupId'] . '_' . $featureJoin['keyId'];
                    if (isset($alreadyJoined[$mappedKey])) {
                        continue;
                    }
                    $alreadyJoined[$mappedKey] = 1;

                    if (isset($featureJoin['secondaryValue'])) {
                        $secondValue = ' and ' . $mappedKey . '.value2 = ' . $db->quote($featureJoin['secondaryValue']);
                    }

                    $table = $me->getDao()->getTableName();
                    $select->addSelect($mappedKey . '.value AS ' . $mappedKey);

                    $select->leftJoin(
                        $table,
                        'object_classificationstore_data_' . $class->getId(),
                        $mappedKey,
                        '('
                        . $mappedKey . '.id = ' . $table . '.id'
                        . ' and ' . $mappedKey . '.fieldname = ' . $db->quote($fieldname)
                        . ' and ' . $mappedKey . '.groupId=' . $featureJoin['groupId']
                        . ' and ' . $mappedKey . '.keyId=' . $featureJoin['keyId']
                        . ' and ' . $mappedKey . '.language = ' . $db->quote($featureJoin['language'])
                        . ($secondValue ?? '')
                        . ')'
                    );
                }

                $havings = $featureAndSlugFilters['featureConditions'] ?? null;
                if ($havings) {
                    $havings = implode(' AND ', $havings);
                    $select->having($havings);
                }
            });
        }
    }

    /**
     * Adds all the query stuff that is needed for displaying, filtering and exporting the slug grid data.
     */
    public function addSlugJoins(DataObject\Listing\Concrete $list, array $slugJoins, array $featureAndSlugFilters): void
    {
        if ($slugJoins) {
            $me = $list;

            $list->onCreateQueryBuilder(function (DoctrineQueryBuilder $select) use (
                $slugJoins,
                $featureAndSlugFilters,
                $me
            ) {
                $db = Db::get();

                $alreadyJoined = [];

                foreach ($slugJoins as $slugJoin) {
                    $fieldname = $slugJoin['fieldname'];

                    $mappedKey = $fieldname;
                    $alreadyJoined[$mappedKey] = 1;
                    $table = $me->getDao()->getTableName();

                    $select->addSelect('slug AS ' . $mappedKey);
                    $select->leftJoin(
                        $table,
                        DataObject\Data\UrlSlug::TABLE_NAME,
                        $mappedKey,
                        '('
                        . $mappedKey . '.objectId = ' . $table . '.id'
                        . ' and ' . $mappedKey . '.fieldname = ' . $db->quote($fieldname)
                        . ')'
                    );
                }

                $havings = $featureAndSlugFilters['slugConditions'];
                if ($havings) {
                    $havings = implode(' AND ', $havings);
                    $select->having($havings);
                }
            });
        }
    }

    public function prepareListingForGrid(array $requestParams, string $requestedLanguage, User $adminUser): DataObject\Listing\Concrete
    {
        $folder = Model\DataObject::getById((int) $requestParams['folderId']);
        $class = ClassDefinition::getById((string) $requestParams['classId']);
        $className = $class->getName();

        $listClass = '\\Pimcore\\Model\\DataObject\\' . ucfirst($className) . '\\Listing';
        /** @var DataObject\Listing\Concrete $list */
        $list = new $listClass();

        $colMappings = [
            'key' => 'key',
            'filename' => 'key',
            'id' => 'oo_id',
            'published' => 'published',
            'modificationDate' => 'modificationDate',
            'creationDate' => 'creationDate',
        ];

        $start = 0;
        $limit = 20;
        $orderKey = 'id';
        $order = 'ASC';

        $bricks = [];
        if (!empty($requestParams['fields'])) {
            $fields = $requestParams['fields'];
            $bricks = $this->extractBricks($fields);
        }

        if (isset($requestParams['limit'])) {
            $limit = (int)$requestParams['limit'];
        }
        if (isset($requestParams['start'])) {
            $start = (int)$requestParams['start'];
        }

        $sortingSettings = \Pimcore\Bundle\AdminBundle\Helper\QueryParams::extractSortingSettings($requestParams);
        $doNotQuote = false;

        if ($sortingSettings['order']) {
            $order = $sortingSettings['order'];
        }
        if ($sortingSettings['orderKey'] !== null && strlen($sortingSettings['orderKey']) > 0) {
            $orderKey = $sortingSettings['orderKey'];
            if (substr($orderKey, 0, 1) !== '~') {
                if (array_key_exists($orderKey, $colMappings)) {
                    $orderKey = $colMappings[$orderKey];
                } elseif ($orderKey === 'fullpath') {
                    $orderKey = 'CAST(CONCAT(`path`, `key`) AS CHAR CHARACTER SET utf8) COLLATE utf8_general_ci';
                    $doNotQuote = true;
                } elseif ($class->getFieldDefinition($orderKey) instanceof ClassDefinition\Data\QuantityValue) {
                    $orderKey = 'concat(' . $orderKey . '__unit, ' . $orderKey . '__value)';
                    $doNotQuote = true;
                } elseif ($class->getFieldDefinition($orderKey) instanceof ClassDefinition\Data\RgbaColor) {
                    $orderKey = 'concat(' . $orderKey . '__rgb, ' . $orderKey . '__a)';
                    $doNotQuote = true;
                } elseif (strpos($orderKey, '~') !== false) {
                    $orderKeyParts = explode('~', $orderKey);

                    if (strpos($orderKey, '?') !== false) {
                        $brickDescriptor = substr($orderKeyParts[0], 1);
                        $brickDescriptor = json_decode($brickDescriptor, true);
                        $orderKey = $list->quoteIdentifier($brickDescriptor['containerKey'] . '_localized')
                            . '.' . $list->quoteIdentifier($brickDescriptor['brickfield']);
                        $doNotQuote = true;
                    } elseif (count($orderKeyParts) === 2) {
                        $orderKey = $list->quoteIdentifier($orderKeyParts[0])
                            . '.' . $list->quoteIdentifier($orderKeyParts[1]);
                        $doNotQuote = true;

                        $brickDefinition = Objectbrick\Definition::getByKey($orderKeyParts[0]);
                        if ($brickDefinition instanceof Objectbrick\Definition) {
                            $brickFieldDefinition = $brickDefinition->getFieldDefinition($orderKeyParts[1]);

                            if ($brickFieldDefinition instanceof ClassDefinition\Data\QuantityValue) {
                                $orderKey = 'CONCAT('.$list->quoteIdentifier($orderKeyParts[0]).'.'.$list->quoteIdentifier($orderKeyParts[1].'__unit').', '.$list->quoteIdentifier($orderKeyParts[0]).'.'.$list->quoteIdentifier($orderKeyParts[1].'__value').')';
                            } elseif ($brickFieldDefinition instanceof ClassDefinition\Data\RgbaColor) {
                                $orderKey = 'CONCAT('.$list->quoteIdentifier($orderKeyParts[0]).'.'.$list->quoteIdentifier($orderKeyParts[1].'__rgb').', '.$list->quoteIdentifier($orderKeyParts[0]).'.'.$list->quoteIdentifier($orderKeyParts[1].'__a').')';
                            }
                        }
                    }
                } else {
                    $orderKey = $list->getDao()->getTableName() . '.' . $list->quoteIdentifier($orderKey);
                    $doNotQuote = true;
                }
            }
        }

        $conditionFilters = [];

        if (($requestParams['specificId'] ?? false) && is_numeric($requestParams['specificId'])) {
            $conditionFilters[] = 'oo_id = ' . (int) $requestParams['specificId'];
        }

        if (isset($requestParams['only_direct_children']) && $requestParams['only_direct_children'] === 'true') {
            $conditionFilters[] = 'parentId = ' . $folder->getId();
        } else {
            $quotedPath = $list->quote($folder->getRealFullPath());
            $quotedWildcardPath = $list->quote($list->escapeLike(str_replace('//', '/', $folder->getRealFullPath() . '/')) . '%');
            $conditionFilters[] = '(`path` = ' . $quotedPath . ' OR `path` like ' . $quotedWildcardPath . ')';
        }

        if (!$adminUser->isAdmin()) {
            $conditionFilters[] = $this->getPermittedPathsByUser('object', $adminUser);
        }

        $featureJoins = [];
        $slugJoins = [];
        $featureAndSlugFilters = [];

        // create filter condition
        if (!empty($requestParams['filter'])) {
            $conditionFilters[] = $this->getFilterCondition($requestParams['filter'], $class, $list->getDao()->getTableName());
            $featureAndSlugFilters = $this->getFeatureAndSlugFilters($requestParams['filter'], $class, $requestedLanguage);
            if ($featureAndSlugFilters) {
                $featureJoins = array_merge($featureJoins, $featureAndSlugFilters['featureJoins']);
                $slugJoins = array_merge($slugJoins, $featureAndSlugFilters['slugJoins']);
            }
        }
        if (!empty($requestParams['condition']) && $adminUser->isAdmin()) {
            $conditionFilters[] = '(' . $requestParams['condition'] . ')';
        }

        if (!empty($requestParams['query'])) {
            $query = $this->filterQueryParam($requestParams['query']);
            if (!empty($query)) {
                $handleFullTextQueryEvent = new GenericEvent($this, [
                    'query' => $query,
                    'condition' => null,
                    'list' => $list,
                ]);
                $this->eventDispatcher->dispatch(
                    $handleFullTextQueryEvent,
                    AdminEvents::OBJECT_LIST_HANDLE_FULLTEXT_QUERY
                );

                $condition = $handleFullTextQueryEvent->getArgument('condition');
                if ($condition !== null) {
                    $conditionFilters[] = $condition;
                }
            }
        }

        if (!empty($bricks)) {
            foreach ($bricks as $b) {
                $brickType = $b;
                if (is_array($brickType)) {
                    $brickType = $brickType['containerKey'];
                }
                $list->addObjectbrick($brickType);
            }
        }

        $list->setCondition(implode(' AND ', $conditionFilters));
        if (empty($requestParams['batch']) && empty($requestParams['ids'])) {
            $list->setLimit($limit);
            $list->setOffset($start);
        }

        if (isset($sortingSettings['isFeature']) && $sortingSettings['isFeature']) {
            $orderKey = 'cskey_' . $sortingSettings['fieldname'] . '_' . $sortingSettings['groupId'] . '_' . $sortingSettings['keyId'];
            $list->setOrderKey($orderKey);
            $list->setGroupBy('oo_id');

            $parts = explode('_', $orderKey);

            $fieldname = $parts[1];
            /** @var DataObject\ClassDefinition\Data\Classificationstore $csFieldDefinition */
            $csFieldDefinition = $class->getFieldDefinition($fieldname);
            $sortingSettings['language'] = $csFieldDefinition->isLocalized() ? $requestedLanguage : 'default';
            $featureJoins[] = $sortingSettings;
        } else {
            $list->setOrderKey($orderKey, !$doNotQuote);
        }
        $list->setOrder($order);

        //parameters specified in the objects grid
        if (!empty($requestParams['ids'])) {
            $quotedIds = [];
            foreach ($requestParams['ids'] as $id) {
                $quotedIds[] = $list->quote($id);
            }
            if (!empty($quotedIds)) {
                //add a condition if id numbers are specified
                $list->addConditionParam('oo_id IN (' . implode(',', $quotedIds) . ')');
            }
        }

        if ($class->getAllowVariants()) {
            if ($class->getShowVariants()) {
                $list->setObjectTypes([DataObject::OBJECT_TYPE_OBJECT, DataObject::OBJECT_TYPE_VARIANT]);
            }
            if (isset($requestParams['filter_by_object_type'])) {
                if ($requestParams['filter_by_object_type'] === 'only_objects') {
                    $list->setObjectTypes([DataObject::OBJECT_TYPE_OBJECT]);
                } elseif ($requestParams['filter_by_object_type'] === 'only_variant_objects') {
                    $list->setObjectTypes([DataObject::OBJECT_TYPE_VARIANT]);
                }
            }
        }

        $this->addGridFeatureJoins($list, $featureJoins, $class, $featureAndSlugFilters);
        $this->addSlugJoins($list, $slugJoins, $featureAndSlugFilters);

        $list->setLocale($requestedLanguage);

        if (empty($requestParams['filter']) && empty($requestParams['condition']) && empty($requestParams['sort'])) {
            $list->setIgnoreLocalizedFields(true);
        }

        return $list;
    }

    public function prepareAssetListingForGrid(array $allParams, User $adminUser): Model\Asset\Listing
    {
        $db = Db::get();
        $folder = Model\Asset::getById((int) $allParams['folderId']);

        $start = 0;
        $limit = null;
        $orderKey = 'id';
        $order = 'ASC';

        if (isset($allParams['limit'])) {
            $limit = (int)$allParams['limit'];
        }
        if (isset($allParams['start'])) {
            $start = (int)$allParams['start'];
        }

        $orderKeyQuote = true;
        $sortingSettings = \Pimcore\Bundle\AdminBundle\Helper\QueryParams::extractSortingSettings($allParams);
        if ($sortingSettings['orderKey']) {
            $orderKey = explode('~', $sortingSettings['orderKey'])[0];
            if ($orderKey === 'fullpath') {
                $orderKey = 'CAST(CONCAT(`path`,filename) AS CHAR CHARACTER SET utf8) COLLATE utf8_general_ci';
                $orderKeyQuote = false;
            } elseif ($orderKey === 'filename') {
                $orderKey = 'CAST(filename AS CHAR CHARACTER SET utf8) COLLATE utf8_general_ci';
                $orderKeyQuote = false;
            }

            $order = $sortingSettings['order'];
        }

        $list = new Model\Asset\Listing();

        $conditionFilters = [];
        if (isset($allParams['only_direct_children']) && $allParams['only_direct_children'] == 'true') {
            $conditionFilters[] = 'parentId = ' . $folder->getId();
        } else {
            $conditionFilters[] = 'path LIKE ' . ($folder->getRealFullPath() === '/' ? "'/%'" : $list->quote($list->escapeLike($folder->getRealFullPath()) . '/%'));
        }

        if (isset($allParams['only_unreferenced']) && $allParams['only_unreferenced'] === 'true') {
            $conditionFilters[] = 'id NOT IN (SELECT targetid FROM dependencies WHERE targettype=\'asset\')';
        }

        $conditionFilters[] = "type != 'folder'";
        $filterJson = $allParams['filter'] ?? null;
        if ($filterJson) {
            $filters = json_decode($filterJson, true);
            foreach ($filters as $filter) {
                $operator = '=';
                $notSubselect = '';

                $filterDef = explode('~', $filter['property']);
                $filterField = $filterDef[0];
                $filterOperator = $filter['operator'];
                $filterType = $filter['type'];

                if ($filterType == 'string') {
                    $operator = 'LIKE';
                } elseif ($filterType == 'numeric') {
                    if ($filterOperator == 'lt') {
                        $operator = '<';
                    } elseif ($filterOperator == 'gt') {
                        $operator = '>';
                    } elseif ($filterOperator == 'eq') {
                        $operator = '=';
                    } elseif ($filterOperator == 'in') {
                        $operator = 'IN';

                        $filterValue = $filter['value'] ?? '';
                        if (!is_array($filterValue)) {
                            $matches = preg_split('/[^0-9\.]+/', $filterValue, -1, PREG_SPLIT_NO_EMPTY);
                            if (is_array($matches) && count($matches) > 0) {
                                $filter['value'] = array_unique(array_map(floatval(...), $matches));
                            } else {
                                continue;
                            }
                        }
                    }
                } elseif ($filterType == 'date') {
                    $filter['value'] = strtotime($filter['value']);
                    if ($filterOperator == 'lt') {
                        $operator = '<';
                    } elseif ($filterOperator == 'gt') {
                        $operator = '>';
                    } elseif ($filterOperator == 'eq') {
                        $operator = 'BETWEEN';
                        //if the equal operator is chosen with the date type, condition has to be changed
                        $maxTime = $filter['value'] + (86400 - 1); //specifies the top point of the range used in the condition
                        $filter['value'] =
                            $db->quote((string)$filter['value']) . ' AND ' . $db->quote((string)$maxTime);
                    }
                } elseif ($filterType == 'list') {
                    $operator = 'IN';
                } elseif ($filterType == 'boolean') {
                    $operator = '=';
                    if ((int) $filter['value'] === 0) {
                        $notSubselect = 'NOT';
                    }
                    $filter['value'] = 1;
                }
                // system field
                $value = $filter['value'] ?? '';
                if ($operator == 'LIKE') {
                    $value = $db->quote('%' . $value . '%');
                } elseif ($operator == 'IN') {
                    if (empty($value)) {
                        continue;
                    }

                    $quoted = array_map(function ($val) use ($db) {
                        return $db->quote((string)$val);
                    }, $value);
                    $value = '(' . implode(',', $quoted) . ')';
                } elseif ($operator == 'BETWEEN') {
                } else {
                    $value = $db->quote((string)$value);
                }

                if (isset($filterDef[1]) && $filterDef[1] == 'system') {
                    if ($filterField == 'fullpath') {
                        $filterField = 'CONCAT(`path`,filename)';
                    } else {
                        $filterField = $db->quoteIdentifier($filterField);
                    }
                    $conditionFilters[] = $filterField . ' ' . $operator . ' ' . $value;
                } else {
                    $language = $allParams['language'];
                    if (isset($filterDef[1])) {
                        $language = $filterDef[1];
                    }
                    $language = str_replace(['none', 'default'], '', $language);
                    $conditionFilters[] =
                        'id ' . $notSubselect .
                        ' IN (SELECT cid FROM assets_metadata WHERE `name` = ' . $db->quote($filterField) .
                        ' AND `data` ' . $operator . ' ' . $value .
                        ' AND `language` = ' . $db->quote($language).
                        ')';
                }
            }
        }

        if (!$adminUser->isAdmin()) {
            $conditionFilters[] = $this->getPermittedPathsByUser('asset', $adminUser);
        }

        //filtering for tags
        if (!empty($allParams['tagIds'])) {
            foreach ($allParams['tagIds'] as $tagId) {
                $tagId = (int) $tagId;
                if ($allParams['considerChildTags'] ?? false) {
                    $tag = Model\Element\Tag::getById($tagId);
                    if ($tag) {
                        $tagPath = $tag->getFullIdPath();
                        $conditionFilters[] =
                            'id IN (SELECT cId FROM `tags_assignment` INNER JOIN `tags` ON
                            tags.id = tags_assignment.tagid WHERE `ctype` = "asset"
                            AND (`id` = ' .$tagId. ' OR `idPath` LIKE ' . $db->quote($tagPath . '%') . '))';
                    }
                } else {
                    $conditionFilters[] =
                        'id IN (SELECT cId FROM `tags_assignment` WHERE `ctype` = "asset" AND tagid = ' .$tagId. ')';
                }
            }
        }

        $condition = implode(' AND ', $conditionFilters);

        $list->setCondition($condition);
        $list->setLimit($limit);
        $list->setOffset($start);
        $list->setOrder($order);
        $list->setOrderKey($orderKey, $orderKeyQuote);

        return $list;
    }

    protected function filterQueryParam(string $query): string
    {
        if ($query == '*') {
            $query = '';
        }

        $query = str_replace('%', '*', $query);
        $query = str_replace('@', '#', $query);
        $query = preg_replace("@([^ ])\-@", '$1 ', $query);

        $query = str_replace(['<', '>', '(', ')', '~'], ' ', $query);

        // it is not allowed to have * behind another *
        $query = preg_replace('#[*]+#', '*', $query);

        // no boolean operators at the end of the query
        $query = rtrim($query, '+- ');

        return $query;
    }

    /**
     * @throws FilesystemException
     * @throws Exception|\PhpOffice\PhpSpreadsheet\Reader\Exception
     */
    public function createXlsxExportFile(FilesystemOperator $storage, string $fileHandle, string $csvFile): BinaryFileResponse
    {
        $csvReader = new Csv();
        $csvReader->setDelimiter(';');
        $csvReader->setSheetIndex(0);

        $spreadsheet = $csvReader->loadSpreadsheetFromString($storage->read($csvFile));
        $writer = new Xlsx($spreadsheet);
        $xlsxFilename = PIMCORE_SYSTEM_TEMP_DIRECTORY. '/' .$fileHandle. '.xlsx';
        $writer->save($xlsxFilename);

        $response = new BinaryFileResponse($xlsxFilename);
        $response->headers->set('Content-Type', 'application/xlsx');
        $response->setContentDisposition(ResponseHeaderBag::DISPOSITION_ATTACHMENT, 'export.xlsx');
        $response->deleteFileAfterSend(true);

        $storage->delete($csvFile);

        return $response;
    }

    /**
     * A more performant alternative to "CONCAT(`path`,`key`) LIKE $fullpath"
     */
    private function optimizedConcatLike(string $fullpath, string $type = 'object'): string
    {
        //special case for the root folder
        if ($fullpath === '/') {
            return '`path` LIKE "/%"';
        }

        $pathParts = explode('/', $fullpath);
        $leaf = array_pop($pathParts);
        $path = implode('/', $pathParts);
        $queryColumn = $type === 'asset' ? '`filename`' : '`key`';

        return '(
            (`path` = "' . $path . '/" AND ' . $queryColumn .  ' = "' . $leaf . '")
            OR
            `path` LIKE "' . $fullpath . '/%"
        )';
    }

    /**
     * A more performant alternative to "CONCAT(`path`,`key`) NOT LIKE $fullpath"
     * Set $onlyChildren to true when you want to exclude the folder/element itself
     */
    private function optimizedConcatNotLike(
        string $fullpath,
        bool $onlyChildren = false,
        string $type = 'object'
    ): string {
        $pathParts = explode('/', $fullpath);
        $leaf = array_pop($pathParts);
        $path = implode('/', $pathParts);
        $queryColumn = $type === 'asset' ? '`filename`' : '`key`';

        if ($onlyChildren) {
            return '`path` NOT LIKE "' . $fullpath . '/%"';
        }

        return '(
            NOT (`path` = "' . $path . '/" AND ' . $queryColumn .  ' = "' . $leaf . '")
            AND
            `path` NOT LIKE "' . $fullpath . '/%"
        )';

    }

    /**
     *
     *
     * @internal
     */
    public function getPermittedPathsByUser(string $type, User $user): string
    {
        $allowedTypes = [];

        if ($user->isAllowed($type . 's')) { //the permissions are just plural
            $elementPaths = Service::findForbiddenPaths($type, $user);
            $onlyChildren = false;
            $forbiddenPathSql = [];
            $allowedPathSql = [];
            foreach ($elementPaths['forbidden'] as $forbiddenPath => $allowedPaths) {
                $exceptions = '';
                if ($allowedPaths) {
                    $exceptionsConcat = '';
                    foreach ($allowedPaths as $path) {
                        if ($exceptionsConcat !== '') {
                            $exceptionsConcat.= ' OR ';
                        }
                        $exceptionsConcat.= $this->optimizedConcatLike($path, $type);
                    }
                    $exceptions = ' OR (' . $exceptionsConcat . ')';
                    //if any allowed child is found, the current folder can be listed but its content is still blocked
                    $onlyChildren = true;
                }
                $forbiddenPathSql[] =
                    '(' . $this->optimizedConcatNotLike($forbiddenPath, $onlyChildren, $type) . $exceptions . ')'
                ;
            }
            foreach ($elementPaths['allowed'] as $allowedPaths) {
                $allowedPathSql[] = $this->optimizedConcatLike($allowedPaths, $type);
            }

            // this is to avoid query error when implode is empty.
            // the result would be like `(((path1 OR path2) AND (not_path3 AND not_path4)))`
            $forbiddenAndAllowedSql = '(';

            if (!empty($allowedPathSql) || !empty($forbiddenPathSql)) {
                $forbiddenAndAllowedSql .= '(';
                $forbiddenAndAllowedSql .= $allowedPathSql ? '( ' . implode(' OR ', $allowedPathSql) . ' )' : '';

                if (!empty($forbiddenPathSql)) {
                    //if $allowedPathSql "implosion" is present, we need `AND` in between
                    $forbiddenAndAllowedSql .= $allowedPathSql ? ' AND ' : '';
                    $forbiddenAndAllowedSql .= implode(' AND ', $forbiddenPathSql);
                }
                $forbiddenAndAllowedSql .= ' )';
            }

            $forbiddenAndAllowedSql.= ' )';

            $allowedTypes[] = $forbiddenAndAllowedSql;
        }

        //if allowedTypes is still empty after getting the workspaces, it means that there are no any main permissions set
        // by setting a `false` condition in the query makes sure that nothing would be displayed.
        if (!$allowedTypes) {
            $allowedTypes = ['false'];
        }

        return '('.implode(' OR ', $allowedTypes) .')';
    }
}
