Persist official climb attempts with climbs pages

Record one attempt row per (ride, climb, occurrence) when a ride detail
loads, snapshotting distance, elevation gain, slope, cotacol and attempt
timestamps; a climbs-backfill CLI command seeds all existing rides.

New /climbs list page aggregates attempts (medians computed in Go, best
time as min duration) with sortable columns, and /climbs/{id} shows the
median scalar stats, a median normalized elevation profile (50 steps
over each attempt's own length), the full-precision recorded route of
the median-distance attempt on an OSM map, and every attempt with time
at second precision.

1 files changed, 398 insertions, 0 deletions

diff --git a/web/climbs.go b/web/climbs.go
new file mode 100644
index 0000000..6859545
--- /dev/null
+++ b/web/climbs.go
@@ -0,0 +1,398 @@
+package web
+
+import (
+	"database/sql"
+	"github.com/jftuga/geodist"
+	"github.com/martinlehoux/biking_home/climb"
+	"log/slog"
+	"math"
+	"net/http"
+	"net/url"
+	"slices"
+	"strconv"
+	"strings"
+
+	"github.com/martinlehoux/biking_home/official_climb"
+	"github.com/martinlehoux/biking_home/ride"
+	"github.com/martinlehoux/kagamigo/kcore"
+)
+
+func climbDetailURL(id int64) string {
+	return "/climbs/" + strconv.FormatInt(id, 10)
+}
+
+type routeCandidate struct {
+	coords   []geodist.Coord
+	distance float64
+}
+
+type ClimbProfileData struct {
+	DistanceKm float64     `json:"distanceKm"`
+	Elevations []float64   `json:"elevations"`
+	Route      [][]float64 `json:"route,omitempty"`
+}
+type ClimbAttemptView struct {
+	RideID   int64
+	RideName string
+	Date     string
+	Duration string
+	Distance string
+	Cotacol  string
+}
+
+type ClimbStatsView struct {
+	Attempts   string
+	Distance   string
+	Slope      string
+	Cotacol    string
+	Category   string
+	BestTime   string
+	ElevationG string
+}
+
+type ClimbDetailPageData struct {
+	Climb     official_climb.OfficialClimb
+	Stats     ClimbStatsView
+	Attempts  []ClimbAttemptView
+	Profile   ClimbProfileData
+	Endpoints ClimbMapEndpoints
+}
+
+type ClimbMapEndpoints struct {
+	Start []float64 `json:"start"`
+	End   []float64 `json:"end"`
+	Name  string    `json:"name"`
+}
+
+func (s *Server) handleClimb(w http.ResponseWriter, r *http.Request) {
+	id, err := strconv.ParseInt(r.PathValue("id"), 10, 64)
+	if err != nil || id <= 0 {
+		http.NotFound(w, r)
+		return
+	}
+	climb, found, err := official_climb.GetByID(s.db, id)
+	if err != nil {
+		slog.Error("Failed to load official climb", "climb_id", id, "error", err)
+		http.Error(w, "failed to load climb", http.StatusInternalServerError)
+		return
+	}
+	if !found {
+		http.NotFound(w, r)
+		return
+	}
+	attempts, err := official_climb.AttemptsForClimb(s.db, id)
+	if err != nil {
+		slog.Error("Failed to load climb attempts", "climb_id", id, "error", err)
+		http.Error(w, "failed to load climb attempts", http.StatusInternalServerError)
+		return
+	}
+	aggregate := official_climb.AggregateAttempts(attempts)
+	profile := s.buildClimbProfile(climb, aggregate, attempts)
+	kcore.RenderPage(r.Context(), ClimbDetailPage(ClimbDetailPageData{
+		Climb:    climb,
+		Stats:    buildClimbStatsView(aggregate),
+		Attempts: buildClimbAttemptViews(s.db, attempts),
+		Profile:  profile,
+		Endpoints: ClimbMapEndpoints{
+			Start: []float64{climb.StartCoord.Lat, climb.StartCoord.Lon},
+			End:   []float64{climb.EndCoord.Lat, climb.EndCoord.Lon},
+			Name:  climb.Name,
+		},
+	}), w)
+}
+
+// buildClimbProfile computes the median normalized elevation profile from the
+// linked rides: each ride is parsed, its climb occurrences matched, and every
+// occurrence resampled onto the shared normalized axis.
+func (s *Server) buildClimbProfile(officialClimb official_climb.OfficialClimb, aggregate official_climb.Aggregate, attempts []official_climb.Attempt) ClimbProfileData {
+	profile := ClimbProfileData{DistanceKm: aggregate.DistanceM / 1000}
+	if len(attempts) == 0 {
+		return profile
+	}
+	matchPolicy := s.officialClimbMatchPolicy()
+	elevationSamples := make([][]float64, 0, len(attempts))
+	routeCandidates := make([]routeCandidate, 0, len(attempts))
+	seen := make(map[int64]bool)
+	for _, attempt := range attempts {
+		if seen[attempt.RideID] {
+			continue
+		}
+		seen[attempt.RideID] = true
+		item, found, err := ride.GetByID(s.db, attempt.RideID)
+		if err != nil || !found {
+			continue
+		}
+		parsed, err := ride.ParseStoredRide(item)
+		if err != nil {
+			slog.Warn("Failed to parse ride for climb profile", "climb_id", officialClimb.ID, "ride_id", attempt.RideID, "error", err)
+			continue
+		}
+		for _, matched := range official_climb.MatchClimbs(parsed.Coords, officialClimb, matchPolicy.EndpointRadiusM) {
+			segment := climb.Climb{Route: climb.Route{
+				Distances:  parsed.Distances[matched.StartIndex : matched.EndIndex+1],
+				Elevations: parsed.Elevations[matched.StartIndex : matched.EndIndex+1],
+				Coords:     parsed.Coords[matched.StartIndex : matched.EndIndex+1],
+			}}
+			if sample := official_climb.NormalizeProfile(segment); len(sample) > 0 {
+				elevationSamples = append(elevationSamples, sample)
+			}
+			routeCandidates = append(routeCandidates, routeCandidate{
+				coords:   segment.Coords,
+				distance: segment.Distances[len(segment.Distances)-1] - segment.Distances[0],
+			})
+		}
+	}
+	profile.Elevations = official_climb.MedianProfile(elevationSamples)
+	if route := medianRoute(routeCandidates); len(route) > 1 {
+		profile.Route = make([][]float64, len(route))
+		for i, coord := range route {
+			profile.Route[i] = []float64{coord.Lon, coord.Lat}
+		}
+	}
+	return profile
+}
+
+// medianRoute picks the recorded route of the attempt whose distance is the
+// median across occurrences: full recorded precision, no resampling, and
+// robust to one GPS-drifted ride.
+func medianRoute(candidates []routeCandidate) []geodist.Coord {
+	if len(candidates) == 0 {
+		return nil
+	}
+	distances := make([]float64, len(candidates))
+	for i, candidate := range candidates {
+		distances[i] = candidate.distance
+	}
+	median := official_climb.Median(distances)
+	best := candidates[0]
+	bestDiff := math.Abs(best.distance - median)
+	for _, candidate := range candidates[1:] {
+		if diff := math.Abs(candidate.distance - median); diff < bestDiff {
+			best = candidate
+			bestDiff = diff
+		}
+	}
+	return best.coords
+}
+
+func buildClimbStatsView(aggregate official_climb.Aggregate) ClimbStatsView {
+	stats := ClimbStatsView{
+		Attempts:   strconv.Itoa(aggregate.Attempts),
+		Distance:   formatDistance(aggregate.DistanceM),
+		Slope:      formatSlope(aggregate.SlopePercent),
+		Cotacol:    formatCotacol(aggregate.Cotacol),
+		Category:   aggregate.Category.String(),
+		ElevationG: formatElevation(aggregate.ElevationGainM),
+		BestTime:   "-",
+	}
+	if aggregate.HasBest {
+		stats.BestTime = formatDuration(int64(aggregate.BestDuration.Seconds()))
+	}
+	return stats
+}
+
+func buildClimbAttemptViews(db *sql.DB, attempts []official_climb.Attempt) []ClimbAttemptView {
+	names := make(map[int64]string)
+	views := make([]ClimbAttemptView, len(attempts))
+	for i, attempt := range attempts {
+		name, known := names[attempt.RideID]
+		if !known {
+			if item, found, err := ride.GetByID(db, attempt.RideID); err == nil && found {
+				name = item.Name
+			}
+			names[attempt.RideID] = name
+		}
+		views[i] = ClimbAttemptView{
+			RideID:   attempt.RideID,
+			RideName: name,
+			Date:     formatRideDate(attempt.StartedAt),
+			Duration: formatDuration(int64(attempt.EndedAt.Sub(attempt.StartedAt).Seconds())),
+			Distance: formatDistance(attempt.DistanceM),
+			Cotacol:  formatCotacol(attempt.Cotacol),
+		}
+	}
+	return views
+}
+
+func climbsListURL() string {
+	return "/climbs"
+}
+
+type ClimbSort struct {
+	Column     string
+	Descending bool
+}
+
+const (
+	climbSortName     = "name"
+	climbSortCategory = "category"
+	climbSortAttempts = "attempts"
+	climbSortDistance = "distance"
+	climbSortSlope    = "slope"
+	climbSortCotacol  = "cotacol"
+	climbSortBestTime = "best_time"
+)
+
+var climbSortColumns = map[string]bool{
+	climbSortName:     true,
+	climbSortCategory: true,
+	climbSortAttempts: true,
+	climbSortDistance: true,
+	climbSortSlope:    true,
+	climbSortCotacol:  true,
+	climbSortBestTime: true,
+}
+
+func parseClimbSort(query url.Values) ClimbSort {
+	column := query.Get("sort")
+	if !climbSortColumns[column] {
+		column = climbSortCotacol
+	}
+	direction := query.Get("dir")
+	return ClimbSort{Column: column, Descending: direction != "asc"}
+}
+
+func sortListedClimbs(climbs []official_climb.ListedClimb, sort ClimbSort) []official_climb.ListedClimb {
+	ordered := make([]official_climb.ListedClimb, len(climbs))
+	copy(ordered, climbs)
+	slices.SortStableFunc(ordered, func(a, b official_climb.ListedClimb) int {
+		var cmp int
+		switch sort.Column {
+		case climbSortName:
+			cmp = strings.Compare(a.Name, b.Name)
+		case climbSortCategory:
+			cmp = int(climb.CategoryOf(a.Cotacol)) - int(climb.CategoryOf(b.Cotacol))
+		case climbSortAttempts:
+			cmp = a.Attempts - b.Attempts
+		case climbSortDistance:
+			cmp = compareFloat(a.DistanceM, b.DistanceM)
+		case climbSortSlope:
+			cmp = compareFloat(a.SlopePercent, b.SlopePercent)
+		case climbSortBestTime:
+			cmp = compareFloat(bestTimeOrInfinity(a), bestTimeOrInfinity(b))
+		default:
+			cmp = compareFloat(a.Cotacol, b.Cotacol)
+		}
+		if sort.Descending {
+			return -cmp
+		}
+		return cmp
+	})
+	return ordered
+}
+
+func bestTimeOrInfinity(climb official_climb.ListedClimb) float64 {
+	if !climb.HasBest {
+		return math.Inf(1)
+	}
+	return climb.BestDurationS
+}
+
+func compareFloat(a, b float64) int {
+	switch {
+	case a < b:
+		return -1
+	case a > b:
+		return 1
+	default:
+		return 0
+	}
+}
+
+type ClimbListView struct {
+	Climb    official_climb.ListedClimb
+	Category string
+	Distance string
+	Slope    string
+	Cotacol  string
+	BestTime string
+	URL      string
+}
+
+type ClimbsPageData struct {
+	Items   []ClimbListView
+	Headers []RideSortHeader
+}
+
+func buildClimbListViews(climbs []official_climb.ListedClimb) []ClimbListView {
+	views := make([]ClimbListView, len(climbs))
+	for i, listed := range climbs {
+		bestTime := "-"
+		if listed.HasBest {
+			bestTime = formatDuration(int64(listed.BestDurationS))
+		}
+		views[i] = ClimbListView{
+			Climb:    listed,
+			Category: listed.Category(),
+			Distance: formatDistance(listed.DistanceM),
+			Slope:    formatSlope(listed.SlopePercent),
+			Cotacol:  formatCotacol(listed.Cotacol),
+			BestTime: bestTime,
+			URL:      climbDetailURL(listed.ID),
+		}
+	}
+	return views
+}
+
+func (s *Server) handleClimbs(w http.ResponseWriter, r *http.Request) {
+	sort := parseClimbSort(r.URL.Query())
+	climbs, err := official_climb.ListWithAggregates(s.db)
+	if err != nil {
+		slog.Error("Failed to load climbs", "error", err)
+		http.Error(w, "failed to load climbs", http.StatusInternalServerError)
+		return
+	}
+	climbs = sortListedClimbs(climbs, sort)
+	kcore.RenderPage(r.Context(), ClimbsPage(ClimbsPageData{
+		Items:   buildClimbListViews(climbs),
+		Headers: climbSortHeaders(sort),
+	}), w)
+}
+
+func climbSortHeaders(current ClimbSort) []RideSortHeader {
+	columns := []struct {
+		key   string
+		label string
+		class string
+	}{
+		{key: climbSortName, label: "Climb"},
+		{key: climbSortCategory, label: "Category"},
+		{key: climbSortAttempts, label: "Attempts", class: "numeric"},
+		{key: climbSortDistance, label: "Distance", class: "numeric"},
+		{key: climbSortSlope, label: "Avg slope", class: "numeric"},
+		{key: climbSortCotacol, label: "Cotacol", class: "numeric"},
+		{key: climbSortBestTime, label: "Best time", class: "numeric"},
+	}
+	headers := make([]RideSortHeader, 0, len(columns))
+	for _, column := range columns {
+		descending := false
+		active := current.Column == column.key
+		if active {
+			descending = !current.Descending
+		}
+		query := url.Values{}
+		query.Set("sort", column.key)
+		query.Set("dir", rideSortDirection(RideSort{Column: column.key, Descending: descending}))
+		headerClass := column.class
+		ariaSort := "none"
+		indicator := ""
+		if active {
+			headerClass += " active"
+			if current.Descending {
+				ariaSort = "descending"
+				indicator = "↓"
+			} else {
+				ariaSort = "ascending"
+				indicator = "↑"
+			}
+		}
+		headers = append(headers, RideSortHeader{
+			Label:     column.label,
+			Class:     headerClass,
+			URL:       climbsListURL() + "?" + query.Encode(),
+			AriaSort:  ariaSort,
+			Indicator: indicator,
+		})
+	}
+	return headers
+}