CardScoreDataServer/app/api/image_data.py

import os
import uuid
import json
from datetime import date, datetime
from typing import Optional, Dict, Any, List

from fastapi import APIRouter, File, UploadFile, Depends, HTTPException, Form, Query
from fastapi.responses import JSONResponse, FileResponse
from mysql.connector.pooling import PooledMySQLConnection

from app.core.config import settings
from app.core.logger import get_logger
from app.utils.scheme import ImageRecordResponse, map_row_to_model
from app.core.database_loader import get_db_connection

# --- 初始化 ---
logger = get_logger(__name__)
router = APIRouter()

# 创建一个依赖项的别名
db_dependency = Depends(get_db_connection)


# --- API 接口实现 ---

#  1: 存储图片和JSON数据
@router.post("/insert", status_code=201, summary="1. 存储图片和JSON数据")
async def create_image_data(
        image: UploadFile = File(..., description="要上传的图片文件"),
        json_data_str: str = Form(..., description="与图片关联的JSON格式字符串"),
        img_name: Optional[str] = Form(None, description="图片的可选名称"),
        db_conn: PooledMySQLConnection = db_dependency
):
    """
    接收图片和JSON数据，存入数据库。
    - 图片存储在 `Data` 目录。
    - 记录存入MySQL，`img_id` 自动生成。
    """
    try:
        img_result_json = json.loads(json_data_str)
    except json.JSONDecodeError:
        raise HTTPException(status_code=400, detail="`json_data_str` 格式无效。")

    # 生成唯一文件名并保存图片
    file_extension = os.path.splitext(image.filename)[1]
    unique_filename = f"{uuid.uuid4()}{file_extension}"
    image_path = settings.DATA_DIR / unique_filename

    try:
        with open(image_path, "wb") as buffer:
            content = await image.read()
            buffer.write(content)
        logger.info(f"图片已保存到: {image_path}")
    except Exception as e:
        logger.error(f"保存图片失败: {e}")
        raise HTTPException(status_code=500, detail="无法保存图片文件。")

    # 将记录插入数据库
    cursor = None
    try:
        cursor = db_conn.cursor()
        query = (
            f"INSERT INTO {settings.DB_TABLE_NAME} (img_name, img_path, img_result_json) "
            "VALUES (%s, %s, %s)"
        )
        # 确保存入数据库的是JSON字符串
        params = (img_name, str(image_path), json.dumps(img_result_json, ensure_ascii=False))
        cursor.execute(query, params)
        db_conn.commit()
        new_id = cursor.lastrowid
        logger.info(f"新记录已创建, ID: {new_id}")
        return {"message": "成功存储图片和数据", "img_id": new_id}
    except Exception as e:
        db_conn.rollback()
        logger.error(f"数据库插入失败: {e}")
        if os.path.exists(image_path):
            os.remove(image_path)
        raise HTTPException(status_code=500, detail="数据库插入失败。")
    finally:
        if cursor:
            cursor.close()


# 2: 获取数据列表 (带筛选)
@router.get("/data_list", response_model=List[ImageRecordResponse], summary="2. 获取数据列表 (可筛选)")
def list_image_records(
        start_id: Optional[int] = Query(None, description="筛选条件：起始img_id"),
        end_id: Optional[int] = Query(None, description="筛选条件：结束img_id"),
        name_like: Optional[str] = Query(None, description="筛选条件：名称模糊搜索"),
        start_date: Optional[date] = Query(None, description="筛选条件：起始日期 (YYYY-MM-DD)"),
        end_date: Optional[date] = Query(None, description="筛选条件：结束日期 (YYYY-MM-DD)"),
        skip: int = Query(0, ge=0, description="分页：跳过的记录数"),
        limit: int = Query(100, ge=1, le=1000, description="分页：每页的记录数"),
        db_conn: PooledMySQLConnection = db_dependency
):
    """
    根据多个可选条件查询记录列表，并支持分页。
    """
    query = f"SELECT * FROM {settings.DB_TABLE_NAME}"
    conditions = []
    params = []

    if start_id is not None:
        conditions.append("img_id >= %s")
        params.append(start_id)
    if end_id is not None:
        conditions.append("img_id <= %s")
        params.append(end_id)
    if name_like:
        conditions.append("img_name LIKE %s")
        params.append(f"%{name_like}%")
    if start_date:
        conditions.append("created_at >= %s")
        params.append(start_date)
    if end_date:
        conditions.append("created_at < DATE_ADD(%s, INTERVAL 1 DAY)")
        params.append(end_date)

    if conditions:
        query += " WHERE " + " AND ".join(conditions)

    query += " ORDER BY img_id DESC LIMIT %s OFFSET %s"
    params.extend([limit, skip])

    cursor = None
    try:
        cursor = db_conn.cursor()
        cursor.execute(query, tuple(params))
        columns = [desc[0] for desc in cursor.description]
        return [map_row_to_model(row, columns) for row in cursor.fetchall()]
    except Exception as e:
        logger.error(f"查询列表失败: {e}")
        raise HTTPException(status_code=500, detail="获取数据列表失败。")
    finally:
        if cursor:
            cursor.close()


