# -*- coding: utf-8 -*-
"""
Multi-Step Filter Optimizer for FilterMate

Provides adaptive multi-step filtering strategies for large datasets with combined
attribute and geometric filters. Optimizes performance by reducing candidate sets
at each step before applying expensive spatial predicates.

Key Strategies:
1. ATTRIBUTE_FIRST - Apply attribute filter before geometry (fastest for selective filters)
2. BBOX_THEN_ATTRIBUTE - Bbox pre-filter, then attribute, then full predicate
3. HYBRID_PROGRESSIVE - Multi-step reduction with adaptive chunking
4. STATISTICAL_ADAPTIVE - Uses PostgreSQL statistics for optimal ordering

Performance Benefits:
- 5-20x faster on datasets >100k features with selective attribute filters
- Adaptive strategy selection based on filter selectivity estimation
- Memory-efficient progressive reduction for very large datasets

Usage:
    from ...core.strategies import (        MultiStepFilterOptimizer,
        FilterPlan,
        get_optimal_filter_plan
    )

    optimizer = MultiStepFilterOptimizer(conn, layer_props)
    plan = optimizer.create_optimal_plan(
        attribute_expr="status = 'active'",
        spatial_expr="ST_Intersects(...)",
        source_bbox=(xmin, ymin, xmax, ymax)
    )
    result = plan.execute()

v2.5.10 - Performance optimization for large datasets (January 2026)
"""

import time
import re
from typing import (
    Dict, List, Optional, Tuple, Callable
)
from dataclasses import dataclass, field
from enum import Enum, auto

from ...infrastructure.logging import get_logger

logger = get_logger(__name__)

# Centralized psycopg2 availability (v2.8.6 refactoring)
from ...infrastructure.database.postgresql_support import PSYCOPG2_AVAILABLE

# For backward compatibility
POSTGRESQL_AVAILABLE = PSYCOPG2_AVAILABLE

# Import psycopg2.sql if available
if PSYCOPG2_AVAILABLE:
    from psycopg2 import sql as psycopg2_sql
else:
    psycopg2_sql = None


class FilterStepType(Enum):
    """Types of filter steps in a multi-step plan."""
    BBOX_PREFILTER = auto()       # Fast bounding box using && operator
    ATTRIBUTE_FILTER = auto()      # Attribute/non-spatial predicates
    SPATIAL_PREDICATE = auto()     # Full spatial predicate (ST_Intersects, etc.)
    INDEX_SCAN = auto()            # Direct index scan (GiST, B-tree)
    RESULT_LIMIT = auto()          # LIMIT clause for early termination


class FilterStrategy(Enum):
    """High-level filtering strategies."""
    DIRECT = "direct"                          # Simple single-step filter
    ATTRIBUTE_FIRST = "attribute_first"        # Attribute before geometry
    BBOX_THEN_FULL = "bbox_then_full"          # Classic two-phase
    ATTRIBUTE_BBOX_SPATIAL = "attribute_bbox_spatial"  # Three-step optimal
    PROGRESSIVE_CHUNKS = "progressive_chunks"  # Chunked for very large data
    STATISTICAL_OPTIMAL = "statistical_optimal"  # PostgreSQL stats-driven


@dataclass
class FilterStepResult:
    """Result of a single filter step."""
    step_type: FilterStepType
    candidate_count: int
    execution_time_ms: float
    reduction_ratio: float  # How much this step reduced the dataset
    expression_used: str = ""

    @property
    def is_effective(self) -> bool:
        """Check if step was effective (reduced candidates significantly)."""
        return self.reduction_ratio >= 0.1  # At least 10% reduction


@dataclass
class FilterPlanResult:
    """Result of executing a complete filter plan."""
    success: bool
    feature_ids: Optional[List[int]] = None
    feature_count: int = 0
    strategy_used: FilterStrategy = FilterStrategy.DIRECT
    total_execution_time_ms: float = 0.0
    steps_executed: int = 0

    # Detailed step results
    step_results: List[FilterStepResult] = field(default_factory=list)

    # Statistics
    initial_estimate: int = 0
    final_count: int = 0
    overall_reduction_ratio: float = 0.0
    memory_saved_estimate_mb: float = 0.0

    error: Optional[str] = None

    def get_performance_summary(self) -> str:
        """Get human-readable performance summary."""
        if not self.success:
            return f"Failed: {self.error}"

        lines = [
            f"Strategy: {self.strategy_used.value}",
            f"Steps executed: {self.steps_executed}",
            f"Total time: {self.total_execution_time_ms:.1f}ms",
            f"Results: {self.final_count:,} features",
            f"Reduction: {self.overall_reduction_ratio:.1%}",
        ]

        for i, step in enumerate(self.step_results, 1):
            lines.append(
                f"  Step {i} ({step.step_type.name}): "
                f"{step.candidate_count:,} candidates, "
                f"{step.execution_time_ms:.1f}ms, "
                f"reduction {step.reduction_ratio:.1%}"
            )

        return "\n".join(lines)


