"""Database type presets — centralized config for all supported remote DB types.

Each preset defines default port, preferred drivers, session init commands, etc.
Used by discovery, connection creation, remote executor, and the frontend form.
"""

from __future__ import annotations

from dataclasses import dataclass, field


@dataclass
class DbPreset:
    """Configuration preset for a database type."""

    key: str  # Internal key stored in DB: 'sqlserver', 'sybase', etc.
    label: str  # Human-readable: 'SQL Server', 'SAP Sybase ASE', etc.
    default_port: int
    drivers: list[str]  # Preferred ODBC drivers in priority order (first match wins)
    session_init: list[str] = field(default_factory=list)  # SQL run right after connect
    schema_dialect: str = "ansi"  # Which query style for schema discovery
    tds_version: str | None = None  # For FreeTDS connections
    auth_modes: list[str] = field(default_factory=lambda: ["sql"])
    uses_pymysql: bool = False  # True = pymysql, False = pyodbc
    uses_libpq: bool = False  # True = psycopg/asyncpg URI, not ODBC
    placeholder: dict[str, str] = field(default_factory=dict)
    notes: str = ""


DB_PRESETS: dict[str, DbPreset] = {
    "sqlserver": DbPreset(
        key="sqlserver",
        label="SQL Server",
        default_port=1433,
        drivers=[
            "ODBC Driver 18 for SQL Server",
            "ODBC Driver 17 for SQL Server",
            "SQL Server Native Client 11.0",
            "SQL Server",
        ],
        session_init=["SET NOCOUNT ON", "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED"],
        schema_dialect="sqlserver",
        auth_modes=["sql", "windows"],
        placeholder={"host": "server\\instance or IP", "database": "master"},
        notes="For named instances use host\\INSTANCE (port resolved by SQL Browser).",
    ),
    "sybase": DbPreset(
        key="sybase",
        label="SAP Sybase ASE",
        default_port=5000,
        drivers=[
            "Adaptive Server Enterprise",
            "SAP ASE ODBC Driver",
            "Sybase ASE ODBC Driver",
            "FreeTDS",
        ],
        session_init=["SET NOCOUNT ON", "SET QUOTED_IDENTIFIER ON"],
        schema_dialect="sybase",
        tds_version="5.0",
        placeholder={"host": "hostname or IP", "database": "master"},
        notes="SAP ECC on Sybase uses TDS 5.0. FreeTDS works on Linux/macOS.",
    ),
    "hana": DbPreset(
        key="hana",
        label="SAP HANA",
        default_port=30015,
        drivers=["HDBODBC", "HDBODBC32"],
        schema_dialect="hana",
        placeholder={"host": "hostname or IP", "database": "(schema — optional)"},
        notes="Uses SERVERNODE=host:port format.",
    ),
    "postgresql": DbPreset(
        key="postgresql",
        label="PostgreSQL",
        default_port=5432,
        drivers=["PostgreSQL Unicode", "PostgreSQL ANSI", "psqlODBC"],
        schema_dialect="postgresql",
        uses_libpq=True,
        placeholder={"host": "localhost", "database": "postgres"},
        notes="Uses libpq URI (not ODBC).",
    ),
    "mysql": DbPreset(
        key="mysql",
        label="MySQL / MariaDB",
        default_port=3306,
        drivers=["MySQL ODBC 8.0 Unicode Driver", "MySQL ODBC 5.3 Unicode Driver"],
        schema_dialect="mysql",
        uses_pymysql=True,
        placeholder={"host": "localhost", "database": "mysql"},
        notes="Uses pymysql driver. MariaDB compatible.",
    ),
    "oracle": DbPreset(
        key="oracle",
        label="Oracle",
        default_port=1521,
        drivers=[
            "Oracle in OraDB21Home1",
            "Oracle in OraDB19Home1",
            "Oracle in instantclient_21_0",
        ],
        schema_dialect="ansi",
        placeholder={"host": "hostname or IP", "database": "ORCL (SID or service)"},
        notes="DBQ uses host:port/service_name format.",
    ),
    "db2": DbPreset(
        key="db2",
        label="IBM Db2",
        default_port=50000,
        drivers=["IBM DB2 ODBC DRIVER", "IBM DB2 ODBC DRIVER - DB2COPY1"],
        schema_dialect="ansi",
        placeholder={"host": "hostname or IP", "database": "SAMPLE"},
        notes="Uses HOSTNAME (not SERVER) and PROTOCOL=TCPIP.",
    ),
}


