routers/statistics.py

from fastapi import APIRouter, Depends, HTTPException, status, Request
from sqlalchemy.orm import Session
from sqlalchemy import func
from datetime import datetime, timedelta
from typing import Dict, List, Optional

from database import get_db
from models.user import User
from models.membership import Membership
from models.member_level import MemberLevel
from models.product import Product
from models.product_media import ProductMedia
from models.media import Media
from models.site_visit import SiteVisit
from routers.auth import get_current_user
from utils.ip_location import ip_location_service

router = APIRouter()


def get_client_ip(request: Request) -> str:
    """获取客户端真实IP地址"""
    # 优先从 X-Forwarded-For 获取（经过代理的情况）
    x_forwarded_for = request.headers.get("x-forwarded-for")
    if x_forwarded_for:
        # X-Forwarded-For 可能包含多个IP，取第一个
        return x_forwarded_for.split(",")[0].strip()
    
    # 从 X-Real-IP 获取
    x_real_ip = request.headers.get("x-real-ip")
    if x_real_ip:
        return x_real_ip
    
    # 从请求的 client 获取
    if request.client:
        return request.client.host
    
    return "0.0.0.0"


@router.post("/visits/record")
async def record_site_visit(
    request: Request,
    db: Session = Depends(get_db)
):
    """记录独立站访问（公开接口，无需认证）"""
    try:
        body = await request.json()
    except:
        body = {}
    
    # 获取客户端IP
    ip_address = get_client_ip(request)
    
    # 解析IP地理位置
    location = ip_location_service.get_location(ip_address)
    
    # 获取 company_code（从请求参数或域名）
    company_code = body.get("company_code", "default")
    
    # 创建访问记录
    visit = SiteVisit(
        company_code=company_code,
        ip_address=ip_address,
        visitor_id=body.get("visitor_id"),
        country=location.get("country"),
        region=location.get("region"),
        city=location.get("city"),
        latitude=location.get("latitude"),
        longitude=location.get("longitude"),
        user_agent=request.headers.get("user-agent"),
        referer=request.headers.get("referer"),
        page_url=body.get("page_url", request.url.path)
    )
    
    db.add(visit)
    db.commit()
    db.refresh(visit)
    
    return {
        "status": "success",
        "visit_id": visit.id,
        "location": location
    }


@router.get("/dashboard")
async def get_dashboard_statistics(
    current_user: User = Depends(get_current_user),
    db: Session = Depends(get_db)
):
    """获取仪表盘统计指标"""
    company_code = current_user.company_code
    
    # 1. SKU数量
    sku_count = db.query(Product).filter(
        Product.company_code == company_code
    ).count()
    
    # 2. 图片占用空间 (Media模型的file_size是字节)
    total_image_size_bytes = db.query(func.sum(Media.file_size)).filter(
        Media.company_code == company_code,
        Media.media_type == 'image'
    ).scalar() or 0
    
    # 转换为MB (字节 -> MB)
    total_image_size_mb = total_image_size_bytes / (1024 * 1024)
    # 转换为GB
    total_image_size_gb = round(total_image_size_mb / 1024, 2)
    
    # 3. 会员信息
    membership = db.query(Membership).filter(
        Membership.company_code == company_code
    ).order_by(Membership.end_date.desc()).first()
    
    days_remaining = 0
    member_level = ""
    member_level_name = ""
    
    if membership:
        if membership.end_date:
            delta = membership.end_date - datetime.now().date()
            days_remaining = max(0, delta.days)
        
        member_level = membership.level
        
        level_info = db.query(MemberLevel).filter(
            MemberLevel.level == membership.level
        ).first()
        
        if level_info:
            member_level_name = level_info.name
    
    # 4. 昨日独立站IP访问数
    yesterday = datetime.now().date() - timedelta(days=1)
    yesterday_start = datetime.combine(yesterday, datetime.min.time())
    yesterday_end = datetime.combine(yesterday, datetime.max.time())
    
    yesterday_ip_count = db.query(
        SiteVisit.ip_address
    ).filter(
        SiteVisit.company_code == company_code,
        SiteVisit.visit_date >= yesterday_start,
        SiteVisit.visit_date <= yesterday_end
    ).distinct().count()
    
    # 5. 近7天独立站IP访问数
    seven_days_ago = datetime.now().date() - timedelta(days=7)
    seven_days_start = datetime.combine(seven_days_ago, datetime.min.time())
    
    seven_days_ip_count = db.query(
        SiteVisit.ip_address
    ).filter(
        SiteVisit.company_code == company_code,
        SiteVisit.visit_date >= seven_days_start
    ).distinct().count()
    
    # 6. 近30天独立站访问数
    thirty_days_ago = datetime.now().date() - timedelta(days=30)
    thirty_days_start = datetime.combine(thirty_days_ago, datetime.min.time())
    
    thirty_days_visit_count = db.query(SiteVisit).filter(
        SiteVisit.company_code == company_code,
        SiteVisit.visit_date >= thirty_days_start
    ).count()
    
    return {
        "sku_count": sku_count,
        "image_storage": {
            "total_mb": round(total_image_size_mb, 2),
            "total_gb": total_image_size_gb
        },
        "membership": {
            "level": member_level,
            "level_name": member_level_name,
            "days_remaining": days_remaining,
            "end_date": membership.end_date if membership else None
        },
        "site_visits": {
            "yesterday_ips": yesterday_ip_count,
            "last_7_days_ips": seven_days_ip_count,
            "last_30_days_visits": thirty_days_visit_count
        }
    }