#  2: 根据img_id查询
@router.get("/query/{img_id}", response_model=ImageRecordResponse, summary="2. 根据img_id查询完整记录")
def get_record_by_id(img_id: int, db_conn: PooledMySQLConnection = db_dependency):
    """获取指定ID的完整数据库记录。"""
    cursor = None
    try:
        cursor = db_conn.cursor()
        query = f"SELECT * FROM {settings.DB_TABLE_NAME} WHERE img_id = %s"
        cursor.execute(query, (img_id,))
        result = cursor.fetchone()
        if not result:
            raise HTTPException(status_code=404, detail=f"ID为 {img_id} 的记录未找到。")

        columns = [desc[0] for desc in cursor.description]
        return map_row_to_model(result, columns)
    except Exception as e:
        logger.error(f"ID查询失败 ({img_id}): {e}")
        if isinstance(e, HTTPException): raise e
        raise HTTPException(status_code=500, detail="数据库查询失败。")
    finally:
        if cursor:
            cursor.close()


#  3: 根据img_name查询
@router.get("/query/name/{img_name}", response_model=List[ImageRecordResponse], summary="3. 根据img_name查询记录")
def get_records_by_name(img_name: str, db_conn: PooledMySQLConnection = db_dependency):
    """获取所有与指定名称匹配的记录列表。"""
    cursor = None
    try:
        cursor = db_conn.cursor()
        query = f"SELECT * FROM {settings.DB_TABLE_NAME} WHERE img_name = %s"
        cursor.execute(query, (img_name,))
        results = cursor.fetchall()
        if not results:
            return []  # 未找到则返回空列表

        columns = [desc[0] for desc in cursor.description]
        return [map_row_to_model(row, columns) for row in results]
    except Exception as e:
        logger.error(f"名称查询失败 ({img_name}): {e}")
        raise HTTPException(status_code=500, detail="数据库查询失败。")
    finally:
        if cursor:
            cursor.close()


#  5: 修改JSON数据
@router.put("/update/json/{img_id}", status_code=200, summary="5. 修改指定ID记录的JSON数据")
def update_record_json(
        img_id: int,
        new_json_data: Dict[str, Any],
        db_conn: PooledMySQLConnection = db_dependency
):
    """根据img_id，用请求体中的新JSON覆盖原有的JSON数据。"""
    cursor = None
    try:
        cursor = db_conn.cursor()
        new_json_str = json.dumps(new_json_data, ensure_ascii=False)
        query = f"UPDATE {settings.DB_TABLE_NAME} SET img_result_json_new = %s WHERE img_id = %s"
        cursor.execute(query, (new_json_str, img_id))

        if cursor.rowcount == 0:
            raise HTTPException(status_code=404, detail=f"ID为 {img_id} 的记录未找到。")

        db_conn.commit()
        logger.info(f"ID {img_id} 的JSON数据已更新。")
        return {"message": f"成功更新 ID {img_id} 的JSON数据"}
    except Exception as e:
        db_conn.rollback()
        logger.error(f"更新JSON失败 ({img_id}): {e}")
        if isinstance(e, HTTPException): raise e
        raise HTTPException(status_code=500, detail="更新JSON数据失败。")
    finally:
        if cursor:
            cursor.close()


