CardScoreDataServer/app/api/cards.py

from datetime import datetime, date
import os
from typing import Optional, List
from fastapi import APIRouter, Depends, HTTPException, Query
from app.core.minio_client import minio_client
from mysql.connector.pooling import PooledMySQLConnection

from app.core.config import settings
from app.core.logger import get_logger
from app.core.database_loader import get_db_connection
from app.api.users import get_current_user, require_admin_user
from app.utils.scheme import (
    CardDetailResponse, CardListDetailResponse, CardType, SortBy, CardNoList,
    SortOrder, CardListResponseWrapper, CardListWithTotal, ReviewUpdate
)
from app.crud import crud_card

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


@router.post("/created", response_model=CardDetailResponse, status_code=201, summary="创建一个新的卡牌记录")
def create_card(
        card_name: Optional[str] = Query(None, summary="卡牌的名称"),
        cardNo: Optional[str] = Query(None, summary="卡牌编号"),
        card_type: CardType = Query(CardType.pokemon, summary="卡牌类型"),
        db_conn: PooledMySQLConnection = db_dependency
):
    """创建一个新的卡牌实体，此时它不关联任何图片。"""
    try:
        with db_conn.cursor(dictionary=True) as cursor:
            # 检查重复 cardNo 并删除旧数据
            if cardNo:
                cursor.execute(
                    f"SELECT id FROM {settings.DB_CARD_TABLE_NAME} WHERE cardNo = %s",
                    (cardNo,)
                )
                existing_rows = cursor.fetchall()
                for row in existing_rows:
                    # 调用已有的删除函数删除旧卡牌
                    delete_card(row['id'], db_conn)

            query = f"INSERT INTO {settings.DB_CARD_TABLE_NAME} (card_name, cardNo, card_type) VALUES (%s, %s, %s)"
            cursor.execute(query, (card_name, cardNo, card_type.value))
            db_conn.commit()
            new_id = cursor.lastrowid
            logger.info(f"新卡牌已创建, ID: {new_id}, 类型: {card_type.value}")

            # 返回刚创建的空卡牌信息
            cursor.execute(f"SELECT * FROM {settings.DB_CARD_TABLE_NAME} WHERE id = %s", (new_id,))
            new_card_data = cursor.fetchone()

            response_data = {**new_card_data, "images": []}
            return CardDetailResponse.model_validate(response_data)

    except Exception as e:
        db_conn.rollback()
        logger.error(f"创建卡牌失败: {e}")
        raise HTTPException(status_code=500, detail="数据库插入失败。")


@router.get("/query", response_model=CardDetailResponse, summary="获取指定卡牌的详细信息")
def get_card_details(card_id: int, db_conn: PooledMySQLConnection = db_dependency):
    """获取卡牌元数据以及所有与之关联的图片信息，包含 id_prev 和 id_next。"""

    # 1. 获取基本信息
    card_data = crud_card.get_card_with_details(db_conn, card_id)
    if not card_data:
        raise HTTPException(status_code=404, detail=f"ID为 {card_id} 的卡牌未找到。")

    # 2. 获取 id_prev 和 id_next
    try:
        with db_conn.cursor() as cursor:
            # 查询上一个ID (ID < current ORDER BY DESC LIMIT 1)
            sql_prev = f"SELECT id FROM {settings.DB_CARD_TABLE_NAME} WHERE id < %s ORDER BY id DESC LIMIT 1"
            cursor.execute(sql_prev, (card_id,))
            row_prev = cursor.fetchone()
            card_data['id_prev'] = row_prev[0] if row_prev else None

            # 查询下一个ID (ID > current ORDER BY ASC LIMIT 1)
            sql_next = f"SELECT id FROM {settings.DB_CARD_TABLE_NAME} WHERE id > %s ORDER BY id ASC LIMIT 1"
            cursor.execute(sql_next, (card_id,))
            row_next = cursor.fetchone()
            card_data['id_next'] = row_next[0] if row_next else None

    except Exception as e:
        logger.error(f"获取卡牌 {card_id} 的前后ID失败: {e}")
        # 不中断主流程，可以设为 None
        card_data['id_prev'] = None
        card_data['id_next'] = None

    return CardDetailResponse.model_validate(card_data)


