CardScoreDataServer/app/api/cards.py

import os
from datetime import datetime
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, CardImageResponse, CardListDetailResponse

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="卡牌的名称"),
        db_conn: PooledMySQLConnection = db_dependency
):
    """创建一个新的卡牌实体，此时它不关联任何图片。"""
    cursor = None
    try:
        cursor = db_conn.cursor()
        query = f"INSERT INTO {settings.DB_CARD_TABLE_NAME} (card_name) VALUES (%s)"
        cursor.execute(query, (card_name,))
        db_conn.commit()
        new_card_id = cursor.lastrowid
        logger.info(f"新卡牌已创建, ID: {new_card_id}")

        # 返回刚创建的空卡牌信息
        return CardDetailResponse(
            card_id=new_card_id,
            card_name=card_name,
            created_at=datetime.now(),  # 模拟值
            updated_at=datetime.now(),  # 模拟值
            images=[]
        )
    except Exception as e:
        db_conn.rollback()
        logger.error(f"创建卡牌失败: {e}")
        raise HTTPException(status_code=500, detail="数据库插入失败。")
    finally:
        if cursor:
            cursor.close()


@router.get("/query/{card_id}", response_model=CardDetailResponse, summary="获取指定卡牌的详细信息")
def get_card_details(card_id: int, db_conn: PooledMySQLConnection = db_dependency):
    """获取卡牌元数据以及所有与之关联的图片信息。"""
    cursor = None
    try:
        cursor = db_conn.cursor(dictionary=True)  # 使用字典游标方便映射

        # 1. 获取卡牌信息
        query_card = f"SELECT * FROM {settings.DB_CARD_TABLE_NAME} WHERE card_id = %s"
        cursor.execute(query_card, (card_id,))
        card_data = cursor.fetchone()
        if not card_data:
            raise HTTPException(status_code=404, detail=f"ID为 {card_id} 的卡牌未找到。")

        # 2. 获取所有关联的图片ID
        image_ids = [
            card_data['front_face_id'], card_data['back_face_id'],
            card_data['front_edge_id'], card_data['back_edge_id']
        ]
        # 过滤掉 NULL 值
        valid_image_ids = [img_id for img_id in image_ids if img_id is not None]

        images = []
        if valid_image_ids:
            # 使用 IN 子句一次性查询所有图片
            format_strings = ','.join(['%s'] * len(valid_image_ids))
            query_images = f"SELECT * FROM {settings.DB_IMAGE_TABLE_NAME} WHERE image_id IN ({format_strings})"
            cursor.execute(query_images, tuple(valid_image_ids))
            image_records = cursor.fetchall()
            images = [CardImageResponse.model_validate(row) for row in image_records]

        # 组合成最终响应
        card_response = CardDetailResponse.model_validate(card_data)
        card_response.images = images
        return card_response

    except Exception as e:
        logger.error(f"查询卡牌详情失败 ({card_id}): {e}")
        if isinstance(e, HTTPException): raise e
        raise HTTPException(status_code=500, detail="数据库查询失败。")
    finally:
        if cursor:
            cursor.close()