@router.get("/visits/by-region")
async def get_visits_by_region(
    current_user: User = Depends(get_current_user),
    days: int = 30,
    db: Session = Depends(get_db)
):
    """获取按区域统计的IP访问数"""
    company_code = current_user.company_code
    
    start_date = datetime.now().date() - timedelta(days=days)
    start_datetime = datetime.combine(start_date, datetime.min.time())
    
    # 按国家统计
    country_stats = db.query(
        SiteVisit.country,
        SiteVisit.ip_address
    ).filter(
        SiteVisit.company_code == company_code,
        SiteVisit.visit_date >= start_datetime,
        SiteVisit.country.isnot(None)
    ).group_by(
        SiteVisit.country,
        SiteVisit.ip_address
    ).all()
    
    # 统计数据
    country_ip_counts: Dict[str, int] = {}
    for country, ip in country_stats:
        if country:
            country_ip_counts[country] = country_ip_counts.get(country, 0) + 1
    
    # 按地区/大洲分类
    continent_mapping = {
        "中国": "亚洲",
        "日本": "亚洲",
        "韩国": "亚洲",
        "印度": "亚洲",
        "泰国": "亚洲",
        "越南": "亚洲",
        "马来西亚": "亚洲",
        "新加坡": "亚洲",
        "菲律宾": "亚洲",
        "印度尼西亚": "亚洲",
        "美国": "北美洲",
        "加拿大": "北美洲",
        "墨西哥": "北美洲",
        "英国": "欧洲",
        "德国": "欧洲",
        "法国": "欧洲",
        "意大利": "欧洲",
        "西班牙": "欧洲",
        "荷兰": "欧洲",
        "波兰": "欧洲",
        "俄罗斯": "欧洲",
        "巴西": "南美洲",
        "阿根廷": "南美洲",
        "智利": "南美洲",
        "澳大利亚": "大洋洲",
        "新西兰": "大洋洲",
        "南非": "非洲",
        "埃及": "非洲",
        "尼日利亚": "非洲"
    }
    
    continent_stats: Dict[str, int] = {}
    for country, count in country_ip_counts.items():
        continent = continent_mapping.get(country, "其他")
        continent_stats[continent] = continent_stats.get(continent, 0) + count
    
    # 构建世界地图数据
    world_map_data = []
    for country, count in country_ip_counts.items():
        world_map_data.append({
            "country": country,
            "value": count
        })
    
    # 按访问量排序
    world_map_data.sort(key=lambda x: x["value"], reverse=True)
    
    return {
        "period_days": days,
        "continent_stats": continent_stats,
        "country_stats": country_ip_counts,
        "world_map_data": world_map_data,
        "total_unique_ips": sum(country_ip_counts.values())
    }


@router.get("/visits/by-country/{country}")
async def get_visits_by_country(
    country: str,
    current_user: User = Depends(get_current_user),
    days: int = 30,
    db: Session = Depends(get_db)
):
    """获取指定国家的访问详情"""
    company_code = current_user.company_code
    
    start_date = datetime.now().date() - timedelta(days=days)
    start_datetime = datetime.combine(start_date, datetime.min.time())
    
    visits = db.query(SiteVisit).filter(
        SiteVisit.company_code == company_code,
        SiteVisit.country == country,
        SiteVisit.visit_date >= start_datetime
    ).order_by(
        SiteVisit.visit_date.desc()
    ).limit(100).all()
    
    return [
        {
            "id": visit.id,
            "ip_address": visit.ip_address,
            "visit_date": visit.visit_date,
            "page_url": visit.page_url,
            "city": visit.city,
            "region": visit.region
        }
        for visit in visits
    ]


@router.get("/visits/daily-trend")
async def get_visits_daily_trend(
    current_user: User = Depends(get_current_user),
    days: int = 30,
    db: Session = Depends(get_db)
):
    """获取每日访问趋势"""
    company_code = current_user.company_code
    
    start_date = datetime.now().date() - timedelta(days=days)
    start_datetime = datetime.combine(start_date, datetime.min.time())
    
    daily_visits = db.query(
        SiteVisit.visit_date,
        SiteVisit.ip_address
    ).filter(
        SiteVisit.company_code == company_code,
        SiteVisit.visit_date >= start_datetime
    ).all()
    
    # 按日期统计独立IP数
    daily_ip_counts: Dict[str, set] = {}
    for visit_date, ip in daily_visits:
        date_str = visit_date.strftime("%Y-%m-%d")
        if date_str not in daily_ip_counts:
            daily_ip_counts[date_str] = set()
        daily_ip_counts[date_str].add(ip)
    
    # 构建趋势数据
    trend_data = []
    for date_str in sorted(daily_ip_counts.keys()):
        trend_data.append({
            "date": date_str,
            "unique_ips": len(daily_ip_counts[date_str])
        })
    
    return {
        "period_days": days,
        "trend_data": trend_data
    }