<?php
require_once('productSearch.php');

class ProductFilter {

  protected PDO $pdo;
  protected array $activeFilters = [];
  protected array $allGroups = [];
  protected array $allGroupsBySlug = [];
  protected array $allItems = [];
  protected array $allItemsByGroupSlug = [];
  protected array $allDisplayGroups = [];
  protected array $input = [];
  
  protected ?ProductSearch $searchProvider = null;
  protected ?string $searchQuery = null;
  protected bool $searchActive = false;
  protected ?string $searchLanguage = null;
  
  protected array $levelGuids = [];
  protected bool $levelFilteringEnabled = false;
  protected ?bool $levelsExistCache = null;
  
  
  protected string $stockFilterMode = 'all'; // 'all' | 'hide_oos' | 'only_instock' | 'only_backorder'
  protected ?bool $hasReservationTable = null;

  public function setConnection(PDO $conn): void { 
  	$this->pdo = $conn;
  }

  public function init(): void {
    $this->loadGroups('tags');
    $this->loadGroups('properties');
    $this->loadItems('tags');
    $this->loadItems('properties');
    $this->loadActiveFiltersFromSlugs();
  }
  
  
  public function setSearchProvider(ProductSearch $provider): void {
    $this->searchProvider = $provider;
  }
  
  public function setSearchQuery(?string $q): void {
    $q = $q !== null ? trim($q) : null;
    $this->searchQuery = ($q === '') ? null : $q;
    $this->searchActive = ($this->searchQuery !== null);
  }
  public function setSearchLanguage(?string $lang): void {
    $this->searchLanguage = $lang ?: null;
    if ($this->searchProvider && $lang !== null) {
      $this->searchProvider = new ProductSearch($lang);
    }
  }
  
  public function setStockFilterMode(string $mode): void {
	  $allowed = ['all','hide_oos','only_instock','only_backorder'];
	  $this->stockFilterMode = in_array($mode, $allowed, true) ? $mode : 'all';
	}
  
  public function enableLevelFiltering(array $levelGuids): void {
    $levelGuids = array_values(array_filter(array_map('trim', $levelGuids)));
    $this->levelGuids = $levelGuids;
    $this->levelFilteringEnabled = !empty($levelGuids);
  }

  protected function levelsExist(): bool {
    if ($this->levelsExistCache !== null) return $this->levelsExistCache;
    $result = $this->pdo->query("SELECT 1 FROM products_levels_links LIMIT 1");
    $this->levelsExistCache = (bool) $result->fetchColumn();
    return $this->levelsExistCache;
  }

  protected function loadGroups(string $type): void {
    global $language;

    $images = [];
    $sql = "SELECT id_module, id_media FROM products_".$type."_groups_images ORDER BY sortorder ASC";
    $result = $this->pdo->prepare($sql);
    $result->execute();
    while ($arr = $result->fetch()) {
      $images[$arr['id_module']] = $arr['id_media'];
    }

    $sql = "SELECT id, guidv4, url, title, field_type, c_filter, c_list, display, content, addition, class, sortorder, context_guidv4_group, context_guidv4, step_group
            FROM products_".$type."_groups
            WHERE c_active = '1' AND (c_filter = '1' OR c_list = '1') AND language = :language
            ORDER BY sortorder ASC";
    $result = $this->pdo->prepare($sql);
    $result->bindValue(':language', $language, PDO::PARAM_STR);
    $result->execute();
    while ($arr = $result->fetch()) {
      if (isset($images[$arr['id']])) {
        $arr['id_media'] = $images[$arr['id']];
      }
      $arr['type'] = $type;
      
      $this->allDisplayGroups[$arr['guidv4']] = $arr;
      
      if ($arr['c_filter'] === '1') {
	      $this->allGroups[$arr['guidv4']] = $arr;
	      if (!empty($arr['url'])) {
	        $this->allGroupsBySlug[$type][$arr['url']] = $arr;
	      }
	    }
    }
  }

