<?php
/**
 * Entity Query Handler
 *
 * Generic query patterns for all non-product entity types.
 * Extracted from EntitySelector trait.
 *
 * Entity types handled:
 * - Categories
 * - Manufacturers
 * - Suppliers
 * - CMS Pages
 * - CMS Categories
 * - Employees
 * - Customers
 * - Customer Groups
 * - Carriers
 * - Zones
 * - Countries
 * - Currencies
 * - Languages
 * - Shops
 * - Shop Groups
 * - Profiles
 * - Order States
 * - Taxes
 *
 * @package MyPrestaRocks\EntitySelector
 */

namespace MyPrestaRocks\EntitySelector\EntitySelector;

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

use Db;
use Context;

class EntityQueryHandler
{
    /**
     * @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;
    }

    /**
     * Count all active entities of a type
     *
     * @param string $entityType Entity type key
     * @return int Count
     */
    public function countAllActive($entityType)
    {
        $method = 'countAllActive' . $this->camelCase($entityType);

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

        return 0;
    }

    /**
     * Get matching entity IDs by groups
     *
     * @param string $entityType Entity type key
     * @param array $groups Condition groups
     * @return array Entity IDs
     */
    public function getMatchingIds($entityType, array $groups)
    {
        $method = 'getMatching' . $this->camelCase($entityType) . 'Ids';

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

        return [];
    }

