"""External Connections domain — business logic."""
from __future__ import annotations

from datetime import datetime, timezone
from typing import Any, Optional
from uuid import UUID

import httpx
import structlog

from typing import TYPE_CHECKING

from src.config import get_settings
from src.core.exceptions import ConflictError, NotFoundError, ValidationError
from src.core.security.encryption import decrypt_value, encrypt_value
from src.domain.external_connections.models import ProjectExternalConnection
from src.domain.external_connections.repository import ExternalConnectionRepository
from src.domain.external_connections.schemas import (
    ConnectionResponse,
    CreateConnectionRequest,
    SchemaSnapshotResponse,
    TestConnectionResponse,
    UpdateConnectionRequest,
)
from src.integrations.cloud_control_client import CloudControlClient

if TYPE_CHECKING:
    from src.domain.cloud_infra.service import CfCredentials, CloudInfraService

logger = structlog.get_logger()

# Connection count limits per plan tier (enforced at create time)
_PLAN_MAX_CONNECTIONS: dict[str, int] = {
    "free": 1,
    "starter": 3,
    "pro": 10,
}
_DEFAULT_MAX_CONNECTIONS = 1
_UNLIMITED = -1

_CF_HYPERDRIVE_API = "https://api.cloudflare.com/client/v4"


class ExternalConnectionService:
    def __init__(
        self,
        *,
        repo: ExternalConnectionRepository,
        cloud_client: CloudControlClient,
        cloud_infra_svc: Optional["CloudInfraService"] = None,
    ) -> None:
        self._repo = repo
        self._cloud = cloud_client
        self._infra = cloud_infra_svc

    async def _resolve_cf_creds(self, workspace_id: UUID) -> Optional["CfCredentials"]:
        """Return workspace-scoped CF credentials if CloudInfraService is available."""
        if not self._infra:
            return None
        from src.core.exceptions import NotFoundError as _NFE
        try:
            return await self._infra.get_credentials_for_workspace(workspace_id)
        except (_NFE, ValueError):
            return None

    # ------------------------------------------------------------------
    # CRUD
    # ------------------------------------------------------------------

    async def list_connections(self, project_id: UUID) -> list[ConnectionResponse]:
        rows = await self._repo.list_for_project(project_id)
        return [_connection_response(r) for r in rows]

    async def get_connection(self, project_id: UUID, name: str) -> ConnectionResponse:
        row = await self._repo.get_by_name(project_id, name)
        if not row:
            raise NotFoundError(f"Connection {name!r} not found")
        return _connection_response(row)

    async def create_connection(
        self,
        project_id: UUID,
        workspace_id: UUID,
        body: CreateConnectionRequest,
        plan_slug: Optional[str] = None,
    ) -> ConnectionResponse:
        existing = await self._repo.get_by_name(project_id, body.name)
        if existing:
            raise ConflictError(f"Connection {body.name!r} already exists for this project")

        _enforce_connection_limit(
            count=await self._repo.count_for_project(project_id),
            plan_slug=plan_slug,
        )

        _validate_connection_fields(body)

        now = datetime.now(timezone.utc)
        fields: dict[str, Any] = dict(
            project_id=project_id,
            workspace_id=workspace_id,
            name=body.name,
            type=body.type,
            status="pending",
            readonly=body.readonly,
            created_at=now,
            updated_at=now,
        )

        if body.type in ("postgres", "mysql"):
            fields.update(
                host=body.host,
                port=body.port,
                database=body.database,
                username=body.username,
                password_enc=encrypt_value(body.password) if body.password else None,
                ssl_mode=body.ssl_mode,
                max_rows_per_query=body.max_rows_per_query,
            )
        else:
            fields.update(
                http_base_url=body.http_base_url,
                http_auth_type=body.http_auth_type,
                http_auth_secret_enc=(
                    encrypt_value(body.http_auth_secret)
                    if body.http_auth_secret
                    else None
                ),
                http_default_headers_json=body.http_default_headers,
                max_response_size_bytes=body.max_response_size_bytes,
            )

        row = await self._repo.create(**fields)

        # Provision CF Hyperdrive for SQL connections
        if body.type in ("postgres", "mysql") and body.password:
            cf_creds = await self._resolve_cf_creds(workspace_id)
            hyperdrive_id = await _create_hyperdrive(
                conn_type=body.type,
                host=body.host,
                port=body.port,
                database=body.database,
                username=body.username,
                password=body.password,
                ssl_mode=body.ssl_mode,
                cf_creds=cf_creds,
            )
            row = await self._repo.update(row, hyperdrive_id=hyperdrive_id)

        await self._push_to_cloud(project_id, row)
        logger.info("external_connection_created", project_id=str(project_id), name=body.name)
        return _connection_response(row)

    async def update_connection(
        self,
        project_id: UUID,
        name: str,
        body: UpdateConnectionRequest,
    ) -> ConnectionResponse:
        row = await self._repo.get_by_name(project_id, name)
        if not row:
            raise NotFoundError(f"Connection {name!r} not found")

        updates: dict[str, Any] = {"updated_at": datetime.now(timezone.utc)}

        if row.type in ("postgres", "mysql"):
            if body.host is not None:
                updates["host"] = body.host
            if body.port is not None:
                updates["port"] = body.port
            if body.database is not None:
                updates["database"] = body.database
            if body.username is not None:
                updates["username"] = body.username
            if body.ssl_mode is not None:
                updates["ssl_mode"] = body.ssl_mode
            if body.max_rows_per_query is not None:
                updates["max_rows_per_query"] = body.max_rows_per_query

            # Password rotation — update Hyperdrive config in place
            if body.password is not None:
                updates["password_enc"] = encrypt_value(body.password)
                host = body.host or row.host
                port = body.port or row.port
                db = body.database or row.database
                user = body.username or row.username
                ssl = body.ssl_mode or row.ssl_mode or "require"
                cf_creds = await self._resolve_cf_creds(row.workspace_id)

                if row.hyperdrive_id:
                    await _update_hyperdrive(
                        hyperdrive_id=row.hyperdrive_id,
                        conn_type=row.type,
                        host=host,
                        port=port,
                        database=db,
                        username=user,
                        password=body.password,
                        ssl_mode=ssl,
                        cf_creds=cf_creds,
                    )
                else:
                    hyperdrive_id = await _create_hyperdrive(
                        conn_type=row.type,
                        host=host,
                        port=port,
                        database=db,
                        username=user,
                        password=body.password,
                        ssl_mode=ssl,
                        cf_creds=cf_creds,
                    )
                    updates["hyperdrive_id"] = hyperdrive_id
        else:
            if body.http_base_url is not None:
                updates["http_base_url"] = body.http_base_url
            if body.http_auth_type is not None:
                updates["http_auth_type"] = body.http_auth_type
            if body.http_auth_secret is not None:
                updates["http_auth_secret_enc"] = encrypt_value(body.http_auth_secret)
            if body.http_default_headers is not None:
                updates["http_default_headers_json"] = body.http_default_headers
            if body.max_response_size_bytes is not None:
                updates["max_response_size_bytes"] = body.max_response_size_bytes

        if body.readonly is not None:
            updates["readonly"] = body.readonly

        row = await self._repo.update(row, **updates)
        await self._push_to_cloud(project_id, row)
        logger.info("external_connection_updated", project_id=str(project_id), name=name)
        return _connection_response(row)

    async def delete_connection(self, project_id: UUID, name: str) -> None:
        row = await self._repo.get_by_name(project_id, name)
        if not row:
            raise NotFoundError(f"Connection {name!r} not found")

        if row.hyperdrive_id:
            cf_creds = await self._resolve_cf_creds(row.workspace_id)
            await _delete_hyperdrive(row.hyperdrive_id, cf_creds=cf_creds)

        await self._repo.delete(row)
        await self._cloud.delete_external_connection(
            project_id=str(project_id), name=name
        )
        logger.info("external_connection_deleted", project_id=str(project_id), name=name)

    # ------------------------------------------------------------------
    # Test + schema introspection
    # ------------------------------------------------------------------

    async def test_connection(
        self, project_id: UUID, name: str
    ) -> TestConnectionResponse:
        row = await self._repo.get_by_name(project_id, name)
        if not row:
            raise NotFoundError(f"Connection {name!r} not found")

        row = await self._repo.update(
            row, status="testing", updated_at=datetime.now(timezone.utc)
        )

        result = await self._cloud.trigger_connection_test(
            project_id=str(project_id), name=name
        )

        now = datetime.now(timezone.utc)
        if result.get("ok"):
            schema = result.get("schema_snapshot")
            row = await self._repo.update(
                row,
                status="active",
                last_tested_at=now,
                last_test_error=None,
                schema_snapshot_json=schema,
                schema_snapshot_at=now if schema else row.schema_snapshot_at,
                updated_at=now,
            )
            await self._push_to_cloud(project_id, row)
            return TestConnectionResponse(
                ok=True,
                status="active",
                schema_snapshot_json=schema,
                schema_snapshot_at=now if schema else None,
            )
        else:
            error = result.get("error", "Connection test failed")
            row = await self._repo.update(
                row,
                status="error",
                last_tested_at=now,
                last_test_error=error,
                updated_at=now,
            )
            return TestConnectionResponse(ok=False, status="error", error=error)

    async def get_schema(self, project_id: UUID, name: str) -> SchemaSnapshotResponse:
        row = await self._repo.get_by_name(project_id, name)
        if not row:
            raise NotFoundError(f"Connection {name!r} not found")
        return SchemaSnapshotResponse(
            name=row.name,
            type=row.type,
            schema_snapshot_json=row.schema_snapshot_json,
            schema_snapshot_at=row.schema_snapshot_at,
        )

    async def refresh_schema(
        self, project_id: UUID, name: str
    ) -> ConnectionResponse:
        row = await self._repo.get_by_name(project_id, name)
        if not row:
            raise NotFoundError(f"Connection {name!r} not found")
        if row.type not in ("postgres", "mysql"):
            raise ConflictError("Schema introspection is only available for SQL connections")
        if row.status != "active":
            raise ConflictError("Connection must be active before refreshing schema")

        result = await self._cloud.trigger_connection_test(
            project_id=str(project_id), name=name
        )
        if result.get("ok") and result.get("schema_snapshot"):
            now = datetime.now(timezone.utc)
            row = await self._repo.update(
                row,
                schema_snapshot_json=result["schema_snapshot"],
                schema_snapshot_at=now,
                updated_at=now,
            )
        return _connection_response(row)

    # ------------------------------------------------------------------
    # Internal
    # ------------------------------------------------------------------

    async def _push_to_cloud(
        self, project_id: UUID, row: ProjectExternalConnection
    ) -> None:
        await self._cloud.upsert_external_connection(
            project_id=str(project_id),
            connection_id=str(row.id),
            workspace_id=str(row.workspace_id),
            name=row.name,
            conn_type=row.type,
            status=row.status,
            readonly=row.readonly,
            hyperdrive_id=row.hyperdrive_id,
            http_base_url=row.http_base_url,
            http_auth_type=row.http_auth_type,
            max_rows_per_query=row.max_rows_per_query,
        )


