# -*- coding: utf-8 -*-
"""
Spatialite Backend for FilterMate

Backend for Spatialite databases.
Uses Spatialite spatial functions which are largely compatible with PostGIS.

v2.4.0 Improvements:
- WKT caching for repeated filter operations
- Improved CRS handling

v2.4.14 Improvements:
- Direct mod_spatialite loading for GeoPackage (bypasses GDAL limitations)
- Fallback to FID-based filtering when setSubsetString doesn't support Spatialite SQL
- Improved GeoPackage spatial query performance

v2.4.20 Improvements:
- PRIORITY DIRECT SQL for GeoPackage (more reliable than native mode)
- Cache invalidation to force retesting with direct SQL mode

v2.4.21 Improvements:
- CRITICAL FIX: Remote/distant layers detection before Spatialite testing
- Prevents "unable to open database file" errors for WFS/HTTP/service layers
- File existence verification before SQLite connection attempts
"""

from typing import Dict, Optional, Tuple, List
import sqlite3
import time
import re
import os
from qgis.core import QgsVectorLayer, QgsDataSourceUri
from .base_backend import GeometricFilterBackend
from ..logging_config import get_tasks_logger
from ..constants import PROVIDER_SPATIALITE
from ..appUtils import safe_set_subset_string

logger = get_tasks_logger()

# v2.4.21: Force cache clear on module reload to apply remote detection fix
# This ensures that the new logic is used instead of cached results
_CACHE_VERSION = "2.4.21"  # Increment this to force cache invalidation

# Import WKT Cache for performance optimization (v2.4.0)
try:
    from .wkt_cache import get_wkt_cache, WKTCache
    WKT_CACHE_AVAILABLE = True
except ImportError:
    WKT_CACHE_AVAILABLE = False
    get_wkt_cache = None
    WKTCache = None


# Cache for mod_spatialite availability (tested once per session)
_MOD_SPATIALITE_AVAILABLE: Optional[bool] = None
_MOD_SPATIALITE_EXTENSION_NAME: Optional[str] = None


def _test_mod_spatialite_available() -> Tuple[bool, Optional[str]]:
    """
    Test if mod_spatialite extension can be loaded directly via sqlite3.
    
    This is different from testing via GDAL/OGR - even if GDAL's GeoPackage
    driver doesn't support Spatialite SQL in setSubsetString, we may still
    be able to load mod_spatialite directly for SQL queries.
    
    Returns:
        Tuple of (available: bool, extension_name: str or None)
    """
    global _MOD_SPATIALITE_AVAILABLE, _MOD_SPATIALITE_EXTENSION_NAME
    
    if _MOD_SPATIALITE_AVAILABLE is not None:
        return (_MOD_SPATIALITE_AVAILABLE, _MOD_SPATIALITE_EXTENSION_NAME)
    
    # Test extensions in order of preference
    extension_names = ['mod_spatialite', 'mod_spatialite.dll', 'libspatialite.so']
    
    for ext_name in extension_names:
        try:
            conn = sqlite3.connect(':memory:')
            conn.enable_load_extension(True)
            conn.load_extension(ext_name)
            
            # Verify spatial functions work
            cursor = conn.cursor()
            cursor.execute("SELECT ST_GeomFromText('POINT(0 0)', 4326) IS NOT NULL")
            result = cursor.fetchone()
            conn.close()
            
            if result and result[0]:
                logger.info(f"✓ mod_spatialite available via extension: {ext_name}")
                _MOD_SPATIALITE_AVAILABLE = True
                _MOD_SPATIALITE_EXTENSION_NAME = ext_name
                return (True, ext_name)
                
        except Exception as e:
            logger.debug(f"mod_spatialite extension '{ext_name}' not available: {e}")
            continue
    
    logger.warning("mod_spatialite extension not available - direct SQL queries not possible")
    _MOD_SPATIALITE_AVAILABLE = False
    _MOD_SPATIALITE_EXTENSION_NAME = None
    return (False, None)


