<?php

use Doctrine\DBAL\Exception\UniqueConstraintViolationException;
use KupShop\AdminBundle\Util\ActivityLog;
use KupShop\CatalogBundle\Section\SectionTree;
use KupShop\KupShopBundle\Config;
use KupShop\KupShopBundle\Context\ContextManager;
use KupShop\KupShopBundle\Util\Compat\ServiceContainer;
use KupShop\KupShopBundle\Util\Database\QueryHint;
use KupShop\KupShopBundle\Util\Logging\SentryLogger;
use Psr\Log\LoggerInterface;
use Query\QueryBuilder;
use Symfony\Component\HttpKernel\Exception\NotFoundHttpException;

mb_internal_encoding('utf-8');

set_time_limit(999999);

ini_set('memory_limit', '2G');

QueryHint::routeToMaster();

getRaven()->setChannel(SentryLogger::CHANNEL_SYNC);

class AbraBase
{
    use DatabaseCommunication;

    protected $client;

    protected $batch_size = 1000;
    protected $separator = '~|~';
    protected $settings = [];
    protected $abraNull = '0000000000';
    protected $paginateByCode = false;

    public static $ABRA_UIDS;
    public static $PRODUCT_FIELDS;
    public static $SECTION_FIELDS;
    public static $PRODUCER_FIELDS;

    /** @var LoggerInterface */
    protected $logger;

    protected ContextManager $contextManager;

    public function __construct($settings = null)
    {
        if (is_null($settings)) {
            $settings = Config::get()['Modules']['abra'];
        }

        $this->settings = array_merge($this->settings, $settings);

        // In development/review use test Abra server
        if (isDevelopment()) {
            if (isset($this->settings['test_server'])) {
                $this->settings['server'] = $this->settings['test_server'];
            }
        }

        ini_set('default_socket_timeout', 300);

        $this->client = $this->getSoapClient();

        $this->logger = ServiceContainer::getService('logger');
        $this->contextManager = ServiceContainer::getService(ContextManager::class);
    }

    public function getSoapClient($server = 'server')
    {
        // Allow incorrect SSL certificate
        $context = stream_context_create([
            'ssl' => [
                // set some SSL/TLS specific options
                'verify_peer' => false,
                'verify_peer_name' => false,
                'allow_self_signed' => true,
            ],
        ]);

        $options = [
            'trace' => 1,
            'features' => SOAP_SINGLE_ELEMENT_ARRAYS,
            'connection_timeout' => 300,
            'location' => str_replace('/wsdl/', '/soap/', $this->settings[$server]),
            'stream_context' => $context,
        ];
        if (!empty($this->settings['basic_auth_login'])) {
            $options['login'] = $this->settings['basic_auth_login'];
        }
        if (!empty($this->settings['basic_auth_password'])) {
            $options['password'] = $this->settings['basic_auth_password'];
        }

        return new SoapClient($this->settings[$server], $options);
    }

    public function prettyXml($string)
    {
        if (!$string) {
            return;
        }

        $domxml = new DOMDocument('1.0');
        $domxml->preserveWhiteSpace = false;
        $domxml->formatOutput = true;
        /* @var $xml SimpleXMLElement */
        $domxml->loadXML($string);

        return $domxml->saveXML();
    }

    public function printTrace()
    {
        $requestHeaders = $this->client->__getLastRequestHeaders();
        $request = $this->prettyXml($this->client->__getLastRequest());
        $responseHeaders = $this->client->__getLastResponseHeaders();
        $response = $this->prettyXml($this->client->__getLastResponse());

        echo '<code>'.nl2br(htmlspecialchars($requestHeaders, true)).'</code>';
        echo highlight_string($request, true)."<br/>\n";

        echo '<code>'.nl2br(htmlspecialchars($responseHeaders, true)).'</code>'."<br/>\n";
        echo highlight_string($response, true)."<br/>\n";
    }

    public function logTrace($type)
    {
        $requestHeaders = $this->client->__getLastRequestHeaders();
        $request = $this->prettyXml($this->client->__getLastRequest());
        $responseHeaders = $this->client->__getLastResponseHeaders();
        $response = $this->prettyXml($this->client->__getLastResponse());

        $this->log(["Trace send {$type}", $requestHeaders, $request]);
        $this->log(["Trace receive {$type}", $responseHeaders, $response]);
    }