@dataclass
class FilterStep:
    """Definition of a single filter step."""
    step_type: FilterStepType
    expression: str
    priority: int = 0  # Lower = execute first
    estimated_selectivity: float = 1.0  # 0.0-1.0, lower = more selective
    requires_previous_ids: bool = False  # If True, uses IDs from previous step
    chunk_size: int = 10000  # For chunked processing


@dataclass
class LayerStatistics:
    """PostgreSQL layer statistics for optimization."""
    table_name: str
    schema: str = "public"
    estimated_rows: int = 0
    has_spatial_index: bool = True
    has_attribute_indexes: Dict[str, bool] = field(default_factory=dict)
    column_statistics: Dict[str, Dict] = field(default_factory=dict)
    bbox: Optional[Tuple[float, float, float, float]] = None
    last_analyzed: Optional[str] = None

    @classmethod
    def from_postgresql(cls, conn, schema: str, table: str) -> 'LayerStatistics':
        """Fetch statistics from PostgreSQL system catalogs."""
        stats = cls(table_name=table, schema=schema)

        try:
            with conn.cursor() as cur:
                # Get row estimate
                cur.execute("""
                    SELECT reltuples::bigint
                    FROM pg_class c
                    JOIN pg_namespace n ON n.oid = c.relnamespace
                    WHERE n.nspname = %s AND c.relname = %s
                """, (schema, table))
                row = cur.fetchone()
                if row:
                    stats.estimated_rows = max(0, int(row[0]))

                # Check for spatial index
                cur.execute("""
                    SELECT COUNT(*) > 0
                    FROM pg_indexes
                    WHERE schemaname = %s AND tablename = %s
                    AND indexdef ILIKE '%%gist%%'
                """, (schema, table))
                row = cur.fetchone()
                stats.has_spatial_index = bool(row and row[0])

                # Get column statistics for selectivity estimation
                cur.execute("""
                    SELECT attname, n_distinct, null_frac
                    FROM pg_stats
                    WHERE schemaname = %s AND tablename = %s
                """, (schema, table))
                for row in cur.fetchall():
                    col_name, n_distinct, null_frac = row
                    stats.column_statistics[col_name] = {
                        'n_distinct': n_distinct,
                        'null_frac': null_frac or 0.0
                    }

                # Get table bbox from geometry_columns if available
                cur.execute("""
                    SELECT ST_XMin(bbox), ST_YMin(bbox), ST_XMax(bbox), ST_YMax(bbox)
                    FROM (
                        SELECT ST_EstimatedExtent(%s, %s, 'geom') as bbox
                    ) sub
                    WHERE bbox IS NOT NULL
                """, (schema, table))
                row = cur.fetchone()
                if row and all(v is not None for v in row):
                    stats.bbox = tuple(row)

        except Exception as e:
            logger.debug(f"Could not fetch full statistics for {schema}.{table}: {e}")

        return stats


