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 ) 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="卡牌的名称"), 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, card_type) VALUES (%s, %s)" cursor.execute(query, (card_name, 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", response_model=List[CardListDetailResponse], summary="获取卡牌列表（支持筛选和排序）") def list_cards_detailed( card_id: Optional[int] = Query(None, description="筛选条件：卡牌ID (精确匹配)"), card_name: Optional[str] = Query(None, description="筛选条件：卡牌名称 (模糊匹配)"), card_type: Optional[CardType] = Query(None, description="筛选条件：卡牌类型"), is_edited: Optional[bool] = Query(None, description="筛选：是否已编辑"), min_detection_score: Optional[float] = Query(None, ge=-100, le=10, description="筛选：最小检测分"), max_detection_score: Optional[float] = Query(None, ge=0, le=10, description="筛选：最大检测分"), min_modified_score: Optional[float] = Query(None, ge=-100, le=10, description="筛选：最小修改分"), max_modified_score: Optional[float] = Query(None, ge=0, le=10, description="筛选：最大修改分"), created_start: Optional[date] = Query(None, description="筛选：创建日期起始 (含), 输入如:2025-10-01"), created_end: Optional[date] = Query(None, description="筛选：创建日期结束 (含)"), updated_start: Optional[date] = Query(None, description="筛选：更新日期起始 (含)"), updated_end: Optional[date] = Query(None, description="筛选：更新日期结束 (含)"), sort_by: SortBy = Query(SortBy.updated_at, description="排序字段"), sort_order: SortOrder = Query(SortOrder.desc, description="排序顺序"), skip: int = Query(0, ge=0, description="分页：跳过的记录数"), page_num: int = Query(None, ge=1, description="分页：第n页: (page_num-1) * limit"), limit: int = Query(100, ge=1, le=1000, description="分页：每页的记录数"), db_conn: PooledMySQLConnection = db_dependency ): """获取卡牌的基础信息列表，支持按名称、类型筛选，以及多字段排序和分页。""" if page_num is not None: skip = (page_num-1) * limit try: cards_with_images = crud_card.get_card_list_with_images( db_conn, card_id, card_name, card_type, is_edited, 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 ) card_list = [CardListDetailResponse.model_validate(c) for c in cards_with_images] logger.info(f"查询到 {len(card_list)} 条记录") return card_list 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="删除卡牌失败。")