<?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.

// Require composer dependecies if necessary
if (file_exists(__DIR__ . '/vendor/autoload.php')) {
    require_once(__DIR__ . '/vendor/autoload.php');
}

if (!class_exists('ViisonCommon_Plugin_BootstrapV14')) {
    require_once('ViisonCommon/PluginBootstrapV14.php');
}

include_once(__DIR__ . '/ViisonShippingCommon/ShippingProviderInterfaceLoader.php');

use Shopware\Plugins\ViisonCommon\Classes\Exceptions\InstallationException;
use Shopware\Plugins\ViisonCommon\Classes\Installation\ArticleAttributeElement;
use Shopware\Plugins\ViisonCommon\Classes\Installation\AttributeColumn\AttributeColumnDescription;
use Shopware\Plugins\ViisonCommon\Classes\Installation\AttributeColumn\AttributeColumnInstaller;
use Shopware\Plugins\ViisonCommon\Classes\Installation\AttributeColumn\AttributeColumnUninstaller;
use Shopware\Plugins\ViisonCommon\Classes\Installation\AttributeConfiguration;
use Shopware\Plugins\ViisonCommon\Classes\Installation\BackendSessionLocaleClassMigration;
use Shopware\Plugins\ViisonCommon\Classes\Installation\Menu;
use Shopware\Plugins\ViisonCommon\Classes\Installation\SQLHelper;
use Shopware\Plugins\ViisonCommon\Classes\Subscribers as ViisonCommonSubscribers;
use Shopware\Plugins\ViisonCommon\Classes\Util\Util as ViisonCommonUtil;
use Shopware\Plugins\ViisonDHL\Classes\DHLPortalConfigScraper;
use Shopware\Plugins\ViisonDHL\Classes\PluginInfo;
use Shopware\Plugins\ViisonDHL\Subscribers;
use Shopware\Plugins\ViisonShippingCommon\Classes\Mail\ReturnLabelMailContextHelper;
use Shopware\Plugins\ViisonShippingCommon\Installation\ShippingCommonInstaller;
use Shopware\Plugins\ViisonShippingCommon\Migration\Encryption\RemoveEncryptionMigration;
use Shopware\Plugins\ViisonShippingCommon\Subscriber as ViisonShippingCommonSubscribers;

/*
 * "Declare" some methods that are not required in our bootstrap, but whose existence is checked during Shopware's
 * code review:
 *
 * public function getVersion() {}
 */

final class Shopware_Plugins_Backend_ViisonDHL_Bootstrap extends ViisonCommon_Plugin_BootstrapV14
{
    /**
     * @inheritdoc
     */
    public function __construct($name, $info = null)
    {
        parent::__construct($name, $info);

        // Make sure that SOAP requests do not last longer than the maximum execution time of PHP.
        // The request will time out and throw a SoapFault if the request does not finish in time.
        // This only works since PHP 5.4.33 for SOAP sessions via HTTPS (https://bugs.php.net/bug.php?id=48524)
        // There is no reason to set a default socket timeout if max_execution_time is 0 (meaning there
        // is no limit to the script execution time). If feasible, we set the default socket timeout to
        // max_execution_time - 5. As we need to make sure that some time remains for performing the actual request,
        // we reserve a relative part of the time (80%) for this if max_execution_time is set to a small value,
        // Unfortunately, the fix in PHP was reverted in version 5.4.34, therefore this does not have the
        // desired effect for many of our customers at the moment.
        $max_execution_time = ini_get('max_execution_time');
        if ($max_execution_time > 0) {
            $default_socket_timeout = max($max_execution_time - 5, $max_execution_time * 0.8);
            // phpcs:ignore
            ini_set('default_socket_timeout', $default_socket_timeout);
        }
    }

    /**
     * @inheritdoc
     */
    protected $codeVersion = '4.32.1.55';

    /**
     * @inheritdoc
     */
    protected $minRequiredShopwareVersion = '5.5.0';