    public static $GET_ALL_TYPES = [
        '42P1E2VUANDL342Q01C0CX3FCC' => [
            'batch' => 100,
        ],
        'C3V5QDVZ5BDL342M01C0CX3FCC' => [
            'batch' => 100,
        ],
        'GAWVAN4GFNDL342T01C0CX3FCC' => [
            'batch' => 100,
        ],
        'GHYLVQXQ3FE13DQC01C0CX3F40' => [
            'batch' => 100,
        ],
        '05CPMINJW3DL342X01C0CX3FCC' => [
            'batch' => 100,
        ],
        'G2WVAN4GFNDL342T01C0CX3FCC' => [
            'batch' => 100,
        ],
        '4K3EXM5PQBCL35CH000ILPWJF4' => [
            'batch' => 100,
        ],
        'G5YLVQXQ3FE13DQC01C0CX3F40' => [
            'batch' => 100,
        ],
    ];

    protected $type;

    public function getAllData($type = null, $params = [])
    {
        if (!empty($params['transaction'])) {
            sqlStartTransaction();
        }

        while (ob_get_level()) {
            ob_end_flush();
        }

        $defaults = [
            'start' => $this->abraNull,
            'batch' => null,
            'count' => null,
        ];

        $types = static::$GET_ALL_TYPES;

        if ($type) {
            if (substr($type, -1) == '+' || substr($type, -1) == ' ') {
                $type = substr($type, 0, -1);
                $types = array_slice($types, array_search($type, array_keys($types)));
            } else {
                $types = [$type => ($types[$type] ?? [])];
            }
        }

        foreach ($types as $type => $options) {
            $this->type = $type;
            $options = array_merge($defaults, $options, $params);

            echo "<br>Starting {$type}<br>";
            flush();

            if (empty(static::$ABRA_UIDS[$type]) || static::$ABRA_UIDS[$type] == 'ignore') {
                echo "ignored.\n";
                continue;
            }

            $index = $options['start'];
            $options['start'] = $this->abraNull;
            $old_index = null;
            $retry_count = 3;
            $total_count = $options['count'];
            $count = 0;

            do {
                $old_index = $index;

                echo "<br>Fetching {$index} ...";
                logError(__FILE__, __LINE__, "Fetching {$type} : {$index} ...");
                flush();

                $timestamp = getScriptTime();

                try {
                    $items = $this->getOneDataBlock($type, $index, $options['batch']);
                } catch (Exception $e) {
                    $this->printTrace();

                    if (--$retry_count > 0) {
                        $old_index = null;
                        continue;
                    }

                    throw $e;
                }

                if (!empty($params['test'])) {
                    $this->printTrace();
                }
                $delay = number_format(getScriptTime() - $timestamp, 2);
                $timestamp = getScriptTime();

                echo "Took {$delay} s. <br>Processing ....";
                flush();

                foreach ($items as $change) {
                    $index = $this->processChange(join($this->separator, [0, substr($change, 4)]), $index);
                }

                $count += count($items);
                $delay = number_format(getScriptTime() - $timestamp, 2);
                $timestamp = getScriptTime();
                echo "Took {$delay} s. <br>Updating delayed ....";

                try {
                    $this->updateScheduledProducts();
                } catch (Exception $e) {
                    var_dump(['updateScheduledProducts exception', $e->getMessage()]);
                }

                $delay = number_format(getScriptTime() - $timestamp, 2);
                echo "Took {$delay} s";
                $retry_count = 3;
            } while ($old_index != $index && (is_null($total_count) || $total_count > $count));

            echo "<br>Ended {$type}<br>";
            flush();
        }

        $this->finalizeSync();
    }

    public function getOneDataBlock($type, $index, $batch)
    {
        // detect CLSID
        if (strlen($type) == 26) {
            return $this->client->weGetAll($type, $index, $batch);
        }

        $selection = null;
        $filter = null;
        if (isset($_GET['filter']) && !empty($_GET['filter'])) {
            $filter = explode(',', $_GET['filter']);
            $filter = array_map(function ($value) { return "'".$value."'"; }, $filter);
            $filter = join(',', $filter);
            $selection = 'SELECTION';
        }

        $this->paginateByCode = true;

        return $this->client->weGetObjectType($type, $index, $batch, $selection, $filter);
    }

    public function updateScheduledProducts()
    {
    }

    public function sync()
    {
        $this->syncOrders();

        $this->syncProducts();

        $this->finalizeSync();
    }

    // Utility functions
    public function getLastChangeID()
    {
        return intval(sqlFetchAssoc($this->selectSQL('abra_settings', ['name' => 'last_change'], ['value']))['value'] ?? 0);
    }

    public function updateLastChangeID($newID)
    {
        $this->updateSQL('abra_settings', ['value' => $newID], ['name' => 'last_change']);
    }

    public function updateOrderStatuses()
    {
    }

