<?php

namespace KupShop\AdminBundle\Util;

use KupShop\ContentBundle\Util\BlocksTrait;
use KupShop\KupShopBundle\Config;
use Query\Operator;

class ProductUtils
{
    use BlocksTrait;
    use \DatabaseCommunication;

    public function duplicateProduct(int $oldId): ?int
    {
        $qb = sqlQueryBuilder()
            ->select('*')
            ->from('products')
            ->andWhere(Operator::equals(['id' => $oldId]));

        $results = $qb->execute()->fetch();
        $results['id'] = 0;
        $results['pieces_sold'] = 0;

        if (!findModule(\Modules::PRODUCTS, \Modules::SUB_DONT_GENERATE_CODE)) {
            $results['code'] = \Variations::makeCodeUnique(0, $results['code']);
        } else {
            $results['code'] = null;
        }

        unset($results['ean']);
        unset($results['date_added']);
        unset($results['id_block']);

        sqlQueryBuilder()
            ->insert('products')
            ->setValue('date_added', 'NOW()')
            ->directValues($results)
            ->execute();

        return sqlInsertId();
    }

    public function copyProductData(int $oldId, int $newId, array $params)
    {
        if ($oldId == $newId) {
            return;
        }

        if (isset($params['_all_'])) {
            // Dopln me, kdyz se prida nova polozka pro kopirovani
            $params = array_merge([
                'product' => '_all_',
                'pricelist' => true,
                'photos' => true,
                'variations' => true,
                'parameters' => true,
                'sections' => true,
                'products_collections' => true,
                'products_related' => true,
                'attachments' => true,
                'articles' => true,
                'translations' => true,
                'sets' => true,
                'gifts' => true,
                'links' => true,
                'templates' => true,
                'charges' => true,
                'descr_plus' => true,
            ], $params);
        }
        $queryParams = [
            'old_id' => $oldId,
            'new_id' => $newId,
        ];

        if ($params['product'] ?? false) {
            if ($params['product'] === '_all_') {
                $params['product'] = ['*'];
            }
            $product = sqlQueryBuilder()->select($params['product'])
                ->from('products')
                ->where(Operator::equals(['id' => $oldId]))
                ->execute()
                ->fetch();

            if ($params['product'] === ['*']) {
                unset($product['id']);
                unset($product['pieces_sold']);
                unset($product['code']);
                unset($product['ean']);
                unset($product['date_added']);
            }

            sqlQueryBuilder()
                ->update('products')
                ->directValues($product)
                ->where(Operator::equals(['id' => $newId]))
                ->execute();
        }

        if (findModule(\Modules::PRODUCTS_VARIATIONS) && ($params['variations'] ?? false)) {
            \Variations::duplicateVariations($oldId, $newId);
        }

        if (findModule(\Modules::PRICELISTS) && ($params['pricelist'] ?? false)) {
            $where = 1;
            // zkopiruje pouze nevariantni ceniky - pouziva se to pri rozpadu variant na produkty
            if ($params['pricelist'] === 'products') {
                $where = 'id_variation IS NULL';
            }
            sqlQuery('INSERT INTO pricelists_products (id, id_pricelist, id_product, id_variation,price,discount)
                SELECT 0, id_pricelist,:new_id, id_variation,price,discount
				FROM pricelists_products
				WHERE id_product=:old_id AND '.$where,
                $queryParams
            );

            sqlQueryBuilder()->update('pricelists_products', 'pp')
                ->join('pp', 'products_variations', 'pvOld', 'pp.id_variation = pvOld.id')
                ->join('pvOld', 'products_variations', 'pvNew', 'pvNew.id_product = :newId AND pvNew.title = pvOld.title')
                ->where(Operator::equals([
                    'pp.id_product' => $queryParams['new_id'],
                ]))
                ->setParameter('newId', $queryParams['new_id'])
                ->set('pp.id_variation', 'pvNew.id')
                ->execute();
        }

        if (findModule(\Modules::PHOTOS) && ($params['photos'] ?? false)) {
            $columns = sqlGetConnection()->getSchemaManager()->listTableColumns('photos_products_relation');
            $columnNames = array_map(function ($column) {return $column->getName(); }, $columns);
            $columnNames = array_combine($columnNames, $columnNames);
            $columnValues = $columnNames;
            $columnValues['id_product'] = $newId;

            $where = 1;
            // zkopiruje pouze nevariantni fotky - pouziva se to pri rozpadu variant na produkty
            if ($params['photos'] === 'products') {
                $where = 'id_variation IS NULL';
            }

            sqlQuery(
                'INSERT INTO photos_products_relation ('.join(',', $columnNames).')
                 SELECT '.join(',', $columnValues).'
                 FROM photos_products_relation
                 WHERE id_product=:old_id AND '.$where,
                $queryParams
            );
            \Photos::checkLeadPhoto('photos_products_relation', 'id_product', $newId);

            if (findModule(\Modules::PRODUCTS_VARIATIONS_PHOTOS)) {
                // Handle variation images
                sqlQueryBuilder()->update('photos_products_relation', 'ppr')
                    ->join('ppr', 'products_variations', 'pvOld', 'ppr.id_variation = pvOld.id')
                    ->join('pvOld', 'products_variations', 'pvNew', 'pvNew.id_product = :newId AND pvNew.title = pvOld.title')
                    ->where(Operator::equals([
                        'ppr.id_product' => $newId,
                    ]))
                    ->setParameter('newId', $newId)
                    ->set('ppr.id_variation', 'pvNew.id')
                    ->execute();
            }
        }

        if (findModule(\Modules::PRODUCTS_PARAMETERS) && ($params['parameters'] ?? false)) {
            $additionalField = '';
            if (findModule(\Modules::PRODUCTS_PARAMETERS.\Modules::SUB_CONFIGURATIONS)) {
                $additionalField = ', configuration_price';
            }

            sqlQuery(
                'INSERT INTO parameters_products (id_product, id_parameter, value_list, value_char, value_float, unit, weight'.$additionalField.')
                SELECT :new_id, id_parameter, value_list, value_char, value_float, unit, weight'.$additionalField.'
                FROM parameters_products
                WHERE id_product=:old_id',
                $queryParams
            );

            // smazani duplicitnich hodnot dle value_list
            sqlQuery('DELETE pp1
                                FROM parameters_products pp1
                                JOIN (
                                    SELECT id_product, id_parameter, value_list, MIN(id) as min_id
                                    FROM parameters_products
                                    WHERE id_product = :new_id
                                    AND value_list IS NOT NULL
                                    GROUP BY id_product, id_parameter, value_list
                                    HAVING count(*) > 1
                                ) pp2 ON pp1.id_product = pp2.id_product
                                    AND pp1.id_parameter = pp2.id_parameter
                                    AND pp1.value_list = pp2.value_list
                                WHERE pp1.id_product = :new_id
                                AND pp1.value_list IS NOT NULL
                                AND pp1.id != pp2.min_id;', $queryParams);

            // konfigurace parametru
            if (findModule(\Modules::PRODUCTS_PARAMETERS.\Modules::SUB_CONFIGURATIONS)) {
                sqlQuery(
                    'INSERT INTO parameters_configurations (id_parameter, id_product)
                    SELECT id_parameter, :new_id
                    FROM parameters_configurations
                    WHERE id_product=:old_id',
                    $queryParams
                );
            }

            // skupina parametru
            if (findModule(\Modules::PARAMETER_GROUPS)) {
                sqlQuery(
                    'UPDATE products SET id_parameter_group = (
                                    SELECT id_parameter_group FROM products WHERE id = :old_id
                                )
                                WHERE id = :new_id',
                    $queryParams
                );
            }
        }
        if (findModule(\Modules::PRODUCTS_SECTIONS) && ($params['sections'] ?? false)) {
            sqlQuery('INSERT IGNORE INTO products_in_sections (id_product, id_section, figure, generated) 
					 SELECT :new_id, id_section, figure, generated
					 FROM products_in_sections
					 WHERE id_product=:old_id',
                $queryParams
            );
        }

        if (findModule(\Modules::PRODUCTS_COLLECTIONS) && ($params['products_collections'] ?? false)) {
            sqlQuery('INSERT IGNORE INTO products_collections (id_product, id_product_related) 
					 SELECT :new_id, id_product_related
					 FROM products_collections
					 WHERE id_product=:old_id',
                $queryParams
            );
        }

        if (findModule(\Modules::PRODUCTS_RELATED) && ($params['products_related'] ?? false)) {
            if (findModule(\Modules::PRODUCTS_RELATED, \Modules::SUB_TYPES)) {
                sqlQuery(
                    'INSERT IGNORE INTO products_related (id_top_product, id_rel_product, position, type)
					 SELECT :new_id, id_rel_product, position, type
					 FROM products_related
					 WHERE id_top_product=:old_id',
                    $queryParams
                );
            } else {
                sqlQuery(
                    'INSERT IGNORE INTO products_related (id_top_product, id_rel_product, position)
					 SELECT :new_id, id_rel_product, position
					 FROM products_related
					 WHERE id_top_product=:old_id',
                    $queryParams
                );
            }
        }

        if (findModule(\Modules::ATTACHMENTS) && ($params['attachments'] ?? false)) {
            sqlQuery('INSERT INTO attachments (id_product, title, link)
                    SELECT :new_id, title, link
                    FROM attachments
                    WHERE id_product=:old_id',
                $queryParams
            );
        }

        if (findModule(\Modules::ARTICLES) && ($params['articles'] ?? false)) {
            sqlQuery('INSERT INTO products_in_articles (id_product, id_article)
                    SELECT :new_id, id_article
                    FROM products_in_articles
                    WHERE id_product=:old_id',
                $queryParams
            );
        }

        if (findModule(\Modules::PRODUCT_SETS) && ($params['sets'] ?? false)) {
            sqlQuery('INSERT INTO products_sets (id_product, id_product_set, price, pieces, id_variation)
                    SELECT :new_id, id_product_set, price, pieces, id_variation
                    FROM products_sets
                    WHERE id_product=:old_id',
                $queryParams
            );
        }

        if (findModule(\Modules::PRODUCT_GIFTS) && ($params['gifts'] ?? false)) {
            sqlQuery('INSERT INTO products_gifts (id_product, id_product_gift, price, pieces, id_variation_gift)
                    SELECT :new_id, id_product_gift, price, pieces, id_variation_gift
                    FROM products_gifts
                    WHERE id_product=:old_id',
                $queryParams
            );
        }

        if (findModule(\Modules::LINKS) && ($params['links'] ?? false)) {
            sqlQuery('INSERT INTO links (id_product, title, link, type)
                    SELECT :new_id, title, link, type
                    FROM links
                    WHERE id_product=:old_id',
                $queryParams
            );
        }

        if (findModule(\Modules::TEMPLATES) && ($params['templates'] ?? false)) {
            sqlQuery('INSERT INTO templates_products (id_product, id_template)
                    SELECT :new_id, id_template
                    FROM templates_products
                    WHERE id_product=:old_id',
                $queryParams
            );
        }

        if (findModule(\Modules::PRODUCTS_CHARGES) && $params['charges'] ?? false) {
            sqlQuery('INSERT INTO products_charges (id_product, id_charge)
                    SELECT :new_id, id_charge
                    FROM products_charges
                    WHERE id_product=:old_id',
                $queryParams
            );
        }

        if (findModule(\Modules::PRODUCTS, \Modules::SUB_DESCR_PLUS) && ($params['descr_plus'] ?? false)) {
            $copyRootBlockID = sqlQueryBuilder()->select('id_block')->from('products')
                ->where(Operator::equals(['id' => $oldId]))
                ->execute()->fetchOne();
            if ($copyRootBlockID) {
                $blockID = $this->duplicateBlock($copyRootBlockID);
                sqlQueryBuilder()->update('products')
                    ->directValues(['id_block' => $blockID])
                    ->where(Operator::equals(['id' => $newId]))
                    ->execute();
                sqlQuery(
                    "REPLACE INTO photos_products_descr_plus_relation 
                        (id_photo, id_product, date_added, show_in_lead, position)
                        SELECT id_photo, :new_id, NOW(), 'N', position
                        FROM photos_products_descr_plus_relation WHERE id_product = :old_id",
                    $queryParams
                );
                \Photos::checkLeadPhoto('photos_products_descr_plus_relation', 'id_product', $newId);
            }
        }

        if (findModule(\Modules::TRANSLATIONS) && ($params['translations'] ?? false)) {
            sqlQuery('INSERT INTO products_translations (id_product, id_language, id_admin, title, short_descr, long_descr, meta_title, meta_description, meta_keywords, parameters, figure)
                    SELECT :new_id, id_language, id_admin, title, short_descr, long_descr, meta_title, meta_description, meta_keywords, parameters, figure
                    FROM products_translations
                    WHERE id_product = :old_id',
                $queryParams
            );
        }
        if (findModule(\Modules::LABELS) && ($params['labels'] ?? false)) {
            sqlQuery('INSERT INTO product_labels_relation (id_product, id_label, generated)
                SELECT :new_id, id_label, generated FROM product_labels_relation WHERE id_product = :old_id;',
                $queryParams);
        }
    }

