# 资讯API路由
from fastapi import APIRouter, Depends, HTTPException, Query, Response, Body
from sqlalchemy.orm import Session, joinedload
from sqlalchemy import text
from typing import List, Optional
from pydantic import BaseModel
from datetime import datetime, date
import os

from app.core.database import get_db
from app.core.auth import get_current_user
from app.core.coolbot_db import coolbot_engine
from app.models.models import User, Information, Collection

router = APIRouter(prefix="/api/information", tags=["资讯"])


# Schema
class InformationCreate(BaseModel):
    info_type: str  # seek-寻配号, deal-成交数据, publish-发布
    title: str
    content: Optional[str]
    collection_id: Optional[str] = None
    expect_category: Optional[str] = None
    expect_version: Optional[str] = None
    expect_packaging: Optional[str] = None
    expect_number: Optional[str] = None
    expect_price_min: Optional[float] = None
    expect_price_max: Optional[float] = None
    deal_price: Optional[float] = None
    deal_date: Optional[date] = None
    packaging: Optional[str] = None
    is_graded: Optional[bool] = False
    grading_company: Optional[str] = None
    grading_score: Optional[str] = None
    category: Optional[str] = None
    deal_no: Optional[str] = None


class InformationUpdate(BaseModel):
    title: Optional[str] = None
    content: Optional[str]
    status: Optional[str] = None
    expect_category: Optional[str] = None
    expect_version: Optional[str] = None
    expect_packaging: Optional[str] = None
    expect_number: Optional[str] = None
    expect_price_min: Optional[float] = None
    expect_price_max: Optional[float] = None
    deal_price: Optional[float] = None
    deal_date: Optional[date] = None
    packaging: Optional[str] = None
    is_graded: Optional[bool] = None
    grading_company: Optional[str] = None
    grading_score: Optional[str] = None


class InformationResponse(BaseModel):
    id: str
    user_id: str
    info_type: str
    title: str
    content: Optional[str]
    collection_id: Optional[str]
    expect_category: Optional[str]
    expect_version: Optional[str]
    expect_packaging: Optional[str]
    expect_number: Optional[str]
    expect_price_min: Optional[float]
    expect_price_max: Optional[float]
    deal_price: Optional[float]
    deal_date: Optional[date]
    status: str
    is_matched: Optional[str] = "pending"
    matched_user_id: Optional[str] = None
    matched_contact: Optional[str] = None
    view_count: int
    contact_count: int
    created_at: datetime
    # 评级相关字段
    packaging: Optional[str] = None
    is_graded: Optional[bool] = False
    grading_company: Optional[str] = None
    grading_score: Optional[str] = None
    category: Optional[str] = None
    deal_no: Optional[str] = None
    # 用户信息
    user_name: Optional[str] = None
    user_avatar: Optional[str] = None
    # 关联藏品信息
    collection_name: Optional[str] = None
    collection_category: Optional[str] = None
    collection_version: Optional[str] = None
    collection_number: Optional[str] = None
    # 匹配数量（我的藏品中满足条件的数量）
    matched_count: Optional[int] = 0
    # 网络数据匹配数量（coolbot_data数据库中满足条件的数量）
    network_matched_count: Optional[int] = 0

    class Config:
        from_attributes = True


# 资讯列表
@router.get("/list", response_model=List[InformationResponse])
def get_information_list(
    info_type: Optional[str] = Query(None, description="类型: seek/deal/publish"),
    status: str = Query("active", description="状态: active/closed/expired"),
    user_id: Optional[str] = Query(None, description="用户ID，用于获取该用户的行情"),
    deal_date: Optional[str] = Query(None, description="成交日期过滤，格式YYYY-MM-DD"),
    page: int = Query(1, ge=1),
    page_size: int = Query(20, ge=1, le=500),
    current_user: Optional[User] = Depends(get_current_user),
    db: Session = Depends(get_db),
    response: Response = None
):
    """获取资讯列表（公开，无需登录）"""
    query = db.query(Information).options(
        joinedload(Information.user),
        joinedload(Information.collection)
    ).filter(Information.status == status)
    
    if info_type:
        query = query.filter(Information.info_type == info_type)
    
    # 如果传入了user_id，只返回该用户的行情
    if user_id:
        query = query.filter(Information.user_id == user_id)
    
    # 成交日期过滤
    if deal_date:
        from datetime import date
        deal_date_obj = date.fromisoformat(deal_date)
        query = query.filter(Information.deal_date == deal_date_obj)
    
    # 按创建时间倒序
    query = query.order_by(Information.created_at.desc())
    
    # 分页
    offset = (page - 1) * page_size
    items = query.offset(offset).limit(page_size).all()
    
    # 转换结果
    result = []
    for item in items:
        # 计算匹配数量（仅对seek类型，且用户登录时）
        matched_count = 0
        if item.info_type == 'seek' and item.expect_number and current_user:
            matched_count = match_collections_count(db, current_user.f99_90_id, item.expect_number)
        
        result.append(InformationResponse(
            id=item.id,
            user_id=item.user_id,
            info_type=item.info_type,
            title=item.title,
            content=item.content,
            collection_id=item.collection_id,
            expect_category=item.expect_category,
            expect_version=item.expect_version,
            expect_packaging=item.expect_packaging,
            expect_number=item.expect_number,
            expect_price_min=item.expect_price_min,
            expect_price_max=item.expect_price_max,
            deal_price=item.deal_price,
            deal_date=item.deal_date,
            status=item.status,
            is_matched=item.is_matched,
            matched_user_id=item.matched_user_id,
            matched_contact=item.matched_contact,
            view_count=item.view_count,
            contact_count=item.contact_count,
            created_at=item.created_at,
            user_name=item.user.f01_01_name if item.user else None,
            user_avatar=item.user.avatar if item.user else None,
            collection_name=item.collection.f01_01_name if item.collection else None,
            collection_category=item.collection.f01_03_category if item.collection else None,
            collection_version=item.collection.f02_11_version if item.collection else None,
            collection_number=item.collection.f02_10_prefix_serial if item.collection else None,
            packaging=item.packaging,
            is_graded=item.is_graded or False,
            grading_company=item.grading_company,
            grading_score=item.grading_score,
            category=item.category,
            deal_no=item.deal_no,
            matched_count=matched_count,
            network_matched_count=match_collections_count_from_coolbot(item.expect_number) if item.info_type == 'seek' and item.expect_number else 0,
        ))
    
    # 获取总数并设置响应头
    from fastapi import Response
    total_query = db.query(Information).filter(Information.status == status)
    if info_type:
        total_query = total_query.filter(Information.info_type == info_type)
    total_count = total_query.count()
    total_pages = (total_count + page_size - 1) // page_size
    
    # 设置响应头
    response.headers['X-Total-Pages'] = str(total_pages)
    response.headers['X-Total-Count'] = str(total_count)
    
    return result


