<?php
// Copyright (c) Pickware GmbH. All rights reserved.
// This file is part of software that is released under a proprietary license.
// You must not copy, modify, distribute, make publicly available, or execute
// its contents or parts thereof without express permission by the copyright
// holder, unless otherwise permitted by law.

namespace Shopware\Plugins\ViisonPickwareMobile\Components\PickProfile;

use Shopware\Bundle\AttributeBundle\Service\CrudService as AttributeCrudService;
use Shopware\Bundle\AttributeBundle\Service\TypeMapping;
use Shopware\Plugins\ViisonPickwareMobile\Components\PickingOrderFilter\OrderFilterConditionQueryComponent\AttributeFieldQueryComponentArrayDecodingPreprocessor;
use Shopware\Plugins\ViisonPickwareMobile\Components\PickingOrderFilter\OrderFilterConditionQueryComponent\GroupingConstraintQueryComponentArrayDecodingPreprocessor;
use Shopware\Plugins\ViisonPickwareMobile\Components\PickingOrderFilter\OrderFilterConditionQueryComponent\LikeOperatorQueryComponentArrayDecodingPreprocessor;
use Shopware\Plugins\ViisonPickwareMobile\Components\PickingOrderFilter\OrderFilterConditionQueryComponent\DateOperatorQueryComponentArrayDecodingPreprocessor;
use Shopware\Plugins\ViisonPickwareMobile\Components\PickingOrderFilter\OrderFilterConditionQueryComponent\OrderItemCategoryConditionQueryComponentArrayDecodingPreprocessor;
use Shopware\Plugins\ViisonPickwareMobile\Components\PickingOrderFilter\OrderFilterConditionQueryComponent\OrderItemCountConditionQueryComponentArrayDecodingPreprocessor;
use Shopware\Plugins\ViisonPickwareMobile\Components\QueryComponentArrayCoding\ComparisonQueryComponentArrayCoder;
use Shopware\Plugins\ViisonPickwareMobile\Components\QueryComponentArrayCoding\UnaryOperationQueryComponentArrayCoder;

class PickProfileOrderFilterOptionFactoryService
{
    /**
     * Strings defining different types of values any filter option might have.
     */
    const VALUE_TYPE_BOOLEAN = 'boolean';
    const VALUE_TYPE_DATE = 'date';
    const VALUE_TYPE_NUMBER = 'number';
    const VALUE_TYPE_STORE = 'store';
    const VALUE_TYPE_TEXT = 'text';

