from __future__ import annotations

import json
from pathlib import Path
from typing import Any

import asyncpg
from loguru import logger

from .config import settings

_pool: asyncpg.Pool | None = None


async def get_pool() -> asyncpg.Pool:
    global _pool
    if _pool is None:
        _pool = await asyncpg.create_pool(
            host=settings.db_host,
            port=settings.db_port,
            database=settings.db_name,
            user=settings.db_user,
            password=settings.db_password,
            min_size=1,
            max_size=10,
            command_timeout=60,
        )
        logger.info("Database pool created: {}:{}/{}", settings.db_host, settings.db_port, settings.db_name)
    return _pool


async def close_pool() -> None:
    global _pool
    if _pool is not None:
        await _pool.close()
        _pool = None


async def fetch_setting(key: str, default: Any = None) -> Any:
    pool = await get_pool()
    row = await pool.fetchrow("SELECT value_json FROM app_settings WHERE key=$1", key)
    if not row:
        return default
    value = row["value_json"]
    if isinstance(value, str):
        try:
            return json.loads(value)
        except json.JSONDecodeError:
            return value
    return value


async def fetch_int_setting(key: str, default: int) -> int:
    try:
        return int(await fetch_setting(key, default))
    except Exception:
        return default


async def fetch_float_setting(key: str, default: float) -> float:
    try:
        return float(await fetch_setting(key, default))
    except Exception:
        return default


async def fetch_bool_setting(key: str, default: bool) -> bool:
    try:
        value = await fetch_setting(key, default)
        if isinstance(value, bool):
            return value
        if isinstance(value, str):
            return value.strip().lower() in {"1", "true", "yes", "on"}
        return bool(value)
    except Exception:
        return default


async def apply_migrations(migrations_dir: Path) -> None:
    pool = await get_pool()
    async with pool.acquire() as conn:
        await conn.execute(
            """
            CREATE TABLE IF NOT EXISTS schema_migrations (
              version TEXT PRIMARY KEY,
              applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
            )
            """
        )
        for path in sorted(migrations_dir.glob("*.sql")):
            version = path.name
            exists = await conn.fetchval("SELECT 1 FROM schema_migrations WHERE version=$1", version)
            if exists:
                continue
            sql = path.read_text(encoding="utf-8")
            async with conn.transaction():
                await conn.execute(sql)
                await conn.execute("INSERT INTO schema_migrations(version) VALUES($1)", version)
            logger.info("Applied migration {}", version)