    public function authorizeOrders($spec, $message)
    {
        $orders = sqlQueryBuilder()->select('o.id')->from('orders', 'o')
            ->join('o', 'abra_orders', 'ao', 'ao.id_order = o.id')
            ->join('o', 'delivery_type', 'dt', 'dt.id = o.id_delivery')
//            ->andWhere(\Query\Operator::not(\Query\Operator::findInSet(['D'], 'o.flags')))
            ->andWhere($spec)->execute();
        foreach ($orders as $order) {
            $this->changeOrderStatus($order['id'], 1, $message);
        }
    }

    public function changeOrderStatus($id_order, $status, $comment, $service_note = null, $emailType = null)
    {
        try {
            $order = new Order();
            $order->createFromDB($id_order);

            $this->log(['Change order status', 'id' => $id_order, 'code' => $order->order_no, 'status' => $status, 'comment' => $comment]);

            $ret = $this->client->__call('weSetReceivedOrderSimpleUpdate', [$this->getWebSource($order), $this->getOrderNumber($order), ["Status~|~{$status}"]]);
            $this->printTrace();

            if ($service_note) {
                $service_note = $this->prepareFieldLengthForOrder($this->prepareTextForOrder($service_note), 200);
                $this->client->__call('weSetReceivedOrderSimpleUpdate', [$this->getWebSource($order), $this->getOrderNumber($order), ["CustomerServiceNote~|~{$service_note}"]]);
            }

            if ($ret[0] != 'True') {
                throw new Exception("{$ret[1]} - {$ret[2]}");
            }

            $order->changeStatus($status, $comment, isset($emailType), null, $emailType);
        } catch (Exception $e) {
            $this->log(['Change order status', 'id' => $id_order, 'exception' => $e->getMessage()]);

            $this->logException($e);
        }
    }

    protected $mappingCache = ['id_abra' => null, 'id' => null];

    public function getMappingFromAbra($type, $code)
    {
        if ($this->mappingCache['id_abra'] == $type.$code) {
            return $this->mappingCache['id'];
        }

        $id = returnSQLResult("SELECT id_{$type} FROM abra_{$type}s WHERE id_abra=:code", ['code' => $code]);
        if ($id) {
            $this->mappingCache = ['id_abra' => $type.$code, 'id' => $id];
        }

        return $id;
    }

    public function getMappingFromAbraMultiple($type, $code)
    {
        $ids = sqlFetchAll(sqlQuery("SELECT id_{$type} as id FROM abra_{$type}s WHERE id_abra=:code ORDER BY id_{$type}", ['code' => $code]), ['id' => 'id']);

        return array_values($ids);
    }

    public function setMappingFromAbra($type, $id_kupshop, $code_abra)
    {
        sqlQuery("INSERT INTO abra_{$type}s (id_abra, id_{$type}) VALUES (:code, :id)", ['code' => $code_abra, 'id' => $id_kupshop]);
    }

    public function getMappingToAbra($type, $id)
    {
        $table = "abra_{$type}s";
        if ($type == 'delivery') {
            $table = 'abra_deliveries';
        }

        return returnSQLResult("SELECT id_abra FROM {$table} WHERE id_{$type}=:id", ['id' => $id]);
    }

    public function preparePriceToAbra($price)
    {
        return str_replace('.', ',', $price);
    }

    public function preparePriceFromAbra($price)
    {
        $value = str_replace(',', '.', $price);

        if ($value[0] === '.') {
            $value = '0'.$value;
        }

        if ($value[-1] === '.') {
            $value .= '0';
        }

        return $value;
    }

    public function parseBool($value)
    {
        return $value == 'Ano';
    }

    public function parseNullable($value)
    {
        if ($value == '' || ((string) $value) === $this->abraNull) {
            return null;
        }

        return $value;
    }

    public function updateProductFlag($data, $enable)
    {
        if ($enable) {
            sqlQuery('UPDATE products SET campaign = ADD_TO_SET(:flag, campaign) WHERE id=:id_product', $data);
        } else {
            sqlQuery('UPDATE products SET campaign = REMOVE_FROM_SET(:flag, campaign) WHERE id=:id_product', $data);
        }
    }

    public function updateVariationFlag($data, $enable)
    {
        if ($enable) {
            sqlQuery('UPDATE products_variations SET campaign = ADD_TO_SET(:flag, campaign) WHERE id=:id_variation', $data);
        } else {
            sqlQuery('UPDATE products_variations SET campaign = REMOVE_FROM_SET(:flag, campaign) WHERE id=:id_variation', $data);
        }
    }

    // Find functions
    protected $listProducer;

