bridzik/api/stats.py

"""Self-hosted usage analytics: zapis pageview beacon + citanie agregatov pre /admin/stats.

Oddelene od api/history.py (ktory drzi zivu hru + restore-on-startup logiku),
rovnako ako je api/auth.py samostatny modul.
"""

import os

import geoip2.database
import geoip2.errors
from sqlalchemy import extract, func, select
from user_agents import parse as parse_ua

from db.db import async_session
from db.models import Game, Guess, PageView, Player

_geoip_reader: "geoip2.database.Reader | None" = None
_geoip_load_attempted = False


def _country_for_ip(ip: str) -> str:
    """ISO kod krajiny z lokalneho .mmdb (GEOIP_DB_PATH), alebo "" ak nie je
    dostupny subor alebo sa IP neda rozlusit (privatna/lokalna adresa a pod.)."""
    global _geoip_reader, _geoip_load_attempted
    if not ip:
        return ""
    if _geoip_reader is None:
        if _geoip_load_attempted:
            return ""
        _geoip_load_attempted = True
        path = os.environ.get("GEOIP_DB_PATH", "")
        if not path or not os.path.exists(path):
            return ""
        _geoip_reader = geoip2.database.Reader(path)
    try:
        return _geoip_reader.country(ip).country.iso_code or ""
    except (geoip2.errors.AddressNotFoundError, ValueError):
        return ""


async def record_pageview(path: str, referrer: str, user_agent: str, ip: str = "") -> None:
    ua = parse_ua(user_agent)
    device_type = (
        "bot" if ua.is_bot else "mobile" if ua.is_mobile else "tablet" if ua.is_tablet else "pc"
    )
    async with async_session() as session:
        session.add(
            PageView(
                path=path,
                referrer=referrer,
                user_agent=user_agent,
                browser=ua.browser.family[:40],
                os=ua.os.family[:40],
                device_type=device_type,
                ip=ip[:45],
                country=_country_for_ip(ip),
            )
        )
        await session.commit()


_DAYS_WINDOW = 30  # kazdy "za den" graf/rozklad zobrazuje rovnake okno


async def _pageviews_by_day_and(session, column) -> dict[str, dict[str, int]]:
    """Denne navstevy rozdelene podla danej dimenzie (device_type/browser/os),
    napr. {"2026-07-01": {"pc": 3, "mobile": 1}, ...} -- pre prepinatelny graf.
    Orezane na _DAYS_WINDOW dni, rovnako ako pageviews_per_day (a ostatne denne
    grafy), aby prepnutie medzi dimenziami neroztiahlo graf na celu historiu."""
    vday = func.date(PageView.created_at)
    recent_days = (
        select(vday.label("day"))
        .distinct()
        .order_by(vday.desc())
        .limit(_DAYS_WINDOW)
        .subquery()
    )
    rows = (
        await session.execute(
            select(vday.label("day"), column.label("cat"), func.count().label("n"))
            .where(vday.in_(select(recent_days.c.day)))
            .group_by(vday, column)
            .order_by(vday.desc())
        )
    ).all()
    nested: dict[str, dict[str, int]] = {}
    for r in rows:
        nested.setdefault(str(r.day), {})[r.cat] = r.n
    return nested


