desungongpai/app/routers/bluetooth.py

"""
Bluetooth Router - 蓝牙数据接口
API endpoints for querying Bluetooth punch and location records.
"""

import math
from datetime import datetime
from typing import Literal

from fastapi import APIRouter, Depends, HTTPException, Query
from fastapi.responses import Response
from sqlalchemy import func, select
from sqlalchemy.ext.asyncio import AsyncSession

from app.database import get_db
from app.models import BluetoothRecord
from app.services.export_utils import build_csv_content, csv_filename
from app.schemas import (
    APIResponse,
    BluetoothRecordResponse,
    PaginatedList,
)
from app.services import device_service

router = APIRouter(prefix="/api/bluetooth", tags=["Bluetooth / 蓝牙数据"])


@router.get(
    "",
    response_model=APIResponse[PaginatedList[BluetoothRecordResponse]],
    summary="获取蓝牙记录列表 / List bluetooth records",
)
async def list_bluetooth_records(
    device_id: int | None = Query(default=None, description="设备ID / Device ID"),
    record_type: str | None = Query(default=None, description="记录类型 / Record type (punch/location)"),
    beacon_mac: str | None = Query(default=None, description="信标MAC / Beacon MAC filter"),
    start_time: datetime | None = Query(default=None, description="开始时间 / Start time (ISO 8601)"),
    end_time: datetime | None = Query(default=None, description="结束时间 / End time (ISO 8601)"),
    sort_order: Literal["asc", "desc"] = Query(default="desc", description="排序方向 / Sort order (asc/desc)"),
    page: int = Query(default=1, ge=1, description="页码 / Page number"),
    page_size: int = Query(default=20, ge=1, le=100, description="每页数量 / Items per page"),
    db: AsyncSession = Depends(get_db),
):
    """
    获取蓝牙数据记录列表，支持按设备、记录类型、信标MAC、时间范围过滤。
    List Bluetooth records with filters for device, record type, beacon MAC, and time range.
    """
    query = select(BluetoothRecord)
    count_query = select(func.count(BluetoothRecord.id))

    if device_id is not None:
        query = query.where(BluetoothRecord.device_id == device_id)
        count_query = count_query.where(BluetoothRecord.device_id == device_id)

    if record_type:
        query = query.where(BluetoothRecord.record_type == record_type)
        count_query = count_query.where(BluetoothRecord.record_type == record_type)

    if beacon_mac:
        query = query.where(BluetoothRecord.beacon_mac == beacon_mac)
        count_query = count_query.where(BluetoothRecord.beacon_mac == beacon_mac)

    if start_time:
        query = query.where(BluetoothRecord.recorded_at >= start_time)
        count_query = count_query.where(BluetoothRecord.recorded_at >= start_time)

    if end_time:
        query = query.where(BluetoothRecord.recorded_at <= end_time)
        count_query = count_query.where(BluetoothRecord.recorded_at <= end_time)

    total_result = await db.execute(count_query)
    total = total_result.scalar() or 0

    offset = (page - 1) * page_size
    order = BluetoothRecord.recorded_at.asc() if sort_order == "asc" else BluetoothRecord.recorded_at.desc()
    query = query.order_by(order).offset(offset).limit(page_size)
    result = await db.execute(query)
    records = list(result.scalars().all())

    return APIResponse(
        data=PaginatedList(
            items=[BluetoothRecordResponse.model_validate(r) for r in records],
            total=total,
            page=page,
            page_size=page_size,
            total_pages=math.ceil(total / page_size) if total else 0,
        )
    )


