<?php
namespace EasyChat\Model\Cms;

use EasyChat\Library\Catalog\CatalogSearchUnavailableException;

class CmsModelOpenCart extends CmsModelBase {

     
    private $language_id_cache = null;

     
    private $store_id_cache = null;

     
    private $stock_checkout_cache = null;

     
    private $currency_cache = null;

     
    private $customer_group_id_cache = null;

     
    private $special_price_table_cache = null;

     
    private $seo_schema_cache = null;

    







    private function getDefaultLanguageId(): int {
        if ($this->language_id_cache !== null) {
            return $this->language_id_cache;
        }
        $p = $this->prefix;
        try {
            $result = $this->db->query(
                "SELECT value FROM `{$p}setting`
                 WHERE `group` = 'config' AND `key` = 'config_language_id'
                 LIMIT 1"
            );
            if (!empty($result->row['value'])) {
                $this->language_id_cache = (int)$result->row['value'];
                return $this->language_id_cache;
            }
        } catch (\Throwable $e) {}
        try {
            $result = $this->db->query(
                "SELECT language_id FROM `{$p}language`
                 WHERE status = 1 ORDER BY sort_order ASC LIMIT 1"
            );
            if (!empty($result->row['language_id'])) {
                $this->language_id_cache = (int)$result->row['language_id'];
                return $this->language_id_cache;
            }
        } catch (\Throwable $e) {}
        $this->language_id_cache = 1;
        return $this->language_id_cache;
    }

    


    public function getProducts(): array {
        $p       = $this->prefix;
        $lang_id = $this->getDefaultLanguageId();
        $result  = $this->db->query(
            "SELECT p.product_id AS id, COALESCE(pd.name, '') AS name
             FROM `{$p}product` p
             LEFT JOIN `{$p}product_description` pd
                 ON pd.product_id = p.product_id AND pd.language_id = {$lang_id}
             WHERE p.status = 1
             ORDER BY p.product_id ASC"
        );
        return $result->rows ?? [];
    }

    public function getProductsBatch(int $after_id, int $limit): array {
        $p = $this->prefix;
        $after_id = max(0, $after_id);
        $limit = max(1, min(100, $limit));
        $lang_id = $this->getDefaultLanguageId();
        $store_id = $this->getStoreId();
         
         
        $result = $this->db->query(
            "SELECT p.product_id AS id, COALESCE(pd.name, '') AS name
             FROM `{$p}product` p
             INNER JOIN `{$p}product_to_store` p2s
               ON p2s.product_id = p.product_id AND p2s.store_id = {$store_id}
             LEFT JOIN `{$p}product_description` pd
               ON pd.product_id = p.product_id AND pd.language_id = {$lang_id}
             WHERE p.status = 1 AND p.product_id > {$after_id}
               AND (p.date_available IS NULL OR p.date_available = '0000-00-00' OR p.date_available <= NOW())
             ORDER BY p.product_id ASC LIMIT {$limit}"
        );
        return $result->rows ?? array();
    }

    


    public function getProductText(int $product_id): string {
        $parts = [];
        $p     = $this->prefix;

        $lang_id = $this->getDefaultLanguageId();
        $store_id = $this->getStoreId();
        $result  = $this->db->query(
            "SELECT pd.name, pd.description, p.model, p.sku
             FROM `{$p}product` p
             INNER JOIN `{$p}product_to_store` p2s
                 ON p2s.product_id = p.product_id AND p2s.store_id = {$store_id}
             LEFT JOIN `{$p}product_description` pd
                 ON pd.product_id = p.product_id AND pd.language_id = {$lang_id}
             WHERE p.product_id = $product_id AND p.status = 1
               AND (p.date_available IS NULL OR p.date_available = '0000-00-00' OR p.date_available <= NOW())
             LIMIT 1"
        );
        $product = $result->row ?? [];

        if (empty($product)) {
            return '';
        }

        if ($this->settings['chat_embedding_use_name'] ?? false) {
            if (!empty($product['name'])) {
                $parts[] = $product['name'];
            }
        }

        if ($this->settings['chat_embedding_use_desc'] ?? false) {
            if (!empty($product['description'])) {
                $parts[] = strip_tags($product['description']);
            }
        }

        if ($this->settings['chat_embedding_use_model'] ?? false) {
            if (!empty($product['model'])) {
                $parts[] = 'Модель: ' . $product['model'];
            }
        }

        if ($this->settings['chat_embedding_use_sku'] ?? false) {
            if (!empty($product['sku'])) {
                $parts[] = 'Артикул: ' . $product['sku'];
            }
        }

        if ($this->settings['chat_embedding_use_attrs'] ?? false) {
            $attrs = $this->getAttributes($product_id, $p, $lang_id);
            if (!empty($attrs)) {
                $parts[] = 'Характеристики: ' . implode(', ', $attrs);
            }
        }

        if ($this->settings['chat_embedding_use_options'] ?? false) {
            $options = $this->getOptions($product_id, $p, $lang_id);
            if (!empty($options)) {
                $parts[] = 'Опции: ' . implode('; ', $options);
            }
        }

        return implode('. ', array_filter(array_map('trim', $parts)));
    }

    


