<?php

/**
 * ---------------------------------------------------------------------
 *
 * GLPI - Gestionnaire Libre de Parc Informatique
 *
 * http://glpi-project.org
 *
 * @copyright 2015-2023 Teclib' and contributors.
 * @copyright 2003-2014 by the INDEPNET Development Team.
 * @licence   https://www.gnu.org/licenses/gpl-3.0.html
 *
 * ---------------------------------------------------------------------
 *
 * LICENSE
 *
 * This file is part of GLPI.
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <https://www.gnu.org/licenses/>.
 *
 * ---------------------------------------------------------------------
 */

/**
 * Update from 9.1 to 9.2
 *
 * @return bool for success (will die for most error)
 **/
function update91xto920()
{
    global $DB, $migration;

    $current_config   = Config::getConfigurationValues('core');
    $updateresult     = true;
    $ADDTODISPLAYPREF = [];

   //TRANS: %s is the number of new version
    $migration->displayTitle(sprintf(__('Update to %s'), '9.2'));
    $migration->setVersion('9.2');

   // add business criticity
    $migration->addField("glpi_infocoms", "businesscriticities_id", "integer");
    $migration->migrationOneTable('glpi_infocoms');
    $migration->addKey("glpi_infocoms", "businesscriticities_id");

    if (!$DB->tableExists("glpi_businesscriticities")) {
        $query = "CREATE TABLE `glpi_businesscriticities` (
        `id` int NOT NULL AUTO_INCREMENT,
        `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
        `entities_id` int NOT NULL DEFAULT '0',
        `is_recursive` tinyint NOT NULL DEFAULT '0',
        `comment` text COLLATE utf8_unicode_ci,
        `date_mod` datetime DEFAULT NULL,
        `date_creation` datetime DEFAULT NULL,
        `businesscriticities_id` int NOT NULL DEFAULT '0',
        `completename` text COLLATE utf8_unicode_ci,
        `level` int NOT NULL DEFAULT '0',
        `ancestors_cache` longtext COLLATE utf8_unicode_ci,
        `sons_cache` longtext COLLATE utf8_unicode_ci,
        PRIMARY KEY (`id`),
        KEY `name` (`name`),
        KEY `unicity` (`businesscriticities_id`,`name`),
        KEY `date_mod` (`date_mod`),
        KEY `date_creation` (`date_creation`)
                ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
        $DB->queryOrDie($query, "Add business criticity table");
    }

   // Issue #1250 - Add decimal to monitor size
    $migration->changeField('glpi_monitors', 'size', 'size', 'DECIMAL(5,2) NOT NULL DEFAULT "0"');

   //Make software license type a tree dropdown
    $migration->addField("glpi_softwarelicensetypes", "softwarelicensetypes_id", "integer");
    $migration->addField("glpi_softwarelicensetypes", "level", "integer");
    $migration->addField("glpi_softwarelicensetypes", "ancestors_cache", "longtext");
    $migration->addField("glpi_softwarelicensetypes", "sons_cache", "longtext");
    $migration->addField("glpi_softwarelicensetypes", "entities_id", "integer");
    $migration->addField("glpi_softwarelicensetypes", "is_recursive", "bool");
    $tree = $migration->addField("glpi_softwarelicensetypes", "completename", "text");
    $migration->migrationOneTable('glpi_softwarelicensetypes');
    $migration->addKey("glpi_softwarelicensetypes", "softwarelicensetypes_id");

   //First time the dropdown is changed from CommonDropdown to CommonTreeDropdown
    if ($tree) {
        $DB->updateOrDie(
            "glpi_softwarelicensetypes",
            [
                'completename' =>  new \QueryExpression(DBmysql::quoteName("name")),
                'is_recursive' => "1"
            ],
            [true],
            "9.2 make glpi_softwarelicensetypes a tree dropdown"
        );
    }

   // give READ right on components to profiles having UPDATE right
    $DB->updateOrDie(
        "glpi_profilerights",
        [
            'rights' => new \QueryExpression($DB->quoteName("rights") . " | " . READ)
        ],
        [
            new \QueryExpression(DBmysql::quoteName("rights") . " & " . DBmysql::quoteValue(UPDATE)),
            'name' => "device"
        ],
        "grant READ right on components to profiles having UPDATE right"
    );

    $migration->displayMessage(sprintf(__('Add of - %s to database'), 'Knowbase item link to tickets'));
    if (!$DB->tableExists('glpi_knowbaseitems_items')) {
        $query = "CREATE TABLE `glpi_knowbaseitems_items` (
                 `id` int NOT NULL AUTO_INCREMENT,
                 `knowbaseitems_id` int NOT NULL,
                 `itemtype` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
                 `items_id` int NOT NULL DEFAULT '0',
                 `date_creation` datetime DEFAULT NULL,
                 `date_mod` datetime DEFAULT NULL,
                 PRIMARY KEY (`id`),
                 UNIQUE KEY `unicity` (`itemtype`,`items_id`,`knowbaseitems_id`),
                 KEY `itemtype` (`itemtype`),
                 KEY `item_id` (`items_id`),
                 KEY `item` (`itemtype`,`items_id`)
               ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
        $DB->queryOrDie($query, "9.2 add table glpi_knowbaseitems_items");
    }

    $migration->displayMessage(sprintf(__('Add of - %s to database'), 'Knowbase item revisions'));
    if (!$DB->tableExists('glpi_knowbaseitems_revisions')) {
        $query = "CREATE TABLE `glpi_knowbaseitems_revisions` (
                 `id` int NOT NULL AUTO_INCREMENT,
                 `knowbaseitems_id` int NOT NULL,
                 `revision` int NOT NULL,
                 `name` text COLLATE utf8_unicode_ci,
                 `answer` longtext COLLATE utf8_unicode_ci,
                 `language` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
                 `users_id` int NOT NULL DEFAULT '0',
                 `date_creation` datetime DEFAULT NULL,
                 PRIMARY KEY (`id`),
                 UNIQUE KEY `unicity` (`knowbaseitems_id`, `revision`, `language`),
                 KEY `revision` (`revision`)
               ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
        $DB->queryOrDie($query, "9.2 add table glpi_knowbaseitems_revisions");
    }

    $migration->addField("glpi_knowbaseitemtranslations", "users_id", "integer");
    $migration->migrationOneTable("glpi_knowbaseitemtranslations");
    $migration->addKey("glpi_knowbaseitemtranslations", "users_id");

   //set kb translations users...
    $knowitems_iterator = $DB->request([
        'SELECT' => ['glpi_knowbaseitems.id', 'glpi_knowbaseitems.users_id'],
        'FROM' => 'glpi_knowbaseitems',
        'INNER JOIN' => [
            "glpi_knowbaseitemtranslations" => [
                'FKEY' => [
                    'glpi_knowbaseitemtranslations' => 'knowbaseitems_id',
                    'glpi_knowbaseitems'            => 'id'
                ]
            ]
        ]
    ]);
    foreach ($knowitems_iterator as $knowitems) {
        $DB->updateOrDie(
            "glpi_knowbaseitemtranslations",
            ['users_id' => $knowitems['users_id']],
            ['knowbaseitems_id' => $knowitems['id']],
            "Set knowledge base translations users"
        );
    }

    $migration->addField("glpi_knowbaseitemtranslations", "date_mod", "DATETIME");
    $migration->addField("glpi_knowbaseitemtranslations", "date_creation", "DATETIME");

    $migration->displayMessage(sprintf(__('Add of - %s to database'), 'Knowbase item comments'));
    if (!$DB->tableExists('glpi_knowbaseitems_comments')) {
        $query = "CREATE TABLE `glpi_knowbaseitems_comments` (
                 `id` int NOT NULL AUTO_INCREMENT,
                 `knowbaseitems_id` int NOT NULL,
                 `users_id` int NOT NULL DEFAULT '0',
                 `language` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
                 `comment` text COLLATE utf8_unicode_ci NOT NULL,
                 `parent_comment_id` int DEFAULT NULL,
                 `date_creation` datetime DEFAULT NULL,
                 `date_mod` datetime DEFAULT NULL,
                 PRIMARY KEY (`id`)
                ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
        $DB->queryOrDie($query, "9.2 add table glpi_knowbaseitems_comments");
    }

    $DB->updateOrDie(
        "glpi_profilerights",
        [
            'rights' => new \QueryExpression(
                DBmysql::quoteName("rights") . " | " . DBmysql::quoteValue(KnowbaseItem::COMMENTS)
            )
        ],
        ['name' => "knowbase"],
        "9.2 update knowledge base with comment right"
    );

   // add kb category to task categories
    $migration->addField("glpi_taskcategories", "knowbaseitemcategories_id", "integer");
    $migration->migrationOneTable("glpi_taskcategories");
    $migration->addKey("glpi_taskcategories", "knowbaseitemcategories_id");

   // #1476 - Add users_id on glpi_documents_items
    $migration->addField("glpi_documents_items", "users_id", "integer", ['null' => true]);
    $migration->migrationOneTable("glpi_documents_items");
    $migration->addKey("glpi_documents_items", "users_id");
   // TODO : can be improved when DBmysql->buildUpdate() support joins
    $migration->addPostQuery(
        "UPDATE `glpi_documents_items`,
                                    `glpi_documents`
                             SET `glpi_documents_items`.`users_id` = `glpi_documents`.`users_id`
                             WHERE `glpi_documents_items`.`documents_id` = `glpi_documents`.`id`",
        "9.2 update set users_id on glpi_documents_items"
    );

   //add product number
    $product_types = ['Computer',
        'Printer',
        'NetworkEquipment',
        'Phone',
        'Peripheral',
        'Monitor'
    ];

    foreach ($product_types as $type) {
        if (class_exists($type . 'Model')) {
            $table = getTableForItemType($type . 'Model');
            $migration->addField($table, 'product_number', 'string', ['after' => 'comment']);
            $migration->migrationOneTable($table);
            $migration->addKey($table, 'product_number');
        }
    }

   // add fields on every item_device tables
    $tables = ['glpi_items_devicecases',
        'glpi_items_devicecontrols',
        'glpi_items_devicedrives',
        'glpi_items_devicegraphiccards',
        'glpi_items_deviceharddrives',
        'glpi_items_devicememories',
        'glpi_items_devicemotherboards',
        'glpi_items_devicenetworkcards',
        'glpi_items_devicepcis',
        'glpi_items_devicepowersupplies',
        'glpi_items_deviceprocessors',
        'glpi_items_devicesoundcards'
    ];

   //add serial, location and state on each devices items
    foreach ($tables as $table) {
        $migration->addField($table, "otherserial", "varchar(255) NULL DEFAULT NULL");
        $migration->addField($table, "locations_id", "int NOT NULL DEFAULT '0'");
        $migration->addField($table, "states_id", "int NOT NULL DEFAULT '0'");
        $migration->migrationOneTable($table);
        $migration->addKey($table, 'otherserial');
        $migration->addKey($table, 'locations_id');
        $migration->addKey($table, 'states_id');
    }

   // Create tables :
    $tables = ['glpi_devicecasemodels',
        'glpi_devicecontrolmodels',
        'glpi_devicedrivemodels',
        'glpi_devicegraphiccardmodels',
        'glpi_deviceharddrivemodels',
        'glpi_devicememorymodels',
        'glpi_devicemotherboardmodels',
        'glpi_devicenetworkcardmodels',
        'glpi_devicepcimodels',
        'glpi_devicepowersupplymodels',
        'glpi_deviceprocessormodels',
        'glpi_devicesoundcardmodels',
        'glpi_devicegenericmodels',
        'glpi_devicebatterymodels',
        'glpi_devicefirmwaremodels',
        'glpi_devicesensormodels'
    ];

    foreach ($tables as $table) {
        if (!$DB->tableExists($table)) {
            $query = "CREATE TABLE `$table` (
                      `id` INT NOT NULL AUTO_INCREMENT,
                      `name` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
                      `comment` TEXT NULL COLLATE 'utf8_unicode_ci',
                      `product_number` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
                      PRIMARY KEY (`id`),
                      INDEX `name` (`name`),
                      INDEX `product_number` (`product_number`)
                   ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
            $DB->queryOrDie($query, "9.2 add model tables for devices");
        }
    }

   // Add a field in glpi_device* tables :
    $tables = ['glpi_devicecases'         => 'devicecasemodels_id',
        'glpi_devicecontrols'      => 'devicecontrolmodels_id',
        'glpi_devicedrives'        => 'devicedrivemodels_id',
        'glpi_devicegraphiccards'  => 'devicegraphiccardmodels_id',
        'glpi_deviceharddrives'    => 'deviceharddrivemodels_id',
        'glpi_devicememories'      => 'devicememorymodels_id',
        'glpi_devicemotherboards'  => 'devicemotherboardmodels_id',
        'glpi_devicenetworkcards'  => 'devicenetworkcardmodels_id',
        'glpi_devicepcis'          => 'devicepcimodels_id',
        'glpi_devicepowersupplies' => 'devicepowersupplymodels_id',
        'glpi_deviceprocessors'    => 'deviceprocessormodels_id',
        'glpi_devicesoundcards'    => 'devicesoundcardmodels_id'
    ];

    foreach ($tables as $table => $field) {
        $migration->addField($table, $field, 'int DEFAULT NULL', ['after' => 'is_recursive']);
        $migration->migrationOneTable($table);
        $migration->addKey($table, $field);
    }

    if (!$DB->tableExists('glpi_devicegenerics')) {
        $query = "CREATE TABLE `glpi_devicegenerics` (
                  `id` int NOT NULL AUTO_INCREMENT,
                  `designation` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
                  `devicegenerictypes_id` int NOT NULL DEFAULT '0',
                  `comment` text COLLATE utf8_unicode_ci,
                  `manufacturers_id` int NOT NULL DEFAULT '0',
                  `entities_id` int NOT NULL DEFAULT '0',
                  `is_recursive` tinyint NOT NULL DEFAULT '0',
                  `locations_id` int NOT NULL DEFAULT '0',
                  `states_id` int NOT NULL DEFAULT '0',
                  `devicegenericmodels_id` int DEFAULT NULL,
                  `date_mod` datetime DEFAULT NULL,
                  `date_creation` datetime DEFAULT NULL,
                  PRIMARY KEY (`id`),
                  KEY `designation` (`designation`),
                  KEY `manufacturers_id` (`manufacturers_id`),
                  KEY `devicegenerictypes_id` (`devicegenerictypes_id`),
                  KEY `entities_id` (`entities_id`),
                  KEY `is_recursive` (`is_recursive`),
                  KEY `locations_id` (`locations_id`),
                  KEY `states_id` (`states_id`),
                  KEY `date_mod` (`date_mod`),
                  KEY `date_creation` (`date_creation`),
                  KEY `devicegenericmodels_id` (`devicegenericmodels_id`)
               ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
         $DB->queryOrDie($query, "9.2 add table glpi_devicegenerics");
    }

    if (!$DB->tableExists('glpi_items_devicegenerics')) {
        $query = "CREATE TABLE `glpi_items_devicegenerics` (
                   `id` INT NOT NULL AUTO_INCREMENT,
                   `items_id` INT NOT NULL DEFAULT '0',
                   `itemtype` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
                   `devicegenerics_id` INT NOT NULL DEFAULT '0',
                   `is_deleted` TINYINT NOT NULL DEFAULT '0',
                   `is_dynamic` TINYINT NOT NULL DEFAULT '0',
                   `entities_id` INT NOT NULL DEFAULT '0',
                   `is_recursive` TINYINT NOT NULL DEFAULT '0',
                   `serial` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
                   `otherserial` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
                   `locations_id` INT NOT NULL DEFAULT '0',
                   `states_id` INT NOT NULL DEFAULT '0',
                   PRIMARY KEY (`id`),
                   INDEX `computers_id` (`items_id`),
                   INDEX `devicegenerics_id` (`devicegenerics_id`),
                   INDEX `is_deleted` (`is_deleted`),
                   INDEX `is_dynamic` (`is_dynamic`),
                   INDEX `entities_id` (`entities_id`),
                   INDEX `is_recursive` (`is_recursive`),
                   INDEX `serial` (`serial`),
                   INDEX `item` (`itemtype`, `items_id`),
                   INDEX `otherserial` (`otherserial`)
                ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
        $DB->queryOrDie($query, "9.2 add table glpi_items_devicegenerics");
    }

    if (!$DB->tableExists('glpi_devicegenerictypes')) {
        $query = "CREATE TABLE `glpi_devicegenerictypes` (
                  `id` INT NOT NULL AUTO_INCREMENT,
                  `name` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
                  `comment` TEXT NULL COLLATE 'utf8_unicode_ci',
                   PRIMARY KEY (`id`),
                   INDEX `name` (`name`)
                ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
        $DB->queryOrDie($query, "9.2 add table glpi_devicegenerictypes");
    }

    if (!$DB->tableExists('glpi_devicebatteries')) {
        $query = "CREATE TABLE `glpi_devicebatteries` (
                  `id` int NOT NULL AUTO_INCREMENT,
                  `designation` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
                  `comment` text COLLATE utf8_unicode_ci,
                  `manufacturers_id` int NOT NULL DEFAULT '0',
                  `voltage` int DEFAULT NULL,
                  `capacity` int DEFAULT NULL,
                  `devicebatterytypes_id` int NOT NULL DEFAULT '0',
                  `entities_id` int NOT NULL DEFAULT '0',
                  `is_recursive` tinyint NOT NULL DEFAULT '0',
                  `devicebatterymodels_id` int DEFAULT NULL,
                  `date_mod` datetime DEFAULT NULL,
                  `date_creation` datetime DEFAULT NULL,
                  PRIMARY KEY (`id`),
                  KEY `designation` (`designation`),
                  KEY `manufacturers_id` (`manufacturers_id`),
                  KEY `entities_id` (`entities_id`),
                  KEY `is_recursive` (`is_recursive`),
                  KEY `date_mod` (`date_mod`),
                  KEY `date_creation` (`date_creation`),
                  KEY `devicebatterymodels_id` (`devicebatterymodels_id`),
                  KEY `devicebatterytypes_id` (`devicebatterytypes_id`)
                ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
        $DB->queryOrDie($query, "9.2 add table glpi_devicebatteries");
    }

    if (!$DB->tableExists('glpi_items_devicebatteries')) {
        $query = "CREATE TABLE `glpi_items_devicebatteries` (
                  `id` int NOT NULL AUTO_INCREMENT,
                  `items_id` int NOT NULL DEFAULT '0',
                  `itemtype` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
                  `devicebatteries_id` int NOT NULL DEFAULT '0',
                  `manufacturing_date` date DEFAULT NULL,
                  `is_deleted` tinyint NOT NULL DEFAULT '0',
                  `is_dynamic` tinyint NOT NULL DEFAULT '0',
                  `entities_id` int NOT NULL DEFAULT '0',
                  `is_recursive` tinyint NOT NULL DEFAULT '0',
                  `serial` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
                  `otherserial` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
                  `locations_id` int NOT NULL DEFAULT '0',
                  `states_id` int NOT NULL DEFAULT '0',
                  PRIMARY KEY (`id`),
                  KEY `computers_id` (`items_id`),
                  KEY `devicebatteries_id` (`devicebatteries_id`),
                  KEY `is_deleted` (`is_deleted`),
                  KEY `is_dynamic` (`is_dynamic`),
                  KEY `entities_id` (`entities_id`),
                  KEY `is_recursive` (`is_recursive`),
                  KEY `serial` (`serial`),
                  KEY `item` (`itemtype`,`items_id`),
                  KEY `otherserial` (`otherserial`)
               ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
        $DB->queryOrDie($query, "9.2 add table glpi_items_devicebatteries");
    }

    if (!$DB->tableExists('glpi_devicebatterytypes')) {
        $query = "CREATE TABLE `glpi_devicebatterytypes` (
                  `id` int NOT NULL AUTO_INCREMENT,
                  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
                  `comment` text COLLATE utf8_unicode_ci,
                  `date_mod` datetime DEFAULT NULL,
                  `date_creation` datetime DEFAULT NULL,
                  PRIMARY KEY (`id`),
                  KEY `name` (`name`),
                  KEY `date_mod` (`date_mod`),
                  KEY `date_creation` (`date_creation`)
               ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
        $DB->queryOrDie($query, "9.2 add table glpi_devicebatterytypes");
    }

    if (!$DB->tableExists('glpi_devicefirmwares')) {
        $query = "CREATE TABLE `glpi_devicefirmwares` (
                  `id` int NOT NULL AUTO_INCREMENT,
                  `designation` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
                  `comment` text COLLATE utf8_unicode_ci,
                  `manufacturers_id` int NOT NULL DEFAULT '0',
                  `date` date DEFAULT NULL,
                  `version` varchar(255) DEFAULT NULL,
                  `devicefirmwaretypes_id` int NOT NULL DEFAULT '0',
                  `entities_id` int NOT NULL DEFAULT '0',
                  `is_recursive` tinyint NOT NULL DEFAULT '0',
                  `devicefirmwaremodels_id` int DEFAULT NULL,
                  `date_mod` datetime DEFAULT NULL,
                  `date_creation` datetime DEFAULT NULL,
                  PRIMARY KEY (`id`),
                  KEY `designation` (`designation`),
                  KEY `manufacturers_id` (`manufacturers_id`),
                  KEY `entities_id` (`entities_id`),
                  KEY `is_recursive` (`is_recursive`),
                  KEY `date_mod` (`date_mod`),
                  KEY `date_creation` (`date_creation`),
                  KEY `devicefirmwaremodels_id` (`devicefirmwaremodels_id`),
                  KEY `devicefirmwaretypes_id` (`devicefirmwaretypes_id`)
               ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
        $DB->queryOrDie($query, "9.2 add table glpi_devicefirmwares");
    }
    if (!$DB->tableExists('glpi_items_devicefirmwares')) {
        $query = "CREATE TABLE `glpi_items_devicefirmwares` (
                  `id` int NOT NULL AUTO_INCREMENT,
                  `items_id` int NOT NULL DEFAULT '0',
                  `itemtype` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
                  `devicefirmwares_id` int NOT NULL DEFAULT '0',
                  `is_deleted` tinyint NOT NULL DEFAULT '0',
                  `is_dynamic` tinyint NOT NULL DEFAULT '0',
                  `entities_id` int NOT NULL DEFAULT '0',
                  `is_recursive` tinyint NOT NULL DEFAULT '0',
                  `serial` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
                  `otherserial` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
                  `locations_id` int NOT NULL DEFAULT '0',
                  `states_id` int NOT NULL DEFAULT '0',
                  PRIMARY KEY (`id`),
                  KEY `computers_id` (`items_id`),
                  KEY `devicefirmwares_id` (`devicefirmwares_id`),
                  KEY `is_deleted` (`is_deleted`),
                  KEY `is_dynamic` (`is_dynamic`),
                  KEY `entities_id` (`entities_id`),
                  KEY `is_recursive` (`is_recursive`),
                  KEY `serial` (`serial`),
                  KEY `item` (`itemtype`,`items_id`),
                  KEY `otherserial` (`otherserial`)
               ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
        $DB->queryOrDie($query, "9.2 add table glpi_items_devicefirmwares");
    }
    if (!$DB->tableExists('glpi_devicefirmwaretypes')) {
        $query = "CREATE TABLE `glpi_devicefirmwaretypes` (
                  `id` int NOT NULL AUTO_INCREMENT,
                  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
                  `comment` text COLLATE utf8_unicode_ci,
                  `date_mod` datetime DEFAULT NULL,
                  `date_creation` datetime DEFAULT NULL,
                  PRIMARY KEY (`id`),
                  KEY `name` (`name`),
                  KEY `date_mod` (`date_mod`),
                  KEY `date_creation` (`date_creation`)
               ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
        $DB->queryOrDie($query, "9.2 add table glpi_devicefirmwaretypes");

        $DB->insertOrDie("glpi_devicefirmwaretypes", [
            'id'              => "1",
            'name'            => "BIOS",
            'comment'         => null,
            'date_mod'        => null,
            'date_creation'   => null
        ]);

        $DB->insertOrDie("glpi_devicefirmwaretypes", [
            'id'              => "2",
            'name'            => "UEFI",
            'comment'         => null,
            'date_mod'        => null,
            'date_creation'   => null
        ]);

        $DB->insertOrDie("glpi_devicefirmwaretypes", [
            'id'              => "3",
            'name'            => "Firmware",
            'comment'         => null,
            'date_mod'        => null,
            'date_creation'   => null
        ]);
    }

   //Device sensors
    if (!$DB->tableExists('glpi_devicesensors')) {
        $query = "CREATE TABLE `glpi_devicesensors` (
                  `id` int NOT NULL AUTO_INCREMENT,
                  `designation` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
                  `devicesensortypes_id` int NOT NULL DEFAULT '0',
                  `devicesensormodels_id` int NOT NULL DEFAULT '0',
                  `comment` text COLLATE utf8_unicode_ci,
                  `manufacturers_id` int NOT NULL DEFAULT '0',
                  `entities_id` int NOT NULL DEFAULT '0',
                  `is_recursive` tinyint NOT NULL DEFAULT '0',
                  `locations_id` int NOT NULL DEFAULT '0',
                  `states_id` int NOT NULL DEFAULT '0',
                  `date_mod` datetime DEFAULT NULL,
                  `date_creation` datetime DEFAULT NULL,
                  PRIMARY KEY (`id`),
                  KEY `designation` (`designation`),
                  KEY `manufacturers_id` (`manufacturers_id`),
                  KEY `devicesensortypes_id` (`devicesensortypes_id`),
                  KEY `entities_id` (`entities_id`),
                  KEY `is_recursive` (`is_recursive`),
                  KEY `locations_id` (`locations_id`),
                  KEY `states_id` (`states_id`),
                  KEY `date_mod` (`date_mod`),
                  KEY `date_creation` (`date_creation`)
               ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
         $DB->queryOrDie($query, "9.2 add table glpi_devicesensors");
    }

    if (!$DB->tableExists('glpi_items_devicesensors')) {
        $query = "CREATE TABLE `glpi_items_devicesensors` (
                   `id` INT NOT NULL AUTO_INCREMENT,
                   `items_id` INT NOT NULL DEFAULT '0',
                   `itemtype` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
                   `devicesensors_id` INT NOT NULL DEFAULT '0',
                   `is_deleted` TINYINT NOT NULL DEFAULT '0',
                   `is_dynamic` TINYINT NOT NULL DEFAULT '0',
                   `entities_id` INT NOT NULL DEFAULT '0',
                   `is_recursive` TINYINT NOT NULL DEFAULT '0',
                   `serial` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
                   `otherserial` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
                   `locations_id` INT NOT NULL DEFAULT '0',
                   `states_id` INT NOT NULL DEFAULT '0',
                   PRIMARY KEY (`id`),
                   INDEX `computers_id` (`items_id`),
                   INDEX `devicesensors_id` (`devicesensors_id`),
                   INDEX `is_deleted` (`is_deleted`),
                   INDEX `is_dynamic` (`is_dynamic`),
                   INDEX `entities_id` (`entities_id`),
                   INDEX `is_recursive` (`is_recursive`),
                   INDEX `serial` (`serial`),
                   INDEX `item` (`itemtype`, `items_id`),
                   INDEX `otherserial` (`otherserial`)
                )
                COLLATE='utf8_unicode_ci'
                ENGINE=MyISAM;";
        $DB->queryOrDie($query, "9.2 add table glpi_items_devicesensors");
    }

    if (!$DB->tableExists('glpi_devicesensortypes')) {
        $query = "CREATE TABLE `glpi_devicesensortypes` (
                  `id` INT NOT NULL AUTO_INCREMENT,
                  `name` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
                  `comment` TEXT NULL COLLATE 'utf8_unicode_ci',
                   PRIMARY KEY (`id`),
                   INDEX `name` (`name`)
                )
                COLLATE='utf8_unicode_ci' ENGINE=MyISAM;";
        $DB->queryOrDie($query, "9.2 add table glpi_devicesensortypes");
    }

   //Father/son for Software licenses
    $migration->addField("glpi_softwarelicenses", "softwarelicenses_id", "integer", ['after' => 'softwares_id']);
    $new = $migration->addField("glpi_softwarelicenses", "completename", "text", ['after' => 'softwarelicenses_id']);
    $migration->addField("glpi_softwarelicenses", "level", "integer", ['after' => 'completename']);
    $migration->migrationOneTable("glpi_softwarelicenses");
    if ($new) {
        $DB->updateOrDie(
            "glpi_softwarelicenses",
            [
                'completename' => new \QueryExpression(DBmysql::quoteName("name"))
            ],
            [true],
            "9.2 copy name to completename for software licenses"
        );
    }

   // add template key to itiltasks
    $migration->addField("glpi_tickettasks", "tasktemplates_id", "integer");
    $migration->migrationOneTable('glpi_tickettasks');
    $migration->addKey("glpi_tickettasks", "tasktemplates_id");

    $migration->addField("glpi_problemtasks", "tasktemplates_id", "integer");
    $migration->migrationOneTable('glpi_problemtasks');
    $migration->addKey("glpi_problemtasks", "tasktemplates_id");

    $migration->addField("glpi_changetasks", "tasktemplates_id", "integer");
    $migration->migrationOneTable('glpi_changetasks');
    $migration->addKey("glpi_changetasks", "tasktemplates_id");

   // add missing fields to tasktemplate
    $migration->addField("glpi_tasktemplates", "state", "integer");
    $migration->addField("glpi_tasktemplates", "is_private", "bool");
    $migration->addField("glpi_tasktemplates", "users_id_tech", "integer");
    $migration->addField("glpi_tasktemplates", "groups_id_tech", "integer");
    $migration->migrationOneTable('glpi_tasktemplates');
    $migration->addKey("glpi_tickettasks", "is_private");
    $migration->addKey("glpi_tickettasks", "users_id_tech");
    $migration->addKey("glpi_tickettasks", "groups_id_tech");

   // #1735 - Add new notifications
    $notification       = new Notification();
    $notificationtarget = new NotificationTarget();
    $new_notifications  = [
        'requester_user'  => ['label'      => 'New user in requesters',
            'targets_id' => Notification::AUTHOR
        ],
        'requester_group' => ['label'      => 'New group in requesters',
            'targets_id' => Notification::REQUESTER_GROUP
        ],
        'observer_user'   => ['label'      => 'New user in observers',
            'targets_id' => Notification::OBSERVER
        ],
        'observer_group'  => ['label'      => 'New group in observers',
            'targets_id' => Notification::OBSERVER_GROUP
        ],
        'assign_user'     => ['label'      => 'New user in assignees',
            'targets_id' => Notification::ASSIGN_TECH
        ],
        'assign_group'    => ['label'      => 'New group in assignees',
            'targets_id' => Notification::ITEM_TECH_GROUP_IN_CHARGE
        ],
        'assign_supplier' => ['label'      => 'New supplier in assignees',
            'targets_id' => Notification::SUPPLIER
        ],
    ];

    if ($DB->fieldExists("glpi_notifications", "mode", false)) {
        $notificationtemplates_id = 0;
        $notificationtemplate = new NotificationTemplate();
        if ($notificationtemplate->getFromDBByCrit(['name' => 'Tickets', 'itemtype' => 'Ticket'])) {
            $notificationtemplates_id = $notificationtemplate->fields['id'];
        }

        foreach ($new_notifications as $event => $notif_options) {
            $notifications_id = $notification->add([
                'name'                     => $notif_options['label'],
                'itemtype'                 => 'Ticket',
                'event'                    => $event,
                'mode'                     => Notification_NotificationTemplate::MODE_MAIL,
                'notificationtemplates_id' => $notificationtemplates_id,
                'is_recursive'             => 1,
                'is_active'                => 0,
            ]);

            $notificationtarget->add([
                'items_id'         => $notif_options['targets_id'],
                'type'             => 1,
                'notifications_id' => $notifications_id,
            ]);
        }
    }

    $migration->addField('glpi_states', 'is_visible_certificate', 'bool', ['value' => 1]);
    $migration->addKey('glpi_states', 'is_visible_certificate');

    /** ************ New SLM structure ************ */
    if (!$DB->tableExists('glpi_olas')) {
        $query = "CREATE TABLE `glpi_olas` (
                  `id` int NOT NULL AUTO_INCREMENT,
                  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
                  `entities_id` int NOT NULL DEFAULT '0',
                  `is_recursive` tinyint NOT NULL DEFAULT '0',
                  `type` int NOT NULL DEFAULT '0',
                  `comment` text COLLATE utf8_unicode_ci,
                  `number_time` int NOT NULL,
                  `calendars_id` int NOT NULL DEFAULT '0',
                  `date_mod` datetime DEFAULT NULL,
                  `definition_time` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
                  `end_of_working_day` tinyint NOT NULL DEFAULT '0',
                  `date_creation` datetime DEFAULT NULL,
                  `slms_id` int NOT NULL DEFAULT '0',
                  PRIMARY KEY (`id`),
                  KEY `name` (`name`),
                  KEY `calendars_id` (`calendars_id`),
                  KEY `date_mod` (`date_mod`),
                  KEY `date_creation` (`date_creation`),
                  KEY `slms_id` (`slms_id`)
                ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;";
        $DB->queryOrDie($query, "9.2 add table glpi_olas");
    }

    if (!$DB->tableExists('glpi_olalevelactions')) {
        $query = "CREATE TABLE `glpi_olalevelactions` (
               `id` int NOT NULL AUTO_INCREMENT,
               `olalevels_id` int NOT NULL DEFAULT '0',
               `action_type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
               `field` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
               `value` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
               PRIMARY KEY (`id`),
               KEY `olalevels_id` (`olalevels_id`)
            ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;";
        $DB->queryOrDie($query, "9.2 add table glpi_olalevelactions");
    }

    if (!$DB->tableExists('glpi_olalevelcriterias')) {
        $query = "CREATE TABLE `glpi_olalevelcriterias` (
               `id` int NOT NULL AUTO_INCREMENT,
               `olalevels_id` int NOT NULL DEFAULT '0',
               `criteria` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
               `condition` int NOT NULL DEFAULT '0' COMMENT 'see define.php PATTERN_* and REGEX_* constant',
               `pattern` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
               PRIMARY KEY (`id`),
               KEY `olalevels_id` (`olalevels_id`),
               KEY `condition` (`condition`)
            ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;";
        $DB->queryOrDie($query, "9.2 add table glpi_olalevelcriterias");
    }

    if (!$DB->tableExists('glpi_olalevels')) {
        $query = "CREATE TABLE `glpi_olalevels` (
               `id` int NOT NULL AUTO_INCREMENT,
               `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
               `olas_id` int NOT NULL DEFAULT '0',
               `execution_time` int NOT NULL,
               `is_active` tinyint NOT NULL DEFAULT '1',
               `entities_id` int NOT NULL DEFAULT '0',
               `is_recursive` tinyint NOT NULL DEFAULT '0',
               `match` char(10) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'see define.php *_MATCHING constant',
               `uuid` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
               PRIMARY KEY (`id`),
               KEY `name` (`name`),
               KEY `is_active` (`is_active`),
               KEY `olas_id` (`olas_id`)
            ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;";
        $DB->queryOrDie($query, "9.2 add table glpi_olalevels");
    }

    if (!$DB->tableExists('glpi_olalevels_tickets')) {
        $query = "CREATE TABLE `glpi_olalevels_tickets` (
                  `id` int NOT NULL AUTO_INCREMENT,
                  `tickets_id` int NOT NULL DEFAULT '0',
                  `olalevels_id` int NOT NULL DEFAULT '0',
                  `date` datetime DEFAULT NULL,
                  PRIMARY KEY (`id`),
                  KEY `tickets_id` (`tickets_id`),
                  KEY `olalevels_id` (`olalevels_id`),
                  KEY `unicity` (`tickets_id`,`olalevels_id`)
               ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;";
        $DB->queryOrDie($query, "9.2 add table glpi_olalevels_tickets");

        $DB->updateOrInsert("glpi_crontasks", [
            'frequency'       => "604800",
            'param'           => null,
            'state'           => "1",
            'mode'            => "1",
            'allowmode'       => "3",
            'hourmin'         => "0",
            'hourmax'         => "24",
            'logs_lifetime'   => "30",
            'lastrun'         => null,
            'lastcode'        => null,
            'comment'         => null
        ], [
            'itemtype'  => "OlaLevel_Ticket",
            'name'      => "olaticket"
        ]);
    }

    if (!$DB->tableExists('glpi_slms')) {
       // Changing the structure of the table 'glpi_slas'
        $migration->renameTable('glpi_slas', 'glpi_slms');
        $migration->migrationOneTable('glpi_slas');
    }

   // Changing the structure of the table 'glpi_slts'
    if ($DB->tableExists('glpi_slts')) {
        $migration->renameTable('glpi_slts', 'glpi_slas');
        $migration->migrationOneTable('glpi_slts');
        $migration->changeField('glpi_slas', 'slas_id', 'slms_id', 'integer');
        $migration->dropKey('glpi_slas', 'slas_id');
        $migration->addKey('glpi_slas', 'slms_id');
    }

   // Slalevels changes
    if ($DB->fieldExists("glpi_slalevels", "slts_id")) {
        $migration->changeField('glpi_slalevels', 'slts_id', 'slas_id', 'integer');
        $migration->migrationOneTable('glpi_slalevels');
        $migration->dropKey('glpi_slalevels', 'slts_id');
        $migration->addKey('glpi_slalevels', 'slas_id');
    }

   // Ticket changes
    if (!$DB->fieldExists("glpi_tickets", "ola_waiting_duration", false)) {
        $migration->addField(
            "glpi_tickets",
            "ola_waiting_duration",
            "integer",
            ['after' => 'sla_waiting_duration']
        );
        $migration->migrationOneTable('glpi_tickets');
    }

    if (!$DB->fieldExists("glpi_tickets", "olas_tto_id", false)) {
        $migration->addField("glpi_tickets", "olas_tto_id", "integer", ['after' => 'ola_waiting_duration']);
        $migration->migrationOneTable('glpi_tickets');
        $migration->addKey('glpi_tickets', 'olas_tto_id');
    }

    if (!$DB->fieldExists("glpi_tickets", "olas_ttr_id", false)) {
        $migration->addField("glpi_tickets", "olas_ttr_id", "integer", ['after' => 'olas_tto_id']);
        $migration->migrationOneTable('glpi_tickets');
        $migration->addKey('glpi_tickets', 'olas_ttr_id');
    }

    if (!$DB->fieldExists("glpi_tickets", "ttr_olalevels_id", false)) {
        $migration->addField("glpi_tickets", "ttr_olalevels_id", "integer", ['after' => 'olas_ttr_id']);
        $migration->migrationOneTable('glpi_tickets');
    }

    if (!$DB->fieldExists("glpi_tickets", "internal_time_to_resolve", false)) {
        $migration->addField(
            "glpi_tickets",
            "internal_time_to_resolve",
            "datetime",
            ['after' => 'ttr_olalevels_id']
        );
        $migration->migrationOneTable('glpi_tickets');
        $migration->addKey('glpi_tickets', 'internal_time_to_resolve');
    }

    if (!$DB->fieldExists("glpi_tickets", "internal_time_to_own", false)) {
        $migration->addField(
            "glpi_tickets",
            "internal_time_to_own",
            "datetime",
            ['after' => 'internal_time_to_resolve']
        );
        $migration->migrationOneTable('glpi_tickets');
        $migration->addKey('glpi_tickets', 'internal_time_to_own');
    }

    if ($DB->fieldExists("glpi_tickets", "slts_tto_id")) {
        $migration->changeField("glpi_tickets", "slts_tto_id", "slas_tto_id", "integer");
        $migration->migrationOneTable('glpi_tickets');
        $migration->addKey('glpi_tickets', 'slas_tto_id');
        $migration->dropKey('glpi_tickets', 'slts_tto_id');
    }

    if ($DB->fieldExists("glpi_tickets", "slts_ttr_id")) {
        $migration->changeField("glpi_tickets", "slts_ttr_id", "slas_ttr_id", "integer");
        $migration->migrationOneTable('glpi_tickets');
        $migration->addKey('glpi_tickets', 'slas_ttr_id');
        $migration->dropKey('glpi_tickets', 'slts_ttr_id');
    }
    if ($DB->fieldExists("glpi_tickets", "due_date")) {
        $migration->changeField('glpi_tickets', 'due_date', 'time_to_resolve', 'datetime');
        $migration->migrationOneTable('glpi_tickets');
        $migration->dropKey('glpi_tickets', 'due_date');
        $migration->addKey('glpi_tickets', 'time_to_resolve');
    }

   //Change changes
    if ($DB->fieldExists("glpi_changes", "due_date")) {
        $migration->changeField('glpi_changes', 'due_date', 'time_to_resolve', 'datetime');
        $migration->migrationOneTable('glpi_changes');
        $migration->dropKey('glpi_changes', 'due_date');
        $migration->addKey('glpi_changes', 'time_to_resolve');
    }

   //Problem changes
    if ($DB->fieldExists("glpi_problems", "due_date")) {
        $migration->changeField('glpi_problems', 'due_date', 'time_to_resolve', 'datetime');
        $migration->migrationOneTable('glpi_problems');
        $migration->dropKey('glpi_problems', 'due_date');
        $migration->addKey('glpi_problems', 'time_to_resolve');
    }

   // ProfileRights changes
    $DB->updateOrDie(
        "glpi_profilerights",
        ['name' => "slm"],
        ['name' => "sla"],
        "SLM profilerights migration"
    );

   //Sla rules criterias migration
    $DB->updateOrDie(
        "glpi_rulecriterias",
        ['criteria' => "slas_ttr_id"],
        ['criteria' => "slts_ttr_id"],
        "SLA rulecriterias migration"
    );

    $DB->updateOrDie(
        "glpi_rulecriterias",
        ['criteria' => "slas_tto_id"],
        ['criteria' => "slts_tto_id"],
        "SLA rulecriterias migration"
    );

   // Sla rules actions migration
    $DB->updateOrDie(
        "glpi_ruleactions",
        ['field' => "slas_ttr_id"],
        ['field' => "slts_ttr_id"],
        "SLA ruleactions migration"
    );

    $DB->updateOrDie(
        "glpi_ruleactions",
        ['field' => "slas_tto_id"],
        ['field' => "slts_tto_id"],
        "SLA ruleactions migration"
    );

   /************** Auto login **************/
    $migration->addConfig([
        'login_remember_time'      => 604800,
        'login_remember_default'   => 1
    ]);

    if ($DB->tableExists('glpi_bookmarks')) {
        $migration->renameTable("glpi_bookmarks", "glpi_savedsearches");

        $migration->addField("glpi_savedsearches", "last_execution_time", "int NULL DEFAULT NULL");
        $migration->addField(
            "glpi_savedsearches",
            "do_count",
            "tinyint NOT NULL DEFAULT '2' COMMENT 'Do or do not count results on list display; see SavedSearch::COUNT_* constants'"
        );
        $migration->addField(
            "glpi_savedsearches",
            "last_execution_date",
            "DATETIME NULL DEFAULT NULL"
        );
        $migration->addField("glpi_savedsearches", "counter", "int NOT NULL DEFAULT '0'");
        $migration->migrationOneTable("glpi_savedsearches");
        $migration->addKey("glpi_savedsearches", 'last_execution_time');
        $migration->addKey("glpi_savedsearches", 'do_count');
        $migration->addKey("glpi_savedsearches", 'last_execution_date');
    }
   //ensure do_count is set to AUTO
    $migration->addPostQuery(
        $DB->buildUpdate(
            "glpi_savedsearches",
            ['do_count' => SavedSearch::COUNT_AUTO],
            [true]
        )
    );

    $migration->addPostQuery(
        $DB->buildUpdate(
            "glpi_savedsearches",
            ['entities_id' => "0"],
            ['entities_id' => "-1"]
        )
    );

    if (
        !countElementsInTable(
            'glpi_rules',
            ['sub_type' => 'RuleSoftwareCategory',
                'uuid'     => '500717c8-2bd6e957-53a12b5fd38869.86003425'
            ]
        )
    ) {
        $rule = new Rule();
        $rules_id = $rule->add(['name'         => 'Import category from inventory tool',
            'is_active'    => 0,
            'uuid'         => '500717c8-2bd6e957-53a12b5fd38869.86003425',
            'entities_id'  => 0,
            'is_recursive' => 1,
            'sub_type'     => 'RuleSoftwareCategory',
            'match'        => Rule::AND_MATCHING,
            'condition'    => 1,
            'description'  => ''
        ]);
        if ($rules_id) {
            $criteria = new RuleCriteria();
            $criteria->add(['rules_id'  => $rules_id,
                'criteria'  => 'name',
                'condition' => '0',
                'pattern'   => '*'
            ]);

            $action = new RuleAction();
            $action->add(['rules_id'    => $rules_id,
                'action_type' => 'assign',
                'field'       => '_import_category',
                'value'       => '1'
            ]);
        }
    }

    if ($DB->tableExists('glpi_queuedmails')) {
        $migration->renameTable("glpi_queuedmails", "glpi_queuednotifications");
    }

    $migration->addPostQuery(
        $DB->buildUpdate(
            "glpi_crontasks",
            ['itemtype' => "QueuedNotification"],
            ['itemtype' => "QueuedMail"]
        )
    );
    $migration->addPostQuery(
        $DB->buildUpdate(
            "glpi_crontasks",
            ['name' => "queuednotification"],
            ['name' => "queuedmail"]
        )
    );
    $migration->addPostQuery(
        $DB->buildUpdate(
            "glpi_crontasks",
            ['name' => "queuednotificationclean"],
            ['name' => "queuedmailclean"]
        )
    );
    $migration->addPostQuery(
        $DB->buildUpdate(
            "glpi_profilerights",
            ['name' => "queuednotification"],
            ['name' => "queuedmail"]
        )
    );

    if (isset($current_config['use_mailing']) && !isset($current_config['use_notifications'])) {
        /** Notifications modes */
        $migration->addConfig([
            'use_notifications'                 => $current_config['use_mailing'],
            'notifications_mailing'    => $current_config['use_mailing'],
            'notifications_ajax'       => 0,
            'notifications_ajax_check_interval' => '5',
            'notifications_ajax_sound' => null,
            'notifications_ajax_icon_url'       => '/pics/glpi.png'
        ]);
    }

    if (!$DB->tableExists('glpi_notifications_notificationtemplates')) {
        $query = "CREATE TABLE `glpi_notifications_notificationtemplates` (
                  `id` int NOT NULL AUTO_INCREMENT,
                  `notifications_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
                  `mode` varchar(20) COLLATE utf8_unicode_ci NOT NULL COMMENT 'See Notification_NotificationTemplate::MODE_* constants',
                  `notificationtemplates_id` int NOT NULL DEFAULT '0',
                  PRIMARY KEY (`id`),
                  UNIQUE KEY `unicity` (`notifications_id`, `mode`, `notificationtemplates_id`),
                  KEY `notifications_id` (`notifications_id`),
                  KEY `notificationtemplates_id` (`notificationtemplates_id`),
                  KEY `mode` (`mode`)
                ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
        $DB->queryOrDie($query, "9.2 add table glpi_notifications_notificationtemplates");
    }

    if ($DB->fieldExists("glpi_notifications", "mode", false)) {
       // TODO can be done when DB::updateOrInsert() supports SELECT
        $query = "REPLACE INTO `glpi_notifications_notificationtemplates`
                       (`notifications_id`, `mode`, `notificationtemplates_id`)
                       SELECT `id`, `mode`, `notificationtemplates_id`
                       FROM `glpi_notifications`";
        $DB->queryOrDie($query, "9.2 migrate notifications templates");

       //migrate any existing mode before removing the field
        $migration->dropField('glpi_notifications', 'mode');
        $migration->dropField('glpi_notifications', 'notificationtemplates_id');

        $migration->migrationOneTable("glpi_notifications");
    }

    $migration->addField(
        'glpi_queuednotifications',
        'mode',
        'varchar(20) COLLATE utf8_unicode_ci NOT NULL COMMENT \'See Notification_NotificationTemplate::MODE_* constants\''
    );
    $migration->migrationOneTable("glpi_queuednotifications");
    $migration->addKey('glpi_queuednotifications', 'mode');
    $migration->addPostQuery(
        $DB->buildUpdate(
            "glpi_queuednotifications",
            ['mode' => Notification_NotificationTemplate::MODE_MAIL],
            [true]
        ),
        "9.2 set default mode in queue"
    );

    $migration->addPostQuery(
        $DB->buildUpdate(
            "glpi_notifications_notificationtemplates",
            ['mode' => Notification_NotificationTemplate::MODE_MAIL],
            ['mode' => "mail"]
        ),
        "9.2 set default mode in notifications templates"
    );

   // Migration Bookmark -> SavedSearch_Alert
   //TRANS: %s is the table or item to migrate
    if ($DB->tableExists('glpi_bookmarks_users')) {
        $migration->renameTable("glpi_bookmarks_users", "glpi_savedsearches_users");
        $migration->changeField(
            'glpi_savedsearches_users',
            'bookmarks_id',
            'savedsearches_id',
            'int NOT NULL DEFAULT "0"'
        );
    }

    if (!$DB->tableExists('glpi_savedsearches_alerts')) {
        $query = "CREATE TABLE `glpi_savedsearches_alerts` (
                  `id` int NOT NULL AUTO_INCREMENT,
                  `savedsearches_id` int NOT NULL DEFAULT '0',
                  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
                  `is_active` tinyint NOT NULL DEFAULT '0',
                  `operator` tinyint NOT NULL,
                  `value` int NOT NULL,
                  `date_mod` datetime DEFAULT NULL,
                  `date_creation` datetime DEFAULT NULL,
                  PRIMARY KEY (`id`),
                  KEY `name` (`name`),
                  KEY `is_active` (`is_active`),
                  KEY `date_mod` (`date_mod`),
                  KEY `date_creation` (`date_creation`),
                  UNIQUE KEY `unicity` (`savedsearches_id`,`operator`, `value`)
                 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
        $DB->queryOrDie($query, "9.2 add table glpi_savedsearches_alerts");
    }

    $migration->displayMessage(sprintf(__('Data migration - %s'), 'glpi_displaypreferences'));

    $ADDTODISPLAYPREF['SavedSearch'] = [8, 9, 3, 10, 11];
    foreach ($ADDTODISPLAYPREF as $type => $tab) {
        $rank = 1;
        foreach ($tab as $newval) {
            $DB->updateOrInsert("glpi_displaypreferences", [
                'rank'      => $rank++
            ], [
                'users_id'  => "0",
                'itemtype'  => $type,
                'num'       => $newval,
            ]);
        }
    }

    if (countElementsInTable('glpi_logs') < 2000000) {
       //add index only if this sounds... possible.
        $migration->addKey("glpi_logs", "id_search_option");
    } else {
       //Just display a Warning to the user.
        $migration->displayWarning("An index must be added in the 'id_search_option' field " .
         "of the 'glpi_logs table'; but your glpi_logs table is " .
         "too huge. You'll have to add it on your database " .
         "with the following query:\n" .
         "'ALTER TABLE glpi_logs ADD INDEX id_search_option(id_search_option);'");
    }

   // count cron task
    if (
        !countElementsInTable(
            'glpi_crontasks',
            ['itemtype' => 'SavedSearch', 'name' => 'countAll']
        )
    ) {
        $DB->insertOrDie(
            "glpi_crontasks",
            [
                'itemtype'        => "SavedSearch",
                'name'            => "countAll",
                'frequency'       => "604800",
                'param'           => null,
                'state'           => "0",
                'mode'            => "1",
                'allowmode'       => "3",
                'hourmin'         => "0",
                'hourmax'         => "24",
                'logs_lifetime'   => "30",
                'lastrun'         => null,
                'lastcode'        => null,
                'comment'         => null
            ],
            "9.2 Add countAll SavedSearch cron task"
        );
    };

   // alerts cron task
    if (
        !countElementsInTable(
            'glpi_crontasks',
            ['itemtype' => 'SavedSearch_Alert', 'name' => 'savedsearchesalerts']
        )
    ) {
        $DB->insertOrDie(
            "glpi_crontasks",
            [
                'itemtype'        => "SavedSearch_Alert",
                'name'            => "savedsearchesalerts",
                'frequency'       => "86400",
                'param'           => null,
                'state'           => "0",
                'mode'            => "1",
                'allowmode'       => "3",
                'hourmin'         => "0",
                'hourmax'         => "24",
                'logs_lifetime'   => "30",
                'lastrun'         => null,
                'lastcode'        => null,
                'comment'         => null
            ],
            "9.2 Add saved searches alerts cron task"
        );
    }

    if (
        !countElementsInTable(
            'glpi_notifications',
            ['itemtype' => 'SavedSearch_Alert']
        )
    ) {
        $DB->insertOrDie(
            "glpi_notifications",
            [
                'id'              => null,
                'name'            => "Saved searches",
                'entities_id'     => "0",
                'itemtype'        => "SavedSearch_Alert",
                'event'           => "alert",
                'comment'         => "",
                'is_recursive'    => "1",
                'is_active'       => "1",
                'date_creation'   => new \QueryExpression("NOW()"),
                'date_mod'        => new \QueryExpression("NOW()")
            ],
            "9.2 Add saved search alerts notification"
        );
        $notid = $DB->insertId();

        $DB->insertOrDie(
            "glpi_notificationtemplates",
            [
                'name'            => "Saved searches alerts",
                'itemtype'        => "SavedSearch_Alert",
                'date_mod'        => new \QueryExpression("NOW()")
            ],
            "9.2 Add saved search alerts notification template"
        );
        $nottid = $DB->insertId();

        $where =  [
            'notifications_id'         => $notid,
            'mode'                     => Notification_NotificationTemplate::MODE_MAIL,
            'notificationtemplates_id' => $nottid
        ];
        if (countElementsInTable('glpi_notifications_notificationtemplates', $where)) {
            $DB->updateOrInsert("glpi_notifications_notificationtemplates", [
                'id'                       => null
            ], [
                'notifications_id'         => $notid,
                'mode'                     => Notification_NotificationTemplate::MODE_MAIL,
                'notificationtemplates_id' => $nottid
            ]);
        }

        $DB->insertOrDie(
            "glpi_notificationtargets",
            [
                'id'               => null,
                'items_id'         => "19",
                'type'             => "1",
                'notifications_id' => $notid
            ],
            "9.2 Add saved search alerts notification targets"
        );

        $query = "INSERT INTO `glpi_notificationtemplatetranslations`
                       (`notificationtemplates_id`, `language`,`subject`,
                              `content_text`,
                              `content_html`)
                     VALUES ($notid, '', '##savedsearch.action## ##savedsearch.name##',
                     '##savedsearch.type## ###savedsearch.id## - ##savedsearch.name##

##savedsearch.message##

##lang.savedsearch.url##
##savedsearch.url##

Regards,',
                     '&lt;table&gt;
                     &lt;tbody&gt;
                     &lt;tr&gt;&lt;th colspan=\"2\"&gt;&lt;a href=\"##savedsearch.url##\"&gt;##savedsearch.type## ###savedsearch.id## - ##savedsearch.name##&lt;/a&gt;&lt;/th&gt;&lt;/tr&gt;
                     &lt;tr&gt;&lt;td colspan=\"2\"&gt;&lt;a href=\"##savedsearch.url##\"&gt;##savedsearch.message##&lt;/a&gt;&lt;/td&gt;&lt;/tr&gt;
                     &lt;tr&gt;
                     &lt;td&gt;##lang.savedsearch.url##&lt;/td&gt;
                     &lt;td&gt;##savedsearch.url##&lt;/td&gt;
                     &lt;/tr&gt;
                     &lt;/tbody&gt;
                     &lt;/table&gt;
                     &lt;p&gt;&lt;span style=\"font-size: small;\"&gt;Hello &lt;br /&gt;Regards,&lt;/span&gt;&lt;/p&gt;')";

        $DB->queryOrDie($query, "9.2 add saved searches alerts notification translation");
    }

   // Create a dedicated token for api
    if (!$DB->fieldExists('glpi_users', 'api_token')) {
        $migration->addField('glpi_users', 'api_token', 'string', ['after' => 'personal_token_date']);
        $migration->addField('glpi_users', 'api_token_date', 'datetime', ['after' => 'api_token']);
        $migration->displayWarning(
            "Api users tokens has been reset, if you use REST/XMLRPC api with personal token for authentication, please reset your user's token.",
            true
        );
    }

    if (!$DB->tableExists('glpi_items_operatingsystems')) {
        $query = "CREATE TABLE `glpi_items_operatingsystems` (
                  `id` int NOT NULL AUTO_INCREMENT,
                  `items_id` int NOT NULL DEFAULT '0',
                  `itemtype` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
                  `operatingsystems_id` int NOT NULL DEFAULT '0',
                  `operatingsystemversions_id` int NOT NULL DEFAULT '0',
                  `operatingsystemservicepacks_id` int NOT NULL DEFAULT '0',
                  `operatingsystemarchitectures_id` int NOT NULL DEFAULT '0',
                  `operatingsystemkernelversions_id` int NOT NULL DEFAULT '0',
                  `license_number` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
                  `license_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
                  `operatingsystemeditions_id` int NOT NULL DEFAULT '0',
                  `date_mod` datetime DEFAULT NULL,
                  `date_creation` datetime DEFAULT NULL,
                  PRIMARY KEY (`id`),
                  KEY `items_id` (`items_id`),
                  KEY `item` (`itemtype`,`items_id`),
                  KEY `operatingsystems_id` (`operatingsystems_id`),
                  KEY `operatingsystemservicepacks_id` (`operatingsystemservicepacks_id`),
                  KEY `operatingsystemversions_id` (`operatingsystemversions_id`),
                  KEY `operatingsystemarchitectures_id` (`operatingsystemarchitectures_id`),
                  KEY `operatingsystemkernelversions_id` (`operatingsystemkernelversions_id`),
                  KEY `operatingsystemeditions_id` (`operatingsystemeditions_id`),
                  UNIQUE KEY `unicity`(`items_id`,`itemtype`, `operatingsystems_id`,
                                       `operatingsystemarchitectures_id`)
                ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
        $DB->queryOrDie($query, "9.2 add table glpi_items_operatingsystems");
    }

    if (!$DB->tableExists('glpi_operatingsystemkernels')) {
        $query = "CREATE TABLE `glpi_operatingsystemkernels` (
                  `id` int NOT NULL AUTO_INCREMENT,
                  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
                  `comment` text COLLATE utf8_unicode_ci,
                  `date_mod` datetime DEFAULT NULL,
                  `date_creation` datetime DEFAULT NULL,
                  PRIMARY KEY (`id`),
                  KEY `name` (`name`)
                ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
        $DB->queryOrDie($query, "9.2 add table glpi_operatingsystemkernels");
    }

    if (!$DB->tableExists('glpi_operatingsystemkernelversions')) {
        $query = "CREATE TABLE `glpi_operatingsystemkernelversions` (
                  `id` int NOT NULL AUTO_INCREMENT,
                  `operatingsystemkernels_id` int NOT NULL DEFAULT '0',
                  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
                  `comment` text COLLATE utf8_unicode_ci,
                  `date_mod` datetime DEFAULT NULL,
                  `date_creation` datetime DEFAULT NULL,
                  PRIMARY KEY (`id`),
                  KEY `name` (`name`),
                  KEY `operatingsystemkernels_id` (`operatingsystemkernels_id`)
                ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
        $DB->queryOrDie($query, "9.2 add table glpi_operatingsystemversions");
    }

    if (!$DB->tableExists('glpi_operatingsystemeditions')) {
        $query = "CREATE TABLE `glpi_operatingsystemeditions` (
                  `id` int NOT NULL AUTO_INCREMENT,
                  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
                  `comment` text COLLATE utf8_unicode_ci,
                  `date_mod` datetime DEFAULT NULL,
                  `date_creation` datetime DEFAULT NULL,
                  PRIMARY KEY (`id`),
                  KEY `name` (`name`)
                ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
        $DB->queryOrDie($query, "9.2 add table glpi_operatingsystemeditions");
    }

    if ($DB->fieldExists('glpi_computers', 'operatingsystems_id')) {
       //migrate data from computers table, and drop old fields
       // TODO can be done when DB::updateOrInsert() supports SELECT
        $query = "REPLACE INTO `glpi_items_operatingsystems`
                       (`itemtype`, `items_id`, `operatingsystems_id`, `operatingsystemversions_id`,
                        `operatingsystemservicepacks_id`, `operatingsystemarchitectures_id`,
                        `license_number`, `license_id`)
                       SELECT 'Computer', `id`, `operatingsystems_id`, `operatingsystemversions_id`,
                              `operatingsystemservicepacks_id`, `operatingsystemarchitectures_id`,
                              `os_license_number`, `os_licenseid`
                FROM `glpi_computers`
                WHERE `operatingsystems_id` != 0
                      OR `operatingsystemservicepacks_id` != 0
                      OR `operatingsystemarchitectures_id` != 0
                      OR `os_license_number` IS NOT NULL
                      OR `os_kernel_version` IS NOT NULL
                      OR `os_licenseid` IS NOT NULL";
        $DB->queryOrDie($query, "9.2 migrate main operating system information");

       //migrate kernel versions.
        $kver = new OperatingSystemKernelVersion();
        $mapping = [];
        foreach (
            $DB->request(['SELECT' => ['id', 'os_kernel_version'],
                'FROM'   => 'glpi_computers',
                'NOT'   => ['os_kernel_version' => null]
            ]) as $data
        ) {
            $key = md5($data['os_kernel_version']);
            if (!isset($mapping[$key])) {
                $mapping[$key] = [];
            }
            $kver->add(['version' => $DB->escape($data['os_kernel_version'])]);
            $mapping[$key][$data['id']] = $kver->getID();
        }

        foreach ($mapping as $map) {
            foreach ($map as $computers_id => $kver_id) {
                $DB->updateOrDie(
                    "glpi_items_operatingsystems",
                    ['operatingsystemkernelversions_id' => $kver_id],
                    [
                        'itemtype' => "Computer",
                        'items_id' => $computers_id
                    ]
                );
            }
        }

        $migration->dropKey('glpi_computers', 'operatingsystems_id');
        $migration->dropField('glpi_computers', 'operatingsystems_id');
        $migration->dropKey('glpi_computers', 'operatingsystemservicepacks_id');
        $migration->dropField('glpi_computers', 'operatingsystemservicepacks_id');
        $migration->dropKey('glpi_computers', 'operatingsystemversions_id');
        $migration->dropField('glpi_computers', 'operatingsystemversions_id');
        $migration->dropKey('glpi_computers', 'operatingsystemarchitectures_id');
        $migration->dropField('glpi_computers', 'operatingsystemarchitectures_id');
        $migration->dropField('glpi_computers', 'os_license_number');
        $migration->dropField('glpi_computers', 'os_licenseid');
        $migration->dropField('glpi_computers', 'os_kernel_version');
    }

    if (!$DB->fieldExists('glpi_items_operatingsystems', 'is_deleted')) {
        $migration->addField(
            'glpi_items_operatingsystems',
            'is_deleted',
            "tinyint NOT NULL DEFAULT '0'"
        );
        $migration->addKey('glpi_items_operatingsystems', 'is_deleted');
    }

    if (!$DB->fieldExists('glpi_items_operatingsystems', 'is_dynamic')) {
        $migration->addField(
            'glpi_items_operatingsystems',
            'is_dynamic',
            "tinyint NOT NULL DEFAULT '0'"
        );
        $migration->addKey('glpi_items_operatingsystems', 'is_dynamic');
    }

    if (!$DB->fieldExists('glpi_items_operatingsystems', 'entities_id')) {
        $migration->addField(
            'glpi_items_operatingsystems',
            'entities_id',
            "int NOT NULL DEFAULT '0'"
        );
        $migration->addKey('glpi_items_operatingsystems', 'entities_id');
    }

   //add db version
    $migration->addConfig(['dbversion' => '9.1.3']);

   // Add certificates management
    if (!$DB->tableExists('glpi_certificates')) {
        $query = "CREATE TABLE `glpi_certificates` (
        `id` INT NOT NULL AUTO_INCREMENT,
        `name` VARCHAR(255) COLLATE utf8_unicode_ci  DEFAULT NULL,
        `serial` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
        `otherserial` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
        `entities_id` INT NOT NULL DEFAULT '0',
        `is_recursive` TINYINT NOT NULL DEFAULT '0',
        `comment` text COLLATE utf8_unicode_ci,
        `is_deleted` tinyint NOT NULL DEFAULT '0',
        `is_template` tinyint NOT NULL DEFAULT '0',
        `template_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
        `certificatetypes_id`  INT NOT NULL DEFAULT '0' COMMENT 'RELATION to glpi_certificatetypes (id)',
        `dns_name` VARCHAR(255) COLLATE utf8_unicode_ci  DEFAULT NULL,
        `dns_suffix` VARCHAR(255) COLLATE utf8_unicode_ci  DEFAULT NULL,
        `users_id_tech` INT NOT NULL DEFAULT '0' COMMENT 'RELATION to glpi_users (id)',
        `groups_id_tech` INT NOT NULL DEFAULT '0' COMMENT 'RELATION to glpi_groups (id)',
        `locations_id` INT NOT NULL DEFAULT '0' COMMENT 'RELATION to glpi_locations (id)',
        `manufacturers_id` INT NOT NULL DEFAULT '0' COMMENT 'RELATION to glpi_manufacturers (id)',
        `users_id` int NOT NULL DEFAULT '0',
        `groups_id` int NOT NULL DEFAULT '0',
        `is_autosign` TINYINT NOT NULL DEFAULT '0',
        `date_expiration` DATE DEFAULT NULL,
        `states_id` INT NOT NULL DEFAULT '0' COMMENT 'RELATION to states (id)',
        `command` TEXT COLLATE utf8_unicode_ci,
        `certificate_request` TEXT COLLATE utf8_unicode_ci,
        `certificate_item` TEXT COLLATE utf8_unicode_ci,
        `date_creation` DATETIME DEFAULT NULL,
        `date_mod` DATETIME DEFAULT NULL,
        PRIMARY KEY (`id`),
        KEY `name` (`name`),
        KEY `entities_id` (`entities_id`),
        KEY `is_template` (`is_template`),
        KEY `is_deleted` (`is_deleted`),
        KEY `certificatetypes_id` (`certificatetypes_id`),
        KEY `users_id_tech` (`users_id_tech`),
        KEY `groups_id_tech` (`groups_id_tech`),
        KEY `groups_id` (`groups_id`),
        KEY `users_id` (`users_id`),
        KEY `locations_id` (`locations_id`),
        KEY `manufacturers_id` (`manufacturers_id`),
        KEY `states_id` (`states_id`),
        KEY `date_creation` (`date_creation`),
        KEY `date_mod` (`date_mod`)
      ) ENGINE = MyISAM DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci";
        $DB->queryOrDie($query, "9.2 copy add certificate table");
    }

    if (!$DB->tableExists('glpi_certificates_items')) {
        $query = "CREATE TABLE `glpi_certificates_items` (
           `id` INT NOT NULL AUTO_INCREMENT,
           `certificates_id` INT NOT NULL DEFAULT '0',
           `items_id` INT NOT NULL DEFAULT '0' COMMENT 'RELATION to various tables, according to itemtype (id)',
           `itemtype` VARCHAR(100) COLLATE utf8_unicode_ci NOT NULL COMMENT 'see .class.php file',
           `date_creation` DATETIME DEFAULT NULL,
           `date_mod` DATETIME DEFAULT NULL,
           PRIMARY KEY (`id`),
           UNIQUE KEY `unicity` (`certificates_id`, `itemtype`, `items_id`),
           KEY `device` (`items_id`, `itemtype`),
           KEY `item` (`itemtype`, `items_id`),
           KEY `date_creation` (`date_creation`),
           KEY `date_mod` (`date_mod`)
        ) ENGINE = MyISAM DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci";
        $DB->queryOrDie($query, "9.2 copy add certificate items table");
    }

    if (!$DB->tableExists('glpi_certificatetypes')) {
        $query = "CREATE TABLE `glpi_certificatetypes` (
           `id` INT NOT NULL AUTO_INCREMENT,
           `entities_id` INT NOT NULL DEFAULT '0',
           `is_recursive` TINYINT NOT NULL DEFAULT '0',
           `name` VARCHAR(255) COLLATE utf8_unicode_ci DEFAULT NULL,
           `comment` TEXT COLLATE utf8_unicode_ci,
           `date_creation` DATETIME DEFAULT NULL,
           `date_mod` DATETIME DEFAULT NULL,
           PRIMARY KEY (`id`),
           KEY `entities_id` (`entities_id`),
           KEY `is_recursive` (`is_recursive`),
           KEY `name` (`name`),
           KEY `date_creation` (`date_creation`),
           KEY `date_mod` (`date_mod`)
        ) ENGINE = MyISAM DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci";
        $DB->queryOrDie($query, "9.2 copy add certificate type table");
    }

    if (countElementsInTable("glpi_profilerights", ['name' => 'certificate']) == 0) {
       //new right for certificate
       //give full rights to profiles having config right
        foreach ($DB->request("glpi_profilerights", "`name` = 'config'") as $profrights) {
            if ($profrights['rights'] && (READ + UPDATE)) {
                $rightValue = CREATE | READ | UPDATE | DELETE  | PURGE | READNOTE | UPDATENOTE | UNLOCK;
            } else {
                $rightValue = 0;
            }

            $DB->insertOrDie(
                "glpi_profilerights",
                [
                    'id'           => null,
                    'profiles_id'  => $profrights['profiles_id'],
                    'name'         => "certificate",
                    'rights'       => $rightValue,
                ],
                "9.2 add right for certificates"
            );
        }
    }

   // add alert for certificates
    $migration->addField(
        "glpi_entities",
        'use_certificates_alert',
        "integer",
        ['value' => -2,
            'after' => 'send_licenses_alert_before_delay'
        ]
    );
    $migration->addField(
        "glpi_entities",
        'send_certificates_alert_before_delay',
        "integer",
        ['value'     => -2,
            'after'     => 'use_certificates_alert',
            'update'    => '0', // No delay for root entity
            'condition' => 'WHERE `id` = 0'
        ]
    );
    CronTask::register(
        'Certificate',
        'certificate',
        DAY_TIMESTAMP,
        [
            'comment' => '',
            'mode'    => CronTask::MODE_INTERNAL
        ]
    );
    if (!countElementsInTable('glpi_notifications', ['itemtype' => 'Certificate'])) {
        $DB->insertOrDie(
            "glpi_notifications",
            [
                'id'              => null,
                'name'            => "Certificates",
                'entities_id'     => "0",
                'itemtype'        => "Certificate",
                'event'           => "alert",
                'comment'         => "",
                'is_recursive'    => "1",
                'is_active'       => "1",
                'date_creation'   => new \QueryExpression("NOW()"),
                'date_mod'        => new \QueryExpression("NOW()")
            ],
            "9.2 Add certificate alerts notification"
        );
        $notid = $DB->insertId();

        $DB->insertOrDie(
            "glpi_notificationtemplates",
            [
                'name'      => "Certificates",
                'itemtype'  => "Certificate",
                'date_mod'  => new \QueryExpression("NOW()")
            ],
            "9.2 Add certifcate alerts notification template"
        );
        $nottid = $DB->insertId();

        $where = [
            'notifications_id'         => $notid,
            'mode'                     => Notification_NotificationTemplate::MODE_MAIL,
            'notificationtemplates_id' => $nottid
        ];
        if (!countElementsInTable('glpi_notifications_notificationtemplates', $where)) {
            $DB->updateOrInsert("glpi_notifications_notificationtemplates", [
                'id'                       => null
            ], [
                'notifications_id'         => $notid,
                'mode'                     => Notification_NotificationTemplate::MODE_MAIL,
                'notificationtemplates_id' => $nottid,
            ]);
        }

        $query = "INSERT INTO `glpi_notificationtemplatetranslations`
                  (`notificationtemplates_id`, `language`, `subject`, `content_text`, `content_html`)
                VALUES ($notid, '', '##certificate.action##  ##certificate.entity##',
                        '##lang.certificate.entity## : ##certificate.entity##

##FOREACHcertificates##

##lang.certificate.serial## : ##certificate.serial##

##lang.certificate.expirationdate## : ##certificate.expirationdate##

##certificate.url##
 ##ENDFOREACHcertificates##','&lt;p&gt;
##lang.certificate.entity## : ##certificate.entity##&lt;br /&gt;
##FOREACHcertificates##
&lt;br /&gt;##lang.certificate.name## : ##certificate.name##&lt;br /&gt;
##lang.certificate.serial## : ##certificate.serial##&lt;br /&gt;
##lang.certificate.expirationdate## : ##certificate.expirationdate##
&lt;br /&gt; &lt;a href=\"##certificate.url##\"&gt; ##certificate.url##
&lt;/a&gt;&lt;br /&gt; ##ENDFOREACHcertificates##&lt;/p&gt;')";

        $DB->queryOrDie($query, "9.2 add certificates alerts notification translation");

        $DB->insertOrDie(
            "glpi_notificationtargets",
            [
                'id'                 => null,
                'notifications_id'   => $notid,
                'type'               => Notification::USER_TYPE,
                'items_id'           => Notification::ITEM_TECH_IN_CHARGE
            ],
            "9.2 add certificates alerts notification target"
        );
        $DB->insertOrDie(
            "glpi_notificationtargets",
            [
                'id'                 => null,
                'notifications_id'   => $notid,
                'type'               => Notification::USER_TYPE,
                'items_id'           => Notification::ITEM_TECH_GROUP_IN_CHARGE
            ],
            "9.2 add certificates alerts notification target"
        );
    }

   /************** Simcard component **************/
    $migration->addField("glpi_states", "is_visible_line", "bool", ["after" => "is_visible_softwarelicense"]);

    if (!$DB->tableExists('glpi_lineoperators')) {
        $query = "CREATE TABLE IF NOT EXISTS `glpi_lineoperators` (
                   `id` int NOT NULL AUTO_INCREMENT,
                   `name` varchar(255) NOT NULL DEFAULT '',
                   `comment` text COLLATE utf8_unicode_ci,
                   `mcc` int DEFAULT NULL,
                   `mnc` int DEFAULT NULL,
                   `entities_id`      INT NOT NULL DEFAULT 0,
                   `is_recursive`     TINYINT NOT NULL DEFAULT 0,
                   `date_mod` datetime DEFAULT NULL,
                   `date_creation` datetime DEFAULT NULL,
                   PRIMARY KEY (`id`),
                   KEY `name` (`name`),
                   KEY `entities_id`  (`entities_id`),
                   KEY `is_recursive` (`is_recursive`),
                   KEY `date_mod` (`date_mod`),
                   KEY `date_creation` (`date_creation`),
                   UNIQUE KEY `unicity` (`mcc`,`mnc`)
                ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;";
        $DB->queryOrDie($query, "9.2 add table glpi_lineoperators");
    }

    if (!$DB->tableExists('glpi_linetypes')) {
        $query = "CREATE TABLE IF NOT EXISTS `glpi_linetypes` (
         `id` int NOT NULL AUTO_INCREMENT,
         `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
         `comment` text COLLATE utf8_unicode_ci,
         `date_mod` datetime DEFAULT NULL,
         `date_creation` datetime DEFAULT NULL,
         PRIMARY KEY (`id`),
         KEY `name` (`name`),
         KEY `date_mod` (`date_mod`),
         KEY `date_creation` (`date_creation`)
         ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;";
        $DB->queryOrDie($query, "9.2 add table glpi_linetypes");
    }

    if (!$DB->tableExists('glpi_lines')) {
        $query = "CREATE TABLE `glpi_lines` (
            `id`                   INT NOT NULL auto_increment,
            `name`                 VARCHAR(255) NOT NULL DEFAULT '',
            `entities_id`          INT NOT NULL DEFAULT 0,
            `is_recursive`         TINYINT NOT NULL DEFAULT 0,
            `is_deleted`           TINYINT NOT NULL DEFAULT 0,
            `caller_num`           VARCHAR(255) NOT NULL DEFAULT '',
            `caller_name`          VARCHAR(255) NOT NULL DEFAULT '',
            `users_id`             INT NOT NULL DEFAULT 0,
            `groups_id`            INT NOT NULL DEFAULT 0,
            `lineoperators_id`     INT NOT NULL DEFAULT 0,
            `locations_id`         INT NOT NULL DEFAULT '0',
            `states_id`            INT NOT NULL DEFAULT '0',
            `linetypes_id`         INT NOT NULL DEFAULT '0',
            `date_creation`        DATETIME DEFAULT NULL,
            `date_mod`             DATETIME DEFAULT NULL,
            `comment`              TEXT DEFAULT NULL,
            PRIMARY KEY            (`id`),
            KEY `entities_id`      (`entities_id`),
            KEY `is_recursive`     (`is_recursive`),
            KEY `users_id`         (`users_id`),
            KEY `lineoperators_id` (`lineoperators_id`)
            ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
        $DB->queryOrDie($query, "9.2 add table glpi_lines");
    }

    if (!$DB->tableExists('glpi_devicesimcardtypes')) {
        $query = "CREATE TABLE IF NOT EXISTS `glpi_devicesimcardtypes` (
                  `id` int NOT NULL AUTO_INCREMENT,
                  `name` varchar(255) NOT NULL DEFAULT '',
                  `comment` text COLLATE utf8_unicode_ci,
                  `date_mod` datetime DEFAULT NULL,
                  `date_creation` datetime DEFAULT NULL,
                  PRIMARY KEY (`id`),
                  KEY `name` (`name`),
                  KEY `date_mod` (`date_mod`),
                  KEY `date_creation` (`date_creation`)
                ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;";
        $DB->queryOrDie($query, "9.2 add table glpi_devicesimcardtypes");
    }

    if (!countElementsInTable('glpi_devicesimcardtypes', ['name' => 'Full SIM'])) {
        $DB->insertOrDie("glpi_devicesimcardtypes", [
            'id'              => null,
            'name'            => "Full SIM",
            'comment'         => null,
            'date_mod'        => null,
            'date_creation'   => null
        ]);
    }
    if (!countElementsInTable('glpi_devicesimcardtypes', ['name' => 'Mini SIM'])) {
        $DB->insertOrDie("glpi_devicesimcardtypes", [
            'id'              => null,
            'name'            => "Mini SIM",
            'comment'         => null,
            'date_mod'        => null,
            'date_creation'   => null
        ]);
    }
    if (!countElementsInTable('glpi_devicesimcardtypes', ['name' => 'Micro SIM'])) {
        $DB->insertOrDie("glpi_devicesimcardtypes", [
            'id'              => null,
            'name'            => "Micro SIM",
            'comment'         => null,
            'date_mod'        => null,
            'date_creation'   => null
        ]);
    }
    if (!countElementsInTable('glpi_devicesimcardtypes', ['name' => 'Nano SIM'])) {
        $DB->insertOrDie("glpi_devicesimcardtypes", [
            'id'              => null,
            'name'            => "Nano SIM",
            'comment'         => null,
            'date_mod'        => null,
            'date_creation'   => null
        ]);
    }

    if (!$DB->tableExists('glpi_devicesimcards')) {
        $query = "CREATE TABLE IF NOT EXISTS `glpi_devicesimcards` (
               `id` int NOT NULL AUTO_INCREMENT,
               `designation` varchar(255) DEFAULT NULL,
               `comment` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL,
               `entities_id` int NOT NULL DEFAULT '0',
               `is_recursive` tinyint NOT NULL DEFAULT '0',
               `manufacturers_id` int NOT NULL DEFAULT '0',
               `voltage` int DEFAULT NULL,
               `devicesimcardtypes_id` int NOT NULL DEFAULT '0',
               `date_mod` datetime DEFAULT NULL,
               `date_creation` datetime DEFAULT NULL,
               `allow_voip` tinyint NOT NULL DEFAULT '0',
               PRIMARY KEY (`id`),
               KEY `designation` (`designation`),
               KEY `entities_id` (`entities_id`),
               KEY `is_recursive` (`is_recursive`),
               KEY `devicesimcardtypes_id` (`devicesimcardtypes_id`),
               KEY `date_mod` (`date_mod`),
               KEY `date_creation` (`date_creation`),
               KEY `manufacturers_id` (`manufacturers_id`)
            ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
        $DB->queryOrDie($query, "9.2 add table glpi_devicesimcards");
    }

    if (!$DB->tableExists('glpi_items_devicesimcards')) {
        $query = "CREATE TABLE IF NOT EXISTS `glpi_items_devicesimcards` (
                  `id` int NOT NULL AUTO_INCREMENT,
                  `items_id` int NOT NULL DEFAULT '0' COMMENT 'RELATION to various table, according to itemtype (id)',
                  `itemtype` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
                  `devicesimcards_id` int NOT NULL DEFAULT '0',
                  `is_deleted` tinyint NOT NULL DEFAULT '0',
                  `is_dynamic` tinyint NOT NULL DEFAULT '0',
                  `entities_id` int NOT NULL DEFAULT '0',
                  `serial` varchar(255) NULL DEFAULT NULL,
                  `otherserial` varchar(255) NULL DEFAULT NULL,
                  `states_id` int NOT NULL DEFAULT '0',
                  `locations_id` int NOT NULL DEFAULT '0',
                  `lines_id` int NOT NULL DEFAULT '0',
                  `pin` varchar(255) NOT NULL DEFAULT '',
                  `pin2` varchar(255) NOT NULL DEFAULT '',
                  `puk` varchar(255) NOT NULL DEFAULT '',
                  `puk2` varchar(255) NOT NULL DEFAULT '',
                  PRIMARY KEY (`id`),
                  KEY `item` (`itemtype`,`items_id`),
                  KEY `devicesimcards_id` (`devicesimcards_id`),
                  KEY `is_deleted` (`is_deleted`),
                  KEY `is_dynamic` (`is_dynamic`),
                  KEY `entities_id` (`entities_id`),
                  KEY `serial` (`serial`),
                  KEY `otherserial` (`otherserial`),
                  KEY `states_id` (`states_id`),
                  KEY `locations_id` (`locations_id`),
                  KEY `lines_id` (`lines_id`)
                ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
        $DB->queryOrDie($query, "9.2 add table glpi_items_devicesimcards");
    }

    if (countElementsInTable("glpi_profilerights", ['name' => 'line']) == 0) {
       //new right for line
       //give full rights to profiles having config right
        foreach ($DB->request("glpi_profilerights", "`name` = 'config'") as $profrights) {
            if ($profrights['rights'] && (READ + UPDATE)) {
                $rightValue = CREATE | READ | UPDATE | DELETE | PURGE | READNOTE | UPDATENOTE;
            } else {
                $rightValue = 0;
            }
            $DB->insertOrDie(
                "glpi_profilerights",
                [
                    'id'           => null,
                    'profiles_id'  => $profrights['profiles_id'],
                    'name'         => "line",
                    'rights'       => $rightValue
                ],
                "9.2 add right for line"
            );
        }
    }

    if (countElementsInTable("glpi_profilerights", ['name' => 'lineoperator']) == 0) {
       //new right for lineoperator
       //give full rights to profiles having config right
        foreach ($DB->request("glpi_profilerights", "`name` = 'config'") as $profrights) {
            if ($profrights['rights'] && (READ + UPDATE)) {
                $rightValue = CREATE | READ | UPDATE | DELETE | PURGE;
            } else {
                $rightValue = 0;
            }
            $DB->insertOrDie(
                "glpi_profilerights",
                [
                    'id'           => null,
                    'profiles_id'  => $profrights['profiles_id'],
                    'name'         => "lineoperator",
                    'rights'       => $rightValue
                ],
                "9.2 add right for lineoperator"
            );
        }
    }

    if (countElementsInTable("glpi_profilerights", ['name' => 'devicesimcard_pinpuk']) == 0) {
       //new right for simcard pin and puk
       //give full rights to profiles having config right
        foreach ($DB->request("glpi_profilerights", "`name` = 'config'") as $profrights) {
            if ($profrights['rights'] && (READ + UPDATE)) {
                $rightValue = READ | UPDATE;
            } else {
                $rightValue = 0;
            }
            $DB->insertOrDie(
                "glpi_profilerights",
                [
                    'id'           => null,
                    'profiles_id'  => $profrights['profiles_id'],
                    'name'         => "devicesimcard_pinpuk",
                    'rights'       => $rightValue
                ],
                "9.2 add right for simcards pin and puk codes"
            );
        }
    }

   //Firmware for phones
    if ($DB->fieldExists('glpi_phones', 'firmware')) {
        $iterator = $DB->request([
            'SELECT' => ['id', 'firmware'],
            'FROM'   => 'glpi_phones',
            'NOT'    => ['firmware' => null]
        ]);

        $firmwares = [];
        foreach ($iterator as $row) {
            if (!isset($firmwares[$row['firmware']])) {
                $fw = new DeviceFirmware();
                if ($fw->getFromDBByCrit(['designation' => $DB->escape($row['firmware'])])) {
                    $firmwares[$row['firmware']] = $fw->getID();
                } else {
                    $id = $fw->add([
                        'designation'              => $DB->escape($row['firmware']),
                        'devicefirmwaretypes_id'   => '3' //type "firmware"
                    ]);
                    $firmwares[$row['firmware']] = $id;
                }
            }

            //add link
            $item_fw = new Item_DeviceFirmware();
            $item_fw->add([
                'itemtype'           => 'Phone',
                'items_id'           => $row['id'],
                'devicefirmwares_id' => $firmwares[$row['firmware']]
            ]);
        }

        $migration->dropField('glpi_phones', 'firmware');
    }

   //Firmware for network equipements
    if ($DB->tableExists('glpi_networkequipmentfirmwares')) {
        $mapping = [];
        $iterator = $DB->request('glpi_networkequipmentfirmwares');
        foreach ($iterator as $row) {
            $fw = new DeviceFirmware();
            $id = $fw->add([
                'designation'              => $DB->escape($row['name']),
                'comment'                  => $DB->escape($row['comment']),
                'devicefirmwaretypes_id'   => 3, //type "Firmware"
                'date_creation'            => $row['date_creation'],
                'date_mod'                 => $row['date_mod']
            ]);
            $mapping[$row['id']] = $id;
        }

        $iterator = $DB->request('glpi_networkequipments');
        foreach ($iterator as $row) {
            if (isset($mapping[$row['networkequipmentfirmwares_id']])) {
                $itemdevice = new Item_DeviceFirmware();
                $itemdevice->add([
                    'itemtype'           => 'NetworkEquipment',
                    'items_id'           => $row['id'],
                    'devicefirmwares_id' => $mapping[$row['networkequipmentfirmwares_id']]
                ]);
            }
        }

        $migration->dropKey('glpi_networkequipments', 'networkequipmentfirmwares_id');
        $migration->dropField('glpi_networkequipments', 'networkequipmentfirmwares_id');
        $migration->dropTable('glpi_networkequipmentfirmwares');
    }

   // add projecttemplate
    if (!$DB->fieldExists('glpi_projects', 'projecttemplates_id')) {
        $migration->addField("glpi_projects", "projecttemplates_id", "integer");
        $migration->addField("glpi_projects", "is_template", "bool");
        $migration->addField("glpi_projects", "template_name", "string");
        $migration->addKey("glpi_projects", "projecttemplates_id");
    }

    if (!$DB->fieldExists('glpi_projecttasks', 'projecttasktemplates_id')) {
        $migration->addField("glpi_projecttasks", "projecttasktemplates_id", "integer");
        $migration->addField("glpi_projecttasks", "is_template", "bool");
        $migration->addField("glpi_projecttasks", "template_name", "string");
        $migration->addKey("glpi_projecttasks", "projecttasktemplates_id");
    }

    if (!$DB->tableExists('glpi_projecttasktemplates')) {
        $query = "CREATE TABLE `glpi_projecttasktemplates` (
                       `id` int NOT NULL AUTO_INCREMENT,
                       `entities_id` int NOT NULL DEFAULT '0',
                       `is_recursive` tinyint NOT NULL DEFAULT '0',
                       `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
                       `description` longtext COLLATE utf8_unicode_ci,
                       `comment` longtext COLLATE utf8_unicode_ci,
                       `projects_id` int NOT NULL DEFAULT '0',
                       `projecttasks_id` int NOT NULL DEFAULT '0',
                       `plan_start_date` datetime DEFAULT NULL,
                       `plan_end_date` datetime DEFAULT NULL,
                       `real_start_date` datetime DEFAULT NULL,
                       `real_end_date` datetime DEFAULT NULL,
                       `planned_duration` int NOT NULL DEFAULT '0',
                       `effective_duration` int NOT NULL DEFAULT '0',
                       `projectstates_id` int NOT NULL DEFAULT '0',
                       `projecttasktypes_id` int NOT NULL DEFAULT '0',
                       `users_id` int NOT NULL DEFAULT '0',
                       `percent_done` int NOT NULL DEFAULT '0',
                       `is_milestone` tinyint NOT NULL DEFAULT '0',
                       `comments` text COLLATE utf8_unicode_ci,
                       `date_mod` datetime DEFAULT NULL,
                       `date_creation` datetime DEFAULT NULL,
                       PRIMARY KEY (`id`),
                       KEY `name` (`name`),
                       KEY `entities_id` (`entities_id`),
                       KEY `is_recursive` (`is_recursive`),
                       KEY `projects_id` (`projects_id`),
                       KEY `projecttasks_id` (`projecttasks_id`),
                       KEY `date_creation` (`date_creation`),
                       KEY `date_mod` (`date_mod`),
                       KEY `users_id` (`users_id`),
                       KEY `plan_start_date` (`plan_start_date`),
                       KEY `plan_end_date` (`plan_end_date`),
                       KEY `real_start_date` (`real_start_date`),
                       KEY `real_end_date` (`real_end_date`),
                       KEY `percent_done` (`percent_done`),
                       KEY `projectstates_id` (`projectstates_id`),
                       KEY `projecttasktypes_id` (`projecttasktypes_id`),
                       KEY `is_milestone` (`is_milestone`)
                     ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;";
        $DB->queryOrDie($query, "9.2 add table glpi_projecttasktemplates");
    }

   //add editor in followupps
    if (!$DB->fieldExists('glpi_ticketfollowups', 'users_id_editor')) {
        $migration->addField("glpi_ticketfollowups", "users_id_editor", "int NOT NULL DEFAULT '0'", ['after' => 'users_id']);
        $migration->addKey("glpi_ticketfollowups", "users_id_editor");
    }

   //add editor in *tasks
    if (!$DB->fieldExists('glpi_tickettasks', 'users_id_editor')) {
        $migration->addField("glpi_tickettasks", "users_id_editor", "int NOT NULL DEFAULT '0'", ['after' => 'users_id']);
        $migration->addKey("glpi_tickettasks", "users_id_editor");
    }
    if (!$DB->fieldExists('glpi_changetasks', 'users_id_editor')) {
        $migration->addField("glpi_changetasks", "users_id_editor", "int NOT NULL DEFAULT '0'", ['after' => 'users_id']);
        $migration->addKey("glpi_changetasks", "users_id_editor");
    }
    if (!$DB->fieldExists('glpi_problemtasks', 'users_id_editor')) {
        $migration->addField("glpi_problemtasks", "users_id_editor", "int NOT NULL DEFAULT '0'", ['after' => 'users_id']);
        $migration->addKey("glpi_problemtasks", "users_id_editor");
    }

   //Add a new sync_field in LDAP configuration
    if (!$DB->fieldExists('glpi_authldaps', 'sync_field')) {
        $migration->addField("glpi_authldaps", "sync_field", "string", ['after' => 'login_field', 'null' => true]);
        $migration->addKey('glpi_authldaps', 'sync_field');
    }
   //Add a new sync_field for users
    if (!$DB->fieldExists('glpi_users', 'sync_field')) {
        $migration->addField("glpi_users", "sync_field", "string", ['null' => true]);
        $migration->addKey('glpi_users', 'sync_field');
    }

    $migration->addConfig([
        'smtp_max_retries'   => 5,
        'smtp_sender'        => 'NULL',
        'from_email'         => 'NULL',
        'from_email_name'    => 'NULL'
    ]);

   //register telemetry crontask
    CronTask::register(
        'Telemetry',
        'telemetry',
        MONTH_TIMESTAMP,
        [
            'comment'   => '',
            'mode'      => CronTask::MODE_INTERNAL,
            'state'     => CronTask::STATE_DISABLE
        ]
    );
    $migration->addConfig([
        'instance_uuid'      => Telemetry::generateInstanceUuid(),
        'registration_uuid'  => Telemetry::generateRegistrationUuid()
    ]);

    if (isIndex('glpi_authldaps', 'use_tls')) {
        $query = "ALTER TABLE `glpi_authldaps` DROP INDEX `use_tls`";
        $DB->queryOrDie($query, "9.2 drop index use_tls for glpi_authldaps");
    }

   //Fix some field order from old migrations
    $migration->migrationOneTable('glpi_states');
    $DB->queryOrDie("ALTER TABLE `glpi_budgets` CHANGE `date_creation` `date_creation` DATETIME NULL DEFAULT NULL AFTER `date_mod`");
    $DB->queryOrDie("ALTER TABLE `glpi_changetasks` CHANGE `groups_id_tech` `groups_id_tech` INT NOT NULL DEFAULT '0' AFTER `users_id_tech`");
    $DB->queryOrDie("ALTER TABLE `glpi_problemtasks` CHANGE `groups_id_tech` `groups_id_tech` INT NOT NULL DEFAULT '0' AFTER `users_id_tech`");
    $DB->queryOrDie("ALTER TABLE `glpi_tickettasks` CHANGE `groups_id_tech` `groups_id_tech` INT NOT NULL DEFAULT '0' AFTER `users_id_tech`");
    $DB->queryOrDie("ALTER TABLE `glpi_knowbaseitemcategories` CHANGE `sons_cache` `sons_cache` LONGTEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL AFTER `level`");
    $DB->queryOrDie("ALTER TABLE `glpi_requesttypes` CHANGE `is_followup_default` `is_followup_default` TINYINT NOT NULL DEFAULT '0' AFTER `is_helpdesk_default`");
    $DB->queryOrDie("ALTER TABLE `glpi_requesttypes` CHANGE `is_mailfollowup_default` `is_mailfollowup_default` TINYINT NOT NULL DEFAULT '0' AFTER `is_mail_default`");
    $DB->queryOrDie("ALTER TABLE `glpi_requesttypes` CHANGE `comment` `comment` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL AFTER `is_ticketfollowup`");
    $DB->queryOrDie("ALTER TABLE `glpi_requesttypes` CHANGE `date_mod` `date_mod` DATETIME NULL DEFAULT NULL AFTER `comment`");
    $DB->queryOrDie("ALTER TABLE `glpi_requesttypes` CHANGE `date_creation` `date_creation` DATETIME NULL DEFAULT NULL AFTER `date_mod`");
    $DB->queryOrDie("ALTER TABLE `glpi_groups` CHANGE `is_task` `is_task` TINYINT NOT NULL DEFAULT '1' AFTER `is_assign`");
    $DB->queryOrDie("ALTER TABLE `glpi_states` CHANGE `date_mod` `date_mod` DATETIME NULL DEFAULT NULL AFTER `is_visible_certificate`");
    $DB->queryOrDie("ALTER TABLE `glpi_states` CHANGE `date_creation` `date_creation` DATETIME NULL DEFAULT NULL AFTER `date_mod`");
    $DB->queryOrDie("ALTER TABLE `glpi_taskcategories` CHANGE `is_active` `is_active` TINYINT NOT NULL DEFAULT '1' AFTER `sons_cache`");
    $DB->queryOrDie("ALTER TABLE `glpi_users` CHANGE `palette` `palette` CHAR(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL AFTER `layout`");
    $DB->queryOrDie("ALTER TABLE `glpi_users` CHANGE `set_default_requester` `set_default_requester` TINYINT NULL DEFAULT NULL AFTER `ticket_timeline_keep_replaced_tabs`");
    $DB->queryOrDie("ALTER TABLE `glpi_users` CHANGE `plannings` `plannings` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL AFTER `highcontrast_css`");

   //Fix bad default values
    $DB->queryOrDie("ALTER TABLE `glpi_states` CHANGE `is_visible_softwarelicense` `is_visible_softwarelicense` TINYINT NOT NULL DEFAULT '1'");
    $DB->queryOrDie("ALTER TABLE `glpi_states` CHANGE `is_visible_line` `is_visible_line` TINYINT NOT NULL DEFAULT '1'");

   //Fields added in 0905_91 script but not in empty sql...
    if (!$DB->fieldExists('glpi_changetasks', 'date_creation', false)) {
        $migration->addField('glpi_changetasks', 'date_creation', 'datetime', ['after' => 'date_mod']);
        $migration->addKey('glpi_changetasks', 'date_creation');
    }
    if (!$DB->fieldExists('glpi_networkportfiberchannels', 'date_mod', false)) {
        $migration->addField('glpi_networkportfiberchannels', 'date_mod', 'datetime', ['after' => 'speed']);
        $migration->addKey('glpi_networkportfiberchannels', 'date_mod');
    }
    if (!$DB->fieldExists('glpi_networkportfiberchannels', 'date_creation', false)) {
        $migration->addField('glpi_networkportfiberchannels', 'date_creation', 'datetime', ['after' => 'date_mod']);
        $migration->addKey('glpi_networkportfiberchannels', 'date_creation');
    }
    if (!$DB->fieldExists('glpi_problemtasks', 'date_creation', false)) {
        $migration->addField('glpi_problemtasks', 'date_creation', 'datetime', ['after' => 'date_mod']);
        $migration->addKey('glpi_problemtasks', 'date_creation');
    }
    if (!$DB->fieldExists('glpi_slms', 'date_creation', false)) {
        $migration->addField('glpi_slms', 'date_creation', 'datetime', ['after' => 'date_mod']);
        $migration->addKey('glpi_slms', 'date_creation');
    }
    if (!$DB->fieldExists('glpi_ticketfollowups', 'date_creation', false)) {
        $migration->addField('glpi_ticketfollowups', 'date_creation', 'datetime', ['after' => 'date_mod']);
        $migration->addKey('glpi_ticketfollowups', 'date_creation');
    }
    if (!$DB->fieldExists('glpi_tickettasks', 'date_creation', false)) {
        $migration->addField('glpi_tickettasks', 'date_creation', 'datetime', ['after' => 'date_mod']);
        $migration->addKey('glpi_tickettasks', 'date_creation');
    }
    if (!$DB->fieldExists('glpi_softwarelicenses', 'contact_num', false)) {
        $migration->addField(
            "glpi_softwarelicenses",
            "contact_num",
            "varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL",
            ['after' => 'contact']
        );
    }

   //Fix comments...
    $DB->queryOrDie("ALTER TABLE `glpi_savedsearches` CHANGE `type` `type` INT NOT NULL DEFAULT '0' COMMENT 'see SavedSearch:: constants'");

   //Fix unicity...
    $tables = [
        'glpi_slalevels_tickets'   => ['tickets_id', 'slalevels_id'],
        'glpi_businesscriticities' => ['businesscriticities_id', 'name'],
        'glpi_documentcategories'  => ['documentcategories_id', 'name'],
        'glpi_olalevels_tickets'   => ['tickets_id', 'olalevels_id'],
        'glpi_states'              => ['states_id', 'name'],
        'glpi_tickets_tickets'     => ['tickets_id_1', 'tickets_id_2'],
        'glpi_tickettemplatehiddenfields'      => ['tickettemplates_id', 'num'],
        'glpi_tickettemplatemandatoryfields'   => ['tickettemplates_id', 'num']
    ];
    foreach ($tables as $table => $fields) {
        $add = true;
        $result = $DB->query("SHOW INDEX FROM `$table` WHERE Key_name='unicity'");
        if ($result && $DB->numrows($result)) {
            $row = $DB->fetchAssoc($result);
            if ($row['Non_unique'] == 1) {
                $migration->dropKey($table, 'unicity');
                $migration->migrationOneTable($table);
            } else {
                $add = false;
            }
        }

        if ($add) {
           //missing or bad unique key ==> add it.
            $migration->addKey(
                $table,
                $fields,
                'unicity',
                'UNIQUE'
            );
        }
    }

   // unique index added in a previous 9.2-dev
    $migration->dropKey('glpi_tickettemplatepredefinedfields', 'unicity');
    $migration->addKey(
        'glpi_tickettemplatepredefinedfields',
        ['tickettemplates_id', 'num'],
        'tickettemplates_id_id_num'
    );

   //removed field
    if ($DB->fieldExists('glpi_slms', 'resolution_time')) {
        $migration->dropField('glpi_slms', 'resolution_time');
    }

   //wrong type
    $DB->queryOrDie("ALTER TABLE `glpi_users` CHANGE `keep_devices_when_purging_item` `keep_devices_when_purging_item` TINYINT NULL DEFAULT NULL");

   //missing index
    $migration->addKey('glpi_networknames', 'is_deleted');
    $migration->addKey('glpi_networknames', 'is_dynamic');
    $migration->addKey('glpi_projects', 'is_template');
    $migration->addKey('glpi_projecttasks', 'is_template');
    $migration->dropKey('glpi_savedsearches_users', 'bookmarks_id');
    $migration->addKey('glpi_savedsearches_users', 'savedsearches_id');
   //this one was not on the correct field
    $migration->dropKey('glpi_softwarelicenses', 'is_deleted');
    $migration->migrationOneTable('glpi_softwarelicenses');
    $migration->addKey('glpi_softwarelicenses', 'is_deleted');
    $migration->addKey('glpi_states', 'is_visible_line');
    $migration->addKey('glpi_tasktemplates', 'is_private');
    $migration->addKey('glpi_tasktemplates', 'users_id_tech');
    $migration->addKey('glpi_tasktemplates', 'groups_id_tech');

   //add timeline_position in ticketfollowups
   //add timeline_position in tickettasks
   //add timeline_position in documents_items
   //add timeline_position in ticketvalidations
    $timeline_tables = ['glpi_ticketfollowups', 'glpi_tickettasks', 'glpi_documents_items', 'glpi_ticketvalidations'];
    foreach ($timeline_tables as $tl_table) {
       //add timeline_position in $tl_table
        if (!$DB->fieldExists($tl_table, 'timeline_position')) {
            $migration->addField($tl_table, "timeline_position", "tinyint NOT NULL DEFAULT '0'");
            $where = [
                "$tl_table.tickets_id"  => new \QueryExpression(
                    DBmysql::quoteName("glpi_tickets_users.tickets_id")
                ),
                "$tl_table.users_id"    => new \QueryExpression(
                    DBmysql::quoteName("glpi_tickets_users.users_id")
                ),
            ];
            if (!$DB->fieldExists($tl_table, 'tickets_id')) {
                $where = [
                    "$tl_table.itemtype"    => "Ticket",
                    "$tl_table.items_id"    => new \QueryExpression(
                        DBmysql::quoteName("glpi_tickets_users.tickets_id")
                    ),
                    "$tl_table.users_id"    => new \QueryExpression(
                        DBmysql::quoteName("glpi_tickets_users.users_id")
                    ),
                ];
            }

            $update = new \QueryExpression(
                DBmysql::quoteName($tl_table) . ", " . DBmysql::quoteName("glpi_tickets_users")
            );
            $migration->addPostQuery(
                $DB->buildUpdate(
                    $update,
                    [
                        "$tl_table.timeline_position" => new \QueryExpression("IF(" .
                     DBmysql::quoteName("glpi_tickets_users.type") . " NOT IN (1,3) AND " .
                     DBmysql::quoteName("glpi_tickets_users.type") . " IN (2), 4, 1)")
                    ],
                    $where
                )
            );

            $where = [
                "$tl_table.tickets_id"           => new \QueryExpression(
                    DBmysql::quoteName("glpi_groups_tickets.tickets_id")
                ),
                "glpi_groups_users.groups_id"    => new \QueryExpression(
                    DBmysql::quoteName("glpi_groups_tickets.groups_id")
                ),
                "$tl_table.users_id"             => new \QueryExpression(
                    DBmysql::quoteName("glpi_groups_users.users_id")
                ),
            ];
            if (!$DB->fieldExists($tl_table, 'tickets_id')) {
                $where = [
                    "$tl_table.itemtype"             => "Ticket",
                    "$tl_table.items_id"             => new \QueryExpression(
                        DBmysql::quoteName("glpi_groups_tickets.tickets_id")
                    ),
                    "glpi_groups_users.groups_id"    => new \QueryExpression(
                        DBmysql::quoteName("glpi_groups_tickets.groups_id")
                    ),
                    "$tl_table.users_id"             => new \QueryExpression(
                        DBmysql::quoteName("glpi_groups_users.users_id")
                    ),
                ];
            }

            $update = new \QueryExpression(
                DBmysql::quoteName($tl_table) . ", " . DBmysql::quoteName("glpi_groups_tickets") .
                ", " . DBmysql::quoteName("glpi_groups_users")
            );
            $migration->addPostQuery(
                $DB->buildUpdate(
                    $update,
                    [
                        "$tl_table.timeline_position" => new \QueryExpression("IF(" .
                     DBmysql::quoteName("glpi_groups_tickets.type") . " NOT IN (1,3) AND " .
                     DBmysql::quoteName("glpi_groups_tickets.type") . " IN (2), 4, 1)")
                    ],
                    $where
                )
            );

            $migration->addPostQuery(
                $DB->buildUpdate(
                    $tl_table,
                    ["$tl_table.timeline_position" => "1"],
                    ["$tl_table.timeline_position" => "0"]
                )
            );
        }
    }

   // ************ Keep it at the end **************
    $migration->executeMigration();

    return $updateresult;
}
