"""
Time-based execution plan comparison evaluator
Compares the same SQL plan differences across different time periods or schemas
"""

import argparse
import datetime
from typing import Any, Dict, List, Optional, Tuple

from evaluation import EvaluationMetrics, compare_plans, parse_text
from evaluation.config import EvaluationConfig, TargetConfig, get_default_config
from evaluation.db_connector import execute_query
from evaluation.plan_evaluator import EvaluationResult, PlanEntry, PlanEvaluator


class WorkloadPlanEntry(PlanEntry):
    """Workload plan entry, uses DIGEST_TEXT as grouping key instead of SQL text"""

    def __init__(
        self,
        sql: str,
        plan_text: str,
        version: str = "v4",
        schema: str = "",
        digest_text: str = "",
        metadata: Dict = None,
    ):
        super().__init__(sql, plan_text, version, schema, metadata or {})
        self.digest_text = digest_text

    def get_sql_digest(self) -> str:
        """Use database DIGEST_TEXT as grouping key to ensure SQLs with same semantics can be compared"""
        return self.digest_text if self.digest_text else super().get_sql_digest()


class WorkloadPlanEvaluator(PlanEvaluator):
    """Workload execution plan comparison evaluator
    Compares the same SQL plan differences across different workloads/schemas
    """

    def __init__(
        self,
        real_workload_config: Optional[TargetConfig],
        synthetic_workload_config: Optional[TargetConfig],
        evaluation_config: Optional[EvaluationConfig] = None,
    ):
        super().__init__()

        # Validate workload configuration: either both provided or both None
        if (real_workload_config is None) != (synthetic_workload_config is None):
            raise ValueError(
                "Workload configurations must be either both provided (for comparison) "
                "or both None (not supported currently). Mixed configuration is not supported."
            )

        if real_workload_config is None and synthetic_workload_config is None:
            raise ValueError(
                "Both workload configurations are None. Please provide real_workload_config and synthetic_workload_config."
            )

        self.real_workload_config = real_workload_config
        self.synthetic_workload_config = synthetic_workload_config
        self.evaluation_config = evaluation_config or get_default_config()

    def analyze_sql_plan_differences(
        self, generate_report=True, limit_sqls: Optional[int] = None
    ) -> EvaluationResult:
        """Analyze performance and plan differences for the same SQL across different time periods"""
        print("=== Execution Plan Analysis ===")
        if (
            self.real_workload_config.start_time
            and self.synthetic_workload_config.start_time
        ):
            print("Comparing two time periods:")
        else:
            print("Comparing two workloads (schema-based):")
        print(f"Real workload: {self.real_workload_config.label}")
        print(f"Synthetic workload: {self.synthetic_workload_config.label}")

        # 1. Find common SQLs and load all plan data in optimized batch queries
        print("Finding common SQLs and loading plan data...")
        common_sqls, plans1, plans2 = self._find_common_sqls_with_plans(limit_sqls)
        print(f"Found {len(common_sqls)} common SQL patterns")

        if not common_sqls:
            print("No common SQLs found, exiting analysis")
            return self._create_empty_result()

        print(f"Real workload loaded {len(plans1)} plans")
        print(f"Synthetic workload loaded {len(plans2)} plans")

        # 3. Perform detailed comparisons (maintain original comparison logic)
        result = self._perform_detailed_comparisons(plans1, plans2, common_sqls)

        # 5. Print console output (maintain original format)
        self._print_analysis_summary(result, common_sqls)

        # 5.1 Print correlation analysis
        self._print_correlation_analysis(result)

        # 6. Generate report
        if generate_report:
            report_path = self._generate_time_based_report(result, common_sqls)
            print(f"\n📄 Detailed analysis report generated: {report_path}")

        return result

    def _find_common_sqls_with_plans(
        self, limit: Optional[int] = None
    ) -> Tuple[List[Dict[str, Any]], List[PlanEntry], List[PlanEntry]]:
        """Find common SQLs and fetch all plan data - supports hybrid data sources"""

        # Check if we need hybrid queries (different data sources)
        if (
            self.real_workload_config.is_sqlite
            != self.synthetic_workload_config.is_sqlite
        ):
            return self._find_common_sqls_hybrid(limit)
        else:
            # Both are same data source type, use original logic
            return self._find_common_sqls_single_source(limit)

    def _find_common_sqls_hybrid(
        self, limit: Optional[int] = None
    ) -> Tuple[List[Dict[str, Any]], List[PlanEntry], List[PlanEntry]]:
        """Find common SQLs between SQLite and TiDB data sources"""

        # Fetch data from both sources separately
        plans1, results1 = self._fetch_plans_from_source(self.real_workload_config)
        plans2, results2 = self._fetch_plans_from_source(self.synthetic_workload_config)

        if not results1 or not results2:
            return [], [], []

        # Create digest mappings
        digest_to_result1 = {r["digest_text"]: r for r in results1}
        digest_to_result2 = {r["digest_text"]: r for r in results2}

        # Find common digests
        common_digests = set(digest_to_result1.keys()) & set(digest_to_result2.keys())

        if not common_digests:
            return [], [], []

        # Apply limit if specified
        if limit:
            common_digests = sorted(common_digests)[:limit]

        # Build results
        common_sqls = []
        common_plans1 = []
        common_plans2 = []

        for digest_text in common_digests:
            result1 = digest_to_result1[digest_text]
            result2 = digest_to_result2[digest_text]

            # Calculate latency ratio (handle type conversion)
            latency_ratio = (
                float(result2["avg_latency"]) / float(result1["avg_latency"])
                if result1["avg_latency"] > 0
                else float("inf")
            )

            # Build common SQL performance data
            common_sql = {
                "DIGEST_TEXT": digest_text,
                "p1_exec_count": result1["exec_count"],
                "p1_avg_latency": result1["avg_latency"],
                "p2_exec_count": result2["exec_count"],
                "p2_avg_latency": result2["avg_latency"],
                "latency_ratio": latency_ratio,
            }
            common_sqls.append(common_sql)

            # Create plan entries for real workload
            if result1["query_sample_text"] and result1["plan"]:
                plan1 = WorkloadPlanEntry(
                    sql=result1["query_sample_text"],
                    plan_text=result1["plan"],
                    version="v4",
                    schema=self.real_workload_config.schema,
                    digest_text=digest_text,
                    metadata={
                        "digest_text": digest_text,
                        "exec_count": result1["exec_count"],
                        "avg_latency": result1["avg_latency"],
                        "workload_label": self.real_workload_config.label,
                    },
                )
                common_plans1.append(plan1)

            # Create plan entries for synthetic workload
            if result2["query_sample_text"] and result2["plan"]:
                plan2 = WorkloadPlanEntry(
                    sql=result2["query_sample_text"],
                    plan_text=result2["plan"],
                    version="v4",
                    schema=self.synthetic_workload_config.schema,
                    digest_text=digest_text,
                    metadata={
                        "digest_text": digest_text,
                        "exec_count": result2["exec_count"],
                        "avg_latency": result2["avg_latency"],
                        "workload_label": self.synthetic_workload_config.label,
                    },
                )
                common_plans2.append(plan2)

        return common_sqls, common_plans1, common_plans2

    def _fetch_plans_from_source(
        self, target_config: TargetConfig
    ) -> Tuple[List[PlanEntry], List[Dict]]:
        """Fetch plans from a specific data source (SQLite or TiDB)"""

        if target_config.is_sqlite:
            return self._fetch_plans_from_sqlite(target_config)
        else:
            return self._fetch_plans_from_tidb(target_config)

    def _fetch_plans_from_sqlite(
        self, target_config: TargetConfig
    ) -> Tuple[List[PlanEntry], List[Dict]]:
        """Fetch plans from SQLite database"""
        from evaluation.sqlite_connector import SQLiteConnector

        # Build query for SQLite
        columns = self.evaluation_config.get_column_mapping("sqlite")

        conditions = []
        params = {}

        # Schema condition
        if target_config.schema:
            conditions.append(f"{columns['schema_name']} = :schema")
            params["schema"] = target_config.schema

        # Time conditions
        if target_config.start_time and target_config.end_time:
            conditions.extend(
                [
                    f"{columns['start_time']} >= :start_time",
                    f"{columns['end_time']} <= :end_time",
                ]
            )
            params["start_time"] = target_config.start_time
            params["end_time"] = target_config.end_time

        # Plan condition
        conditions.append(f"{columns['plan_column']} IS NOT NULL")
        conditions.append(f"{columns['plan_column']} != ''")

        where_clause = "WHERE " + " AND ".join(conditions)

        query = f"""
            SELECT
                {columns['digest_text']} as digest_text,
                {columns['query_sample']} as query_sample_text,
                {columns['plan_column']} as plan,
                SUM({columns['exec_count']}) as exec_count,
                AVG({columns['avg_latency']}) as avg_latency
            FROM {target_config.table_name}
            {where_clause}
            GROUP BY {columns['digest_text']}
            ORDER BY exec_count DESC
        """

        # Execute query using SQLite connector
        connector = SQLiteConnector(target_config.sqlite_db_path)
        if not connector.connect():
            raise RuntimeError(
                f"Failed to connect to SQLite: {target_config.sqlite_db_path}"
            )

        try:
            results = connector.execute_query(query, params)
        finally:
            connector.close()

        # Filter out rows without plan text
        results = [result for result in results if result.get("plan")]

        # Create plan entries
        plans = []
        for result in results:
            if result["query_sample_text"] and result["plan"]:
                plan = WorkloadPlanEntry(
                    sql=result["query_sample_text"],
                    plan_text=result["plan"],
                    version="v4",
                    schema=target_config.schema,
                    digest_text=result["digest_text"],
                    metadata={
                        "digest_text": result["digest_text"],
                        "exec_count": result["exec_count"],
                        "avg_latency": result["avg_latency"],
                        "workload_label": target_config.label,
                    },
                )
                plans.append(plan)

        return plans, results

    def _fetch_plans_from_tidb(
        self, target_config: TargetConfig
    ) -> Tuple[List[PlanEntry], List[Dict]]:
        """Fetch plans from TiDB database"""

        # Build query for TiDB
        columns = self.evaluation_config.get_column_mapping("tidb")

        conditions = []
        params = {}

        # Schema condition
        if target_config.schema:
            conditions.append(f"{columns['schema_name']} = :schema")
            params["schema"] = target_config.schema

        # Time conditions
        if target_config.start_time and target_config.end_time:
            conditions.extend(
                [
                    f"{columns['start_time']} >= :start_time",
                    f"{columns['end_time']} <= :end_time",
                ]
            )
            params["start_time"] = target_config.start_time
            params["end_time"] = target_config.end_time

        # Plan condition
        conditions.append(f"{columns['plan_column']} IS NOT NULL")

        where_clause = "WHERE " + " AND ".join(conditions)

        query = f"""
            SELECT
                {columns['digest_text']} as digest_text,
                ANY_VALUE({columns['query_sample']}) as query_sample_text,
                ANY_VALUE({columns['plan_column']}) as plan,
                SUM({columns['exec_count']}) as exec_count,
                AVG({columns['avg_latency']}) as avg_latency
            FROM {target_config.table_name}
            {where_clause}
            GROUP BY {columns['digest_text']}
            ORDER BY exec_count DESC
        """

        # Execute query using TiDB connector
        results = execute_query(query, params, target_config.schema)

        # Filter out rows without plan text
        results = [result for result in results if result.get("plan")]

        # Create plan entries
        plans = []
        for result in results:
            if result["query_sample_text"] and result["plan"]:
                plan = WorkloadPlanEntry(
                    sql=result["query_sample_text"],
                    plan_text=result["plan"],
                    version="v4",
                    schema=target_config.schema,
                    digest_text=result["digest_text"],
                    metadata={
                        "digest_text": result["digest_text"],
                        "exec_count": result["exec_count"],
                        "avg_latency": result["avg_latency"],
                        "workload_label": target_config.label,
                    },
                )
                plans.append(plan)

        return plans, results

    def _find_common_sqls_single_source(
        self, limit: Optional[int] = None
    ) -> Tuple[List[Dict[str, Any]], List[PlanEntry], List[PlanEntry]]:
        """Find common SQLs using original single-source logic (both from same DB)"""
        # Determine table names
        table1 = (
            self.real_workload_config.table_name
            or self.evaluation_config.get_table_name()
        )
        table2 = (
            self.synthetic_workload_config.table_name
            or self.evaluation_config.get_table_name()
        )

        # Determine data source type based on first config (both should be same)
        data_source = "sqlite" if self.real_workload_config.is_sqlite else "tidb"

        # Build query parameters
        query_params = {
            "table1": table1,
            "table2": table2,
            "limit_clause": f"LIMIT {limit}" if limit else "",
        }

        # Build ALL query parameters FIRST (before get_query call)
        # Real workload parameters
        if self.real_workload_config.schema:
            query_params["schema1"] = self.real_workload_config.schema
        if self.real_workload_config.start_time and self.real_workload_config.end_time:
            query_params.update(
                {
                    "start_time1": self.real_workload_config.start_time,
                    "end_time1": self.real_workload_config.end_time,
                }
            )

        # Synthetic workload parameters
        if self.synthetic_workload_config.schema:
            query_params["schema2"] = self.synthetic_workload_config.schema
        if (
            self.synthetic_workload_config.start_time
            and self.synthetic_workload_config.end_time
        ):
            query_params.update(
                {
                    "start_time2": self.synthetic_workload_config.start_time,
                    "end_time2": self.synthetic_workload_config.end_time,
                }
            )

        # Get SQL query from config WITH all parameters and data source
        columns = self.evaluation_config.get_column_mapping(data_source)
        query_config = self.evaluation_config.queries

        # Manually build the query with correct column mappings
        query_template = query_config.queries["find_common_sqls"]
        query = query_template.format(**columns, **query_params)

        # Build execution parameters for query execution
        params = {}

        # Real workload parameters
        if self.real_workload_config.schema:
            params["schema1"] = self.real_workload_config.schema
        if self.real_workload_config.start_time and self.real_workload_config.end_time:
            params.update(
                {
                    "start_time1": self.real_workload_config.start_time,
                    "end_time1": self.real_workload_config.end_time,
                }
            )

        # Synthetic workload parameters
        if self.synthetic_workload_config.schema:
            params["schema2"] = self.synthetic_workload_config.schema
        if (
            self.synthetic_workload_config.start_time
            and self.synthetic_workload_config.end_time
        ):
            params.update(
                {
                    "start_time2": self.synthetic_workload_config.start_time,
                    "end_time2": self.synthetic_workload_config.end_time,
                }
            )

        # Execute query based on data source
        if data_source == "sqlite":
            # For SQLite, use the SQLite connector with first config's database
            from evaluation.sqlite_connector import SQLiteConnector

            connector = SQLiteConnector(self.real_workload_config.sqlite_db_path)
            if not connector.connect():
                raise RuntimeError(
                    f"Failed to connect to SQLite: {self.real_workload_config.sqlite_db_path}"
                )
            try:
                results = connector.execute_query(query, params)
            finally:
                connector.close()
        else:
            # For TiDB, use the original method
            results = execute_query(query, params)

        if not results:
            return [], [], []

        # Separate common_sqls data and create plan entries directly
        common_sqls = []
        plans1 = []
        plans2 = []

        for row in results:
            # Build common SQL performance data
            common_sql = {
                "DIGEST_TEXT": row["DIGEST_TEXT"],
                "p1_exec_count": row["p1_exec_count"],
                "p1_avg_latency": row["p1_avg_latency"],
                "p2_exec_count": row["p2_exec_count"],
                "p2_avg_latency": row["p2_avg_latency"],
                "latency_ratio": row["latency_ratio"],
            }
            common_sqls.append(common_sql)

            # Create plan entries for real workload
            if row["p1_query_sample"] and row["p1_plan"]:
                plan1 = WorkloadPlanEntry(
                    sql=row["p1_query_sample"],
                    plan_text=row["p1_plan"],
                    version="v4",
                    schema=self.real_workload_config.schema,
                    digest_text=row["DIGEST_TEXT"],
                    metadata={
                        "digest_text": row["DIGEST_TEXT"],
                        "exec_count": row["p1_exec_count"],
                        "avg_latency": row["p1_avg_latency"],
                        "workload_label": self.real_workload_config.label,
                    },
                )
                plans1.append(plan1)

            # Create plan entries for synthetic workload
            if row["p2_query_sample"] and row["p2_plan"]:
                plan2 = WorkloadPlanEntry(
                    sql=row["p2_query_sample"],
                    plan_text=row["p2_plan"],
                    version="v4",
                    schema=self.synthetic_workload_config.schema,
                    digest_text=row["DIGEST_TEXT"],
                    metadata={
                        "digest_text": row["DIGEST_TEXT"],
                        "exec_count": row["p2_exec_count"],
                        "avg_latency": row["p2_avg_latency"],
                        "workload_label": self.synthetic_workload_config.label,
                    },
                )
                plans2.append(plan2)

        return common_sqls, plans1, plans2

    def _find_common_sqls(self, limit: Optional[int] = None) -> List[Dict[str, Any]]:
        """Find common SQLs between two time periods (backward compatibility)"""
        common_sqls, _, _ = self._find_common_sqls_with_plans(limit)
        return common_sqls

    def _perform_detailed_comparisons(
        self, plans1: List[PlanEntry], plans2: List[PlanEntry], common_sqls: List[Dict]
    ) -> EvaluationResult:
        """Perform detailed plan comparisons, maintaining original comparison logic"""
        comparison_results = []
        analysis_results = []

        # Create mapping by digest_text
        plans1_map = {p.digest_text: p for p in plans1}
        plans2_map = {p.digest_text: p for p in plans2}
        performance_map = {sql["DIGEST_TEXT"]: sql for sql in common_sqls}

        for digest_text in performance_map:
            if digest_text in plans1_map and digest_text in plans2_map:
                plan1_entry = plans1_map[digest_text]
                plan2_entry = plans2_map[digest_text]
                perf_data = performance_map[digest_text]

                try:
                    # Parse plans
                    parsed_p1 = parse_text(plan1_entry.sql, plan1_entry.plan_text, "v4")
                    parsed_p2 = parse_text(plan2_entry.sql, plan2_entry.plan_text, "v4")

                    # Use original comparison logic
                    comparison = compare_plans(parsed_p1, parsed_p2)
                    p1_complexity = self._calculate_complexity_score(parsed_p1)
                    p2_complexity = self._calculate_complexity_score(parsed_p2)
                    plan_scores = create_plan_comparison_score(
                        comparison, p1_complexity, p2_complexity
                    )

                    # Get plan tree structure
                    p1_tree = (
                        get_plan_tree_structure(parsed_p1.root)
                        if parsed_p1.root
                        else []
                    )
                    p2_tree = (
                        get_plan_tree_structure(parsed_p2.root)
                        if parsed_p2.root
                        else []
                    )

                    # Build comparison result
                    comp_result = {
                        "sql": plan1_entry.sql,
                        "is_same": comparison.is_same,
                        "reason": comparison.reason,
                        "similarity_score": plan_scores["overall_similarity"],
                        "est_row_similarity": comparison.est_row_similarity,
                        "plan1_complexity": p1_complexity,
                        "plan2_complexity": p2_complexity,
                        "complexity_ratio": plan_scores["complexity_ratio"],
                        "performance_data": perf_data,
                        "plan_comparison": plan_scores,
                        "details": comparison.details,
                        "plan_trees": {"period1": p1_tree, "period2": p2_tree},
                        "plan_details": {
                            "p1_root_type": (
                                str(parsed_p1.root.get_type())
                                if parsed_p1.root
                                else "None"
                            ),
                            "p2_root_type": (
                                str(parsed_p2.root.get_type())
                                if parsed_p2.root
                                else "None"
                            ),
                            "p1_root_estrows": (
                                parsed_p1.root.get_est_row() if parsed_p1.root else 0
                            ),
                            "p2_root_estrows": (
                                parsed_p2.root.get_est_row() if parsed_p2.root else 0
                            ),
                        },
                    }
                    comparison_results.append(comp_result)

                    # Analysis results
                    from evaluation.plan_evaluator import PlanAnalysisResult

                    analysis1 = PlanAnalysisResult(
                        plan_entry=plan1_entry,
                        complexity_score=p1_complexity,
                        node_count=(
                            self._count_nodes(parsed_p1.root) if parsed_p1.root else 0
                        ),
                        depth=(
                            self._calculate_depth(parsed_p1.root)
                            if parsed_p1.root
                            else 0
                        ),
                        operator_counts=(
                            self._count_operators(parsed_p1.root)
                            if parsed_p1.root
                            else {}
                        ),
                    )
                    analysis2 = PlanAnalysisResult(
                        plan_entry=plan2_entry,
                        complexity_score=p2_complexity,
                        node_count=(
                            self._count_nodes(parsed_p2.root) if parsed_p2.root else 0
                        ),
                        depth=(
                            self._calculate_depth(parsed_p2.root)
                            if parsed_p2.root
                            else 0
                        ),
                        operator_counts=(
                            self._count_operators(parsed_p2.root)
                            if parsed_p2.root
                            else {}
                        ),
                    )
                    analysis_results.extend([analysis1, analysis2])

                except Exception as e:
                    print(f"  ❌ Comparison error {digest_text}: {e}")
                    comp_result = {
                        "sql": plan1_entry.sql,
                        "error": str(e),
                        "is_same": False,
                        "similarity_score": 0.0,
                        "performance_data": perf_data,
                    }
                    comparison_results.append(comp_result)

        # Calculate metrics
        metrics = self._calculate_summary_metrics(comparison_results)

        return EvaluationResult(
            total_plans=len(plans1) + len(plans2),
            comparable_groups=len(comparison_results),
            comparison_results=comparison_results,
            metrics=metrics,
            analysis_results=analysis_results,
        )

    def _calculate_summary_metrics(
        self, comparison_results: List[Dict]
    ) -> "EvaluationMetrics":
        """Calculate summary metrics"""
        from evaluation.plan_evaluator import EvaluationMetrics

        metrics = EvaluationMetrics()
        metrics.total_comparisons = len(comparison_results)
        metrics.identical_plans = sum(
            1 for r in comparison_results if r.get("is_same", False)
        )
        metrics.different_plans = metrics.total_comparisons - metrics.identical_plans
        metrics.comparison_errors = sum(1 for r in comparison_results if r.get("error"))

        # Similarity metrics
        similarity_scores = [
            r.get("similarity_score", 0.0)
            for r in comparison_results
            if not r.get("error")
        ]
        if similarity_scores:
            metrics.avg_similarity = sum(similarity_scores) / len(similarity_scores)
            metrics.min_similarity = min(similarity_scores)
            metrics.max_similarity = max(similarity_scores)

            # Similarity distribution
            high_sim = sum(1 for s in similarity_scores if s > 0.8)
            medium_sim = sum(1 for s in similarity_scores if 0.4 <= s <= 0.8)
            low_sim = sum(1 for s in similarity_scores if s < 0.4)

            metrics.similarity_distribution = {
                "high": high_sim,
                "medium": medium_sim,
                "low": low_sim,
            }

        # Complexity metrics
        complexity_ratios = [
            r.get("complexity_ratio", 1.0)
            for r in comparison_results
            if not r.get("error") and r.get("complexity_ratio") != float("inf")
        ]
        if complexity_ratios:
            metrics.avg_complexity_ratio = sum(complexity_ratios) / len(
                complexity_ratios
            )
            metrics.max_complexity_ratio = max(complexity_ratios)

        # Execution count correlation analysis
        exec_count_pairs = []
        latency_pairs = []
        for r in comparison_results:
            if not r.get("error") and "performance_data" in r:
                perf_data = r["performance_data"]
                p1_exec = perf_data.get("p1_exec_count", 0)
                p2_exec = perf_data.get("p2_exec_count", 0)
                p1_latency = perf_data.get("p1_avg_latency", 0)
                p2_latency = perf_data.get("p2_avg_latency", 0)

                # Convert to float type to avoid Decimal type issues
                if p1_exec > 0 and p2_exec > 0:
                    exec_count_pairs.append((float(p1_exec), float(p2_exec)))
                if p1_latency > 0 and p2_latency > 0:
                    latency_pairs.append((float(p1_latency), float(p2_latency)))

        # Calculate Pearson correlation coefficient
        if len(exec_count_pairs) >= 2:
            exec_correlation = self._calculate_pearson_correlation(exec_count_pairs)
            metrics.performance_correlations["exec_count_correlation"] = (
                exec_correlation
            )

        if len(latency_pairs) >= 2:
            latency_correlation = self._calculate_pearson_correlation(latency_pairs)
            metrics.performance_correlations["latency_correlation"] = (
                latency_correlation
            )

        return metrics

    def _calculate_pearson_correlation(self, pairs: List[Tuple[float, float]]) -> float:
        """Calculate Pearson correlation coefficient

        Args:
            pairs: List of (x, y) value pairs

        Returns:
            Pearson correlation coefficient (between -1 and 1)
        """
        if len(pairs) < 2:
            return 0.0

        # Extract x and y values
        x_values = [pair[0] for pair in pairs]
        y_values = [pair[1] for pair in pairs]

        n = len(pairs)

        # Calculate means
        x_mean = sum(x_values) / n
        y_mean = sum(y_values) / n

        # Calculate covariance and variance
        numerator = sum((x - x_mean) * (y - y_mean) for x, y in pairs)

        x_variance = sum((x - x_mean) ** 2 for x in x_values)
        y_variance = sum((y - y_mean) ** 2 for y in y_values)

        denominator = (x_variance * y_variance) ** 0.5

        # Avoid division by zero
        if denominator == 0:
            return 0.0

        correlation = numerator / denominator

        # Ensure result is within [-1, 1] range (handle floating point precision issues)
        return max(-1.0, min(1.0, correlation))

    def _enrich_with_performance_data(
        self, result: EvaluationResult, common_sqls: List[Dict]
    ):
        """Add performance data to evaluation results"""
        # Create mapping from SQL digest to performance data
        performance_map = {sql["DIGEST_TEXT"]: sql for sql in common_sqls}

        # Add performance information to comparison results
        for comp_result in result.comparison_results:
            sql = comp_result.get("sql", "")
            # Find corresponding digest through SQL text (simplified processing)
            for digest_text, perf_data in performance_map.items():
                if sql and digest_text in sql:  # Simple matching, can be improved
                    comp_result["performance_data"] = perf_data
                    break

    def _print_analysis_summary(
        self, result: EvaluationResult, common_sqls: List[Dict]
    ):
        """Print analysis summary (maintain original format)"""
        # performance_map = {sql["DIGEST_TEXT"]: sql for sql in common_sqls}

        for i, comp_result in enumerate(result.comparison_results, 1):
            sql = comp_result.get("sql", "")
            perf_data = comp_result.get("performance_data")

            if perf_data:
                print(
                    f"\n=== Analyzing SQL {i}/{len(result.comparison_results)}: {sql[:60]}... ==="
                )
                print(
                    f"  📊 Performance: {perf_data['p1_avg_latency']:.0f}ns → {perf_data['p2_avg_latency']:.0f}ns ({perf_data['latency_ratio']:.1f}x)"
                )

                if (
                    "plan1_complexity" in comp_result
                    and "plan2_complexity" in comp_result
                ):
                    print(
                        f"  📈 Complexity: {comp_result['plan1_complexity']:.1f} → {comp_result['plan2_complexity']:.1f} ({comp_result.get('complexity_ratio', 'N/A'):.1f}x)"
                    )

                print(
                    f"  ⭐ Plan similarity: Structure {comp_result.get('similarity_score', 0):.2f}"
                )

                # Plan status determination
                similarity = comp_result.get("similarity_score", 0)
                if similarity > 0.9:
                    plan_status = "✅ Plans are essentially the same"
                elif similarity > 0.5:
                    plan_status = "⚠️ Plans have differences"
                else:
                    plan_status = "❌ Plans are completely different"
                print(f"  🔍 {plan_status}")

    def _print_correlation_analysis(self, result: EvaluationResult):
        """Print correlation analysis results"""
        correlations = result.metrics.performance_correlations

        if correlations:
            print("\n=== 📊 Execution Pattern Correlation Analysis ===")

            if "exec_count_correlation" in correlations:
                exec_corr = correlations["exec_count_correlation"]
                exec_status = self._interpret_correlation(exec_corr)
                print(
                    f"  🔢 Execution count correlation: {exec_corr:.3f} ({exec_status})"
                )

            if "latency_correlation" in correlations:
                latency_corr = correlations["latency_correlation"]
                latency_status = self._interpret_correlation(latency_corr)
                print(f"  ⏱️ Latency correlation: {latency_corr:.3f} ({latency_status})")

            # Print detailed data table
            self._print_execution_data_table(result)

            # Correlation interpretation
            if "exec_count_correlation" in correlations:
                exec_corr = correlations["exec_count_correlation"]
                if exec_corr > 0.7:
                    print(
                        "  ✅ Workload patterns are highly similar, comparison results have high reliability"
                    )
                elif exec_corr > 0.3:
                    print(
                        "  ⚠️ Workload patterns are moderately similar, comparison results should be interpreted with caution"
                    )
                else:
                    print(
                        "  ❌ Workload patterns differ significantly, comparison results have low reliability"
                    )

    def _print_execution_data_table(self, result: EvaluationResult):
        """Print detailed execution count data table"""
        print("\n📋 Detailed execution count data:")
        print(
            f"{'#':>3} | {'Real Workload Count':>18} | {'Synthetic Count':>18} | {'Ratio':>8} | {'SQL Summary':>40}"
        )
        print(f"{'-' * 3}+{'-' * 17}+{'-' * 17}+{'-' * 10}+{'-' * 42}")

        for i, comp_result in enumerate(result.comparison_results, 1):
            if not comp_result.get("error") and "performance_data" in comp_result:
                perf_data = comp_result["performance_data"]
                p1_exec = perf_data.get("p1_exec_count", 0)
                p2_exec = perf_data.get("p2_exec_count", 0)
                sql = comp_result.get("sql", "")

                # Calculate ratio
                if p1_exec > 0:
                    ratio = float(p2_exec) / float(p1_exec)
                else:
                    ratio = float("inf") if p2_exec > 0 else 1.0

                # Truncate SQL display
                sql_preview = sql[:37] + "..." if len(sql) > 40 else sql

                print(
                    f"{i:>3} | {p1_exec:>18,} | {p2_exec:>18,} | {ratio:>8.2f} | {sql_preview:>40}"
                )

        # Calculate statistics
        exec_counts_p1 = []
        exec_counts_p2 = []
        for comp_result in result.comparison_results:
            if not comp_result.get("error") and "performance_data" in comp_result:
                perf_data = comp_result["performance_data"]
                p1_exec = float(perf_data.get("p1_exec_count", 0))
                p2_exec = float(perf_data.get("p2_exec_count", 0))
                if p1_exec > 0 and p2_exec > 0:
                    exec_counts_p1.append(p1_exec)
                    exec_counts_p2.append(p2_exec)

        if exec_counts_p1:
            print("\n📊 Statistical Summary:")
            print(f"  Real workload total executions: {sum(exec_counts_p1):,.0f}")
            print(f"  Synthetic workload total executions: {sum(exec_counts_p2):,.0f}")
            print(
                f"  Real workload average executions: {sum(exec_counts_p1) / len(exec_counts_p1):,.1f}"
            )
            print(
                f"  Synthetic workload average executions: {sum(exec_counts_p2) / len(exec_counts_p2):,.1f}"
            )
            print(
                f"  Overall execution count ratio: {sum(exec_counts_p2) / sum(exec_counts_p1):.2f}"
            )

    def _interpret_correlation(self, correlation: float) -> str:
        """Interpret correlation strength"""
        abs_corr = abs(correlation)
        if abs_corr >= 0.8:
            return "Strong correlation"
        elif abs_corr >= 0.5:
            return "Moderate correlation"
        elif abs_corr >= 0.3:
            return "Weak correlation"
        else:
            return "No correlation"

    def _create_empty_result(self) -> EvaluationResult:
        """Create empty evaluation result"""
        from evaluation.plan_evaluator import EvaluationMetrics

        return EvaluationResult(
            total_plans=0,
            comparable_groups=0,
            comparison_results=[],
            metrics=EvaluationMetrics(),
            analysis_results=[],
        )

    def _generate_time_based_report(
        self, result: EvaluationResult, common_sqls: List[Dict]
    ) -> str:
        """Generate detailed time-based analysis report (restore original detailed format)"""
        timestamp = datetime.datetime.now().strftime("%Y%m%d_%H%M%S")
        report_path = (
            f"evaluation/reports/TIME_BASED_PLAN_COMPARISON_REPORT_{timestamp}.md"
        )

        # Sort by performance difference
        sorted_results = sorted(
            result.comparison_results,
            key=lambda x: x.get("performance_data", {}).get("latency_ratio", 0),
            reverse=True,
        )

        content = f"""# TiDB Execution Plan Detailed Comparison Report

Generation time: {datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")} (UTC)

## Summary

Analyzed **{len(common_sqls)}** identical SQL patterns' execution plan differences across two time periods.

### Workload Definitions
- **Real workload ({self.real_workload_config.label})**: {f'{self.real_workload_config.start_time} - {self.real_workload_config.end_time}' if self.real_workload_config.start_time else 'All time (no time filtering)'}
- **Synthetic workload ({self.synthetic_workload_config.label})**: {f'{self.synthetic_workload_config.start_time} - {self.synthetic_workload_config.end_time}' if self.synthetic_workload_config.start_time else 'All time (no time filtering)'}

## Performance Overview

| # | SQL Summary | Latency Multiplier | Plan Similarity | Root Node Change |
|---|-------------|-------------------|-----------------|------------------|
"""

        # Add overview table
        for i, comp_result in enumerate(sorted_results, 1):
            sql_preview = (
                comp_result.get("sql", "")[:40] + "..."
                if len(comp_result.get("sql", "")) > 40
                else comp_result.get("sql", "")
            )
            perf_data = comp_result.get("performance_data", {})
            plan_comparison = comp_result.get("plan_comparison", {})

            latency_ratio = perf_data.get("latency_ratio", "N/A")
            similarity = plan_comparison.get(
                "overall_similarity", comp_result.get("similarity_score", 0.0)
            )

            # Get root node changes
            plan_details = comp_result.get("plan_details", {})
            p1_root = plan_details.get("p1_root_type", "Unknown")
            p2_root = plan_details.get("p2_root_type", "Unknown")
            root_change = f"{p1_root} → {p2_root}"

            content += f"| {i} | {sql_preview} | {latency_ratio:.1f}x | {similarity:.2f} | {root_change} |\n"

        content += "\n## Detailed Analysis\n"

        # Add detailed analysis
        for i, comp_result in enumerate(sorted_results, 1):
            perf_data = comp_result.get("performance_data", {})
            plan_comparison = comp_result.get("plan_comparison", {})

            # Get stored plan tree structure and detailed information
            sql = comp_result.get("sql", "")
            plan_trees = comp_result.get("plan_trees", {})
            plan_details = comp_result.get("plan_details", {})

            p1_tree_str = "\n".join(
                plan_trees.get("period1", ["Plan tree unavailable"])
            )
            p2_tree_str = "\n".join(
                plan_trees.get("period2", ["Plan tree unavailable"])
            )
            p1_root_type = plan_details.get("p1_root_type", "Unknown")
            p2_root_type = plan_details.get("p2_root_type", "Unknown")
            p1_root_estrows = plan_details.get("p1_root_estrows", 0)
            p2_root_estrows = plan_details.get("p2_root_estrows", 0)

            content += f"""
### {i}. SQL Analysis

**SQL Statement:**
```sql
{sql}
```

**Performance Comparison:**
- Real workload ({self.real_workload_config.label}): Executed {perf_data.get('p1_exec_count', 0):,} times, Average latency {perf_data.get('p1_avg_latency', 0):,.0f}ns
- Synthetic workload ({self.synthetic_workload_config.label}): Executed {perf_data.get('p2_exec_count', 0):,} times, Average latency {perf_data.get('p2_avg_latency', 0):,.0f}ns
- **Performance Difference: {perf_data.get('latency_ratio', 0):.1f}x**

**Plan Comparison Metrics:**
- Structure Similarity: {plan_comparison.get('structure_similarity', 0.0):.2f}
- EstRow Similarity: {plan_comparison.get('estrow_similarity', 0.0):.2f}
- Complexity Similarity: {plan_comparison.get('complexity_similarity', 0.0):.2f}
- **Overall Similarity: {plan_comparison.get('overall_similarity', 0.0):.2f}**

**Plan Complexity:**
- Real workload ({self.real_workload_config.label}) Complexity: {plan_comparison.get('p1_complexity', 0.0):.1f}
- Synthetic workload ({self.synthetic_workload_config.label}) Complexity: {plan_comparison.get('p2_complexity', 0.0):.1f}
- Complexity Ratio: {plan_comparison.get('complexity_ratio', 0.0):.1f}x

**Execution Plan Comparison:**

Real workload ({self.real_workload_config.label}) Plan Tree:
```
{p1_tree_str if p1_tree_str else 'Plan tree parsing...'}
```

Synthetic workload ({self.synthetic_workload_config.label}) Plan Tree:
```
{p2_tree_str if p2_tree_str else 'Plan tree parsing...'}
```

**Root Node Comparison:**
- Real workload ({self.real_workload_config.label}): {p1_root_type} (EstRows: {p1_root_estrows})
- Synthetic workload ({self.synthetic_workload_config.label}): {p2_root_type} (EstRows: {p2_root_estrows})

---
"""

        # Add summary
        if result.comparison_results:
            avg_latency_ratio = sum(
                r.get("performance_data", {}).get("latency_ratio", 0)
                for r in result.comparison_results
            ) / len(result.comparison_results)
            max_latency_ratio = max(
                r.get("performance_data", {}).get("latency_ratio", 0)
                for r in result.comparison_results
            )
            max_similarity = max(
                r.get("plan_comparison", {}).get(
                    "overall_similarity", r.get("similarity_score", 0)
                )
                for r in result.comparison_results
            )

            content += f"""
## Summary

### Key Findings
1. **Average Performance Difference**: {avg_latency_ratio:.1f}x
2. **Average Plan Similarity**: {result.metrics.avg_similarity:.2f}
3. **Maximum Performance Difference**: {max_latency_ratio:.1f}x
4. **Most Similar Plan**: {max_similarity:.2f}

### Optimization Recommendations
1. Focus on SQLs with performance differences exceeding 10x
2. For plans with low EstRow similarity, update statistics
3. For plans with low structural similarity, check index configuration
4. Regularly monitor plan changes and establish baselines

### Execution Pattern Correlation Analysis"""

        # Add correlation analysis
        correlations = result.metrics.performance_correlations
        if correlations:
            if "exec_count_correlation" in correlations:
                exec_corr = correlations["exec_count_correlation"]
                exec_status = self._interpret_correlation(exec_corr)
                content += f"""
- **Execution Count Correlation**: {exec_corr:.3f} ({exec_status})"""

            if "latency_correlation" in correlations:
                latency_corr = correlations["latency_correlation"]
                latency_status = self._interpret_correlation(latency_corr)
                content += f"""
- **Latency Correlation**: {latency_corr:.3f} ({latency_status})"""

            # Add correlation interpretation
            if "exec_count_correlation" in correlations:
                exec_corr = correlations["exec_count_correlation"]
                if exec_corr > 0.7:
                    content += """

🟢 **High Execution Count Correlation**: The workload patterns of the two time periods are very similar, comparison results have high reliability."""
                elif exec_corr > 0.3:
                    content += """

🟡 **Moderate Execution Count Correlation**: The workload patterns of the two time periods have some similarity, comparison results should be interpreted with caution."""
                else:
                    content += """

🔴 **Weak Execution Count Correlation**: The workload patterns of the two time periods differ significantly, comparison results have low reliability."""

                content += """

**Note**: Execution count correlation reflects the degree of correlation between SQL execution frequencies in the two time periods. High correlation indicates stable business patterns, making comparison results more valuable for reference.

### Detailed Execution Count Data

| # | Period 1 Exec Count | Period 2 Exec Count | Ratio | SQL Summary |
|---|---------------------|---------------------|-------|-------------|"""

                # Add execution count data table
                for i, comp_result in enumerate(sorted_results, 1):
                    if (
                        not comp_result.get("error")
                        and "performance_data" in comp_result
                    ):
                        perf_data = comp_result["performance_data"]
                        p1_exec = perf_data.get("p1_exec_count", 0)
                        p2_exec = perf_data.get("p2_exec_count", 0)
                        sql = comp_result.get("sql", "")

                        # Calculate ratio
                        if p1_exec > 0:
                            ratio = float(p2_exec) / float(p1_exec)
                        else:
                            ratio = float("inf") if p2_exec > 0 else 1.0

                        # Truncate SQL display
                        sql_preview = sql[:40] + "..." if len(sql) > 40 else sql

                        content += f"""
| {i} | {p1_exec:,} | {p2_exec:,} | {ratio:.2f} | {sql_preview} |"""

                # Add statistical summary
                exec_counts_p1 = []
                exec_counts_p2 = []
                for comp_result in result.comparison_results:
                    if (
                        not comp_result.get("error")
                        and "performance_data" in comp_result
                    ):
                        perf_data = comp_result["performance_data"]
                        p1_exec = float(perf_data.get("p1_exec_count", 0))
                        p2_exec = float(perf_data.get("p2_exec_count", 0))
                        if p1_exec > 0 and p2_exec > 0:
                            exec_counts_p1.append(p1_exec)
                            exec_counts_p2.append(p2_exec)

                if exec_counts_p1:
                    content += f"""

### Execution Count Statistical Summary

- **Real workload ({self.real_workload_config.label}) Total Executions**: {sum(exec_counts_p1):,.0f}
- **Synthetic workload ({self.synthetic_workload_config.label}) Total Executions**: {sum(exec_counts_p2):,.0f}
- **Real workload ({self.real_workload_config.label}) Average Executions**: {sum(exec_counts_p1) / len(exec_counts_p1):,.1f}
- **Synthetic workload ({self.synthetic_workload_config.label}) Average Executions**: {sum(exec_counts_p2) / len(exec_counts_p2):,.1f}
- **Overall Execution Count Ratio**: {sum(exec_counts_p2) / sum(exec_counts_p1):.2f}"""

        content += """

---
*Report automatically generated by TiDB Execution Plan Comparison Tool*
"""

        with open(report_path, "w", encoding="utf-8") as f:
            f.write(content)

        return report_path