class SelectivityEstimator:
    """
    Estimates filter selectivity for query optimization.

    Selectivity = fraction of rows that pass the filter (0.0 to 1.0).
    Lower selectivity = more rows filtered out = more efficient to apply first.
    """

    # Default selectivity estimates for common operators
    DEFAULT_SELECTIVITY = {
        '=': 0.01,      # Equality on indexed column
        '<>': 0.99,     # Not equal (most rows pass)
        '>': 0.33,      # Range predicates
        '<': 0.33,
        '>=': 0.33,
        '<=': 0.33,
        'BETWEEN': 0.10,
        'IN': 0.05,     # IN list
        'LIKE': 0.25,   # Wildcard search
        'ILIKE': 0.25,
        'IS NULL': 0.05,
        'IS NOT NULL': 0.95,
        # Spatial predicates
        'ST_Intersects': 0.10,  # Depends heavily on geometry
        'ST_Contains': 0.05,
        'ST_Within': 0.05,
        'ST_DWithin': 0.15,
        '&&': 0.20,  # Bbox operator
    }

    def __init__(self, layer_stats: Optional[LayerStatistics] = None):
        """Initialize estimator with optional layer statistics."""
        self.layer_stats = layer_stats

    def estimate_attribute_selectivity(
        self,
        expression: str,
        column_stats: Optional[Dict] = None
    ) -> float:
        """
        Estimate selectivity for an attribute expression.

        Args:
            expression: SQL WHERE clause fragment
            column_stats: Column statistics from pg_stats

        Returns:
            Estimated selectivity (0.0 to 1.0)
        """
        expr_upper = expression.upper()

        # Check for equality conditions
        if '=' in expression and '<>' not in expression:
            # Try to use column statistics for better estimate
            if column_stats:
                n_distinct = column_stats.get('n_distinct', 100)
                if n_distinct > 0:
                    return 1.0 / n_distinct
                elif n_distinct < 0:
                    # Negative n_distinct means fraction of rows
                    return min(0.5, -n_distinct)
            return self.DEFAULT_SELECTIVITY['=']

        # Check for IN clauses
        in_match = re.search(r'\bIN\s*\(\s*([^)]+)\s*\)', expr_upper)
        if in_match:
            # Estimate based on number of values in IN list
            values = in_match.group(1).split(',')
            return min(0.5, len(values) * self.DEFAULT_SELECTIVITY['IN'])

        # Check for BETWEEN
        if 'BETWEEN' in expr_upper:
            return self.DEFAULT_SELECTIVITY['BETWEEN']

        # Check for IS NULL / IS NOT NULL
        if 'IS NOT NULL' in expr_upper:
            if column_stats:
                return 1.0 - column_stats.get('null_frac', 0.05)
            return self.DEFAULT_SELECTIVITY['IS NOT NULL']
        if 'IS NULL' in expr_upper:
            if column_stats:
                return column_stats.get('null_frac', 0.05)
            return self.DEFAULT_SELECTIVITY['IS NULL']

        # Check for LIKE/ILIKE
        if 'ILIKE' in expr_upper or 'LIKE' in expr_upper:
            # Prefix-anchored LIKE is more selective
            if re.search(r"LIKE\s+'[^%]", expr_upper):
                return 0.05  # Prefix match
            return self.DEFAULT_SELECTIVITY['LIKE']

        # Check for range operators
        for op in ['>=', '<=', '<>', '>', '<']:
            if op in expression:
                return self.DEFAULT_SELECTIVITY.get(op, 0.33)

        # Default fallback
        return 0.5

    def estimate_spatial_selectivity(
        self,
        source_bbox: Tuple[float, float, float, float],
        layer_bbox: Optional[Tuple[float, float, float, float]] = None,
        predicate: str = 'ST_Intersects'
    ) -> float:
        """
        Estimate selectivity for spatial predicate based on bbox overlap.

        Args:
            source_bbox: Bounding box of source geometry
            layer_bbox: Bounding box of target layer
            predicate: Spatial predicate name

        Returns:
            Estimated selectivity (0.0 to 1.0)
        """
        if layer_bbox is None:
            # Use default estimate
            return self.DEFAULT_SELECTIVITY.get(predicate, 0.10)

        # Calculate bbox overlap ratio
        src_xmin, src_ymin, src_xmax, src_ymax = source_bbox
        lyr_xmin, lyr_ymin, lyr_xmax, lyr_ymax = layer_bbox

        # Calculate intersection
        int_xmin = max(src_xmin, lyr_xmin)
        int_ymin = max(src_ymin, lyr_ymin)
        int_xmax = min(src_xmax, lyr_xmax)
        int_ymax = min(src_ymax, lyr_ymax)

        if int_xmax <= int_xmin or int_ymax <= int_ymin:
            return 0.0  # No overlap

        # Calculate areas
        (src_xmax - src_xmin) * (src_ymax - src_ymin)
        lyr_area = (lyr_xmax - lyr_xmin) * (lyr_ymax - lyr_ymin)
        int_area = (int_xmax - int_xmin) * (int_ymax - int_ymin)

        if lyr_area <= 0:
            return 0.5

        # Selectivity is roughly intersection/layer ratio
        # Apply predicate-specific adjustment
        base_selectivity = int_area / lyr_area

        # Adjust based on predicate type
        predicate_upper = predicate.upper()
        if 'CONTAINS' in predicate_upper or 'WITHIN' in predicate_upper:
            base_selectivity *= 0.5  # These are more restrictive
        elif 'TOUCHES' in predicate_upper:
            base_selectivity *= 0.1  # Very restrictive (boundary only)
        elif 'DISJOINT' in predicate_upper:
            base_selectivity = 1.0 - base_selectivity

        return max(0.001, min(1.0, base_selectivity))

    def estimate_combined_selectivity(
        self,
        attribute_expr: Optional[str],
        spatial_expr: Optional[str],
        source_bbox: Optional[Tuple[float, float, float, float]] = None
    ) -> Tuple[float, float]:
        """
        Estimate selectivity for both attribute and spatial components.

        Returns:
            (attribute_selectivity, spatial_selectivity)
        """
        attr_sel = 1.0
        if attribute_expr:
            attr_sel = self.estimate_attribute_selectivity(attribute_expr)

        spatial_sel = 1.0
        if spatial_expr and source_bbox:
            layer_bbox = self.layer_stats.bbox if self.layer_stats else None
            spatial_sel = self.estimate_spatial_selectivity(
                source_bbox, layer_bbox, spatial_expr
            )

        return (attr_sel, spatial_sel)


