from datetime import datetime
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/{id}", response_model=CardDetailResponse, summary="获取指定卡牌的详细信息")
def get_card_details(id: int, db_conn: PooledMySQLConnection = db_dependency):
    """获取卡牌元数据以及所有与之关联的图片信息。分数是预先计算好的。"""
    # REFACTORED: Use CRUD function
    card_data = crud_card.get_card_with_details(db_conn, id)
    if not card_data:
        raise HTTPException(status_code=404, detail=f"ID为 {id} 的卡牌未找到。")
    return CardDetailResponse.model_validate(card_data)


@router.get("/query_next/{id}", response_model=CardDetailResponse, summary="获取指定卡牌id的下一个卡的详细信息")
def get_next_card_details(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, (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: {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="筛选条件：卡牌类型"),
        sort_by: SortBy = Query(SortBy.updated_at, description="排序字段"),
        sort_order: SortOrder = Query(SortOrder.desc, description="排序顺序"),
        skip: int = Query(0, ge=0, description="分页：跳过的记录数"),
        limit: int = Query(100, ge=1, le=1000, description="分页：每页的记录数"),
        db_conn: PooledMySQLConnection = db_dependency
):
    """获取卡牌的基础信息列表，支持按名称、类型筛选，以及多字段排序和分页。"""
    try:
        cards_with_images = crud_card.get_card_list_with_images(
            db_conn, card_id, card_name, card_type, sort_by, sort_order, skip, limit
        )
        card_list = [CardListDetailResponse.model_validate(c) for c in cards_with_images]
        logger.info(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="删除卡牌失败。")