// Package project implements the read-only /api/v1/projects aggregation API.
// It groups sessions by working_dir and returns per-project statistics
// computed in a single SQL round-trip. The owner-scope resolution rules
// are identical to the session package: AllOwners / SpecificOwner / default.
package project

import (
	"context"
	"sort"
	"strings"

	"go.bigb.es/auxilia/culpa"

	"sourcecraft.dev/bigbes/lethe/internal/domain/session"
	"sourcecraft.dev/bigbes/lethe/internal/platform/database"
)

// Project is the aggregated row returned per distinct working_dir. Hosts and
// Tools are populated by post-query deduplication of the comma-joined strings
// that SQLite's GROUP_CONCAT returns, so the JSON shape is always a clean
// sorted array.
type Project struct {
	Cwd            string   `db:"cwd"              json:"cwd"`
	Sessions       int64    `db:"sessions"         json:"sessions"`
	TurnCount      int64    `db:"turn_count"       json:"turn_count"`
	TokensInTotal  int64    `db:"tokens_in_total"  json:"tokens_in_total"`
	TokensOutTotal int64    `db:"tokens_out_total" json:"tokens_out_total"`
	LastActive     int64    `db:"last_active"      json:"last_active"`
	TopTool        string   `db:"top_tool"         json:"top_tool"`
	Hosts          []string `db:"-"                json:"hosts"`
	Tools          []string `db:"-"                json:"tools"`

	// rawHosts and rawTools hold the comma-joined GROUP_CONCAT output before
	// the post-query split+dedupe step.
	rawHosts string `db:"raw_hosts"`
	rawTools string `db:"raw_tools"`
}

// ListFilter aggregates every option List supports.
type ListFilter struct {
	Owner  session.OwnerScope
	Since  *int64
	Limit  int
	Offset int
}

// Repository is the SQL steward for the project aggregation API. It is
// stateless beyond its injected dependencies.
type Repository struct {
	Database *database.Database `inject:""`
}

// Init satisfies the steward Initer contract.
func (r *Repository) Init(_ context.Context) error { return nil }

// List runs the aggregation query and returns one Project row per distinct
// non-NULL working_dir that the owner scope allows. An empty result returns a
// non-nil zero-length slice so JSON-encoding produces [] rather than null.
//
// The SQL is a single SELECT…GROUP BY query that joins sessions to turns. The
// correlated subquery for top_tool picks the tool with the highest turn count
// for the cwd, breaking ties by smallest tool name (ORDER BY tool ASC).
//
// Ordering is MAX(ended_at) DESC (most-recently-active project first).
func (r *Repository) List(ctx context.Context, f ListFilter) ([]Project, error) {
	var (
		sb   strings.Builder
		args []any
	)

	sb.WriteString(`
SELECT
    s.working_dir AS cwd,
    COUNT(DISTINCT s.owner || '|' || s.tool || '|' || s.host || '|' || s.session_id) AS sessions,
    COALESCE(COUNT(t.turn_id), 0) AS turn_count,
    COALESCE(SUM(t.tokens_in), 0) AS tokens_in_total,
    COALESCE(SUM(t.tokens_out), 0) AS tokens_out_total,
    MAX(s.ended_at) AS last_active,
    GROUP_CONCAT(DISTINCT s.host) AS raw_hosts,
    GROUP_CONCAT(DISTINCT s.tool) AS raw_tools,
    COALESCE(
        (
        SELECT sub.tool
        FROM (
            SELECT t2.tool, COUNT(*) AS cnt
            FROM sessions s2
            LEFT JOIN turns t2 ON t2.owner = s2.owner AND t2.tool = s2.tool
                              AND t2.host = s2.host AND t2.session_id = s2.session_id
            WHERE s2.working_dir = s.working_dir`)

	// Owner clause in the top_tool correlated subquery mirrors outer scope.
	switch {
	case f.Owner.AllOwners:
		// no additional clause
	case f.Owner.SpecificOwner != nil:
		sb.WriteString(` AND s2.owner = ?`)
		args = append(args, *f.Owner.SpecificOwner)
	default:
		sb.WriteString(` AND s2.owner = ?`)
		args = append(args, f.Owner.User)
	}

	sb.WriteString(`
            GROUP BY t2.tool
            ORDER BY cnt DESC, t2.tool ASC
            LIMIT 1
        ) sub
        ), '') AS top_tool
FROM sessions s
LEFT JOIN turns t ON t.owner = s.owner AND t.tool = s.tool
                 AND t.host = s.host AND t.session_id = s.session_id
WHERE s.working_dir IS NOT NULL`)

	// Owner clause for the outer query.
	switch {
	case f.Owner.AllOwners:
		// no additional clause
	case f.Owner.SpecificOwner != nil:
		sb.WriteString(` AND s.owner = ?`)
		args = append(args, *f.Owner.SpecificOwner)
	default:
		sb.WriteString(` AND s.owner = ?`)
		args = append(args, f.Owner.User)
	}

	if f.Since != nil {
		sb.WriteString(` AND s.started_at >= ?`)
		args = append(args, *f.Since)
	}

	sb.WriteString(` GROUP BY s.working_dir ORDER BY last_active DESC LIMIT ? OFFSET ?`)
	args = append(args, f.Limit, f.Offset)

	// Use a raw row struct so sqlx can scan raw_hosts / raw_tools as strings.
	type rawRow struct {
		Cwd            string `db:"cwd"`
		Sessions       int64  `db:"sessions"`
		TurnCount      int64  `db:"turn_count"`
		TokensInTotal  int64  `db:"tokens_in_total"`
		TokensOutTotal int64  `db:"tokens_out_total"`
		LastActive     int64  `db:"last_active"`
		TopTool        string `db:"top_tool"`
		RawHosts       string `db:"raw_hosts"`
		RawTools       string `db:"raw_tools"`
	}

	rows := make([]rawRow, 0)
	if err := r.Database.DB.SelectContext(ctx, &rows, sb.String(), args...); err != nil {
		return nil, culpa.WithCode(culpa.Wrap(err, "list projects"), "DB_QUERY")
	}

	out := make([]Project, 0, len(rows))
	for _, row := range rows {
		p := Project{
			Cwd:            row.Cwd,
			Sessions:       row.Sessions,
			TurnCount:      row.TurnCount,
			TokensInTotal:  row.TokensInTotal,
			TokensOutTotal: row.TokensOutTotal,
			LastActive:     row.LastActive,
			TopTool:        row.TopTool,
			Hosts:          splitDedup(row.RawHosts),
			Tools:          splitDedup(row.RawTools),
		}
		out = append(out, p)
	}
	return out, nil
}

// splitDedup splits a comma-joined GROUP_CONCAT string, deduplicates the
// values, and returns a sorted slice. An empty input returns an empty (non-nil)
// slice.
func splitDedup(raw string) []string {
	if raw == "" {
		return []string{}
	}
	parts := strings.Split(raw, ",")
	seen := make(map[string]struct{}, len(parts))
	out := make([]string, 0, len(parts))
	for _, p := range parts {
		p = strings.TrimSpace(p)
		if p == "" {
			continue
		}
		if _, ok := seen[p]; !ok {
			seen[p] = struct{}{}
			out = append(out, p)
		}
	}
	sort.Strings(out)
	return out
}