<?php

class Upgrade34 extends UpgradeNew
{
    public function checkRightfulness_1()
    {
        return findModule('abra') && $this->checkTableExists('abra_users');
    }

    /** Abra sync - add abra deliveries and payments sync tables */
    public function makeChanges_1()
    {
        sqlQuery('CREATE TABLE abra_users (
         `id_abra` VARCHAR(30) NOT NULL,
         `id_user` INT UNSIGNED NOT NULL
      ) ENGINE=InnoDB');

        sqlQuery('ALTER TABLE `abra_users`
        ADD KEY `id_abra` (`id_abra`)');

        sqlQuery('ALTER TABLE `abra_users`
        ADD CONSTRAINT `abra_users_ibfk_1` FOREIGN KEY (`id_user`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE');

        $this->upgradeOK();
    }

    public function checkRightfulness_2()
    {
        return findModule('watchdog') && $this->checkTableExists('products_watchdog');
    }

    /** Watchdog */
    public function makeChanges_2()
    {
        sqlQuery('CREATE TABLE products_watchdog (
         `id_user` INT UNSIGNED NOT NULL,
         `id_product` INT NOT NULL
      ) ENGINE=InnoDB');

        sqlQuery('ALTER TABLE `products_watchdog`
        ADD CONSTRAINT `watchdog_ibfk_2` FOREIGN KEY (`id_product`) REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE');

        sqlQuery('ALTER TABLE `products_watchdog`
        ADD CONSTRAINT `watchdog_ibfk_1` FOREIGN KEY (`id_user`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE');

        $this->upgradeOK();
    }

    public function checkRightfulness_3()
    {
        return findModule('eshop_users') && $this->checkColumnExists('users', 'figure');
    }

    /** Disabled user account */
    public function makeChanges_3()
    {
        sqlQuery('ALTER TABLE users
          ADD COLUMN `figure` ENUM(\'Y\',\'N\') DEFAULT \'Y\' AFTER user_key');

        $this->upgradeOK();
    }

    public function checkRightfulness_4()
    {
        return findModule('currencies') && $this->checkColumnExists('users', 'currency');
    }

    /** Add 'currencies' enum [CZK / EUR] for users */
    public function makeChanges_4()
    {
        sqlQuery('ALTER TABLE users ADD COLUMN `currency` VARCHAR(3) DEFAULT \'CZK\' AFTER country');
        $this->upgradeOK();
    }

    public function checkRightfulness_5()
    {
        return findModule('currencies') && $this->checkColumnExists('orders', 'currency');
    }

    /** Add 'currencies' enum [CZK / EUR] for orders */
    public function makeChanges_5()
    {
        sqlQuery('ALTER TABLE orders ADD COLUMN `currency` VARCHAR(3) DEFAULT \'CZK\' AFTER order_no');
        $this->upgradeOK();
    }

    protected $decimal_precision = '15,4';

    public function checkRightfulness_6()
    {
        return $this->checkColumnType('products', 'price', 'DECIMAL('.$this->decimal_precision.')');
    }

    /** Change type of price, price_common, discount to DECIMAL */
    public function makeChanges_6()
    {
        sqlQuery('ALTER TABLE `products` CHANGE `price` `price` DECIMAL('.$this->decimal_precision.') NOT NULL DEFAULT \'0\'');
        sqlQuery('ALTER TABLE `products` CHANGE `price_common` `price_common` DECIMAL('.$this->decimal_precision.') NOT NULL DEFAULT \'0\'');
        sqlQuery('ALTER TABLE `products` CHANGE `discount` `discount` DECIMAL(12,8) UNSIGNED NOT NULL DEFAULT \'0\'');

        sqlQuery('ALTER TABLE `orders` CHANGE `total_price` `total_price` DECIMAL('.$this->decimal_precision.') NOT NULL DEFAULT \'0\'');
        sqlQuery('ALTER TABLE `order_items` CHANGE `total_price` `total_price` DECIMAL('.$this->decimal_precision.') NOT NULL DEFAULT \'0\'');
        sqlQuery('ALTER TABLE `order_items` CHANGE `piece_price` `piece_price` DECIMAL('.$this->decimal_precision.') NOT NULL DEFAULT \'0\'');

        if (findModule('eshop_delivery')) {
            sqlQuery('ALTER TABLE `delivery_type` CHANGE `price` `price` DECIMAL('.$this->decimal_precision.') NULL');
            sqlQuery('ALTER TABLE `delivery_type` CHANGE `price_dont_countin_from` `price_dont_countin_from` DECIMAL('.$this->decimal_precision.') NULL');

            sqlQuery('ALTER TABLE `delivery_type_delivery` CHANGE `price` `price` DECIMAL('.$this->decimal_precision.') NOT NULL DEFAULT \'0\'');
            sqlQuery('ALTER TABLE `delivery_type_payment` CHANGE `price` `price` DECIMAL('.$this->decimal_precision.') NOT NULL DEFAULT \'0\'');
        }

        $this->upgradeOK();
    }

    public function checkRightfulness_7()
    {
        return $this->checkColumnExists('orders', 'currency_rate');
    }

    /** Add 'currency_rate' to orders */
    public function makeChanges_7()
    {
        sqlQuery('ALTER TABLE orders ADD COLUMN `currency_rate` DECIMAL(15,8) DEFAULT \'1\' AFTER order_no');
        $this->upgradeOK();
    }

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

    /** Users Price Levels - add missing constraints */
    public function makeChanges_8()
    {
        sqlQuery('DELETE udpl FROM users_dealer_price_level udpl
            LEFT JOIN price_levels pl ON pl.id=udpl.id_price_level
            LEFT JOIN users u ON u.id=udpl.id_user
            WHERE pl.id IS NULL OR u.id IS NULL');

        sqlQuery('ALTER TABLE users_dealer_price_level ADD FOREIGN KEY (`id_price_level`) REFERENCES `price_levels`(`id`) ON DELETE CASCADE ON UPDATE CASCADE');
        sqlQuery('ALTER TABLE users_dealer_price_level ADD FOREIGN KEY (`id_user`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE');

        $this->upgradeOK();
    }

    public function check_UsersDealerPricelevelUnique()
    {
        return findModule('price_levels') && $this->checkConstraintExists('users_dealer_price_level', 'id_user');
    }

    /** Add UNIQUE on users_dealer_price_level (id_user) */
    public function upgrade_UsersDealerPricelevelUnique()
    {
        sqlQuery('ALTER TABLE users_dealer_price_level ADD UNIQUE (id_user)');

        $this->upgradeOK();
    }

    public function checkRightfulness_9()
    {
        return findModule('eshop_users') && $this->checkColumnExists('users', 'date_logged');
    }

    /** Users.date_logged - add date of last login */
    public function makeChanges_9()
    {
        sqlQuery('ALTER TABLE users ADD COLUMN date_logged DATETIME NULL DEFAULT NULL');

        $this->upgradeOK();
    }

    public function checkRightfulness_10()
    {
        return findModule('eshop_users') && $this->checkColumnCollation('users', 'name', 'cp1250_general_ci') && $this->checkColumnCollation('users', 'name', 'utf8mb4_general_ci');
    }

    /** Users - make fields case insensitive */
    public function makeChanges_10()
    {
        sqlQuery('ALTER TABLE `users` CHANGE `name` `name` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT \'\',
            CHANGE `surname` `surname` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT \'\',
            CHANGE `firm` `firm` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT \'\',
            CHANGE `street` `street` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT \'\',
            CHANGE `city` `city` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT \'\',
            CHANGE `zip` `zip` VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT \'\',
            CHANGE `country` `country` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT \'\',
            CHANGE `ico` `ico` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT \'\',
            CHANGE `dic` `dic` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT \'\',
            
            CHANGE `delivery_name` `delivery_name` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT \'\',
            CHANGE `delivery_surname` `delivery_surname` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT \'\',
            CHANGE `delivery_firm` `delivery_firm` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT \'\',
            CHANGE `delivery_street` `delivery_street` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT \'\',
            CHANGE `delivery_city` `delivery_city` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT \'\',
            CHANGE `delivery_country` `delivery_country` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT \'\'');

        $this->upgradeOK();
    }

    public function checkRightfulness_11()
    {
        return findModule('eshop_users') && !$this->checkColumnExists('users', 'login');
    }

    /** Users - remove login field and set unactive when login is empty */
    public function makeChanges_11()
    {
        sqlQuery("UPDATE users SET figure='N' WHERE login IS NULL");
        sqlQuery('ALTER TABLE users DROP COLUMN login');

        $this->upgradeOK();
    }

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

    /** Delivery - limit to only some currencies */
    public function makeChanges_12()
    {
        sqlQuery('ALTER TABLE `delivery_type_delivery`
        ADD `currencies` VARCHAR(200) NULL DEFAULT NULL');
    }

    public function checkRightfulness_13()
    {
        return $this->checkColumnType('order_edit', 'total_price', 'DECIMAL('.$this->decimal_precision.')') && findModule('order_edit');
    }

    /** Change decimal type - order_edit */
    public function makeChanges_13()
    {
        sqlQuery('ALTER TABLE `order_edit` CHANGE `total_price` `total_price` DECIMAL('.$this->decimal_precision.') NOT NULL DEFAULT \'0\'');
        sqlQuery('ALTER TABLE `order_edit` CHANGE `piece_price` `piece_price` DECIMAL('.$this->decimal_precision.') NOT NULL DEFAULT \'0\'');

        $this->upgradeOK();
    }

    public function checkRightfulness_14()
    {
        return findModule('order_payment') && $this->checkColumnType('order_payments', 'price', 'DECIMAL('.$this->decimal_precision.')');
    }

    /** Change decimal type  - order_payments */
    public function makeChanges_14()
    {
        sqlQuery('ALTER TABLE `order_payments` CHANGE `price` `price` DECIMAL('.$this->decimal_precision.') NOT NULL');

        $this->upgradeOK();
    }

    public function checkRightfulness_15()
    {
        return findModule('products_of_suppliers') && $this->checkColumnType('products_of_suppliers', 'price_buy', 'DECIMAL('.$this->decimal_precision.')');
    }

    /** Change decimal type - products_of_suppliers */
    public function makeChanges_15()
    {
        sqlQuery('ALTER TABLE `products_of_suppliers` CHANGE `price_buy` `price_buy` DECIMAL('.$this->decimal_precision.') DEFAULT NULL');
        sqlQuery('ALTER TABLE `products_of_suppliers` CHANGE `price_sell` `price_sell` DECIMAL('.$this->decimal_precision.') DEFAULT NULL');

        $this->upgradeOK();
    }

    public function checkRightfulness_productsOfSuppliersNote()
    {
        return (findModule(Modules::PRODUCTS_SUPPLIERS) || findModule(Modules::STOCK_IN)) && $this->checkColumnExists('products_of_suppliers', 'note');
    }

    /** Add column note into table products_of_suppliers */
    public function makeChanges_productsOfSuppliersNote()
    {
        sqlQuery('ALTER TABLE products_of_suppliers ADD COLUMN note MEDIUMTEXT DEFAULT NULL');

        $this->upgradeOK();
    }

    public function checkRightfulness_16()
    {
        return findModule('products_variations') && $this->checkColumnType('products_variations', 'price', 'DECIMAL('.$this->decimal_precision.')');
    }

    /** Change decimal type - products_variations */
    public function makeChanges_16()
    {
        sqlQuery('ALTER TABLE `products_variations` CHANGE `price` `price` DECIMAL('.$this->decimal_precision.') DEFAULT NULL');

        $this->upgradeOK();
    }

    public function checkRightfulness_17()
    {
        return findModule('stock_in') && $this->checkColumnType('stock_in', 'total_price', 'DECIMAL('.$this->decimal_precision.')');
    }

    /** Change decimal type - stock_in */
    public function makeChanges_17()
    {
        sqlQuery('ALTER TABLE `stock_in` CHANGE `total_price` `total_price` DECIMAL('.$this->decimal_precision.') NOT NULL DEFAULT \'0\'');
        sqlQuery('ALTER TABLE `stock_in` CHANGE `transport_price` `transport_price` DECIMAL('.$this->decimal_precision.') NOT NULL DEFAULT \'0\'');

        sqlQuery('ALTER TABLE `stock_in_items` CHANGE `price` `price` DECIMAL('.$this->decimal_precision.') NOT NULL');

        $this->upgradeOK();
    }
}