def match_collections_count(db: Session, user_id: str, expect_number: str) -> int:
    """根据号码特征计算匹配藏品数量"""
    if not expect_number or len(expect_number) != 10:
        return 0
    
    # 固定前缀
    if not expect_number.startswith('J0'):
        return 0
    
    pattern = expect_number[2:]  # 后8位
    if not pattern:
        return 0
    
    # 获取用户所有藏品
    collections = db.query(Collection).filter(
        Collection.f99_91_user_id == user_id,
        Collection.f01_04_status == "in_collection"
    ).all()
    
    count = 0
    for c in collections:
        number = c.f02_10_prefix_serial or ''
        # 去掉J0前缀后取前8位
        if len(number) >= 10 and number.startswith('J0'):
            col_pattern = number[2:10]
            if match_pattern(col_pattern, pattern):
                count += 1
        elif len(number) >= 8:
            col_pattern = number[:8]
            if match_pattern(col_pattern, pattern):
                count += 1
    
    return count


def match_collections_count_from_coolbot(expect_number: str) -> int:
    """根据号码特征计算匹配藏品数量（从coolbot_data数据库）"""
    if not expect_number or len(expect_number) != 10:
        return 0
    
    # 固定前缀
    if not expect_number.startswith('J0'):
        return 0
    
    pattern = expect_number[2:]  # 后8位
    if not pattern:
        return 0
    
    # 直接查询coolbot_data数据库
    query = text("""
        SELECT COUNT(*) FROM collections 
        WHERE crown_code IS NOT NULL 
        AND crown_code != ''
        AND LENGTH(crown_code) >= 10
        AND crown_code LIKE 'J0%'
    """)
    
    try:
        with coolbot_engine.connect() as conn:
            result = conn.execute(query)
            total_count = result.scalar() or 0
            
            # 遍历匹配
            query_all = text("""
                SELECT id, crown_code FROM collections 
                WHERE crown_code IS NOT NULL 
                AND crown_code != ''
                AND LENGTH(crown_code) >= 10
                AND crown_code LIKE 'J0%'
            """)
            result = conn.execute(query_all)
            
            match_count = 0
            for row in result:
                crown_code = row[1]
                if crown_code and len(crown_code) >= 10:
                    col_pattern = crown_code[2:10]
                    if match_pattern(col_pattern, pattern):
                        match_count += 1
            
            return match_count
    except Exception as e:
        print(f"Error querying coolbot_data: {e}")
        return 0


def match_collections_list_from_coolbot(expect_number: str, limit: int = 20) -> List[dict]:
    """获取匹配的藏品列表（从coolbot_data数据库）"""
    if not expect_number or len(expect_number) != 10:
        return []
    
    # 固定前缀
    if not expect_number.startswith('J0'):
        return []
    
    pattern = expect_number[2:]  # 后8位
    if not pattern:
        return []
    
    # 直接查询coolbot_data数据库
    query = text("""
        SELECT id, name, category, crown_code, price, post_title, post_url, author, post_crawled_at 
        FROM collections 
        WHERE crown_code IS NOT NULL 
        AND crown_code != ''
        AND LENGTH(crown_code) >= 10
        AND crown_code LIKE 'J0%'
    """)
    
    try:
        with coolbot_engine.connect() as conn:
            result = conn.execute(query)
            
            matched = []
            for row in result:
                crown_code = row[3]
                if crown_code and len(crown_code) >= 10:
                    col_pattern = crown_code[2:10]
                    if match_pattern(col_pattern, pattern):
                        matched.append({
                            "id": row[0],
                            "name": row[1],
                            "category": row[2],
                            "crown_code": crown_code,
                            "price": float(row[4]) if row[4] else None,
                            "post_title": row[5],
                            "post_url": row[6],
                            "author": row[7],
                            "post_crawled_at": row[8].isoformat() if row[8] else None
                        })
                        if len(matched) >= limit:
                            break
            
            return matched
    except Exception as e:
        print(f"Error querying coolbot_data: {e}")
        return []


def match_pattern(col_number: str, pattern: str) -> bool:
    """匹配号码特征模式"""
    # X = 任意数字
    # A = 非4
    # B = 非47
    # C = 非347
    # D = 非247
    # E = 非2347
    # F = 非23457
    # G = 非123457
    
    # 注意：col_number已经是去掉J0前缀后的8位号码，不需要再处理
    col_num = col_number
    
    for i, p in enumerate(pattern):
        if i >= len(col_num):
            return False
        
        c = col_num[i]
        
        if p == 'X':
            if not c.isdigit():
                return False
        elif p == 'A':
            if c == '4':
                return False
        elif p == 'B':
            if c in '47':
                return False
        elif p == 'C':
            if c in '347':
                return False
        elif p == 'D':
            if c in '247':
                return False
        elif p == 'E':
            if c in '2347':
                return False
        elif p == 'F':
            if c in '23457':
                return False
        elif p == 'G':
            if c in '123457':
                return False
        else:
            # 数字或字母必须完全匹配
            if p != c:
                return False
    
    return True


