# retoor <retoor@molodetz.nl>

from .core import TTLCache, datetime, db, timedelta, timezone
from .ranking import get_top_authors


_stats_cache = TTLCache(ttl=30, max_size=50)


def get_site_stats() -> dict:
    cached = _stats_cache.get("site")
    if cached is not None:
        return cached
    today_start = (
        datetime.now(timezone.utc)
        .replace(hour=0, minute=0, second=0, microsecond=0)
        .isoformat()
    )
    stats = {
        "total_members": db["users"].count() if "users" in db.tables else 0,
        "posts_today": db["posts"].count(
            created_at={">=": today_start}, deleted_at=None
        )
        if "posts" in db.tables
        else 0,
        "total_projects": db["projects"].count(deleted_at=None)
        if "projects" in db.tables
        else 0,
        "total_gists": db["gists"].count(deleted_at=None)
        if "gists" in db.tables
        else 0,
    }
    _stats_cache.set("site", stats)
    return stats


_analytics_cache = TTLCache(ttl=300, max_size=50)


def get_platform_analytics(top_n: int = 10) -> dict:
    top_n = max(1, min(int(top_n or 10), 50))
    cache_key = f"analytics:{top_n}"
    cached = _analytics_cache.get(cache_key)
    if cached is not None:
        return cached

    now = datetime.now(timezone.utc)
    d1 = (now - timedelta(days=1)).isoformat()
    d7 = (now - timedelta(days=7)).isoformat()
    d30 = (now - timedelta(days=30)).isoformat()

    active_24h = active_7d = active_30d = 0
    sources = [
        table
        for table in ("posts", "comments", "gists", "projects")
        if table in db.tables
    ]
    if sources:
        union = " UNION ALL ".join(
            f"SELECT user_uid, created_at FROM {table} WHERE deleted_at IS NULL"
            for table in sources
        )
        rows = db.query(
            "SELECT "
            "COUNT(DISTINCT CASE WHEN created_at >= :d1 THEN user_uid END) AS a1, "
            "COUNT(DISTINCT CASE WHEN created_at >= :d7 THEN user_uid END) AS a7, "
            "COUNT(DISTINCT CASE WHEN created_at >= :d30 THEN user_uid END) AS a30 "
            f"FROM ({union})",
            d1=d1,
            d7=d7,
            d30=d30,
        )
        for row in rows:
            active_24h = row["a1"] or 0
            active_7d = row["a7"] or 0
            active_30d = row["a30"] or 0

    signed_in_now = 0
    if "sessions" in db.tables:
        for row in db.query(
            "SELECT COUNT(DISTINCT user_uid) AS n FROM sessions WHERE expires_at > :now AND deleted_at IS NULL",
            now=now.isoformat(),
        ):
            signed_in_now = row["n"] or 0

    def _users_created_since(cutoff: str) -> int:
        return (
            db["users"].count(created_at={">=": cutoff}) if "users" in db.tables else 0
        )

    site = get_site_stats()
    totals = {
        "posts": db["posts"].count(deleted_at=None) if "posts" in db.tables else 0,
        "comments": db["comments"].count(deleted_at=None)
        if "comments" in db.tables
        else 0,
        "gists": site["total_gists"],
        "projects": site["total_projects"],
        "news": db["news"].count(deleted_at=None) if "news" in db.tables else 0,
    }
    top_authors = [
        {"username": author.get("username", ""), "stars": author.get("stars", 0)}
        for author in get_top_authors(top_n)
    ]

    result = {
        "total_members": site["total_members"],
        "active_24h": active_24h,
        "active_7d": active_7d,
        "active_30d": active_30d,
        "signed_in_now": signed_in_now,
        "new_24h": _users_created_since(d1),
        "new_7d": _users_created_since(d7),
        "new_30d": _users_created_since(d30),
        "posts_today": site["posts_today"],
        "totals": totals,
        "top_authors": top_authors,
        "active_definition": (
            "Active = created a post, comment, gist, or project within the window. "
            "Automated/bot accounts are not separately flagged."
        ),
        "signed_in_definition": (
            "signed_in_now = distinct members holding an unexpired session cookie, i.e. who "
            "logged in within the session lifetime (default 7 days, up to 30 with remember-me) "
            "and have not logged out. It is NOT a real-time presence/online count - the platform "
            "does not track per-request last-seen activity - so it is normally a large fraction "
            "of recently active members. Do not describe it as 'currently online' or 'logged in "
            "right now'."
        ),
    }
    _analytics_cache.set(cache_key, result)
    return result


_gist_languages_cache = TTLCache(ttl=60, max_size=200)


def get_gist_languages() -> set[str]:
    cached = _gist_languages_cache.get("codes")
    if cached is not None:
        return cached
    codes: set[str] = set()
    if "gists" in db.tables:
        for row in db.query(
            "SELECT DISTINCT language FROM gists WHERE deleted_at IS NULL"
        ):
            language = row.get("language")
            if language:
                codes.add(language)
    _gist_languages_cache.set("codes", codes)
    return codes
