<?php

namespace AIPT\Utils\ProductFilter;

/**
 * ProductFilterBuilder - WooCommerce ürünleri için filtreleme mantığı oluşturur
 *
 * Bu sınıf, WooCommerce ürünlerini filtrelemek için wc_get_products()
 * fonksiyonunu kullanarak sorguları oluşturur ve yönetir. Fluent interface
 * kullanarak filtreleme kriterlerinin kolay bir şekilde birleştirilmesini sağlar.
 *
 * Filter logic supports both AND (intersection) and OR (union) modes via filterLogic().
 *
 * @version 2.4.5
 * @since 2.4.1 Migrated from WP_Query to wc_get_products() for better WooCommerce integration
 * @since 2.4.2 Fixed stock_status and price ordering using WooCommerce native parameters
 * @since 2.4.3 Fixed filter intersection logic and price sorting with include parameter
 * @since 2.4.4 Added AND/OR filter logic support, fixed price sorting to work at DB level across all pages
 */
class ProductFilterBuilder {
    /**
     * @var array wc_get_products() için oluşturulan argümanlar
     */
    private $args = [];

    /**
     * @var array Taksonomi sorguları
     */
    private $tax_query = [];

    /**
     * @var array Filtrelenmiş ürün ID'leri - tüm filtreler bu array üzerinden kesişim alır
     */
    private $filtered_product_ids = null;

    /**
     * @var string|null Price sorting direction (ASC veya DESC)
     */
    private $price_sort_direction = null;

    /**
     * @var int|null Cursor for cursor-based pagination (last seen product ID)
     */
    private $cursor_after = null;

    /**
     * @var int|null Cursor limit for cursor-based pagination
     */
    private $cursor_limit = null;

    /**
     * @var string Filter logic mode: 'and' (intersection) or 'or' (union)
     */
    private $filter_logic = 'and';

    /**
     * @var array Additional SQL WHERE clauses applied by the optimized query path.
     */
    private $where_parts = [];

    /**
     * @var array Negative filters that can be replayed through legacy fallbacks if SQL optimization fails.
     */
    private $negative_filters = [];

    /**
     * @var bool|null Cached wc_product_meta_lookup table availability.
     */
    private static $product_meta_lookup_exists = null;

    /**
     * ProductFilterBuilder constructor.
     */
    public function __construct() {
        $this->clear();
    }

    /**
     * Enable cursor-based pagination for large datasets.
     * More efficient than offset pagination for large tables.
     *
     * @param int|null $after_id Product ID to start after (cursor)
     * @param int      $limit    Number of products to fetch
     * @return ProductFilterBuilder
     */
    public function cursorPagination(?int $after_id, int $limit = 50): ProductFilterBuilder {
        $this->cursor_after = $after_id;
        $this->cursor_limit = max(1, min($limit, 500)); 

        $this->args['paginate'] = false;
        $this->args['limit'] = $this->cursor_limit;
        return $this;
    }

    /**
     * Sayfa numarası ve sayfa başına öğe sayısını ayarlar
     *
     * @param int $page Sayfa numarası
     * @param int $perPage Sayfa başına öğe sayısı
     * @return ProductFilterBuilder
     */
    public function pagination($page, $perPage = 10) {
        $this->args['page'] = max(1, absint($page));
        $this->args['limit'] = max(1, absint($perPage));
        return $this;
    }

    /**
     * Metin tabanlı arama filtresi ekler (başlık veya SKU'da)
     *
     * @param string $search Arama terimi
     * @return ProductFilterBuilder
     */
    public function search($search) {
        if (!empty($search)) {
            global $wpdb;
            $search_term = sanitize_text_field($search);

            $search_query = $wpdb->prepare(
                "SELECT DISTINCT p.ID
                FROM {$wpdb->posts} p
                LEFT JOIN {$wpdb->postmeta} pm ON (p.ID = pm.post_id AND pm.meta_key = '_sku')
                WHERE p.post_type = 'product'
                AND p.post_status IN ('publish', 'draft', 'private')
                AND (
                    p.post_title LIKE %s
                    OR pm.meta_value LIKE %s
                )",
                '%' . $wpdb->esc_like($search_term) . '%',
                '%' . $wpdb->esc_like($search_term) . '%'
            );

            $product_ids = $wpdb->get_col($search_query);
            $this->restrictToProductIds($product_ids);
        }

        return $this;
    }

    /**
     * Ürün durumu filtresi ekler (publish, draft, private)
     *
     * @param string|array $status Ürün durumu
     * @return ProductFilterBuilder
     */
    public function status($status) {
        if (!empty($status)) {
            $this->args['status'] = is_array($status) ? array_map('sanitize_text_field', $status) : sanitize_text_field($status);
        }

        return $this;
    }

    /**
     * Stok durumu filtresi ekler
     * SQL ile filtered_product_ids'e ekler - diğer filtrelerle kesişim sağlar
     *
     * @param string $stockStatus Stok durumu (instock, outofstock, onbackorder)
     * @return ProductFilterBuilder
     */
    public function stockStatus($stockStatus) {
        if (empty($stockStatus)) {
            return $this;
        }

        $allowed = ['instock', 'outofstock', 'onbackorder'];
        $sanitized = sanitize_text_field($stockStatus);

        if (!in_array($sanitized, $allowed, true)) {
            return $this;
        }

        global $wpdb;

        $query = $wpdb->prepare(
            "SELECT DISTINCT p.ID
            FROM {$wpdb->posts} p
            INNER JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id
            WHERE p.post_type = 'product'
            AND p.post_status IN ('publish', 'draft', 'private')
            AND pm.meta_key = '_stock_status'
            AND pm.meta_value = %s",
            $sanitized
        );

        $product_ids = $wpdb->get_col($query);
        $this->restrictToProductIds($product_ids);

        return $this;
    }

