"""PostgreSQL async connection support for server-mode deployments.

Per ADR-048 (Server Application Architecture), the CleverAgents server uses
PostgreSQL for multi-user persistence.  This module provides:

- :class:`PostgreSQLConnectionConfig` — validated configuration for a
  PostgreSQL connection
- :func:`build_postgresql_url` — construct a SQLAlchemy-compatible async URL
- :func:`is_postgresql_url` — check whether a database URL targets PostgreSQL

The implementation is a **stub** that validates configuration and constructs
connection URLs.  Actual async engine creation requires ``asyncpg`` and
``sqlalchemy[asyncio]`` to be installed in the server deployment.

In local mode (SQLite), these utilities are not used.  They are imported
only by server-mode infrastructure code.
"""

from __future__ import annotations

import logging
import re

from pydantic import BaseModel, ConfigDict, field_validator

_logger: logging.Logger = logging.getLogger(__name__)

# Pattern for a minimal PostgreSQL URL (scheme://[user[:pass]@]host[:port]/db)
_PG_URL_PATTERN: re.Pattern[str] = re.compile(
    r"^postgresql(?:\+\w+)?://",
    re.IGNORECASE,
)

# Async driver suffix used by SQLAlchemy for asyncpg
_ASYNC_DRIVER = "asyncpg"


class PostgreSQLConnectionConfig(BaseModel):
    """Validated configuration for a PostgreSQL server-mode connection.

    Attributes:
        host: Database server hostname or IP address.
        port: Database server port (default: 5432).
        database: Database name.
        username: Database user.
        password: Database password (treated as a secret).
        ssl_mode: SSL mode for the connection (default: ``"prefer"``).
        pool_size: SQLAlchemy connection pool size (default: 5).
        max_overflow: Maximum pool overflow connections (default: 10).
    """

    model_config = ConfigDict(frozen=True, strict=False)

    host: str
    port: int = 5432
    database: str
    username: str
    password: str
    ssl_mode: str = "prefer"
    pool_size: int = 5
    max_overflow: int = 10

    @field_validator("host")
    @classmethod
    def _validate_host(cls, value: str) -> str:
        if not value or not value.strip():
            raise ValueError("host must not be empty")
        return value.strip()

    @field_validator("database")
    @classmethod
    def _validate_database(cls, value: str) -> str:
        if not value or not value.strip():
            raise ValueError("database must not be empty")
        return value.strip()

    @field_validator("username")
    @classmethod
    def _validate_username(cls, value: str) -> str:
        if not value or not value.strip():
            raise ValueError("username must not be empty")
        return value.strip()

    @field_validator("port")
    @classmethod
    def _validate_port(cls, value: int) -> int:
        if not (1 <= value <= 65535):
            raise ValueError("port must be between 1 and 65535")
        return value

    @field_validator("pool_size")
    @classmethod
    def _validate_pool_size(cls, value: int) -> int:
        if value < 1:
            raise ValueError("pool_size must be at least 1")
        return value

    @field_validator("max_overflow")
    @classmethod
    def _validate_max_overflow(cls, value: int) -> int:
        if value < 0:
            raise ValueError("max_overflow must be non-negative")
        return value

    def to_url(self, *, async_driver: bool = True) -> str:
        """Build a SQLAlchemy-compatible connection URL.

        Args:
            async_driver: When ``True`` (default), use the ``asyncpg``
                driver (``postgresql+asyncpg://``).  When ``False``, use
                the synchronous ``psycopg2`` driver.

        Returns:
            A SQLAlchemy connection URL string.
        """
        driver = f"+{_ASYNC_DRIVER}" if async_driver else ""
        return (
            f"postgresql{driver}://{self.username}:{self.password}"
            f"@{self.host}:{self.port}/{self.database}"
        )


def build_postgresql_url(
    host: str,
    database: str,
    username: str,
    password: str,
    *,
    port: int = 5432,
    async_driver: bool = True,
) -> str:
    """Build a SQLAlchemy-compatible PostgreSQL connection URL.

    Args:
        host: Database server hostname or IP address.
        database: Database name.
        username: Database user.
        password: Database password.
        port: Database server port (default: 5432).
        async_driver: When ``True`` (default), use the ``asyncpg`` driver.

    Returns:
        A SQLAlchemy connection URL string.

    Raises:
        ValueError: When any required parameter is empty or invalid.
    """
    config = PostgreSQLConnectionConfig(
        host=host,
        port=port,
        database=database,
        username=username,
        password=password,
    )
    return config.to_url(async_driver=async_driver)


def is_postgresql_url(url: str) -> bool:
    """Return ``True`` when *url* targets a PostgreSQL database.

    Recognises both ``postgresql://`` and ``postgresql+<driver>://`` schemes.

    Args:
        url: Database URL to check.

    Returns:
        ``True`` when the URL scheme indicates PostgreSQL.
    """
    if not url:
        return False
    return bool(_PG_URL_PATTERN.match(url))


__all__ = [
    "PostgreSQLConnectionConfig",
    "build_postgresql_url",
    "is_postgresql_url",
]