    public function copyVariationData(int $oldVariationId, int $newVariationId): void
    {
        $qb = sqlQueryBuilder()
            ->from('products_variations')
            ->where(Operator::equals(['id' => $oldVariationId]));

        $fields = $this->getVariationFields();
        // code se nekopiruje kvuli duplicite
        if (($index = array_search('code', $fields)) !== false) {
            unset($fields[$index]);
        }

        foreach ($fields as $field) {
            $qb->addSelect($field);
        }

        $variation = $qb->execute()->fetch();

        sqlQueryBuilder()
            ->update('products_variations')
            ->directValues($variation)
            ->where(Operator::equals(['id' => $newVariationId]))
            ->execute();
    }

    public function copyProductDataToVariation(int $productId, int $variationId): void
    {
        $qb = sqlQueryBuilder()
            ->from('products')
            ->where(Operator::equals(['id' => $productId]));

        foreach ($this->getVariationFields() as $field) {
            $qb->addSelect($field);
        }

        $product = $qb->execute()->fetch();

        sqlQueryBuilder()
            ->update('products_variations')
            ->directValues($product)
            ->where(Operator::equals(['id' => $variationId]))
            ->execute();
    }

    public function copyVariationDataToProduct(int $variationId, int $productId): void
    {
        $qb = sqlQueryBuilder()
            ->from('products_variations')
            ->where(Operator::equals(['id' => $variationId]));

        foreach ($this->getVariationFields() as $field) {
            $qb->addSelect($field);
        }

        $variation = $qb->execute()->fetchAssociative();

        sqlQueryBuilder()
            ->update('products')
            ->directValues($variation)
            ->where(Operator::equals(['id' => $productId]))
            ->execute();
    }