    /**
     * Adds category filter.
     *
     * Resolves slugs to term IDs via get_terms() which respects
     * Polylang's terms_clauses filter for multilingual compatibility.
     * This also removes the wp_terms JOIN, improving query performance.
     *
     * @param string|array $category Category slug or array of slugs.
     * @return ProductFilterBuilder
     */
    public function category($category) {
        if (empty($category)) {
            return $this;
        }

        $slugs = is_array($category) ? $category : [$category];
        $slugs = array_map('sanitize_text_field', $slugs);

        if (empty($slugs)) {
            return $this;
        }

        $term_ids = $this->resolveTermSlugsToIds($slugs, 'product_cat');

        if (empty($term_ids)) {
            $this->restrictToProductIds([]);
            return $this;
        }

        $this->filterByTermIds($term_ids, 'product_cat');

        return $this;
    }

    /**
     * Adds tag filter (by specific tag slugs).
     *
     * Resolves slugs to term IDs via get_terms() which respects
     * Polylang's terms_clauses filter for multilingual compatibility.
     *
     * @param string|array $tag Tag slug or array of slugs.
     * @return ProductFilterBuilder
     */
    public function tag($tag) {
        if (empty($tag)) {
            return $this;
        }

        $slugs = is_array($tag) ? $tag : [$tag];
        $slugs = array_map('sanitize_text_field', $slugs);

        if (empty($slugs)) {
            return $this;
        }

        $term_ids = $this->resolveTermSlugsToIds($slugs, 'product_tag');

        if (empty($term_ids)) {
            $this->restrictToProductIds([]);
            return $this;
        }

        $this->filterByTermIds($term_ids, 'product_tag');

        return $this;
    }

    /**
     * Resolves taxonomy term slugs to term IDs via WordPress get_terms() API.
     *
     * Delegates Polylang-specific adjustments (language bypass, translation expansion)
     * to PolylangTermHandler for multilingual compatibility.
     *
     * @param array  $slugs    Array of term slugs to resolve.
     * @param string $taxonomy Taxonomy name (e.g., 'product_cat', 'product_tag').
     * @return int[] Array of resolved term IDs.
     */
    private function resolveTermSlugsToIds(array $slugs, string $taxonomy): array {
        $args = [
            'taxonomy'   => $taxonomy,
            'slug'       => $slugs,
            'fields'     => 'ids',
            'hide_empty' => false,
        ];

        PolylangTermHandler::adjustTermQueryArgs($args);

        $terms = get_terms($args);

        if (is_wp_error($terms) || empty($terms)) {
            return [];
        }

        $term_ids = array_map('intval', $terms);

        return PolylangTermHandler::expandTermIdsWithTranslations($term_ids);
    }

    /**
     * Filters products by taxonomy term IDs.
     *
     * Uses term_id matching instead of slug matching, eliminating the need
     * for a wp_terms table JOIN and improving query performance.
     *
     * @param int[]  $term_ids Array of term IDs to filter by.
     * @param string $taxonomy Taxonomy name (e.g., 'product_cat', 'product_tag').
     * @return void
     */
    private function filterByTermIds(array $term_ids, string $taxonomy): void {
        global $wpdb;

        $placeholders = implode(',', array_fill(0, count($term_ids), '%d'));

        $sql = "SELECT DISTINCT p.ID FROM {$wpdb->posts} p INNER JOIN {$wpdb->term_relationships} tr ON p.ID = tr.object_id INNER JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE p.post_type = 'product' AND p.post_status IN ('publish', 'draft', 'private') AND tt.taxonomy = %s AND tt.term_id IN ($placeholders)";

        $args = array_merge([$taxonomy], $term_ids);

        $query = $wpdb->prepare($sql, ...$args);

        $product_ids = $wpdb->get_col($query);
        $this->restrictToProductIds($product_ids);
    }

    /**
     * Açıklama durumuna göre filtreler
     *
     * @param bool|null $hasDescription Tam açıklama var mı
     * @param bool|null $hasShortDescription Kısa açıklama var mı
     * @return ProductFilterBuilder
     */
    public function descriptions($hasDescription = null, $hasShortDescription = null) {
        if ($hasDescription === null && $hasShortDescription === null) {
            return $this;
        }

        global $wpdb;

        $conditions = ["p.post_type = 'product'", "p.post_status IN ('publish', 'draft', 'private')"];

        if ($hasDescription === true) {
            $conditions[] = "p.post_content IS NOT NULL AND TRIM(p.post_content) != ''";
        } elseif ($hasDescription === false) {
            $conditions[] = "(p.post_content IS NULL OR TRIM(p.post_content) = '')";
        }

        if ($hasShortDescription === true) {
            $conditions[] = "p.post_excerpt IS NOT NULL AND TRIM(p.post_excerpt) != ''";
        } elseif ($hasShortDescription === false) {
            $conditions[] = "(p.post_excerpt IS NULL OR TRIM(p.post_excerpt) = '')";
        }

        $where_clause = implode(' AND ', $conditions);

        $product_ids = $wpdb->get_col("SELECT ID FROM {$wpdb->posts} p WHERE {$where_clause}");

        $this->restrictToProductIds($product_ids);

        return $this;
    }

