# -*- coding: utf-8 -*-
"""
Spatialite Expression Builder.

v4.1.0: Migrated from before_migration/modules/backends/spatialite_backend.py

This module contains the SQL expression building logic for Spatialite spatial filters.
It implements the GeometricFilterPort interface for Spatialite backends.

Features:
- GeoPackage support with GeomFromGPB conversion
- R-tree spatial index optimization
- WKT simplification for large geometries
- Centroid optimization
- CRS transformation handling

Author: FilterMate Team
Date: January 2026
"""

import logging
from typing import Dict, Optional, Any

try:
    from qgis.core import QgsVectorLayer
except ImportError:
    QgsVectorLayer = None

logger = logging.getLogger('FilterMate.Backend.Spatialite.ExpressionBuilder')

# Import the port interface
try:
    from ....core.ports.geometric_filter_port import GeometricFilterPort
except ImportError:
    from core.ports.geometric_filter_port import GeometricFilterPort

# Import safe_set_subset_string from infrastructure
try:
    from ....infrastructure.database.sql_utils import safe_set_subset_string
except ImportError:
    def safe_set_subset_string(layer, expression):
        """Fallback implementation."""
        if layer is None:
            return False
        try:
            return layer.setSubsetString(expression)
        except Exception:
            return False

# Sentinel value for OGR fallback
USE_OGR_FALLBACK = "__USE_OGR_FALLBACK__"

# Thresholds
SPATIALITE_WKT_SIMPLIFY_THRESHOLD = 100000  # 100KB - simplify WKT above this


