import sqlite3
import aiosqlite
from config import DB_PATH


def init_db_sync():
    """Синхронная инициализация базы данных."""
    with sqlite3.connect(DB_PATH, timeout=10) as conn:
        conn.execute("PRAGMA journal_mode=WAL;")
        conn.execute("""
            CREATE TABLE IF NOT EXISTS scores (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL,
                username TEXT,
                first_name TEXT,
                score INTEGER NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
        conn.execute("""
            CREATE TABLE IF NOT EXISTS best_scores (
                user_id INTEGER PRIMARY KEY,
                username TEXT,
                first_name TEXT,
                best_score INTEGER NOT NULL,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
        conn.commit()


async def init_db():
    """Асинхронная инициализация базы данных."""
    async with aiosqlite.connect(DB_PATH, timeout=10) as db:
        await db.execute("PRAGMA journal_mode=WAL;")
        await db.execute("""
            CREATE TABLE IF NOT EXISTS scores (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL,
                username TEXT,
                first_name TEXT,
                score INTEGER NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
        await db.execute("""
            CREATE TABLE IF NOT EXISTS best_scores (
                user_id INTEGER PRIMARY KEY,
                username TEXT,
                first_name TEXT,
                best_score INTEGER NOT NULL,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
        await db.commit()


def save_score_sync(user_id: int, username: str, first_name: str, score: int):
    """Синхронно сохранить результат игры."""
    init_db_sync()
    with sqlite3.connect(DB_PATH, timeout=10) as conn:
        conn.execute(
            "INSERT INTO scores (user_id, username, first_name, score) VALUES (?, ?, ?, ?)",
            (user_id, username, first_name, score)
        )
        conn.execute("""
            INSERT INTO best_scores (user_id, username, first_name, best_score)
            VALUES (?, ?, ?, ?)
            ON CONFLICT(user_id) DO UPDATE SET
                username = CASE WHEN excluded.username != '' THEN excluded.username ELSE best_scores.username END,
                first_name = CASE WHEN excluded.first_name != '' THEN excluded.first_name ELSE best_scores.first_name END,
                best_score = MAX(best_score, excluded.best_score),
                updated_at = CURRENT_TIMESTAMP
        """, (user_id, username, first_name, score))
        conn.commit()


async def save_score(user_id: int, username: str, first_name: str, score: int):
    """Сохранить результат игры (асинхронно)."""
    async with aiosqlite.connect(DB_PATH, timeout=10) as db:
        await db.execute(
            "INSERT INTO scores (user_id, username, first_name, score) VALUES (?, ?, ?, ?)",
            (user_id, username, first_name, score)
        )
        await db.execute("""
            INSERT INTO best_scores (user_id, username, first_name, best_score)
            VALUES (?, ?, ?, ?)
            ON CONFLICT(user_id) DO UPDATE SET
                username = CASE WHEN excluded.username != '' THEN excluded.username ELSE best_scores.username END,
                first_name = CASE WHEN excluded.first_name != '' THEN excluded.first_name ELSE best_scores.first_name END,
                best_score = MAX(best_score, excluded.best_score),
                updated_at = CURRENT_TIMESTAMP
        """, (user_id, username, first_name, score))
        await db.commit()


async def get_leaderboard(limit: int = 10) -> list[dict]:
    """Получить топ игроков."""
    async with aiosqlite.connect(DB_PATH, timeout=10) as db:
        db.row_factory = aiosqlite.Row
        cursor = await db.execute(
            "SELECT user_id, username, first_name, best_score FROM best_scores ORDER BY best_score DESC LIMIT ?",
            (limit,)
        )
        rows = await cursor.fetchall()
        return [dict(row) for row in rows]


async def get_user_best(user_id: int) -> int | None:
    """Получить лучший результат пользователя."""
    async with aiosqlite.connect(DB_PATH, timeout=10) as db:
        cursor = await db.execute(
            "SELECT best_score FROM best_scores WHERE user_id = ?",
            (user_id,)
        )
        row = await cursor.fetchone()
        return row[0] if row else None