  protected function loadItems(string $type): void {
    global $language;

    $images = [];
    $sql = "SELECT id_module, id_media FROM products_".$type."_images ORDER BY sortorder ASC";
    $result = $this->pdo->prepare($sql);
    $result->execute();
    while ($arr = $result->fetch()) {
      $images[$arr['id_module']][] = $arr['id_media'];
    }

    $sql = "SELECT id, guidv4, url, title, guidv4_group, content, addition, class, sortorder, c_boolean 
            FROM products_".$type."
            WHERE c_active = '1' AND language = :language
            ORDER BY sortorder ASC";
    $result = $this->pdo->prepare($sql);
    $result->bindValue(':language', $language, PDO::PARAM_STR);
    $result->execute();
    while ($arr = $result->fetch()) {
      if (isset($images[$arr['id']])) {
        $arr['id_media'] = $images[$arr['id']];
      }
      $group = $this->allDisplayGroups[$arr['guidv4_group']] ?? null;
      if (!$group) continue;
      $type = $group['type'];
      $groupSlug = $group['url'];
      $this->allItems[$arr['guidv4_group']][] = $arr;
      if (!empty($arr['url'])) {
        $this->allItemsByGroupSlug[$type][$groupSlug][$arr['url']] = $arr;
      }
    }
  }
  
  public function setInput(array $input): void {
	  $this->input = $input;
	}

  protected function loadActiveFiltersFromSlugs(): void {
  	$input = $this->input ?? $_GET;
  	foreach ($input as $key => $value) {
      foreach (['tags', 'properties'] as $type) {
        $prefix = $type . '_';
        if (strpos($key, $prefix) === 0) {
          $groupSlug = substr($key, strlen($prefix));
          $itemSlugs = is_array($value) ? $value : explode(',', $value);

          if (!isset($this->allGroupsBySlug[$type][$groupSlug])) continue;

          $groupId = $this->allGroupsBySlug[$type][$groupSlug]['guidv4'];
          $itemsInGroup = $this->allItemsByGroupSlug[$type][$groupSlug] ?? [];

          foreach ($itemSlugs as $slug) {
            $slug = trim($slug);
            if (isset($itemsInGroup[$slug])) {
              $this->activeFilters[$groupId][] = $itemsInGroup[$slug]['guidv4'];
            }
          }
        }
      }
    }
  }
  
  public function getFilteredActiveFiltersUpToStep(int $stepIndex): array {
	  $allowedSteps = [];
	  $seenSteps = [];
	
	  foreach ($this->allGroups as $group) {
	    $step = $group['step_group'] ?? '__default';
	    if (!in_array($step, $seenSteps, true)) {
	      $seenSteps[] = $step;
	    }
	    if (count($seenSteps) > $stepIndex + 1) break;
	    $allowedSteps[] = $step;
	  }
	
	  $limited = [];
	
	  foreach ($this->activeFilters as $groupId => $itemIds) {
	    $group = $this->allGroups[$groupId] ?? null;
	    if (!$group) continue;
	
	    $step = $group['step_group'] ?? '__default';
	    if (in_array($step, $allowedSteps, true)) {
	      $limited[$groupId] = $itemIds;
	    }
	  }
	
	  return $limited;
	}


  public function getAvailableItemsForEachGroup(?array $filters = null): array {
    $result = [];
    if ($filters === null) {
	    $filters = $this->activeFilters;
	  }

    foreach ($this->allGroups as $groupGuid => $group) {
      if (!empty($group['context_guidv4_group']) && !empty($group['context_guidv4'])) {
        $contextActive = in_array(
          $group['context_guidv4'],
          $filters[$group['context_guidv4_group']] ?? [],
          true
        );
        if (!$contextActive) {
          $result[$groupGuid] = [];
          continue;
        }
      }
      
      $filteredCopy = $filters;
      $wasGroupFiltered = isset($filteredCopy[$groupGuid]);
      unset($filteredCopy[$groupGuid]);

      $productIds = $this->getProductIdsForFilters($filteredCopy);
      $productIds = $this->filterIdsByStock($productIds);

      if (empty($productIds)) {
        $result[$groupGuid] = [];
        continue;
      }

      $productIds = array_values($productIds);
      if (count($productIds) === 1) {
        $productIds[] = -1;
      }

      $type = $group['type'];
      $placeholders = implode(',', array_fill(0, count($productIds), '?'));
      $sql = "SELECT guidv4_group, guidv4, COUNT(*) as count
              FROM products_product_".$type."_links
              WHERE id_module IN ($placeholders)
              AND guidv4_group = ?
              GROUP BY guidv4_group, guidv4";

      $stmt = $this->pdo->prepare($sql);
      $params = [...$productIds, $groupGuid];
      $stmt->execute($params);
      $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

      foreach ($rows as $row) {
        $result[$row['guidv4_group']][$row['guidv4']] = (int)$row['count'];
      }

      if ($wasGroupFiltered) {
        foreach ($this->activeFilters[$groupGuid]  ?? [] as $itemId) {
          if (!isset($result[$groupGuid][$itemId])) {
            $result[$groupGuid][$itemId] = 0;
          }
        }
      }
    }
    return $result;
  }