class SpatialiteExpressionBuilder(GeometricFilterPort):
    """
    Spatialite expression builder.

    Generates Spatialite SQL expressions for spatial filtering.
    Implements the legacy GeometricFilterPort interface for backward compatibility.

    Features:
    - GeoPackage binary geometry support
    - WKT simplification for large geometries
    - R-tree index optimization via temp tables
    - Centroid optimization
    - CRS transformation

    Example:
        builder = SpatialiteExpressionBuilder(task_params)
        expr = builder.build_expression(
            layer_props={'layer_table_name': 'buildings'},
            predicates={'intersects': True},
            source_geom='POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'
        )
    """

    # Spatial predicate mapping. ``ST_*``-prefixed names work uniformly:
    # - SpatiaLite (>= 4.0, OGC alias): ST_Intersects(...) is equivalent to Intersects(...)
    # - GeoPackage native SQL: ST_Intersects is the standard OGC name
    # - QGIS OGR provider on .gpkg: GDAL passes the WHERE through to SQLite,
    #   which understands both the bare and ST_-prefixed forms when SpatiaLite
    #   is loaded but ONLY ST_-prefixed when it isn't.
    # 2026-04-29 fix: switched from bare names to ST_-prefixed because the
    # cascade silently no-oped on GPKG-via-OGR — the bare ``Intersects(...)``
    # was either rejected or silently dropped depending on whether SpatiaLite
    # was loaded in the GDAL stack.
    PREDICATE_FUNCTIONS = {
        'intersects': 'ST_Intersects',
        'contains': 'ST_Contains',
        'within': 'ST_Within',
        'touches': 'ST_Touches',
        'overlaps': 'ST_Overlaps',
        'crosses': 'ST_Crosses',
        'disjoint': 'ST_Disjoint',
        'equals': 'ST_Equals',
        'covers': 'ST_Covers',
        'coveredby': 'ST_CoveredBy',
    }

    def __init__(self, task_params: Dict[str, Any]):
        """
        Initialize Spatialite expression builder.

        Args:
            task_params: Task configuration parameters
        """
        super().__init__(task_params)
        self._logger = logger
        self.last_error = None

    def get_backend_name(self) -> str:
        """Get backend name."""
        return "Spatialite"

    def supports_layer(self, layer: 'QgsVectorLayer') -> bool:
        """
        Check if this backend supports the given layer.

        Args:
            layer: QGIS vector layer to check

        Returns:
            True if layer is Spatialite or GeoPackage
        """
        if layer is None:
            return False

        provider = layer.providerType()
        if provider == 'spatialite':
            return True

        # Check for GeoPackage via OGR
        if provider == 'ogr':
            source = layer.source().lower()
            if '.gpkg' in source or 'gpkg|' in source:
                return True

        return False

    def build_expression(
        self,
        layer_props: Dict[str, Any],
        predicates: Dict[str, bool],
        source_geom: Optional[str] = None,
        buffer_value: Optional[float] = None,
        buffer_expression: Optional[str] = None,
        source_filter: Optional[str] = None,
        use_centroids: bool = False,
        **kwargs
    ) -> str:
        """
        Build Spatialite filter expression.

        Args:
            layer_props: Layer properties
            predicates: Spatial predicates to apply
            source_geom: Source geometry WKT
            buffer_value: Buffer distance
            buffer_expression: Dynamic buffer expression
            source_filter: Source filter (not used)
            use_centroids: Use centroid optimization
            **kwargs: Additional parameters

        Returns:
            Spatialite SQL expression or USE_OGR_FALLBACK sentinel
        """
        self.log_debug(f"Building Spatialite expression for {layer_props.get('layer_name', 'unknown')}")

        # FIX v4.2.13: Spatialite cannot evaluate dynamic buffer expressions with field references
        # Unlike PostgreSQL which can create temp tables with pre-calculated buffers,
        # Spatialite's Buffer(GeomFromText(wkt), "field_name" * 2) fails because
        # the field reference is not valid in the context of a WKT literal.
        # Solution: Fall back to OGR backend which uses QGIS native expression evaluation.
        if buffer_expression and buffer_expression.strip():
            self.log_warning(f"🔄 Dynamic buffer expression detected: {buffer_expression}")
            self.log_warning("   Spatialite cannot evaluate field references in Buffer()")
            self.log_warning("   → Falling back to OGR backend for native QGIS expression evaluation")
            return USE_OGR_FALLBACK

        # Extract layer properties
        layer_props.get("layer_table_name") or layer_props.get("layer_name")
        geom_field = self._detect_geometry_column(layer_props)
        layer = layer_props.get("layer")

        # Validate source geometry
        if not source_geom:
            self.log_error("No source geometry provided")
            return "1 = 0"

        if not isinstance(source_geom, str):
            self.log_error(f"Invalid source geometry type: {type(source_geom)}")
            return "1 = 0"

        wkt_length = len(source_geom)
        self.log_debug(f"Source WKT length: {wkt_length} chars")

        # Handle GeometryCollection (causes RTTOPO errors)
        is_geometry_collection = source_geom.strip().upper().startswith('GEOMETRYCOLLECTION')
        if is_geometry_collection:
            self.log_warning("GeometryCollection detected - returning OGR fallback")
            return USE_OGR_FALLBACK

        # Simplify large WKT
        if wkt_length >= SPATIALITE_WKT_SIMPLIFY_THRESHOLD:
            source_geom = self._simplify_wkt(source_geom)
            wkt_length = len(source_geom)
            self.log_info(f"WKT simplified to {wkt_length} chars")

        # Build geometry expression
        geom_expr = f'"{geom_field}"'

        # 2026-04-29 fix: previously wrapped ``GeomFromGPB("geom")`` for
        # GeoPackage layers, but QGIS's OGR provider already passes the
        # column through GDAL which auto-decodes the GPB blob to a real
        # geometry by the time SQLite sees the WHERE clause. Re-decoding
        # an already-decoded geometry made the cascade silently no-op
        # (``GeomFromGPB`` returned NULL → ``ST_Intersects(NULL, ...)`` →
        # rejected/ignored, layer left unfiltered). The bare column
        # reference is the correct shape for both pure SpatiaLite and
        # GPKG-via-OGR. Centroid optimization is still applied below.

        # Apply centroid optimization
        if use_centroids:
            geom_expr = self._apply_centroid_transform(geom_expr, layer_props)

        # Get SRIDs
        target_srid = self._get_layer_srid(layer)

        # 2026-07-29 fix: prefer the caller-supplied ``source_srid`` kwarg.
        # ExpressionBuilder._get_expression_builder() computes it from
        # ``self.source_layer_crs_authid``, which reflects the CRS the WKT
        # was ACTUALLY generated in — including the metric reprojection
        # applied by ``configure_metric_crs()`` for buffer calculations
        # (triggered for any geographic/non-metric source CRS, unconditionally,
        # not just when a buffer is requested). ``self._get_source_srid()``
        # instead reads ``task_params['infos']['layer_crs_authid']``, which
        # ``initialization_handler.py`` auto-fills ONCE from the layer's
        # *original* CRS and never updates after reprojection. When source
        # and target share that original CRS, ``source_srid == target_srid``
        # skips ``ST_Transform`` entirely, so the (metric) WKT coordinates get
        # tagged with the (geographic) original SRID and compared as-is —
        # ``ST_Intersects`` never matches, ``setSubsetString`` succeeds, and
        # the layer silently renders zero features. This mirrors the fix
        # already applied to PostgreSQLExpressionBuilder.build_expression()
        # (which reads ``kwargs.get('source_srid')``) — Spatialite's builder
        # was never updated to match.
        source_srid = kwargs.get('source_srid')
        if source_srid is None:
            source_srid = self._get_source_srid()

        self.log_debug(f"SRIDs: source={source_srid}, target={target_srid}")

        # Build source geometry SQL
        # FIX v4.2.11: Pass buffer_expression for dynamic buffer support
        source_geom_sql = self._build_source_geometry_sql(
            source_geom, source_srid, target_srid, buffer_value, buffer_expression
        )

        # Build predicate expressions
        predicate_expressions = []

        for predicate_name, predicate_value in predicates.items():
            if not predicate_value:
                continue

            # Get Spatialite function name
            predicate_func = self.PREDICATE_FUNCTIONS.get(
                predicate_name.lower().replace('st_', ''),
                'Intersects'
            )

            # Build expression
            expr = f"{predicate_func}({geom_expr}, {source_geom_sql})"
            predicate_expressions.append(expr)

        # Combine predicates
        if not predicate_expressions:
            self.log_warning("No predicates specified")
            return "1 = 0"

        if len(predicate_expressions) == 1:
            return predicate_expressions[0]
        else:
            return f"({' OR '.join(predicate_expressions)})"

    def apply_filter(
        self,
        layer: 'QgsVectorLayer',
        expression: str,
        old_subset: Optional[str] = None,
        combine_operator: Optional[str] = None
    ) -> bool:
        """
        Apply filter to Spatialite layer.

        Args:
            layer: Layer to filter
            expression: SQL expression
            old_subset: Existing filter
            combine_operator: Combine operator (AND/OR)

        Returns:
            True if filter applied successfully
        """
        try:
            if not expression:
                self.last_error = "Empty expression, skipping filter"
                self.log_warning(self.last_error)
                return False

            # Check for OGR fallback sentinel
            if expression == USE_OGR_FALLBACK:
                self.last_error = "OGR fallback requested"
                self.log_info(self.last_error)
                return False

            # Combine with existing filter if needed
            if old_subset and combine_operator:
                if self._is_geometric_filter(old_subset):
                    self.log_info("Replacing geometric filter in old_subset")
                    final_expression = expression
                else:
                    final_expression = f"({old_subset}) {combine_operator} ({expression})"
            else:
                final_expression = expression

            # 2026-04-29 fix: prefer the task's subset queue when available so
            # `setSubsetString` runs on the Qt main thread. The cascade is
            # invoked from `QgsTask.run()` (worker thread) and the docstring
            # of ``FilterEngineTask.queue_subset_request`` is explicit:
            # "Subset strings cannot be applied directly from background
            # threads due to Qt thread safety constraints." Calling
            # `safe_set_subset_string` straight from the worker silently
            # no-ops on spatialite (the cascade reported "8/8 filtered
            # successfully" while every distant layer kept its full feature
            # count). When no queue is wired (tests, public-API direct use),
            # fall back to the synchronous path.
            queue_callback = self.task_params.get('_subset_queue_callback') if self.task_params else None
            if queue_callback is not None:
                queue_callback(layer, final_expression)
                self.log_info("✓ Filter queued for main-thread application")
                return True

            success = safe_set_subset_string(layer, final_expression)

            if success:
                self.log_info("✓ Filter applied successfully")
            else:
                self.last_error = f"setSubsetString failed for {layer.name()} (spatialite)"
                self.log_error(f"✗ Failed to apply filter: {self.last_error}")

            return success

        except Exception as e:
            self.last_error = f"Spatialite apply_filter exception: {e}"
            self.log_error(self.last_error)
            return False

    # =========================================================================
    # Private Helper Methods
    # =========================================================================

    # NOTE v4.0.1: _detect_geometry_column, _apply_centroid_transform,
    # _get_layer_srid, _get_source_srid are inherited from GeometricFilterPort

    def _is_geopackage(self, layer) -> bool:
        """Check if layer is from a GeoPackage."""
        if not layer:
            return False
        source = layer.source().lower()
        return '.gpkg' in source or 'gpkg|' in source

    def _build_source_geometry_sql(
        self,
        source_wkt: str,
        source_srid: int,
        target_srid: int,
        buffer_value: Optional[float],
        buffer_expression: Optional[str] = None
    ) -> str:
        """
        Build SQL for source geometry.

        FIX v4.2.11: Added buffer_expression support for dynamic buffer.

        Args:
            source_wkt: WKT string of source geometry
            source_srid: Source SRID
            target_srid: Target SRID for transformation
            buffer_value: Static buffer value
            buffer_expression: Dynamic buffer expression (QGIS syntax)
        """
        # Escape single quotes in WKT
        escaped_wkt = source_wkt.replace("'", "''")

        # 2026-04-29 fix: ST_-prefixed function names everywhere — see comment
        # on PREDICATE_FUNCTIONS for the rationale (works on SpatiaLite and
        # GPKG-via-OGR, bare names worked only when SpatiaLite extension was
        # explicitly loaded in the GDAL stack).
        source_geom_sql = f"ST_GeomFromText('{escaped_wkt}', {source_srid})"

        # Apply MakeValid
        source_geom_sql = f"ST_MakeValid({source_geom_sql})"

        # Apply CRS transformation if needed
        if source_srid != target_srid:
            source_geom_sql = f"ST_Transform({source_geom_sql}, {target_srid})"
            self.log_info(f"Applying CRS transform: {source_srid} → {target_srid}")

        # FIX v4.2.11: Support dynamic buffer expressions
        # Priority: buffer_expression (dynamic) > buffer_value (static)
        if buffer_expression and buffer_expression.strip():
            # Convert QGIS expression to Spatialite SQL
            from .filter_executor import qgis_expression_to_spatialite
            buffer_expr_sql = qgis_expression_to_spatialite(buffer_expression)

            self.log_info(f"🔧 Applying dynamic buffer expression: {buffer_expr_sql[:100]}...")
            source_geom_sql = f"ST_Buffer({source_geom_sql}, {buffer_expr_sql})"

        # Apply static buffer
        elif buffer_value is not None and buffer_value != 0:
            source_geom_sql = f"ST_Buffer({source_geom_sql}, {buffer_value})"
            self.log_info(f"Applying buffer: {buffer_value}")

            # Wrap negative buffers in MakeValid
            if buffer_value < 0:
                source_geom_sql = f"ST_MakeValid({source_geom_sql})"

        return source_geom_sql

    def _simplify_wkt(self, wkt: str) -> str:
        """Simplify WKT geometry to reduce complexity."""
        try:
            from qgis.core import QgsGeometry
            geom = QgsGeometry.fromWkt(wkt)
            if geom and not geom.isEmpty():
                # Calculate tolerance based on bbox
                bbox = geom.boundingBox()
                tolerance = max(bbox.width(), bbox.height()) / 1000

                # Simplify
                simplified = geom.simplify(tolerance)
                if simplified and not simplified.isEmpty():
                    return simplified.asWkt()
        except Exception as e:
            self.log_warning(f"WKT simplification failed: {e}")

        return wkt

    def _is_geometric_filter(self, subset: str) -> bool:
        """Check if subset contains geometric filter patterns."""
        subset_upper = subset.upper()

        geometric_patterns = [
            'INTERSECTS',
            'CONTAINS',
            'WITHIN',
            'TOUCHES',
            'OVERLAPS',
            'CROSSES',
            'DISJOINT',
            'BUFFER',
            'GEOMFROMTEXT',
            'GEOMFROMGPB'
        ]

        return any(p in subset_upper for p in geometric_patterns)


# =============================================================================
# Exports
# =============================================================================

__all__ = [
    'SpatialiteExpressionBuilder',
    'USE_OGR_FALLBACK',
]