    public function findProducer($name)
    {
        if ($name == '') {
            return null;
        }

        if (is_null($this->listProducer)) {
            $this->listProducer = sqlFetchAll(sqlQuery('SELECT id, LOWER(name) AS name FROM producers'), ['id' => 'name']);
        }

        if (($index = array_search(mb_strtolower($name), $this->listProducer)) !== false) {
            return $index;
        }

        $max_position = returnSQLResult('SELECT MAX(position) FROM producers');
        $this->insertSQL('producers', ['name' => $name, 'position' => $max_position + 1]);
        $index = sqlInsertId();

        $this->listProducer[$index] = mb_strtolower($name);

        return $index;
    }

    protected $listVAT;
    protected $listVATDefault = 1;

    public function findVAT($percent)
    {
        if (is_null($this->listVAT)) {
            foreach (sqlQuery('SELECT id, vat, is_default FROM vats') as $row) {
                $this->listVAT[$row['id']] = $row['vat'];
                if ($row['is_default'] == 'Y') {
                    $this->listVATDefault = $row['id'];
                }
            }
        }

        if ($index = array_search($percent, $this->listVAT)) {
            return $index;
        }

        $this->insertSQL('vats', ['descr' => "Daň {$percent}%", 'vat' => $percent]);
        $index = sqlInsertId();

        $this->listVAT[$index] = $percent;

        return $index;
    }

    protected $listParameter = [];

    public function findParameterValue($parameter_id, $value)
    {
        if (!isset($this->listParameter[$parameter_id])) {
            $this->listParameter[$parameter_id] = sqlFetchAll(
                sqlQuery('SELECT id, LOWER(value) AS value
                      FROM parameters_list WHERE id_parameter=:id_parameter',
                    ['id_parameter' => $parameter_id]),
                ['id' => 'value']);
        }

        $params = &$this->listParameter[$parameter_id];

        if ($index = array_search(mb_strtolower($value), $params)) {
            return $index;
        }

        try {
            $this->insertSQL('parameters_list', ['value' => $value, 'id_parameter' => $parameter_id]);
            $index = sqlInsertId();
        } catch (Exception $e) {
            var_dump($e->getMessage());
            $index = sqlFetchAssoc($this->selectSQL('parameters_list', ['value' => $value, 'id_parameter' => $parameter_id], ['id']))['id'];
        }

        $params[$index] = mb_strtolower($value);

        return $index;
    }

    public function findCategory(&$categories, &$parentCat = null)
    {
        // Get next category part
        $categoryName = array_shift($categories);
        if ($categoryName === null) { // Terminate recurse
            return $parentCat['id'];
        }

        if (empty($categoryName)) {
            return $this->findCategory($categories, $parentCat);
        }

        // Get parent ID and menu
        $parentId = null;
        if ($parentCat) {
            $menu = &$parentCat['submenu'];
            $parentId = $parentCat['id'];
        } else {
            $menu = ServiceContainer::getService(SectionTree::class)->getTree();
        }

        // Find matching section
        $found = null;

        if (!empty($menu)) {
            foreach ($menu as &$category) {
                if (mb_strtolower($category['title']) == mb_strtolower($categoryName)) {
                    $found = &$category;
                    break;
                }
            }
        }

        // If not found, create one
        if (empty($found)) {
            $this->insertSQL('sections', ['name' => $categoryName, 'lead_figure' => 'N', 'behaviour' => 2]);

            $catId = sqlInsertId();

            $this->insertSQL('sections_relation', ['id_section' => $catId, 'id_topsection' => $parentId, 'position' => 99]);

            $found = [
                'id' => $catId,
                'title' => mb_strtolower($categoryName),
            ];

            $menu[] = &$found;
        }

        return $this->findCategory($categories, $found);
    }

    // Sync functions
    public function getOrdersToSync(): QueryBuilder
    {
        return sqlQueryBuilder()
            ->select('o.id')
            ->from('orders', 'o')
            ->leftJoin('o', 'abra_orders', 'ao', 'o.id=ao.id_order')
            ->where('ao.id_abra IS NULL AND o.date_created < DATE_SUB(NOW(), INTERVAL 30 SECOND)');
    }

    public function syncOrders()
    {
        $orders = $this->getOrdersToSync()->setMaxResults(10)->execute();

        foreach ($orders as $order) {
            $this->syncOrder($order['id']);
        }

        $this->updateOrderStatuses();
    }

