# -*- 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
"""

from typing import Dict, Optional, Tuple
import sqlite3
import time
import re
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()

# 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


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
    """
    
    # 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
    
    # 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()
        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]
                logger.debug(f"Spatialite support 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

    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.
        
        Args:
            geom_expr: Geometry expression to buffer
            buffer_value: Buffer distance
            
        Returns:
            Spatialite ST_Buffer expression with style parameter
        """
        endcap_style = self._get_buffer_endcap_style()
        
        if endcap_style == 'round':
            # Default style - no need to specify
            return f"ST_Buffer({geom_expr}, {buffer_value})"
        else:
            # Spatialite uses same syntax as PostGIS for endcap
            return f"ST_Buffer({geom_expr}, {buffer_value}, 'endcap={endcap_style}')"
    
    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
        
        CRITICAL: GeoPackage/SQLite support depends on GDAL being compiled with Spatialite.
        This method now tests if spatial functions actually work before returning True.
        
        Args:
            layer: QGIS vector layer to check
        
        Returns:
            True if layer supports Spatialite spatial functions
        """
        provider_type = layer.providerType()
        
        # 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
            
            if source_path.lower().endswith('.gpkg') or source_path.lower().endswith('.sqlite'):
                # Test if Spatialite functions are available via setSubsetString
                if self._test_spatialite_functions(layer):
                    file_type = "GeoPackage" if source_path.lower().endswith('.gpkg') else "SQLite"
                    self.log_info(f"✓ {file_type} layer: {layer.name()} - Spatialite functions available")
                    return True
                else:
                    self.log_warning(
                        f"⚠️ {layer.name()}: GeoPackage/SQLite detected but Spatialite functions NOT available.\n"
                        f"   This happens when GDAL was not compiled with Spatialite support.\n"
                        f"   Falling back to OGR backend (QGIS processing)."
                    )
                    return False
        
        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]
                self.log_debug(f"Using cached Spatialite support result for {layer.name()}: {cached}")
                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]
                    self.log_debug(f"Using cached Spatialite support for file {source_path}: {cached}")
                    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 _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")
        
        # Build geometry expression for target layer
        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}"'
        
        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}")
        
        # 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 with proper SRID
            # Use source SRID for GeomFromText, then transform to target if needed
            if needs_transform:
                # Transform source geometry to target CRS
                source_geom_expr = f"ST_Transform(GeomFromText('{source_geom}', {source_srid}), {target_srid})"
                self.log_info(f"Using ST_Transform: SRID {source_srid} → {target_srid}")
            else:
                source_geom_expr = f"GeomFromText('{source_geom}', {target_srid})"
                self.log_debug(f"Using SRID {target_srid} (same as target)")
        
        # Apply buffer using ST_Buffer() SQL function if specified
        # This uses Spatialite native spatial functions instead of QGIS processing
        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}'"
            
            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 original CRS
                self.log_info(f"🌍 Geographic CRS (SRID={target_srid}) - applying buffer in EPSG:3857")
                source_geom_expr = (
                    f"ST_Transform("
                    f"ST_Buffer("
                    f"ST_Transform({source_geom_expr}, 3857), "
                    f"{buffer_value}{buffer_style_param}), "
                    f"{target_srid})"
                )
                self.log_info(f"✓ Applied ST_Buffer({buffer_value}m, endcap={endcap_style}) via EPSG:3857 reprojection")
            else:
                # Projected CRS: buffer value is directly in map units (usually meters)
                source_geom_expr = self._build_st_buffer_with_style(source_geom_expr, buffer_value)
                self.log_info(f"✓ Applied ST_Buffer({buffer_value}, endcap={endcap_style}) in native CRS (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]}...")
            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.
        
        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
            
            # 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")
            
            # Apply the filter (thread-safe)
            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 get_backend_name(self) -> str:
        """Get backend name"""
        return "Spatialite"
