package stats
import (
"context"
"strings"
"go.bigb.es/auxilia/culpa"
"sourcecraft.dev/bigbes/lethe/internal/domain/session"
"sourcecraft.dev/bigbes/lethe/internal/platform/database"
)
// ToolRollup holds aggregate stats for one tool across the requested range.
// DailySparkline has one entry per daily-window bucket (oldest first),
// containing the turn count for that bucket.
type ToolRollup struct {
Tool string `json:"tool"`
Sessions int64 `json:"sessions"`
Turns int64 `json:"turns"`
TokensIn int64 `json:"tokens_in"`
TokensOut int64 `json:"tokens_out"`
DailySparkline []int64 `json:"daily_sparkline"`
}
// DailyBucket is one day in the daily time-series. PerTool maps tool name to
// turn count for that day.
type DailyBucket struct {
DateUnix int64 `json:"date_unix"`
PerTool map[string]int64 `json:"per_tool"`
}
// HeatmapCell is one day in the 84-cell activity heatmap.
type HeatmapCell struct {
DateUnix int64 `json:"date_unix"`
Count int64 `json:"count"`
}
// CwdRow is one entry in the top-cwd ranking.
type CwdRow struct {
Cwd string `json:"cwd"`
Count int64 `json:"count"`
}
// HourBucket is one hour-of-day bucket (0..23) with its turn count.
type HourBucket struct {
Hour int `json:"hour"`
Count int64 `json:"count"`
}
// HostRow is one entry in the host breakdown.
type HostRow struct {
Host string `json:"host"`
Count int64 `json:"count"`
}
// Stats is the single response bundling all six aggregations.
type Stats struct {
PerTool []ToolRollup `json:"per_tool"`
Daily []DailyBucket `json:"daily"`
Heatmap []HeatmapCell `json:"heatmap"`
TopCwd []CwdRow `json:"top_cwd"`
HourOfDay []HourBucket `json:"hour_of_day"`
HostSplit []HostRow `json:"host_split"`
}
// Filter controls which rows Stats includes.
// - RangeSince=nil means all time (no lower timestamp bound).
// - Now is injected for deterministic window calculations in tests; in
// production the handler injects time.Now().Unix().
type Filter struct {
Owner session.OwnerScope
RangeSince *int64 // nil = all
Now int64 // injected for determinism
}
// Repository is the SQL steward for the stats aggregation API.
type Repository struct {
Database *database.Database `inject:""`
}
// Init satisfies the steward Initer contract.
func (r *Repository) Init(_ context.Context) error { return nil }
// ownerClause returns the SQL fragment and arg (if any) for the owner scope,
// joining the given table alias. prefix is the "AND " string prepended when
// used in a WHERE continuation; connector is e.g. "WHERE " for the first clause.
// Returns an empty string and nil arg when AllOwners is set.
func ownerClause(alias string, owner session.OwnerScope) (clause string, arg any, hasArg bool) {
switch {
case owner.AllOwners:
return "", nil, false
case owner.SpecificOwner != nil:
return alias + ".owner = ?", *owner.SpecificOwner, true
default:
return alias + ".owner = ?", owner.User, true
}
}
// appendOwnerClause appends the owner WHERE/AND clause to the builder.
// sep should be " WHERE " for the first clause, " AND " for subsequent ones.
func appendOwnerClause(sb *strings.Builder, args *[]any, sep, alias string, owner session.OwnerScope) {
clause, arg, hasArg := ownerClause(alias, owner)
if clause == "" {
return
}
sb.WriteString(sep)
sb.WriteString(clause)
if hasArg {
*args = append(*args, arg)
}
}
// sparklineCap is the maximum number of daily buckets in a ToolRollup sparkline.
const sparklineCap = 60
// Stats runs six queries against the turns/sessions tables and returns a
// fully-populated Stats struct. All returned slices are non-nil; missing
// time-window slots are filled with zero values.
func (r *Repository) Stats(ctx context.Context, f Filter) (*Stats, error) {
// Determine daily window size from range.
// dailyDays is the raw request days (may exceed sparklineCap).
// For all-time (RangeSince=nil) we use sparklineCap so Daily has a
// bounded, useful window.
var dailyDays int
if f.RangeSince != nil {
dailyDays = int((f.Now - *f.RangeSince) / 86400)
} else {
dailyDays = sparklineCap
}
// sparklineDays is the sparkline length, capped at sparklineCap.
sparklineDays := dailyDays
if sparklineDays > sparklineCap {
sparklineDays = sparklineCap
}
// ── 1. PerTool rollup ───────────────────────────────────────────────────
type toolRow struct {
Tool string `db:"tool"`
Sessions int64 `db:"sessions"`
Turns int64 `db:"turns"`
TokensIn int64 `db:"tokens_in"`
TokensOut int64 `db:"tokens_out"`
}
var toolRows []toolRow
{
var sb strings.Builder
var args []any
sb.WriteString(`
SELECT
t.tool,
COUNT(DISTINCT s.owner || '|' || s.tool || '|' || s.host || '|' || s.session_id) AS sessions,
COUNT(t.turn_id) AS turns,
COALESCE(SUM(t.tokens_in), 0) AS tokens_in,
COALESCE(SUM(t.tokens_out), 0) AS tokens_out
FROM turns t
JOIN sessions s ON s.owner = t.owner AND s.tool = t.tool AND s.host = t.host AND s.session_id = t.session_id
WHERE 1=1`)
appendOwnerClause(&sb, &args, " AND ", "t", f.Owner)
if f.RangeSince != nil {
sb.WriteString(" AND t.timestamp >= ?")
args = append(args, *f.RangeSince)
}
sb.WriteString(" GROUP BY t.tool ORDER BY turns DESC")
toolRows = make([]toolRow, 0)
if err := r.Database.DB.SelectContext(ctx, &toolRows, sb.String(), args...); err != nil {
return nil, culpa.WithCode(culpa.Wrap(err, "stats per_tool"), "DB_QUERY")
}
}
// ── 2. Daily time-series ────────────────────────────────────────────────
// The daily window spans the full requested range (not capped at 60).
dailyWindow := DailyWindow(f.Now, dailyDays)
var dailyDBRows []dailyBucketDBRow
{
var sb strings.Builder
var args []any
// Bucket by UTC midnight: (timestamp / 86400) * 86400
sb.WriteString(`
SELECT
(t.timestamp / 86400) * 86400 AS date_unix,
t.tool,
COUNT(t.turn_id) AS turns
FROM turns t
JOIN sessions s ON s.owner = t.owner AND s.tool = t.tool AND s.host = t.host AND s.session_id = t.session_id
WHERE 1=1`)
appendOwnerClause(&sb, &args, " AND ", "t", f.Owner)
if f.RangeSince != nil {
sb.WriteString(" AND t.timestamp >= ?")
args = append(args, *f.RangeSince)
}
sb.WriteString(" GROUP BY date_unix, t.tool ORDER BY date_unix ASC")
dailyDBRows = make([]dailyBucketDBRow, 0)
if err := r.Database.DB.SelectContext(ctx, &dailyDBRows, sb.String(), args...); err != nil {
return nil, culpa.WithCode(culpa.Wrap(err, "stats daily"), "DB_QUERY")
}
}
// Convert DB rows to DailyBucket slice (sparse), then fill the window.
sparseDaily := buildSparseDaily(dailyDBRows)
filledDaily := FillDaily(dailyWindow, sparseDaily)
// ── 3. Heatmap (always 84 cells regardless of range) ───────────────────
heatmapWindow := HeatmapWindow(f.Now)
heatmapStart := heatmapWindow[0]
type heatmapDBRow struct {
DateUnix int64 `db:"date_unix"`
Count int64 `db:"count"`
}
var heatmapDBRows []heatmapDBRow
{
var sb strings.Builder
var args []any
sb.WriteString(`
SELECT
(t.timestamp / 86400) * 86400 AS date_unix,
COUNT(t.turn_id) AS count
FROM turns t
JOIN sessions s ON s.owner = t.owner AND s.tool = t.tool AND s.host = t.host AND s.session_id = t.session_id
WHERE t.timestamp >= ?`)
args = append(args, heatmapStart)
appendOwnerClause(&sb, &args, " AND ", "t", f.Owner)
sb.WriteString(" GROUP BY date_unix ORDER BY date_unix ASC")
heatmapDBRows = make([]heatmapDBRow, 0)
if err := r.Database.DB.SelectContext(ctx, &heatmapDBRows, sb.String(), args...); err != nil {
return nil, culpa.WithCode(culpa.Wrap(err, "stats heatmap"), "DB_QUERY")
}
}
// Build heatmap: left-join heatmap DB rows onto the window.
heatmapByDate := make(map[int64]int64, len(heatmapDBRows))
for _, row := range heatmapDBRows {
heatmapByDate[row.DateUnix] = row.Count
}
heatmap := make([]HeatmapCell, len(heatmapWindow))
for i, ts := range heatmapWindow {
heatmap[i] = HeatmapCell{DateUnix: ts, Count: heatmapByDate[ts]}
}
// ── 4. TopCwd (capped at 20) ────────────────────────────────────────────
var topCwd []CwdRow
{
var sb strings.Builder
var args []any
sb.WriteString(`
SELECT
s.working_dir AS cwd,
COUNT(t.turn_id) AS count
FROM turns t
JOIN sessions s ON s.owner = t.owner AND s.tool = t.tool AND s.host = t.host AND s.session_id = t.session_id
WHERE s.working_dir IS NOT NULL`)
appendOwnerClause(&sb, &args, " AND ", "t", f.Owner)
if f.RangeSince != nil {
sb.WriteString(" AND t.timestamp >= ?")
args = append(args, *f.RangeSince)
}
sb.WriteString(" GROUP BY s.working_dir ORDER BY count DESC LIMIT 20")
topCwd = make([]CwdRow, 0)
if err := r.Database.DB.SelectContext(ctx, &topCwd, sb.String(), args...); err != nil {
return nil, culpa.WithCode(culpa.Wrap(err, "stats top_cwd"), "DB_QUERY")
}
}
// ── 5. HourOfDay ────────────────────────────────────────────────────────
type hourDBRow struct {
Hour int `db:"hour"`
Count int64 `db:"count"`
}
var hourDBRows []hourDBRow
{
var sb strings.Builder
var args []any
// SQLite: strftime('%H', ts, 'unixepoch') returns the UTC hour as "00"–"23".
// CAST to INTEGER gives 0..23.
sb.WriteString(`
SELECT
CAST(strftime('%H', t.timestamp, 'unixepoch') AS INTEGER) AS hour,
COUNT(t.turn_id) AS count
FROM turns t
JOIN sessions s ON s.owner = t.owner AND s.tool = t.tool AND s.host = t.host AND s.session_id = t.session_id
WHERE 1=1`)
appendOwnerClause(&sb, &args, " AND ", "t", f.Owner)
if f.RangeSince != nil {
sb.WriteString(" AND t.timestamp >= ?")
args = append(args, *f.RangeSince)
}
sb.WriteString(" GROUP BY hour ORDER BY hour ASC")
hourDBRows = make([]hourDBRow, 0)
if err := r.Database.DB.SelectContext(ctx, &hourDBRows, sb.String(), args...); err != nil {
return nil, culpa.WithCode(culpa.Wrap(err, "stats hour_of_day"), "DB_QUERY")
}
}
// Fill 24-slot window.
hourSlots := HourWindow()
for _, row := range hourDBRows {
if row.Hour >= 0 && row.Hour < 24 {
hourSlots[row.Hour].Count = row.Count
}
}
// ── 6. HostSplit ─────────────────────────────────────────────────────────
var hostSplit []HostRow
{
var sb strings.Builder
var args []any
sb.WriteString(`
SELECT
s.host,
COUNT(t.turn_id) AS count
FROM turns t
JOIN sessions s ON s.owner = t.owner AND s.tool = t.tool AND s.host = t.host AND s.session_id = t.session_id
WHERE 1=1`)
appendOwnerClause(&sb, &args, " AND ", "t", f.Owner)
if f.RangeSince != nil {
sb.WriteString(" AND t.timestamp >= ?")
args = append(args, *f.RangeSince)
}
sb.WriteString(" GROUP BY s.host ORDER BY count DESC")
hostSplit = make([]HostRow, 0)
if err := r.Database.DB.SelectContext(ctx, &hostSplit, sb.String(), args...); err != nil {
return nil, culpa.WithCode(culpa.Wrap(err, "stats host_split"), "DB_QUERY")
}
}
// ── Assemble PerTool with sparklines ─────────────────────────────────────
// Sparklines are capped at sparklineCap buckets (most recent).
sparklineSlice := filledDaily
if len(filledDaily) > sparklineDays+1 {
sparklineSlice = filledDaily[len(filledDaily)-(sparklineDays+1):]
}
perToolOut := make([]ToolRollup, 0, len(toolRows))
for _, tr := range toolRows {
sparkline := make([]int64, len(sparklineSlice))
for i, b := range sparklineSlice {
sparkline[i] = b.PerTool[tr.Tool]
}
perToolOut = append(perToolOut, ToolRollup{
Tool: tr.Tool,
Sessions: tr.Sessions,
Turns: tr.Turns,
TokensIn: tr.TokensIn,
TokensOut: tr.TokensOut,
DailySparkline: sparkline,
})
}
return &Stats{
PerTool: perToolOut,
Daily: filledDaily,
Heatmap: heatmap,
TopCwd: topCwd,
HourOfDay: hourSlots,
HostSplit: hostSplit,
}, nil
}
// buildSparseDaily converts flat DB rows (date_unix, tool, turns) into
// DailyBucket values. Rows are grouped by date_unix.
func buildSparseDaily(rows []dailyBucketDBRow) []DailyBucket {
if len(rows) == 0 {
return []DailyBucket{}
}
// Group by date.
byDate := make(map[int64]map[string]int64)
for _, r := range rows {
if byDate[r.DateUnix] == nil {
byDate[r.DateUnix] = make(map[string]int64)
}
byDate[r.DateUnix][r.Tool] += r.Turns
}
out := make([]DailyBucket, 0, len(byDate))
for dateUnix, perTool := range byDate {
out = append(out, DailyBucket{DateUnix: dateUnix, PerTool: perTool})
}
return out
}
// dailyBucketDBRow is the local scan target for the daily query.
type dailyBucketDBRow struct {
DateUnix int64 `db:"date_unix"`
Tool string `db:"tool"`
Turns int64 `db:"turns"`
}