  public function getMatchingProductIds(array $options = []): array {
    global $language;
    
  	$baseIds = $this->getProductIdsForFilters($this->activeFilters);
    $baseIds = array_values(array_unique(array_map('intval', $baseIds)));
    $baseIds = $this->filterIdsByStock($baseIds); 
    
    if (empty($baseIds)) {
    	return [];
    }
    
    $hasLimit = isset($options['limit']);
    $hasSorting = isset($options['sort_by']) || isset($options['sort_dir']);
    $hasPrice = isset($options['price_min']) || isset($options['price_max']) || isset($options['id_country']) || isset($options['quantity']);
    
    if (!$hasLimit && !$hasSorting && !$hasPrice) {
    	return $baseIds;
    }

    if (empty($baseIds)) {
    	return [];
    }
    
    
    $limit = (int)($options['limit']  ?? 24);
    $offset = (int)($options['offset'] ?? 0);
    $id_country = $options['id_country'] ?? null;
    $fallback_country = $options['fallback_country'] ?? null;
    $quantity = (int)($options['quantity'] ?? 1);
    $priceMin = $options['price_min'] ?? null;
    $priceMax = $options['price_max'] ?? null;

    $sortByMap = [
    	'sortorder'	=> 'products.sortorder',
    	'sku' => 'products.sku',
      'title' => 'products_content.title',
      'price'	=> 'effective_price'
    ];
    
    $sortBy = $sortByMap[$options['sort_by'] ?? 'sortorder'] ?? 'products.sortorder';
    $sortDir = strtoupper($options['sort_dir'] ?? 'ASC') === 'DESC' ? 'DESC' : 'ASC';
    
    $needsTitleJoin = ($options['sort_by'] ?? '') === 'title';

    $inPlaceholders = [];
		foreach ($baseIds as $idx => $id) {
			$inPlaceholders[] = ":id{$idx}";
		}
		$in = implode(',', $inPlaceholders);

    $sql = "SELECT products.id, 
    				(
    					SELECT products_prices.price_gross
    					FROM products_prices AS products_prices
              WHERE products_prices.id_module = products.id 
              	AND products_prices.price_role = 'base'
              	AND (products_prices.id_country IS NULL OR products_prices.id_country = :id_country OR products_prices.id_country = :fallback_country)
                AND (products_prices.quantity IS NULL OR products_prices.quantity <= :quantity)
              ORDER BY
                (products_prices.id_country = :id_country) DESC,
                (products_prices.id_country = :fallback_country) DESC,
                (products_prices.id_country IS NULL) ASC,
                COALESCE(products_prices.quantity, 0) DESC
              LIMIT 1
            ) AS effective_price
		        FROM products AS products ";
    if ($needsTitleJoin) {
      $sql .= " JOIN products_content AS products_content
                  ON products_content.id_module = products.id
                 AND products_content.language = :language ";
    }
		$sql .= "WHERE products.c_active = '1'
       			AND (products.id_module IS NULL OR products.id_module = 0)
		        AND products.id IN ($in)";

    $having = [];
    if ($priceMin !== null) $having[] = "effective_price >= :price_min";
    if ($priceMax !== null) $having[] = "effective_price <= :price_max";
    if (!empty($having)) {
    	$sql .= " HAVING ".implode(' AND ', $having);
    }

    if ($hasSorting) {
			$sql .= " ORDER BY ".$sortBy." ".$sortDir.", products.id ASC";
		} else if ($hasLimit) {
			$sql .= " ORDER BY products.id ASC";
		}

    if ($hasLimit) {
    	$sql .= " LIMIT :limit OFFSET :offset";
    }

    $result = $this->pdo->prepare($sql);

   	foreach ($baseIds as $idx => $id) {
   		$result->bindValue(":id{$idx}", (int)$id, PDO::PARAM_INT);
		}
		
		if ($needsTitleJoin) {
			$result->bindValue(':language', $language);
    }

    $result->bindValue(':id_country', $id_country, $id_country === null ? PDO::PARAM_NULL : PDO::PARAM_INT);
    $result->bindValue(':fallback_country', $fallback_country, $fallback_country === null ? PDO::PARAM_NULL : PDO::PARAM_INT);
    $result->bindValue(':quantity', $quantity, PDO::PARAM_INT);
    if ($priceMin !== null) $result->bindValue(':price_min', $priceMin);
    if ($priceMax !== null) $result->bindValue(':price_max', $priceMax);
    if ($hasLimit) {
      $result->bindValue(':limit',  $limit,  PDO::PARAM_INT);
      $result->bindValue(':offset', $offset, PDO::PARAM_INT);
    }

    $result->execute();
    $ids = array_map('intval', array_column($result->fetchAll(PDO::FETCH_ASSOC), 'id'));
		return $this->filterIdsByStock($ids);
	}
	
	
	public function getEffectivePriceRange(?array $filters = null, ?int $id_country = null, int $quantity = 1, ?int $fallback_country = null): array {
	  $filters = $filters ?? $this->activeFilters;
	
	  $baseIds = $this->getProductIdsForFilters($filters);
	  $baseIds = array_values(array_unique(array_map('intval', $baseIds)));
	  $baseIds = $this->filterIdsByStock($baseIds);
	  if (empty($baseIds)) {
	    return [null, null];
	  }
	
	  $inPlaceholders = [];
	  foreach ($baseIds as $idx => $id) {
	    $inPlaceholders[] = ":id{$idx}";
	  }
	  $in = implode(',', $inPlaceholders);
	
	  $sql = "SELECT MIN(effective_price) AS price_min, MAX(effective_price) AS price_max
	          FROM (
	            SELECT products.id,
	              (
	                SELECT products_prices.price_gross
	                FROM products_prices AS products_prices
	                WHERE products_prices.id_module = products.id
              			AND products_prices.price_role = 'base'
              			AND (products_prices.id_country IS NULL OR products_prices.id_country = :id_country OR products_prices.id_country = :fallback_country)
	                  AND (products_prices.quantity IS NULL OR products_prices.quantity <= :quantity)
	                ORDER BY
	                  (products_prices.id_country = :id_country) DESC,
		                (products_prices.id_country = :fallback_country) DESC,
		                (products_prices.id_country IS NULL) ASC,
	                  COALESCE(products_prices.quantity, 0) DESC
	                LIMIT 1
	              ) AS effective_price
	            FROM products AS products
	            WHERE products.c_active = '1'
	              AND products.id IN ($in)
	          ) t
	          WHERE t.effective_price IS NOT NULL";
	
	  $result = $this->pdo->prepare($sql);
	
	  foreach ($baseIds as $idx => $id) {
	    $result->bindValue(":id{$idx}", $id, PDO::PARAM_INT);
	  }
	  $result->bindValue(':id_country', $id_country, $id_country === null ? PDO::PARAM_NULL : PDO::PARAM_INT);
    $result->bindValue(':fallback_country', $fallback_country, $fallback_country === null ? PDO::PARAM_NULL : PDO::PARAM_INT);
	  $result->bindValue(':quantity', $quantity, PDO::PARAM_INT);
	
	  $result->execute();
	  $arr = $result->fetch(PDO::FETCH_ASSOC) ?: [];
	
	  $min = isset($arr['price_min']) ? (float)$arr['price_min'] : null;
	  $max = isset($arr['price_max']) ? (float)$arr['price_max'] : null;
	
	  return [$min, $max];
	}
  
