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, ResultImagePathType
from app.utils.scheme import ImageType, IMAGE_TYPE_TO_SCORE_TYPE
from app.core.database_loader import get_db_connection
from app.crud import crud_card

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}。")

        try:
            crud_card.update_card_scores_and_status(db_conn, card_id)
            logger.info(f"卡牌 {card_id} 的分数和状态已更新。")
        except Exception as score_update_e:
            # 即使分数更新失败，图片也已经成功插入，记录错误但不回滚
            logger.error(f"更新卡牌 {card_id} 分数失败: {score_update_e}")

        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 会自动更新
    """
    card_id_to_update = None
    cursor = None
    try:
        cursor = db_conn.cursor(dictionary=True)

        # 1️ 获取图片信息
        cursor.execute(f"SELECT image_type, card_id 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} 的图片未找到。")

        card_id_to_update = row["card_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}")

        logger.info("开始计算分数")
        # 2️ 调用远程计算接口
        try:
            response = await run_in_threadpool(
                lambda: requests.post(
                    settings.SCORE_RECALCULATE_ENDPOINT,
                    params={"score_type": score_type}, json=new_json_data, timeout=20
                )
            )
        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}")
        logger.info("分数计算完成")

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

        cursor.execute(update_query, (recalculated_json_str, id))

        logger.info(f"更新 id={id} 的操作影响了 {cursor.rowcount} 行。")

        if cursor.rowcount == 0:
            logger.warning(f"更新 id={id} 的 modified_json 未影响任何行。")

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

        # 更新对应的 cards 的分数状态
        try:
            crud_card.update_card_scores_and_status(db_conn, card_id_to_update)
            logger.info(f"卡牌 {card_id_to_update} 的分数和状态已更新。")
        except Exception as score_update_e:
            logger.error(f"更新卡牌 {card_id_to_update} 分数失败: {score_update_e}")

        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.put("/update/annotated/{id}", status_code=200, summary="为指定图片记录上传绘制后的结果图")
async def upload_result_image(
        id: int = Path(..., description="要更新的图片记录ID"),
        path_type: ResultImagePathType = Form(...,
                                              description="要更新的路径类型 ('detection' 或 'modified')"),
        image: UploadFile = File(..., description="结果图片文件"),
        db_conn: PooledMySQLConnection = db_dependency
):
    """
    为一条现有的 card_images 记录上传 detection_image_path 或 modified_image_path 对应的图片。
    如果该字段已有图片，旧的图片文件将被删除。
    """
    # 1. 保存新图片文件
    file_extension = os.path.splitext(image.filename)[1]
    unique_filename = f"{uuid.uuid4()}{file_extension}"
    new_image_path = settings.DATA_DIR / unique_filename
    relative_path = f"/{new_image_path.parent.name}/{new_image_path.name}"
    try:
        with open(new_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(dictionary=True)

        # 2. 确定要更新的字段名
        column_to_update = f"{path_type.value}_image_path"

        # 3. 查询旧的图片路径，以便稍后删除
        cursor.execute(f"SELECT {column_to_update} FROM {settings.DB_IMAGE_TABLE_NAME} WHERE id = %s", (id,))
        record = cursor.fetchone()
        if not record:
            os.remove(new_image_path)  # 如果记录不存在，删除刚上传的文件
            raise HTTPException(status_code=404, detail=f"ID为 {id} 的图片记录未找到。")

        old_path = record.get(column_to_update)

        # 4. 更新数据库
        update_query = f"UPDATE {settings.DB_IMAGE_TABLE_NAME} SET {column_to_update} = %s WHERE id = %s"
        cursor.execute(update_query, (relative_path, id))
        db_conn.commit()
        logger.info(f"图片记录 {id} 的 {column_to_update} 已更新为 {relative_path}")

        # 5. 删除旧的物理文件（如果存在）
        if old_path:
            absolute_old_path = settings.BASE_PATH / old_path.lstrip('/\\')
            if os.path.exists(absolute_old_path):
                try:
                    os.remove(absolute_old_path)
                    logger.info(f"旧的结果图片文件已删除: {absolute_old_path}")
                except OSError as e:
                    logger.error(f"删除旧文件失败 {absolute_old_path}: {e}")

        # 6. 返回更新后的路径
        return {
            "message": f"成功更新图片ID {id} 的结果图",
            column_to_update: relative_path
        }

    except Exception as e:
        db_conn.rollback()
        # 如果发生任何错误，都要删除刚刚上传的新文件
        if os.path.exists(new_image_path):
            os.remove(new_image_path)
        logger.error(f"更新结果图片路径失败 (id={id}): {e}")
        if isinstance(e, HTTPException): raise e
        raise HTTPException(status_code=500, detail="数据库操作失败，更改已回滚。")
    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()