    public function moveProductBlocksToNewProduct(int $oldProductId, int $newProductId): void
    {
        if (!findModule(\Modules::PRODUCTS, \Modules::SUB_DESCR_PLUS)) {
            return;
        }

        $getProductBlockId = function (int $productId): ?int {
            $id = sqlQueryBuilder()
                ->select('id_block')
                ->from('products')
                ->where(Operator::equals(['id' => $productId]))
                ->execute()->fetchColumn();

            if (!$id) {
                return null;
            }

            return (int) $id;
        };

        // nemame co kopirovat
        if (!($oldRootBlockId = $getProductBlockId($oldProductId))) {
            return;
        }

        // pokud produkt nema zadny blok, tak mu nastavime ten, ktery mel stary produkt
        if (!($newRootBlockId = $getProductBlockId($newProductId))) {
            sqlQueryBuilder()
                ->update('products')
                ->directValues(
                    [
                        'id_block' => $oldRootBlockId,
                    ]
                )
                ->where(Operator::equals(['id' => $newProductId]))
                ->execute();

            return;
        }

        sqlQueryBuilder()
            ->update('blocks')
            ->directValues(
                [
                    'id_root' => $newRootBlockId,
                ]
            )
            ->where(Operator::equals(['id_root' => $oldRootBlockId]))
            ->execute();

        sqlQueryBuilder()
            ->update('blocks')
            ->directValues(
                [
                    'id_parent' => $newRootBlockId,
                ]
            )
            ->where(Operator::equals(['id_parent' => $oldRootBlockId]))
            ->execute();
    }

