<?php

class Upgrade38 extends UpgradeNew
{
    public function check_DatesOnProductsVariations()
    {
        return $this->checkColumnExists('products_variations', 'updated');
    }

    /** Create add date to products variations */
    public function upgrade_DatesOnProductsVariations()
    {
        sqlQuery("ALTER TABLE products_variations ADD `updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
                  ALTER TABLE products_variations ADD `date_added` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'");

        $this->upgradeOK();
    }

    public function check_utfToDatabase()
    {
        return $this->checkDatabaseEncoding('utf8mb4');
    }

    /** Set UTF to database */
    public function upgrade_utfToDatabase()
    {
        sqlQuery("ALTER DATABASE `{$GLOBALS['cfg']['Connection']['database']}` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci");

        $this->upgradeOK();
    }

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

    /** Set UTF to all tables */
    public function upgrade_utfToAllTables()
    {
        sqlQuery('SET FOREIGN_KEY_CHECKS=0;');

        $platform = sqlGetConnection()->getDatabasePlatform();
        $platform->registerDoctrineTypeMapping('enum', 'string');

        foreach (sqlGetConnection()->getSchemaManager()->listTables() as $table) {
            try {
                sqlQuery("ALTER TABLE {$table->getName()} CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci");
            } catch (Exception $e) {
                echo $e->getMessage();
            }
        }

        $this->upgradeOK();
    }

    public function check_AddFeedTokenField()
    {
        return findModule(\Modules::XML_FEEDS_B2B, \Modules::SUB_SECURE) && $this->checkColumnExists('users', 'feed_token');
    }

    /** Add feeds token field into users */
    public function upgrade_AddFeedTokenField()
    {
        sqlQuery('ALTER TABLE users ADD `feed_token` varchar(60) DEFAULT NULL;');
        sqlQuery('ALTER TABLE users ADD `feed_in_store` INT(1) DEFAULT 1;');

        $this->upgradeOK();
    }

    public function check_OrderItemsDatetime()
    {
        return $this->checkColumnType('order_items', 'date', 'TIMESTAMP');
    }

    /** Order items change date to datetime */
    public function upgrade_OrderItemsDatetime()
    {
        sqlQuery('ALTER TABLE order_items MODIFY COLUMN `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;');

        $this->upgradeOK();
    }

    public function check_RemoveDuplicatePhotosRelation()
    {
        $id_variation = sqlNumRows(sqlQuery("SHOW FIELDS FROM photos_products_relation WHERE Field = 'id_variation'"));
        if (findModule(\Modules::PRODUCTS_VARIATIONS_PHOTOS) || $id_variation) {
            return sqlNumRows(sqlQuery('SELECT id_photo, id_product, COUNT(*) AS cnt 
                FROM photos_products_relation 
                WHERE id_variation IS NULL 
                GROUP BY id_product, id_photo 
                HAVING COUNT(*) > 1;')) > 0;
        }

        return false;
    }

    /** Remove duplicate photos_products_relation */
    public function upgrade_RemoveDuplicatePhotosRelation()
    {
        $duplicates = sqlQuery('SELECT id_photo, id_product, COUNT(*) as cnt 
                FROM photos_products_relation 
                WHERE id_variation IS NULL 
                GROUP BY id_product, id_photo 
                HAVING COUNT(*) > 1
                ORDER BY position');

        foreach ($duplicates as $row) {
            sqlQuery('DELETE FROM photos_products_relation
                  WHERE id_variation IS NULL AND id_photo=:id_photo AND id_product=:id_product
                  ORDER BY position 
                  LIMIT '.($row['cnt'] - 1), $row);
        }

        $this->upgradeOK();
    }

    public function check_OrdersAdmin()
    {
        return $this->checkColumnExists('orders', 'admin');
    }

    /** Add admin column to order */
    public function upgrade_OrdersAdmin()
    {
        sqlQuery('ALTER TABLE orders ADD `admin` INT DEFAULT NULL');
        sqlQuery('ALTER TABLE orders ADD FOREIGN KEY (`admin`) REFERENCES `admins`(`id`) ON DELETE SET NULL ON UPDATE CASCADE');

        $this->upgradeOK();
    }

    public function check_OrdersPOS()
    {
        return (findModule(Modules::POS) || findModule(Modules::NEW_POS)) && $this->checkColumnExists('orders', 'pos');
    }

    /** Add pos column to order */
    public function upgrade_OrdersPOS()
    {
        sqlQuery('ALTER TABLE orders ADD `pos` INT NOT NULL DEFAULT 0');

        $this->upgradeOK();
    }

    public function check_DiscountToOrderItems()
    {
        return $this->checkColumnExists('order_items', 'discount');
    }

    /** Order items add discount field*/
    public function upgrade_DiscountToOrderItems()
    {
        sqlQuery('ALTER TABLE order_items ADD COLUMN `discount` DECIMAL(15,4) NOT NULL DEFAULT 0');

        $this->upgradeOK();
    }

    public function check_DiscountToOrderItemsEdit()
    {
        return findModule(Modules::ORDER_EDIT) && $this->checkColumnExists('order_edit', 'discount');
    }

    /** order_edit add discount field*/
    public function upgrade_DiscountToOrderItemsEdit()
    {
        sqlQuery('ALTER TABLE order_edit ADD COLUMN `discount` DECIMAL(15,4) NOT NULL DEFAULT 0');

        $this->upgradeOK();
    }

    private $pos_delivery_id;
    private $pos_payment_cash;
    private $pos_payment_card;
    private $pos_payment_transfer;

    //    public function check_PosDeliveryTypeClass()
    //    {
    //        if (!findModule(Modules::POS)) {
    //            return false;
    //        }
    //        $this->pos_delivery_id = sqlQueryBuilder()
    //            ->select('id')
    //            ->from('delivery_type_delivery')
    //            ->where('name=:name')
    //            ->setParameter('name', 'Osobní Odběr')
    //            ->execute()
    //            ->fetchColumn();
    //
    //        return !$this->pos_delivery_id;
    //    }
    //
    //    /** Add class OsobniOdber to delivery type osobni odber */
    //    public function upgrade_PosDeliveryTypeClass()
    //    {
    //        sqlQueryBuilder()
    //            ->update('delivery_type_delivery')
    //            ->values([
    //                'name' => ':name',
    //                'class' => ':class',
    //            ])->where('name=Osobní odběr')
    //            ->setParameters([
    //                'name' => 'Osobní odběr',
    //                'class' => 'OsobniOdber',
    //            ])
    //            ->execute();
    //
    //        $this->pos_delivery_id = sqlInsertId();
    //
    //        $this->upgradeOK();
    //    }
    //
    //    public function check_PosDeliveryTypes()
    //    {
    //        if (!findModule(Modules::POS) || $this->pos_delivery_id) {
    //            return false;
    //        }
    //        $this->pos_delivery_id = sqlQueryBuilder()
    //            ->select('id')
    //            ->from('delivery_type_delivery')
    //            ->where('class=:class')
    //            ->setParameter('class', 'OsobniOdber')
    //            ->execute()
    //            ->fetchColumn();
    //
    //        return !$this->pos_delivery_id;
    //    }
    //
    //    /** Create missing delivery required in POS */
    //    public function upgrade_PosDeliveryTypes()
    //    {
    //        sqlQueryBuilder()
    //            ->insert('delivery_type_delivery')
    //            ->values([
    //                'name' => ':name',
    //                'class' => ':class',
    //            ])
    //            ->setParameters([
    //                'name' => 'Osobní odběr',
    //                'class' => 'OsobniOdber',
    //            ])
    //            ->execute();
    //
    //        $this->pos_delivery_id = sqlInsertId();
    //
    //        $this->upgradeOK();
    //    }
    //
    //    public function check_PosPaymentCash()
    //    {
    //        if (!findModule(Modules::POS)) {
    //            return false;
    //        }
    //        $this->pos_payment_cash = sqlQueryBuilder()
    //            ->select('id')
    //            ->from('delivery_type_payment')
    //            ->where('class=:class')
    //            ->setParameter('class', 'Hotovost')
    //            ->execute()
    //            ->fetchColumn();
    //
    //        return !$this->pos_payment_cash;
    //    }
    //
    //    /** Create missing payment required in POS - cash*/
    //    public function upgrade_PosPaymentCash()
    //    {
    //        sqlQueryBuilder()
    //            ->insert('delivery_type_payment')
    //            ->values([
    //                'name' => ':name',
    //                'class' => ':class',
    //            ])
    //            ->setParameters([
    //                'name' => 'Hotově',
    //                'class' => 'Hotovost',
    //            ])
    //            ->execute();
    //
    //        $this->pos_payment_cash = sqlInsertId();
    //
    //        $this->upgradeOK();
    //    }
    //
    //    public function check_PosPaymentCard()
    //    {
    //        if (!findModule(Modules::POS)) {
    //            return false;
    //        }
    //        $this->pos_payment_card = sqlQueryBuilder()
    //            ->select('id')
    //            ->from('delivery_type_payment')
    //            ->where('class=:class')
    //            ->setParameter('class', 'PlatebniKarta')
    //            ->execute()
    //            ->fetchColumn();
    //
    //        return !$this->pos_payment_card;
    //    }
    //
    //    /** Create missing payment required in POS - card*/
    //    public function upgrade_PosPaymentCard()
    //    {
    //        sqlQueryBuilder()
    //            ->insert('delivery_type_payment')
    //            ->values([
    //                'name' => ':name',
    //                'class' => ':class',
    //            ])
    //            ->setParameters([
    //                'name' => 'Platební kartou',
    //                'class' => 'PlatebniKarta',
    //            ])
    //            ->execute();
    //
    //        $this->pos_payment_card = sqlInsertId();
    //
    //        $this->upgradeOK();
    //    }
    //
    //    public function check_PosPaymentTranfer()
    //    {
    //        if (!findModule(Modules::POS)) {
    //            return false;
    //        }
    //        $this->pos_payment_transfer = sqlQueryBuilder()
    //            ->select('id')
    //            ->from('delivery_type_payment')
    //            ->where('class=:class')
    //            ->setParameter('class', 'Prevodem')
    //            ->execute()
    //            ->fetchColumn();
    //
    //        return !$this->pos_payment_transfer;
    //    }
    //
    //    /** Create missing payment required in POS - transfer*/
    //    public function upgrade_PosPaymentTranfer()
    //    {
    //        sqlQueryBuilder()
    //            ->insert('delivery_type_payment')
    //            ->values([
    //                'name' => ':name',
    //                'class' => ':class',
    //            ])
    //            ->setParameters([
    //                'name' => 'Převodem',
    //                'class' => 'Prevodem',
    //            ])
    //            ->execute();
    //
    //        $this->pos_payment_transfer = sqlInsertId();
    //
    //        $this->upgradeOK();
    //    }
    //
    //    public function check_PosDeliveryTypesCash()
    //    {
    //        if (!findModule(Modules::POS)) {
    //            return false;
    //        }
    //        $cash = sqlQueryBuilder()
    //            ->select('id')
    //            ->from('delivery_type')
    //            ->where('id_delivery=:id_delivery AND id_payment=:id_payment')
    //            ->setParameters(['id_payment' => $this->pos_payment_cash, 'id_delivery' => $this->pos_delivery_id])
    //            ->execute()
    //            ->fetchColumn();
    //
    //        return !$cash;
    //    }
    //
    //    /** Create missing delivery types required in POS - CASH */
    //    public function upgrade_PosDeliveryTypesCash()
    //    {
    //        $pos = returnSQLResult('SELECT MAX(list_order) FROM delivery_type');
    //        sqlQueryBuilder()
    //            ->insert('delivery_type')
    //            ->values([
    //                'id_delivery' => $this->pos_delivery_id,
    //                'id_payment' => $this->pos_payment_cash,
    //                'figure' => '"N"',
    //                'list_order' => $pos,
    //            ])
    //            ->execute();
    //
    //        $this->upgradeOK();
    //    }
    //
    //    public function check_PosDeliveryTypesCard()
    //    {
    //        if (!findModule(Modules::POS)) {
    //            return false;
    //        }
    //        $card = sqlQueryBuilder()
    //            ->select('id')
    //            ->from('delivery_type')
    //            ->where('id_delivery=:id_delivery AND id_payment=:id_payment')
    //            ->setParameters(['id_payment' => $this->pos_payment_card, 'id_delivery' => $this->pos_delivery_id])
    //            ->execute()
    //            ->fetchColumn();
    //
    //        return !$card;
    //    }
    //
    //    /** Create missing delivery types required in POS - card */
    //    public function upgrade_PosDeliveryTypesCard()
    //    {
    //        $pos = returnSQLResult('SELECT MAX(list_order) FROM delivery_type');
    //        sqlQueryBuilder()
    //            ->insert('delivery_type')
    //            ->values([
    //                'id_delivery' => $this->pos_delivery_id,
    //                'id_payment' => $this->pos_payment_card,
    //                'figure' => '"N"',
    //                'list_order' => $pos,
    //            ])
    //            ->execute();
    //
    //        $this->upgradeOK();
    //    }
    //
    //    public function check_PosDeliveryTypesTransfer()
    //    {
    //        if (!findModule(Modules::POS)) {
    //            return false;
    //        }
    //        $card = sqlQueryBuilder()
    //            ->select('id')
    //            ->from('delivery_type')
    //            ->where('id_delivery=:id_delivery AND id_payment=:id_payment')
    //            ->setParameters(['id_payment' => $this->pos_payment_transfer, 'id_delivery' => $this->pos_delivery_id])
    //            ->execute()
    //            ->fetchColumn();
    //
    //        return !$card;
    //    }
    //
    //    /** Create missing delivery types required in POS - transfer */
    //    public function upgrade_PosDeliveryTypesTransfer()
    //    {
    //        $pos = returnSQLResult('SELECT MAX(list_order) FROM delivery_type');
    //        sqlQueryBuilder()
    //            ->insert('delivery_type')
    //            ->values([
    //                'id_delivery' => $this->pos_delivery_id,
    //                'id_payment' => $this->pos_payment_transfer,
    //                'figure' => '"N"',
    //                'list_order' => $pos,
    //            ])
    //            ->execute();
    //
    //        $this->upgradeOK();
    //    }
}