routers/utils.py

from database import fetch_one, fetch_all, execute


async def check_achievement(user_id: int, category: str, current: int, required: int, name: str):
    achievement = await fetch_one(
        "SELECT id FROM achievements WHERE name=? AND category=? AND total_required=?",
        [name, category, str(required)],
    )
    if not achievement:
        return

    aid = achievement["id"]
    ua = await fetch_one(
        "SELECT * FROM user_achievements WHERE user_id=? AND achievement_id=?",
        [str(user_id), str(aid)],
    )

    if ua:
        if ua.get("is_unlocked"):
            return
        if ua.get("progress", 0) >= ua.get("total", 1):
            await execute(
                "UPDATE user_achievements SET is_unlocked=1, unlocked_at=datetime('now') WHERE id=?",
                [str(ua["id"])],
            )
            await create_notification(user_id, f"إنجاز مفتوح: {name}", f"لقد حصلت على إنجاز {name}!", "achievement_unlocked")
    else:
        await execute(
            "INSERT INTO user_achievements (user_id, achievement_id, progress, total, is_unlocked) VALUES (?, ?, ?, ?, ?)",
            [str(user_id), str(aid), str(current), str(required), "1" if current >= required else "0"],
        )
        if current >= required:
            await create_notification(user_id, f"إنجاز مفتوح: {name}", f"لقد حصلت على إنجاز {name}!", "achievement_unlocked")


async def progress_achievement(user_id: int, category: str, increment: int = 1):
    achievements = await fetch_all(
        "SELECT id, name, total_required FROM achievements WHERE category=?",
        [category],
    )
    for a in achievements:
        aid = a["id"]
        total = a["total_required"]
        name = a["name"]

        ua = await fetch_one(
            "SELECT * FROM user_achievements WHERE user_id=? AND achievement_id=?",
            [str(user_id), str(aid)],
        )

        if ua:
            if ua.get("is_unlocked"):
                continue
            new_progress = (ua.get("progress", 0) or 0) + increment
            is_unlocked = "1" if new_progress >= total else "0"
            await execute(
                "UPDATE user_achievements SET progress=?, is_unlocked=?, unlocked_at=CASE WHEN ?=1 THEN datetime('now') ELSE NULL END WHERE id=?",
                [str(new_progress), is_unlocked, is_unlocked, str(ua["id"])],
            )
            if new_progress >= total:
                await create_notification(user_id, f"إنجاز مفتوح: {name}", f"لقد حصلت على إنجاز {name}!", "achievement_unlocked")
        else:
            await execute(
                "INSERT INTO user_achievements (user_id, achievement_id, progress, total, is_unlocked) VALUES (?, ?, ?, ?, ?)",
                [str(user_id), str(aid), str(increment), str(total), "1" if increment >= total else "0"],
            )
            if increment >= total:
                await create_notification(user_id, f"إنجاز مفتوح: {name}", f"لقد حصلت على إنجاز {name}!", "achievement_unlocked")


async def create_notification(user_id: int, title: str, body: str, ntype: str = "info"):
    try:
        await execute(
            "INSERT INTO notifications (user_id, title, body, type) VALUES (?, ?, ?, ?)",
            [str(user_id), title, body, ntype],
        )
    except Exception:
        pass