    /**
     * @var array[] Array defining the available comparison operators and their supported value types.
     */
    const COMPARISON_OPERATORS = [
        [
            'id' => 'equal',
            'value' => '=',
            'supportedValueTypes' => [
                self::VALUE_TYPE_BOOLEAN,
                self::VALUE_TYPE_NUMBER,
                self::VALUE_TYPE_TEXT,
            ],
        ],
        [
            'id' => 'not_equal',
            'value' => '!=',
            'supportedValueTypes' => [
                self::VALUE_TYPE_NUMBER,
                self::VALUE_TYPE_TEXT,
            ],
        ],
        [
            'id' => 'less_than',
            'value' => '<',
            'supportedValueTypes' => [
                self::VALUE_TYPE_NUMBER,
            ],
        ],
        [
            'id' => 'less_than_equal',
            'value' => '<=',
            'supportedValueTypes' => [
                self::VALUE_TYPE_NUMBER,
            ],
        ],
        [
            'id' => 'greater_than',
            'value' => '>',
            'supportedValueTypes' => [
                self::VALUE_TYPE_NUMBER,
            ],
        ],
        [
            'id' => 'greater_than_equal',
            'value' => '>=',
            'supportedValueTypes' => [
                self::VALUE_TYPE_NUMBER,
            ],
        ],
        [
            'id' => 'one_of',
            'value' => 'IN',
            'supportedValueTypes' => [
                self::VALUE_TYPE_STORE,
            ],
        ],
        [
            'id' => 'not_one_of',
            'value' => 'NOT IN',
            'supportedValueTypes' => [
                self::VALUE_TYPE_STORE,
            ],
        ],
        [
            'id' => 'starts_with',
            'value' => LikeOperatorQueryComponentArrayDecodingPreprocessor::LIKE_OPERATOR_PREFIX,
            'supportedValueTypes' => [
                self::VALUE_TYPE_TEXT,
            ],
        ],
        [
            'id' => 'not_starts_with',
            'value' => LikeOperatorQueryComponentArrayDecodingPreprocessor::NOT_LIKE_OPERATOR_PREFIX,
            'supportedValueTypes' => [
                self::VALUE_TYPE_TEXT,
            ],
        ],
        [
            'id' => 'contains',
            'value' => LikeOperatorQueryComponentArrayDecodingPreprocessor::LIKE_OPERATOR_INFIX,
            'supportedValueTypes' => [
                self::VALUE_TYPE_TEXT,
            ],
        ],
        [
            'id' => 'not_contains',
            'value' => LikeOperatorQueryComponentArrayDecodingPreprocessor::NOT_LIKE_OPERATOR_INFIX,
            'supportedValueTypes' => [
                self::VALUE_TYPE_TEXT,
            ],
        ],
        [
            'id' => 'ends_with',
            'value' => LikeOperatorQueryComponentArrayDecodingPreprocessor::LIKE_OPERATOR_SUFFIX,
            'supportedValueTypes' => [
                self::VALUE_TYPE_TEXT,
            ],
        ],
        [
            'id' => 'not_ends_with',
            'value' => LikeOperatorQueryComponentArrayDecodingPreprocessor::NOT_LIKE_OPERATOR_SUFFIX,
            'supportedValueTypes' => [
                self::VALUE_TYPE_TEXT,
            ],
        ],
        [
            'id' => DateOperatorQueryComponentArrayDecodingPreprocessor::WAS_DAYS_AGO,
            'value' => DateOperatorQueryComponentArrayDecodingPreprocessor::WAS_DAYS_AGO,
            'supportedValueTypes' => [
                self::VALUE_TYPE_DATE,
            ],
        ],
        [
            'id' => DateOperatorQueryComponentArrayDecodingPreprocessor::WAS_MIN_DAYS_AGO,
            'value' => DateOperatorQueryComponentArrayDecodingPreprocessor::WAS_MIN_DAYS_AGO,
            'supportedValueTypes' => [
                self::VALUE_TYPE_DATE,
            ],
        ],
        [
            'id' => DateOperatorQueryComponentArrayDecodingPreprocessor::WAS_MAX_DAYS_AGO,
            'value' => DateOperatorQueryComponentArrayDecodingPreprocessor::WAS_MAX_DAYS_AGO,
            'supportedValueTypes' => [
                self::VALUE_TYPE_DATE,
            ],
        ],
        [
            'id' => DateOperatorQueryComponentArrayDecodingPreprocessor::IS_IN_DAYS,
            'value' => DateOperatorQueryComponentArrayDecodingPreprocessor::IS_IN_DAYS,
            'supportedValueTypes' => [
                self::VALUE_TYPE_DATE,
            ],
        ],
        [
            'id' => DateOperatorQueryComponentArrayDecodingPreprocessor::IS_IN_MIN_DAYS,
            'value' => DateOperatorQueryComponentArrayDecodingPreprocessor::IS_IN_MIN_DAYS,
            'supportedValueTypes' => [
                self::VALUE_TYPE_DATE,
            ],
        ],
        [
            'id' => DateOperatorQueryComponentArrayDecodingPreprocessor::IS_IN_MAX_DAYS,
            'value' => DateOperatorQueryComponentArrayDecodingPreprocessor::IS_IN_MAX_DAYS,
            'supportedValueTypes' => [
                self::VALUE_TYPE_DATE,
            ],
        ],
    ];

