"""Authentication database — delegates to the shared asyncpg pool.

Auth and conversation share the same PostgreSQL database (DB_* env vars).
Rather than maintaining a second pool, this module reuses the pool owned
by ``backend.chanakya.conversation.database``.
"""

import asyncpg
from backend.chanakya.conversation.database import (
    ensure_pool as _ensure_pool,
    close_pool as _close_pool,
)


async def init_auth_db() -> None:
    """Warm up the shared DB pool at startup."""
    await _ensure_pool()


async def close_auth_db() -> None:
    """Close the shared DB pool at shutdown."""
    await _close_pool()


async def ensure_pool() -> asyncpg.Pool:
    return await _ensure_pool()


# ---------------------------------------------------------------------------
# User queries
# ---------------------------------------------------------------------------

async def get_user_by_username(username: str) -> dict | None:
    pool = await _ensure_pool()
    async with pool.acquire() as conn:
        row = await conn.fetchrow(
            """
            SELECT id, username, hashed_password, deleted_at
            FROM eb_users
            WHERE username = $1 AND deleted_at IS NULL
            """,
            username,
        )
    if row is None:
        return None
    return {
        "id": row["id"],
        "username": row["username"],
        "hashed_password": row["hashed_password"],
        "deleted_at": row["deleted_at"],
        "is_active": row["deleted_at"] is None,
    }


async def get_user_by_id(user_id: str) -> dict | None:
    return await get_user_by_username(user_id)


async def upsert_user(user_id: int, username: str, hashed_password: str) -> str:
    pool = await _ensure_pool()
    async with pool.acquire() as conn:
        result = await conn.fetchval(
            """
            INSERT INTO eb_users (id, username, hashed_password)
            VALUES ($1, $2, $3)
            ON CONFLICT (id) DO UPDATE
                SET username        = EXCLUDED.username,
                    hashed_password = EXCLUDED.hashed_password
            RETURNING (xmax = 0) AS was_inserted
            """,
            user_id,
            username,
            hashed_password,
        )
    return "inserted" if result else "updated"