    public function syncProducts()
    {
        $last_change = $this->getLastChangeID();
        echo "<br>last_change start: {$last_change}<br>";

        $startTime = getScriptTime();

        do {
            $this->logger->notice("Abra fetching: start:{$last_change}, size:{$this->batch_size}");
            $changes = $this->client->weGetChanges($last_change, $this->batch_size);
            $this->logger->notice('Abra fetched from:'.reset($changes).' to:'.end($changes));

            if (getVal('test')) {
                $this->printTrace();
            }

            if (empty($changes)) {
                return false;
            }

            if ($last_change > 0) {
                $first_change = explode($this->separator, reset($changes))[0];
                if ($first_change != $last_change + 1) {
                    $this->log(['Přeskočená synchronizace', $first_change, $last_change]);
                }
            }

            // logError(__FILE__, __LINE__, 'Abra sync: '.print_r($changes, true));
            foreach ($changes as $change) {
                $this->logger->notice("Abra sync: {$change}", ['change' => $change]);
                $this->processChange($change);
            }

            $last_change = explode($this->separator, end($changes))[0];

            echo "<br>last_change stop: {$last_change}<br>";
            $this->logger->notice('Abra update last change: '.$last_change);
            $this->updateLastChangeID($last_change);

            // Clear changes
            if (!isLocalDevelopment()) {
                var_dump($this->client->weClearChanges($last_change));
            }
        } while (count($changes) == $this->batch_size && (getScriptTime() - $startTime) < (3 * 60));

        return true;
    }

    public function processChange($change, $index = null)
    {
        $change = explode($this->separator, $change);
        $type = static::$ABRA_UIDS[$change[1]];
        $method = "sync_{$type}";

        $last_change = $change[0];
        if (!$last_change) {
            if ($this->paginateByCode) {
                $last_change = $change[3];
            } else {
                // Index by position
                static $last_id = null;
                $last_change = $index;
                if ($last_id != $change[3]) {
                    $last_change++;
                }
                $last_id = $change[3];
            }
        }

        if ($change[2] !== 'LastID') {
            $this->$method($change[3], $change[2], $change[4], $change[5] ?? null);
        }

        return $last_change;
    }

    public function sync_product($code, $field, $value, $value2 = null)
    {
        $id = $this->getMappingFromAbra('product', $code);

        if (!$id) {
            // Create Product
            $vat_id = null;
            getVat($vat_id);
            $this->insertSQL('products', ['code' => $code, 'vat' => $vat_id, 'figure' => 'N']);
            $id = sqlInsertId();

            $this->setMappingFromAbra('product', $id, $code);
        }

        $column = static::$PRODUCT_FIELDS[$field];
        if (!$column) {
            throw new Exception("Nedefinovaná položka synchronizace: {$field}");
        }

        $this->sync_products_switch($code, $column, $value, $value2, $id);
    }

