"""
Configuration management for TiDB execution plan evaluation system.
Handles database connections, table names, and SQL query templates.
"""

import os
from dataclasses import dataclass, field
from typing import Dict, Optional

from .db_connector import load_env_file


@dataclass
class SQLiteConfig:
    """SQLite database configuration"""

    db_path: str = "simcluster.db"

    @classmethod
    def from_env(cls, env_file_path: str = None) -> "SQLiteConfig":
        """Create SQLite config from environment variables and .env file"""
        # Load from .env file first
        env_vars = load_env_file(env_file_path)

        # Environment variables take precedence over .env file
        return cls(
            db_path=os.getenv("SQLITE_DB_PATH")
            or env_vars.get("SQLITE_DB_PATH", "simcluster.db")
        )


@dataclass
class DatabaseConfig:
    """Database connection configuration"""

    host: str = "127.0.0.1"
    port: int = 4000
    username: str = "root"
    password: str = ""
    default_db: str = "INFORMATION_SCHEMA"

    @classmethod
    def from_env(cls, env_file_path: str = None) -> "DatabaseConfig":
        """Create database config from environment variables and .env file"""
        # Load from .env file first
        env_vars = load_env_file(env_file_path)

        # Environment variables take precedence over .env file
        return cls(
            host=os.getenv("DB_URL") or env_vars.get("DB_URL", "127.0.0.1"),
            port=int(os.getenv("DB_PORT") or env_vars.get("DB_PORT", "4000")),
            username=os.getenv("DB_USERNAME") or env_vars.get("DB_USERNAME", "root"),
            password=os.getenv("DB_PASSWORD") or env_vars.get("DB_PASSWORD", ""),
            default_db=os.getenv("DEFAULT_DB")
            or env_vars.get("DEFAULT_DB", "INFORMATION_SCHEMA"),
        )


@dataclass
class TableConfig:
    """Table name configuration for plan analysis"""

    statements_history_table: str = (
        "INFORMATION_SCHEMA.CLUSTER_STATEMENTS_SUMMARY_HISTORY"
    )

    @classmethod
    def from_env(cls, env_file_path: str = None) -> "TableConfig":
        """Create table config from environment variables and .env file"""
        # Load from .env file first
        env_vars = load_env_file(env_file_path)

        # Environment variables take precedence over .env file
        statements_table = os.getenv("STATEMENTS_TABLE") or env_vars.get(
            "STATEMENTS_TABLE", "INFORMATION_SCHEMA.CLUSTER_STATEMENTS_SUMMARY_HISTORY"
        )

        return cls(statements_history_table=statements_table)

    def get_table_name(self) -> str:
        """Get the configured table name"""
        return self.statements_history_table