    /**
     * Etiket durumuna göre filtreler (var/yok)
     *
     * @param bool|null $hasTags Etiketleri var mı
     * @return ProductFilterBuilder
     */
    public function tags($hasTags = null) {
        if ($hasTags === null) {
            return $this;
        }

        global $wpdb;

        if ($hasTags) {

            $product_ids = $wpdb->get_col("
                SELECT DISTINCT p.ID
                FROM {$wpdb->posts} p
                INNER JOIN {$wpdb->term_relationships} tr ON p.ID = tr.object_id
                INNER JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
                WHERE p.post_type = 'product'
                AND tt.taxonomy = 'product_tag'
                AND p.post_status IN ('publish', 'draft', 'private')
            ");
        } else {
            $this->addNegativeFilterWherePart(
                'no_tags',
                "NOT EXISTS (
                    SELECT 1
                    FROM {$this->getTableName('term_relationships', 'term_relationships')} tr
                    INNER JOIN {$this->getTableName('term_taxonomy', 'term_taxonomy')} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
                    WHERE tr.object_id = p.ID
                    AND tt.taxonomy = 'product_tag'
                )"
            );

            return $this;
        }

        $this->restrictToProductIds($product_ids);

        return $this;
    }

    /**
     * Öne çıkan görsel (featured image) durumuna göre filtreler
     *
     * @param bool $hasImage Öne çıkan görseli var mı
     * @return ProductFilterBuilder
     */
    public function image($hasImage) {
        if ($hasImage === null) {
            return $this;
        }

        global $wpdb;

        if ($hasImage) {

            $product_ids = $wpdb->get_col("
                SELECT DISTINCT p.ID
                FROM {$wpdb->posts} p
                INNER JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id
                WHERE p.post_type = 'product'
                AND p.post_status IN ('publish', 'draft', 'private')
                AND pm.meta_key = '_thumbnail_id'
                AND pm.meta_value IS NOT NULL
                AND pm.meta_value != ''
                AND pm.meta_value != '0'
            ");
        } else {
            $this->addNegativeFilterWherePart(
                'no_image',
                "NOT EXISTS (
                    SELECT 1
                    FROM {$this->getTableName('postmeta', 'postmeta')} pm
                    WHERE pm.post_id = p.ID
                    AND pm.meta_key = '_thumbnail_id'
                    AND pm.meta_value IS NOT NULL
                    AND pm.meta_value != ''
                    AND pm.meta_value != '0'
                )"
            );

            return $this;
        }

        $this->restrictToProductIds($product_ids);

        return $this;
    }

    /**
     * Galeri görselleri durumuna göre filtreler
     *
     * @param bool $hasGallery Galeri görselleri var mı
     * @return ProductFilterBuilder
     */
    public function gallery($hasGallery) {
        if ($hasGallery === null) {
            return $this;
        }

        global $wpdb;

        if ($hasGallery) {

            $product_ids = $wpdb->get_col("
                SELECT DISTINCT p.ID
                FROM {$wpdb->posts} p
                INNER JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id
                WHERE p.post_type = 'product'
                AND p.post_status IN ('publish', 'draft', 'private')
                AND pm.meta_key = '_product_image_gallery'
                AND pm.meta_value IS NOT NULL
                AND TRIM(pm.meta_value) != ''
            ");
        } else {
            $this->addNegativeFilterWherePart(
                'no_gallery',
                "NOT EXISTS (
                    SELECT 1
                    FROM {$this->getTableName('postmeta', 'postmeta')} pm
                    WHERE pm.post_id = p.ID
                    AND pm.meta_key = '_product_image_gallery'
                    AND pm.meta_value IS NOT NULL
                    AND TRIM(pm.meta_value) != ''
                )"
            );

            return $this;
        }

        $this->restrictToProductIds($product_ids);

        return $this;
    }

    /**
     * SKU durumuna göre filtreler
     *
     * @param bool $hasSku SKU var mı
     * @return ProductFilterBuilder
     */
    public function sku($hasSku) {
        if ($hasSku === null) {
            return $this;
        }

        global $wpdb;

        if ($hasSku) {

            $product_ids = $wpdb->get_col("
                SELECT DISTINCT p.ID
                FROM {$wpdb->posts} p
                INNER JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id
                WHERE p.post_type = 'product'
                AND p.post_status IN ('publish', 'draft', 'private')
                AND pm.meta_key = '_sku'
                AND pm.meta_value IS NOT NULL
                AND TRIM(pm.meta_value) != ''
            ");
        } else {
            $this->addNegativeFilterWherePart(
                'no_sku',
                "NOT EXISTS (
                    SELECT 1
                    FROM {$this->getTableName('postmeta', 'postmeta')} pm
                    WHERE pm.post_id = p.ID
                    AND pm.meta_key = '_sku'
                    AND pm.meta_value IS NOT NULL
                    AND TRIM(pm.meta_value) != ''
                )"
            );

            return $this;
        }

        $this->restrictToProductIds($product_ids);

        return $this;
    }

    /**
     * Ürün tipine göre filtreler (simple, variable, grouped, external)
     *
     * @param string|array $types Ürün tipi veya tipleri
     * @return ProductFilterBuilder
     */
    public function productType($types) {
        if (empty($types)) {
            return $this;
        }

        global $wpdb;

        $types = is_array($types) ? $types : [$types];
        $types = array_map('sanitize_text_field', $types);

        $allowed_types = ['simple', 'variable', 'grouped', 'external'];
        $types = array_intersect($types, $allowed_types);

        if (empty($types)) {
            return $this;
        }

        $placeholders = implode(',', array_fill(0, count($types), '%s'));

        $sql = "SELECT DISTINCT p.ID 
                FROM {$wpdb->posts} p 
                INNER JOIN {$wpdb->term_relationships} tr ON p.ID = tr.object_id 
                INNER JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id 
                INNER JOIN {$wpdb->terms} t ON tt.term_id = t.term_id 
                WHERE p.post_type = 'product' 
                AND p.post_status IN ('publish', 'draft', 'private') 
                AND tt.taxonomy = 'product_type' 
                AND t.slug IN ($placeholders)";

        $query = $wpdb->prepare($sql, ...$types);

        $product_ids = $wpdb->get_col($query);
        $this->restrictToProductIds($product_ids);

        return $this;
    }

