"""价格路由"""
from fastapi import APIRouter, Query
from typing import Optional
from datetime import datetime

from api.models.response import PriceHistoryResponse
from api.middleware.response import ApiResponse, PaginatedData
from database import db

router = APIRouter(prefix="/api/v1/prices", tags=["prices"])

@router.get("/latest", response_model=ApiResponse)
async def get_latest_prices(
    category: Optional[str] = Query(None, description="品类筛选"),
    source: Optional[str] = Query(None, description="来源筛选"),
    limit: int = Query(50, ge=1, le=200, description="返回数量")
):
    """获取最新价格记录"""
    with db.get_cursor() as cursor:
        sql = """
            SELECT ph.*, c.name as collection_name, c.category, c.serial_number
            FROM price_history ph
            LEFT JOIN collections c ON ph.collection_id = c.id
        """
        params = []
        where_clauses = []
        
        if category:
            where_clauses.append("c.category = %s")
            params.append(category)
        if source:
            where_clauses.append("ph.source = %s")
            params.append(source)
        
        if where_clauses:
            sql += " WHERE " + " AND ".join(where_clauses)
        
        sql += " ORDER BY ph.crawled_at DESC LIMIT %s"
        params.append(limit)
        
        cursor.execute(sql, params)
        items = cursor.fetchall()
    
    return ApiResponse.success(items)

@router.get("/trend", response_model=ApiResponse)
async def get_price_trend(
    collection_id: int = Query(..., description="藏品ID"),
    days: int = Query(30, ge=1, le=365, description="统计天数")
):
    """获取价格趋势（按天统计）"""
    with db.get_cursor() as cursor:
        cursor.execute("""
            SELECT 
                DATE(ph.crawled_at) as date,
                AVG(ph.price) as avg_price,
                MIN(ph.price) as min_price,
                MAX(ph.price) as max_price,
                COUNT(*) as record_count
            FROM price_history ph
            WHERE ph.collection_id = %s
              AND ph.crawled_at >= DATE_SUB(NOW(), INTERVAL %s DAY)
            GROUP BY DATE(ph.crawled_at)
            ORDER BY date ASC
        """, (collection_id, days))
        items = cursor.fetchall()
    
    return ApiResponse.success(items)