<?php
/**
 * Product Condition Resolver
 *
 * Handles product ID resolution for all selection methods.
 * Extracted from EntitySelector trait.
 *
 * Contains 44+ methods for product selection:
 * - getIdsByMethod() - Router method
 * - getAllActiveProductIds()
 * - getProductIdsByCategories()
 * - getProductIdsByManufacturers()
 * - getProductIdsBySuppliers()
 * - getProductIdsByTags()
 * - getProductIdsByAttributes()
 * - getProductIdsByFeatures()
 * - getProductIdsByCombinationAttributes()
 * - getProductIdsByCarriers()
 * - getProductIdsByCondition()
 * - getProductIdsByVisibility()
 * - getProductIdsByActiveStatus()
 * - getProductIdsByStockStatus()
 * - getProductIdsByOnSale()
 * - getProductIdsByHasSpecificPrice()
 * - getProductIdsByIsVirtual()
 * - getProductIdsByIsPack()
 * - getProductIdsByHasCombinations()
 * - getProductIdsByAvailableForOrder()
 * - getProductIdsByOnlineOnly()
 * - getProductIdsByHasRelated()
 * - getProductIdsByHasCustomization()
 * - getProductIdsByHasAttachments()
 * - getProductIdsByHasAdditionalShipping()
 * - getProductIdsByNamePattern()
 * - getProductIdsByReferencePattern()
 * - getProductIdsByDescriptionPattern()
 * - getProductIdsByLongDescriptionPattern()
 * - getProductIdsByEan13Pattern()
 * - getProductIdsByUpcPattern()
 * - getProductIdsByIsbnPattern()
 * - getProductIdsByMpnPattern()
 * - getProductIdsByMetaTitlePattern()
 * - getProductIdsByMetaDescriptionPattern()
 * - getProductIdsByIdRange()
 * - getProductIdsByPriceRange()
 * - getProductIdsByWeightRange()
 * - getProductIdsByQuantityRange()
 * - getProductIdsByPositionRange()
 * - getProductIdsByDateAdded()
 * - getProductIdsByDateUpdated()
 *
 * Plus:
 * - applyModifiers() - Apply limit/sort to product IDs
 *
 * @package MyPrestaRocks\EntitySelector
 */

namespace MyPrestaRocks\EntitySelector\EntitySelector;

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

use Db;
use Context;

class ProductConditionResolver
{
    /**
     * @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 products
     *
     * @return int Product count
     */
    public function countAllActive()
    {
        return (int) Db::getInstance()->getValue('
            SELECT COUNT(DISTINCT p.id_product)
            FROM ' . _DB_PREFIX_ . 'product p
            INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                ON ps.id_product = p.id_product AND ps.id_shop = ' . (int) $this->idShop . '
            WHERE ps.active = 1
        ');
    }

    /**
     * Get matching product IDs from condition groups
     *
     * @param string $blockType Block type ('products' or 'combinations')
     * @param array $groups Condition groups
     * @return array Product IDs
     */
    public function getMatchingIds($blockType, array $groups)
    {
        $allMatchingIds = [];

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

            $includedIds = $this->getIdsByMethod($includeMethod, $includeValues);

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

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

            $groupResult = array_diff($includedIds, $excludedIds);

            // Apply group modifiers if present
            if (!empty($group['modifiers'])) {
                $groupResult = $this->applyModifiers($groupResult, $group['modifiers']);
            }

            $allMatchingIds = array_merge($allMatchingIds, $groupResult);
        }

        return array_unique($allMatchingIds);
    }

    /**
     * Get matching product IDs from condition groups
     * Alias for getMatchingIds() for backwards compatibility
     *
     * @param array $groups Condition groups
     * @param int $idLang Language ID (not used, kept for signature compatibility)
     * @param int $idShop Shop ID (not used, kept for signature compatibility)
     * @return array Product IDs
     */
    public function getMatchingProductIds(array $groups, $idLang = null, $idShop = null)
    {
        return $this->getMatchingIds('products', $groups);
    }

    /**
     * Route to appropriate method based on selection method
     *
     * @param string $method Selection method key
     * @param mixed $values Method values
     * @return array Product IDs
     */
    public function getIdsByMethod($method, $values)
    {
        switch ($method) {
            case 'all':
                return $this->getAllActiveProductIds();

            case 'specific':
                if (!is_array($values)) {
                    return [];
                }
                // Support prefixed string IDs (c:32, p:17) — return as-is
                $hasPrefixed = false;
                foreach ($values as $v) {
                    if (is_string($v) && (strpos($v, 'c:') === 0 || strpos($v, 'p:') === 0)) {
                        $hasPrefixed = true;
                        break;
                    }
                }
                return $hasPrefixed ? array_map('strval', $values) : array_map('intval', $values);

            case 'by_category':
                return $this->getProductIdsByCategories($values);

            case 'by_manufacturer':
                return $this->getProductIdsByManufacturers($values);

            case 'by_supplier':
                return $this->getProductIdsBySuppliers($values);

            case 'by_tag':
                return $this->getProductIdsByTags($values);

            case 'by_attribute':
                return $this->getProductIdsByAttributes($values);

            case 'by_feature':
                return $this->getProductIdsByFeatures($values);

            case 'by_combination':
                return $this->getProductIdsByCombinationAttributes($values);

            case 'by_carrier':
                return $this->getProductIdsByCarriers($values);

            case 'by_condition':
                return $this->getProductIdsByCondition($values);

            case 'by_visibility':
                return $this->getProductIdsByVisibility($values);

            case 'by_active_status':
                return $this->getProductIdsByActiveStatus($values);

            case 'by_stock_status':
                return $this->getProductIdsByStockStatus($values);

            case 'by_on_sale':
                return $this->getProductIdsByOnSale($values);

            case 'by_has_specific_price':
                return $this->getProductIdsByHasSpecificPrice($values);

            case 'by_is_virtual':
                return $this->getProductIdsByIsVirtual($values);

            case 'by_is_pack':
                return $this->getProductIdsByIsPack($values);

            case 'by_has_combinations':
                return $this->getProductIdsByHasCombinations($values);

            case 'by_available_for_order':
                return $this->getProductIdsByAvailableForOrder($values);

            case 'by_online_only':
                return $this->getProductIdsByOnlineOnly($values);

            case 'by_has_related':
                return $this->getProductIdsByHasRelated($values);

            case 'by_has_customization':
                return $this->getProductIdsByHasCustomization($values);

            case 'by_has_attachments':
                return $this->getProductIdsByHasAttachments($values);

            case 'by_has_additional_shipping':
                return $this->getProductIdsByHasAdditionalShipping($values);

            case 'by_out_of_stock_behavior':
                return $this->getProductIdsByOutOfStockBehavior($values);

            case 'by_delivery_time':
                return $this->getProductIdsByDeliveryTime($values);

            case 'by_carrier_restriction':
                return $this->getProductIdsByCarrierRestriction($values);

            case 'by_name_pattern':
                return $this->getProductIdsByNamePattern($values);

            case 'by_reference_pattern':
                return $this->getProductIdsByReferencePattern($values);

            case 'by_description_pattern':
                return $this->getProductIdsByDescriptionPattern($values);

            case 'by_long_description_pattern':
                return $this->getProductIdsByLongDescriptionPattern($values);

            case 'by_ean13_pattern':
                return $this->getProductIdsByEan13Pattern($values);

            case 'by_upc_pattern':
                return $this->getProductIdsByUpcPattern($values);

            case 'by_isbn_pattern':
                return $this->getProductIdsByIsbnPattern($values);

            case 'by_mpn_pattern':
                return $this->getProductIdsByMpnPattern($values);

            case 'by_meta_title_pattern':
                return $this->getProductIdsByMetaTitlePattern($values);

            case 'by_meta_description_pattern':
                return $this->getProductIdsByMetaDescriptionPattern($values);

            case 'by_id_range':
                return $this->getProductIdsByIdRange($values);

            case 'by_price_range':
                return $this->getProductIdsByPriceRange($values);

            case 'by_weight_range':
                return $this->getProductIdsByWeightRange($values);

            case 'by_quantity_range':
                return $this->getProductIdsByQuantityRange($values);

            case 'by_position_range':
                return $this->getProductIdsByPositionRange($values);

            case 'by_date_added':
                return $this->getProductIdsByDateAdded($values);

            case 'by_date_updated':
                return $this->getProductIdsByDateUpdated($values);

            default:
                return [];
        }
    }