    /**
     * Öne çıkan (featured) durumuna göre filtreler
     *
     * @param bool $isFeatured Öne çıkan mı
     * @return ProductFilterBuilder
     */
    public function featured($isFeatured) {
        if ($isFeatured === null) {
            return $this;
        }

        global $wpdb;

        if ($isFeatured) {

            $product_ids = $wpdb->get_col("
                SELECT DISTINCT p.ID
                FROM {$wpdb->posts} p
                INNER JOIN {$wpdb->term_relationships} tr ON p.ID = tr.object_id
                INNER JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
                INNER JOIN {$wpdb->terms} t ON tt.term_id = t.term_id
                WHERE p.post_type = 'product'
                AND p.post_status IN ('publish', 'draft', 'private')
                AND tt.taxonomy = 'product_visibility'
                AND t.slug = 'featured'
            ");
        } else {
            $this->addNegativeFilterWherePart(
                'not_featured',
                "NOT EXISTS (
                    SELECT 1
                    FROM {$this->getTableName('term_relationships', 'term_relationships')} tr
                    INNER JOIN {$this->getTableName('term_taxonomy', 'term_taxonomy')} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
                    INNER JOIN {$this->getTableName('terms', 'terms')} t ON tt.term_id = t.term_id
                    WHERE tr.object_id = p.ID
                    AND tt.taxonomy = 'product_visibility'
                    AND t.slug = 'featured'
                )"
            );

            return $this;
        }

        $this->restrictToProductIds($product_ids);

        return $this;
    }

    /**
     * Filters by on-sale status.
     *
     * @param bool $isOnSale Whether to filter for on-sale products.
     * @return ProductFilterBuilder
     */
	public function onSale($isOnSale) {
		if ($isOnSale === null) {
			return $this;
		}

		if ($isOnSale) {
			$on_sale_function = 'wc_get_product_ids_on_sale';
			$product_ids = \function_exists($on_sale_function)
				? (array) \call_user_func($on_sale_function)
				: [];
			$this->restrictToProductIds($product_ids);
		} else {

			if ($this->isProductMetaLookupAvailable()) {
				$lookup = $this->getProductMetaLookupTableName();
				$this->addNegativeFilterWherePart(
					'not_on_sale',
					"NOT EXISTS (
						SELECT 1
						FROM {$lookup} pml_sale
						WHERE pml_sale.product_id = p.ID
						AND pml_sale.onsale = 1
					)"
				);

				return $this;
			}

			$this->addNegativeFilterWherePart(
				'not_on_sale',
				"NOT EXISTS (
					SELECT 1
					FROM {$this->getTableName('postmeta', 'postmeta')} pm
					WHERE pm.post_id = p.ID
					AND pm.meta_key = '_sale_price'
					AND pm.meta_value IS NOT NULL
					AND TRIM(pm.meta_value) != ''
					AND CAST(pm.meta_value AS DECIMAL(10,2)) > 0
				)"
			);