# 获取单条资讯
@router.get("/{info_id}", response_model=InformationResponse)
def get_information(
    info_id: str,
    db: Session = Depends(get_db)
):
    """获取资讯详情"""
    item = db.query(Information).options(
        joinedload(Information.user),
        joinedload(Information.collection)
    ).filter(Information.id == info_id).first()
    
    if not item:
        raise HTTPException(status_code=404, detail="资讯不存在")
    
    # 增加浏览次数
    item.view_count += 1
    db.commit()
    
    return InformationResponse(
        id=item.id,
        user_id=item.user_id,
        info_type=item.info_type,
        title=item.title,
        content=item.content,
        collection_id=item.collection_id,
        expect_category=item.expect_category,
        expect_version=item.expect_version,
        expect_packaging=item.expect_packaging,
        expect_number=item.expect_number,
        expect_price_min=item.expect_price_min,
        expect_price_max=item.expect_price_max,
        deal_price=item.deal_price,
        deal_date=item.deal_date,
        status=item.status,
        view_count=item.view_count,
        contact_count=item.contact_count,
        created_at=item.created_at,
        packaging=item.packaging,
        is_graded=item.is_graded or False,
        grading_company=item.grading_company,
        grading_score=item.grading_score,
        category=item.category,
        user_name=item.user.f01_01_name if item.user else None,
        user_avatar=item.user.avatar if item.user else None,
        collection_name=item.collection.f01_01_name if item.collection else None,
        collection_category=item.collection.f01_03_category if item.collection else None,
        collection_version=item.collection.f02_11_version if item.collection else None,
        collection_number=item.collection.f02_10_prefix_serial if item.collection else None,
    )


