CardScoreDataServer/app/api/images.py

import os
import uuid
import json
import requests
from typing import Optional, Dict, Any
from fastapi import APIRouter, File, UploadFile, Depends, HTTPException, Form, Path
from fastapi.concurrency import run_in_threadpool
from fastapi.responses import JSONResponse, FileResponse
from mysql.connector.pooling import PooledMySQLConnection
from mysql.connector import IntegrityError

from app.core.config import settings
from app.core.logger import get_logger
from app.utils.scheme import CardImageResponse, ImageJsonPairResponse
from app.utils.scheme import ImageType, IMAGE_TYPE_TO_SCORE_TYPE
from app.core.database_loader import get_db_connection

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

@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="图片文件"),
        image_name: Optional[str] = Form(None, description="图片的可选名称"),
        json_data_str: str = Form(..., description="与图片关联的JSON字符串"),
        db_conn: PooledMySQLConnection = db_dependency
):
    """
    上传一张图片，并将其作为一条新记录存入 card_images 表。
    这是一个事务性操作，并会检查是否存在重复的 (card_id, image_type) 组合。
    """
    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
    relative_path = f"/{image_path.parent.name}/{image_path.name}"
    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()

        # 检查 card_id 是否存在
        cursor.execute(f"SELECT id FROM {settings.DB_CARD_TABLE_NAME} WHERE id = %s", (card_id,))
        if not cursor.fetchone():
            raise HTTPException(status_code=404, detail=f"ID为 {card_id} 的卡牌不存在。")

        query_insert_image = (
            f"INSERT INTO {settings.DB_IMAGE_TABLE_NAME} "
            "(card_id, image_type, image_name, image_path, detection_json) "
            "VALUES (%s, %s, %s, %s, %s)"
        )
        params = (
            card_id, image_type.value, image_name, relative_path, json.dumps(detection_json, ensure_ascii=False))

        cursor.execute(query_insert_image, params)
        new_id = cursor.lastrowid

        db_conn.commit()
        logger.info(f"图片 {new_id} 已成功关联到卡牌 {card_id}，类型为 {image_type.value}。")

        cursor.execute(f"SELECT * FROM {settings.DB_IMAGE_TABLE_NAME} WHERE id = %s", (new_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 IntegrityError as e:
        db_conn.rollback()
        if os.path.exists(image_path): os.remove(image_path)
        if e.errno == 1062:
            raise HTTPException(
                status_code=409,
                detail=f"卡牌ID {card_id} 已存在类型为 '{image_type.value}' 的图片，请勿重复添加。"
            )
        logger.error(f"数据库完整性错误: {e}")
        raise HTTPException(status_code=500, detail="数据库操作失败。")

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

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


@router.put("/update/json/{id}", status_code=200, summary="重新计算分数, 并修改图片的 modified_json")
async def update_image_modified_json(
        id: int,
        new_json_data: dict,
        db_conn: PooledMySQLConnection = db_dependency
):
    """
    根据 id 获取 image_type, 调用外部接口重新计算分数, 并更新 modified_json。
    updated_at 会自动更新
    """
    cursor = None
    try:
        cursor = db_conn.cursor(dictionary=True)

        # 1️ 获取图片信息
        cursor.execute(f"SELECT image_type FROM {settings.DB_IMAGE_TABLE_NAME} WHERE id = %s", (id,))
        row = cursor.fetchone()
        if not row:
            raise HTTPException(status_code=404, detail=f"ID为 {id} 的图片未找到。")

        image_type = row["image_type"]
        score_type = IMAGE_TYPE_TO_SCORE_TYPE.get(image_type)
        if not score_type:
            raise HTTPException(status_code=400, detail=f"未知的 image_type: {image_type}")

        # 2️ 调用远程计算接口
        try:
            params = {"score_type": score_type}
            payload = new_json_data
            response = await run_in_threadpool(
                lambda: requests.post(
                    settings.SCORE_RECALCULATE_ENDPOINT,
                    params=params,
                    json=payload,
                    timeout=30
                )
            )
        except Exception as e:
            raise HTTPException(status_code=500, detail=f"调用分数计算服务失败: {e}")

        if response.status_code != 200:
            logger.error(f"分数计算接口返回错误: {response.status_code}, {response.text}")
            raise HTTPException(status_code=response.status_code,
                                detail=f"分数计算接口返回错误: {response.text}")

        recalculated_json = response.json()

        # 3️ 保存结果到数据库
        recalculated_json_str = json.dumps(recalculated_json, ensure_ascii=False)
        update_query = f"UPDATE {settings.DB_IMAGE_TABLE_NAME} SET modified_json = %s WHERE id = %s"
        cursor.execute(update_query, (recalculated_json_str, id))

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

        db_conn.commit()
        logger.info(f"图片ID {id} 的 modified_json 已更新并重新计算。")

        return {
            "message": f"成功更新图片ID {id} 的JSON数据",
            "image_type": image_type,
            "score_type": score_type
        }

    except HTTPException:
        db_conn.rollback()
        raise
    except Exception as e:
        db_conn.rollback()
        logger.error(f"更新JSON失败 ({id}): {e}")
        raise HTTPException(status_code=500, detail=f"更新JSON数据失败: {e}")
    finally:
        if cursor:
            cursor.close()

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

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

        image_path = result[0]
        absolute_path = settings.BASE_PATH / image_path.lstrip('/\\')

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

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