    /**
     * Apply modifiers (limit, sort) to product IDs
     *
     * @param array $productIds Product IDs
     * @param array $modifiers Modifier settings
     * @return array Modified product IDs
     */
    public function applyModifiers(array $productIds, array $modifiers)
    {
        if (empty($productIds)) {
            return [];
        }

        $sortBy = $modifiers['sort_by'] ?? '';
        $sortDir = strtoupper($modifiers['sort_dir'] ?? 'ASC') === 'DESC' ? 'DESC' : 'ASC';
        $limit = isset($modifiers['limit']) && $modifiers['limit'] > 0 ? (int) $modifiers['limit'] : 0;

        // If no sorting needed and limit applies, just slice
        if (empty($sortBy) && $limit > 0) {
            return array_slice($productIds, 0, $limit);
        }

        // If no modifications needed, return as-is
        if (empty($sortBy) && $limit === 0) {
            return $productIds;
        }

        // Apply sorting via SQL
        $orderBy = '';
        switch ($sortBy) {
            case 'name':
                $orderBy = 'pl.name ' . $sortDir;
                break;
            case 'price':
                $orderBy = 'ps.price ' . $sortDir;
                break;
            case 'date_add':
                $orderBy = 'p.date_add ' . $sortDir;
                break;
            case 'date_upd':
                $orderBy = 'p.date_upd ' . $sortDir;
                break;
            case 'position':
                $orderBy = 'cp.position ' . $sortDir;
                break;
            case 'quantity':
                $orderBy = 'COALESCE(sa.quantity, 0) ' . $sortDir;
                break;
            case 'reference':
                $orderBy = 'p.reference ' . $sortDir;
                break;
            case 'id':
            default:
                $orderBy = 'p.id_product ' . $sortDir;
                break;
        }

        $sql = 'SELECT DISTINCT p.id_product
            FROM ' . _DB_PREFIX_ . 'product p
            INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps ON ps.id_product = p.id_product AND ps.id_shop = ' . (int) $this->idShop . '
            INNER JOIN ' . _DB_PREFIX_ . 'product_lang pl ON pl.id_product = p.id_product AND pl.id_lang = ' . (int) $this->idLang . ' AND pl.id_shop = ' . (int) $this->idShop . '
            LEFT JOIN ' . _DB_PREFIX_ . 'category_product cp ON cp.id_product = p.id_product AND cp.id_category = p.id_category_default
            LEFT JOIN ' . _DB_PREFIX_ . 'stock_available sa ON sa.id_product = p.id_product AND sa.id_product_attribute = 0 AND sa.id_shop = ' . (int) $this->idShop . '
            WHERE p.id_product IN (' . implode(',', array_map('intval', $productIds)) . ')
            ORDER BY ' . $orderBy;

        if ($limit > 0) {
            $sql .= ' LIMIT ' . $limit;
        }

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

        return array_column($results ?: [], 'id_product');
    }

    // ========================================
    // Core Selection Methods
    // ========================================