class FilterPlanBuilder:
    """
    Builds optimal filter execution plans based on query analysis.

    Uses selectivity estimates to determine the most efficient
    ordering of filter steps.
    """

    # Thresholds for strategy selection
    SMALL_DATASET = 10000
    MEDIUM_DATASET = 100000
    LARGE_DATASET = 500000
    VERY_LARGE_DATASET = 1000000

    # Selectivity thresholds
    HIGH_SELECTIVITY = 0.1  # Filters out >90% of rows
    MEDIUM_SELECTIVITY = 0.3

    def __init__(
        self,
        layer_stats: Optional[LayerStatistics] = None,
        estimator: Optional[SelectivityEstimator] = None
    ):
        """Initialize plan builder."""
        self.layer_stats = layer_stats
        self.estimator = estimator or SelectivityEstimator(layer_stats)

    def build_optimal_plan(
        self,
        attribute_expr: Optional[str] = None,
        spatial_expr: Optional[str] = None,
        source_bbox: Optional[Tuple[float, float, float, float]] = None,
        feature_count: int = 0
    ) -> Tuple[FilterStrategy, List[FilterStep]]:
        """
        Build optimal filter plan based on expressions and statistics.

        Args:
            attribute_expr: Attribute/non-spatial WHERE clause
            spatial_expr: Spatial predicate expression
            source_bbox: Bounding box of source geometry
            feature_count: Estimated row count (uses stats if 0)

        Returns:
            (strategy, list of filter steps in execution order)
        """
        if feature_count == 0 and self.layer_stats:
            feature_count = self.layer_stats.estimated_rows

        # Estimate selectivities
        attr_sel, spatial_sel = self.estimator.estimate_combined_selectivity(
            attribute_expr, spatial_expr, source_bbox
        )

        logger.debug(
            f"Selectivity estimates: attribute={attr_sel:.3f}, spatial={spatial_sel:.3f}, "
            f"rows={feature_count:,}"
        )

        steps = []

        # ===== Small datasets: Direct single-step =====
        if feature_count < self.SMALL_DATASET:
            return self._build_direct_plan(attribute_expr, spatial_expr)

        # ===== Determine optimal ordering =====
        has_attribute = attribute_expr is not None and attribute_expr.strip()
        has_spatial = spatial_expr is not None and spatial_expr.strip()
        has_bbox = source_bbox is not None

        # Calculate estimated intermediate sizes
        after_attribute = int(feature_count * attr_sel) if has_attribute else feature_count
        after_bbox = int(feature_count * min(spatial_sel * 3, 1.0)) if has_bbox else feature_count  # Bbox is less precise

        # ===== Strategy selection based on selectivity =====

        # Case 1: Highly selective attribute filter
        if has_attribute and attr_sel < self.HIGH_SELECTIVITY:
            # Attribute first is likely most efficient
            steps.append(FilterStep(
                step_type=FilterStepType.ATTRIBUTE_FILTER,
                expression=attribute_expr,
                priority=1,
                estimated_selectivity=attr_sel
            ))

            if has_bbox and after_attribute > 1000:
                # Add bbox filter on reduced set
                steps.append(FilterStep(
                    step_type=FilterStepType.BBOX_PREFILTER,
                    expression=self._build_bbox_expression(source_bbox),
                    priority=2,
                    estimated_selectivity=spatial_sel,
                    requires_previous_ids=True
                ))

            if has_spatial:
                steps.append(FilterStep(
                    step_type=FilterStepType.SPATIAL_PREDICATE,
                    expression=spatial_expr,
                    priority=3,
                    estimated_selectivity=spatial_sel,
                    requires_previous_ids=True
                ))

            return (FilterStrategy.ATTRIBUTE_FIRST, steps)

        # Case 2: Bbox is effective and attribute is not very selective
        if has_bbox and (not has_attribute or attr_sel > self.MEDIUM_SELECTIVITY):
            # Bbox first
            steps.append(FilterStep(
                step_type=FilterStepType.BBOX_PREFILTER,
                expression=self._build_bbox_expression(source_bbox),
                priority=1,
                estimated_selectivity=spatial_sel * 3  # Bbox is less precise
            ))

            if has_attribute and after_bbox > 1000:
                steps.append(FilterStep(
                    step_type=FilterStepType.ATTRIBUTE_FILTER,
                    expression=attribute_expr,
                    priority=2,
                    estimated_selectivity=attr_sel,
                    requires_previous_ids=True
                ))

            if has_spatial:
                steps.append(FilterStep(
                    step_type=FilterStepType.SPATIAL_PREDICATE,
                    expression=spatial_expr,
                    priority=3,
                    estimated_selectivity=spatial_sel,
                    requires_previous_ids=True
                ))

            return (FilterStrategy.BBOX_THEN_FULL, steps)

        # Case 3: Both filters have similar selectivity - use three-step
        if has_attribute and has_bbox and has_spatial:
            # Order by estimated reduction
            if attr_sel < spatial_sel:
                # Attribute, bbox, spatial
                steps = [
                    FilterStep(
                        step_type=FilterStepType.ATTRIBUTE_FILTER,
                        expression=attribute_expr,
                        priority=1,
                        estimated_selectivity=attr_sel
                    ),
                    FilterStep(
                        step_type=FilterStepType.BBOX_PREFILTER,
                        expression=self._build_bbox_expression(source_bbox),
                        priority=2,
                        estimated_selectivity=spatial_sel * 3,
                        requires_previous_ids=True
                    ),
                    FilterStep(
                        step_type=FilterStepType.SPATIAL_PREDICATE,
                        expression=spatial_expr,
                        priority=3,
                        estimated_selectivity=spatial_sel,
                        requires_previous_ids=True
                    )
                ]
            else:
                # Bbox, attribute, spatial
                steps = [
                    FilterStep(
                        step_type=FilterStepType.BBOX_PREFILTER,
                        expression=self._build_bbox_expression(source_bbox),
                        priority=1,
                        estimated_selectivity=spatial_sel * 3
                    ),
                    FilterStep(
                        step_type=FilterStepType.ATTRIBUTE_FILTER,
                        expression=attribute_expr,
                        priority=2,
                        estimated_selectivity=attr_sel,
                        requires_previous_ids=True
                    ),
                    FilterStep(
                        step_type=FilterStepType.SPATIAL_PREDICATE,
                        expression=spatial_expr,
                        priority=3,
                        estimated_selectivity=spatial_sel,
                        requires_previous_ids=True
                    )
                ]

            return (FilterStrategy.ATTRIBUTE_BBOX_SPATIAL, steps)

        # Fallback: Direct execution
        return self._build_direct_plan(attribute_expr, spatial_expr)

    def _build_direct_plan(
        self,
        attribute_expr: Optional[str],
        spatial_expr: Optional[str]
    ) -> Tuple[FilterStrategy, List[FilterStep]]:
        """Build simple direct execution plan."""
        combined = []
        if attribute_expr:
            combined.append(attribute_expr)
        if spatial_expr:
            combined.append(spatial_expr)

        expr = " AND ".join(f"({e})" for e in combined) if combined else "TRUE"

        return (FilterStrategy.DIRECT, [
            FilterStep(
                step_type=FilterStepType.SPATIAL_PREDICATE if spatial_expr else FilterStepType.ATTRIBUTE_FILTER,
                expression=expr,
                priority=1
            )
        ])

    def _build_bbox_expression(
        self,
        bbox: Tuple[float, float, float, float],
        geom_column: str = "geom",
        srid: int = 4326
    ) -> str:
        """Build PostGIS bounding box filter expression."""
        xmin, ymin, xmax, ymax = bbox
        return (
            f'"{geom_column}" && '
            f"ST_MakeEnvelope({xmin}, {ymin}, {xmax}, {ymax}, {srid})"
        )