async def get_daily_stats() -> dict:
    async with async_session() as session:
        # func.date() (not cast(..., Date)) -- the `date()` SQL function is portable
        # across SQLite and Postgres and returns a plain string/date value without
        # the double-conversion issue cast(..., Date) triggers on SQLite (aiosqlite
        # already coerces TIMESTAMP columns to datetime before the Date result
        # processor tries to re-parse them as an ISO string).
        day = func.date(Game.created_at)
        game_rows = (
            await session.execute(
                select(day.label("day"), func.count().label("n"))
                .group_by(day)
                .order_by(day.desc())
                .limit(_DAYS_WINDOW)
            )
        ).all()

        pday = func.date(Player.created_at)
        player_rows = (
            await session.execute(
                select(pday.label("day"), func.count().label("n"))
                .group_by(pday)
                .order_by(pday.desc())
                .limit(_DAYS_WINDOW)
            )
        ).all()

        total, finished = (
            await session.execute(select(func.count(), func.count(Game.ended_at)))
        ).one()

        avg_duration = (
            await session.execute(
                select(func.avg(func.extract("epoch", Game.ended_at - Game.created_at))).where(
                    Game.ended_at.is_not(None)
                )
            )
        ).scalar()

        total_players = (await session.execute(select(func.count(Player.id)))).scalar()

        peak_hours = (
            await session.execute(
                select(extract("hour", Game.created_at).label("h"), func.count().label("n"))
                .group_by("h")
                .order_by("h")
            )
        ).all()

        rday = func.date(Game.created_at)
        rounds_rows = (
            await session.execute(
                select(rday.label("day"), func.count().label("n"))
                .select_from(Guess)
                .join(Game, Guess.game_id == Game.id)
                .group_by(rday)
                .order_by(rday.desc())
                .limit(_DAYS_WINDOW)
            )
        ).all()

        vday = func.date(PageView.created_at)
        pageview_rows = (
            await session.execute(
                select(vday.label("day"), func.count().label("n"))
                .group_by(vday)
                .order_by(vday.desc())
                .limit(_DAYS_WINDOW)
            )
        ).all()
        top_referrers = (
            await session.execute(
                select(PageView.referrer, func.count().label("n"))
                .where(PageView.referrer != "")
                .group_by(PageView.referrer)
                .order_by(func.count().desc())
                .limit(20)
            )
        ).all()
        top_paths = (
            await session.execute(
                select(PageView.path, func.count().label("n"))
                .group_by(PageView.path)
                .order_by(func.count().desc())
                .limit(20)
            )
        ).all()

        browsers = (
            await session.execute(
                select(PageView.browser, func.count().label("n"))
                .group_by(PageView.browser)
                .order_by(func.count().desc())
            )
        ).all()
        os_rows = (
            await session.execute(
                select(PageView.os, func.count().label("n"))
                .group_by(PageView.os)
                .order_by(func.count().desc())
            )
        ).all()
        device_rows = (
            await session.execute(
                select(PageView.device_type, func.count().label("n"))
                .group_by(PageView.device_type)
                .order_by(func.count().desc())
            )
        ).all()
        country_rows = (
            await session.execute(
                select(PageView.country, func.count().label("n"))
                .where(PageView.country != "")
                .group_by(PageView.country)
                .order_by(func.count().desc())
            )
        ).all()

        pageviews_per_day_by_device = await _pageviews_by_day_and(session, PageView.device_type)
        pageviews_per_day_by_browser = await _pageviews_by_day_and(session, PageView.browser)
        pageviews_per_day_by_os = await _pageviews_by_day_and(session, PageView.os)

    return {
        "games_per_day": {str(r.day): r.n for r in game_rows},
        "players_per_day": {str(r.day): r.n for r in player_rows},
        "completion_rate": finished / total if total else None,
        "avg_game_duration_minutes": (avg_duration / 60) if avg_duration else None,
        "total_players": total_players,
        "peak_hours": {int(r.h): r.n for r in peak_hours},
        "rounds_per_day": {str(r.day): r.n for r in rounds_rows},
        "pageviews_per_day": {str(r.day): r.n for r in pageview_rows},
        "pageviews_per_day_by_device": pageviews_per_day_by_device,
        "pageviews_per_day_by_browser": pageviews_per_day_by_browser,
        "pageviews_per_day_by_os": pageviews_per_day_by_os,
        "top_referrers": {r.referrer: r.n for r in top_referrers},
        "top_paths": {r.path: r.n for r in top_paths},
        "browsers": {r.browser: r.n for r in browsers},
        "operating_systems": {r.os: r.n for r in os_rows},
        "device_types": {r.device_type: r.n for r in device_rows},
        "countries": {r.country: r.n for r in country_rows},
    }