# #region Core.ConnectionService [C:3] [TYPE Module] [SEMANTICS settings,connections,service]
# @ingroup Core
# @BRIEF CRUD service for DatabaseConnection configurations with encrypted password management
#        and connectivity testing. Uses ConfigManager for persistence, EncryptionManager for secrets.
# @LAYER Core
# @RATIONALE Connections are cross-cutting infrastructure shared across features (translate, migration).
#            Centralized service prevents scattered encryption logic and ensures referential integrity.
#
# #region ConnectionService.create_connection [C:3] [TYPE Function] [SEMANTICS settings,connections,create]
# @BRIEF Create a new database connection with encrypted password.
# @PRE ConfigManager is initialized. EncryptionManager is available.
# @POST Connection appended to GlobalSettings.connections with encrypted password. Saved to DB.
# @SIDE_EFFECT Encrypts password using EncryptionManager. Persists config via ConfigManager.save().
# #endregion
#
# #region ConnectionService.update_connection [C:3] [TYPE Function] [SEMANTICS settings,connections,update]
# @BRIEF Update an existing database connection. Empty password = keep existing.
# @PRE Connection exists. ConfigManager initialized.
# @POST Connection fields updated. Password encrypted if changed. Saved to DB.
# @SIDE_EFFECT Re-encrypts password if changed. Persists config.
# #endregion
#
# #region ConnectionService.delete_connection [C:3] [TYPE Function] [SEMANTICS settings,connections,delete]
# @BRIEF Delete a database connection. Blocked if referenced by active TranslationJobs.
# @PRE Connection exists. No active TranslationJob.connection_id matches this connection.
# @POST Connection removed from GlobalSettings.connections. Saved to DB. Returns blocking job names if blocked.
# @SIDE_EFFECT Persists config. Checks job references.
# @REJECTED Cascade-delete jobs — would orphan user data. Soft block with job names instead.
# #endregion
#
# #region ConnectionService.test_connection [C:3] [TYPE Function] [SEMANTICS settings,connections,test]
# @BRIEF Test connectivity to a saved database connection. Opens temp native driver, runs SELECT 1.
# @PRE Connection exists with valid config and decrypted password.
# @POST Returns {success, latency_ms, db_version, error}. Connection closed after test.
# @SIDE_EFFECT Opens/closes native DB connection. Network I/O.
# #endregion

import logging
import time as time_module
from typing import Any

from .config_manager import ConfigManager
from .config_models import DatabaseConnection
from .encryption import EncryptionManager

logger = logging.getLogger(__name__)

MASKED_PASSWORD = "********"


# #region lookup_connection [C:2] [TYPE Function]
# @BRIEF Find a DatabaseConnection by id in the connections list. Returns index + connection or -1 + None.
def _lookup_connection(config_manager: ConfigManager, connection_id: str) -> tuple[int, DatabaseConnection | None]:
    for idx, conn in enumerate(config_manager.config.settings.connections):
        if conn.id == connection_id:
            return idx, conn
    return -1, None


# #endregion _lookup_connection


# #region _validate_name_uniqueness [C:2] [TYPE Function]
# @BRIEF Check that no existing connection has the same name (case-insensitive). Exclude connection_id if provided.
def _validate_name_uniqueness(
    config_manager: ConfigManager, name: str, exclude_id: str | None = None
) -> str | None:
    for conn in config_manager.config.settings.connections:
        if conn.name.strip().lower() == name.strip().lower():
            if exclude_id is not None and conn.id == exclude_id:
                continue
            return f"Connection name '{name}' already exists"
    return None


# #endregion _validate_name_uniqueness


