freedakgmail
35 lines
1.4 KiB
SQL
35 lines
1.4 KiB
SQL
-- name: CreateUsageLog :one
|
|
INSERT INTO app_usage_logs (
|
|
app_id, user_id, dept_id, conversation_id, message_count,
|
|
prompt_tokens, completion_tokens, total_tokens, model_name,
|
|
estimated_cost, duration_ms, is_successful, error_message, client_type
|
|
) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)
|
|
RETURNING *;
|
|
|
|
-- name: GetRecentUsedApps :many
|
|
SELECT DISTINCT ON (a.id) a.*, c.name as category_name, l.created_at as last_used_at
|
|
FROM app_usage_logs l
|
|
JOIN applications a ON l.app_id = a.id
|
|
LEFT JOIN categories c ON a.category_id = c.id
|
|
WHERE l.user_id = $1
|
|
ORDER BY a.id, l.created_at DESC
|
|
LIMIT $2;
|
|
|
|
-- name: GetUserStats :one
|
|
SELECT
|
|
COUNT(*) as total_conversations,
|
|
COALESCE(SUM(total_tokens), 0) as total_tokens,
|
|
COALESCE(SUM(estimated_cost), 0) as total_cost
|
|
FROM app_usage_logs
|
|
WHERE user_id = $1
|
|
AND created_at >= $2;
|
|
|
|
-- name: GetOverviewStats :one
|
|
SELECT
|
|
(SELECT COUNT(*) FROM users WHERE status = 'active') as total_users,
|
|
(SELECT COUNT(*) FROM applications WHERE status = 'approved') as total_apps,
|
|
(SELECT COUNT(DISTINCT user_id) FROM app_usage_logs WHERE created_at >= $1) as active_users,
|
|
(SELECT COUNT(*) FROM app_usage_logs WHERE created_at >= $1) as total_conversations,
|
|
(SELECT COALESCE(SUM(total_tokens), 0) FROM app_usage_logs WHERE created_at >= $2) as monthly_tokens,
|
|
(SELECT COALESCE(SUM(estimated_cost), 0) FROM app_usage_logs WHERE created_at >= $2) as monthly_cost;
|