  public function getProductIdsForFilters(array $filters): array {
    $baseIds = [];
    if (empty($filters)) {
      $baseIds = $this->getAllProductIds();
    } else {
      $contextGroups = [];
      foreach ($this->allGroups as $groupId => $group) {
        if (empty($filters[$groupId])) continue;

        $itemIds = $filters[$groupId];
        $type = $group['type'];
        $step = $group['step_group'] ?? '__default';
        $context = $group['context_guidv4_group'] ?? $groupId;
        $contextKey = "{$group['type']}|{$step}|{$context}";

        $contextGroups[$contextKey][] = [
          'groupId' => $groupId,
          'itemIds' => $itemIds,
          'type' => $type
        ];
      }

      if (empty($contextGroups)) {
        $baseIds = $this->getAllProductIds();
      } else {
        $subqueries = [];
        $params = [];
        foreach ($contextGroups as $groupFilters) {
				  $type = $groupFilters[0]['type'];
				  $orParts = [];
				
				  foreach ($groupFilters as $filter) {
				    $placeholders = implode(',', array_fill(0, count($filter['itemIds']), '?'));
				
				    $orParts[] = " (links.guidv4_group = ? AND links.guidv4 IN ($placeholders)) ";
				
				    $params[] = $filter['groupId'];
				
				    foreach ($filter['itemIds'] as $id) {
				      $params[] = $id;
				    }
				  }
				
				  $subqueries[] = "SELECT DISTINCT CASE WHEN products.id_module IS NOT NULL AND products.id_module != 0 THEN products.id_module ELSE products.id END AS id_module
				  								FROM products_product_".$type."_links AS links
				  								INNER JOIN products AS products ON products.id = links.id_module WHERE ".implode(' OR ', $orParts);
				}
        $sql = implode(" INTERSECT ", $subqueries);
        $result = $this->pdo->prepare($sql);
        $result->execute($params);
        $baseIds = $result->fetchAll(PDO::FETCH_COLUMN);
      }
    }
    
    if ($this->searchActive && $this->searchProvider && $this->searchQuery !== null) {
      $searchIds = $this->searchProvider->searchIds($this->pdo, $this->searchQuery);
      if (empty($searchIds)) return [];
      $baseIds = empty($baseIds) ? $searchIds : array_values(array_intersect($baseIds, $searchIds));
      return $baseIds;
    }
    
    if (!$this->levelFilteringEnabled || $this->searchActive || empty($this->levelGuids) || !$this->levelsExist()) {
      return $baseIds;
    }

    if (empty($baseIds)) return [];

    $baseIds = array_values($baseIds);
    if (count($baseIds) === 1) $baseIds[] = -1;

    $placeBase = implode(',', array_fill(0, count($baseIds), '?'));
    $placeLvl  = implode(',', array_fill(0, count($this->levelGuids), '?'));

    $sql = "
      WITH base(id) AS (
        SELECT id FROM products WHERE id IN ($placeBase) AND c_active = '1'
      ),
      direct_match AS (
        SELECT DISTINCT b.id
        FROM base b
        JOIN products_levels_links pll ON pll.id_module = b.id
        WHERE pll.guidv4 IN ($placeLvl)
      ),
      inherited_match AS (
        SELECT DISTINCT v.id
        FROM base v
        JOIN products p ON p.id = v.id
        JOIN products parent ON parent.id = p.id_module
        LEFT JOIN products_levels_links vll ON vll.id_module = v.id
        JOIN products_levels_links pll ON pll.id_module = parent.id
        WHERE p.id_module IS NOT NULL AND p.id_module != 0 AND vll.id_module IS NULL AND pll.guidv4 IN ($placeLvl)
      )
      SELECT id FROM direct_match
      UNION
      SELECT id FROM inherited_match";

    $result = $this->pdo->prepare($sql);
    $result->execute([...$baseIds, ...$this->levelGuids, ...$this->levelGuids]);
    $filteredByLevel = $result->fetchAll(PDO::FETCH_COLUMN);

    return $filteredByLevel;
  }
  
  
  public function getAllProductIds(): array {
	  $stmt = $this->pdo->query("SELECT id FROM products WHERE c_active = '1' AND (id_module IS NULL OR id_module = 0)");
	  return $stmt->fetchAll(PDO::FETCH_COLUMN);
  }

