<?php
/**
 * Entity Search Engine
 *
 * Handles AJAX search functionality for all entity types.
 * Provides search, count, and getByIds methods for each entity.
 *
 * @package MyPrestaRocks\EntitySelector
 */

namespace MyPrestaRocks\EntitySelector\EntitySelector;

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

use Db;
use DbQuery;
use Context;
use Configuration;
use Tools;
use ImageType;
use Shop;

class EntitySearchEngine
{
    /**
     * @var int Language ID
     */
    protected $idLang;

    /**
     * @var int Shop ID
     */
    protected $idShop;

    /**
     * Constructor
     *
     * @param int $idLang Language ID
     * @param int $idShop Shop ID
     */
    public function __construct($idLang = null, $idShop = null)
    {
        $this->idLang = $idLang ?: (int) Context::getContext()->language->id;
        $this->idShop = $idShop ?: (int) Context::getContext()->shop->id;
    }

    /**
     * Search for entities by type
     *
     * @param string $entityType Entity type key
     * @param string $query Search query
     * @param int $limit Results limit
     * @param int $offset Results offset
     * @param array $filters Additional filters
     * @return array Search results
     */
    public function search($entityType, $query, $limit = 20, $offset = 0, array $filters = [])
    {
        // product_selection_level: route product searches to combination methods
        $psl = $filters['product_selection_level'] ?? 'product';
        if ($entityType === 'products' && ($psl === 'combination' || $psl === 'both')) {
            return $this->searchProductCombinations($query, $this->idLang, $this->idShop, $limit, $offset, $filters);
        }

        $method = 'searchTarget' . $this->camelCase($entityType);

        if (method_exists($this, $method)) {
            return $this->$method($query, $this->idLang, $this->idShop, $limit, $offset, $filters);
        }

        return [];
    }

    /**
     * Count entities by type
     *
     * @param string $entityType Entity type key
     * @param string $query Search query
     * @param array $filters Additional filters
     * @return int Total count
     */
    public function count($entityType, $query, array $filters = [])
    {
        $psl = $filters['product_selection_level'] ?? 'product';
        if ($entityType === 'products' && ($psl === 'combination' || $psl === 'both')) {
            return $this->countProductCombinations($query, $this->idLang, $this->idShop, $filters);
        }

        $method = 'countTarget' . $this->camelCase($entityType);

        if (method_exists($this, $method)) {
            return $this->$method($query, $this->idLang, $this->idShop, $filters);
        }

        return 0;
    }

    /**
     * Get entities by IDs
     *
     * @param string $entityType Entity type key
     * @param array $ids Entity IDs
     * @return array Entities data
     */
    public function getByIds($entityType, array $ids, $productSelectionLevel = 'product')
    {
        // If product type with combination-level IDs (prefixed strings), use combination handler
        if ($entityType === 'products' && ($productSelectionLevel === 'combination' || $productSelectionLevel === 'both')) {
            return $this->getProductCombinationsByIds($ids, $this->idLang, $this->idShop);
        }

        $method = 'getTarget' . $this->camelCase($entityType) . 'ByIds';

        if (method_exists($this, $method)) {
            return $this->$method($ids, $this->idLang, $this->idShop);
        }

        return [];
    }

    /**
     * Convert snake_case to CamelCase
     *
     * @param string $string
     * @return string
     */
    protected function camelCase($string)
    {
        return str_replace(' ', '', ucwords(str_replace(['_', '-'], ' ', $string)));
    }

    /**
     * Escape pattern for LIKE queries
     *
     * @param string $pattern
     * @return string
     */
    protected function escapePattern($pattern)
    {
        return str_replace(['%', '_'], ['\\%', '\\_'], pSQL($pattern));
    }

    /**
     * Build ORDER BY clause based on entity type and sort field
     *
     * @param string $entityType Entity type (products, categories, etc.)
     * @param array $filters Filters containing sort_by and sort_dir
     * @param array $columnMap Mapping of sort field names to SQL columns
     * @return string ORDER BY clause
     */
    protected function buildOrderBy($entityType, array $filters, array $columnMap)
    {
        $sortBy = $filters['sort_by'] ?? 'name';
        $sortDir = strtoupper($filters['sort_dir'] ?? 'ASC');

        if (!in_array($sortDir, ['ASC', 'DESC'])) {
            $sortDir = 'ASC';
        }

        // Get the column for sorting
        $column = $columnMap[$sortBy] ?? $columnMap['name'] ?? 'name';

        return $column . ' ' . $sortDir;
    }

    // =========================================================================
    // PRODUCTS
    // =========================================================================

    /**
     * Search products
     *
     * @param string $query Search query
     * @param int $idLang Language ID
     * @param int $idShop Shop ID
     * @param int $limit Results limit
     * @param int $offset Results offset
     * @param array $filters Additional filters
     * @return array Product results
     */
    public function searchTargetProducts($query, $idLang, $idShop, $limit = 20, $offset = 0, array $filters = [])
    {
        $sql = new DbQuery();
        $sql->select('DISTINCT p.id_product, pl.name, p.reference, ps.price AS base_price, p.active');
        $sql->select('sa.quantity AS stock_qty, m.name AS manufacturer_name');
        $sql->select('cl.name AS category_name, p.id_category_default');
        $sql->select('i.id_image');
        // Check for discount
        $sql->select('(ps.on_sale = 1 OR EXISTS (
            SELECT 1 FROM ' . _DB_PREFIX_ . 'specific_price sp
            WHERE sp.id_product = p.id_product
            AND sp.reduction > 0
            AND (sp.from = "0000-00-00 00:00:00" OR sp.from <= NOW())
            AND (sp.to = "0000-00-00 00:00:00" OR sp.to >= NOW())
        )) AS has_discount');
        // Sales quantity
        $sql->select('(SELECT COALESCE(SUM(od.product_quantity), 0) FROM ' . _DB_PREFIX_ . 'order_detail od
            INNER JOIN ' . _DB_PREFIX_ . 'orders o ON o.id_order = od.id_order
            WHERE od.product_id = p.id_product AND o.valid = 1) AS sales_qty');
        $sql->from('product', 'p');
        $sql->innerJoin('product_shop', 'ps', 'ps.id_product = p.id_product AND ps.id_shop = ' . (int) $idShop);
        $sql->leftJoin('product_lang', 'pl', 'pl.id_product = p.id_product AND pl.id_lang = ' . (int) $idLang . ' AND pl.id_shop = ' . (int) $idShop);
        $sql->leftJoin('manufacturer', 'm', 'm.id_manufacturer = p.id_manufacturer');
        $sql->leftJoin('category_lang', 'cl', 'cl.id_category = p.id_category_default AND cl.id_lang = ' . (int) $idLang . ' AND cl.id_shop = ' . (int) $idShop);
        $sql->leftJoin('stock_available', 'sa', 'sa.id_product = p.id_product AND sa.id_product_attribute = 0 AND sa.id_shop = ' . (int) $idShop);
        $sql->leftJoin('image', 'i', 'i.id_product = p.id_product AND i.cover = 1');

