"""Auto-discovery of database connections from multiple sources.

Discovers existing connections from:
  1. ODBC DSNs (Windows registry — server, database, port, driver)
  2. DBeaver data-sources.json (host, port, database, provider)
  3. SSMS registered servers (RegSrvr.xml)
  4. SQL Server Browser service (UDP 1434 — network scan)

Users pick a discovered connection and only need to provide credentials.
"""

from __future__ import annotations

import asyncio
import json
import logging
import os
import platform
import socket
from pathlib import Path
from typing import Any

logger = logging.getLogger(__name__)


def _guess_db_type(driver: str) -> str:
    """Guess database type from driver/provider name."""
    d = driver.lower()
    # Sybase/ASE must be checked BEFORE generic "sap" (which could be HANA)
    if any(k in d for k in ("sybase", "adaptive server", "ase odbc", "jconnect", "freetds")):
        return "sybase"
    elif "sql server" in d or "sqlsrv" in d or "mssql" in d:
        return "sqlserver"
    elif "hana" in d or "hdb" in d or "sap" in d:
        return "hana"
    elif "postgresql" in d or "postgres" in d:
        return "postgresql"
    elif "mysql" in d or "mariadb" in d:
        return "mysql"
    elif "oracle" in d or "ora" in d:
        return "oracle"
    elif "db2" in d or "ibm" in d:
        return "db2"
    return "unknown"


def _guess_source_system(name: str, db_type: str) -> str:
    """Guess the ERP source system from name and db_type."""
    n = name.lower()
    if "ecc" in n or "sap r/3" in n or "sap ecc" in n:
        return "sap_ecc"
    elif "b1" in n or "business one" in n or "sbo" in n:
        return "sap_b1"
    elif "hubspot" in n:
        return "hubspot"
    elif "infor" in n or "m3" in n or "ln" in n:
        return "infor"
    elif db_type == "hana":
        return "sap_b1"
    return ""


def _build_connection_string(dsn: dict) -> str:
    """Build a connection string template from discovered DSN details.

    Uses {username} and {password} placeholders for credential injection.
    """
    from database.db_presets import build_template, DB_PRESETS

    db_type = dsn.get("db_type", "unknown")
    server = dsn.get("server", "")
    port = dsn.get("port", "")
    database = dsn.get("database", "")
    driver = dsn.get("driver", "")

    if db_type in DB_PRESETS:
        return build_template(
            db_type=db_type,
            host=server,
            port=port,
            database=database,
            driver=driver or None,
        )

    # Fallback: DSN-based connection
    dsn_name = dsn.get("dsn_name", "")
    if dsn_name:
        return f"DSN={dsn_name};UID={{username}};PWD={{password}}"

    return ""


# ---------------------------------------------------------------------------
# ODBC DSN Discovery (Windows Registry — full details)
# ---------------------------------------------------------------------------

def discover_odbc_dsns() -> list[dict]:
    """Read ODBC DSNs from Windows registry with full details (server, database, port)."""
    dsns: list[dict] = []

    if platform.system() == "Windows":
        dsns = _read_windows_registry_full()
    else:
        dsns = _read_odbc_ini()

    return dsns


