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 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.utils.scheme import (
    CardDetailResponse, CardListDetailResponse, CardType, SortBy,
    SortOrder, CardListResponseWrapper, CardListWithTotal
)
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:

            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),
        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
        )

        # 组装返回数据，注意这里要进行 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. 删除物理文件
            for path in image_paths_to_delete:
                absolute_path = settings.BASE_PATH / path.lstrip('/\\')
                if os.path.exists(absolute_path):
                    try:
                        os.remove(absolute_path)
                        logger.info(f"图片文件已删除: {absolute_path}")
                    except OSError as e:
                        logger.error(f"删除文件失败 {absolute_path}: {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,
        review_state: int = Query(..., ge=1, le=4, description="审核状态 (1待复检, 2已复检, 3审核未通过, 4审核通过)"),
        db_conn: PooledMySQLConnection = db_dependency
):
    """
    修改某张卡牌的审核状态。

    review_state 参数说明:
    - 1: 待复检 (默认状态)
    - 2: 已复检
    - 3: 审核未通过
    - 4: 审核通过
    """
    try:
        with db_conn.cursor() as cursor:
            # 更新指定 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))

            if cursor.rowcount == 0:
                raise HTTPException(status_code=404, detail=f"ID为 {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="修改审核状态失败，数据库操作错误。")