def get_preset(db_type: str) -> DbPreset | None:
    """Get preset by db_type key."""
    return DB_PRESETS.get(db_type)


def build_connection_string(
    db_type: str,
    host: str,
    port: int | str,
    database: str,
    username: str,
    password: str,
    driver: str | None = None,
    extra_params: str | None = None,
) -> str:
    """Build a connection string from structured parameters using preset rules."""
    preset = DB_PRESETS.get(db_type)
    if not preset:
        raise ValueError(f"Unknown db_type: {db_type}. Available: {list(DB_PRESETS.keys())}")

    drv = driver or preset.drivers[0]
    port_val = int(port) if port else preset.default_port

    if db_type == "sqlserver":
        # Named instance (host\INSTANCE) → don't append port
        if "\\" in host:
            server = host
        else:
            server = f"{host},{port_val}"
        cs = f"DRIVER={{{drv}}};SERVER={server};DATABASE={database};UID={username};PWD={password}"

    elif db_type == "sybase":
        cs = f"DRIVER={{{drv}}};SERVER={host};PORT={port_val};DATABASE={database};UID={username};PWD={password}"
        if preset.tds_version and "freetds" in drv.lower():
            cs += f";TDS_Version={preset.tds_version}"

    elif db_type == "hana":
        cs = f"DRIVER={{{drv}}};SERVERNODE={host}:{port_val};UID={username};PWD={password}"
        if database:
            cs += f";CURRENTSCHEMA={database}"

    elif db_type == "postgresql":
        cs = f"postgresql://{username}:{password}@{host}:{port_val}/{database}"

    elif db_type == "mysql":
        cs = f"mysql://{username}:{password}@{host}:{port_val}/{database}"

    elif db_type == "oracle":
        cs = f"DRIVER={{{drv}}};DBQ={host}:{port_val}/{database};UID={username};PWD={password}"

    elif db_type == "db2":
        cs = (
            f"DRIVER={{{drv}}};HOSTNAME={host};PORT={port_val};"
            f"DATABASE={database};UID={username};PWD={password};PROTOCOL=TCPIP"
        )
    else:
        raise ValueError(f"Unknown db_type: {db_type}")

    if extra_params:
        if "://" in cs:
            sep = "&" if "?" in cs else "?"
            cs += f"{sep}{extra_params}"
        else:
            cs += f";{extra_params}"

    return cs


def build_template(
    db_type: str,
    host: str,
    port: int | str,
    database: str,
    driver: str | None = None,
) -> str:
    """Build a connection string template with {username}/{password} placeholders."""
    return build_connection_string(
        db_type=db_type,
        host=host,
        port=port,
        database=database,
        username="{username}",
        password="{password}",
        driver=driver,
    )


def detect_driver(db_type: str, available_drivers: list[str]) -> str | None:
    """Find the best available ODBC driver for a db_type.

    Searches available_drivers using the preset's preferred driver list.
    Returns the first match or None.
    """
    preset = DB_PRESETS.get(db_type)
    if not preset:
        return None

    for preferred in preset.drivers:
        for installed in available_drivers:
            if preferred.lower() in installed.lower():
                return installed
    return None


def presets_for_api() -> list[dict]:
    """Return presets as dicts for the REST API / frontend."""
    return [
        {
            "key": p.key,
            "label": p.label,
            "default_port": p.default_port,
            "drivers": p.drivers,
            "auth_modes": p.auth_modes,
            "placeholder": p.placeholder,
            "notes": p.notes,
        }
        for p in DB_PRESETS.values()
    ]
