PRINTFILM: AI short-video marketing and AI short-drama creation platform
"""管理端统计聚合:仪表盘调用量/费用/排行。"""
from __future__ import annotations
from datetime import date, datetime, timedelta, timezone
from typing import Any
from sqlalchemy import Date, case, cast, func, or_, select, union_all
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.orm import aliased
from app.models import Order, Project, UsageEvent, User
from app.models_drama import DramaProject
# 同一 task 内只应有一行的计费 key(并发双记时需折叠);llm/tts 等同 key 多行合法保留
_SINGLE_SHOT_BILLING_KEY_PREFIXES = ("seedance",)
_SINGLE_SHOT_BILLING_KEYS = frozenset({"seedream"})
def _utc_today_start() -> datetime:
return datetime.now(timezone.utc).replace(hour=0, minute=0, second=0, microsecond=0)
def _utc_month_start() -> datetime:
now = datetime.now(timezone.utc)
return now.replace(day=1, hour=0, minute=0, second=0, microsecond=0)
def _day_key(value: Any) -> str:
"""把 DB 返回的 date/datetime/str 统一成 YYYY-MM-DD。"""
if isinstance(value, datetime):
return value.date().isoformat()
if isinstance(value, date):
return value.isoformat()
text = str(value or "").strip()
return text[:10] if len(text) >= 10 else text
async def _usage_day_expr(db: AsyncSession):
"""按日历日分桶(PostgreSQL CAST AS DATE)。"""
_ = db
return cast(UsageEvent.created_at, Date)
def _derived_capability_expr():
"""能力分桶:优先 billing_key 推导,与 billing_key_to_capability 语义一致。"""
return case(
(UsageEvent.billing_key == "llm_chat", "llm"),
(UsageEvent.billing_key == "seedream", "image"),
(UsageEvent.billing_key.like("seedance%"), "video"),
(UsageEvent.billing_key == "tts", "tts"),
(UsageEvent.billing_key.is_not(None), "other"),
(UsageEvent.capability.is_not(None), UsageEvent.capability),
else_="other",
)
def _capability_scope_clause(capability: str) -> Any:
"""能力筛选:与分桶共用推导表达式。"""
cap = (capability or "").strip().lower()
if not cap or cap == "all":
return True
return _derived_capability_expr() == cap
def _usage_scope_filters(
*,
domain: str = "all",
capability: str = "all",
) -> list[Any]:
"""领域 / 能力筛选条件(不含时间)。"""
filters: list[Any] = []
if domain and domain != "all":
filters.append(UsageEvent.domain == domain)
if capability and capability != "all":
filters.append(_capability_scope_clause(capability))
return filters
async def _usage_window_totals(
db: AsyncSession,
*,
since: datetime | None = None,
scope_filters: list[Any] | None = None,
) -> dict[str, int]:
"""聚合 usage_events:calls / charge。"""
filters: list[Any] = list(scope_filters or [])
if since is not None:
filters.append(UsageEvent.created_at >= since)
stmt = select(
func.count(UsageEvent.id),
func.coalesce(func.sum(UsageEvent.charge_fen), 0),
)
for clause in filters:
stmt = stmt.where(clause)
row = (await db.execute(stmt)).one()
return {
"calls": int(row[0] or 0),
"charge_fen": int(row[1] or 0),
}
async def _group_usage(
db: AsyncSession,
*,
group_col,
since: datetime | None = None,
scope_filters: list[Any] | None = None,
limit: int | None = None,
) -> list[dict[str, Any]]:
"""按某一列分组聚合 usage。"""
key = func.coalesce(group_col, "unknown")
stmt = (
select(
key.label("key"),
func.count(UsageEvent.id).label("calls"),
func.coalesce(func.sum(UsageEvent.charge_fen), 0).label("charge_fen"),
)
.group_by(key)
.order_by(func.coalesce(func.sum(UsageEvent.charge_fen), 0).desc())
)
filters: list[Any] = list(scope_filters or [])
if since is not None:
filters.append(UsageEvent.created_at >= since)
for clause in filters:
stmt = stmt.where(clause)
if limit is not None:
stmt = stmt.limit(limit)
rows = (await db.execute(stmt)).all()
return [
{
"key": str(r.key or "unknown"),
"calls": int(r.calls or 0),
"charge_fen": int(r.charge_fen or 0),
}
for r in rows
]
async def build_admin_dashboard_stats(
db: AsyncSession,
*,
days: int = 7,
domain: str = "all",
capability: str = "all",
top_metric: str = "charge",
) -> dict[str, Any]:
"""组装管理端仪表盘全部统计字段。"""
today = _utc_today_start()
month = _utc_month_start()
window_days = max(1, min(30, int(days)))
range_start = today - timedelta(days=window_days - 1)
scope = _usage_scope_filters(domain=domain, capability=capability)
user_count = int((await db.execute(select(func.count()).select_from(User))).scalar_one() or 0)
drama_project_count = int(
(await db.execute(select(func.count()).select_from(DramaProject))).scalar_one() or 0
)
paid_total = int(
(
await db.execute(
select(func.coalesce(func.sum(Order.amount_fen), 0)).where(Order.status == "paid")
)
).scalar_one()
or 0
)
paid_today = int(
(
await db.execute(
select(func.coalesce(func.sum(Order.amount_fen), 0)).where(
Order.status == "paid",
Order.paid_at.is_not(None),
Order.paid_at >= today,
)
)
).scalar_one()
or 0
)
status_rows = (
await db.execute(select(Project.status, func.count()).group_by(Project.status))
).all()
project_status_counts = {str(status): int(cnt) for status, cnt in status_rows}
total_u = await _usage_window_totals(db)
today_u = await _usage_window_totals(db, since=today)
month_u = await _usage_window_totals(db, since=month)
by_capability = await _group_usage(
db, group_col=_derived_capability_expr(), since=range_start, scope_filters=scope
)
by_domain = await _group_usage(
db, group_col=UsageEvent.domain, since=range_start, scope_filters=scope
)
# 按日趋势(筛选窗口内补零)
day_expr = await _usage_day_expr(db)
daily_stmt = (
select(
day_expr.label("day"),
func.count(UsageEvent.id).label("calls"),
func.coalesce(func.sum(UsageEvent.charge_fen), 0).label("charge_fen"),
)
.where(UsageEvent.created_at >= range_start)
.group_by(day_expr)
.order_by(day_expr.asc())
)
for clause in scope:
daily_stmt = daily_stmt.where(clause)
daily_rows = (await db.execute(daily_stmt)).all()
daily_map: dict[str, dict[str, int]] = {}
for r in daily_rows:
daily_map[_day_key(r.day)] = {
"calls": int(r.calls or 0),
"charge_fen": int(r.charge_fen or 0),
}
daily_usage: list[dict[str, Any]] = []
for i in range(window_days):
key = (range_start + timedelta(days=i)).date().isoformat()
hit = daily_map.get(key) or {"calls": 0, "charge_fen": 0}
daily_usage.append(
{
"date": key,
"calls": hit["calls"],
"charge_fen": hit["charge_fen"],
}
)
# 筛选窗口内用户 Top10(按指标排序)
owner = aliased(User)
metric_key = (top_metric or "charge").strip().lower()
order_col = func.coalesce(func.sum(UsageEvent.charge_fen), 0)
if metric_key == "calls":
order_col = func.count(UsageEvent.id)
top_stmt = (
select(
UsageEvent.user_id,
owner.email,
func.count(UsageEvent.id).label("calls"),
func.coalesce(func.sum(UsageEvent.charge_fen), 0).label("charge_fen"),
)
.outerjoin(owner, owner.id == UsageEvent.user_id)
.where(UsageEvent.created_at >= range_start)
.group_by(UsageEvent.user_id, owner.email)
.order_by(order_col.desc())
.limit(10)
)
for clause in scope:
top_stmt = top_stmt.where(clause)
top_rows = (await db.execute(top_stmt)).all()
top_users_by_charge = [
{
"user_id": int(r.user_id),
"email": r.email,
"calls": int(r.calls or 0),
"charge_fen": int(r.charge_fen or 0),
}
for r in top_rows
]
return {
"user_count": user_count,
"order_paid_total_fen": paid_total,
"order_paid_today_fen": paid_today,
"project_status_counts": project_status_counts,
"drama_project_count": drama_project_count,
"usage_calls_today": today_u["calls"],
"usage_calls_month": month_u["calls"],
"usage_calls_total": total_u["calls"],
"usage_charge_today_fen": today_u["charge_fen"],
"usage_charge_month_fen": month_u["charge_fen"],
"usage_charge_total_fen": total_u["charge_fen"],
"usage_by_capability": by_capability,
"usage_by_domain": by_domain,
"daily_usage": daily_usage,
"top_users_by_charge": top_users_by_charge,
}
def _usage_billing_cases():
"""按 billing_key 拆分图/视/LLM/TTS 调用次数。"""
return (
case((UsageEvent.billing_key == "seedream", 1), else_=0),
case((UsageEvent.billing_key.like("seedance%"), 1), else_=0),
case((UsageEvent.billing_key == "llm_chat", 1), else_=0),
case((UsageEvent.billing_key == "tts", 1), else_=0),
)
def _empty_usage_summary() -> dict[str, int]:
return {
"charge_fen": 0,
"tokens": 0,
"calls": 0,
"image_gens": 0,
"video_gens": 0,
"llm_calls": 0,
"tts_gens": 0,
}
def _row_to_usage_summary(row: Any) -> dict[str, int]:
return {
"charge_fen": int(row[0] or 0),
"tokens": int(row[1] or 0),
"calls": int(row[2] or 0),
"image_gens": int(row[3] or 0),
"video_gens": int(row[4] or 0),
"llm_calls": int(row[5] or 0),
"tts_gens": int(row[6] or 0),
}
def _single_shot_billing_key_filter():
"""单次调用类 billing_key:seedance* / seedream。"""
clauses = [UsageEvent.billing_key.like(f"{p}%") for p in _SINGLE_SHOT_BILLING_KEY_PREFIXES]
if _SINGLE_SHOT_BILLING_KEYS:
clauses.append(UsageEvent.billing_key.in_(tuple(_SINGLE_SHOT_BILLING_KEYS)))
return or_(*clauses)
def _deduped_usage_event_ids_subq():
"""
仅对单次调用类 key 按 task_run+billing_key 保留最早一行,避免并发双记抬高汇总。
llm_chat / tts 等同 key 多行全部保留;无 task_run_id 的孤儿行全部保留。
"""
single_shot = _single_shot_billing_key_filter()
ranked = (
select(
UsageEvent.id.label("id"),
func.row_number()
.over(
partition_by=(UsageEvent.task_run_id, UsageEvent.billing_key),
order_by=UsageEvent.id.asc(),
)
.label("rn"),
)
.where(UsageEvent.task_run_id.is_not(None), single_shot)
.subquery()
)
deduped_single = select(ranked.c.id).where(ranked.c.rn == 1)
keep_multi = select(UsageEvent.id).where(
UsageEvent.task_run_id.is_not(None),
~single_shot,
)
orphans = select(UsageEvent.id).where(UsageEvent.task_run_id.is_(None))
return union_all(deduped_single, keep_multi, orphans).subquery()
async def _sum_task_charged_fen(
db: AsyncSession,
*,
project_ids: list[int] | None = None,
drama_project_ids: list[int] | None = None,
) -> dict[int, int]:
"""按任务实扣汇总(钱包结算口径)。"""
from app.models_tasks import TaskRun
if project_ids is not None:
if not project_ids:
return {}
rows = (
await db.execute(
select(
TaskRun.project_id,
func.coalesce(func.sum(TaskRun.billing_charged_fen), 0),
)
.where(TaskRun.project_id.in_(project_ids))
.group_by(TaskRun.project_id)
)
).all()
return {int(r[0]): int(r[1] or 0) for r in rows if r[0] is not None}
if drama_project_ids is not None:
if not drama_project_ids:
return {}
rows = (
await db.execute(
select(
TaskRun.drama_project_id,
func.coalesce(func.sum(TaskRun.billing_charged_fen), 0),
)
.where(TaskRun.drama_project_id.in_(drama_project_ids))
.group_by(TaskRun.drama_project_id)
)
).all()
return {int(r[0]): int(r[1] or 0) for r in rows if r[0] is not None}
return {}
async def _sum_orphan_usage_charge_fen(
db: AsyncSession,
*,
scope_col,
scope_ids: list[int],
) -> dict[int, int]:
"""无 task_run_id 的用量扣费(未走 TaskRun 结算的历史/旁路行)。"""
if not scope_ids:
return {}
rows = (
await db.execute(
select(
scope_col,
func.coalesce(func.sum(UsageEvent.charge_fen), 0),
)
.where(scope_col.in_(scope_ids), UsageEvent.task_run_id.is_(None))
.group_by(scope_col)
)
).all()
return {int(r[0]): int(r[1] or 0) for r in rows if r[0] is not None}
async def _aggregate_usage_by_scope(
db: AsyncSession,
*,
scope_col,
scope_ids: list[int],
project_ids: list[int] | None = None,
drama_project_ids: list[int] | None = None,
) -> dict[int, dict[str, Any]]:
"""按项目/漫剧项目聚合去重后的用量行;扣费用任务实扣+孤儿用量。"""
image_case, video_case, llm_case, tts_case = _usage_billing_cases()
empty = _empty_usage_summary()
if not scope_ids:
return {}
deduped = _deduped_usage_event_ids_subq()
result = await db.execute(
select(
scope_col,
func.coalesce(func.sum(UsageEvent.charge_fen), 0),
func.coalesce(func.sum(UsageEvent.total_tokens), 0),
func.count(UsageEvent.id),
func.coalesce(func.sum(image_case), 0),
func.coalesce(func.sum(video_case), 0),
func.coalesce(func.sum(llm_case), 0),
func.coalesce(func.sum(tts_case), 0),
)
.where(scope_col.in_(scope_ids), UsageEvent.id.in_(select(deduped.c.id)))
.group_by(scope_col)
)
out: dict[int, dict[str, Any]] = {pid: dict(empty) for pid in scope_ids}
for row in result.all():
out[int(row[0])] = _row_to_usage_summary(row[1:])
charged_map = await _sum_task_charged_fen(
db, project_ids=project_ids, drama_project_ids=drama_project_ids
)
orphan_map = await _sum_orphan_usage_charge_fen(db, scope_col=scope_col, scope_ids=scope_ids)
for pid in scope_ids:
if pid in charged_map or pid in orphan_map:
out[pid]["charge_fen"] = int(charged_map.get(pid, 0)) + int(orphan_map.get(pid, 0))
return out
async def aggregate_usage_summary(
db: AsyncSession,
*,
project_id: int | None = None,
drama_project_id: int | None = None,
project_ids: list[int] | None = None,
drama_project_ids: list[int] | None = None,
) -> dict[str, Any] | dict[int, dict[str, Any]]:
"""
项目级用量汇总。
- 单项目:传 project_id 或 drama_project_id,返回一条 dict
- 批量:传 project_ids 或 drama_project_ids,返回 {id: dict}
扣费 = TaskRun 实扣 + 无任务孤儿用量;调用与 token 按去重后的 usage 行统计。
"""
empty = _empty_usage_summary()
if project_ids is not None:
return await _aggregate_usage_by_scope(
db,
scope_col=UsageEvent.project_id,
scope_ids=project_ids,
project_ids=project_ids,
)
if drama_project_ids is not None:
return await _aggregate_usage_by_scope(
db,
scope_col=UsageEvent.drama_project_id,
scope_ids=drama_project_ids,
drama_project_ids=drama_project_ids,
)
if project_id is not None:
batch = await aggregate_usage_summary(db, project_ids=[project_id])
return batch.get(project_id, dict(empty))
if drama_project_id is not None:
batch = await aggregate_usage_summary(db, drama_project_ids=[drama_project_id])
return batch.get(drama_project_id, dict(empty))
return dict(empty)