from fastapi import APIRouter, Request, HTTPException
from auth import require_auth
from database import get_pool
from config import ADMIN_IDS

router = APIRouter(prefix="/leaderboard", tags=["leaderboard"])

LEVELS = ["A1", "A2", "B1", "B2", "C1", "C2"]


@router.get("")
async def get_leaderboard(request: Request, limit: int = 50):
    """Рейтинг игроков — быстрое чтение из кэша"""
    telegram_id, _ = await require_auth(request)

    if limit < 1:
        limit = 1
    elif limit > 100:
        limit = 100

    pool = await get_pool()
    async with pool.acquire() as conn:
        user = await conn.fetchrow(
            "SELECT 1, current_level FROM users WHERE telegram_id = $1", telegram_id
        )
        if not user:
            raise HTTPException(401, "User not registered")

        current_level = user["current_level"] or "A1"

        # Быстрое чтение из MATERIALIZED VIEW
        rows = await conn.fetch("""
            SELECT * FROM leaderboard_cache
            ORDER BY total_score DESC
            LIMIT $1
        """, limit)

        leaderboard = []
        for i, row in enumerate(rows):
            leaderboard.append({
                "rank": i + 1,
                "telegram_id": row["telegram_id"],
                "username": row["username"] or f"Player{row['telegram_id']}",
                "total_score": row["total_score"],
                "wordsearch_score": row["wordsearch_score"],
                "sentence_score": row["sentence_score"],
                "wordquiz_score": row["wordquiz_score"],
                "words_guessed": row["words_guessed"],
                "bubblewords_score": row["bubblewords_score"],
                "bubble_levels_completed": row["bubble_levels_completed"],
                "levels_completed": 0,
                "sentences_completed": 0,
            })

        # Место пользователя
        user_rank = await conn.fetchval("""
            SELECT COUNT(*) + 1 FROM leaderboard_cache
            WHERE total_score > (
                SELECT COALESCE(total_score, 0) FROM leaderboard_cache WHERE telegram_id = $1
            )
        """, telegram_id)

        # Персональная статистика пользователя
        my_row = await conn.fetchrow("""
            SELECT * FROM leaderboard_cache WHERE telegram_id = $1
        """, telegram_id)

        if my_row:
            my_wordsearch = my_row["wordsearch_score"]
            my_sentence = my_row["sentence_score"]
            my_wordquiz = my_row["wordquiz_score"]
            my_words_guessed = my_row["words_guessed"]
            my_bubblewords = my_row["bubblewords_score"]
            my_bubble_levels = my_row["bubble_levels_completed"]
        else:
            my_wordsearch = 0
            my_sentence = 0
            my_wordquiz = 0
            my_words_guessed = 0
            my_bubblewords = 0
            my_bubble_levels = 0

        # Считаем НАПРЯМУЮ из основных таблиц
        my_wordsearch_levels = await conn.fetchval(
            "SELECT COUNT(DISTINCT level_id) FROM user_progress WHERE user_id = $1 AND completed_at IS NOT NULL",
            telegram_id
        ) or 0

        my_sentences_completed = await conn.fetchval(
            "SELECT COUNT(*) FROM sentence_progress WHERE user_id = $1 AND completed_at IS NOT NULL",
            telegram_id
        ) or 0

        # Если пользователя нет в кэше — считаем очки из основных таблиц
        if not my_row:
            my_wordsearch = await conn.fetchval(
                "SELECT COALESCE(SUM(score), 0) FROM user_progress WHERE user_id = $1 AND completed_at IS NOT NULL",
                telegram_id
            ) or 0

            my_sentence = await conn.fetchval(
                "SELECT COALESCE(SUM(score), 0) FROM sentence_progress WHERE user_id = $1 AND completed_at IS NOT NULL",
                telegram_id
            ) or 0

            my_wordquiz = await conn.fetchval(
                "SELECT COALESCE(total_score, 0) FROM word_quiz_progress WHERE user_id = $1",
                telegram_id
            ) or 0

            my_words_guessed = await conn.fetchval(
                "SELECT COALESCE(words_guessed, 0) FROM word_quiz_progress WHERE user_id = $1",
                telegram_id
            ) or 0

            my_bubblewords = await conn.fetchval(
                "SELECT COALESCE(total_score, 0) FROM bubble_words_progress WHERE user_id = $1",
                telegram_id
            ) or 0

            my_bubble_levels = await conn.fetchval(
                "SELECT COALESCE(levels_completed, 0) FROM bubble_words_progress WHERE user_id = $1",
                telegram_id
            ) or 0

        my_total = my_wordsearch + my_sentence + my_wordquiz + my_bubblewords
        my_position = user_rank or 1

        # 🔧 ФИКС N+1 (19.09.2026): 48 запросов → 5.
        # Было: цикл по 6 уровням × 8 запросов = 48 round-trip к БД.
        # Стало: 5 агрегированных запросов + сборка level_stats в Python.

        # 1. Справочники (не зависят от юзера) — одним запросом
        ref_row = await conn.fetchrow("""
            SELECT
                (SELECT COUNT(*) FROM levels WHERE level = 'A1') AS a1_levels,
                (SELECT COUNT(*) FROM levels WHERE level = 'A2') AS a2_levels,
                (SELECT COUNT(*) FROM levels WHERE level = 'B1') AS b1_levels,
                (SELECT COUNT(*) FROM levels WHERE level = 'B2') AS b2_levels,
                (SELECT COUNT(*) FROM levels WHERE level = 'C1') AS c1_levels,
                (SELECT COUNT(*) FROM levels WHERE level = 'C2') AS c2_levels,
                (SELECT COUNT(*) FROM sentences WHERE level = 'A1') AS a1_sent,
                (SELECT COUNT(*) FROM sentences WHERE level = 'A2') AS a2_sent,
                (SELECT COUNT(*) FROM sentences WHERE level = 'B1') AS b1_sent,
                (SELECT COUNT(*) FROM sentences WHERE level = 'B2') AS b2_sent,
                (SELECT COUNT(*) FROM sentences WHERE level = 'C1') AS c1_sent,
                (SELECT COUNT(*) FROM sentences WHERE level = 'C2') AS c2_sent
        """)

        # 2. Очки и прогресс по всем уровням сразу — 4 запроса с GROUP BY
        ws_rows = await conn.fetch("""
            SELECT level,
                   COALESCE(SUM(score), 0) AS score,
                   COUNT(*) AS completed
            FROM user_progress
            WHERE user_id = $1 AND completed_at IS NOT NULL
            GROUP BY level
        """, telegram_id)
        ws_by_level = {r["level"]: r for r in ws_rows}

        s_rows = await conn.fetch("""
            SELECT level,
                   COALESCE(SUM(score), 0) AS score,
                   COUNT(*) AS completed
            FROM sentence_progress
            WHERE user_id = $1 AND completed_at IS NOT NULL
            GROUP BY level
        """, telegram_id)
        s_by_level = {r["level"]: r for r in s_rows}

        wq_rows = await conn.fetch("""
            SELECT level, COALESCE(total_score, 0) AS score
            FROM word_quiz_progress
            WHERE user_id = $1
        """, telegram_id)
        wq_by_level = {r["level"]: r for r in wq_rows}

        bw_rows = await conn.fetch("""
            SELECT level, COALESCE(total_score, 0) AS score
            FROM bubble_words_progress
            WHERE user_id = $1
        """, telegram_id)
        bw_by_level = {r["level"]: r for r in bw_rows}

        # 3. Собираем level_stats в Python — без единого запроса
        level_stats = {}
        for lvl in LEVELS:
            lvl_lower = lvl.lower()

            ws_score = ws_by_level.get(lvl, {}).get("score", 0) or 0
            ws_completed = ws_by_level.get(lvl, {}).get("completed", 0) or 0
            s_score = s_by_level.get(lvl, {}).get("score", 0) or 0
            s_completed = s_by_level.get(lvl, {}).get("completed", 0) or 0
            wq_score = wq_by_level.get(lvl, {}).get("score", 0) or 0
            bw_score = bw_by_level.get(lvl, {}).get("score", 0) or 0

            total_ws_levels = ref_row[f"{lvl_lower}_levels"] or 0
            total_sentences = ref_row[f"{lvl_lower}_sent"] or 0

            total_items = total_ws_levels + total_sentences
            completed_items = ws_completed + s_completed
            progress_percent = round((completed_items / total_items * 100), 1) if total_items > 0 else 0

            level_stats[lvl] = {
                "total_score": ws_score + s_score + wq_score + bw_score,
                "progress_percent": progress_percent,
                "wordsearch_score": ws_score,
                "sentence_score": s_score,
                "wordquiz_score": wq_score,
                "bubblewords_score": bw_score,
            }

    # 🔧 ИСПРАВЛЕНО: вызов достижений ВНЕ блока async with pool.acquire()
    from routers.achievements import check_and_award_achievements
    if my_position <= 3:
        await check_and_award_achievements(telegram_id, "rank_reached", my_position)

    return {
        "leaderboard": leaderboard,
        "current_level": current_level,
        "my_rank": {
            "rank": my_position,
            "telegram_id": telegram_id,
            "total_score": my_total,
            "wordsearch_score": my_wordsearch,
            "wordsearch_levels": my_wordsearch_levels,
            "sentence_score": my_sentence,
            "sentences_completed": my_sentences_completed,
            "wordquiz_score": my_wordquiz,
            "words_guessed": my_words_guessed,
            "bubblewords_score": my_bubblewords,
            "bubble_levels_completed": my_bubble_levels,
        },
        "level_stats": level_stats,
    }


