buff3.0/backend-admin/app/api/admin/cards.py

"""
卡密管理接口
"""
import logging
from fastapi import APIRouter, Depends, Query, Request
from fastapi.responses import StreamingResponse
from sqlalchemy.orm import Session
from typing import Optional

from pydantic import BaseModel, Field
import secrets
import string
import csv
import io
from app.database import get_db
from app.api.deps import get_current_admin
from app.models.card import Card
from datetime import datetime
from app.utils.timezone import beijing_now

logger = logging.getLogger(__name__)

router = APIRouter()

class CardGenerateRequest(BaseModel):
    """生成卡密请求"""
    software_code: str
    days: int
    discount: float
    count: int
    is_gift: int = Field(0, ge=0, le=1, description="0-收费卡密，1-赠送卡密")
    remark: Optional[str] = Field(None, max_length=500, description="备注，可选")

class CardBatchExtractRequest(BaseModel):
    """批量提取卡密请求"""
    ids: list[int] = Field(..., min_length=1, description="卡密ID列表")

class CardIssueRequest(BaseModel):
    """发放卡密请求"""
    issue_price: float = Field(..., ge=0, description="发放价格")
    issued_to: str = Field(..., min_length=1, max_length=200, description="发放给谁（文本）")
    issue_remark: Optional[str] = Field(None, max_length=500, description="发放备注，可选")

class CardBatchIssueRequest(BaseModel):
    """批量发放卡密请求"""
    ids: list[int] = Field(..., min_length=1, description="卡密ID列表")
    issue_price: float = Field(..., ge=0, description="发放价格")
    issued_to: str = Field(..., min_length=1, max_length=200, description="发放给谁（文本）")
    issue_remark: Optional[str] = Field(None, max_length=500, description="发放备注，可选")

class CardRevokeRequest(BaseModel):
    """注销卡密请求"""
    revoked_reason: Optional[str] = Field(None, max_length=500, description="注销原因，可选")

class CardBatchRevokeRequest(BaseModel):
    """批量注销卡密请求"""
    ids: list[int] = Field(..., min_length=1, description="卡密ID列表")
    revoked_reason: Optional[str] = Field(None, max_length=500, description="注销原因，可选")

class CardBatchSpecialCloseRequest(BaseModel):
    """批量设置特殊关闭请求"""
    ids: list[int] = Field(..., min_length=1, description="卡密ID列表")
    special_close: int = Field(..., ge=0, le=1, description="特殊关闭：0-关闭，1-打开")

def generate_card_code() -> str:
    """生成卡密代码"""
    characters = string.ascii_uppercase + string.digits
    return ''.join(secrets.choice(characters) for _ in range(16))

@router.post("/generate")
async def generate_cards(
    req: CardGenerateRequest,
    request: Request,
    db: Session = Depends(get_db),
    current_admin = Depends(get_current_admin)
):
    """生成卡密"""
    try:
        remark_clean = (req.remark or "").strip() or None

        cards = []
        for _ in range(req.count):
            card_code = generate_card_code()
            # 确保卡密唯一
            while db.query(Card).filter(Card.card_code == card_code).first():
                card_code = generate_card_code()

            card = Card(
                card_code=card_code,
                software_code=req.software_code,
                days=req.days,
                discount=req.discount,
                status=0,
                is_gift=req.is_gift,
                remark=remark_clean,
                created_at=beijing_now(),
                updated_at=beijing_now()
            )
            db.add(card)
            cards.append(card)

        # 刷新以获取ID
        db.flush()

        # 创建历史记录
        from app.utils.history import create_card_history, create_operation_log
        for card in cards:
            create_card_history(db, card, "INSERT")

        # 记录操作日志
        client_ip = request.client.host if request.client else None
        gift_label = "赠送" if req.is_gift == 1 else "收费"
        create_operation_log(
            db=db,
            admin_id=current_admin.id,
            module="卡密管理",
            action="生成卡密",
            description=f"批量生成卡密：{req.count}张（{gift_label}），软件：{req.software_code}，天数：{req.days}天",
            new_data={
                "software_code": req.software_code,
                "days": req.days,
                "discount": req.discount,
                "count": req.count,
                "is_gift": req.is_gift,
                "remark": remark_clean,
                "card_codes": [c.card_code for c in cards]
            },
            ip_address=client_ip
        )

        # 一次性提交所有更改
        db.commit()

        return {
            "code": 200,
            "msg": "生成成功",
            "data": {
                "count": len(cards),
                "cards": [c.card_code for c in cards]
            }
        }

    except Exception as e:
        try:
            db.rollback()
        except Exception:
            # 回滚失败记录警告但继续处理
            pass
        logger.exception("生成卡密失败")
        return {"code": 500, "msg": "系统异常，操作未完成，请稍后重试；若持续发生请联系客服"}

