"""撈 cost 分布,找誰最貴。"""
import os, sys
ENV = "/volume1/homes/robertsu/coba/app/.env"
if os.path.exists(ENV):
    for l in open(ENV):
        if "=" in l and not l.startswith("#"):
            k, v = l.strip().split("=",1); os.environ.setdefault(k,v)
sys.path.insert(0,"/volume1/homes/robertsu/coba/app")

import asyncio
from datetime import datetime, timedelta, timezone
from sqlalchemy import select, func, desc
from app.database import AsyncSessionLocal
from app.models.agent_activity import AgentActivity

async def main():
    # reset 後系統是空的,撈不到資料 — 撈過去 7 天 (含 reset 前資料)
    async with AsyncSessionLocal() as db:
        total_24h = (await db.execute(
            select(func.sum(AgentActivity.cost_usd))
            .where(AgentActivity.created_at >= datetime.now(timezone.utc) - timedelta(hours=24),
                   AgentActivity.cost_usd.is_not(None))
        )).scalar() or 0
        total_7d = (await db.execute(
            select(func.sum(AgentActivity.cost_usd))
            .where(AgentActivity.created_at >= datetime.now(timezone.utc) - timedelta(days=7),
                   AgentActivity.cost_usd.is_not(None))
        )).scalar() or 0

        # by type 過去 7 天
        by_type = (await db.execute(
            select(AgentActivity.activity_type,
                   func.count().label("c"),
                   func.sum(AgentActivity.cost_usd).label("cost"),
                   func.avg(AgentActivity.cost_usd).label("avg_cost"))
            .where(AgentActivity.created_at >= datetime.now(timezone.utc) - timedelta(days=7))
            .group_by(AgentActivity.activity_type)
            .order_by(desc("cost"))
        )).all()

        # 最貴的單筆
        top_expensive = (await db.execute(
            select(AgentActivity)
            .where(AgentActivity.created_at >= datetime.now(timezone.utc) - timedelta(days=7),
                   AgentActivity.cost_usd.is_not(None))
            .order_by(desc(AgentActivity.cost_usd))
            .limit(10)
        )).scalars().all()

    print("="*70)
    print(" Cost 分析(注意:剛剛 reset 後資料為 0,以下是 reset 前的歷史)")
    print("="*70)
    print(f"  過去 24 小時總 cost: ${total_24h:.4f} ≈ NT${total_24h*32:.1f}")
    print(f"  過去 7 天總 cost  : ${total_7d:.4f} ≈ NT${total_7d*32:.1f}")
    print(f"\n=== 過去 7 天 by activity_type ===")
    for at, c, cost, avg in by_type:
        cost = cost or 0
        avg = avg or 0
        print(f"  {at:25s}  count={c:4d}  total=${cost:.4f}  avg=${avg:.5f}")

    print(f"\n=== 過去 7 天 最貴 10 筆 ===")
    for a in top_expensive:
        print(f"  ${a.cost_usd:.5f}  [{a.activity_type:20s}] {(a.summary or '')[:60]}")

asyncio.run(main())