# 发布资讯
@router.post("/", response_model=InformationResponse)
def create_information(
    data: InformationCreate,
    current_user: User = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """发布资讯"""
    # 生成行情编号：日期 + 5位自然数（从00001开始）
    deal_no = None
    if data.info_type == 'deal':
        today = datetime.now().strftime('%Y%m%d')
        # 查询当天已有行情数量
        from app.models.models import Information
        count_today = db.query(Information).filter(
            Information.info_type == 'deal',
            Information.deal_no.like(f'DJ{today}%')
        ).count()
        # 编号 = 日期 + 5位自然数（如 DJ2026041100001）
        seq = count_today + 1
        deal_no = f'DJ{today}{seq:05d}'
    
    info = Information(
        user_id=current_user.f99_90_id,
        info_type=data.info_type,
        title=data.title,
        content=data.content,
        collection_id=data.collection_id,
        expect_category=data.expect_category,
        expect_version=data.expect_version,
        expect_packaging=data.expect_packaging,
        expect_number=data.expect_number,
        expect_price_min=data.expect_price_min,
        expect_price_max=data.expect_price_max,
        deal_price=data.deal_price,
        deal_date=data.deal_date,
        packaging=data.packaging,
        is_graded=data.is_graded or False,
        grading_company=data.grading_company,
        grading_score=data.grading_score,
        category=data.category,
        deal_no=deal_no,
        status="active"
    )
    db.add(info)
    db.commit()
    db.refresh(info)
    
    return InformationResponse(
        id=info.id,
        user_id=info.user_id,
        info_type=info.info_type,
        title=info.title,
        content=info.content,
        collection_id=info.collection_id,
        expect_category=info.expect_category,
        expect_version=info.expect_version,
        expect_packaging=info.expect_packaging,
        expect_number=info.expect_number,
        expect_price_min=info.expect_price_min,
        expect_price_max=info.expect_price_max,
        deal_price=info.deal_price,
        deal_date=info.deal_date,
        status=info.status,
        view_count=info.view_count,
        contact_count=info.contact_count,
        created_at=info.created_at,
        user_name=current_user.f01_01_name,
        user_avatar=current_user.avatar,
        collection_name=None,
        collection_category=None,
        collection_version=None,
        collection_number=None,
    )


# 更新资讯
@router.put("/{info_id}", response_model=InformationResponse)
def update_information(
    info_id: str,
    data: InformationUpdate,
    current_user: User = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """更新资讯"""
    info = db.query(Information).filter(
        Information.id == info_id,
        Information.user_id == current_user.f99_90_id
    ).first()
    
    if not info:
        raise HTTPException(status_code=404, detail="资讯不存在或无权修改")
    
    # 更新字段
    if data.title is not None:
        info.title = data.title
    if data.content is not None:
        info.content = data.content
    if data.status is not None:
        info.status = data.status
    if data.expect_category is not None:
        info.expect_category = data.expect_category
    if data.expect_version is not None:
        info.expect_version = data.expect_version
    if data.expect_packaging is not None:
        info.expect_packaging = data.expect_packaging
    if data.expect_number is not None:
        info.expect_number = data.expect_number
    if data.expect_price_min is not None:
        info.expect_price_min = data.expect_price_min
    if data.expect_price_max is not None:
        info.expect_price_max = data.expect_price_max
    if data.deal_price is not None:
        info.deal_price = data.deal_price
    if data.deal_date is not None:
        info.deal_date = data.deal_date
    if data.packaging is not None:
        info.packaging = data.packaging
    if data.is_graded is not None:
        info.is_graded = data.is_graded
    if data.grading_company is not None:
        info.grading_company = data.grading_company
    if data.grading_score is not None:
        info.grading_score = data.grading_score
    if data.category is not None:
        info.category = data.category
    
    db.commit()
    db.refresh(info)
    
    return InformationResponse(
        id=info.id,
        user_id=info.user_id,
        info_type=info.info_type,
        title=info.title,
        content=info.content,
        collection_id=info.collection_id,
        expect_category=info.expect_category,
        expect_version=info.expect_version,
        expect_packaging=info.expect_packaging,
        expect_number=info.expect_number,
        expect_price_min=info.expect_price_min,
        expect_price_max=info.expect_price_max,
        deal_price=info.deal_price,
        deal_date=info.deal_date,
        status=info.status,
        view_count=info.view_count,
        contact_count=info.contact_count,
        created_at=info.created_at,
        packaging=info.packaging,
        is_graded=info.is_graded or False,
        grading_company=info.grading_company,
        grading_score=info.grading_score,
        category=info.category,
        user_name=current_user.f01_01_name,
        user_avatar=current_user.avatar,
        collection_name=info.collection.f01_01_name if info.collection else None,
        collection_category=info.collection.f01_03_category if info.collection else None,
        collection_version=info.collection.f02_11_version if info.collection else None,
        collection_number=info.collection.f02_10_prefix_serial if info.collection else None,
    )


# 删除资讯
@router.delete("/{info_id}")
def delete_information(
    info_id: str,
    current_user: User = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """删除资讯"""
    # 允许admin删除任何人的资讯
    if current_user.role == "admin":
        info = db.query(Information).filter(Information.id == info_id).first()
    else:
        info = db.query(Information).filter(
            Information.id == info_id,
            Information.user_id == current_user.f99_90_id
        ).first()
    
    if not info:
        raise HTTPException(status_code=404, detail="资讯不存在或无权删除")
    
    db.delete(info)
    db.commit()
    
    return {"message": "删除成功"}


# 寻配号 - 自动匹配推荐藏品
@router.get("/seek/match")
def get_seek_match(
    info_id: str,
    current_user: Optional[User] = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """获取符合条件的我的藏品推荐"""
    if not current_user:
        raise HTTPException(status_code=401, detail="请先登录")
    
    info = db.query(Information).filter(
        Information.id == info_id,
        Information.info_type == "seek"
    ).first()
    
    if not info:
        raise HTTPException(status_code=404, detail="寻配号信息不存在")
    
    # 更新用户配号(寻号)次数
    current_user.f99_96_search_count = (current_user.f99_96_search_count or 0) + 1
    db.commit()
    
    # 获取用户所有藏品
    collections = db.query(Collection).filter(
        Collection.f99_91_user_id == current_user.f99_90_id,
        Collection.f01_04_status == "in_collection"
    ).all()
    
    # 去掉版别筛选，因为藏品分类和发布需求的版别不同
    # if info.expect_category:
    #     collections = [c for c in collections if c.f01_03_category == info.expect_category]
    
    # 按号码特征模式匹配
    matched = []
    if info.expect_number and len(info.expect_number) == 10:
        pattern = info.expect_number[2:]  # 后8位
        for c in collections:
            number = c.f02_10_prefix_serial or ''
            # 去掉J0前缀后取前8位
            if len(number) >= 10 and number.startswith('J0'):
                col_pattern = number[2:10]  # 取J0后面的8位
                if match_pattern(col_pattern, pattern):
                    matched.append(c)
            elif len(number) >= 8:
                col_pattern = number[:8]  # 取前8位
                if match_pattern(col_pattern, pattern):
                    matched.append(c)
    else:
        matched = collections
    
    return {
        "info_id": info_id,
        "matched_count": len(matched),
        "collections": [
            {
                "id": c.f99_90_id,
                "code": c.f01_02_code or '',
                "name": c.f01_01_name,
                "number": c.f02_10_prefix_serial,
                "status": c.f01_04_status,
                "category": c.f01_03_category,
                "version": c.f02_11_version,
                "packaging": c.f02_12_packaging,
                "cost_price": c.f05_40_cost_price,
            }
            for c in matched
        ],
        "network_matched_count": match_collections_count_from_coolbot(info.expect_number) if info.expect_number else 0,
        "network_collections": match_collections_list_from_coolbot(info.expect_number, limit=20) if info.expect_number else []
    }


# 获取网络数据匹配列表
@router.get("/seek/network-match/{info_id}")
def get_network_match(
    info_id: str,
    limit: int = Query(20, ge=1, le=100),
    db: Session = Depends(get_db)
):
    """获取一尘数据库中匹配的藏品列表"""
    info = db.query(Information).filter(
        Information.id == info_id,
        Information.info_type == "seek"
    ).first()
    
    if not info:
        raise HTTPException(status_code=404, detail="寻配号信息不存在")
    
    if not info.expect_number:
        return {"matched_count": 0, "collections": []}
    
    matched = match_collections_list_from_coolbot(info.expect_number, limit=limit)
    
    return {
        "matched_count": len(matched),
        "collections": matched
    }


# 我的寻号列表
@router.get("/my-seeks")
def get_my_seeks(
    current_user: Optional[User] = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """获取当前用户发布的所有寻号信息"""
    if not current_user:
        raise HTTPException(status_code=401, detail="请先登录")
    
    items = db.query(Information).filter(
        Information.user_id == current_user.f99_90_id,
        Information.info_type == "seek",
        Information.status == "active"
    ).order_by(Information.created_at.desc()).all()
    
    result = []
    for item in items:
        # 计算匹配数量
        matched_count = 0
        if item.expect_number:
            matched_count = match_collections_count(db, current_user.f99_90_id, item.expect_number)
        
        result.append(InformationResponse(
            id=item.id,
            user_id=item.user_id,
            info_type=item.info_type,
            title=item.title,
            content=item.content,
            collection_id=item.collection_id,
            expect_category=item.expect_category,
            expect_version=item.expect_version,
            expect_packaging=item.expect_packaging,
            expect_number=item.expect_number,
            expect_price_min=item.expect_price_min,
            expect_price_max=item.expect_price_max,
            deal_price=item.deal_price,
            deal_date=item.deal_date,
            status=item.status,
            is_matched=item.is_matched,
            matched_user_id=item.matched_user_id,
            matched_contact=item.matched_contact,
            view_count=item.view_count,
            contact_count=item.contact_count,
            created_at=item.created_at,
            user_name=item.user.f01_01_name if item.user else None,
            user_avatar=item.user.avatar if item.user else None,
            collection_name=item.collection.f01_01_name if item.collection else None,
            collection_category=item.collection.f01_03_category if item.collection else None,
            collection_version=item.collection.f02_11_version if item.collection else None,
            collection_number=item.collection.f02_10_prefix_serial if item.collection else None,
            matched_count=matched_count,
            network_matched_count=match_collections_count_from_coolbot(item.expect_number) if item.info_type == 'seek' and item.expect_number else 0,
        ))
    
    # 获取总数并设置响应头
    from fastapi import Response
    total_query = db.query(Information).filter(Information.status == status)
    if info_type:
        total_query = total_query.filter(Information.info_type == info_type)
    total_count = total_query.count()
    total_pages = (total_count + page_size - 1) // page_size
    
    # 设置响应头
    response.headers['X-Total-Pages'] = str(total_pages)
    response.headers['X-Total-Count'] = str(total_count)
    
    return result


# 成交数据统计
@router.get("/deal/stats")
def get_deal_stats(
    days: int = Query(7, ge=1, le=90, description="统计天数"),
    db: Session = Depends(get_db)
):
    """获取成交数据统计"""
    from sqlalchemy import func
    from datetime import timedelta
    
    start_date = datetime.now() - timedelta(days=days)
    
    # 按版别统计
    by_version = db.query(
        Information.expect_version,
        func.count(Information.id).label("count"),
        func.avg(Information.deal_price).label("avg_price"),
        func.max(Information.deal_price).label("max_price"),
        func.min(Information.deal_price).label("min_price")
    ).filter(
        Information.info_type == "deal",
        Information.status == "active",
        Information.created_at >= start_date
    ).group_by(Information.expect_version).all()
    
    # 按包装统计
    by_packaging = db.query(
        Information.expect_packaging,
        func.count(Information.id).label("count"),
        func.avg(Information.deal_price).label("avg_price")
    ).filter(
        Information.info_type == "deal",
        Information.status == "active",
        Information.created_at >= start_date
    ).group_by(Information.expect_packaging).all()
    
    # 按号码分类统计
    by_number = db.query(
        Information.expect_number,
        func.count(Information.id).label("count"),
        func.avg(Information.deal_price).label("avg_price")
    ).filter(
        Information.info_type == "deal",
        Information.status == "active",
        Information.expect_number.isnot(None),
        Information.created_at >= start_date
    ).group_by(Information.expect_number).all()
    
    return {
        "days": days,
        "by_version": [
            {"version": r[0] or "未知", "count": r[1], "avg_price": float(r[2] or 0), "max_price": float(r[3] or 0), "min_price": float(r[4] or 0)}
            for r in by_version if r[0]
        ],
        "by_packaging": [
            {"packaging": r[0] or "未知", "count": r[1], "avg_price": float(r[2] or 0)}
            for r in by_packaging if r[0]
        ],
        "by_number": [
            {"number": r[0], "count": r[1], "avg_price": float(r[2] or 0)}
            for r in by_number
        ]
    }


# 获取我的发布列表
@router.get("/my/list", response_model=List[InformationResponse])
def get_my_information_list(
    page: int = Query(1, ge=1),
    page_size: int = Query(20, ge=1, le=500),
    current_user: User = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """获取我的发布列表"""
    items = db.query(Information).options(
        joinedload(Information.collection)
    ).filter(
        Information.user_id == current_user.f99_90_id
    ).order_by(Information.created_at.desc()).offset((page-1)*page_size).limit(page_size).all()
    
    result = []
    for item in items:
        result.append(InformationResponse(
            id=item.id,
            user_id=item.user_id,
            info_type=item.info_type,
            title=item.title,
            content=item.content,
            collection_id=item.collection_id,
            expect_category=item.expect_category,
            expect_version=item.expect_version,
            expect_packaging=item.expect_packaging,
            expect_number=item.expect_number,
            expect_price_min=item.expect_price_min,
            expect_price_max=item.expect_price_max,
            deal_price=item.deal_price,
            deal_date=item.deal_date,
            status=item.status,
            is_matched=item.is_matched,
            matched_user_id=item.matched_user_id,
            matched_contact=item.matched_contact,
            view_count=item.view_count,
            contact_count=item.contact_count,
            created_at=item.created_at,
            user_name=current_user.f01_01_name,
            user_avatar=current_user.avatar,
            collection_name=item.collection.f01_01_name if item.collection else None,
            collection_category=item.collection.f01_03_category if item.collection else None,
            collection_version=item.collection.f02_11_version if item.collection else None,
            collection_number=item.collection.f02_10_prefix_serial if item.collection else None,
        ))
    
    return result

# ============ 获取当前用户发布的列表 ============
@router.get("/my")
def get_my_information(
    page: int = Query(1, ge=1),
    limit: int = Query(20, ge=1, le=100),
    current_user: User = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """获取当前用户发布的信息列表"""
    total = db.query(Information).filter(Information.author == current_user.f01_01_name).count()
    infos = db.query(Information).filter(
        Information.author == current_user.f01_01_name
    ).order_by(Information.created_at.desc()).offset((page-1)*limit).limit(limit).all()
    
    return {
        "total": total,
        "list": [{
            "id": i.id,
            "title": i.title,
            "content": i.content,
            "info_type": i.info_type,
            "author": i.author,
            "created_at": i.created_at.isoformat() if i.created_at else None
        } for i in infos]
    }


# ============ 匹配寻号 ============
class MatchSeekRequest(BaseModel):
    info_id: str
    collection_id: Optional[str] = None
    contact: Optional[str] = None


@router.post("/seek/match-confirm")
def match_seek(
    request: MatchSeekRequest,
    current_user: User = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """确认匹配寻号 - 用户愿意交换联系方式给发布者"""
    info = db.query(Information).filter(
        Information.id == request.info_id,
        Information.info_type == "seek",
        Information.status == "active"
    ).first()
    
    if not info:
        raise HTTPException(status_code=404, detail="寻配号信息不存在")
    
    # 检查是否已被匹配（一个寻号只能被一个用户匹配）
    if info.is_matched == "matched":
        raise HTTPException(status_code=400, detail="该寻号已被其他用户匹配")
    
    # 更新匹配状态
    info.is_matched = "matched"
    info.matched_user_id = current_user.f99_90_id
    # 保存匹配者的联系方式
    info.matched_contact = request.contact or ''
    
    # 更新发布寻号者的内容，显示有藏品被匹配
    original_content = info.content or ""
    # 添加匹配信息：藏品被XX藏友匹配，联系方式为：xxx
    match_info = f"\n藏品被{current_user.f01_01_name}匹配成功！联系方式：{request.collection_id or '已交换'}"
    info.content = original_content + match_info
    
    db.commit()
    
    return {"message": "匹配成功，已通知发布者", "is_matched": "matched"}


# ============ 添加留言 ============
class CommentRequest(BaseModel):
    information_id: str
    content: str


@router.post("/comment")
def add_comment(
    request: CommentRequest,
    current_user: User = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """添加留言"""
    info = db.query(Information).filter(
        Information.id == request.information_id
    ).first()
    
    if not info:
        raise HTTPException(status_code=404, detail="资讯不存在")
    
    # 创建留言
    from app.models.models import InformationComment
    comment = InformationComment(
        information_id=request.information_id,
        user_id=current_user.f99_90_id,
        content=request.content
    )
    db.add(comment)
    db.commit()
    
    return {
        "message": "留言成功",
        "comment": {
            "id": comment.id,
            "content": comment.content,
            "user_name": current_user.f01_01_name,
            "user_avatar": current_user.avatar,
            "created_at": comment.created_at
        }
    }


# ============ 获取评论列表 ============
@router.get("/comments/{information_id}")
def get_comments(
    information_id: str,
    db: Session = Depends(get_db)
):
    """获取资讯的评论列表"""
    from app.models.models import InformationComment
    comments = db.query(InformationComment).filter(
        InformationComment.information_id == information_id
    ).order_by(InformationComment.created_at.desc()).all()
    
    return [
        {
            "id": c.id,
            "content": c.content,
            "user_name": c.user.f01_01_name if c.user else '匿名用户',
            "user_avatar": c.user.avatar if c.user else None,
            "created_at": c.created_at
        }
        for c in comments
    ]


# ============ 获取匹配者信息 ============
@router.get("/seek/matched-user/{info_id}")
def get_matched_user(
    info_id: str,
    current_user: User = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """获取寻号的匹配者信息（仅发布者可见）"""
    info = db.query(Information).filter(
        Information.id == info_id,
        Information.info_type == "seek"
    ).first()
    
    if not info:
        raise HTTPException(status_code=404, detail="寻配号信息不存在")
    
    # 只有发布者可以看到匹配者信息
    if info.user_id != current_user.f99_90_id:
        raise HTTPException(status_code=403, detail="无权访问")
    
    if not info.matched_user_id:
        return {"message": "暂无匹配者"}
    
    # 获取匹配者信息
    matched_user = db.query(User).filter(User.f99_90_id == info.matched_user_id).first()
    if not matched_user:
        return {"message": "匹配者不存在"}
    
    return {
        "matched_user_id": info.matched_user_id,
        "user_name": matched_user.f01_01_name,
        "phone": matched_user.phone,
        "matched_contact": info.matched_contact,
        "matched_at": info.updated_at.isoformat() if info.updated_at else None
    }


# ============ 获取发布者信息 ============
@router.get("/seek/publisher/{info_id}")
def get_publisher_info(
    info_id: str,
    current_user: User = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """获取寻号的发布者信息（仅匹配者可见）"""
    info = db.query(Information).filter(
        Information.id == info_id,
        Information.info_type == "seek"
    ).first()
    
    if not info:
        raise HTTPException(status_code=404, detail="寻配号信息不存在")
    
    # 只有匹配者可以看到发布者信息
    if not info.matched_user_id or info.matched_user_id != current_user.f99_90_id:
        raise HTTPException(status_code=403, detail="无权访问")
    
    # 获取发布者信息
    publisher = db.query(User).filter(User.f99_90_id == info.user_id).first()
    if not publisher:
        return {"message": "发布者不存在"}
    
    # 从content中解析联系方式
    contact = ''
    if info.content:
        import re
        match = re.search(r'联系方式[:\s]*(.+?)(?:\n|$)', info.content)
        if match:
            contact = match.group(1).strip()
    
    return {
        "user_id": info.user_id,
        "user_name": publisher.f01_01_name,
        "phone": publisher.phone,
        "contact": contact,
        "created_at": info.created_at.isoformat() if info.created_at else None
    }


@router.get("/yichen-posts")
def get_yichen_posts(category: str = None, search: str = None, page: int = 1, page_size: int = 20):
    from app.models.models import Information
    from sqlalchemy import desc
    query = db.query(Information).filter(Information.info_type == 'yichen')
    if category:
        query = query.filter(Information.expect_category == category)
    if search:
        query = query.filter(Information.title.contains(search))
    total = query.count()
    offset = (page - 1) * page_size
    items = query.order_by(desc(Information.created_at)).offset(offset).limit(page_size).all()
    return {"data": items, "pagination": {"page": page, "limit": page_size, "total": total}}


@router.get("/seek/stats")
def get_seek_stats(
    current_user: User = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """获取寻配号统计数据"""
    # 寻号需求数（seek类型且expect_number不为空的总数）
    seek_count = db.query(Information).filter(
        Information.info_type == 'seek',
        Information.expect_number.isnot(None),
        Information.expect_number != ''
    ).count()
    
    # 我的匹配：自有藏品匹配成功的寻号帖子数量
    # 即 is_matched = 'confirmed' 的记录，用户ID等于当前用户
    user_matched_count = 0
    if current_user:
        user_matched_count = db.query(Information).filter(
            Information.info_type == 'seek',
            Information.expect_number.isnot(None),
            Information.expect_number != '',
            Information.matched_user_id == current_user.f99_90_id,
            Information.is_matched == 'confirmed'
        ).count()
    
    # 总共匹配：自有匹配成功 + 网络数据匹配成功
    # 自有匹配成功：is_matched = 'confirmed'
    # 网络数据匹配成功：查询每个帖子的network_matched_count并求和
    seeks = db.query(Information).filter(
        Information.info_type == 'seek',
        Information.expect_number.isnot(None),
        Information.expect_number != ''
    ).all()
    
    total_self_matched = 0
    total_network_matched = 0
    for seek in seeks:
        # 自身匹配成功
        if seek.is_matched == 'confirmed':
            total_self_matched += 1
        # 网络数据匹配成功（通过coolbot数据库查询）
        if seek.expect_number:
            network_count = match_collections_count_from_coolbot(seek.expect_number)
            total_network_matched += network_count
    
    total_matched_count = total_self_matched + total_network_matched
    
    return {
        "seekCount": seek_count,
        "userMatchedCount": user_matched_count,
        "totalMatchedCount": total_matched_count
    }

# 批量解析行情数据API
@router.post("/batch-parse")
async def batch_parse_deals(text: str = Body(..., embed=True)):
    """使用AI智能解析批量行情文本"""
    import httpx
    import json
    import re
    
    # 使用阿里云百炼Coding Plan API
    api_key = "sk-sp-d5ce68bb203e48ca857c2aea25255b26"
    base_url = "https://coding.dashscope.aliyuncs.com/v1"
    
    # 更详细的解析提示词
    prompt = f"""你是一个专业的龙钞行情数据提取助手。请从以下文本中提取所有龙钞行情记录。

【解析规则】
1. 每条记录格式：冠字号 价格 评级/包装 出售者
2. 冠字号：J0开头的9位数字（如J0298810101）
3. 价格：¥xxx,xxx 格式，去掉逗号转为数字
4. 评级/包装：PC69/PMG68/爱藏67+/爱藏67 标十 标百 单张
5. 出售者：人名
6. 号码分类：根据冠字号数字特征判断（圆圆号/倒置号/金马王/金马号/金山王/天马王/金山号/天马号/朦胧王/朦胧号/如意号/钻石号/永恒号/带7号/带4号）

【输出格式】
返回JSON数组，每条记录包含：
- serial: 冠字号（完整9位，如J0298810101）
- price: 价格（数字）
- grade: 评级（如PC69, PMG68, 爱藏67+, 爱藏67）
- packaging: 包装类型（标十/标百/单张）
- category: 号码分类
- seller: 出售者
- date: 交易日（从文本中提取日期，如2026-03-29）

只返回JSON数组，不要其他内容。

文本：
{text}"""

    try:
        async with httpx.AsyncClient(timeout=120.0) as client:
            response = await client.post(
                f"{base_url}/chat/completions",
                json={
                    "model": "qwen3.6-plus",
                    "messages": [
                        {"role": "system", "content": "你是一个专业的收藏品行情数据提取助手，擅长从文本中提取结构化的交易数据。只返回JSON数组。"},
                        {"role": "user", "content": prompt}
                    ],
                    "temperature": 0.1
                },
                headers={
                    "Authorization": f"Bearer {api_key}",
                    "Content-Type": "application/json"
                }
            )
            
            if response.status_code != 200:
                return {"success": False, "error": f"API错误: {response.status_code}, {response.text[:200]}"}
            
            result = response.json()
            # 阿里云百炼OpenAI兼容格式
            choices = result.get("choices", [])
            content = ""
            if choices and len(choices) > 0:
                content = choices[0].get("message", {}).get("content", "")
            
            # 解析JSON
            try:
                # 尝试提取JSON
                if "```json" in content:
                    content = content.split("```json")[1].split("```")[0]
                elif "```" in content:
                    content = content.split("```")[1].split("```")[0]
                
                # 尝试直接解析
                data = json.loads(content.strip())
                return {"success": True, "data": data}
            except json.JSONDecodeError:
                # 尝试用正则提取
                match = re.search(r'\[.*\]', content, re.DOTALL)
                if match:
                    try:
                        data = json.loads(match.group())
                        return {"success": True, "data": data}
                    except:
                        pass
                return {"success": False, "error": "解析失败", "raw": content[:500]}
                
    except Exception as e:
        return {"success": False, "error": str(e)}

# 本地正则解析函数
def parse_deals_locally(text: str, default_packaging: str = '', default_date: str = '', default_platform: str = ''):
    """本地正则解析批量行情文本"""
    import re
    from datetime import datetime
    results = []
    
    # 尝试从文本中提取日期（可能出现在标题或时间戳中）
    # 格式如: 3月29日, 2026年3月29日, 2026-03-29
    date_patterns = [
        r'(\d{1,2})月(\d{1,2})日',
        r'(\d{4})年(\d{1,2})月(\d{1,2})日',
        r'(\d{4})-(\d{1,2})-(\d{1,2})'
    ]
    
    extracted_date = None
    for pattern in date_patterns:
        match = re.search(pattern, text)
        if match:
            try:
                if len(match.groups()) == 2:
                    # 3月29日 - 使用当前年份
                    extracted_date = f"2026-{int(match.group(1)):02d}-{int(match.group(2)):02d}"
                elif len(match.groups()) == 3:
                    if int(match.group(1)) > 2000:
                        # 2026年3月29日
                        extracted_date = f"{match.group(1)}-{int(match.group(2)):02d}-{int(match.group(3)):02d}"
                    else:
                        # 3月29日格式
                        extracted_date = f"2026-{int(match.group(1)):02d}-{int(match.group(2)):02d}"
                break
            except:
                pass
    
    # 默认使用今天
    default_date = datetime.now().strftime('%Y-%m-%d')
    deal_date = extracted_date or default_date
    
    lines = text.strip().split('\n')
    
    for line in lines:
        line = line.strip()
        if not line or 'J0' not in line:
            continue
        
        # 提取冠字号 J0 + 8-9位数字
        serial_match = re.search(r'J0(\d{8,9})', line)
        if not serial_match:
            continue
        
        serial_num = serial_match.group(1)
        if len(serial_num) == 9:
            serial_num = serial_num[:8]
        serial = 'J0' + serial_num
        
        # 提取价格 ¥xxx,xxx 或 xxx,xxx（必须在J0之后）
        serial_pos = line.find(serial)
        after_serial = line[serial_pos + len(serial):]
        price_match = re.search(r'[¥￥]?\s*([1-9][\d,]{2,})', after_serial)
        if not price_match:
            continue
        price = int(price_match.group(1).replace(',', ''))
        
        # 提取卖家（价格后面的中文字符）
        after_price_pos = after_serial.find(price_match.group(0)) + len(price_match.group(0))
        after_price = after_serial[after_price_pos:]
        seller_match = re.search(r'([^\d\s¥￥]+?)(?:\s*$|\s*\n)', after_price)
        seller = seller_match.group(1).strip() if seller_match else ''
        
        # 分类判断
        digits = serial_num
        d = digits
        category = '通货'
        if not any(c in d for c in ['1','2','3','4','5','7']): category = '圆圆号'
        elif not any(c in d for c in ['2','3','4','5','7']): category = '倒置号'
        elif not any(c in d for c in ['1','2','3','4','7']): category = '金马王'
        elif not any(c in d for c in ['2','3','4','7']): category = '金马号'
        elif not any(c in d for c in ['1','2','4','5','7']): category = '金山王'
        elif not any(c in d for c in ['1','2','4','7']): category = '天马王'
        elif not any(c in d for c in ['2','4','5','7']): category = '金山号'
        elif not any(c in d for c in ['2','4','7']): category = '天马号'
        elif not any(c in d for c in ['1','3','4','5','7']): category = '朦胧王'
        elif not any(c in d for c in ['3','4','5','7']): category = '朦胧号'
        elif not any(c in d for c in ['1','3','4','7']): category = '如意号'
        elif not any(c in d for c in ['3','4','7']): category = '钻石号'
        elif not any(c in d for c in ['4','7']): category = '永恒号'
        elif '4' not in d: category = '带7号'
        
        # 提取评级机构 PCGS/PMG/ACG/爱藏
        grade = ''
        grading_company = ''
        packaging = '单张'
        
        if 'PC69' in line or 'PC68' in line or 'PC67' in line:
            grade_match = re.search(r'PC(6[789]|5\d?)', line)
            grade = 'PC' + grade_match.group(1) if grade_match else ''
            grading_company = 'PCGS'
        elif 'PMG68' in line or 'PMG67' in line:
            grade_match = re.search(r'PMG(6[789]|5\d?)', line)
            grade = 'PMG' + grade_match.group(1) if grade_match else ''
            grading_company = 'PMG'
        elif 'ACG' in line:
            grade_match = re.search(r'ACG(6[789]|5\d?)', line)
            grade = 'ACG' + grade_match.group(1) if grade_match else ''
            grading_company = 'ACG'
        elif '爱藏67+' in line:
            grade = '67+'
            grading_company = '爱藏'
        elif '爱藏67' in line:
            grade = '67'
            grading_company = '爱藏'
        
        # 判断包装类型
        packaging = '单张'
        
        # 如果传入了默认包装类型，先使用默认
        if default_packaging:
            packaging = default_packaging
        
        # 简化识别：带"刀"字=标百，带"标"字=标十
        if '刀' in line:
            packaging = '标百'
        elif '标' in line:
            packaging = '标十'
        
        # 尾号判断：如果冠字号尾号是01/11/21/31/41/51/61/71/81/91，且有刀/标字样，基本确认是标百
        if len(serial_num) >= 2:
            tail = serial_num[-2:]
            if tail in ['01', '11', '21', '31', '41', '51', '61', '71', '81', '91']:
                if '刀' in line or ('标' in line and packaging == '单张'):
                    packaging = '标百'
                    packaging = '标百'
        
        # 如果没有刀/标字样，但尾号是01且没有其他特征，可能是标百
        if packaging == '单张' and len(serial_num) >= 2:
            tail = serial_num[-2:]
            if tail == '01':
                # 检查是否在特定语境下
                packaging = '标百'
        
        # 计算尾号和大小号
        tail_number = ''
        size_type = ''
        if packaging == '标十' and len(serial_num) >= 2:
            tail_number = serial_num[-2:]
            size_type = tail_number in ['01','11','21','31','41','51'] and '小号' or '大号'
        elif packaging == '标百' and len(serial_num) >= 3:
            tail_number = serial_num[-3:]
            size_type = tail_number in ['101','201','301','401','501'] and '小号' or '大号'
        
        results.append({
            'serial': serial,
            'price': price,
            'category': category,
            'seller': seller,
            'packaging': packaging,
            'grade': grade,
            'grading_company': grading_company,
            'deal_date': deal_date or default_date,  # 成交时间
            'entry_date': default_date,  # 录入时间
            'is_graded': bool(grade),
            'tail_number': tail_number,  # 尾号
            'size_type': size_type,  # 大小号
            'platform': default_platform  # 平台
        })
    
    return results

@router.post("/batch-parse-local")
async def batch_parse_deals_local(request: dict = Body(...)):
    """本地正则解析批量行情文本（无需AI）"""
    text = request.get('text', '')
    default_packaging = request.get('defaultPackaging', '')
    default_date = request.get('defaultDate', '')
    default_platform = request.get('defaultPlatform', '')
    results = parse_deals_locally(text, default_packaging, default_date, default_platform)
    return {"success": True, "data": results}
