# app/services/vas_task_service.py from datetime import datetime, date, timedelta from typing import List, Optional from sqlalchemy.ext.asyncio import AsyncSession from sqlalchemy import func, text, select, or_, and_, cast, String from redis.asyncio import Redis from app.core.logger import logger from app.utils.search import apply_keyword_search_stmt from app.utils.pagination import paginate from app.core.biz_exception import NotFoundError, BizLogicError from app.models.vas_task import VasTask from app.models.order import VasOrder from app.schemas.vas_task import VasTaskCreate, VasTaskUpdate from app.services.task_handlers import task_processor class VasTaskService: @staticmethod async def create(db: AsyncSession, data: VasTaskCreate) -> VasTask: rec = VasTask( **data.dict(), status="pending", created_at=datetime.utcnow(), ) db.add(rec) await db.commit() await db.refresh(rec) logger.info(f"📋 [VAS_TASK] Created task: id={rec.id} order_id={rec.order_id} routing_key={rec.routing_key}") return rec @staticmethod async def pop_vas_task(db: AsyncSession, routing_key: str, cooldown_seconds: int = 60) -> VasTask: stmt = ( select(VasTask) .where(VasTask.routing_key == routing_key) .where(VasTask.status == 'pending') .where( or_( VasTask.attempt_count == 0, and_( VasTask.attempt_count > 0, VasTask.updated_at < (func.utc_timestamp() - text(f"INTERVAL {cooldown_seconds} SECOND")) ) ) ) .order_by(VasTask.priority.desc(), VasTask.id.asc()) .limit(1) .with_for_update(skip_locked=True) ) result = await db.execute(stmt) task = result.scalar_one_or_none() if not task: raise NotFoundError(message="Task not found") task.status = 'running' task.attempt_count += 1 task.updated_at = func.utc_timestamp() await db.commit() await db.refresh(task) logger.info(f"🏃 [VAS_TASK] Task popped: id={task.id} routing_key={routing_key} attempt={task.attempt_count}") return task @staticmethod async def get_task_by_order_id( db: AsyncSession, order_id: str, ) -> List[VasTask]: stmt = ( select( VasTask, VasOrder.product_id.label("order_product_id"), VasOrder.product_name.label("order_product_name"), VasOrder.user_name.label("order_user_name"), VasOrder.status.label("order_status"), VasOrder.final_amount.label("order_final_amount"), VasOrder.base_currency.label("order_base_currency"), VasOrder.created_at.label("order_created_at"), ) .outerjoin(VasOrder, VasTask.order_id == VasOrder.id) .where(VasTask.order_id == order_id) .order_by(VasTask.created_at.asc(), VasTask.id.asc()) ) rows = (await db.execute(stmt)).all() items = [] for row in rows: task = row[0] task.product_id = row[1] task.product_name = row[2] task.order_user_name = row[3] task.order_status = row[4] task.order_final_amount = row[5] task.order_base_currency = row[6] task.order_created_at = row[7] items.append(task) return items @staticmethod async def list_task( db: AsyncSession, status: Optional[str] = None, routing_key: Optional[str] = None, script_version: Optional[str] = None, keyword: Optional[str] = None, product_id: Optional[int] = None, sort_by: str = "created_at", sort_order: str = "desc", page: int = 1, size: int = 20, ): stmt = ( select( VasTask, VasOrder.product_id.label("order_product_id"), VasOrder.product_name.label("order_product_name"), VasOrder.user_name.label("order_user_name"), VasOrder.status.label("order_status"), VasOrder.final_amount.label("order_final_amount"), VasOrder.base_currency.label("order_base_currency"), VasOrder.created_at.label("order_created_at"), ) .outerjoin(VasOrder, VasTask.order_id == VasOrder.id) ) count_stmt = select(func.count(VasTask.id)).outerjoin(VasOrder, VasTask.order_id == VasOrder.id) if status and status != "all": stmt = stmt.where(VasTask.status == status) count_stmt = count_stmt.where(VasTask.status == status) if routing_key and routing_key != "all": stmt = stmt.where(VasTask.routing_key == routing_key) count_stmt = count_stmt.where(VasTask.routing_key == routing_key) if script_version and script_version != "all": stmt = stmt.where(VasTask.script_version == script_version) count_stmt = count_stmt.where(VasTask.script_version == script_version) if product_id: stmt = stmt.where(VasOrder.product_id == product_id) count_stmt = count_stmt.where(VasOrder.product_id == product_id) if keyword and keyword.strip(): kw = f"%{keyword.strip()}%" filter_cond = or_( cast(VasTask.id, String).ilike(kw), VasTask.order_id.ilike(kw), VasTask.routing_key.ilike(kw), VasOrder.product_name.ilike(kw), VasOrder.user_name.ilike(kw), ) stmt = stmt.where(filter_cond) count_stmt = count_stmt.where(filter_cond) total = await db.scalar(count_stmt) or 0 # 排序逻辑优化: # - created_at: 最新入队/创建时间优先 (默认) # - priority: 优先级最高优先 (紧急任务靠前,同优先级按最早创建时间先进先出 FIFO) # - updated_at: 最近活跃/更新时间优先 # - id: 任务序列号排序 # - order_id: 关联订单号聚合排序 is_asc = str(sort_order).lower() == "asc" if sort_by == "priority": if is_asc: stmt = stmt.order_by(VasTask.priority.asc(), VasTask.created_at.asc(), VasTask.id.asc()) else: stmt = stmt.order_by(VasTask.priority.desc(), VasTask.created_at.asc(), VasTask.id.asc()) elif sort_by == "updated_at": stmt = stmt.order_by( VasTask.updated_at.asc() if is_asc else VasTask.updated_at.desc(), VasTask.id.desc() ) elif sort_by == "id": stmt = stmt.order_by( VasTask.id.asc() if is_asc else VasTask.id.desc() ) elif sort_by == "order_id": stmt = stmt.order_by( VasTask.order_id.asc() if is_asc else VasTask.order_id.desc(), VasTask.id.asc() ) else: # 默认 created_at stmt = stmt.order_by( VasTask.created_at.asc() if is_asc else VasTask.created_at.desc(), VasTask.id.desc() ) if page < 1: page = 1 if size < 1: size = 20 stmt = stmt.offset((page - 1) * size).limit(size) rows = (await db.execute(stmt)).all() items = [] for row in rows: task = row[0] task.product_id = row[1] task.product_name = row[2] task.order_user_name = row[3] task.order_status = row[4] task.order_final_amount = row[5] task.order_base_currency = row[6] task.order_created_at = row[7] items.append(task) return { "items": items, "total": total, "page": page, "size": size, } @staticmethod async def get_expiring_tasks( db: AsyncSession, threshold_days: int = 3 ) -> List[dict]: """ 获取即将过期的任务列表 """ now = datetime.utcnow() threshold_date = now + timedelta(days=threshold_days) stmt = select(VasTask).where( VasTask.status == "grabbed", VasTask.grabbed_history.isnot(None) ) result = await db.execute(stmt) tasks = result.scalars().all() expiring_tasks = [] for task in tasks: grabbed_history = task.grabbed_history or {} slot_date_str = grabbed_history.get("slot_date") if not slot_date_str: continue try: try: slot_date = datetime.strptime(slot_date_str, "%Y-%m-%d") except ValueError: slot_date = datetime.strptime(slot_date_str, "%d/%m/%Y") if slot_date <= threshold_date: days_remaining = (slot_date.date() - now.date()).days expiring_tasks.append({ "task_id": task.id, "order_id": task.order_id, "routing_key": task.routing_key, "slot_date": slot_date_str, "slot_time": grabbed_history.get("slot_time"), "days_remaining": days_remaining, "days_remaining_display": f"{days_remaining} days" if days_remaining > 0 else "Today/Expired", "status": task.status }) except (ValueError, TypeError): continue expiring_tasks.sort(key=lambda x: x["days_remaining"]) return expiring_tasks @staticmethod async def update(db: AsyncSession, id: int, payload: VasTaskUpdate) -> VasTask: stmt = select(VasTask).where(VasTask.id == id) result = await db.execute(stmt) rec = result.scalar_one_or_none() if not rec: raise NotFoundError("Task not exist") old_status = rec.status if payload.status is not None: rec.status = payload.status if payload.grabbed_history is not None: rec.grabbed_history = payload.grabbed_history if payload.meta is not None: rec.meta = payload.meta await db.commit() await db.refresh(rec) logger.info(f"🔄 [VAS_TASK] Updated task {id}: status {old_status} -> {rec.status}") return rec @staticmethod async def get_task_by_id( db: AsyncSession, task_id: int, ) -> VasTask: stmt = ( select( VasTask, VasOrder.product_id.label("order_product_id"), VasOrder.product_name.label("order_product_name"), VasOrder.user_name.label("order_user_name"), VasOrder.status.label("order_status"), VasOrder.final_amount.label("order_final_amount"), VasOrder.base_currency.label("order_base_currency"), VasOrder.created_at.label("order_created_at"), ) .outerjoin(VasOrder, VasTask.order_id == VasOrder.id) .where(VasTask.id == task_id) ) row = (await db.execute(stmt)).first() if not row: raise NotFoundError("Task not exist") task = row[0] task.product_id = row[1] task.product_name = row[2] task.order_user_name = row[3] task.order_status = row[4] task.order_final_amount = row[5] task.order_base_currency = row[6] task.order_created_at = row[7] return task @staticmethod async def return_to_queue(db: AsyncSession, id: int) -> VasTask: stmt = select(VasTask).where(VasTask.id == id) result = await db.execute(stmt) rec = result.scalar_one_or_none() if not rec: raise NotFoundError("Task not exist") if rec.status in ["completed", "cancelled"]: raise BizLogicError(f"Cannot return a {rec.status} task to queue") if rec.order_id: order_stmt = select(VasOrder).where(VasOrder.id == rec.order_id) order_result = await db.execute(order_stmt) order = order_result.scalar_one_or_none() if order and order.status in ["closed", "refunded"]: raise BizLogicError("Cannot return task to queue: associated order is closed") rec.status = "pending" await db.commit() await db.refresh(rec) logger.info(f"🔁 [VAS_TASK] Returned task {id} to queue (status=pending)") return rec @staticmethod async def pause(db: AsyncSession, id: int) -> VasTask: stmt = select(VasTask).where(VasTask.id == id) result = await db.execute(stmt) rec = result.scalar_one_or_none() if not rec: raise NotFoundError("Task not exist") if rec.status == "pause": return rec if rec.status in ["completed", "cancelled"]: raise BizLogicError(f"Cannot pause a {rec.status} task") rec.status = "pause" await db.commit() await db.refresh(rec) logger.info(f"⏸️ [VAS_TASK] Paused task {id} (status=pause)") return rec @staticmethod async def manual_confirm(db: AsyncSession, id: int, redis_client: Redis) -> VasTask: stmt = select(VasTask).where(VasTask.id == id) result = await db.execute(stmt) task = result.scalar_one_or_none() if not task: raise NotFoundError("Task not exist") task.status = "completed" order_stmt = select(VasOrder).where(VasOrder.id == task.order_id) order_result = await db.execute(order_stmt) order = order_result.scalar_one_or_none() if not order: raise NotFoundError("Order not exist") order.status = "completed" await task_processor.execute(task.routing_key, db, redis_client, task, order) await db.commit() await db.refresh(task) logger.info(f"✅ [VAS_TASK] Manually confirmed task {id} and order {order.id} as completed") return task