			return $this;
		}

        return $this;
    }

    /**
     * Sıralama parametrelerini ayarlar
     *
     * @param string $orderby Sıralama alanı (date, title, price, id, menu_order)
     * @param string $order Sıralama yönü (ASC veya DESC)
     * @return ProductFilterBuilder
     */
    public function orderBy($orderby, $order = 'DESC') {
        $order = strtoupper(sanitize_text_field($order));
        $this->args['order'] = in_array($order, ['ASC', 'DESC'], true) ? $order : 'DESC';

        $orderby = sanitize_text_field($orderby);

        if ($orderby === 'price') {
            $this->price_sort_direction = $this->args['order'];
            $this->args['orderby'] = 'date'; 
        } else {
            $this->price_sort_direction = null;
            $allowed_orderby = ['date', 'title', 'id', 'name', 'modified', 'menu_order'];
            $this->args['orderby'] = in_array($orderby, $allowed_orderby, true) ? $orderby : 'date';
        }

        return $this;
    }

    /**
     * Sets the filter logic mode (AND or OR)
     *
     * @param string $logic 'and' for intersection (default), 'or' for union
     * @return ProductFilterBuilder
     */
    public function filterLogic($logic) {
        $logic = strtolower(sanitize_text_field($logic));
        $this->filter_logic = in_array($logic, ['and', 'or'], true) ? $logic : 'and';
        return $this;
    }

    /**
     * Tüm parametreleri temizler ve varsayılan duruma döner
     *
     * @return ProductFilterBuilder
     */
    public function clear() {
        $this->args = [
            'limit' => 10,
            'page' => 1,
            'status' => ['publish', 'draft', 'private'],
            'orderby' => 'date',
            'order' => 'DESC',
            'return' => 'ids',
            'paginate' => true
        ];

        $this->tax_query = [];
        $this->filtered_product_ids = null;
        $this->price_sort_direction = null;
        $this->cursor_after = null;
        $this->cursor_limit = null;
        $this->filter_logic = 'and';
        $this->where_parts = [];
        $this->negative_filters = [];

        return $this;
    }

    /**
     * Oluşturulan wc_get_products() argümanlarını döndürür
     *
     * @return array wc_get_products() argümanları
     */
    public function getArgs() {
        $args = $this->args;

        $tax_queries = [];

        if (!empty($this->tax_query)) {
            foreach ($this->tax_query as $query) {
                $tax_queries[] = $query;
            }
        }

        if (function_exists('wc_get_product_visibility_term_ids')) {
            $product_visibility_terms = \wc_get_product_visibility_term_ids();

            if (!empty($product_visibility_terms['exclude-from-catalog'])) {
                $tax_queries[] = [
                    'taxonomy' => 'product_visibility',
                    'field'    => 'term_taxonomy_id',
                    'terms'    => [$product_visibility_terms['exclude-from-catalog']],
                    'operator' => 'NOT IN',
                ];
            }
        }

        if (!empty($tax_queries)) {
            $args['tax_query'] = array_merge(['relation' => 'AND'], $tax_queries);
        }

        if ($this->filtered_product_ids !== null) {
            $filtered_ids = $this->filtered_product_ids;

            if ($this->price_sort_direction !== null && !empty($filtered_ids)) {
                $filtered_ids = $this->sortProductIdsByPrice($filtered_ids, $this->price_sort_direction);

                $args['orderby'] = 'post__in';
                unset($args['order']); 
            }

            $args['include'] = !empty($filtered_ids) ? $filtered_ids : [0];
        } elseif ($this->price_sort_direction !== null) {

            $direction = strtoupper($this->price_sort_direction) === 'DESC' ? 'DESC' : 'ASC';
            

            $args['orderby']  = 'meta_value_num';
            $args['meta_key'] = '_price';
            $args['order']    = $direction;
            

            $price_meta_query = [
                [
                    'key'     => '_price',
                    'compare' => 'EXISTS',
                ],
            ];
            

            if ( isset( $args['meta_query'] ) && is_array( $args['meta_query'] ) ) {
                $args['meta_query'] = array_merge( $args['meta_query'], $price_meta_query );
            } else {
                $args['meta_query'] = $price_meta_query;
            }
        }

        if ($this->cursor_after !== null) {

            if ($this->filtered_product_ids !== null) {

                sort($this->filtered_product_ids, SORT_NUMERIC);
                

                $this->filtered_product_ids = array_filter(
                    $this->filtered_product_ids,
                    fn($id) => $id > $this->cursor_after
                );
                $args['include'] = !empty($this->filtered_product_ids) 
                    ? array_slice($this->filtered_product_ids, 0, $this->cursor_limit ?? 50) 
                    : [0];
            } else {

                global $wpdb;

                $product_ids = $wpdb->get_col($wpdb->prepare(
                    "SELECT ID FROM {$wpdb->posts} 
                     WHERE post_type = 'product' 
                     AND post_status IN ('publish', 'draft', 'private')
                     AND ID > %d 
                     ORDER BY ID ASC 
                     LIMIT %d",
                    $this->cursor_after,
                    $this->cursor_limit ?? 50
                ));
                $args['include'] = !empty($product_ids) ? array_map('intval', $product_ids) : [0];
            }

            $args['orderby'] = 'ID';
            $args['order'] = 'ASC';
        }

        return $args;
    }

    /**
     * Determines whether filters require the optimized direct SQL path.
     *
     * @return bool
     */
    private function shouldUseCustomSqlQuery(): bool {
        return !empty($this->where_parts)
            || ($this->price_sort_direction !== null && $this->isProductMetaLookupAvailable());
    }

    /**
     * Loads filtered products through a direct SQL query with paginated IDs.
     *
     * @return array|null Product result or null when the legacy wc_get_products() path should be used.
     */
    private function getProductsWithCustomSql(): ?array {
        global $wpdb;

        try {
            $limit  = max(1, absint($this->args['limit'] ?? 10));
            $page   = max(1, absint($this->args['page'] ?? 1));
            $offset = ($page - 1) * $limit;

            if ($this->cursor_limit !== null) {
                $limit  = max(1, absint($this->cursor_limit));
                $offset = 0;
            }

            $ids_query   = $this->buildFilteredProductIdsQuery($limit, $offset);
            $count_query = $this->buildFilteredProductCountQuery();

            $product_ids = $wpdb->get_col($ids_query);

            if (!empty($wpdb->last_error)) {
                throw new \RuntimeException($wpdb->last_error);
            }

            $total = (int) $wpdb->get_var($count_query);

            if (!empty($wpdb->last_error)) {
                throw new \RuntimeException($wpdb->last_error);
            }

            $product_ids = array_map('intval', $product_ids ?: []);
            $this->primeProductCaches($product_ids);

            return [
                'products' => $this->formatProductsForResponse($product_ids),
                'total' => $total,
                'total_pages' => $limit > 0 ? (int) ceil($total / $limit) : 0,
            ];
        } catch (\Throwable $e) {
            $this->logFallbackError('Custom product filter SQL failed: ' . $e->getMessage());
            return null;
        }
    }

    /**
     * Builds the paginated product ID SQL used by the optimized query path.
     *
     * @param int $limit  Page size.
     * @param int $offset SQL offset.
     * @return string SQL query.
     */
    private function buildFilteredProductIdsQuery($limit = 0, $offset = 0): string {
        $limit  = max(0, absint($limit));
        $offset = max(0, absint($offset));

        $join_parts = [];
        $order_clause = $this->buildOrderClause($join_parts);

        $sql = $this->buildFilteredProductBaseQuery('SELECT DISTINCT p.ID', $join_parts);
        $sql .= $order_clause;

        if ($limit > 0) {
            global $wpdb;
            $sql .= $wpdb->prepare(' LIMIT %d OFFSET %d', $limit, $offset);
        }

        return $sql;
    }

    /**
     * Builds the count SQL used by the optimized query path.
     *
     * @return string SQL query.
     */
    private function buildFilteredProductCountQuery(): string {
        return $this->buildFilteredProductBaseQuery('SELECT COUNT(DISTINCT p.ID)');
    }

    /**
     * Builds the shared SELECT/FROM/WHERE portion for optimized product filtering.
     *
     * @param string $select_clause SELECT expression.
     * @param array  $join_parts    SQL JOIN clauses.
     * @return string SQL query without ORDER/LIMIT.
     */
    private function buildFilteredProductBaseQuery(string $select_clause, array $join_parts = []): string {
        $posts = $this->getTableName('posts', 'posts');

        $where_parts = $this->buildBaseWhereParts();

        foreach ($this->where_parts as $where_part) {
            $where_parts[] = '(' . $where_part . ')';
        }

        $filtered_ids_where = $this->buildFilteredProductIdsWherePart();
        if ($filtered_ids_where !== '') {
            $where_parts[] = $filtered_ids_where;
        }

        if ($this->cursor_after !== null) {
            global $wpdb;
            $where_parts[] = $wpdb->prepare('p.ID > %d', absint($this->cursor_after));
        }

        $joins = !empty($join_parts) ? ' ' . implode(' ', $join_parts) : '';

        return $select_clause . " FROM {$posts} p{$joins} WHERE " . implode(' AND ', $where_parts);
    }

    /**
     * Builds base product type/status conditions for direct SQL filtering.
     *
     * @return array SQL WHERE clauses.
     */
    private function buildBaseWhereParts(): array {
        return [
            "p.post_type = 'product'",
            $this->buildStatusWherePart(),
        ];
    }

    /**
     * Builds the post_status SQL condition from the current builder args.
     *
     * @return string SQL WHERE clause.
     */
    private function buildStatusWherePart(): string {
        global $wpdb;

        $statuses = $this->args['status'] ?? ['publish', 'draft', 'private'];
        $statuses = is_array($statuses) ? $statuses : [$statuses];
        $statuses = array_values(array_filter(array_map('sanitize_text_field', $statuses)));

        if (empty($statuses)) {
            $statuses = ['publish', 'draft', 'private'];
        }

        if (count($statuses) === 1) {
            return $wpdb->prepare('p.post_status = %s', $statuses[0]);
        }

        $placeholders = implode(',', array_fill(0, count($statuses), '%s'));

        return $wpdb->prepare("p.post_status IN ({$placeholders})", ...$statuses);
    }

    /**
     * Builds a safe ID restriction for filters that still use the legacy ID list path.
     *
     * @return string SQL WHERE clause or empty string.
     */
    private function buildFilteredProductIdsWherePart(): string {
        if ($this->filtered_product_ids === null) {
            return '';
        }

        $filtered_ids = array_values(array_unique(array_map('intval', $this->filtered_product_ids)));

        if (empty($filtered_ids)) {
            return '1 = 0';
        }

        return 'p.ID IN (' . implode(',', $filtered_ids) . ')';
    }

    /**
     * Builds the ORDER BY clause for optimized product filtering.
     *
     * @return string SQL ORDER BY clause.
     */
    private function buildOrderClause(array &$join_parts = []): string {
        if ($this->price_sort_direction !== null && $this->isProductMetaLookupAvailable()) {
            $direction = strtoupper($this->price_sort_direction) === 'DESC' ? 'DESC' : 'ASC';
            $price_column = $direction === 'DESC' ? 'pml.max_price' : 'pml.min_price';
            $join_parts[] = "LEFT JOIN {$this->getProductMetaLookupTableName()} pml ON pml.product_id = p.ID";

            return " ORDER BY CASE WHEN {$price_column} IS NULL THEN 1 ELSE 0 END, {$price_column} {$direction}, p.ID {$direction}";
        }

        if ($this->price_sort_direction !== null) {
            $direction = strtoupper($this->price_sort_direction) === 'DESC' ? 'DESC' : 'ASC';
            $join_parts[] = "LEFT JOIN {$this->getTableName('postmeta', 'postmeta')} pm_price ON p.ID = pm_price.post_id AND pm_price.meta_key = '_price'";

            return " ORDER BY CASE WHEN pm_price.meta_value IS NULL OR pm_price.meta_value = '' THEN 1 ELSE 0 END, CAST(pm_price.meta_value AS DECIMAL(10,2)) {$direction}, p.ID {$direction}";
        }

        $direction = strtoupper($this->args['order'] ?? 'DESC') === 'ASC' ? 'ASC' : 'DESC';
        $orderby   = $this->args['orderby'] ?? 'date';

        $columns = [
            'title'      => 'p.post_title',
            'name'       => 'p.post_title',
            'id'         => 'p.ID',
            'ID'         => 'p.ID',
            'modified'   => 'p.post_modified',
            'menu_order' => 'p.menu_order',
            'date'       => 'p.post_date',
        ];

        $column = $columns[$orderby] ?? 'p.post_date';

        return " ORDER BY {$column} {$direction}, p.ID {$direction}";
    }

    /**
     * Adds an optimized NOT EXISTS filter clause.
     *
     * @param string $filter_key Filter identifier for fallback diagnostics.
     * @param string $where_part SQL WHERE clause.
     * @return void
     */
    private function addNegativeFilterWherePart(string $filter_key, string $where_part): void {
        $this->where_parts[] = trim($where_part);
        $this->negative_filters[$filter_key] = true;
    }

    /**
     * Checks whether WooCommerce's product meta lookup table can be used safely.
     *
     * @return bool True when the lookup table is available.
     */
    private function isProductMetaLookupAvailable(): bool {
        if (self::$product_meta_lookup_exists !== null) {
            return self::$product_meta_lookup_exists;
        }

        if (defined('WC_VERSION') && version_compare((string) constant('WC_VERSION'), '3.6.0', '<')) {
            self::$product_meta_lookup_exists = false;
            return false;
        }

        global $wpdb;

        try {
            $table = $this->getProductMetaLookupTableName();

            if (!method_exists($wpdb, 'get_var') || !method_exists($wpdb, 'prepare')) {
                self::$product_meta_lookup_exists = false;
                return false;
            }

            $found_table = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table));

            self::$product_meta_lookup_exists = $found_table === $table;
        } catch (\Throwable $e) {
            $this->logFallbackError('wc_product_meta_lookup detection failed: ' . $e->getMessage());
            self::$product_meta_lookup_exists = false;
        }

        return self::$product_meta_lookup_exists;
    }

    /**
     * Gets the WooCommerce product meta lookup table name.
     *
     * @return string Table name.
     */
    private function getProductMetaLookupTableName(): string {
        return $this->getTableName('wc_product_meta_lookup', 'wc_product_meta_lookup');
    }

    /**
     * Gets a wpdb table name with a prefix fallback for lightweight test spies.
     *
     * @param string $property wpdb table property.
     * @param string $fallback_suffix Table suffix without prefix.
     * @return string Table name.
     */
    private function getTableName(string $property, string $fallback_suffix): string {
        global $wpdb;

        if (isset($wpdb->{$property}) && is_string($wpdb->{$property}) && $wpdb->{$property} !== '') {
            return $wpdb->{$property};
        }

        $prefix = isset($wpdb->prefix) && is_string($wpdb->prefix) ? $wpdb->prefix : '';

        return $prefix . $fallback_suffix;
    }

    /**
     * Sorts product IDs by price at the database level
     *
     * This method is called when price sorting is active to ensure correct
     * sorting across all pages, not just the current page.
     *
     * @param array  $product_ids Product IDs to sort
     * @param string $direction   Sort direction (ASC or DESC)
     * @return array Sorted product IDs
     */
	private function sortProductIdsByPrice($product_ids, $direction = 'ASC') {
		if (empty($product_ids)) {
			return [];
		}

		$direction = strtoupper($direction) === 'DESC' ? 'DESC' : 'ASC';
		$product_ids = array_values(array_unique(array_map('intval', $product_ids)));

		if ($this->isProductMetaLookupAvailable()) {
			$sorted_ids = $this->sortProductIdsByPriceLookup($product_ids, $direction);

			if (!empty($sorted_ids)) {
				return $sorted_ids;
			}

			$this->logFallbackError('wc_product_meta_lookup returned no price rows; falling back to _price postmeta sort.');
		}

		return $this->sortProductIdsByPricePostmeta($product_ids, $direction);
    }

    /**
     * Sorts product IDs through WooCommerce's product meta lookup table.
     *
     * @param array  $product_ids Product IDs to sort.
     * @param string $direction   Sort direction.
     * @return array Sorted product IDs.
     */
    private function sortProductIdsByPriceLookup(array $product_ids, string $direction): array {
        global $wpdb;

        $placeholders = implode(',', array_fill(0, count($product_ids), '%d'));
        $price_column = $direction === 'DESC' ? 'pml.max_price' : 'pml.min_price';
        $lookup_table = $this->getProductMetaLookupTableName();

        try {

            $query = $wpdb->prepare(
                "SELECT p.ID
                FROM {$this->getTableName('posts', 'posts')} p
                INNER JOIN {$lookup_table} pml ON pml.product_id = p.ID
                WHERE p.ID IN ({$placeholders})
                ORDER BY
                    CASE WHEN {$price_column} IS NULL THEN 1 ELSE 0 END,
                    {$price_column} {$direction},
                    p.ID {$direction}",
                ...$product_ids
            );

            $sorted_ids = $wpdb->get_col($query);

            if (!empty($wpdb->last_error)) {
                throw new \RuntimeException($wpdb->last_error);
            }

            return array_map('intval', $sorted_ids ?: []);
        } catch (\Throwable $e) {
            $this->logFallbackError('wc_product_meta_lookup price sort failed: ' . $e->getMessage());
            return [];
        }
    }

    /**
     * Legacy _price postmeta price sorting fallback.
     *
     * @param array  $product_ids Product IDs to sort.
     * @param string $direction   Sort direction.
     * @return array Sorted product IDs.
     */
    private function sortProductIdsByPricePostmeta(array $product_ids, string $direction): array {
        global $wpdb;

        $placeholders = implode(',', array_fill(0, count($product_ids), '%d'));

        $query = $wpdb->prepare("
            SELECT p.ID
            FROM {$this->getTableName('posts', 'posts')} p
            LEFT JOIN {$this->getTableName('postmeta', 'postmeta')} pm ON p.ID = pm.post_id AND pm.meta_key = '_price'
            WHERE p.ID IN ({$placeholders})
            ORDER BY
                CASE WHEN pm.meta_value IS NULL OR pm.meta_value = '' THEN 1 ELSE 0 END,
                CAST(pm.meta_value AS DECIMAL(10,2)) {$direction}
        ", ...$product_ids);

        $sorted_ids = $wpdb->get_col($query);

        return array_map('intval', $sorted_ids ?: []);
    }

	/**
	 * Restrict to specific product IDs with filter logic support.
	 *
	 * @param array|null $product_ids New filtered product IDs to combine.
	 * @return self Fluent interface.
	 */
	private function restrictToProductIds( $product_ids ) {
		if ( $product_ids === null ) {
			return $this;
		}

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

		if ( $this->filtered_product_ids === null ) {

			$this->filtered_product_ids = $product_ids;
		} elseif ( $this->filter_logic === 'or' ) {

			$this->filtered_product_ids = array_values( array_unique( array_merge( $this->filtered_product_ids, $product_ids ) ) );
		} else {

			$this->filtered_product_ids = array_values( array_intersect( $this->filtered_product_ids, $product_ids ) );
		}

		return $this;
	}

    /**
     * Filtreleri uygular ve ürünleri getirir
     *
     * @return array Ürünler, toplam ürün sayısı ve toplam sayfa sayısı
     */
    public function getProducts() {
        try {
            $args = $this->getArgs();

            $user_id = get_current_user_id();

            $cache_key = 'aipt_products_' . $user_id . '_' . md5(
                wp_json_encode($args)
                . ($this->price_sort_direction ?? '')
                . wp_json_encode($this->where_parts)
            );
            $cached_result = wp_cache_get($cache_key, 'aipt_products');

            if ($cached_result !== false) {
                return $cached_result;
            }

            if (isset($args['include']) && $args['include'] === [0]) {
                $empty_result = [
                    'products' => [],
                    'total' => 0,
                    'total_pages' => 0,
                    'message' => 'No products found matching the selected filters.'
                ];
                wp_cache_set($cache_key, $empty_result, 'aipt_products', 300);
                return $empty_result;
            }

            if ($this->shouldUseCustomSqlQuery()) {
                $custom_result = $this->getProductsWithCustomSql();

                if ($custom_result !== null) {
                    wp_cache_set($cache_key, $custom_result, 'aipt_products', 300);
                    return $custom_result;
                }

                error_log(
                    '[AIPT] ProductFilterBuilder: custom SQL path returned null with '
                    . count($this->where_parts)
                    . ' negative-filter clause(s) pending — falling back to wc_get_products() may include rows that should be excluded.'
                );
            }

            $results = \wc_get_products($args);
            $products = [];

            if ($results && isset($results->products)) {
                $product_ids_for_page = array_map('intval', $results->products);

                $this->primeProductCaches($product_ids_for_page);
                $products = $this->formatProductsForResponse($product_ids_for_page);
            }

            $total = isset($results->total) ? (int) $results->total : 0;
            $max_num_pages = isset($results->max_num_pages) ? (int) $results->max_num_pages : 0;

            $result = [
                'products' => $products,
                'total' => $total,
                'total_pages' => $max_num_pages
            ];

            wp_cache_set($cache_key, $result, 'aipt_products', 300);

            return $result;

        } catch (\Exception $e) {
            if (defined('WP_DEBUG') && WP_DEBUG) {
            }

            return [
                'products' => [],
                'total' => 0,
                'total_pages' => 0,
                'error' => 'An error occurred while searching products. Please try again or contact support if the issue persists.'
            ];
        }
    }

    /**
     * Primes post, meta, taxonomy, and thumbnail-related caches for a product page.
     *
     * @param array $product_ids Product IDs for the current page.
     * @return void
     */
    private function primeProductCaches(array $product_ids): void {
        $product_ids = array_values(array_filter(array_map('intval', $product_ids)));

        if (empty($product_ids)) {
            return;
        }

        \update_meta_cache('post', $product_ids);

        $term_cache_function = 'update_object_term_cache';
        if (\function_exists($term_cache_function)) {
            \call_user_func($term_cache_function, $product_ids, 'product');
        }

        $post_cache_function = '_prime_post_caches';
        if (\function_exists($post_cache_function)) {
            \call_user_func($post_cache_function, $product_ids, true, true);
        }
    }

    /**
     * Formats product IDs into the response shape expected by the admin UI.
     *
     * @param array $product_ids Product IDs.
     * @return array Formatted products.
     */
    private function formatProductsForResponse(array $product_ids): array {
        $products = [];

        foreach ($product_ids as $product_id) {
            $product = \wc_get_product($product_id);

            if (!$product) {
                continue;
            }

            $categories = \wc_get_product_terms($product_id, 'product_cat', ['fields' => 'names']);
            $categories = \is_wp_error($categories) ? [] : $categories;
            $decoded_categories = array_map([self::class, 'decodeHtmlEntities'], $categories);

            $tags = \wc_get_product_terms($product_id, 'product_tag', ['fields' => 'names']);
            $tags = \is_wp_error($tags) ? [] : $tags;
            $decoded_tags = array_map([self::class, 'decodeHtmlEntities'], $tags);

            $description = (string) $product->get_description();
            $short_description = (string) $product->get_short_description();
            $has_description = !empty(trim($description));
            $has_short_description = !empty(trim($short_description));

            $image_id = $product->get_image_id();
            $image_url = $image_id ? \wp_get_attachment_image_url($image_id, 'thumbnail') : \wc_placeholder_img_src('thumbnail');

            $price = $product->get_price();
            $numeric_price = $price !== '' ? (float) $price : 0;
            $date_created = $product->get_date_created();
            $date_modified = $product->get_date_modified();

            $products[] = [
                'id' => $product_id,
                'name' => $product->get_name(),
                'slug' => (string) \get_post_field('post_name', $product_id),
                'status' => $product->get_status(),
                'stock_status' => $product->get_stock_status(),
                'categories' => $decoded_categories,
                'tags' => $decoded_tags,
                'hasDescription' => $has_description,
                'hasShortDescription' => $has_short_description,
                'description' => $description,
                'shortDescription' => $short_description,
                'image' => $image_url,
                'dateCreated' => $date_created ? $date_created->date('Y-m-d H:i:s') : '',
                'dateModified' => $date_modified ? $date_modified->date('Y-m-d H:i:s') : '',
                'price' => $price,
                'numeric_price' => $numeric_price,
                'sku' => $product->get_sku()
            ];
        }

        return $products;
    }

    /**
     * Logs fallback-only errors without interrupting production requests.
     *
     * @param string $message Error message.
     * @return void
     */
    private function logFallbackError(string $message): void {
        if (defined('WP_DEBUG') && WP_DEBUG) {
        }
    }

    /**
     * HTML entities decode helper
     *
     * @param string $string
     * @return string
     */
    private static function decodeHtmlEntities($string) {
        return html_entity_decode($string, ENT_QUOTES, 'UTF-8');
    }
}