def get_plan_tree_structure(node, prefix="", is_last=True) -> List[str]:
    """Get string representation of plan tree"""
    if node is None:
        return ["Plan tree unavailable (node is None)"]

    lines = []

    # Current node
    connector = "└── " if is_last else "├── "
    lines.append(
        f"{prefix}{connector}{node.get_id()} ({node.get_type()}) [EstRows: {node.get_est_row()}]"
    )

    # Child nodes
    children = node.get_children()
    for i, child in enumerate(children):
        # Skip None children
        if child is None:
            continue

        is_child_last = i == len(children) - 1
        child_prefix = prefix + ("    " if is_last else "│   ")
        lines.extend(get_plan_tree_structure(child, child_prefix, is_child_last))

    return lines


def create_plan_comparison_score(
    comparison_result, p1_complexity, p2_complexity
) -> Dict:
    """Create comprehensive plan comparison metrics"""
    # Basic similarity scores
    structure_score = comparison_result.similarity_score
    estrow_score = comparison_result.est_row_similarity

    # Complexity difference score (closer to 1 is better)
    complexity_ratio = (
        min(p1_complexity, p2_complexity) / max(p1_complexity, p2_complexity)
        if max(p1_complexity, p2_complexity) > 0
        else 1.0
    )

    # Overall score (Structure 60% + EstRow 30% + Complexity 10%)
    overall_score = structure_score * 0.6 + estrow_score * 0.3 + complexity_ratio * 0.1

    return {
        "structure_similarity": structure_score,
        "estrow_similarity": estrow_score,
        "complexity_similarity": complexity_ratio,
        "overall_similarity": overall_score,
        "p1_complexity": p1_complexity,
        "p2_complexity": p2_complexity,
        "complexity_ratio": (
            p2_complexity / p1_complexity if p1_complexity > 0 else float("inf")
        ),
    }


