video/service/device_action_service.py

from fastapi import APIRouter, Query, Path
from mysql.connector import Error as MySQLError

from ds.db import db
from schema.device_action_schema import (
    DeviceActionCreate,
    DeviceActionResponse,
    DeviceActionListResponse
)
from schema.response_schema import APIResponse

# 路由配置
router = APIRouter(
    prefix="/device/actions",
    tags=["设备操作记录"]
)


# ------------------------------
# 内部方法: 新增设备操作记录（适配id自增）
# ------------------------------
def add_device_action(action_data: DeviceActionCreate) -> DeviceActionResponse:
    """
    新增设备操作记录（内部方法、非接口）
    :param action_data: 含client_ip和action（0/1）
    :return: 新增的完整记录
    """
    conn = None
    cursor = None
    try:
        conn = db.get_connection()
        cursor = conn.cursor(dictionary=True)

        # 插入SQL（id自增、依赖数据库自动生成）
        insert_query = """
            INSERT INTO device_action 
            (client_ip, action, created_at, updated_at)
            VALUES (%s, %s, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
        """
        cursor.execute(insert_query, (
            action_data.client_ip,
            action_data.action
        ))
        conn.commit()

        # 获取新增记录（通过自增ID查询）
        new_id = cursor.lastrowid
        cursor.execute("SELECT * FROM device_action WHERE id = %s", (new_id,))
        new_action = cursor.fetchone()

        return DeviceActionResponse(**new_action)

    except MySQLError as e:
        if conn:
            conn.rollback()
        raise Exception(f"新增记录失败: {str(e)}") from e
    finally:
        db.close_connection(conn, cursor)


# ------------------------------
# 接口: 分页查询操作记录列表（仅返回 total + device_actions）
# ------------------------------
@router.get("/list", response_model=APIResponse, summary="分页查询设备操作记录")
async def get_device_action_list(
        page: int = Query(1, ge=1, description="页码、默认1"),
        page_size: int = Query(10, ge=1, le=100, description="每页条数、1-100"),
        client_ip: str = Query(None, description="按客户端IP筛选"),
        action: int = Query(None, ge=0, le=1, description="按状态筛选（0=离线、1=上线）")
):
    conn = None
    cursor = None
    try:
        conn = db.get_connection()
        cursor = conn.cursor(dictionary=True)

        # 1. 构建筛选条件（参数化查询、避免注入）
        where_clause = []
        params = []
        if client_ip:
            where_clause.append("client_ip = %s")
            params.append(client_ip)
        if action is not None:
            where_clause.append("action = %s")
            params.append(action)

        # 2. 查询总记录数（用于返回 total）
        count_sql = "SELECT COUNT(*) AS total FROM device_action"
        if where_clause:
            count_sql += " WHERE " + " AND ".join(where_clause)
        cursor.execute(count_sql, params)
        total = cursor.fetchone()["total"]

        # 3. 分页查询记录（按创建时间倒序、确保最新记录在前）
        offset = (page - 1) * page_size
        list_sql = "SELECT * FROM device_action"
        if where_clause:
            list_sql += " WHERE " + " AND ".join(where_clause)
        list_sql += " ORDER BY created_at DESC LIMIT %s OFFSET %s"
        params.extend([page_size, offset])  # 追加分页参数（page/page_size仅用于查询、不返回）

        cursor.execute(list_sql, params)
        action_list = cursor.fetchall()

        # 4. 仅返回 total + device_actions
        return APIResponse(
            code=200,
            message="查询成功",
            data=DeviceActionListResponse(
                total=total,
                device_actions=[DeviceActionResponse(**item) for item in action_list]
            )
        )

    except MySQLError as e:
        raise Exception(f"查询记录失败: {str(e)}") from e
    finally:
        db.close_connection(conn, cursor)


@router.get("/{client_ip}", response_model=APIResponse, summary="根据IP查询设备操作记录")
async def get_device_actions_by_ip(
        client_ip: str = Path(..., description="客户端IP地址")
):
    conn = None
    cursor = None
    try:
        conn = db.get_connection()
        cursor = conn.cursor(dictionary=True)

        # 1. 查询总记录数
        count_sql = "SELECT COUNT(*) AS total FROM device_action WHERE client_ip = %s"
        cursor.execute(count_sql, (client_ip,))
        total = cursor.fetchone()["total"]

        # 2. 查询该IP的所有记录（按创建时间倒序）
        list_sql = """
            SELECT * FROM device_action 
            WHERE client_ip = %s 
            ORDER BY created_at DESC
        """
        cursor.execute(list_sql, (client_ip,))
        action_list = cursor.fetchall()

        # 3. 返回结果
        return APIResponse(
            code=200,
            message="查询成功",
            data=DeviceActionListResponse(
                total=total,
                device_actions=[DeviceActionResponse(**item) for item in action_list]
            )
        )

    except MySQLError as e:
        raise Exception(f"查询记录失败: {str(e)}") from e
    finally:
        db.close_connection(conn, cursor)