class SpatialiteGeometricFilter(GeometricFilterBackend):
    """
    Spatialite backend for geometric filtering.
    
    This backend provides filtering for Spatialite layers using:
    - Spatialite spatial functions (similar to PostGIS)
    - SQL-based filtering
    - Good performance for small to medium datasets
    
    v2.4.0: Added WKT caching for repeated filter operations
    v2.4.1: Improved GeoPackage detection with file-level caching
    v2.4.12: Added thread-safe cache access with lock
    v2.4.14: Added direct SQL mode for GeoPackage when setSubsetString doesn't support Spatialite
    v2.4.20: Priority direct SQL mode for GeoPackage - more reliable than native setSubsetString
    """
    
    # Class-level caches for Spatialite support testing
    _spatialite_support_cache: Dict[str, bool] = {}  # layer_id -> supports
    _spatialite_file_cache: Dict[str, bool] = {}  # file_path -> supports
    
    # Cache for direct SQL mode (GeoPackage with mod_spatialite but without setSubsetString support)
    # layer_id -> True means use direct SQL mode (query FIDs via mod_spatialite, then simple IN filter)
    _direct_sql_mode_cache: Dict[str, bool] = {}
    
    # v2.4.20: Cache version tracking for automatic invalidation on upgrade
    _cache_version: str = ""
    
    # Thread lock for cache access (thread-safety for large GeoPackage with 50+ layers)
    import threading
    _cache_lock = threading.RLock()
    
    @classmethod
    def clear_support_cache(cls):
        """
        Clear the Spatialite support test cache.
        
        Call this when reloading layers or when support status may have changed.
        """
        with cls._cache_lock:
            cls._spatialite_support_cache.clear()
            cls._spatialite_file_cache.clear()
            cls._direct_sql_mode_cache.clear()
        logger.debug("Spatialite support cache cleared")
    
    @classmethod
    def invalidate_layer_cache(cls, layer_id: str):
        """
        Invalidate the cache for a specific layer.
        
        Args:
            layer_id: ID of the layer to invalidate
        """
        with cls._cache_lock:
            if layer_id in cls._spatialite_support_cache:
                del cls._spatialite_support_cache[layer_id]
            if layer_id in cls._direct_sql_mode_cache:
                del cls._direct_sql_mode_cache[layer_id]
                logger.debug(f"Spatialite cache invalidated for layer {layer_id}")
    
    def __init__(self, task_params: Dict):
        """
        Initialize Spatialite backend.
        
        Args:
            task_params: Task parameters dictionary
        """
        super().__init__(task_params)
        self.logger = logger
        self._temp_table_name = None
        self._temp_table_conn = None
        # CRITICAL FIX: Temp tables don't work with setSubsetString!
        # QGIS uses its own connection to evaluate subset strings,
        # and SQLite TEMP tables are connection-specific.
        # When we create a TEMP table in our connection, QGIS cannot see it.
        # Solution: Always use inline WKT in GeomFromText() for subset strings.
        self._use_temp_table = False  # DISABLED: doesn't work with setSubsetString
        
        # WKT cache reference (v2.4.0)
        self._wkt_cache = get_wkt_cache() if WKT_CACHE_AVAILABLE else None
        
        # v2.4.20: Auto-clear cache if version changed (ensures new direct SQL logic is used)
        with self.__class__._cache_lock:
            if self.__class__._cache_version != _CACHE_VERSION:
                logger.info(f"🔄 Cache version changed ({self.__class__._cache_version} → {_CACHE_VERSION}), clearing Spatialite support cache")
                self.__class__._spatialite_support_cache.clear()
                self.__class__._spatialite_file_cache.clear()
                self.__class__._direct_sql_mode_cache.clear()
                self.__class__._cache_version = _CACHE_VERSION

    def _get_buffer_endcap_style(self) -> str:
        """
        Get the Spatialite buffer endcap style from task_params.
        
        Spatialite ST_Buffer supports endcap parameter (same as PostGIS):
        - 'round' (default)
        - 'flat' 
        - 'square'
        
        Returns:
            Spatialite endcap style string
        """
        if not self.task_params:
            return 'round'
        
        filtering_params = self.task_params.get("filtering", {})
        if not filtering_params.get("has_buffer_type", False):
            return 'round'
        
        buffer_type_str = filtering_params.get("buffer_type", "Round")
        
        # Map FilterMate buffer types to Spatialite endcap styles
        buffer_type_mapping = {
            "Round": "round",
            "Flat": "flat", 
            "Square": "square"
        }
        
        endcap_style = buffer_type_mapping.get(buffer_type_str, "round")
        self.log_debug(f"Using buffer endcap style: {endcap_style}")
        return endcap_style
    
    def _build_st_buffer_with_style(self, geom_expr: str, buffer_value: float) -> str:
        """
        Build ST_Buffer expression with endcap style from task_params.
        
        Supports both positive buffers (expansion) and negative buffers (erosion/shrinking).
        Negative buffers only work on polygon geometries - they shrink the polygon inward.
        
        Args:
            geom_expr: Geometry expression to buffer
            buffer_value: Buffer distance (positive=expand, negative=shrink/erode)
            
        Returns:
            Spatialite ST_Buffer expression with style parameter
            
        Note:
            - Negative buffer on a polygon shrinks it inward
            - Negative buffer on a point or line returns empty geometry
            - Very large negative buffers may collapse the polygon entirely
            - Negative buffers are wrapped in MakeValid() to prevent invalid geometries
            - Returns NULL if buffer produces empty geometry (v2.4.23 fix for negative buffers)
        """
        endcap_style = self._get_buffer_endcap_style()
        
        # Log negative buffer usage for visibility
        if buffer_value < 0:
            self.log_info(f"📐 Using negative buffer (erosion): {buffer_value}m")
        
        # Build base buffer expression
        if endcap_style == 'round':
            # Default style - no need to specify
            buffer_expr = f"ST_Buffer({geom_expr}, {buffer_value})"
        else:
            # Spatialite uses same syntax as PostGIS for endcap
            buffer_expr = f"ST_Buffer({geom_expr}, {buffer_value}, 'endcap={endcap_style}')"
        
        # CRITICAL FIX v2.3.9: Wrap negative buffers in MakeValid()
        # CRITICAL FIX v2.4.23: Use ST_IsEmpty() to detect ALL empty geometry types
        # CRITICAL FIX v2.5.5: Fixed bug where NULLIF only detected GEOMETRYCOLLECTION EMPTY
        #                      but not POLYGON EMPTY, MULTIPOLYGON EMPTY, etc.
        # Negative buffers (erosion/shrinking) can produce invalid or empty geometries,
        # especially on complex polygons or when buffer is too large.
        # MakeValid() ensures the result is always geometrically valid.
        # ST_IsEmpty() detects ALL empty geometry types (POLYGON EMPTY, MULTIPOLYGON EMPTY, etc.)
        # Note: Spatialite uses MakeValid() instead of ST_MakeValid()
        if buffer_value < 0:
            self.log_info(f"  🛡️ Wrapping negative buffer in MakeValid() + ST_IsEmpty check for empty geometry handling")
            # Use CASE WHEN to return NULL if buffer produces empty geometry
            # This ensures empty results from negative buffers don't match spatial predicates
            validated_expr = f"MakeValid({buffer_expr})"
            return f"CASE WHEN ST_IsEmpty({validated_expr}) = 1 THEN NULL ELSE {validated_expr} END"
        else:
            return buffer_expr
    
    def supports_layer(self, layer: QgsVectorLayer) -> bool:
        """
        Check if this backend supports the given layer.
        
        Supports:
        - Native Spatialite layers (providerType == 'spatialite')
        - GeoPackage files via OGR IF Spatialite functions are available
        - SQLite files via OGR IF Spatialite functions are available
        - GeoPackage/SQLite via DIRECT SQL mode if mod_spatialite is available
          (even when GDAL's OGR driver doesn't support Spatialite in setSubsetString)
        
        CRITICAL: GeoPackage/SQLite support depends on GDAL being compiled with Spatialite.
        This method now tests if spatial functions actually work before returning True.
        
        v2.4.14: Added direct SQL mode fallback for GeoPackage when setSubsetString
        doesn't support Spatialite but mod_spatialite is available.
        
        Args:
            layer: QGIS vector layer to check
        
        Returns:
            True if layer supports Spatialite spatial functions
        """
        provider_type = layer.providerType()
        layer_id = layer.id()
        
        # Native Spatialite provider - fully supported
        if provider_type == PROVIDER_SPATIALITE:
            self.log_debug(f"✓ Native Spatialite layer: {layer.name()}")
            return True
        
        # GeoPackage/SQLite via OGR - need to test if Spatialite functions work
        if provider_type == 'ogr':
            source = layer.source()
            source_path = source.split('|')[0] if '|' in source else source
            
            # v2.4.21: CRITICAL FIX - Detect remote/distant sources before testing
            # Remote sources should NOT use Spatialite backend - use OGR fallback instead
            source_lower = source_path.lower().strip()
            
            # Check for remote URLs (http, https, ftp, etc.)
            remote_prefixes = ('http://', 'https://', 'ftp://', 'wfs:', 'wms:', 'wcs://', '/vsicurl/')
            if any(source_lower.startswith(prefix) for prefix in remote_prefixes):
                self.log_info(f"⚠️ Remote source detected for {layer.name()} - Spatialite NOT supported")
                self.log_debug(f"   → Source: {source_path[:100]}...")
                return False
            
            # Check for service markers in source string (WFS, OAPIF, etc.)
            service_markers = ['url=', 'service=', 'srsname=', 'typename=', 'version=']
            if any(marker in source_lower for marker in service_markers):
                self.log_info(f"⚠️ Service source detected for {layer.name()} - Spatialite NOT supported")
                self.log_debug(f"   → Source contains service markers")
                return False
            
            # v2.4.21: Verify file exists before testing Spatialite support
            # This prevents "unable to open database file" errors for non-existent paths
            if source_path.lower().endswith('.gpkg') or source_path.lower().endswith('.sqlite'):
                file_type = "GeoPackage" if source_path.lower().endswith('.gpkg') else "SQLite"
                
                # Check if file exists locally
                if not os.path.isfile(source_path):
                    self.log_info(f"⚠️ {file_type} file not found for {layer.name()} - Spatialite NOT supported")
                    self.log_debug(f"   → Path: {source_path}")
                    self.log_debug(f"   → This may be a remote or virtual source")
                    return False
                
                self.log_info(f"🔍 Testing Spatialite support for {file_type} layer: {layer.name()}")
                
                # Check cache first for this layer - only use cache if we have a POSITIVE result
                # FIX v2.4.20: Always retest if cached mode is "native" - direct SQL is more reliable
                with self.__class__._cache_lock:
                    if layer_id in self.__class__._spatialite_support_cache:
                        cached = self.__class__._spatialite_support_cache[layer_id]
                        if cached:  # Cached positive result
                            use_direct = self.__class__._direct_sql_mode_cache.get(layer_id, False)
                            if use_direct:
                                # Direct SQL mode cached - safe to use
                                self.log_info(f"  → CACHE HIT (True): mode=direct SQL")
                                return True
                            else:
                                # Native mode cached - retest for direct SQL (more reliable)
                                self.log_info(f"  → CACHE HIT (True, native mode) - retesting for direct SQL...")
                                # Invalidate cache to force retest with direct SQL priority
                                del self.__class__._spatialite_support_cache[layer_id]
                                if layer_id in self.__class__._direct_sql_mode_cache:
                                    del self.__class__._direct_sql_mode_cache[layer_id]
                        else:
                            # Cached negative result - need to retest with direct SQL
                            self.log_info(f"  → CACHE HIT (False) - retesting with direct SQL mode...")
                            # Remove from cache to force retest
                            del self.__class__._spatialite_support_cache[layer_id]
                
                # FIX v2.4.20: PRIORITY DIRECT SQL for GeoPackage
                # The native setSubsetString mode with Spatialite SQL is unreliable:
                # - Simple test expressions (ST_Intersects with POINT) may pass
                # - But complex expressions with WKT geometries are silently ignored by GDAL
                # - This causes filters to appear successful but return ALL features
                #
                # Solution: Always prefer direct SQL mode for GeoPackage/SQLite
                # This queries FIDs directly via mod_spatialite and applies simple "fid IN (...)" filter
                
                # Test 1: Try direct SQL mode FIRST (more reliable for complex expressions)
                # This works even when GDAL's OGR driver doesn't support Spatialite SQL
                mod_available, ext_name = _test_mod_spatialite_available()
                self.log_info(f"  → mod_spatialite available: {mod_available}")
                if mod_available:
                    # Verify we can connect to this specific file with mod_spatialite
                    direct_works = self._test_direct_spatialite_connection(source_path)
                    self.log_info(f"  → Direct connection test: {direct_works}")
                    if direct_works:
                        self.log_info(
                            f"✓ {file_type} layer: {layer.name()} - Using DIRECT SQL mode "
                            f"(mod_spatialite bypassing GDAL)"
                        )
                        with self.__class__._cache_lock:
                            self.__class__._direct_sql_mode_cache[layer_id] = True  # Use direct SQL mode
                            self.__class__._spatialite_support_cache[layer_id] = True
                        return True
                    else:
                        self.log_info(f"  → Direct SQL mode failed, trying native mode as fallback...")
                else:
                    self.log_info(f"  → mod_spatialite not available, trying native mode...")
                
                # Test 2: Fallback to native setSubsetString with Spatialite SQL
                # NOTE: This may work for simple expressions but fail for complex WKT geometries
                native_works = self._test_spatialite_functions_no_cache(layer)
                if native_works:
                    self.log_warning(
                        f"⚠️ {file_type} layer: {layer.name()} - Using NATIVE mode (less reliable)\n"
                        f"   Direct SQL mode unavailable. Native mode may fail with complex geometries.\n"
                        f"   Install mod_spatialite for more reliable spatial filtering."
                    )
                    with self.__class__._cache_lock:
                        self.__class__._spatialite_support_cache[layer_id] = True
                        self.__class__._direct_sql_mode_cache[layer_id] = False  # Use native mode
                    return True
                
                # Both methods failed - cache negative result
                with self.__class__._cache_lock:
                    self.__class__._spatialite_support_cache[layer_id] = False
                
                self.log_warning(
                    f"⚠️ {layer.name()}: GeoPackage/SQLite detected but Spatialite NOT available.\n"
                    f"   • setSubsetString test: FAILED (GDAL not compiled with Spatialite)\n"
                    f"   • Direct SQL test: FAILED (mod_spatialite extension not loadable)\n"
                    f"   Falling back to OGR backend (QGIS processing)."
                )
                return False
            else:
                # OGR layer but not GeoPackage/SQLite - not supported by Spatialite backend
                self.log_debug(
                    f"⚠️ {layer.name()}: OGR layer but not GeoPackage/SQLite "
                    f"(source ends with: ...{source_path[-30:] if len(source_path) > 30 else source_path})"
                )
                return False
        
        # Provider is neither 'spatialite' nor 'ogr' - not supported
        self.log_debug(f"⚠️ {layer.name()}: Provider '{provider_type}' not supported by Spatialite backend")
        return False
    
    def _test_direct_spatialite_connection(self, file_path: str) -> bool:
        """
        Test if we can open a GeoPackage/SQLite file with mod_spatialite directly.
        
        Args:
            file_path: Path to the GeoPackage or SQLite file
            
        Returns:
            True if connection with mod_spatialite works
        """
        try:
            mod_available, ext_name = _test_mod_spatialite_available()
            if not mod_available or not ext_name:
                return False
            
            if not os.path.isfile(file_path):
                self.log_warning(f"File not found: {file_path}")
                return False
            
            conn = sqlite3.connect(file_path)
            conn.enable_load_extension(True)
            conn.load_extension(ext_name)
            
            # Test spatial function works
            cursor = conn.cursor()
            cursor.execute("SELECT ST_GeomFromText('POINT(0 0)', 4326) IS NOT NULL")
            result = cursor.fetchone()
            conn.close()
            
            return result and result[0]
            
        except Exception as e:
            self.log_debug(f"Direct Spatialite connection test failed for {file_path}: {e}")
            return False
    
    def _test_spatialite_functions(self, layer: QgsVectorLayer) -> bool:
        """
        Test if Spatialite spatial functions work on this layer.
        
        Tests by trying a simple GeomFromText expression in setSubsetString.
        If it fails, Spatialite functions are not available.
        
        Uses a cached result per layer ID AND per source file to avoid repeated testing.
        For GeoPackage with 40+ layers, testing one layer is enough to know
        if Spatialite functions work for the whole file.
        
        IMPROVED v2.4.1: Better detection of geometry column for GeoPackage layers
        - Tries layer.geometryColumn() first
        - Falls back to common column names (geometry, geom)
        - Uses simpler test expressions that are more likely to succeed
        - Cache by source file for multi-layer GeoPackages
        
        FIXED v2.4.11: Use simpler test expression without spatial functions
        - First test if basic subset works
        - Then test if ST_IsValid (simpler than ST_Intersects) works
        - Better error diagnostics
        
        Args:
            layer: Layer to test
            
        Returns:
            True if Spatialite functions work, False otherwise
        """
        # Use class-level cache (defined as class attributes)
        layer_id = layer.id()
        
        # THREAD SAFETY v2.4.12: Use lock when accessing cache
        with self.__class__._cache_lock:
            if layer_id in self.__class__._spatialite_support_cache:
                cached = self.__class__._spatialite_support_cache[layer_id]
                # v2.4.13: Log at INFO level if cache returns False (helps diagnose fallback issues)
                if cached:
                    self.log_debug(f"Using cached Spatialite support result for {layer.name()}: {cached}")
                else:
                    self.log_info(f"⚠️ CACHE HIT (False) for {layer.name()} - Spatialite test previously failed, using OGR fallback")
                return cached
        
        # OPTIMIZATION: Check if we already tested this source file (e.g., GeoPackage)
        # For multi-layer GeoPackages, we only need to test once per file
        source = layer.source()
        source_path = source.split('|')[0] if '|' in source else source
        # Normalize path for consistent cache key (handle Windows case-insensitivity)
        import os
        source_path_normalized = os.path.normpath(source_path).lower() if source_path else ""
        
        # Check file cache with lock
        with self.__class__._cache_lock:
            if source_path_normalized.endswith('.gpkg') or source_path_normalized.endswith('.sqlite'):
                if source_path_normalized in self.__class__._spatialite_file_cache:
                    cached = self.__class__._spatialite_file_cache[source_path_normalized]
                    # v2.4.13: Log at INFO level for positive file cache (helps confirm Spatialite works for file)
                    if cached:
                        self.log_info(f"✓ FILE CACHE HIT for {layer.name()} - Spatialite verified for this GeoPackage")
                    else:
                        self.log_info(f"⚠️ FILE CACHE HIT (False) for {layer.name()} - Spatialite unavailable for this file")
                    self.__class__._spatialite_support_cache[layer_id] = cached
                    return cached
        
        try:
            # Save current subset string
            original_subset = layer.subsetString()
            
            # Get geometry column name - try multiple methods
            geom_col = layer.geometryColumn()
            
            # EARLY CHECK: Detect layers without geometry
            # These layers can still use Spatialite for attribute filtering
            has_geometry = layer.geometryType() != 4  # 4 = QgsWkbTypes.NullGeometry
            if not has_geometry and not geom_col:
                self.log_info(f"⚠️ Layer {layer.name()} has no geometry - using attribute-only Spatialite mode")
                # For non-spatial layers, we still support Spatialite for attribute filtering
                # Cache only by layer ID, NOT by file (to avoid affecting spatial layers)
                with self.__class__._cache_lock:
                    self.__class__._spatialite_support_cache[layer_id] = True
                return True
            
            self.log_info(f"🔍 Testing Spatialite support for {layer.name()}")
            self.log_info(f"  → Geometry column from layer: '{geom_col}'")
            self.log_info(f"  → Provider: {layer.providerType()}")
            self.log_info(f"  → Has geometry: {has_geometry}")
            self.log_info(f"  → Source: {source_path[:80]}...")
            
            # Build list of candidate geometry column names
            candidates = []
            if geom_col:
                candidates.append(geom_col)
            # Common GeoPackage/Spatialite column names
            candidates.extend(['geometry', 'geom', 'GEOMETRY', 'GEOM', 'the_geom'])
            # Remove duplicates while preserving order
            seen = set()
            unique_candidates = []
            for c in candidates:
                if c.lower() not in seen:
                    seen.add(c.lower())
                    unique_candidates.append(c)
            
            self.log_debug(f"  → Candidate geometry columns: {unique_candidates}")
            
            # STEP 1: First test if basic subset works at all
            basic_test = "1 = 0"  # Should always work, returns no features
            try:
                basic_result = layer.setSubsetString(basic_test)
                layer.setSubsetString(original_subset if original_subset else "")
                if not basic_result:
                    self.log_error(f"  ✗ Basic subset test failed for {layer.name()} - layer may not support subset strings")
                    with self.__class__._cache_lock:
                        self.__class__._spatialite_support_cache[layer_id] = False
                        # Do NOT cache by file - other layers may work fine
                    return False
                else:
                    self.log_debug(f"  ✓ Basic subset test passed")
            except Exception as e:
                self.log_error(f"  ✗ Basic subset test exception: {e}")
                with self.__class__._cache_lock:
                    self.__class__._spatialite_support_cache[layer_id] = False
                    # Do NOT cache by file - other layers may work fine
                return False
            
            # STEP 2: Try each candidate geometry column with progressively simpler tests
            result = False
            for test_geom_col in unique_candidates:
                # Test 1: Simple geometry not null (should always work if column exists)
                test_expr_simple = f"\"{test_geom_col}\" IS NOT NULL AND 1 = 0"
                try:
                    result_simple = layer.setSubsetString(test_expr_simple)
                    layer.setSubsetString(original_subset if original_subset else "")
                    if not result_simple:
                        self.log_debug(f"  → Column '{test_geom_col}' does not exist or is not accessible")
                        continue
                    else:
                        self.log_debug(f"  ✓ Column '{test_geom_col}' exists")
                except Exception:
                    continue
                
                # Test 2: GeomFromText (tests if spatial functions are available)
                test_expr_geom = f"GeomFromText('POINT(0 0)', 4326) IS NOT NULL AND 1 = 0"
                try:
                    result_geom = layer.setSubsetString(test_expr_geom)
                    layer.setSubsetString(original_subset if original_subset else "")
                    if not result_geom:
                        self.log_warning(f"  ✗ GeomFromText function NOT available - Spatialite extension not loaded")
                        # This means GDAL was not compiled with Spatialite
                        break
                    else:
                        self.log_debug(f"  ✓ GeomFromText function available")
                except Exception as e:
                    self.log_warning(f"  ✗ GeomFromText test exception: {e}")
                    break
                
                # Test 3: Full ST_Intersects test
                test_expr = f"ST_Intersects(\"{test_geom_col}\", GeomFromText('POINT(0 0)', 4326)) = 1 AND 1 = 0"
                try:
                    result = layer.setSubsetString(test_expr)
                except Exception as e:
                    self.log_debug(f"  → ST_Intersects test exception with column '{test_geom_col}': {e}")
                    result = False
                
                # Restore original subset immediately
                try:
                    layer.setSubsetString(original_subset if original_subset else "")
                except Exception:
                    pass
                
                if result:
                    self.log_info(f"  ✓ Spatialite test PASSED for {layer.name()} with column '{test_geom_col}'")
                    break
                else:
                    self.log_debug(f"  → ST_Intersects test failed with column '{test_geom_col}', trying next...")
            
            # Cache the result by layer ID (with lock for thread safety)
            with self.__class__._cache_lock:
                self.__class__._spatialite_support_cache[layer_id] = result
                
                # IMPORTANT FIX: Only cache POSITIVE results by file
                # A layer may fail the test due to missing geometry column, but other layers
                # in the same file may have geometry and support Spatialite functions.
                # Caching negative results by file would cause false negatives.
                if result and (source_path_normalized.endswith('.gpkg') or source_path_normalized.endswith('.sqlite')):
                    self.__class__._spatialite_file_cache[source_path_normalized] = True
                    self.log_info(f"✓ Spatialite support verified for file: {source_path}")
            
            if result:
                self.log_debug(f"✓ Spatialite function test PASSED for {layer.name()}")
                return True
            else:
                # Log more informatively for user troubleshooting
                provider_type = layer.providerType()
                source = layer.source()
                source_path = source.split('|')[0] if '|' in source else source
                file_ext = source_path.split('.')[-1].lower() if '.' in source_path else 'unknown'
                
                if file_ext in ('shp', 'geojson', 'json', 'kml'):
                    self.log_warning(
                        f"✗ Spatialite functions NOT supported for {layer.name()} ({file_ext}). "
                        f"Only GeoPackage (.gpkg) and SQLite (.sqlite) support Spatialite SQL. "
                        f"Using OGR backend (QGIS processing) as fallback."
                    )
                elif file_ext in ('gpkg', 'sqlite'):
                    self.log_warning(
                        f"✗ Spatialite functions unavailable for {layer.name()}. "
                        f"GDAL may not be compiled with Spatialite extension. "
                        f"Using OGR backend (QGIS processing) as fallback."
                    )
                else:
                    self.log_debug(f"✗ Spatialite function test FAILED for {layer.name()} - tried all column candidates")
                return False
                
        except Exception as e:
            self.log_error(f"✗ Spatialite function test ERROR for {layer.name()}: {e}")
            import traceback
            self.log_debug(f"Traceback: {traceback.format_exc()}")
            # IMPORTANT FIX: Only cache by layer ID, NOT by file
            # An error for one layer shouldn't affect other layers in the same file
            with self.__class__._cache_lock:
                self.__class__._spatialite_support_cache[layer_id] = False
                # Do NOT cache by file on error - other layers may work fine
            return False
    
    def _test_spatialite_functions_no_cache(self, layer: QgsVectorLayer) -> bool:
        """
        Test if Spatialite spatial functions work on this layer WITHOUT using cache.
        
        This is a lighter version of _test_spatialite_functions that:
        - Does NOT check or update the cache
        - Only tests the basic Spatialite functionality
        - Used by supports_layer() for retesting when cache has negative results
        
        Args:
            layer: Layer to test
            
        Returns:
            True if Spatialite functions work via setSubsetString, False otherwise
        """
        try:
            # Save current subset string
            original_subset = layer.subsetString()
            
            # Get geometry column name
            geom_col = layer.geometryColumn()
            
            # Check for non-geometry layers
            has_geometry = layer.geometryType() != 4  # 4 = QgsWkbTypes.NullGeometry
            if not has_geometry and not geom_col:
                self.log_debug(f"Layer {layer.name()} has no geometry - attribute-only mode")
                return True  # Non-spatial layers work fine
            
            # Build list of candidate geometry column names
            candidates = []
            if geom_col:
                candidates.append(geom_col)
            candidates.extend(['geometry', 'geom', 'GEOMETRY', 'GEOM', 'the_geom'])
            # Remove duplicates
            seen = set()
            unique_candidates = []
            for c in candidates:
                if c.lower() not in seen:
                    seen.add(c.lower())
                    unique_candidates.append(c)
            
            # Test 1: Basic subset string
            basic_test = "1 = 0"
            try:
                basic_result = layer.setSubsetString(basic_test)
                layer.setSubsetString(original_subset if original_subset else "")
                if not basic_result:
                    return False
            except Exception:
                return False
            
            # Test 2: GeomFromText and ST_Intersects
            for test_geom_col in unique_candidates:
                # Check column exists
                test_expr_simple = f"\"{test_geom_col}\" IS NOT NULL AND 1 = 0"
                try:
                    result_simple = layer.setSubsetString(test_expr_simple)
                    layer.setSubsetString(original_subset if original_subset else "")
                    if not result_simple:
                        continue
                except Exception:
                    continue
                
                # Test GeomFromText
                test_expr_geom = f"GeomFromText('POINT(0 0)', 4326) IS NOT NULL AND 1 = 0"
                try:
                    result_geom = layer.setSubsetString(test_expr_geom)
                    layer.setSubsetString(original_subset if original_subset else "")
                    if not result_geom:
                        return False  # GDAL not compiled with Spatialite
                except Exception:
                    return False
                
                # Test ST_Intersects
                test_expr = f"ST_Intersects(\"{test_geom_col}\", GeomFromText('POINT(0 0)', 4326)) = 1 AND 1 = 0"
                try:
                    result = layer.setSubsetString(test_expr)
                    layer.setSubsetString(original_subset if original_subset else "")
                    if result:
                        return True  # Success!
                except Exception:
                    pass
            
            return False
            
        except Exception as e:
            self.log_debug(f"_test_spatialite_functions_no_cache error: {e}")
            return False
    
    def _get_spatialite_db_path(self, layer: QgsVectorLayer) -> Optional[str]:
        """
        Extract database file path from Spatialite/GeoPackage layer.
        
        Supports:
        - Native Spatialite databases (.sqlite)
        - GeoPackage files (.gpkg) - which use SQLite internally
        
        Note: GDAL GeoPackage driver requires read/write access to the file.
        
        Args:
            layer: Spatialite/GeoPackage vector layer
        
        Returns:
            Database file path or None if not found or not accessible
        """
        import os
        
        try:
            source = layer.source()
            self.log_debug(f"Layer source: {source}")
            
            # Try using QgsDataSourceUri (most reliable)
            uri = QgsDataSourceUri(source)
            db_path = uri.database()
            
            if db_path and db_path.strip():
                self.log_debug(f"Database path from URI: {db_path}")
                
                # Verify file exists
                if not os.path.isfile(db_path):
                    self.log_error(f"Database file not found: {db_path}")
                    return None
                
                # Check file permissions (GDAL GeoPackage driver requires read/write)
                if not os.access(db_path, os.R_OK):
                    self.log_error(
                        f"GeoPackage/Spatialite file not readable: {db_path}. "
                        f"GDAL driver requires read access."
                    )
                    return None
                
                if not os.access(db_path, os.W_OK):
                    self.log_warning(
                        f"GeoPackage/Spatialite file not writable: {db_path}. "
                        f"GDAL driver typically requires write access even for read operations. "
                        f"This may cause issues with spatial indexes and temporary tables."
                    )
                    # Don't return None - allow read-only operation but warn
                
                return db_path
            
            # Fallback: Parse source string manually
            # Format: dbname='/path/to/file.sqlite' table="table_name"
            match = re.search(r"dbname='([^']+)'", source)
            if match:
                db_path = match.group(1)
                self.log_debug(f"Database path from regex: {db_path}")
                return db_path
            
            # Another format: /path/to/file.gpkg|layername=table_name (GeoPackage)
            # or /path/to/file.sqlite|layername=table_name
            if '|' in source:
                db_path = source.split('|')[0]
                self.log_debug(f"Database path from pipe split: {db_path}")
                return db_path
            
            self.log_warning(f"Could not extract database path from source: {source}")
            return None
            
        except Exception as e:
            self.log_error(f"Error extracting database path: {str(e)}")
            return None
    
    def _create_temp_geometry_table(
        self,
        db_path: str,
        wkt_geom: str,
        srid: int = 4326
    ) -> Tuple[Optional[str], Optional[sqlite3.Connection]]:
        """
        Create temporary table with source geometry and spatial index.
        
        ⚠️ WARNING: This optimization is DISABLED for setSubsetString!
        
        WHY DISABLED:
        - SQLite TEMP tables are connection-specific
        - QGIS uses its own connection for evaluating subset strings
        - When we create a TEMP table, QGIS cannot see it
        - Result: "no such table: _fm_temp_geom_xxx" error
        
        SOLUTION:
        - Use inline WKT with GeomFromText() for subset strings
        - This function kept for potential future use with direct SQL queries
        - Could be re-enabled for export operations (not filtering)
        
        Performance Note:
        - Inline WKT: O(n × m) where m = WKT parsing time
        - With temp table: O(1) insertion + O(log n) indexed queries
        - Trade-off: Compatibility vs Performance
        
        Args:
            db_path: Path to Spatialite database
            wkt_geom: WKT geometry string
            srid: SRID for geometry (default 4326)
        
        Returns:
            Tuple (temp_table_name, connection) or (None, None) if failed
        """
        try:
            # Generate unique temp table name based on timestamp
            timestamp = int(time.time() * 1000000)  # Microseconds
            temp_table = f"_fm_temp_geom_{timestamp}"
            
            self.log_info(f"Creating temp geometry table '{temp_table}' in {db_path}")
            
            # Connect to database
            conn = sqlite3.connect(db_path)
            conn.enable_load_extension(True)
            
            # Load spatialite extension
            try:
                conn.load_extension('mod_spatialite')
            except (AttributeError, OSError):
                try:
                    conn.load_extension('mod_spatialite.dll')  # Windows
                except Exception as ext_error:
                    self.log_error(f"Could not load spatialite extension: {ext_error}")
                    conn.close()
                    return None, None
            
            cursor = conn.cursor()
            
            # Create temp table
            cursor.execute(f"""
                CREATE TEMP TABLE {temp_table} (
                    id INTEGER PRIMARY KEY,
                    geometry GEOMETRY
                )
            """)
            self.log_debug(f"Temp table {temp_table} created")
            
            # Insert geometry
            cursor.execute(f"""
                INSERT INTO {temp_table} (id, geometry)
                VALUES (1, GeomFromText(?, ?))
            """, (wkt_geom, srid))
            
            self.log_debug(f"Geometry inserted into {temp_table}")
            
            # Create spatial index on temp table
            # Spatialite uses virtual table for spatial index
            try:
                cursor.execute(f"""
                    SELECT CreateSpatialIndex('{temp_table}', 'geometry')
                """)
                self.log_info(f"✓ Spatial index created on {temp_table}")
            except Exception as idx_error:
                self.log_warning(f"Could not create spatial index: {idx_error}. Continuing without index.")
            
            conn.commit()
            
            self.log_info(
                f"✓ Temp table '{temp_table}' created successfully with spatial index. "
                f"WKT size: {len(wkt_geom)} chars"
            )
            
            return temp_table, conn
            
        except Exception as e:
            self.log_error(f"Error creating temp geometry table: {str(e)}")
            import traceback
            self.log_debug(f"Traceback: {traceback.format_exc()}")
            if conn:
                try:
                    conn.close()
                except (AttributeError, OSError):
                    pass
            return None, None
    
    def cleanup(self):
        """
        Clean up temporary table and close connection.
        
        Should be called after filtering is complete.
        """
        if self._temp_table_name and self._temp_table_conn:
            try:
                self.log_debug(f"Cleaning up temp table {self._temp_table_name}")
                cursor = self._temp_table_conn.cursor()
                cursor.execute(f"DROP TABLE IF EXISTS {self._temp_table_name}")
                self._temp_table_conn.commit()
                self._temp_table_conn.close()
                self.log_info(f"✓ Temp table {self._temp_table_name} cleaned up")
            except Exception as e:
                self.log_warning(f"Error cleaning up temp table: {str(e)}")
            finally:
                self._temp_table_name = None
                self._temp_table_conn = None
    
    def build_expression(
        self,
        layer_props: Dict,
        predicates: Dict,
        source_geom: Optional[str] = None,
        buffer_value: Optional[float] = None,
        buffer_expression: Optional[str] = None,
        source_filter: Optional[str] = None,
        **kwargs
    ) -> str:
        """
        Build Spatialite filter expression.
        
        OPTIMIZATION: Uses temporary table with spatial index instead of inline WKT
        for massive performance improvement on medium-large datasets.
        
        Performance:
        - Without temp table: O(n × m) where m = WKT parsing overhead
        - With temp table: O(n log n) with spatial index
        - Gain: 10× on 5k features, 50× on 20k features
        
        Args:
            layer_props: Layer properties
            predicates: Spatial predicates to apply
            source_geom: Source geometry (WKT string)
            buffer_value: Buffer distance
            buffer_expression: Expression for dynamic buffer
            source_filter: Source layer filter (not used in Spatialite)
        
        Returns:
            Spatialite SQL expression string
        """
        self.log_debug(f"Building Spatialite expression for {layer_props.get('layer_name', 'unknown')}")
        
        # Extract layer properties
        # Use layer_table_name (actual source table) if available, fallback to layer_name (display name)
        table = layer_props.get("layer_table_name") or layer_props.get("layer_name")
        geom_field = layer_props.get("layer_geometry_field", "geom")
        primary_key = layer_props.get("primary_key_name")
        layer = layer_props.get("layer")  # QgsVectorLayer instance
        
        # CRITICAL FIX: Get actual geometry column name from layer's data source
        # Use QGIS APIs in the safest order to avoid bad guesses that break subset strings
        # FIX v2.4.13: Only use fallback methods if previous method returned nothing
        detected_geom_field = None
        if layer:
            try:
                # METHOD 0: Directly ask the layer (most reliable and cheap)
                geom_col_from_layer = layer.geometryColumn()
                if geom_col_from_layer and geom_col_from_layer.strip():
                    detected_geom_field = geom_col_from_layer
                    self.log_debug(f"Geometry column from layer.geometryColumn(): '{detected_geom_field}'")
                
                # METHOD 1: QGIS provider URI parsing (only if METHOD 0 failed)
                if not detected_geom_field:
                    provider = layer.dataProvider()
                    from qgis.core import QgsDataSourceUri
                    uri_string = provider.dataSourceUri()
                    uri_obj = QgsDataSourceUri(uri_string)
                    uri_geom_col = uri_obj.geometryColumn()
                    if uri_geom_col and uri_geom_col.strip():
                        detected_geom_field = uri_geom_col
                        self.log_debug(f"Geometry column from URI: '{detected_geom_field}'")
                    else:
                        # METHOD 2: Manual URI inspection (only if METHOD 1 failed)
                        if '|' in uri_string:
                            parts = uri_string.split('|')
                            for part in parts:
                                if part.startswith('geometryname='):
                                    detected_geom_field = part.split('=')[1]
                                    self.log_debug(f"Geometry column from URI part: '{detected_geom_field}'")
                                    break
                
                # METHOD 3: Query database metadata as last resort (only if previous methods failed)
                if not detected_geom_field:
                    db_path = self._get_spatialite_db_path(layer)
                    
                    if db_path:
                        import sqlite3
                        try:
                            conn = sqlite3.connect(db_path)
                            cursor = conn.cursor()
                            
                            # Extract actual table name from URI (without layer name prefix)
                            from qgis.core import QgsDataSourceUri
                            provider = layer.dataProvider()
                            uri_string = provider.dataSourceUri()
                            uri_obj = QgsDataSourceUri(uri_string)
                            actual_table = uri_obj.table()
                            if not actual_table:
                                # Fallback: extract from URI string
                                for part in uri_string.split('|'):
                                    if part.startswith('layername='):
                                        actual_table = part.split('=')[1]
                                        break
                            
                            if actual_table:
                                # Query GeoPackage geometry_columns table
                                cursor.execute(
                                    "SELECT column_name FROM gpkg_geometry_columns WHERE table_name = ?",
                                    (actual_table,)
                                )
                                result = cursor.fetchone()
                                if result and result[0]:
                                    detected_geom_field = result[0]
                                    self.log_debug(f"Geometry column from gpkg_geometry_columns: '{detected_geom_field}'")
                            
                            conn.close()
                        except Exception as e:
                            self.log_warning(f"Database query error: {e}")
                
                # Apply detected geometry field
                if detected_geom_field:
                    geom_field = detected_geom_field
                    self.log_info(f"✓ Detected geometry column: '{geom_field}'")
                else:
                    self.log_warning(f"Could not detect geometry column, using default: '{geom_field}'")
                    
            except Exception as e:
                self.log_warning(f"Error detecting geometry column name: {e}")
        
        # Source geometry should be WKT string from prepare_spatialite_source_geom
        if not source_geom:
            self.log_error("No source geometry provided for Spatialite filter")
            return ""
        
        if not isinstance(source_geom, str):
            self.log_error(f"Invalid source geometry type for Spatialite: {type(source_geom)}")
            return ""
        
        wkt_length = len(source_geom)
        self.log_debug(f"Source WKT length: {wkt_length} chars")
        
        # DIAGNOSTIC v2.4.10: Log WKT preview and bounding box
        from qgis.core import QgsMessageLog, Qgis, QgsGeometry
        wkt_preview = source_geom[:250] if len(source_geom) > 250 else source_geom
        QgsMessageLog.logMessage(
            f"Spatialite build_expression WKT ({wkt_length} chars): {wkt_preview}...",
            "FilterMate", Qgis.Info
        )
        # Try to calculate bounding box of source geometry
        try:
            temp_geom = QgsGeometry.fromWkt(source_geom.replace("''", "'"))
            if temp_geom and not temp_geom.isEmpty():
                bbox = temp_geom.boundingBox()
                QgsMessageLog.logMessage(
                    f"  Source geometry bbox: ({bbox.xMinimum():.1f},{bbox.yMinimum():.1f})-({bbox.xMaximum():.1f},{bbox.yMaximum():.1f})",
                    "FilterMate", Qgis.Info
                )
        except Exception as e:
            QgsMessageLog.logMessage(f"  Could not parse WKT bbox: {e}", "FilterMate", Qgis.Warning)
        
        # Build geometry expression for target layer
        # CRITICAL FIX v2.4.12/v2.4.13: GeoPackage stores geometries in GPB (GeoPackage Binary) format
        # We MUST use GeomFromGPB() to convert GPB to Spatialite geometry before spatial predicates
        # NOTE: The function is GeomFromGPB() NOT ST_GeomFromGPB() (ST_ version doesn't exist!)
        # Without this conversion, ST_Intersects returns TRUE for ALL features!
        geom_expr = f'"{geom_field}"'
        
        # Check if we need table prefix (usually not needed for subset strings)
        if table and '.' in str(table):
            geom_expr = f'"{table}"."{geom_field}"'
        
        # Detect if this is a GeoPackage layer (needs GPB conversion)
        is_geopackage = False
        if layer:
            source = layer.source().lower()
            is_geopackage = '.gpkg' in source or 'gpkg|' in source
        
        # Apply GPB conversion for GeoPackage layers
        # CRITICAL v2.4.13: Use GeomFromGPB() NOT ST_GeomFromGPB()
        # The SpatiaLite function is GeomFromGPB() (without ST_ prefix)
        # Alternatively, CastAutomagic() auto-detects GPB or standard WKB
        if is_geopackage:
            geom_expr = f'GeomFromGPB({geom_expr})'
            self.log_info(f"GeoPackage detected: using GeomFromGPB() for geometry conversion")
        
        self.log_info(f"Geometry column detected: '{geom_field}' for layer {layer_props.get('layer_name', 'unknown')}")
        
        # Get target layer SRID for comparison
        target_srid = 4326  # Default fallback
        if layer:
            crs = layer.crs()
            if crs and crs.isValid():
                authid = crs.authid()
                if ':' in authid:
                    try:
                        target_srid = int(authid.split(':')[1])
                        self.log_debug(f"Target layer SRID: {target_srid} (from {authid})")
                    except (ValueError, IndexError):
                        self.log_warning(f"Could not parse SRID from {authid}, using default 4326")
        
        # Get source geometry SRID from task parameters (this is the CRS of the WKT)
        # The WKT was created in source_layer_crs_authid in prepare_spatialite_source_geom
        source_srid = target_srid  # Default: assume same CRS
        if hasattr(self, 'task_params') and self.task_params:
            source_crs_authid = self.task_params.get('infos', {}).get('layer_crs_authid')
            if source_crs_authid and ':' in str(source_crs_authid):
                try:
                    source_srid = int(source_crs_authid.split(':')[1])
                    self.log_debug(f"Source geometry SRID: {source_srid} (from {source_crs_authid})")
                except (ValueError, IndexError):
                    self.log_warning(f"Could not parse source SRID from {source_crs_authid}")
        
        # DIAGNOSTIC v2.4.11: Log SRIDs for debugging
        from qgis.core import QgsMessageLog, Qgis
        QgsMessageLog.logMessage(
            f"  Spatialite SRID check: source={source_srid}, target={target_srid}, needs_transform={source_srid != target_srid}",
            "FilterMate", Qgis.Info
        )
        
        # Check if CRS transformation is needed
        needs_transform = source_srid != target_srid
        if needs_transform:
            self.log_info(f"CRS mismatch: Source SRID={source_srid}, Target SRID={target_srid}")
            self.log_info(f"Will use ST_Transform to reproject source geometry")
        
        # CRITICAL: Temp tables DON'T WORK with setSubsetString!
        # QGIS uses its own connection and cannot see TEMP tables from our connection.
        # Always use inline WKT for subset string filtering.
        use_temp_table = False  # FORCED: temp tables incompatible with setSubsetString
        
        if use_temp_table and layer:
            self.log_info(f"WKT size {wkt_length} chars - using OPTIMIZED temp table method")
            
            # Get database path
            db_path = self._get_spatialite_db_path(layer)
            
            if db_path:
                # Create temp table
                temp_table, conn = self._create_temp_geometry_table(db_path, source_geom, source_srid)
                
                if temp_table and conn:
                    # Store for cleanup later
                    self._temp_table_name = temp_table
                    self._temp_table_conn = conn
                    
                    # Build optimized expression using temp table JOIN
                    # This uses spatial index for O(log n) performance
                    source_geom_expr = f"{temp_table}.geometry"
                    
                    self.log_info("✓ Using temp table with spatial index for filtering")
                else:
                    # Fallback to inline WKT
                    self.log_warning("Temp table creation failed, falling back to inline WKT")
                    use_temp_table = False
            else:
                self.log_warning("Could not get database path, falling back to inline WKT")
                use_temp_table = False
        else:
            use_temp_table = False
        
        # Use inline WKT with SRID (required for setSubsetString compatibility)
        if not use_temp_table:
            if wkt_length > 500000:
                self.log_warning(
                    f"Very large WKT ({wkt_length} chars) in subset string. "
                    "This may cause slow performance. Consider using smaller source selection or PostgreSQL."
                )
            elif wkt_length > 100000:
                self.log_info(
                    f"Large WKT ({wkt_length} chars) in subset string. "
                    "Performance may be reduced for datasets >10k features."
                )
            
            # Build source geometry expression
            # CRITICAL v2.4.22: Don't transform here if buffer will need geographic transformation
            # The buffer logic below will handle all transformations properly
            # We just create the base GeomFromText expression in source SRID
            source_geom_expr = f"GeomFromText('{source_geom}', {source_srid})"
            self.log_debug(f"Created base geometry expression with SRID {source_srid}")
        
        # Apply buffer using ST_Buffer() SQL function if specified
        # This uses Spatialite native spatial functions instead of QGIS processing
        # Supports both positive (expand) and negative (shrink/erode) buffers
        if buffer_value is not None and buffer_value != 0:
            # Check if CRS is geographic - buffer needs to be in appropriate units
            is_target_geographic = target_srid == 4326 or (layer and layer.crs().isGeographic())
            
            # Get buffer endcap style from task_params
            endcap_style = self._get_buffer_endcap_style()
            buffer_style_param = "" if endcap_style == 'round' else f", 'endcap={endcap_style}'"
            
            # Log negative buffer usage
            buffer_type_str = "expansion" if buffer_value > 0 else "erosion (shrink)"
            
            if is_target_geographic:
                # Geographic CRS: buffer is in degrees, which is problematic
                # Use ST_Transform to project to Web Mercator (EPSG:3857) for metric buffer
                # Then transform back to target CRS
                # 
                # CRITICAL v2.4.22 FIX: Build complete transformation chain:
                # 1. Start with source geometry in source_srid
                # 2. Transform to 3857 for metric buffer
                # 3. Apply buffer in meters
                # 4. Transform result to target_srid
                self.log_info(f"🌍 Geographic CRS (target SRID={target_srid}, source SRID={source_srid}) - applying buffer in EPSG:3857")
                
                # Build transformation chain
                if source_srid == 3857:
                    # Source already in 3857, just buffer and transform to target
                    buffered_geom = f"ST_Buffer({source_geom_expr}, {buffer_value}{buffer_style_param})"
                elif source_srid == target_srid:
                    # Source and target are same geographic CRS, transform to 3857 for buffer then back
                    buffered_geom = f"ST_Buffer(ST_Transform({source_geom_expr}, 3857), {buffer_value}{buffer_style_param})"
                else:
                    # Source is different from target, transform source to 3857 for buffer
                    buffered_geom = f"ST_Buffer(ST_Transform({source_geom_expr}, 3857), {buffer_value}{buffer_style_param})"
                
                # CRITICAL FIX v2.3.9: Wrap negative buffers in MakeValid()
                # CRITICAL FIX v2.4.23: Use ST_IsEmpty() to detect ALL empty geometry types
                # CRITICAL FIX v2.5.5: Fixed bug where NULLIF only detected GEOMETRYCOLLECTION EMPTY
                #                      but not POLYGON EMPTY, MULTIPOLYGON EMPTY, etc.
                # Note: Spatialite uses MakeValid() instead of ST_MakeValid()
                if buffer_value < 0:
                    self.log_info(f"  🛡️ Wrapping negative buffer in MakeValid() + ST_IsEmpty check for empty geometry handling")
                    validated_expr = f"MakeValid({buffered_geom})"
                    buffered_geom = f"CASE WHEN ST_IsEmpty({validated_expr}) = 1 THEN NULL ELSE {validated_expr} END"
                
                # Transform buffered result to target SRID
                source_geom_expr = f"ST_Transform({buffered_geom}, {target_srid})"
                self.log_info(f"✓ Applied ST_Buffer({buffer_value}m, {buffer_type_str}, endcap={endcap_style}) via EPSG:3857 reprojection")
            else:
                # Projected CRS: buffer value is directly in map units (usually meters)
                # First ensure geometry is in target SRID if transformation is needed
                if needs_transform:
                    source_geom_expr = f"ST_Transform({source_geom_expr}, {target_srid})"
                    self.log_info(f"Transformed source: SRID {source_srid} → {target_srid}")
                
                # Then apply buffer in native CRS
                source_geom_expr = self._build_st_buffer_with_style(source_geom_expr, buffer_value)
                self.log_info(f"✓ Applied ST_Buffer({buffer_value}, {buffer_type_str}, endcap={endcap_style}) in native CRS (SRID={target_srid})")
        else:
            # No buffer: just apply CRS transformation if needed
            if not use_temp_table and needs_transform:
                source_geom_expr = f"ST_Transform({source_geom_expr}, {target_srid})"
                self.log_info(f"Transformed source (no buffer): SRID {source_srid} → {target_srid}")
        
        # Dynamic buffer expressions use attribute values
        if buffer_expression:
            self.log_info(f"Using dynamic buffer expression: {buffer_expression}")
            # Replace any table prefix in buffer expression for subset string context
            clean_buffer_expr = buffer_expression
            if '"' in clean_buffer_expr and '.' not in clean_buffer_expr:
                # Expression like "field_name" - use as-is for attribute-based buffer
                # Apply endcap style if configured
                endcap_style = self._get_buffer_endcap_style()
                if endcap_style == 'round':
                    source_geom_expr = f"ST_Buffer({source_geom_expr}, {clean_buffer_expr})"
                else:
                    source_geom_expr = f"ST_Buffer({source_geom_expr}, {clean_buffer_expr}, 'endcap={endcap_style}')"
                self.log_info(f"✓ Applied dynamic ST_Buffer with expression: {clean_buffer_expr} (endcap={endcap_style})")
        
        # Build predicate expressions with OPTIMIZED order
        # Order by selectivity (most selective first = fastest short-circuit)
        # intersects > within > contains > overlaps > touches
        predicate_order = ['intersects', 'within', 'contains', 'overlaps', 'touches', 'crosses', 'disjoint']
        
        # Normalize predicate keys: convert indices ('0', '4') to names ('intersects', 'touches')
        # This handles the format from execute_filtering where predicates = {str(idx): sql_func}
        index_to_name = {
            '0': 'intersects', '1': 'contains', '2': 'disjoint', '3': 'equals',
            '4': 'touches', '5': 'overlaps', '6': 'within', '7': 'crosses'
        }
        
        normalized_predicates = {}
        for key, value in predicates.items():
            # Try to normalize the key
            if key in index_to_name:
                # Key is a string index like '0'
                normalized_key = index_to_name[key]
            elif key.lower().startswith('st_'):
                # Key is SQL function like 'ST_Intersects'
                normalized_key = key.lower().replace('st_', '')
            elif key.lower() in predicate_order or key.lower() == 'equals':
                # Key is already a name like 'intersects'
                normalized_key = key.lower()
            else:
                # Unknown format, use as-is
                normalized_key = key
            normalized_predicates[normalized_key] = value
        
        # Sort predicates by optimal order
        ordered_predicates = sorted(
            normalized_predicates.items(),
            key=lambda x: predicate_order.index(x[0]) if x[0] in predicate_order else 999
        )
        
        predicate_expressions = []
        for predicate_name, predicate_func in ordered_predicates:
            # Apply spatial predicate
            # Format: ST_Intersects("geometry", source_geom_expr)
            expr = f"{predicate_func}({geom_expr}, {source_geom_expr})"
            predicate_expressions.append(expr)
            self.log_debug(f"Added predicate: {predicate_func} (optimal order)")
        
        # Combine predicates with OR
        # Note: SQL engines typically evaluate OR left-to-right
        # Most selective predicates first = fewer expensive operations
        if predicate_expressions:
            combined = " OR ".join(predicate_expressions)
            method = "temp table" if use_temp_table else "inline WKT"
            self.log_info(
                f"Built Spatialite expression with {len(predicate_expressions)} predicate(s) "
                f"using {method} method"
            )
            self.log_debug(f"Expression preview: {combined[:150]}...")
            
            # DIAGNOSTIC v2.4.11: Log full expression for first predicate to help debug
            from qgis.core import QgsMessageLog, Qgis
            first_expr_preview = predicate_expressions[0][:300] if predicate_expressions else "NONE"
            QgsMessageLog.logMessage(
                f"  Spatialite predicate: {first_expr_preview}...",
                "FilterMate", Qgis.Info
            )
            
            return combined
        
        self.log_warning("No predicates to apply")
        return ""
    
    def apply_filter(
        self,
        layer: QgsVectorLayer,
        expression: str,
        old_subset: Optional[str] = None,
        combine_operator: Optional[str] = None
    ) -> bool:
        """
        Apply filter to Spatialite layer using setSubsetString.
        
        v2.4.14: Added direct SQL mode for GeoPackage when setSubsetString doesn't
        support Spatialite SQL but mod_spatialite is available. In this mode,
        we query matching FIDs via direct SQL and apply a simple "fid IN (...)" filter.
        
        Args:
            layer: Spatialite layer to filter
            expression: Spatialite SQL expression
            old_subset: Existing subset string
            combine_operator: Operator to combine filters (AND/OR)
        
        Returns:
            True if filter applied successfully
        """
        import time
        start_time = time.time()
        
        try:
            if not expression:
                self.log_warning("Empty expression, skipping filter")
                return False
            
            # Check if direct SQL mode is needed for this layer
            layer_id = layer.id()
            use_direct_sql = False
            with self.__class__._cache_lock:
                use_direct_sql = self.__class__._direct_sql_mode_cache.get(layer_id, False)
            
            # v2.4.20: Log which mode is being used for debugging
            from qgis.core import QgsMessageLog, Qgis
            mode_str = "DIRECT SQL" if use_direct_sql else "NATIVE (setSubsetString)"
            QgsMessageLog.logMessage(
                f"Spatialite apply_filter: {layer.name()} → mode={mode_str}",
                "FilterMate", Qgis.Info
            )
            
            if use_direct_sql:
                self.log_info(f"🚀 Using DIRECT SQL mode for {layer.name()} (bypassing GDAL/OGR)")
                return self._apply_filter_direct_sql(layer, expression, old_subset, combine_operator)
            
            # NATIVE MODE: Using setSubsetString with Spatialite SQL
            self.log_info(f"📝 Using NATIVE mode for {layer.name()} (setSubsetString with Spatialite SQL)")
            
            # Log layer information
            self.log_debug(f"Layer provider: {layer.providerType()}")
            self.log_debug(f"Layer source: {layer.source()[:100]}...")
            self.log_debug(f"Current feature count: {layer.featureCount()}")
            
            # Combine with existing filter if specified
            # CRITICAL FIX: Check for invalid old_subset patterns that should NOT be combined
            # These patterns indicate a previous geometric filter that should be replaced
            if old_subset:
                old_subset_upper = old_subset.upper()
                
                # Pattern 1: __source alias (only valid inside EXISTS subqueries)
                has_source_alias = '__source' in old_subset.lower()
                
                # Pattern 2: EXISTS subquery (avoid nested EXISTS)
                has_exists = 'EXISTS (' in old_subset_upper or 'EXISTS(' in old_subset_upper
                
                # Pattern 3: Spatial predicates (likely from previous geometric filter)
                # Spatialite uses same names as PostGIS for most functions
                spatial_predicates = [
                    'ST_INTERSECTS', 'ST_CONTAINS', 'ST_WITHIN', 'ST_TOUCHES',
                    'ST_OVERLAPS', 'ST_CROSSES', 'ST_DISJOINT', 'ST_EQUALS',
                    'INTERSECTS', 'CONTAINS', 'WITHIN'  # Spatialite-specific
                ]
                has_spatial_predicate = any(pred in old_subset_upper for pred in spatial_predicates)
                
                # If old_subset contains geometric filter patterns, replace instead of combine
                if has_source_alias or has_exists or has_spatial_predicate:
                    self.log_info(f"🔄 Old subset contains geometric filter - replacing instead of combining")
                    self.log_info(f"  → Old subset: '{old_subset[:80]}...'")
                    final_expression = expression
                else:
                    if not combine_operator:
                        combine_operator = 'AND'
                        self.log_info(f"🔗 Préservation du filtre existant avec {combine_operator}")
                    self.log_info(f"  → Ancien subset: '{old_subset[:80]}...' (longueur: {len(old_subset)})")
                    self.log_info(f"  → Nouveau filtre: '{expression[:80]}...' (longueur: {len(expression)})")
                    final_expression = f"({old_subset}) {combine_operator} ({expression})"
                    self.log_info(f"  → Expression combinée: longueur {len(final_expression)} chars")
            else:
                final_expression = expression
            
            self.log_debug(f"Applying Spatialite filter to {layer.name()}")
            self.log_debug(f"Expression length: {len(final_expression)} chars")
            
            # THREAD SAFETY FIX: Use queue callback if available (called from background thread)
            # This defers the setSubsetString() call to the main thread in finished()
            queue_callback = self.task_params.get('_subset_queue_callback')
            
            if queue_callback:
                # Queue for main thread application
                queue_callback(layer, final_expression)
                self.log_debug(f"Spatialite filter queued for main thread application")
                result = True  # We assume success, actual application happens in finished()
            else:
                # Fallback: direct application (for testing or non-task contexts)
                self.log_warning(f"No queue callback - applying directly (may cause thread issues)")
                result = safe_set_subset_string(layer, final_expression)
            
            elapsed = time.time() - start_time
            
            if result:
                feature_count = layer.featureCount()
                self.log_info(f"✓ {layer.name()}: {feature_count} features ({elapsed:.2f}s)")
                
                if feature_count == 0:
                    self.log_warning("Filter resulted in 0 features - check CRS or expression")
                
                if elapsed > 5.0:
                    self.log_warning(f"Slow operation - consider PostgreSQL for large datasets")
            else:
                self.log_error(f"✗ Filter failed for {layer.name()}")
                self.log_error(f"  → Provider: {layer.providerType()}")
                self.log_error(f"  → Geometry column from layer: '{layer.geometryColumn()}'")
                self.log_error(f"  → Expression length: {len(final_expression)} chars")
                self.log_error(f"  → Expression preview: {final_expression[:500]}...")
                
                # Try to get the actual error from the layer
                if layer.error() and layer.error().message():
                    self.log_error(f"  → Layer error: {layer.error().message()}")
                
                # DIAGNOSTIC v2.4.13: More detailed diagnostics for troubleshooting
                try:
                    from qgis.core import QgsDataSourceUri
                    uri_obj = QgsDataSourceUri(layer.dataProvider().dataSourceUri())
                    self.log_error(f"  → URI geometry column: '{uri_obj.geometryColumn()}'")
                    self.log_error(f"  → URI table: '{uri_obj.table()}'")
                    self.log_error(f"  → Source: {layer.source()[:150]}...")
                except Exception as uri_err:
                    self.log_error(f"  → Could not parse URI: {uri_err}")
                
                self.log_error("Check: spatial functions available, geometry column, SQL syntax")
                
                # Check if expression references a temp table (common mistake)
                if '_fm_temp_geom_' in final_expression:
                    self.log_error("⚠️ Expression references temp table - this doesn't work with QGIS!")
                
                # DIAGNOSTIC v2.4.13: Test both geometry column access AND spatial functions
                try:
                    from ..appUtils import is_layer_source_available, safe_set_subset_string
                    if not is_layer_source_available(layer):
                        self.log_warning("Layer invalid or source missing; skipping test expression")
                    else:
                        geom_col = layer.geometryColumn()
                        
                        # Test 1: Simple geometry not null
                        if geom_col:
                            test_expr = f'"{geom_col}" IS NOT NULL AND 1=0'
                            self.log_debug(f"Testing geometry column access: {test_expr}")
                            test_result = safe_set_subset_string(layer, test_expr)
                            if test_result:
                                self.log_info("✓ Geometry column access OK")
                            else:
                                self.log_error(f"✗ Cannot access geometry column '{geom_col}'")
                            safe_set_subset_string(layer, "")
                        
                        # Test 2: GeomFromText function
                        test_expr_geom = "GeomFromText('POINT(0 0)', 4326) IS NOT NULL AND 1=0"
                        self.log_debug(f"Testing GeomFromText: {test_expr_geom}")
                        test_result2 = safe_set_subset_string(layer, test_expr_geom)
                        if test_result2:
                            self.log_info("✓ GeomFromText function available")
                        else:
                            self.log_error("✗ GeomFromText function NOT available")
                            self.log_error("   → GDAL may not be compiled with Spatialite extension")
                            self.log_error("   → Try using OGR backend for this layer")
                        safe_set_subset_string(layer, "")
                        
                        # Test 3: ST_Intersects function
                        if geom_col:
                            test_expr_intersects = f"ST_Intersects(\"{geom_col}\", GeomFromText('POINT(0 0)', 4326)) = 1 AND 1=0"
                            self.log_debug(f"Testing ST_Intersects: {test_expr_intersects}")
                            test_result3 = safe_set_subset_string(layer, test_expr_intersects)
                            if test_result3:
                                self.log_info("✓ ST_Intersects function available")
                                self.log_error("   → Problem is with the SOURCE GEOMETRY (WKT too long or invalid?)")
                            else:
                                self.log_error("✗ ST_Intersects function NOT available")
                            safe_set_subset_string(layer, "")
                except Exception as test_error:
                    self.log_debug(f"Test expression error: {test_error}")
            
            return result
            
        except Exception as e:
            self.log_error(f"Exception while applying filter: {str(e)}")
            import traceback
            self.log_debug(f"Traceback: {traceback.format_exc()}")
            return False
    
    def _apply_filter_direct_sql(
        self,
        layer: QgsVectorLayer,
        expression: str,
        old_subset: Optional[str] = None,
        combine_operator: Optional[str] = None
    ) -> bool:
        """
        Apply filter using direct SQL queries with mod_spatialite.
        
        This method bypasses GDAL's OGR driver and queries the GeoPackage/SQLite
        directly using mod_spatialite. It retrieves matching FIDs and applies
        a simple "fid IN (...)" filter that works with any OGR driver.
        
        v2.4.14: New method for GeoPackage support when GDAL doesn't support
        Spatialite SQL in setSubsetString.
        
        Args:
            layer: GeoPackage/SQLite layer to filter
            expression: Spatialite SQL expression (will be adapted for direct SQL)
            old_subset: Existing subset string
            combine_operator: Operator to combine filters (AND/OR)
        
        Returns:
            True if filter applied successfully
        """
        import time
        start_time = time.time()
        
        try:
            # Get file path and table name
            source = layer.source()
            source_path = source.split('|')[0] if '|' in source else source
            
            # v2.4.21: CRITICAL - Verify source is a local file before connecting
            # This prevents SQLite errors on remote/virtual sources
            source_lower = source_path.lower().strip()
            remote_prefixes = ('http://', 'https://', 'ftp://', 'wfs:', 'wms:', 'wcs://', '/vsicurl/')
            if any(source_lower.startswith(prefix) for prefix in remote_prefixes):
                self.log_error(f"Cannot use direct SQL on remote source: {layer.name()}")
                return False
            
            if not os.path.isfile(source_path):
                self.log_error(f"Source file not found for direct SQL: {source_path}")
                self.log_error(f"  → This may be a remote or virtual source")
                return False
            
            # Get table name
            table_name = None
            if '|layername=' in source:
                table_name = source.split('|layername=')[1].split('|')[0]
            
            if not table_name:
                from qgis.core import QgsDataSourceUri
                uri = QgsDataSourceUri(source)
                table_name = uri.table()
            
            if not table_name:
                self.log_error(f"Could not determine table name for direct SQL mode")
                return False
            
            # Get mod_spatialite extension name
            mod_available, ext_name = _test_mod_spatialite_available()
            if not mod_available or not ext_name:
                self.log_error(f"mod_spatialite not available for direct SQL mode")
                return False
            
            # Connect to the database with mod_spatialite
            conn = sqlite3.connect(source_path)
            conn.enable_load_extension(True)
            conn.load_extension(ext_name)
            cursor = conn.cursor()
            
            # Build a SELECT query to get matching FIDs
            # The expression is a WHERE clause, we need to extract the conditions
            # and build a SELECT fid FROM table WHERE expression query
            
            # Get the primary key column name (usually 'fid' for GeoPackage)
            pk_col = 'fid'  # Default for GeoPackage
            try:
                pk_indices = layer.primaryKeyAttributes()
                if pk_indices:
                    fields = layer.fields()
                    pk_col = fields.at(pk_indices[0]).name()
            except Exception:
                pass
            
            # Build the SELECT query
            # The expression is the WHERE clause from build_expression
            select_query = f'SELECT "{pk_col}" FROM "{table_name}" WHERE {expression}'
            
            self.log_info(f"  → Direct SQL query: {select_query[:200]}...")
            
            # Execute the query
            try:
                cursor.execute(select_query)
                matching_fids = [row[0] for row in cursor.fetchall()]
            except Exception as sql_error:
                self.log_error(f"Direct SQL query failed: {sql_error}")
                conn.close()
                return False
            
            conn.close()
            
            # DIAGNOSTIC v2.4.11: Log number of matching FIDs to QGIS message panel
            from qgis.core import QgsMessageLog, Qgis
            QgsMessageLog.logMessage(
                f"  → Direct SQL found {len(matching_fids)} matching FIDs for {layer.name()}",
                "FilterMate", Qgis.Info
            )
            self.log_info(f"  → Found {len(matching_fids)} matching features via direct SQL")
            
            if len(matching_fids) == 0:
                # No matching features - apply empty filter
                fid_expression = "1 = 0"  # No features will match
            elif len(matching_fids) > 10000:
                # Too many FIDs - use range-based filter if possible
                self.log_warning(
                    f"Large result set ({len(matching_fids)} FIDs). "
                    f"Performance may be affected. Consider PostgreSQL for better performance."
                )
                fid_expression = f'"{pk_col}" IN ({", ".join(str(fid) for fid in matching_fids)})'
            else:
                fid_expression = f'"{pk_col}" IN ({", ".join(str(fid) for fid in matching_fids)})'
            
            # Combine with old_subset if needed
            if old_subset:
                old_subset_upper = old_subset.upper()
                
                # Check for patterns that should not be combined
                has_source_alias = '__source' in old_subset.lower()
                has_exists = 'EXISTS (' in old_subset_upper or 'EXISTS(' in old_subset_upper
                spatial_predicates = [
                    'ST_INTERSECTS', 'ST_CONTAINS', 'ST_WITHIN', 'ST_TOUCHES',
                    'ST_OVERLAPS', 'ST_CROSSES', 'ST_DISJOINT', 'ST_EQUALS',
                    'INTERSECTS', 'CONTAINS', 'WITHIN'
                ]
                has_spatial_predicate = any(pred in old_subset_upper for pred in spatial_predicates)
                
                if has_source_alias or has_exists or has_spatial_predicate:
                    self.log_info(f"🔄 Old subset contains geometric filter - replacing")
                    final_expression = fid_expression
                else:
                    if not combine_operator:
                        combine_operator = 'AND'
                    final_expression = f"({old_subset}) {combine_operator} ({fid_expression})"
            else:
                final_expression = fid_expression
            
            self.log_info(f"  → Applying FID-based filter: {len(final_expression)} chars")
            
            # DIAGNOSTIC v2.4.11: Log first few FIDs to verify correct filtering
            from qgis.core import QgsMessageLog, Qgis
            if matching_fids and len(matching_fids) > 0:
                fid_preview = matching_fids[:10]
                QgsMessageLog.logMessage(
                    f"  → FID-based filter for {layer.name()}: first FIDs = {fid_preview}{'...' if len(matching_fids) > 10 else ''}",
                    "FilterMate", Qgis.Info
                )
            
            # Apply the FID-based filter using queue callback or direct
            queue_callback = self.task_params.get('_subset_queue_callback')
            
            if queue_callback:
                queue_callback(layer, final_expression)
                result = True
            else:
                result = safe_set_subset_string(layer, final_expression)
            
            elapsed = time.time() - start_time
            
            if result:
                self.log_info(
                    f"✓ {layer.name()}: {len(matching_fids)} features via direct SQL ({elapsed:.2f}s)"
                )
            else:
                self.log_error(f"✗ Direct SQL filter failed for {layer.name()}")
            
            return result
            
        except Exception as e:
            self.log_error(f"Exception in direct SQL filter: {str(e)}")
            import traceback
            self.log_debug(f"Traceback: {traceback.format_exc()}")
            return False
    
    def get_backend_name(self) -> str:
        """Get backend name"""
        return "Spatialite"