    /**
     * @var array[] Defining all operators that are not accompanied by a value.
     */
    const UNARY_OPERATORS = [
        [
            'id' => 'is_null',
            'value' => 'IS NULL',
            'supportedValueTypes' => [
                self::VALUE_TYPE_STORE,
                self::VALUE_TYPE_NUMBER,
                self::VALUE_TYPE_BOOLEAN,
                self::VALUE_TYPE_TEXT,
            ],
            'requiresNullability' => true,
        ],
        [
            'id' => 'is_not_null',
            'value' => 'IS NOT NULL',
            'supportedValueTypes' => [
                self::VALUE_TYPE_STORE,
                self::VALUE_TYPE_NUMBER,
                self::VALUE_TYPE_BOOLEAN,
                self::VALUE_TYPE_TEXT,
            ],
            'requiresNullability' => true,
        ],
        [
            'id' => 'is_empty',
            'value' => 'is_empty',
            'supportedValueTypes' => [
                self::VALUE_TYPE_TEXT,
            ],
            'requiresNullability' => false,
        ],
        [
            'id' => 'is_not_empty',
            'value' => 'is_not_empty',
            'supportedValueTypes' => [
                self::VALUE_TYPE_TEXT,
            ],
            'requiresNullability' => false,
        ],
        [
            'id' => DateOperatorQueryComponentArrayDecodingPreprocessor::IS_TODAY,
            'value' => DateOperatorQueryComponentArrayDecodingPreprocessor::IS_TODAY,
            'supportedValueTypes' => [
                self::VALUE_TYPE_DATE,
            ],
            'requiresNullability' => false,
        ],
    ];

    /**
     * Arrays defining different value types as available in the pick profile order filter rule builder.
     */
    const VALUE_TYPE_DEFINITION_DATE = [
        'valueType' => self::VALUE_TYPE_DATE,
    ];
    const VALUE_TYPE_DEFINITION_BOOLEAN = [
        'valueType' => self::VALUE_TYPE_BOOLEAN,
    ];
    const VALUE_TYPE_DEFINITION_NUMBER = [
        'valueType' => self::VALUE_TYPE_NUMBER,
    ];
    const VALUE_TYPE_DEFINITION_STORE_CATEGORY = [
        'valueType' => self::VALUE_TYPE_STORE,
        'store' => [
            'name' => 'Shopware.apps.ViisonPickwareMobilePickProfiles.store.Category',
            'displayField' => 'name',
        ],
    ];
    const VALUE_TYPE_DEFINITION_STORE_COUNTRY = [
        'valueType' => self::VALUE_TYPE_STORE,
        'store' => [
            'name' => 'Shopware.apps.Base.store.Country',
            'displayField' => 'name',
        ],
    ];
    const VALUE_TYPE_DEFINITION_STORE_COUNTRY_STATE = [
        'valueType' => self::VALUE_TYPE_STORE,
        'store' => [
            'name' => 'Shopware.apps.Base.store.CountryState',
            'displayField' => 'name',
        ],
    ];
    const VALUE_TYPE_DEFINITION_STORE_CUSTOMER_GROUP = [
        'valueType' => self::VALUE_TYPE_STORE,
        'store' => [
            'name' => 'Shopware.apps.Base.store.CustomerGroup',
            'displayField' => 'name',
            'valueField' => 'key',
        ],
    ];
    const VALUE_TYPE_DEFINITION_STORE_DISPATCH_METHOD = [
        'valueType' => self::VALUE_TYPE_STORE,
        'store' => [
            'name' => 'Shopware.apps.Base.store.Dispatch',
            'displayField' => 'name',
        ],
    ];
    const VALUE_TYPE_DEFINITION_STORE_PAYMENT_METHOD = [
        'valueType' => self::VALUE_TYPE_STORE,
        'store' => [
            'name' => 'Shopware.apps.Base.store.Payment',
            'displayField' => 'description',
        ],
    ];
    const VALUE_TYPE_DEFINITION_STORE_PAYMENT_STATUS = [
        'valueType' => self::VALUE_TYPE_STORE,
        'store' => [
            'name' => 'Shopware.apps.Base.store.PaymentStatus',
            'displayField' => 'description',
        ],
    ];
    const VALUE_TYPE_DEFINITION_STORE_SHOP = [
        'valueType' => self::VALUE_TYPE_STORE,
        'store' => [
            'name' => 'Shopware.apps.Base.store.ShopLanguage',
            'displayField' => 'name',
        ],
    ];
    const VALUE_TYPE_DEFINITION_TEXT = [
        'valueType' => self::VALUE_TYPE_TEXT,
    ];

