<?php

class Upgrade42 extends UpgradeNew
{
    public function check_productsOfSuppliersCoreLength()
    {
        return findModule(Modules::PRODUCTS_SUPPLIERS) && $this->checkColumnType('products_of_suppliers', 'code', 'VARCHAR(100)');
    }

    /** products_of_suppliers: make 'code' 100 chars long */
    public function upgrade_productsOfSuppliersCoreLength()
    {
        sqlQuery('ALTER TABLE products_of_suppliers MODIFY COLUMN code VARCHAR(100);');
    }

    public function check_photos_products_relation_id_photo_index()
    {
        return !findModule(Modules::PRODUCTS_VARIATIONS_PHOTOS) && !$this->checkConstraintWithColumnExists('photos_products_relation', 'id_photo', 'id_variation');
    }

    /** photos_products_relation: remove `id_variation` column from UNIQUE KEY `id_photo` */
    public function upgrade_photos_products_relation_id_photo_index()
    {
        sqlQuery('SET FOREIGN_KEY_CHECKS = 0');
        sqlQuery('ALTER TABLE `photos_products_relation` DROP INDEX `id_photo`');
        sqlQuery('ALTER TABLE `photos_products_relation` ADD UNIQUE KEY `id_photo` (`id_photo`,`id_product`)');
        sqlQuery('SET FOREIGN_KEY_CHECKS = 1');

        $this->upgradeOK();
    }

    public function check_photos_products_relation_id_photo_index2()
    {
        return findModule(Modules::PRODUCTS_VARIATIONS_PHOTOS) && $this->checkConstraintWithColumnExists('photos_products_relation', 'id_photo', 'id_variation');
    }

    /** photos_products_relation: add `id_variation` column to UNIQUE KEY `id_photo` (module PRODUCTS_VARIATIONS_PHOTOS) */
    public function upgrade_photos_products_relation_id_photo_index2()
    {
        sqlQuery('SET FOREIGN_KEY_CHECKS = 0');
        sqlQuery('ALTER TABLE `photos_products_relation` ADD INDEX `id_photo_tmp` (`id_photo`)');
        sqlQuery('ALTER TABLE `photos_products_relation` DROP INDEX `id_photo`');
        sqlQuery('ALTER TABLE `photos_products_relation` ADD UNIQUE KEY `id_photo` (`id_photo`,`id_product`,`id_variation`)');
        sqlQuery('ALTER TABLE `photos_products_relation` DROP INDEX `id_photo_tmp`');
        sqlQuery('SET FOREIGN_KEY_CHECKS = 1');

        $this->upgradeOK();
    }

    public function check_ProductsRelatedTypes()
    {
        return findModule(Modules::PRODUCTS_RELATED, Modules::SUB_TYPES) && $this->checkTableExists('products_related_types');
    }