  public function getGroups(): array {
    return $this->allGroups;
  }

  public function getItems(): array {
    return $this->allItems;
  }

  public function getActiveFilters(): array {
    return $this->activeFilters;
  }

  public function getGroupSlug(string $groupId): ?string {
    return $this->allGroups[$groupId]['url'] ?? null;
  }

  public function getItemSlug(string $groupId, string $itemGuid): ?string {
    foreach ($this->allItems[$groupId] ?? [] as $item) {
      if ($item['guidv4'] === $itemGuid) {
        return $item['url'] ?? null;
      }
    }
    return null;
  }
  
  public function getDisplayGroups(): array {
	  return $this->allDisplayGroups;
	}

  public function getDisplayMode(string $groupId): string {
    return $this->allGroups[$groupId]['display'] ?? 'always';
  }

  public function getFieldType(string $groupId): ?string {
    return $this->allGroups[$groupId]['field_type'] ?? null;
  }
  
  public function convertFiltersToGuids(array $slugInput): array {
	  $guidFilters = [];
	
	  foreach ($slugInput AS $key => $value) {
	    foreach (['tags', 'properties'] AS $type) {
	      $prefix = $type . '_';
	      if (strpos($key, $prefix) === 0) {
	        $groupSlug = substr($key, strlen($prefix));
	        $itemSlugs = is_array($value) ? $value : explode(',', $value);
	
	        $groupData = $this->allGroupsBySlug[$type][$groupSlug] ?? null;
	        if (!$groupData) continue;
	
	        $groupGuid = $groupData['guidv4'];
	        $itemsInGroup = $this->allItemsByGroupSlug[$type][$groupSlug] ?? [];
	
	        foreach ($itemSlugs AS $slug) {
	          $slug = trim($slug);
	          if (isset($itemsInGroup[$slug])) {
	            $guidFilters[$groupGuid][] = $itemsInGroup[$slug]['guidv4'];
	          }
	        }
	      }
	    }
	  }
	
	  return $guidFilters;
	}
		