    public function getPages(): array {
        $p       = $this->prefix;
        $lang_id = $this->getDefaultLanguageId();
        $result  = $this->db->query(
            "SELECT i.information_id AS id, COALESCE(id_desc.title, '') AS name
             FROM `{$p}information` i
             LEFT JOIN `{$p}information_description` id_desc
                 ON id_desc.information_id = i.information_id AND id_desc.language_id = {$lang_id}
             WHERE i.status = 1
             ORDER BY i.information_id ASC"
        );
        return $result->rows ?? [];
    }

    public function getPagesBatch(int $after_id, int $limit): array {
        $p = $this->prefix;
        $after_id = max(0, $after_id);
        $limit = max(1, min(100, $limit));
        $lang_id = $this->getDefaultLanguageId();
        $result = $this->db->query(
            "SELECT i.information_id AS id, COALESCE(id_desc.title, '') AS name
             FROM `{$p}information` i
             LEFT JOIN `{$p}information_description` id_desc
               ON id_desc.information_id = i.information_id AND id_desc.language_id = {$lang_id}
             WHERE i.status = 1 AND i.information_id > {$after_id}
             ORDER BY i.information_id ASC LIMIT {$limit}"
        );
        return $result->rows ?? array();
    }

    


    public function getPageText(int $page_id): string {
        $p       = $this->prefix;
        $lang_id = $this->getDefaultLanguageId();
        $result  = $this->db->query(
            "SELECT id_desc.title AS name, id_desc.description
             FROM `{$p}information` i
             LEFT JOIN `{$p}information_description` id_desc
                 ON id_desc.information_id = i.information_id AND id_desc.language_id = {$lang_id}
             WHERE i.information_id = $page_id
             LIMIT 1"
        );
        $page = $result->row ?? [];

        $parts = [];
        if (!empty($page['name']))        $parts[] = $page['name'];
        if (!empty($page['description'])) $parts[] = strip_tags($page['description']);

        return implode('. ', array_filter(array_map('trim', $parts)));
    }

    



