from datetime import datetime from typing import List, Optional from redis.asyncio import Redis from sqlalchemy import select, case from sqlalchemy.ext.asyncio import AsyncSession from app.utils.search import apply_keyword_search_stmt from app.utils.pagination import paginate from app.core.biz_exception import NotFoundError, PermissionDeniedError, BizLogicError from app.core.logger import logger from app.models.user import VasUser from app.models.order import VasOrder from app.models.vas_task import VasTask from app.models.payment import VasPayment from app.models.ticket import VasTicket from app.models.ticket_message import VasTicketMessage from app.schemas.ticket import VasTicketCreate from app.services.notification_service import NotificationService from app.services.webhook_service import WebhookService class TicketService: @staticmethod async def create( db: AsyncSession, data: VasTicketCreate, current_user: VasUser, redis_client: Redis ): # 1. 验证订单存在性及访问权限 order_res = await db.execute( select(VasOrder).where(VasOrder.id == data.order_id) ) order = order_res.scalar_one_or_none() if not order: raise NotFoundError("Associated order not found") if current_user.role != "admin" and str(order.user_id) != str(current_user.id): raise PermissionDeniedError("Permission denied: You do not have permission to create a ticket for this order") # 2. 一个订单只能有一个处于打开状态的工单 (pending, info_required) existing_ticket = (await db.execute( select(VasTicket).where( VasTicket.order_id == data.order_id, VasTicket.status.in_(["pending", "info_required"]) ) )).scalars().first() if existing_ticket: raise BizLogicError(f"An open ticket (#{existing_ticket.id}) already exists for this order. Duplicate submissions are not allowed.") ticket = VasTicket( **data.dict(), user_id=current_user.id, status="pending", created_at=datetime.utcnow(), updated_at=datetime.utcnow(), ) db.add(ticket) await db.commit() await db.refresh(ticket) formatted_time = ticket.created_at.strftime('%Y-%m-%d %H:%M') + " (UTC)" await NotificationService.post_message( db=db, channel="email", payload={ "template_id": "ticket_created", "receiver": current_user.email, "payload": { "app_name": "STRATA", "username": current_user.email, "ticket_id": ticket.id, "ticket_type": ticket.type, "ticket_url": "https://text.skin/dashboard", "created_at": formatted_time } }, ) return ticket @staticmethod async def update_status( db: AsyncSession, ticket_id: int, status: str, comment: str = "", admin_id: Optional[str] = None, user: Optional[VasUser] = None, ) -> VasTicket: result = await db.execute( select(VasTicket) .where(VasTicket.id == ticket_id) .with_for_update() ) ticket = result.scalar_one_or_none() if not ticket: raise NotFoundError("Ticket not exist") effective_admin_id = admin_id or (str(user.id) if user else "system") ticket.status = status ticket.admin_comment = comment ticket.updated_at = datetime.utcnow() db.add( VasTicketMessage( ticket_id=ticket.id, sender_type="admin", sender_id=str(effective_admin_id), content=comment, created_at=datetime.utcnow(), ) ) if status == "resolved": await TicketService._handle_resolution(db, ticket) elif status == "rejected": await TicketService._handle_rejection(db, ticket) await db.commit() await db.refresh(ticket) # 标记解决时,自动给工单提出者发送提醒邮件 if status == "resolved": try: user_stmt = select(VasUser).where(VasUser.id == ticket.user_id) ticket_user = (await db.execute(user_stmt)).scalar_one_or_none() if ticket_user and ticket_user.email: formatted_time = ticket.updated_at.strftime('%Y-%m-%d %H:%M') + " (UTC)" await NotificationService.post_message( db=db, channel="email", payload={ "template_id": "ticket_resolved", "receiver": ticket_user.email, "payload": { "app_name": "STRATA", "username": ticket_user.nickname or ticket_user.email, "ticket_id": ticket.id, "order_id": ticket.order_id or "N/A", "ticket_type": ticket.type, "status": "Resolved", "resolution_comment": comment or "Your ticket has been marked as resolved by customer service.", "ticket_url": "https://text.skin/dashboard", "resolved_at": formatted_time, }, }, ) except Exception as e: logger.error(f"Failed to post ticket resolved notification: {e}") return ticket # ========================= # 工单解决逻辑 # ========================= @staticmethod async def _handle_resolution( db: AsyncSession, ticket: VasTicket, ) -> None: if not ticket.order_id: return result = await db.execute( select(VasOrder) .where(VasOrder.id == ticket.order_id) .with_for_update() ) order = result.scalar_one_or_none() if not order: return # ---------- 退款 ---------- if ticket.type == "refund": order.status = "closed" pay_res = await db.execute( select(VasPayment).where( VasPayment.order_id == order.id, VasPayment.status.in_(["succeeded", "late_paid"]), ) ) payment = pay_res.scalar_one_or_none() if payment: payment.status = "refunded" payment.refunded_at = datetime.utcnow() payment.refund_reason = ticket.reason task_res = await db.execute( select(VasTask).where( VasTask.order_id == order.id, VasTask.status.in_(["pending", "grabbed", "running", "pause", "completed"]), ) ) for task in task_res.scalars().all(): task.status = "cancelled" # ========================= # 工单拒绝逻辑 # ========================= @staticmethod async def _handle_rejection( db: AsyncSession, ticket: VasTicket, ) -> None: if not ticket.order_id: return result = await db.execute( select(VasOrder) .where(VasOrder.id == ticket.order_id) .with_for_update() ) order = result.scalar_one_or_none() if not order: return # if ticket.type == "refund" and order.status == "refund_pending": # order.status = "paid" @staticmethod async def add_message( db: AsyncSession, ticket_id: int, sender_type: str, # "user" | "admin" | "system" sender_id: Optional[str] = None, content: str = "", attachments: Optional[dict] = None, ): ticket = await db.get(VasTicket, ticket_id) if not ticket: raise NotFoundError("Ticket not exist") if sender_type != "admin" and sender_id and str(ticket.user_id) != str(sender_id): raise PermissionDeniedError("Permission denied: You do not have access to this ticket") message = VasTicketMessage( ticket_id=ticket_id, sender_type=sender_type, sender_id=sender_id, content=content, attachments=attachments, created_at=datetime.utcnow(), ) db.add(message) ticket.updated_at = datetime.utcnow() await db.commit() await db.refresh(message) return message @staticmethod async def list_messages( db: AsyncSession, ticket_id: int, page: int = 1, size: int = 20, current_user: Optional[VasUser] = None, ): # 校验 ticket 是否存在及权限 ticket = await db.get(VasTicket, ticket_id) if not ticket: raise NotFoundError("Ticket not exist") if current_user and current_user.role != "admin" and str(ticket.user_id) != str(current_user.id): raise PermissionDeniedError("Permission denied: You do not have access to this ticket") stmt = ( select(VasTicketMessage) .where(VasTicketMessage.ticket_id == ticket_id) .order_by(VasTicketMessage.created_at.desc()) ) return await paginate(db, stmt, page, size) @staticmethod async def list_by_user( db: AsyncSession, user_id: str, page: int = 1, size: int = 20, keyword: Optional[str] = None, ): stmt = select(VasTicket).where(VasTicket.user_id == user_id) stmt = apply_keyword_search_stmt( stmt=stmt, model=VasTicket, keyword=keyword, fields=["order_id", "user_id", "reason", "admin_comment"], ) stmt = stmt.order_by(VasTicket.id.desc()) return await paginate(db, stmt, page, size) @staticmethod async def list_all( db: AsyncSession, page: int = 1, size: int = 20, keyword: Optional[str] = None, ): stmt = select(VasTicket) # 关键词搜索 stmt = apply_keyword_search_stmt( stmt=stmt, model=VasTicket, keyword=keyword, fields=["order_id", "user_id", "reason", "admin_comment"], ) # 排序:未解决优先 ('pending','info_required') -> 0, 已解决 ('resolved','rejected') -> 1 stmt = stmt.order_by( case( (VasTicket.status.in_(["pending", "info_required"]), 0), else_=1 ), VasTicket.id.desc() ) return await paginate(db, stmt, page, size)