@router.get(
    "/export",
    summary="导出蓝牙记录 CSV / Export bluetooth records CSV",
)
async def export_bluetooth(
    device_id: int | None = Query(default=None, description="设备ID"),
    record_type: str | None = Query(default=None, description="记录类型 (punch/location)"),
    beacon_mac: str | None = Query(default=None, description="信标MAC"),
    start_time: datetime | None = Query(default=None, description="开始时间 ISO 8601"),
    end_time: datetime | None = Query(default=None, description="结束时间 ISO 8601"),
    db: AsyncSession = Depends(get_db),
):
    """导出蓝牙记录为 CSV，支持设备/类型/信标/时间筛选。最多导出 50000 条。"""
    query = select(BluetoothRecord)
    if device_id is not None:
        query = query.where(BluetoothRecord.device_id == device_id)
    if record_type:
        query = query.where(BluetoothRecord.record_type == record_type)
    if beacon_mac:
        query = query.where(BluetoothRecord.beacon_mac == beacon_mac)
    if start_time:
        query = query.where(BluetoothRecord.recorded_at >= start_time)
    if end_time:
        query = query.where(BluetoothRecord.recorded_at <= end_time)
    query = query.order_by(BluetoothRecord.recorded_at.desc()).limit(50000)

    result = await db.execute(query)
    records = list(result.scalars().all())

    headers = ["ID", "设备ID", "IMEI", "记录类型", "信标MAC", "UUID", "Major", "Minor", "RSSI", "电量", "考勤类型", "纬度", "经度", "记录时间"]
    fields = ["id", "device_id", "imei", "record_type", "beacon_mac", "beacon_uuid", "beacon_major", "beacon_minor", "rssi", "beacon_battery", "attendance_type", "latitude", "longitude", "recorded_at"]

    content = build_csv_content(headers, records, fields)
    return Response(
        content=content,
        media_type="text/csv; charset=utf-8",
        headers={"Content-Disposition": f"attachment; filename={csv_filename('bluetooth')}"},
    )


@router.get(
    "/stats",
    response_model=APIResponse[dict],
    summary="蓝牙数据统计 / Bluetooth statistics",
)
async def bluetooth_stats(
    start_time: datetime | None = Query(default=None, description="开始时间"),
    end_time: datetime | None = Query(default=None, description="结束时间"),
    db: AsyncSession = Depends(get_db),
):
    """
    蓝牙数据统计：总记录数、按类型分布、按信标MAC分组TOP20、RSSI分布。
    Bluetooth stats: total, by type, top beacons, RSSI distribution.
    """
    from sqlalchemy import case

    filters = []
    if start_time:
        filters.append(BluetoothRecord.recorded_at >= start_time)
    if end_time:
        filters.append(BluetoothRecord.recorded_at <= end_time)

    def _where(q):
        return q.where(*filters) if filters else q

    total = (await db.execute(_where(select(func.count(BluetoothRecord.id))))).scalar() or 0

    # By record_type
    type_result = await db.execute(_where(
        select(BluetoothRecord.record_type, func.count(BluetoothRecord.id))
        .group_by(BluetoothRecord.record_type)
    ))
    by_type = {row[0]: row[1] for row in type_result.all()}

    # Top 20 beacons by record count
    beacon_result = await db.execute(_where(
        select(BluetoothRecord.beacon_mac, func.count(BluetoothRecord.id).label("cnt"))
        .where(BluetoothRecord.beacon_mac.is_not(None))
        .group_by(BluetoothRecord.beacon_mac)
        .order_by(func.count(BluetoothRecord.id).desc())
        .limit(20)
    ))
    top_beacons = [{"beacon_mac": row[0], "count": row[1]} for row in beacon_result.all()]

    # RSSI distribution
    rssi_result = await db.execute(_where(
        select(
            func.sum(case(((BluetoothRecord.rssi.is_not(None)) & (BluetoothRecord.rssi >= -50), 1), else_=0)).label("strong"),
            func.sum(case(((BluetoothRecord.rssi < -50) & (BluetoothRecord.rssi >= -70), 1), else_=0)).label("medium"),
            func.sum(case(((BluetoothRecord.rssi < -70) & (BluetoothRecord.rssi.is_not(None)), 1), else_=0)).label("weak"),
            func.sum(case((BluetoothRecord.rssi.is_(None), 1), else_=0)).label("unknown"),
        )
    ))
    rrow = rssi_result.one()
    rssi_distribution = {
        "strong_above_-50": int(rrow.strong or 0),
        "medium_-50_-70": int(rrow.medium or 0),
        "weak_below_-70": int(rrow.weak or 0),
        "unknown": int(rrow.unknown or 0),
    }

    return APIResponse(data={
        "total": total,
        "by_type": by_type,
        "top_beacons": top_beacons,
        "rssi_distribution": rssi_distribution,
    })


