<?php

/**
 * Created by PhpStorm.
 * User: ondra
 * Date: 10.11.17
 * Time: 10:56.
 */

namespace KupShop\IndexedFilterBundle\Resources\upgrade;

use KupShop\IndexedFilterBundle\Util\FilterUrlDefaultValue;

class IndexedFiltersUpgrade extends \UpgradeNew
{
    protected $priority = 100;

    private static function getSectionsTables()
    {
        return [
            'parameters_sections',
            'products_variations_sections',
        ];
    }

    private static function getTables()
    {
        return [
            'producers',
            'sections',
            'parameters_list',
        ];
    }

    private static function getTables_translations()
    {
        return [
            'producers_translations',
            'sections_translations',
            'parameters_list_translations',
        ];
    }

    public function check_filterColumns()
    {
        return $this->checkColumnExists('parameters_list', 'filter_url');
    }

    /** Filters: add filter_url and title column into defined tables */
    public function upgrade_filterColumns()
    {
        foreach (self::getTables() as $table) {
            sqlQuery(
                'ALTER TABLE '.$table.' ADD filter_url VARCHAR(50) DEFAULT NULL,
                                        ADD title VARCHAR(50) DEFAULT NULL,
                                        ADD INDEX filter_url (filter_url)'
            );
        }

        $this->upgradeOK();
    }

    public function check_filterColumns_translations()
    {
        return findModule(\Modules::TRANSLATIONS) && $this->checkColumnExists('parameters_list_translations', 'filter_url');
    }

    /** Filters: add filter_url and title column into defined tables */
    public function upgrade_filterColumns_translations()
    {
        foreach (self::getTables_translations() as $table) {
            sqlQuery(
                'ALTER TABLE '.$table.' ADD filter_url VARCHAR(50) DEFAULT NULL,
                                        ADD title VARCHAR(50) DEFAULT NULL,
                                        ADD INDEX filter_url (filter_url)'
            );
        }

        $this->upgradeOK();
    }

    public function check_filterIndexColumns()
    {
        return $this->checkColumnExists('parameters_sections', 'indexing');
    }

    /** Filters: add indexing and to_title columns into defined tables */
    public function upgrade_filterIndexColumns()
    {
        foreach (self::getSectionsTables() as $table) {
            sqlQuery(
                'ALTER TABLE '.$table.' ADD indexing ENUM("Y", "N") DEFAULT "N",
                                        ADD to_title ENUM("Y", "N") DEFAULT "N"'
            );
        }

        $this->upgradeOK();
    }

    public function check_filterIndexColumnsProducers()
    {
        return $this->checkColumnExists('sections', 'producers_indexing');
    }

    /** Filters: add producers_indexing and producers_to_title */
    public function upgrade_filterIndexColumnsProducers()
    {
        sqlQuery(
            'ALTER TABLE sections ADD producers_indexing ENUM("Y", "N") DEFAULT "N",
                                  ADD producers_to_title ENUM("Y", "N") DEFAULT "N"'
        );

        $this->upgradeOK();
    }

    public function check_productsVariationsChoicesValues()
    {
        return $this->checkColumnExists('products_variations_choices_values', 'filter_url');
    }

    /** Filters: add filter_url and title into products_variations_choices_values */
    public function upgrade_productsVariationsChoicesValues()
    {
        sqlQuery(
            'ALTER TABLE products_variations_choices_values 
                ADD filter_url VARCHAR(50) DEFAULT NULL,
                ADD title VARCHAR(50) DEFAULT NULL,
                ADD INDEX filter_url (filter_url)'
        );

        $this->upgradeOK();
    }

    public function check_productsVariationsChoicesValues_translations()
    {
        return findModule(\Modules::TRANSLATIONS) && $this->checkColumnExists('products_variations_choices_values_translations', 'filter_url');
    }

    /** Filters: add filter_url and title into products_variations_choices_values_translations */
    public function upgrade_productsVariationsChoicesValues_translations()
    {
        sqlQuery(
            'ALTER TABLE products_variations_choices_values_translations 
                ADD filter_url VARCHAR(50) DEFAULT NULL,
                ADD title VARCHAR(50) DEFAULT NULL,
                ADD INDEX filter_url (filter_url)'
        );

        $this->upgradeOK();
    }

    public function check_filter_url()
    {
        return !$this->checkColumnExists('parameters_list', 'filter_url');
    }

    /** Filters: insert default values into filter_url */
    public function upgrade_filter_url()
    {
        $upgrade = FilterUrlDefaultValue::upgrade_filter_url_All();
        print_r($upgrade);

        $this->upgradeOK();
    }

