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"`
}