H3ConuMS-v2/backend/app/api/v1/stats.py

"""统计 API"""
from fastapi import APIRouter, Depends
from sqlalchemy.orm import Session
from sqlalchemy import func, case
from app.core.database import get_db
from app.middleware.permission_middleware import require_permission
from app.models.device import ONUDevice, DeviceStatusHistory, DeviceDailySnapshot
from datetime import datetime, timedelta, date

router = APIRouter(prefix="/api/stats", tags=["统计"])


@router.get("/dashboard")
def get_dashboard(
    db: Session = Depends(get_db),
    _: dict = Depends(require_permission('device.view')),
):
    """仪表板统计：总体、城区、城郊、乡镇在线率"""
    # 每台设备最新状态子查询
    latest_subq = (
        db.query(
            DeviceStatusHistory.onu_device_id,
            func.max(DeviceStatusHistory.checked_at).label("max_checked_at")
        )
        .group_by(DeviceStatusHistory.onu_device_id)
        .subquery()
    )
    latest_status_subq = (
        db.query(
            DeviceStatusHistory.onu_device_id,
            DeviceStatusHistory.status
        )
        .join(
            latest_subq,
            (DeviceStatusHistory.onu_device_id == latest_subq.c.onu_device_id) &
            (DeviceStatusHistory.checked_at == latest_subq.c.max_checked_at)
        )
        .subquery()
    )

    rows = (
        db.query(
            ONUDevice.region,
            ONUDevice.school_name,
            func.count().label("total"),
            func.sum(case((latest_status_subq.c.status == 'online', 1), else_=0)).label("online"),
            func.sum(case((latest_status_subq.c.status == 'offline', 1), else_=0)).label("offline"),
        )
        .outerjoin(latest_status_subq, ONUDevice.id == latest_status_subq.c.onu_device_id)
        .group_by(ONUDevice.region, ONUDevice.school_name)
        .order_by(ONUDevice.region, ONUDevice.school_name)
        .all()
    )

    overall = {"total": 0, "online": 0, "offline": 0}
    urban_schools, suburban_schools = [], []
    rural_towns = {}

    for row in rows:
        region = row.region or ""
        total = int(row.total or 0)
        online = int(row.online or 0)
        offline = int(row.offline or 0)
        overall["total"] += total
        overall["online"] += online
        overall["offline"] += offline

        school_stat = {"name": row.school_name or "未知", "total": total, "online": online, "offline": offline}

        if region == "城区":
            urban_schools.append(school_stat)
        elif region == "城郊":
            suburban_schools.append(school_stat)
        else:
            if region not in rural_towns:
                rural_towns[region] = {"region": region, "total": 0, "online": 0, "offline": 0, "schools": []}
            rural_towns[region]["total"] += total
            rural_towns[region]["online"] += online
            rural_towns[region]["offline"] += offline
            rural_towns[region]["schools"].append(school_stat)

    def sort_by_rate(items):
        return sorted(items, key=lambda x: x["online"] / x["total"] if x["total"] > 0 else 0)

    def agg(items):
        return {"total": sum(s["total"] for s in items), "online": sum(s["online"] for s in items), "offline": sum(s["offline"] for s in items)}

    rural_list = sort_by_rate(list(rural_towns.values()))

    return {
        "overall": overall,
        "urban": {**agg(urban_schools), "schools": sort_by_rate(urban_schools)},
        "suburban": {**agg(suburban_schools), "schools": sort_by_rate(suburban_schools)},
        "rural": {**agg(list(rural_towns.values())), "towns": rural_list},
    }


@router.get("/summary")
def get_summary(
    db: Session = Depends(get_db),
    _: dict = Depends(require_permission('device.view')),
):
    """获取统计摘要"""
    total = db.query(ONUDevice).count()
    latest_status = db.query(
        DeviceStatusHistory.status,
        func.count(DeviceStatusHistory.id)
    ).group_by(DeviceStatusHistory.status).all()
    status_dict = dict(latest_status)
    return {"total": total, "online": status_dict.get('online', 0), "offline": status_dict.get('offline', 0)}


@router.get("/by-region")
def get_by_region(
    db: Session = Depends(get_db),
    _: dict = Depends(require_permission('device.view')),
):
    """各区域设备数量及在线率（用于饼图）"""
    latest_subq = (
        db.query(
            DeviceStatusHistory.onu_device_id,
            func.max(DeviceStatusHistory.checked_at).label("max_checked_at")
        )
        .group_by(DeviceStatusHistory.onu_device_id)
        .subquery()
    )
    latest_status_subq = (
        db.query(
            DeviceStatusHistory.onu_device_id,
            DeviceStatusHistory.status
        )
        .join(
            latest_subq,
            (DeviceStatusHistory.onu_device_id == latest_subq.c.onu_device_id) &
            (DeviceStatusHistory.checked_at == latest_subq.c.max_checked_at)
        )
        .subquery()
    )

    rows = (
        db.query(
            ONUDevice.region,
            func.count().label("total"),
            func.sum(case((latest_status_subq.c.status == 'online', 1), else_=0)).label("online"),
            func.sum(case((latest_status_subq.c.status == 'offline', 1), else_=0)).label("offline"),
        )
        .outerjoin(latest_status_subq, ONUDevice.id == latest_status_subq.c.onu_device_id)
        .group_by(ONUDevice.region)
        .order_by(func.count().desc())
        .all()
    )

    return [
        {
            "region": row.region or "未知",
            "total": int(row.total or 0),
            "online": int(row.online or 0),
            "offline": int(row.offline or 0),
        }
        for row in rows
    ]


