<?php

// -----------------------------------------------------------------

use KupShop\CatalogBundle\Section\SectionTree;
use KupShop\KupShopBundle\Util\Compat\ServiceContainer;

$vat = [];
$SQL = sqlQuery('SELECT id, vat 
					FROM '.getTableName('vats').'  ');
while ($row = sqlFetchArray($SQL)) {
    $GLOBALS['vat'][$row['id']] = $row['vat'];
}
unset($row);

// kontrola delky zpracovani skriptu
controlTimeLimit();

// -----------------------------------------------------------------

$producer = [];
$SQL = sqlQuery('SELECT id, name 
					FROM '.getTableName('producers').'  ');
while ($row = sqlFetchArray($SQL)) {
    $GLOBALS['producer'][$row['id']] = strtolower(trim($row['name']));
}
unset($row);

function getVatID($val)
{
    global $cfg;
    $key = 0;

    if (($key = array_search($val, $GLOBALS['vat'])) == false) {
        $SQL = sqlQuery('INSERT INTO '.getTableName('vats')." (vat,is_default) VALUES ('".$val."','N')", '@');
        $key = sqlInsertId();
        $GLOBALS['vat'][$key] = $val;
    }

    return $key;
}

function getProducer($val)
{
    global $cfg;
    $key = 0;

    $searchVal = strtolower(trim($val));
    if (($key = array_search($searchVal, $GLOBALS['producer'])) == false && $searchVal != '') {
        try {
            $SQL = sqlQuery('INSERT INTO '.getTableName('producers').' (name) VALUES (:val)', ['val' => $val]);
            $key = sqlInsertId();
        } catch (Exception $e) {
            $SQL = sqlFetchAssoc(sqlQuery('SELECT p.id FROM '.getTableName('producers').' AS p WHERE (p.name) = (:val)', ['val' => $val]));
            $key = $SQL['id'];
        }
        $GLOBALS['producer'][$key] = $searchVal;
    }

    return $key;
}

function updatable(&$header, $field, $create)
{
    global $cfg;

    if (empty($cfg['Sync']['Fields'])) {
        logError(__FILE__, __LINE__, 'Nejsou nastaveny pole pro synchronizaci!');
    }

    if (isset($header[$field])) {
        if (isset($cfg['Sync']['Fields'][$field])) {
            $value = $cfg['Sync']['Fields'][$field];

            return $create || (!$create && $value);
        }
    }

    return false;
}

function POHODA_Products(&$params)
{
    $request = $params['request'];

    switch ($request) {
        // odesilani zmen do POHODY
        case 'download':
            return POHODA_ProductsDownload($params);
            break;

            // prijimani zmen z POHODY
        case 'upload':
            return POHODA_ProductsUpload($params);
            break;

            // pokud je spatny pozadavek
        default:
            $response = [
                'status' => 400,
                'statusMessage' => getTextString('errors', 400),
                'serverTimestamp' => iso8601_encode(time()),
                'requestedParams' => $params,
                'responseData' => [],
            ];

            return new xmlrpcresp(php_xmlrpc_encode($response));
            break;
    }
}

// ----------------------------------------------------
// odesilani zmen do POHODY

function POHODA_ProductsDownload(&$params)
{
    $producer = [];
    $SQL = sqlQuery('SELECT id, name FROM '.getTableName('producers').'  ');
    while ($row = sqlFetchArray($SQL)) {
        $producer[$row['id']] = $row['name'];
    }
    unset($row);

    $params = $params['requestParams'];

    // logError(__FILE__, __LINE__, print_r($params, true), true);

    // pokud neni specifikovano datum od kdy hledat udaje vrati chybu
    if (!isset($params['dateFrom'])) {
        $response = [
            'status' => 406,
            'statusMessage' => getTextString('errors', 406),
            'serverTimestamp' => iso8601_encode(time()),
            'requestedParams' => $params,
            'responseData' => [],
        ];
        $response2 = php_xmlrpc_encode($response);

        return new xmlrpcresp($response2);
    }

    $response = [
        'status' => 200,
        'statusMessage' => getTextString('errors', 200),
        'serverTimestamp' => iso8601_encode(time()),
        'requestedParams' => $params,
        'responseData' => [],
    ];

    $query = 'SELECT pv.id, p.title, p.code, p.price, p.vat, p.discount, p.guarantee, p.producer, p.long_descr, p.parameters, pv.in_store, p.pieces_sold 
				FROM '.getTableName('products').' p JOIN '.getTableName('products_variations')." pv ON p.id=pv.id_product 
				WHERE updated >= '".date('Y-m-d H:i:s', iso8601_decode($params['dateFrom']))."'";

    $SQL = sqlQuery($query);

    if (sqlNumRows($SQL) == 0) {
        $response['status'] = 204;
        $response['statusMessage'] = getTextString('errors', 204);

        return new xmlrpcresp(php_xmlrpc_encode($response));
    }

    $count = 0;
    while ($row = @sqlFetchAssoc($SQL)) {
        $pictures = [];

        $dataItem = [
            'header' => [
                'productId' => intval($row['id']),
                'title' => cs_win2ascii($row['title']),
                'code' => cs_win2ascii(POHODA_CreateVariationCode($row['code'], $row['id'])),
                'price' => doubleval($row['price']),
                'rateVAT' => doubleval($row['vat']),
                'discount' => doubleval($row['discount']),
                'guaranteeType' => 'month',
                'guaranteeQuantity' => intval($row['guarantee']),
                'producer' => cs_win2ascii($producer[$row['producer']]),
                'description' => cs_win2ascii($row['long_descr']),
                'specification' => cs_win2ascii($row['parameters']),
                'stockQuantity' => intval($row['in_store']),
                'piecesSold' => intval($row['pieces_sold']),
            ],
            'pictures' => $pictures,
        ];

        $response['responseData'][$count] = $dataItem;
        // $response['responseData'][$count] = $row;
        $count++;

        unset($pictures);
    }

    return new xmlrpcresp(php_xmlrpc_encode($response));
}

// ----------------------------------------------------
// prijimani zmen z POHODY

function POHODA_ProductsUpload(&$params)
{
    global $cfg;

    $requestParams = &$params['requestParams'];
    $params = &$params['requestData'];

    // logError(__FILE__, __LINE__, print_r($params, true), true);

    $response = [
        'status' => 200,
        'statusMessage' => getTextString('errors', 200),
        'requestedParams' => $requestParams,
        'responseData' => [],
    ];

    //    logError(__FILE__, __LINE__, 'Pohoda: pocet produktu: '.count($params), true);
    //    logError(__FILE__, __LINE__, 'Prvni synchronizovany: '.print_r(reset($params), true), true);

    $updated_codes = [];

    foreach ($params as $param) {
        if (!empty($param['header']['type'])) {
            $response['responseData'][] = call_user_func("POHODA_ProductsUpload_{$param['header']['type']}", $param);
            continue;
        }

        // Parse product code and separate code/variants
        $product = POHODA_ParseProductCode($param['header']['code'], $param['header']);
        $param['header']['code'] = $product['code'];

        if (!empty($product['productId'])) {
            $param['header']['productId'] = $product['productId'];
        }
        if (!empty($product['variationId'])) {
            $param['header']['variationId'] = $product['variationId'];
        }

        if (!isset($param['header']['productId'])) {
            $param['header']['productId'] = 0;
        }

        // handle variant ID in product ID
        if ($param['header']['productId'] < 0) {
            $query = 'SELECT p.id, p.code FROM products_variations pv LEFT JOIN '.getTableName('products').' p ON pv.id_product=p.id WHERE pv.id='.(-$param['header']['productId']);
            $SQL = sqlQuery($query);
            if ($row = sqlFetchAssoc($SQL)) {
                if ($row['code'] == $param['header']['code']) {
                    $param['header']['variationId'] = -$param['header']['productId'];
                    $param['header']['productId'] = $row['id'];
                } else {
                    // logError(__FILE__, __LINE__, "Product code differ!: ".print_r($param, true)." != ".print_r($row, true), true);
                    $param['header']['productId'] = 0;
                }
            } else {
                $param['header']['productId'] = 0;
            }
        }

        $param['header']['productIdOld'] = $param['header']['productId'];

        $structItemDetails = null;

        // pokud se posle dataItemStatus deleted smaze polozku z databaze
        if (isset($param['dataItemStatus']) && ($param['dataItemStatus'] == 'deleted')) {
            // logError(__FILE__, __LINE__, "Delete product: ".print_r($param, true)." - ".print_r($product, true), true);

            if (!empty($cfg['Sync']['DeleteProducts']) && !empty($param['header']['productId'])) {
                $productObj = new Product($param['header']['productId']);

                if (count($product['variants']) != 0 && !empty($param['header']['variationId'])) {
                    // Delete variant
                    // logError(__FILE__, __LINE__, "Deleting product: {$param['header']['productId']} variation: {$param['header']['variationId']}");
                    $productObj->deleteVariation($param['header']['variationId']);

                    $structItemDetails = POHODA_getDataItemDetails($param['header']['productId'], 'ok', 200, '');
                    $response['responseData'][] = $structItemDetails;

                    continue;
                } elseif (count($product['variants']) == 0) {
                    // Delete product without variants
                    // logError(__FILE__, __LINE__, "Deleting product: {$param['header']['productId']}");
                    $rows = $productObj->deleteVariation();

                    if ($rows != 0) {
                        $structItemDetails = POHODA_getDataItemDetails($param['header']['productId'], 'ok', 200, '');
                        $response['responseData'][] = $structItemDetails;
                    } else {
                        $structItemDetails = POHODA_getDataItemDetails($param['header']['productId'], 'warning', 404, '');
                        $response['responseData'][] = $structItemDetails;
                    }

                    continue;
                }
            }

            // logError(__FILE__, __LINE__, "Not handled delete: ".print_r($params[$i], true)." - ".print_r($product, true));

            $structItemDetails = POHODA_getDataItemDetails($param['header']['productId'], 'ok', 200, '');
            $structItemDetails['dataItemId'] = $param['header']['id'];
            // $structItemDetails = POHODA_getDataItemDetails(0, 'error', 400, '');
            $response['responseData'][] = $structItemDetails;
            continue;
        }

        if (empty($param['header']['code'])) {
            if (!isset($cfg['Sync']['skipErrors'])) {
                logError(__FILE__, __LINE__, 'Empty code: '.$param['header']['code'], true);
            }
            $structItemDetails = POHODA_getDataItemDetails(0, 'error', 400, '');
            $response['responseData'][] = $structItemDetails;
            continue;
        }

        if ($param['header']['productId'] == 0) {
            $query = 'SELECT id FROM '.getTableName('products')." WHERE code='".$param['header']['code']."'";
            $SQL = sqlQuery($query);
            if ($row = sqlFetchAssoc($SQL)) {
                $param['header']['productId'] = $row['id'];

            /*if(!isset($updated_codes[$param['header']['code']])){
               //logError(__FILE__, __LINE__, "Deleting previous variations: ".$param['header']['productId'].", code: '".$param['header']['code']."'", true);
               sqlQuery("DELETE FROM ".getTableName("products_variations_choices_categorization")." WHERE id_product=".$param['header']['productId']);
              sqlQuery("DELETE FROM pv USING ".getTableName("products_variations")." pv LEFT JOIN ".getTableName("products_variations_combination")." pvc ON pv.id=pvc.id_variation WHERE pvc.id_variation IS NULL");
            }*/
            } else {
                if (empty($cfg['Sync']['CreateProducts'])) {
                    // logError(__FILE__, __LINE__, "Unknown product: ".$param['header']['code'], true);
                    $values = POHODA_setDataItemValues('productId', 0, 'id', 0);
                    $structItemDetails = POHODA_getDataItemDetails(0, 'error', 400, $values);
                    $response['responseData'][] = $structItemDetails;
                    continue;
                }
            }
        }

        if (!isset($updated_codes[$param['header']['code']])) {
            // pokud se preda productId nebo neni nulove provede se update polozky, jinak se vlozi nova
            // queryHead je nutna pro pripad ze productId predane v params neexistuje v databazi a nedojde k updatu ale provede se dodatecne nove vlozeni
            if (isset($param['header']['productId']) && (!empty($param['header']['productId']) || $param['header']['productId'] != 0)) {
                $update = true;
                $queryHead = 'UPDATE '.getTableName('products').' SET ';
            } else {
                $update = false;
                $queryHead = 'REPLACE INTO '.getTableName('products').' SET ';
            }

            $query = POHODA_ProductsUpload_getQuery($param, $update);

            if ($update) {
                $queryEnd = 'WHERE id='.intval($param['header']['productId']);
            } else {
                $queryEnd = '';
            }

            // logError(__FILE__, __LINE__, $queryHead.$query.$queryEnd);
            $SQL = sqlQuery($queryHead.$query.$queryEnd);

            // pokud byl vlozen nebo upraven radek vrati type "ok" a status 200 nebo "warning" a jiny status
            // pokud nedoslo k upraveni radky ani k vlozeni noveho vrati "error"
            if (sqlAffectedRows($SQL) != 0) {
                if ($update) {
                    if (!empty($cfg['Sync']['LogUpdateProduct'])) {
                        static $logger = null;

                        $logger = $logger ?? ServiceContainer::getService('logger');
                        $logger->error('PohodaSync', ['query' => $queryHead.$query.$queryEnd, 'params' => $param]);
                    }
                    // logError(__FILE__, __LINE__, "Update", true);
                    $values = POHODA_setDataItemValues('productId', $param['header']['productId'], 'id', $param['header']['productId']);
                    $structItemDetails = POHODA_getDataItemDetails($param['header']['productId'], 'ok', 200, $values);
                } else {
                    // logError(__FILE__, __LINE__, "New one id:".sqlInsertId(), true);
                    $values = POHODA_setDataItemValues('productId', $param['header']['productId'], 'id', sqlInsertId());
                    $structItemDetails = POHODA_getDataItemDetails(sqlInsertId(), 'ok', 200, $values);
                    $param['header']['productId'] = sqlInsertId();
                }
            } elseif ($update) {
                // byla pozadovana uprava polozky ale productId predane v params neexistuje v databazi
                // vlozi se jako nova polozka a vrati se id nove polozky
                if (!empty($cfg['Sync']['CreateProducts'])) {
                    $queryHead = 'INSERT INTO '.getTableName('products').' SET ';

                    $query = POHODA_ProductsUpload_getQuery($param, false);

                    logError(__FILE__, __LINE__, 'spatne id pokus o vlozeni nove polozky: '.$param['header']['productId'].' inserting: '.$queryHead.$query.print_r($param, true), true);

                    $SQL = sqlQuery($queryHead.$query);

                    if (sqlAffectedRows($SQL) != 0) {
                        /*$values = POHODA_setDataItemValues("productId", $param['header']['productId'], "id", sqlInsertId());
                     $structItemDetails = POHODA_getDataItemDetails(sqlInsertId(), 'warning', 800, $values);*/
                        $param['header']['productId'] = sqlInsertId();
                    } else {
                        $values = POHODA_setDataItemValues('productId', $param['header']['productId'], 'id', 0);
                        $structItemDetails = POHODA_getDataItemDetails($param['header']['productId'], 'error', 404, $values);
                    }
                } else {
                    $structItemDetails = POHODA_getDataItemDetails(0, 'error', 500, '');
                }
            } else {
                $structItemDetails = POHODA_getDataItemDetails(0, 'error', 500, '');
            }

            $updated_codes[$param['header']['code']] = $param['header']['productId'];
        }

        // Make sure variant exists and update variant stock
        if (!empty($product['variants'])) {
            if (!empty($param['header']['variationId'])) {
                $variation_id = $param['header']['variationId'];
            } else {
                $variation_id = Variations::createProductVariation($param['header']['productId'], $product['variants']);
            }
            $param['header']['variationId'] = $variation_id;

            // Finally update stock value
            $values = POHODA_ProductsUpload_getQuery($param, false, true);
            $query = 'UPDATE '.getTableName('products_variations').' SET '.$values.' WHERE id='.$variation_id;
            $SQL = sqlQuery($query);
        }
        /*else
         logError(__FILE__, __LINE__, "Nejsou varianty: ".$param['header']['productId'], true);*/

        if (!isDevelopment() && !empty($cfg['Sync']['Photos']['url']) && !empty($param['header']['photos'])) {
            $photos = explode(';', rtrim($param['header']['photos'], ';'));

            $downloader = new Downloader();
            $downloader->setMethod('curl');
            $photosIds = [];
            foreach ($photos as $photo) {
                $photosIds[] = $downloader->importProductImage($cfg['Sync']['Photos']['url'].$photo, true);
            }

            $first = true;
            sqlStartTransaction();
            sqlQuery('DELETE FROM photos_products_relation WHERE id_product=:id_product', ['id_product' => $param['header']['productId']]);
            foreach ($photosIds as $photoId) {
                sqlQuery('INSERT IGNORE INTO '.getTableName('photos-products').' (id_photo, id_product, show_in_lead, active) VALUES (:id_photo, :id_product, :show_in_lead, :active)',
                    [
                        'id_photo' => $photoId,
                        'id_product' => $param['header']['productId'],
                        'show_in_lead' => $first ? 'Y' : 'N',
                        'active' => 'Y',
                    ]);
                $first = false;
            }
            sqlFinishTransaction();
        }

        if (!empty($param['header']['productId'])) {
            foreach ($cfg['Sync']['Languages'] ?? [] as $lang) {
                if (!empty($param['header']["title_{$lang}"] ?? null)) {
                    $productsTranslations = ServiceContainer::getService(\KupShop\I18nBundle\Translations\ProductsTranslation::class);
                    $productsTranslations->saveSingleObject($lang,
                        $param['header']['productId'], [
                            'title' => $param['header']["title_{$lang}"],
                        ]
                    );
                }
            }

            if (!empty($cfg['Sync']['Parameters'])) {
                global $listParameterAll;
                if (empty($listParameterAll)) {
                    $listParameterAll = Parameter::get();
                }

                foreach ($cfg['Sync']['Parameters'] ?? [] as $field_name => $id) {
                    $existsValues = sqlFetchAll(sqlQuery('SELECT * FROM parameters_products WHERE id_product=:id_product and id_parameter=:id_parameter', ['id_product' => $param['header']['productId'], 'id_parameter' => $id]));

                    if ($existsValues) {
                        continue;
                    }

                    sqlQuery('DELETE FROM parameters_products WHERE id_product=:id_product and id_parameter=:id_parameter', ['id_product' => $param['header']['productId'], 'id_parameter' => $id]);

                    $parameter = $listParameterAll[$id];

                    $values = explode(';', rtrim($param['header'][$field_name], ';'));

                    foreach ($values as $value) {
                        if (!empty($param['header'][$field_name])) {
                            $val = getParamValue($parameter, $value);
                            $data = [
                                "value_{$parameter->value_type}" => $val,
                                'id_product' => $param['header']['productId'],
                                'id_parameter' => $parameter->id,
                                'value' => $val,
                                'unit' => getVal(0, $parameter->unit),
                            ];
                            $parameter->setValue($data);
                        }
                    }
                }
            }

            if (updatable($param['header'], 'section_ids', !$update)) {
                $section_ids = array_filter(explode(';', rtrim($param['header']['section_ids'], ';')));

                if (count($section_ids) > 0) {
                    POHODA_ProductsUpdateSections($section_ids, $param['header']['productId']);
                }
            }

            if (!empty($cfg['Sync']['ProductsOfSuppliers']) && !empty($param['header']['supplier'])) {
                $search_fields = [
                    'id_supplier' => intval($param['header']['supplier']),
                    'id_product' => $param['header']['productId'],
                ];

                $update_fields = [
                    'in_store' => intval($param['header']['stockQuantity']),
                    'code' => $param['header']['code'],
                ];

                $id = sqlQueryBuilder()
                    ->select('id')
                    ->from('products_of_suppliers')
                    ->where(\Query\Operator::equals($search_fields))
                    ->execute()->fetchColumn();

                if ($id) {
                    sqlQueryBuilder()
                        ->update('products_of_suppliers')
                        ->directValues($update_fields)
                        ->set('last_sync', 'NOW()')
                        ->where(\Query\Operator::equals(['id' => $id]))
                        ->execute();
                } else {
                    sqlQueryBuilder()
                        ->insert('products_of_suppliers')
                        ->directValues(array_merge($search_fields, $update_fields))
                        ->set('last_sync', 'NOW()')
                        ->execute();
                }
            }

            if (!empty($cfg['Sync']['SubstractNewOrdersFromStore'])) {
                if (empty($param['header']['variationId'])) {
                    sqlQuery('UPDATE products p
                        JOIN (
                         SELECT oi.id_product, SUM(pieces) pieces
                         FROM order_items oi
                           JOIN orders o ON o.id = oi.id_order
                         WHERE o.status = 0 AND oi.id_product=:id_product AND oi.id_variation IS NULL
                         GROUP BY oi.id_product
                       ) q ON q.id_product = p.id
                      SET p.in_store = p.in_store - q.pieces', ['id_product' => $param['header']['productId']]);
                } else {
                    sqlQuery('UPDATE products_variations pv
                            JOIN (
                             SELECT oi.id_variation, SUM(pieces) pieces
                             FROM order_items oi
                               JOIN orders o ON o.id = oi.id_order
                             WHERE o.status = 0 AND oi.id_product=:id_product AND oi.id_variation=:id_variation
                             GROUP BY oi.id_variation
                           ) q ON q.id_variation=pv.id
                          SET pv.in_store = pv.in_store - q.pieces', [
                        'id_product' => $param['header']['productId'],
                        'id_variation' => $param['header']['variationId'],
                    ]);
                }
            }

            if (!empty($cfg['Sync']['Stores'])) {
                foreach ($cfg['Sync']['Stores'] as $store) {
                    if (isset($param['header']["store{$store}"])) {
                        $storesService = ServiceContainer::getService(\KupShop\StoresBundle\Utils\StoresInStore::class);
                        $storesService->updateStoreItem([
                            'quantity' => $param['header']["store{$store}"],
                            'id_store' => $store,
                            'id_product' => $param['header']['productId'],
                            'id_variation' => $param['header']['variationId'] ?? null,
                        ], false);
                    }
                }
            }
        }

        if ($structItemDetails == null) {
            // logError(__FILE__, __LINE__, "Last empty: ".$param['header']['productId']."-".$param['header']['productIdOld'] . ", code: '".$param['header']['code']."'", true);
            $values = '';
            if ($param['header']['productIdOld'] != $param['header']['productId']) {
                $values = POHODA_setDataItemValues('productId', $param['header']['productIdOld'], 'id', $param['header']['productId']);
            }
            $structItemDetails = POHODA_getDataItemDetails($param['header']['productId'], 'ok', 200, $values);
        }

        if (!empty($product['variants'])) {
            $structItemDetails['dataItemId'] = -$variation_id;
        }

        $response['responseData'][] = $structItemDetails;
    }

    POHODA_UpdateProductsSupplier();

    // Set product price from variations
    if (!empty($cfg['Sync']['SetProductPrice'])) {
        sqlQuery('UPDATE products p
            SET p.price=(
            SELECT COALESCE(MIN(pv.price), p.price)
            FROM products_variations pv
            WHERE pv.id_product=p.id AND pv.in_store > 0
        )');
    }

    // Set product price from variations
    if (!empty($cfg['Sync']['Collections'])) {
        POHODA_SyncCollections();
    }

    return new xmlrpcresp(php_xmlrpc_encode($response));
}

function POHODA_ProductsUpload_section($item)
{
    $cfg = \KupShop\KupShopBundle\Config::get();

    $data = $item['header'];
    $id = $data['code'];

    if (empty($cfg['Sync']['Sections'])) {
        return POHODA_getDataItemDetails($id, 'ok', 200, '');
    }

    if ($param['dataItemStatus'] ?? null == 'deleted') {
        sqlQuery('UPDATE sections SET figure=\'N\' WHERE id=:code', $data);

        return POHODA_getDataItemDetails($id, 'ok', 200, '');
    }

    $exists = sqlQuery('SELECT * FROM sections WHERE id=:code', $data)->fetch();

    $qb = sqlQueryBuilder()
        ->{$exists ? 'update' : 'insert'}('sections')
        ->andWhere(\Query\Operator::equals(['id' => $id]));

    $values = [];
    if (isset($id)) {
        $values['id'] = $id;
    }

    if (isset($data['title'])) {
        $values['name'] = $data['title'];
    }

    if (isset($data['visible'])) {
        $values['figure'] = $data['visible'] == 'true' ? 'Y' : 'N';
    }

    $qb->directValues($values)
        ->execute();

    if (!isDevelopment() && !empty($data['photo'])) {
        $downloader = new Downloader();
        $downloader->setMethod('curl');
        $picture = $downloader->downloadImage($cfg['Sync']['Photos']['url'].trim($data['photo']));

        if ($picture) {
            $img = new Photos('section', $cfg['Path']['admin_to_root']);
            $img->newImage($id);
            $img->uploadImage($picture, false);

            if ($img->checkFileType()) {
                $img->insertImageIntoDB();
            }
        }
    }

    if (!empty($data['id_parent'])) {
        sqlQuery('DELETE FROM sections_relation WHERE id_section = :code', $data);

        sqlQueryBuilder()->insert('sections_relation')
            ->directValues(['id_section' => $id, 'id_topsection' => $data['id_parent'], 'position' => $data['position']])
            ->execute();
    }

    MenuSectionTree::invalidateCache();

    return POHODA_getDataItemDetails($id, 'ok', 200, '');
}

function POHODA_ProductsUpdateSections($section_ids, $product_id)
{
    sqlStartTransaction();

    sqlQuery('DELETE FROM products_in_sections WHERE id_product=:id_product', ['id_product' => $product_id]);

    $sectionTree = ServiceContainer::getService(SectionTree::class);

    foreach ($section_ids as $id) {
        $section = $sectionTree->getSectionById($id);

        if ($section && count($section->getChildren()) == 0) {
            sqlQuery('INSERT INTO products_in_sections (id_product, id_section) VALUES (:id_product, :id_section)',
                ['id_product' => $product_id, 'id_section' => $id]);
        }
    }

    sqlFinishTransaction();
}

/**
 * @return string
 */
function POHODA_ProductsUpload_getQuery($param, $update, $variation = false)
{
    global $cfg;
    $query = '';

    $vat_id = getVatID($param['header']['rateVAT']);
    $producer_id = getProducer($param['header']['producer']);

    if (updatable($param['header'], 'EAN', !$update)) {
        $query .= 'ean='.intval($param['header']['EAN']).', ';
    }
    if (updatable($param['header'], 'price', !$update)) {
        $query .= "price='".doubleval($param['header']['price'])."', ";
    }
    if (updatable($param['header'], 'stockQuantity', !$update)) {
        $query .= 'in_store='.intval($param['header']['stockQuantity']).', ';
    }

    if (!$variation) {
        if (updatable($param['header'], 'code', !$update)) {
            $query .= "code='".sqlFormatInput($param['header']['code'])."', ";
        }
        if (updatable($param['header'], 'title', !$update)) {
            $query .= "title='".sqlFormatInput($param['header']['title'])."', ";
        }
        if (updatable($param['header'], 'rateVAT', !$update)) {
            $query .= 'vat='.intval($vat_id).', ';
        }
        if (updatable($param['header'], 'discount', !$update)) {
            $query .= 'discount='.doubleval($param['header']['discount']).', ';
        }
        if (updatable($param['header'], 'guaranteeQuantity', !$update)) {
            $query .= 'guarantee='.intval($param['header']['guaranteeQuantity']).', ';
        }
        if (updatable($param['header'], 'piecesSold', !$update)) {
            $query .= 'pieces_sold='.intval($param['header']['piecesSold']).', ';
        }
        if (updatable($param['header'], 'description', !$update)) {
            $query .= "long_descr='".sqlFormatInput($param['header']['description'])."', ";
        }
        if (updatable($param['header'], 'specification', !$update)) {
            $query .= "parameters='".sqlFormatInput($param['header']['specification'])."', ";
        }
        if (updatable($param['header'], 'producer', !$update) && $param['header']['producer'] != '') {
            $query .= 'producer='.intval($producer_id).', ';
        }
        if (updatable($param['header'], 'flags', !$update)) {
            $flags = trim($param['header']['flags'], ',');

            $sync_flags = $cfg['Sync']['FlagsToSync'];

            $query .= "campaign=ADD_TO_SET('".$flags."', ".recurseRemoveFlag($sync_flags).'), ';
        }
        if (!$update) {
            $query .= 'date_added=NOW(), ';
        }
        $query .= "updated='".date('Y-m-d H:i:s')."' ";
    } else {
        if (updatable($param['header'], 'variation_EAN', !$update)) {
            $query .= 'ean='.intval($param['header']['variation_EAN']).', ';
        }
        if (updatable($param['header'], 'variation_price', !$update)) {
            $query .= "price='".processVat(doubleval($param['header']['variation_price']), $vat_id)."', ";
        }
        if (updatable($param['header'], 'variation_code', !$update)) {
            $query .= "code='".sqlFormatInput($param['header']['variation_code'])."', ";
        }

        if (updatable($param['header'], 'weight', !$update)) {
            $query .= "weight='".sqlFormatInput($param['header']['weight'])."', ";
        }
    }

    foreach ($cfg['Sync']['PriceLists'] ?? [] as $priceListFieldName => $idPriceList) {
        if (!empty($param['header']["{$priceListFieldName}Price"])) {
            $priceListWorker = \KupShop\KupShopBundle\Util\Compat\ServiceContainer::getService(\KupShop\PricelistBundle\Util\PriceListWorker::class);
            $priceListWorker->updatePricelists($idPriceList, [
                'price' => processVat($param['header']["{$priceListFieldName}Price"], $vat_id),
                'discount' => $param['header']["{$priceListFieldName}Discount"] ?? null,
                'showVat' => 'N',
            ], $param['header']['productId'], getVat($vat_id), $param['header']['variationId'] ?? null);
        }
    }

    return rtrim($query, ', ');
}

function processVat($price, $vat_id)
{
    global $cfg;

    if (!empty($cfg['Sync']['PriceWithVat'])) {
        $price = calcPrice($price, -getVat($vat_id));
    }

    return $price;
}

function recurseRemoveFlag($flags)
{
    if (empty($flags[0])) {
        return 'campaign';
    }

    $flag = array_shift($flags);

    return "REMOVE_FROM_SET('".$flag."', ".recurseRemoveFlag($flags).')';
}

function getParamValue($parameter, $value)
{
    foreach ($parameter->fetchListValues() as $listValue) {
        if (mb_strtolower($listValue['value'], 'utf-8') == mb_strtolower($value, 'utf-8')) {
            return $listValue['id'];
        }
    }

    sqlQuery('INSERT INTO parameters_list (id_parameter, value) VALUES (:id_parameter, :value)', ['id_parameter' => $parameter->id, 'value' => $value]);
    $value = sqlInsertId();
    $parameter->fetchListValues(true);

    return $value;
}

/*
 * SELECT p.id, p.code, p.title, pv.title as variant FROM products_variations pv left join products p on p.id=pv.id_product where sync_count<5
 */