"""总览报表 —— 回答「这套自动化到底在不在起作用」。

治理待办回答的是「今天要做什么」;这一页回答的是另一个问题:**我们把这件事
做成了吗**。两者的数据来源一样,但看的方向相反 —— 待办看缺口,报表看趋势
和构成。

## 最重要的那张图是「来源分布」,不是「成员总数」

成员总数是一个几乎不动的数(1016),它不说明任何事。真正说明问题的是**这些
成员资格是怎么来的**:

* ``approval`` / ``auto_join`` —— 走了这套自动化;
* ``import`` / ``reconcile`` —— 是我们上线时或每日对账**发现**的存量,
  它们证明的恰恰是「在此之前没有任何流程管过」;
* ``manual`` —— 管理员手工挂的。

上线第一天这个分布几乎全是 import(1016 条里 1016 条)。这个比例往
approval/auto_join 移动多少,就是这套东西真正交付了多少 —— 而不是「我们写了
多少个端点」。

## 刻意不做的事

**不做"健康分"这类合成指标。** 把覆盖率、坐席、待办揉成一个 0-100 的分数,
看的人无法从分数反推该做什么,而它每次变化都要重新解释一遍。这一页给的都是
能直接对应到一个动作的原始数字。

**趋势只看事件,不看快照。** ``github_org_membership_events`` 是只追加的,
所以「过去 90 天加入/移除了多少」是一次如实的查询;而拿当前状态倒推历史
会把「加入后又移除」算成从没发生过。
"""
from __future__ import annotations

from datetime import UTC, date, datetime, timedelta
from typing import Any

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

from app.identity.person_models import IdentityPerson

from . import constants as const
from .models import GithubOrg, GithubOrgMembership, GithubOrgMembershipEvent

#: 趋势窗口。90 天足够看出一个季度的形状,又不至于让事件扫描变成全表。
TREND_DAYS = 90

#: 「自动化来的」与「我们发现的」——来源分布那张图的两个阵营。
AUTOMATED_SOURCES = (const.SOURCE_APPROVAL, const.SOURCE_AUTO_JOIN)
DISCOVERED_SOURCES = (const.SOURCE_IMPORT, const.SOURCE_RECONCILE)


def _iso(v: Any) -> str | None:
    return v.isoformat() if v is not None else None