    public function searchProducts(string $query , float $max_price = 0.0, array $options = array()): array {
        $p       = $this->prefix;
        $words   = $this->splitWords($query);
        $limit   = max(1, min(100, (int)($options['candidate_limit'] ?? $this->getSearchLimit())));
        $lang_id = $this->getDefaultLanguageId();
        $store_id = $this->getStoreId();
        $likes     = [];
        $relevance = [];
        foreach ($words as $word) {
            $esc         = $this->escapeLikeWord($word);
            $likes[] = "(LOWER(pd.name) LIKE '%{$esc}%'
                        OR LOWER(p.model) LIKE '%{$esc}%'
                        OR LOWER(p.sku) LIKE '%{$esc}%')";
            $relevance[] = "(CASE
                WHEN LOWER(p.sku) = '{$esc}' OR LOWER(p.model) = '{$esc}' THEN 12
                WHEN LOWER(pd.name) LIKE '%{$esc}%' THEN 4
                WHEN LOWER(p.sku) LIKE '%{$esc}%' OR LOWER(p.model) LIKE '%{$esc}%' THEN 6
                ELSE 0 END)";
        }

        $rel_expr = implode(' + ', $relevance);
        $compact = $this->normalizeSearchIdentifier($query);
        $compact = mb_substr((string)$compact, 0, 120, 'UTF-8');
        $compact_esc = $this->db->escape($compact);
        $compact_model = $this->compactSearchIdentifierSql('p.model');
        $compact_sku = $this->compactSearchIdentifierSql('p.sku');
        $compact_name = $this->compactSearchIdentifierSql('pd.name');
        $identifier_cond = $compact_esc !== ''
            ? "({$compact_model} = '{$compact_esc}' OR {$compact_sku} = '{$compact_esc}' OR {$compact_name} LIKE '%{$compact_esc}%')"
            : '0';

        $customer_group_id = $this->getCustomerGroupId();
        $special_price_subquery = $this->specialPriceSubquery($customer_group_id);

        $make_sql = function (string $op) use ($p, $likes, $rel_expr, $lang_id, $limit, $max_price, $store_id, $identifier_cond, $special_price_subquery): string {
            $cond = implode(" {$op} ", $likes);
            $price_cond = !$max_price ? '1' : 'COALESCE(ps.price, p.price) <= ' . (float)$max_price;
            return "SELECT DISTINCT p.product_id, pd.name, p.model, p.sku,
                        p.quantity, p.subtract, ss.name AS stock_status,
                        COALESCE(ps.price, p.price) AS price,
                        ({$rel_expr}) AS relevance
                    FROM `{$p}product` p
                    INNER JOIN `{$p}product_to_store` p2s
                        ON p2s.product_id = p.product_id AND p2s.store_id = {$store_id}
                    LEFT JOIN `{$p}product_description` pd
                        ON pd.product_id = p.product_id AND pd.language_id = {$lang_id}
                    LEFT JOIN `{$p}stock_status` ss
                        ON ss.stock_status_id = p.stock_status_id AND ss.language_id = {$lang_id}
                    LEFT JOIN (
                        {$special_price_subquery}
                    ) ps ON ps.product_id = p.product_id
                    WHERE p.status = 1
                      AND (p.date_available IS NULL OR p.date_available = '0000-00-00' OR p.date_available <= NOW())
                      AND (({$cond}) OR {$identifier_cond})
                      AND {$price_cond}
                    ORDER BY relevance DESC, p.product_id DESC
                    LIMIT {$limit}";
        };

        $multi = count($words) > 1;
        $sql   = $make_sql($multi ? 'AND' : 'OR');
        $started_at = microtime(true);

        try {
            $result = $this->db->query($sql);

            if ($multi && empty($result->rows)) {
                $result = $this->db->query($make_sql('OR'));
            }

            $products = [];
            $base_url = $this->getBaseUrl();
            $stock_checkout = $this->isStockCheckoutAllowed();
            $currency = $this->getCurrencyCode();
            foreach ($result->rows as $row) {
                $quantity = (float)($row['quantity'] ?? 0);
                $stock = $this->openCartAvailability($row, $stock_checkout);
                $price_value = (float)($row['price'] ?? 0);
                $products[] = [
                    'id' => (string)$row['product_id'],
                    'name' => (string)$row['name'],
                    'model' => (string)($row['model'] ?? ''),
                    'sku' => (string)($row['sku'] ?? ''),
                    'price' => $this->formatCatalogPrice($price_value, $currency),
                    'price_value' => $price_value,
                    'currency' => $currency,
                    'link' => $base_url . '/index.php?route=product/product&product_id=' . $row['product_id'],
                    'availability' => $stock['availability'],
                    'availability_label' => $stock['label'],
                    'can_order' => $stock['can_order'],
                    'quantity' => $quantity,
                    'relevance' => (int)($row['relevance'] ?? 0),
                ];
            }
            $duration_ms = (int)round((microtime(true) - $started_at) * 1000);
            $this->log('[CmsSearch] OpenCart products mode=' . ($multi ? 'and' : 'or') . ' results=' . count($products) . ' duration_ms=' . $duration_ms);
            return $products;
        } catch (\Throwable $e) {
            $this->log('[CmsSearch] OpenCart products failed code=query_error');
            throw new CatalogSearchUnavailableException('opencart', $e);
        }
    }

    



    public function searchPages(string $query): array {
        $p       = $this->prefix;
        $words   = $this->splitWords($query);
        $limit   = $this->getSearchLimit();
        $lang_id = $this->getDefaultLanguageId();

        $likes     = [];
        $relevance = [];
        foreach ($words as $word) {
            $esc         = $this->escapeLikeWord($word);
            $likes[]     = "(LOWER(id_desc.title) LIKE '%{$esc}%' OR LOWER(id_desc.description) LIKE '%{$esc}%')";
            $relevance[] = "(CASE WHEN LOWER(id_desc.title) LIKE '%{$esc}%' THEN 2 WHEN LOWER(id_desc.description) LIKE '%{$esc}%' THEN 1 ELSE 0 END)";
        }

        $rel_expr = implode(' + ', $relevance);
        $make_sql = function (string $op) use ($p, $likes, $rel_expr, $lang_id, $limit): string {
            $cond = implode(" {$op} ", $likes);
            return "SELECT DISTINCT i.information_id, id_desc.title AS name, id_desc.description,
                        ({$rel_expr}) AS relevance
                    FROM `{$p}information` i
                    LEFT JOIN `{$p}information_description` id_desc
                        ON id_desc.information_id = i.information_id AND id_desc.language_id = {$lang_id}
                    WHERE i.status = 1
                      AND ({$cond})
                    ORDER BY relevance DESC, i.information_id DESC
                    LIMIT {$limit}";
        };

        $multi = count($words) > 1;
        $sql   = $make_sql($multi ? 'AND' : 'OR');
        $used_mode = $multi ? 'and' : 'or';

        try {
            $result = $this->db->query($sql);

            if ($multi && empty($result->rows)) {
                $sql = $make_sql('OR');
                $used_mode = 'or_fallback';
                $result = $this->db->query($sql);
            }

            $pages    = [];
            $base_url = $this->getBaseUrl();
            foreach ($result->rows as $row) {
                $excerpt = mb_substr(trim(strip_tags((string)$row['description'])), 0, 200, 'UTF-8');
                $pages[] = [
                    'id'      => (string)$row['information_id'],
                    'name'    => $row['name'],
                    'excerpt' => $excerpt,
                    'link'    => $base_url . '/index.php?route=information/information&information_id=' . $row['information_id'],
                ];
            }
            $this->log('[CmsSearch] OpenCart pages mode=' . $used_mode . ' results=' . count($pages));
            return $pages;
        } catch (\Throwable $e) {
            $this->logFailure('[CmsSearch] OpenCart pages failed', $e);
            return [];
        }
    }

    



    public function resolvePublicUrls(array $items, string $resource_type): array {
        $items = parent::resolvePublicUrls($items, $resource_type);
        if (!$items || strtolower($this->getConfigValue('config_seo_url', '0')) !== '1') {
            return $items;
        }

        $resource_type = strtolower(trim($resource_type));
        $query_key = $this->openCartSeoQueryKey($resource_type);
        if ($query_key === '') {
            return $items;
        }

        $ids = array();
        foreach ($items as $item) {
            $id = (int)($item['id'] ?? $item[$query_key] ?? 0);
            if ($id > 0) {
                $ids[$id] = $id;
            }
        }
        if (!$ids) {
            return $items;
        }

        $keywords = $this->loadOpenCartSeoKeywords($query_key, array_values($ids));
        if (!$keywords) {
            return $items;
        }

        $base_url = $this->getBaseUrl();
        foreach ($items as &$item) {
            $id = (int)($item['id'] ?? $item[$query_key] ?? 0);
            if (!isset($keywords[$id])) {
                continue;
            }
            $seo_url = $this->joinPublicUrl($base_url, $keywords[$id]);
            if ($seo_url !== '') {
                $item['link'] = $seo_url;
                if (isset($item['url'])) {
                    $item['url'] = $seo_url;
                }
            }
        }
        unset($item);
        return $items;
    }

    



    public function resolvePageContext(string $page_type, string $page_id): array {
        $page_type = strtolower(preg_replace('/[^a-z0-9_-]/i', '', trim($page_type)));
        $id = (int)$page_id;
        if ($id <= 0) {
            return [];
        }

        if ($page_type === 'product') {
            return $this->resolveProductPageContext($id);
        }
        if ($page_type === 'category') {
            return $this->resolveCategoryPageContext($id);
        }
        if ($page_type === 'information') {
            return $this->resolveInformationPageContext($id);
        }

        return [];
    }

     
     
     

    private function resolveProductPageContext(int $product_id): array {
        $p = $this->prefix;
        $lang_id = $this->getDefaultLanguageId();
        try {
            $result = $this->db->query(
                "SELECT p.product_id, p.model, p.sku, p.price, p.quantity, p.subtract, p.status,
                        ps.price AS special_price,
                        pd.name, pd.description,
                        m.name AS manufacturer,
                        ss.name AS stock_status
                 FROM `{$p}product` p
                 LEFT JOIN `{$p}product_description` pd
                    ON pd.product_id = p.product_id AND pd.language_id = {$lang_id}
                 LEFT JOIN `{$p}manufacturer` m
                    ON m.manufacturer_id = p.manufacturer_id
                 LEFT JOIN `{$p}stock_status` ss
                    ON ss.stock_status_id = p.stock_status_id AND ss.language_id = {$lang_id}
                 LEFT JOIN `{$p}product_special` ps
                    ON ps.product_id = p.product_id
                   AND ps.customer_group_id = " . $this->getCustomerGroupId() . "
                   AND (ps.date_start = '0000-00-00' OR ps.date_start <= CURDATE())
                   AND (ps.date_end = '0000-00-00' OR ps.date_end >= CURDATE())
                 WHERE p.product_id = {$product_id}
                 ORDER BY ps.priority ASC, ps.price ASC
                 LIMIT 1"
            );
        } catch (\Throwable $e) {
            $this->logFailure('[CmsContext] OpenCart product failed', $e);
            return [];
        }

        $row = $result->row ?? [];
        if (!$row) {
            return [];
        }

        $stock = $this->openCartAvailability($row, $this->isStockCheckoutAllowed());
        $show_quantity = (string)($this->settings['chat_cms_show_exact_quantity'] ?? '0') === '1';
        $lines = ['Тип страницы: товар'];
        $this->addContextLine($lines, 'Название', (string)($row['name'] ?? ''));
        $this->addContextLine($lines, 'ID товара', (string)$product_id);
        $this->addContextLine($lines, 'Модель', (string)($row['model'] ?? ''));
        $this->addContextLine($lines, 'Артикул', (string)($row['sku'] ?? ''));
        $this->addContextLine($lines, 'Производитель', (string)($row['manufacturer'] ?? ''));
        $this->addContextLine($lines, 'Цена', $this->formatContextPrice($row['price'] ?? ''));
        $this->addContextLine($lines, 'Спеццена', $this->formatContextPrice($row['special_price'] ?? ''));
        $this->addContextLine($lines, 'Наличие', $stock['label']);
        if ($show_quantity) {
            $this->addContextLine($lines, 'Количество на складе', isset($row['quantity']) ? (string)(int)$row['quantity'] : '');
        }
        $this->addContextLine($lines, 'Описание', $this->contextText((string)($row['description'] ?? ''), 520));

        $attrs = $this->getAttributes($product_id, $p, $lang_id);
        if ($attrs) {
            $this->addContextLine($lines, 'Характеристики', $this->contextText(implode('; ', array_slice($attrs, 0, 12)), 700));
        }
        $options = $this->getOptions($product_id, $p, $lang_id);
        if ($options) {
            $this->addContextLine($lines, 'Опции', $this->contextText(implode('; ', array_slice($options, 0, 8)), 520));
        }

        $facts = [
            'type' => 'product',
            'product_id' => (string)$product_id,
            'name' => (string)($row['name'] ?? ''),
            'model' => (string)($row['model'] ?? ''),
            'sku' => (string)($row['sku'] ?? ''),
            'manufacturer' => (string)($row['manufacturer'] ?? ''),
            'price' => $this->formatContextPrice($row['price'] ?? ''),
            'special_price' => $this->formatContextPrice($row['special_price'] ?? ''),
            'availability' => $stock['availability'],
            'availability_label' => $stock['label'],
            'can_order' => $stock['can_order'],
            'description_excerpt' => $this->contextText((string)($row['description'] ?? ''), 520),
            'attributes' => array_slice($attrs, 0, 12),
            'options' => array_slice($options, 0, 8),
        ];
        if ($show_quantity) {
            $facts['quantity'] = isset($row['quantity']) ? (string)(int)$row['quantity'] : '';
        }

        return [
            'kind' => 'product',
            'page_type' => 'product',
            'page_id' => (string)$product_id,
            'title' => (string)($row['name'] ?? ''),
            'url' => $this->resolveSinglePublicUrl('product', $product_id, $this->getBaseUrl() . '/index.php?route=product/product&product_id=' . $product_id),
            'facts' => $facts,
            'prompt_lines' => $lines,
        ];
    }

    private function resolveCategoryPageContext(int $category_id): array {
        $p = $this->prefix;
        $lang_id = $this->getDefaultLanguageId();
        try {
            $result = $this->db->query(
                "SELECT c.category_id, c.status, cd.name, cd.description
                 FROM `{$p}category` c
                 LEFT JOIN `{$p}category_description` cd
                    ON cd.category_id = c.category_id AND cd.language_id = {$lang_id}
                 WHERE c.category_id = {$category_id}
                 LIMIT 1"
            );
        } catch (\Throwable $e) {
            $this->logFailure('[CmsContext] OpenCart category failed', $e);
            return [];
        }

        $row = $result->row ?? [];
        if (!$row) {
            return [];
        }

        $lines = ['Тип страницы: категория'];
        $this->addContextLine($lines, 'Название', (string)($row['name'] ?? ''));
        $this->addContextLine($lines, 'ID категории', (string)$category_id);
        $this->addContextLine($lines, 'Описание', $this->contextText((string)($row['description'] ?? ''), 620));

        return [
            'kind' => 'category',
            'page_type' => 'category',
            'page_id' => (string)$category_id,
            'title' => (string)($row['name'] ?? ''),
            'url' => $this->resolveSinglePublicUrl('category', $category_id, $this->getBaseUrl() . '/index.php?route=product/category&path=' . $category_id),
            'facts' => [
                'type' => 'category',
                'category_id' => (string)$category_id,
                'name' => (string)($row['name'] ?? ''),
                'description_excerpt' => $this->contextText((string)($row['description'] ?? ''), 620),
            ],
            'prompt_lines' => $lines,
        ];
    }

    private function resolveInformationPageContext(int $information_id): array {
        $p = $this->prefix;
        $lang_id = $this->getDefaultLanguageId();
        try {
            $result = $this->db->query(
                "SELECT i.information_id, i.status, id_desc.title, id_desc.description
                 FROM `{$p}information` i
                 LEFT JOIN `{$p}information_description` id_desc
                    ON id_desc.information_id = i.information_id AND id_desc.language_id = {$lang_id}
                 WHERE i.information_id = {$information_id}
                 LIMIT 1"
            );
        } catch (\Throwable $e) {
            $this->logFailure('[CmsContext] OpenCart information failed', $e);
            return [];
        }

        $row = $result->row ?? [];
        if (!$row) {
            return [];
        }

        $lines = ['Тип страницы: информационная страница'];
        $this->addContextLine($lines, 'Название', (string)($row['title'] ?? ''));
        $this->addContextLine($lines, 'ID страницы', (string)$information_id);
        $this->addContextLine($lines, 'Содержание', $this->contextText((string)($row['description'] ?? ''), 700));

        return [
            'kind' => 'information',
            'page_type' => 'information',
            'page_id' => (string)$information_id,
            'title' => (string)($row['title'] ?? ''),
            'url' => $this->resolveSinglePublicUrl('information', $information_id, $this->getBaseUrl() . '/index.php?route=information/information&information_id=' . $information_id),
            'facts' => [
                'type' => 'information',
                'information_id' => (string)$information_id,
                'title' => (string)($row['title'] ?? ''),
                'content_excerpt' => $this->contextText((string)($row['description'] ?? ''), 700),
            ],
            'prompt_lines' => $lines,
        ];
    }

    private function addContextLine(array &$lines, string $label, string $value): void {
        $value = $this->contextText($value, 900);
        if ($value !== '') {
            $lines[] = $label . ': ' . $value;
        }
    }

    private function contextText(string $value, int $limit): string {
        $value = html_entity_decode(strip_tags($value), ENT_QUOTES | ENT_HTML5, 'UTF-8');
        $value = preg_replace('/\s+/u', ' ', trim($value));
        return mb_strlen($value, 'UTF-8') > $limit ? mb_substr($value, 0, $limit, 'UTF-8') : $value;
    }

    private function formatContextPrice($value): string {
        if ($value === null || $value === '') {
            return '';
        }
        $price = (float)$value;
        return $price > 0 ? number_format($price, 2, '.', '') : '';
    }

    private function getStoreId(): int {
        if ($this->store_id_cache !== null) {
            return $this->store_id_cache;
        }

        $this->store_id_cache = 0;
        $host = strtolower((string)(\EasyChat\Library\RequestContext::server()['HTTP_HOST'] ?? ''));
        $host = preg_replace('/:\d+$/', '', $host);
        if ($host === '') {
            return $this->store_id_cache;
        }

        try {
             
             
            $result = $this->db->query("SELECT `store_id`, `url` FROM `{$this->prefix}store`");
            foreach (($result->rows ?? array()) as $row) {
                foreach (array($row['url'] ?? '') as $url) {
                    $url_host = strtolower((string)parse_url((string)$url, PHP_URL_HOST));
                    if ($url_host !== '' && $url_host === $host) {
                        $this->store_id_cache = (int)$row['store_id'];
                        return $this->store_id_cache;
                    }
                }
            }
        } catch (\Throwable $e) {
             
        }
        return $this->store_id_cache;
    }

    private function isStockCheckoutAllowed(): bool {
        if ($this->stock_checkout_cache !== null) {
            return $this->stock_checkout_cache;
        }
        $value = strtolower($this->getConfigValue('config_stock_checkout', '0'));
        $this->stock_checkout_cache = in_array($value, array('1', 'true', 'yes', 'on'), true);
        return $this->stock_checkout_cache;
    }

    private function getCustomerGroupId(): int {
        if ($this->customer_group_id_cache !== null) {
            return $this->customer_group_id_cache;
        }
        $this->customer_group_id_cache = max(1, (int)$this->getConfigValue('config_customer_group_id', '1'));
        return $this->customer_group_id_cache;
    }

    private function openCartAvailability(array $row, bool $stock_checkout): array {
        $quantity = (float)($row['quantity'] ?? 0);
        $subtract = (int)($row['subtract'] ?? 1) === 1;
        if ($quantity > 0 || !$subtract) {
            return array('availability' => 'in_stock', 'label' => 'В наличии', 'can_order' => true);
        }
        if ($stock_checkout) {
            $label = trim((string)($row['stock_status'] ?? '')) ?: 'Доступно под заказ';
            return array('availability' => 'preorder', 'label' => $label, 'can_order' => true);
        }
        $label = trim((string)($row['stock_status'] ?? '')) ?: 'Нет в наличии';
        return array('availability' => 'out_of_stock', 'label' => $label, 'can_order' => false);
    }

    private function getCurrencyCode(): string {
        if ($this->currency_cache !== null) {
            return $this->currency_cache;
        }
        $currency = strtoupper(trim($this->getConfigValue('config_currency', '')));
        $this->currency_cache = preg_match('/^[A-Z]{3}$/', $currency) ? $currency : '';
        return $this->currency_cache;
    }

    



    private function specialPriceSubquery(int $customer_group_id): string {
        if ($this->special_price_table_cache === null) {
            $this->special_price_table_cache = '';
            $candidates = array(
                $this->prefix . 'product_special',
                $this->prefix . 'product_discount',
            );
            try {
                $quoted = array_map(function (string $table): string {
                    return "'" . $this->db->escape($table) . "'";
                }, $candidates);
                $result = $this->db->query(
                    'SELECT table_name FROM information_schema.tables'
                    . ' WHERE table_schema = DATABASE() AND table_name IN (' . implode(',', $quoted) . ')'
                );
                $found = array();
                foreach (($result->rows ?? array()) as $row) {
                    $found[(string)($row['table_name'] ?? $row['TABLE_NAME'] ?? '')] = true;
                }
                foreach ($candidates as $candidate) {
                    if (!empty($found[$candidate])) {
                        $this->special_price_table_cache = $candidate;
                        break;
                    }
                }
            } catch (\Throwable $e) {
                 
                 
                foreach ($candidates as $candidate) {
                    try {
                        $this->db->query("SELECT 1 FROM `{$candidate}` LIMIT 0");
                        $this->special_price_table_cache = $candidate;
                        break;
                    } catch (\Throwable $probe_error) {
                    }
                }
            }
        }

        if ($this->special_price_table_cache === '') {
            return 'SELECT NULL AS product_id, NULL AS price WHERE 1 = 0';
        }

        $table = $this->special_price_table_cache;
        $special_filter = substr($table, -strlen('product_discount')) === 'product_discount'
            ? 'AND special = 1'
            : '';
        return "SELECT product_id, MIN(price) AS price
                FROM `{$table}`
                WHERE customer_group_id = {$customer_group_id}
                  {$special_filter}
                  AND (date_start IS NULL OR date_start = '0000-00-00' OR date_start <= NOW())
                  AND (date_end IS NULL OR date_end = '0000-00-00' OR date_end >= NOW())
                GROUP BY product_id";
    }

    private function getConfigValue(string $key, string $default = ''): string {
        $key = $this->db->escape($key);
        $store_id = $this->getStoreId();
        foreach (array('`code`', '`group`') as $group_column) {
            try {
                $result = $this->db->query(
                    "SELECT value FROM `{$this->prefix}setting`
                     WHERE {$group_column} = 'config' AND `key` = '{$key}'
                       AND store_id IN (0, {$store_id})
                     ORDER BY store_id DESC LIMIT 1"
                );
                if (isset($result->row['value'])) {
                    return (string)$result->row['value'];
                }
            } catch (\Throwable $e) {
                 
            }
        }
        return $default;
    }

    private function formatCatalogPrice(float $price, string $currency): string {
        if ($price <= 0) {
            return 'Цена по запросу';
        }
        $formatted = number_format($price, 2, '.', '');
        return $currency !== '' ? $formatted . ' ' . $currency : $formatted;
    }

    private function openCartSeoQueryKey(string $resource_type): string {
        if ($resource_type === 'product') {
            return 'product_id';
        }
        if ($resource_type === 'category') {
            return 'category_id';
        }
        if ($resource_type === 'page' || $resource_type === 'information') {
            return 'information_id';
        }
        return '';
    }

     
    private function loadOpenCartSeoKeywords(string $query_key, array $ids): array {
        $ids = array_values(array_unique(array_filter(array_map('intval', $ids))));
        if (!$ids) {
            return array();
        }
        $id_list = implode(',', $ids);
        $store_id = $this->getStoreId();
        $language_id = $this->getDefaultLanguageId();
        $p = $this->prefix;
        $schemas = $this->seo_schema_cache !== null
            ? array($this->seo_schema_cache)
            : array('oc4', 'oc3', 'oc2');

        foreach ($schemas as $schema) {
            try {
                if ($schema === 'oc4') {
                    $result = $this->db->query(
                        "SELECT CAST(`value` AS UNSIGNED) AS resource_id, `keyword`
                         FROM `{$p}seo_url`
                         WHERE `key` = '" . $this->db->escape($query_key) . "'
                           AND CAST(`value` AS UNSIGNED) IN ({$id_list})
                           AND `store_id` = {$store_id} AND `language_id` = {$language_id}
                         ORDER BY `sort_order` DESC"
                    );
                } elseif ($schema === 'oc3') {
                    $queries = array_map(function ($id) use ($query_key) {
                        return "'" . $this->db->escape($query_key . '=' . (int)$id) . "'";
                    }, $ids);
                    $result = $this->db->query(
                        "SELECT CAST(SUBSTRING_INDEX(`query`, '=', -1) AS UNSIGNED) AS resource_id, `keyword`
                         FROM `{$p}seo_url`
                         WHERE `query` IN (" . implode(',', $queries) . ")
                           AND `store_id` = {$store_id} AND `language_id` = {$language_id}"
                    );
                } else {
                    $queries = array_map(function ($id) use ($query_key) {
                        return "'" . $this->db->escape($query_key . '=' . (int)$id) . "'";
                    }, $ids);
                    $result = $this->db->query(
                        "SELECT CAST(SUBSTRING_INDEX(`query`, '=', -1) AS UNSIGNED) AS resource_id, `keyword`
                         FROM `{$p}url_alias` WHERE `query` IN (" . implode(',', $queries) . ")"
                    );
                }

                $this->seo_schema_cache = $schema;
                $keywords = array();
                foreach (($result->rows ?? array()) as $row) {
                    $id = (int)($row['resource_id'] ?? 0);
                    $keyword = trim((string)($row['keyword'] ?? ''), '/');
                    if ($id > 0 && $keyword !== '' && !isset($keywords[$id])) {
                        $keywords[$id] = $keyword;
                    }
                }
                return $keywords;
            } catch (\Throwable $e) {
                if ($this->seo_schema_cache !== null) {
                    return array();
                }
            }
        }
        $this->seo_schema_cache = 'none';
        return array();
    }

    private function joinPublicUrl(string $base_url, string $keyword): string {
        $keyword = trim($keyword);
        if ($base_url === '' || $keyword === '' || strpos($keyword, '\\') !== false
            || preg_match('/[\x00-\x20\x7F]/u', $keyword)) {
            return '';
        }
        $url = rtrim($base_url, '/') . '/' . ltrim($keyword, '/');
        return \EasyChat\Library\Catalog\CatalogUrlPolicy::isSafeLink($url) ? $url : '';
    }

    private function getAttributes(int $product_id, string $p, int $lang_id): array {
        $sql = "SELECT ad.name AS attr_name, pa.text AS attr_value
                FROM `{$p}product_attribute` pa
                JOIN `{$p}attribute_description` ad
                    ON ad.attribute_id = pa.attribute_id AND ad.language_id = {$lang_id}
                WHERE pa.product_id = $product_id AND pa.language_id = {$lang_id}
                ORDER BY ad.name ASC";
        try {
            $result = $this->db->query($sql);
            $attrs  = [];
            foreach ($result->rows as $row) {
                if (!empty($row['attr_value'])) {
                    $attrs[] = $row['attr_name'] . ': ' . $row['attr_value'];
                }
            }
            return $attrs;
        } catch (\Throwable $e) {
            return [];
        }
    }

    private function getOptions(int $product_id, string $p, int $lang_id): array {
        $sql = "SELECT od.name AS option_name,
                       GROUP_CONCAT(ovd.name ORDER BY ovd.name SEPARATOR ', ') AS option_values
                FROM `{$p}product_option` po
                JOIN `{$p}option_description` od
                    ON od.option_id = po.option_id AND od.language_id = {$lang_id}
                LEFT JOIN `{$p}product_option_value` pov
                    ON pov.product_id = po.product_id AND pov.option_id = po.option_id
                LEFT JOIN `{$p}option_value_description` ovd
                    ON ovd.option_value_id = pov.option_value_id AND ovd.language_id = {$lang_id}
                WHERE po.product_id = $product_id
                GROUP BY po.option_id
                ORDER BY od.name ASC";
        try {
            $result  = $this->db->query($sql);
            $options = [];
            foreach ($result->rows as $row) {
                if (!empty($row['option_values'])) {
                    $options[] = $row['option_name'] . ': ' . $row['option_values'];
                }
            }
            return $options;
        } catch (\Throwable $e) {
            return [];
        }
    }
}