def _read_windows_registry_full() -> list[dict]:
    """Read ODBC DSNs from Windows registry including all subkey details."""
    dsns: list[dict] = []
    try:
        import winreg  # type: ignore[import-not-found]
    except ImportError:
        logger.info("winreg not available — not on Windows")
        return dsns

    locations = [
        (winreg.HKEY_LOCAL_MACHINE, r"SOFTWARE\ODBC\ODBC.INI", "system"),
        (winreg.HKEY_LOCAL_MACHINE, r"SOFTWARE\WOW6432Node\ODBC\ODBC.INI", "system"),
        (winreg.HKEY_CURRENT_USER, r"SOFTWARE\ODBC\ODBC.INI", "user"),
    ]

    for hive, base_path, scope in locations:
        try:
            sources_key = winreg.OpenKey(hive, base_path + r"\ODBC Data Sources")
        except OSError:
            continue

        i = 0
        while True:
            try:
                dsn_name, driver_name, _ = winreg.EnumValue(sources_key, i)
            except OSError:
                break
            i += 1

            # Read the detail subkey for this DSN
            detail = {"dsn_name": dsn_name, "driver": driver_name, "scope": scope}
            try:
                dsn_key = winreg.OpenKey(hive, base_path + "\\" + dsn_name)
                j = 0
                while True:
                    try:
                        val_name, val_data, _ = winreg.EnumValue(dsn_key, j)
                        detail[val_name] = val_data
                        j += 1
                    except OSError:
                        break
                winreg.CloseKey(dsn_key)
            except OSError:
                pass

            db_type = _guess_db_type(driver_name)
            dsns.append({
                "name": dsn_name,
                "source": "ODBC DSN",
                "db_type": db_type,
                "source_system": _guess_source_system(dsn_name, db_type),
                "driver": driver_name,
                "server": detail.get("Server", detail.get("ServerName", "")),
                "port": detail.get("Port", ""),
                "database": detail.get("Database", detail.get("DefaultDatabase", "")),
                "dsn_name": dsn_name,
                "connection_string_template": _build_connection_string({
                    "db_type": db_type,
                    "server": detail.get("Server", detail.get("ServerName", "")),
                    "port": detail.get("Port", ""),
                    "database": detail.get("Database", detail.get("DefaultDatabase", "")),
                    "driver": driver_name,
                    "dsn_name": dsn_name,
                }),
                "needs_credentials": True,
            })
        winreg.CloseKey(sources_key)

    return dsns


def _read_odbc_ini() -> list[dict]:
    """Read ODBC DSNs via pyodbc (Linux/macOS fallback)."""
    dsns: list[dict] = []
    try:
        import pyodbc
        sources = pyodbc.dataSources()
        for name, driver in sources.items():
            db_type = _guess_db_type(driver)
            dsns.append({
                "name": name,
                "source": "ODBC DSN",
                "db_type": db_type,
                "source_system": _guess_source_system(name, db_type),
                "driver": driver,
                "server": "",
                "port": "",
                "database": "",
                "dsn_name": name,
                "connection_string_template": f"DSN={name};UID={{username}};PWD={{password}}",
                "needs_credentials": True,
            })
    except Exception as e:
        logger.info(f"Could not read ODBC sources: {e}")
    return dsns


# ---------------------------------------------------------------------------
# DBeaver data-sources.json Discovery
# ---------------------------------------------------------------------------

def discover_dbeaver() -> list[dict]:
    """Read saved connections from DBeaver's data-sources.json."""
    connections: list[dict] = []

    candidate_dirs: list[Path] = []

    if platform.system() == "Windows":
        appdata = os.environ.get("APPDATA", "")
        userprofile = os.environ.get("USERPROFILE", "")
        if appdata:
            candidate_dirs.append(Path(appdata) / "DBeaverData" / "workspace6" / "General" / ".dbeaver")
        if userprofile:
            candidate_dirs.append(Path(userprofile) / ".dbeaver")
    else:
        home = Path.home()
        candidate_dirs.append(home / ".local" / "share" / "DBeaverData" / "workspace6" / "General" / ".dbeaver")
        candidate_dirs.append(home / ".dbeaver")

    for dbeaver_dir in candidate_dirs:
        ds_file = dbeaver_dir / "data-sources.json"
        if not ds_file.exists():
            continue

        try:
            with open(ds_file, "r", encoding="utf-8") as f:
                data = json.load(f)
        except (json.JSONDecodeError, OSError) as e:
            logger.warning(f"Could not read DBeaver config at {ds_file}: {e}")
            continue

        for conn_id, conn in data.get("connections", {}).items():
            config = conn.get("configuration", {})
            provider = conn.get("provider", "")
            name = conn.get("name", conn_id)
            host = config.get("host", "")
            port = config.get("port", "")
            database = config.get("database", "")
            db_type = _guess_db_type(provider)

            connections.append({
                "name": name,
                "source": "DBeaver",
                "db_type": db_type,
                "source_system": _guess_source_system(name, db_type),
                "driver": conn.get("driver", provider),
                "server": host,
                "port": port,
                "database": database,
                "connection_string_template": _build_connection_string({
                    "db_type": db_type,
                    "server": host,
                    "port": port,
                    "database": database,
                }),
                "needs_credentials": True,
            })

    return connections


