<?php

use KupShop\FeedsBundle\Utils\FeedUtils;

class FeedsUpgrade extends UpgradeNew
{
    protected $priority = 100;

    public function check_FeedsTable()
    {
        return $this->checkTableExists('feeds');
    }

    /** Create table feeds */
    public function upgrade_FeedsTable()
    {
        sqlQuery('CREATE TABLE IF NOT EXISTS feeds
            (
                id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                id_language VARCHAR(2) DEFAULT NULL,
                id_currency VARCHAR(3) DEFAULT NULL,
                type VARCHAR(255) NOT NULL,
                name VARCHAR(50) NOT NULL,
                active TINYINT(1) DEFAULT 0,
                created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated DATETIME DEFAULT NULL,
                regenerated DATETIME DEFAULT NULL,
                last_download DATETIME DEFAULT NULL,
                expensive TINYINT(1) NOT NULL DEFAULT 0,
                restrict_ip TINYINT(1) DEFAULT 0,
                template LONGTEXT DEFAULT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
        ');

        $this->upgradeOK();
    }

    public function check_FeedLanguageOptional()
    {
        return $this->checkColumnIsNull('feeds', 'id_language', true);
    }

    /** Make feeds.id_language and id_currency nullable */
    public function upgrade_FeedLanguageOptional()
    {
        sqlQuery('ALTER TABLE feeds MODIFY COLUMN id_language VARCHAR(2) DEFAULT NULL;');
        sqlQuery('ALTER TABLE feeds MODIFY COLUMN id_currency VARCHAR(3) DEFAULT NULL;');

        $this->upgradeOK();
    }

    public function check_FeedForeignKeys()
    {
        return $this->checkForeignKeyExists('feeds', 'id_language') && findModule(Modules::TRANSLATIONS);
    }

    /** Add foreign keys for id_language and id_currency to table feeds */
    public function upgrade_FeedForeignKeys()
    {
        sqlQuery('
            ALTER TABLE feeds
              ADD FOREIGN KEY (id_language)
                REFERENCES languages(id)
                ON DELETE RESTRICT ON UPDATE CASCADE;');
        sqlQuery('
            ALTER TABLE feeds
              ADD FOREIGN KEY (id_currency)
                REFERENCES currencies(id)
                ON DELETE RESTRICT ON UPDATE CASCADE;');
        $this->upgradeOK();
    }

    public function check_FeedProductsTable()
    {
        return $this->checkTableExists('feed_products');
    }

    /** Create table feed_products */
    public function upgrade_FeedProductsTable()
    {
        sqlQuery('CREATE TABLE IF NOT EXISTS feed_products
            (
                id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                id_feed INT UNSIGNED NOT NULL,
                id_product INT NOT NULL,
                id_section INT DEFAULT NULL,
                active TINYINT(1) DEFAULT NULL,
                title VARCHAR(255) DEFAULT NULL,
                cpc DECIMAL(15,4) DEFAULT NULL,
                FOREIGN KEY (id_feed)
                    REFERENCES feeds(id)
                    ON DELETE CASCADE ON UPDATE CASCADE,
                FOREIGN KEY (id_product)
                    REFERENCES products(id)
                    ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
            
            CREATE UNIQUE INDEX feed_products_id_feed_id_product_uindex
                ON feed_products (id_feed, id_product);
        ');

        $this->upgradeOK();
    }

    public function check_FeedsHashColumn()
    {
        return $this->checkColumnExists('feeds', 'hash');
    }

    /** Migrate feeds hashes into database */
    public function upgrade_FeedsHashColumn()
    {
        sqlQuery('ALTER TABLE feeds ADD COLUMN hash VARCHAR(60)');

        $feeds = sqlQueryBuilder()->select('id, created')->from('feeds')->execute();
        foreach ($feeds as $feed) {
            $hash = FeedUtils::createHash([
                $feed['id'],
                $feed['created'],
            ]);
            sqlQueryBuilder()
                ->update('feeds')->set('hash', ':hash')
                ->setParameter('hash', $hash)
                ->where(\Query\Operator::equals(['id' => $feed['id']]))
                ->execute();
        }
    }

    public function check_FeedProductsGiftTextColumn()
    {
        return findModule(Modules::FEED_GIFTS) && $this->checkColumnExists('feed_products', 'gift_text');
    }

    /** Add column gift_text to feed_products */
    public function upgrade_FeedProductsGiftTextColumn()
    {
        sqlQuery('ALTER TABLE feed_products ADD COLUMN gift_text VARCHAR(160) AFTER cpc');
        $this->upgradeOK();
    }

    public function check_FeedsSettingsColumn()
    {
        return $this->checkColumnExists('feeds', 'settings');
    }

    /** Add settings column into feeds table */
    public function upgrade_FeedsSettingsColumn()
    {
        sqlQuery('ALTER TABLE feeds ADD COLUMN settings MEDIUMTEXT NULL');
        $this->upgradeOK();
    }

    public function check_OnlyFeedsProductsColumn()
    {
        return $this->checkColumnExists('feeds', 'only_feed_products');
    }

    /** Add 'only_feed_products' column into 'feeds' table */
    public function upgrade_OnlyFeedsProductsColumn()
    {
        sqlQuery('ALTER TABLE feeds ADD COLUMN only_feed_products ENUM("Y", "N") DEFAULT "N"');

        $this->upgradeOK();
    }

    //    public function check_FeedIPsTable()
    //    {
    //        return $this->checkTableExists('feed_ips');
    //    }
    //
    //    /** Create table feed_ips */
    //    public function upgrade_FeedIPsTable()
    //    {
    //        sqlQuery('CREATE TABLE IF NOT EXISTS feed_ips
    //            (
    //                id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    //                id_feed INT UNSIGNED NOT NULL,
    //                ip VARCHAR(255) NOT NULL,
    //                last_download DATETIME DEFAULT NULL,
    //                counter INT UNSIGNED NOT NULL DEFAULT 0,
    //                FOREIGN KEY (id_feed)
    //                    REFERENCES feeds(id)
    //                    ON DELETE CASCADE ON UPDATE CASCADE
    //            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    //
    //            CREATE UNIQUE INDEX id_feed_ip_uindex
    //                ON feed_ips (id_feed, ip);
    //        ');
    //
    //        $this->upgradeOK();
    //    }

    public function check_FeedMapsTable()
    {
        return $this->checkTableExists('feed_maps');
    }

    /** Create table feed_maps */
    public function upgrade_FeedMapsTable()
    {
        sqlQuery('CREATE TABLE IF NOT EXISTS feed_maps
            (
                id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(50) NOT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
            
            CREATE UNIQUE INDEX feed_maps_name_uindex ON feed_maps (name);
        ');

        $this->upgradeOK();
    }

    public function check_FeedMapPhrasesTable()
    {
        return $this->checkTableExists('feed_map_phrases');
    }

    /** Create table feed_map_phrases */
    public function upgrade_FeedMapPhrasesTable()
    {
        sqlQuery("CREATE TABLE IF NOT EXISTS feed_map_phrases
            (
                id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                id_feed_map INT UNSIGNED NOT NULL,
                original VARCHAR(255) NOT NULL,
                description VARCHAR(255) NOT NULL DEFAULT '',
                mapped VARCHAR(255) DEFAULT NULL,
                FOREIGN KEY (id_feed_map)
                    REFERENCES feed_maps(id)
                    ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

            CREATE UNIQUE INDEX id_feed_map_original_uindex
                ON feed_map_phrases (id_feed_map, original);
        ");

        $this->upgradeOK();
    }

    public function check_feedsReportColumn()
    {
        return $this->checkColumnExists('feeds', 'report');
    }

    /** Add 'report' column into table 'feeds' */
    public function upgrade_feedsReportColumn()
    {
        sqlQuery('ALTER TABLE feeds ADD COLUMN report LONGTEXT DEFAULT NULL');

        $this->upgradeOK();
    }

    public function check_feedsSectionMappingColumn()
    {
        return $this->checkColumnExists('feeds', 'section_mapping');
    }

    /** Add 'section_mapping' column into table 'feeds' */
    public function upgrade_feedsSectionMappingColumn()
    {
        sqlQuery("ALTER TABLE feeds ADD COLUMN section_mapping VARCHAR(255) NOT NULL DEFAULT ''");

        $this->upgradeOK();
    }

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

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

        $this->upgradeOK();
    }

    public function check_feedsExpensiveColumn()
    {
        return $this->checkColumnExists('feeds', 'expensive')
            && !$this->checkColumnExists('feeds', 'regeneration_interval');
    }

    /** Remove 'regeneration_interval', add 'expensive' in table 'feeds' */
    public function upgrade_feedsExpensiveColumn()
    {
        sqlQuery('ALTER TABLE feeds DROP COLUMN regeneration_interval;');
        sqlQuery('ALTER TABLE feeds ADD expensive TINYINT(1) NOT NULL DEFAULT 0 AFTER last_download;');

        $this->upgradeOK();
    }

    public function check_feedsCountryIDColumn()
    {
        return findModule(Modules::CURRENCIES) && $this->checkColumnExists('feeds', 'id_country');
    }

    /** Add 'id_country' column into table 'feeds' */
    public function upgrade_feedsCountryIDColumn()
    {
        sqlQuery('ALTER TABLE feeds ADD COLUMN id_country VARCHAR(10) DEFAULT NULL AFTER id_currency');
        sqlQuery('ALTER TABLE feeds ADD FOREIGN KEY (id_country)
            REFERENCES countries(id)
            ON DELETE CASCADE ON UPDATE CASCADE');

        $this->upgradeOK();
    }

    public function check_deprecatedFeeds()
    {
        $deprecated = ['facebook', 'glami', 'google', 'googleDelivery', 'heureka', 'heurekaDelivery', 'seznam', 'deprecated'];
        $count = sqlQueryBuilder()->select('COUNT(*)')->from('feeds')
            ->andWhere(\Query\Operator::inStringArray($deprecated, 'type'))
            ->execute()->fetchOne();

        return $count > 0;
    }

    /** Delete deprecated feeds */
    public function upgrade_deprecatedFeeds()
    {
        $deprecated = ['facebook', 'glami', 'google', 'googleDelivery', 'heureka', 'heurekaDelivery', 'seznam', 'deprecated'];
        $count = sqlQueryBuilder()->delete('feeds')
            ->andWhere(\Query\Operator::inStringArray($deprecated, 'type'))
            ->execute();

        $this->upgradeOK();
    }
}