@router.get("")
async def get_cards(
    page: int = Query(1, ge=1),
    page_size: int = Query(10, ge=1, le=100),
    keyword: str = Query(None, description="搜索关键词（卡密代码或备注）"),
    software_code: str = Query(None, description="软件特征码筛选"),
    status: int = Query(None, description="状态筛选：0-未激活，1-已激活，2-已过期"),
    is_gift: int = Query(None, ge=0, le=1, description="类型筛选：0-收费，1-赠送"),
    is_extracted: int = Query(None, ge=0, le=1, description="提取状态筛选：0-未提取，1-已提取"),
    is_revoked: int = Query(None, ge=0, le=1, description="注销筛选：0-未注销，1-已注销"),
    special_close: int = Query(None, ge=0, le=1, description="特殊关闭筛选：0-关闭，1-打开"),
    db: Session = Depends(get_db),
    current_admin = Depends(get_current_admin)
):
    """获取卡密列表"""
    from sqlalchemy import or_

    from app.models.software import Software
    from app.models.user import User

    offset = (page - 1) * page_size

    # 构建查询，关联软件表和用户表
    query = db.query(Card, Software.name.label('software_name'), User.username.label('username'))\
        .join(Software, Card.software_code == Software.code)\
        .outerjoin(User, Card.user_id == User.id)\
        .filter(Card.is_deleted == 0)

    # 搜索
    if keyword:
        like = f"%{keyword}%"
        query = query.filter(
            or_(Card.card_code.like(like), Card.remark.like(like))
        )

    # 筛选
    if software_code:
        query = query.filter(Card.software_code == software_code)

    if status is not None:
        query = query.filter(Card.status == status)

    if is_gift is not None:
        query = query.filter(Card.is_gift == is_gift)

    if is_extracted is not None:
        query = query.filter(Card.is_extracted == is_extracted)

    if is_revoked is not None:
        query = query.filter(Card.is_revoked == is_revoked)

    if special_close is not None:
        query = query.filter(Card.special_close == special_close)

    # 排序（同创建时间时按 id 降序，保证更新后顺序稳定）
    query = query.order_by(Card.created_at.desc(), Card.id.desc())

    cards_with_names = query.offset(offset).limit(page_size).all()
    total = query.count()

    return {
        "code": 200,
        "msg": "成功",
        "data": [
            {
                "id": c.id,
                "card_code": c.card_code,
                "software_code": c.software_code,
                "software_name": software_name,
                "days": c.days,
                "discount": float(c.discount),
                "status": c.status,
                "is_gift": c.is_gift,
                "is_extracted": c.is_extracted,
                "special_close": c.special_close,
                "issue_price": float(c.issue_price) if c.issue_price is not None else None,
                "issued_to": c.issued_to,
                "issue_remark": c.issue_remark,
                "issued_at": c.issued_at.isoformat() if c.issued_at else None,
                "issued_admin_id": c.issued_admin_id,
                "remark": c.remark,
                "is_revoked": c.is_revoked,
                "revoked_at": c.revoked_at.isoformat() if c.revoked_at else None,
                "revoked_reason": c.revoked_reason,
                "revoked_admin_id": c.revoked_admin_id,
                "user_id": c.user_id,
                "username": username,
                "activated_at": c.activated_at.isoformat() if c.activated_at else None,
                "expire_time": c.expire_time.isoformat() if c.expire_time else None,
                "created_at": c.created_at.isoformat() if c.created_at else None,
                "updated_at": c.updated_at.isoformat() if c.updated_at else None
            }
            for c, software_name, username in cards_with_names
        ],
        "total": total,
        "page": page,
        "page_size": page_size
    }