# ---------------------------------------------------------------------------
# CF Hyperdrive API helpers
# ---------------------------------------------------------------------------


def _cf_headers(cf_creds: Optional["CfCredentials"] = None) -> dict[str, str]:
    token = cf_creds.api_token if cf_creds else str(get_settings().cloudflare_api_token)
    return {"Authorization": f"Bearer {token}", "Content-Type": "application/json"}


def _cf_account_id(cf_creds: Optional["CfCredentials"] = None) -> str:
    return cf_creds.account_id if cf_creds else get_settings().cloudflare_account_id


def _hyperdrive_origin(
    conn_type: str,
    host: str,
    port: int,
    database: str,
    username: str,
    password: str,
    ssl_mode: str,
) -> dict:
    scheme = "postgres" if conn_type == "postgres" else "mysql"
    return {
        "host": host,
        "port": port,
        "database": database,
        "user": username,
        "password": password,
        "scheme": scheme,
        "access_type": "internet",
        "ssl": ssl_mode != "disable",
    }


async def _create_hyperdrive(
    conn_type: str,
    host: str,
    port: int,
    database: str,
    username: str,
    password: str,
    ssl_mode: str,
    *,
    cf_creds: Optional["CfCredentials"] = None,
) -> str:
    account_id = _cf_account_id(cf_creds)
    payload = {
        "name": f"p2hs-{conn_type}-{host[:20]}".replace(".", "-"),
        "origin": _hyperdrive_origin(
            conn_type, host, port, database, username, password, ssl_mode
        ),
    }
    async with httpx.AsyncClient(timeout=15) as client:
        resp = await client.post(
            f"{_CF_HYPERDRIVE_API}/accounts/{account_id}/hyperdrive/configs",
            headers=_cf_headers(cf_creds),
            json=payload,
        )
        resp.raise_for_status()
        data = resp.json()
    hyperdrive_id: str = data["result"]["id"]
    logger.info("hyperdrive_created", hyperdrive_id=hyperdrive_id)
    return hyperdrive_id


