"""
Shared utility functions for PopCorn API routers
"""
from database import fetch_one, fetch_all, execute
from config import ACCOUNTS, ORG, DEV_MODE

ACHIEVEMENT_THRESHOLDS = {
    "watching": [1, 5, 10, 25, 50, 100, 250, 500, 1000],
    "social": [1, 5, 10, 25, 50, 100],
    "store": [1, 5, 10, 25, 50],
    "comments": [1, 10, 25, 50, 100, 500],
    "parties": [1, 5, 10, 25, 50],
    "streak": [1, 3, 7, 14, 30, 60, 90, 180, 365],
}


async def progress_achievement(user_id: int | str, category: str, current_count: int) -> None:
    """Progress an achievement category. Unlock it if threshold reached."""
    thresholds = ACHIEVEMENT_THRESHOLDS.get(category, [])
    target_count = None
    for t in thresholds:
        if current_count >= t:
            target_count = t

    if target_count is None:
        return

    achievement = await fetch_one(
        "SELECT id FROM achievements WHERE category=? AND total_required=?",
        [category, str(target_count)],
    )
    if not achievement:
        return

    existing = await fetch_one(
        "SELECT * FROM user_achievements WHERE user_id=? AND achievement_id=?",
        [str(user_id), str(achievement["id"])],
    )
    if existing and existing.get("is_unlocked"):
        return

    await execute(
        """INSERT INTO user_achievements (user_id, achievement_id, progress, is_unlocked, unlocked_at)
           VALUES (?, ?, ?, 1, datetime('now'))
           ON CONFLICT(user_id, achievement_id) DO UPDATE SET
           progress=?, is_unlocked=1, unlocked_at=CASE WHEN is_unlocked=0 THEN datetime('now') ELSE unlocked_at END""",
        [str(user_id), str(achievement["id"]), str(target_count), str(target_count)],
    )


async def create_notification(user_id: int | str, type: str, title: str, body: str, data: str = "{}") -> dict:
    """Create a notification for a user."""
    await execute(
        """INSERT INTO notifications (user_id, type, title, body, data, is_read, created_at)
           VALUES (?, ?, ?, ?, ?, 0, datetime('now'))""",
        [str(user_id), type, title, body, data],
    )
    return {"created": True}


async def create_or_update_user(user_data: dict) -> dict:
    """Create a user if not exists, or update the existing one."""
    tg_id = str(user_data["id"])
    existing = await fetch_one("SELECT * FROM users WHERE user_id=?", [tg_id])
    if existing:
        await execute(
            "UPDATE users SET first_name=?, last_name=?, username=?, language_code=?, photo_url=?, is_premium=?, last_active_at=datetime('now') WHERE user_id=?",
            [
                user_data.get("first_name", ""),
                user_data.get("last_name", ""),
                user_data.get("username", ""),
                user_data.get("language_code", "en"),
                user_data.get("photo_url", ""),
                "1" if user_data.get("is_premium") else "0",
                tg_id,
            ],
        )
        return existing

    await execute(
        """INSERT INTO users (user_id, first_name, last_name, username, language_code, photo_url, is_premium, stars_balance, kernels_balance, points, created_at, last_active_at)
           VALUES (?, ?, ?, ?, ?, ?, ?, 100, 5, 0, datetime('now'), datetime('now'))""",
        [
            tg_id,
            user_data.get("first_name", ""),
            user_data.get("last_name", ""),
            user_data.get("username", ""),
            user_data.get("language_code", "en"),
            user_data.get("photo_url", ""),
            "1" if user_data.get("is_premium") else "0",
        ],
    )
    return await fetch_one("SELECT * FROM users WHERE user_id=?", [tg_id])


def is_dev_mode() -> bool:
    return DEV_MODE