	public function convertFiltersToSlugs(array $guidFilters): array {
	  $slugInput = [];
	
	  foreach ($guidFilters AS $groupGuid => $itemGuids) {
	    $group = $this->allGroups[$groupGuid] ?? null;
	    if (!$group || empty($group['url'])) continue;
	
	    $type = $group['type'];
	    $groupSlug = $group['url'];
	    $slugs = [];
	
	    foreach ($itemGuids AS $itemGuid) {
	      foreach ($this->allItems[$groupGuid] ?? [] AS $item) {
	        if ($item['guidv4'] === $itemGuid && !empty($item['url'])) {
	          $slugs[] = $item['url'];
	          break;
	        }
	      }
	    }
	
	    if (!empty($slugs)) {
	      $slugInput["{$type}_{$groupSlug}"] = implode(',', $slugs);
	    }
	  }
	
	  return $slugInput;
	}
	
	public function resolve_sort_params(array $src, string $defaultBy = 'sortorder', string $defaultDir = 'ASC'): array {
	  $allowedBy = ['sortorder','price','sku','title'];
	  $allowedDir = ['ASC','DESC'];
	
	  $sort_by = $defaultBy;
	  $sort_dir = $defaultDir;

	  if (!empty($src['sort_by']) || !empty($src['sort_dir'])) {
	    $by = strtolower(trim((string)($src['sort_by'] ?? '')));
	    $dir = strtoupper(trim((string)($src['sort_dir'] ?? '')));
	    if (in_array($by, $allowedBy, true)) $sort_by = $by;
	    if (in_array($dir, $allowedDir, true)) $sort_dir = $dir;
	    return [$sort_by, $sort_dir];
	  }

	  if (isset($src['sort'])) {
	    $val = (string)$src['sort'];
	
	    $parts = explode('_', $val, 2);
	    $by = strtolower(trim($parts[0] ?? ''));
	    $dir = strtoupper(trim($parts[1] ?? ''));
	
	    if (!in_array($by, $allowedBy, true)) $by = $defaultBy;
	    if (!in_array($dir, $allowedDir, true)) $dir = $defaultDir;
	
	    return [$by, $dir];
	  }
	
	  return [$sort_by, $sort_dir];
	}
	
	protected function filterIdsByStock(array $ids): array {
		if (empty($ids) || $this->stockFilterMode === 'all') return $ids;
	
		$ids = array_values(array_unique(array_map('intval', $ids)));
	
		$inPlaceholders = [];
		foreach ($ids as $idx => $id) {
			$inPlaceholders[] = ":id{$idx}";
		}
		$in = implode(',', $inPlaceholders);
	
		$hasReservationTable = $this->hasReservationTable();
	
		if ($hasReservationTable) {
			$sql_available = "products_sub.stock - COALESCE((
				SELECT SUM(stock_reservation.quantity)
				FROM stock_reservation AS stock_reservation
				WHERE stock_reservation.id_product = products_sub.id
					AND stock_reservation.status = 'active'
					AND stock_reservation.reserved_until >= :reserved_until_product
			), 0)";
	
