apps/api/routers/trade.py

from fastapi import APIRouter, Depends
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select, func, desc

from packages.core.database import get_db_session
from packages.core.models import StandardTradeRecord
from apps.api.schemas.trade import TradeQueryRequest, TradeRecordResponse, PaginatedResponse

router = APIRouter()

@router.post("/search", response_model=PaginatedResponse[TradeRecordResponse])
async def search_trade_records(
    query: TradeQueryRequest,
    db: AsyncSession = Depends(get_db_session)
):
    """
    检索标准贸易记录
    支持按国家、方向、HS、商品、企业名、起运/目的国及日期范围筛选
    """
    stmt = select(StandardTradeRecord)
    count_stmt = select(func.count()).select_from(StandardTradeRecord)
    
    # 动态构建查询条件
    filters = []
    if query.source_country:
        filters.append(StandardTradeRecord.source_country == query.source_country)
    if query.trade_direction:
        filters.append(StandardTradeRecord.trade_direction == query.trade_direction)
    if query.hs_code:
        filters.append(StandardTradeRecord.hs_code.like(f"{query.hs_code}%"))
    if query.product_name:
        filters.append(StandardTradeRecord.product_name.ilike(f"%{query.product_name}%"))
    if query.importer_name:
        filters.append(StandardTradeRecord.importer_name.ilike(f"%{query.importer_name}%"))
    if query.exporter_name:
        filters.append(StandardTradeRecord.exporter_name.ilike(f"%{query.exporter_name}%"))
    if query.origin_country:
        filters.append(StandardTradeRecord.origin_country == query.origin_country)
    if query.destination_country:
        filters.append(StandardTradeRecord.destination_country == query.destination_country)
    if query.start_date:
        filters.append(StandardTradeRecord.trade_date >= query.start_date)
    if query.end_date:
        filters.append(StandardTradeRecord.trade_date <= query.end_date)
        
    if filters:
        stmt = stmt.where(*filters)
        count_stmt = count_stmt.where(*filters)
        
    # 计算总数
    count_stmt = select(func.count()).select_from(stmt.subquery())
    total_result = await db.execute(count_stmt)
    total = total_result.scalar() or 0
    
    # 分页查询
    stmt = stmt.order_by(desc(StandardTradeRecord.trade_date))
    stmt = stmt.offset((query.page - 1) * query.limit).limit(query.limit)
    
    result = await db.execute(stmt)
    records = result.scalars().all()
    
    return PaginatedResponse(
        total=total,
        items=records
    )