@dataclass
class QueryConfig:
    """SQL query templates configuration"""

    # Standard column names for SQL execution history tables (TiDB format)
    tidb_columns: Dict[str, str] = field(
        default_factory=lambda: {
            "digest_text": "DIGEST_TEXT",
            "schema_name": "SCHEMA_NAME",
            "query_sample": "QUERY_SAMPLE_TEXT",
            "plan_column": "PLAN",
            "exec_count": "EXEC_COUNT",
            "avg_latency": "AVG_LATENCY",
            "start_time": "SUMMARY_BEGIN_TIME",
            "end_time": "SUMMARY_END_TIME",
        }
    )

    # Standard column names for SQLite execution history tables
    sqlite_columns: Dict[str, str] = field(
        default_factory=lambda: {
            "digest_text": "digest_text",
            "schema_name": "schema_name",
            "query_sample": "query_sample_text",
            "plan_column": "plan",
            "exec_count": "exec_count",
            "avg_latency": "avg_latency",
            "start_time": "summary_begin_time",
            "end_time": "summary_end_time",
        }
    )

    # Legacy columns property for backward compatibility
    columns: Dict[str, str] = field(
        default_factory=lambda: {
            "digest_text": "DIGEST_TEXT",
            "schema_name": "SCHEMA_NAME",
            "query_sample": "QUERY_SAMPLE_TEXT",
            "plan_column": "PLAN",
            "exec_count": "EXEC_COUNT",
            "avg_latency": "AVG_LATENCY",
            "start_time": "SUMMARY_BEGIN_TIME",
            "end_time": "SUMMARY_END_TIME",
        }
    )

    # SQL query templates
    queries: Dict[str, str] = field(
        default_factory=lambda: {
            "find_common_sqls": """
            SELECT 
                p1.{digest_text} as DIGEST_TEXT,
                p1.EXEC_COUNT as p1_exec_count,
                p1.AVG_LATENCY as p1_avg_latency,  
                p1.{query_sample} as p1_query_sample,
                p1.{plan_column} as p1_plan,
                p2.EXEC_COUNT as p2_exec_count,
                p2.AVG_LATENCY as p2_avg_latency,
                p2.{query_sample} as p2_query_sample, 
                p2.{plan_column} as p2_plan,
                (p2.AVG_LATENCY / p1.AVG_LATENCY) as latency_ratio
            FROM 
                (SELECT {digest_text}, 
                        ANY_VALUE({query_sample}) as {query_sample}, 
                        ANY_VALUE({plan_column}) as {plan_column},
                        SUM({exec_count}) as EXEC_COUNT, 
                        AVG({avg_latency}) as AVG_LATENCY
                 FROM {table1} 
                 {p1_where_clause}
                 GROUP BY {digest_text}) p1
            JOIN 
                (SELECT {digest_text}, 
                        ANY_VALUE({query_sample}) as {query_sample}, 
                        ANY_VALUE({plan_column}) as {plan_column},
                        SUM({exec_count}) as EXEC_COUNT, 
                        AVG({avg_latency}) as AVG_LATENCY
                 FROM {table2} 
                 {p2_where_clause}
                 GROUP BY {digest_text}) p2
            ON p1.{digest_text} = p2.{digest_text}
            ORDER BY latency_ratio DESC
            {limit_clause}
        """
        }
    )

    def get_columns(self, data_source: str = "tidb") -> Dict[str, str]:
        """Get standard column names for SQL execution history tables"""
        if data_source.lower() == "sqlite":
            return self.sqlite_columns
        else:
            return self.tidb_columns

    def get_query(self, query_name: str, **kwargs) -> str:
        """Get formatted SQL query with column mappings applied"""
        if query_name not in self.queries:
            raise ValueError(f"Query '{query_name}' not found")

        query_template = self.queries[query_name]
        columns = self.get_columns()

        # Handle conditional filtering for find_common_sqls
        if query_name == "find_common_sqls":
            # Build conditional WHERE clauses
            p1_conditions = []
            p2_conditions = []

            # Add schema conditions only if provided
            if kwargs.get("schema1"):
                p1_conditions.append(f"{columns['schema_name']} = :schema1")
            if kwargs.get("schema2"):
                p2_conditions.append(f"{columns['schema_name']} = :schema2")

            # Add time conditions only if provided
            if kwargs.get("start_time1") and kwargs.get("end_time1"):
                p1_conditions.extend(
                    [
                        f"{columns['start_time']} >= :start_time1",
                        f"{columns['end_time']} <= :end_time1",
                    ]
                )

            if kwargs.get("start_time2") and kwargs.get("end_time2"):
                p2_conditions.extend(
                    [
                        f"{columns['start_time']} >= :start_time2",
                        f"{columns['end_time']} <= :end_time2",
                    ]
                )

            # Add plan column condition (always required)
            p1_conditions.append(f"{columns['plan_column']} IS NOT NULL")
            p2_conditions.append(f"{columns['plan_column']} IS NOT NULL")

            # Build WHERE clauses
            p1_where = (
                "WHERE " + "\n                 AND ".join(p1_conditions)
                if p1_conditions
                else ""
            )
            p2_where = (
                "WHERE " + "\n                 AND ".join(p2_conditions)
                if p2_conditions
                else ""
            )

            kwargs["p1_where_clause"] = p1_where
            kwargs["p2_where_clause"] = p2_where

        # Apply column mappings
        formatted_query = query_template.format(**columns, **kwargs)
        return formatted_query


@dataclass
class EvaluationConfig:
    """Main configuration class combining all settings"""

    database: DatabaseConfig = field(default_factory=DatabaseConfig.from_env)
    sqlite: SQLiteConfig = field(default_factory=SQLiteConfig.from_env)
    tables: TableConfig = field(default_factory=TableConfig.from_env)
    queries: QueryConfig = field(default_factory=QueryConfig)

    # Analysis parameters
    max_sqls_limit: Optional[int] = None
    enable_correlation_analysis: bool = True
    generate_reports: bool = True

    @classmethod
    def create_default(cls) -> "EvaluationConfig":
        """Create default configuration"""
        return cls()

    def get_table_name(self) -> str:
        """Get table name for analysis"""
        return self.tables.get_table_name()

    def get_column_mapping(self, data_source: str = "tidb") -> Dict[str, str]:
        """Get column mappings for analysis"""
        return self.queries.get_columns(data_source)


# Global default configuration instance
_default_config: Optional[EvaluationConfig] = None


def get_default_config() -> EvaluationConfig:
    """Get or create default configuration instance"""
    global _default_config
    if _default_config is None:
        _default_config = EvaluationConfig.create_default()
    return _default_config


def set_default_config(config: EvaluationConfig) -> None:
    """Set default configuration instance"""
    global _default_config
    _default_config = config


@dataclass
class TargetConfig:
    """Target workload configuration for comparison"""

    schema: str
    start_time: Optional[str] = None
    end_time: Optional[str] = None
    table_name: Optional[str] = None
    data_source_type: str = "tidb"  # "tidb" or "sqlite"
    sqlite_db_path: Optional[str] = None

    def __post_init__(self):
        if not self.table_name:
            if self.data_source_type.lower() == "sqlite":
                self.table_name = "cluster_statements_summary_history"
            else:
                config = get_default_config()
                self.table_name = config.get_table_name()

        # Set default SQLite path if needed
        if self.data_source_type.lower() == "sqlite" and not self.sqlite_db_path:
            self.sqlite_db_path = "simcluster.db"

    @property
    def label(self) -> str:
        """Generate label from schema and time info"""
        source_label = f"[{self.data_source_type.upper()}]"
        if self.start_time and self.end_time:
            return f"{source_label} {self.schema} ({self.start_time} - {self.end_time})"
        else:
            return f"{source_label} {self.schema} (all time)"

    @property
    def is_sqlite(self) -> bool:
        """Check if this target uses SQLite"""
        return self.data_source_type.lower() == "sqlite"

    @property
    def is_tidb(self) -> bool:
        """Check if this target uses TiDB"""
        return self.data_source_type.lower() == "tidb"