@router.get("/admin/stats")
async def get_admin_stats(request: Request):
    """Админ-статистика: только для ADMIN_IDS"""
    telegram_id, _ = await require_auth(request)
    
    if telegram_id not in ADMIN_IDS:
        raise HTTPException(403, "Access denied")
    
    pool = await get_pool()
    async with pool.acquire() as conn:
        total_users = await conn.fetchval("SELECT COUNT(*) FROM users") or 0
        
        new_today = await conn.fetchval(
            "SELECT COUNT(*) FROM users WHERE created_at::date = CURRENT_DATE"
        ) or 0
        
        online_now = await conn.fetchval("""
            SELECT COUNT(DISTINCT user_id) FROM (
                SELECT user_id FROM user_progress WHERE completed_at > NOW() - INTERVAL '30 minutes'
                UNION
                SELECT user_id FROM sentence_progress WHERE completed_at > NOW() - INTERVAL '30 minutes'
                UNION
                SELECT user_id FROM daily_logins WHERE login_date = CURRENT_DATE
            ) active
        """) or 0
        
        ws_players = await conn.fetchval(
            "SELECT COUNT(DISTINCT user_id) FROM user_progress WHERE completed_at IS NOT NULL"
        ) or 0
        
        s_players = await conn.fetchval(
            "SELECT COUNT(DISTINCT user_id) FROM sentence_progress WHERE completed_at IS NOT NULL"
        ) or 0
        
        wq_players = await conn.fetchval(
            "SELECT COUNT(*) FROM word_quiz_progress WHERE words_guessed > 0"
        ) or 0
        
        bw_players = await conn.fetchval(
            "SELECT COUNT(*) FROM bubble_words_progress WHERE levels_completed > 0"
        ) or 0
        
        # 🔧 ИСПРАВЛЕНО: 'open_flashcards' → 'open_teacher_menu'
        # Раньше метрика всегда была 0, потому что такого кода достижения больше нет
        teacher_menu_opened = await conn.fetchval("""
            SELECT COUNT(*) FROM user_achievements ua
            JOIN achievements a ON ua.achievement_id = a.id
            WHERE a.code = 'open_teacher_menu'
        """) or 0
        
        # Количество пользователей по уровням
        level_counts = {}
        for lvl in LEVELS:
            count = await conn.fetchval(
                "SELECT COUNT(*) FROM users WHERE current_level = $1", lvl
            ) or 0
            level_counts[lvl] = count
        
    return {
        "total_users": total_users,
        "new_today": new_today,
        "online_now": online_now,
        "wordsearch_players": ws_players,
        "sentence_players": s_players,
        "wordquiz_players": wq_players,
        "bubblewords_players": bw_players,
        "teacher_menu_opened": teacher_menu_opened,
        "level_counts": level_counts,
    }


@router.get("/top")
async def get_top_players(request: Request):
    """Топ-3 игрока для главного экрана — из кэша"""
    telegram_id, _ = await require_auth(request)

    pool = await get_pool()
    async with pool.acquire() as conn:
        rows = await conn.fetch("""
            SELECT telegram_id, username, total_score
            FROM leaderboard_cache
            ORDER BY total_score DESC
            LIMIT 3
        """)

        top3 = []
        medals = ["🥇", "🥈", "🥉"]
        for i, row in enumerate(rows):
            top3.append({
                "rank": i + 1,
                "medal": medals[i],
                "username": row["username"] or f"Player{row['telegram_id']}",
                "total_score": row["total_score"],
            })

        return {"top3": top3}