<?php

class Upgrade32 extends UpgradeNew
{
    public function checkRightfulness_1()
    {
        return findModule('eshop_delivery') && $this->checkTableExists('delivery_type_delivery');
    }

    /** Split delivery_types to delivery and payment */
    public function makeChanges_1()
    {
        sqlQuery("
         CREATE TABLE `delivery_type_delivery` (
         `id` INT(11) NOT NULL,
           `name` VARCHAR(50) NOT NULL,
           `price` FLOAT NOT NULL DEFAULT '0',
           `photo` VARCHAR(50) DEFAULT NULL
         ) ENGINE=InnoDB;

         -- --------------------------------------------------------

         --
         -- Table structure for table `delivery_type_payment`
         --

         CREATE TABLE `delivery_type_payment` (
         `id` INT(11) NOT NULL,
           `name` VARCHAR(50) NOT NULL,
           `price` FLOAT NOT NULL DEFAULT '0',
           `class` VARCHAR(20) DEFAULT NULL,
           `photo` VARCHAR(50) DEFAULT NULL
         ) ENGINE=InnoDB;

         --
         -- Indexes for dumped tables
         --

         --
         -- Indexes for table `delivery_type_delivery`
         --
         ALTER TABLE `delivery_type_delivery`
          ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `name` (`name`);

         --
         -- Indexes for table `delivery_type_payment`
         --
         ALTER TABLE `delivery_type_payment`
          ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `name` (`name`);

         --
         -- AUTO_INCREMENT for dumped tables
         --

         --
         -- AUTO_INCREMENT for table `delivery_type_delivery`
         --
         ALTER TABLE `delivery_type_delivery`
         MODIFY `id` INT(11) NOT NULL AUTO_INCREMENT;
         --
         -- AUTO_INCREMENT for table `delivery_type_payment`
         --
         ALTER TABLE `delivery_type_payment`
         MODIFY `id` INT(11) NOT NULL AUTO_INCREMENT;SET FOREIGN_KEY_CHECKS=1;
      ");

        $this->upgradeOK();
    }

    public function checkRightfulness_2()
    {
        return findModule('eshop_delivery') && $this->checkColumnExists('delivery_type', 'id_delivery');
    }

    /** Create relations */
    public function makeChanges_2()
    {
        sqlQuery('ALTER TABLE `delivery_type` ADD `id_delivery` INT NULL AFTER `id`, ADD `id_payment` INT NULL AFTER `id_delivery`');
        sqlQuery('ALTER TABLE `delivery_type` ADD INDEX(`id_delivery`)');
        sqlQuery('ALTER TABLE `delivery_type` ADD INDEX(`id_payment`)');

        sqlQuery('ALTER TABLE `delivery_type` ADD FOREIGN KEY (`id_delivery`) REFERENCES `delivery_type_delivery`(`id`) ON DELETE SET NULL ON UPDATE CASCADE');
        sqlQuery('ALTER TABLE `delivery_type` ADD FOREIGN KEY (`id_payment`) REFERENCES `delivery_type_payment`(`id`) ON DELETE SET NULL ON UPDATE CASCADE');

        $this->upgradeOK();
    }

    public function checkRightfulness_3()
    {
        return findModule('eshop_delivery') && !$this->checkColumnExists('delivery_type', 'delivery');
    }

    /** Migrate existing deliveries */
    public function makeChanges_3()
    {
        $image = 'NULL';
        if (findModule('eshop_delivery', 'images')) {
            $image = 'delivery_photo';
        }

        sqlQuery('DELETE FROM `delivery_type_delivery`');
        sqlQuery("INSERT INTO `delivery_type_delivery`(`id`, `name`, `price`, `photo`)
          SELECT NULL, delivery, 0, {$image} FROM `delivery_type` WHERE delivery != '' GROUP BY delivery");

        if (findModule('eshop_delivery', 'images')) {
            $image = 'payment_photo';
        }

        $class = 'NULL';
        if (findModule('payments')) {
            $class = 'payment_class';
        }

        sqlQuery('DELETE FROM `delivery_type_payment`');
        sqlQuery("INSERT INTO `delivery_type_payment`(`id`, `name`, `price`, `class`, `photo`)
          SELECT NULL, payment, 0, {$class}, {$image} FROM `delivery_type` WHERE payment != '' GROUP BY payment");

        sqlQuery('UPDATE `delivery_type` dt SET `id_delivery`=(SELECT id FROM delivery_type_delivery dtd WHERE dtd.name=dt.delivery)');
        sqlQuery('UPDATE `delivery_type` dt SET `id_payment`=(SELECT id FROM delivery_type_payment dtp WHERE dtp.name=dt.payment)');

        sqlQuery('ALTER TABLE `delivery_type` CHANGE `price` `price` FLOAT NULL DEFAULT NULL');
        sqlQuery('ALTER TABLE `delivery_type` CHANGE `price_dont_countin_from` `price_dont_countin_from` FLOAT NULL DEFAULT NULL');

        // Update price_dont_countin_from - set to NULL where was 0
        sqlQuery('UPDATE `delivery_type` SET `price_dont_countin_from`=NULL WHERE `price_dont_countin_from` = 0');

        if (findModule('payments')) {
            sqlQuery('ALTER TABLE `delivery_type` DROP `payment_class`');
        }

        sqlQuery('ALTER TABLE `delivery_type` DROP `payment`, DROP `delivery`');

        $this->upgradeOK();
    }

    public function checkRightfulness_4()
    {
        return $this->checkColumnExists('photos_products_relation', 'position');
    }

    /** Add position column for photo ordering */
    public function makeChanges_4()
    {
        sqlQuery('ALTER TABLE '.getTableName('photos_products_relation').' ADD `position` INT(11) NULL ');

        sqlQuery('UPDATE '.getTableName('photos_products_relation')." SET `position`='1'  WHERE `show_in_lead`='N' ");

        sqlQuery('UPDATE '.getTableName('photos_products_relation')." SET `position`='0'  WHERE `show_in_lead`='Y' ");

        $this->upgradeOK();
    }

    public function checkRightfulness_5()
    {
        return findModule('price_levels') && $this->checkForeignKeyExists('price_levels_products', 'id_product');
    }

    /** Price Levels Products - add missing constraint */
    public function makeChanges_5()
    {
        sqlQuery('ALTER TABLE '.getTableName('price_levels_products').' CHANGE `id_product` `id_product` INT(11) NOT NULL');

        sqlQuery('DELETE plp FROM '.getTableName('price_levels_products').' plp
            LEFT JOIN '.getTableName('price_levels').' pl ON pl.id=plp.id_price_level
            LEFT JOIN '.getTableName('products').' p ON p.id=plp.id_product
            WHERE pl.id IS NULL OR p.id IS NULL');

        sqlQuery('ALTER TABLE '.getTableName('price_levels_products').' ADD FOREIGN KEY (`id_price_level`) REFERENCES `price_levels`(`id`) ON DELETE CASCADE ON UPDATE CASCADE');
        sqlQuery('ALTER TABLE '.getTableName('price_levels_products').' ADD FOREIGN KEY (`id_product`) REFERENCES `products`(`id`) ON DELETE CASCADE ON UPDATE CASCADE');

        $this->upgradeOK();
    }

    public function checkRightfulness_6()
    {
        return findModule('price_levels') && $this->checkForeignKeyExists('price_levels_sections', 'id_section');
    }

    /** Price Levels Sections - add missing constraint */
    public function makeChanges_6()
    {
        sqlQuery('ALTER TABLE '.getTableName('price_levels_sections').' CHANGE `id_section` `id_section` INT(11) NOT NULL');

        sqlQuery('DELETE plp FROM '.getTableName('price_levels_sections').' plp
            LEFT JOIN '.getTableName('price_levels').' pl ON pl.id=plp.id_price_level
            LEFT JOIN '.getTableName('sections').' p ON p.id=plp.id_section
            WHERE pl.id IS NULL OR p.id IS NULL');

        sqlQuery('ALTER TABLE '.getTableName('price_levels_sections').' ADD FOREIGN KEY (`id_price_level`) REFERENCES `price_levels`(`id`) ON DELETE CASCADE ON UPDATE CASCADE');
        sqlQuery('ALTER TABLE '.getTableName('price_levels_sections').' ADD FOREIGN KEY (`id_section`) REFERENCES `sections`(`id`) ON DELETE CASCADE ON UPDATE CASCADE');

        $this->upgradeOK();
    }

    public function checkRightfulness_7()
    {
        return findModule('price_levels') && $this->checkForeignKeyExists('price_levels_producers', 'id_producer');
    }

    /** Price Levels Producers - add missing constraint */
    public function makeChanges_7()
    {
        sqlQuery('ALTER TABLE '.getTableName('price_levels_producers').' CHANGE `id_producer` `id_producer` INT(10) UNSIGNED NOT NULL');

        sqlQuery('DELETE plp FROM '.getTableName('price_levels_producers').' plp
            LEFT JOIN '.getTableName('price_levels').' pl ON pl.id=plp.id_price_level
            LEFT JOIN '.getTableName('producers').' p ON p.id=plp.id_producer
            WHERE pl.id IS NULL OR p.id IS NULL');

        sqlQuery('ALTER TABLE '.getTableName('price_levels_producers').' ADD FOREIGN KEY (`id_price_level`) REFERENCES `price_levels`(`id`) ON DELETE CASCADE ON UPDATE CASCADE');
        sqlQuery('ALTER TABLE '.getTableName('price_levels_producers').' ADD FOREIGN KEY (`id_producer`) REFERENCES `producers`(`id`) ON DELETE CASCADE ON UPDATE CASCADE');

        $this->upgradeOK();
    }

    public function checkRightfulness_8()
    {
        return findModule('price_levels') && $this->checkForeignKeyExists('price_levels_producers', 'id_producer');
    }

    /** Price Levels Producers - add missing constraint */
    public function makeChanges_8()
    {
        sqlQuery('ALTER TABLE '.getTableName('sections').' CHANGE `lead_figure` `lead_figure` ENUM("Y","N") NOT NULL DEFAULT "N"');

        sqlQuery('ALTER TABLE '.getTableName('sections').' CHANGE `behaviour` `behaviour` ENUM("1","2") NOT NULL DEFAULT "2"');

        $this->upgradeOK();
    }

    public function checkRightfulness_9()
    {
        return findModule('deliveries') && $this->checkColumnExists('delivery_type_delivery', 'class');
    }

    /** Delivery Type Delivery - class */
    public function makeChanges_9()
    {
        sqlQuery('ALTER TABLE '.getTableName('delivery_type_delivery').' ADD `class` VARCHAR(50) NULL DEFAULT NULL');

        $this->upgradeOK();
    }

    //
    //   function checkRightfulness_10()
    //   {
    //      return findModule('eshop_delivery') && $this->checkColumnExists('delivery_type_delivery', 'in_person');
    //   }
    //
    //   /** Delivery Type Delivery - in_person */
    //   function makeChanges_10()
    //   {
    //      sqlQuery("ALTER TABLE ".getTableName('delivery_type_delivery')." ADD `in_person` ENUM('Y','N') DEFAULT 'N'");
    //
    //      $this->upgradeOK();
    //   }

    public function checkRightfulness_11()
    {
        return findModule('eshop_delivery') && $this->checkColumnExists('delivery_type_delivery', 'time_days');
    }

    /** Delivery Type Delivery - delivery days and delivery hour */
    public function makeChanges_11()
    {
        sqlQuery('ALTER TABLE '.getTableName('delivery_type_delivery').' ADD `time_days` INT NULL DEFAULT NULL , ADD `time_hours` TIME NULL DEFAULT NULL');

        $this->upgradeOK();
    }

    public function checkRightfulness_12()
    {
        return $this->checkEnumExists('products', 'figure', 'O');
    }

    /** Old products - product.figure ENUM add 'O' */
    public function makeChanges_12()
    {
        $this->checkEnumExists('products', 'figure', 'O', $figures);
        $figures = str_replace(')', ",'O')", $figures);
        sqlQuery('ALTER TABLE '.getTableName('products')." CHANGE `figure` `figure` {$figures} DEFAULT 'Y'");

        $this->upgradeOK();
    }

    public function checkRightfulness_13()
    {
        return $this->checkColumnExists('producers', 'meta_title');
    }

    /** Producers - add SEO columns  */
    public function makeChanges_13()
    {
        $SQL = sqlQuery('ALTER TABLE '.getTableName('producers').' ADD
	   		(`meta_title` VARCHAR(70) NULL DEFAULT NULL,
	   		`meta_description` VARCHAR(250) NULL DEFAULT NULL,
	   		`meta_keywords` VARCHAR(100) NULL DEFAULT NULL)');

        $this->upgradeOK();
    }

    public function checkRightfulness_14()
    {
        return findModule('sliders') && $this->checkColumnExists('sliders_images', 'date_from');
    }

    /** Sliders images - add dates columns  */
    public function makeChanges_14()
    {
        sqlQuery('ALTER TABLE '.getTableName('sliders_images').' ADD `date_from` DATETIME NULL');
        sqlQuery('ALTER TABLE '.getTableName('sliders_images').' ADD `date_to` DATETIME NULL');

        $this->upgradeOK();
    }

    public function checkRightfulness_15()
    {
        return findModule('sliders') && $this->checkColumnExists('sliders', 'size');
    }

    /** Sliders - add size  */
    public function makeChanges_15()
    {
        sqlQuery('ALTER TABLE '.getTableName('sliders').' ADD `size` VARCHAR(20) NULL DEFAULT NULL');

        $this->upgradeOK();
    }

    public function checkRightfulness_16()
    {
        return $this->checkColumnExists('parameters', 'position');
    }

    /** Add position column for parameters */
    public function makeChanges_16()
    {
        sqlQuery('ALTER TABLE '.getTableName('parameters').' ADD `position` INT(11) NULL ');

        $this->upgradeOK();
    }

    public function checkRightfulness_17()
    {
        return $this->checkColumnExists('producers', 'orderby');
    }

    /** Add ordering columns for producers */
    public function makeChanges_17()
    {
        sqlQuery('ALTER TABLE '.getTableName('producers')." ADD `orderby` ENUM('date','title','price','store') NOT NULL DEFAULT 'title' ");
        sqlQuery('ALTER TABLE '.getTableName('producers')." ADD `orderdir` ENUM('ASC','DESC') NOT NULL DEFAULT 'ASC' ");

        $this->upgradeOK();
    }

    public function checkRightfulness_18()
    {
        return findModule('eshop_users') && $this->checkTableExists('users_groups');
    }

    /** Create Users Groups and relations table*/
    public function makeChanges_18()
    {
        sqlQuery('CREATE TABLE IF NOT EXISTS `users_groups` (
        `id` INT(11) NOT NULL,
        `name` VARCHAR(50) NOT NULL,
        `descr` TEXT
      ) ENGINE=InnoDB;

      ALTER TABLE `users_groups` ADD PRIMARY KEY (`id`);
      ALTER TABLE `users_groups`
         MODIFY `id` INT(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=1;

      ALTER TABLE `users_groups` ADD UNIQUE(`name`);

      CREATE TABLE IF NOT EXISTS `users_groups_relations` (
        `id_group` INT(11) NOT NULL,
        `id_user` INT(11) UNSIGNED NOT NULL
      ) ENGINE=InnoDB;

      ALTER TABLE `users_groups_relations` ADD PRIMARY KEY (`id_group`,`id_user`), ADD KEY `users_groups_relations_ibfk_2` (`id_user`);

      ALTER TABLE `users_groups_relations`
      ADD CONSTRAINT `users_groups_relations_ibfk_2` FOREIGN KEY (`id_user`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
      ADD CONSTRAINT `users_groups_relations_ibfk_1` FOREIGN KEY (`id_group`) REFERENCES `users_groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

      ALTER TABLE `users_groups` ADD UNIQUE(`name`);
      ');

        $this->upgradeOK();
    }

    public function checkRightfulness_19()
    {
        return $this->checkColumnExists('products_in_sections', 'position');
    }

    public function checkRightfulness_20()
    {
        return !$this->checkColumnExists('settings', 'key_name') && Settings::getDefault()->user_rights_version == 1;
    }

    /** Move analytics from config to settings */
    public function makeChanges_20()
    {
        global $dbcfg, $cfg;
        $settings = Settings::createFromDB();
        if (empty($dbcfg['analytics']['google_analytics']['ID']) && !empty($cfg['Modules']['google_analytics']['ID'])) {
            $settings->analytics['google_analytics']['ID'] = $cfg['Modules']['google_analytics']['ID'];
        }

        if (empty($dbcfg['analytics']['google_conversion']['ID']) && !empty($cfg['Modules']['google_conversion']['ID'])) {
            $settings->analytics['google_conversion']['ID'] = $cfg['Modules']['google_conversion']['ID'];
        }

        if (empty($dbcfg['analytics']['google_conversion']['label']) && !empty($cfg['Modules']['google_conversion']['label'])) {
            $settings->analytics['google_conversion']['label'] = $cfg['Modules']['google_conversion']['label'];
        }

        if (empty($dbcfg['analytics']['heureka_overeno']['ID']) && !empty($cfg['Heureka']['id'])) {
            $settings->analytics['heureka_overeno']['ID'] = $cfg['Heureka']['id'];
        }

        if (empty($dbcfg['analytics']['heureka_overeno']['ID']) && !empty($cfg['Modules']['heureka_overeno']['id'])) {
            $settings->analytics['heureka_overeno']['ID'] = $cfg['Modules']['heureka_overeno']['id'];
        }

        if (empty($dbcfg['analytics']['heureka_conversion']['ID']) && !empty($cfg['Modules']['heureka_conversion']['ID'])) {
            $settings->analytics['heureka_conversion']['ID'] = $cfg['Modules']['heureka_conversion']['ID'];
        }

        if (empty($dbcfg['analytics']['zbozi_cz_feedback']['chsum']) && !empty($cfg['Modules']['zbozi_cz_feedback']['chsum'])) {
            $settings->analytics['zbozi_cz_feedback']['chsum'] = $cfg['Modules']['zbozi_cz_feedback']['chsum'];
        }

        if (empty($dbcfg['analytics']['sklik_conversion']['ID']) && !empty($cfg['Modules']['sklik_conversion']['ID'])) {
            $settings->analytics['sklik_conversion']['ID'] = $cfg['Modules']['sklik_conversion']['ID'];
        }

        if (empty($dbcfg['analytics']['seznam_rtg']['ID']) && !empty($cfg['Modules']['seznam_rtg']['ID'])) {
            $settings->analytics['seznam_rtg']['ID'] = $cfg['Modules']['seznam_rtg']['ID'];
        }

        $this->commitDataMigration(2);

        $this->upgradeOK();
    }

    public function checkRightfulness_22()
    {
        return !$this->checkColumnExists('settings', 'settings') && $this->checkColumnCollation('settings', 'settings', 'utf8mb4_bin');
    }

    /** Change settings to UTF-8 */
    public function makeChanges_22()
    {
        sqlQuery('ALTER TABLE `settings` CHANGE `settings` `settings` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL;');

        $this->upgradeOK();
    }
}