<?php

namespace KupShop\ElninoBundle\Resources\upgrade;

use KupShop\AdminBundle\Util\ProductUtils;
use KupShop\KupShopBundle\Util\Compat\ServiceContainer;

class ProductUpgrade extends \UpgradeNew
{
    public function check_indexOnSizeColumn()
    {
        return $this->checkIndexNameExists('products', 'size');
    }

    /** Add index on 'size' column in 'products' table */
    public function upgrade_indexOnSizeColumn()
    {
        sqlQuery('CREATE INDEX size ON products(size)');

        $this->upgradeOK();
    }

    public function check_descrCollation()
    {
        return $this->checkColumnCollation('products', 'long_descr', 'utf8mb4_general_ci');
    }

    /**
     * Change collation of short_descr, long_descr, parameters in products to utf8mb4_general_ci.
     */
    public function upgrade_descrCollation()
    {
        sqlQuery('ALTER TABLE products MODIFY short_descr TINYTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci');
        sqlQuery('ALTER TABLE products MODIFY long_descr TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci');
        sqlQuery('ALTER TABLE products MODIFY parameters TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci');

        $this->upgradeOK();
    }

    public function check_titleLength()
    {
        return $this->checkColumnType('products', 'title', 'varchar(255)');
    }

    /** Increase products.title length to 255 */
    public function upgrade_titleLength()
    {
        sqlQuery("ALTER TABLE products MODIFY title VARCHAR(255) CHARACTER SET utf8mb4 DEFAULT '' NOT NULL");

        $this->upgradeOK();
    }

    public function check_NewlyAddedColumn()
    {
        return $this->checkColumnExists('products', 'newly_added');
    }

    /** add 'newly_added' column into products */
    public function upgrade_NewlyAddedColumn()
    {
        sqlQuery('ALTER TABLE products ADD COLUMN newly_added TINYINT DEFAULT 1');
        sqlQuery('UPDATE products SET newly_added = 0');

        $this->upgradeOK();
    }

    public function check_IdSerieColumn()
    {
        return $this->checkColumnExists('products', 'id_serie');
    }

    /** Add id_serie column to product */
    public function upgrade_IdSerieColumn()
    {
        sqlQuery('ALTER TABLE products ADD COLUMN id_serie INT(10) UNSIGNED AFTER id_model;');
        sqlQuery('ALTER TABLE products ADD INDEX id_serie (id_serie);');
        sqlQuery('ALTER TABLE products ADD FOREIGN KEY (id_serie) REFERENCES abra_series (id_serie) ON DELETE SET NULL');
        sqlQuery('UPDATE products p
        LEFT JOIN abra_models abm ON p.id_model = abm.id_abra
        INNER JOIN abra_series series ON abm.id_serie = series.id_abra
        SET p.id_serie = series.id_serie;');

        $this->upgradeOK();
    }

    public function check_IdEqualsCode()
    {
        $db = $GLOBALS['cfg']['Connection']['database'];

        // Autoincrement je zasekly dole a prirazuje IDcka ktery se pouzijou
        return returnSQLResult("SELECT auto_increment FROM information_schema.TABLES WHERE TABLE_NAME ='products' AND TABLE_SCHEMA='{$db}'") < 1000000;
    }

    /** Ensure ID == CODE */
    public function upgrade_IdEqualsCode()
    {
        sqlQuery('ALTER TABLE products AUTO_INCREMENT 100000;');

        sqlQuery('DELETE FROM products WHERE code=""');
        $SQL = sqlQuery('SELECT p.code FROM products p WHERE code != id AND CONVERT(CONVERT(code, INT), CHAR) = code ORDER BY id ASC;');
        foreach ($SQL as $product) {
            try {
                sqlQuery('UPDATE products p SET p.id = p.code WHERE code=:code', $product);
            } catch (\Exception $exception) {
                $this->addNote($exception->getMessage());
            }
        }

        $this->upgradeOK();
    }

    public function check_productSetSize()
    {
        return $this->checkColumnType('products', 'set', 'varchar(255)');
    }

    /** Increase size of products.set and products_translations.set to 255 */
    public function upgrade_productSetSize()
    {
        sqlQuery('ALTER TABLE products MODIFY `set` VARCHAR(255) NULL;');
        if (!$this->checkColumnExists('products_translations', 'set')) { // if column exists then
            sqlQuery('ALTER TABLE products_translations MODIFY `set` VARCHAR(255) DEFAULT NULL;');
        }
        sqlQuery('ALTER TABLE products_variations MODIFY `set` VARCHAR(255) charset utf8mb4 NULL;');
        $this->upgradeOK();
    }

    public function check_delayedUpdatesPK()
    {
        return $this->checkColumnExists('abra_delayed_update', 'id');
    }

    /** Add PK to abra_delayed_update */
    public function upgrade_delayedUpdatesPK()
    {
        sqlQuery('ALTER TABLE abra_delayed_update ADD `id` INT AUTO_INCREMENT PRIMARY KEY FIRST;');

        $this->upgradeOK();
    }

    public function check_productInStore15_remove()
    {
        // Nechci tyhle sloupce mazat na koza-it. Ať to nemusím celý předělávat, když Elnino neví vůbec co se tam má dít.
        // Až si to Garnát rozmyslí, snad tu customizaci smažeme a nebude třeba nic dělat.
        // A ani na parfimo-de to nechci předělávat, když se ruší.
        if (in_array(findModule('abra', 'web'), ['parfimo.it', 'parfimo.de'])) {
            return false;
        }

        return !$this->checkColumnExists('products', 'in_store_15');
    }

    /** Move products.in_store_* to custom data */
    public function upgrade_productInStore15_remove()
    {
        $productsWithStore = sqlQueryBuilder()->select('id, in_store_1, in_store_6, in_store_15')
            ->from('products')
            ->where('in_store_1 > 0 OR in_store_6 > 0 OR in_store_15 > 0')
            ->execute();
        /** @var ProductUtils $utils */
        $utils = ServiceContainer::getService(ProductUtils::class);

        // Move stores to custom data
        foreach ($productsWithStore as $row) {
            $utils->updateCustomData($row['id'], ['elnino_stores' => [
                '1' => $row['in_store_1'],
                '6' => $row['in_store_6'],
                '15' => $row['in_store_15'],
            ]]);
        }

        sqlQuery('ALTER TABLE products DROP COLUMN in_store_1;');
        sqlQuery('ALTER TABLE products DROP COLUMN in_store_6;');
        sqlQuery('ALTER TABLE products DROP COLUMN in_store_15;');

        $this->upgradeOK();
    }

    public function check_GPSRSupplierFields()
    {
        return $this->checkColumnExists('products', 'sup_name');
    }

    /** Add fields sup_name, sup_addr, sup_contact */
    public function upgrade_GPSRSupplierFields()
    {
        sqlQuery('ALTER TABLE products ADD `sup_name` VARCHAR(191) DEFAULT NULL;');
        sqlQuery('ALTER TABLE products ADD `sup_addr` VARCHAR(191) DEFAULT NULL;');
        sqlQuery('ALTER TABLE products ADD `sup_contact` VARCHAR(191) DEFAULT NULL;');

        $this->upgradeOK();
    }
}
