<?php
/**
 * Copyright ETS Software Technology Co., Ltd
 *
 * NOTICE OF LICENSE
 *
 * This file is not open source! Each license that you purchased is only available for 1 website only.
 * If you want to use this file on more websites (or projects), you need to purchase additional licenses.
 * You are not allowed to redistribute, resell, lease, license, sub-license or offer our resources to any third party.
 *
 * DISCLAIMER
 *
 * Do not edit or add to this file if you wish to upgrade PrestaShop to newer
 * versions in the future.
 *
 * @author ETS Software Technology Co., Ltd
 * @copyright  ETS Software Technology Co., Ltd
 * @license    Valid for 1 website (or project) for each purchase of license
 */

if (!defined('_PS_VERSION_')) { exit; }

class EMTools
{
    /**
     * @return bool
     */
    public static function setProductOutOfStock()
    {
        $id_shop_group = version_compare(_PS_VERSION_, '1.5.0.10', '>=') ? 'id_shop_group' : 'id_group_shop';
        // Set quantity:
        $query = '
            INSERT IGNORE INTO `' . _DB_PREFIX_ . 'stock_available` (
                `id_stock_available`
              , `id_product`
              , `id_product_attribute`
              , `id_shop`
              , `' . pSQL($id_shop_group) . '`
              ,`quantity`
            )
            SELECT * FROM (
                SELECT
                  NULL as `id_stock_available`
                   , `id_product`
                   , 0 as `id_product_attribute`
                   , `id_shop`
                   , `' . pSQL($id_shop_group) . '`
                   , SUM(`quantity`) as `quantity`
                FROM `' . _DB_PREFIX_ . 'stock_available`
                WHERE `id_product_attribute` > 0
                GROUP BY `id_product`, `id_shop`, `' . pSQL($id_shop_group) . '`
                UNION ALL
                SELECT NULL as `id_stock_available`
                     , p.`id_product`
                     , 0 as `id_product_attribute`
                     , shop.`id_shop`
                     , 0 as `' . pSQL($id_shop_group) . '`
                     , p.quantity
                FROM `' . _DB_PREFIX_ . 'product` p
                    INNER JOIN `' . _DB_PREFIX_ . 'product_shop` shop ON (shop.`id_product` = p.`id_product`)
                    LEFT JOIN `' . _DB_PREFIX_ . 'stock_available` stock ON (stock.`id_product` = p.`id_product` AND stock.`id_shop` = shop.`id_shop`)
                WHERE stock.`id_product` is NULL OR stock.`id_product` <= 0
                GROUP BY p.`id_product`, shop.`id_shop`
            ) st
            ON DUPLICATE KEY UPDATE `quantity` = st.`quantity`
        ';
        if ($res = Db::getInstance()->execute($query)) {

            // Set out of stock:
            $query = '
                UPDATE `' . _DB_PREFIX_ . 'stock_available` st
                    LEFT JOIN `' . _DB_PREFIX_ . 'product` p ON p.`id_product` = st.`id_product`
                SET st.`out_of_stock` = p.`out_of_stock`
                WHERE st.`out_of_stock` is NULL OR st.`out_of_stock` <=0
            ';
            $res &= Db::getInstance()->execute($query);
        }

        return $res;
    }