@router.get("/trend")
def get_trend(
    days: int = 7,
    db: Session = Depends(get_db),
    _: dict = Depends(require_permission('device.view')),
):
    """获取状态趋势数据（优先查快照表，不足时实时聚合）"""
    today = date.today()
    date_range = [(today - timedelta(days=i)).strftime('%Y-%m-%d') for i in range(days - 1, -1, -1)]

    # 查快照表（不含今天，今天用实时数据）
    snapshots = (
        db.query(DeviceDailySnapshot)
        .filter(DeviceDailySnapshot.snapshot_date.in_(date_range[:-1]))
        .all()
    )
    snapshot_map = {s.snapshot_date: s for s in snapshots}

    # 今天实时聚合 — 取每个设备最新状态（不限日期），反映真实当前状况
    today_str = today.strftime('%Y-%m-%d')

    latest_subq = (
        db.query(
            DeviceStatusHistory.onu_device_id,
            func.max(DeviceStatusHistory.checked_at).label("max_checked_at"),
        )
        .group_by(DeviceStatusHistory.onu_device_id)
        .subquery()
    )
    today_row = (
        db.query(
            func.sum(case((DeviceStatusHistory.status == 'online', 1), else_=0)).label("online"),
            func.sum(case((DeviceStatusHistory.status == 'offline', 1), else_=0)).label("offline"),
        )
        .join(
            latest_subq,
            (DeviceStatusHistory.onu_device_id == latest_subq.c.onu_device_id) &
            (DeviceStatusHistory.checked_at == latest_subq.c.max_checked_at)
        )
        .one()
    )

    result = []
    for d in date_range:
        if d == today_str:
            result.append({
                "date": d,
                "online": int(today_row.online or 0),
                "offline": int(today_row.offline or 0),
            })
        elif d in snapshot_map:
            s = snapshot_map[d]
            result.append({"date": d, "online": s.online, "offline": s.offline})
        else:
            # 快照缺失时实时聚合该天数据
            day = datetime.strptime(d, '%Y-%m-%d').date()
            day_start = datetime.combine(day, datetime.min.time())
            day_end = datetime.combine(day, datetime.max.time())
            subq = (
                db.query(
                    DeviceStatusHistory.onu_device_id,
                    func.max(DeviceStatusHistory.checked_at).label("max_checked_at"),
                )
                .filter(DeviceStatusHistory.checked_at.between(day_start, day_end))
                .group_by(DeviceStatusHistory.onu_device_id)
                .subquery()
            )
            row = (
                db.query(
                    func.sum(case((DeviceStatusHistory.status == 'online', 1), else_=0)).label("online"),
                    func.sum(case((DeviceStatusHistory.status == 'offline', 1), else_=0)).label("offline"),
                )
                .join(
                    subq,
                    (DeviceStatusHistory.onu_device_id == subq.c.onu_device_id) &
                    (DeviceStatusHistory.checked_at == subq.c.max_checked_at)
                )
                .one()
            )
            result.append({"date": d, "online": int(row.online or 0), "offline": int(row.offline or 0)})

    return result

@router.get("/olt-stats")
def get_olt_stats(
    db: Session = Depends(get_db),
    _: dict = Depends(require_permission('device.view')),
):
    """获取每台 OLT 下的设备在线率统计"""
    from app.models.device import OLTDevice

    latest_subq = (
        db.query(
            DeviceStatusHistory.onu_device_id,
            func.max(DeviceStatusHistory.checked_at).label("max_checked_at")
        )
        .group_by(DeviceStatusHistory.onu_device_id)
        .subquery()
    )
    latest_status_subq = (
        db.query(DeviceStatusHistory.onu_device_id, DeviceStatusHistory.status)
        .join(latest_subq,
              (DeviceStatusHistory.onu_device_id == latest_subq.c.onu_device_id) &
              (DeviceStatusHistory.checked_at == latest_subq.c.max_checked_at))
        .subquery()
    )

    rows = (
        db.query(
            OLTDevice.id,
            OLTDevice.ip_address,
            OLTDevice.location,
            OLTDevice.region,
            func.count(ONUDevice.id).label("total"),
            func.sum(case((latest_status_subq.c.status == 'online', 1), else_=0)).label("online"),
            func.sum(case((latest_status_subq.c.status == 'offline', 1), else_=0)).label("offline"),
        )
        .outerjoin(ONUDevice, ONUDevice.olt_id == OLTDevice.id)
        .outerjoin(latest_status_subq, ONUDevice.id == latest_status_subq.c.onu_device_id)
        .group_by(OLTDevice.id)
        .order_by(OLTDevice.region, OLTDevice.location)
        .all()
    )

    return [
        {
            "olt_id": r.id,
            "name": r.location or r.ip_address,
            "ip": r.ip_address,
            "region": r.region or "未知",
            "total": int(r.total or 0),
            "online": int(r.online or 0),
            "offline": int(r.offline or 0),
        }
        for r in rows
    ]