    /**
     * @inheritdoc
     */
    public function runUpdate($oldVersion)
    {
        // Check for an old DHL Adapter (ViisonIntraship plugin) and, if available, make sure its version
        // supports the plugin migration
        if (ViisonCommonUtil::isPluginInstalledAndActive('Backend', 'ViisonIntraship')
            && !ViisonCommonUtil::isPluginInstalledAndActive('Backend', 'ViisonIntraship', '3.1.0')
        ) {
            throw new Exception('Das Plugin kann nicht installiert werden. Bitte stellen Sie zunächst sicher, dass das alte DHL Adapter Plugin mindestens in Version 3.1.0 installiert ist, um dessen Daten zu migrieren.');
        }

        if (!class_exists('SOAPClient')) {
            throw new Exception('Dieses Plugin benötigt die PHP-Erweiterung "SOAPClient". Bitte wenden Sie sich an Ihren Hoster bzw. Serveradministrator, um diese zu aktivieren.');
        }

        // Prepare some helpers
        /** @var Zend_Db_Adapter_Abstract $database */
        $database = $this->get('db');
        $modelManager = $this->get('models');
        $container = $this->get('service_container');
        $sqlHelper = new SQLHelper($database);
        $articleAttributeElementInstallationHelper = new ArticleAttributeElement\InstallationHelper($modelManager);
        $attributeConfigurationInstallationHelper = new AttributeConfiguration\InstallationHelper($modelManager);
        $attributeColumnInstaller = new AttributeColumnInstaller($modelManager, $sqlHelper);
        $me = $this;
        $menuInstallationHelper = new Menu\InstallationHelper($modelManager, ViisonCommonUtil::getPlugin($this->getName()), function ($options) use ($me) {
            // See Shopware_Components_Plugin_Bootstrap::createMenuItem for possible 'options' fields
            return $me->createMenuItem($options);
        });
        $shippingCommonInstaller = new ShippingCommonInstaller($modelManager, $sqlHelper, $database, $container);
        $shippingCommonInstaller->installViisonShippingCommon();

        $oldInstallVersion = ViisonCommonUtil::convertBinaryVersionToInstallVersion($oldVersion);

        switch ($oldInstallVersion) {
            case 'install':
                /* Subscribes */

                // Subscribe events
                $this->subscribeEvent(
                    'Enlight_Controller_Front_DispatchLoopStartup',
                    'onStartDispatch',
                    -1
                );

                /* Menu items */

                $customerMenuItem = $this->Menu()->findOneBy([
                    'controller' => 'Customer',
                    'parent' => null,
                ]);
                // Create main DHL menu item (pseudo item without action)
                $dhlMenuItem = $menuInstallationHelper->ensureMenuItemInDatabaseIs([
                    'parent' => $customerMenuItem,
                    'controller' => 'ViisonDHL',
                    'action' => null,
                    'label' => 'DHL (VIISON)',
                    'class' => 'c-sprite-dhl-icon',
                    'active' => true,
                ]);
                // Create menu item for launching the DHL daily closing app
                $menuInstallationHelper->ensureMenuItemInDatabaseIs([
                    'parent' => $dhlMenuItem,
                    'controller' => 'ViisonDHLDailyClosing',
                    'action' => 'Index',
                    'label' => 'Tagesabschluss',
                    'class' => 'sprite-report-paper',
                    'active' => true,
                ]);
                // Create menu item for launching the DHL free form labels app
                $menuInstallationHelper->ensureMenuItemInDatabaseIs([
                    'parent' => $dhlMenuItem,
                    'controller' => 'ViisonDHLFreeFormLabels',
                    'action' => 'Index',
                    'label' => 'Etiketten ohne Bestellung',
                    'class' => 'sprite-report-paper',
                    'active' => true,
                ]);
                // Create menu item for launching the DHL config app
                $menuInstallationHelper->ensureMenuItemInDatabaseIs([
                    'parent' => $dhlMenuItem,
                    'controller' => 'ViisonDHLConfig',
                    'action' => 'Index',
                    'label' => 'Konfiguration',
                    'class' => 'sprite-wrench-screwdriver',
                    'active' => true,
                ]);

                /* Attribute fields */

                $attributeColumnInstaller->addAttributeColumnsIfNotExist([
                    new AttributeColumnDescription(
                        's_articles_attributes',
                        'viison_country_of_origin',
                        'INT(11)'
                    ),
                    new AttributeColumnDescription(
                        's_articles_attributes',
                        'viison_customs_tariff_number',
                        'VARCHAR(255)'
                    ),
                ]);

                /* Attribute configurations */

                // Add element for the customs tariff number (element is converted to attribute configuration
                // automatically when installing in Shopware >= 5.2)
                $articleAttributeElementInstallationHelper->createArticleAttributeElementUnlessNameExists(
                    'string',
                    'viisonCustomsTariffNumber',
                    'Zolltarifnummer',
                    'Hinterlegen Sie hier die Zolltarifnummer, die bei Erstellung von Exportdokumenten verwendet werden soll.',
                    101
                );
                // Add element for the country of origin (element cannot be converted automatically for Shopware
                // >= 5.2, because it represents an entity selection)
                if (class_exists('Shopware\\Models\\Attribute\\Configuration')) {
                    // Shopware >= 5.2
                    $attributeConfigurationInstallationHelper->createAttributeConfigurationUnlessExists(
                        's_articles_attributes',
                        'viison_country_of_origin',
                        'single_selection',
                        'Herkunftsland',
                        'Hinterlegen Sie hier das Herkunftsland des Artikels, das bei Erstellung von Exportdokumenten verwendet werden soll.',
                        101,
                        'Shopware\\Models\\Country\\Country'
                    );
                } else {
                    // Shopware < 5.2
                    $articleAttributeElementInstallationHelper->createArticleAttributeElementUnlessNameExists(
                        'select',
                        'viisonCountryOfOrigin',
                        'Herkunftsland',
                        'Hinterlegen Sie hier das Herkunftsland des Artikels, das bei Erstellung von Exportdokumenten verwendet werden soll.',
                        101,
                        null,
                        false,
                        true,
                        'base.Country'
                    );
                }

                /* Database tables */

                // Create custom table for generated labels
                $database->query(
                    'CREATE TABLE IF NOT EXISTS s_order_viison_dhl (
                        id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
                        orderId INT(11) UNSIGNED,
                        productId INT(11) UNSIGNED,
                        trackingCode VARCHAR(255) NOT NULL,
                        url VARCHAR(1023) NOT NULL,
                        exportDocumentUrl VARCHAR(1023),
                        returnShipment TINYINT(1) NOT NULL DEFAULT 0,
                        customerAddress VARCHAR(1023),
                        weight FLOAT,
                        created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                        PRIMARY KEY (id),
                        KEY orderId (orderId),
                        KEY productId (productId)
                    ) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci'
                );
                // Manually remove the default of the 'created' column, since it is not possible to create a
                // timestamp field without a default and extra 'on update CURRENT_TIMESTAMP'
                $database->query(
                    'ALTER TABLE s_order_viison_dhl
                    ALTER COLUMN created DROP DEFAULT'
                );
                // Create custom table for storing DHL products
                $database->query(
                    'CREATE TABLE IF NOT EXISTS s_viison_dhl_products (
                        id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
                        name VARCHAR(255) NOT NULL,
                        code VARCHAR(3) NOT NULL,
                        process VARCHAR(2),
                        product VARCHAR(255),
                        packageDimensionsRequired TINYINT(1) NOT NULL DEFAULT 0,
                        dateTimeOption VARCHAR(255),
                        premiumOption TINYINT(1) NOT NULL DEFAULT 0,
                        bulkfreightOption TINYINT(1) NOT NULL DEFAULT 0,
                        insuranceAmount INT(11),
                        deliveryTimeframe VARCHAR(255),
                        PRIMARY KEY (id)
                    ) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci'
                );
                // Create custom table for associations between DHL products and dispatch methods
                $database->query(
                    'CREATE TABLE IF NOT EXISTS s_premium_dispatch_viison_dhl (
                        id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
                        productId INT(11) UNSIGNED NOT NULL,
                        dispatchId INT(11) UNSIGNED NOT NULL,
                        exportDocumentRequired TINYINT(1) NOT NULL DEFAULT 0,
                        visualAgeCheck INT(11),
                        personalHandover TINYINT(1) NOT NULL DEFAULT 0,
                        PRIMARY KEY (id),
                        KEY productId (productId),
                        UNIQUE KEY dispatchId (dispatchId)
                    ) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci'
                );
                // Create custom table for storing plugin configurations for each shop
                $database->query(
                    'CREATE TABLE IF NOT EXISTS s_core_shops_viison_dhl (
                        id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
                        shopId INT(11) UNSIGNED NOT NULL,
                        senderName VARCHAR(30),
                        streetName VARCHAR(40),
                        streetNumber VARCHAR(7),
                        zipCode VARCHAR(10),
                        city VARCHAR(30),
                        contactPerson VARCHAR(30),
                        phoneNumber VARCHAR(20),
                        username VARCHAR(255),
                        password VARCHAR(255),
                        email VARCHAR(255),
                        EKP VARCHAR(10),
                        partnerIdEPN VARCHAR(2),
                        partnerIdBPI VARCHAR(2),
                        partnerIdEPI VARCHAR(2),
                        partnerIdKTG VARCHAR(2),
                        partnerIdKWZ VARCHAR(2),
                        defaultShipmentWeightEPN DECIMAL(10,3) UNSIGNED DEFAULT 31.5,
                        defaultShipmentWeightBPI DECIMAL(10,3) UNSIGNED DEFAULT 5.0,
                        defaultShipmentWeightEPI DECIMAL(10,3) UNSIGNED DEFAULT 5.0,
                        defaultShipmentWeightKTG DECIMAL(10,3) UNSIGNED DEFAULT 5.0,
                        defaultShipmentWeightKWZ DECIMAL(10,3) UNSIGNED DEFAULT 5.0,
                        defaultShipmentWeightEXP DECIMAL(10,3) UNSIGNED DEFAULT 5.0,
                        calculateWeight TINYINT(1) DEFAULT 0,
                        fillerSurcharge DECIMAL(10,3) UNSIGNED DEFAULT 5.0,
                        packagingSurcharge DECIMAL(10,3) UNSIGNED DEFAULT 0.5,
                        defaultPackageLength INT(11),
                        defaultPackageWidth INT(11),
                        defaultPackageHeight INT(11),
                        bankAccountOwner VARCHAR(30),
                        bankName VARCHAR(30),
                        bankIBAN VARCHAR(34),
                        sendDispatchNotification TINYINT(1) DEFAULT 0,
                        dispatchNotificationSender VARCHAR(50),
                        sendDeliveryNotification TINYINT(1) DEFAULT 0,
                        exportDocumentTypeDescription VARCHAR(30),
                        exportIncoterm VARCHAR(3),
                        exportDescription VARCHAR(255),
                        exportPlaceOfCommittal VARCHAR(255),
                        onlineRetoureUsername VARCHAR(255),
                        onlineRetourePassword VARCHAR(255),
                        onlineRetourePortalId VARCHAR(255),
                        onlineRetourePortalNameAT VARCHAR(255),
                        onlineRetourePortalNameBE VARCHAR(255),
                        onlineRetourePortalNameBG VARCHAR(255),
                        onlineRetourePortalNameCH VARCHAR(255),
                        onlineRetourePortalNameCY VARCHAR(255),
                        onlineRetourePortalNameCZ VARCHAR(255),
                        onlineRetourePortalNameDE VARCHAR(255),
                        onlineRetourePortalNameDK VARCHAR(255),
                        onlineRetourePortalNameEE VARCHAR(255),
                        onlineRetourePortalNameES VARCHAR(255),
                        onlineRetourePortalNameFI VARCHAR(255),
                        onlineRetourePortalNameFR VARCHAR(255),
                        onlineRetourePortalNameGB VARCHAR(255),
                        onlineRetourePortalNameGR VARCHAR(255),
                        onlineRetourePortalNameHU VARCHAR(255),
                        onlineRetourePortalNameIE VARCHAR(255),
                        onlineRetourePortalNameIT VARCHAR(255),
                        onlineRetourePortalNameLT VARCHAR(255),
                        onlineRetourePortalNameLU VARCHAR(255),
                        onlineRetourePortalNameLV VARCHAR(255),
                        onlineRetourePortalNameNL VARCHAR(255),
                        onlineRetourePortalNamePL VARCHAR(255),
                        onlineRetourePortalNamePT VARCHAR(255),
                        onlineRetourePortalNameRO VARCHAR(255),
                        onlineRetourePortalNameSE VARCHAR(255),
                        onlineRetourePortalNameSK VARCHAR(255),
                        onlineRetourePortalNameSI VARCHAR(255),
                        autoSendReturnLabel TINYINT(1) DEFAULT 0,
                        printOnlyIfCodeable TINYINT(1) DEFAULT 0,
                        expressUsername VARCHAR(255),
                        expressPassword VARCHAR(255),
                        expressAccountNumber VARCHAR(255),
                        encryptionMethod VARCHAR(255),
                        PRIMARY KEY (id),
                        UNIQUE KEY shopId (shopId)
                    ) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci'
                );

                /* Other */

                // Add static DHL products
                $sqlHelper->insertIfNotExists(
                    's_viison_dhl_products',
                    [
                        'name',
                        'code',
                        'process',
                        'product',
                    ],
                    [
                        [
                            'DHL Paket',
                            'EPN',
                            '01',
                            'V01PAK',
                        ],
                        [
                            'DHL Paket International',
                            'BPI',
                            '53',
                            'V53WPAK',
                        ],
                        [
                            'DHL Europaket',
                            'EPI',
                            '54',
                            'V54EPAK',
                        ],
                    ]
                );
                $sqlHelper->insertIfNotExists(
                    's_viison_dhl_products',
                    [
                        'name',
                        'code',
                        'process',
                        'product',
                        'deliveryTimeframe',
                    ],
                    [
                        [
                            'DHL Kurier Taggleich 10-12 Uhr',
                            'KTG',
                            '01',
                            'V06TG',
                            '10001200',
                        ],
                        [
                            'DHL Kurier Taggleich 12-14 Uhr',
                            'KTG',
                            '01',
                            'V06TG',
                            '12001400',
                        ],
                        [
                            'DHL Kurier Taggleich 14-16 Uhr',
                            'KTG',
                            '01',
                            'V06TG',
                            '14001600',
                        ],
                        [
                            'DHL Kurier Taggleich 16-18 Uhr',
                            'KTG',
                            '01',
                            'V06TG',
                            '16001800',
                        ],
                        [
                            'DHL Kurier Taggleich 18-20 Uhr',
                            'KTG',
                            '01',
                            'V06TG',
                            '18002000',
                        ],
                        [
                            'DHL Kurier Taggleich 19-21 Uhr',
                            'KTG',
                            '01',
                            'V06TG',
                            '19002100',
                        ],
                        [
                            'DHL Kurier Wunschzeit 10-12 Uhr',
                            'KWZ',
                            '01',
                            'V06WZ',
                            '10001200',
                        ],
                        [
                            'DHL Kurier Wunschzeit 12-14 Uhr',
                            'KWZ',
                            '01',
                            'V06WZ',
                            '12001400',
                        ],
                        [
                            'DHL Kurier Wunschzeit 14-16 Uhr',
                            'KWZ',
                            '01',
                            'V06WZ',
                            '14001600',
                        ],
                        [
                            'DHL Kurier Wunschzeit 16-18 Uhr',
                            'KWZ',
                            '01',
                            'V06WZ',
                            '16001800',
                        ],
                        [
                            'DHL Kurier Wunschzeit 18-20 Uhr',
                            'KWZ',
                            '01',
                            'V06WZ',
                            '18002000',
                        ],
                        [
                            'DHL Kurier Wunschzeit 19-21 Uhr',
                            'KWZ',
                            '01',
                            'V06WZ',
                            '19002100',
                        ],
                    ],
                    ['deliveryTimeframe']
                );
                $sqlHelper->insertIfNotExists(
                    's_viison_dhl_products',
                    [
                        'name',
                        'code',
                        'process',
                        'product',
                        'insuranceAmount',
                    ],
                    [
                        [
                            'DHL Paket (2.500 € Versicherung)',
                            'EPN',
                            '01',
                            'V01PAK',
                            2500,
                        ],
                        [
                            'DHL Paket (25.000 € Versicherung)',
                            'EPN',
                            '01',
                            'V01PAK',
                            25000,
                        ],
                        [
                            'DHL Paket International (2.500 € Versicherung)',
                            'BPI',
                            '53',
                            'V53WPAK',
                            2500,
                        ],
                        [
                            'DHL Paket International (25.000 € Versicherung)',
                            'BPI',
                            '53',
                            'V53WPAK',
                            25000,
                        ],
                    ]
                );
                $sqlHelper->insertIfNotExists(
                    's_viison_dhl_products',
                    [
                        'name',
                        'code',
                        'process',
                        'product',
                        'bulkfreightOption',
                    ],
                    [
                        [
                            'DHL Paket (Sperrgut)',
                            'EPN',
                            '01',
                            'V01PAK',
                            1,
                        ],
                        [
                            'DHL Paket International (Sperrgut)',
                            'BPI',
                            '53',
                            'V53WPAK',
                            1,
                        ],
                    ]
                );
                $sqlHelper->insertIfNotExists(
                    's_viison_dhl_products',
                    [
                        'name',
                        'code',
                        'process',
                        'product',
                        'premiumOption',
                    ],
                    [
                        'DHL Paket International Premium',
                        'BPI',
                        '53',
                        'V53WPAK',
                        1,
                    ]
                );
                $sqlHelper->insertIfNotExists(
                    's_viison_dhl_products',
                    [
                        'name',
                        'code',
                        'process',
                        'product',
                        'bulkfreightOption',
                        'insuranceAmount',
                    ],
                    [
                        [
                            'DHL Paket (Sperrgut, 2.500 € Versicherung)',
                            'EPN',
                            '01',
                            'V01PAK',
                            1,
                            2500,
                        ],
                        [
                            'DHL Paket (Sperrgut, 25.000 € Versicherung)',
                            'EPN',
                            '01',
                            'V01PAK',
                            1,
                            25000,
                        ],
                        [
                            'DHL Paket International (Sperrgut, 2.500 € Versicherung)',
                            'BPI',
                            '53',
                            'V53WPAK',
                            1,
                            2500,
                        ],
                        [
                            'DHL Paket International (Sperrgut, 25.000 € Versicherung)',
                            'BPI',
                            '53',
                            'V53WPAK',
                            1,
                            25000,
                        ],
                    ]
                );
                $sqlHelper->insertIfNotExists(
                    's_viison_dhl_products',
                    [
                        'name',
                        'code',
                        'process',
                        'product',
                        'premiumOption',
                        'insuranceAmount',
                    ],
                    [
                        [
                            'DHL Paket International Premium (2.500 € Versicherung)',
                            'BPI',
                            '53',
                            'V53WPAK',
                            1,
                            2500,
                        ],
                        [
                            'DHL Paket International Premium (25.000 € Versicherung)',
                            'BPI',
                            '53',
                            'V53WPAK',
                            1,
                            25000,
                        ],
                    ]
                );
                $sqlHelper->insertIfNotExists(
                    's_viison_dhl_products',
                    [
                        'name',
                        'code',
                        'packageDimensionsRequired',
                        'dateTimeOption',
                        'insuranceAmount',
                    ],
                    [
                        [
                            'DHL Express Paket (standard, 2.500 € Versicherung)',
                            'EXP',
                            1,
                            'Express',
                            2500,
                        ],
                        [
                            'DHL Express Paket (standard, 25.000 € Versicherung)',
                            'EXP',
                            1,
                            'Express',
                            25000,
                        ],
                        [
                            'DHL Express Paket (bis 9 Uhr, 2.500 € Versicherung)',
                            'EXP',
                            1,
                            'Express0900',
                            2500,
                        ],
                        [
                            'DHL Express Paket (bis 9 Uhr, 25.000 € Versicherung)',
                            'EXP',
                            1,
                            'Express0900',
                            25000,
                        ],
                        [
                            'DHL Express Paket (bis 10 Uhr, 2.500 € Versicherung)',
                            'EXP',
                            1,
                            'Express1000',
                            2500,
                        ],
                        [
                            'DHL Express Paket (bis 10 Uhr, 25.000 € Versicherung)',
                            'EXP',
                            1,
                            'Express1000',
                            25000,
                        ],
                        [
                            'DHL Express Paket (bis 12 Uhr, 2.500 € Versicherung)',
                            'EXP',
                            1,
                            'Express1200',
                            2500,
                        ],
                        [
                            'DHL Express Paket (bis 12 Uhr, 25.000 € Versicherung)',
                            'EXP',
                            1,
                            'Express1200',
                            25000,
                        ],
                        [
                            'DHL Express Paket (ECONOMY SELECT, international, 2.500 € Versicherung)',
                            'EXP',
                            1,
                            'EconomySelect',
                            2500,
                        ],
                        [
                            'DHL Express Paket (ECONOMY SELECT, international, 25.000 € Versicherung)',
                            'EXP',
                            1,
                            'EconomySelect',
                            25000,
                        ],
                    ]
                );

                // Add DHL configuration for all existing shops
                $database->query(
                    'INSERT IGNORE INTO s_core_shops_viison_dhl (shopId, exportIncoterm, encryptionMethod)
                        SELECT id, \'-\', :encryptionMethod
                        FROM s_core_shops
                        ORDER BY id',
                    [
                        'encryptionMethod' => (extension_loaded('openssl')) ? 'aes-256-cbc' : 'rijndael256',
                    ]
                );

                // Add a new email template for sending return labels
                $database->query(
                    'INSERT INTO s_core_config_mails (name, frommail, fromname, subject, content, ishtml, mailtype, context)
                    VALUES (?, ?, ?, ?, CONCAT_WS(CHAR(10 using utf8), ?, ?, ?, ?, ?, ?, ?, ?, ?), 0, 1, ?)
                    ON DUPLICATE KEY UPDATE
                        name = VALUES(name),
                        frommail = VALUES(frommail),
                        fromname = VALUES(fromname),
                        subject = VALUES(subject),
                        content = VALUES(content),
                        ishtml = VALUES(ishtml),
                        mailtype = VALUES(mailtype),
                        context = VALUES(context)',
                    [
                        'ViisonDHLReturnLabelEmail',
                        '{config name=mail}',
                        '{config name=shopName}',
                        'Rücksendung der Bestellung{if isset($orderNumber)} {$orderNumber}{/if}',
                        'Sehr geehrte{if $userSalutation eq "mr"}r Herr{elseif $userSalutation eq "ms"} Frau{/if} {$userFirstName} {$userLastName},',
                        ' ',
                        'im Anhang dieser E-Mail finden Sie ein Versandetikett von DHL zur Rücksendung ihrer Bestellung{if isset($orderNumber)} mit der Nummer {$orderNumber}{/if}.',
                        'Bitte drucken Sie es aus und nutzen Sie es, um die kostenfreie Rücksendung in Anspruch zu nehmen.',
                        'Bitte trennen Sie zunächst den Einlieferungsbeleg ab und lassen Sie diesen von DHL abstempeln.',
                        'Wir wünschen Ihnen noch einen schönen Tag.',
                        ' ',
                        'Ihr Team von {config name=shopName}.',
                        ' ',
                        'a:4:{s:11:"orderNumber";i:20000;s:14:"userSalutation";s:2:"mr";s:13:"userFirstName";s:3:"Max";s:12:"userLastName";s:10:"Mustermann";}',
                    ]
                );

                // Check whether to migrate data from an old DHL Adapter (ViisonIntraship plugin)
                if (ViisonCommonUtil::isPluginInstalledAndActive('Backend', 'ViisonIntraship')) {
                    $unmappedDispatchMethods = $this->migrateDataFromOldDHLPlugin();
                }

            case '4.0.0':
                // Fix any 'ViisonIntraship' URLs in the existing (migrated) labels
                $database->query(
                    'UPDATE s_order_viison_dhl
                    SET url = IF(ISNULL(url), NULL, REPLACE(url, \'ViisonIntrashipOrder\', \'ViisonDHLOrder\')),
                        exportDocumentUrl = IF(ISNULL(exportDocumentUrl), NULL, REPLACE(exportDocumentUrl, \'ViisonIntrashipOrder\', \'ViisonDHLOrder\'))
                    WHERE url IS NOT NULL
                    OR exportDocumentUrl IS NOT NULL'
                );
            case '4.0.1':
                // Nothing to do here
            case '4.0.2':
                // Nothing to do here
            case '4.0.3':
                // Nothing to do here
            case '4.0.4':
                // Register the viison:dhl:accountinfo CLI command
                $this->subscribeEvent(
                    'Shopware_Console_Add_Command',
                    'onAddConsoleCommand'
                );
            case '4.1.0':
                // Nothing to do here
            case '4.1.1':
                // Create delivery time frame DB Table and add some default values
                $database->query(
                    'CREATE TABLE IF NOT EXISTS s_viison_dhl_delivery_time_frame (
                        id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
                        timeFrame VARCHAR(100),
                        PRIMARY KEY (id)
                    ) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci'
                );
                $sqlHelper->insertIfNotExists(
                    's_viison_dhl_delivery_time_frame',
                    ['timeFrame'],
                    [
                        ['10001200'],
                        ['12001400'],
                        ['14001600'],
                        ['16001800'],
                        ['18002000'],
                        ['19002100'],
                    ]
                );

                // Add 'DHL Paket Taggleich' to product table
                $sqlHelper->insertIfNotExists(
                    's_viison_dhl_products',
                    [
                        'name',
                        'code',
                        'process',
                        'product',
                    ],
                    [
                        [
                            'DHL Paket Taggleich',
                            'ETG',
                            '06',
                            'V06PAK',
                        ],
                    ]
                );

                // Add new partner id for DHL Paket Taggleich products
                if (!$sqlHelper->doesColumnExist('s_core_shops_viison_dhl', 'partnerIdETG')) {
                    $database->query(
                        'ALTER TABLE s_core_shops_viison_dhl
                         ADD partnerIdETG VARCHAR(2) DEFAULT NULL AFTER partnerIdEPN'
                    );
                }

                // Remove obsolete KTG and KWZ products and the respective columns
                $database->query(
                    'DELETE FROM s_viison_dhl_products
                    WHERE code IN (\'KTG\', \'KWZ\')'
                );
                $sqlHelper->dropColumnIfExists('s_core_shops_viison_dhl', 'partnerIdKTG');
                $sqlHelper->dropColumnIfExists('s_core_shops_viison_dhl', 'partnerIdKWZ');
                $sqlHelper->dropColumnIfExists('s_core_shops_viison_dhl', 'defaultShipmentWeightKTG');
                $sqlHelper->dropColumnIfExists('s_core_shops_viison_dhl', 'defaultShipmentWeightKWZ');
                $sqlHelper->dropColumnIfExists('s_viison_dhl_products', 'deliveryTimeFrame');

                /**
                 * Change order of menu items
                 */

                // Create menu item for launching the DHL free form labels app
                $menuInstallationHelper->ensureMenuItemInDatabaseIs([
                    'parent' => $dhlMenuItem,
                    'controller' => 'ViisonDHLFreeFormLabels',
                    'action' => 'Index',
                    'label' => 'Etiketten ohne Bestellung',
                    'class' => 'sprite-report-paper',
                    'active' => true,
                ]);
                // Create menu item for launching the DHL config app
                $menuInstallationHelper->ensureMenuItemInDatabaseIs([
                    'parent' => $dhlMenuItem,
                    'controller' => 'ViisonDHLConfig',
                    'action' => 'Index',
                    'label' => 'Konfiguration',
                    'class' => 'sprite-wrench-screwdriver',
                    'active' => true,
                ]);
                // Create menu item for launching the DHL daily closing app
                $menuInstallationHelper->ensureMenuItemInDatabaseIs([
                    'parent' => $dhlMenuItem,
                    'controller' => 'ViisonDHLDailyClosing',
                    'action' => 'Index',
                    'label' => 'Tagesabschluss',
                    'class' => 'sprite-report-paper',
                    'active' => true,
                ]);

            case '4.2.0':
                // Delete menu items in root
                $menuInstallationHelper->deleteMenuItemFromDatabase([
                    'parent' => null,
                    'label' => 'Tagesabschluss',
                ]);
                $menuInstallationHelper->deleteMenuItemFromDatabase([
                    'parent' => null,
                    'label' => 'Etiketten ohne Bestellung',
                ]);
                $menuInstallationHelper->deleteMenuItemFromDatabase([
                    'parent' => null,
                    'label' => 'Konfiguration',
                ]);

                // Name fix
                $sqlHelper->dropColumnIfExists('s_viison_dhl_products', 'deliveryTimeframe');

            case '4.2.1':
                if (empty($customerMenuItem)) {
                    $customerMenuItem = $this->Menu()->findOneBy([
                        'controller' => 'Customer',
                        'parent' => null,
                    ]);
                }

                if (empty($dhlMenuItem)) {
                    // Create main DHL menu item (pseudo item without action)
                    $dhlMenuItem = $menuInstallationHelper->ensureMenuItemInDatabaseIs([
                        'parent' => $customerMenuItem,
                        'controller' => 'ViisonDHL',
                        'action' => null,
                        'label' => 'DHL (VIISON)',
                        'class' => 'c-sprite-dhl-icon',
                        'active' => true,
                    ]);
                }

                // Delete process for menu items in DHL Nav Bar
                $menuInstallationHelper->deleteMenuItemFromDatabase([
                    'parent' => $dhlMenuItem,
                    'label' => 'Tagesabschluss',
                ]);
                $menuInstallationHelper->deleteMenuItemFromDatabase([
                    'parent' => $dhlMenuItem,
                    'label' => 'Etiketten ohne Bestellung',
                ]);
                $menuInstallationHelper->deleteMenuItemFromDatabase([
                    'parent' => $dhlMenuItem,
                    'label' => 'Konfiguration',
                ]);

                // Remove OLD DHL (Viison) Menu
                $menuInstallationHelper->deleteMenuItemFromDatabase([
                    'parent' => $customerMenuItem,
                    'label' => 'DHL (VIISON)',
                ]);

                /**
                 * Creation process for the items in different order
                 */

                // Create main DHL menu item without VIISON in name
                $dhlMenuItem = $menuInstallationHelper->ensureMenuItemInDatabaseIs([
                    'parent' => $customerMenuItem,
                    'controller' => 'ViisonDHL',
                    'action' => null,
                    'label' => 'DHL',
                    'class' => 'c-sprite-dhl-icon',
                    'active' => true,
                ]);

                // Create menu item for launching the DHL free form labels app
                $menuInstallationHelper->ensureMenuItemInDatabaseIs([
                    'parent' => $dhlMenuItem,
                    'controller' => 'ViisonDHLFreeFormLabels',
                    'action' => 'Index',
                    'label' => 'Etiketten ohne Bestellung',
                    'class' => 'sprite-report-paper',
                    'active' => true,
                ]);
                // Create menu item for launching the DHL config app
                $menuInstallationHelper->ensureMenuItemInDatabaseIs([
                    'parent' => $dhlMenuItem,
                    'controller' => 'ViisonDHLConfig',
                    'action' => 'Index',
                    'label' => 'Konfiguration',
                    'class' => 'sprite-wrench-screwdriver',
                    'active' => true,
                ]);
                // Create menu item for launching the DHL daily closing app
                $menuInstallationHelper->ensureMenuItemInDatabaseIs([
                    'parent' => $dhlMenuItem,
                    'controller' => 'ViisonDHLDailyClosing',
                    'action' => 'Index',
                    'label' => 'Tagesabschluss',
                    'class' => 'sprite-report-paper',
                    'active' => true,
                ]);

            case '4.2.2':
                // Nothing to do here
            case '4.2.3':
                // Nothing to do here
            case '4.2.4':
                // Add default shipment weight for DHL Paket taggleich (ETG)
                if (!$sqlHelper->doesColumnExist('s_core_shops_viison_dhl', 'defaultShipmentWeightETG')) {
                    $database->query(
                        'ALTER TABLE s_core_shops_viison_dhl
                                 ADD defaultShipmentWeightETG DECIMAL(10,3) UNSIGNED DEFAULT 5.0 AFTER defaultShipmentWeightEPN'
                    );
                }
            case '4.2.5':
                // Include support for feature 'Salutation Toggle' from ShippingCommon
                $sqlHelper->addColumnIfNotExists('s_core_shops_viison_dhl', 'isSalutationRequired', 'tinyint(1) not null default 1');
            case '4.3.0':
                // Nothing to do here
            case '4.3.1':
                // Nothing to do here
            case '4.3.2':
                // Nothing to do here
            case '4.3.3':
                // Nothing to do here
            case '4.3.4':
                // Nothing to do here
            case '4.3.5':
                /** Clear corresponding dispatch type assignments for 'OLD' DHL Express products */
                $database->query(
                    'DELETE d FROM s_premium_dispatch_viison_dhl d
                             INNER JOIN s_viison_dhl_products p
                             ON d.productId = p.id
                             WHERE p.code = \'EXP\''
                );

                /** Remove 'OLD' DHL Express products */
                $database->query(
                    'DELETE FROM s_viison_dhl_products
                                WHERE code IN (\'EXP\')'
                );

                /** Add new column to determine if the product is dutiable or not */
                $sqlHelper->addColumnIfNotExists('s_viison_dhl_products', 'isDutiable', 'TINYINT(1) NOT NULL DEFAULT 0');

                /** Add new DHL Express Products */
                $sqlHelper->insertIfNotExists(
                    's_viison_dhl_products',
                    [
                        'name',
                        'code',
                        'product',
                        'packageDimensionsRequired',
                        'isDutiable',
                    ],
                    [
                        [
                            'DHL Domestic Express',
                            'EXP',
                            'N',
                            1,
                            0,
                        ],
                        [
                            'DHL Domestic Express 9:00',
                            'EXP',
                            'I',
                            1,
                            0,
                        ],
                        [
                            'DHL Domestic Express 10:00',
                            'EXP',
                            'O',
                            1,
                            0,
                        ],
                        [
                            'DHL Domestic Express 12:00',
                            'EXP',
                            '1',
                            1,
                            0,
                        ],
                        [
                            'DHL Express Worldwide (EU)',
                            'EXP',
                            'U',
                            1,
                            0,
                        ],
                        [
                            'DHL Express Worldwide (DOC)',
                            'EXP',
                            'D',
                            1,
                            0,
                        ],
                        [
                            'DHL Express Worldwide 9:00 (DOC)',
                            'EXP',
                            'K',
                            1,
                            0,
                        ],
                        [
                            'DHL Express Worldwide 10:30 (DOC)',
                            'EXP',
                            'L',
                            1,
                            0,
                        ],
                        [
                            'DHL Express Worldwide 12:00 (DOC)',
                            'EXP',
                            'T',
                            1,
                            0,
                        ],
                        [
                            'DHL Express Envelope',
                            'EXP',
                            'X',
                            1,
                            0,
                        ],
                        [
                            'DHL Express Worldwide (Non-DOC)',
                            'EXP',
                            'P',
                            1,
                            1,
                        ],
                        [
                            'DHL Express Worldwide 9:00 (Non-DOC)',
                            'EXP',
                            'E',
                            1,
                            1,
                        ],
                        [
                            'DHL Express Worldwide 10:30 (Non-DOC)',
                            'EXP',
                            'M',
                            1,
                            1,
                        ],
                        [
                            'DHL Express Worldwide 12:00 (Non-DOC)',
                            'EXP',
                            'Y',
                            1,
                            1,
                        ],
                        [
                            'DHL Express Economy Select (EU)',
                            'EXP',
                            'W',
                            1,
                            0,
                        ],
                        [
                            'DHL Express Economy Select (Non-EU)',
                            'EXP',
                            'H',
                            1,
                            1,
                        ],
                    ]
                );

                /**
                 * Add column to describe which product is available in which countries
                 *
                 * NOTE: It stores Country ISO codes & it is varchar(50) because maybe in the future
                 *       one product can be used in many countries, at it is nicer to see ISO codes
                 *       in LIKE array structure than countryID's
                 *
                 * EXAMPLE: 'AT', 'DE', ...
                 *
                 * @var SQLHelper
                 */
                $sqlHelper->addColumnIfNotExists(
                    's_viison_dhl_products',
                    'availableInCountriesIso',
                    'VARCHAR(50) default \'DE\''
                );

                /**@var SQLHelper $sqlHelper Insert new AT Products */
                $sqlHelper->insertIfNotExists(
                    's_viison_dhl_products',
                    [
                        'name',
                        'code',
                        'process',
                        'product',
                        'availableInCountriesIso',
                    ],
                    [
                        [
                            'DHL Paket Austria',
                            'APN',
                            '86',
                            'V86PARCEL',
                            'AT',
                        ],
                        [
                            'DHL Paket Connect Austria',
                            'APC',
                            '87',
                            'V87PARCEL',
                            'AT',
                        ],
                        [
                            'DHL Paket International Austria',
                            'ABI',
                            '82',
                            'V82PARCEL',
                            'AT',
                        ],
                    ]
                );

                /**@var SQLHelper $sqlHelper Add column to the config for the country of the sender, Default is Germany */
                $sqlHelper->addColumnIfNotExists(
                    's_core_shops_viison_dhl',
                    'countryiso',
                    'VARCHAR(2) DEFAULT \'DE\' NOT NULL'
                );

                /**
                 * Add AT specific columns inside Configuration
                 */
                // Partner ID's for the new AT Products
                $sqlHelper->addColumnIfNotExistsAfterColumnName(
                    's_core_shops_viison_dhl',
                    'partnerIdAPN',
                    'VARCHAR(2)',
                    'partnerIdEPI'
                );
                $sqlHelper->addColumnIfNotExistsAfterColumnName(
                    's_core_shops_viison_dhl',
                    'partnerIdAPC',
                    'VARCHAR(2)',
                    'partnerIdAPN'
                );
                $sqlHelper->addColumnIfNotExistsAfterColumnName(
                    's_core_shops_viison_dhl',
                    'partnerIdABI',
                    'VARCHAR(2)',
                    'partnerIdAPC'
                );

                // Default shipment weight for AT Products
                $sqlHelper->addColumnIfNotExistsAfterColumnName(
                    's_core_shops_viison_dhl',
                    'defaultShipmentWeightAPN',
                    'DECIMAL(10,3) UNSIGNED DEFAULT 31.5',
                    'defaultShipmentWeightEXP'
                );
                $sqlHelper->addColumnIfNotExistsAfterColumnName(
                    's_core_shops_viison_dhl',
                    'defaultShipmentWeightAPC',
                    'DECIMAL(10,3) UNSIGNED DEFAULT 31.5',
                    'defaultShipmentWeightAPN'
                );
                $sqlHelper->addColumnIfNotExistsAfterColumnName(
                    's_core_shops_viison_dhl',
                    'defaultShipmentWeightABI',
                    'DECIMAL(10,3) UNSIGNED DEFAULT 31.5',
                    'defaultShipmentWeightAPC'
                );

                // Add Name2 from GKP to config
                $sqlHelper->addColumnIfNotExistsAfterColumnName(
                    's_core_shops_viison_dhl',
                    'senderName2',
                    'VARCHAR(30)',
                    'senderName'
                );

                /**
                 * DHL Paket Prio and Connect for DE
                 */
                $sqlHelper->insertIfNotExists(
                    's_viison_dhl_products',
                    [
                        'name',
                        'code',
                        'process',
                        'product',
                        'availableInCountriesIso',
                    ],
                    [
                        [
                            'DHL Paket Connect',
                            'BPC',
                            '55',
                            'V55PAK',
                            'DE',
                        ],
                        [
                            'DHL Paket Prio',
                            'EPO',
                            '01',
                            'V01PRIO',
                            'DE',
                        ],
                    ]
                );

                // Partner ID's for Prio and Connect
                $sqlHelper->addColumnIfNotExistsAfterColumnName(
                    's_core_shops_viison_dhl',
                    'partnerIdEPO',
                    'VARCHAR(2)',
                    'partnerIdEPN'
                );
                $sqlHelper->addColumnIfNotExistsAfterColumnName(
                    's_core_shops_viison_dhl',
                    'partnerIdBPC',
                    'VARCHAR(2)',
                    'partnerIdBPI'
                );

                // Default shipment weight for Prio and Connect
                $sqlHelper->addColumnIfNotExistsAfterColumnName(
                    's_core_shops_viison_dhl',
                    'defaultShipmentWeightEPO',
                    'DECIMAL(10,3) UNSIGNED DEFAULT 31.5',
                    'defaultShipmentWeightEPN'
                );
                $sqlHelper->addColumnIfNotExistsAfterColumnName(
                    's_core_shops_viison_dhl',
                    'defaultShipmentWeightBPC',
                    'DECIMAL(10,3) UNSIGNED DEFAULT 31.5',
                    'defaultShipmentWeightBPI'
                );
            case '4.4.0':
                // Nothing to do here
            case '4.4.1':
                // Nothing to do here
            case '4.4.2':
                // Nothing to do here
            case '4.4.3':
                // Nothing to do here
            case '4.4.4':
                /** Include support for payment means for cash on delivery service */
                $sqlHelper->addColumnIfNotExists(
                    's_core_shops_viison_dhl',
                    'cashOnDeliveryPaymentMeansIds',
                    'VARCHAR(255) DEFAULT \'3\''
                );

                /** Set International shipping to Express products */
                $database->query(
                    'UPDATE s_viison_dhl_products
                     SET availableInCountriesIso = \'INT\'
                     WHERE code = \'EXP\''
                );

                /**
                 * NOTE: This way is faster.
                 *       We are not using the deleteMenuItemFromDatabase method from $this->Menu()
                 *       because the install method inside engine/Shopware/Components/Namespace/installPlugin()
                 *       is using 'flush()' (line 382) where the old Intraship icon is recreated again.
                 */
                $database->query(
                    'DELETE FROM s_core_menu WHERE name = \'DHL (VIISON)\''
                );

            case '4.5.0':
                // Nothing to do here
            case '4.5.1':
                // Nothing to do here
            case '4.5.2':
                // Nothing to do here
            case '4.5.3':
                // Nothing to do here
            case '4.5.4':
                // Nothing to do here
            case '4.6.0':
                // Nothing to do here
            case '4.6.1':
                // Nothing to do here
            case '4.6.2':
                // Nothing to do here
            case '4.6.3':
                // Nothing to do here
            case '4.6.4':
                // Nothing to do here
            case '4.6.5':
                // Nothing to do here
            case '4.6.6':
                // Nothing to do here
            case '4.6.7':
                // Nothing to do here
            case '4.6.8':
                // Nothing to do here
            case '4.6.9':
                // Nothing to do here
            case '4.6.10':
                // Nothing to do here
            case '4.6.11':
                // We need to update the mail context so the mail variables will be visible before the first mail creation
                ReturnLabelMailContextHelper::updateTemplateToDefaultContextValue(
                    'ViisonDHLReturnLabelEmail',
                    $database
                );
            case '4.6.12':
                // Nothing to do here
            case '4.7.0':
                // Nothing to do here
            case '4.7.1':
                // Nothing to do here
            case '4.7.2':
                // Nothing to do here
            case '4.7.3':
                // Nothing to do here
            case '4.7.4':
                $sqlHelper->addIndexIfNotExists(
                    's_order_viison_dhl',
                    $sqlHelper->getConstraintIdentifier('s_order_viison_dhl', 'trackingCode', 'idx'),
                    ['trackingCode']
                );
            case '4.7.5':
                // Nothing to do
            case '4.7.6':
                // Nothing to do
            case '4.7.7':
                // Nothing to do
            case '4.7.8':
                // Nothing to do
            case '4.7.9':
                // Nothing to do
            case '4.7.10':
                $sqlHelper->addColumnIfNotExists(
                    's_viison_dhl_products',
                    'requiresEmail',
                    'TINYINT(1) default 0'
                );
                $sqlHelper->insertOrUpdateUniqueRow(
                    's_viison_dhl_products',
                    [
                        'product' => 'V87PARCEL',
                    ],
                    [
                        'requiresEmail' => 1,
                    ]
                );
                // Since we renamed isCustomerContactDataTransferAllowed into gdprConfiguration into gdprMailConfiguration
                // we need to double check the column names fo prevent idempotency bugs.
                if (!$sqlHelper->doesColumnExist('s_core_shops_viison_dhl', 'gdprConfiguration') &&
                    !$sqlHelper->doesColumnExist('s_core_shops_viison_dhl', 'gdprMailConfiguration')) {
                    $sqlHelper->addColumnIfNotExists(
                        's_core_shops_viison_dhl',
                        'isCustomerContactDataTransferAllowed',
                        'TINYINT(1) DEFAULT 0'
                    );
                }
            case '4.8.0':
                // Nothing to do
            case '4.8.1':
                // Nothing to do
            case '4.8.2':
                // Nothing to do
            case '4.8.3':
                // Nothing to do
            case '4.8.4':
                $sqlHelper->insertOrUpdateUniqueRow(
                    // Due to a bug in the DHL API implementation, there currently can't be created a
                    // 'DHL Paket International Austria' with 'Slowenia' as destionation without giving an email address
                    // of the receiver. So we enforce the email for the whole prduct.
                    's_viison_dhl_products',
                    [
                        'product' => 'V82PARCEL',
                    ],
                    [
                        'requiresEmail' => 1,
                    ]
                );
            case '4.8.5':
                // Nothing to do
            case '4.8.6':
                $sqlHelper->alterColumnIfExists(
                    's_core_shops_viison_dhl',
                    'isCustomerContactDataTransferAllowed',
                    'gdprConfiguration',
                    'VARCHAR(255) default \'never\''
                );
                if ($sqlHelper->doesColumnExist('s_core_shops_viison_dhl', 'gdprConfiguration')) {
                    $database->query('UPDATE s_core_shops_viison_dhl SET gdprConfiguration = \'never\' WHERE gdprConfiguration = \'0\'');
                    $database->query('UPDATE s_core_shops_viison_dhl SET gdprConfiguration = \'customerChoice\' WHERE gdprConfiguration = \'1\'');
                }
            case '4.9.0':
                // Nothing to do
            case '4.9.1':
                $sqlHelper->addColumnIfNotExists('s_core_shops_viison_dhl', 'useTestingWebservice', 'TINYINT(1) NOT NULL DEFAULT 0');
                // Clear username and password for everyone that used the DHL testing credentials. Then they will get
                // an error next time they try to create a label. This will lead them to enter their DHL developer portal credentials
                // in the adapter configuration.
                $result = $database->query(
                    'UPDATE `s_core_shops_viison_dhl`
                    SET `username` = \'\', `password` = \'\', `useTestingWebservice` = 1
                    WHERE `username` = \'2222222222_01\''
                );
                $numberOfUpdatedColumns = $result->rowCount();
                if ($numberOfUpdatedColumns) {
                    $this->addLocalizedGrowlMessage(
                        'bootstrap/viison_dhl/main',
                        'update/hint/enter_testing_credentials'
                    );
                }

                $backendSessionMigration = new BackendSessionLocaleClassMigration(
                    $this->get('models'),
                    $this->get('db'),
                    $this->get('pluginlogger'),
                    $this->get('application')->Container()
                );
                $backendSessionMigration->fixSessions();

                $removeEncryptionMigration = new RemoveEncryptionMigration(
                    $database,
                    $this->getLogger(),
                    $modelManager
                );
                $migrationSuccessful = $removeEncryptionMigration->migrate(
                    's_core_shops_viison_dhl',
                    [
                        'password',
                        'onlineRetourePassword',
                        'expressPassword',
                    ],
                    "8D-sdNlKJS,Adh9q2.edanj2e\0\0\0\0\0\0\0"
                );
                if (!$migrationSuccessful) {
                    // Migration doesn't work just show a hint message
                    $this->addLocalizedGrowlMessage(
                        'bootstrap/viison_dhl/main',
                        'update/hint/password_encryption'
                    );
                }
                // Add configuration field to toggle sending the customers phone number to the shipping provider
                $sqlHelper->addColumnIfNotExists(
                    's_core_shops_viison_dhl',
                    'gdprPhoneConfiguration',
                    'VARCHAR(255) DEFAULT \'never\''
                );
                // In addition rename the old gdpr config field into more specific gdpr-mail field
                if ($sqlHelper->doesColumnExist('s_core_shops_viison_dhl', 'gdprConfiguration')) {
                    $database->query('ALTER TABLE s_core_shops_viison_dhl CHANGE gdprConfiguration gdprMailConfiguration VARCHAR(255) DEFAULT \'never\'');
                }
            case '4.10.0':
                // Nothing to do
            case '4.11.0':
                // Nothing to do
            case '4.11.1':
                // Nothing to do
            case '4.11.2':
                // Nothing to do
            case '4.11.3':
                // Nothing to do
            case '4.11.4':
                // Nothing to do
            case '4.11.5':
                // Nothing to do
            case '4.11.6':
                $sqlHelper->addColumnIfNotExists(
                    's_premium_dispatch_viison_dhl',
                    'incoterm',
                    'VARCHAR(255) NOT NULL DEFAULT \'\''
                );
                $sqlHelper->addColumnIfNotExists(
                    's_premium_dispatch_viison_dhl',
                    'frankatur',
                    'VARCHAR(255) NOT NULL DEFAULT \'\''
                );
            case '4.12.0':
                // Nothing to do
            case '4.12.1':
                $sqlHelper->addColumnIfNotExists(
                    's_premium_dispatch_viison_dhl',
                    'isSaturdayDelivery',
                    'TINYINT(1) NOT NULL DEFAULT 0'
                );
                $sqlHelper->addColumnIfNotExists(
                    's_premium_dispatch_viison_dhl',
                    'isInsured',
                    'TINYINT(1) DEFAULT 0'
                );
            case '4.13.0':
                // Nothing to do
            case '4.14.0':
                // Nothing to do
            case '4.15.0':
                // Nothing to do
            case '4.15.1':
                // Nothing to do
            case '4.16.0':
                // Nothing to do
            case '4.16.1':
                // Nothing to do
            case '4.17.0':
                // Nothing to do
            case '4.17.1':
                // Nothing to do
            case '4.17.2':
                // Nothing to do
            case '4.17.3':
                // Nothing to do
            case '4.17.4':
                // Nothing to do
            case '4.17.5':
                // Nothing to do
            case '4.17.6':
                // Nothing to do
            case '4.17.7':
                $isReturnApiUpdate = $sqlHelper->doesColumnExist('s_core_shops_viison_dhl', 'onlineRetoureUsername');
                $sqlHelper->dropColumnIfExists('s_core_shops_viison_dhl', 'onlineRetoureUsername');
                $sqlHelper->dropColumnIfExists('s_core_shops_viison_dhl', 'onlineRetourePassword');
                $sqlHelper->dropColumnIfExists('s_core_shops_viison_dhl', 'onlineRetourePortalId');
                $sqlHelper->addColumnIfNotExists(
                    's_core_shops_viison_dhl',
                    'onlineRetourePortalNameHR',
                    'VARCHAR(255)'
                );
                if ($oldInstallVersion !== 'install' && $isReturnApiUpdate) {
                    $this->addLocalizedGrowlMessage(
                        'bootstrap/viison_dhl/main',
                        'update/hint/using_return_api'
                    );
                }
                unset($isReturnApiUpdate);
            case '4.18.0':
                // Nothing to do
            case '4.18.1':
                // Nothing to do
            case '4.18.2':
                // Remove obsolete product "Paket Taggleich" which has the Code ETG. Since this is the only use of the
                // Code ETG this also removes all columns with connections to ETG.
                $database->query(
                    'DELETE FROM s_viison_dhl_products
                        WHERE code = \'ETG\''
                );
                $sqlHelper->dropColumnIfExists('s_core_shops_viison_dhl', 'partnerIdETG');
                $sqlHelper->dropColumnIfExists('s_core_shops_viison_dhl', 'defaultShipmentWeightETG');
                $database->query(
                    'DROP TABLE IF EXISTS
                    s_viison_dhl_delivery_time_frame'
                );
                $sqlHelper->addColumnIfNotExists(
                    's_premium_dispatch_viison_dhl',
                    'parcelOutletRouting',
                    'TINYINT(1) DEFAULT 0'
                );
            case '4.19.0':
                // Nothing to do
            case '4.19.1':
                // Insert new Warenpost product
                $sqlHelper->insertIfNotExists(
                    's_viison_dhl_products',
                    [
                        'name',
                        'code',
                        'process',
                        'product',
                        'availableInCountriesIso',
                    ],
                    [
                        [
                            'Warenpost',
                            'WAP', // Since the code field is not used anymore the term WAP is created by us and used for everything that relates to "Warenpost"
                            '62',
                            'V62WP',
                            'DE',
                        ],
                    ]
                );

                // Partner ID for Warenpost
                $sqlHelper->addColumnIfNotExistsAfterColumnName(
                    's_core_shops_viison_dhl',
                    'partnerIdWAP',
                    'VARCHAR(2)',
                    'partnerIdEPI'
                );

                // Default shipment weight for Warenpost
                $sqlHelper->addColumnIfNotExistsAfterColumnName(
                    's_core_shops_viison_dhl',
                    'defaultShipmentWeightWAP',
                    'DECIMAL(10,3) UNSIGNED DEFAULT 1',
                    'defaultShipmentWeightEPI'
                );
            case '4.20.0':
                // Nothing to do
            case '4.20.1':
                $sqlHelper->addColumnIfNotExists(
                    's_core_shops_viison_dhl',
                    'sendersCustomsReference',
                    'VARCHAR(255) NOT NULL DEFAULT \'\''
                );
                $sqlHelper->addColumnIfNotExists(
                    's_core_shops_viison_dhl',
                    'exportTypeOfShipment',
                    'VARCHAR(255)'
                );
                // Set exportTypeOfShipment to COMMERICAL_GOODS for every subshop config with set export configs
                // Because incoterm needs to be set if any export configs are used we can use incoterm to
                // determine if the export part of the config was configured before the plugin update.
                $database->query(
                    'UPDATE s_core_shops_viison_dhl
                        SET exportTypeOfShipment = \'COMMERICAL_GOODS\'
                        WHERE NOT exportIncoterm = \'-\''
                );
            case '4.21.0':
                // Fix the typo in exportTypeOfShipment from above
                $database->query(
                    'UPDATE s_core_shops_viison_dhl
                        SET exportTypeOfShipment = \'COMMERCIAL_GOODS\'
                        WHERE exportTypeOfShipment = \'COMMERICAL_GOODS\''
                );
            case '4.21.1':
                $sqlHelper->addColumnIfNotExists(
                    's_premium_dispatch_viison_dhl',
                    'endorsementType',
                    'VARCHAR(255)'
                );
            case '4.22.0':
                // Remove austria products
                $database->query(
                    'DELETE FROM s_viison_dhl_products
                        WHERE code = \'APN\' OR code = \'APC\' OR code = \'ABI\''
                );
                // Remove partnerId and default shipment weight for austria products
                $sqlHelper->dropColumnIfExists('s_core_shops_viison_dhl', 'partnerIdAPN');
                $sqlHelper->dropColumnIfExists('s_core_shops_viison_dhl', 'defaultShipmentWeightAPN');
                $sqlHelper->dropColumnIfExists('s_core_shops_viison_dhl', 'partnerIdAPC');
                $sqlHelper->dropColumnIfExists('s_core_shops_viison_dhl', 'defaultShipmentWeightAPC');
                $sqlHelper->dropColumnIfExists('s_core_shops_viison_dhl', 'partnerIdABI');
                $sqlHelper->dropColumnIfExists('s_core_shops_viison_dhl', 'defaultShipmentWeightABI');
            case '4.23.0':
                // Nothing to do
            case '4.23.1':
                // Nothing to do
            case '4.24.0':
                // Nothing to do
            case '4.24.1':
                // Nothing to do
            case '4.24.2':
                // Nothing to do
            case '4.24.3':
                // Nothing to do
            case '4.25.0':
                // Nothing to do
            case '4.25.1':
                // Nothing to do
            case '4.25.2':
                // Nothing to do
            case '4.25.3':
                // Nothing to do
            case '4.25.4':
                // Nothing to do
            case '4.25.5':
                // Nothing to do
            case '4.25.6':
                // Insert new Warenpost product
                $sqlHelper->insertIfNotExists(
                    's_viison_dhl_products',
                    [
                        'name',
                        'code',
                        'process',
                        'product',
                        'availableInCountriesIso',
                    ],
                    [
                        [
                            'Warenpost International',
                            'WPI',
                            '66',
                            'V66WPI',
                            'DE',
                        ],
                    ]
                );

                $sqlHelper->insertIfNotExists(
                    's_viison_dhl_products',
                    [
                        'name',
                        'code',
                        'process',
                        'product',
                        'premiumOption',
                    ],
                    [
                        [
                            'Warenpost International Premium',
                            'WPI',
                            '66',
                            'V66WPI',
                            1,
                        ],
                    ]
                );

                // Partner ID for Warenpost
                $sqlHelper->addColumnIfNotExistsAfterColumnName(
                    's_core_shops_viison_dhl',
                    'partnerIdWPI',
                    'VARCHAR(2)',
                    'partnerIdWAP'
                );

                // Default shipment weight for Warenpost
                $sqlHelper->addColumnIfNotExistsAfterColumnName(
                    's_core_shops_viison_dhl',
                    'defaultShipmentWeightWPI',
                    'DECIMAL(10,3) UNSIGNED DEFAULT 1',
                    'defaultShipmentWeightWAP'
                );
            case '4.26.0':
                // Nothing to do
            case '4.27.0':
                // Nothing to do
            case '4.27.1':
                // Nothing to do
            case '4.28.0':
                $sqlHelper->addColumnIfNotExists(
                    's_premium_dispatch_viison_dhl',
                    'identCheckAge',
                    'INT(11)'
                );
            case '4.29.0':
                $sqlHelper->addColumnIfNotExists(
                    's_premium_dispatch_viison_dhl',
                    'postalDeliveryDutyPaid',
                    'TINYINT(1) DEFAULT 0'
                );
                $sqlHelper->addColumnIfNotExists(
                    's_premium_dispatch_viison_dhl',
                    'closestDroppointDelivery',
                    'TINYINT(1) DEFAULT 0'
                );
            case '4.30.0':
                // Nothing to do
            case '4.30.1':
                $sqlHelper->addColumnIfNotExists(
                    's_premium_dispatch_viison_dhl',
                    'signedForByRecipient',
                    'TINYINT(1) DEFAULT 0'
                );
            case '4.31.0':
                $sqlHelper->insertOrUpdateUniqueRow(
                    's_viison_dhl_products',
                    [
                        'product' => 'O',
                    ],
                    [
                        'name' => 'DHL Domestic Express 10:30',
                    ]
                );
            case '4.32.0':
                // Nothing to do
            case '4.32.1':
                // Next release

                // *** *** *** *** ***
                // NEVER REMOVE THE FOLLOWING BREAK! All updates must be added above this comment block!
                // *** *** *** *** ***
                break;
            default:
                throw InstallationException::updateFromVersionNotSupported(
                    $this,
                    $oldInstallVersion,
                    $this->getVersion()
                );
        }

        // Update ViisonCommon snippets
        $this->importViisonCommonSnippetsIntoDb();

        $this->removeObsoletePluginFiles();
        $this->clearCacheIfNecessary();

        if (isset($unmappedDispatchMethods) && count($unmappedDispatchMethods) > 0) {
            $this->addLocalizedGrowlMessage(
                'bootstrap/viison_dhl/main',
                'update/hint/unmapped_dispatch_methods',
                implode('", "', $unmappedDispatchMethods)
            );
        }
    }

    /**
     * @inheritdoc
     */
    public function runUninstall($deleteData)
    {
        if (!$deleteData) {
            // Nothing to do
            return;
        }

        // Prepare some helpers
        $database = $this->get('db');
        $modelManager = $this->get('models');

        $removeEncryptionMigration = new RemoveEncryptionMigration(
            $database,
            $this->getLogger(),
            $modelManager
        );
        $removeEncryptionMigration->uninstall('s_core_shops_viison_dhl');

        $articleAttributeElementUninstallationHelper = new ArticleAttributeElement\UninstallationHelper($modelManager);
        $sqlHelper = new SQLHelper($database);
        $attributeColumnUninstaller = new AttributeColumnUninstaller($modelManager, $sqlHelper);

        // Remove custom database tables
        $database->query(
            'DROP TABLE IF EXISTS
                s_order_viison_dhl,
                s_viison_dhl_products,
                s_premium_dispatch_viison_dhl,
                s_core_shops_viison_dhl,
                s_viison_dhl_delivery_time_frame'
        );

        $container = $this->get('service_container');
        $shippingCommonInstaller = new ShippingCommonInstaller($modelManager, $sqlHelper, $database, $container);
        $shippingCommonInstaller->uninstallViisonShippingCommon();

        $this->clearCacheIfNecessary();
    }

    /**
     * @inheritdoc
     */
    protected function runActivation()
    {
        $this->clearCacheIfNecessary();
    }

    /**
     * @inheritdoc
     */
    protected function runDeactivation()
    {
        $this->clearCacheIfNecessary();
    }

    /**
     * Registers the plugin's namespaces and performs any migrations necessary when calling this method
     * for the first time after upgrading to Shopware 5.2.x.
     */
    public function afterInit()
    {
        $this->loadDependencies();
        $this->loadPlugin();
    }

    /* Events & Hooks */

    /**
     * This callback function is triggered at the very beginning of the dispatch process and allows
     * us to register additional events on the fly.
     */
    public function onStartDispatch(\Enlight_Event_EventArgs $args)
    {
        // Nothing to do here, since the dynamic subscribers were already registered in 'afterInit()''
    }

    /**
     * Add the viison:dhl:accountinfo CLI command.
     *
     * @param Enlight_Event_EventArgs $args
     * @return DHLPortalConfigScraper\GetDHLAccountInformationCommand
     */
    public function onAddConsoleCommand(Enlight_Event_EventArgs $args)
    {
        // Add the composer autoloader for the dependencies of this plugin and of ShippingCommon (both dependencies are in the same vendor directory)
        $autoLoader = __DIR__ . '/vendor/autoload.php';
        // The vendor directory only exists inside the plugin for community store customers, when the whole shop is installed
        // via composer (e.g. as is the case for the Eisenschmidt shop), the dependencies are installed in the shop-level
        // vendor directory and therefore automatically get loaded.
        if (file_exists($autoLoader)) {
            require_once($autoLoader);
        }

        return new DHLPortalConfigScraper\GetDHLAccountInformationCommand();
    }

    /* Install helpers */

    /**
     * Uses the dependency loader to load the namespaces and susbcribers of all required,
     * shared dependencies.
     */
    private function loadDependencies()
    {
        // Require all shared dependencies
        $loader = VIISON\ShopwarePluginDependencyLoader\Loader::getInstance();
        $loader->requireDependencies($this->Path(), [
            'ViisonCommon',
            'ViisonShippingCommon',
        ]);
        Shopware\Plugins\ViisonCommon\Classes\PickwareAutoloadDependencyLoader::ensureDependenciesLoaded();

        // Add the subscribers of all dependencies
        $viisonCommonSubscriberRegistrator = new Shopware\Plugins\ViisonCommon\Classes\SubscriberRegistrator($this);
        $viisonCommonSubscriberRegistrator->registerSubscribers();

        // Load the Shopware polyfill
        require_once __DIR__ . '/ViisonCommon/Polyfill/Loader.php';

        // Make sure this plugin is installed and activated before registering functionality located within ShippingCommon
        if (!$this->isInstalledAndActive()) {
            return;
        }

        $viisonShippingCommonSubscriberRegistrator = new Shopware\Plugins\ViisonShippingCommon\Classes\SubscriberRegistrator($this);
        $viisonShippingCommonSubscriberRegistrator->registerSubscribers();

        // Call the legacy include helper to add backwards compatibility with outdated shipping plugins
        require_once __DIR__ . '/ViisonShippingCommon/legacy/IncludeHelper.php';
    }

    /**
     * First checks whether the plugin has a valid license, is installed and is active.
     * If all this is true, first the namespaces of this plugin are registered with the
     * class loader, before all subscribers are instanciated and added to the event manager.
     */
    private function loadPlugin()
    {
        // Check plugin licence
        if (!$this->checkLicense(false)) {
            return;
        }

        // Make sure this plugin is installed and active before registering the susbcribers
        if (!$this->isInstalledAndActive()) {
            return;
        }

        // Create all plugin subscribers
        $subscribers = [
            new Subscribers\Backend\Article($this),
            new Subscribers\Backend\Index($this),
            new Subscribers\Backend\Order($this),
            new Subscribers\Backend\ViisonShippingCommonOrder($this),
            new Subscribers\CompatibilityCheckSubscriber($this),
            new Subscribers\Controllers($this),
            new Subscribers\Modules\Order($this),
            new Subscribers\Services($this),
            new Subscribers\Components\DocumentSubscriber($this),
            new Subscribers\ShippingProviders($this, 'Shopware\\Plugins\\ViisonDHL\\Classes\\ShippingProvider'),
            new Subscribers\SubApplicationRegistration($this),
            new ViisonCommonSubscribers\ViewLoading($this, 'ViisonDHL'),
            new ViisonShippingCommonSubscribers\ConfigImporterSubscriber($this, new PluginInfo()),
        ];

        // Make sure that the subscribers are only added once
        if (!$this->isSubscriberRegistered($subscribers[0])) {
            $eventManager = $this->get('events');
            foreach ($subscribers as $subscriber) {
                $eventManager->addSubscriber($subscriber);
            }
        }

        // Check for an old DHL Adapter (ViisonIntraship plugin) and, if available, rename it
        $intrashipPlugin = ViisonCommonUtil::getPlugin('ViisonIntraship');
        if ($intrashipPlugin) {
            $intrashipPlugin->setLabel('DHL Adapter (alt)');
            $this->get('models')->flush($intrashipPlugin);
        }

        // Check for Shopware 5.2 attributew handling and fix migrated article elements if necessary
        // TODO: Remove this code, when setting the minimum required shopware version to 5.2
        if (class_exists('Shopware\\Models\\Attribute\\Configuration')) {
            $repository = $this->get('models')->getRepository('Shopware\\Models\\Attribute\\Configuration');
            // Country of origin
            $configuration = $repository->findOneBy([
                'columnName' => 'viisonCountryOfOrigin',
            ]);
            if ($configuration) {
                $configuration->setColumnName('viison_country_of_origin');
                $configuration->setColumnType('single_selection');
                $configuration->setEntity('Shopware\\Models\\Country\\Country');
                $this->get('models')->flush($configuration);
            }
            // Customs tariff number
            $configuration = $repository->findOneBy([
                'columnName' => 'viisonCustomsTariffNumber',
            ]);
            if ($configuration) {
                $configuration->setColumnName('viison_customs_tariff_number');
                $this->get('models')->flush($configuration);
            }
        }
    }

    /**
     * Clears relevant parts of the Shopware cache, if the Shopware version is < 5.1.2.
     */
    private function clearCacheIfNecessary()
    {
        if ($this->assertMinimumVersion('5.1.2')) {
            return;
        }

        $this->get('cache')->clean(Zend_Cache::CLEANING_MODE_MATCHING_ANY_TAG, [
            'Shopware_Config',
            'Shopware_Plugin',
            'Shopware_Models',
        ]);
        $this->get('shopware.cache_manager')->clearTemplateCache();
        $this->get('shopware.cache_manager')->clearProxyCache();
    }

    /**
     * Migrates the data of the old DHL Adapter (ViisonIntraship plugin) to this plugins database tables.
     * This includes copying all entries of 's_order_viison_intraship' and 's_core_shops_viison_intraship'
     * to 's_order_viison_dhl' and 's_core_shops_viison_dhl' respectively. The DHL products found in the
     * old table 's_viison_intraship_dhl_products' are checked for respective products in the new table
     * and, if a matching product is found, their associations in 's_order_viison_dhl' are kept and their
     * dispatch mappings are copied from 's_premium_dispatch_viison_intraship' to 's_premium_dispatch_viison_dhl'.
     * Otherwise it is check whether any dispatch methods are mapped to the old, obsolete product and if
     * true, their names are collected and returned after finishing all migrations. Finally, the old return
     * label email template is checked for user modifications and, if modified, it is copied to replace the
     * email template installed by this plugin to make sure that the user does not have to apply their
     * changes again.
     *
     * @return array
     */
    private function migrateDataFromOldDHLPlugin()
    {
        $database = $this->get('db');
        $incompleteDispatchMethods = [];
        // Copy the data from the old tables to the new tables
        $oldProducts = $database->fetchAll('SELECT * FROM s_viison_intraship_dhl_products');
        foreach ($oldProducts as $oldProduct) {
            // Try to find a product in this plugins table having the same configuration. This manual look up
            // is necessary, because the old product table might contain products which are no longer valid
            // and hence should not be copied to the new table.
            $newProductId = $database->fetchOne(
                'SELECT id
                FROM s_viison_dhl_products
                WHERE
                    code = :code
                    AND packageDimensionsRequired = :packageDimensionsRequired
                    AND premiumOption = :premiumOption
                    AND bulkfreightOption = :bulkfreightOption' .
                    ' AND process' . (($oldProduct['process']) ? (' = \'' . $oldProduct['process'] . '\'') : ' IS NULL') .
                    ' AND product' . (($oldProduct['product']) ? (' = \'' . $oldProduct['product'] . '\'') : ' IS NULL') .
                    ' AND dateTimeOption' . (($oldProduct['dateTimeOption']) ? (' = \'' . $oldProduct['dateTimeOption'] . '\'') : ' IS NULL') .
                    ' AND insuranceAmount' . (($oldProduct['insuranceAmount']) ? (' = \'' . $oldProduct['insuranceAmount'] . '\'') : ' IS NULL') .
                    ' AND deliveryTimeframe' . (($oldProduct['deliveryTimeframe']) ? (' = \'' . $oldProduct['deliveryTimeframe'] . '\'') : ' IS NULL'),
                [
                    'code' => $oldProduct['code'],
                    'packageDimensionsRequired' => $oldProduct['packageDimensionsRequired'],
                    'premiumOption' => intval($oldProduct['businessPackInternational'] === 'Premium'),
                    'bulkfreightOption' => intval($oldProduct['bulkfreightOption'] == 1),
                ]
            );

            // Always copy the labels, which were created using the (old) product
            $database->query(
                'INSERT IGNORE INTO s_order_viison_dhl (orderId, productId, trackingCode, url, exportDocumentUrl, returnShipment, customerAddress, weight, created)
                    SELECT orderId, IF(:newProductId <> 0, :newProductId, NULL), trackingCode, url, exportDocumentUrl, returnShipment, customerAddress, weight, created
                    FROM s_order_viison_intraship
                    WHERE productId = :oldProductId',
                [
                    'newProductId' => intval($newProductId),
                    'oldProductId' => $oldProduct['id'],
                ]
            );

            // Copy dispatch method mappings only if a respective new product exists
            if ($newProductId) {
                $database->query(
                    'INSERT IGNORE INTO s_premium_dispatch_viison_dhl (productId, dispatchId, exportDocumentRequired, visualAgeCheck, personalHandover)
                        SELECT :newProductId, dispatchId, exportDocumentRequired, visualAgeCheck, personalHandover
                        FROM s_premium_dispatch_viison_intraship
                        WHERE productId = :oldProductId',
                    [
                        'newProductId' => $newProductId,
                        'oldProductId' => $oldProduct['id'],
                    ]
                );
            } else {
                // No respective new product found, hence check for any dispatch methods that
                // are mapped to the old product
                $incompleteDispatchMethods += $database->fetchCol(
                    'SELECT d.name
                    FROM s_premium_dispatch d
                    JOIN s_premium_dispatch_viison_intraship di
                        ON di.dispatchId = d.id
                    WHERE di.productId = ?',
                    [
                        $oldProduct['id']
                    ]
                );
            }
        }

        // Copy all labels without a product (return labels)
        $database->query(
            'INSERT IGNORE INTO s_order_viison_dhl (orderId, trackingCode, url, exportDocumentUrl, returnShipment, customerAddress, weight, created)
                SELECT orderId, trackingCode, url, exportDocumentUrl, returnShipment, customerAddress, weight, created
                FROM s_order_viison_intraship
                WHERE productId IS NULL'
        );

        // Shop configs
        $oldConfigs = $database->fetchAll('SELECT * FROM s_core_shops_viison_intraship');
        foreach ($oldConfigs as $oldConfig) {
            // Remove the new config for the same shop to be able to copy the old one
            $database->query(
                'DELETE FROM s_core_shops_viison_dhl
                WHERE shopId = ?',
                [
                    $oldConfig['shopId']
                ]
            );

            // Remove values that should not be copied to the new config
            unset($oldConfig['id']);
            unset($oldConfig['accountType']);
            unset($oldConfig['partnerIdEXP']);
            unset($oldConfig['personalHandover']);
            unset($oldConfig['defaultMinimumAge']);
            unset($oldConfig['dispatchNotificationText']);
            unset($oldConfig['allowWebServiceVersion1_1']);
            unset($oldConfig['useDedicatedExpressAccount']);
            unset($oldConfig['bankBIC']);
            unset($oldConfig['bankAccountNumber']);
            unset($oldConfig['bankCode']);

            // Save the modified config in the database
            $database->insert('s_core_shops_viison_dhl', $oldConfig);
        }

        // Check for an existing, manually changed return label email template
        $oldEmailTemplateExistsAndWasChanged = $database->fetchOne(
            'SELECT id
            FROM s_core_config_mails
            WHERE name = \'ViisonIntrashipReturnLabelEmail\'
            AND dirty = 1'
        );
        if ($oldEmailTemplateExistsAndWasChanged) {
            // Replace the installed return label email template by the old template to keep user changes
            $database->query(
                'DELETE FROM s_core_config_mails
                WHERE name = \'ViisonDHLReturnLabelEmail\''
            );
            $database->query(
                'INSERT IGNORE INTO s_core_config_mails (name, frommail, fromname, subject, content, ishtml, mailtype, context)
                    SELECT \'ViisonDHLReturnLabelEmail\', frommail, fromname, subject, content, ishtml, mailtype, context
                    FROM s_core_config_mails
                    WHERE name = \'ViisonIntrashipReturnLabelEmail\''
            );
        }

        return $incompleteDispatchMethods;
    }
}
