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, 81 insertions, 0 deletions

diff --git a/web/official-climbs_test.go b/web/official-climbs_test.go
new file mode 100644
index 0000000..ba71f19
--- /dev/null
+++ b/web/official-climbs_test.go
@@ -0,0 +1,81 @@
+package web
+
+import (
+	"database/sql"
+	"github.com/martinlehoux/biking_home/internal/ridetest"
+	"testing"
+	"time"
+
+	"github.com/martinlehoux/biking_home/official_climb"
+	"github.com/martinlehoux/biking_home/ride"
+	"github.com/martinlehoux/biking_home/rideanalysis"
+	"github.com/stretchr/testify/assert"
+	"github.com/stretchr/testify/require"
+)
+
+var testRideStart = time.Date(2026, 9, 1, 8, 0, 0, 0, time.UTC)
+
+func testRideForClimb(t *testing.T) (ride.Ride, official_climb.OfficialClimb) {
+	t.Helper()
+	parsed := ridetest.MustRide("1km at 14.7% | 1km at -6.7%", ridetest.StartCoord(43.61, 5.42), ridetest.StartElevation(300), ridetest.Sampling(1000), ridetest.StartTime(testRideStart))
+	return parsed, official_climb.OfficialClimb{Name: "Col de Test", StartCoord: parsed.Coords[0], EndCoord: parsed.Coords[1]}
+}
+
+func insertTestRide(t *testing.T, db *sql.DB) int64 {
+	t.Helper()
+	result, err := db.Exec(`
+		INSERT INTO rides (external_id, gpx_path, name, external_sport_type, start_date, distance_m, external_moving_time_s, elapsed_time_s, total_elevation_gain_m, average_speed_mps)
+		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
+	`, "strava:climb-test", "missing.gpx", "Test Ride", "Ride", "2026-09-01T08:00:00Z", 20_000, 0, 0, 0, 0)
+	require.NoError(t, err)
+	id, err := result.LastInsertId()
+	require.NoError(t, err)
+	return id
+}
+
+func TestAttemptsFromAnalysisSnapshotsOfficialClimbs(t *testing.T) {
+	parsed, climb := testRideForClimb(t)
+	analysis := rideanalysis.Analyze(parsed, nil, []official_climb.OfficialClimb{{ID: 42, Name: climb.Name, StartCoord: climb.StartCoord, EndCoord: climb.EndCoord}}, official_climb.DefaultMatchPolicy())
+
+	attempts := attemptsFromAnalysis(7, analysis, parsed)
+	require.Len(t, attempts, 1)
+	assert.Equal(t, int64(7), attempts[0].RideID)
+	assert.Equal(t, int64(42), attempts[0].ClimbID)
+	assert.Equal(t, 0, attempts[0].Occurrence)
+	assert.InDelta(t, 1000, attempts[0].DistanceM, 1)
+	assert.InDelta(t, 147, attempts[0].ElevationGainM, 1)
+	assert.InDelta(t, 14.7, attempts[0].SlopePercent, 0.1)
+	assert.Positive(t, attempts[0].Cotacol)
+	assert.Equal(t, "v1", attempts[0].CotacolAlgoVersion)
+	assert.Equal(t, testRideStart, attempts[0].StartedAt.UTC())
+	assert.False(t, attempts[0].EndedAt.IsZero(), "attempt end must come from ride timestamps")
+}
+
+func TestAttemptsFromAnalysisSkipsUnmatchedClimbs(t *testing.T) {
+	parsed, _ := testRideForClimb(t)
+	analysis := rideanalysis.Analyze(parsed, nil, nil, official_climb.DefaultMatchPolicy())
+
+	attempts := attemptsFromAnalysis(7, analysis, parsed)
+	assert.Empty(t, attempts)
+}
+
+func TestRecordOfficialClimbAttemptsPersistsAndReplaces(t *testing.T) {
+	server, db := newWebTestServer(t)
+	rideID := insertTestRide(t, db)
+	parsed, climb := testRideForClimb(t)
+	created, err := official_climb.Create(db, climb)
+	require.NoError(t, err)
+	analysis := rideanalysis.Analyze(parsed, nil, []official_climb.OfficialClimb{created}, official_climb.DefaultMatchPolicy())
+
+	server.recordOfficialClimbAttempts(rideID, parsed, analysis)
+	attempts, err := official_climb.AttemptsForClimb(db, created.ID)
+	require.NoError(t, err)
+	require.Len(t, attempts, 1)
+	assert.Equal(t, rideID, attempts[0].RideID)
+
+	// Re-recording replaces, never duplicates.
+	server.recordOfficialClimbAttempts(rideID, parsed, analysis)
+	attempts, err = official_climb.AttemptsForClimb(db, created.ID)
+	require.NoError(t, err)
+	require.Len(t, attempts, 1)
+}