    public function generateSet($id, $setsProducts)
    {
        foreach ($setsProducts as $set) {
            sqlQueryBuilder()
            ->insert('products_sets')
            ->directValues([
                'id_product' => $id,
                'id_product_set' => $set['id_product_set'],
                'pieces' => $set['pieces'],
            ])->execute();
        }
    }

    public function generateGift($id, $giftsProducts)
    {
        foreach ($giftsProducts as $set) {
            sqlQueryBuilder()
            ->insert('products_gifts')
            ->directValues([
                'id_product' => $id,
                'id_product_gift' => $set['id_product_gift'],
                'pieces' => $set['pieces'],
            ])->execute();
        }
    }

    public function editVariation($productId, $variationId, $parts, $variationTitle = '')
    {
        // Find values labels
        $query = 'SELECT id, label
         FROM '.getTableName('products_variations_choices_labels').'
         WHERE 0=1 ';
        foreach ($parts as $variant => $value) {
            $query .= " or id='".$variant."' ";
        }
        $SQL = sqlQuery($query);

        $labels = [];
        while ($row = sqlFetchAssoc($SQL)) {
            $labels[$row['id']] = $row['label'];
        }

        // Check if edit is necessary
        $titleParts = [];
        foreach ($parts as $variant => $value) {
            $titleParts[] = "{$labels[$variant]}: {$value}";
        }
        $title = join(', ', $titleParts);

        if ($variationTitle == $title) {
            return;
        }

        // Find values IDs
        $query = 'SELECT id, COALESCE(value, code) as name, id_label
         FROM '.getTableName('products_variations_choices_values').'
         WHERE (0=1 ';
        foreach ($parts as $variant => $value) {
            $query .= ' OR (id_label='.$variant." AND COALESCE(code, value)='".sqlFormatInput($value)."') ";
        }
        $query .= ')';
        $SQL = sqlQuery($query);

        $values = [];
        $names = [];
        while ($row = sqlFetchAssoc($SQL)) {
            $values[$row['id_label']] = $row['id'];
            $names[$row['id_label']] = $row['name'];
        }

        if (!$variationId) {
            $query = 'INSERT INTO '.getTableName('products_variations').' (id_product, title) VALUES ('.$productId.',"'.sqlFormatInput($title).'")';
            sqlQuery($query);
            $variationId = sqlInsertId();
        } else {
            sqlQueryBuilder()->update('products_variations')
                ->directValues([
                    'title' => $title,
                ])
                ->where(Operator::equals(['id' => $variationId]))
                ->execute();
        }

        sqlQueryBuilder()
            ->delete('products_variations_combination')
            ->where(Operator::equals(['id_variation' => $variationId]))
            ->execute();

        // Insert values
        foreach ($parts as $variant => $value) {
            if (!isset($values[$variant])) { // Insert new value if does not exists
                $query = 'INSERT INTO '.getTableName('products_variations_choices_values').'
               (id_label, value, code) VALUES
               ('.$variant.",'".sqlFormatInput($value)."','".sqlFormatInput($value)."')";
                sqlQuery($query);
                $values[$variant] = sqlInsertId();
            }

            $query = 'INSERT INTO '.getTableName('products_variations_combination').' (id_variation, id_label, id_value) VALUES ('.$variationId.','.$variant.",'".$values[$variant]."')";
            sqlQuery($query);
        }

        sqlQueryBuilder()
            ->delete('products_variations_choices_categorization')
            ->where(Operator::equals(['id_product' => $productId]))
            ->execute();

        $index = 0;
        foreach ($parts as $variant => $value) {
            $query = 'INSERT IGNORE INTO '.getTableName('products_variations_choices_categorization').' (id_product, id_label, list_order) VALUES ('.$productId.','.$variant.",'".$index++."')";
            sqlQuery($query);
        }
    }

    /**
     * @param $update callable(string table, string variationField)
     */
    public function updateTablesWithCallback(callable $update)
    {
        $update('order_items');
        $update('inventory_items');
        $update('cart');

        if (findModule(\Modules::PRODUCT_SETS)) {
            $update('products_sets', 'id_product_set', 'id_variation');
        }
        if (findModule(\Modules::ORDER_EDIT)) {
            $update('order_edit');
        }
        if (findModule(\Modules::PRODUCTS_SUPPLIERS)) {
            $update('products_of_suppliers');
        }
        if (findModule(\Modules::WATCHDOG)) {
            $update('products_watchdog');
        }
        if (findModule(\Modules::RETURNS)) {
            $update('return_replacements');
        }
        if (findModule(\Modules::REVIEWS)) {
            $update('reviews', 'id_product', 'id_product_variation');
        }
        if (findModule(\Modules::STOCK_IN)) {
            $update('stock_in_items');
        }
        if (findModule(\Modules::PRICELISTS)) {
            $update('pricelists_products');
        }
        if (findModule(\Modules::ORDERS_OF_SUPPLIERS)) {
            $update('orders_of_suppliers');
        }
        if (findModule(\Modules::STORES)) {
            $update('stores_items');
            $update('stores_transfers_items');

            if (findModule(\Modules::STOCK_IN)) {
                $update('stores_log');
            }
        }
        if (findModule(\Modules::WAREHOUSE)) {
            $update('warehouse_products');
        }
        if (findModule(\Modules::PRODUCTS_VARIATIONS_PHOTOS)) {
            $update('photos_products_relation');
        }
    }

    public function updateCustomData($id_product, $values_or_callback)
    {
        sqlGetConnection()->transactional(function () use ($values_or_callback, $id_product) {
            $data = sqlQueryBuilder()->select('data')
                ->from('products')
                ->where(Operator::equals(['id' => $id_product]))
                ->orderBy('id', 'DESC')
                ->forUpdate()
                ->execute()
                ->fetchColumn(0);
            $data = json_decode($data, true) ?? [];
            if (is_callable($values_or_callback)) {
                $data = $values_or_callback($data);
            } else {
                $data = array_merge($data, $values_or_callback);
            }
            sqlQueryBuilder()->update('products')->directValues(['data' => json_encode($data)])->where(Operator::equals(['id' => $id_product]))->execute();
        });
    }

    public function getCustomData($id_product)
    {
        return json_decode(sqlQueryBuilder()->select('data')
            ->from('products')
            ->where(Operator::equals(['id' => $id_product]))
            ->execute()
            ->fetchColumn(0));
    }

    public function getOrderTitle($product)
    {
        $cfg = Config::get();
        $title = $product->title;

        if ($product instanceof \Variation) {
            $title = \Variations::fillInProductTitle($product->variationId, $title);
        }

        if (empty($cfg['Order']['hideCode']) && $product['code'] != '') {
            $title .= str_replace('%CODE', trim($product['code']), translate_shop('code', 'order'));
        }

        return $title;
    }

    protected function getVariationFields(): array
    {
        $fields = [
            'price',
            'in_store',
            'delivery_time',
            'IF(figure="Y", "Y", "N") as figure',
            'width',
            'height',
            'depth',
            'data',
        ];

        if (findModule(\Modules::PRODUCTS, \Modules::SUB_NOTE)) {
            $fields[] = 'note';
        }

        if (findModule(\Modules::MISSING_PRODUCTS)) {
            $fields[] = 'in_store_min';
        }

        // EAN nekopíruju když mám warehouse, protože je tam kontrola unikátnosti EANu a an tom by to padlo
        if (!findModule(\Modules::WAREHOUSE)) {
            $fields[] = 'ean';
        }

        if (findModule(\Modules::PRODUCTS, \Modules::SUB_WEIGHT)) {
            $fields[] = 'weight';
        }

        if (findModule(\Modules::BONUS_PROGRAM)) {
            $fields[] = 'bonus_points';
        }

        if (findModule(\Modules::PRODUCTS, \Modules::SUB_PRICE_COMMON)) {
            $fields[] = 'price_common';
        }

        if (findModule(\Modules::PRODUCTS, \Modules::SUB_PRICE_BUY)) {
            $fields[] = 'price_buy';
        }

        if (findModule(\Modules::PRODUCTS_VARIATIONS, \Modules::SUB_CODE)) {
            $fields[] = 'code';
        }

        return $fields;
    }
}