use anyhow::Result;
use chrono::{DateTime, Utc};
use super::ToolAnalyticsRepository;
use crate::models::cli::{
ToolAgentUsageRow, ToolErrorRow, ToolExecutionRow, ToolStatsRow, ToolStatusBreakdownRow,
ToolSummaryRow,
};
impl ToolAnalyticsRepository {
pub async fn get_stats(
&self,
start: DateTime<Utc>,
end: DateTime<Utc>,
tool_filter: Option<&str>,
) -> Result<ToolStatsRow> {
if let Some(tool) = tool_filter {
let pattern = format!("%{}%", tool);
sqlx::query_as!(
ToolStatsRow,
r#"
SELECT
COUNT(DISTINCT tool_name)::bigint as "total_tools!",
COUNT(*)::bigint as "total_executions!",
COUNT(*) FILTER (WHERE status = 'success')::bigint as "successful!",
COUNT(*) FILTER (WHERE status = 'failed')::bigint as "failed!",
COUNT(*) FILTER (WHERE status = 'timeout')::bigint as "timeout!",
COALESCE(AVG(execution_time_ms)::float8, 0) as "avg_time!",
COALESCE(PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY execution_time_ms)::float8, 0) as "p95_time!"
FROM mcp_tool_executions
WHERE created_at >= $1 AND created_at < $2 AND tool_name ILIKE $3
"#,
start, end, pattern
)
.fetch_one(&*self.pool)
.await
.map_err(Into::into)
} else {
sqlx::query_as!(
ToolStatsRow,
r#"
SELECT
COUNT(DISTINCT tool_name)::bigint as "total_tools!",
COUNT(*)::bigint as "total_executions!",
COUNT(*) FILTER (WHERE status = 'success')::bigint as "successful!",
COUNT(*) FILTER (WHERE status = 'failed')::bigint as "failed!",
COUNT(*) FILTER (WHERE status = 'timeout')::bigint as "timeout!",
COALESCE(AVG(execution_time_ms)::float8, 0) as "avg_time!",
COALESCE(PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY execution_time_ms)::float8, 0) as "p95_time!"
FROM mcp_tool_executions
WHERE created_at >= $1 AND created_at < $2
"#,
start, end
)
.fetch_one(&*self.pool)
.await
.map_err(Into::into)
}
}
pub async fn tool_exists(
&self,
tool_name: &str,
start: DateTime<Utc>,
end: DateTime<Utc>,
) -> Result<i64> {
let pattern = format!("%{}%", tool_name);
let count = sqlx::query_scalar!(
r#"SELECT COUNT(*)::bigint as "count!" FROM mcp_tool_executions WHERE tool_name ILIKE $1 AND created_at >= $2 AND created_at < $3"#,
pattern,
start,
end
)
.fetch_one(&*self.pool)
.await?;
Ok(count)
}
pub async fn get_tool_summary(
&self,
tool_name: &str,
start: DateTime<Utc>,
end: DateTime<Utc>,
) -> Result<ToolSummaryRow> {
let pattern = format!("%{}%", tool_name);
sqlx::query_as!(
ToolSummaryRow,
r#"
SELECT
COUNT(*)::bigint as "total!",
COUNT(*) FILTER (WHERE status = 'success')::bigint as "successful!",
COUNT(*) FILTER (WHERE status = 'failed')::bigint as "failed!",
COUNT(*) FILTER (WHERE status = 'timeout')::bigint as "timeout!",
COALESCE(AVG(execution_time_ms)::float8, 0) as "avg_time!",
COALESCE(PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY execution_time_ms)::float8, 0) as "p95_time!"
FROM mcp_tool_executions
WHERE tool_name ILIKE $1 AND created_at >= $2 AND created_at < $3
"#,
pattern, start, end
)
.fetch_one(&*self.pool)
.await
.map_err(Into::into)
}
pub async fn get_status_breakdown(
&self,
tool_name: &str,
start: DateTime<Utc>,
end: DateTime<Utc>,
) -> Result<Vec<ToolStatusBreakdownRow>> {
let pattern = format!("%{}%", tool_name);
sqlx::query_as!(
ToolStatusBreakdownRow,
r#"
SELECT status as "status!", COUNT(*)::bigint as "status_count!"
FROM mcp_tool_executions
WHERE tool_name ILIKE $1 AND created_at >= $2 AND created_at < $3
GROUP BY status
ORDER BY 2 DESC
"#,
pattern,
start,
end
)
.fetch_all(&*self.pool)
.await
.map_err(Into::into)
}
pub async fn get_top_errors(
&self,
tool_name: &str,
start: DateTime<Utc>,
end: DateTime<Utc>,
) -> Result<Vec<ToolErrorRow>> {
let pattern = format!("%{}%", tool_name);
sqlx::query_as!(
ToolErrorRow,
r#"
SELECT
COALESCE(SUBSTRING(error_message FROM 1 FOR 100), 'Unknown error') as "error_msg",
COUNT(*)::bigint as "error_count!"
FROM mcp_tool_executions
WHERE tool_name ILIKE $1 AND created_at >= $2 AND created_at < $3 AND status = 'failed'
GROUP BY SUBSTRING(error_message FROM 1 FOR 100)
ORDER BY 2 DESC
LIMIT 10
"#,
pattern,
start,
end
)
.fetch_all(&*self.pool)
.await
.map_err(Into::into)
}
pub async fn get_usage_by_agent(
&self,
tool_name: &str,
start: DateTime<Utc>,
end: DateTime<Utc>,
) -> Result<Vec<ToolAgentUsageRow>> {
let pattern = format!("%{}%", tool_name);
sqlx::query_as!(
ToolAgentUsageRow,
r#"
SELECT
COALESCE(at.agent_name, CASE WHEN mte.task_id IS NULL THEN 'Direct Call' ELSE 'Unlinked Task' END) as "agent_name",
COUNT(*)::bigint as "usage_count!"
FROM mcp_tool_executions mte
LEFT JOIN agent_tasks at ON at.task_id = mte.task_id
WHERE mte.tool_name ILIKE $1 AND mte.created_at >= $2 AND mte.created_at < $3
GROUP BY COALESCE(at.agent_name, CASE WHEN mte.task_id IS NULL THEN 'Direct Call' ELSE 'Unlinked Task' END)
ORDER BY 2 DESC
LIMIT 10
"#,
pattern, start, end
)
.fetch_all(&*self.pool)
.await
.map_err(Into::into)
}
pub async fn get_executions_for_trends(
&self,
start: DateTime<Utc>,
end: DateTime<Utc>,
tool_filter: Option<&str>,
) -> Result<Vec<ToolExecutionRow>> {
if let Some(tool) = tool_filter {
let pattern = format!("%{}%", tool);
sqlx::query_as!(
ToolExecutionRow,
r#"
SELECT
created_at as "created_at!",
status,
execution_time_ms
FROM mcp_tool_executions
WHERE created_at >= $1 AND created_at < $2 AND tool_name ILIKE $3
ORDER BY created_at
"#,
start,
end,
pattern
)
.fetch_all(&*self.pool)
.await
.map_err(Into::into)
} else {
sqlx::query_as!(
ToolExecutionRow,
r#"
SELECT
created_at as "created_at!",
status,
execution_time_ms
FROM mcp_tool_executions
WHERE created_at >= $1 AND created_at < $2
ORDER BY created_at
"#,
start,
end
)
.fetch_all(&*self.pool)
.await
.map_err(Into::into)
}
}
}