@router.put("/{card_id}/extract")
async def extract_card(
    card_id: int,
    request: Request,
    db: Session = Depends(get_db),
    current_admin = Depends(get_current_admin)
):
    """提取单个卡密（标记为已提取）"""
    from app.utils.history import create_card_history, create_operation_log

    try:
        card = db.query(Card).filter(Card.id == card_id, Card.is_deleted == 0).first()
        if not card:
            return {"code": 404, "msg": "卡密不存在"}

        card.is_extracted = 1
        card.updated_at = beijing_now()

        create_card_history(db, card, "UPDATE")
        client_ip = request.client.host if request.client else None
        create_operation_log(
            db=db,
            admin_id=current_admin.id,
            module="卡密管理",
            action="提取卡密",
            description=f"提取卡密：{card.card_code}",
            new_data={"card_id": card.id, "card_code": card.card_code, "is_extracted": 1},
            ip_address=client_ip
        )
        db.commit()

        return {
            "code": 200,
            "msg": "提取成功",
            "data": {"id": card.id, "card_code": card.card_code, "is_extracted": card.is_extracted}
        }
    except Exception:
        db.rollback()
        logger.exception("提取卡密失败")
        return {"code": 500, "msg": "系统异常，操作未完成，请稍后重试；若持续发生请联系客服"}

@router.put("/{card_id}/issue")
async def issue_card(
    card_id: int,
    req: CardIssueRequest,
    request: Request,
    db: Session = Depends(get_db),
    current_admin = Depends(get_current_admin)
):
    """发放单个卡密（记录发放信息）"""
    from app.utils.history import create_card_history, create_operation_log

    try:
        card = db.query(Card).filter(Card.id == card_id, Card.is_deleted == 0).first()
        if not card:
            return {"code": 404, "msg": "卡密不存在"}

        old_data = {
            "issue_price": float(card.issue_price) if card.issue_price is not None else None,
            "issued_to": card.issued_to,
            "issue_remark": card.issue_remark,
            "issued_at": card.issued_at.isoformat() if card.issued_at else None,
            "issued_admin_id": card.issued_admin_id,
        }

        now = beijing_now()
        card.issue_price = req.issue_price
        card.issued_to = req.issued_to.strip()
        card.issue_remark = (req.issue_remark or "").strip() or None
        card.issued_at = now
        card.issued_admin_id = current_admin.id
        card.updated_at = now

        create_card_history(db, card, "UPDATE")
        client_ip = request.client.host if request.client else None
        create_operation_log(
            db=db,
            admin_id=current_admin.id,
            module="卡密管理",
            action="发放卡密",
            description=f"发放卡密：{card.card_code}",
            old_data=old_data,
            new_data={
                "card_id": card.id,
                "card_code": card.card_code,
                "issue_price": req.issue_price,
                "issued_to": card.issued_to,
                "issue_remark": card.issue_remark,
                "issued_at": now.isoformat(),
                "issued_admin_id": current_admin.id,
            },
            ip_address=client_ip
        )
        db.commit()

        return {"code": 200, "msg": "发放成功"}
    except Exception:
        db.rollback()
        logger.exception("发放卡密失败")
        return {"code": 500, "msg": "系统异常，操作未完成，请稍后重试；若持续发生请联系客服"}