			$sql_variant_available = "variants.stock - COALESCE((
				SELECT SUM(stock_reservation_variant.quantity)
				FROM stock_reservation AS stock_reservation_variant
				WHERE stock_reservation_variant.id_product = variants.id
					AND stock_reservation_variant.status = 'active'
					AND stock_reservation_variant.reserved_until >= :reserved_until_variant
			), 0)";
		} else {
			$sql_available = "products_sub.stock";
			$sql_variant_available = "variants.stock";
		}
	
		$sql = "SELECT products.id
						FROM (
							SELECT
								products_sub.id,
								products_sub.c_digital,
								products_sub.c_coupon,
								products_sub.c_stock,
								products_sub.c_oversell,
								($sql_available) AS available
							FROM products AS products_sub
							WHERE products_sub.id IN ($in)
								AND products_sub.c_active = '1'
						) AS products ";
	
		switch ($this->stockFilterMode) {
			case 'hide_oos':
				$sql .= "WHERE (
					products.c_digital = '1'
					OR products.c_coupon = '1'
					OR products.c_stock = '0'
					OR products.available > 0
					OR products.c_oversell = '1'
					OR EXISTS (
						SELECT 1
						FROM products AS variants
						WHERE variants.id_module = products.id
							AND variants.c_active = '1'
							AND (
								variants.c_digital = '1'
								OR variants.c_coupon = '1'
								OR variants.c_stock = '0'
								OR ($sql_variant_available) > 0
								OR variants.c_oversell = '1'
							)
					)
				)";
				break;
	
			case 'only_instock':
				$sql .= "WHERE (
					products.c_digital = '1'
					OR products.c_coupon = '1'
					OR products.c_stock = '0'
					OR products.available > 0
					OR EXISTS (
						SELECT 1
						FROM products AS variants
						WHERE variants.id_module = products.id
							AND variants.c_active = '1'
							AND (
								variants.c_digital = '1'
								OR variants.c_coupon = '1'
								OR variants.c_stock = '0'
								OR ($sql_variant_available) > 0
							)
					)
				)";
				break;
	
			case 'only_backorder':
				$sql .= "WHERE (
					(
						products.c_stock = '1'
						AND products.available <= 0
						AND products.c_oversell = '1'
					)
					OR EXISTS (
						SELECT 1
						FROM products AS variants
						WHERE variants.id_module = products.id
							AND variants.c_active = '1'
							AND variants.c_stock = '1'
							AND ($sql_variant_available) <= 0
							AND variants.c_oversell = '1'
					)
				)";
				break;
	
			default:
				return $ids;
		}
	
		$result = $this->pdo->prepare($sql);
	
		foreach ($ids as $idx => $id) {
			$result->bindValue(":id{$idx}", $id, PDO::PARAM_INT);
		}
	
		if ($hasReservationTable) {
			$now = time();
	
			$result->bindValue(':reserved_until_product', $now, PDO::PARAM_INT);
			$result->bindValue(':reserved_until_variant', $now, PDO::PARAM_INT);
		}
	
		$result->execute();
	
		return array_map('intval', $result->fetchAll(PDO::FETCH_COLUMN));
	}
	
	public function hasReservationTable(): bool {
	  if ($this->hasReservationTable !== null) return $this->hasReservationTable;
	  try {
	    $sql = "SELECT 1 FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'stock_reservation' LIMIT 1";
	    $result = $this->pdo->query($sql);
	    $this->hasReservationTable = (bool)$result->fetchColumn();
	  } catch (\Throwable $e) {
	    try {
	    	$sql = "SELECT 1 FROM stock_reservation LIMIT 1";
	      $result = $this->pdo->query($sql);
	      $this->hasReservationTable = true;
	    } catch (\Throwable $e2) {
	      $this->hasReservationTable = false;
	    }
	  }
	  return $this->hasReservationTable;
	}
}