"""
EIGIS Database Connection & Session Management
PostGIS-optimized connection pool for high-concurrency ingestion
"""
import asyncpg
from typing import Optional
import json
import os

# Database configuration - supports environment variables for microservices deployment
DB_CONFIG = {
    "host": os.getenv("DB_HOST", "localhost"),
    "port": int(os.getenv("DB_PORT", 5432)),
    "database": os.getenv("DB_NAME", "eigis_db"),
    "user": os.getenv("DB_USER", "eigis_admin"),
    "password": os.getenv("DB_PASSWORD", "eigis_secure_2026"),
    "min_size": int(os.getenv("DB_POOL_MIN", 5)),
    "max_size": int(os.getenv("DB_POOL_MAX", 50)),
    "max_inactive_connection_lifetime": 300.0,
    "command_timeout": 60,
}

_pool: Optional[asyncpg.Pool] = None


async def get_pool() -> asyncpg.Pool:
    """Get or create the async connection pool."""
    global _pool
    if _pool is None:
        _pool = await asyncpg.create_pool(**DB_CONFIG)
    return _pool


async def close_pool():
    """Close the connection pool gracefully."""
    global _pool
    if _pool:
        await _pool.close()
        _pool = None


async def execute(query: str, *args):
    """Execute a query and return the status."""
    pool = await get_pool()
    async with pool.acquire() as conn:
        return await conn.execute(query, *args)


async def execute_many(query: str, args_list):
    """Execute a query with multiple argument sets (bulk insert)."""
    pool = await get_pool()
    async with pool.acquire() as conn:
        async with conn.transaction():
            await conn.executemany(query, args_list)


async def fetch(query: str, *args):
    """Fetch multiple rows."""
    pool = await get_pool()
    async with pool.acquire() as conn:
        return await conn.fetch(query, *args)


async def fetchrow(query: str, *args):
    """Fetch a single row."""
    pool = await get_pool()
    async with pool.acquire() as conn:
        return await conn.fetchrow(query, *args)


async def fetchval(query: str, *args):
    """Fetch a single value."""
    pool = await get_pool()
    async with pool.acquire() as conn:
        return await conn.fetchval(query, *args)