@router.post("/issue")
async def issue_cards_batch(
    req: CardBatchIssueRequest,
    request: Request,
    db: Session = Depends(get_db),
    current_admin = Depends(get_current_admin)
):
    """批量发放卡密（记录发放信息）"""
    from app.utils.history import create_card_history, create_operation_log

    try:
        cards = db.query(Card).filter(
            Card.id.in_(req.ids),
            Card.is_deleted == 0
        ).all()

        if not cards:
            return {"code": 404, "msg": "未找到可发放的卡密"}

        now = beijing_now()
        issued_to_clean = req.issued_to.strip()
        issue_remark_clean = (req.issue_remark or "").strip() or None

        card_codes = []
        for card in cards:
            card.issue_price = req.issue_price
            card.issued_to = issued_to_clean
            card.issue_remark = issue_remark_clean
            card.issued_at = now
            card.issued_admin_id = current_admin.id
            card.updated_at = now
            card_codes.append(card.card_code)
            create_card_history(db, card, "UPDATE")

        client_ip = request.client.host if request.client else None
        create_operation_log(
            db=db,
            admin_id=current_admin.id,
            module="卡密管理",
            action="批量发放卡密",
            description=f"批量发放卡密：{len(card_codes)}张",
            new_data={
                "card_ids": req.ids,
                "card_codes": card_codes,
                "issue_price": req.issue_price,
                "issued_to": issued_to_clean,
                "issue_remark": issue_remark_clean,
                "issued_at": now.isoformat(),
                "issued_admin_id": current_admin.id,
            },
            ip_address=client_ip
        )
        db.commit()

        return {"code": 200, "msg": "批量发放成功", "data": {"count": len(card_codes)}}
    except Exception:
        db.rollback()
        logger.exception("批量发放卡密失败")
        return {"code": 500, "msg": "系统异常，操作未完成，请稍后重试；若持续发生请联系客服"}

@router.post("/extract")
async def extract_cards_batch(
    req: CardBatchExtractRequest,
    request: Request,
    db: Session = Depends(get_db),
    current_admin = Depends(get_current_admin)
):
    """批量提取卡密（标记为已提取）"""
    from app.utils.history import create_card_history, create_operation_log

    try:
        cards = db.query(Card).filter(
            Card.id.in_(req.ids),
            Card.is_deleted == 0
        ).all()

        if not cards:
            return {"code": 404, "msg": "未找到可提取的卡密"}

        now = beijing_now()
        card_codes = []
        for card in cards:
            card.is_extracted = 1
            card.updated_at = now
            card_codes.append(card.card_code)
            create_card_history(db, card, "UPDATE")

        client_ip = request.client.host if request.client else None
        create_operation_log(
            db=db,
            admin_id=current_admin.id,
            module="卡密管理",
            action="批量提取卡密",
            description=f"批量提取卡密：{len(card_codes)}张",
            new_data={"card_ids": req.ids, "card_codes": card_codes, "is_extracted": 1},
            ip_address=client_ip
        )
        db.commit()

        return {
            "code": 200,
            "msg": "批量提取成功",
            "data": {"count": len(card_codes), "cards": card_codes}
        }
    except Exception:
        db.rollback()
        logger.exception("批量提取卡密失败")
        return {"code": 500, "msg": "系统异常，操作未完成，请稍后重试；若持续发生请联系客服"}

@router.post("/special-close")
async def batch_set_special_close(
    req: CardBatchSpecialCloseRequest,
    request: Request,
    db: Session = Depends(get_db),
    current_admin = Depends(get_current_admin)
):
    """批量设置卡密特殊关闭标识（0-关闭，1-打开）"""
    from app.utils.history import create_card_history, create_operation_log

    try:
        cards = db.query(Card).filter(
            Card.id.in_(req.ids),
            Card.is_deleted == 0
        ).all()

        if not cards:
            return {"code": 404, "msg": "未找到可操作的卡密"}

        now = beijing_now()
        card_codes = []
        for card in cards:
            card.special_close = req.special_close
            card.updated_at = now
            card_codes.append(card.card_code)
            create_card_history(db, card, "UPDATE")

        action_label = "打开" if req.special_close == 1 else "关闭"
        client_ip = request.client.host if request.client else None
        create_operation_log(
            db=db,
            admin_id=current_admin.id,
            module="卡密管理",
            action=f"批量特殊关闭{action_label}",
            description=f"批量特殊关闭{action_label}：{len(card_codes)}张",
            new_data={
                "card_ids": req.ids,
                "card_codes": card_codes,
                "special_close": req.special_close,
            },
            ip_address=client_ip
        )
        db.commit()

        return {
            "code": 200,
            "msg": f"批量特殊关闭{action_label}成功",
            "data": {"count": len(card_codes), "special_close": req.special_close}
        }
    except Exception:
        db.rollback()
        logger.exception("批量设置特殊关闭失败")
        return {"code": 500, "msg": "系统异常，操作未完成，请稍后重试；若持续发生请联系客服"}