async def _by_org(db: AsyncSession) -> list[dict[str, Any]]:
    """每个组织一行:成员构成 + 认人覆盖率 + 坐席水位。"""
    live = const.MEMBERSHIP_LIVE_STATES
    rows = list(
        (
            await db.execute(
                select(
                    GithubOrg.id,
                    GithubOrg.slug,
                    GithubOrg.is_community,
                    GithubOrg.offboarding_policy,
                    GithubOrg.seat_plan,
                    GithubOrg.seat_filled,
                    GithubOrg.seat_total,
                    GithubOrg.seat_checked_at,
                    func.sum(
                        case(
                            (
                                GithubOrgMembership.state
                                == const.MEMBERSHIP_STATE_ACTIVE,
                                1,
                            ),
                            else_=0,
                        )
                    ).label("active"),
                    func.sum(
                        case(
                            (
                                GithubOrgMembership.state
                                == const.MEMBERSHIP_STATE_PENDING_INVITE,
                                1,
                            ),
                            else_=0,
                        )
                    ).label("pending"),
                    func.sum(
                        case(
                            (
                                GithubOrgMembership.state
                                == const.MEMBERSHIP_STATE_AWAITING_MANUAL,
                                1,
                            ),
                            else_=0,
                        )
                    ).label("manual_wait"),
                    func.sum(
                        case(
                            (GithubOrgMembership.person_id.is_(None), 1),
                            else_=0,
                        )
                    ).label("unlinked_all"),
                    func.sum(
                        case(
                            (
                                GithubOrgMembership.external_confirmed_at.is_not(
                                    None
                                ),
                                1,
                            ),
                            else_=0,
                        )
                    ).label("external"),
                    func.sum(
                        case(
                            (
                                GithubOrgMembership.external_kind
                                == const.EXTERNAL_KIND_BOT,
                                1,
                            ),
                            else_=0,
                        )
                    ).label("bots"),
                )
                .join(
                    GithubOrgMembership,
                    (GithubOrgMembership.org_id == GithubOrg.id)
                    & GithubOrgMembership.state.in_(live),
                    isouter=True,
                )
                .where(GithubOrg.deleted_at.is_(None))
                .group_by(
                    GithubOrg.id, GithubOrg.slug, GithubOrg.is_community,
                    GithubOrg.offboarding_policy, GithubOrg.seat_plan,
                    GithubOrg.seat_filled, GithubOrg.seat_total,
                    GithubOrg.seat_checked_at,
                )
                .order_by(GithubOrg.sort_order, GithubOrg.slug)
            )
        ).all()
    )
    out = []
    for r in rows:
        unlinked_all = int(r.unlinked_all or 0)
        external = int(r.external or 0)
        bots = int(r.bots or 0)
        # 「还没认」= 认不出的总数减去已确认是外部人员的。后者是**已处理**,
        # 把它算进缺口里会让这个数永远降不下去 —— 与治理面同一条口径。
        pending_id = max(unlinked_all - external, 0)
        live_total = (
            int(r.active or 0) + int(r.pending or 0) + int(r.manual_wait or 0)
        )
        identified = max(live_total - unlinked_all, 0)
        out.append(
            {
                "org_id": r.id,
                "org_slug": r.slug,
                "is_community": bool(r.is_community),
                "offboarding_policy": r.offboarding_policy,
                "active": int(r.active or 0),
                "pending_invite": int(r.pending or 0),
                "awaiting_manual": int(r.manual_wait or 0),
                "live_total": live_total,
                "identified": identified,
                "not_yet_identified": pending_id,
                "confirmed_external": external,
                # 机器账号单独报:它和「别家公司的人」要问的问题不同。
                "bots": bots,
                "outside_humans": max(external - bots, 0),
                # 覆盖率的分母刻意用 live_total 而不是 identified —— 我们想
                # 知道的是「这个组织里有多少人是离职回收够得着的」。
                "identified_pct": (
                    round(identified * 100.0 / live_total, 1)
                    if live_total else None
                ),
                "seat_plan": r.seat_plan,
                "seat_filled": r.seat_filled,
                "seat_total": r.seat_total,
                "seat_remaining": const.seat_remaining(
                    r.seat_plan, r.seat_filled, r.seat_total,
                ),
                "seat_checked_at": _iso(r.seat_checked_at),
                "seat_billing_url": const.seat_billing_url(r.slug),
            }
        )
    return out


async def _by_source(db: AsyncSession) -> dict[str, Any]:
    """成员资格是怎么来的 —— 这一页最该被看见的一张图。"""
    rows = list(
        (
            await db.execute(
                select(
                    GithubOrgMembership.source,
                    func.count().label("n"),
                )
                .where(
                    GithubOrgMembership.state.in_(const.MEMBERSHIP_LIVE_STATES)
                )
                .group_by(GithubOrgMembership.source)
                .order_by(func.count().desc())
            )
        ).all()
    )
    counts = {str(s): int(n) for s, n in rows}
    total = sum(counts.values())
    automated = sum(counts.get(s, 0) for s in AUTOMATED_SOURCES)
    discovered = sum(counts.get(s, 0) for s in DISCOVERED_SOURCES)
    return {
        "counts": counts,
        "total": total,
        "automated": automated,
        "discovered": discovered,
        "manual": counts.get(const.SOURCE_MANUAL, 0),
        # 「这套自动化覆盖了多少」。上线第一天它是 0 —— 那是对的,
        # 因为在此之前没有任何流程管过这件事。
        "automated_pct": (
            round(automated * 100.0 / total, 1) if total else None
        ),
    }