# Convenience function for workload comparison
def compare_workloads(
    real_schema: str,
    synthetic_schema: str,
    real_time_range: tuple = None,
    synthetic_time_range: tuple = None,
    real_data_source: str = "sqlite",  # SQLite stores original/historical data
    synthetic_data_source: str = "tidb",  # TiDB stores current/synthetic data
    sqlite_db_path: str = "simcluster.db",
    generate_report: bool = True,
):
    """Compare real workload vs synthetic workload with hybrid data source support

    Args:
        real_schema: Schema name for real workload (e.g., 'test' in SQLite)
        synthetic_schema: Schema name for synthetic workload (e.g., 'test' in TiDB)
        real_time_range: Optional (start_time, end_time) tuple for real workload
        synthetic_time_range: Optional (start_time, end_time) tuple for synthetic workload
        real_data_source: Data source for real workload ('sqlite' for historical data)
        synthetic_data_source: Data source for synthetic workload ('tidb' for current data)
        sqlite_db_path: Path to SQLite database file (contains captured historical data)
        generate_report: Whether to generate detailed report

    Note:
        By default, this compares:
        - Real data: SQLite database (historical/captured data from previous time period)
        - Synthetic data: TiDB INFORMATION_SCHEMA (current synthetic workload data)
    """
    real_config = TargetConfig(
        schema=real_schema,
        start_time=real_time_range[0] if real_time_range else None,
        end_time=real_time_range[1] if real_time_range else None,
        data_source_type=real_data_source,
        sqlite_db_path=sqlite_db_path if real_data_source == "sqlite" else None,
    )

    synthetic_config = TargetConfig(
        schema=synthetic_schema,
        start_time=synthetic_time_range[0] if synthetic_time_range else None,
        end_time=synthetic_time_range[1] if synthetic_time_range else None,
        data_source_type=synthetic_data_source,
        sqlite_db_path=sqlite_db_path if synthetic_data_source == "sqlite" else None,
    )

    evaluator = WorkloadPlanEvaluator(real_config, synthetic_config)
    return evaluator.analyze_sql_plan_differences(generate_report=generate_report)