@router.get("/card_list", response_model=List[CardListDetailResponse], summary="获取卡牌列表")
def list_cards_detailed(
        start_id: Optional[int] = Query(None, description="筛选条件：起始 card_id"),
        end_id: Optional[int] = Query(None, description="筛选条件：结束 card_id"),
        skip: int = Query(0, ge=0, description="分页：跳过的记录数"),
        limit: int = Query(100, ge=1, le=1000, description="分页：每页的记录数"),
        db_conn: PooledMySQLConnection = db_dependency
):
    """
    获取卡牌的详细列表，支持按 card_id 范围筛选，并返回每张卡牌关联的图片ID和名称。
    """
    cursor = None
    try:
        cursor = db_conn.cursor(dictionary=True)

        # 基础查询语句，使用 LEFT JOIN 连接四次 images 表
        # 每次连接都用一个别名 (img_ff, img_bf, ...) 来区分
        base_query = f"""
            SELECT
                c.card_id,
                c.card_name,
                c.created_at,
                c.updated_at,
                img_ff.image_id AS front_face_id,
                img_ff.image_name AS front_face_name,
                img_bf.image_id AS back_face_id,
                img_bf.image_name AS back_face_name,
                img_fe.image_id AS front_edge_id,
                img_fe.image_name AS front_edge_name,
                img_be.image_id AS back_edge_id,
                img_be.image_name AS back_edge_name
            FROM
                {settings.DB_CARD_TABLE_NAME} AS c
            LEFT JOIN {settings.DB_IMAGE_TABLE_NAME} AS img_ff ON c.front_face_id = img_ff.image_id
            LEFT JOIN {settings.DB_IMAGE_TABLE_NAME} AS img_bf ON c.back_face_id = img_bf.image_id
            LEFT JOIN {settings.DB_IMAGE_TABLE_NAME} AS img_fe ON c.front_edge_id = img_fe.image_id
            LEFT JOIN {settings.DB_IMAGE_TABLE_NAME} AS img_be ON c.back_edge_id = img_be.image_id
        """

        # 动态构建 WHERE 条件
        conditions = []
        params = []
        if start_id is not None:
            conditions.append("c.card_id >= %s")
            params.append(start_id)
        if end_id is not None:
            conditions.append("c.card_id <= %s")
            params.append(end_id)

        if conditions:
            base_query += " WHERE " + " AND ".join(conditions)

        # 添加排序和分页
        base_query += " ORDER BY c.card_id DESC LIMIT %s OFFSET %s"
        params.extend([limit, skip])

        cursor.execute(base_query, tuple(params))
        sql_results = cursor.fetchall()

        # 手动将扁平的SQL结果映射到嵌套的Pydantic模型
        response_list = []
        for row in sql_results:
            card_data = {
                "card_id": row["card_id"],
                "card_name": row["card_name"],
                "created_at": row["created_at"],
                "updated_at": row["updated_at"],
            }
            # 检查并组装 front_face 数据
            if row["front_face_id"]:
                card_data["front_face"] = {"image_id": row["front_face_id"], "image_name": row["front_face_name"]}
            # 检查并组装 back_face 数据
            if row["back_face_id"]:
                card_data["back_face"] = {"image_id": row["back_face_id"], "image_name": row["back_face_name"]}
            # 检查并组装 front_edge 数据
            if row["front_edge_id"]:
                card_data["front_edge"] = {"image_id": row["front_edge_id"], "image_name": row["front_edge_name"]}
            # 检查并组装 back_edge 数据
            if row["back_edge_id"]:
                card_data["back_edge"] = {"image_id": row["back_edge_id"], "image_name": row["back_edge_name"]}

            response_list.append(CardListDetailResponse.model_validate(card_data))

        return response_list

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


@router.delete("/delete/{card_id}", status_code=200, summary="删除卡牌及其所有关联图片")
def delete_card(card_id: int, db_conn: PooledMySQLConnection = db_dependency):
    """
    删除一张卡牌。由于外键约束ON DELETE SET NULL，仅删除卡牌记录。
    如果需要同时删除图片，需要先查询图片并手动删除。
    我们将实现级联删除图片文件。
    """
    cursor = None
    try:
        cursor = db_conn.cursor()

        # 1. 查询所有关联图片的路径
        query_paths = f"""
            SELECT i.image_path
            FROM {settings.DB_CARD_TABLE_NAME} c
            JOIN {settings.DB_IMAGE_TABLE_NAME} i ON i.image_id IN 
                (c.front_face_id, c.back_face_id, c.front_edge_id, c.back_edge_id)
            WHERE c.card_id = %s
        """
        cursor.execute(query_paths, (card_id,))
        image_paths = [row[0] for row in cursor.fetchall()]

        # 2. 删除卡牌记录 (这将触发删除图片记录，因为我们下面的逻辑会删除图片)
        # 注意: 如果直接删除 card, 外键设为 SET NULL, 图片记录不会被删。所以我们先删图片，再删card

        # 3. 删除图片记录
        if image_paths:
            format_strings = ','.join(['%s'] * len(image_paths))
            query_delete_images = f"DELETE FROM {settings.DB_IMAGE_TABLE_NAME} WHERE image_path IN ({format_strings})"
            cursor.execute(query_delete_images, tuple(image_paths))

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

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

        # 5. 删除物理文件
        for path in image_paths:
            if os.path.exists(path):
                os.remove(path)
                logger.info(f"图片文件已删除: {path}")

        db_conn.commit()
        logger.info(f"ID {card_id} 的卡牌和关联文件已成功删除。")
        return {"message": f"成功删除卡牌 ID {card_id} 及其所有关联数据"}
    except Exception as e:
        db_conn.rollback()
        logger.error(f"删除卡牌失败 ({card_id}): {e}")
        if isinstance(e, HTTPException): raise e
        raise HTTPException(status_code=500, detail="删除卡牌失败。")
    finally:
        if cursor:
            cursor.close()