from typing import Any, Optional, List
from datetime import datetime, timedelta, timezone

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

from app.models.user import User
from app.models.resources_material import ResourcesMaterial
from app.models.material_cost import MaterialCost
from app.models.user_oauth import UserOAuth
from app.models.user_oauth_account import UserOAuthAccount
from app.dependencies import get_current_user, get_db, get_admin_user
from app.services.material_consumption_queue import sync_all_advertisers_consumption, _fetch_and_save_consumption
from app.services.material_consumption_service import get_consumption_list, format_consumption_response

router = APIRouter(prefix="/material-consumption", tags=["material-consumption"])


@router.get(
    "/list",
    summary="查询素材消耗列表",
    description="查询当前用户的素材消耗列表",
)
async def user_consumption_list(
    page: int = Query(1, ge=1, description="页码"),
    page_size: int = Query(20, ge=1, le=100, description="每页数量"),
    advertiser_id: Optional[str] = Query(None, description="广告主ID筛选"),
    consume_date: Optional[List[str]] = Query(None, description="消耗日期范围，格式: ['开始日期','结束日期']"),
    current_user: User = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
) -> Any | dict:
    consumptions, total = await get_consumption_list(
        db=db,
        page=page,
        page_size=page_size,
        advertiser_id=advertiser_id,
        consume_date=consume_date,
        current_user=current_user,
    )
    
    return {
        "code": 0,
        "data": format_consumption_response(consumptions),
        "pagination": {
            "page": page,
            "page_size": page_size,
            "total": total,
        },
    }


@router.get(
    "/sync",
    summary="手动同步素材消耗",
    description="手动触发当前用户授权的广告主的素材消耗同步任务，加入队列顺序执行",
)
async def sync_consumption(
    date: str = Query(None, description="同步日期，默认为昨天"),
    advertiser_id: Optional[str] = Query(None, description="指定广告主ID，不指定则同步所有授权的广告主"),
    current_user: User = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
) -> Any | dict:
    if date is None:
        date = (datetime.now(timezone.utc) - timedelta(days=1)).strftime("%Y-%m-%d")
    
    from app.services.material_consumption_queue import material_consumption_queue
    
    query = (
        select(UserOAuth.id, UserOAuthAccount.advertiser_id)
        .join(UserOAuthAccount, UserOAuth.id == UserOAuthAccount.oauth_id)
        .join(ResourcesMaterial, UserOAuthAccount.advertiser_id == ResourcesMaterial.advertiser_id)
        .where(
            UserOAuth.user_id == current_user.id,
            UserOAuth.deleted_at.is_(None),
            UserOAuthAccount.deleted_at.is_(None),
            ResourcesMaterial.deleted_at.is_(None),
            ResourcesMaterial.material_id.is_not_(None),
        )
        .distinct()
    )
    
    if advertiser_id:
        query = query.where(UserOAuthAccount.advertiser_id == advertiser_id)
    
    result = await db.execute(query)
    oauth_advertiser_pairs = result.all()
    
    if not oauth_advertiser_pairs:
        return {
            "code": 0,
            "message": "没有找到可同步的广告主",
        }
    
    for oauth_id, adv_id in oauth_advertiser_pairs:
        await material_consumption_queue.enqueue({
            "oauth_id": oauth_id,
            "advertiser_id": adv_id,
            "date": date,
        })
    
    return {
        "code": 0,
        "message": f"已将 {len(oauth_advertiser_pairs)} 个广告主的消耗更新任务加入队列",
    }