# ---------------------------------------------------------------------------
# SSMS Registered Servers Discovery
# ---------------------------------------------------------------------------

def discover_ssms() -> list[dict]:
    """Read registered servers from SQL Server Management Studio."""
    servers: list[dict] = []

    if platform.system() != "Windows":
        return servers

    appdata = os.environ.get("APPDATA", "")
    if not appdata:
        return servers

    import glob
    pattern = os.path.join(appdata, "Microsoft", "SQL Server Management Studio", "*", "RegSrvr.xml")

    for filepath in glob.glob(pattern):
        try:
            import xml.etree.ElementTree as ET
            tree = ET.parse(filepath)
            root = tree.getroot()

            for elem in root.iter():
                tag = elem.tag.split("}")[-1] if "}" in elem.tag else elem.tag
                if tag == "RegisteredServer":
                    name_el = None
                    server_el = None
                    for child in elem.iter():
                        ctag = child.tag.split("}")[-1] if "}" in child.tag else child.tag
                        if ctag == "Name" and name_el is None:
                            name_el = child
                        elif ctag == "ServerName" and server_el is None:
                            server_el = child

                    if server_el is not None and server_el.text:
                        server_name = server_el.text
                        display_name = name_el.text if name_el is not None else server_name
                        servers.append({
                            "name": display_name,
                            "source": "SSMS",
                            "db_type": "sqlserver",
                            "source_system": _guess_source_system(display_name, "sqlserver"),
                            "driver": "ODBC Driver 17 for SQL Server",
                            "server": server_name,
                            "port": "",
                            "database": "",
                            "connection_string_template": _build_connection_string({
                                "db_type": "sqlserver",
                                "server": server_name,
                            }),
                            "needs_credentials": True,
                        })
        except Exception as e:
            logger.warning(f"Could not read SSMS RegSrvr.xml at {filepath}: {e}")

    return servers


# ---------------------------------------------------------------------------
# SQL Server Browser Discovery (UDP 1434)
# ---------------------------------------------------------------------------

def _discover_sql_browser(targets: list[str] | None = None, timeout: float = 3) -> list[dict]:
    """Discover SQL Server instances via the SQL Server Browser service (UDP 1434).

    Args:
        targets: Specific IPs/hostnames to query. If None, does a broadcast.
        timeout: Seconds to wait for responses.
    """
    instances: list[dict] = []

    if targets is None:
        targets = ["255.255.255.255"]

    for target in targets:
        try:
            sock = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
            sock.settimeout(timeout)
            if target == "255.255.255.255":
                sock.setsockopt(socket.SOL_SOCKET, socket.SO_BROADCAST, 1)

            sock.sendto(b"\x02", (target, 1434))

            try:
                while True:
                    data, addr = sock.recvfrom(4096)
                    if len(data) < 3:
                        continue

                    payload = data[3:].decode("ascii", errors="ignore")
                    for record in payload.split(";;"):
                        if not record.strip():
                            continue
                        parts = record.strip(";").split(";")
                        info: dict[str, str] = {}
                        for k in range(0, len(parts) - 1, 2):
                            info[parts[k]] = parts[k + 1]

                        server_name = info.get("ServerName", addr[0])
                        instance = info.get("InstanceName", "")
                        tcp_port = info.get("tcp", "1433")
                        full_server = f"{server_name}\\{instance}" if instance else server_name

                        instances.append({
                            "name": f"{server_name} ({instance})" if instance else server_name,
                            "source": "SQL Server Browser",
                            "db_type": "sqlserver",
                            "source_system": "",
                            "driver": "ODBC Driver 17 for SQL Server",
                            "server": full_server,
                            "port": tcp_port,
                            "database": "",
                            "version": info.get("Version", ""),
                            "connection_string_template": _build_connection_string({
                                "db_type": "sqlserver",
                                "server": full_server,
                                "port": tcp_port,
                            }),
                            "needs_credentials": True,
                        })
            except socket.timeout:
                pass
            finally:
                sock.close()
        except Exception as e:
            logger.info(f"SQL Server Browser scan failed for {target}: {e}")

    return instances