    /**
     * Get all active product IDs
     *
     * @return array Product IDs
     */
    public function getAllActiveProductIds()
    {
        $results = Db::getInstance()->executeS('
            SELECT DISTINCT p.id_product
            FROM ' . _DB_PREFIX_ . 'product p
            INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                ON ps.id_product = p.id_product AND ps.id_shop = ' . (int) $this->idShop . '
            WHERE ps.active = 1
        ');

        return array_column($results ?: [], 'id_product');
    }

    /**
     * Get product IDs by categories
     *
     * @param array $categoryIds Category IDs
     * @return array Product IDs
     */
    public function getProductIdsByCategories($categoryIds)
    {
        if (empty($categoryIds)) {
            return [];
        }

        $categoryIds = array_map('intval', $categoryIds);

        $results = Db::getInstance()->executeS('
            SELECT DISTINCT cp.id_product
            FROM ' . _DB_PREFIX_ . 'category_product cp
            INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                ON ps.id_product = cp.id_product AND ps.id_shop = ' . (int) $this->idShop . '
            WHERE cp.id_category IN (' . implode(',', $categoryIds) . ')
            AND ps.active = 1
        ');

        return array_column($results ?: [], 'id_product');
    }

    /**
     * Get product IDs by manufacturers
     *
     * @param array $manufacturerIds Manufacturer IDs
     * @return array Product IDs
     */
    public function getProductIdsByManufacturers($manufacturerIds)
    {
        if (empty($manufacturerIds)) {
            return [];
        }

        $manufacturerIds = array_map('intval', $manufacturerIds);

        $results = Db::getInstance()->executeS('
            SELECT DISTINCT p.id_product
            FROM ' . _DB_PREFIX_ . 'product p
            INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                ON ps.id_product = p.id_product AND ps.id_shop = ' . (int) $this->idShop . '
            WHERE p.id_manufacturer IN (' . implode(',', $manufacturerIds) . ')
            AND ps.active = 1
        ');

        return array_column($results ?: [], 'id_product');
    }

    /**
     * Get product IDs by suppliers
     *
     * @param array $supplierIds Supplier IDs
     * @return array Product IDs
     */
    public function getProductIdsBySuppliers($supplierIds)
    {
        if (empty($supplierIds)) {
            return [];
        }

        $supplierIds = array_map('intval', $supplierIds);

        $results = Db::getInstance()->executeS('
            SELECT DISTINCT ps2.id_product
            FROM ' . _DB_PREFIX_ . 'product_supplier ps2
            INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                ON ps.id_product = ps2.id_product AND ps.id_shop = ' . (int) $this->idShop . '
            WHERE ps2.id_supplier IN (' . implode(',', $supplierIds) . ')
            AND ps.active = 1
        ');

        return array_column($results ?: [], 'id_product');
    }

    // ========================================
    // Entity-Based Selection Methods
    // ========================================

    /**
     * Get product IDs by tags
     *
     * @param array $tagIds Tag IDs
     * @return array Product IDs
     */
    public function getProductIdsByTags($tagIds)
    {
        if (empty($tagIds)) {
            return [];
        }

        $tagIds = array_map('intval', $tagIds);

        $results = Db::getInstance()->executeS('
            SELECT DISTINCT pt.id_product
            FROM ' . _DB_PREFIX_ . 'product_tag pt
            INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                ON ps.id_product = pt.id_product AND ps.id_shop = ' . (int) $this->idShop . '
            WHERE pt.id_tag IN (' . implode(',', $tagIds) . ')
            AND ps.active = 1
        ');

        return array_column($results ?: [], 'id_product');
    }

    /**
     * Get product IDs by attributes
     *
     * @param array $attributeIds Attribute IDs
     * @return array Product IDs
     */
    public function getProductIdsByAttributes($attributeIds)
    {
        if (empty($attributeIds)) {
            return [];
        }

        $attributeIds = array_map('intval', $attributeIds);

        $results = Db::getInstance()->executeS('
            SELECT DISTINCT pa.id_product
            FROM ' . _DB_PREFIX_ . 'product_attribute pa
            INNER JOIN ' . _DB_PREFIX_ . 'product_attribute_combination pac
                ON pac.id_product_attribute = pa.id_product_attribute
            INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                ON ps.id_product = pa.id_product AND ps.id_shop = ' . (int) $this->idShop . '
            WHERE pac.id_attribute IN (' . implode(',', $attributeIds) . ')
            AND ps.active = 1
        ');

        return array_column($results ?: [], 'id_product');
    }

    /**
     * Get product IDs by features
     *
     * @param array $featureValueIds Feature value IDs
     * @return array Product IDs
     */
    public function getProductIdsByFeatures($featureValueIds)
    {
        if (empty($featureValueIds)) {
            return [];
        }

        $featureValueIds = array_map('intval', $featureValueIds);

        $results = Db::getInstance()->executeS('
            SELECT DISTINCT fp.id_product
            FROM ' . _DB_PREFIX_ . 'feature_product fp
            INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                ON ps.id_product = fp.id_product AND ps.id_shop = ' . (int) $this->idShop . '
            WHERE fp.id_feature_value IN (' . implode(',', $featureValueIds) . ')
            AND ps.active = 1
        ');

        return array_column($results ?: [], 'id_product');
    }

    /**
     * Get product IDs by combination attributes
     *
     * Values format: { groupId: [valueId1, valueId2], ... }
     * Logic: Within group = OR (any of the values), Between groups = AND (all groups must match)
     *
     * @param array $values Attribute groups with selected values
     * @return array Product IDs
     */
    public function getProductIdsByCombinationAttributes($values)
    {
        if (empty($values) || !is_array($values)) {
            return [];
        }

        // Handle new format: { mode: 'products'|'combinations', attributes: { groupId: [valueIds] } }
        // Also support legacy format: { groupId: [valueIds] }
        $mode = 'products';
        $attributes = $values;

        if (isset($values['mode']) && isset($values['attributes'])) {
            $mode = $values['mode'];
            $attributes = $values['attributes'];
        }

        if (empty($attributes) || !is_array($attributes)) {
            return [];
        }

        // Build query to find product_attributes (combinations) that match ALL selected groups
        $groupConditions = [];

        foreach ($attributes as $groupId => $valueIds) {
            if (!is_array($valueIds) || empty($valueIds)) {
                continue;
            }

            $groupId = (int) $groupId;
            $valueIds = array_map('intval', $valueIds);

            $groupConditions[] = '(
                SELECT COUNT(*) FROM ' . _DB_PREFIX_ . 'product_attribute_combination pac_g' . $groupId . '
                INNER JOIN ' . _DB_PREFIX_ . 'attribute a_g' . $groupId . '
                    ON a_g' . $groupId . '.id_attribute = pac_g' . $groupId . '.id_attribute
                    AND a_g' . $groupId . '.id_attribute_group = ' . $groupId . '
                WHERE pac_g' . $groupId . '.id_product_attribute = pa.id_product_attribute
                AND pac_g' . $groupId . '.id_attribute IN (' . implode(',', $valueIds) . ')
            ) > 0';
        }

        if (empty($groupConditions)) {
            return [];
        }

        if ($mode === 'combinations') {
            $results = Db::getInstance()->executeS('
                SELECT DISTINCT pa.id_product_attribute
                FROM ' . _DB_PREFIX_ . 'product_attribute pa
                INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                    ON ps.id_product = pa.id_product AND ps.id_shop = ' . (int) $this->idShop . '
                WHERE ps.active = 1
                AND ' . implode(' AND ', $groupConditions) . '
            ');

            return array_column($results ?: [], 'id_product_attribute');
        }

        // Default: return product IDs
        $results = Db::getInstance()->executeS('
            SELECT DISTINCT pa.id_product
            FROM ' . _DB_PREFIX_ . 'product_attribute pa
            INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                ON ps.id_product = pa.id_product AND ps.id_shop = ' . (int) $this->idShop . '
            WHERE ps.active = 1
            AND ' . implode(' AND ', $groupConditions) . '
        ');

        return array_column($results ?: [], 'id_product');
    }

    /**
     * Get product IDs by carriers
     *
     * @param array $carrierIds Carrier IDs
     * @return array Product IDs
     */
    public function getProductIdsByCarriers($carrierIds)
    {
        if (empty($carrierIds)) {
            return [];
        }

        $carrierIds = array_map('intval', $carrierIds);

        $results = Db::getInstance()->executeS('
            SELECT DISTINCT pc.id_product
            FROM ' . _DB_PREFIX_ . 'product_carrier pc
            INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                ON ps.id_product = pc.id_product AND ps.id_shop = ' . (int) $this->idShop . '
            WHERE pc.id_carrier_reference IN (
                SELECT c.id_reference FROM ' . _DB_PREFIX_ . 'carrier c
                WHERE c.id_carrier IN (' . implode(',', $carrierIds) . ')
            )
            AND ps.active = 1
        ');

        return array_column($results ?: [], 'id_product');
    }

    // ========================================
    // Property-Based Selection Methods
    // ========================================

    /**
     * Get product IDs by condition (new, used, refurbished)
     *
     * @param array $conditions Condition values
     * @return array Product IDs
     */
    public function getProductIdsByCondition($conditions)
    {
        if (empty($conditions)) {
            return [];
        }

        if (!is_array($conditions)) {
            $conditions = [$conditions];
        }

        $validConditions = array_intersect($conditions, ['new', 'used', 'refurbished']);
        if (empty($validConditions)) {
            return [];
        }

        $conditionsSql = "'" . implode("','", array_map('pSQL', $validConditions)) . "'";

        $results = Db::getInstance()->executeS('
            SELECT DISTINCT p.id_product
            FROM ' . _DB_PREFIX_ . 'product p
            INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                ON ps.id_product = p.id_product AND ps.id_shop = ' . (int) $this->idShop . '
            WHERE p.condition IN (' . $conditionsSql . ')
            AND ps.active = 1
        ');

        return array_column($results ?: [], 'id_product');
    }

    /**
     * Get product IDs by visibility
     *
     * @param array $visibilities Visibility values
     * @return array Product IDs
     */
    public function getProductIdsByVisibility($visibilities)
    {
        if (empty($visibilities)) {
            return [];
        }

        if (!is_array($visibilities)) {
            $visibilities = [$visibilities];
        }

        $validVisibilities = array_intersect($visibilities, ['both', 'catalog', 'search', 'none']);
        if (empty($validVisibilities)) {
            return [];
        }

        $visibilitiesSql = "'" . implode("','", array_map('pSQL', $validVisibilities)) . "'";

        $results = Db::getInstance()->executeS('
            SELECT DISTINCT p.id_product
            FROM ' . _DB_PREFIX_ . 'product p
            INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                ON ps.id_product = p.id_product AND ps.id_shop = ' . (int) $this->idShop . '
            WHERE ps.visibility IN (' . $visibilitiesSql . ')
            AND ps.active = 1
        ');

        return array_column($results ?: [], 'id_product');
    }

    /**
     * Get product IDs by active status
     *
     * @param array $statuses Status values ('active', 'inactive')
     * @return array Product IDs
     */
    public function getProductIdsByActiveStatus($statuses)
    {
        if (empty($statuses)) {
            return [];
        }

        if (!is_array($statuses)) {
            $statuses = [$statuses];
        }

        $conditions = [];
        if (in_array('active', $statuses)) {
            $conditions[] = 'ps.active = 1';
        }
        if (in_array('inactive', $statuses)) {
            $conditions[] = 'ps.active = 0';
        }

        if (empty($conditions)) {
            return [];
        }

        $results = Db::getInstance()->executeS('
            SELECT DISTINCT p.id_product
            FROM ' . _DB_PREFIX_ . 'product p
            INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                ON ps.id_product = p.id_product AND ps.id_shop = ' . (int) $this->idShop . '
            WHERE (' . implode(' OR ', $conditions) . ')
        ');

        return array_column($results ?: [], 'id_product');
    }

    /**
     * Get product IDs by stock status
     *
     * @param array $statuses Stock status values
     * @return array Product IDs
     */
    public function getProductIdsByStockStatus($statuses)
    {
        if (empty($statuses)) {
            return [];
        }

        if (!is_array($statuses)) {
            $statuses = [$statuses];
        }

        $statusConditions = [];
        foreach ($statuses as $status) {
            switch ($status) {
                case 'in_stock':
                    $statusConditions[] = 'COALESCE(sa.quantity, 0) > 0';
                    break;
                case 'out_of_stock':
                    $statusConditions[] = 'COALESCE(sa.quantity, 0) <= 0';
                    break;
                case 'low_stock':
                    $statusConditions[] = '(COALESCE(sa.quantity, 0) > 0 AND COALESCE(sa.quantity, 0) < 5)';
                    break;
            }
        }

        if (empty($statusConditions)) {
            return [];
        }

        $results = Db::getInstance()->executeS('
            SELECT DISTINCT p.id_product
            FROM ' . _DB_PREFIX_ . 'product p
            INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                ON ps.id_product = p.id_product AND ps.id_shop = ' . (int) $this->idShop . '
            LEFT JOIN ' . _DB_PREFIX_ . 'stock_available sa
                ON sa.id_product = p.id_product AND sa.id_product_attribute = 0 AND sa.id_shop = ' . (int) $this->idShop . '
            WHERE ps.active = 1
            AND (' . implode(' OR ', $statusConditions) . ')
        ');

        return array_column($results ?: [], 'id_product');
    }

    // ========================================
    // Boolean Flag Methods
    // ========================================

    /**
     * Get product IDs by on_sale flag
     *
     * @param mixed $values 'yes' or 'no' or array
     * @return array Product IDs
     */
    public function getProductIdsByOnSale($values = null)
    {
        $onSale = $this->parseBooleanValue($values, true);

        $results = Db::getInstance()->executeS('
            SELECT DISTINCT p.id_product
            FROM ' . _DB_PREFIX_ . 'product p
            INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                ON ps.id_product = p.id_product AND ps.id_shop = ' . (int) $this->idShop . '
            WHERE ps.active = 1 AND ps.on_sale = ' . ($onSale ? '1' : '0') . '
        ');

        return array_column($results ?: [], 'id_product');
    }

    /**
     * Get product IDs by has_specific_price
     *
     * @param mixed $values 'yes' or 'no' or array
     * @return array Product IDs
     */
    public function getProductIdsByHasSpecificPrice($values = null)
    {
        $hasSpecificPrice = $this->parseBooleanValue($values, true);

        if ($hasSpecificPrice) {
            $results = Db::getInstance()->executeS('
                SELECT DISTINCT sp.id_product
                FROM ' . _DB_PREFIX_ . 'specific_price sp
                INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                    ON ps.id_product = sp.id_product AND ps.id_shop = ' . (int) $this->idShop . '
                WHERE ps.active = 1
                AND (sp.id_shop = 0 OR sp.id_shop = ' . (int) $this->idShop . ')
                AND (sp.from = "0000-00-00 00:00:00" OR sp.from <= NOW())
                AND (sp.to = "0000-00-00 00:00:00" OR sp.to >= NOW())
            ');
        } else {
            $results = Db::getInstance()->executeS('
                SELECT DISTINCT p.id_product
                FROM ' . _DB_PREFIX_ . 'product p
                INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                    ON ps.id_product = p.id_product AND ps.id_shop = ' . (int) $this->idShop . '
                WHERE ps.active = 1
                AND p.id_product NOT IN (
                    SELECT sp.id_product FROM ' . _DB_PREFIX_ . 'specific_price sp
                    WHERE (sp.id_shop = 0 OR sp.id_shop = ' . (int) $this->idShop . ')
                    AND (sp.from = "0000-00-00 00:00:00" OR sp.from <= NOW())
                    AND (sp.to = "0000-00-00 00:00:00" OR sp.to >= NOW())
                )
            ');
        }

        return array_column($results ?: [], 'id_product');
    }

    /**
     * Get product IDs by is_virtual flag
     *
     * @param mixed $values 'yes' or 'no' or array
     * @return array Product IDs
     */
    public function getProductIdsByIsVirtual($values = null)
    {
        $isVirtual = $this->parseBooleanValue($values, true);

        $results = Db::getInstance()->executeS('
            SELECT DISTINCT p.id_product
            FROM ' . _DB_PREFIX_ . 'product p
            INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                ON ps.id_product = p.id_product AND ps.id_shop = ' . (int) $this->idShop . '
            WHERE ps.active = 1 AND p.is_virtual = ' . ($isVirtual ? '1' : '0') . '
        ');

        return array_column($results ?: [], 'id_product');
    }

    /**
     * Get product IDs by is_pack flag
     *
     * @param mixed $values 'yes' or 'no' or array
     * @return array Product IDs
     */
    public function getProductIdsByIsPack($values = null)
    {
        $isPack = $this->parseBooleanValue($values, true);

        if ($isPack) {
            $results = Db::getInstance()->executeS('
                SELECT DISTINCT p.id_product
                FROM ' . _DB_PREFIX_ . 'product p
                INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                    ON ps.id_product = p.id_product AND ps.id_shop = ' . (int) $this->idShop . '
                INNER JOIN ' . _DB_PREFIX_ . 'pack pk ON pk.id_product_pack = p.id_product
                WHERE ps.active = 1
            ');
        } else {
            $results = Db::getInstance()->executeS('
                SELECT DISTINCT p.id_product
                FROM ' . _DB_PREFIX_ . 'product p
                INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                    ON ps.id_product = p.id_product AND ps.id_shop = ' . (int) $this->idShop . '
                WHERE ps.active = 1
                AND p.id_product NOT IN (SELECT pk.id_product_pack FROM ' . _DB_PREFIX_ . 'pack pk)
            ');
        }

        return array_column($results ?: [], 'id_product');
    }

    /**
     * Get product IDs by has_combinations flag
     *
     * @param mixed $values 'yes' or 'no' or array
     * @return array Product IDs
     */
    public function getProductIdsByHasCombinations($values = null)
    {
        $hasCombinations = $this->parseBooleanValue($values, true);

        if ($hasCombinations) {
            $results = Db::getInstance()->executeS('
                SELECT DISTINCT pa.id_product
                FROM ' . _DB_PREFIX_ . 'product_attribute pa
                INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                    ON ps.id_product = pa.id_product AND ps.id_shop = ' . (int) $this->idShop . '
                WHERE ps.active = 1
            ');
        } else {
            $results = Db::getInstance()->executeS('
                SELECT DISTINCT p.id_product
                FROM ' . _DB_PREFIX_ . 'product p
                INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                    ON ps.id_product = p.id_product AND ps.id_shop = ' . (int) $this->idShop . '
                WHERE ps.active = 1
                AND p.id_product NOT IN (SELECT pa.id_product FROM ' . _DB_PREFIX_ . 'product_attribute pa)
            ');
        }

        return array_column($results ?: [], 'id_product');
    }

    /**
     * Get product IDs by available_for_order flag
     *
     * @param mixed $values 'yes' or 'no' or array
     * @return array Product IDs
     */
    public function getProductIdsByAvailableForOrder($values = null)
    {
        $availableForOrder = $this->parseBooleanValue($values, true);

        $results = Db::getInstance()->executeS('
            SELECT DISTINCT p.id_product
            FROM ' . _DB_PREFIX_ . 'product p
            INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                ON ps.id_product = p.id_product AND ps.id_shop = ' . (int) $this->idShop . '
            WHERE ps.active = 1 AND ps.available_for_order = ' . ($availableForOrder ? '1' : '0') . '
        ');

        return array_column($results ?: [], 'id_product');
    }

    /**
     * Get product IDs by online_only flag
     *
     * @param mixed $values 'yes' or 'no' or array
     * @return array Product IDs
     */
    public function getProductIdsByOnlineOnly($values = null)
    {
        $onlineOnly = $this->parseBooleanValue($values, true);

        $results = Db::getInstance()->executeS('
            SELECT DISTINCT p.id_product
            FROM ' . _DB_PREFIX_ . 'product p
            INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                ON ps.id_product = p.id_product AND ps.id_shop = ' . (int) $this->idShop . '
            WHERE ps.active = 1 AND ps.online_only = ' . ($onlineOnly ? '1' : '0') . '
        ');

        return array_column($results ?: [], 'id_product');
    }

    /**
     * Get product IDs by has_related products flag
     *
     * @param mixed $values 'yes' or 'no' or array
     * @return array Product IDs
     */
    public function getProductIdsByHasRelated($values = null)
    {
        $hasRelated = $this->parseBooleanValue($values, true);

        if ($hasRelated) {
            $results = Db::getInstance()->executeS('
                SELECT DISTINCT a.id_product_1 as id_product
                FROM ' . _DB_PREFIX_ . 'accessory a
                INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                    ON ps.id_product = a.id_product_1 AND ps.id_shop = ' . (int) $this->idShop . '
                WHERE ps.active = 1
            ');
        } else {
            $results = Db::getInstance()->executeS('
                SELECT DISTINCT p.id_product
                FROM ' . _DB_PREFIX_ . 'product p
                INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                    ON ps.id_product = p.id_product AND ps.id_shop = ' . (int) $this->idShop . '
                WHERE ps.active = 1
                AND p.id_product NOT IN (SELECT a.id_product_1 FROM ' . _DB_PREFIX_ . 'accessory a)
            ');
        }

        return array_column($results ?: [], 'id_product');
    }

    /**
     * Get product IDs by has_customization flag
     *
     * @param mixed $values 'yes' or 'no' or array
     * @return array Product IDs
     */
    public function getProductIdsByHasCustomization($values = null)
    {
        $hasCustomization = $this->parseBooleanValue($values, true);

        if ($hasCustomization) {
            $results = Db::getInstance()->executeS('
                SELECT DISTINCT cf.id_product
                FROM ' . _DB_PREFIX_ . 'customization_field cf
                INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                    ON ps.id_product = cf.id_product AND ps.id_shop = ' . (int) $this->idShop . '
                WHERE ps.active = 1
            ');
        } else {
            $results = Db::getInstance()->executeS('
                SELECT DISTINCT p.id_product
                FROM ' . _DB_PREFIX_ . 'product p
                INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                    ON ps.id_product = p.id_product AND ps.id_shop = ' . (int) $this->idShop . '
                WHERE ps.active = 1
                AND p.id_product NOT IN (SELECT cf.id_product FROM ' . _DB_PREFIX_ . 'customization_field cf)
            ');
        }

        return array_column($results ?: [], 'id_product');
    }

    /**
     * Get product IDs by has_attachments flag
     *
     * @param mixed $values 'yes' or 'no' or array
     * @return array Product IDs
     */
    public function getProductIdsByHasAttachments($values = null)
    {
        $hasAttachments = $this->parseBooleanValue($values, true);

        if ($hasAttachments) {
            $results = Db::getInstance()->executeS('
                SELECT DISTINCT pa.id_product
                FROM ' . _DB_PREFIX_ . 'product_attachment pa
                INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                    ON ps.id_product = pa.id_product AND ps.id_shop = ' . (int) $this->idShop . '
                WHERE ps.active = 1
            ');
        } else {
            $results = Db::getInstance()->executeS('
                SELECT DISTINCT p.id_product
                FROM ' . _DB_PREFIX_ . 'product p
                INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                    ON ps.id_product = p.id_product AND ps.id_shop = ' . (int) $this->idShop . '
                WHERE ps.active = 1
                AND p.id_product NOT IN (SELECT pa.id_product FROM ' . _DB_PREFIX_ . 'product_attachment pa)
            ');
        }

        return array_column($results ?: [], 'id_product');
    }

    /**
     * Get product IDs by has_additional_shipping flag
     *
     * @param mixed $values 'yes' or 'no' or array
     * @return array Product IDs
     */
    public function getProductIdsByHasAdditionalShipping($values = null)
    {
        $hasAdditionalShipping = $this->parseBooleanValue($values, true);

        $results = Db::getInstance()->executeS('
            SELECT DISTINCT p.id_product
            FROM ' . _DB_PREFIX_ . 'product p
            INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                ON ps.id_product = p.id_product AND ps.id_shop = ' . (int) $this->idShop . '
            WHERE ps.active = 1
            AND p.additional_shipping_cost ' . ($hasAdditionalShipping ? '> 0' : '= 0') . '
        ');

        return array_column($results ?: [], 'id_product');
    }

    /**
     * Get product IDs by out of stock behavior
     *
     * @param array $behaviors Behavior values ('deny', 'allow', 'default')
     * @return array Product IDs
     */
    public function getProductIdsByOutOfStockBehavior($behaviors)
    {
        if (empty($behaviors)) {
            return [];
        }

        if (!is_array($behaviors)) {
            $behaviors = [$behaviors];
        }

        $conditions = [];
        foreach ($behaviors as $behavior) {
            switch ($behavior) {
                case 'deny':
                    $conditions[] = 'sa.out_of_stock = 0';
                    break;
                case 'allow':
                    $conditions[] = 'sa.out_of_stock = 1';
                    break;
                case 'default':
                    $conditions[] = 'sa.out_of_stock = 2';
                    break;
            }
        }

        if (empty($conditions)) {
            return [];
        }

        $results = Db::getInstance()->executeS('
            SELECT DISTINCT p.id_product
            FROM ' . _DB_PREFIX_ . 'product p
            INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                ON ps.id_product = p.id_product AND ps.id_shop = ' . (int) $this->idShop . '
            LEFT JOIN ' . _DB_PREFIX_ . 'stock_available sa
                ON sa.id_product = p.id_product AND sa.id_product_attribute = 0 AND sa.id_shop = ' . (int) $this->idShop . '
            WHERE ps.active = 1
            AND (' . implode(' OR ', $conditions) . ')
        ');

        return array_column($results ?: [], 'id_product');
    }

    /**
     * Get product IDs by delivery time setting
     *
     * @param array $settings Delivery time settings ('none', 'default', 'specific')
     * @return array Product IDs
     */
    public function getProductIdsByDeliveryTime($settings)
    {
        if (empty($settings)) {
            return [];
        }

        // Check if additional_delivery_times column exists (added in PS 1.7.7.0)
        static $columnExists = null;
        if ($columnExists === null) {
            $columns = Db::getInstance()->executeS('SHOW COLUMNS FROM ' . _DB_PREFIX_ . 'product_shop LIKE \'additional_delivery_times\'');
            $columnExists = !empty($columns);
        }

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

        if (!is_array($settings)) {
            $settings = [$settings];
        }

        $conditions = [];
        foreach ($settings as $setting) {
            switch ($setting) {
                case 'none':
                    $conditions[] = 'ps.additional_delivery_times = 0';
                    break;
                case 'default':
                    $conditions[] = 'ps.additional_delivery_times = 1';
                    break;
                case 'specific':
                    $conditions[] = 'ps.additional_delivery_times = 2';
                    break;
            }
        }

        if (empty($conditions)) {
            return [];
        }

        $results = Db::getInstance()->executeS('
            SELECT DISTINCT p.id_product
            FROM ' . _DB_PREFIX_ . 'product p
            INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                ON ps.id_product = p.id_product AND ps.id_shop = ' . (int) $this->idShop . '
            WHERE ps.active = 1
            AND (' . implode(' OR ', $conditions) . ')
        ');

        return array_column($results ?: [], 'id_product');
    }

    /**
     * Get product IDs by carrier restriction
     *
     * @param array $restrictions Restriction values ('restricted', 'all')
     * @return array Product IDs
     */
    public function getProductIdsByCarrierRestriction($restrictions)
    {
        if (empty($restrictions)) {
            return [];
        }

        if (!is_array($restrictions)) {
            $restrictions = [$restrictions];
        }

        $results = [];

        if (in_array('restricted', $restrictions)) {
            $restricted = Db::getInstance()->executeS('
                SELECT DISTINCT pc.id_product
                FROM ' . _DB_PREFIX_ . 'product_carrier pc
                INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                    ON ps.id_product = pc.id_product AND ps.id_shop = ' . (int) $this->idShop . '
                WHERE ps.active = 1
            ');
            $results = array_merge($results, array_column($restricted ?: [], 'id_product'));
        }

        if (in_array('all', $restrictions)) {
            $unrestricted = Db::getInstance()->executeS('
                SELECT DISTINCT p.id_product
                FROM ' . _DB_PREFIX_ . 'product p
                INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                    ON ps.id_product = p.id_product AND ps.id_shop = ' . (int) $this->idShop . '
                WHERE ps.active = 1
                AND p.id_product NOT IN (SELECT pc.id_product FROM ' . _DB_PREFIX_ . 'product_carrier pc)
            ');
            $results = array_merge($results, array_column($unrestricted ?: [], 'id_product'));
        }

        return array_unique($results);
    }

    // ========================================
    // Pattern Matching Methods
    // ========================================

    /**
     * Get product IDs by name pattern
     *
     * @param mixed $patterns Pattern(s) to match
     * @return array Product IDs
     */
    public function getProductIdsByNamePattern($patterns)
    {
        $conditions = $this->buildPatternConditions($patterns, 'pl.name');
        if (empty($conditions)) {
            return [];
        }

        $results = Db::getInstance()->executeS('
            SELECT DISTINCT p.id_product
            FROM ' . _DB_PREFIX_ . 'product p
            INNER JOIN ' . _DB_PREFIX_ . 'product_lang pl
                ON pl.id_product = p.id_product AND pl.id_lang = ' . (int) $this->idLang . ' AND pl.id_shop = ' . (int) $this->idShop . '
            INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                ON ps.id_product = p.id_product AND ps.id_shop = ' . (int) $this->idShop . '
            WHERE (' . implode(' OR ', $conditions) . ')
            AND ps.active = 1
        ');

        return array_column($results ?: [], 'id_product');
    }

    /**
     * Get product IDs by reference pattern
     *
     * @param mixed $patterns Pattern(s) to match
     * @return array Product IDs
     */
    public function getProductIdsByReferencePattern($patterns)
    {
        $conditions = $this->buildPatternConditions($patterns, 'p.reference');
        if (empty($conditions)) {
            return [];
        }

        $results = Db::getInstance()->executeS('
            SELECT DISTINCT p.id_product
            FROM ' . _DB_PREFIX_ . 'product p
            INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                ON ps.id_product = p.id_product AND ps.id_shop = ' . (int) $this->idShop . '
            WHERE (' . implode(' OR ', $conditions) . ')
            AND ps.active = 1
        ');

        return array_column($results ?: [], 'id_product');
    }

    /**
     * Get product IDs by description pattern
     *
     * @param mixed $patterns Pattern(s) to match
     * @return array Product IDs
     */
    public function getProductIdsByDescriptionPattern($patterns)
    {
        $conditions = $this->buildPatternConditions($patterns, 'pl.description_short');
        if (empty($conditions)) {
            return [];
        }

        $results = Db::getInstance()->executeS('
            SELECT DISTINCT p.id_product
            FROM ' . _DB_PREFIX_ . 'product p
            INNER JOIN ' . _DB_PREFIX_ . 'product_lang pl
                ON pl.id_product = p.id_product AND pl.id_lang = ' . (int) $this->idLang . ' AND pl.id_shop = ' . (int) $this->idShop . '
            INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                ON ps.id_product = p.id_product AND ps.id_shop = ' . (int) $this->idShop . '
            WHERE (' . implode(' OR ', $conditions) . ')
            AND ps.active = 1
        ');

        return array_column($results ?: [], 'id_product');
    }

    /**
     * Get product IDs by long description pattern
     *
     * @param mixed $patterns Pattern(s) to match
     * @return array Product IDs
     */
    public function getProductIdsByLongDescriptionPattern($patterns)
    {
        $conditions = $this->buildPatternConditions($patterns, 'pl.description');
        if (empty($conditions)) {
            return [];
        }

        $results = Db::getInstance()->executeS('
            SELECT DISTINCT p.id_product
            FROM ' . _DB_PREFIX_ . 'product p
            INNER JOIN ' . _DB_PREFIX_ . 'product_lang pl
                ON pl.id_product = p.id_product AND pl.id_lang = ' . (int) $this->idLang . ' AND pl.id_shop = ' . (int) $this->idShop . '
            INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                ON ps.id_product = p.id_product AND ps.id_shop = ' . (int) $this->idShop . '
            WHERE (' . implode(' OR ', $conditions) . ')
            AND ps.active = 1
        ');

        return array_column($results ?: [], 'id_product');
    }

    /**
     * Get product IDs by EAN13 pattern
     *
     * @param mixed $patterns Pattern(s) to match
     * @return array Product IDs
     */
    public function getProductIdsByEan13Pattern($patterns)
    {
        $conditions = $this->buildPatternConditions($patterns, 'p.ean13');
        if (empty($conditions)) {
            return [];
        }

        $results = Db::getInstance()->executeS('
            SELECT DISTINCT p.id_product
            FROM ' . _DB_PREFIX_ . 'product p
            INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                ON ps.id_product = p.id_product AND ps.id_shop = ' . (int) $this->idShop . '
            WHERE (' . implode(' OR ', $conditions) . ')
            AND ps.active = 1
        ');

        return array_column($results ?: [], 'id_product');
    }

    /**
     * Get product IDs by UPC pattern
     *
     * @param mixed $patterns Pattern(s) to match
     * @return array Product IDs
     */
    public function getProductIdsByUpcPattern($patterns)
    {
        $conditions = $this->buildPatternConditions($patterns, 'p.upc');
        if (empty($conditions)) {
            return [];
        }

        $results = Db::getInstance()->executeS('
            SELECT DISTINCT p.id_product
            FROM ' . _DB_PREFIX_ . 'product p
            INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                ON ps.id_product = p.id_product AND ps.id_shop = ' . (int) $this->idShop . '
            WHERE (' . implode(' OR ', $conditions) . ')
            AND ps.active = 1
        ');

        return array_column($results ?: [], 'id_product');
    }

    /**
     * Get product IDs by ISBN pattern
     *
     * @param mixed $patterns Pattern(s) to match
     * @return array Product IDs
     */
    public function getProductIdsByIsbnPattern($patterns)
    {
        $conditions = $this->buildPatternConditions($patterns, 'p.isbn');
        if (empty($conditions)) {
            return [];
        }

        $results = Db::getInstance()->executeS('
            SELECT DISTINCT p.id_product
            FROM ' . _DB_PREFIX_ . 'product p
            INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                ON ps.id_product = p.id_product AND ps.id_shop = ' . (int) $this->idShop . '
            WHERE (' . implode(' OR ', $conditions) . ')
            AND ps.active = 1
        ');

        return array_column($results ?: [], 'id_product');
    }

    /**
     * Get product IDs by MPN pattern
     *
     * @param mixed $patterns Pattern(s) to match
     * @return array Product IDs
     */
    public function getProductIdsByMpnPattern($patterns)
    {
        $conditions = $this->buildPatternConditions($patterns, 'p.mpn');
        if (empty($conditions)) {
            return [];
        }

        $results = Db::getInstance()->executeS('
            SELECT DISTINCT p.id_product
            FROM ' . _DB_PREFIX_ . 'product p
            INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                ON ps.id_product = p.id_product AND ps.id_shop = ' . (int) $this->idShop . '
            WHERE (' . implode(' OR ', $conditions) . ')
            AND ps.active = 1
        ');

        return array_column($results ?: [], 'id_product');
    }

    /**
     * Get product IDs by meta title pattern
     *
     * @param mixed $patterns Pattern(s) to match
     * @return array Product IDs
     */
    public function getProductIdsByMetaTitlePattern($patterns)
    {
        $conditions = $this->buildPatternConditions($patterns, 'pl.meta_title');
        if (empty($conditions)) {
            return [];
        }

        $results = Db::getInstance()->executeS('
            SELECT DISTINCT p.id_product
            FROM ' . _DB_PREFIX_ . 'product p
            INNER JOIN ' . _DB_PREFIX_ . 'product_lang pl
                ON pl.id_product = p.id_product AND pl.id_lang = ' . (int) $this->idLang . ' AND pl.id_shop = ' . (int) $this->idShop . '
            INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                ON ps.id_product = p.id_product AND ps.id_shop = ' . (int) $this->idShop . '
            WHERE (' . implode(' OR ', $conditions) . ')
            AND ps.active = 1
        ');

        return array_column($results ?: [], 'id_product');
    }

    /**
     * Get product IDs by meta description pattern
     *
     * @param mixed $patterns Pattern(s) to match
     * @return array Product IDs
     */
    public function getProductIdsByMetaDescriptionPattern($patterns)
    {
        $conditions = $this->buildPatternConditions($patterns, 'pl.meta_description');
        if (empty($conditions)) {
            return [];
        }

        $results = Db::getInstance()->executeS('
            SELECT DISTINCT p.id_product
            FROM ' . _DB_PREFIX_ . 'product p
            INNER JOIN ' . _DB_PREFIX_ . 'product_lang pl
                ON pl.id_product = p.id_product AND pl.id_lang = ' . (int) $this->idLang . ' AND pl.id_shop = ' . (int) $this->idShop . '
            INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                ON ps.id_product = p.id_product AND ps.id_shop = ' . (int) $this->idShop . '
            WHERE (' . implode(' OR ', $conditions) . ')
            AND ps.active = 1
        ');

        return array_column($results ?: [], 'id_product');
    }

    // ========================================
    // Range Methods
    // ========================================

    /**
     * Get product IDs by ID range
     *
     * @param array $ranges Range values
     * @return array Product IDs
     */
    public function getProductIdsByIdRange($ranges)
    {
        $rangeConditions = $this->buildRangeConditions($ranges, 'p.id_product', 1);
        if (empty($rangeConditions)) {
            return [];
        }

        $results = Db::getInstance()->executeS('
            SELECT DISTINCT p.id_product
            FROM ' . _DB_PREFIX_ . 'product p
            INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                ON ps.id_product = p.id_product AND ps.id_shop = ' . (int) $this->idShop . '
            WHERE ps.active = 1 AND (' . implode(' OR ', $rangeConditions) . ')
        ');

        return array_column($results ?: [], 'id_product');
    }

    /**
     * Get product IDs by price range
     *
     * @param array $ranges Range values
     * @return array Product IDs
     */
    public function getProductIdsByPriceRange($ranges)
    {
        $rangeConditions = $this->buildRangeConditions($ranges, 'ps.price', 0);
        if (empty($rangeConditions)) {
            return [];
        }

        $results = Db::getInstance()->executeS('
            SELECT DISTINCT p.id_product
            FROM ' . _DB_PREFIX_ . 'product p
            INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                ON ps.id_product = p.id_product AND ps.id_shop = ' . (int) $this->idShop . '
            WHERE ps.active = 1 AND (' . implode(' OR ', $rangeConditions) . ')
        ');

        return array_column($results ?: [], 'id_product');
    }

    /**
     * Get product IDs by weight range
     *
     * @param array $ranges Range values
     * @return array Product IDs
     */
    public function getProductIdsByWeightRange($ranges)
    {
        $rangeConditions = $this->buildRangeConditions($ranges, 'p.weight', 0);
        if (empty($rangeConditions)) {
            return [];
        }

        $results = Db::getInstance()->executeS('
            SELECT DISTINCT p.id_product
            FROM ' . _DB_PREFIX_ . 'product p
            INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                ON ps.id_product = p.id_product AND ps.id_shop = ' . (int) $this->idShop . '
            WHERE ps.active = 1 AND (' . implode(' OR ', $rangeConditions) . ')
        ');

        return array_column($results ?: [], 'id_product');
    }

    /**
     * Get product IDs by quantity range
     *
     * @param array $ranges Range values
     * @return array Product IDs
     */
    public function getProductIdsByQuantityRange($ranges)
    {
        $rangeConditions = $this->buildRangeConditions($ranges, 'COALESCE(sa.quantity, 0)', 0);
        if (empty($rangeConditions)) {
            return [];
        }

        $results = Db::getInstance()->executeS('
            SELECT DISTINCT p.id_product
            FROM ' . _DB_PREFIX_ . 'product p
            INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                ON ps.id_product = p.id_product AND ps.id_shop = ' . (int) $this->idShop . '
            LEFT JOIN ' . _DB_PREFIX_ . 'stock_available sa
                ON sa.id_product = p.id_product AND sa.id_product_attribute = 0 AND sa.id_shop = ' . (int) $this->idShop . '
            WHERE ps.active = 1 AND (' . implode(' OR ', $rangeConditions) . ')
        ');

        return array_column($results ?: [], 'id_product');
    }

    /**
     * Get product IDs by position range
     *
     * @param array $ranges Range values
     * @return array Product IDs
     */
    public function getProductIdsByPositionRange($ranges)
    {
        $rangeConditions = $this->buildRangeConditions($ranges, 'cp.position', 0);
        if (empty($rangeConditions)) {
            return [];
        }

        $results = Db::getInstance()->executeS('
            SELECT DISTINCT p.id_product
            FROM ' . _DB_PREFIX_ . 'product p
            INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                ON ps.id_product = p.id_product AND ps.id_shop = ' . (int) $this->idShop . '
            INNER JOIN ' . _DB_PREFIX_ . 'category_product cp
                ON cp.id_product = p.id_product AND cp.id_category = p.id_category_default
            WHERE ps.active = 1 AND (' . implode(' OR ', $rangeConditions) . ')
        ');

        return array_column($results ?: [], 'id_product');
    }

    // ========================================
    // Date Range Methods
    // ========================================

    /**
     * Get product IDs by date added
     *
     * @param array $dateRange Date range with 'from' and 'to' keys
     * @return array Product IDs
     */
    public function getProductIdsByDateAdded($dateRange)
    {
        $fromDate = isset($dateRange['from']) && $dateRange['from'] ? pSQL($dateRange['from']) : null;
        $toDate = isset($dateRange['to']) && $dateRange['to'] ? pSQL($dateRange['to']) : null;

        if (!$fromDate && !$toDate) {
            return [];
        }

        $sql = 'SELECT DISTINCT p.id_product
            FROM ' . _DB_PREFIX_ . 'product p
            INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                ON ps.id_product = p.id_product AND ps.id_shop = ' . (int) $this->idShop . '
            WHERE ps.active = 1';

        if ($fromDate) {
            $sql .= " AND p.date_add >= '" . $fromDate . " 00:00:00'";
        }
        if ($toDate) {
            $sql .= " AND p.date_add <= '" . $toDate . " 23:59:59'";
        }

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

        return array_column($results ?: [], 'id_product');
    }

    /**
     * Get product IDs by date updated
     *
     * @param array $dateRange Date range with 'from' and 'to' keys
     * @return array Product IDs
     */
    public function getProductIdsByDateUpdated($dateRange)
    {
        $fromDate = isset($dateRange['from']) && $dateRange['from'] ? pSQL($dateRange['from']) : null;
        $toDate = isset($dateRange['to']) && $dateRange['to'] ? pSQL($dateRange['to']) : null;

        if (!$fromDate && !$toDate) {
            return [];
        }

        $sql = 'SELECT DISTINCT p.id_product
            FROM ' . _DB_PREFIX_ . 'product p
            INNER JOIN ' . _DB_PREFIX_ . 'product_shop ps
                ON ps.id_product = p.id_product AND ps.id_shop = ' . (int) $this->idShop . '
            WHERE ps.active = 1';

        if ($fromDate) {
            $sql .= " AND p.date_upd >= '" . $fromDate . " 00:00:00'";
        }
        if ($toDate) {
            $sql .= " AND p.date_upd <= '" . $toDate . " 23:59:59'";
        }

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

        return array_column($results ?: [], 'id_product');
    }

    // ========================================
    // Helper Methods
    // ========================================

    /**
     * Parse boolean value from various formats
     *
     * @param mixed $values Value(s) to parse
     * @param bool $default Default value if cannot parse
     * @return bool
     */
    protected function parseBooleanValue($values, $default = true)
    {
        if ($values === null) {
            return $default;
        }

        if (is_array($values)) {
            if (in_array('yes', $values) || in_array(true, $values, true) || in_array('1', $values) || in_array(1, $values, true)) {
                return true;
            }
            if (in_array('no', $values) || in_array(false, $values, true) || in_array('0', $values) || in_array(0, $values, true)) {
                return false;
            }
            return $default;
        }

        if ($values === 'yes' || $values === true || $values === '1' || $values === 1) {
            return true;
        }
        if ($values === 'no' || $values === false || $values === '0' || $values === 0) {
            return false;
        }

        return $default;
    }

    /**
     * Build pattern conditions array for SQL queries
     *
     * @param mixed $patterns Pattern(s) to process
     * @param string $fieldName SQL field name
     * @return array SQL conditions
     */
    protected function buildPatternConditions($patterns, $fieldName)
    {
        if (empty($patterns)) {
            return [];
        }

        if (!is_array($patterns)) {
            $patterns = [$patterns];
        }

        $patterns = array_filter($patterns);
        if (empty($patterns)) {
            return [];
        }

        $conditions = [];
        foreach ($patterns as $patternItem) {
            if (is_array($patternItem) && isset($patternItem['pattern'])) {
                $pattern = $patternItem['pattern'];
                $caseSensitive = !empty($patternItem['caseSensitive']);
            } else {
                $pattern = is_string($patternItem) ? $patternItem : '';
                $caseSensitive = false;
            }

            if (empty($pattern)) {
                continue;
            }

            $regexPattern = $this->convertPatternToRegex($pattern, $caseSensitive);
            if ($regexPattern !== null) {
                if ($caseSensitive) {
                    $conditions[] = 'BINARY ' . $fieldName . ' REGEXP BINARY \'' . pSQL($regexPattern) . '\'';
                } else {
                    $conditions[] = 'LOWER(' . $fieldName . ') REGEXP LOWER(\'' . pSQL($regexPattern) . '\')';
                }
            }
        }

        return $conditions;
    }

    /**
     * Convert user-friendly pattern to MySQL REGEXP pattern
     *
     * User syntax:
     *   * = any text (wildcard)
     *   {number} = any number sequence (0-9+)
     *   {letter} = single letter (A-Z, a-z)
     *
     * @param string $pattern User-friendly pattern
     * @param bool $caseSensitive Whether the pattern is case-sensitive
     * @return string|null MySQL REGEXP pattern or null if invalid
     */
    protected function convertPatternToRegex($pattern, $caseSensitive = false)
    {
        if (empty($pattern)) {
            return null;
        }

        $tokens = [
            '{number}' => "\x00NUMBER\x00",
            '{letter}' => "\x00LETTER\x00",
        ];

        $pattern = str_replace(array_keys($tokens), array_values($tokens), $pattern);
        $pattern = str_replace('*', "\x00WILDCARD\x00", $pattern);
        $pattern = preg_quote($pattern, '/');

        $letterPattern = $caseSensitive ? '[A-Za-z]' : '[a-z]';
        $pattern = str_replace(preg_quote("\x00WILDCARD\x00", '/'), '.*', $pattern);
        $pattern = str_replace(preg_quote("\x00NUMBER\x00", '/'), '[0-9]+', $pattern);
        $pattern = str_replace(preg_quote("\x00LETTER\x00", '/'), $letterPattern, $pattern);

        return $pattern;
    }

    /**
     * Build range conditions for SQL queries
     *
     * @param array $ranges Range values
     * @param string $fieldName SQL field name
     * @param int $minAllowed Minimum allowed value
     * @return array SQL conditions
     */
    protected function buildRangeConditions($ranges, $fieldName, $minAllowed = 0)
    {
        if (empty($ranges) || !is_array($ranges)) {
            return [];
        }

        $rangeConditions = [];
        foreach ($ranges as $range) {
            if (!is_array($range)) {
                continue;
            }

            $minVal = isset($range['min']) && $range['min'] !== '' ? (float) $range['min'] : null;
            $maxVal = isset($range['max']) && $range['max'] !== '' ? (float) $range['max'] : null;

            if ($minVal !== null && $minVal < $minAllowed) {
                $minVal = $minAllowed;
            }
            if ($maxVal !== null && $maxVal < $minAllowed) {
                $maxVal = $minAllowed;
            }

            if ($minVal === null && $maxVal === null) {
                continue;
            }

            if ($minVal !== null && $maxVal !== null && $minVal > $maxVal) {
                $temp = $minVal;
                $minVal = $maxVal;
                $maxVal = $temp;
            }

            $condition = '(1=1';
            if ($minVal !== null) {
                $condition .= ' AND ' . $fieldName . ' >= ' . $minVal;
            }
            if ($maxVal !== null) {
                $condition .= ' AND ' . $fieldName . ' <= ' . $maxVal;
            }
            $condition .= ')';
            $rangeConditions[] = $condition;
        }

        return $rangeConditions;
    }

    /**
     * Get matching product attribute combinations based on attribute filters
     *
     * @param array $productIds Product IDs to filter
     * @param array $combinationConditions Combination filter conditions
     * @return array Array of ['id_product' => X, 'id_product_attribute' => Y]
     */
    public function getMatchingCombinations(array $productIds, array $combinationConditions)
    {
        if (empty($productIds) || empty($combinationConditions) || empty($combinationConditions['conditions'])) {
            return [];
        }

        $conditions = $combinationConditions['conditions'];

        // Build SQL to find matching combinations
        // Logic: AND between attribute groups, OR within values
        $sql = 'SELECT DISTINCT pa.id_product, pa.id_product_attribute
                FROM ' . _DB_PREFIX_ . 'product_attribute pa
                INNER JOIN ' . _DB_PREFIX_ . 'product_attribute_shop pas
                    ON pas.id_product_attribute = pa.id_product_attribute
                    AND pas.id_shop = ' . (int) $this->idShop . '
                WHERE pa.id_product IN (' . implode(',', array_map('intval', $productIds)) . ')';

        // Build condition for each attribute group (ANDed together)
        $groupConditions = [];
        foreach ($conditions as $condition) {
            $attributeGroupId = (int) ($condition['attribute_group'] ?? 0);
            $attributeValues = isset($condition['values']) ? array_map('intval', (array) $condition['values']) : [];

            if ($attributeGroupId && !empty($attributeValues)) {
                // Subquery to check this attribute group has one of the specified values
                $groupConditions[] = 'EXISTS (
                    SELECT 1 FROM ' . _DB_PREFIX_ . 'product_attribute_combination pac
                    INNER JOIN ' . _DB_PREFIX_ . 'attribute a ON a.id_attribute = pac.id_attribute
                    WHERE pac.id_product_attribute = pa.id_product_attribute
                    AND a.id_attribute_group = ' . $attributeGroupId . '
                    AND a.id_attribute IN (' . implode(',', $attributeValues) . ')
                )';
            }
        }

        if (empty($groupConditions)) {
            return [];
        }

        $sql .= ' AND ' . implode(' AND ', $groupConditions);

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

        return $results ?: [];
    }

    /**
     * Get matching product and combination IDs from groups
     *
     * For groups with combination filters, returns both product IDs and combination details.
     *
     * @param array $groups Selection groups
     * @return array ['products' => [id_product, ...], 'combinations' => [['id_product' => X, 'id_product_attribute' => Y], ...]]
     */
    public function getMatchingProductAndCombinationIds(array $groups)
    {
        $allProductIds = [];
        $allCombinations = [];

        // Groups are OR'd together
        foreach ($groups as $group) {
            $includeMethod = $group['include']['method'] ?? 'all';
            $includeValues = $group['include']['values'] ?? [];

            // Get included products
            $includedIds = $this->getIdsByMethod($includeMethod, $includeValues);

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

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

            // Remove excluded from included
            $groupProductIds = array_diff($includedIds, $excludedIds);

            // Check if this group has combination filters
            if (!empty($group['combinations']) && !empty($group['combinations']['conditions'])) {
                // Get matching combinations
                $groupCombinations = $this->getMatchingCombinations($groupProductIds, $group['combinations']);
                $allCombinations = array_merge($allCombinations, $groupCombinations);
            } else {
                // No combination filter - just add product IDs
                $allProductIds = array_merge($allProductIds, $groupProductIds);
            }
        }

        return [
            'products' => array_unique($allProductIds),
            'combinations' => $allCombinations,
        ];
    }

    /**
     * Expand product IDs to combination-level prefixed IDs.
     * Products WITH combinations → "c:{id_product_attribute}" per combination.
     * Products WITHOUT combinations (simple) → "p:{id_product}".
     *
     * @param array $productIds Plain integer product IDs
     * @param string $mode 'combination' (only combos + simple) or 'both' (parent + combos + simple)
     * @return array Prefixed string IDs
     */
    public function expandToCombinations(array $productIds, $mode = 'combination')
    {
        if (empty($productIds)) {
            return [];
        }

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

        // Get all combinations for these products
        $sql = 'SELECT pa.id_product_attribute, pa.id_product
            FROM ' . _DB_PREFIX_ . 'product_attribute pa
            INNER JOIN ' . _DB_PREFIX_ . 'product_attribute_shop pas
                ON pas.id_product_attribute = pa.id_product_attribute AND pas.id_shop = ' . (int) $idShop . '
            WHERE pa.id_product IN (' . implode(',', array_map('intval', $productIds)) . ')
            ORDER BY pa.id_product, pa.id_product_attribute';

        $rows = $db->executeS($sql);

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

        $result = [];
        foreach ($productIds as $idProduct) {
            $idProduct = (int) $idProduct;

            if (!empty($combosByProduct[$idProduct])) {
                // Product has combinations
                if ($mode === 'both') {
                    // Include parent product entry
                    $result[] = 'p:' . $idProduct;
                }
                foreach ($combosByProduct[$idProduct] as $idPA) {
                    $result[] = 'c:' . $idPA;
                }
            } else {
                // Simple product — no combinations
                $result[] = 'p:' . $idProduct;
            }
        }

        return $result;
    }
}
