"""放松一下 参与看板 — adoption analytics for IT / management.

Read-only governance view: how much is the arcade being used, by whom,
which games land, and the points economy at a glance. All aggregated from
``arcade_game_sessions`` + ``arcade_wallets`` over a trailing window;
departed / soft-deleted people excluded from headcount-style figures.
"""
from __future__ import annotations

from datetime import datetime, timedelta
from typing import Any
from zoneinfo import ZoneInfo

from sqlalchemy import and_, func, select
from sqlalchemy.ext.asyncio import AsyncSession

from app.domains.arcade.catalog import GAMES
from app.domains.arcade.models import (
    SESSION_COMPLETED,
    ArcadeGameSession,
    ArcadePointsLedger,
    ArcadeWallet,
)
from app.identity.person_models import IdentityOrgMembership, IdentityPerson

_HQ_TZ = ZoneInfo("Asia/Shanghai")


def _active_person_join(stmt):
    return stmt.join(
        IdentityPerson,
        and_(
            IdentityPerson.id == ArcadeGameSession.person_id,
            IdentityPerson.deleted_at.is_(None),
            IdentityPerson.employment_status != "left",
        ),
    )


async def get_participation(db: AsyncSession, *, window_days: int = 30) -> dict[str, Any]:
    today = datetime.now(_HQ_TZ).date()
    since = today - timedelta(days=max(1, window_days))

    headcount = int((await db.scalar(
        select(func.count()).select_from(IdentityPerson).where(
            IdentityPerson.deleted_at.is_(None),
            IdentityPerson.employment_status != "left",
        )
    )) or 0)

    completed_win = _active_person_join(
        select(ArcadeGameSession).where(
            ArcadeGameSession.status == SESSION_COMPLETED,
            ArcadeGameSession.play_date >= since,
        )
    ).subquery()

    total_plays = int((await db.scalar(select(func.count()).select_from(completed_win))) or 0)
    players = int((await db.scalar(
        select(func.count(func.distinct(completed_win.c.person_id))).select_from(completed_win)
    )) or 0)
    players_all_time = int((await db.scalar(
        _active_person_join(
            select(func.count(func.distinct(ArcadeGameSession.person_id)))
            .select_from(ArcadeGameSession)
            .where(ArcadeGameSession.status == SESSION_COMPLETED)
        )
    )) or 0)

    # By game (plays + distinct players) within window.
    game_rows = (await db.execute(
        _active_person_join(
            select(
                ArcadeGameSession.game_key,
                func.count().label("plays"),
                func.count(func.distinct(ArcadeGameSession.person_id)).label("players"),
                func.avg(ArcadeGameSession.score).label("avg_score"),
            ).where(
                ArcadeGameSession.status == SESSION_COMPLETED,
                ArcadeGameSession.play_date >= since,
            )
        ).group_by(ArcadeGameSession.game_key)
    )).all()
    by_game_map = {
        g: {"plays": int(p or 0), "players": int(pl or 0), "avg_score": round(float(av or 0), 1)}
        for g, p, pl, av in game_rows
    }
    by_game = [
        {
            "game_key": spec.key,
            "category": spec.category,
            **by_game_map.get(spec.key, {"plays": 0, "players": 0, "avg_score": 0.0}),
        }
        for spec in GAMES
    ]

    # By department participation (share of dept that played in window).
    dept_sub = (
        select(
            IdentityOrgMembership.person_id.label("pid"),
            func.max(IdentityOrgMembership.org_node_name).label("dept"),
        )
        .where(
            IdentityOrgMembership.is_primary.is_(True),
            IdentityOrgMembership.membership_status == "active",
        )
        .group_by(IdentityOrgMembership.person_id)
        .subquery()
    )
    head_rows = (await db.execute(
        select(
            func.coalesce(dept_sub.c.dept, "—").label("dept"),
            func.count(func.distinct(IdentityPerson.id)).label("headcount"),
        )
        .select_from(IdentityPerson)
        .join(dept_sub, dept_sub.c.pid == IdentityPerson.id, isouter=True)
        .where(IdentityPerson.deleted_at.is_(None), IdentityPerson.employment_status != "left")
        .group_by(func.coalesce(dept_sub.c.dept, "—"))
    )).all()
    head_by_dept = {d: int(h or 0) for d, h in head_rows}

    part_rows = (await db.execute(
        select(
            func.coalesce(dept_sub.c.dept, "—").label("dept"),
            func.count(func.distinct(ArcadeGameSession.person_id)).label("players"),
            func.count().label("plays"),
        )
        .select_from(ArcadeGameSession)
        .join(IdentityPerson, and_(
            IdentityPerson.id == ArcadeGameSession.person_id,
            IdentityPerson.deleted_at.is_(None),
            IdentityPerson.employment_status != "left",
        ))
        .join(dept_sub, dept_sub.c.pid == ArcadeGameSession.person_id, isouter=True)
        .where(ArcadeGameSession.status == SESSION_COMPLETED, ArcadeGameSession.play_date >= since)
        .group_by(func.coalesce(dept_sub.c.dept, "—"))
    )).all()
    part_by_dept = {d: {"players": int(pl or 0), "plays": int(p or 0)} for d, pl, p in part_rows}
    by_department = []
    for dept, hc in head_by_dept.items():
        if hc <= 0:
            continue
        act = part_by_dept.get(dept, {"players": 0, "plays": 0})
        players_d = min(act["players"], hc)
        by_department.append({
            "department": dept,
            "headcount": hc,
            "players": players_d,
            "plays": act["plays"],
            "participation_pct": round(100.0 * players_d / hc, 1),
        })
    by_department.sort(key=lambda x: (-x["participation_pct"], -x["plays"]))

    # Points economy (all-time, active wallets only).
    econ = (await db.execute(
        select(
            func.coalesce(func.sum(ArcadeWallet.total_earned), 0),
            func.coalesce(func.sum(ArcadeWallet.total_spent), 0),
            func.coalesce(func.sum(ArcadeWallet.balance), 0),
        )
        .select_from(ArcadeWallet)
        .join(IdentityPerson, and_(
            IdentityPerson.id == ArcadeWallet.person_id,
            IdentityPerson.deleted_at.is_(None),
            IdentityPerson.employment_status != "left",
        ))
    )).first()
    entry_spend = int((await db.scalar(
        select(func.coalesce(func.sum(-ArcadePointsLedger.delta), 0))
        .where(ArcadePointsLedger.reason == "game_entry")
    )) or 0)

    return {
        "window_days": window_days,
        "since": since.isoformat(),
        "headcount": headcount,
        "players_window": players,
        "players_all_time": players_all_time,
        "total_plays_window": total_plays,
        "participation_pct_window": round(100.0 * players / headcount, 1) if headcount else 0.0,
        "avg_plays_per_player": round(total_plays / players, 1) if players else 0.0,
        "by_game": by_game,
        "by_department": by_department[:30],
        "economy": {
            "total_earned": int(econ[0] or 0) if econ else 0,
            "total_spent": int(econ[1] or 0) if econ else 0,
            "outstanding_balance": int(econ[2] or 0) if econ else 0,
            "game_entry_spent": entry_spend,
        },
        "generated_at": datetime.now(_HQ_TZ).isoformat(),
    }


__all__ = ["get_participation"]