CardScoreDataServer/app/api/images.py

import os
import uuid
import json
from typing import Optional, Dict, Any
from enum import Enum
from fastapi import APIRouter, File, UploadFile, Depends, HTTPException, Form, Path
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 CardImageResponse, ImageJsonPairResponse
from app.core.database_loader import get_db_connection

logger = get_logger(__name__)
router = APIRouter()
db_dependency = Depends(get_db_connection)


class ImageType(str, Enum):
    front_face = "front_face"
    back_face = "back_face"
    front_edge = "front_edge"
    back_edge = "back_edge"


@router.post("/insert/{card_id}", response_model=CardImageResponse, status_code=201,
             summary="为卡牌上传并关联一张图片")
async def upload_image_for_card(
        card_id: int = Path(..., description="要关联的卡牌ID"),
        image_type: ImageType = Form(..., description="图片类型 (front_face, back_face, etc.)"),
        image: UploadFile = File(..., description="图片文件"),
        json_data_str: str = Form(..., description="与图片关联的JSON字符串"),
        image_name: Optional[str] = Form(None, description="图片的可选名称"),
        db_conn: PooledMySQLConnection = db_dependency
):
    """
    上传一张图片，将其存入 card_images 表，并更新 cards 表中对应的外键字段。
    这是一个事务性操作。
    """
    try:
        detection_json = json.loads(json_data_str)
    except json.JSONDecodeError:
        raise HTTPException(status_code=400, detail="JSON格式无效。")

    # 保存图片文件
    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:
            buffer.write(await image.read())
    except Exception as e:
        logger.error(f"保存图片失败: {e}")
        raise HTTPException(status_code=500, detail="无法保存图片文件。")

    cursor = None
    try:
        cursor = db_conn.cursor()

        # 1. 插入图片记录到 card_images
        query_insert_image = (
            f"INSERT INTO {settings.DB_IMAGE_TABLE_NAME} (image_name, image_path, detection_json) "
            "VALUES (%s, %s, %s)"
        )
        params = (image_name, str(image_path), json.dumps(detection_json, ensure_ascii=False))
        cursor.execute(query_insert_image, params)
        new_image_id = cursor.lastrowid

        # 2. 更新 cards 表，将新图片ID设置到对应字段
        # image_type.value 的值是 "front_face", "back_face" 等
        column_to_update = f"{image_type.value}_id"
        query_update_card = (
            f"UPDATE {settings.DB_CARD_TABLE_NAME} SET {column_to_update} = %s "
            f"WHERE card_id = %s"
        )
        cursor.execute(query_update_card, (new_image_id, card_id))

        if cursor.rowcount == 0:
            raise HTTPException(status_code=404, detail=f"ID为 {card_id} 的卡牌未找到，操作已回滚。")

        db_conn.commit()
        logger.info(f"图片 {new_image_id} 已成功关联到卡牌 {card_id} 的 {image_type.value}。")

        # 查询并返回完整的图片记录
        cursor.execute(f"SELECT * FROM {settings.DB_IMAGE_TABLE_NAME} WHERE image_id = %s", (new_image_id,))
        new_image_data = cursor.fetchone()
        columns = [desc[0] for desc in cursor.description]
        return CardImageResponse.model_validate(dict(zip(columns, new_image_data)))

    except Exception as e:
        db_conn.rollback()
        if os.path.exists(image_path):
            os.remove(image_path)  # 如果数据库操作失败，删除已上传的文件
        logger.error(f"关联图片到卡牌失败: {e}")
        if isinstance(e, HTTPException): raise e
        raise HTTPException(status_code=500, detail="数据库操作失败，所有更改已回滚。")
    finally:
        if cursor:
            cursor.close()


@router.get("/jsons/{image_id}", response_model=ImageJsonPairResponse, summary="获取图片的原始和修改后JSON")
def get_image_jsons(image_id: int, db_conn: PooledMySQLConnection = db_dependency):
    """获取指定图片ID的 detection_json 和 modified_json。"""
    cursor = None
    try:
        cursor = db_conn.cursor(dictionary=True)
        query = f"SELECT image_id, detection_json, modified_json FROM {settings.DB_IMAGE_TABLE_NAME} WHERE image_id = %s"
        cursor.execute(query, (image_id,))
        result = cursor.fetchone()
        if not result:
            raise HTTPException(status_code=404, detail=f"ID为 {image_id} 的图片未找到。")

        return ImageJsonPairResponse.model_validate(result)
    except Exception as e:
        logger.error(f"获取JSON对失败 ({image_id}): {e}")
        if isinstance(e, HTTPException): raise e
        raise HTTPException(status_code=500, detail="数据库查询失败。")
    finally:
        if cursor:
            cursor.close()


@router.put("/update/json/{image_id}", status_code=200, summary="7. 修改图片的 modified_json")
def update_image_modified_json(
        image_id: int,
        new_json_data: Dict[str, Any],
        db_conn: PooledMySQLConnection = db_dependency
):
    """根据 image_id 更新 modified_json 字段。updated_at 会自动更新。"""
    cursor = None
    try:
        cursor = db_conn.cursor()
        new_json_str = json.dumps(new_json_data, ensure_ascii=False)
        # updated_at 会由数据库自动更新
        query = f"UPDATE {settings.DB_IMAGE_TABLE_NAME} SET modified_json = %s WHERE image_id = %s"
        cursor.execute(query, (new_json_str, image_id))

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

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


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

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

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

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