class MultiStepFilterExecutor:
    """
    Executes multi-step filter plans efficiently.

    Handles:
    - Intermediate result caching
    - Chunked processing for large candidate sets
    - Progress reporting
    - Error recovery
    """

    DEFAULT_CHUNK_SIZE = 10000
    MAX_IN_CLAUSE_SIZE = 50000

    def __init__(
        self,
        connection,
        schema: str = "public",
        table: str = "",
        primary_key: str = "id",
        geometry_column: str = "geom",
        srid: int = 4326,
        chunk_size: int = None
    ):
        """Initialize executor."""
        self.connection = connection
        self.schema = schema
        self.table = table
        self.primary_key = primary_key
        self.geometry_column = geometry_column
        self.srid = srid
        self.chunk_size = chunk_size or self.DEFAULT_CHUNK_SIZE

    def execute_plan(
        self,
        steps: List[FilterStep],
        progress_callback: Optional[Callable[[str, int, int], None]] = None
    ) -> FilterPlanResult:
        """
        Execute a filter plan and return results.

        Args:
            steps: List of filter steps to execute
            progress_callback: Callback(step_name, current, total)

        Returns:
            FilterPlanResult with feature IDs and statistics
        """
        start_time = time.time()
        result = FilterPlanResult(success=True)
        result.steps_executed = 0

        candidate_ids = None  # None means "all rows"
        current_count = 0

        try:
            # Get initial estimate
            result.initial_estimate = self._get_row_count_estimate()
            current_count = result.initial_estimate

            # Execute each step
            for i, step in enumerate(steps):
                step_start = time.time()

                if progress_callback:
                    progress_callback(step.step_type.name, i + 1, len(steps))

                logger.info(
                    f"📍 Step {i + 1}/{len(steps)}: {step.step_type.name} "
                    f"(candidates: {current_count:,})"
                )

                # Execute step
                new_ids = self._execute_step(step, candidate_ids)

                # Calculate statistics
                step_time = (time.time() - step_start) * 1000
                new_count = len(new_ids) if new_ids is not None else current_count
                reduction = 1.0 - (new_count / max(1, current_count))

                step_result = FilterStepResult(
                    step_type=step.step_type,
                    candidate_count=new_count,
                    execution_time_ms=step_time,
                    reduction_ratio=reduction,
                    expression_used=step.expression[:100] + "..." if len(step.expression) > 100 else step.expression
                )
                result.step_results.append(step_result)
                result.steps_executed += 1

                logger.info(
                    f"   → {new_count:,} candidates remaining "
                    f"({reduction:.1%} reduction, {step_time:.1f}ms)"
                )

                # Update for next iteration
                candidate_ids = new_ids
                current_count = new_count

                # Early termination if no candidates left
                if new_count == 0:
                    logger.info("   → No candidates remaining, stopping early")
                    break

            # Finalize result
            result.feature_ids = candidate_ids or []
            result.feature_count = len(result.feature_ids)
            result.final_count = result.feature_count
            result.total_execution_time_ms = (time.time() - start_time) * 1000
            result.overall_reduction_ratio = (
                1.0 - (result.final_count / max(1, result.initial_estimate))
            )

            # Estimate memory saved (vs loading all features)
            result.memory_saved_estimate_mb = (
                (result.initial_estimate - result.final_count) * 100
            ) / (1024 * 1024)

        except Exception as e:
            result.success = False
            result.error = str(e)
            result.total_execution_time_ms = (time.time() - start_time) * 1000
            logger.error(f"Multi-step filter error: {e}")
            import traceback
            logger.debug(traceback.format_exc())

        return result

    def _execute_step(
        self,
        step: FilterStep,
        candidate_ids: Optional[List[int]]
    ) -> List[int]:
        """Execute a single filter step."""

        if step.step_type == FilterStepType.BBOX_PREFILTER:
            return self._execute_bbox_step(step, candidate_ids)
        elif step.step_type == FilterStepType.ATTRIBUTE_FILTER:
            return self._execute_attribute_step(step, candidate_ids)
        elif step.step_type == FilterStepType.SPATIAL_PREDICATE:
            return self._execute_spatial_step(step, candidate_ids)
        else:
            raise ValueError(f"Unknown step type: {step.step_type}")

    def _execute_bbox_step(
        self,
        step: FilterStep,
        candidate_ids: Optional[List[int]]
    ) -> List[int]:
        """Execute bounding box pre-filter."""
        if candidate_ids is not None and len(candidate_ids) == 0:
            return []

        if candidate_ids is not None and len(candidate_ids) <= self.MAX_IN_CLAUSE_SIZE:
            # Filter within candidate set
            ','.join(str(id) for id in candidate_ids)
            query = """
                SELECT "{self.primary_key}"
                FROM "{self.schema}"."{self.table}"
                WHERE "{self.primary_key}" IN ({ids_str})
                  AND {step.expression}
            """
        else:
            # Full table scan with bbox
            query = """
                SELECT "{self.primary_key}"
                FROM "{self.schema}"."{self.table}"
                WHERE {step.expression}
            """

        return self._fetch_ids(query)

    def _execute_attribute_step(
        self,
        step: FilterStep,
        candidate_ids: Optional[List[int]]
    ) -> List[int]:
        """Execute attribute filter."""
        if candidate_ids is not None and len(candidate_ids) == 0:
            return []

        if candidate_ids is not None:
            # Process in chunks if needed
            if len(candidate_ids) > self.MAX_IN_CLAUSE_SIZE:
                return self._execute_chunked(step, candidate_ids)

            ','.join(str(id) for id in candidate_ids)
            query = """
                SELECT "{self.primary_key}"
                FROM "{self.schema}"."{self.table}"
                WHERE "{self.primary_key}" IN ({ids_str})
                  AND ({step.expression})
            """
        else:
            query = """
                SELECT "{self.primary_key}"
                FROM "{self.schema}"."{self.table}"
                WHERE {step.expression}
            """

        return self._fetch_ids(query)

    def _execute_spatial_step(
        self,
        step: FilterStep,
        candidate_ids: Optional[List[int]]
    ) -> List[int]:
        """Execute spatial predicate filter."""
        if candidate_ids is not None and len(candidate_ids) == 0:
            return []

        if candidate_ids is not None:
            if len(candidate_ids) > self.MAX_IN_CLAUSE_SIZE:
                return self._execute_chunked(step, candidate_ids)

            ','.join(str(id) for id in candidate_ids)
            query = """
                SELECT "{self.primary_key}"
                FROM "{self.schema}"."{self.table}"
                WHERE "{self.primary_key}" IN ({ids_str})
                  AND ({step.expression})
            """
        else:
            query = """
                SELECT "{self.primary_key}"
                FROM "{self.schema}"."{self.table}"
                WHERE {step.expression}
            """

        return self._fetch_ids(query)

    def _execute_chunked(
        self,
        step: FilterStep,
        candidate_ids: List[int]
    ) -> List[int]:
        """Execute step in chunks for very large candidate sets."""
        results = []

        for i in range(0, len(candidate_ids), self.chunk_size):
            chunk = candidate_ids[i:i + self.chunk_size]
            ','.join(str(id) for id in chunk)

            query = """
                SELECT "{self.primary_key}"
                FROM "{self.schema}"."{self.table}"
                WHERE "{self.primary_key}" IN ({ids_str})
                  AND ({step.expression})
            """

            chunk_results = self._fetch_ids(query)
            results.extend(chunk_results)

        return results

    def _fetch_ids(self, query: str) -> List[int]:
        """Execute query and fetch result IDs."""
        with self.connection.cursor() as cur:
            cur.execute(query)
            return [row[0] for row in cur.fetchall()]

    def _get_row_count_estimate(self) -> int:
        """Get estimated row count from PostgreSQL statistics."""
        try:
            with self.connection.cursor() as cur:
                cur.execute("""
                    SELECT reltuples::bigint
                    FROM pg_class c
                    JOIN pg_namespace n ON n.oid = c.relnamespace
                    WHERE n.nspname = %s AND c.relname = %s
                """, (self.schema, self.table))
                row = cur.fetchone()
                if row:
                    return max(0, int(row[0]))
        except Exception:  # nosec B110 - best-effort estimate, falls back below
            pass
        return 0


