"""
PostgreSQL Read-Only Connection for Chat History Analytics.
Uses asyncpg for async queries — read-only (SELECT only).
"""

import logging
from typing import Any

import asyncpg

from config import (HISTORY_POSTGRES_DB, HISTORY_POSTGRES_HOST, HISTORY_POSTGRES_PASSWORD, HISTORY_POSTGRES_PORT,
                    HISTORY_POSTGRES_USER)

logger = logging.getLogger(__name__)

__all__ = ["PostgresReadonly"]


class PostgresReadonly:
    """Async read-only PostgreSQL connection via asyncpg."""

    _pool: asyncpg.Pool | None = None

    @classmethod
    async def _get_pool(cls) -> asyncpg.Pool:
        if cls._pool is None:
            cls._pool = await asyncpg.create_pool(
                host=HISTORY_POSTGRES_HOST,
                port=HISTORY_POSTGRES_PORT,
                user=HISTORY_POSTGRES_USER,
                password=HISTORY_POSTGRES_PASSWORD,
                database=HISTORY_POSTGRES_DB,
                min_size=1,
                max_size=5,
                command_timeout=30,
            )
            logger.info(
                "✅ PostgresReadonly pool created: %s:%s/%s",
                HISTORY_POSTGRES_HOST,
                HISTORY_POSTGRES_PORT,
                HISTORY_POSTGRES_DB,
            )
        return cls._pool

    @classmethod
    async def execute_query_async(cls, sql: str) -> list[dict[str, Any]]:
        """Execute a SELECT query and return list of dicts."""
        from config import USE_LOCAL_SQLITE
        if USE_LOCAL_SQLITE:
            from common.sqlite_mock import MockCursor
            mc = MockCursor()
            mc.execute(sql)
            return mc.fetchall()
            
        pool = await cls._get_pool()
        async with pool.acquire() as conn:
            rows = await conn.fetch(sql)
            return [dict(r) for r in rows]

    @classmethod
    async def execute_insert_async(cls, sql: str, *args) -> int:
        """Execute an INSERT query with args and return the new ID."""
        from config import USE_LOCAL_SQLITE
        if USE_LOCAL_SQLITE:
            from common.sqlite_mock import MockCursor
            mc = MockCursor()
            mc.execute(sql, args)
            return mc._last_insert_id or 0
            
        pool = await cls._get_pool()
        async with pool.acquire() as conn:
            result = await conn.fetchval(sql, *args)
            return result