@router.get("/offline-schools")
def get_offline_schools(
    db: Session = Depends(get_db),
    _: dict = Depends(require_permission('device.view')),
):
    """获取全部离线的学校列表"""
    _subq = (
        db.query(DeviceStatusHistory.onu_device_id,
                 func.max(DeviceStatusHistory.checked_at).label("max_checked_at"))
        .group_by(DeviceStatusHistory.onu_device_id).subquery()
    )
    _status_subq = (
        db.query(DeviceStatusHistory.onu_device_id, DeviceStatusHistory.status)
        .join(_subq,
              (DeviceStatusHistory.onu_device_id == _subq.c.onu_device_id) &
              (DeviceStatusHistory.checked_at == _subq.c.max_checked_at)).subquery()
    )
    rows = (
        db.query(
            ONUDevice.school_name, ONUDevice.region,
            func.count().label("total"),
            func.sum(case((_status_subq.c.status == 'online', 1), else_=0)).label("online"),
        )
        .outerjoin(_status_subq, ONUDevice.id == _status_subq.c.onu_device_id)
        .group_by(ONUDevice.school_name, ONUDevice.region)
        .having(func.sum(case((_status_subq.c.status == 'online', 1), else_=0)) == 0)
        .all()
    )
    return [
        {"school_name": r.school_name or "未知", "region": r.region or "未知", "total": int(r.total or 0)}
        for r in rows if int(r.total or 0) > 0
    ]


@router.get("/model-distribution")
def get_model_distribution(
    db: Session = Depends(get_db),
    _: dict = Depends(require_permission('device.view')),
):
    """统计 ONU 设备型号分布"""
    rows = (
        db.query(
            ONUDevice.model,
            func.count(ONUDevice.id).label("count"),
        )
        .filter(ONUDevice.model.isnot(None), ONUDevice.model != '')
        .group_by(ONUDevice.model)
        .order_by(func.count(ONUDevice.id).desc())
        .all()
    )
    unknown = db.query(func.count(ONUDevice.id)).filter(
        (ONUDevice.model.is_(None)) | (ONUDevice.model == '')
    ).scalar() or 0

    result = [{"model": r.model or "未知", "count": r.count} for r in rows]
    if unknown > 0:
        result.append({"model": "未知型号", "count": unknown})
    return result


@router.get("/school-locations")
def get_school_locations(
    db: Session = Depends(get_db),
    _: dict = Depends(require_permission('device.view')),
):
    """获取各学校的聚合位置数据（用于地图展示）"""
    latest_subq = (
        db.query(
            DeviceStatusHistory.onu_device_id,
            func.max(DeviceStatusHistory.checked_at).label("max_checked_at")
        )
        .group_by(DeviceStatusHistory.onu_device_id)
        .subquery()
    )
    latest_status_subq = (
        db.query(
            DeviceStatusHistory.onu_device_id,
            DeviceStatusHistory.status
        )
        .join(
            latest_subq,
            (DeviceStatusHistory.onu_device_id == latest_subq.c.onu_device_id) &
            (DeviceStatusHistory.checked_at == latest_subq.c.max_checked_at)
        )
        .subquery()
    )

    rows = (
        db.query(
            ONUDevice.school_name,
            ONUDevice.region,
            ONUDevice.latitude,
            ONUDevice.longitude,
            func.count().label("total"),
            func.sum(case((latest_status_subq.c.status == 'online', 1), else_=0)).label("online"),
            func.sum(case((latest_status_subq.c.status == 'offline', 1), else_=0)).label("offline"),
        )
        .outerjoin(latest_status_subq, ONUDevice.id == latest_status_subq.c.onu_device_id)
        .filter(ONUDevice.latitude.isnot(None))
        .filter(ONUDevice.longitude.isnot(None))
        .group_by(ONUDevice.school_name, ONUDevice.region, ONUDevice.latitude, ONUDevice.longitude)
        .all()
    )

    return [
        {
            "school_name": row.school_name or "未知",
            "region": row.region or "未知",
            "latitude": row.latitude,
            "longitude": row.longitude,
            "total": int(row.total or 0),
            "online": int(row.online or 0),
            "offline": int(row.offline or 0),
        }
        for row in rows
    ]