    /** add types to products_related */
    public function upgrade_ProductsRelatedTypes()
    {
        sqlQuery('
            CREATE TABLE products_related_types (
                `id` int auto_increment PRIMARY KEY ,
                `name` VARCHAR(50) NOT NULL
            );
        ');

        $this->insertSQL('products_related_types', ['name' => 'Související']);

        $this->upgradeOK();
    }

    /** Add products related type */
    public function check_ProductsRelatedType()
    {
        return findModule(Modules::PRODUCTS_RELATED, Modules::SUB_TYPES) && $this->checkColumnExists('products_related', 'type');
    }

    public function upgrade_ProductsRelatedType()
    {
        sqlQuery('ALTER TABLE products_related ADD COLUMN `type` INT DEFAULT NULL');
        sqlQuery('ALTER TABLE products_related ADD FOREIGN KEY (`type`) REFERENCES `products_related_types`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;');

        $this->upgradeOK();
    }

    public function check_ProductsRelatedTypesPrimaryKey()
    {
        return findModule(Modules::PRODUCTS_RELATED, Modules::SUB_TYPES)
            && $this->checkColumnIsNull('products_related', 'type', false);
    }

    /** products_related: update primary key to be able have multiple types of products_related */
    public function upgrade_ProductsRelatedTypesPrimaryKey()
    {
        // pokud neexistuje zadnej type, tak ho pridam
        if (!($typeId = sqlQuery('SELECT id FROM products_related_types ORDER BY id ASC')->fetchColumn())) {
            $this->insertSQL('products_related_types', ['name' => 'Související']);
            $typeId = sqlInsertId();
        }

        // nastavit vsem co maji NULL type
        sqlQuery('UPDATE products_related SET type = :type WHERE type IS NULL', ['type' => $typeId]);
        sqlQuery('ALTER TABLE products_related DROP FOREIGN KEY products_related_ibfk_3');
        sqlQuery('ALTER TABLE products_related CHANGE type type INT NOT NULL;');
        // znovu vytvorit foreign key - kvuli zmene na ON DELETE CASCADE
        sqlQuery('ALTER TABLE products_related ADD FOREIGN KEY (`type`) REFERENCES `products_related_types`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;');
        // dropnout a pridat primary key i s typem
        sqlQuery('ALTER TABLE products_related DROP PRIMARY KEY, ADD PRIMARY KEY (id_top_product, id_rel_product, type);');

        $this->upgradeOK();
    }

    public function check_AddDataColumn()
    {
        return $this->checkColumnExists('delivery_type_delivery', 'data');
    }

    /** Add field data to delivery_type_delivery */
    public function upgrade_AddDataColumn()
    {
        sqlQuery('alter table delivery_type_delivery
	                add data text null;');
        $this->upgradeOK();
    }

    public function check_AddCustomDataColumn()
    {
        return $this->checkColumnExists('delivery_type', 'data');
    }

    /** Add field data to delivery_type */
    public function upgrade_AddCustomDataColumn()
    {
        sqlQuery('alter table delivery_type
	                add data text null;');
        $this->upgradeOK();
    }

    public function check_AddRegDeliveryDontCountinFrom()
    {
        return $this->checkColumnExists('delivery_type', 'price_dont_countin_from_reg');
    }

    /** Add price_dont_countin_from_reg to delivery_type */
    public function upgrade_AddRegDeliveryDontCountinFrom()
    {
        sqlQuery('ALTER TABLE delivery_type ADD COLUMN `price_dont_countin_from_reg` DECIMAL(15,4) NULL;');
        $this->upgradeOK();
    }

    public function check_pr_id_sec_pos_id_producer()
    {
        return $this->checkColumnExists('products_in_sections_positions', 'id_producer');
    }

    /** Add field id_producer into products_in_sections_positions */
    public function upgrade_pr_id_sec_pos_id_producer()
    {
        try {
            sqlQuery('alter table products_in_sections_positions drop foreign key products_in_sections_positions_ibfk_1');
        } catch (Doctrine\DBAL\Exception\DriverException $e) {
        }

        try {
            sqlQuery('alter table products_in_sections_positions drop foreign key products_in_sections_positions_products_id_fk');
        } catch (Doctrine\DBAL\Exception\DriverException $e) {
        }

        sqlQuery('alter table products_in_sections_positions drop foreign key products_in_sections_positions_ibfk_2');

        sqlQuery('alter table products_in_sections_positions drop primary key');

        sqlQuery('
            alter table products_in_sections_positions modify id_section int(11) null;
            
            alter table products_in_sections_positions
                add id_producer int(11) unsigned null;
            
            alter table products_in_sections_positions
                add constraint products_in_sections_positions_pk
                    unique (id_product, id_section, id_producer);
            
            ALTER TABLE products_in_sections_positions ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
            
            alter table products_in_sections_positions
                add constraint products_in_sections_positions_producers_id_fk
                    foreign key (id_producer) references producers (id);
            
            alter table products_in_sections_positions
                add constraint products_in_sections_positions_ibfk_1
                    foreign key (id_product) references products (id);
            
            alter table products_in_sections_positions
                add constraint products_in_sections_positions_ibfk_2
                    foreign key (id_section) references sections (id);
        ');

        $this->upgradeOK();
    }

    public function check_ImportMultiplierProductsOfSuppliers()
    {
        if ($this->checkColumnExists('products_of_suppliers', 'import_multiplier')) {
            // table or field doesn't exist
            return false;
        }

        return $this->checkColumnDefault('products_of_suppliers', 'import_multiplier', '1');
    }

    /** Change column import_multiplier from float to float not null default 1 */
    public function upgrade_ImportMultiplierProductsOfSuppliers()
    {
        sqlQuery('UPDATE products_of_suppliers SET import_multiplier=1 WHERE import_multiplier IS NULL');
        sqlQuery('ALTER TABLE products_of_suppliers CHANGE import_multiplier import_multiplier FLOAT NOT NULL DEFAULT 1');

        $this->upgradeOK();
    }

    public function check_FixParametersConfigurationsPrice()
    {
        return findModule('products_parameters', 'configurations') && $this->checkColumnType('parameters_products', 'configuration_price', 'DECIMAL(15,4)');
    }

    /** Modify price of conf products parameters */
    public function upgrade_FixParametersConfigurationsPrice()
    {
        sqlQuery('
        ALTER TABLE `parameters_products` MODIFY COLUMN `configuration_price` DECIMAL(15,4) NULL DEFAULT NULL
         ');

        $this->upgradeOK();
    }

    public function check_orderEmailLength()
    {
        return $this->checkColumnType('orders', 'invoice_email', 'VARCHAR(100)');
    }

    /** increase email length to 100 */
    public function upgrade_orderEmailLength()
    {
        sqlQuery('
        ALTER TABLE `orders` MODIFY COLUMN `invoice_email` varchar(100) default \'\' not null
         ');

        $this->upgradeOK();
    }

    public function check_templatesNameLength()
    {
        return findModule(\Modules::TEMPLATES) && $this->checkColumnType('templates', 'name', 'VARCHAR(100)');
    }

    /** increase template name length to 100 */
    public function upgrade_templatesNameLength()
    {
        sqlQuery('
        ALTER TABLE `templates` MODIFY COLUMN `name` varchar(100) not null;
         ');

        $this->upgradeOK();
    }

    public function check_AddAdminsDataColumn()
    {
        return $this->checkColumnExists('admins', 'data');
    }

    /** Add field data to admins */
    public function upgrade_AddAdminsDataColumn()
    {
        sqlQuery('ALTER TABLE admins ADD data TEXT NULL;');
        $this->upgradeOK();
    }

    public function check_SupplierDataColumn()
    {
        return $this->checkColumnExists('suppliers', 'data');
    }

    /** Add 'data' column into table 'suppliers' */
    public function upgrade_SupplierDataColumn()
    {
        sqlQuery('ALTER TABLE suppliers ADD COLUMN data MEDIUMTEXT DEFAULT NULL');

        $this->upgradeOK();
    }

    public function check_SupplierEmailColumnLength()
    {
        return $this->checkColumnType('suppliers', 'email', 'VARCHAR(50)');
    }

    /** increase length of 'email' column in 'suppliers' table */
    public function upgrade_SupplierEmailColumnLength()
    {
        sqlQuery('ALTER TABLE suppliers CHANGE COLUMN email email VARCHAR(50) DEFAULT NULL');

        $this->upgradeOK();
    }

    public function check_SupplierAddressLength()
    {
        return $this->checkColumnType('suppliers', 'address', 'MEDIUMTEXT');
    }

    /** Change 'address' column in 'suppliers' table to mediumtext */
    public function upgrade_SupplierAddressLength()
    {
        sqlQuery('ALTER TABLE suppliers CHANGE COLUMN address address MEDIUMTEXT DEFAULT NULL');

        $this->upgradeOK();
    }

    public function check_ProductsNoteLength()
    {
        return findModule(\Modules::PRODUCTS, Modules::SUB_NOTE) && $this->checkColumnType('products', 'note', 'VARCHAR(255)');
    }

    /** increase products/variations note to 255 */
    public function upgrade_ProductsNoteLength()
    {
        sqlQuery('
            ALTER TABLE `products` MODIFY COLUMN `note` varchar(255) null;
            ALTER TABLE `products_variations` MODIFY COLUMN `note` varchar(255) null;
         ');

        $this->upgradeOK();
    }

    public function check_POSOrdersFlag()
    {
        return findModule(Modules::POS) && Settings::getDefault()->user_rights_version == 11;
    }

    /** Add POS flag to orders created by POS */
    public function upgrade_POSOrdersFlag()
    {
        $settings = \Settings::getDefault();

        sqlQuery('UPDATE orders SET flags = ADD_TO_SET("POS", flags) WHERE pos = 1');

        $this->commitDataMigration(12);

        $this->upgradeOK();
    }

    public function check_ProducersDescr()
    {
        return !$this->checkColumnExists('producers', 'descr');
    }

    /** Delete description from producers */
    public function upgrade_ProducersDescr()
    {
        sqlQuery('ALTER TABLE producers DROP COLUMN descr');
        $this->upgradeOK();
    }

    public function check_TemplatesText()
    {
        return !$this->checkColumnExists('templates', 'text');
    }

    /** Delete text from templates */
    public function upgrade_TemplatesText()
    {
        sqlQuery('ALTER TABLE templates DROP COLUMN text');
        $this->upgradeOK();
    }

    public function check_DataDeliveryTypePayment()
    {
        return $this->checkColumnExists('delivery_type_payment', 'data');
    }

    /** Add 'data' column to table 'delivery_type_payment' */
    public function upgrade_DataDeliveryTypePayment()
    {
        sqlQuery('ALTER TABLE delivery_type_payment ADD COLUMN data MEDIUMTEXT DEFAULT NULL');

        $this->upgradeOK();
    }

    public function check_cartTableForeignKeyForUser()
    {
        return $this->checkConstraintRule('cart', 'cart_ibfk_3');
    }

    /** Add 'cart_ibfk_3' foreign key to table 'cart' and update id_user to null where id_user = 0*/
    public function upgrade_cartTableForeignKeyForUser()
    {
        sqlQuery('ALTER TABLE cart MODIFY COLUMN id_user INT UNSIGNED NULL');
        sqlQuery('UPDATE cart SET id_user = NULL WHERE id_user = 0');
        sqlQuery('
            DELETE c FROM cart c
                LEFT JOIN users u on u.id = c.id_user
            WHERE c.id_user IS NOT NULL AND u.id IS NULL
         ');
        sqlQuery('ALTER TABLE cart ADD CONSTRAINT cart_ibfk_3 FOREIGN KEY (id_user) REFERENCES users(id) ON DELETE CASCADE');

        $this->upgradeOK();
    }

    public function check_templatesCategoriesPositions(): bool
    {
        return !$this->checkTableExists('templates_categories') && $this->checkColumnExists('templates_categories', 'product_detail_position');
    }

    /** Add `product_detail_position` column to template_categories */
    public function upgrade_templatesCategoriesPositions(): void
    {
        sqlQuery("ALTER TABLE `templates_categories` ADD COLUMN `product_detail_position` VARCHAR(63) NOT NULL DEFAULT '';");

        $this->upgradeOK();
    }

    public function check_addOrderPaymentsIndexOnDate(): bool
    {
        return $this->checkIndexNameExists('order_payments', 'order_payments_date_index');
    }

    /** Add `order_payments_date_index` index to order_payments on date */
    public function upgrade_addOrderPaymentsIndexOnDate(): void
    {
        sqlQuery('create index order_payments_date_index on order_payments (date)');

        $this->upgradeOK();
    }
}