# #region ConnectionService [C:3] [TYPE Class] [SEMANTICS settings,connections,service]
# @ingroup Core
# @BRIEF Service class wrapping all DatabaseConnection CRUD + test operations.
class ConnectionService:
    def __init__(self, config_manager: ConfigManager):
        self.config_manager = config_manager
        self._encryption: EncryptionManager | None = None

    # #region _get_encryption [C:1] [TYPE Function]
    def _get_encryption(self) -> EncryptionManager | None:
        if self._encryption is None:
            try:
                self._encryption = EncryptionManager()
            except (RuntimeError, Exception):
                self._encryption = None
        return self._encryption

    # #endregion _get_encryption

    # #region _encrypt_password [C:2] [TYPE Function]
    def _encrypt_password(self, password: str) -> str:
        encryption = self._get_encryption()
        if encryption is None:
            return password
        try:
            return encryption.encrypt(password)
        except Exception:
            return password

    # #endregion _encrypt_password

    # #region _decrypt_password [C:2] [TYPE Function]
    def _decrypt_password(self, password: str) -> str:
        encryption = self._get_encryption()
        if encryption is None:
            return password
        if password == MASKED_PASSWORD:
            return password
        try:
            return encryption.decrypt(password)
        except Exception:
            return password

    # #endregion _decrypt_password

    # #region list_connections [C:2] [TYPE Function] [SEMANTICS settings,connections,list]
    # @BRIEF Return all connections with passwords masked. Used for API responses.
    # @POST Returns list[dict] with password replaced by MASKED_PASSWORD. No side effects.
    def list_connections(self) -> list[dict[str, Any]]:
        result = []
        for conn in self.config_manager.config.settings.connections:
            d = conn.model_dump()
            d["password"] = MASKED_PASSWORD
            d["used_by"] = self._count_job_references(conn.id)
            result.append(d)
        return result

    # #endregion list_connections

    # #region get_connection [C:2] [TYPE Function] [SEMANTICS settings,connections,get]
    # @BRIEF Return a single connection by id with password DECRYPTED (for internal use — never exposed to API).
    # @PRE Connection exists.
    # @POST Returns DatabaseConnection model with decrypted password, or None if not found.
    def get_connection(self, connection_id: str) -> DatabaseConnection | None:
        _, conn = _lookup_connection(self.config_manager, connection_id)
        if conn is None:
            return None
        decrypted_pwd = self._decrypt_password(conn.password)
        conn.password = decrypted_pwd
        return conn

    # #endregion get_connection

    # #region create_connection [C:3] [TYPE Function] [SEMANTICS settings,connections,create]
    # @BRIEF Create a new database connection. Validates name uniqueness. Encrypts password.
    # @PRE Provided data has all required fields. Name does not conflict.
    # @POST Connection created and persisted. Returns created DatabaseConnection dict with masked password.
    # @SIDE_EFFECT Password encrypted. ConfigManager.save() called.
    def create_connection(self, data: dict[str, Any]) -> dict[str, Any]:
        name_error = _validate_name_uniqueness(self.config_manager, data.get("name", ""))
        if name_error:
            raise ValueError(name_error)

        raw_password = data.get("password", "")
        encrypted_password = self._encrypt_password(raw_password) if raw_password else raw_password

        conn = DatabaseConnection(
            name=data.get("name", ""),
            host=data.get("host", ""),
            port=int(data.get("port", 5432)),
            database=data.get("database", ""),
            username=data.get("username", ""),
            password=encrypted_password,
            dialect=data.get("dialect", "postgresql"),
            extra_params=data.get("extra_params", {}),
            pool_size=int(data.get("pool_size", 5)),
        )

        self.config_manager.config.settings.connections.append(conn)
        self.config_manager.save()

        result = conn.model_dump()
        result["password"] = MASKED_PASSWORD
        return result

    # #endregion create_connection

    # #region update_connection [C:3] [TYPE Function] [SEMANTICS settings,connections,update]
    # @BRIEF Update an existing connection. Empty/masked password = keep existing.
    # @PRE Connection exists. Name does not conflict (if changed).
    # @POST Connection updated and persisted. Password re-encrypted if changed.
    # @SIDE_EFFECT ConfigManager.save() called.
    def update_connection(self, connection_id: str, data: dict[str, Any]) -> dict[str, Any]:
        idx, existing = _lookup_connection(self.config_manager, connection_id)
        if existing is None:
            raise ValueError(f"Connection '{connection_id}' not found")

        # Check name uniqueness if name changed
        new_name = data.get("name", existing.name)
        if new_name.strip().lower() != existing.name.strip().lower():
            name_error = _validate_name_uniqueness(self.config_manager, new_name, exclude_id=connection_id)
            if name_error:
                raise ValueError(name_error)

        # Build updated fields
        update_data = existing.model_dump()
        for field in ("name", "host", "port", "database", "username", "dialect", "extra_params", "pool_size"):
            if field in data and data[field] is not None:
                update_data[field] = data[field]

        # Handle password: empty or masked = keep existing
        raw_password = data.get("password", "")
        if raw_password and raw_password != MASKED_PASSWORD:
            update_data["password"] = self._encrypt_password(raw_password)
        # else: keep existing encrypted password

        update_data["updated_at"] = time_module.strftime("%Y-%m-%dT%H:%M:%S", time_module.gmtime())
        updated_conn = DatabaseConnection.model_validate(update_data)
        self.config_manager.config.settings.connections[idx] = updated_conn
        self.config_manager.save()

        result = updated_conn.model_dump()
        result["password"] = MASKED_PASSWORD
        return result

    # #endregion update_connection

    # #region delete_connection [C:3] [TYPE Function] [SEMANTICS settings,connections,delete]
    # @BRIEF Delete a connection. Blocked if referenced by any TranslationJob with active status.
    # @PRE Connection exists.
    # @POST Connection removed. Returns blocking_jobs list if blocked.
    # @SIDE_EFFECT ConfigManager.save() called on success.
    def delete_connection(self, connection_id: str) -> dict[str, Any]:
        blocking_jobs = self._find_blocking_jobs(connection_id)
        if blocking_jobs:
            return {"success": False, "blocked": True, "blocking_jobs": blocking_jobs}

        idx, _ = _lookup_connection(self.config_manager, connection_id)
        if idx == -1:
            raise ValueError(f"Connection '{connection_id}' not found")

        self.config_manager.config.settings.connections.pop(idx)
        self.config_manager.save()
        return {"success": True, "blocked": False}

    # #endregion delete_connection

    # #region test_connection [C:3] [TYPE Function] [SEMANTICS settings,connections,test]
    # @BRIEF Test connectivity to a saved connection. Opens native driver, runs SELECT 1.
    # @PRE Connection exists and is reachable from the superset-tools backend.
    # @POST Returns {success, latency_ms, db_version, error}. Connection always closed.
    # @SIDE_EFFECT Opens/closes native DB connection. Network I/O. May trigger auth.
    async def test_connection(self, connection_id: str) -> dict[str, Any]:
        conn = self.get_connection(connection_id)
        if conn is None:
            return {"success": False, "error": f"Connection '{connection_id}' not found"}

        start = time_module.time()
        try:
            # Use dialect-appropriate test
            if conn.dialect in ("postgresql",):
                version = await self._test_postgresql(conn)
            elif conn.dialect == "clickhouse":
                version = self._test_clickhouse(conn)
            elif conn.dialect == "mysql":
                version = self._test_mysql(conn)
            else:
                return {"success": False, "error": f"Unsupported dialect: {conn.dialect}"}

            elapsed_ms = int((time_module.time() - start) * 1000)
            return {
                "success": True,
                "latency_ms": elapsed_ms,
                "db_version": version,
            }
        except Exception as e:
            elapsed_ms = int((time_module.time() - start) * 1000)
            return {
                "success": False,
                "latency_ms": elapsed_ms,
                "error": str(e),
            }

    # #endregion test_connection

    # #region _test_postgresql [C:2] [TYPE Function] [SEMANTICS settings,connections,test-pg]
    # @BRIEF Test PostgreSQL connectivity using asyncpg. Returns server version.
    async def _test_postgresql(self, conn: DatabaseConnection) -> str:
        try:
            import asyncpg  # type: ignore[import-untyped]
        except ImportError:
            raise RuntimeError("asyncpg is not installed. Add to requirements.txt.")

        attempt = await asyncpg.connect(
            host=conn.host,
            port=conn.port,
            user=conn.username,
            password=conn.password,
            database=conn.database,
            timeout=10,
        )
        try:
            row = await attempt.fetchrow("SELECT version()")
            version = row[0] if row else "Unknown"
            return version
        finally:
            await attempt.close()

    # #endregion _test_postgresql

    # #region _test_clickhouse [C:2] [TYPE Function] [SEMANTICS settings,connections,test-ch]
    # @BRIEF Test ClickHouse connectivity using clickhouse-connect. Returns server version.
    def _test_clickhouse(self, conn: DatabaseConnection) -> str:
        try:
            import clickhouse_connect  # type: ignore[import-untyped]
        except ImportError:
            raise RuntimeError("clickhouse-connect is not installed. Add to requirements.txt.")

        client = clickhouse_connect.get_client(
            host=conn.host,
            port=conn.port,
            username=conn.username,
            password=conn.password,
            database=conn.database,
            connect_timeout=10,
        )
        try:
            result = client.query("SELECT version()")
            if result.result_rows:
                return str(result.result_rows[0][0])
            return "Unknown"
        finally:
            client.close()

    # #endregion _test_clickhouse

    # #region _test_mysql [C:2] [TYPE Function] [SEMANTICS settings,connections,test-mysql]
    # @BRIEF Test MySQL connectivity using asyncmy or pymysql.
    def _test_mysql(self, conn: DatabaseConnection) -> str:
        try:
            import pymysql  # type: ignore[import-untyped]
        except ImportError:
            raise RuntimeError("pymysql is not installed.")

        connection = pymysql.connect(
            host=conn.host,
            port=conn.port,
            user=conn.username,
            password=conn.password,
            database=conn.database,
            connect_timeout=10,
        )
        try:
            with connection.cursor() as cursor:
                cursor.execute("SELECT version()")
                row = cursor.fetchone()
                return str(row[0]) if row else "Unknown"
        finally:
            connection.close()

    # #endregion _test_mysql

    # #region _count_job_references [C:2] [TYPE Function] [SEMANTICS settings,connections,refs]
    # @BRIEF Count how many active TranslationJobs reference this connection.
    def _count_job_references(self, connection_id: str) -> int:
        """Count active TranslationJobs referencing this connection."""
        try:
            from ..models.translate import TranslationJob
            from ..core.database import SessionLocal

            db = SessionLocal()
            try:
                count = db.query(TranslationJob).filter(
                    TranslationJob.connection_id == connection_id,
                    TranslationJob.is_active == True,  # noqa: E712
                ).count()
                return count
            finally:
                db.close()
        except Exception:
            return 0

    # #endregion _count_job_references

    # #region _find_blocking_jobs [C:2] [TYPE Function] [SEMANTICS settings,connections,blocking]
    # @BRIEF Find names of active TranslationJobs that reference this connection.
    def _find_blocking_jobs(self, connection_id: str) -> list[str]:
        try:
            from ..models.translate import TranslationJob
            from ..core.database import SessionLocal

            db = SessionLocal()
            try:
                jobs = db.query(TranslationJob).filter(
                    TranslationJob.connection_id == connection_id,
                    TranslationJob.is_active == True,  # noqa: E712
                ).all()
                return [job.name for job in jobs]
            finally:
                db.close()
        except Exception:
            return []

    # #endregion _find_blocking_jobs

    # #region validate_connection_refs [C:2] [TYPE Function] [SEMANTICS settings,connections,validate]
    # @BRIEF Scan all TranslationJobs and verify referenced connection IDs exist. Log orphans.
    # @POST Returns list of orphaned job names with missing connection IDs.
    def validate_connection_refs(self) -> list[dict[str, str]]:
        """Verify all TranslationJob.connection_id values reference existing connections. Returns orphans."""
        orphans = []
        try:
            from ..models.translate import TranslationJob
            from ..core.database import SessionLocal

            db = SessionLocal()
            try:
                valid_ids = {c.id for c in self.config_manager.config.settings.connections}
                jobs = db.query(TranslationJob).filter(
                    TranslationJob.connection_id.isnot(None),
                    TranslationJob.connection_id != "",
                ).all()
                for job in jobs:
                    if job.connection_id not in valid_ids:
                        orphans.append({
                            "job_id": job.id,
                            "job_name": job.name,
                            "missing_connection_id": job.connection_id,
                        })
                        logger.warning(
                            "Orphaned connection reference: job '%s' (%s) references "
                            "connection_id '%s' which does not exist",
                            job.name, job.id, job.connection_id,
                        )
            finally:
                db.close()
        except Exception as e:
            logger.error("Failed to validate connection refs: %s", e)
        return orphans

    # #endregion validate_connection_refs


# #endregion ConnectionService
# #endregion Core.ConnectionService