async def _update_hyperdrive(
    hyperdrive_id: str,
    conn_type: str,
    host: str,
    port: int,
    database: str,
    username: str,
    password: str,
    ssl_mode: str,
    *,
    cf_creds: Optional["CfCredentials"] = None,
) -> None:
    account_id = _cf_account_id(cf_creds)
    payload = {
        "origin": _hyperdrive_origin(
            conn_type, host, port, database, username, password, ssl_mode
        ),
    }
    async with httpx.AsyncClient(timeout=15) as client:
        resp = await client.patch(
            f"{_CF_HYPERDRIVE_API}/accounts/{account_id}/hyperdrive/configs/{hyperdrive_id}",
            headers=_cf_headers(cf_creds),
            json=payload,
        )
        resp.raise_for_status()
    logger.info("hyperdrive_updated", hyperdrive_id=hyperdrive_id)


async def _delete_hyperdrive(
    hyperdrive_id: str,
    *,
    cf_creds: Optional["CfCredentials"] = None,
) -> None:
    account_id = _cf_account_id(cf_creds)
    async with httpx.AsyncClient(timeout=15) as client:
        resp = await client.delete(
            f"{_CF_HYPERDRIVE_API}/accounts/{account_id}/hyperdrive/configs/{hyperdrive_id}",
            headers=_cf_headers(cf_creds),
        )
        if resp.status_code != 404:
            resp.raise_for_status()
    logger.info("hyperdrive_deleted", hyperdrive_id=hyperdrive_id)