@router.get("/{card_id}")
async def get_card_detail(
    card_id: int,
    db: Session = Depends(get_db),
    current_admin = Depends(get_current_admin)
):
    """获取卡密详情"""
    from app.models.user import User
    
    card = db.query(Card).filter(Card.id == card_id).first()
    if not card:
        return {"code": 404, "msg": "卡密不存在"}
    
    user_info = None
    if card.user_id:
        user = db.query(User).filter(User.id == card.user_id).first()
        if user:
            user_info = {
                "id": user.id,
                "username": user.username,
                "email": user.email,
                "machine_code": user.machine_code,
            }
    
    return {
        "code": 200,
        "msg": "成功",
        "data": {
            "id": card.id,
            "card_code": card.card_code,
            "software_code": card.software_code,
            "days": card.days,
            "discount": float(card.discount),
            "status": card.status,
            "is_gift": card.is_gift,
            "is_extracted": card.is_extracted,
            "special_close": card.special_close,
            "issue_price": float(card.issue_price) if card.issue_price is not None else None,
            "issued_to": card.issued_to,
            "issue_remark": card.issue_remark,
            "issued_at": card.issued_at.isoformat() if card.issued_at else None,
            "issued_admin_id": card.issued_admin_id,
            "remark": card.remark,
            "is_revoked": card.is_revoked,
            "revoked_at": card.revoked_at.isoformat() if card.revoked_at else None,
            "revoked_reason": card.revoked_reason,
            "revoked_admin_id": card.revoked_admin_id,
            "user_id": card.user_id,
            "user_info": user_info,
            "activated_at": card.activated_at.isoformat() if card.activated_at else None,
            "expire_time": card.expire_time.isoformat() if card.expire_time else None,
            "created_at": card.created_at.isoformat() if card.created_at else None,
            "updated_at": card.updated_at.isoformat() if card.updated_at else None
        }
    }

@router.put("/{card_id}/revoke")
async def revoke_card(
    card_id: int,
    req: CardRevokeRequest,
    request: Request,
    db: Session = Depends(get_db),
    current_admin = Depends(get_current_admin),
):
    """注销单个卡密（标记为不可再使用）"""
    from app.utils.history import create_card_history, create_operation_log

    try:
        card = db.query(Card).filter(Card.id == card_id, Card.is_deleted == 0).first()
        if not card:
            return {"code": 404, "msg": "卡密不存在"}
        if card.is_revoked == 1:
            return {"code": 400, "msg": "该卡密已注销"}
        if card.status != 0:
            return {"code": 400, "msg": "仅支持注销未激活的卡密"}

        old_data = {
            "is_revoked": card.is_revoked,
            "revoked_at": card.revoked_at.isoformat() if card.revoked_at else None,
            "revoked_reason": card.revoked_reason,
            "revoked_admin_id": card.revoked_admin_id,
        }

        now = beijing_now()
        card.is_revoked = 1
        card.is_deleted = 1
        card.revoked_at = now
        card.revoked_reason = (req.revoked_reason or "").strip() or None
        card.revoked_admin_id = current_admin.id
        card.updated_at = now

        create_card_history(db, card, "UPDATE")
        client_ip = request.client.host if request.client else None
        create_operation_log(
            db=db,
            admin_id=current_admin.id,
            module="卡密管理",
            action="注销卡密",
            description=f"注销卡密：{card.card_code}",
            old_data=old_data,
            new_data={
                "card_id": card.id,
                "card_code": card.card_code,
                "is_revoked": 1,
                "is_deleted": 1,
                "revoked_at": now.isoformat(),
                "revoked_reason": card.revoked_reason,
                "revoked_admin_id": current_admin.id,
            },
            ip_address=client_ip,
        )
        db.commit()
        return {"code": 200, "msg": "注销成功"}
    except Exception:
        db.rollback()
        logger.exception("注销卡密失败")
        return {"code": 500, "msg": "系统异常，操作未完成，请稍后重试；若持续发生请联系客服"}