    /**
     * @var array The fields and their respective value type definitions of database table `s_order` that should be
     *      available for building order filter rules.
     */
    const FILTER_OPTIONS_ORDER = [
        'cleared' => self::VALUE_TYPE_DEFINITION_STORE_PAYMENT_STATUS,
        'subshopID' => self::VALUE_TYPE_DEFINITION_STORE_SHOP,
        'dispatchID' => self::VALUE_TYPE_DEFINITION_STORE_DISPATCH_METHOD,
        'paymentID' => self::VALUE_TYPE_DEFINITION_STORE_PAYMENT_METHOD,
        'invoice_amount' => self::VALUE_TYPE_DEFINITION_NUMBER,
        'net' => self::VALUE_TYPE_DEFINITION_BOOLEAN,
        'comment' => self::VALUE_TYPE_DEFINITION_TEXT,
        'customercomment' => self::VALUE_TYPE_DEFINITION_TEXT,
        'internalcomment' => self::VALUE_TYPE_DEFINITION_TEXT,
        'ordertime' => self::VALUE_TYPE_DEFINITION_DATE,
        OrderItemCountConditionQueryComponentArrayDecodingPreprocessor::FIELD_NAME_DISTINCT_PRODUCT_COUNT => self::VALUE_TYPE_DEFINITION_NUMBER,
        OrderItemCountConditionQueryComponentArrayDecodingPreprocessor::FIELD_NAME_TOTAL_ITEM_QUANTITY => self::VALUE_TYPE_DEFINITION_NUMBER,
    ];

    /**
     * @var array The fields and their respective value type definitions of database tables `s_order_shippingaddress`
     *      and `s_order_billingaddress` that should be available for building order filter rules.
     */
    const FILTER_OPTIONS_ORDER_ADDRESS = [
        'company' => self::VALUE_TYPE_DEFINITION_TEXT,
        'firstname' => self::VALUE_TYPE_DEFINITION_TEXT,
        'lastname' => self::VALUE_TYPE_DEFINITION_TEXT,
        'street' => self::VALUE_TYPE_DEFINITION_TEXT,
        'zipcode' => self::VALUE_TYPE_DEFINITION_TEXT,
        'city' => self::VALUE_TYPE_DEFINITION_TEXT,
        'countryID' => self::VALUE_TYPE_DEFINITION_STORE_COUNTRY,
        'stateID' => self::VALUE_TYPE_DEFINITION_STORE_COUNTRY_STATE,
        'additional_address_line1' => self::VALUE_TYPE_DEFINITION_TEXT,
        'additional_address_line2' => self::VALUE_TYPE_DEFINITION_TEXT,
    ];

    /**
     * @var array The fields and their respective value type definitions of database table `s_order_details` that should
     *      be available for building order filter rules.
     */
    const FILTER_OPTIONS_ORDER_DETAIL = [
        'articleordernumber' => self::VALUE_TYPE_DEFINITION_TEXT,
        'price' => self::VALUE_TYPE_DEFINITION_NUMBER,
        'quantity' => self::VALUE_TYPE_DEFINITION_NUMBER,
        'name' => self::VALUE_TYPE_DEFINITION_TEXT,
        'ean' => self::VALUE_TYPE_DEFINITION_TEXT,
        'pack_unit' => self::VALUE_TYPE_DEFINITION_TEXT,
    ];

    /**
     * @var array The fields and their respective value type definitions of database table `s_articles_details` that
     *      should be available for building order filter rules.
     */
    const FILTER_OPTIONS_ARTICLE_DETAIL = [
        'suppliernumber' => self::VALUE_TYPE_DEFINITION_TEXT,
        'weight' => self::VALUE_TYPE_DEFINITION_NUMBER,
        'width' => self::VALUE_TYPE_DEFINITION_NUMBER,
        'height' => self::VALUE_TYPE_DEFINITION_NUMBER,
        'length' => self::VALUE_TYPE_DEFINITION_NUMBER,
        'purchaseprice' => self::VALUE_TYPE_DEFINITION_NUMBER,
        OrderItemCategoryConditionQueryComponentArrayDecodingPreprocessor::CATEGORY_ID_FIELD_NAME => self::VALUE_TYPE_DEFINITION_STORE_CATEGORY,
    ];

    /**
     * @var array The fields and their respective value type definitions of database table `s_user` that should be
     *      available for building order filter rules.
     */
    const FILTER_OPTIONS_CUSTOMER = [
        'customergroup' => self::VALUE_TYPE_DEFINITION_STORE_CUSTOMER_GROUP,
    ];