async def _trend(db: AsyncSession, *, days: int, today: date) -> dict[str, Any]:
    """近 N 天的加入 / 移除。只读只追加的事件流,不拿当前状态倒推历史。"""
    since = datetime.combine(
        today - timedelta(days=days), datetime.min.time(),
    )
    joined_types = (const.EVENT_JOINED, const.EVENT_INVITED)
    rows = list(
        (
            await db.execute(
                select(
                    func.date(GithubOrgMembershipEvent.created_at).label("d"),
                    func.sum(
                        case(
                            (
                                GithubOrgMembershipEvent.event_type.in_(
                                    joined_types
                                ),
                                1,
                            ),
                            else_=0,
                        )
                    ).label("joined"),
                    func.sum(
                        case(
                            (
                                GithubOrgMembershipEvent.event_type
                                == const.EVENT_REMOVED,
                                1,
                            ),
                            else_=0,
                        )
                    ).label("removed"),
                )
                .where(GithubOrgMembershipEvent.created_at >= since)
                .group_by(func.date(GithubOrgMembershipEvent.created_at))
                .order_by(func.date(GithubOrgMembershipEvent.created_at))
            )
        ).all()
    )
    return {
        "days": days,
        "points": [
            {
                "date": (r.d.isoformat() if hasattr(r.d, "isoformat") else str(r.d)),
                "joined": int(r.joined or 0),
                "removed": int(r.removed or 0),
            }
            for r in rows
        ],
    }


async def _offboarding_scoreboard(db: AsyncSession) -> dict[str, Any]:
    """离职回收的成绩单 —— 这套东西最初要修的就是这一半。"""
    rows = list(
        (
            await db.execute(
                select(
                    GithubOrgMembership.removal_source,
                    func.count().label("n"),
                )
                .where(
                    GithubOrgMembership.state
                    == const.MEMBERSHIP_STATE_REMOVED,
                    GithubOrgMembership.removal_source.is_not(None),
                )
                .group_by(GithubOrgMembership.removal_source)
            )
        ).all()
    )
    by_source = {str(s): int(n) for s, n in rows}
    still_in = int(
        (
            await db.execute(
                select(func.count())
                .select_from(GithubOrgMembership)
                .join(GithubOrg, GithubOrg.id == GithubOrgMembership.org_id)
                .join(
                    IdentityPerson,
                    IdentityPerson.id == GithubOrgMembership.person_id,
                )
                .where(
                    GithubOrgMembership.state.in_(const.MEMBERSHIP_LIVE_STATES),
                    GithubOrg.deleted_at.is_(None),
                    GithubOrg.offboarding_policy
                    != const.OFFBOARDING_POLICY_EXEMPT,
                    IdentityPerson.employment_status.in_(
                        ("left", "leaver_pending"),
                    ),
                )
            )
        ).scalar_one()
    )
    return {
        "by_removal_source": by_source,
        "reclaimed_by_offboarding": by_source.get(
            const.REMOVAL_SOURCE_OFFBOARDING, 0,
        ),
        # 应该恒为 0。它不是统计,是这套东西的成败本身。
        "leavers_still_in_orgs": still_in,
    }


async def build_report(
    db: AsyncSession, *, days: int = TREND_DAYS, today: date | None = None,
) -> dict[str, Any]:
    """一次调用拿到整页报表。

    与治理待办共用同一批底层语义(live 状态集、豁免组织、「已确认外部」不算
    缺口),但**不复用它的返回值** —— 待办是「要做什么」,报表是「做成了
    多少」,两者的取数方向不同,硬拼在一起会让两边都变形。
    """
    today = today or datetime.now(UTC).date()
    by_org = await _by_org(db)
    live_total = sum(o["live_total"] for o in by_org)
    identified = sum(o["identified"] for o in by_org)
    return {
        "generated_at": _iso(datetime.now(UTC)),
        "totals": {
            "orgs": len(by_org),
            "live_memberships": live_total,
            "identified": identified,
            "not_yet_identified": sum(
                o["not_yet_identified"] for o in by_org
            ),
            "confirmed_external": sum(
                o["confirmed_external"] for o in by_org
            ),
            "bots": sum(o["bots"] for o in by_org),
            "outside_humans": sum(o["outside_humans"] for o in by_org),
            "identified_pct": (
                round(identified * 100.0 / live_total, 1)
                if live_total else None
            ),
        },
        "by_org": by_org,
        "by_source": await _by_source(db),
        "trend": await _trend(db, days=days, today=today),
        "offboarding": await _offboarding_scoreboard(db),
    }