@router.post("/revoke")
async def revoke_cards_batch(
    req: CardBatchRevokeRequest,
    request: Request,
    db: Session = Depends(get_db),
    current_admin = Depends(get_current_admin),
):
    """批量注销卡密（标记为不可再使用）"""
    from app.utils.history import create_card_history, create_operation_log

    try:
        cards = db.query(Card).filter(
            Card.id.in_(req.ids),
            Card.is_deleted == 0,
        ).all()
        if not cards:
            return {"code": 404, "msg": "未找到可注销的卡密"}

        now = beijing_now()
        reason_clean = (req.revoked_reason or "").strip() or None

        revoked_codes: list[str] = []
        skipped: list[dict] = []
        for card in cards:
            if card.is_revoked == 1:
                skipped.append({"id": card.id, "card_code": card.card_code, "reason": "已注销"})
                continue
            if card.status != 0:
                skipped.append({"id": card.id, "card_code": card.card_code, "reason": "非未激活"})
                continue
            card.is_revoked = 1
            card.is_deleted = 1
            card.revoked_at = now
            card.revoked_reason = reason_clean
            card.revoked_admin_id = current_admin.id
            card.updated_at = now
            revoked_codes.append(card.card_code)
            create_card_history(db, card, "UPDATE")

        if not revoked_codes:
            return {"code": 400, "msg": "没有可注销的卡密", "data": {"skipped": skipped}}

        client_ip = request.client.host if request.client else None
        create_operation_log(
            db=db,
            admin_id=current_admin.id,
            module="卡密管理",
            action="批量注销卡密",
            description=f"批量注销卡密：{len(revoked_codes)}张",
            new_data={
                "card_ids": req.ids,
                "revoked_card_codes": revoked_codes,
                "revoked_at": now.isoformat(),
                "revoked_reason": reason_clean,
                "revoked_admin_id": current_admin.id,
                "is_deleted": 1,
                "skipped": skipped,
            },
            ip_address=client_ip,
        )
        db.commit()
        return {"code": 200, "msg": "批量注销成功", "data": {"count": len(revoked_codes), "skipped": skipped}}
    except Exception:
        db.rollback()
        logger.exception("批量注销卡密失败")
        return {"code": 500, "msg": "系统异常，操作未完成，请稍后重试；若持续发生请联系客服"}

@router.get("/{card_id}/activate")
async def get_card_activate_info(
    card_id: int,
    db: Session = Depends(get_db),
    current_admin = Depends(get_current_admin)
):
    """获取卡密激活信息"""
    card = db.query(Card).filter(Card.id == card_id).first()
    if not card:
        return {"code": 404, "msg": "卡密不存在"}
    
    return {
        "code": 200,
        "msg": "成功",
        "data": {
            "card_code": card.card_code,
            "user_id": card.user_id,
            "activated_at": card.activated_at.isoformat() if card.activated_at else None,
                "expire_time": card.expire_time.isoformat() if card.expire_time else None,
                "issue_price": float(card.issue_price) if card.issue_price is not None else None,
                "issued_to": card.issued_to,
                "issue_remark": card.issue_remark,
                "issued_at": card.issued_at.isoformat() if card.issued_at else None,
                "issued_admin_id": card.issued_admin_id,
        }
    }