@router.get(
    "/fields",
    summary="获取素材消耗字段描述",
    description="返回素材消耗表每个字段对应的中文描述，帮助前端理解字段含义",
)
async def get_consumption_fields() -> Any | dict:
    fields = [
        {"field": "id", "description": "主键"},
        {"field": "advertiser_id", "description": "广告主id"},
        {"field": "material_id", "description": "素材id"},
        {"field": "consume_date", "description": "消耗日期"},
        {"field": "stat_cost", "description": "消耗金额"},
        {"field": "show_cnt", "description": "展示数"},
        {"field": "cpm_platform", "description": "平均千次展现费用(元)"},
        {"field": "click_cnt", "description": "点击数"},
        {"field": "ctr", "description": "点击率"},
        {"field": "cpc_platform", "description": "平均点击单价(元)"},
        {"field": "convert_cnt", "description": "转化数"},
        {"field": "conversion_cost", "description": "平均转化成本(元)"},
        {"field": "conversion_rate", "description": "转化率"},
        {"field": "deep_convert_cnt", "description": "深度转化数"},
        {"field": "deep_convert_cost", "description": "深度转化成本(元)"},
        {"field": "deep_convert_rate", "description": "深度转化率"},
        {"field": "active", "description": "激活数"},
        {"field": "active_cost", "description": "激活成本(元)"},
        {"field": "active_rate", "description": "激活率"},
        {"field": "active_register", "description": "注册数"},
        {"field": "active_register_cost", "description": "注册成本(元)"},
        {"field": "active_register_rate", "description": "注册率"},
        {"field": "attribution_next_day_open_cnt", "description": "次留数"},
        {"field": "attribution_next_day_open_cost", "description": "次留成本"},
        {"field": "attribution_next_day_open_rate", "description": "次留率"},
        {"field": "active_pay", "description": "首次付费数"},
        {"field": "active_pay_cost", "description": "首次付费成本(元)"},
        {"field": "active_pay_rate", "description": "首次付费率"},
        {"field": "phone", "description": "点击电话按钮"},
        {"field": "form", "description": "用户在门店落地页多线沟通提交表单的次数"},
        {"field": "download_start", "description": "用户点击下载开始的次数"},
        {"field": "form_submit", "description": "用户查看附加创意后，提交表单的次数"},
        {"field": "button", "description": "用户点击按钮button的次数"},
        {"field": "view", "description": "用户在关键页面的浏览次数"},
        {"field": "message", "description": "用户点击短信咨询的次数"},
        {"field": "consult", "description": "用户点击在线咨询按钮的次数"},
        {"field": "consult_effective", "description": "用户在门店落地页多线沟通的在线咨询中有效咨询的次数"},
        {"field": "shopping", "description": "用户购买商品的次数"},
        {"field": "customer_effective", "description": "有效获客"},
        {"field": "attribution_game_in_app_ltv_1day", "description": "当日付费金额"},
        {"field": "attribution_game_in_app_roi_1day", "description": "当日付费ROI"},
        {"field": "loan_completion", "description": "完件数(互联网金融-贷款行业中，用户成功提交贷款额度申请的行为)"},
        {"field": "loan_completion_cost", "description": "完件成本(元)"},
        {"field": "loan_completion_rate", "description": "完件率"},
        {"field": "loan_credit", "description": "授信数(互联网金融-贷款行业中，用户提交贷款额度申请后，客户审批通过，给予用户可贷款的额度)"},
        {"field": "loan_credit_cost", "description": "授信成本(元)"},
        {"field": "loan_credit_rate", "description": "授信率"},
        {"field": "in_app_order_gmv", "description": "引流电商订单GMV(当您使用\"in_app_order\"事件回传订单金额时，对应的GMV金额)"},
        {"field": "in_app_order_roi", "description": "引流电商订单ROI"},
        {"field": "in_app_pay_gmv", "description": "引流电商支付GMV"},
        {"field": "in_app_pay_roi", "description": "引流电商支付ROI"},
        {"field": "total_play", "description": "播放量(播放时间大于0S的数量，在某些蜂窝网络环境下，需要您手动点击开始才会开始播放，因此有时播放数小于展示数)"},
        {"field": "valid_play", "description": "有效播放数"},
        {"field": "valid_play_cost", "description": "有效播放成本(元)"},
        {"field": "valid_play_rate", "description": "有效播放率"},
        {"field": "valid_play_of_mille", "description": "千次有效播放数"},
        {"field": "valid_play_cost_of_mille", "description": "千次有效播放成本(元)"},
        {"field": "average_play_time_per_play", "description": "平均单次播放时长"},
        {"field": "play_over_rate", "description": "完播率"},
        {"field": "dy_like", "description": "点赞数"},
        {"field": "dy_comment", "description": "评论量"},
        {"field": "dy_share", "description": "分享量"},
        {"field": "report_cnt", "description": "举报数"},
        {"field": "created_at", "description": "创建时间"},
    ]
    
    return {
        "code": 0,
        "data": fields,
    }


@router.get(
    "/advertisers",
    summary="获取用户的广告主列表",
    description="获取当前用户有权限的广告主列表，用于筛选",
)
async def get_advertisers(
    current_user: User = Depends(get_current_user),
    db: AsyncSession = Depends(get_db),
) -> Any | dict:
    result = await db.execute(
        select(UserOAuthAccount.advertiser_id)
        .join(UserOAuth, UserOAuthAccount.oauth_id == UserOAuth.id)
        .where(
            UserOAuth.user_id == current_user.id,
            UserOAuth.deleted_at.is_(None),
            UserOAuthAccount.deleted_at.is_(None),
        )
        .distinct()
    )
    advertisers = result.scalars().all()
    
    return {
        "code": 0,
        "data": [{"advertiser_id": str(aid)} for aid in advertisers],
    }


@router.get(
    "/admin/list",
    summary="管理员查询所有素材消耗列表",
    description="管理员可查看系统中所有用户的素材消耗数据",
)
async def admin_consumption_list(
    page: int = Query(1, ge=1, description="页码"),
    page_size: int = Query(20, ge=1, le=100, description="每页数量"),
    advertiser_id: Optional[str] = Query(None, description="广告主ID筛选"),
    user_id: Optional[str] = Query(None, description="用户ID筛选"),
    consume_date: Optional[List[str]] = Query(None, description="消耗日期范围，格式: ['开始日期','结束日期']"),
    current_user: User = Depends(get_admin_user),
    db: AsyncSession = Depends(get_db),
) -> Any | dict:
    #需要判断管理员吗
    if not current_user.is_admin:
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="只有管理员才能查询所有素材消耗列表",
        )
        

    consumptions, total = await get_consumption_list(
        db=db,
        page=page,
        page_size=page_size,
        advertiser_id=advertiser_id,
        user_id=user_id,
        consume_date=consume_date,
        current_user=None,
    )
    
    return {
        "code": 0,
        "data": format_consumption_response(consumptions, include_oauth_id=True),
        "pagination": {
            "page": page,
            "page_size": page_size,
            "total": total,
        },
    }