# ---------------------------------------------------------------------------
# Validation + limit helpers
# ---------------------------------------------------------------------------


def _enforce_connection_limit(count: int, plan_slug: Optional[str]) -> None:
    if plan_slug and plan_slug.startswith("growth"):
        return  # unlimited
    slug = (plan_slug or "free").lower()
    limit = _PLAN_MAX_CONNECTIONS.get(slug, _DEFAULT_MAX_CONNECTIONS)
    if count >= limit:
        raise ConflictError(
            f"Connection limit reached for your plan ({limit}). "
            "Upgrade to add more external connections."
        )


def _validate_connection_fields(body: CreateConnectionRequest) -> None:
    if body.type == "mysql":
        # Core can provision a real Hyperdrive config for a MySQL database (the
        # scheme-branching in _hyperdrive_origin() below supports it), but Cloud's
        # query engine only speaks Postgres wire protocol today — there is no
        # MySQL query path anywhere in P2HSCloud. Reject at creation time rather
        # than letting a customer "successfully" connect a database that will
        # only ever 503 on first query (audit finding F-11).
        raise ValidationError(
            "MySQL connections are not yet supported: provisioning would succeed, "
            "but querying is not yet implemented on the Cloud side. Use 'postgres' "
            "for now."
        )
    if body.type in ("postgres", "mysql"):
        missing = [f for f in ("host", "port", "database", "username", "password") if not getattr(body, f)]
        if missing:
            raise ValidationError(f"SQL connection requires: {', '.join(missing)}")
    elif body.type == "http_api":
        if not body.http_base_url:
            raise ValidationError("http_api connection requires http_base_url")
        if not body.http_auth_type:
            raise ValidationError("http_api connection requires http_auth_type")
    else:
        raise ValidationError(f"Unsupported connection type: {body.type!r}")


def _connection_response(row: ProjectExternalConnection) -> ConnectionResponse:
    return ConnectionResponse(
        id=row.id,
        project_id=row.project_id,
        workspace_id=row.workspace_id,
        name=row.name,
        type=row.type,
        status=row.status,
        readonly=row.readonly,
        hyperdrive_id=row.hyperdrive_id,
        ssl_mode=row.ssl_mode,
        max_rows_per_query=row.max_rows_per_query,
        host=row.host,
        port=row.port,
        database=row.database,
        username=row.username,
        http_base_url=row.http_base_url,
        http_auth_type=row.http_auth_type,
        http_default_headers=row.http_default_headers_json,
        max_response_size_bytes=row.max_response_size_bytes,
        schema_snapshot_json=row.schema_snapshot_json,
        schema_snapshot_at=row.schema_snapshot_at,
        last_tested_at=row.last_tested_at,
        last_test_error=row.last_test_error,
        created_at=row.created_at,
        updated_at=row.updated_at,
    )