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

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

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()


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
    """
    
    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
    
    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 to avoid repeated testing.
        
        Args:
            layer: Layer to test
            
        Returns:
            True if Spatialite functions work, False otherwise
        """
        # Use class-level cache to avoid repeated tests
        if not hasattr(self.__class__, '_spatialite_support_cache'):
            self.__class__._spatialite_support_cache = {}
        
        layer_id = layer.id()
        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
        
        try:
            # Get geometry column name
            geom_col = layer.geometryColumn()
            if not geom_col:
                geom_col = "geom"  # Default fallback
            
            # Save current subset string
            original_subset = layer.subsetString()
            
            # Try a simple Spatialite function test
            # Use a simple GeomFromText that should return no features (false condition)
            # This tests if GeomFromText is recognized as a function
            test_expr = f"ST_Intersects(\"{geom_col}\", GeomFromText('POINT(0 0)', 4326)) = 1 AND 1 = 0"
            
            # Try to apply the test expression
            result = layer.setSubsetString(test_expr)
            
            # Restore original subset immediately
            layer.setSubsetString(original_subset if original_subset else "")
            
            # Cache the result
            self.__class__._spatialite_support_cache[layer_id] = result
            
            if result:
                self.log_debug(f"✓ Spatialite function test PASSED for {layer.name()}")
                return True
            else:
                self.log_debug(f"✗ Spatialite function test FAILED for {layer.name()}")
                return False
                
        except Exception as e:
            self.log_debug(f"✗ Spatialite function test ERROR for {layer.name()}: {e}")
            # Cache as False on error
            self.__class__._spatialite_support_cache[layer_id] = False
            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
        if layer:
            try:
                # METHOD 0: Directly ask the layer (most reliable and cheap)
                geom_col_from_layer = layer.geometryColumn()
                if geom_col_from_layer:
                    geom_field = geom_col_from_layer
                    self.log_debug(f"Geometry column from layer: {geom_field}")
                
                # METHOD 1: QGIS provider URI parsing
                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:
                    geom_field = uri_geom_col
                else:
                    # METHOD 2: Manual URI inspection
                    if '|' in uri_string:
                        parts = uri_string.split('|')
                        for part in parts:
                            if part.startswith('geometryname='):
                                geom_field = part.split('=')[1]
                                break
                    
                    # METHOD 3: Query database metadata as last resort
                    if geom_field == layer_props.get("layer_geometry_field", "geom"):
                        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)
                                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
                                
                                # Query GeoPackage geometry_columns table
                                cursor.execute(
                                    "SELECT column_name FROM gpkg_geometry_columns WHERE table_name = ?",
                                    (actual_table,)
                                )
                                result = cursor.fetchone()
                                if result:
                                    geom_field = result[0]
                                
                                conn.close()
                            except Exception as e:
                                self.log_warning(f"Database query error: {e}")
            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_debug(f"Geometry expression: {geom_expr}")
        
        # 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())
            
            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}), "
                    f"{target_srid})"
                )
                self.log_info(f"✓ Applied ST_Buffer({buffer_value}m) via EPSG:3857 reprojection")
            else:
                # Projected CRS: buffer value is directly in map units (usually meters)
                source_geom_expr = f"ST_Buffer({source_geom_expr}, {buffer_value})"
                self.log_info(f"✓ Applied ST_Buffer({buffer_value}) 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
                source_geom_expr = f"ST_Buffer({source_geom_expr}, {clean_buffer_expr})"
                self.log_info(f"✓ Applied dynamic ST_Buffer with expression: {clean_buffer_expr}")
        
        # 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("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!")
                
                # Try a simple test to see if spatial functions work
                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:
                        test_expr = f'"{layer.geometryColumn()}" IS NOT NULL'
                        self.log_debug(f"Testing simple expression: {test_expr}")
                        test_result = safe_set_subset_string(layer, test_expr)
                        if test_result:
                            self.log_info("Simple geometry test passed - issue is with spatial expression")
                            # Restore no filter
                            safe_set_subset_string(layer, "")
                        else:
                            self.log_error("Even simple geometry expression failed - layer may not support subset strings")
                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"