@router.get("/export/excel")
async def export_cards_excel(
    request: Request,
    keyword: str = Query(None, description="搜索关键词"),
    software_code: str = Query(None, description="软件特征码筛选"),
    status: int = Query(None, description="状态筛选"),
    is_gift: int = Query(None, ge=0, le=1, description="类型筛选：0-收费，1-赠送"),
    is_extracted: int = Query(None, ge=0, le=1, description="提取状态筛选：0-未提取，1-已提取"),
    is_revoked: int = Query(None, ge=0, le=1, description="注销筛选：0-未注销，1-已注销"),
    special_close: int = Query(None, ge=0, le=1, description="特殊关闭筛选：0-关闭，1-打开"),
    db: Session = Depends(get_db),
    current_admin = Depends(get_current_admin)
):
    """导出卡密为Excel"""
    from sqlalchemy import or_

    from app.models.software import Software
    from app.models.user import User

    # 应用相同的筛选条件构建查询，关联软件表和用户表
    query = db.query(Card, Software.name.label('software_name'), User.username.label('username'))\
        .join(Software, Card.software_code == Software.code)\
        .outerjoin(User, Card.user_id == User.id)\
        .filter(Card.is_deleted == 0)

    # 搜索
    if keyword:
        like = f"%{keyword}%"
        query = query.filter(
            or_(Card.card_code.like(like), Card.remark.like(like))
        )

    # 筛选
    if software_code:
        query = query.filter(Card.software_code == software_code)

    if status is not None:
        query = query.filter(Card.status == status)

    if is_gift is not None:
        query = query.filter(Card.is_gift == is_gift)

    if is_extracted is not None:
        query = query.filter(Card.is_extracted == is_extracted)

    if is_revoked is not None:
        query = query.filter(Card.is_revoked == is_revoked)

    if special_close is not None:
        query = query.filter(Card.special_close == special_close)

    cards_with_names = query.order_by(Card.created_at.desc(), Card.id.desc()).all()

    # 记录操作日志
    from app.utils.history import create_operation_log
    client_ip = request.client.host if request.client else None
    create_operation_log(
        db=db,
        admin_id=current_admin.id,
        module="卡密管理",
        action="导出卡密Excel",
        description=f"导出卡密数据为Excel，共导出{len(cards_with_names)}条卡密记录",
        new_data={
            "export_count": len(cards_with_names),
            "export_format": "Excel",
            "filters": {
                "keyword": keyword,
                "software_code": software_code,
                "status": status,
                "is_gift": is_gift,
                "is_extracted": is_extracted,
                "is_revoked": is_revoked,
                "special_close": special_close,
            }
        },
        ip_address=client_ip
    )
    db.commit()

    # 生成CSV格式（临时方案，后续可以升级为真正的Excel格式）
    output = io.StringIO()
    writer = csv.writer(output)
    writer.writerow([
        'ID', '卡密代码', '软件特征码', '软件名称', '天数', '折扣', '状态', '卡密类型', '提取状态', '特殊关闭',
        '注销状态', '注销时间', '注销原因', '注销管理员ID',
        '发放价格', '发放对象', '发放备注', '发放时间', '发放管理员ID',
        '备注',
        '激活用户ID', '用户名称', '激活时间', '到期时间', '创建时间', '更新时间'
    ])

    status_map = {0: '未激活', 1: '已激活', 2: '已过期'}
    gift_map = {0: '收费', 1: '赠送'}
    extracted_map = {0: '未提取', 1: '已提取'}
    special_close_map = {0: '关闭', 1: '打开'}
    revoked_map = {0: '未注销', 1: '已注销'}
    for card, software_name, username in cards_with_names:
        writer.writerow([
            card.id,
            card.card_code,
            card.software_code,
            software_name,
            card.days,
            float(card.discount),
            status_map.get(card.status, '未知'),
            gift_map.get(card.is_gift, '未知'),
            extracted_map.get(card.is_extracted, '未知'),
            special_close_map.get(getattr(card, "special_close", 0) or 0, '未知'),
            revoked_map.get(getattr(card, "is_revoked", 0), '未知'),
            card.revoked_at.isoformat() if getattr(card, "revoked_at", None) else '',
            (card.revoked_reason or '') if getattr(card, "revoked_reason", None) is not None else '',
            card.revoked_admin_id or '',
            float(card.issue_price) if card.issue_price is not None else '',
            card.issued_to or '',
            card.issue_remark or '',
            card.issued_at.isoformat() if card.issued_at else '',
            card.issued_admin_id or '',
            card.remark or '',
            card.user_id or '',
            username or '',
            card.activated_at.isoformat() if card.activated_at else '',
            card.expire_time.isoformat() if card.expire_time else '',
            card.created_at.isoformat() if card.created_at else '',
            card.updated_at.isoformat() if card.updated_at else ''
        ])

    output.seek(0)
    return StreamingResponse(
        iter([output.getvalue()]),
        media_type="text/csv",
        headers={"Content-Disposition": f"attachment; filename=cards_{beijing_now().strftime('%Y%m%d_%H%M%S')}.csv"}
    )