from typing import Any, Dict, Optional

import os

import aiosqlite


_db: Optional[aiosqlite.Connection] = None


async def get_db() -> aiosqlite.Connection:
    global _db
    if _db is None:
        db_path = os.environ.get("USERS_DB_PATH", "users.db")
        _db = await aiosqlite.connect(db_path)
        _db.row_factory = aiosqlite.Row
    return _db


async def init_auth_db() -> None:
    db = await get_db()
    await db.execute(
        """
        CREATE TABLE IF NOT EXISTS auth_users (
            id INTEGER PRIMARY KEY,
            username TEXT,
            full_name TEXT,
            allow INTEGER NOT NULL DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );
        """
    )
    await db.execute(
        """
        CREATE TABLE IF NOT EXISTS auth_admins (
            id INTEGER PRIMARY KEY,
            username TEXT,
            full_name TEXT,
            is_super INTEGER NOT NULL DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );
        """
    )
    await db.commit()


def _row_to_dict(row: Optional[aiosqlite.Row]) -> Optional[Dict[str, Any]]:
    if row is None:
        return None
    return {k: row[k] for k in row.keys()}


def normalize_username(username: Optional[str]) -> Optional[str]:
    if not username:
        return None
    uname = username.strip().lstrip("@").lower()
    return uname or None


async def get_user(user_id: int) -> Optional[Dict[str, Any]]:
    db = await get_db()
    async with db.execute("SELECT * FROM auth_users WHERE id = ?", (user_id,)) as cur:
        row = await cur.fetchone()
    return _row_to_dict(row)


async def get_user_by_username(username: str) -> Optional[Dict[str, Any]]:
    norm = normalize_username(username)
    if not norm:
        return None
    db = await get_db()
    async with db.execute(
        "SELECT * FROM auth_users WHERE LOWER(username) = ?", (norm,)
    ) as cur:
        row = await cur.fetchone()
    return _row_to_dict(row)


async def create_user(
    user_id: int,
    username: Optional[str],
    full_name: Optional[str],
    allow: bool,
) -> None:
    norm = normalize_username(username)
    db = await get_db()
    await db.execute(
        """
        INSERT INTO auth_users (id, username, full_name, allow)
        VALUES (?, ?, ?, ?)
        ON CONFLICT(id) DO UPDATE SET
            username = excluded.username,
            full_name = excluded.full_name,
            allow = excluded.allow
        """,
        (user_id, norm, full_name, 1 if allow else 0),
    )
    await db.commit()


async def set_user_allow(user_id: int, allow: bool) -> bool:
    db = await get_db()
    async with db.execute(
        "UPDATE auth_users SET allow = ? WHERE id = ?",
        (1 if allow else 0, user_id),
    ) as cur:
        await db.commit()
        return cur.rowcount > 0


async def is_admin(user_id: int) -> bool:
    db = await get_db()
    async with db.execute(
        "SELECT 1 FROM auth_admins WHERE id = ?", (user_id,)
    ) as cur:
        row = await cur.fetchone()
    return row is not None


async def upsert_admin(
    user_id: int,
    username: Optional[str],
    full_name: Optional[str],
    is_super: bool = False,
) -> None:
    norm = normalize_username(username)
    db = await get_db()
    await db.execute(
        """
        INSERT INTO auth_admins (id, username, full_name, is_super)
        VALUES (?, ?, ?, ?)
        ON CONFLICT(id) DO UPDATE SET
            username = excluded.username,
            full_name = excluded.full_name,
            is_super = excluded.is_super
        """,
        (user_id, norm, full_name, 1 if is_super else 0),
    )
    await db.commit()


async def find_user(identifier: str) -> Optional[Dict[str, Any]]:
    ident = identifier.strip()
    if not ident:
        return None
    if ident.startswith("@"):
        return await get_user_by_username(ident)
    try:
        uid = int(ident)
        return await get_user(uid)
    except ValueError:
        # Пытаемся трактовать как username без @
        return await get_user_by_username(ident)