cpa-plugin/internal/repository/sqlite_summary.go

package repository

import (
	"context"
	"database/sql"
	"fmt"
	"time"

	"billing/internal/collection"
)

const usageDashboardUsersSQL = `
WITH today AS (
    SELECT d.managed_key_id, COUNT(*) AS requests,
           COALESCE(SUM(u.input_tokens),0) AS input_tokens,
           COALESCE(SUM(u.output_tokens),0) AS output_tokens,
           COALESCE(SUM(u.total_tokens),0) AS total_tokens,
           COALESCE(SUM(u.cost_micros),0) AS cost_micros
    FROM request_detail_index d INDEXED BY idx_request_detail_time
    LEFT JOIN usage_records u ON u.id=d.usage_id
    WHERE d.requested_at>=?
    GROUP BY d.managed_key_id
), dashboard_keys AS (
    SELECT id, name FROM managed_keys WHERE status <> 'archived' AND show_in_stats=1
    UNION ALL
    SELECT t.managed_key_id, '未识别'
    FROM today t LEFT JOIN managed_keys k ON k.id=t.managed_key_id
    WHERE k.id IS NULL
)
SELECT k.id, k.name,
       COALESCE(t.requests,0), COALESCE(t.input_tokens,0), COALESCE(t.output_tokens,0),
       COALESCE(t.total_tokens,0), COALESCE(t.cost_micros,0),
       (SELECT recent.requested_at FROM request_detail_index recent
        WHERE recent.managed_key_id=k.id
        ORDER BY recent.requested_at DESC, recent.id DESC LIMIT 1)
FROM dashboard_keys k
LEFT JOIN today t ON t.managed_key_id=k.id
ORDER BY COALESCE(t.total_tokens,0) DESC, k.name COLLATE NOCASE`

func (r *SQLiteUsageRepository) UsageDashboard(ctx context.Context, today time.Time, days int) (collection.UsageDashboard, error) {
	if days < 1 {
		days = 7
	}
	start := time.Date(today.Year(), today.Month(), today.Day(), 0, 0, 0, 0, today.Location())
	dashboard := collection.UsageDashboard{}
	if err := r.readDB.QueryRowContext(ctx, `
SELECT COUNT(*), COALESCE(SUM(u.input_tokens),0), COALESCE(SUM(u.output_tokens),0),
       COALESCE(SUM(u.total_tokens),0), COALESCE(SUM(u.cost_micros),0)
FROM request_detail_index d LEFT JOIN usage_records u ON u.id=d.usage_id
WHERE d.requested_at>=?`, formatTime(start)).Scan(&dashboard.Today.Requests, &dashboard.Today.InputTokens, &dashboard.Today.OutputTokens, &dashboard.Today.TotalTokens, &dashboard.Today.CostMicros); err != nil {
		return dashboard, fmt.Errorf("汇总今日用量: %w", err)
	}
	rows, err := r.readDB.QueryContext(ctx, usageDashboardUsersSQL, formatTime(start))
	if err != nil {
		return dashboard, fmt.Errorf("汇总用户用量: %w", err)
	}
	for rows.Next() {
		var item collection.UserUsageSummary
		var last sql.NullString
		if err := rows.Scan(&item.KeyID, &item.KeyAlias, &item.Today.Requests, &item.Today.InputTokens, &item.Today.OutputTokens, &item.Today.TotalTokens, &item.Today.CostMicros, &last); err != nil {
			_ = rows.Close()
			return dashboard, fmt.Errorf("读取用户用量汇总: %w", err)
		}
		if last.Valid {
			parsed, parseErr := time.Parse(time.RFC3339Nano, last.String)
			if parseErr == nil {
				item.LastUsedAt = &parsed
			}
		}
		dashboard.Users = append(dashboard.Users, item)
	}
	_ = rows.Close()

	dailyStart := start.AddDate(0, 0, -(days - 1))
	rows, err = r.readDB.QueryContext(ctx, `
SELECT DATE(d.requested_at, '+8 hours'), COALESCE(SUM(u.total_tokens),0), COALESCE(SUM(u.cost_micros),0)
FROM request_detail_index d LEFT JOIN usage_records u ON u.id=d.usage_id
WHERE d.requested_at>=? GROUP BY DATE(d.requested_at, '+8 hours') ORDER BY 1`, formatTime(dailyStart))
	if err != nil {
		return dashboard, fmt.Errorf("汇总每日用量: %w", err)
	}
	byDate := make(map[string]int64)
	costByDate := make(map[string]int64)
	for rows.Next() {
		var date string
		var tokens, costMicros int64
		if err := rows.Scan(&date, &tokens, &costMicros); err != nil {
			_ = rows.Close()
			return dashboard, fmt.Errorf("读取每日用量: %w", err)
		}
		byDate[date] = tokens
		costByDate[date] = costMicros
	}
	_ = rows.Close()
	for index := 0; index < days; index++ {
		date := dailyStart.AddDate(0, 0, index)
		key := date.Format("2006-01-02")
		dashboard.Days = append(dashboard.Days, collection.DailyUsageSummary{Date: date, TotalTokens: byDate[key], CostMicros: costByDate[key]})
	}

	rows, err = r.readDB.QueryContext(ctx, `
SELECT d.managed_key_id, DATE(d.requested_at, '+8 hours'),
       COALESCE(SUM(u.total_tokens),0), COALESCE(SUM(u.cost_micros),0)
FROM request_detail_index d LEFT JOIN usage_records u ON u.id=d.usage_id
WHERE d.requested_at>=?
GROUP BY d.managed_key_id, DATE(d.requested_at, '+8 hours')`, formatTime(dailyStart))
	if err != nil {
		return dashboard, fmt.Errorf("汇总用户每日用量: %w", err)
	}
	userDays := make(map[string]map[string]collection.DailyUsageSummary)
	for rows.Next() {
		var keyID, date string
		var tokens, costMicros int64
		if err := rows.Scan(&keyID, &date, &tokens, &costMicros); err != nil {
			_ = rows.Close()
			return dashboard, fmt.Errorf("读取用户每日用量: %w", err)
		}
		if userDays[keyID] == nil {
			userDays[keyID] = make(map[string]collection.DailyUsageSummary)
		}
		userDays[keyID][date] = collection.DailyUsageSummary{TotalTokens: tokens, CostMicros: costMicros}
	}
	_ = rows.Close()
	for index := range dashboard.Users {
		for dayIndex := 0; dayIndex < days; dayIndex++ {
			date := dailyStart.AddDate(0, 0, dayIndex)
			key := date.Format("2006-01-02")
			item := userDays[dashboard.Users[index].KeyID][key]
			item.Date = date
			dashboard.Users[index].Days = append(dashboard.Users[index].Days, item)
		}
	}
	return dashboard, nil
}