if __name__ == "__main__":
    parser = argparse.ArgumentParser(
        description="Compare real workload vs synthetic workload with hybrid data source support."
    )
    parser.add_argument(
        "--schema", default="test", help="the schema name of the workload to evaluate"
    )
    parser.add_argument(
        "--meta-path",
        default="simcluster.db",
        help="the sqlite path stored collected meta",
    )
    parser.add_argument(
        "--generate-report",
        default=False,
        action="store_true",
        help="if generate report file",
    )
    parser.add_argument(
        "--source-range-begin", default=None, help="the begin time of source workload"
    )
    parser.add_argument(
        "--source-range-end", default=None, help="the end time of source workload"
    )
    parser.add_argument(
        "--synthetic-range-begin",
        default=None,
        help="the begin time of synthetic workload",
    )
    parser.add_argument(
        "--synthetic-range-end", default=None, help="the end time of synthetic workload"
    )
    args = parser.parse_args()

    real_time_range = None
    synthetic_time_range = None

    if args.source_range_begin and args.source_range_end:
        real_time_range = (args.source_range_begin, args.source_range_end)
    if args.synthetic_range_begin and args.synthetic_range_end:
        synthetic_time_range = (args.synthetic_range_begin, args.synthetic_range_end)

    # Test hybrid comparison: SQLite (real) vs TiDB (synthetic)
    print("=== Testing Hybrid Comparison: SQLite (real) vs TiDB (synthetic) ===")
    compare_workloads(
        real_schema=args.schema,  # Historical data in SQLite
        synthetic_schema=args.schema,  # Current synthetic data in TiDB
        real_time_range=real_time_range,
        synthetic_time_range=synthetic_time_range,
        real_data_source="sqlite",  # SQLite contains captured historical data
        synthetic_data_source="tidb",  # TiDB contains current synthetic workload
        sqlite_db_path=args.meta_path,
        generate_report=args.generate_report,
    )