class MultiStepFilterOptimizer:
    """
    Main entry point for multi-step filter optimization.

    Combines statistics gathering, plan building, and execution.

    Usage:
        optimizer = MultiStepFilterOptimizer(conn, layer_props)
        result = optimizer.filter_optimal(
            attribute_expr="status = 'active'",
            spatial_expr="ST_Intersects(geom, ST_GeomFromText('...'))",
            source_bbox=(0, 0, 100, 100)
        )
    """

    def __init__(
        self,
        connection,
        layer_props: Dict,
        use_statistics: bool = True
    ):
        """
        Initialize optimizer.

        Args:
            connection: psycopg2 database connection
            layer_props: Layer properties dictionary
            use_statistics: Whether to fetch PostgreSQL statistics
        """
        self.connection = connection
        self.schema = layer_props.get('layer_schema', layer_props.get('schema', 'public'))
        self.table = layer_props.get('layer_table_name', layer_props.get('table', ''))
        self.primary_key = layer_props.get('layer_pk', layer_props.get('primary_key', 'id'))
        self.geometry_column = layer_props.get('layer_geometry_field', layer_props.get('geometry_column', 'geom'))
        self.srid = layer_props.get('layer_srid', layer_props.get('srid', 4326))

        # Fetch statistics if requested
        self.layer_stats = None
        if use_statistics and POSTGRESQL_AVAILABLE:
            self.layer_stats = LayerStatistics.from_postgresql(
                connection, self.schema, self.table
            )
            logger.debug(f"Layer statistics: {self.layer_stats.estimated_rows:,} rows estimated")

        # Initialize components
        self.estimator = SelectivityEstimator(self.layer_stats)
        self.plan_builder = FilterPlanBuilder(self.layer_stats, self.estimator)
        self.executor = MultiStepFilterExecutor(
            connection,
            self.schema,
            self.table,
            self.primary_key,
            self.geometry_column,
            self.srid
        )

    def filter_optimal(
        self,
        attribute_expr: Optional[str] = None,
        spatial_expr: Optional[str] = None,
        source_bbox: Optional[Tuple[float, float, float, float]] = None,
        progress_callback: Optional[Callable] = None
    ) -> FilterPlanResult:
        """
        Execute optimal multi-step filter.

        Args:
            attribute_expr: Attribute/non-spatial WHERE clause
            spatial_expr: Spatial predicate expression
            source_bbox: Bounding box of source geometry
            progress_callback: Progress reporting callback

        Returns:
            FilterPlanResult with feature IDs and statistics
        """
        # Build optimal plan
        feature_count = self.layer_stats.estimated_rows if self.layer_stats else 0
        strategy, steps = self.plan_builder.build_optimal_plan(
            attribute_expr, spatial_expr, source_bbox, feature_count
        )

        logger.info(f"📊 Selected strategy: {strategy.value} with {len(steps)} steps")

        # Execute plan
        result = self.executor.execute_plan(steps, progress_callback)
        result.strategy_used = strategy

        logger.info(f"📈 Filter complete: {result.get_performance_summary()}")

        return result

    def create_optimal_plan(
        self,
        attribute_expr: Optional[str] = None,
        spatial_expr: Optional[str] = None,
        source_bbox: Optional[Tuple[float, float, float, float]] = None
    ) -> Tuple[FilterStrategy, List[FilterStep]]:
        """
        Create optimal plan without executing (for inspection).

        Returns:
            (strategy, steps)
        """
        feature_count = self.layer_stats.estimated_rows if self.layer_stats else 0
        return self.plan_builder.build_optimal_plan(
            attribute_expr, spatial_expr, source_bbox, feature_count
        )


# Convenience function
def get_optimal_filter_plan(
    connection,
    layer_props: Dict,
    attribute_expr: Optional[str] = None,
    spatial_expr: Optional[str] = None,
    source_bbox: Optional[Tuple[float, float, float, float]] = None
) -> FilterPlanResult:
    """
    Convenience function for optimal filtering.

    Usage:
        result = get_optimal_filter_plan(
            conn,
            {'schema': 'public', 'table': 'buildings', 'primary_key': 'gid'},
            attribute_expr="status = 'active'",
            spatial_expr="ST_Intersects(geom, ...)",
            source_bbox=(0, 0, 100, 100)
        )
    """
    optimizer = MultiStepFilterOptimizer(connection, layer_props)
    return optimizer.filter_optimal(
        attribute_expr, spatial_expr, source_bbox
    )
