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.utils.scheme import ( CardDetailResponse, CardListDetailResponse, CardType, SortBy, 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), 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. 删除物理文件 (改为 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, 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="修改审核状态失败，数据库操作错误。")