    public function check_indexed_filters_content()
    {
        return $this->checkTableExists('indexed_filters_content');
    }

    /** Indexed filters: create `indexed_filters_content` table */
    public function upgrade_indexed_filters_content()
    {
        sqlQuery('create table indexed_filters_content (
                id               int auto_increment primary key,
                id_section       int          null,
                id_producer      int unsigned null,
                parameter1       int          null,
                parameter2       int          null,
                parameter3       int          null,
                title            varchar(255) null,
                meta_title       varchar(255) null,
                meta_description text         null,
                id_block         int unsigned null,
                constraint indexed_filters_content_sections_id_fk
                    foreign key (id_section) references sections (id)
                        on update cascade on delete cascade
            )'
        );

        $this->upgradeOK();
    }

    public function check_indexed_filters_content_producersForeignKey()
    {
        return findModule(\Modules::PRODUCERS) && $this->checkForeignKeyExists('indexed_filters_content', 'id_producer');
    }

    /** Add producers foreign key into 'indexed_filters_content' */
    public function upgrade_indexed_filters_content_producersForeignKey()
    {
        sqlQuery('ALTER TABLE indexed_filters_content 
            ADD CONSTRAINT indexed_filters_content_producers_id_fk 
            FOREIGN KEY (id_producer) REFERENCES producers(id) ON DELETE CASCADE ON UPDATE CASCADE');

        $this->upgradeOK();
    }

    public function check_indexed_filters_content_parametersForeignKey()
    {
        return findModule(\Modules::PRODUCTS_PARAMETERS) && $this->checkForeignKeyExists('indexed_filters_content', 'parameter1');
    }

    /** Add parameters foreign keys into 'indexed_filters_content' */
    public function upgrade_indexed_filters_content_parametersForeignKey()
    {
        sqlQuery('ALTER TABLE indexed_filters_content 
            ADD CONSTRAINT indexed_filters_content_parameter1_fk 
            FOREIGN KEY (parameter1) REFERENCES parameters_list(id) ON DELETE CASCADE ON UPDATE CASCADE');

        sqlQuery('ALTER TABLE indexed_filters_content 
            ADD CONSTRAINT indexed_filters_content_parameter2_fk 
            FOREIGN KEY (parameter2) REFERENCES parameters_list(id) ON DELETE CASCADE ON UPDATE CASCADE');

        sqlQuery('ALTER TABLE indexed_filters_content 
            ADD CONSTRAINT indexed_filters_content_parameter3_fk 
            FOREIGN KEY (parameter3) REFERENCES parameters_list(id) ON DELETE CASCADE ON UPDATE CASCADE');

        $this->upgradeOK();
    }

    public function check_indexed_filters_content_blocksForeignKey()
    {
        return $this->checkForeignKeyExists('indexed_filters_content', 'id_block');
    }

    /** Add blocks foreign key into 'indexed_filters_content' */
    public function upgrade_indexed_filters_content_blocksForeignKey()
    {
        sqlQuery('ALTER TABLE indexed_filters_content 
            ADD CONSTRAINT indexed_filters_content_blocks_id_fk 
            FOREIGN KEY (id_block) REFERENCES blocks(id) ON DELETE SET NULL ON UPDATE CASCADE');

        $this->upgradeOK();
    }

    public function check_indexed_filters_content_translations()
    {
        return findModule(\Modules::TRANSLATIONS) && $this->checkTableExists('indexed_filters_content_translations');
    }

    /** Create indexed_filters_content_translations table */
    public function upgrade_indexed_filters_content_translations()
    {
        sqlQuery('CREATE TABLE IF NOT EXISTS indexed_filters_content_translations 
            (
                id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                id_indexed_filter INT NOT NULL,
                id_language VARCHAR(2) NOT NULL,
                id_admin INT DEFAULT NULL,
                created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated DATETIME DEFAULT NULL,
                title            VARCHAR(255) DEFAULT NULL,
                meta_title       VARCHAR(255) DEFAULT NULL,
                meta_description TEXT DEFAULT NULL, 
                FOREIGN KEY (id_indexed_filter)
                  REFERENCES indexed_filters_content (id)
                  ON DELETE CASCADE ON UPDATE CASCADE
            );
            
            CREATE UNIQUE INDEX indexed_filters_content_translations_uindex
              ON indexed_filters_content_translations (id_indexed_filter, id_language);'
        );

        $this->upgradeOK();
    }
}