    /**
     * Get entity IDs by method
     *
     * @param string $entityType Entity type key
     * @param string $method Selection method
     * @param mixed $values Method values
     * @return array Entity IDs
     */
    public function getIdsByMethod($entityType, $method, $values)
    {
        $methodName = 'get' . $this->camelCase($entityType) . 'IdsByMethod';

        if (method_exists($this, $methodName)) {
            return $this->$methodName($method, $values, $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)));
    }

    /**
     * Extract pattern string from pattern value
     *
     * @param mixed $pattern Pattern value (string or array)
     * @return string The pattern string
     */
    protected function extractPatternString($pattern)
    {
        if (is_array($pattern)) {
            return isset($pattern['pattern']) ? (string) $pattern['pattern'] : '';
        }
        return (string) $pattern;
    }

    // ========================================
    // Category Methods
    // ========================================

    /**
     * Count all active categories
     *
     * @param int $idLang Language ID
     * @param int $idShop Shop ID
     * @return int Count
     */
    public function countAllActiveCategories($idLang, $idShop)
    {
        return (int) Db::getInstance()->getValue('
            SELECT COUNT(DISTINCT c.id_category)
            FROM ' . _DB_PREFIX_ . 'category c
            INNER JOIN ' . _DB_PREFIX_ . 'category_shop cs ON c.id_category = cs.id_category AND cs.id_shop = ' . (int) $idShop . '
            WHERE c.active = 1 AND c.id_category > 1
        ');
    }

    /**
     * Get matching category IDs from groups
     *
     * @param array $groups Condition groups
     * @param int $idLang Language ID
     * @param int $idShop Shop ID
     * @return array Category IDs
     */
    public function getMatchingCategoryIds($groups, $idLang, $idShop)
    {
        $allMatchingIds = [];

        foreach ($groups as $group) {
            $includeMethod = $group['include']['method'] ?? 'all';
            $includeValues = $group['include']['values'] ?? [];

            $includedIds = $this->getCategoryIdsByMethod($includeMethod, $includeValues, $idLang, $idShop);

            $excludedIds = [];
            $excludes = $group['excludes'] ?? [];
            if (!empty($group['exclude']['method'])) {
                $excludes = [$group['exclude']];
            }

            foreach ($excludes as $exclude) {
                if (empty($exclude['method'])) {
                    continue;
                }
                $excludeIds = $this->getCategoryIdsByMethod($exclude['method'], $exclude['values'] ?? [], $idLang, $idShop);
                $excludedIds = array_merge($excludedIds, $excludeIds);
            }

            $groupResult = array_diff($includedIds, $excludedIds);
            $allMatchingIds = array_merge($allMatchingIds, $groupResult);
        }

        return array_unique($allMatchingIds);
    }

    /**
     * Get category IDs by selection method
     *
     * @param string $method Selection method
     * @param mixed $values Method values
     * @param int $idLang Language ID
     * @param int $idShop Shop ID
     * @return array Category IDs
     */
    public function getCategoryIdsByMethod($method, $values, $idLang, $idShop)
    {
        switch ($method) {
            case 'all':
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT c.id_category
                    FROM ' . _DB_PREFIX_ . 'category c
                    INNER JOIN ' . _DB_PREFIX_ . 'category_shop cs ON c.id_category = cs.id_category AND cs.id_shop = ' . (int) $idShop . '
                    WHERE c.active = 1 AND c.id_category > 1
                ');
                return array_column($results ?: [], 'id_category');

            case 'specific':
                return array_map('intval', $values);

            default:
                return [];
        }
    }

    // ========================================
    // Manufacturer Methods
    // ========================================

    /**
     * Count all active manufacturers
     */
    public function countAllActiveManufacturers($idLang, $idShop)
    {
        return (int) Db::getInstance()->getValue('
            SELECT COUNT(DISTINCT m.id_manufacturer)
            FROM ' . _DB_PREFIX_ . 'manufacturer m
            INNER JOIN ' . _DB_PREFIX_ . 'manufacturer_shop ms ON m.id_manufacturer = ms.id_manufacturer AND ms.id_shop = ' . (int) $idShop . '
            WHERE m.active = 1
        ');
    }

    /**
     * Get matching manufacturer IDs from groups
     */
    public function getMatchingManufacturerIds($groups, $idLang, $idShop)
    {
        $allMatchingIds = [];

        foreach ($groups as $group) {
            $includeMethod = $group['include']['method'] ?? 'all';
            $includeValues = $group['include']['values'] ?? [];

            $includedIds = $this->getManufacturerIdsByMethod($includeMethod, $includeValues, $idLang, $idShop);

            $excludedIds = [];
            $excludes = $group['excludes'] ?? [];
            if (!empty($group['exclude']['method'])) {
                $excludes = [$group['exclude']];
            }

            foreach ($excludes as $exclude) {
                if (empty($exclude['method'])) {
                    continue;
                }
                $excludeIds = $this->getManufacturerIdsByMethod($exclude['method'], $exclude['values'] ?? [], $idLang, $idShop);
                $excludedIds = array_merge($excludedIds, $excludeIds);
            }

            $groupResult = array_diff($includedIds, $excludedIds);
            $allMatchingIds = array_merge($allMatchingIds, $groupResult);
        }

        return array_unique($allMatchingIds);
    }

    /**
     * Get manufacturer IDs by selection method
     */
    public function getManufacturerIdsByMethod($method, $values, $idLang, $idShop)
    {
        switch ($method) {
            case 'all':
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT m.id_manufacturer
                    FROM ' . _DB_PREFIX_ . 'manufacturer m
                    INNER JOIN ' . _DB_PREFIX_ . 'manufacturer_shop ms ON m.id_manufacturer = ms.id_manufacturer AND ms.id_shop = ' . (int) $idShop . '
                    WHERE m.active = 1
                ');
                return array_column($results ?: [], 'id_manufacturer');

            case 'specific':
                return array_map('intval', $values);

            default:
                return [];
        }
    }

    // ========================================
    // Supplier Methods
    // ========================================

    /**
     * Count all active suppliers
     */
    public function countAllActiveSuppliers($idLang, $idShop)
    {
        return (int) Db::getInstance()->getValue('
            SELECT COUNT(DISTINCT s.id_supplier)
            FROM ' . _DB_PREFIX_ . 'supplier s
            INNER JOIN ' . _DB_PREFIX_ . 'supplier_shop ss ON s.id_supplier = ss.id_supplier AND ss.id_shop = ' . (int) $idShop . '
            WHERE s.active = 1
        ');
    }

    /**
     * Get matching supplier IDs from groups
     */
    public function getMatchingSupplierIds($groups, $idLang, $idShop)
    {
        $allMatchingIds = [];

        foreach ($groups as $group) {
            $includeMethod = $group['include']['method'] ?? 'all';
            $includeValues = $group['include']['values'] ?? [];

            $includedIds = $this->getSupplierIdsByMethod($includeMethod, $includeValues, $idLang, $idShop);

            $excludedIds = [];
            $excludes = $group['excludes'] ?? [];
            if (!empty($group['exclude']['method'])) {
                $excludes = [$group['exclude']];
            }

            foreach ($excludes as $exclude) {
                if (empty($exclude['method'])) {
                    continue;
                }
                $excludeIds = $this->getSupplierIdsByMethod($exclude['method'], $exclude['values'] ?? [], $idLang, $idShop);
                $excludedIds = array_merge($excludedIds, $excludeIds);
            }

            $groupResult = array_diff($includedIds, $excludedIds);
            $allMatchingIds = array_merge($allMatchingIds, $groupResult);
        }

        return array_unique($allMatchingIds);
    }

    /**
     * Get supplier IDs by selection method
     */
    public function getSupplierIdsByMethod($method, $values, $idLang, $idShop)
    {
        switch ($method) {
            case 'all':
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT s.id_supplier
                    FROM ' . _DB_PREFIX_ . 'supplier s
                    INNER JOIN ' . _DB_PREFIX_ . 'supplier_shop ss ON s.id_supplier = ss.id_supplier AND ss.id_shop = ' . (int) $idShop . '
                    WHERE s.active = 1
                ');
                return array_column($results ?: [], 'id_supplier');

            case 'specific':
                return array_map('intval', $values);

            default:
                return [];
        }
    }

    // ========================================
    // CMS Methods
    // ========================================

    /**
     * Count all active CMS pages
     */
    public function countAllActiveCms($idLang, $idShop)
    {
        return (int) Db::getInstance()->getValue('
            SELECT COUNT(DISTINCT c.id_cms)
            FROM ' . _DB_PREFIX_ . 'cms c
            INNER JOIN ' . _DB_PREFIX_ . 'cms_shop cs ON c.id_cms = cs.id_cms AND cs.id_shop = ' . (int) $idShop . '
            WHERE c.active = 1
        ');
    }

    /**
     * Get matching CMS IDs from groups
     */
    public function getMatchingCmsIds($groups, $idLang, $idShop)
    {
        $allMatchingIds = [];

        foreach ($groups as $group) {
            $includeMethod = $group['include']['method'] ?? 'all';
            $includeValues = $group['include']['values'] ?? [];

            $includedIds = $this->getCmsIdsByMethod($includeMethod, $includeValues, $idLang, $idShop);

            $excludedIds = [];
            $excludes = $group['excludes'] ?? [];
            if (!empty($group['exclude']['method'])) {
                $excludes = [$group['exclude']];
            }

            foreach ($excludes as $exclude) {
                if (empty($exclude['method'])) {
                    continue;
                }
                $excludeIds = $this->getCmsIdsByMethod($exclude['method'], $exclude['values'] ?? [], $idLang, $idShop);
                $excludedIds = array_merge($excludedIds, $excludeIds);
            }

            $groupResult = array_diff($includedIds, $excludedIds);
            $allMatchingIds = array_merge($allMatchingIds, $groupResult);
        }

        return array_unique($allMatchingIds);
    }

    /**
     * Get CMS IDs by selection method
     */
    public function getCmsIdsByMethod($method, $values, $idLang, $idShop)
    {
        switch ($method) {
            case 'all':
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT c.id_cms
                    FROM ' . _DB_PREFIX_ . 'cms c
                    INNER JOIN ' . _DB_PREFIX_ . 'cms_shop cs ON c.id_cms = cs.id_cms AND cs.id_shop = ' . (int) $idShop . '
                    WHERE c.active = 1
                ');
                return array_column($results ?: [], 'id_cms');

            case 'specific':
            case 'by_cms':
                return array_map('intval', $values);

            case 'by_cms_category':
                if (empty($values)) {
                    return [];
                }
                $categoryIds = array_map('intval', $values);
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT c.id_cms
                    FROM ' . _DB_PREFIX_ . 'cms c
                    INNER JOIN ' . _DB_PREFIX_ . 'cms_shop cs ON c.id_cms = cs.id_cms AND cs.id_shop = ' . (int) $idShop . '
                    WHERE c.active = 1 AND c.id_cms_category IN (' . implode(',', $categoryIds) . ')
                ');
                return array_column($results ?: [], 'id_cms');

            case 'by_name_pattern':
                if (empty($values)) {
                    return [];
                }
                $patterns = is_array($values) ? $values : [$values];
                $conditions = [];
                foreach ($patterns as $pattern) {
                    $safe = pSQL($this->extractPatternString($pattern));
                    $conditions[] = "(cl.meta_title LIKE '%" . $safe . "%' OR cl.meta_description LIKE '%" . $safe . "%')";
                }
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT c.id_cms
                    FROM ' . _DB_PREFIX_ . 'cms c
                    INNER JOIN ' . _DB_PREFIX_ . 'cms_shop cs ON c.id_cms = cs.id_cms AND cs.id_shop = ' . (int) $idShop . '
                    INNER JOIN ' . _DB_PREFIX_ . 'cms_lang cl ON c.id_cms = cl.id_cms AND cl.id_lang = ' . (int) $idLang . ' AND cl.id_shop = ' . (int) $idShop . '
                    WHERE c.active = 1
                    AND (' . implode(' OR ', $conditions) . ')
                ');
                return array_column($results ?: [], 'id_cms');

            case 'by_date_added':
                $from = isset($values['from']) ? pSQL($values['from']) : null;
                $to = isset($values['to']) ? pSQL($values['to']) : null;
                $conditions = ['c.active = 1'];
                if ($from) {
                    $conditions[] = "DATE(c.date_add) >= '" . $from . "'";
                }
                if ($to) {
                    $conditions[] = "DATE(c.date_add) <= '" . $to . "'";
                }
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT c.id_cms
                    FROM ' . _DB_PREFIX_ . 'cms c
                    INNER JOIN ' . _DB_PREFIX_ . 'cms_shop cs ON c.id_cms = cs.id_cms AND cs.id_shop = ' . (int) $idShop . '
                    WHERE ' . implode(' AND ', $conditions) . '
                ');
                return array_column($results ?: [], 'id_cms');

            case 'by_date_updated':
                $from = isset($values['from']) ? pSQL($values['from']) : null;
                $to = isset($values['to']) ? pSQL($values['to']) : null;
                $conditions = ['c.active = 1'];
                if ($from) {
                    $conditions[] = "DATE(c.date_upd) >= '" . $from . "'";
                }
                if ($to) {
                    $conditions[] = "DATE(c.date_upd) <= '" . $to . "'";
                }
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT c.id_cms
                    FROM ' . _DB_PREFIX_ . 'cms c
                    INNER JOIN ' . _DB_PREFIX_ . 'cms_shop cs ON c.id_cms = cs.id_cms AND cs.id_shop = ' . (int) $idShop . '
                    WHERE ' . implode(' AND ', $conditions) . '
                ');
                return array_column($results ?: [], 'id_cms');

            default:
                return [];
        }
    }

    // ========================================
    // CMS Category Methods
    // ========================================

    /**
     * Count all active CMS categories
     */
    public function countAllActiveCmsCategories($idLang, $idShop)
    {
        return (int) Db::getInstance()->getValue('
            SELECT COUNT(DISTINCT cc.id_cms_category)
            FROM ' . _DB_PREFIX_ . 'cms_category cc
            INNER JOIN ' . _DB_PREFIX_ . 'cms_category_shop ccs ON cc.id_cms_category = ccs.id_cms_category AND ccs.id_shop = ' . (int) $idShop . '
            WHERE cc.active = 1
        ');
    }

    /**
     * Get matching CMS category IDs from groups
     */
    public function getMatchingCmsCategoryIds($groups, $idLang, $idShop)
    {
        $allMatchingIds = [];

        foreach ($groups as $group) {
            $includeMethod = $group['include']['method'] ?? 'all';
            $includeValues = $group['include']['values'] ?? [];

            $includedIds = $this->getCmsCategoryIdsByMethod($includeMethod, $includeValues, $idLang, $idShop);

            $excludedIds = [];
            $excludes = $group['excludes'] ?? [];
            if (!empty($group['exclude']['method'])) {
                $excludes = [$group['exclude']];
            }

            foreach ($excludes as $exclude) {
                if (empty($exclude['method'])) {
                    continue;
                }
                $excludeIds = $this->getCmsCategoryIdsByMethod($exclude['method'], $exclude['values'] ?? [], $idLang, $idShop);
                $excludedIds = array_merge($excludedIds, $excludeIds);
            }

            $groupResult = array_diff($includedIds, $excludedIds);
            $allMatchingIds = array_merge($allMatchingIds, $groupResult);
        }

        return array_unique($allMatchingIds);
    }

    /**
     * Get CMS category IDs by selection method
     */
    public function getCmsCategoryIdsByMethod($method, $values, $idLang, $idShop)
    {
        switch ($method) {
            case 'all':
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT cc.id_cms_category
                    FROM ' . _DB_PREFIX_ . 'cms_category cc
                    INNER JOIN ' . _DB_PREFIX_ . 'cms_category_shop ccs ON cc.id_cms_category = ccs.id_cms_category AND ccs.id_shop = ' . (int) $idShop . '
                    WHERE cc.active = 1
                ');
                return array_column($results ?: [], 'id_cms_category');

            case 'specific':
            case 'by_cms_category':
                return array_map('intval', $values);

            case 'by_name_pattern':
                if (empty($values)) {
                    return [];
                }
                $patterns = is_array($values) ? $values : [$values];
                $conditions = [];
                foreach ($patterns as $pattern) {
                    $safe = pSQL($this->extractPatternString($pattern));
                    $conditions[] = "(ccl.name LIKE '%" . $safe . "%' OR ccl.description LIKE '%" . $safe . "%')";
                }
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT cc.id_cms_category
                    FROM ' . _DB_PREFIX_ . 'cms_category cc
                    INNER JOIN ' . _DB_PREFIX_ . 'cms_category_shop ccs ON cc.id_cms_category = ccs.id_cms_category AND ccs.id_shop = ' . (int) $idShop . '
                    INNER JOIN ' . _DB_PREFIX_ . 'cms_category_lang ccl ON cc.id_cms_category = ccl.id_cms_category AND ccl.id_lang = ' . (int) $idLang . ' AND ccl.id_shop = ' . (int) $idShop . '
                    WHERE cc.active = 1
                    AND (' . implode(' OR ', $conditions) . ')
                ');
                return array_column($results ?: [], 'id_cms_category');

            case 'by_date_added':
                $from = isset($values['from']) ? pSQL($values['from']) : null;
                $to = isset($values['to']) ? pSQL($values['to']) : null;
                $conditions = ['cc.active = 1'];
                if ($from) {
                    $conditions[] = "DATE(cc.date_add) >= '" . $from . "'";
                }
                if ($to) {
                    $conditions[] = "DATE(cc.date_add) <= '" . $to . "'";
                }
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT cc.id_cms_category
                    FROM ' . _DB_PREFIX_ . 'cms_category cc
                    INNER JOIN ' . _DB_PREFIX_ . 'cms_category_shop ccs ON cc.id_cms_category = ccs.id_cms_category AND ccs.id_shop = ' . (int) $idShop . '
                    WHERE ' . implode(' AND ', $conditions) . '
                ');
                return array_column($results ?: [], 'id_cms_category');

            default:
                return [];
        }
    }

    // ========================================
    // Employee Methods
    // ========================================

    /**
     * Count all active employees
     */
    public function countAllActiveEmployees($idLang, $idShop)
    {
        return (int) Db::getInstance()->getValue('
            SELECT COUNT(DISTINCT e.id_employee)
            FROM ' . _DB_PREFIX_ . 'employee e
            WHERE e.active = 1
        ');
    }

    /**
     * Get matching employee IDs from groups
     */
    public function getMatchingEmployeeIds($groups, $idLang, $idShop)
    {
        $allMatchingIds = [];

        foreach ($groups as $group) {
            $includeMethod = $group['include']['method'] ?? 'all';
            $includeValues = $group['include']['values'] ?? [];

            $includedIds = $this->getEmployeeIdsByMethod($includeMethod, $includeValues, $idLang, $idShop);

            $excludedIds = [];
            $excludes = $group['excludes'] ?? [];
            if (!empty($group['exclude']['method'])) {
                $excludes = [$group['exclude']];
            }

            foreach ($excludes as $exclude) {
                if (empty($exclude['method'])) {
                    continue;
                }
                $excludeIds = $this->getEmployeeIdsByMethod($exclude['method'], $exclude['values'] ?? [], $idLang, $idShop);
                $excludedIds = array_merge($excludedIds, $excludeIds);
            }

            $groupResult = array_diff($includedIds, $excludedIds);
            $allMatchingIds = array_merge($allMatchingIds, $groupResult);
        }

        return array_unique($allMatchingIds);
    }

    /**
     * Get employee IDs by selection method
     */
    public function getEmployeeIdsByMethod($method, $values, $idLang, $idShop)
    {
        switch ($method) {
            case 'all':
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT e.id_employee
                    FROM ' . _DB_PREFIX_ . 'employee e
                    WHERE e.active = 1
                ');
                return array_column($results ?: [], 'id_employee');

            case 'specific':
            case 'by_employee':
                return array_map('intval', $values);

            case 'by_profile':
                if (empty($values)) {
                    return [];
                }
                $profileIds = array_map('intval', $values);
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT e.id_employee
                    FROM ' . _DB_PREFIX_ . 'employee e
                    WHERE e.active = 1
                    AND e.id_profile IN (' . implode(',', $profileIds) . ')
                ');
                return array_column($results ?: [], 'id_employee');

            case 'by_name_pattern':
                if (empty($values)) {
                    return [];
                }
                $patterns = is_array($values) ? $values : [$values];
                $conditions = [];
                foreach ($patterns as $pattern) {
                    $safe = pSQL($this->extractPatternString($pattern));
                    $conditions[] = "(e.firstname LIKE '%" . $safe . "%' OR e.lastname LIKE '%" . $safe . "%' OR CONCAT(e.firstname, ' ', e.lastname) LIKE '%" . $safe . "%')";
                }
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT e.id_employee
                    FROM ' . _DB_PREFIX_ . 'employee e
                    WHERE e.active = 1
                    AND (' . implode(' OR ', $conditions) . ')
                ');
                return array_column($results ?: [], 'id_employee');

            case 'by_email_pattern':
                if (empty($values)) {
                    return [];
                }
                $patterns = is_array($values) ? $values : [$values];
                $conditions = [];
                foreach ($patterns as $pattern) {
                    $safe = pSQL($this->extractPatternString($pattern));
                    $conditions[] = "e.email LIKE '%" . $safe . "%'";
                }
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT e.id_employee
                    FROM ' . _DB_PREFIX_ . 'employee e
                    WHERE e.active = 1
                    AND (' . implode(' OR ', $conditions) . ')
                ');
                return array_column($results ?: [], 'id_employee');

            case 'by_active_status':
                $isActive = ($values === 'active' || $values === true || $values === 'true' || $values === '1' || $values === 1);
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT e.id_employee
                    FROM ' . _DB_PREFIX_ . 'employee e
                    WHERE e.active = ' . ($isActive ? '1' : '0') . '
                ');
                return array_column($results ?: [], 'id_employee');

            default:
                return [];
        }
    }

    // ========================================
    // Customer Methods
    // ========================================

    /**
     * Count all active customers
     */
    public function countAllActiveCustomers($idLang, $idShop)
    {
        return (int) Db::getInstance()->getValue('
            SELECT COUNT(DISTINCT c.id_customer)
            FROM ' . _DB_PREFIX_ . 'customer c
            WHERE c.active = 1 AND c.deleted = 0
        ');
    }

    /**
     * Get matching customer IDs from groups
     */
    public function getMatchingCustomerIds($groups, $idLang, $idShop)
    {
        $allMatchingIds = [];

        foreach ($groups as $group) {
            $includeMethod = $group['include']['method'] ?? 'all';
            $includeValues = $group['include']['values'] ?? [];

            $includedIds = $this->getCustomerIdsByMethod($includeMethod, $includeValues, $idLang, $idShop);

            $excludedIds = [];
            $excludes = $group['excludes'] ?? [];
            if (!empty($group['exclude']['method'])) {
                $excludes = [$group['exclude']];
            }

            foreach ($excludes as $exclude) {
                if (empty($exclude['method'])) {
                    continue;
                }
                $excludeIds = $this->getCustomerIdsByMethod($exclude['method'], $exclude['values'] ?? [], $idLang, $idShop);
                $excludedIds = array_merge($excludedIds, $excludeIds);
            }

            $groupResult = array_diff($includedIds, $excludedIds);
            $allMatchingIds = array_merge($allMatchingIds, $groupResult);
        }

        return array_unique($allMatchingIds);
    }

    /**
     * Get customer IDs by selection method
     */
    public function getCustomerIdsByMethod($method, $values, $idLang, $idShop)
    {
        switch ($method) {
            case 'all':
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT c.id_customer
                    FROM ' . _DB_PREFIX_ . 'customer c
                    WHERE c.active = 1 AND c.deleted = 0
                ');
                return array_column($results ?: [], 'id_customer');

            case 'specific':
            case 'by_customer':
                return array_map('intval', $values);

            case 'by_group':
            case 'by_customer_group':
                if (empty($values)) {
                    return [];
                }
                $groupIds = array_map('intval', $values);
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT cg.id_customer
                    FROM ' . _DB_PREFIX_ . 'customer_group cg
                    INNER JOIN ' . _DB_PREFIX_ . 'customer c ON cg.id_customer = c.id_customer
                    WHERE c.active = 1 AND c.deleted = 0
                    AND cg.id_group IN (' . implode(',', $groupIds) . ')
                ');
                return array_column($results ?: [], 'id_customer');

            case 'by_name_pattern':
                if (empty($values)) {
                    return [];
                }
                $patterns = is_array($values) ? $values : [$values];
                $conditions = [];
                foreach ($patterns as $pattern) {
                    $safe = pSQL($this->extractPatternString($pattern));
                    $conditions[] = "(c.firstname LIKE '%" . $safe . "%' OR c.lastname LIKE '%" . $safe . "%')";
                }
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT c.id_customer
                    FROM ' . _DB_PREFIX_ . 'customer c
                    WHERE c.active = 1 AND c.deleted = 0
                    AND (' . implode(' OR ', $conditions) . ')
                ');
                return array_column($results ?: [], 'id_customer');

            case 'by_email_pattern':
                if (empty($values)) {
                    return [];
                }
                $patterns = is_array($values) ? $values : [$values];
                $conditions = [];
                foreach ($patterns as $pattern) {
                    $conditions[] = "c.email LIKE '%" . pSQL($this->extractPatternString($pattern)) . "%'";
                }
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT c.id_customer
                    FROM ' . _DB_PREFIX_ . 'customer c
                    WHERE c.active = 1 AND c.deleted = 0
                    AND (' . implode(' OR ', $conditions) . ')
                ');
                return array_column($results ?: [], 'id_customer');

            case 'by_company':
                $hasCompany = ($values === 'yes' || $values === true || $values === '1');
                if ($hasCompany) {
                    $results = Db::getInstance()->executeS('
                        SELECT DISTINCT c.id_customer
                        FROM ' . _DB_PREFIX_ . 'customer c
                        WHERE c.active = 1 AND c.deleted = 0
                        AND c.company IS NOT NULL AND c.company != ""
                    ');
                } else {
                    $results = Db::getInstance()->executeS('
                        SELECT DISTINCT c.id_customer
                        FROM ' . _DB_PREFIX_ . 'customer c
                        WHERE c.active = 1 AND c.deleted = 0
                        AND (c.company IS NULL OR c.company = "")
                    ');
                }
                return array_column($results ?: [], 'id_customer');

            case 'by_company_pattern':
                if (empty($values)) {
                    return [];
                }
                $patterns = is_array($values) ? $values : [$values];
                $conditions = [];
                foreach ($patterns as $pattern) {
                    $conditions[] = "c.company LIKE '%" . pSQL($this->extractPatternString($pattern)) . "%'";
                }
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT c.id_customer
                    FROM ' . _DB_PREFIX_ . 'customer c
                    WHERE c.active = 1 AND c.deleted = 0
                    AND (' . implode(' OR ', $conditions) . ')
                ');
                return array_column($results ?: [], 'id_customer');

            case 'by_address_count':
                $min = isset($values['min']) && $values['min'] !== '' ? (int) $values['min'] : null;
                $max = isset($values['max']) && $values['max'] !== '' ? (int) $values['max'] : null;
                $havingConditions = [];
                if ($min !== null) {
                    $havingConditions[] = 'address_count >= ' . $min;
                }
                if ($max !== null) {
                    $havingConditions[] = 'address_count <= ' . $max;
                }
                if (empty($havingConditions)) {
                    return [];
                }
                $results = Db::getInstance()->executeS('
                    SELECT c.id_customer, COUNT(a.id_address) as address_count
                    FROM ' . _DB_PREFIX_ . 'customer c
                    LEFT JOIN ' . _DB_PREFIX_ . 'address a ON a.id_customer = c.id_customer AND a.deleted = 0
                    WHERE c.active = 1 AND c.deleted = 0
                    GROUP BY c.id_customer
                    HAVING ' . implode(' AND ', $havingConditions) . '
                ');
                return array_column($results ?: [], 'id_customer');

            case 'by_order_count':
            case 'by_total_orders':
                $min = isset($values['min']) && $values['min'] !== '' ? (int) $values['min'] : null;
                $max = isset($values['max']) && $values['max'] !== '' ? (int) $values['max'] : null;
                $havingConditions = [];
                if ($min !== null) {
                    $havingConditions[] = 'order_count >= ' . $min;
                }
                if ($max !== null) {
                    $havingConditions[] = 'order_count <= ' . $max;
                }
                if (empty($havingConditions)) {
                    return [];
                }
                $results = Db::getInstance()->executeS('
                    SELECT c.id_customer, COUNT(o.id_order) as order_count
                    FROM ' . _DB_PREFIX_ . 'customer c
                    LEFT JOIN ' . _DB_PREFIX_ . 'orders o ON o.id_customer = c.id_customer AND o.valid = 1
                    WHERE c.active = 1 AND c.deleted = 0
                    GROUP BY c.id_customer
                    HAVING ' . implode(' AND ', $havingConditions) . '
                ');
                return array_column($results ?: [], 'id_customer');

            case 'by_turnover':
            case 'by_total_spent':
                $min = isset($values['min']) && $values['min'] !== '' ? (float) $values['min'] : null;
                $max = isset($values['max']) && $values['max'] !== '' ? (float) $values['max'] : null;
                $havingConditions = [];
                if ($min !== null) {
                    $havingConditions[] = 'total_spent >= ' . $min;
                }
                if ($max !== null) {
                    $havingConditions[] = 'total_spent <= ' . $max;
                }
                if (empty($havingConditions)) {
                    return [];
                }
                $results = Db::getInstance()->executeS('
                    SELECT c.id_customer, COALESCE(SUM(o.total_paid_tax_incl), 0) as total_spent
                    FROM ' . _DB_PREFIX_ . 'customer c
                    LEFT JOIN ' . _DB_PREFIX_ . 'orders o ON o.id_customer = c.id_customer AND o.valid = 1
                    WHERE c.active = 1 AND c.deleted = 0
                    GROUP BY c.id_customer
                    HAVING ' . implode(' AND ', $havingConditions) . '
                ');
                return array_column($results ?: [], 'id_customer');

            case 'by_active_status':
                $isActive = ($values === 'active');
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT c.id_customer
                    FROM ' . _DB_PREFIX_ . 'customer c
                    WHERE c.deleted = 0
                    AND c.active = ' . ($isActive ? '1' : '0') . '
                ');
                return array_column($results ?: [], 'id_customer');

            case 'by_newsletter':
                $isSubscribed = ($values === 'subscribed');
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT c.id_customer
                    FROM ' . _DB_PREFIX_ . 'customer c
                    WHERE c.active = 1 AND c.deleted = 0
                    AND c.newsletter = ' . ($isSubscribed ? '1' : '0') . '
                ');
                return array_column($results ?: [], 'id_customer');

            case 'by_guest':
            case 'by_is_guest':
                $isGuest = ($values === 'guest' || $values === true || $values === 'true' || $values === '1' || $values === 1);
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT c.id_customer
                    FROM ' . _DB_PREFIX_ . 'customer c
                    WHERE c.active = 1 AND c.deleted = 0
                    AND c.is_guest = ' . ($isGuest ? '1' : '0') . '
                ');
                return array_column($results ?: [], 'id_customer');

            case 'by_date_added':
                $from = isset($values['from']) ? pSQL($values['from']) : null;
                $to = isset($values['to']) ? pSQL($values['to']) : null;
                $conditions = ['c.active = 1', 'c.deleted = 0'];
                if ($from) {
                    $conditions[] = "DATE(c.date_add) >= '" . $from . "'";
                }
                if ($to) {
                    $conditions[] = "DATE(c.date_add) <= '" . $to . "'";
                }
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT c.id_customer
                    FROM ' . _DB_PREFIX_ . 'customer c
                    WHERE ' . implode(' AND ', $conditions) . '
                ');
                return array_column($results ?: [], 'id_customer');

            default:
                return [];
        }
    }

    // ========================================
    // Customer Group Methods
    // ========================================

    /**
     * Count all customer groups
     */
    public function countAllCustomerGroups($idLang, $idShop)
    {
        return (int) Db::getInstance()->getValue('
            SELECT COUNT(DISTINCT g.id_group)
            FROM ' . _DB_PREFIX_ . 'group g
            INNER JOIN ' . _DB_PREFIX_ . 'group_shop gs ON g.id_group = gs.id_group AND gs.id_shop = ' . (int) $idShop . '
        ');
    }

    /**
     * Get matching customer group IDs from groups
     */
    public function getMatchingCustomerGroupIds($groups, $idLang, $idShop)
    {
        $allMatchingIds = [];

        foreach ($groups as $group) {
            $includeMethod = $group['include']['method'] ?? 'all';
            $includeValues = $group['include']['values'] ?? [];

            $includedIds = $this->getCustomerGroupIdsByMethod($includeMethod, $includeValues, $idLang, $idShop);

            $excludedIds = [];
            $excludes = $group['excludes'] ?? [];
            if (!empty($group['exclude']['method'])) {
                $excludes = [$group['exclude']];
            }

            foreach ($excludes as $exclude) {
                if (empty($exclude['method'])) {
                    continue;
                }
                $excludeIds = $this->getCustomerGroupIdsByMethod($exclude['method'], $exclude['values'] ?? [], $idLang, $idShop);
                $excludedIds = array_merge($excludedIds, $excludeIds);
            }

            $groupResult = array_diff($includedIds, $excludedIds);
            $allMatchingIds = array_merge($allMatchingIds, $groupResult);
        }

        return array_unique($allMatchingIds);
    }

    /**
     * Get customer group IDs by selection method
     */
    public function getCustomerGroupIdsByMethod($method, $values, $idLang, $idShop)
    {
        switch ($method) {
            case 'all':
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT g.id_group
                    FROM ' . _DB_PREFIX_ . 'group g
                    INNER JOIN ' . _DB_PREFIX_ . 'group_shop gs ON g.id_group = gs.id_group AND gs.id_shop = ' . (int) $idShop . '
                ');
                return array_column($results ?: [], 'id_group');

            case 'specific':
            case 'by_customer_group':
                return array_map('intval', $values);

            case 'by_name_pattern':
                if (empty($values)) {
                    return [];
                }
                $patterns = is_array($values) ? $values : [$values];
                $conditions = [];
                foreach ($patterns as $pattern) {
                    $safe = pSQL($this->extractPatternString($pattern));
                    $conditions[] = "gl.name LIKE '%" . $safe . "%'";
                }
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT g.id_group
                    FROM ' . _DB_PREFIX_ . 'group g
                    INNER JOIN ' . _DB_PREFIX_ . 'group_shop gs ON g.id_group = gs.id_group AND gs.id_shop = ' . (int) $idShop . '
                    INNER JOIN ' . _DB_PREFIX_ . 'group_lang gl ON g.id_group = gl.id_group AND gl.id_lang = ' . (int) $idLang . '
                    WHERE (' . implode(' OR ', $conditions) . ')
                ');
                return array_column($results ?: [], 'id_group');

            case 'by_show_prices':
                $showPrices = ($values === true || $values === 'true' || $values === '1' || $values === 1);
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT g.id_group
                    FROM ' . _DB_PREFIX_ . 'group g
                    INNER JOIN ' . _DB_PREFIX_ . 'group_shop gs ON g.id_group = gs.id_group AND gs.id_shop = ' . (int) $idShop . '
                    WHERE g.show_prices = ' . ($showPrices ? '1' : '0') . '
                ');
                return array_column($results ?: [], 'id_group');

            case 'by_customer_count':
                $min = isset($values['min']) ? (int) $values['min'] : 0;
                $max = isset($values['max']) ? (int) $values['max'] : PHP_INT_MAX;
                $results = Db::getInstance()->executeS('
                    SELECT g.id_group, COUNT(DISTINCT cg.id_customer) as customer_count
                    FROM ' . _DB_PREFIX_ . 'group g
                    INNER JOIN ' . _DB_PREFIX_ . 'group_shop gs ON g.id_group = gs.id_group AND gs.id_shop = ' . (int) $idShop . '
                    LEFT JOIN ' . _DB_PREFIX_ . 'customer_group cg ON g.id_group = cg.id_group
                    GROUP BY g.id_group
                    HAVING customer_count >= ' . $min . ' AND customer_count <= ' . $max . '
                ');
                return array_column($results ?: [], 'id_group');

            case 'by_price_display':
                if (empty($values) || empty($values[0])) {
                    return [];
                }
                $priceDisplayType = $values[0] === 'tax_excl' ? 0 : 1;
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT g.id_group
                    FROM ' . _DB_PREFIX_ . 'group g
                    INNER JOIN ' . _DB_PREFIX_ . 'group_shop gs ON g.id_group = gs.id_group AND gs.id_shop = ' . (int) $idShop . '
                    WHERE g.price_display_method = ' . (int) $priceDisplayType . '
                ');
                return array_column($results ?: [], 'id_group');

            default:
                return [];
        }
    }

    // ========================================
    // Carrier Methods
    // ========================================

    /**
     * Count all active carriers
     */
    public function countAllActiveCarriers($idLang, $idShop)
    {
        return (int) Db::getInstance()->getValue('
            SELECT COUNT(DISTINCT c.id_carrier)
            FROM ' . _DB_PREFIX_ . 'carrier c
            WHERE c.active = 1 AND c.deleted = 0
        ');
    }

    /**
     * Get matching carrier IDs from groups
     */
    public function getMatchingCarrierIds($groups, $idLang, $idShop)
    {
        $allMatchingIds = [];

        foreach ($groups as $group) {
            $includeMethod = $group['include']['method'] ?? 'all';
            $includeValues = $group['include']['values'] ?? [];

            $includedIds = $this->getCarrierIdsByMethod($includeMethod, $includeValues, $idLang, $idShop);

            $excludedIds = [];
            $excludes = $group['excludes'] ?? [];
            if (!empty($group['exclude']['method'])) {
                $excludes = [$group['exclude']];
            }

            foreach ($excludes as $exclude) {
                if (empty($exclude['method'])) {
                    continue;
                }
                $excludeIds = $this->getCarrierIdsByMethod($exclude['method'], $exclude['values'] ?? [], $idLang, $idShop);
                $excludedIds = array_merge($excludedIds, $excludeIds);
            }

            $groupResult = array_diff($includedIds, $excludedIds);
            $allMatchingIds = array_merge($allMatchingIds, $groupResult);
        }

        return array_unique($allMatchingIds);
    }

    /**
     * Get carrier IDs by selection method
     */
    public function getCarrierIdsByMethod($method, $values, $idLang, $idShop)
    {
        switch ($method) {
            case 'all':
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT c.id_carrier
                    FROM ' . _DB_PREFIX_ . 'carrier c
                    WHERE c.active = 1 AND c.deleted = 0
                ');
                return array_column($results ?: [], 'id_carrier');

            case 'specific':
            case 'by_carrier':
                return array_map('intval', $values);

            case 'by_zone':
                if (empty($values)) {
                    return [];
                }
                $zoneIds = array_map('intval', $values);
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT cz.id_carrier
                    FROM ' . _DB_PREFIX_ . 'carrier_zone cz
                    INNER JOIN ' . _DB_PREFIX_ . 'carrier c ON cz.id_carrier = c.id_carrier
                    WHERE c.active = 1 AND c.deleted = 0
                    AND cz.id_zone IN (' . implode(',', $zoneIds) . ')
                ');
                return array_column($results ?: [], 'id_carrier');

            case 'by_customer_group':
                if (empty($values)) {
                    return [];
                }
                $groupIds = array_map('intval', $values);
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT cg.id_carrier
                    FROM ' . _DB_PREFIX_ . 'carrier_group cg
                    INNER JOIN ' . _DB_PREFIX_ . 'carrier c ON cg.id_carrier = c.id_carrier
                    WHERE c.active = 1 AND c.deleted = 0
                    AND cg.id_group IN (' . implode(',', $groupIds) . ')
                ');
                return array_column($results ?: [], 'id_carrier');

            case 'by_name_pattern':
                if (empty($values)) {
                    return [];
                }
                $patterns = is_array($values) ? $values : [$values];
                $conditions = [];
                foreach ($patterns as $pattern) {
                    $safe = pSQL($this->extractPatternString($pattern));
                    $conditions[] = "c.name LIKE '%" . $safe . "%'";
                }
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT c.id_carrier
                    FROM ' . _DB_PREFIX_ . 'carrier c
                    WHERE c.active = 1 AND c.deleted = 0
                    AND (' . implode(' OR ', $conditions) . ')
                ');
                return array_column($results ?: [], 'id_carrier');

            case 'by_is_free':
                $isFree = ($values === true || $values === 'true' || $values === '1' || $values === 1);
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT c.id_carrier
                    FROM ' . _DB_PREFIX_ . 'carrier c
                    WHERE c.active = 1 AND c.deleted = 0
                    AND c.is_free = ' . ($isFree ? '1' : '0') . '
                ');
                return array_column($results ?: [], 'id_carrier');

            case 'by_active_status':
                if (empty($values) || empty($values[0])) {
                    return [];
                }
                $active = $values[0] === 'active' ? 1 : 0;
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT c.id_carrier
                    FROM ' . _DB_PREFIX_ . 'carrier c
                    WHERE c.deleted = 0
                    AND c.active = ' . (int) $active . '
                ');
                return array_column($results ?: [], 'id_carrier');

            case 'by_shipping_handling':
                if (empty($values) || empty($values[0])) {
                    return [];
                }
                $withHandling = $values[0] === 'with_handling' ? 1 : 0;
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT c.id_carrier
                    FROM ' . _DB_PREFIX_ . 'carrier c
                    WHERE c.active = 1 AND c.deleted = 0
                    AND c.shipping_handling = ' . (int) $withHandling . '
                ');
                return array_column($results ?: [], 'id_carrier');

            case 'by_free_shipping':
                if (empty($values) || empty($values[0])) {
                    return [];
                }
                $isFree = $values[0] === 'free' ? 1 : 0;
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT c.id_carrier
                    FROM ' . _DB_PREFIX_ . 'carrier c
                    WHERE c.active = 1 AND c.deleted = 0
                    AND c.is_free = ' . (int) $isFree . '
                ');
                return array_column($results ?: [], 'id_carrier');

            case 'by_price_range':
            case 'by_shipping_cost':
                if (empty($values)) {
                    return [];
                }
                $min = isset($values['min']) ? (float) $values['min'] : null;
                $max = isset($values['max']) ? (float) $values['max'] : null;
                $conditions = ['c.active = 1', 'c.deleted = 0'];
                if ($min !== null) {
                    $conditions[] = 'dr.price >= ' . $min;
                }
                if ($max !== null) {
                    $conditions[] = 'dr.price <= ' . $max;
                }
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT c.id_carrier
                    FROM ' . _DB_PREFIX_ . 'carrier c
                    INNER JOIN ' . _DB_PREFIX_ . 'delivery dr ON c.id_carrier = dr.id_carrier
                    WHERE ' . implode(' AND ', $conditions) . '
                ');
                return array_column($results ?: [], 'id_carrier');

            case 'by_weight_range':
            case 'by_max_weight':
                if (empty($values)) {
                    return [];
                }
                $min = isset($values['min']) ? (float) $values['min'] : null;
                $max = isset($values['max']) ? (float) $values['max'] : null;
                $conditions = ['c.active = 1', 'c.deleted = 0'];
                if ($min !== null) {
                    $conditions[] = 'c.max_weight >= ' . $min;
                }
                if ($max !== null) {
                    $conditions[] = 'c.max_weight <= ' . $max;
                }
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT c.id_carrier
                    FROM ' . _DB_PREFIX_ . 'carrier c
                    WHERE ' . implode(' AND ', $conditions) . '
                ');
                return array_column($results ?: [], 'id_carrier');

            default:
                return [];
        }
    }

    // ========================================
    // Zone Methods
    // ========================================

    /**
     * Count all active zones
     */
    public function countAllActiveZones($idLang, $idShop)
    {
        return (int) Db::getInstance()->getValue('
            SELECT COUNT(DISTINCT z.id_zone)
            FROM ' . _DB_PREFIX_ . 'zone z
            WHERE z.active = 1
        ');
    }

    /**
     * Get matching zone IDs from groups
     */
    public function getMatchingZoneIds($groups, $idLang, $idShop)
    {
        $allMatchingIds = [];

        foreach ($groups as $group) {
            $includeMethod = $group['include']['method'] ?? 'all';
            $includeValues = $group['include']['values'] ?? [];

            $includedIds = $this->getZoneIdsByMethod($includeMethod, $includeValues, $idLang, $idShop);

            $excludedIds = [];
            $excludes = $group['excludes'] ?? [];
            if (!empty($group['exclude']['method'])) {
                $excludes = [$group['exclude']];
            }

            foreach ($excludes as $exclude) {
                if (empty($exclude['method'])) {
                    continue;
                }
                $excludeIds = $this->getZoneIdsByMethod($exclude['method'], $exclude['values'] ?? [], $idLang, $idShop);
                $excludedIds = array_merge($excludedIds, $excludeIds);
            }

            $groupResult = array_diff($includedIds, $excludedIds);
            $allMatchingIds = array_merge($allMatchingIds, $groupResult);
        }

        return array_unique($allMatchingIds);
    }

    /**
     * Get zone IDs by selection method
     */
    public function getZoneIdsByMethod($method, $values, $idLang, $idShop)
    {
        switch ($method) {
            case 'all':
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT z.id_zone
                    FROM ' . _DB_PREFIX_ . 'zone z
                    WHERE z.active = 1
                ');
                return array_column($results ?: [], 'id_zone');

            case 'specific':
            case 'by_zone':
                return array_map('intval', $values);

            case 'by_name_pattern':
                if (empty($values)) {
                    return [];
                }
                $patterns = is_array($values) ? $values : [$values];
                $conditions = [];
                foreach ($patterns as $pattern) {
                    $safe = pSQL($this->extractPatternString($pattern));
                    $conditions[] = "z.name LIKE '%" . $safe . "%'";
                }
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT z.id_zone
                    FROM ' . _DB_PREFIX_ . 'zone z
                    WHERE z.active = 1
                    AND (' . implode(' OR ', $conditions) . ')
                ');
                return array_column($results ?: [], 'id_zone');

            case 'by_active_status':
                $isActive = ($values === 'active' || $values === true || $values === 'true' || $values === '1' || $values === 1);
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT z.id_zone
                    FROM ' . _DB_PREFIX_ . 'zone z
                    WHERE z.active = ' . ($isActive ? '1' : '0') . '
                ');
                return array_column($results ?: [], 'id_zone');

            default:
                return [];
        }
    }

    // ========================================
    // Country Methods
    // ========================================

    /**
     * Count all active countries
     */
    public function countAllActiveCountries($idLang, $idShop)
    {
        return (int) Db::getInstance()->getValue('
            SELECT COUNT(DISTINCT c.id_country)
            FROM ' . _DB_PREFIX_ . 'country c
            INNER JOIN ' . _DB_PREFIX_ . 'country_shop cs ON c.id_country = cs.id_country AND cs.id_shop = ' . (int) $idShop . '
            WHERE c.active = 1
        ');
    }

    /**
     * Get matching country IDs from groups
     */
    public function getMatchingCountryIds($groups, $idLang, $idShop)
    {
        $allMatchingIds = [];

        foreach ($groups as $group) {
            $includeMethod = $group['include']['method'] ?? 'all';
            $includeValues = $group['include']['values'] ?? [];

            $includedIds = $this->getCountryIdsByMethod($includeMethod, $includeValues, $idLang, $idShop);

            $excludedIds = [];
            $excludes = $group['excludes'] ?? [];
            if (!empty($group['exclude']['method'])) {
                $excludes = [$group['exclude']];
            }

            foreach ($excludes as $exclude) {
                if (empty($exclude['method'])) {
                    continue;
                }
                $excludeIds = $this->getCountryIdsByMethod($exclude['method'], $exclude['values'] ?? [], $idLang, $idShop);
                $excludedIds = array_merge($excludedIds, $excludeIds);
            }

            $groupResult = array_diff($includedIds, $excludedIds);
            $allMatchingIds = array_merge($allMatchingIds, $groupResult);
        }

        return array_unique($allMatchingIds);
    }

    /**
     * Get country IDs by selection method
     */
    public function getCountryIdsByMethod($method, $values, $idLang, $idShop)
    {
        switch ($method) {
            case 'all':
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT c.id_country
                    FROM ' . _DB_PREFIX_ . 'country c
                    INNER JOIN ' . _DB_PREFIX_ . 'country_shop cs ON c.id_country = cs.id_country AND cs.id_shop = ' . (int) $idShop . '
                    WHERE c.active = 1
                ');
                return array_column($results ?: [], 'id_country');

            case 'specific':
            case 'by_country':
                return array_map('intval', $values);

            case 'by_iso_code':
                if (empty($values)) {
                    return [];
                }
                $patterns = is_array($values) ? $values : [$values];
                $conditions = [];
                foreach ($patterns as $pattern) {
                    $safe = pSQL($this->extractPatternString($pattern));
                    $conditions[] = "c.iso_code LIKE '%" . $safe . "%'";
                }
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT c.id_country
                    FROM ' . _DB_PREFIX_ . 'country c
                    INNER JOIN ' . _DB_PREFIX_ . 'country_shop cs ON c.id_country = cs.id_country AND cs.id_shop = ' . (int) $idShop . '
                    WHERE c.active = 1
                    AND (' . implode(' OR ', $conditions) . ')
                ');
                return array_column($results ?: [], 'id_country');

            case 'by_needs_zipcode':
                $needsZip = ($values === true || $values === 'true' || $values === '1' || $values === 1);
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT c.id_country
                    FROM ' . _DB_PREFIX_ . 'country c
                    INNER JOIN ' . _DB_PREFIX_ . 'country_shop cs ON c.id_country = cs.id_country AND cs.id_shop = ' . (int) $idShop . '
                    WHERE c.active = 1
                    AND c.need_zip_code = ' . ($needsZip ? '1' : '0') . '
                ');
                return array_column($results ?: [], 'id_country');

            case 'by_zone':
                if (empty($values)) {
                    return [];
                }
                $zoneIds = array_map('intval', $values);
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT c.id_country
                    FROM ' . _DB_PREFIX_ . 'country c
                    INNER JOIN ' . _DB_PREFIX_ . 'country_shop cs ON c.id_country = cs.id_country AND cs.id_shop = ' . (int) $idShop . '
                    WHERE c.active = 1
                    AND c.id_zone IN (' . implode(',', $zoneIds) . ')
                ');
                return array_column($results ?: [], 'id_country');

            case 'by_name_pattern':
                if (empty($values) || empty($values[0])) {
                    return [];
                }
                $pattern = '%' . pSQL($this->extractPatternString($values[0])) . '%';
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT c.id_country
                    FROM ' . _DB_PREFIX_ . 'country c
                    INNER JOIN ' . _DB_PREFIX_ . 'country_shop cs ON c.id_country = cs.id_country AND cs.id_shop = ' . (int) $idShop . '
                    INNER JOIN ' . _DB_PREFIX_ . 'country_lang cl ON c.id_country = cl.id_country AND cl.id_lang = ' . (int) $idLang . '
                    WHERE c.active = 1
                    AND cl.name LIKE \'' . $pattern . '\'
                ');
                return array_column($results ?: [], 'id_country');

            case 'by_active_status':
                if (empty($values) || empty($values[0])) {
                    return [];
                }
                $active = $values[0] === 'active' ? 1 : 0;
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT c.id_country
                    FROM ' . _DB_PREFIX_ . 'country c
                    INNER JOIN ' . _DB_PREFIX_ . 'country_shop cs ON c.id_country = cs.id_country AND cs.id_shop = ' . (int) $idShop . '
                    WHERE c.active = ' . (int) $active . '
                ');
                return array_column($results ?: [], 'id_country');

            case 'by_contains_states':
                if (empty($values) || empty($values[0])) {
                    return [];
                }
                $containsStates = $values[0] === 'yes' ? 1 : 0;
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT c.id_country
                    FROM ' . _DB_PREFIX_ . 'country c
                    INNER JOIN ' . _DB_PREFIX_ . 'country_shop cs ON c.id_country = cs.id_country AND cs.id_shop = ' . (int) $idShop . '
                    WHERE c.active = 1
                    AND c.contains_states = ' . (int) $containsStates . '
                ');
                return array_column($results ?: [], 'id_country');

            case 'by_need_zip_code':
                if (empty($values) || empty($values[0])) {
                    return [];
                }
                $needZip = $values[0] === 'yes' ? 1 : 0;
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT c.id_country
                    FROM ' . _DB_PREFIX_ . 'country c
                    INNER JOIN ' . _DB_PREFIX_ . 'country_shop cs ON c.id_country = cs.id_country AND cs.id_shop = ' . (int) $idShop . '
                    WHERE c.active = 1
                    AND c.need_zip_code = ' . (int) $needZip . '
                ');
                return array_column($results ?: [], 'id_country');

            case 'by_zip_format':
                if (empty($values) || empty($values[0])) {
                    return [];
                }
                $pattern = '%' . pSQL($this->extractPatternString($values[0])) . '%';
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT c.id_country
                    FROM ' . _DB_PREFIX_ . 'country c
                    INNER JOIN ' . _DB_PREFIX_ . 'country_shop cs ON c.id_country = cs.id_country AND cs.id_shop = ' . (int) $idShop . '
                    WHERE c.active = 1
                    AND c.zip_code_format LIKE \'' . $pattern . '\'
                ');
                return array_column($results ?: [], 'id_country');

            case 'by_need_identification':
                if (empty($values) || empty($values[0])) {
                    return [];
                }
                $needIdent = $values[0] === 'yes' ? 1 : 0;
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT c.id_country
                    FROM ' . _DB_PREFIX_ . 'country c
                    INNER JOIN ' . _DB_PREFIX_ . 'country_shop cs ON c.id_country = cs.id_country AND cs.id_shop = ' . (int) $idShop . '
                    WHERE c.active = 1
                    AND c.need_identification_number = ' . (int) $needIdent . '
                ');
                return array_column($results ?: [], 'id_country');

            default:
                return [];
        }
    }

    // ========================================
    // Currency Methods
    // ========================================

    /**
     * Count all active currencies
     */
    public function countAllActiveCurrencies($idLang, $idShop)
    {
        return (int) Db::getInstance()->getValue('
            SELECT COUNT(DISTINCT c.id_currency)
            FROM ' . _DB_PREFIX_ . 'currency c
            INNER JOIN ' . _DB_PREFIX_ . 'currency_shop cs ON c.id_currency = cs.id_currency AND cs.id_shop = ' . (int) $idShop . '
            WHERE c.active = 1
        ');
    }

    /**
     * Get matching currency IDs from groups
     */
    public function getMatchingCurrencyIds($groups, $idLang, $idShop)
    {
        $allMatchingIds = [];

        foreach ($groups as $group) {
            $includeMethod = $group['include']['method'] ?? 'all';
            $includeValues = $group['include']['values'] ?? [];

            $includedIds = $this->getCurrencyIdsByMethod($includeMethod, $includeValues, $idLang, $idShop);

            $excludedIds = [];
            $excludes = $group['excludes'] ?? [];
            if (!empty($group['exclude']['method'])) {
                $excludes = [$group['exclude']];
            }

            foreach ($excludes as $exclude) {
                if (empty($exclude['method'])) {
                    continue;
                }
                $excludeIds = $this->getCurrencyIdsByMethod($exclude['method'], $exclude['values'] ?? [], $idLang, $idShop);
                $excludedIds = array_merge($excludedIds, $excludeIds);
            }

            $groupResult = array_diff($includedIds, $excludedIds);
            $allMatchingIds = array_merge($allMatchingIds, $groupResult);
        }

        return array_unique($allMatchingIds);
    }

    /**
     * Get currency IDs by selection method
     */
    public function getCurrencyIdsByMethod($method, $values, $idLang, $idShop)
    {
        switch ($method) {
            case 'all':
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT c.id_currency
                    FROM ' . _DB_PREFIX_ . 'currency c
                    INNER JOIN ' . _DB_PREFIX_ . 'currency_shop cs ON c.id_currency = cs.id_currency AND cs.id_shop = ' . (int) $idShop . '
                    WHERE c.active = 1
                ');
                return array_column($results ?: [], 'id_currency');

            case 'specific':
            case 'by_currency':
                return array_map('intval', $values);

            case 'by_name_pattern':
                if (empty($values)) {
                    return [];
                }
                $patterns = is_array($values) ? $values : [$values];
                $conditions = [];
                foreach ($patterns as $pattern) {
                    $safe = pSQL($this->extractPatternString($pattern));
                    $conditions[] = "(c.name LIKE '%" . $safe . "%' OR c.iso_code LIKE '%" . $safe . "%')";
                }
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT c.id_currency
                    FROM ' . _DB_PREFIX_ . 'currency c
                    INNER JOIN ' . _DB_PREFIX_ . 'currency_shop cs ON c.id_currency = cs.id_currency AND cs.id_shop = ' . (int) $idShop . '
                    WHERE c.active = 1
                    AND (' . implode(' OR ', $conditions) . ')
                ');
                return array_column($results ?: [], 'id_currency');

            case 'by_iso_code':
                if (empty($values)) {
                    return [];
                }
                $patterns = is_array($values) ? $values : [$values];
                $conditions = [];
                foreach ($patterns as $pattern) {
                    $safe = pSQL($this->extractPatternString($pattern));
                    $conditions[] = "c.iso_code LIKE '%" . $safe . "%'";
                }
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT c.id_currency
                    FROM ' . _DB_PREFIX_ . 'currency c
                    INNER JOIN ' . _DB_PREFIX_ . 'currency_shop cs ON c.id_currency = cs.id_currency AND cs.id_shop = ' . (int) $idShop . '
                    WHERE c.active = 1
                    AND (' . implode(' OR ', $conditions) . ')
                ');
                return array_column($results ?: [], 'id_currency');

            case 'by_conversion_rate':
                $min = isset($values['min']) ? (float) $values['min'] : 0;
                $max = isset($values['max']) ? (float) $values['max'] : PHP_INT_MAX;
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT c.id_currency
                    FROM ' . _DB_PREFIX_ . 'currency c
                    INNER JOIN ' . _DB_PREFIX_ . 'currency_shop cs ON c.id_currency = cs.id_currency AND cs.id_shop = ' . (int) $idShop . '
                    WHERE c.active = 1
                    AND c.conversion_rate >= ' . $min . ' AND c.conversion_rate <= ' . $max . '
                ');
                return array_column($results ?: [], 'id_currency');

            case 'by_active_status':
                $isActive = ($values === 'active');
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT c.id_currency
                    FROM ' . _DB_PREFIX_ . 'currency c
                    INNER JOIN ' . _DB_PREFIX_ . 'currency_shop cs ON c.id_currency = cs.id_currency AND cs.id_shop = ' . (int) $idShop . '
                    WHERE c.active = ' . ($isActive ? '1' : '0') . '
                ');
                return array_column($results ?: [], 'id_currency');

            default:
                return [];
        }
    }

    // ========================================
    // Language Methods
    // ========================================

    /**
     * Count all active languages
     */
    public function countAllActiveLanguages($idLang, $idShop)
    {
        return (int) Db::getInstance()->getValue('
            SELECT COUNT(DISTINCT l.id_lang)
            FROM ' . _DB_PREFIX_ . 'lang l
            INNER JOIN ' . _DB_PREFIX_ . 'lang_shop ls ON l.id_lang = ls.id_lang AND ls.id_shop = ' . (int) $idShop . '
            WHERE l.active = 1
        ');
    }

    /**
     * Get matching language IDs from groups
     */
    public function getMatchingLanguageIds($groups, $idLang, $idShop)
    {
        $allMatchingIds = [];

        foreach ($groups as $group) {
            $includeMethod = $group['include']['method'] ?? 'all';
            $includeValues = $group['include']['values'] ?? [];

            $includedIds = $this->getLanguageIdsByMethod($includeMethod, $includeValues, $idLang, $idShop);

            $excludedIds = [];
            $excludes = $group['excludes'] ?? [];
            if (!empty($group['exclude']['method'])) {
                $excludes = [$group['exclude']];
            }

            foreach ($excludes as $exclude) {
                if (empty($exclude['method'])) {
                    continue;
                }
                $excludeIds = $this->getLanguageIdsByMethod($exclude['method'], $exclude['values'] ?? [], $idLang, $idShop);
                $excludedIds = array_merge($excludedIds, $excludeIds);
            }

            $groupResult = array_diff($includedIds, $excludedIds);
            $allMatchingIds = array_merge($allMatchingIds, $groupResult);
        }

        return array_unique($allMatchingIds);
    }

    /**
     * Get language IDs by selection method
     */
    public function getLanguageIdsByMethod($method, $values, $idLang, $idShop)
    {
        switch ($method) {
            case 'all':
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT l.id_lang
                    FROM ' . _DB_PREFIX_ . 'lang l
                    INNER JOIN ' . _DB_PREFIX_ . 'lang_shop ls ON l.id_lang = ls.id_lang AND ls.id_shop = ' . (int) $idShop . '
                    WHERE l.active = 1
                ');
                return array_column($results ?: [], 'id_lang');

            case 'specific':
            case 'by_language':
                return array_map('intval', $values);

            case 'by_name_pattern':
                if (empty($values)) {
                    return [];
                }
                $patterns = is_array($values) ? $values : [$values];
                $conditions = [];
                foreach ($patterns as $pattern) {
                    $safe = pSQL($this->extractPatternString($pattern));
                    $conditions[] = "(l.name LIKE '%" . $safe . "%' OR l.iso_code LIKE '%" . $safe . "%')";
                }
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT l.id_lang
                    FROM ' . _DB_PREFIX_ . 'lang l
                    INNER JOIN ' . _DB_PREFIX_ . 'lang_shop ls ON l.id_lang = ls.id_lang AND ls.id_shop = ' . (int) $idShop . '
                    WHERE l.active = 1
                    AND (' . implode(' OR ', $conditions) . ')
                ');
                return array_column($results ?: [], 'id_lang');

            case 'by_iso_code':
                if (empty($values)) {
                    return [];
                }
                $patterns = is_array($values) ? $values : [$values];
                $conditions = [];
                foreach ($patterns as $pattern) {
                    $safe = pSQL($this->extractPatternString($pattern));
                    $conditions[] = "l.iso_code LIKE '%" . $safe . "%'";
                }
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT l.id_lang
                    FROM ' . _DB_PREFIX_ . 'lang l
                    INNER JOIN ' . _DB_PREFIX_ . 'lang_shop ls ON l.id_lang = ls.id_lang AND ls.id_shop = ' . (int) $idShop . '
                    WHERE l.active = 1
                    AND (' . implode(' OR ', $conditions) . ')
                ');
                return array_column($results ?: [], 'id_lang');

            case 'by_active_status':
                $isActive = ($values === 'active' || $values === true || $values === 'true' || $values === '1' || $values === 1);
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT l.id_lang
                    FROM ' . _DB_PREFIX_ . 'lang l
                    INNER JOIN ' . _DB_PREFIX_ . 'lang_shop ls ON l.id_lang = ls.id_lang AND ls.id_shop = ' . (int) $idShop . '
                    WHERE l.active = ' . ($isActive ? '1' : '0') . '
                ');
                return array_column($results ?: [], 'id_lang');

            case 'by_rtl':
            case 'by_is_rtl':
                $isRtl = ($values === 'yes' || $values === true || $values === 'true' || $values === '1' || $values === 1);
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT l.id_lang
                    FROM ' . _DB_PREFIX_ . 'lang l
                    INNER JOIN ' . _DB_PREFIX_ . 'lang_shop ls ON l.id_lang = ls.id_lang AND ls.id_shop = ' . (int) $idShop . '
                    WHERE l.active = 1
                    AND l.is_rtl = ' . ($isRtl ? '1' : '0') . '
                ');
                return array_column($results ?: [], 'id_lang');

            default:
                return [];
        }
    }

    // ========================================
    // Shop Methods
    // ========================================

    /**
     * Count all active shops
     */
    public function countAllActiveShops($idLang, $idShop)
    {
        return (int) Db::getInstance()->getValue('
            SELECT COUNT(DISTINCT s.id_shop)
            FROM ' . _DB_PREFIX_ . 'shop s
            WHERE s.active = 1
        ');
    }

    /**
     * Get matching shop IDs from groups
     */
    public function getMatchingShopIds($groups, $idLang, $idShop)
    {
        $allMatchingIds = [];

        foreach ($groups as $group) {
            $includeMethod = $group['include']['method'] ?? 'all';
            $includeValues = $group['include']['values'] ?? [];

            $includedIds = $this->getShopIdsByMethod($includeMethod, $includeValues, $idLang, $idShop);

            $excludedIds = [];
            $excludes = $group['excludes'] ?? [];
            if (!empty($group['exclude']['method'])) {
                $excludes = [$group['exclude']];
            }

            foreach ($excludes as $exclude) {
                if (empty($exclude['method'])) {
                    continue;
                }
                $excludeIds = $this->getShopIdsByMethod($exclude['method'], $exclude['values'] ?? [], $idLang, $idShop);
                $excludedIds = array_merge($excludedIds, $excludeIds);
            }

            $groupResult = array_diff($includedIds, $excludedIds);
            $allMatchingIds = array_merge($allMatchingIds, $groupResult);
        }

        return array_unique($allMatchingIds);
    }

    /**
     * Get shop IDs by selection method
     */
    public function getShopIdsByMethod($method, $values, $idLang, $idShop)
    {
        switch ($method) {
            case 'all':
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT s.id_shop
                    FROM ' . _DB_PREFIX_ . 'shop s
                    WHERE s.active = 1
                ');
                return array_column($results ?: [], 'id_shop');

            case 'specific':
            case 'by_shop':
                return array_map('intval', $values);

            case 'by_group':
            case 'by_shop_group':
                if (empty($values)) {
                    return [];
                }
                $groupIds = array_map('intval', $values);
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT s.id_shop
                    FROM ' . _DB_PREFIX_ . 'shop s
                    WHERE s.active = 1
                    AND s.id_shop_group IN (' . implode(',', $groupIds) . ')
                ');
                return array_column($results ?: [], 'id_shop');

            case 'by_name_pattern':
                if (empty($values)) {
                    return [];
                }
                $patterns = is_array($values) ? $values : [$values];
                $conditions = [];
                foreach ($patterns as $pattern) {
                    $conditions[] = "s.name LIKE '%" . pSQL($this->extractPatternString($pattern)) . "%'";
                }
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT s.id_shop
                    FROM ' . _DB_PREFIX_ . 'shop s
                    WHERE s.active = 1
                    AND (' . implode(' OR ', $conditions) . ')
                ');
                return array_column($results ?: [], 'id_shop');

            case 'by_active_status':
                $isActive = ($values === 'active' || $values === true || $values === 'true' || $values === '1' || $values === 1);
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT s.id_shop
                    FROM ' . _DB_PREFIX_ . 'shop s
                    WHERE s.active = ' . ($isActive ? '1' : '0') . '
                ');
                return array_column($results ?: [], 'id_shop');

            default:
                return [];
        }
    }

    // ========================================
    // Shop Group Methods
    // ========================================

    /**
     * Count all shop groups
     */
    public function countAllShopGroups($idLang, $idShop)
    {
        return (int) Db::getInstance()->getValue('
            SELECT COUNT(DISTINCT sg.id_shop_group)
            FROM ' . _DB_PREFIX_ . 'shop_group sg
        ');
    }

    /**
     * Get matching shop group IDs from groups
     */
    public function getMatchingShopGroupIds($groups, $idLang, $idShop)
    {
        $allMatchingIds = [];

        foreach ($groups as $group) {
            $includeMethod = $group['include']['method'] ?? 'all';
            $includeValues = $group['include']['values'] ?? [];

            $includedIds = $this->getShopGroupIdsByMethod($includeMethod, $includeValues, $idLang, $idShop);

            $excludedIds = [];
            $excludes = $group['excludes'] ?? [];
            if (!empty($group['exclude']['method'])) {
                $excludes = [$group['exclude']];
            }

            foreach ($excludes as $exclude) {
                if (empty($exclude['method'])) {
                    continue;
                }
                $excludeIds = $this->getShopGroupIdsByMethod($exclude['method'], $exclude['values'] ?? [], $idLang, $idShop);
                $excludedIds = array_merge($excludedIds, $excludeIds);
            }

            $groupResult = array_diff($includedIds, $excludedIds);
            $allMatchingIds = array_merge($allMatchingIds, $groupResult);
        }

        return array_unique($allMatchingIds);
    }

    /**
     * Get shop group IDs by selection method
     */
    public function getShopGroupIdsByMethod($method, $values, $idLang, $idShop)
    {
        switch ($method) {
            case 'all':
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT sg.id_shop_group
                    FROM ' . _DB_PREFIX_ . 'shop_group sg
                ');
                return array_column($results ?: [], 'id_shop_group');

            case 'specific':
            case 'by_shop_group':
                return array_map('intval', $values);

            case 'by_name_pattern':
                if (empty($values)) {
                    return [];
                }
                $patterns = is_array($values) ? $values : [$values];
                $conditions = [];
                foreach ($patterns as $pattern) {
                    $conditions[] = "sg.name LIKE '%" . pSQL($this->extractPatternString($pattern)) . "%'";
                }
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT sg.id_shop_group
                    FROM ' . _DB_PREFIX_ . 'shop_group sg
                    WHERE (' . implode(' OR ', $conditions) . ')
                ');
                return array_column($results ?: [], 'id_shop_group');

            case 'by_share_customer':
                $shareCustomer = ($values === true || $values === 'true' || $values === '1' || $values === 1);
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT sg.id_shop_group
                    FROM ' . _DB_PREFIX_ . 'shop_group sg
                    WHERE sg.share_customer = ' . ($shareCustomer ? '1' : '0') . '
                ');
                return array_column($results ?: [], 'id_shop_group');

            case 'by_share_order':
                $shareOrder = ($values === true || $values === 'true' || $values === '1' || $values === 1);
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT sg.id_shop_group
                    FROM ' . _DB_PREFIX_ . 'shop_group sg
                    WHERE sg.share_order = ' . ($shareOrder ? '1' : '0') . '
                ');
                return array_column($results ?: [], 'id_shop_group');

            case 'by_share_stock':
                $shareStock = ($values === true || $values === 'true' || $values === '1' || $values === 1);
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT sg.id_shop_group
                    FROM ' . _DB_PREFIX_ . 'shop_group sg
                    WHERE sg.share_stock = ' . ($shareStock ? '1' : '0') . '
                ');
                return array_column($results ?: [], 'id_shop_group');

            default:
                return [];
        }
    }

    // ========================================
    // Profile Methods
    // ========================================

    /**
     * Count all profiles
     */
    public function countAllActiveProfiles($idLang, $idShop)
    {
        return (int) Db::getInstance()->getValue('
            SELECT COUNT(DISTINCT p.id_profile)
            FROM ' . _DB_PREFIX_ . 'profile p
        ');
    }

    /**
     * Get matching profile IDs from groups
     */
    public function getMatchingProfileIds($groups, $idLang, $idShop)
    {
        $allMatchingIds = [];

        foreach ($groups as $group) {
            $includeMethod = $group['include']['method'] ?? 'all';
            $includeValues = $group['include']['values'] ?? [];

            $includedIds = $this->getProfileIdsByMethod($includeMethod, $includeValues, $idLang, $idShop);

            $excludedIds = [];
            $excludes = $group['excludes'] ?? [];
            if (!empty($group['exclude']['method'])) {
                $excludes = [$group['exclude']];
            }

            foreach ($excludes as $exclude) {
                if (empty($exclude['method'])) {
                    continue;
                }
                $excludeIds = $this->getProfileIdsByMethod($exclude['method'], $exclude['values'] ?? [], $idLang, $idShop);
                $excludedIds = array_merge($excludedIds, $excludeIds);
            }

            $groupResult = array_diff($includedIds, $excludedIds);
            $allMatchingIds = array_merge($allMatchingIds, $groupResult);
        }

        return array_unique($allMatchingIds);
    }

    /**
     * Get profile IDs by selection method
     */
    public function getProfileIdsByMethod($method, $values, $idLang, $idShop)
    {
        switch ($method) {
            case 'all':
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT p.id_profile
                    FROM ' . _DB_PREFIX_ . 'profile p
                ');
                return array_column($results ?: [], 'id_profile');

            case 'specific':
            case 'by_profile':
                return array_map('intval', $values);

            case 'by_name_pattern':
                if (empty($values)) {
                    return [];
                }
                $patterns = is_array($values) ? $values : [$values];
                $conditions = [];
                foreach ($patterns as $pattern) {
                    $conditions[] = "pl.name LIKE '%" . pSQL($this->extractPatternString($pattern)) . "%'";
                }
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT p.id_profile
                    FROM ' . _DB_PREFIX_ . 'profile p
                    INNER JOIN ' . _DB_PREFIX_ . 'profile_lang pl ON p.id_profile = pl.id_profile AND pl.id_lang = ' . (int) $idLang . '
                    WHERE (' . implode(' OR ', $conditions) . ')
                ');
                return array_column($results ?: [], 'id_profile');

            default:
                return [];
        }
    }

    // ========================================
    // Order State Methods
    // ========================================

    /**
     * Count all order states
     */
    public function countAllActiveOrderStates($idLang, $idShop)
    {
        return (int) Db::getInstance()->getValue('
            SELECT COUNT(DISTINCT os.id_order_state)
            FROM ' . _DB_PREFIX_ . 'order_state os
        ');
    }

    /**
     * Get matching order state IDs from groups
     */
    public function getMatchingOrderStateIds($groups, $idLang, $idShop)
    {
        $allMatchingIds = [];

        foreach ($groups as $group) {
            $includeMethod = $group['include']['method'] ?? 'all';
            $includeValues = $group['include']['values'] ?? [];

            $includedIds = $this->getOrderStateIdsByMethod($includeMethod, $includeValues, $idLang, $idShop);

            $excludedIds = [];
            $excludes = $group['excludes'] ?? [];
            if (!empty($group['exclude']['method'])) {
                $excludes = [$group['exclude']];
            }

            foreach ($excludes as $exclude) {
                if (empty($exclude['method'])) {
                    continue;
                }
                $excludeIds = $this->getOrderStateIdsByMethod($exclude['method'], $exclude['values'] ?? [], $idLang, $idShop);
                $excludedIds = array_merge($excludedIds, $excludeIds);
            }

            $groupResult = array_diff($includedIds, $excludedIds);
            $allMatchingIds = array_merge($allMatchingIds, $groupResult);
        }

        return array_unique($allMatchingIds);
    }

    /**
     * Get order state IDs by selection method
     */
    public function getOrderStateIdsByMethod($method, $values, $idLang, $idShop)
    {
        switch ($method) {
            case 'all':
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT os.id_order_state
                    FROM ' . _DB_PREFIX_ . 'order_state os
                ');
                return array_column($results ?: [], 'id_order_state');

            case 'specific':
            case 'by_order_state':
                return array_map('intval', $values);

            case 'by_name_pattern':
                if (empty($values)) {
                    return [];
                }
                $patterns = is_array($values) ? $values : [$values];
                $conditions = [];
                foreach ($patterns as $pattern) {
                    $safe = pSQL($this->extractPatternString($pattern));
                    $conditions[] = "osl.name LIKE '%" . $safe . "%'";
                }
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT os.id_order_state
                    FROM ' . _DB_PREFIX_ . 'order_state os
                    INNER JOIN ' . _DB_PREFIX_ . 'order_state_lang osl ON os.id_order_state = osl.id_order_state AND osl.id_lang = ' . (int) $idLang . '
                    WHERE (' . implode(' OR ', $conditions) . ')
                ');
                return array_column($results ?: [], 'id_order_state');

            case 'by_send_email':
                $sendEmail = ($values === true || $values === 'true' || $values === '1' || $values === 1);
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT os.id_order_state
                    FROM ' . _DB_PREFIX_ . 'order_state os
                    WHERE os.send_email = ' . ($sendEmail ? '1' : '0') . '
                ');
                return array_column($results ?: [], 'id_order_state');

            case 'by_is_delivery':
                $isDelivery = ($values === true || $values === 'true' || $values === '1' || $values === 1);
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT os.id_order_state
                    FROM ' . _DB_PREFIX_ . 'order_state os
                    WHERE os.delivery = ' . ($isDelivery ? '1' : '0') . '
                ');
                return array_column($results ?: [], 'id_order_state');

            case 'by_is_invoice':
                $isInvoice = ($values === true || $values === 'true' || $values === '1' || $values === 1);
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT os.id_order_state
                    FROM ' . _DB_PREFIX_ . 'order_state os
                    WHERE os.invoice = ' . ($isInvoice ? '1' : '0') . '
                ');
                return array_column($results ?: [], 'id_order_state');

            case 'by_is_paid':
                $isPaid = ($values === true || $values === 'true' || $values === '1' || $values === 1);
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT os.id_order_state
                    FROM ' . _DB_PREFIX_ . 'order_state os
                    WHERE os.paid = ' . ($isPaid ? '1' : '0') . '
                ');
                return array_column($results ?: [], 'id_order_state');

            default:
                return [];
        }
    }

    // ========================================
    // Tax Methods
    // ========================================

    /**
     * Count all active taxes
     */
    public function countAllActiveTaxes($idLang, $idShop)
    {
        return (int) Db::getInstance()->getValue('
            SELECT COUNT(DISTINCT t.id_tax)
            FROM ' . _DB_PREFIX_ . 'tax t
            WHERE t.active = 1
        ');
    }

    /**
     * Get matching tax IDs from groups
     */
    public function getMatchingTaxIds($groups, $idLang, $idShop)
    {
        $allMatchingIds = [];

        foreach ($groups as $group) {
            $includeMethod = $group['include']['method'] ?? 'all';
            $includeValues = $group['include']['values'] ?? [];

            $includedIds = $this->getTaxIdsByMethod($includeMethod, $includeValues, $idLang, $idShop);

            $excludedIds = [];
            $excludes = $group['excludes'] ?? [];
            if (!empty($group['exclude']['method'])) {
                $excludes = [$group['exclude']];
            }

            foreach ($excludes as $exclude) {
                if (empty($exclude['method'])) {
                    continue;
                }
                $excludeIds = $this->getTaxIdsByMethod($exclude['method'], $exclude['values'] ?? [], $idLang, $idShop);
                $excludedIds = array_merge($excludedIds, $excludeIds);
            }

            $groupResult = array_diff($includedIds, $excludedIds);
            $allMatchingIds = array_merge($allMatchingIds, $groupResult);
        }

        return array_unique($allMatchingIds);
    }

    /**
     * Get tax IDs by selection method
     */
    public function getTaxIdsByMethod($method, $values, $idLang, $idShop)
    {
        switch ($method) {
            case 'all':
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT t.id_tax
                    FROM ' . _DB_PREFIX_ . 'tax t
                    WHERE t.active = 1
                ');
                return array_column($results ?: [], 'id_tax');

            case 'specific':
            case 'by_tax':
                return array_map('intval', $values);

            case 'by_name_pattern':
                if (empty($values)) {
                    return [];
                }
                $patterns = is_array($values) ? $values : [$values];
                $conditions = [];
                foreach ($patterns as $pattern) {
                    $safe = pSQL($this->extractPatternString($pattern));
                    $conditions[] = "tl.name LIKE '%" . $safe . "%'";
                }
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT t.id_tax
                    FROM ' . _DB_PREFIX_ . 'tax t
                    INNER JOIN ' . _DB_PREFIX_ . 'tax_lang tl ON t.id_tax = tl.id_tax AND tl.id_lang = ' . (int) $idLang . '
                    WHERE t.active = 1
                    AND (' . implode(' OR ', $conditions) . ')
                ');
                return array_column($results ?: [], 'id_tax');

            case 'by_rate_range':
                if (empty($values)) {
                    return [];
                }
                $min = isset($values['min']) ? (float) $values['min'] : 0;
                $max = isset($values['max']) ? (float) $values['max'] : 100;
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT t.id_tax
                    FROM ' . _DB_PREFIX_ . 'tax t
                    WHERE t.active = 1
                    AND t.rate >= ' . $min . ' AND t.rate <= ' . $max . '
                ');
                return array_column($results ?: [], 'id_tax');

            case 'by_active_status':
                $isActive = ($values === 'active' || $values === true || $values === 'true' || $values === '1' || $values === 1);
                $results = Db::getInstance()->executeS('
                    SELECT DISTINCT t.id_tax
                    FROM ' . _DB_PREFIX_ . 'tax t
                    WHERE t.active = ' . ($isActive ? '1' : '0') . '
                ');
                return array_column($results ?: [], 'id_tax');

            default:
                return [];
        }
    }
}