    public function sync_products_switch($code, $column, $value, $value2, $id)
    {
        switch ($column) {
            case 'id_category':
                $id_section = $this->getMappingFromAbra('section', $value);
                if (!$id_section) {
                    if ($value > 0) {
                        echo 'Unknown section ID: '.$value;
                    }
                    //                  throw new Exception('Unknown section ID: '.$value);

                    return;
                }

                $this->deleteSQL('products_in_sections', ['id_product' => $id]);

                sqlQuery('REPLACE INTO '.getTableName('products_in_sections').' (id_product, id_section)
                      VALUES (:id_product, :id_section)', ['id_product' => $id, 'id_section' => $id_section]);

                return;

            case 'unit':
                $data = ['id_product' => $id, 'id_parameter' => 89];
                $this->deleteSQL('parameters_products', $data);
                if ($value != 'ks') {
                    $data['value_list'] = $this->findParameterValue(89, $value);
                    $this->insertSQL('parameters_products', $data);
                }

                return;

            case 'price':
                $value = $this->preparePriceFromAbra($value) / ((100 + getVat()) / 100);
                break;

            case 'price_common':
            case 'priceWithoutVat':
                $value = $this->preparePriceFromAbra($value);
                break;

            case 'figure':
                $value = ($this->parseBool($value) || $value > 0) ? 'Y' : 'O';
                break;

            case 'weight':
            case 'width':
            case 'height':
            case 'depth':
                $value = $this->preparePriceFromAbra($value) ?: null;
                break;

            case 'main_unit_code':
                $id_parameter_value = returnSQLResult('SELECT id FROM parameters_list WHERE id_parameter=89 AND value=:value', ['value' => $value]);

                if (!$value) {
                    $this->insertSQL('parameters_list', ['value' => $value, 'id_parameter' => 89]);
                    $id_parameter_value = sqlInsertId();
                }

                $this->deleteSQL('parameters_products', ['id_product' => $id, 'id_parameter' => 89]);

                $this->insertSQL('parameters_products', ['id_product' => $id, 'id_parameter' => 89, 'value_list' => $id_parameter_value]);

                return;

            case 'code':
                try {
                    $this->updateSQL('products', [$column => $value], ['id' => $id]);
                } catch (UniqueConstraintViolationException $e) {
                    echo '<br/>ERROR duplicate code: '.$value.'<br/>';
                }

                return;

            case 'vat':
                $value = $this->findVAT($value);
                break;

            case 'suppliers_code':
                // Parametr "Objednávkové číslo"
                $this->updateParameterValue($id, $value, 131);

                return;

            case 'delete':
                $this->updateSQL('products', ['figure' => 'O'], ['id' => $id]);

                return;

            case 'measure_unit':
                $unitId = $this->selectSQL('products_units', ['short_name_admin' => $value], ['id'])->fetchOne();
                $value = $unitId ?: null;
                break;

            case 'measure_quantity':
                $value = $this->preparePriceFromAbra($value);
                break;

            case 'ignore':
                return;
        }

        $this->updateSQL('products', [$column => $value], ['id' => $id]);
    }

    public function sync_section($code, $field, $value)
    {
        $id = $this->getMappingFromAbra('section', $code);

        if (!$id) {
            // Create Section
            $this->insertSQL('sections', ['name' => $value]);
            $id = sqlInsertId();

            $this->insertSQL('sections_relation', ['id_section' => $id, 'id_topsection' => null, 'position' => 999]);

            $this->setMappingFromAbra('section', $id, $code);
        }

        $column = static::$SECTION_FIELDS[$field];

        switch ($column) {
            case 'parent_id':
                $parent_id = $this->getMappingFromAbra('section', $value);
                if (!$parent_id) {
                    if ($value > 0) {
                        echo 'Unknown section ID: '.$value;
                    }
                    // throw new Exception('Unknown section ID: '.$value);

                    break;
                }

                sqlQuery('DELETE FROM sections_relation WHERE id_section=:id', ['id' => $id]);

                sqlQuery('INSERT INTO sections_relation (id_section, id_topsection)
                      VALUES (:id, :parent_id)', ['id' => $id, 'parent_id' => $parent_id, 'position' => 999]);

                MenuSectionTree::invalidateCache();
                break;

            case 'position':
                sqlQuery('UPDATE '.getTableName('sections_relation').'
                     SET position=:position
                     WHERE id_section=:id', ['id' => $id, 'position' => $value]);
                break;

            case 'description':
                $section = $this->selectSQL('sections', ['id' => $id])->fetch();
                if (isset($section['id_block'])) {
                    $block = sqlQueryBuilder()->select('id')->from('blocks')
                        ->where('id_root=:id_block AND id_parent=:id_block')
                        ->setParameter('id_block', $section['id_block'])
                        ->orderBy('position', 'ASC')->setMaxResults(1)
                        ->execute()->fetch();
                    $this->updateSQL('blocks', ['content' => $value], ['id' => $block['id']]);
                } else {
                    $this->insertSQL('blocks', []);
                    $rootBlockID = sqlInsertId();
                    $this->insertSQL('blocks', [
                        'id_root' => $rootBlockID,
                        'id_parent' => $rootBlockID,
                        'position' => 1,
                        'name' => $section['name'],
                        'content' => $value,
                    ]);
                    $this->updateSQL('sections', ['id_block' => $rootBlockID], ['id' => $id]);
                }
                break;

            case 'figure':
                $value = $this->parseBool($value);
                $value = $value ? 'Y' : 'N';
                $this->updateSQL('sections', [$column => $value], ['id' => $id]);
                break;

            case 'delete':
                $this->deleteSQL('sections', ['id' => $id]);

                return;

            case 'name_first':
                // Aktualizovat název sekce jen když je název roven Abra ID - aktualizovat jen poprvé, pak už ne
                $this->updateSQL('sections', ['name' => $value], ['id' => $id, 'name' => $code]);

                return;

            case 'ignore':
                break;

            default:
                $this->updateSQL('sections', [$column => $value], ['id' => $id]);
        }
    }

    public function sync_producer($code, $field, $value)
    {
        $id = $this->getMappingFromAbra('producer', $code);

        if (!$id) {
            // Create Product
            $vat_id = null;
            getVat($vat_id);
            $this->insertSQL('producers', ['name' => $value]);
            $id = sqlInsertId();

            $this->setMappingFromAbra('producer', $id, $code);
        }

        $column = static::$PRODUCER_FIELDS[$field];

        switch ($column) {
            case 'ignore':
                break;

            case 'position':
                sqlQuery('UPDATE producers
                     SET position=:position
                     WHERE id=:id', ['id' => $id, 'position' => $value]);
                break;

            default:
                $this->updateSQL('producers', [$column => $value], ['id' => $id]);
        }
    }

    public function sync_ignore($code, $field, $value)
    {
        // Ignore
    }

    public function syncOrder($id)
    {
        // sqlQuery('SELECT COUNT(*) FROM abra_orders FOR UPDATE'); // Protected by flock now

        $sync_order = $this->getOrder($id);
        $code = getVal('DocumentNumber', $sync_order);

        echo "<h2>synchronizuji objednavku {$code}</h2>";

        try {
            $ret = $this->client->__call('weSetReceivedOrder', $sync_order);
        } catch (Exception $e) {
            $this->printTrace();
            throw $e;
        }

        $this->printTrace();

        $this->logTrace("order {$id} - {$code}");

        if ($ret[0] == 'True') {
            $this->setMappingFromAbra('order', $id, $ret[2]);

            $order = new Order();
            $order->createFromDB($id);
            $order->logHistory(translate('abra_order_send', 'abra'));
        } else {
            $error = $ret[1];
            if ($error == 'Objednávka již v ABŘE existuje' || $error == 'ERROR:WEB009') {
                $this->setMappingFromAbra('order', $id, $ret[2]);
            } else {
                throw new RuntimeException("Synchronizace objednávky {$code} selhala: ".join(', ', $ret));
            }
        }
    }

    public function prepareTextForOrder($text)
    {
        return str_replace(["\r\n", "\n"], '', $text);
    }

    public function prepareFieldLengthForOrder($text, $size)
    {
        if (is_null($text)) {
            return $text;
        }

        $text = mb_substr($text, 0, $size);

        if ($text === false) {
            return '';
        }

        return $text;
    }

    public function getOrder($id)
    {
        $order = new Order();
        $order->createFromDB($id, true, true, true);

        return $this->contextManager->activateOrder($order, function () use ($order) {
            $delivery = $order->getDeliveryType($order->getDeliveryId());

            $sync_order = [
                // Header
                'roID_WPJ' => $order->id,
                'roDocumentNumber' => $order->order_no,
                'roWEBnote' => str_replace(["\r\n", "\n"], '', $order->note_user),
                'roCurrency' => findModule(Modules::CURRENCIES) ? $order->currency : 'CZK',
                'roPaymentCode' => $this->getMappingToAbra('payment', $delivery->id_payment),
                'roTransportCode' => $this->getMappingToAbra('delivery', $delivery->id_delivery),

                // Delivery address
                'adID_WPJ' => $order->id_user,
                'adNameCompany' => $order->delivery_firm,
                'adFirstName' => $order->delivery_name,
                'adLastName' => $order->delivery_surname,
                'adOrgIdentNumber' => null,
                'adVATIdentNumber' => null,
                'adStreet' => $order->delivery_street,
                'adCity' => $order->delivery_city,
                'adPostCode' => $order->delivery_zip,
                'adCountryCode' => $order->delivery_country,
                'adPhoneNumber1' => $order->delivery_phone,
                'adPhoneNumber2' => null,
                'adEmail' => null,

                // Invoicing address
                'afNameCompany' => $order->invoice_firm,
                'afFirstName' => $order->invoice_name,
                'afLastName' => $order->invoice_surname,
                'afOrgIdentNumber' => $order->invoice_ico,
                'afVATIdentNumber' => $order->invoice_dic,
                'afStreet' => $order->invoice_street,
                'afCity' => $order->invoice_city,
                'afPostCode' => $order->invoice_zip,
                'afCountryCode' => $order->invoice_country,
                'afPhoneNumber1' => $order->invoice_phone,
                'afPhoneNumber2' => null,
                'afEmail' => $order->invoice_email,
                'roRows' => $this->getOrderRows($order),
            ];

            return $sync_order;
        });
    }

    /**
     * @param $order Order
     *
     * @return array
     */
    public function getOrderRows($order)
    {
        $order->fetchItems();

        $rows = [];
        $rows_text = [];

        foreach ($order->items as $item) {
            if ($item['id_product']) {
                $code = $this->getMappingToAbra('product', $item['id_product']);
            } else {
                $code = '';
            }

            if ($code) {
                $type = 3;
                $name = '';
                $price = $this->preparePriceToAbra($item['piece_price']['value_without_vat_no_rounding']);
                $quantity = $item['pieces'];
            } else {
                $type = 1;
                $name = $item['descr'];
                $price = $this->preparePriceToAbra($item['total_price']['value_without_vat_no_rounding']);
                $quantity = 1;
            }

            // "3~|~002002~|~1~|~168,5~|~",
            $row = join($this->separator, [
                $type,
                $code,
                $quantity,
                $price,
                $name,
            ]);

            if ($code) {
                $rows[] = $row;
            } else {
                $rows_text[] = $row;
            }
        }

        return array_merge($rows, $rows_text);
    }

    public function finalizeSync()
    {
    }

    public function getClient(): SoapClient
    {
        return $this->client;
    }

    public function convertUnits($column, $unit, int $value): int
    {
        if ($column == 'weight') {
            switch ($unit) {
                case 'g':
                    $value /= 1000;
                    break;
                case 't':
                    $value *= 1000;
            }
        } else {
            switch ($unit) {
                case 'm':
                    $value *= 100;
                    break;
                case 'dm':
                    $value *= 10;
                    break;
                case 'mm':
                    $value /= 10;
                    break;
            }
        }

        return $value;
    }

    protected function createAbraDataObject($name, $value, $type)
    {
        $data = [
            'FieldValueAsString' => '',
            'FieldValueAsFloat' => '',
            'FieldValueAsDateTime' => '',
            'FieldValueAsBoolean' => '',
            'FieldValueAsInteger' => '',
            'FieldDataKind' => 'adkData',
            'BusinessObject' => '',
            'Collection' => '',
        ];

        return array_merge($data, [
            'FieldName' => $name,
            'FieldValueAs'.$type => $value,
            'FieldDataType' => 'dt'.$type,
        ]);
    }

    public function log($data)
    {
        // Do not log in development
        if (isLocalDevelopment()) {
            return;
        }

        /** @var Symfony\Bridge\Monolog\Logger $logger */
        $logger = ServiceContainer::getService('logger');
        $logger->notice('Abra Log: '.print_r($data, true));
    }

    public function logToActivityLog($message, $data = [])
    {
        addActivityLog(ActivityLog::SEVERITY_ERROR, ActivityLog::TYPE_SYNC, 'Abra: '.$message, $data);
    }

    public function logException(Exception $exception)
    {
        $sentry = getRaven();
        $sentry->captureException($exception);
    }

    public function getWebSource(Order $order)
    {
        return $this->settings['web'];
    }

    public function getOrderNumber(Order $order)
    {
        return $order->order_no;
    }

    public function getInvoice(Order $order)
    {
        $result = $this->client->weGetInvoice($this->getWebSource($order), $this->getOrderNumber($order));

        if ($result[0] === 'True') {
            return base64_decode($result[2]);
        } else {
            if (isDevelopment()) {
                $this->printTrace();
            }
            throw new NotFoundHttpException('Order does not have invoice: '.print_r($result, true));
        }
    }

    public function loopSychronization($timeout, $endOnEmptyQueue = false)
    {
        $startTime = getScriptTime();

        $flock_file = fopen('data/tmp/abra_sync.lock', 'wb');

        while (!flock($flock_file, LOCK_EX | LOCK_NB)) {
            if ((getScriptTime() - $startTime) > $timeout) {
                exit('Cannot get lock');
            }
            var_dump('Waiting for lock');
            flush();
            sleep(1);
        }

        // Sync only for 10 seconds to loop more often
        $this->syncTimeout = 10;

        do {
            try {
                var_dump('Loop....');
                flush();

                $tmp = (getScriptTime() - $startTime); // For Sentry

                $this->syncOrders();
                $moreMessages = $this->syncProducts();
                // Update scheduled products only if no more messages waiting
                // Try to deplete queue and send pending orders ASAP
                if ($moreMessages !== true) {
                    $this->updateScheduledProducts();

                    if ($endOnEmptyQueue) {
                        break;
                    }

                    if ((getScriptTime() - $startTime) < $timeout) {
                        sleep(max(1, min(30, $timeout - (getScriptTime() - $startTime) + 1)));
                    }
                }
            } catch (Exception $e) {
                exception_handler($e);
                sleep(max(1, min(60, $timeout - (getScriptTime() - $startTime) + 1)));
            }
        } while ((getScriptTime() - $startTime) < $timeout);

        $this->finalizeSync();

        flock($flock_file, LOCK_UN);
        fclose($flock_file);
    }

    public function updateParameterValue($productId, $value, $id_parameter, $column = 'value_char')
    {
        $data = ['id_product' => $productId, 'id_parameter' => $id_parameter];
        $this->deleteSQL('parameters_products', $data);
        if ($value) {
            $data[$column] = $value;
            $this->insertSQL('parameters_products', $data);
        }
    }

    public function process_message_abra($body)
    {
        $this->processChange($body->data);
    }
}

if (empty($subclass)) {
    class Abra extends AbraBase
    {
    }
}