        // Search query
        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(pl.name LIKE \'%' . $escapedQuery . '%\' OR p.reference LIKE \'%' . $escapedQuery . '%\' OR p.id_product = ' . (int) $query . ')');
        }

        // Apply filters
        $this->applyProductFilters($sql, $filters, $idLang, $idShop);

        // Refine query
        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('pl.name NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('pl.name LIKE \'%' . $refine . '%\'');
            }
        }

        // Sorting
        // Note: Products don't have a global position - only per-category positions in ps_category_product
        $productSortMap = [
            'name' => 'pl.name',
            'id' => 'p.id_product',
            'price' => 'ps.price',
            'reference' => 'p.reference',
            'popularity' => 'sales_qty',
            'stock' => 'stock_qty',
        ];
        $sql->orderBy($this->buildOrderBy('products', $filters, $productSortMap));
        $sql->limit((int) $limit, (int) $offset);

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        $imageType = $this->getProductImageType();
        $context = Context::getContext();
        $locale = $context->getCurrentLocale();
        $currency = $context->currency;

        return array_map(function ($row) use ($imageType, $idLang, $idShop, $locale, $currency) {
            $basePrice = (float) ($row['base_price'] ?? 0);
            $stockQty = (int) ($row['stock_qty'] ?? 0);
            $salesQty = (int) ($row['sales_qty'] ?? 0);
            $hasDiscount = (bool) ($row['has_discount'] ?? false);

            // Format prices
            $regularPriceFormatted = $locale->formatPrice($basePrice, $currency->iso_code);

            // If has discount, get the actual discounted price
            $finalPriceFormatted = $regularPriceFormatted;
            if ($hasDiscount) {
                $product = new \Product((int) $row['id_product'], false, $idLang, $idShop);
                $finalPrice = $product->getPrice(true, null, 2);
                $finalPriceFormatted = $locale->formatPrice($finalPrice, $currency->iso_code);
            }

            // Determine stock status
            $stockStatus = 'in_stock';
            if ($stockQty <= 0) {
                $stockStatus = 'out_of_stock';
            } elseif ($stockQty <= 5) {
                $stockStatus = 'low_stock';
            }

            return [
                'id' => (int) $row['id_product'],
                'type' => 'product',
                'name' => $row['name'],
                'subtitle' => $row['reference'] ? 'Ref: ' . $row['reference'] : null,
                'reference' => $row['reference'],
                'active' => (bool) $row['active'],
                'manufacturer' => $row['manufacturer_name'],
                'category' => $row['category_name'],
                'image' => $row['id_image'] ? $this->getProductImageUrl($row['id_product'], $row['id_image'], $imageType) : null,
                // Formatted fields for list view columns (matching TargetConditions)
                'regular_price_formatted' => $regularPriceFormatted,
                'price_formatted' => $finalPriceFormatted,
                'has_discount' => $hasDiscount,
                'stock_qty' => $stockQty,
                'stock_status' => $stockStatus,
                'sales_qty' => $salesQty,
            ];
        }, $results);
    }

    /**
     * Count products
     *
     * @param string $query Search query
     * @param int $idLang Language ID
     * @param int $idShop Shop ID
     * @param array $filters Additional filters
     * @return int Total count
     */
    public function countTargetProducts($query, $idLang, $idShop, array $filters = [])
    {
        $sql = new DbQuery();
        $sql->select('COUNT(DISTINCT p.id_product)');
        $sql->from('product', 'p');
        $sql->innerJoin('product_shop', 'ps', 'ps.id_product = p.id_product AND ps.id_shop = ' . (int) $idShop);
        $sql->leftJoin('product_lang', 'pl', 'pl.id_product = p.id_product AND pl.id_lang = ' . (int) $idLang . ' AND pl.id_shop = ' . (int) $idShop);

        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(pl.name LIKE \'%' . $escapedQuery . '%\' OR p.reference LIKE \'%' . $escapedQuery . '%\' OR p.id_product = ' . (int) $query . ')');
        }

        $this->applyProductFilters($sql, $filters, $idLang, $idShop);

        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('pl.name NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('pl.name LIKE \'%' . $refine . '%\'');
            }
        }

        return (int) Db::getInstance()->getValue($sql);
    }

    /**
     * Get products by IDs
     *
     * @param array $ids Product IDs
     * @param int $idLang Language ID
     * @param int $idShop Shop ID
     * @return array Products data
     */
    public function getTargetProductsByIds(array $ids, $idLang, $idShop = null)
    {
        if (empty($ids)) {
            return [];
        }

        $idShop = $idShop ?: $this->idShop;

        $sql = new DbQuery();
        $sql->select('p.id_product, pl.name, p.reference, ps.price AS base_price, p.active, i.id_image');
        $sql->select('sa.quantity AS stock_qty');
        // Check for discount
        $sql->select('(ps.on_sale = 1 OR EXISTS (
            SELECT 1 FROM ' . _DB_PREFIX_ . 'specific_price sp
            WHERE sp.id_product = p.id_product
            AND sp.reduction > 0
            AND (sp.from = "0000-00-00 00:00:00" OR sp.from <= NOW())
            AND (sp.to = "0000-00-00 00:00:00" OR sp.to >= NOW())
        )) AS has_discount');
        $sql->from('product', 'p');
        $sql->innerJoin('product_shop', 'ps', 'ps.id_product = p.id_product AND ps.id_shop = ' . (int) $idShop);
        $sql->leftJoin('product_lang', 'pl', 'pl.id_product = p.id_product AND pl.id_lang = ' . (int) $idLang . ' AND pl.id_shop = ' . (int) $idShop);
        $sql->leftJoin('image', 'i', 'i.id_product = p.id_product AND i.cover = 1');
        $sql->leftJoin('stock_available', 'sa', 'sa.id_product = p.id_product AND sa.id_product_attribute = 0 AND sa.id_shop = ' . (int) $idShop);
        $sql->where('p.id_product IN (' . implode(',', array_map('intval', $ids)) . ')');

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        $imageType = $this->getProductImageType();
        $context = Context::getContext();
        $locale = $context->getCurrentLocale();
        $currency = $context->currency;
        $products = [];

        foreach ($results as $row) {
            $basePrice = (float) ($row['base_price'] ?? 0);
            $stockQty = (int) ($row['stock_qty'] ?? 0);
            $hasDiscount = (bool) ($row['has_discount'] ?? false);

            // Format prices
            $regularPriceFormatted = $locale->formatPrice($basePrice, $currency->iso_code);

            // If has discount, get the actual discounted price
            $finalPriceFormatted = $regularPriceFormatted;
            if ($hasDiscount) {
                $product = new \Product((int) $row['id_product'], false, $idLang, $idShop);
                $finalPrice = $product->getPrice(true, null, 2);
                $finalPriceFormatted = $locale->formatPrice($finalPrice, $currency->iso_code);
            }

            // Determine stock status
            $stockStatus = 'in_stock';
            if ($stockQty <= 0) {
                $stockStatus = 'out_of_stock';
            } elseif ($stockQty <= 5) {
                $stockStatus = 'low_stock';
            }

            $products[(int) $row['id_product']] = [
                'id' => (int) $row['id_product'],
                'type' => 'product',
                'name' => $row['name'],
                'subtitle' => $row['reference'] ? 'Ref: ' . $row['reference'] : null,
                'reference' => $row['reference'],
                'active' => (bool) $row['active'],
                'image' => $row['id_image'] ? $this->getProductImageUrl($row['id_product'], $row['id_image'], $imageType) : null,
                // Formatted fields for list view columns (matching TargetConditions)
                'regular_price_formatted' => $regularPriceFormatted,
                'price_formatted' => $finalPriceFormatted,
                'has_discount' => $hasDiscount,
                'stock_qty' => $stockQty,
                'stock_status' => $stockStatus,
            ];
        }

        // Return in original order
        $ordered = [];
        foreach ($ids as $id) {
            if (isset($products[(int) $id])) {
                $ordered[] = $products[(int) $id];
            }
        }

        return $ordered;
    }

    // =========================================================================
    // PRODUCT COMBINATIONS (product_selection_level = combination)
    // =========================================================================

    /**
     * Search products at combination level.
     * Products WITH combinations return one result per combination (id = "c:{id_product_attribute}").
     * Simple products (no combinations) return with id = "p:{id_product}".
     *
     * @param string $query Search query
     * @param int $idLang Language ID
     * @param int $idShop Shop ID
     * @param int $limit Results limit
     * @param int $offset Results offset
     * @param array $filters Additional filters
     * @return array Search results
     */
    public function searchProductCombinations($query, $idLang, $idShop, $limit = 20, $offset = 0, array $filters = [])
    {
        $db = Db::getInstance();
        $escapedQuery = !empty($query) ? $this->escapePattern($query) : '';

        // Step 1: Find matching products (same query as searchTargetProducts but no limit — we paginate the flat result)
        $sqlProducts = new DbQuery();
        $sqlProducts->select('DISTINCT p.id_product, pl.name, p.reference, ps.price AS base_price, ps.active,
            m.name AS manufacturer_name, cl.name AS category_name, i.id_image,
            IFNULL(sa.quantity, 0) AS stock_qty');
        // Sales
        $sqlProducts->select('(SELECT COALESCE(SUM(od.product_quantity), 0) FROM ' . _DB_PREFIX_ . 'order_detail od
            INNER JOIN ' . _DB_PREFIX_ . 'orders o ON o.id_order = od.id_order
            WHERE od.product_id = p.id_product AND o.valid = 1) AS sales_qty');
        $sqlProducts->from('product', 'p');
        $sqlProducts->innerJoin('product_shop', 'ps', 'ps.id_product = p.id_product AND ps.id_shop = ' . (int) $idShop);
        $sqlProducts->leftJoin('product_lang', 'pl', 'pl.id_product = p.id_product AND pl.id_lang = ' . (int) $idLang . ' AND pl.id_shop = ' . (int) $idShop);
        $sqlProducts->leftJoin('manufacturer', 'm', 'm.id_manufacturer = p.id_manufacturer');
        $sqlProducts->leftJoin('category_lang', 'cl', 'cl.id_category = p.id_category_default AND cl.id_lang = ' . (int) $idLang . ' AND cl.id_shop = ' . (int) $idShop);
        $sqlProducts->leftJoin('stock_available', 'sa', 'sa.id_product = p.id_product AND sa.id_product_attribute = 0 AND sa.id_shop = ' . (int) $idShop);
        $sqlProducts->leftJoin('image', 'i', 'i.id_product = p.id_product AND i.cover = 1');

        if (!empty($escapedQuery)) {
            $sqlProducts->where('(pl.name LIKE \'%' . $escapedQuery . '%\' OR p.reference LIKE \'%' . $escapedQuery . '%\' OR p.id_product = ' . (int) $query . ')');
        }

        $this->applyProductFilters($sqlProducts, $filters, $idLang, $idShop);
        $sqlProducts->orderBy('pl.name ASC');

        $matchingProducts = $db->executeS($sqlProducts);
        if (!$matchingProducts) {
            return [];
        }

        $productIds = array_column($matchingProducts, 'id_product');
        $productMap = [];
        foreach ($matchingProducts as $p) {
            $productMap[(int) $p['id_product']] = $p;
        }

        // Step 2: Get all combinations with stock, image, attributes
        $sqlCombos = 'SELECT pa.id_product_attribute, pa.id_product, pa.reference AS combo_reference,
                pas.price AS combo_price_impact,
                IFNULL(sa_c.quantity, 0) AS combo_stock,
                pai.id_image AS combo_id_image,
                GROUP_CONCAT(CONCAT(agl.name, \': \', al.name) ORDER BY ag.position ASC SEPARATOR \', \') AS attributes
            FROM ' . _DB_PREFIX_ . 'product_attribute pa
            INNER JOIN ' . _DB_PREFIX_ . 'product_attribute_shop pas ON pas.id_product_attribute = pa.id_product_attribute AND pas.id_shop = ' . (int) $idShop . '
            INNER JOIN ' . _DB_PREFIX_ . 'product_attribute_combination pac ON pac.id_product_attribute = pa.id_product_attribute
            INNER JOIN ' . _DB_PREFIX_ . 'attribute a ON a.id_attribute = pac.id_attribute
            INNER JOIN ' . _DB_PREFIX_ . 'attribute_lang al ON al.id_attribute = a.id_attribute AND al.id_lang = ' . (int) $idLang . '
            INNER JOIN ' . _DB_PREFIX_ . 'attribute_group ag ON ag.id_attribute_group = a.id_attribute_group
            INNER JOIN ' . _DB_PREFIX_ . 'attribute_group_lang agl ON agl.id_attribute_group = a.id_attribute_group AND agl.id_lang = ' . (int) $idLang . '
            LEFT JOIN ' . _DB_PREFIX_ . 'stock_available sa_c ON sa_c.id_product = pa.id_product AND sa_c.id_product_attribute = pa.id_product_attribute AND sa_c.id_shop = ' . (int) $idShop . '
            LEFT JOIN ' . _DB_PREFIX_ . 'product_attribute_image pai ON pai.id_product_attribute = pa.id_product_attribute
            WHERE pa.id_product IN (' . implode(',', array_map('intval', $productIds)) . ')';

        // Filter combinations by selected attribute values
        $comboAttrIds = $this->extractAttributeFilterIds($filters);
        if (!empty($comboAttrIds)) {
            $sqlCombos .= ' AND EXISTS (
                SELECT 1 FROM ' . _DB_PREFIX_ . 'product_attribute_combination pac_f
                WHERE pac_f.id_product_attribute = pa.id_product_attribute
                AND pac_f.id_attribute IN (' . implode(',', $comboAttrIds) . ')
            )';
        }

        $sqlCombos .= ' GROUP BY pa.id_product_attribute
            ORDER BY pa.id_product, pa.id_product_attribute';

        $combinations = $db->executeS($sqlCombos);

        // Group combinations by product
        $combosByProduct = [];
        if ($combinations) {
            foreach ($combinations as $combo) {
                $combosByProduct[(int) $combo['id_product']][] = $combo;
            }
        }

        // Step 3: Build flat result list with full product data
        $imageType = $this->getProductImageType();
        $context = Context::getContext();
        $locale = $context->getCurrentLocale();
        $currency = $context->currency;
        $isBothMode = ($filters['product_selection_level'] ?? 'combination') === 'both';

        $allResults = [];
        foreach ($productIds as $idProduct) {
            $idProduct = (int) $idProduct;
            $product = $productMap[$idProduct];

            if (!empty($combosByProduct[$idProduct])) {
                // In 'both' mode, add the base product entry first
                if ($isBothMode) {
                    $basePrice = (float) ($product['base_price'] ?? 0);
                    $stockQty = (int) ($product['stock_qty'] ?? 0);
                    $regularPriceFormatted = $locale->formatPrice($basePrice, $currency->iso_code);
                    $finalPrice = \Product::getPriceStatic($idProduct, true, null, 2, null, false, true, 1, false, null, null, null, $dummyNull, true, true, $context);
                    $finalPriceFormatted = $locale->formatPrice($finalPrice, $currency->iso_code);
                    $hasDiscount = ($basePrice > 0 && $finalPrice < $basePrice);
                    $stockStatus = $stockQty <= 0 ? 'out_of_stock' : ($stockQty <= 5 ? 'low_stock' : 'in_stock');

                    $allResults[] = [
                        'id' => 'p:' . $idProduct,
                        'type' => 'product',
                        'name' => $product['name'],
                        'subtitle' => $product['reference'] ? 'Ref: ' . $product['reference'] : null,
                        'reference' => $product['reference'],
                        'is_combination' => false,
                        'is_parent' => true,
                        'combination_count' => count($combosByProduct[$idProduct]),
                        'id_product' => $idProduct,
                        'active' => (bool) $product['active'],
                        'manufacturer' => $product['manufacturer_name'],
                        'category' => $product['category_name'],
                        'image' => $product['id_image'] ? $this->getProductImageUrl($idProduct, $product['id_image'], $imageType) : null,
                        'regular_price_formatted' => $regularPriceFormatted,
                        'price_formatted' => $finalPriceFormatted,
                        'has_discount' => $hasDiscount,
                        'stock_qty' => $stockQty,
                        'stock_status' => $stockStatus,
                        'sales_qty' => (int) ($product['sales_qty'] ?? 0),
                    ];
                }

                // Product HAS combinations — one entry per combination
                foreach ($combosByProduct[$idProduct] as $combo) {
                    $idPA = (int) $combo['id_product_attribute'];
                    $comboStock = (int) $combo['combo_stock'];

                    // Price: use Product::getPrice with combination ID for accurate price
                    $comboPrice = \Product::getPriceStatic($idProduct, true, $idPA, 2, null, false, true, 1, false, null, null, null, $dummyNull, true, true, $context);
                    $comboPriceFormatted = $locale->formatPrice($comboPrice, $currency->iso_code);

                    // Regular price (without reduction)
                    $comboRegularPrice = \Product::getPriceStatic($idProduct, true, $idPA, 2, null, false, false, 1, false, null, null, null, $dummyNull, true, true, $context);
                    $comboRegularPriceFormatted = $locale->formatPrice($comboRegularPrice, $currency->iso_code);

                    $hasDiscount = ($comboRegularPrice > $comboPrice);

                    // Image: combination image if exists, otherwise product cover
                    $imageId = !empty($combo['combo_id_image']) ? $combo['combo_id_image'] : ($product['id_image'] ?? null);

                    // Stock status
                    $stockStatus = 'in_stock';
                    if ($comboStock <= 0) {
                        $stockStatus = 'out_of_stock';
                    } elseif ($comboStock <= 5) {
                        $stockStatus = 'low_stock';
                    }

                    // Name includes attributes: "Product name, Attr1: Val1, Attr2: Val2"
                    $fullName = $product['name'] . ', ' . $combo['attributes'];
                    $ref = $combo['combo_reference'] ?: $product['reference'];

                    $allResults[] = [
                        'id' => 'c:' . $idPA,
                        'type' => 'product',
                        'name' => $fullName,
                        'subtitle' => $ref ? 'Ref: ' . $ref : null,
                        'reference' => $ref,
                        'attributes' => $combo['attributes'],
                        'is_combination' => true,
                        'id_product' => $idProduct,
                        'id_product_attribute' => $idPA,
                        'active' => (bool) $product['active'],
                        'manufacturer' => $product['manufacturer_name'],
                        'category' => $product['category_name'],
                        'image' => $imageId ? $this->getProductImageUrl($idProduct, $imageId, $imageType) : null,
                        'regular_price_formatted' => $comboRegularPriceFormatted,
                        'price_formatted' => $comboPriceFormatted,
                        'has_discount' => $hasDiscount,
                        'stock_qty' => $comboStock,
                        'stock_status' => $stockStatus,
                        'sales_qty' => (int) ($product['sales_qty'] ?? 0),
                    ];
                }
            } else {
                // Simple product — no combinations
                // Skip simple products when attribute filter is active (they can't match)
                if (!empty($comboAttrIds)) {
                    continue;
                }
                $basePrice = (float) ($product['base_price'] ?? 0);
                $stockQty = (int) ($product['stock_qty'] ?? 0);
                $salesQty = (int) ($product['sales_qty'] ?? 0);

                $regularPriceFormatted = $locale->formatPrice($basePrice, $currency->iso_code);
                $finalPrice = \Product::getPriceStatic($idProduct, true, null, 2, null, false, true, 1, false, null, null, null, $dummyNull, true, true, $context);
                $finalPriceFormatted = $locale->formatPrice($finalPrice, $currency->iso_code);
                $hasDiscount = ($basePrice > 0 && $finalPrice < $basePrice);

                $stockStatus = 'in_stock';
                if ($stockQty <= 0) {
                    $stockStatus = 'out_of_stock';
                } elseif ($stockQty <= 5) {
                    $stockStatus = 'low_stock';
                }

                $allResults[] = [
                    'id' => 'p:' . $idProduct,
                    'type' => 'product',
                    'name' => $product['name'],
                    'subtitle' => $product['reference'] ? 'Ref: ' . $product['reference'] : null,
                    'reference' => $product['reference'],
                    'is_combination' => false,
                    'id_product' => $idProduct,
                    'active' => (bool) $product['active'],
                    'manufacturer' => $product['manufacturer_name'],
                    'category' => $product['category_name'],
                    'image' => $product['id_image'] ? $this->getProductImageUrl($idProduct, $product['id_image'], $imageType) : null,
                    'regular_price_formatted' => $regularPriceFormatted,
                    'price_formatted' => $finalPriceFormatted,
                    'has_discount' => $hasDiscount,
                    'stock_qty' => $stockQty,
                    'stock_status' => $stockStatus,
                    'sales_qty' => $salesQty,
                ];
            }
        }

        // Apply offset/limit to the flat list
        return array_slice($allResults, (int) $offset, (int) $limit);
    }

    /**
     * Count products at combination level.
     * Each combination counts as 1. Simple products count as 1.
     *
     * @param string $query Search query
     * @param int $idLang Language ID
     * @param int $idShop Shop ID
     * @param array $filters Additional filters
     * @return int Total count
     */
    public function countProductCombinations($query, $idLang, $idShop, array $filters = [])
    {
        $db = Db::getInstance();
        $escapedQuery = !empty($query) ? $this->escapePattern($query) : '';

        // Get matching product IDs
        $sqlProducts = new DbQuery();
        $sqlProducts->select('DISTINCT p.id_product');
        $sqlProducts->from('product', 'p');
        $sqlProducts->innerJoin('product_shop', 'ps', 'ps.id_product = p.id_product AND ps.id_shop = ' . (int) $idShop);
        $sqlProducts->leftJoin('product_lang', 'pl', 'pl.id_product = p.id_product AND pl.id_lang = ' . (int) $idLang . ' AND pl.id_shop = ' . (int) $idShop);

        if (!empty($escapedQuery)) {
            $sqlProducts->where('(pl.name LIKE \'%' . $escapedQuery . '%\' OR p.reference LIKE \'%' . $escapedQuery . '%\' OR p.id_product = ' . (int) $query . ')');
        }

        $this->applyProductFilters($sqlProducts, $filters, $idLang, $idShop);

        $matchingProducts = $db->executeS($sqlProducts);
        if (!$matchingProducts) {
            return 0;
        }

        $productIds = array_column($matchingProducts, 'id_product');

        // Count combinations for these products
        $sqlComboCount = 'SELECT COUNT(*) FROM ' . _DB_PREFIX_ . 'product_attribute pa
            INNER JOIN ' . _DB_PREFIX_ . 'product_attribute_shop pas ON pas.id_product_attribute = pa.id_product_attribute AND pas.id_shop = ' . (int) $idShop . '
            WHERE pa.id_product IN (' . implode(',', array_map('intval', $productIds)) . ')';

        // Filter combination count by selected attribute values
        $comboAttrIds = $this->extractAttributeFilterIds($filters);
        if (!empty($comboAttrIds)) {
            $sqlComboCount .= ' AND EXISTS (
                SELECT 1 FROM ' . _DB_PREFIX_ . 'product_attribute_combination pac_f
                WHERE pac_f.id_product_attribute = pa.id_product_attribute
                AND pac_f.id_attribute IN (' . implode(',', $comboAttrIds) . ')
            )';
        }

        $comboCount = (int) $db->getValue($sqlComboCount);

        // Count simple products (those with no combinations)
        // Simple products only included when no attribute filter is active
        // (simple products have no combinations, so they can't match attribute filters)
        $simpleCount = 0;
        if (empty($comboAttrIds)) {
            $sqlSimpleCount = 'SELECT COUNT(*) FROM (' . $sqlProducts->build() . ') AS matched_products
                WHERE matched_products.id_product NOT IN (
                    SELECT DISTINCT pa2.id_product FROM ' . _DB_PREFIX_ . 'product_attribute pa2
                    INNER JOIN ' . _DB_PREFIX_ . 'product_attribute_shop pas2 ON pas2.id_product_attribute = pa2.id_product_attribute AND pas2.id_shop = ' . (int) $idShop . '
                )';
            $simpleCount = (int) $db->getValue($sqlSimpleCount);
        }

        // In 'both' mode, also count parent products (products that have combinations)
        $isBothMode = ($filters['product_selection_level'] ?? 'combination') === 'both';
        $parentCount = 0;
        if ($isBothMode) {
            $sqlParentCount = 'SELECT COUNT(DISTINCT pa3.id_product) FROM ' . _DB_PREFIX_ . 'product_attribute pa3
                INNER JOIN ' . _DB_PREFIX_ . 'product_attribute_shop pas3 ON pas3.id_product_attribute = pa3.id_product_attribute AND pas3.id_shop = ' . (int) $idShop . '
                WHERE pa3.id_product IN (' . implode(',', array_map('intval', $productIds)) . ')';
            $parentCount = (int) $db->getValue($sqlParentCount);
        }

        return $comboCount + $simpleCount + $parentCount;
    }

    /**
     * Get product combinations by prefixed IDs.
     * Handles "c:{id_product_attribute}" and "p:{id_product}" formats.
     *
     * @param array $ids Prefixed string IDs
     * @param int $idLang Language ID
     * @param int $idShop Shop ID
     * @return array Entities data
     */
    public function getProductCombinationsByIds(array $ids, $idLang, $idShop = null)
    {
        if (empty($ids)) {
            return [];
        }

        $idShop = $idShop ?: $this->idShop;
        $db = Db::getInstance();

        // Parse prefixed IDs
        $comboIds = [];
        $productIds = [];
        foreach ($ids as $prefixedId) {
            $prefixedId = (string) $prefixedId;
            if (strpos($prefixedId, 'c:') === 0) {
                $comboIds[] = (int) substr($prefixedId, 2);
            } elseif (strpos($prefixedId, 'p:') === 0) {
                $productIds[] = (int) substr($prefixedId, 2);
            } else {
                // Fallback: treat as product ID
                $productIds[] = (int) $prefixedId;
            }
        }

        $imageType = $this->getProductImageType();
        $context = Context::getContext();
        $locale = $context->getCurrentLocale();
        $currency = $context->currency;

        $results = [];

        // Fetch combinations with full data
        if (!empty($comboIds)) {
            $sqlCombos = 'SELECT pa.id_product_attribute, pa.id_product, pa.reference AS combo_reference,
                    pl.name, p.reference AS product_reference, i.id_image,
                    IFNULL(sa_c.quantity, 0) AS combo_stock,
                    pai.id_image AS combo_id_image,
                    GROUP_CONCAT(CONCAT(agl.name, \': \', al.name) ORDER BY ag.position ASC SEPARATOR \', \') AS attributes
                FROM ' . _DB_PREFIX_ . 'product_attribute pa
                INNER JOIN ' . _DB_PREFIX_ . 'product p ON p.id_product = pa.id_product
                INNER JOIN ' . _DB_PREFIX_ . 'product_attribute_shop pas ON pas.id_product_attribute = pa.id_product_attribute AND pas.id_shop = ' . (int) $idShop . '
                INNER JOIN ' . _DB_PREFIX_ . 'product_lang pl ON pl.id_product = p.id_product AND pl.id_lang = ' . (int) $idLang . ' AND pl.id_shop = ' . (int) $idShop . '
                INNER JOIN ' . _DB_PREFIX_ . 'product_attribute_combination pac ON pac.id_product_attribute = pa.id_product_attribute
                INNER JOIN ' . _DB_PREFIX_ . 'attribute a ON a.id_attribute = pac.id_attribute
                INNER JOIN ' . _DB_PREFIX_ . 'attribute_lang al ON al.id_attribute = a.id_attribute AND al.id_lang = ' . (int) $idLang . '
                INNER JOIN ' . _DB_PREFIX_ . 'attribute_group ag ON ag.id_attribute_group = a.id_attribute_group
                INNER JOIN ' . _DB_PREFIX_ . 'attribute_group_lang agl ON agl.id_attribute_group = a.id_attribute_group AND agl.id_lang = ' . (int) $idLang . '
                LEFT JOIN ' . _DB_PREFIX_ . 'stock_available sa_c ON sa_c.id_product = pa.id_product AND sa_c.id_product_attribute = pa.id_product_attribute AND sa_c.id_shop = ' . (int) $idShop . '
                LEFT JOIN ' . _DB_PREFIX_ . 'product_attribute_image pai ON pai.id_product_attribute = pa.id_product_attribute
                LEFT JOIN ' . _DB_PREFIX_ . 'image i ON i.id_product = p.id_product AND i.cover = 1
                WHERE pa.id_product_attribute IN (' . implode(',', array_map('intval', $comboIds)) . ')
                GROUP BY pa.id_product_attribute';

            $comboRows = $db->executeS($sqlCombos);
            if ($comboRows) {
                foreach ($comboRows as $row) {
                    $idPA = (int) $row['id_product_attribute'];
                    $idProduct = (int) $row['id_product'];
                    $key = 'c:' . $idPA;

                    // Price
                    $comboPrice = \Product::getPriceStatic($idProduct, true, $idPA, 2, null, false, true, 1, false, null, null, null, $dummyNull, true, true, $context);
                    $comboRegularPrice = \Product::getPriceStatic($idProduct, true, $idPA, 2, null, false, false, 1, false, null, null, null, $dummyNull, true, true, $context);
                    $hasDiscount = ($comboRegularPrice > $comboPrice);

                    // Image: combination image if exists, otherwise product cover
                    $imageId = !empty($row['combo_id_image']) ? $row['combo_id_image'] : ($row['id_image'] ?? null);

                    // Stock
                    $comboStock = (int) $row['combo_stock'];
                    $stockStatus = $comboStock <= 0 ? 'out_of_stock' : ($comboStock <= 5 ? 'low_stock' : 'in_stock');

                    $ref = $row['combo_reference'] ?: $row['product_reference'];

                    $results[$key] = [
                        'id' => $key,
                        'type' => 'product',
                        'name' => $row['name'] . ', ' . $row['attributes'],
                        'subtitle' => $ref ? 'Ref: ' . $ref : null,
                        'reference' => $ref,
                        'attributes' => $row['attributes'],
                        'is_combination' => true,
                        'id_product' => $idProduct,
                        'id_product_attribute' => $idPA,
                        'image' => $imageId ? $this->getProductImageUrl($idProduct, $imageId, $imageType) : null,
                        'regular_price_formatted' => $locale->formatPrice($comboRegularPrice, $currency->iso_code),
                        'price_formatted' => $locale->formatPrice($comboPrice, $currency->iso_code),
                        'has_discount' => $hasDiscount,
                        'stock_qty' => $comboStock,
                        'stock_status' => $stockStatus,
                    ];
                }
            }
        }

        // Fetch simple products with full data
        if (!empty($productIds)) {
            $sqlProducts = new DbQuery();
            $sqlProducts->select('p.id_product, pl.name, p.reference, ps.price AS base_price,
                IFNULL(sa.quantity, 0) AS stock_qty, i.id_image');
            $sqlProducts->from('product', 'p');
            $sqlProducts->innerJoin('product_shop', 'ps', 'ps.id_product = p.id_product AND ps.id_shop = ' . (int) $idShop);
            $sqlProducts->leftJoin('product_lang', 'pl', 'pl.id_product = p.id_product AND pl.id_lang = ' . (int) $idLang . ' AND pl.id_shop = ' . (int) $idShop);
            $sqlProducts->leftJoin('stock_available', 'sa', 'sa.id_product = p.id_product AND sa.id_product_attribute = 0 AND sa.id_shop = ' . (int) $idShop);
            $sqlProducts->leftJoin('image', 'i', 'i.id_product = p.id_product AND i.cover = 1');
            $sqlProducts->where('p.id_product IN (' . implode(',', array_map('intval', $productIds)) . ')');

            $productRows = $db->executeS($sqlProducts);
            if ($productRows) {
                foreach ($productRows as $row) {
                    $idProduct = (int) $row['id_product'];
                    $key = 'p:' . $idProduct;
                    $stockQty = (int) $row['stock_qty'];

                    $finalPrice = \Product::getPriceStatic($idProduct, true, null, 2, null, false, true, 1, false, null, null, null, $dummyNull, true, true, $context);
                    $regularPrice = (float) $row['base_price'];
                    $hasDiscount = ($regularPrice > 0 && $finalPrice < $regularPrice);

                    $stockStatus = $stockQty <= 0 ? 'out_of_stock' : ($stockQty <= 5 ? 'low_stock' : 'in_stock');

                    $results[$key] = [
                        'id' => $key,
                        'type' => 'product',
                        'name' => $row['name'],
                        'subtitle' => $row['reference'] ? 'Ref: ' . $row['reference'] : null,
                        'reference' => $row['reference'],
                        'is_combination' => false,
                        'id_product' => $idProduct,
                        'image' => $row['id_image'] ? $this->getProductImageUrl($idProduct, $row['id_image'], $imageType) : null,
                        'regular_price_formatted' => $locale->formatPrice($regularPrice, $currency->iso_code),
                        'price_formatted' => $locale->formatPrice($finalPrice, $currency->iso_code),
                        'has_discount' => $hasDiscount,
                        'stock_qty' => $stockQty,
                        'stock_status' => $stockStatus,
                    ];
                }
            }
        }

        // Return in original order
        $ordered = [];
        foreach ($ids as $originalId) {
            $key = (string) $originalId;
            if (isset($results[$key])) {
                $ordered[] = $results[$key];
            } elseif (isset($results['p:' . $key])) {
                // Plain integer ID → stored as "p:{id}" (simple product fallback)
                $ordered[] = $results['p:' . $key];
            } elseif (isset($results['c:' . $key])) {
                $ordered[] = $results['c:' . $key];
            }
        }

        return $ordered;
    }

    /**
     * Apply product-specific filters to query
     *
     * @param DbQuery $sql Query builder
     * @param array $filters Filters to apply
     * @param int $idLang Language ID
     * @param int $idShop Shop ID
     */
    protected function applyProductFilters(DbQuery $sql, array $filters, $idLang, $idShop)
    {
        // Stock filter
        if (!empty($filters['in_stock'])) {
            $sql->leftJoin('stock_available', 'sa_filter', 'sa_filter.id_product = p.id_product AND sa_filter.id_product_attribute = 0 AND sa_filter.id_shop = ' . (int) $idShop);
            $sql->where('sa_filter.quantity > 0');
        }

        // Discounted filter
        if (!empty($filters['discounted'])) {
            $sql->innerJoin('specific_price', 'sp', 'sp.id_product = p.id_product');
            $sql->where('sp.reduction > 0');
            $sql->where('(sp.from = \'0000-00-00 00:00:00\' OR sp.from <= NOW())');
            $sql->where('(sp.to = \'0000-00-00 00:00:00\' OR sp.to >= NOW())');
        }

        // Price range
        if ($filters['price_min'] !== null) {
            $sql->where('p.price >= ' . (float) $filters['price_min']);
        }
        if ($filters['price_max'] !== null) {
            $sql->where('p.price <= ' . (float) $filters['price_max']);
        }

        // Active only
        if (!empty($filters['active_only'])) {
            $sql->where('ps.active = 1');
        }

        // Attribute filter — supports flat array [id1, id2] or grouped [{id_attribute_group, values}]
        if (!empty($filters['attributes'])) {
            $first = reset($filters['attributes']);
            if (is_numeric($first)) {
                // Flat array of attribute value IDs (from filter chips)
                $attrIds = array_map('intval', $filters['attributes']);
                $sql->innerJoin('product_attribute_combination', 'pac_filter', 'pac_filter.id_attribute IN (' . implode(',', $attrIds) . ')');
                $sql->innerJoin('product_attribute', 'pa_filter', 'pa_filter.id_product_attribute = pac_filter.id_product_attribute AND pa_filter.id_product = p.id_product');
            } else {
                // Grouped format: [{id_attribute_group: X, values: [1,2,3]}, ...]
                foreach ($filters['attributes'] as $attrFilter) {
                    if (!empty($attrFilter['values'])) {
                        $attrIds = array_map('intval', $attrFilter['values']);
                        $sql->innerJoin('product_attribute_combination', 'pac_' . (int) $attrFilter['id_attribute_group'], 'pac_' . (int) $attrFilter['id_attribute_group'] . '.id_attribute IN (' . implode(',', $attrIds) . ')');
                        $sql->innerJoin('product_attribute', 'pa_' . (int) $attrFilter['id_attribute_group'], 'pa_' . (int) $attrFilter['id_attribute_group'] . '.id_product_attribute = pac_' . (int) $attrFilter['id_attribute_group'] . '.id_product_attribute AND pa_' . (int) $attrFilter['id_attribute_group'] . '.id_product = p.id_product');
                    }
                }
            }
        }

        // Feature filter — supports flat array [id1, id2] or grouped [{id_feature, values}]
        if (!empty($filters['features'])) {
            $first = reset($filters['features']);
            if (is_numeric($first)) {
                // Flat array of feature value IDs (from filter chips)
                $featIds = array_map('intval', $filters['features']);
                $sql->innerJoin('feature_product', 'fp_filter', 'fp_filter.id_product = p.id_product AND fp_filter.id_feature_value IN (' . implode(',', $featIds) . ')');
            } else {
                // Grouped format: [{id_feature: X, values: [1,2,3]}, ...]
                foreach ($filters['features'] as $featFilter) {
                    if (!empty($featFilter['values'])) {
                        $featIds = array_map('intval', $featFilter['values']);
                        $sql->innerJoin('feature_product', 'fp_' . (int) $featFilter['id_feature'], 'fp_' . (int) $featFilter['id_feature'] . '.id_product = p.id_product AND fp_' . (int) $featFilter['id_feature'] . '.id_feature_value IN (' . implode(',', $featIds) . ')');
                    }
                }
            }
        }

        // Date filters
        if (!empty($filters['date_add_from'])) {
            $sql->where('p.date_add >= \'' . pSQL($filters['date_add_from']) . ' 00:00:00\'');
        }
        if (!empty($filters['date_add_to'])) {
            $sql->where('p.date_add <= \'' . pSQL($filters['date_add_to']) . ' 23:59:59\'');
        }
    }

    /**
     * Extract flat array of attribute value IDs from filters.
     * Handles both flat [id1, id2] and grouped [{id_attribute_group, values}] formats.
     *
     * @param array $filters
     * @return array Integer attribute IDs
     */
    protected function extractAttributeFilterIds(array $filters)
    {
        if (empty($filters['attributes'])) {
            return [];
        }

        $first = reset($filters['attributes']);
        if (is_numeric($first)) {
            return array_map('intval', $filters['attributes']);
        }

        $ids = [];
        foreach ($filters['attributes'] as $attrFilter) {
            if (!empty($attrFilter['values'])) {
                $ids = array_merge($ids, array_map('intval', $attrFilter['values']));
            }
        }
        return $ids;
    }

    /**
     * Get product image type for thumbnails
     *
     * @return string
     */
    protected function getProductImageType()
    {
        $type = ImageType::getFormattedName('small');
        return $type ?: 'small_default';
    }

    /**
     * Get product image URL
     *
     * @param int $idProduct Product ID
     * @param int $idImage Image ID
     * @param string $type Image type
     * @return string
     */
    protected function getProductImageUrl($idProduct, $idImage, $type)
    {
        $link = Context::getContext()->link;
        return $link->getImageLink('product', $idProduct . '-' . $idImage, $type);
    }

    // =========================================================================
    // CATEGORIES
    // =========================================================================

    /**
     * Search categories
     *
     * @param string $query Search query
     * @param int $idLang Language ID
     * @param int $idShop Shop ID
     * @param int $limit Results limit
     * @param int $offset Results offset
     * @param array $filters Additional filters
     * @return array Category results
     */
    public function searchTargetCategories($query, $idLang, $idShop, $limit = 20, $offset = 0, array $filters = [])
    {
        $sql = new DbQuery();
        $sql->select('DISTINCT c.id_category, cl.name, c.active, c.level_depth, c.position');
        $sql->select('(SELECT COUNT(cp2.id_product) FROM ' . _DB_PREFIX_ . 'category_product cp2 WHERE cp2.id_category = c.id_category) AS product_count');
        $sql->from('category', 'c');
        $sql->innerJoin('category_shop', 'cs', 'cs.id_category = c.id_category AND cs.id_shop = ' . (int) $idShop);
        $sql->leftJoin('category_lang', 'cl', 'cl.id_category = c.id_category AND cl.id_lang = ' . (int) $idLang . ' AND cl.id_shop = ' . (int) $idShop);

        // Exclude root category
        $sql->where('c.id_parent > 0');

        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(cl.name LIKE \'%' . $escapedQuery . '%\' OR c.id_category = ' . (int) $query . ')');
        }

        // Refine query
        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('cl.name NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('cl.name LIKE \'%' . $refine . '%\'');
            }
        }

        // Depth filter
        if ($filters['depth'] !== null) {
            $sql->where('c.level_depth = ' . (int) $filters['depth']);
        }

        // Product count filter
        if ($filters['product_count_min'] !== null || $filters['product_count_max'] !== null) {
            $having = [];
            if ($filters['product_count_min'] !== null) {
                $having[] = 'product_count >= ' . (int) $filters['product_count_min'];
            }
            if ($filters['product_count_max'] !== null) {
                $having[] = 'product_count <= ' . (int) $filters['product_count_max'];
            }
            // Having clause needs GROUP BY - restructure query
            $sql->groupBy('c.id_category');
            foreach ($having as $h) {
                $sql->having($h);
            }
        }

        // Has products filter
        if (!empty($filters['has_products'])) {
            $sql->innerJoin('category_product', 'cp_filter', 'cp_filter.id_category = c.id_category');
        }

        // Active filter
        if (!empty($filters['active_only'])) {
            $sql->where('c.active = 1');
        }

        // Sorting
        $categorySortMap = [
            'name' => 'cl.name',
            'id' => 'c.id_category',
            'position' => 'c.position',
            'product_count' => 'product_count',
        ];
        $sql->orderBy($this->buildOrderBy('categories', $filters, $categorySortMap));
        $sql->limit((int) $limit, (int) $offset);

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        return array_map(function ($row) {
            return [
                'id' => (int) $row['id_category'],
                'type' => 'category',
                'name' => $row['name'],
                'active' => (bool) $row['active'],
                'level_depth' => (int) $row['level_depth'],
                'position' => (int) $row['position'],
                'product_count' => (int) $row['product_count'],
                'breadcrumb' => $this->getCategoryBreadcrumb((int) $row['id_category']),
            ];
        }, $results);
    }

    /**
     * Count categories
     *
     * @param string $query Search query
     * @param int $idLang Language ID
     * @param int $idShop Shop ID
     * @param array $filters Additional filters
     * @return int Total count
     */
    public function countTargetCategories($query, $idLang, $idShop, array $filters = [])
    {
        $sql = new DbQuery();
        $sql->select('COUNT(DISTINCT c.id_category)');
        $sql->from('category', 'c');
        $sql->innerJoin('category_shop', 'cs', 'cs.id_category = c.id_category AND cs.id_shop = ' . (int) $idShop);
        $sql->leftJoin('category_lang', 'cl', 'cl.id_category = c.id_category AND cl.id_lang = ' . (int) $idLang . ' AND cl.id_shop = ' . (int) $idShop);
        $sql->where('c.id_parent > 0');

        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(cl.name LIKE \'%' . $escapedQuery . '%\' OR c.id_category = ' . (int) $query . ')');
        }

        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('cl.name NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('cl.name LIKE \'%' . $refine . '%\'');
            }
        }

        if ($filters['depth'] !== null) {
            $sql->where('c.level_depth = ' . (int) $filters['depth']);
        }

        if (!empty($filters['has_products'])) {
            $sql->innerJoin('category_product', 'cp_filter', 'cp_filter.id_category = c.id_category');
        }

        if (!empty($filters['active_only'])) {
            $sql->where('c.active = 1');
        }

        return (int) Db::getInstance()->getValue($sql);
    }

    /**
     * Get categories by IDs
     *
     * @param array $ids Category IDs
     * @param int $idLang Language ID
     * @param int $idShop Shop ID
     * @return array Categories data
     */
    public function getTargetCategoriesByIds(array $ids, $idLang, $idShop = null)
    {
        if (empty($ids)) {
            return [];
        }

        $idShop = $idShop ?: $this->idShop;

        $sql = new DbQuery();
        $sql->select('c.id_category, cl.name, c.active, c.level_depth');
        $sql->from('category', 'c');
        $sql->leftJoin('category_lang', 'cl', 'cl.id_category = c.id_category AND cl.id_lang = ' . (int) $idLang . ' AND cl.id_shop = ' . (int) $idShop);
        $sql->where('c.id_category IN (' . implode(',', array_map('intval', $ids)) . ')');

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        $categories = [];
        foreach ($results as $row) {
            $categories[(int) $row['id_category']] = [
                'id' => (int) $row['id_category'],
                'type' => 'category',
                'name' => $row['name'],
                'active' => (bool) $row['active'],
                'breadcrumb' => $this->getCategoryBreadcrumb((int) $row['id_category']),
            ];
        }

        $ordered = [];
        foreach ($ids as $id) {
            if (isset($categories[(int) $id])) {
                $ordered[] = $categories[(int) $id];
            }
        }

        return $ordered;
    }

    /**
     * Get category breadcrumb
     *
     * @param int $idCategory Category ID
     * @return string
     */
    protected function getCategoryBreadcrumb($idCategory)
    {
        $sql = new DbQuery();
        $sql->select('cl.name');
        $sql->from('category', 'c');
        $sql->leftJoin('category_lang', 'cl', 'cl.id_category = c.id_category AND cl.id_lang = ' . (int) $this->idLang . ' AND cl.id_shop = ' . (int) $this->idShop);
        $sql->where('c.nleft <= (SELECT nleft FROM ' . _DB_PREFIX_ . 'category WHERE id_category = ' . (int) $idCategory . ')');
        $sql->where('c.nright >= (SELECT nright FROM ' . _DB_PREFIX_ . 'category WHERE id_category = ' . (int) $idCategory . ')');
        $sql->where('c.id_parent > 0');
        $sql->orderBy('c.level_depth ASC');

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return '';
        }

        return implode(' > ', array_column($results, 'name'));
    }

    /**
     * Get full category tree with hierarchy
     *
     * @param int $idLang Language ID
     * @param int $idShop Shop ID
     * @param array $selectedIds Currently selected category IDs
     * @param bool $activeOnly Only include active categories
     * @return array Tree structure
     */
    public function getCategoryTree($idLang = null, $idShop = null, array $selectedIds = [], $activeOnly = false)
    {
        $idLang = $idLang ?: $this->idLang;
        $idShop = $idShop ?: $this->idShop;

        $sql = new DbQuery();
        $sql->select('c.id_category, c.id_parent, cl.name, c.active, c.level_depth, c.position');
        $sql->select('(SELECT COUNT(cp.id_product) FROM ' . _DB_PREFIX_ . 'category_product cp WHERE cp.id_category = c.id_category) AS product_count');
        $sql->from('category', 'c');
        $sql->innerJoin('category_shop', 'cs', 'cs.id_category = c.id_category AND cs.id_shop = ' . (int) $idShop);
        $sql->leftJoin('category_lang', 'cl', 'cl.id_category = c.id_category AND cl.id_lang = ' . (int) $idLang . ' AND cl.id_shop = ' . (int) $idShop);
        $sql->where('c.id_parent > 0'); // Exclude root

        if ($activeOnly) {
            $sql->where('c.active = 1');
        }

        $sql->orderBy('c.level_depth ASC, c.position ASC');

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        // Convert selected IDs to lookup set
        $selectedSet = array_flip($selectedIds);

        // Build flat array with parent references
        $categories = [];
        foreach ($results as $row) {
            $id = (int) $row['id_category'];
            $categories[$id] = [
                'id' => $id,
                'id_parent' => (int) $row['id_parent'],
                'name' => $row['name'],
                'active' => (bool) $row['active'],
                'level_depth' => (int) $row['level_depth'],
                'position' => (int) $row['position'],
                'product_count' => (int) $row['product_count'],
                'selected' => isset($selectedSet[$id]),
                'children' => [],
            ];
        }

        // Build tree structure
        $tree = [];
        foreach ($categories as $id => &$category) {
            $parentId = $category['id_parent'];
            if (isset($categories[$parentId])) {
                $categories[$parentId]['children'][] = &$category;
            } else {
                // Top-level category (parent is root)
                $tree[] = &$category;
            }
        }
        unset($category);

        // Sort children by position at each level
        $this->sortTreeByPosition($tree);

        return $tree;
    }

    /**
     * Recursively sort tree children by position
     *
     * @param array &$nodes Tree nodes
     */
    protected function sortTreeByPosition(array &$nodes)
    {
        usort($nodes, function ($a, $b) {
            return $a['position'] - $b['position'];
        });

        foreach ($nodes as &$node) {
            if (!empty($node['children'])) {
                $this->sortTreeByPosition($node['children']);
            }
        }
    }

    /**
     * Get all descendant category IDs for given parent IDs
     *
     * @param array $parentIds Parent category IDs
     * @param int $idShop Shop ID
     * @return array All descendant IDs (including parents)
     */
    public function getCategoryDescendants(array $parentIds, $idShop = null)
    {
        if (empty($parentIds)) {
            return [];
        }

        $idShop = $idShop ?: $this->idShop;
        $allIds = $parentIds;

        // Use nleft/nright for efficient descendant lookup
        $sql = new DbQuery();
        $sql->select('DISTINCT c2.id_category');
        $sql->from('category', 'c1');
        $sql->innerJoin('category', 'c2', 'c2.nleft > c1.nleft AND c2.nright < c1.nright');
        $sql->innerJoin('category_shop', 'cs', 'cs.id_category = c2.id_category AND cs.id_shop = ' . (int) $idShop);
        $sql->where('c1.id_category IN (' . implode(',', array_map('intval', $parentIds)) . ')');

        $results = Db::getInstance()->executeS($sql);

        if ($results) {
            foreach ($results as $row) {
                $allIds[] = (int) $row['id_category'];
            }
        }

        return array_unique($allIds);
    }

    // =========================================================================
    // MANUFACTURERS
    // =========================================================================

    /**
     * Search manufacturers
     *
     * @param string $query Search query
     * @param int $idLang Language ID
     * @param int $idShop Shop ID
     * @param int $limit Results limit
     * @param int $offset Results offset
     * @param array $filters Additional filters
     * @return array Manufacturer results
     */
    public function searchTargetManufacturers($query, $idLang, $idShop, $limit = 20, $offset = 0, array $filters = [])
    {
        $sql = new DbQuery();
        $sql->select('DISTINCT m.id_manufacturer, m.name, m.active');
        $sql->select('(SELECT COUNT(DISTINCT p.id_product) FROM ' . _DB_PREFIX_ . 'product p
            INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps ON ps.id_product = p.id_product AND ps.id_shop = ' . (int) $idShop . '
            WHERE p.id_manufacturer = m.id_manufacturer) AS product_count');
        $sql->from('manufacturer', 'm');
        $sql->innerJoin('manufacturer_shop', 'ms', 'ms.id_manufacturer = m.id_manufacturer AND ms.id_shop = ' . (int) $idShop);

        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(m.name LIKE \'%' . $escapedQuery . '%\' OR m.id_manufacturer = ' . (int) $query . ')');
        }

        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('m.name NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('m.name LIKE \'%' . $refine . '%\'');
            }
        }

        // Has products
        if (!empty($filters['has_products'])) {
            $sql->having('product_count > 0');
        }

        // Product count range
        if ($filters['product_count_min'] !== null || $filters['product_count_max'] !== null) {
            $sql->groupBy('m.id_manufacturer');
            if ($filters['product_count_min'] !== null) {
                $sql->having('product_count >= ' . (int) $filters['product_count_min']);
            }
            if ($filters['product_count_max'] !== null) {
                $sql->having('product_count <= ' . (int) $filters['product_count_max']);
            }
        }

        if (!empty($filters['active_only'])) {
            $sql->where('m.active = 1');
        }

        // Sorting
        $manufacturerSortMap = [
            'name' => 'm.name',
            'id' => 'm.id_manufacturer',
            'product_count' => 'product_count',
        ];
        $sql->orderBy($this->buildOrderBy('manufacturers', $filters, $manufacturerSortMap));
        $sql->limit((int) $limit, (int) $offset);

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        return array_map(function ($row) {
            return [
                'id' => (int) $row['id_manufacturer'],
                'type' => 'manufacturer',
                'name' => $row['name'],
                'active' => (bool) $row['active'],
                'product_count' => (int) $row['product_count'],
            ];
        }, $results);
    }

    /**
     * Count manufacturers
     */
    public function countTargetManufacturers($query, $idLang, $idShop, array $filters = [])
    {
        $sql = new DbQuery();
        $sql->select('COUNT(DISTINCT m.id_manufacturer)');
        $sql->from('manufacturer', 'm');
        $sql->innerJoin('manufacturer_shop', 'ms', 'ms.id_manufacturer = m.id_manufacturer AND ms.id_shop = ' . (int) $idShop);

        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(m.name LIKE \'%' . $escapedQuery . '%\' OR m.id_manufacturer = ' . (int) $query . ')');
        }

        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('m.name NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('m.name LIKE \'%' . $refine . '%\'');
            }
        }

        if (!empty($filters['active_only'])) {
            $sql->where('m.active = 1');
        }

        return (int) Db::getInstance()->getValue($sql);
    }

    /**
     * Get manufacturers by IDs
     */
    public function getTargetManufacturersByIds(array $ids, $idLang)
    {
        if (empty($ids)) {
            return [];
        }

        $sql = new DbQuery();
        $sql->select('m.id_manufacturer, m.name, m.active');
        $sql->from('manufacturer', 'm');
        $sql->where('m.id_manufacturer IN (' . implode(',', array_map('intval', $ids)) . ')');

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        $manufacturers = [];
        foreach ($results as $row) {
            $manufacturers[(int) $row['id_manufacturer']] = [
                'id' => (int) $row['id_manufacturer'],
                'type' => 'manufacturer',
                'name' => $row['name'],
                'active' => (bool) $row['active'],
            ];
        }

        $ordered = [];
        foreach ($ids as $id) {
            if (isset($manufacturers[(int) $id])) {
                $ordered[] = $manufacturers[(int) $id];
            }
        }

        return $ordered;
    }

    // =========================================================================
    // SUPPLIERS
    // =========================================================================

    /**
     * Search suppliers
     */
    public function searchTargetSuppliers($query, $idLang, $idShop, $limit = 20, $offset = 0, array $filters = [])
    {
        $sql = new DbQuery();
        $sql->select('DISTINCT s.id_supplier, s.name, s.active');
        $sql->select('(SELECT COUNT(DISTINCT ps.id_product) FROM ' . _DB_PREFIX_ . 'product_supplier ps
            INNER JOIN ' . _DB_PREFIX_ . 'product_shop psh ON psh.id_product = ps.id_product AND psh.id_shop = ' . (int) $idShop . '
            WHERE ps.id_supplier = s.id_supplier) AS product_count');
        $sql->from('supplier', 's');
        $sql->innerJoin('supplier_shop', 'ss', 'ss.id_supplier = s.id_supplier AND ss.id_shop = ' . (int) $idShop);

        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(s.name LIKE \'%' . $escapedQuery . '%\' OR s.id_supplier = ' . (int) $query . ')');
        }

        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('s.name NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('s.name LIKE \'%' . $refine . '%\'');
            }
        }

        if (!empty($filters['active_only'])) {
            $sql->where('s.active = 1');
        }

        // Sorting
        $supplierSortMap = [
            'name' => 's.name',
            'id' => 's.id_supplier',
            'product_count' => 'product_count',
        ];
        $sql->orderBy($this->buildOrderBy('suppliers', $filters, $supplierSortMap));
        $sql->limit((int) $limit, (int) $offset);

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        return array_map(function ($row) {
            return [
                'id' => (int) $row['id_supplier'],
                'type' => 'supplier',
                'name' => $row['name'],
                'active' => (bool) $row['active'],
                'product_count' => (int) $row['product_count'],
            ];
        }, $results);
    }

    /**
     * Count suppliers
     */
    public function countTargetSuppliers($query, $idLang, $idShop, array $filters = [])
    {
        $sql = new DbQuery();
        $sql->select('COUNT(DISTINCT s.id_supplier)');
        $sql->from('supplier', 's');
        $sql->innerJoin('supplier_shop', 'ss', 'ss.id_supplier = s.id_supplier AND ss.id_shop = ' . (int) $idShop);

        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(s.name LIKE \'%' . $escapedQuery . '%\' OR s.id_supplier = ' . (int) $query . ')');
        }

        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('s.name NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('s.name LIKE \'%' . $refine . '%\'');
            }
        }

        if (!empty($filters['active_only'])) {
            $sql->where('s.active = 1');
        }

        return (int) Db::getInstance()->getValue($sql);
    }

    /**
     * Get suppliers by IDs
     */
    public function getTargetSuppliersByIds(array $ids, $idLang)
    {
        if (empty($ids)) {
            return [];
        }

        $sql = new DbQuery();
        $sql->select('s.id_supplier, s.name, s.active');
        $sql->from('supplier', 's');
        $sql->where('s.id_supplier IN (' . implode(',', array_map('intval', $ids)) . ')');

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        $suppliers = [];
        foreach ($results as $row) {
            $suppliers[(int) $row['id_supplier']] = [
                'id' => (int) $row['id_supplier'],
                'type' => 'supplier',
                'name' => $row['name'],
                'active' => (bool) $row['active'],
            ];
        }

        $ordered = [];
        foreach ($ids as $id) {
            if (isset($suppliers[(int) $id])) {
                $ordered[] = $suppliers[(int) $id];
            }
        }

        return $ordered;
    }

    // =========================================================================
    // CMS PAGES
    // =========================================================================

    /**
     * Search CMS pages
     */
    public function searchTargetCms($query, $idLang, $idShop, $limit = 20, $offset = 0, array $filters = [])
    {
        $sql = new DbQuery();
        $sql->select('DISTINCT c.id_cms, cl.meta_title, c.active, c.position, c.id_cms_category');
        $sql->select('ccl.name AS category_name');
        $sql->from('cms', 'c');
        $sql->innerJoin('cms_shop', 'cs', 'cs.id_cms = c.id_cms AND cs.id_shop = ' . (int) $idShop);
        $sql->leftJoin('cms_lang', 'cl', 'cl.id_cms = c.id_cms AND cl.id_lang = ' . (int) $idLang . ' AND cl.id_shop = ' . (int) $idShop);
        $sql->leftJoin('cms_category_lang', 'ccl', 'ccl.id_cms_category = c.id_cms_category AND ccl.id_lang = ' . (int) $idLang . ' AND ccl.id_shop = ' . (int) $idShop);

        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(cl.meta_title LIKE \'%' . $escapedQuery . '%\' OR c.id_cms = ' . (int) $query . ')');
        }

        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('cl.meta_title NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('cl.meta_title LIKE \'%' . $refine . '%\'');
            }
        }

        if (!empty($filters['active_only'])) {
            $sql->where('c.active = 1');
        }

        // Sorting
        $cmsSortMap = [
            'name' => 'cl.meta_title',
            'id' => 'c.id_cms',
            'position' => 'c.position',
        ];
        $sql->orderBy($this->buildOrderBy('cms', $filters, $cmsSortMap));
        $sql->limit((int) $limit, (int) $offset);

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        return array_map(function ($row) {
            return [
                'id' => (int) $row['id_cms'],
                'type' => 'cms',
                'name' => $row['meta_title'],
                'active' => (bool) $row['active'],
                'position' => (int) $row['position'],
                'category' => $row['category_name'],
            ];
        }, $results);
    }

    /**
     * Count CMS pages
     */
    public function countTargetCms($query, $idLang, $idShop, array $filters = [])
    {
        $sql = new DbQuery();
        $sql->select('COUNT(DISTINCT c.id_cms)');
        $sql->from('cms', 'c');
        $sql->innerJoin('cms_shop', 'cs', 'cs.id_cms = c.id_cms AND cs.id_shop = ' . (int) $idShop);
        $sql->leftJoin('cms_lang', 'cl', 'cl.id_cms = c.id_cms AND cl.id_lang = ' . (int) $idLang . ' AND cl.id_shop = ' . (int) $idShop);

        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(cl.meta_title LIKE \'%' . $escapedQuery . '%\' OR c.id_cms = ' . (int) $query . ')');
        }

        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('cl.meta_title NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('cl.meta_title LIKE \'%' . $refine . '%\'');
            }
        }

        if (!empty($filters['active_only'])) {
            $sql->where('c.active = 1');
        }

        return (int) Db::getInstance()->getValue($sql);
    }

    /**
     * Get CMS pages by IDs
     */
    public function getTargetCmsByIds(array $ids, $idLang)
    {
        if (empty($ids)) {
            return [];
        }

        $sql = new DbQuery();
        $sql->select('c.id_cms, cl.meta_title, c.active');
        $sql->from('cms', 'c');
        $sql->leftJoin('cms_lang', 'cl', 'cl.id_cms = c.id_cms AND cl.id_lang = ' . (int) $idLang . ' AND cl.id_shop = ' . (int) $this->idShop);
        $sql->where('c.id_cms IN (' . implode(',', array_map('intval', $ids)) . ')');

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        $cms = [];
        foreach ($results as $row) {
            $cms[(int) $row['id_cms']] = [
                'id' => (int) $row['id_cms'],
                'type' => 'cms',
                'name' => $row['meta_title'],
                'active' => (bool) $row['active'],
            ];
        }

        $ordered = [];
        foreach ($ids as $id) {
            if (isset($cms[(int) $id])) {
                $ordered[] = $cms[(int) $id];
            }
        }

        return $ordered;
    }

    // =========================================================================
    // CMS CATEGORIES
    // =========================================================================

    /**
     * Search CMS categories
     */
    public function searchTargetCmsCategories($query, $idLang, $idShop, $limit = 20, $offset = 0, array $filters = [])
    {
        $sql = new DbQuery();
        $sql->select('DISTINCT cc.id_cms_category, ccl.name, cc.active, cc.level_depth, cc.position');
        $sql->from('cms_category', 'cc');
        $sql->innerJoin('cms_category_shop', 'ccs', 'ccs.id_cms_category = cc.id_cms_category AND ccs.id_shop = ' . (int) $idShop);
        $sql->leftJoin('cms_category_lang', 'ccl', 'ccl.id_cms_category = cc.id_cms_category AND ccl.id_lang = ' . (int) $idLang . ' AND ccl.id_shop = ' . (int) $idShop);

        // Exclude root
        $sql->where('cc.id_parent > 0');

        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(ccl.name LIKE \'%' . $escapedQuery . '%\' OR cc.id_cms_category = ' . (int) $query . ')');
        }

        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('ccl.name NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('ccl.name LIKE \'%' . $refine . '%\'');
            }
        }

        if (!empty($filters['active_only'])) {
            $sql->where('cc.active = 1');
        }

        // Sorting
        $cmsCategorySortMap = [
            'name' => 'ccl.name',
            'id' => 'cc.id_cms_category',
            'position' => 'cc.position',
        ];
        $sql->orderBy($this->buildOrderBy('cms_categories', $filters, $cmsCategorySortMap));
        $sql->limit((int) $limit, (int) $offset);

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        return array_map(function ($row) {
            return [
                'id' => (int) $row['id_cms_category'],
                'type' => 'cms_category',
                'name' => $row['name'],
                'active' => (bool) $row['active'],
                'level_depth' => (int) $row['level_depth'],
                'position' => (int) $row['position'],
            ];
        }, $results);
    }

    /**
     * Count CMS categories
     */
    public function countTargetCmsCategories($query, $idLang, $idShop, array $filters = [])
    {
        $sql = new DbQuery();
        $sql->select('COUNT(DISTINCT cc.id_cms_category)');
        $sql->from('cms_category', 'cc');
        $sql->innerJoin('cms_category_shop', 'ccs', 'ccs.id_cms_category = cc.id_cms_category AND ccs.id_shop = ' . (int) $idShop);
        $sql->leftJoin('cms_category_lang', 'ccl', 'ccl.id_cms_category = cc.id_cms_category AND ccl.id_lang = ' . (int) $idLang . ' AND ccl.id_shop = ' . (int) $idShop);
        $sql->where('cc.id_parent > 0');

        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(ccl.name LIKE \'%' . $escapedQuery . '%\' OR cc.id_cms_category = ' . (int) $query . ')');
        }

        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('ccl.name NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('ccl.name LIKE \'%' . $refine . '%\'');
            }
        }

        if (!empty($filters['active_only'])) {
            $sql->where('cc.active = 1');
        }

        return (int) Db::getInstance()->getValue($sql);
    }

    /**
     * Get CMS categories by IDs
     */
    public function getTargetCmsCategoriesByIds(array $ids, $idLang)
    {
        if (empty($ids)) {
            return [];
        }

        $sql = new DbQuery();
        $sql->select('cc.id_cms_category, ccl.name, cc.active');
        $sql->from('cms_category', 'cc');
        $sql->leftJoin('cms_category_lang', 'ccl', 'ccl.id_cms_category = cc.id_cms_category AND ccl.id_lang = ' . (int) $idLang . ' AND ccl.id_shop = ' . (int) $this->idShop);
        $sql->where('cc.id_cms_category IN (' . implode(',', array_map('intval', $ids)) . ')');

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        $categories = [];
        foreach ($results as $row) {
            $categories[(int) $row['id_cms_category']] = [
                'id' => (int) $row['id_cms_category'],
                'type' => 'cms_category',
                'name' => $row['name'],
                'active' => (bool) $row['active'],
            ];
        }

        $ordered = [];
        foreach ($ids as $id) {
            if (isset($categories[(int) $id])) {
                $ordered[] = $categories[(int) $id];
            }
        }

        return $ordered;
    }

    // =========================================================================
    // EMPLOYEES
    // =========================================================================

    /**
     * Search employees
     */
    public function searchTargetEmployees($query, $idLang, $idShop, array $filters = [])
    {
        $limit = isset($filters['limit']) ? (int) $filters['limit'] : 20;

        $sql = new DbQuery();
        $sql->select('DISTINCT e.id_employee, e.firstname, e.lastname, e.email, e.active, e.id_profile');
        $sql->select('pl.name AS profile_name');
        $sql->from('employee', 'e');
        $sql->leftJoin('profile_lang', 'pl', 'pl.id_profile = e.id_profile AND pl.id_lang = ' . (int) $idLang);

        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(e.firstname LIKE \'%' . $escapedQuery . '%\' OR e.lastname LIKE \'%' . $escapedQuery . '%\' OR e.email LIKE \'%' . $escapedQuery . '%\' OR e.id_employee = ' . (int) $query . ')');
        }

        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('CONCAT(e.firstname, \' \', e.lastname) NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('CONCAT(e.firstname, \' \', e.lastname) LIKE \'%' . $refine . '%\'');
            }
        }

        if (!empty($filters['active_only'])) {
            $sql->where('e.active = 1');
        }

        // Sorting
        $employeeSortMap = [
            'name' => 'e.lastname',
            'id' => 'e.id_employee',
        ];
        $sql->orderBy($this->buildOrderBy('employees', $filters, $employeeSortMap));
        $sql->limit($limit);

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        return array_map(function ($row) {
            return [
                'id' => (int) $row['id_employee'],
                'type' => 'employee',
                'name' => $row['firstname'] . ' ' . $row['lastname'],
                'email' => $row['email'],
                'active' => (bool) $row['active'],
                'profile' => $row['profile_name'],
            ];
        }, $results);
    }

    /**
     * Count employees
     */
    public function countTargetEmployees($query, $idLang, $idShop, array $filters = [])
    {
        $sql = new DbQuery();
        $sql->select('COUNT(DISTINCT e.id_employee)');
        $sql->from('employee', 'e');

        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(e.firstname LIKE \'%' . $escapedQuery . '%\' OR e.lastname LIKE \'%' . $escapedQuery . '%\' OR e.email LIKE \'%' . $escapedQuery . '%\' OR e.id_employee = ' . (int) $query . ')');
        }

        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('CONCAT(e.firstname, \' \', e.lastname) NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('CONCAT(e.firstname, \' \', e.lastname) LIKE \'%' . $refine . '%\'');
            }
        }

        if (!empty($filters['active_only'])) {
            $sql->where('e.active = 1');
        }

        return (int) Db::getInstance()->getValue($sql);
    }

    /**
     * Get employees by IDs
     */
    public function getTargetEmployeesByIds(array $ids, $idLang)
    {
        if (empty($ids)) {
            return [];
        }

        $sql = new DbQuery();
        $sql->select('e.id_employee, e.firstname, e.lastname, e.email, e.active');
        $sql->from('employee', 'e');
        $sql->where('e.id_employee IN (' . implode(',', array_map('intval', $ids)) . ')');

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        $employees = [];
        foreach ($results as $row) {
            $employees[(int) $row['id_employee']] = [
                'id' => (int) $row['id_employee'],
                'type' => 'employee',
                'name' => $row['firstname'] . ' ' . $row['lastname'],
                'email' => $row['email'],
                'active' => (bool) $row['active'],
            ];
        }

        $ordered = [];
        foreach ($ids as $id) {
            if (isset($employees[(int) $id])) {
                $ordered[] = $employees[(int) $id];
            }
        }

        return $ordered;
    }

    // =========================================================================
    // CUSTOMERS
    // =========================================================================

    /**
     * Search customers
     */
    public function searchTargetCustomers($query, $idLang, $idShop, $limit = 20, $offset = 0, array $filters = [])
    {
        $sql = new DbQuery();
        $sql->select('DISTINCT c.id_customer, c.firstname, c.lastname, c.email, c.active, c.newsletter, c.company');
        $sql->select('c.date_add, gl.name AS group_name');
        $sql->from('customer', 'c');
        $sql->leftJoin('group_lang', 'gl', 'gl.id_group = c.id_default_group AND gl.id_lang = ' . (int) $idLang);
        $sql->where('c.id_shop = ' . (int) $idShop);
        $sql->where('c.deleted = 0');

        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(c.firstname LIKE \'%' . $escapedQuery . '%\' OR c.lastname LIKE \'%' . $escapedQuery . '%\' OR c.email LIKE \'%' . $escapedQuery . '%\' OR c.company LIKE \'%' . $escapedQuery . '%\' OR c.id_customer = ' . (int) $query . ')');
        }

        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('CONCAT(c.firstname, \' \', c.lastname) NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('CONCAT(c.firstname, \' \', c.lastname) LIKE \'%' . $refine . '%\'');
            }
        }

        if (!empty($filters['active_only'])) {
            $sql->where('c.active = 1');
        }

        // Sorting
        $customerSortMap = [
            'name' => 'c.lastname',
            'id' => 'c.id_customer',
        ];
        $sql->orderBy($this->buildOrderBy('customers', $filters, $customerSortMap));
        $sql->limit((int) $limit, (int) $offset);

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        return array_map(function ($row) {
            return [
                'id' => (int) $row['id_customer'],
                'type' => 'customer',
                'name' => $row['firstname'] . ' ' . $row['lastname'],
                'email' => $row['email'],
                'company' => $row['company'],
                'active' => (bool) $row['active'],
                'newsletter' => (bool) $row['newsletter'],
                'group' => $row['group_name'],
                'date_add' => $row['date_add'],
            ];
        }, $results);
    }

    /**
     * Count customers
     */
    public function countTargetCustomers($query, $idLang, $idShop, array $filters = [])
    {
        $sql = new DbQuery();
        $sql->select('COUNT(DISTINCT c.id_customer)');
        $sql->from('customer', 'c');
        $sql->where('c.id_shop = ' . (int) $idShop);
        $sql->where('c.deleted = 0');

        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(c.firstname LIKE \'%' . $escapedQuery . '%\' OR c.lastname LIKE \'%' . $escapedQuery . '%\' OR c.email LIKE \'%' . $escapedQuery . '%\' OR c.company LIKE \'%' . $escapedQuery . '%\' OR c.id_customer = ' . (int) $query . ')');
        }

        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('CONCAT(c.firstname, \' \', c.lastname) NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('CONCAT(c.firstname, \' \', c.lastname) LIKE \'%' . $refine . '%\'');
            }
        }

        if (!empty($filters['active_only'])) {
            $sql->where('c.active = 1');
        }

        return (int) Db::getInstance()->getValue($sql);
    }

    /**
     * Get customers by IDs
     */
    public function getTargetCustomersByIds(array $ids, $idLang)
    {
        if (empty($ids)) {
            return [];
        }

        $sql = new DbQuery();
        $sql->select('c.id_customer, c.firstname, c.lastname, c.email, c.active');
        $sql->from('customer', 'c');
        $sql->where('c.id_customer IN (' . implode(',', array_map('intval', $ids)) . ')');

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        $customers = [];
        foreach ($results as $row) {
            $customers[(int) $row['id_customer']] = [
                'id' => (int) $row['id_customer'],
                'type' => 'customer',
                'name' => $row['firstname'] . ' ' . $row['lastname'],
                'email' => $row['email'],
                'active' => (bool) $row['active'],
            ];
        }

        $ordered = [];
        foreach ($ids as $id) {
            if (isset($customers[(int) $id])) {
                $ordered[] = $customers[(int) $id];
            }
        }

        return $ordered;
    }

    // =========================================================================
    // CUSTOMER GROUPS
    // =========================================================================

    /**
     * Search customer groups
     */
    public function searchTargetCustomerGroups($query, $idLang, $idShop, array $filters = [])
    {
        $limit = isset($filters['limit']) ? (int) $filters['limit'] : 20;

        $sql = new DbQuery();
        $sql->select('DISTINCT g.id_group, gl.name, g.reduction, g.price_display_method, g.show_prices');
        $sql->select('(SELECT COUNT(cg.id_customer) FROM ' . _DB_PREFIX_ . 'customer_group cg WHERE cg.id_group = g.id_group) AS customer_count');
        $sql->from('group', 'g');
        $sql->innerJoin('group_shop', 'gs', 'gs.id_group = g.id_group AND gs.id_shop = ' . (int) $idShop);
        $sql->leftJoin('group_lang', 'gl', 'gl.id_group = g.id_group AND gl.id_lang = ' . (int) $idLang);

        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(gl.name LIKE \'%' . $escapedQuery . '%\' OR g.id_group = ' . (int) $query . ')');
        }

        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('gl.name NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('gl.name LIKE \'%' . $refine . '%\'');
            }
        }

        // Sorting
        $customerGroupSortMap = [
            'name' => 'gl.name',
            'id' => 'g.id_group',
        ];
        $sql->orderBy($this->buildOrderBy('customer_groups', $filters, $customerGroupSortMap));
        $sql->limit($limit);

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        return array_map(function ($row) {
            return [
                'id' => (int) $row['id_group'],
                'type' => 'customer_group',
                'name' => $row['name'],
                'reduction' => (float) $row['reduction'],
                'price_display' => (int) $row['price_display_method'],
                'show_prices' => (bool) $row['show_prices'],
                'customer_count' => (int) $row['customer_count'],
            ];
        }, $results);
    }

    /**
     * Count customer groups
     */
    public function countTargetCustomerGroups($query, $idLang, $idShop, array $filters = [])
    {
        $sql = new DbQuery();
        $sql->select('COUNT(DISTINCT g.id_group)');
        $sql->from('group', 'g');
        $sql->innerJoin('group_shop', 'gs', 'gs.id_group = g.id_group AND gs.id_shop = ' . (int) $idShop);
        $sql->leftJoin('group_lang', 'gl', 'gl.id_group = g.id_group AND gl.id_lang = ' . (int) $idLang);

        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(gl.name LIKE \'%' . $escapedQuery . '%\' OR g.id_group = ' . (int) $query . ')');
        }

        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('gl.name NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('gl.name LIKE \'%' . $refine . '%\'');
            }
        }

        return (int) Db::getInstance()->getValue($sql);
    }

    /**
     * Get customer groups by IDs
     */
    public function getTargetCustomerGroupsByIds(array $ids, $idLang)
    {
        if (empty($ids)) {
            return [];
        }

        $sql = new DbQuery();
        $sql->select('g.id_group, gl.name');
        $sql->from('group', 'g');
        $sql->leftJoin('group_lang', 'gl', 'gl.id_group = g.id_group AND gl.id_lang = ' . (int) $idLang);
        $sql->where('g.id_group IN (' . implode(',', array_map('intval', $ids)) . ')');

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        $groups = [];
        foreach ($results as $row) {
            $groups[(int) $row['id_group']] = [
                'id' => (int) $row['id_group'],
                'type' => 'customer_group',
                'name' => $row['name'],
            ];
        }

        $ordered = [];
        foreach ($ids as $id) {
            if (isset($groups[(int) $id])) {
                $ordered[] = $groups[(int) $id];
            }
        }

        return $ordered;
    }

    // =========================================================================
    // CARRIERS
    // =========================================================================

    /**
     * Search carriers
     */
    public function searchTargetCarriers($query, $idLang, $idShop, array $filters = [])
    {
        $limit = isset($filters['limit']) ? (int) $filters['limit'] : 20;

        $sql = new DbQuery();
        $sql->select('DISTINCT c.id_carrier, c.id_reference, c.name, c.active, c.is_free, c.shipping_handling');
        $sql->from('carrier', 'c');
        $sql->where('c.deleted = 0');

        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(c.name LIKE \'%' . $escapedQuery . '%\' OR c.id_carrier = ' . (int) $query . ')');
        }

        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('c.name NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('c.name LIKE \'%' . $refine . '%\'');
            }
        }

        if (!empty($filters['active_only'])) {
            $sql->where('c.active = 1');
        }

        // Sorting
        $carrierSortMap = [
            'name' => 'c.name',
            'id' => 'c.id_carrier',
            'position' => 'c.position',
        ];
        $sql->orderBy($this->buildOrderBy('carriers', $filters, $carrierSortMap));
        $sql->limit($limit);

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        return array_map(function ($row) {
            return [
                'id' => (int) $row['id_carrier'],
                'type' => 'carrier',
                'id_reference' => (int) $row['id_reference'],
                'name' => $row['name'],
                'active' => (bool) $row['active'],
                'is_free' => (bool) $row['is_free'],
                'shipping_handling' => (bool) $row['shipping_handling'],
            ];
        }, $results);
    }

    /**
     * Count carriers
     */
    public function countTargetCarriers($query, $idLang, $idShop, array $filters = [])
    {
        $sql = new DbQuery();
        $sql->select('COUNT(DISTINCT c.id_carrier)');
        $sql->from('carrier', 'c');
        $sql->where('c.deleted = 0');

        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(c.name LIKE \'%' . $escapedQuery . '%\' OR c.id_carrier = ' . (int) $query . ')');
        }

        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('c.name NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('c.name LIKE \'%' . $refine . '%\'');
            }
        }

        if (!empty($filters['active_only'])) {
            $sql->where('c.active = 1');
        }

        return (int) Db::getInstance()->getValue($sql);
    }

    /**
     * Get carriers by IDs
     */
    public function getTargetCarriersByIds(array $ids, $idLang)
    {
        if (empty($ids)) {
            return [];
        }

        $sql = new DbQuery();
        $sql->select('c.id_carrier, c.name, c.active');
        $sql->from('carrier', 'c');
        $sql->where('c.id_carrier IN (' . implode(',', array_map('intval', $ids)) . ')');

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        $carriers = [];
        foreach ($results as $row) {
            $carriers[(int) $row['id_carrier']] = [
                'id' => (int) $row['id_carrier'],
                'type' => 'carrier',
                'name' => $row['name'],
                'active' => (bool) $row['active'],
            ];
        }

        $ordered = [];
        foreach ($ids as $id) {
            if (isset($carriers[(int) $id])) {
                $ordered[] = $carriers[(int) $id];
            }
        }

        return $ordered;
    }

    // =========================================================================
    // ZONES
    // =========================================================================

    /**
     * Search zones
     */
    public function searchTargetZones($query, $idLang, $idShop, array $filters = [])
    {
        $limit = isset($filters['limit']) ? (int) $filters['limit'] : 20;

        $sql = new DbQuery();
        $sql->select('DISTINCT z.id_zone, z.name, z.active');
        $sql->from('zone', 'z');
        $sql->innerJoin('zone_shop', 'zs', 'zs.id_zone = z.id_zone AND zs.id_shop = ' . (int) $idShop);

        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(z.name LIKE \'%' . $escapedQuery . '%\' OR z.id_zone = ' . (int) $query . ')');
        }

        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('z.name NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('z.name LIKE \'%' . $refine . '%\'');
            }
        }

        if (!empty($filters['active_only'])) {
            $sql->where('z.active = 1');
        }

        // Sorting
        $zoneSortMap = [
            'name' => 'z.name',
            'id' => 'z.id_zone',
        ];
        $sql->orderBy($this->buildOrderBy('zones', $filters, $zoneSortMap));
        $sql->limit($limit);

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        return array_map(function ($row) {
            return [
                'id' => (int) $row['id_zone'],
                'type' => 'zone',
                'name' => $row['name'],
                'active' => (bool) $row['active'],
            ];
        }, $results);
    }

    /**
     * Count zones
     */
    public function countTargetZones($query, $idLang, $idShop, array $filters = [])
    {
        $sql = new DbQuery();
        $sql->select('COUNT(DISTINCT z.id_zone)');
        $sql->from('zone', 'z');
        $sql->innerJoin('zone_shop', 'zs', 'zs.id_zone = z.id_zone AND zs.id_shop = ' . (int) $idShop);

        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(z.name LIKE \'%' . $escapedQuery . '%\' OR z.id_zone = ' . (int) $query . ')');
        }

        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('z.name NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('z.name LIKE \'%' . $refine . '%\'');
            }
        }

        if (!empty($filters['active_only'])) {
            $sql->where('z.active = 1');
        }

        return (int) Db::getInstance()->getValue($sql);
    }

    /**
     * Get zones by IDs
     */
    public function getTargetZonesByIds(array $ids, $idLang)
    {
        if (empty($ids)) {
            return [];
        }

        $sql = new DbQuery();
        $sql->select('z.id_zone, z.name, z.active');
        $sql->from('zone', 'z');
        $sql->where('z.id_zone IN (' . implode(',', array_map('intval', $ids)) . ')');

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        $zones = [];
        foreach ($results as $row) {
            $zones[(int) $row['id_zone']] = [
                'id' => (int) $row['id_zone'],
                'type' => 'zone',
                'name' => $row['name'],
                'active' => (bool) $row['active'],
            ];
        }

        $ordered = [];
        foreach ($ids as $id) {
            if (isset($zones[(int) $id])) {
                $ordered[] = $zones[(int) $id];
            }
        }

        return $ordered;
    }

    // =========================================================================
    // COUNTRIES
    // =========================================================================

    /**
     * Search countries
     */
    public function searchTargetCountries($query, $idLang, $idShop, $limit = 20, $offset = 0, array $filters = [])
    {

        $sql = new DbQuery();
        $sql->select('DISTINCT c.id_country, cl.name, c.iso_code, c.active, c.id_zone, c.contains_states, c.need_zip_code');
        $sql->select('z.name AS zone_name');
        $sql->from('country', 'c');
        $sql->innerJoin('country_shop', 'cs', 'cs.id_country = c.id_country AND cs.id_shop = ' . (int) $idShop);
        $sql->leftJoin('country_lang', 'cl', 'cl.id_country = c.id_country AND cl.id_lang = ' . (int) $idLang);
        $sql->leftJoin('zone', 'z', 'z.id_zone = c.id_zone');

        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(cl.name LIKE \'%' . $escapedQuery . '%\' OR c.iso_code LIKE \'%' . $escapedQuery . '%\' OR c.id_country = ' . (int) $query . ')');
        }

        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('cl.name NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('cl.name LIKE \'%' . $refine . '%\'');
            }
        }

        if (!empty($filters['active_only'])) {
            $sql->where('c.active = 1');
        }

        // Zone filter
        if (!empty($filters['zone'])) {
            $sql->where('c.id_zone = ' . (int) $filters['zone']);
        }

        // Contains states filter
        if (!empty($filters['contains_states'])) {
            $sql->where('c.contains_states = 1');
        }

        // Has holidays filter - check if PublicHoliday class exists and country has holidays
        if (!empty($filters['has_holidays'])) {
            if (class_exists('MyPrestaRocks\\PublicHolidays\\PublicHoliday')) {
                $countriesWithHolidays = \MyPrestaRocks\PublicHolidays\PublicHoliday::getCountriesWithHolidays();
                if (!empty($countriesWithHolidays)) {
                    $sql->where('c.id_country IN (' . implode(',', array_map('intval', $countriesWithHolidays)) . ')');
                } else {
                    // No countries with holidays, return empty
                    $sql->where('1 = 0');
                }
            }
        }

        // Sorting
        $countrySortMap = [
            'name' => 'cl.name',
            'id' => 'c.id_country',
            'zone' => 'z.name',
            'iso_code' => 'c.iso_code',
        ];
        $sql->orderBy($this->buildOrderBy('countries', $filters, $countrySortMap));
        $sql->limit($limit, $offset);

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        return array_map(function ($row) {
            return [
                'id' => (int) $row['id_country'],
                'type' => 'country',
                'name' => $row['name'],
                'iso_code' => $row['iso_code'],
                'active' => (bool) $row['active'],
                'zone' => $row['zone_name'],
                'contains_states' => (bool) $row['contains_states'],
                'need_zip_code' => (bool) $row['need_zip_code'],
            ];
        }, $results);
    }

    /**
     * Count countries
     */
    public function countTargetCountries($query, $idLang, $idShop, array $filters = [])
    {
        $sql = new DbQuery();
        $sql->select('COUNT(DISTINCT c.id_country)');
        $sql->from('country', 'c');
        $sql->innerJoin('country_shop', 'cs', 'cs.id_country = c.id_country AND cs.id_shop = ' . (int) $idShop);
        $sql->leftJoin('country_lang', 'cl', 'cl.id_country = c.id_country AND cl.id_lang = ' . (int) $idLang);

        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(cl.name LIKE \'%' . $escapedQuery . '%\' OR c.iso_code LIKE \'%' . $escapedQuery . '%\' OR c.id_country = ' . (int) $query . ')');
        }

        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('cl.name NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('cl.name LIKE \'%' . $refine . '%\'');
            }
        }

        if (!empty($filters['active_only'])) {
            $sql->where('c.active = 1');
        }

        // Zone filter
        if (!empty($filters['zone'])) {
            $sql->where('c.id_zone = ' . (int) $filters['zone']);
        }

        // Contains states filter
        if (!empty($filters['contains_states'])) {
            $sql->where('c.contains_states = 1');
        }

        // Has holidays filter
        if (!empty($filters['has_holidays'])) {
            if (class_exists('MyPrestaRocks\\PublicHolidays\\PublicHoliday')) {
                $countriesWithHolidays = \MyPrestaRocks\PublicHolidays\PublicHoliday::getCountriesWithHolidays();
                if (!empty($countriesWithHolidays)) {
                    $sql->where('c.id_country IN (' . implode(',', array_map('intval', $countriesWithHolidays)) . ')');
                } else {
                    $sql->where('1 = 0');
                }
            }
        }

        return (int) Db::getInstance()->getValue($sql);
    }

    /**
     * Get countries by IDs
     */
    public function getTargetCountriesByIds(array $ids, $idLang)
    {
        if (empty($ids)) {
            return [];
        }

        $sql = new DbQuery();
        $sql->select('c.id_country, cl.name, c.iso_code, c.active');
        $sql->from('country', 'c');
        $sql->leftJoin('country_lang', 'cl', 'cl.id_country = c.id_country AND cl.id_lang = ' . (int) $idLang);
        $sql->where('c.id_country IN (' . implode(',', array_map('intval', $ids)) . ')');

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        $countries = [];
        foreach ($results as $row) {
            $countries[(int) $row['id_country']] = [
                'id' => (int) $row['id_country'],
                'type' => 'country',
                'name' => $row['name'],
                'iso_code' => $row['iso_code'],
                'active' => (bool) $row['active'],
            ];
        }

        $ordered = [];
        foreach ($ids as $id) {
            if (isset($countries[(int) $id])) {
                $ordered[] = $countries[(int) $id];
            }
        }

        return $ordered;
    }

    // =========================================================================
    // CURRENCIES
    // =========================================================================

    /**
     * Search currencies
     */
    public function searchTargetCurrencies($query, $idLang, $idShop, array $filters = [])
    {
        $limit = isset($filters['limit']) ? (int) $filters['limit'] : 20;

        $sql = new DbQuery();
        $sql->select('DISTINCT c.id_currency, c.iso_code, c.active, c.conversion_rate, cl.name, cl.symbol');
        $sql->from('currency', 'c');
        $sql->innerJoin('currency_shop', 'cs', 'cs.id_currency = c.id_currency AND cs.id_shop = ' . (int) $idShop);
        $sql->leftJoin('currency_lang', 'cl', 'cl.id_currency = c.id_currency AND cl.id_lang = ' . (int) $idLang);

        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(cl.name LIKE \'%' . $escapedQuery . '%\' OR c.iso_code LIKE \'%' . $escapedQuery . '%\' OR c.id_currency = ' . (int) $query . ')');
        }

        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('cl.name NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('cl.name LIKE \'%' . $refine . '%\'');
            }
        }

        if (!empty($filters['active_only'])) {
            $sql->where('c.active = 1');
        }

        // Sorting
        $currencySortMap = [
            'name' => 'cl.name',
            'id' => 'c.id_currency',
            'iso_code' => 'c.iso_code',
            'conversion_rate' => 'c.conversion_rate',
        ];
        $sql->orderBy($this->buildOrderBy('currencies', $filters, $currencySortMap));
        $sql->limit($limit);

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        return array_map(function ($row) {
            return [
                'id' => (int) $row['id_currency'],
                'type' => 'currency',
                'name' => $row['name'] ?: $row['iso_code'],
                'iso_code' => $row['iso_code'],
                'symbol' => $row['symbol'] ?: $row['iso_code'],
                'active' => (bool) $row['active'],
                'conversion_rate' => (float) $row['conversion_rate'],
            ];
        }, $results);
    }

    /**
     * Count currencies
     */
    public function countTargetCurrencies($query, $idLang, $idShop, array $filters = [])
    {
        $sql = new DbQuery();
        $sql->select('COUNT(DISTINCT c.id_currency)');
        $sql->from('currency', 'c');
        $sql->innerJoin('currency_shop', 'cs', 'cs.id_currency = c.id_currency AND cs.id_shop = ' . (int) $idShop);
        $sql->leftJoin('currency_lang', 'cl', 'cl.id_currency = c.id_currency AND cl.id_lang = ' . (int) $idLang);

        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(cl.name LIKE \'%' . $escapedQuery . '%\' OR c.iso_code LIKE \'%' . $escapedQuery . '%\' OR c.id_currency = ' . (int) $query . ')');
        }

        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('cl.name NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('cl.name LIKE \'%' . $refine . '%\'');
            }
        }

        if (!empty($filters['active_only'])) {
            $sql->where('c.active = 1');
        }

        return (int) Db::getInstance()->getValue($sql);
    }

    /**
     * Get currencies by IDs
     */
    public function getTargetCurrenciesByIds(array $ids, $idLang)
    {
        if (empty($ids)) {
            return [];
        }

        $sql = new DbQuery();
        $sql->select('c.id_currency, c.iso_code, c.active, cl.name, cl.symbol');
        $sql->from('currency', 'c');
        $sql->leftJoin('currency_lang', 'cl', 'cl.id_currency = c.id_currency AND cl.id_lang = ' . (int) $idLang);
        $sql->where('c.id_currency IN (' . implode(',', array_map('intval', $ids)) . ')');

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        $currencies = [];
        foreach ($results as $row) {
            $currencies[(int) $row['id_currency']] = [
                'id' => (int) $row['id_currency'],
                'type' => 'currency',
                'name' => $row['name'] ?: $row['iso_code'],
                'iso_code' => $row['iso_code'],
                'symbol' => $row['symbol'] ?: $row['iso_code'],
                'active' => (bool) $row['active'],
            ];
        }

        $ordered = [];
        foreach ($ids as $id) {
            if (isset($currencies[(int) $id])) {
                $ordered[] = $currencies[(int) $id];
            }
        }

        return $ordered;
    }

    // =========================================================================
    // LANGUAGES
    // =========================================================================

    /**
     * Search languages
     */
    public function searchTargetLanguages($query, $idLang, $idShop, array $filters = [])
    {
        $limit = isset($filters['limit']) ? (int) $filters['limit'] : 20;

        $sql = new DbQuery();
        $sql->select('DISTINCT l.id_lang, l.name, l.iso_code, l.active, l.is_rtl, l.locale');
        $sql->from('lang', 'l');
        $sql->innerJoin('lang_shop', 'ls', 'ls.id_lang = l.id_lang AND ls.id_shop = ' . (int) $idShop);

        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(l.name LIKE \'%' . $escapedQuery . '%\' OR l.iso_code LIKE \'%' . $escapedQuery . '%\' OR l.id_lang = ' . (int) $query . ')');
        }

        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('l.name NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('l.name LIKE \'%' . $refine . '%\'');
            }
        }

        if (!empty($filters['active_only'])) {
            $sql->where('l.active = 1');
        }

        // Sorting
        $languageSortMap = [
            'name' => 'l.name',
            'id' => 'l.id_lang',
            'iso_code' => 'l.iso_code',
        ];
        $sql->orderBy($this->buildOrderBy('languages', $filters, $languageSortMap));
        $sql->limit($limit);

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        return array_map(function ($row) {
            return [
                'id' => (int) $row['id_lang'],
                'type' => 'language',
                'name' => $row['name'],
                'iso_code' => $row['iso_code'],
                'locale' => $row['locale'],
                'active' => (bool) $row['active'],
                'is_rtl' => (bool) $row['is_rtl'],
            ];
        }, $results);
    }

    /**
     * Count languages
     */
    public function countTargetLanguages($query, $idLang, $idShop, array $filters = [])
    {
        $sql = new DbQuery();
        $sql->select('COUNT(DISTINCT l.id_lang)');
        $sql->from('lang', 'l');
        $sql->innerJoin('lang_shop', 'ls', 'ls.id_lang = l.id_lang AND ls.id_shop = ' . (int) $idShop);

        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(l.name LIKE \'%' . $escapedQuery . '%\' OR l.iso_code LIKE \'%' . $escapedQuery . '%\' OR l.id_lang = ' . (int) $query . ')');
        }

        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('l.name NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('l.name LIKE \'%' . $refine . '%\'');
            }
        }

        if (!empty($filters['active_only'])) {
            $sql->where('l.active = 1');
        }

        return (int) Db::getInstance()->getValue($sql);
    }

    /**
     * Get languages by IDs
     */
    public function getTargetLanguagesByIds(array $ids, $idLang)
    {
        if (empty($ids)) {
            return [];
        }

        $sql = new DbQuery();
        $sql->select('l.id_lang, l.name, l.iso_code, l.active');
        $sql->from('lang', 'l');
        $sql->where('l.id_lang IN (' . implode(',', array_map('intval', $ids)) . ')');

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        $languages = [];
        foreach ($results as $row) {
            $languages[(int) $row['id_lang']] = [
                'id' => (int) $row['id_lang'],
                'type' => 'language',
                'name' => $row['name'],
                'iso_code' => $row['iso_code'],
                'active' => (bool) $row['active'],
            ];
        }

        $ordered = [];
        foreach ($ids as $id) {
            if (isset($languages[(int) $id])) {
                $ordered[] = $languages[(int) $id];
            }
        }

        return $ordered;
    }

    // =========================================================================
    // SHOPS
    // =========================================================================

    /**
     * Search shops
     */
    public function searchTargetShops($query, $idLang, $idShop, array $filters = [])
    {
        $limit = isset($filters['limit']) ? (int) $filters['limit'] : 20;

        $sql = new DbQuery();
        $sql->select('DISTINCT s.id_shop, s.name, s.active, s.id_shop_group');
        $sql->select('sg.name AS group_name');
        $sql->from('shop', 's');
        $sql->leftJoin('shop_group', 'sg', 'sg.id_shop_group = s.id_shop_group');

        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(s.name LIKE \'%' . $escapedQuery . '%\' OR s.id_shop = ' . (int) $query . ')');
        }

        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('s.name NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('s.name LIKE \'%' . $refine . '%\'');
            }
        }

        if (!empty($filters['active_only'])) {
            $sql->where('s.active = 1');
        }

        // Sorting
        $shopSortMap = [
            'name' => 's.name',
            'id' => 's.id_shop',
            'group' => 'sg.name',
        ];
        $sql->orderBy($this->buildOrderBy('shops', $filters, $shopSortMap));
        $sql->limit($limit);

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        return array_map(function ($row) {
            return [
                'id' => (int) $row['id_shop'],
                'type' => 'shop',
                'name' => $row['name'],
                'active' => (bool) $row['active'],
                'group' => $row['group_name'],
            ];
        }, $results);
    }

    /**
     * Count shops
     */
    public function countTargetShops($query, $idLang, $idShop, array $filters = [])
    {
        $sql = new DbQuery();
        $sql->select('COUNT(DISTINCT s.id_shop)');
        $sql->from('shop', 's');

        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(s.name LIKE \'%' . $escapedQuery . '%\' OR s.id_shop = ' . (int) $query . ')');
        }

        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('s.name NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('s.name LIKE \'%' . $refine . '%\'');
            }
        }

        if (!empty($filters['active_only'])) {
            $sql->where('s.active = 1');
        }

        return (int) Db::getInstance()->getValue($sql);
    }

    /**
     * Get shops by IDs
     */
    public function getTargetShopsByIds(array $ids, $idLang)
    {
        if (empty($ids)) {
            return [];
        }

        $sql = new DbQuery();
        $sql->select('s.id_shop, s.name, s.active');
        $sql->from('shop', 's');
        $sql->where('s.id_shop IN (' . implode(',', array_map('intval', $ids)) . ')');

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        $shops = [];
        foreach ($results as $row) {
            $shops[(int) $row['id_shop']] = [
                'id' => (int) $row['id_shop'],
                'type' => 'shop',
                'name' => $row['name'],
                'active' => (bool) $row['active'],
            ];
        }

        $ordered = [];
        foreach ($ids as $id) {
            if (isset($shops[(int) $id])) {
                $ordered[] = $shops[(int) $id];
            }
        }

        return $ordered;
    }

    // =========================================================================
    // PROFILES
    // =========================================================================

    /**
     * Search profiles
     */
    public function searchTargetProfiles($query, $idLang, $idShop, array $filters = [])
    {
        $limit = isset($filters['limit']) ? (int) $filters['limit'] : 20;

        $sql = new DbQuery();
        $sql->select('DISTINCT p.id_profile, pl.name');
        $sql->from('profile', 'p');
        $sql->leftJoin('profile_lang', 'pl', 'pl.id_profile = p.id_profile AND pl.id_lang = ' . (int) $idLang);

        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(pl.name LIKE \'%' . $escapedQuery . '%\' OR p.id_profile = ' . (int) $query . ')');
        }

        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('pl.name NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('pl.name LIKE \'%' . $refine . '%\'');
            }
        }

        // Sorting
        $profileSortMap = [
            'name' => 'pl.name',
            'id' => 'p.id_profile',
        ];
        $sql->orderBy($this->buildOrderBy('profiles', $filters, $profileSortMap));
        $sql->limit($limit);

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        return array_map(function ($row) {
            return [
                'id' => (int) $row['id_profile'],
                'type' => 'profile',
                'name' => $row['name'],
            ];
        }, $results);
    }

    /**
     * Count profiles
     */
    public function countTargetProfiles($query, $idLang, $idShop, array $filters = [])
    {
        $sql = new DbQuery();
        $sql->select('COUNT(DISTINCT p.id_profile)');
        $sql->from('profile', 'p');
        $sql->leftJoin('profile_lang', 'pl', 'pl.id_profile = p.id_profile AND pl.id_lang = ' . (int) $idLang);

        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(pl.name LIKE \'%' . $escapedQuery . '%\' OR p.id_profile = ' . (int) $query . ')');
        }

        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('pl.name NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('pl.name LIKE \'%' . $refine . '%\'');
            }
        }

        return (int) Db::getInstance()->getValue($sql);
    }

    /**
     * Get profiles by IDs
     */
    public function getTargetProfilesByIds(array $ids, $idLang)
    {
        if (empty($ids)) {
            return [];
        }

        $sql = new DbQuery();
        $sql->select('p.id_profile, pl.name');
        $sql->from('profile', 'p');
        $sql->leftJoin('profile_lang', 'pl', 'pl.id_profile = p.id_profile AND pl.id_lang = ' . (int) $idLang);
        $sql->where('p.id_profile IN (' . implode(',', array_map('intval', $ids)) . ')');

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        $profiles = [];
        foreach ($results as $row) {
            $profiles[(int) $row['id_profile']] = [
                'id' => (int) $row['id_profile'],
                'type' => 'profile',
                'name' => $row['name'],
            ];
        }

        $ordered = [];
        foreach ($ids as $id) {
            if (isset($profiles[(int) $id])) {
                $ordered[] = $profiles[(int) $id];
            }
        }

        return $ordered;
    }

    // =========================================================================
    // ORDER STATES
    // =========================================================================

    /**
     * Search order states
     */
    public function searchTargetOrderStates($query, $idLang, $idShop, array $filters = [])
    {
        $limit = isset($filters['limit']) ? (int) $filters['limit'] : 20;

        $sql = new DbQuery();
        $sql->select('DISTINCT os.id_order_state, osl.name, os.color, os.paid, os.shipped, os.delivery, os.invoice');
        $sql->from('order_state', 'os');
        $sql->leftJoin('order_state_lang', 'osl', 'osl.id_order_state = os.id_order_state AND osl.id_lang = ' . (int) $idLang);
        $sql->where('os.deleted = 0');

        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(osl.name LIKE \'%' . $escapedQuery . '%\' OR os.id_order_state = ' . (int) $query . ')');
        }

        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('osl.name NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('osl.name LIKE \'%' . $refine . '%\'');
            }
        }

        // Sorting
        $orderStateSortMap = [
            'name' => 'osl.name',
            'id' => 'os.id_order_state',
        ];
        $sql->orderBy($this->buildOrderBy('order_states', $filters, $orderStateSortMap));
        $sql->limit($limit);

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        return array_map(function ($row) {
            return [
                'id' => (int) $row['id_order_state'],
                'type' => 'order_state',
                'name' => $row['name'],
                'color' => $row['color'],
                'paid' => (bool) $row['paid'],
                'shipped' => (bool) $row['shipped'],
                'delivery' => (bool) $row['delivery'],
                'invoice' => (bool) $row['invoice'],
            ];
        }, $results);
    }

    /**
     * Count order states
     */
    public function countTargetOrderStates($query, $idLang, $idShop, array $filters = [])
    {
        $sql = new DbQuery();
        $sql->select('COUNT(DISTINCT os.id_order_state)');
        $sql->from('order_state', 'os');
        $sql->leftJoin('order_state_lang', 'osl', 'osl.id_order_state = os.id_order_state AND osl.id_lang = ' . (int) $idLang);
        $sql->where('os.deleted = 0');

        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(osl.name LIKE \'%' . $escapedQuery . '%\' OR os.id_order_state = ' . (int) $query . ')');
        }

        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('osl.name NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('osl.name LIKE \'%' . $refine . '%\'');
            }
        }

        return (int) Db::getInstance()->getValue($sql);
    }

    /**
     * Get order states by IDs
     */
    public function getTargetOrderStatesByIds(array $ids, $idLang)
    {
        if (empty($ids)) {
            return [];
        }

        $sql = new DbQuery();
        $sql->select('os.id_order_state, osl.name, os.color');
        $sql->from('order_state', 'os');
        $sql->leftJoin('order_state_lang', 'osl', 'osl.id_order_state = os.id_order_state AND osl.id_lang = ' . (int) $idLang);
        $sql->where('os.id_order_state IN (' . implode(',', array_map('intval', $ids)) . ')');

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        $states = [];
        foreach ($results as $row) {
            $states[(int) $row['id_order_state']] = [
                'id' => (int) $row['id_order_state'],
                'type' => 'order_state',
                'name' => $row['name'],
                'color' => $row['color'],
            ];
        }

        $ordered = [];
        foreach ($ids as $id) {
            if (isset($states[(int) $id])) {
                $ordered[] = $states[(int) $id];
            }
        }

        return $ordered;
    }

    // =========================================================================
    // TAXES
    // =========================================================================

    /**
     * Search taxes
     */
    public function searchTargetTaxes($query, $idLang, $idShop, array $filters = [])
    {
        $limit = isset($filters['limit']) ? (int) $filters['limit'] : 20;

        $sql = new DbQuery();
        $sql->select('DISTINCT t.id_tax, tl.name, t.rate, t.active');
        $sql->from('tax', 't');
        $sql->leftJoin('tax_lang', 'tl', 'tl.id_tax = t.id_tax AND tl.id_lang = ' . (int) $idLang);

        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(tl.name LIKE \'%' . $escapedQuery . '%\' OR t.id_tax = ' . (int) $query . ')');
        }

        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('tl.name NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('tl.name LIKE \'%' . $refine . '%\'');
            }
        }

        if (!empty($filters['active_only'])) {
            $sql->where('t.active = 1');
        }

        // Sorting
        $taxSortMap = [
            'name' => 'tl.name',
            'id' => 't.id_tax',
            'rate' => 't.rate',
        ];
        $sql->orderBy($this->buildOrderBy('taxes', $filters, $taxSortMap));
        $sql->limit($limit);

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        return array_map(function ($row) {
            return [
                'id' => (int) $row['id_tax'],
                'type' => 'tax',
                'name' => $row['name'],
                'rate' => (float) $row['rate'],
                'active' => (bool) $row['active'],
            ];
        }, $results);
    }

    /**
     * Count taxes
     */
    public function countTargetTaxes($query, $idLang, $idShop, array $filters = [])
    {
        $sql = new DbQuery();
        $sql->select('COUNT(DISTINCT t.id_tax)');
        $sql->from('tax', 't');
        $sql->leftJoin('tax_lang', 'tl', 'tl.id_tax = t.id_tax AND tl.id_lang = ' . (int) $idLang);

        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(tl.name LIKE \'%' . $escapedQuery . '%\' OR t.id_tax = ' . (int) $query . ')');
        }

        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('tl.name NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('tl.name LIKE \'%' . $refine . '%\'');
            }
        }

        if (!empty($filters['active_only'])) {
            $sql->where('t.active = 1');
        }

        return (int) Db::getInstance()->getValue($sql);
    }

    /**
     * Get taxes by IDs
     */
    public function getTargetTaxesByIds(array $ids, $idLang)
    {
        if (empty($ids)) {
            return [];
        }

        $sql = new DbQuery();
        $sql->select('t.id_tax, tl.name, t.rate, t.active');
        $sql->from('tax', 't');
        $sql->leftJoin('tax_lang', 'tl', 'tl.id_tax = t.id_tax AND tl.id_lang = ' . (int) $idLang);
        $sql->where('t.id_tax IN (' . implode(',', array_map('intval', $ids)) . ')');

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        $taxes = [];
        foreach ($results as $row) {
            $taxes[(int) $row['id_tax']] = [
                'id' => (int) $row['id_tax'],
                'type' => 'tax',
                'name' => $row['name'],
                'rate' => (float) $row['rate'],
                'active' => (bool) $row['active'],
            ];
        }

        $ordered = [];
        foreach ($ids as $id) {
            if (isset($taxes[(int) $id])) {
                $ordered[] = $taxes[(int) $id];
            }
        }

        return $ordered;
    }

    // =========================================================================
    // ATTRIBUTES
    // =========================================================================

    /**
     * Search attributes (attribute values with their groups)
     */
    public function searchTargetAttributes($query, $idLang, $idShop, $limit = 20, $offset = 0, array $filters = [])
    {
        $sql = new DbQuery();
        $sql->select('DISTINCT a.id_attribute, al.name, a.color, a.position, a.id_attribute_group');
        $sql->select('agl.name AS group_name, ag.is_color_group');
        $sql->from('attribute', 'a');
        $sql->innerJoin('attribute_shop', 'ash', 'ash.id_attribute = a.id_attribute AND ash.id_shop = ' . (int) $idShop);
        $sql->leftJoin('attribute_lang', 'al', 'al.id_attribute = a.id_attribute AND al.id_lang = ' . (int) $idLang);
        $sql->leftJoin('attribute_group', 'ag', 'ag.id_attribute_group = a.id_attribute_group');
        $sql->leftJoin('attribute_group_lang', 'agl', 'agl.id_attribute_group = a.id_attribute_group AND agl.id_lang = ' . (int) $idLang);

        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(al.name LIKE \'%' . $escapedQuery . '%\' OR agl.name LIKE \'%' . $escapedQuery . '%\' OR a.id_attribute = ' . (int) $query . ')');
        }

        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('al.name NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('al.name LIKE \'%' . $refine . '%\'');
            }
        }

        // Filter by attribute group
        if ($filters['attribute_group'] !== null) {
            $sql->where('a.id_attribute_group = ' . (int) $filters['attribute_group']);
        }

        // Filter by color
        if (!empty($filters['is_color'])) {
            $sql->where('ag.is_color_group = 1');
        }

        // Sorting
        $attributeSortMap = [
            'name' => 'al.name',
            'id' => 'a.id_attribute',
            'position' => 'a.position',
            'group' => 'agl.name',
        ];
        $sql->orderBy($this->buildOrderBy('attributes', $filters, $attributeSortMap));
        $sql->limit((int) $limit, (int) $offset);

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        return array_map(function ($row) {
            return [
                'id' => (int) $row['id_attribute'],
                'type' => 'attribute',
                'name' => $row['name'],
                'color' => $row['color'],
                'position' => (int) $row['position'],
                'id_attribute_group' => (int) $row['id_attribute_group'],
                'group_name' => $row['group_name'],
                'is_color_group' => (bool) $row['is_color_group'],
            ];
        }, $results);
    }

    /**
     * Count attributes
     */
    public function countTargetAttributes($query, $idLang, $idShop, array $filters = [])
    {
        $sql = new DbQuery();
        $sql->select('COUNT(DISTINCT a.id_attribute)');
        $sql->from('attribute', 'a');
        $sql->innerJoin('attribute_shop', 'ash', 'ash.id_attribute = a.id_attribute AND ash.id_shop = ' . (int) $idShop);
        $sql->leftJoin('attribute_lang', 'al', 'al.id_attribute = a.id_attribute AND al.id_lang = ' . (int) $idLang);
        $sql->leftJoin('attribute_group', 'ag', 'ag.id_attribute_group = a.id_attribute_group');
        $sql->leftJoin('attribute_group_lang', 'agl', 'agl.id_attribute_group = a.id_attribute_group AND agl.id_lang = ' . (int) $idLang);

        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(al.name LIKE \'%' . $escapedQuery . '%\' OR agl.name LIKE \'%' . $escapedQuery . '%\' OR a.id_attribute = ' . (int) $query . ')');
        }

        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('al.name NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('al.name LIKE \'%' . $refine . '%\'');
            }
        }

        if ($filters['attribute_group'] !== null) {
            $sql->where('a.id_attribute_group = ' . (int) $filters['attribute_group']);
        }

        if (!empty($filters['is_color'])) {
            $sql->where('ag.is_color_group = 1');
        }

        return (int) Db::getInstance()->getValue($sql);
    }

    /**
     * Get attributes by IDs
     */
    public function getTargetAttributesByIds(array $ids, $idLang)
    {
        if (empty($ids)) {
            return [];
        }

        $sql = new DbQuery();
        $sql->select('a.id_attribute, al.name, a.color, a.id_attribute_group, agl.name AS group_name');
        $sql->from('attribute', 'a');
        $sql->leftJoin('attribute_lang', 'al', 'al.id_attribute = a.id_attribute AND al.id_lang = ' . (int) $idLang);
        $sql->leftJoin('attribute_group_lang', 'agl', 'agl.id_attribute_group = a.id_attribute_group AND agl.id_lang = ' . (int) $idLang);
        $sql->where('a.id_attribute IN (' . implode(',', array_map('intval', $ids)) . ')');

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        $attributes = [];
        foreach ($results as $row) {
            $attributes[(int) $row['id_attribute']] = [
                'id' => (int) $row['id_attribute'],
                'type' => 'attribute',
                'name' => $row['name'],
                'color' => $row['color'],
                'group_name' => $row['group_name'],
            ];
        }

        $ordered = [];
        foreach ($ids as $id) {
            if (isset($attributes[(int) $id])) {
                $ordered[] = $attributes[(int) $id];
            }
        }

        return $ordered;
    }

    // =========================================================================
    // FEATURES
    // =========================================================================

    /**
     * Search features (feature values with their groups)
     */
    public function searchTargetFeatures($query, $idLang, $idShop, $limit = 20, $offset = 0, array $filters = [])
    {
        $sql = new DbQuery();
        $sql->select('DISTINCT fv.id_feature_value, fvl.value AS name, fv.custom, fv.id_feature');
        $sql->select('fl.name AS feature_name');
        $sql->from('feature_value', 'fv');
        $sql->innerJoin('feature_shop', 'fsh', 'fsh.id_feature = fv.id_feature AND fsh.id_shop = ' . (int) $idShop);
        $sql->leftJoin('feature_value_lang', 'fvl', 'fvl.id_feature_value = fv.id_feature_value AND fvl.id_lang = ' . (int) $idLang);
        $sql->leftJoin('feature_lang', 'fl', 'fl.id_feature = fv.id_feature AND fl.id_lang = ' . (int) $idLang);

        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(fvl.value LIKE \'%' . $escapedQuery . '%\' OR fl.name LIKE \'%' . $escapedQuery . '%\' OR fv.id_feature_value = ' . (int) $query . ')');
        }

        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('fvl.value NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('fvl.value LIKE \'%' . $refine . '%\'');
            }
        }

        // Filter by feature group
        if ($filters['feature_group'] !== null) {
            $sql->where('fv.id_feature = ' . (int) $filters['feature_group']);
        }

        // Exclude custom values
        if (!empty($filters['is_custom'])) {
            $sql->where('fv.custom = 1');
        }

        // Sorting
        $featureSortMap = [
            'name' => 'fvl.value',
            'id' => 'fv.id_feature_value',
            'feature' => 'fl.name',
        ];
        $sql->orderBy($this->buildOrderBy('features', $filters, $featureSortMap));
        $sql->limit((int) $limit, (int) $offset);

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        return array_map(function ($row) {
            return [
                'id' => (int) $row['id_feature_value'],
                'type' => 'feature',
                'name' => $row['name'],
                'custom' => (bool) $row['custom'],
                'id_feature' => (int) $row['id_feature'],
                'feature_name' => $row['feature_name'],
            ];
        }, $results);
    }

    /**
     * Count features
     */
    public function countTargetFeatures($query, $idLang, $idShop, array $filters = [])
    {
        $sql = new DbQuery();
        $sql->select('COUNT(DISTINCT fv.id_feature_value)');
        $sql->from('feature_value', 'fv');
        $sql->innerJoin('feature_shop', 'fsh', 'fsh.id_feature = fv.id_feature AND fsh.id_shop = ' . (int) $idShop);
        $sql->leftJoin('feature_value_lang', 'fvl', 'fvl.id_feature_value = fv.id_feature_value AND fvl.id_lang = ' . (int) $idLang);
        $sql->leftJoin('feature_lang', 'fl', 'fl.id_feature = fv.id_feature AND fl.id_lang = ' . (int) $idLang);

        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(fvl.value LIKE \'%' . $escapedQuery . '%\' OR fl.name LIKE \'%' . $escapedQuery . '%\' OR fv.id_feature_value = ' . (int) $query . ')');
        }

        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('fvl.value NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('fvl.value LIKE \'%' . $refine . '%\'');
            }
        }

        if ($filters['feature_group'] !== null) {
            $sql->where('fv.id_feature = ' . (int) $filters['feature_group']);
        }

        if (!empty($filters['is_custom'])) {
            $sql->where('fv.custom = 1');
        }

        return (int) Db::getInstance()->getValue($sql);
    }

    /**
     * Get features by IDs
     */
    public function getTargetFeaturesByIds(array $ids, $idLang)
    {
        if (empty($ids)) {
            return [];
        }

        $sql = new DbQuery();
        $sql->select('fv.id_feature_value, fvl.value AS name, fv.id_feature, fl.name AS feature_name');
        $sql->from('feature_value', 'fv');
        $sql->leftJoin('feature_value_lang', 'fvl', 'fvl.id_feature_value = fv.id_feature_value AND fvl.id_lang = ' . (int) $idLang);
        $sql->leftJoin('feature_lang', 'fl', 'fl.id_feature = fv.id_feature AND fl.id_lang = ' . (int) $idLang);
        $sql->where('fv.id_feature_value IN (' . implode(',', array_map('intval', $ids)) . ')');

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        $features = [];
        foreach ($results as $row) {
            $features[(int) $row['id_feature_value']] = [
                'id' => (int) $row['id_feature_value'],
                'type' => 'feature',
                'name' => $row['name'],
                'feature_name' => $row['feature_name'],
            ];
        }

        $ordered = [];
        foreach ($ids as $id) {
            if (isset($features[(int) $id])) {
                $ordered[] = $features[(int) $id];
            }
        }

        return $ordered;
    }

    // =========================================================================
    // TAGS
    // =========================================================================

    /**
     * Search tags
     */
    public function searchTargetTags($query, $idLang, $idShop, array $filters = [])
    {
        $limit = isset($filters['limit']) ? (int) $filters['limit'] : 20;

        $sql = new DbQuery();
        $sql->select('DISTINCT t.id_tag, t.name, t.id_lang');
        $sql->select('(SELECT COUNT(pt.id_product) FROM ' . _DB_PREFIX_ . 'product_tag pt WHERE pt.id_tag = t.id_tag) AS product_count');
        $sql->from('tag', 't');

        // By default, filter by current language
        $sql->where('t.id_lang = ' . (int) $idLang);

        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(t.name LIKE \'%' . $escapedQuery . '%\' OR t.id_tag = ' . (int) $query . ')');
        }

        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('t.name NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('t.name LIKE \'%' . $refine . '%\'');
            }
        }

        // Sorting
        $tagSortMap = [
            'name' => 't.name',
            'id' => 't.id_tag',
            'popularity' => 'product_count',
        ];
        $sql->orderBy($this->buildOrderBy('tags', $filters, $tagSortMap));
        $sql->limit($limit);

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        return array_map(function ($row) {
            return [
                'id' => (int) $row['id_tag'],
                'type' => 'tag',
                'name' => $row['name'],
                'product_count' => (int) $row['product_count'],
            ];
        }, $results);
    }

    /**
     * Count tags
     */
    public function countTargetTags($query, $idLang, $idShop, array $filters = [])
    {
        $sql = new DbQuery();
        $sql->select('COUNT(DISTINCT t.id_tag)');
        $sql->from('tag', 't');
        $sql->where('t.id_lang = ' . (int) $idLang);

        if (!empty($query)) {
            $escapedQuery = $this->escapePattern($query);
            $sql->where('(t.name LIKE \'%' . $escapedQuery . '%\' OR t.id_tag = ' . (int) $query . ')');
        }

        if (!empty($filters['refine'])) {
            $refine = $this->escapePattern($filters['refine']);
            if (!empty($filters['refine_negate'])) {
                $sql->where('t.name NOT LIKE \'%' . $refine . '%\'');
            } else {
                $sql->where('t.name LIKE \'%' . $refine . '%\'');
            }
        }

        return (int) Db::getInstance()->getValue($sql);
    }

    /**
     * Get tags by IDs
     */
    public function getTargetTagsByIds(array $ids, $idLang)
    {
        if (empty($ids)) {
            return [];
        }

        $sql = new DbQuery();
        $sql->select('t.id_tag, t.name');
        $sql->from('tag', 't');
        $sql->where('t.id_tag IN (' . implode(',', array_map('intval', $ids)) . ')');

        $results = Db::getInstance()->executeS($sql);

        if (!$results) {
            return [];
        }

        $tags = [];
        foreach ($results as $row) {
            $tags[(int) $row['id_tag']] = [
                'id' => (int) $row['id_tag'],
                'type' => 'tag',
                'name' => $row['name'],
            ];
        }

        $ordered = [];
        foreach ($ids as $id) {
            if (isset($tags[(int) $id])) {
                $ordered[] = $tags[(int) $id];
            }
        }

        return $ordered;
    }

    // =========================================================================
    // MPR MATERIALS (module-specific entity type)
    // =========================================================================

    /**
     * Search materials
     */
    public function searchTargetMprMaterials($query, $idLang, $idShop, $limit = 20, $offset = 0, array $filters = [])
    {
        $db = Db::getInstance();
        $escapedQuery = $this->escapePattern($query);

        $sql = new DbQuery();
        $sql->select('m.id_material, m.reference, m.unit, m.unit_cost, m.active,
            ml.name, ml.description,
            IFNULL(SUM(ms.quantity), 0) AS stock_qty');
        $sql->from('mprwarehouserevolution_material', 'm');
        $sql->leftJoin('mprwarehouserevolution_material_lang', 'ml', 'ml.id_material = m.id_material AND ml.id_lang = ' . (int) $idLang);
        $sql->leftJoin('mprwarehouserevolution_material_stock', 'ms', 'ms.id_material = m.id_material');

        if ($query !== '') {
            $sql->where('(ml.name LIKE \'%' . $escapedQuery . '%\' OR m.reference LIKE \'%' . $escapedQuery . '%\' OR m.barcode LIKE \'%' . $escapedQuery . '%\')');
        }

        $sql->groupBy('m.id_material');
        $sql->orderBy('ml.name ASC');
        $sql->limit($limit, $offset);

        $rows = $db->executeS($sql);
        if (!$rows) {
            return [];
        }

        $results = [];
        foreach ($rows as $row) {
            $stockQty = (int) $row['stock_qty'];
            $stockStatus = $stockQty <= 0 ? 'out_of_stock' : ($stockQty <= (int) ($row['min_stock'] ?? 5) ? 'low_stock' : 'in_stock');

            $results[] = [
                'id' => (int) $row['id_material'],
                'type' => 'mpr_material',
                'name' => $row['name'],
                'subtitle' => $row['reference'] ? 'Ref: ' . $row['reference'] : null,
                'reference' => $row['reference'],
                'stock_qty' => $stockQty,
                'stock_status' => $stockStatus,
                'active' => (bool) $row['active'],
            ];
        }

        return $results;
    }

    /**
     * Count materials matching query
     */
    public function countTargetMprMaterials($query, $idLang, $idShop, array $filters = [])
    {
        $escapedQuery = $this->escapePattern($query);

        $sql = new DbQuery();
        $sql->select('COUNT(DISTINCT m.id_material)');
        $sql->from('mprwarehouserevolution_material', 'm');
        $sql->leftJoin('mprwarehouserevolution_material_lang', 'ml', 'ml.id_material = m.id_material AND ml.id_lang = ' . (int) $idLang);

        if ($query !== '') {
            $sql->where('(ml.name LIKE \'%' . $escapedQuery . '%\' OR m.reference LIKE \'%' . $escapedQuery . '%\' OR m.barcode LIKE \'%' . $escapedQuery . '%\')');
        }

        return (int) Db::getInstance()->getValue($sql);
    }

    /**
     * Get materials by IDs
     */
    public function getTargetMprMaterialsByIds(array $ids, $idLang, $idShop = null)
    {
        if (empty($ids)) {
            return [];
        }

        $db = Db::getInstance();

        $sql = new DbQuery();
        $sql->select('m.id_material, m.reference, m.unit, m.unit_cost, m.active,
            ml.name, ml.description,
            IFNULL(SUM(ms.quantity), 0) AS stock_qty');
        $sql->from('mprwarehouserevolution_material', 'm');
        $sql->leftJoin('mprwarehouserevolution_material_lang', 'ml', 'ml.id_material = m.id_material AND ml.id_lang = ' . (int) ($idLang ?: $this->idLang));
        $sql->leftJoin('mprwarehouserevolution_material_stock', 'ms', 'ms.id_material = m.id_material');
        $sql->where('m.id_material IN (' . implode(',', array_map('intval', $ids)) . ')');
        $sql->groupBy('m.id_material');

        $rows = $db->executeS($sql);
        if (!$rows) {
            return [];
        }

        $materials = [];
        foreach ($rows as $row) {
            $stockQty = (int) $row['stock_qty'];
            $materials[(int) $row['id_material']] = [
                'id' => (int) $row['id_material'],
                'type' => 'mpr_material',
                'name' => $row['name'],
                'subtitle' => $row['reference'] ? 'Ref: ' . $row['reference'] : null,
                'reference' => $row['reference'],
                'stock_qty' => $stockQty,
                'stock_status' => $stockQty <= 0 ? 'out_of_stock' : 'in_stock',
                'active' => (bool) $row['active'],
            ];
        }

        // Return in original order
        $ordered = [];
        foreach ($ids as $id) {
            if (isset($materials[(int) $id])) {
                $ordered[] = $materials[(int) $id];
            }
        }

        return $ordered;
    }

    // =========================================================================
    // FILTERABLE DATA (for UI dropdowns)
    // =========================================================================

    /**
     * Get attribute groups with their values for filter dropdown
     *
     * @param int $idLang Language ID
     * @param int $idShop Shop ID
     * @return array Attribute groups with values
     */
    public function getFilterableAttributes($idLang, $idShop)
    {
        $sql = new DbQuery();
        $sql->select('ag.id_attribute_group, agl.name AS group_name, ag.is_color_group');
        $sql->from('attribute_group', 'ag');
        $sql->innerJoin('attribute_group_shop', 'ags', 'ags.id_attribute_group = ag.id_attribute_group AND ags.id_shop = ' . (int) $idShop);
        $sql->leftJoin('attribute_group_lang', 'agl', 'agl.id_attribute_group = ag.id_attribute_group AND agl.id_lang = ' . (int) $idLang);
        $sql->orderBy('ag.position ASC');

        $groups = Db::getInstance()->executeS($sql);

        if (!$groups) {
            return [];
        }

        $result = [];
        foreach ($groups as $group) {
            $sqlValues = new DbQuery();
            $sqlValues->select('a.id_attribute, al.name, a.color');
            $sqlValues->from('attribute', 'a');
            $sqlValues->innerJoin('attribute_shop', 'ash', 'ash.id_attribute = a.id_attribute AND ash.id_shop = ' . (int) $idShop);
            $sqlValues->leftJoin('attribute_lang', 'al', 'al.id_attribute = a.id_attribute AND al.id_lang = ' . (int) $idLang);
            $sqlValues->where('a.id_attribute_group = ' . (int) $group['id_attribute_group']);
            $sqlValues->orderBy('a.position ASC');

            $values = Db::getInstance()->executeS($sqlValues);

            $result[] = [
                'id' => (int) $group['id_attribute_group'],
                'name' => $group['group_name'],
                'is_color' => (bool) $group['is_color_group'],
                'values' => $values ?: [],
            ];
        }

        return $result;
    }

    /**
     * Get features with their values for filter dropdown
     *
     * @param int $idLang Language ID
     * @param int $idShop Shop ID
     * @return array Features with values
     */
    public function getFilterableFeatures($idLang, $idShop)
    {
        $sql = new DbQuery();
        $sql->select('f.id_feature, fl.name AS feature_name');
        $sql->from('feature', 'f');
        $sql->innerJoin('feature_shop', 'fs', 'fs.id_feature = f.id_feature AND fs.id_shop = ' . (int) $idShop);
        $sql->leftJoin('feature_lang', 'fl', 'fl.id_feature = f.id_feature AND fl.id_lang = ' . (int) $idLang);
        $sql->orderBy('f.position ASC');

        $features = Db::getInstance()->executeS($sql);

        if (!$features) {
            return [];
        }

        $result = [];
        foreach ($features as $feature) {
            $sqlValues = new DbQuery();
            $sqlValues->select('fv.id_feature_value, fvl.value AS name');
            $sqlValues->from('feature_value', 'fv');
            $sqlValues->leftJoin('feature_value_lang', 'fvl', 'fvl.id_feature_value = fv.id_feature_value AND fvl.id_lang = ' . (int) $idLang);
            $sqlValues->where('fv.id_feature = ' . (int) $feature['id_feature']);
            $sqlValues->where('fv.custom = 0');
            $sqlValues->orderBy('fvl.value ASC');

            $values = Db::getInstance()->executeS($sqlValues);

            $result[] = [
                'id' => (int) $feature['id_feature'],
                'name' => $feature['feature_name'],
                'values' => $values ?: [],
            ];
        }

        return $result;
    }
}