    /**
     * @var array[] All references and their respective configurations that should be available for building order
     *      filter rules.
     */
    const DEFAULT_FILTER_REFERENCE_CONFIGURATIONS = [
        [
            'id' => 's_order',
            'fields' => self::FILTER_OPTIONS_ORDER,
        ],
        [
            'id' => 's_order_details',
            'fields' => self::FILTER_OPTIONS_ORDER_DETAIL,
            'groupingConstraints' => GroupingConstraintQueryComponentArrayDecodingPreprocessor::GROUPING_CONSTRAINTS,
        ],
        [
            'id' => 's_articles_details',
            'fields' => self::FILTER_OPTIONS_ARTICLE_DETAIL,
            'groupingConstraints' => GroupingConstraintQueryComponentArrayDecodingPreprocessor::GROUPING_CONSTRAINTS,
        ],
        [
            'id' => 's_user',
            'fields' => self::FILTER_OPTIONS_CUSTOMER,
        ],
        [
            'id' => 's_order_shippingaddress',
            'fields' => self::FILTER_OPTIONS_ORDER_ADDRESS,
        ],
        [
            'id' => 's_order_billingaddress',
            'fields' => self::FILTER_OPTIONS_ORDER_ADDRESS,
        ],
    ];

    /**
     * @var array An associative array used for mapping attribute types as defined by Shopware's AttributeBundle to our
     *      own type definitions.
     */
    const ATTRIBUTE_COLUMN_TYPE_MAPPINGS = [
        TypeMapping::TYPE_BOOLEAN => self::VALUE_TYPE_DEFINITION_BOOLEAN,
        TypeMapping::TYPE_COMBOBOX => self::VALUE_TYPE_DEFINITION_TEXT,
        TypeMapping::TYPE_FLOAT => self::VALUE_TYPE_DEFINITION_NUMBER,
        TypeMapping::TYPE_HTML => self::VALUE_TYPE_DEFINITION_TEXT,
        TypeMapping::TYPE_INTEGER => self::VALUE_TYPE_DEFINITION_NUMBER,
        TypeMapping::TYPE_MULTI_SELECTION => self::VALUE_TYPE_DEFINITION_TEXT,
        TypeMapping::TYPE_SINGLE_SELECTION => self::VALUE_TYPE_DEFINITION_TEXT,
        TypeMapping::TYPE_STRING => self::VALUE_TYPE_DEFINITION_TEXT,
        TypeMapping::TYPE_TEXT => self::VALUE_TYPE_DEFINITION_TEXT,
        TypeMapping::TYPE_DATETIME => self::VALUE_TYPE_DEFINITION_DATE,
        TypeMapping::TYPE_DATE => self::VALUE_TYPE_DEFINITION_DATE,
    ];

    const ATTRIBUTE_COLUMN_BLACKLIST = [
        's_articles_attributes' => [
            'pickware_not_relevant_for_picking',
            'pickware_stock_initialization_time',
            'pickware_stock_initialized',
            'articleID',
        ],
        's_order_attributes' => [
            'pickware_batch_picking_box_id',
            'pickware_batch_picking_transaction_id',
            'pickware_last_changed',
            'pickware_pos_cash_register_id',
            'pickware_pos_idempotency_key',
            'pickware_pos_original_invoice_reference',
            'pickware_pos_transaction_type',
            // Note: We should remove this from the black list later to be able to get rid of our workaround that we
            // clear that field when deferring an order. However, we first need to add an `is empty` operator, that
            // matches all empty values, including `NULL`.
            'pickware_processing_warehouse_id',
            // We need to blacklist the drop shipping mail dispatch date, because even though it is a `datetime` field
            // in the database, the pluin maps it to a `string` field.
            'viison_drop_shipping_mails_dispatch_date',
        ],
        's_order_details_attributes' => [
            'pickware_canceled_quantity',
            'pickware_picked_quantity',
        ],
    ];

    /**
     * @var AttributeCrudService
     */
    protected $attributeCrudService;

    /**
     * @var \Zend_Db_Adapter_Abstract
     */
    private $db;

    /**
     * @param AttributeCrudService $attributeCrudService
     * @param \Zend_Db_Adapter_Abstract $db
     */
    public function __construct($attributeCrudService, $db)
    {
        $this->attributeCrudService = $attributeCrudService;
        $this->db = $db;
    }

