"""DuckDB backend implementation for local database queries.

This module provides the DuckDBBackend class that implements the Backend
protocol for executing queries against local DuckDB databases.
"""

import os
import threading
from pathlib import Path

import duckdb

from m4.config import get_default_database_path
from m4.core.backends.base import (
    ConnectionError,
    QueryResult,
    TableNotFoundError,
    sanitize_error_message,
)
from m4.core.context import M4ExecutionContext
from m4.core.datasets import DatasetDefinition


class DuckDBBackend:
    """Backend for executing queries against local DuckDB databases.

    This backend connects to DuckDB database files stored locally and
    executes SQL queries against them. It supports all standard SQL
    operations that DuckDB provides.

    Example:
        backend = DuckDBBackend()
        mimic_demo = DatasetRegistry.get("mimic-iv-demo")

        # Execute a query
        result = backend.execute_query(
            "SELECT * FROM mimiciv_hosp.patients LIMIT 5",
            mimic_demo
        )
        print(result.data)

        # Get table list (returns schema-qualified names)
        tables = backend.get_table_list(mimic_demo)
        # e.g. ["mimiciv_hosp.admissions", "mimiciv_hosp.patients", ...]
    """

    def __init__(self, db_path_override: str | Path | None = None):
        """Initialize DuckDB backend.

        Args:
            db_path_override: Optional path to use instead of auto-detection.
                            If provided, this path is used for all queries
                            regardless of the dataset parameter.
        """
        self._db_path_override = Path(db_path_override) if db_path_override else None
        self._connection_lock = threading.Lock()

    @property
    def name(self) -> str:
        """Get the backend name."""
        return "duckdb"

    def _get_db_path(
        self,
        dataset: DatasetDefinition,
        context: M4ExecutionContext | None = None,
    ) -> Path:
        """Get the database path for a dataset.

        Priority:
        1. Execution context override
        2. Instance override (db_path_override)
        3. Default path based on dataset configuration

        Args:
            dataset: The dataset definition

        Returns:
            Path to the DuckDB database file

        Raises:
            ConnectionError: If no valid database path can be determined
        """
        # Priority 1: Per-client execution context override
        if context and context.db_path:
            return context.db_path

        # Priority 2: Instance override
        if self._db_path_override:
            return self._db_path_override

        if context is None:
            env_path = os.getenv("M4_DB_PATH")
            if env_path:
                return Path(env_path)

        # Priority 3: Default based on dataset
        db_path = get_default_database_path(dataset.name)
        if not db_path:
            raise ConnectionError(
                f"Cannot determine database path for dataset '{dataset.name}'",
                backend=self.name,
            )

        return db_path

    def _connect(
        self,
        dataset: DatasetDefinition,
        context: M4ExecutionContext | None = None,
    ) -> duckdb.DuckDBPyConnection:
        """Create a connection to the DuckDB database.

        Args:
            dataset: The dataset definition

        Returns:
            DuckDB connection object

        Raises:
            ConnectionError: If the database file doesn't exist or can't be opened
        """
        db_path = self._get_db_path(dataset, context)

        if not db_path.exists():
            raise ConnectionError(
                f"Database file not found: {db_path}. "
                "Please initialize the dataset using 'm4 init'.",
                backend=self.name,
            )

        try:
            return duckdb.connect(str(db_path), read_only=True)
        except duckdb.IOException as e:
            if "Could not set lock" in str(e):
                raise ConnectionError(
                    f"Database '{db_path.name}' is locked by another process. "
                    "Close any running M4 servers or other DuckDB connections "
                    "to this database and try again.",
                    backend=self.name,
                ) from e
            raise ConnectionError(
                f"Failed to connect to DuckDB: {e}",
                backend=self.name,
            ) from e
        except Exception as e:
            raise ConnectionError(
                f"Failed to connect to DuckDB: {e}",
                backend=self.name,
            ) from e

    def execute_query(
        self,
        sql: str,
        dataset: DatasetDefinition,
        context: M4ExecutionContext | None = None,
    ) -> QueryResult:
        """Execute a SQL query against the dataset.

        Args:
            sql: SQL query string
            dataset: The dataset definition

        Returns:
            QueryResult with query output as native DataFrame
        """
        try:
            with self._connection_lock:
                conn = self._connect(dataset, context)
                try:
                    df = conn.execute(sql).df()

                    if df.empty:
                        import pandas as pd

                        return QueryResult(
                            dataframe=pd.DataFrame(),
                            row_count=0,
                        )

                    row_count = len(df)
                    truncated = row_count > 50

                    return QueryResult(
                        dataframe=df,
                        row_count=row_count,
                        truncated=truncated,
                    )
                finally:
                    conn.close()

        except ConnectionError:
            raise
        except Exception as e:
            # Use sanitized error message to avoid exposing internal details
            return QueryResult(
                dataframe=None,
                error=sanitize_error_message(e, self.name),
            )

    def get_table_list(
        self,
        dataset: DatasetDefinition,
        context: M4ExecutionContext | None = None,
    ) -> list[str]:
        """Get list of available tables in the dataset.

        Returns schema-qualified names (e.g. ``mimiciv_hosp.patients``) when
        the database contains non-system schemas.  Falls back to unqualified
        names from the ``main`` schema for backward compatibility with custom
        datasets that have no schema mapping.

        Args:
            dataset: The dataset definition

        Returns:
            List of table names (schema-qualified when applicable)
        """
        # First try non-system schemas (created by schema_mapping)
        schema_query = """
        SELECT table_schema || '.' || table_name AS qualified_name
        FROM information_schema.tables
        WHERE table_schema NOT IN ('main', 'information_schema', 'pg_catalog')
        ORDER BY table_schema, table_name
        """
        result = self.execute_query(schema_query, dataset, context)

        if (
            result.error is None
            and result.dataframe is not None
            and not result.dataframe.empty
        ):
            return result.dataframe["qualified_name"].tolist()

        # Fallback: query main schema (backward compat)
        fallback_query = """
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = 'main'
        ORDER BY table_name
        """
        result = self.execute_query(fallback_query, dataset, context)

        if result.error or result.dataframe is None or result.dataframe.empty:
            return []

        return result.dataframe["table_name"].tolist()

    def get_table_info(
        self,
        table_name: str,
        dataset: DatasetDefinition,
        context: M4ExecutionContext | None = None,
    ) -> QueryResult:
        """Get schema information for a specific table.

        Supports both schema-qualified names (``mimiciv_hosp.patients``) and
        simple names (``patients``).  Schema-qualified names use
        ``information_schema.columns``; simple names use ``PRAGMA table_info``.

        Args:
            table_name: Name of the table to inspect (may be schema-qualified)
            dataset: The dataset definition

        Returns:
            QueryResult with column information as DataFrame

        Raises:
            TableNotFoundError: If the table does not exist
        """
        if "." in table_name:
            schema, table = table_name.split(".", 1)
            query = f"""
                SELECT ordinal_position AS cid,
                       column_name AS name,
                       data_type AS type,
                       CASE WHEN is_nullable = 'YES' THEN 1 ELSE 0 END AS notnull,
                       column_default AS dflt_value,
                       0 AS pk
                FROM information_schema.columns
                WHERE table_schema = '{schema}' AND table_name = '{table}'
                ORDER BY ordinal_position
            """
        else:
            query = f"PRAGMA table_info('{table_name}')"

        try:
            with self._connection_lock:
                conn = self._connect(dataset, context)
                try:
                    df = conn.execute(query).df()

                    if df.empty:
                        raise TableNotFoundError(table_name, backend=self.name)

                    return QueryResult(dataframe=df, row_count=len(df))
                finally:
                    conn.close()

        except TableNotFoundError:
            raise
        except ConnectionError:
            raise
        except Exception as e:
            error_str = str(e).lower()
            # Check for table not found patterns in the raw error
            if "does not exist" in error_str or "not found" in error_str:
                raise TableNotFoundError(table_name, backend=self.name)
            return QueryResult(
                dataframe=None,
                error=sanitize_error_message(e, self.name),
            )

    def get_sample_data(
        self,
        table_name: str,
        dataset: DatasetDefinition,
        limit: int = 3,
        context: M4ExecutionContext | None = None,
    ) -> QueryResult:
        """Get sample rows from a table.

        Supports both schema-qualified names (``mimiciv_hosp.patients``) and
        simple names (``patients``).

        Args:
            table_name: Name of the table to sample (may be schema-qualified)
            dataset: The dataset definition
            limit: Maximum number of rows to return

        Returns:
            QueryResult with sample data
        """
        # Sanitize limit
        limit = max(1, min(limit, 100))

        if "." in table_name:
            schema, table = table_name.split(".", 1)
            query = f'SELECT * FROM {schema}."{table}" LIMIT {limit}'
        else:
            query = f'SELECT * FROM "{table_name}" LIMIT {limit}'
        return self.execute_query(query, dataset, context)

    def get_backend_info(
        self,
        dataset: DatasetDefinition,
        context: M4ExecutionContext | None = None,
    ) -> str:
        """Get human-readable information about the current backend.

        Args:
            dataset: The dataset definition

        Returns:
            Formatted string with backend details
        """
        info = (
            f"**Current Backend:** DuckDB (local database)\n**Dataset:** {dataset.name}"
        )
        disclose = (
            context.path_disclosure
            if context
            else os.getenv("M4_PATH_DISCLOSURE", "").lower()
            in {"1", "true", "yes", "on"}
        )
        if disclose:
            try:
                db_path = self._get_db_path(dataset, context)
            except ConnectionError:
                db_path = "unknown"
            info += f"\n**Database Path:** {db_path}"
        return info
