systemprompt-analytics 0.8.0

//! Tool execution leaderboard queries (filtered + unfiltered) for the
//! analytics CLI surface.

use crate::Result;
use chrono::{DateTime, Utc};

use super::ToolAnalyticsRepository;
use crate::models::cli::ToolListRow;

#[derive(Debug)]
pub struct ToolListParams<'a> {
    pub start: DateTime<Utc>,
    pub end: DateTime<Utc>,
    pub limit: i64,
    pub server_filter: Option<&'a str>,
    pub sort_order: &'a str,
}

impl ToolAnalyticsRepository {
    pub async fn list_tools(&self, params: ToolListParams<'_>) -> Result<Vec<ToolListRow>> {
        if let Some(server) = params.server_filter {
            let pattern = format!("%{}%", server);
            self.list_tools_with_filter(&params, &pattern).await
        } else {
            self.list_tools_unfiltered(&params).await
        }
    }

    async fn list_tools_with_filter(
        &self,
        params: &ToolListParams<'_>,
        pattern: &str,
    ) -> Result<Vec<ToolListRow>> {
        let ToolListParams {
            start,
            end,
            limit,
            sort_order,
            ..
        } = *params;
        match sort_order {
            "success_rate" => sqlx::query_as!(
                ToolListRow,
                r#"
                SELECT
                    tool_name as "tool_name!",
                    server_name as "server_name!",
                    COUNT(*)::bigint as "execution_count!",
                    COUNT(*) FILTER (WHERE status = 'success')::bigint as "success_count!",
                    COALESCE(AVG(execution_time_ms)::float8, 0) as "avg_time!",
                    MAX(created_at) as "last_used!"
                FROM mcp_tool_executions
                WHERE created_at >= $1 AND created_at < $2 AND server_name ILIKE $3
                GROUP BY tool_name, server_name
                ORDER BY CASE WHEN COUNT(*) > 0
                    THEN COUNT(*) FILTER (WHERE status = 'success')::float / COUNT(*)::float
                    ELSE 0 END DESC
                LIMIT $4
                "#,
                start,
                end,
                pattern,
                limit
            )
            .fetch_all(&*self.pool)
            .await
            .map_err(Into::into),
            "avg_time" => sqlx::query_as!(
                ToolListRow,
                r#"
                SELECT
                    tool_name as "tool_name!",
                    server_name as "server_name!",
                    COUNT(*)::bigint as "execution_count!",
                    COUNT(*) FILTER (WHERE status = 'success')::bigint as "success_count!",
                    COALESCE(AVG(execution_time_ms)::float8, 0) as "avg_time!",
                    MAX(created_at) as "last_used!"
                FROM mcp_tool_executions
                WHERE created_at >= $1 AND created_at < $2 AND server_name ILIKE $3
                GROUP BY tool_name, server_name
                ORDER BY COALESCE(AVG(execution_time_ms), 0) DESC
                LIMIT $4
                "#,
                start,
                end,
                pattern,
                limit
            )
            .fetch_all(&*self.pool)
            .await
            .map_err(Into::into),
            _ => sqlx::query_as!(
                ToolListRow,
                r#"
                SELECT
                    tool_name as "tool_name!",
                    server_name as "server_name!",
                    COUNT(*)::bigint as "execution_count!",
                    COUNT(*) FILTER (WHERE status = 'success')::bigint as "success_count!",
                    COALESCE(AVG(execution_time_ms)::float8, 0) as "avg_time!",
                    MAX(created_at) as "last_used!"
                FROM mcp_tool_executions
                WHERE created_at >= $1 AND created_at < $2 AND server_name ILIKE $3
                GROUP BY tool_name, server_name
                ORDER BY COUNT(*) DESC
                LIMIT $4
                "#,
                start,
                end,
                pattern,
                limit
            )
            .fetch_all(&*self.pool)
            .await
            .map_err(Into::into),
        }
    }

    async fn list_tools_unfiltered(&self, params: &ToolListParams<'_>) -> Result<Vec<ToolListRow>> {
        let ToolListParams {
            start,
            end,
            limit,
            sort_order,
            ..
        } = *params;
        match sort_order {
            "success_rate" => sqlx::query_as!(
                ToolListRow,
                r#"
                SELECT
                    tool_name as "tool_name!",
                    server_name as "server_name!",
                    COUNT(*)::bigint as "execution_count!",
                    COUNT(*) FILTER (WHERE status = 'success')::bigint as "success_count!",
                    COALESCE(AVG(execution_time_ms)::float8, 0) as "avg_time!",
                    MAX(created_at) as "last_used!"
                FROM mcp_tool_executions
                WHERE created_at >= $1 AND created_at < $2
                GROUP BY tool_name, server_name
                ORDER BY CASE WHEN COUNT(*) > 0
                    THEN COUNT(*) FILTER (WHERE status = 'success')::float / COUNT(*)::float
                    ELSE 0 END DESC
                LIMIT $3
                "#,
                start,
                end,
                limit
            )
            .fetch_all(&*self.pool)
            .await
            .map_err(Into::into),
            "avg_time" => sqlx::query_as!(
                ToolListRow,
                r#"
                SELECT
                    tool_name as "tool_name!",
                    server_name as "server_name!",
                    COUNT(*)::bigint as "execution_count!",
                    COUNT(*) FILTER (WHERE status = 'success')::bigint as "success_count!",
                    COALESCE(AVG(execution_time_ms)::float8, 0) as "avg_time!",
                    MAX(created_at) as "last_used!"
                FROM mcp_tool_executions
                WHERE created_at >= $1 AND created_at < $2
                GROUP BY tool_name, server_name
                ORDER BY COALESCE(AVG(execution_time_ms), 0) DESC
                LIMIT $3
                "#,
                start,
                end,
                limit
            )
            .fetch_all(&*self.pool)
            .await
            .map_err(Into::into),
            _ => sqlx::query_as!(
                ToolListRow,
                r#"
                SELECT
                    tool_name as "tool_name!",
                    server_name as "server_name!",
                    COUNT(*)::bigint as "execution_count!",
                    COUNT(*) FILTER (WHERE status = 'success')::bigint as "success_count!",
                    COALESCE(AVG(execution_time_ms)::float8, 0) as "avg_time!",
                    MAX(created_at) as "last_used!"
                FROM mcp_tool_executions
                WHERE created_at >= $1 AND created_at < $2
                GROUP BY tool_name, server_name
                ORDER BY COUNT(*) DESC
                LIMIT $3
                "#,
                start,
                end,
                limit
            )
            .fetch_all(&*self.pool)
            .await
            .map_err(Into::into),
        }
    }
}