# ---------------------------------------------------------------------------
# Unified Discovery
# ---------------------------------------------------------------------------

class ConnectionDiscovery:
    """Aggregates database connections from all discoverable sources."""

    async def discover_all(self, scan_network: bool = False) -> list[dict]:
        """Discover all available database connections.

        Args:
            scan_network: If True, also probes SQL Server Browser (UDP 1434).
                         Off by default because it requires network access.

        Returns:
            List of discovered connections, each with:
              - name, source, db_type, source_system, driver
              - server, port, database
              - connection_string_template (with {username}/{password} placeholders)
              - needs_credentials (always True for discovered connections)
        """
        all_connections: list[dict] = []

        # Run CPU/IO-bound discovery in threads
        loop = asyncio.get_event_loop()

        results = await asyncio.gather(
            loop.run_in_executor(None, discover_odbc_dsns),
            loop.run_in_executor(None, discover_dbeaver),
            loop.run_in_executor(None, discover_ssms),
            return_exceptions=True,
        )

        for result in results:
            if isinstance(result, Exception):
                logger.warning(f"Discovery source failed: {result}")
            else:
                all_connections.extend(result)

        # Optional network scan
        if scan_network:
            try:
                browser_results = await loop.run_in_executor(None, _discover_sql_browser)
                all_connections.extend(browser_results)
            except Exception as e:
                logger.warning(f"SQL Server Browser scan failed: {e}")

        # Deduplicate by (server, port, database)
        return self._deduplicate(all_connections)

    @staticmethod
    def _deduplicate(connections: list[dict]) -> list[dict]:
        """Remove duplicate connections, preferring richer sources."""
        seen: dict[str, dict] = {}
        # Source priority: DBeaver > ODBC > SSMS > Browser
        priority = {"DBeaver": 4, "ODBC DSN": 3, "SSMS": 2, "SQL Server Browser": 1}

        for conn in connections:
            server = (conn.get("server") or "").lower().strip()
            db = (conn.get("database") or "").lower().strip()
            key = f"{server}|{db}" if db else f"{server}|{conn.get('name', '')}"

            existing = seen.get(key)
            if existing is None:
                seen[key] = conn
            else:
                # Keep the one from the higher-priority source
                if priority.get(conn["source"], 0) > priority.get(existing["source"], 0):
                    seen[key] = conn

        return list(seen.values())

    @staticmethod
    def fill_credentials(template: str, username: str, password: str) -> str:
        """Replace {username} and {password} placeholders in a connection string template."""
        return template.replace("{username}", username).replace("{password}", password)


# Backward compatibility: keep ODBCDiscovery for existing code that imports it
class ODBCDiscovery:
    """Legacy wrapper — use ConnectionDiscovery instead."""

    @staticmethod
    async def list_dsns() -> list[dict]:
        return discover_odbc_dsns()

    @staticmethod
    async def test_dsn(dsn_name: str) -> dict:
        import asyncio

        def _test():
            try:
                import pyodbc
                conn = pyodbc.connect(f"DSN={dsn_name}", timeout=10)
                cursor = conn.cursor()
                cursor.execute("SELECT 1")
                conn.close()
                return {"dsn": dsn_name, "status": "ok"}
            except Exception as e:
                return {"dsn": dsn_name, "status": "error", "message": str(e)}

        return await asyncio.to_thread(_test)