@router.get("/query_next", response_model=CardDetailResponse, summary="获取指定卡牌id的下一个卡的详细信息")
def get_next_card_details(card_id: int, db_conn: PooledMySQLConnection = db_dependency):  # Renamed function
    """获取指定ID的下一张卡牌的元数据以及所有与之关联的图片信息。"""
    try:
        with db_conn.cursor(dictionary=True) as cursor:
            query_next_card = (
                f"SELECT id FROM {settings.DB_CARD_TABLE_NAME} "
                f"WHERE id > %s ORDER BY id ASC LIMIT 1"
            )
            cursor.execute(query_next_card, (card_id,))
            next_card_row = cursor.fetchone()

            if not next_card_row:
                raise HTTPException(status_code=200, detail=f"没有下一张")

            next_card_id = next_card_row['id']
            # 获取单个卡牌的完整信息
            card_data = crud_card.get_card_with_details(db_conn, next_card_id)
            if not card_data:
                raise HTTPException(status_code=404, detail=f"下一个卡牌ID {next_card_id} 未找到详细信息。")

            return CardDetailResponse.model_validate(card_data)

    except Exception as e:
        logger.error(f"查询下一个卡牌详情失败 (基准ID: {card_id}): {e}")
        if isinstance(e, HTTPException): raise e
        raise HTTPException(status_code=500, detail="服务器内部错误，查询数据库失败。")


@router.get("/card_list_filter", response_model=CardListResponseWrapper, summary="获取卡牌列表和总数")
def card_list_filter(
        card_id: Optional[int] = Query(None, description="筛选：卡牌ID"),
        cardNo: Optional[str] = Query(None, description="筛选：卡牌编号"),
        card_name: Optional[str] = Query(None, description="筛选：卡牌名称"),
        card_type: Optional[CardType] = Query(None, description="筛选：卡牌类型"),
        is_edited: Optional[bool] = Query(None, description="筛选：是否已编辑"),
        review_state: Optional[int] = Query(None, ge=1, le=4,
                                            description="筛选：审核状态(1待复检, 2已复检, 3审核未通过, 4审核通过)"),

        min_detection_score: Optional[float] = Query(None, ge=0, le=10),
        max_detection_score: Optional[float] = Query(None, ge=0, le=10),
        min_modified_score: Optional[float] = Query(None, ge=0, le=10),
        max_modified_score: Optional[float] = Query(None, ge=0, le=10),

        created_start: Optional[date] = Query(None),
        created_end: Optional[date] = Query(None),
        updated_start: Optional[date] = Query(None),
        updated_end: Optional[date] = Query(None),

        sort_by: SortBy = Query(SortBy.updated_at),
        sort_order: SortOrder = Query(SortOrder.desc),
        skip: int = Query(0, ge=0),
        page_num: int = Query(None, ge=1),
        limit: int = Query(100, ge=1, le=1000),
        current_user: dict = Depends(get_current_user),
        db_conn: PooledMySQLConnection = db_dependency
):
    """
    获取卡牌列表，返回格式包含 total 和 list。
    结构: { "data": { "total": 100, "list": [...] } }
    """
    if page_num is not None:
        skip = (page_num - 1) * limit

    try:
        result = crud_card.get_card_list_and_count(
            db_conn,
            card_id, cardNo, card_name, card_type, is_edited, review_state,
            min_detection_score, max_detection_score,
            min_modified_score, max_modified_score,
            created_start, created_end,
            updated_start, updated_end,
            sort_by, sort_order, skip, limit,
            None if current_user.get("is_admin") else current_user["id"]
        )

        # 组装返回数据，注意这里要进行 model_validate 转换 list 中的每一项
        # 1. 先把 list 里的字典转成 Pydantic 对象
        validated_list = [CardListDetailResponse.model_validate(c) for c in result['list']]

        # 2. 构造 data 部分
        data_content = CardListWithTotal(
            total=result['total'],
            list=validated_list
        )

        # 3. 构造最外层包装
        return CardListResponseWrapper(data=data_content)

    except Exception as e:
        logger.error(f"查询卡牌列表(带总数)失败: {e}")
        raise HTTPException(status_code=500, detail="获取数据列表失败。")