    public static function regenerateEntire()
    {
        if ($res = Db::getInstance()->execute('
                UPDATE `' . _DB_PREFIX_ . 'category` 
                SET 
                    `id_parent` = ' . (int)Configuration::get('PS_HOME_CATEGORY') . ' 
                WHERE `is_root_category` != 1 
                    AND `id_parent` = ' . (int)Configuration::get('PS_ROOT_CATEGORY')
        )) {
            $res &= Db::getInstance()->execute('
                UPDATE `' . _DB_PREFIX_ . 'category` c
                    JOIN `' . _DB_PREFIX_ . 'category` lc ON c.`id_parent` = lc.`id_category_ets_old`
                SET 
                    c.`id_parent` = lc.`id_category`
                WHERE c.`id_parent` != 0 AND c.is_root_category != 1
            ');
        }

        return $res;
    }

    public static function resetRootCategory()
    {
        static $configs = [
            'PS_HOME_CATEGORY' => [
                'field' => 'is_root_category',
                'value' => 1,
            ],
            'PS_ROOT_CATEGORY' => [
                'field' => 'id_parent',
                'value' => 0,
            ]
        ];
        foreach ($configs as $key => $config) {
            if (($id_category = (int)Db::getInstance()->getValue('SELECT `id_category` FROM `' . _DB_PREFIX_ . 'category` WHERE `' . pSQL($config['field']) . '`=' . (int)$config['value']))
                && $id_category != (int)Configuration::get($key)
            ) {
                Db::getInstance()->update(
                    'configuration'
                    , ['value' => (int)$id_category]
                    , 'name=\'' . pSQL($key) . '\''
                );
            }
        }
    }

    public static function updateShopCategory()
    {
        $shops = Db::getInstance()->executeS('SELECT `id_shop`, `id_category` FROM `' . _DB_PREFIX_ . 'shop`');
        if (is_array($shops)
            && count($shops) > 0
        ) {
            foreach ($shops as $shop) {
                if (isset($shop['id_category'])
                    && $shop['id_category'] > 0
                    && isset($shop['id_shop'])
                    && $shop['id_shop'] > 0
                ) {
                    $id_category = (int)Db::getInstance()->getValue('SELECT `id_category` FROM `' . _DB_PREFIX_ . 'category` WHERE `is_root_category` = 1 AND `id_category_ets_old`=' . (int)$shop['id_category']);
                    if ($id_category <= 0) {
                        $id_category = (int)Configuration::get('PS_HOME_CATEGORY', null, null, (int)$shop['id_shop']);
                    }
                    if ($id_category != $shop['id_category']) {
                        Db::getInstance()->update(
                            'shop'
                            , ['id_category' => (int)$id_category]
                            , 'id_shop=' . (int)$shop['id_shop']
                        );
                    }
                }
            }
        }
    }

    public static function setProductSuppliers()
    {
        $ps_currency_default = Db::getInstance()->getValue('SELECT `value` FROM `' . _DB_PREFIX_ . 'configuration` WHERE name="PS_CURRENCY_DEFAULT"');

        //Get all products with positive quantity
        $resource = Db::getInstance()->query('
            SELECT id_supplier, id_product, supplier_reference, wholesale_price
            FROM `' . _DB_PREFIX_ . 'product`
            WHERE `id_supplier` > 0
        ');

        while ($row = Db::getInstance()->nextRow($resource)) {
            //Set default supplier for product
            Db::getInstance()->execute('
                INSERT IGNORE INTO `' . _DB_PREFIX_ . 'product_supplier`
                (`id_product`, `id_product_attribute`, `id_supplier`,
                    `product_supplier_reference`, `product_supplier_price_te`,
                    `id_currency`)
                VALUES
                ("' . (int)$row['id_product'] . '", "0", "' . (int)$row['id_supplier'] . '",
                "' . pSQL($row['supplier_reference']) . '", "' . (int)$row['wholesale_price'] . '",
                    "' . (int)$ps_currency_default . '")
            ');
            //Try to get product attribues
            $attributes = Db::getInstance()->executeS('
                SELECT id_product_attribute, supplier_reference, wholesale_price
                FROM `' . _DB_PREFIX_ . 'product_attribute`
                WHERE `id_product` = ' . (int)$row['id_product']
            );
            //Add each attribute to stock_available
            foreach ($attributes as $attribute) {
                // set supplier for attribute
                Db::getInstance()->execute('
                    INSERT IGNORE INTO `' . _DB_PREFIX_ . 'product_supplier`
                    (`id_product`, `id_product_attribute`,
                    `id_supplier`, `product_supplier_reference`,
                    `product_supplier_price_te`, `id_currency`)
                    VALUES
                    ("' . (int)$row['id_product'] . '", "' . (int)$attribute['id_product_attribute'] . '",
                    "' . (int)$row['id_supplier'] . '", "' . pSQL($attribute['supplier_reference']) . '",
                    "' . (int)$attribute['wholesale_price'] . '", "' . (int)$ps_currency_default . '")
                ');
            }
        }
    }

    public static function fetch($table, $nb = true, $select = null, $offset = 0, $limit = 0, $operators = [], $get_first = false)
    {
        $dq = new DbQuery();
        $dq->from($table);

        // Select:
        if ($nb) {
            $dq->select('COUNT(*)');
        } elseif (trim($select) !== '') {
            $dq->select($select);
        } else {
            $dq->select('*');
        }

        // Conditions:
        if (is_array($operators)
            && count($operators) > 0
        ) {
            foreach ($operators as $operator) {
                $dq->where($operator);
            }
        }
        // Get first:
        if ($nb ||
            $get_first
        ) {
            return (int)Db::getInstance()->getValue($dq);
        }

        if ($limit) {
            $dq->limit($limit, $offset);
        }
        return Db::getInstance()->executeS($dq);
    }

    public static function tableExist($table)
    {
        return Db::getInstance()->executeS('SHOW TABLES LIKE \'' . _DB_PREFIX_ . bqSQL($table) . '\'');
    }

    public static function columnExist($table, $column)
    {
        return self::tableExist($table) && Db::getInstance()->executeS('SHOW COLUMNS FROM `' . _DB_PREFIX_ . bqSQL($table) . '` LIKE \'' . pSQL($column) . '\'');
    }

    public static function quickSort($list, $field = 'position', $ignore_value = -1)
    {
        $left = $right = array();
        if (count($list) <= 1) {
            return $list;
        }
        $pivot_key = key($list);
        $pivot = array_shift($list);
        // partial:
        foreach ($list as $key => $val) {
            if ($val[$field] <= $pivot[$field]) {
                $left[$key] = $val;
            } elseif ($val[$field] > $pivot[$field]) {
                $right[$key] = $val;
            }
        }
        // recursive:
        return array_merge(self::quickSort($left, $field, $ignore_value), array($pivot_key => $pivot), self::quickSort($right, $field, $ignore_value));
    }

    public static function getDuplicateLanguages()
    {
        $dq = new DbQuery();
        $dq
            ->select('GROUP_CONCAT(`id_lang` SEPARATOR \',\')')
            ->from('lang')
            ->where('id_lang_ets_old is NULL');

        $res = Db::getInstance()->getValue($dq);
        return $res ? explode(',', $res) : [];
    }

    public static function setCurrentState()
    {
        return Db::getInstance()->execute('
            UPDATE `' . _DB_PREFIX_ . 'orders` o
            SET o.`current_state` = IFNULL((
                SELECT oh.`id_order_state`
                FROM `' . _DB_PREFIX_ . 'order_history` oh
                WHERE oh.`id_order` = o.`id_order`
                ORDER BY oh.`date_add` DESC
                LIMIT 1
            ), 0);
        ');
    }

    public static function updateSpecificPrice()
    {
        return Db::getInstance()->execute('UPDATE `' . _DB_PREFIX_ . 'specific_price` SET `price` = -1 WHERE `price` = 0');
    }

    public static function updateCarrierReference()
    {
        return Db::getInstance()->execute('UPDATE `' . _DB_PREFIX_ . 'carrier` SET `id_reference` = `id_carrier`');
    }

    public static function setCacheDefaultAttribute()
    {
        $execCmd = Db::getInstance()->execute('
            UPDATE `' . _DB_PREFIX_ . 'product` p SET `cache_default_attribute` = (SELECT `id_product_attribute` FROM `' . _DB_PREFIX_ . 'product_attribute` WHERE `id_product` = p.`id_product` AND default_on = 1 LIMIT 1) 
            WHERE `cache_default_attribute` IS NULL
        ');
        $execCmd &= Db::getInstance()->execute('UPDATE `' . _DB_PREFIX_ . 'product` SET `product_type` = "combinations" WHERE `cache_default_attribute` != 0');

        return $execCmd;
    }

    public static function upgradeAttributeGroup()
    {
        return Db::getInstance()->execute('UPDATE `' . _DB_PREFIX_ . 'attribute_group` SET `group_type` = IF(`is_color_group`=1, "color", "select") WHERE `is_color_group` = 1 OR `group_type` is NULL OR `group_type`=""');
    }

    public static function insertOrderInvoice()//1.5.0.2
    {
        return Db::getInstance()->execute('
            INSERT INTO `' . _DB_PREFIX_ . 'order_invoice` (`id_order`, `number`, `total_discount_tax_excl`, `total_discount_tax_incl`, `total_paid_tax_excl`, `total_paid_tax_incl`, `total_products`, `total_products_wt`, `total_shipping_tax_excl`, `total_shipping_tax_incl`, `total_wrapping_tax_excl`, `total_wrapping_tax_incl`, `note`, `date_add`) (
                SELECT `id_order`, `invoice_number`, `total_discounts_tax_excl`, `total_discounts_tax_incl`, `total_paid_tax_excl`, `total_paid_tax_incl`, `total_products`, `total_products_wt`, `total_shipping_tax_excl`, `total_shipping_tax_incl`, `total_wrapping_tax_excl`, `total_wrapping_tax_incl`, \'\', `invoice_date`
                FROM `' . _DB_PREFIX_ . 'orders`
                WHERE `invoice_number` != 0
            );
        ');
    }

    public static function updateOrderDetail()//1.5.0.2
    {
        return Db::getInstance()->execute('
            UPDATE `' . _DB_PREFIX_ . 'order_detail` od
            SET od.`id_order_invoice` = IFNULL((
                SELECT oi.`id_order_invoice`
                FROM `' . _DB_PREFIX_ . 'order_invoice` oi
                WHERE oi.`id_order` = od.`id_order` LIMIT 1
            ), 0);
        ');
    }

    public static function addOrderReference() //1.5.0.2
    {
        return Db::getInstance()->execute('UPDATE `' . _DB_PREFIX_ . 'orders` SET `reference` = id_order');
    }

    public static function updateOrderReference()//1.5.0.14
    {
        return Db::getInstance()->execute('UPDATE `' . _DB_PREFIX_ . 'orders` SET `reference` = LPAD(`reference`, 9 , \'0\')');
    }

    public static function insertOrderCarrier()//1.5.0.2
    {
        return Db::getInstance()->execute('
        INSERT INTO `' . _DB_PREFIX_ . 'order_carrier` (`id_order`, `id_carrier`, `id_order_invoice`, `weight`, `shipping_cost_tax_excl`, `shipping_cost_tax_incl`, `tracking_number`, `date_add`) (
            SELECT `id_order`, `id_carrier`, (
                SELECT oi.`id_order_invoice`
                FROM `' . _DB_PREFIX_ . 'order_invoice` oi
                WHERE oi.`id_order` = o.`id_order` LIMIT 1
            ), (
                SELECT SUM(`product_weight`)
                FROM `' . _DB_PREFIX_ . 'order_detail` od
                WHERE od.`id_order` = o.`id_order` LIMIT 1
            ), `total_shipping_tax_excl`, `total_shipping_tax_incl`, `shipping_number`, `date_add`
            FROM `' . _DB_PREFIX_ . 'orders` o
        );
        ');
    }

    public static function insertOrderPayment()//1.5.0.13
    {
        return Db::getInstance()->execute('
        INSERT IGNORE INTO `' . _DB_PREFIX_ . 'order_payment` (`order_reference`, `id_currency`, `amount`, `payment_method`, `conversion_rate`, `date_add`)
            (
                SELECT o.`reference`, o.`id_currency`, o.`total_paid_real`, o.`payment`, o.`conversion_rate`, o.`date_add`
                FROM `' . _DB_PREFIX_ . 'orders` o
                LEFT JOIN `' . _DB_PREFIX_ . 'order_payment` op ON (op.`order_reference` = o.`reference`)
                WHERE op.`id_order_payment` IS NULL
            );
        ');
    }

    public static function updateOrderPayment()
    {
        $res = Db::getInstance()->execute('UPDATE `' . _DB_PREFIX_ . 'order_payment` SET transaction_id=\'\' WHERE transaction_id is NULL');
        $res &= Db::getInstance()->execute('UPDATE `' . _DB_PREFIX_ . 'order_payment` SET card_number=\'\' WHERE card_number is NULL');
        $res &= Db::getInstance()->execute('UPDATE `' . _DB_PREFIX_ . 'order_payment` SET card_brand=\'\' WHERE card_brand is NULL');
        $res &= Db::getInstance()->execute('UPDATE `' . _DB_PREFIX_ . 'order_payment` SET card_expiration=\'\' WHERE card_expiration is NULL');
        $res &= Db::getInstance()->execute('UPDATE `' . _DB_PREFIX_ . 'order_payment` SET card_holder=\'\' WHERE card_holder is NULL');

        return $res;
    }

    public static function addGroups()
    {
        $groups = Db::getInstance()->executeS('SELECT `id_group` FROM `' . _DB_PREFIX_ . 'group` WHERE `id_group_ets_old` is NOT NULL');
        if (is_array($groups) && count($groups)) {
            $query = '';
            foreach ($groups as $group) {
                $query .= '
                    INSERT IGNORE INTO `' . _DB_PREFIX_ . 'module_group` (`id_module`, `id_shop`, `id_group`)
                    (
                        SELECT `id_module`, `id_shop`, ' . (int)$group['id_group'] . ' as `id_group` 
                        FROM `' . _DB_PREFIX_ . 'module_group`
                        WHERE id_group = ' . (int)Configuration::get('PS_UNIDENTIFIED_GROUP') . '
                    );';
            }
            if ($query)
                Db::getInstance()->execute($query);
        }
    }

    public static function buffCategories()
    {
        $result = Db::getInstance()->executeS('SELECT * FROM `' . _DB_PREFIX_ . 'category`');
        $categories = [];
        $parents = [];
        if ($result) {
            foreach ($result as $item) {
                $categories[$item['id_category']] = (int)$item['id_parent'];
                $parents[$item['id_parent']][] = (int)$item['id_category'];
            }
        }
        $cate = [];
        if ($categories && $parents) {
            foreach ($parents as $idParent => $list) {
                if (isset($categories[$idParent]) && in_array($categories[$idParent], $list)) {
                    $cate[] = $idParent;
                }
            }
        }
        $idRootCategory = (int)Configuration::get('PS_HOME_CATEGORY');
        if ($cate)
            Db::getInstance()->execute('UPDATE `' . _DB_PREFIX_ . 'category` SET `id_parent`=' . (int)$idRootCategory . ' WHERE `id_category` IN (' . implode(',', $cate) . ')');
    }

    public static function productAttributeLang()
    {
        Db::getInstance()->execute('INSERT INTO `' . _DB_PREFIX_ . 'product_attribute_lang`
            (id_product_attribute, id_lang, available_now, available_later)
            SELECT pa.id_product_attribute, l.id_lang, \'\', \'\'
            FROM `' . _DB_PREFIX_ . 'product_attribute` pa CROSS JOIN `' . _DB_PREFIX_ . 'lang` l;');
    }

    public static function updateShopImage()
    {
        $res = Db::getInstance()->execute('TRUNCATE TABLE `' . _DB_PREFIX_ . 'image_shop`');
        $res &= Db::getInstance()->execute('
            INSERT INTO `' . _DB_PREFIX_ . 'image_shop` (`id_product`, `id_image`, `id_shop`, `cover`) 
            SELECT i.`id_product`, i.`id_image`,s.`id_shop`, i.`cover` 
            FROM `' . _DB_PREFIX_ . 'image` i CROSS JOIN `' . _DB_PREFIX_ . 'shop` s;)
        ');
        return $res;
    }

    public static function updateProductType()
    {
        Db::getInstance()->execute('UPDATE `' . _DB_PREFIX_ . 'product` SET `product_type` = "standard"');
        Db::getInstance()->execute('UPDATE `' . _DB_PREFIX_ . 'product` SET `product_type` = "combinations" WHERE `cache_default_attribute` != 0');
        Db::getInstance()->execute('UPDATE `' . _DB_PREFIX_ . 'product` SET `product_type` = "pack" WHERE `cache_is_pack` = 1');
        Db::getInstance()->execute('UPDATE `' . _DB_PREFIX_ . 'product` SET `product_type` = "virtual" WHERE `is_virtual` = 1');

        return true;
    }
}