    /**
     * @return array[] An array containing the definitions of all operators available for picking order filter query
     *         components.
     */
    public static function getOperators()
    {
        return array_merge(
            array_map(
                function (array $operator) {
                    $operator['type'] = ComparisonQueryComponentArrayCoder::CODABLE_TYPE;
                    $operator['requiresNullability'] = false;

                    return $operator;
                },
                self::COMPARISON_OPERATORS
            ),
            array_map(
                function (array $operator) {
                    $operator['type'] = UnaryOperationQueryComponentArrayCoder::CODABLE_TYPE;

                    return $operator;
                },
                self::UNARY_OPERATORS
            )
        );
    }

    /**
     * @return array An associative array having table names as keys and the respective filter query component options
     *         as values.
     */
    public function createQueryComponentOptions()
    {
        return array_map(
            function (array $tableConfiguration) {
                $tableConfiguration['fields'] = array_merge(
                    $this->addAttributeFieldNullablility($tableConfiguration['id'], $tableConfiguration['fields']),
                    $this->getAttributeFieldOptions($tableConfiguration['id'])
                );

                return $tableConfiguration;
            },
            self::DEFAULT_FILTER_REFERENCE_CONFIGURATIONS
        );
    }

    /**
     * Returns all attribute table fields incl. their type definitions for the table with the passed `$tableName`,
     * if available.
     *
     * @param string $tableName
     * @return array
     */
    private function getAttributeFieldOptions($tableName)
    {
        // Note: Prior to PHP 7 `isset()` cannot be used with a const array access expression, which is why we need
        // to use `=== null` instead!
        if (AttributeFieldQueryComponentArrayDecodingPreprocessor::ATTRIBUTE_TABLE_NAMES[$tableName] === null) {
            return [];
        }

        $attributeTableName = AttributeFieldQueryComponentArrayDecodingPreprocessor::ATTRIBUTE_TABLE_NAMES[$tableName];
        $attributeColumns = $this->attributeCrudService->getList($attributeTableName);
        $attributeConfigurations = [];
        foreach ($attributeColumns as $column) {
            $columnType = $column->getColumnType();
            // Note: Prior to PHP 7 `isset()` cannot be used with a const array access expression, which is why we need
            // to use `!== null` instead!
            $isValidAttributeColumn = (
                !$column->isIdentifier()
                && !in_array($column->getColumnName(), (self::ATTRIBUTE_COLUMN_BLACKLIST[$attributeTableName] ?: []))
                && self::ATTRIBUTE_COLUMN_TYPE_MAPPINGS[$columnType] !== null
            );
            if ($isValidAttributeColumn) {
                $attributeConfigurations[$column->getColumnName()] = self::ATTRIBUTE_COLUMN_TYPE_MAPPINGS[$columnType];
            }
        }
        ksort($attributeConfigurations, SORT_NATURAL);

        // Add the field nullability before prefixing the column names (array keys). Otherwise it is not possible to
        // match the keys agains the table's field names.
        $attributeConfigurations = $this->addAttributeFieldNullablility($attributeTableName, $attributeConfigurations);
        $attributeConfigurations = array_combine(
            array_map(
                function ($key) {
                    return AttributeFieldQueryComponentArrayDecodingPreprocessor::ATTRIBUTE_FIELD_PREFIX . $key;
                },
                array_keys($attributeConfigurations)
            ),
            $attributeConfigurations
        );

        return $attributeConfigurations;
    }

    /**
     * Adds the nullability to each of the passed `$attributeConfigurations` based on the respective table schema.
     *
     * @param string $tableName
     * @param array $attributeConfigurations
     */
    private function addAttributeFieldNullablility($tableName, array $attributeConfigurations)
    {
        if (count($attributeConfigurations) === 0) {
            return [];
        }

        $tableFields = $this->db->fetchAll(sprintf('DESCRIBE `%s`', $tableName));
        $fieldNullability = [];
        foreach ($tableFields as $field) {
            $fieldNullability[$field['Field']] = $field['Null'] === 'YES';
        }

        foreach ($attributeConfigurations as $fieldName => $configuration) {
            $attributeConfigurations[$fieldName]['isNullable'] = isset($fieldNullability[$fieldName]) && $fieldNullability[$fieldName];
        }

        return $attributeConfigurations;
    }
}