@router.delete("/delete/{id}", status_code=200, summary="删除卡牌及其所有关联图片")
def delete_card(id: int, db_conn: PooledMySQLConnection = db_dependency):
    """
    删除一张卡牌及其所有关联的图片记录和物理文件。
    利用了数据库的 ON DELETE CASCADE 特性。
    """
    try:
        with db_conn.cursor() as cursor:
            # 1. 查询所有关联图片的所有物理文件路径
            query_paths = (f"SELECT image_path, detection_image_path, modified_image_path "
                           f"FROM {settings.DB_IMAGE_TABLE_NAME} WHERE card_id = %s")
            cursor.execute(query_paths, (id,))
            image_paths_to_delete = [path for row in cursor.fetchall() for path in row if path]

            # 2. 删除卡牌记录
            query_delete_card = f"DELETE FROM {settings.DB_CARD_TABLE_NAME} WHERE id = %s"
            cursor.execute(query_delete_card, (id,))

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

            db_conn.commit()
            logger.info(f"ID {id} 的卡牌和关联数据已成功删除。")

            # 3. 删除物理文件 (改为 MinIO 删除)
            for path in image_paths_to_delete:
                # path 通常形如 /Data/xxx.jpg 或者 /DefectImage/xxx.jpg
                object_name = f"{settings.MINIO_BASE_PREFIX}{path}"
                try:
                    minio_client.remove_object(settings.MINIO_BUCKET, object_name)
                    logger.info(f"图片文件已从MinIO删除: {object_name}")
                except Exception as e:
                    logger.error(f"删除MinIO文件失败 {object_name}: {e}")

            return {"message": f"成功删除卡牌 ID {id} 及其所有关联数据"}

    except Exception as e:
        db_conn.rollback()
        logger.error(f"删除卡牌失败 ({id}): {e}")
        if isinstance(e, HTTPException): raise e
        raise HTTPException(status_code=500, detail="删除卡牌失败。")


@router.put("/review_state/{id}", status_code=200, summary="修改卡牌的审核状态")
def update_review_state(
        id: int,
        data: ReviewUpdate,
        current_user: dict = Depends(require_admin_user),
        db_conn: PooledMySQLConnection = db_dependency
):
    """
    修改某张卡牌的审核状态。

    review_state 参数说明:
    - 1: 待复检 (默认状态)
    - 2: 已复检
    - 3: 审核未通过
    - 4: 审核通过
    """
    review_state = data.review_state
    try:
        with db_conn.cursor() as cursor:

            cursor.execute(f"SELECT 1 FROM {settings.DB_CARD_TABLE_NAME} WHERE id = %s", (id,))
            if not cursor.fetchone():
                raise HTTPException(status_code=404, detail=f"ID为 {id} 的卡牌未找到。")

            # 更新指定 card_id 的 review_state 字段
            query_update = f"UPDATE {settings.DB_CARD_TABLE_NAME} SET review_state = %s WHERE id = %s"
            cursor.execute(query_update, (review_state, id))

            db_conn.commit()
            logger.info(f"卡牌 ID {id} 的审核状态已成功修改为 {review_state}。")

            return {"message": f"成功修改卡牌 ID {id} 的审核状态为 {review_state}"}

    except Exception as e:
        db_conn.rollback()
        logger.error(f"修改卡牌 {id} 审核状态失败: {e}")
        if isinstance(e, HTTPException):
            raise e
        raise HTTPException(status_code=500, detail="修改审核状态失败，数据库操作错误。")


@router.post("/batch_query/id_score", status_code=200, summary="批量查询id和分数")
def batch_query_id_score(
        data: CardNoList,
        db_conn: PooledMySQLConnection = db_dependency
):
    """
        根据给定的 cardNo 列表批量查询卡牌对应的卡牌ID (id) 和最终分数 (score)。
        分数返回逻辑：优先返回 modified_score，如果没有 modified_score 则返回 detection_score。
        """
    card_nos = data.cardNoList

    # 如果输入的列表为空，直接返回空结果
    if not card_nos:
        return {"cardNoList": []}

    try:
        with db_conn.cursor(dictionary=True) as cursor:
            # 构造 SQL 的 IN 子句
            format_strings = ','.join(['%s'] * len(card_nos))
            query = f"""
                    SELECT id, cardNo, detection_score, modified_score 
                    FROM {settings.DB_CARD_TABLE_NAME} 
                    WHERE cardNo IN ({format_strings})
                """

            # 执行查询
            cursor.execute(query, tuple(card_nos))
            results = cursor.fetchall()

            # 组装返回数据
            response_list = []
            for row in results:
                # 判断分数：优先取 modified_score，如果为 None 则取 detection_score
                score = row.get('modified_score')
                if score is None:
                    score = row.get('detection_score')

                response_list.append({
                    "cardNo": row['cardNo'],
                    "evaluateNo": row['id'],
                    "score": score
                })

            return {"cardNoList": response_list}

    except Exception as e:
        logger.error(f"批量查询卡牌ID和分数失败: {e}")
        raise HTTPException(status_code=500, detail="批量查询失败，数据库操作错误。")