@router.get(
    "/device/{device_id}",
    response_model=APIResponse[PaginatedList[BluetoothRecordResponse]],
    summary="获取设备蓝牙记录 / Get bluetooth records for device",
)
async def device_bluetooth_records(
    device_id: int,
    record_type: str | None = Query(default=None, description="记录类型 / Record type (punch/location)"),
    start_time: datetime | None = Query(default=None, description="开始时间 / Start time"),
    end_time: datetime | None = Query(default=None, description="结束时间 / End time"),
    page: int = Query(default=1, ge=1, description="页码"),
    page_size: int = Query(default=20, ge=1, le=100, description="每页数量"),
    db: AsyncSession = Depends(get_db),
):
    """
    获取指定设备的蓝牙数据记录。
    Get Bluetooth records for a specific device.
    """
    device = await device_service.get_device(db, device_id)
    if device is None:
        raise HTTPException(status_code=404, detail=f"Device {device_id} not found / 未找到设备{device_id}")

    query = select(BluetoothRecord).where(BluetoothRecord.device_id == device_id)
    count_query = select(func.count(BluetoothRecord.id)).where(BluetoothRecord.device_id == device_id)

    if record_type:
        query = query.where(BluetoothRecord.record_type == record_type)
        count_query = count_query.where(BluetoothRecord.record_type == record_type)

    if start_time:
        query = query.where(BluetoothRecord.recorded_at >= start_time)
        count_query = count_query.where(BluetoothRecord.recorded_at >= start_time)

    if end_time:
        query = query.where(BluetoothRecord.recorded_at <= end_time)
        count_query = count_query.where(BluetoothRecord.recorded_at <= end_time)

    total_result = await db.execute(count_query)
    total = total_result.scalar() or 0

    offset = (page - 1) * page_size
    query = query.order_by(BluetoothRecord.recorded_at.desc()).offset(offset).limit(page_size)
    result = await db.execute(query)
    records = list(result.scalars().all())

    return APIResponse(
        data=PaginatedList(
            items=[BluetoothRecordResponse.model_validate(r) for r in records],
            total=total,
            page=page,
            page_size=page_size,
            total_pages=math.ceil(total / page_size) if total else 0,
        )
    )


@router.post(
    "/batch-delete",
    response_model=APIResponse[dict],
    summary="批量删除蓝牙记录 / Batch delete bluetooth records",
)
async def batch_delete_bluetooth(
    body: dict,
    db: AsyncSession = Depends(get_db),
):
    """批量删除蓝牙记录，最多500条。 / Batch delete bluetooth records (max 500)."""
    record_ids = body.get("record_ids", [])
    if not record_ids:
        raise HTTPException(status_code=400, detail="record_ids is required")
    if len(record_ids) > 500:
        raise HTTPException(status_code=400, detail="Max 500 records per request")
    result = await db.execute(
        select(BluetoothRecord).where(BluetoothRecord.id.in_(record_ids))
    )
    records = list(result.scalars().all())
    for r in records:
        await db.delete(r)
    await db.flush()
    return APIResponse(data={"deleted": len(records)})


# NOTE: /{record_id} must be after /device/{device_id} to avoid route conflicts
@router.get(
    "/{record_id}",
    response_model=APIResponse[BluetoothRecordResponse],
    summary="获取蓝牙记录详情 / Get bluetooth record",
)
async def get_bluetooth_record(record_id: int, db: AsyncSession = Depends(get_db)):
    """按ID获取蓝牙记录详情 / Get bluetooth record details by ID."""
    result = await db.execute(
        select(BluetoothRecord).where(BluetoothRecord.id == record_id)
    )
    record = result.scalar_one_or_none()
    if record is None:
        raise HTTPException(status_code=404, detail=f"Bluetooth record {record_id} not found")
    return APIResponse(data=BluetoothRecordResponse.model_validate(record))