<?php

namespace KupShop\I18nBundle\Resources\upgrade;

use Query\Operator;

class TranslationsUpgrade extends \UpgradeNew
{
    protected $priority = 110;

    protected function isAllowed()
    {
        return findModule(\Modules::TRANSLATIONS);
    }

    // TODO: odebrat az to probehne na vsech shopech
    public function check_FixParametersListDescr()
    {
        $count = sqlQuery("SELECT id FROM parameters_list_translations WHERE description LIKE '%<p>%'")->rowCount();
        if ($count > 0) {
            return true;
        }

        return false;
    }

    /** Remove HTML elements from column 'description' in 'parameters_list_translations' */
    public function upgrade_FixParametersListDescr()
    {
        foreach (sqlQuery('SELECT id, description FROM parameters_list_translations') as $item) {
            $descr = trim(html_entity_decode(strip_tags($item['description'])));
            $this->updateSQL('parameters_list_translations', ['description' => $descr], ['id' => $item['id']]);
        }

        $this->upgradeOK();
    }

    public function check_clean_spaces()
    {
        return $this->checkFunctionExists('CLEAN_SPACES');
    }

    /** Add CLEAN_SPACES database function */
    public function upgrade_clean_spaces()
    {
        sqlQuery("CREATE FUNCTION IF NOT EXISTS CLEAN_SPACES(str TEXT) RETURNS TEXT
        DETERMINISTIC NO SQL
  BEGIN
    set str = strip_tags(str);
    set str = replace(str, '\\t', ' ');
    set str = replace(str, '\\r', ' ');
    set str = replace(str, '\\n', ' ');
    set str = replace(str, 0xC2A0, ' '); /* nbsp */
    set str = replace(str, '  ', ' <');
    set str = replace(str, '< ', '<');
    set str = replace(str, '<', '');
    return trim(str);
  END;");

        $this->upgradeOK();
    }

    public function check_sectionTranslationFigureColumn()
    {
        if (!findModule(\Modules::PRODUCTS_SECTIONS)) {
            return false;
        }

        return $this->checkColumnExists('sections_translations', 'figure');
    }

    /** Add 'figure' column into 'sections_translations' table */
    public function upgrade_sectionTranslationFigureColumn()
    {
        sqlQuery('ALTER TABLE sections_translations ADD COLUMN figure ENUM("Y", "N") DEFAULT NULL');

        $this->upgradeOK();
    }

    public function check_sectionTranslationUrlType()
    {
        if (!findModule(\Modules::PRODUCTS_SECTIONS)) {
            return false;
        }

        return !$this->checkColumnType('sections_translations', 'url', 'VARCHAR(50)');
    }

    /** Increase sections_translations.url column size */
    public function upgrade_sectionTranslationUrlType()
    {
        sqlQuery('ALTER TABLE sections_translations MODIFY url VARCHAR(255);');
        $this->upgradeOK();
    }

    public function check_productTranslationFigureColumn()
    {
        if (!findModule(\Modules::PRODUCTS)) {
            return false;
        }

        return $this->checkColumnExists('products_translations', 'figure');
    }

    /** Add 'figure' column into 'products_translations' table */
    public function upgrade_productTranslationFigureColumn()
    {
        sqlQuery('ALTER TABLE products_translations ADD COLUMN figure ENUM("Y", "N", "O") DEFAULT NULL');

        $this->upgradeOK();
    }

    public function check_parametersListTranslations_length()
    {
        return $this->checkColumnType('parameters_list_translations', 'value', 'VARCHAR(150)');
    }

    /** modify parameters_list_translations - value max length */
    public function upgrade_parametersListTranslations_length()
    {
        sqlQuery('ALTER TABLE parameters_list_translations MODIFY value VARCHAR(150) DEFAULT NULL;');

        $this->upgradeOK();
    }

    public function check_TranslationsFrontend()
    {
        return $this->checkTableExists('translations_frontend');
    }

    /** Add table for frontend translations - translations_frontend */
    public function upgrade_TranslationsFrontend()
    {
        sqlQuery("CREATE TABLE translations_frontend
(
    id INT PRIMARY KEY AUTO_INCREMENT,
    id_language VARCHAR(2),
    `key` TEXT,
    type ENUM('PHP', 'PO'),
    translation TEXT
);
CREATE UNIQUE INDEX translations_frontend_id_language_key_uindex ON translations_frontend (id_language, `key`);");

        $this->upgradeOK();
    }

    public function check_translationsFrontendOriginal()
    {
        return $this->checkColumnExists('translations_frontend', 'original');
    }

    /** Rename translations_frontend.key -> translations_frontend.original */
    public function upgrade_translationsFrontendOriginal()
    {
        sqlQuery('ALTER TABLE translations_frontend CHANGE `key` `original` TEXT NOT NULL');
        sqlQuery('ALTER TABLE translations_frontend CHANGE `id_language` `id_language` VARCHAR(2) NOT NULL');
        sqlQuery("ALTER TABLE translations_frontend CHANGE `type` `type` ENUM('PHP', 'PO') NOT NULL");

        $this->upgradeOK();
    }

    public function check_translationsFrontendIndex()
    {
        return $this->checkIndexNameExists('translations_frontend', 'translations_frontend_id_language_index');
    }

    public function upgrade_translationsFrontendIndex()
    {
        sqlQuery('CREATE INDEX translations_frontend_id_language_index ON translations_frontend (id_language);');
        $this->upgradeOK();
    }

    public function check_translationsFrontendForeignKey()
    {
        return $this->checkForeignKeyExists('translations_frontend', 'id_language');
    }

    /** Add foreign key to translations_frontend.id_language */
    public function upgrade_translationsFrontendForeignKey()
    {
        sqlQuery('DELETE FROM translations_frontend WHERE id_language NOT IN (SELECT id FROM languages); 
                  ALTER TABLE translations_frontend ADD CONSTRAINT fk_translations_frontend_language FOREIGN KEY (id_language) REFERENCES languages(id) ON DELETE CASCADE ON UPDATE CASCADE;');

        $this->upgradeOK();
    }

    public function check_BlockName100Chars()
    {
        return $this->checkColumnType('blocks_translations', 'name', 'VARCHAR(100)');
    }

    /** Make block title translation 100 chars long */
    public function upgrade_BlockName100Chars()
    {
        sqlQuery('
           ALTER TABLE blocks_translations
              CHANGE `name` `name` VARCHAR(100) NULL;');

        $this->upgradeOK();
    }

    public function check_SlidersDataColumn()
    {
        return $this->checkModule(\Modules::SLIDERS) && $this->checkColumnExists('sliders_translations', 'data');
    }

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

        $this->upgradeOK();
    }

    public function check_SlidersImagesDataColumn()
    {
        return $this->checkModule(\Modules::SLIDERS) && $this->checkColumnExists('sliders_images_translations', 'data');
    }

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

        $this->upgradeOK();
    }

    public function check_TranslationsJsonContent()
    {
        return $this->checkColumnExists('blocks_translations', 'json_content');
    }

    /** Add json_content into blocks_translations */
    public function upgrade_TranslationsJsonContent()
    {
        sqlQuery('ALTER TABLE blocks_translations ADD COLUMN json_content LONGTEXT NULL AFTER content');

        $this->upgradeOK();
    }

    public function check_PhotosTranslationsTable()
    {
        return $this->checkTableExists('photos_translations');
    }

    /** Create table photos_translations */
    public function upgrade_PhotosTranslationsTable()
    {
        sqlQuery('CREATE TABLE photos_translations (
            id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            id_photo INT(10) UNSIGNED NOT NULL,
            id_language VARCHAR(2) NOT NULL,
            id_admin INT DEFAULT NULL,
            created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated DATETIME DEFAULT NULL,
            descr TEXT DEFAULT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

        ALTER TABLE photos_translations ADD FOREIGN KEY (id_photo) 
            REFERENCES photos(id) ON DELETE CASCADE;
        
        ');

        $this->upgradeOK();
    }

    public function check_SectionsTranslationsNameLength()
    {
        return $this->checkColumnType('sections_translations', 'name', 'VARCHAR(200)');
    }

    /** sections_translations: make 'name' column same length as in 'sections' table */
    public function upgrade_SectionsTranslationsNameLength()
    {
        sqlQuery('ALTER TABLE sections_translations CHANGE COLUMN name name VARCHAR(200) DEFAULT NULL');

        $this->upgradeOK();
    }

    public function check_caseSensitivity()
    {
        return $this->checkColumnCollation('translations_frontend', 'original', 'utf8mb4_bin');
    }

    /** Enable case sensitive translations */
    public function upgrade_caseSensitivity()
    {
        sqlQuery('alter table translations_frontend modify original mediumtext BINARY');
        $this->upgradeOK();
    }

    public function check_sectionTranslationRedirectUrl()
    {
        if (!findModule(\Modules::PRODUCTS_SECTIONS)) {
            return false;
        }

        return $this->checkColumnExists('sections_translations', 'redirect_url');
    }

    /** add sections_translations.redirect_url column */
    public function upgrade_sectionTranslationRedirectUrl()
    {
        sqlQuery('ALTER TABLE sections_translations ADD COLUMN redirect_url VARCHAR(255) DEFAULT NULL;');
        $this->upgradeOK();
    }

    public function check_articlesBranchesTranslationFigureColumn()
    {
        if (!findModule(\Modules::ARTICLES)) {
            return false;
        }

        return $this->checkColumnExists('articles_branches_translations', 'figure');
    }

    /** Add 'figure' column into 'articles_branches_translations' table */
    public function upgrade_articlesBranchesTranslationFigureColumn()
    {
        sqlQuery('ALTER TABLE articles_branches_translations ADD COLUMN figure ENUM("Y", "N") DEFAULT NULL');

        $this->upgradeOK();
    }

    public function check_TemplatesTranslationFigureColumn()
    {
        if (!findModule(\Modules::TEMPLATES)) {
            return false;
        }

        return $this->checkColumnExists('templates_translations', 'figure');
    }

    /** Add 'figure' column into 'templates_translations' table */
    public function upgrade_TemplatesTranslationFigureColumn()
    {
        sqlQuery('ALTER TABLE templates_translations ADD COLUMN figure ENUM("Y", "N") DEFAULT NULL');

        $this->upgradeOK();
    }

    public function check_TranslationsAddress()
    {
        if (!findModule(\Modules::ARTICLES_AUTHORS)) {
            return false;
        }

        return $this->checkColumnExists('articles_authors_translations', 'address');
    }

    /** Add address into article_authors */
    public function upgrade_TranslationsAddress()
    {
        sqlQuery('ALTER TABLE articles_authors_translations ADD COLUMN address LONGTEXT NULL AFTER note');

        $this->upgradeOK();
    }

    public function check_ArticlesAuthorsTranslationsName(): bool
    {
        return findModule(\Modules::ARTICLES_AUTHORS) && $this->checkColumnExists('articles_authors_translations', 'name');
    }

    /** articles_authors_translations: add `name`, `surname` column */
    public function upgrade_ArticlesAuthorsTranslationsName(): void
    {
        sqlQuery('ALTER TABLE articles_authors_translations ADD COLUMN name VARCHAR(20) DEFAULT NULL AFTER updated');
        sqlQuery('ALTER TABLE articles_authors_translations ADD COLUMN surname VARCHAR(25) DEFAULT NULL AFTER name');

        $this->upgradeOK();
    }

    public function check_SettingsTranslationsHideNotTranslatedObjects(): bool
    {
        $qb = sqlQueryBuilder()
            ->select('value')
            ->from('settings_translations')
            ->where(Operator::equals(['key_name' => 'hide_not_translated_objects']));

        foreach ($qb->execute() as $item) {
            if ($item['value'] === '"Y"') {
                return true;
            }
        }

        return false;
    }

    /** Upgrade "hide_not_translated_objects" value in settings_translations */
    public function upgrade_SettingsTranslationsHideNotTranslatedObjects(): void
    {
        sqlQueryBuilder()
            ->update('settings_translations')
            ->directValues(['value' => '{"articles":"Y","products":"Y","set_categories":"Y"}'])
            ->where(Operator::equals(['key_name' => 'hide_not_translated_objects', 'value' => '"Y"']))
            ->execute();

        $this->upgradeOK();
    }

    public function check_ArticlesDataColumns()
    {
        return $this->checkColumnExists('articles_branches_translations', 'meta_title')
            && $this->checkColumnExists('articles_branches_translations', 'meta_title');
    }

    /** Add meta-title and meta-description to articles sections translations */
    public function upgrade_ArticlesDataColumns()
    {
        sqlQuery('ALTER TABLE articles_branches_translations ADD COLUMN meta_title VARCHAR(100) NULL;
         ALTER TABLE articles_branches_translations ADD COLUMN meta_description VARCHAR(250) NULL;');

        $this->upgradeOK();
    }
}