#  6: 获取图片文件
@router.get("/image/{img_id}", summary="6. 获取指定ID的图片文件")
def get_image_file(img_id: int, db_conn: PooledMySQLConnection = db_dependency):
    """根据img_id查找记录，并返回对应的图片文件。"""
    cursor = None
    try:
        cursor = db_conn.cursor()
        query = f"SELECT img_path FROM {settings.DB_TABLE_NAME} WHERE img_id = %s"
        cursor.execute(query, (img_id,))
        result = cursor.fetchone()

        if not result:
            raise HTTPException(status_code=404, detail=f"ID为 {img_id} 的记录未找到。")

        image_path = result[0]
        if not os.path.exists(image_path):
            logger.error(f"文件在服务器上未找到: {image_path} (数据库ID: {img_id})")
            raise HTTPException(status_code=404, detail="图片文件在服务器上不存在。")

        return FileResponse(image_path)
    except Exception as e:
        logger.error(f"获取图片失败 ({img_id}): {e}")
        if isinstance(e, HTTPException): raise e
        raise HTTPException(status_code=500, detail="获取图片文件失败。")
    finally:
        if cursor:
            cursor.close()


#  7: 获取JSON数据
@router.get("/json/{img_id}", summary="7. 获取指定ID的JSON数据")
def get_record_json(img_id: int, db_conn: PooledMySQLConnection = db_dependency):
    """
    根据img_id查找记录，并仅返回其JSON数据部分。
    优先返回 img_result_json_new，如果为 NULL 则返回 img_result_json。
    """
    cursor = None
    try:
        cursor = db_conn.cursor()
        query = (
            f"SELECT IFNULL({settings.DB_TABLE_NAME}.img_result_json_new, {settings.DB_TABLE_NAME}.img_result_json) AS img_result_json "
            f"FROM {settings.DB_TABLE_NAME} WHERE img_id = %s"
        )
        cursor.execute(query, (img_id,))
        result = cursor.fetchone()  # result will be a tuple like ('{"key": "value"}',)
        if not result:
            raise HTTPException(status_code=404, detail=f"ID为 {img_id} 的记录未找到。")

        # result[0] 已经是经过 IFNULL 处理后的目标 JSON 字符串
        json_to_return = json.loads(result[0])

        return JSONResponse(content=json_to_return)
    except Exception as e:
        logger.error(f"获取JSON失败 ({img_id}): {e}")
        if isinstance(e, HTTPException): raise e
        raise HTTPException(status_code=500, detail="获取JSON数据失败。")
    finally:
        if cursor:
            cursor.close()


#  8: 根据 img_id 删除记录
@router.delete("/delete/{img_id}", status_code=200, summary="8. 根据img_id删除记录及其图片")
def delete_record_by_id(
        img_id: int,
        db_conn: PooledMySQLConnection = db_dependency
):
    """
    根据img_id删除数据库记录以及存储在服务器上的对应图片文件。
    这是一个原子操作，如果文件删除失败，数据库更改将回滚。
    """
    cursor = None
    try:
        cursor = db_conn.cursor()

        # 1. 先查询记录，获取文件路径，并确认记录存在
        query_path = f"SELECT img_path FROM {settings.DB_TABLE_NAME} WHERE img_id = %s"
        cursor.execute(query_path, (img_id,))
        result = cursor.fetchone()

        if not result:
            raise HTTPException(status_code=404, detail=f"ID为 {img_id} 的记录未找到。")

        image_path = result[0]

        # 2. 删除数据库记录
        query_delete = f"DELETE FROM {settings.DB_TABLE_NAME} WHERE img_id = %s"
        cursor.execute(query_delete, (img_id,))

        # 3. 删除对应的图片文件（如果存在）
        if os.path.exists(image_path):
            try:
                os.remove(image_path)
                logger.info(f"图片文件已删除: {image_path}")
            except OSError as e:
                # 如果文件删除失败，回滚数据库操作以保持一致性
                db_conn.rollback()
                logger.error(f"删除文件失败: {image_path}. 数据库操作已回滚。错误: {e}")
                raise HTTPException(status_code=500, detail="删除文件失败，数据库操作已回滚。")
        else:
            logger.warning(f"数据库记录指向的文件不存在，无需删除: {image_path} (ID: {img_id})")

        # 4. 提交事务
        db_conn.commit()
        logger.info(f"ID {img_id} 的记录和关联文件已成功删除。")

        return {"message": f"成功删除 ID {img_id} 的记录及其关联文件"}

    except Exception as e:
        db_conn.rollback()
        logger.error(f"删除记录失败 ({img_id}): {e}")
        if isinstance(e, HTTPException):
            raise e
        raise HTTPException(status_code=500, detail="删除记录时发生服务器内部错误。")
    finally:
        if cursor:
            cursor.close()