Chart power vs time for official climb attempts

Snapshot average power per attempt in a nullable average_power_w column (backfilled from stored rides), plot each powered attempt on the climb page as duration vs average watts with ride hover and click-through, and show avg power in the attempts table.

2 files changed, 36 insertions, 9 deletions

diff --git a/official_climb/attempt.go b/official_climb/attempt.go
index c340fea..1164e01 100644
--- a/official_climb/attempt.go
+++ b/official_climb/attempt.go
@@ -9,7 +9,7 @@ import (
 	"github.com/martinlehoux/biking_home/climb"
 )
 
-// Attempt is one recorded passage of an official climb in a ride, keyed by (rideID, climbID, occurrence); stats are snapshotted so listings never re-parse ride files.
+// Attempt is one recorded passage of an official climb in a ride, keyed by (rideID, climbID, occurrence); stats are snapshotted so listings never re-parse ride files. AveragePowerW is nil for rides without a power meter.
 type Attempt struct {
 	RideID             int64
 	ClimbID            int64
@@ -18,13 +18,14 @@ type Attempt struct {
 	ElevationGainM     float64
 	SlopePercent       float64
 	Cotacol            float64
+	AveragePowerW      *float64
 	CotacolAlgoVersion string
 	StartedAt          time.Time
 	EndedAt            time.Time
 	CreatedAt          time.Time
 }
 
-const attemptColumns = "a.ride_id, a.climb_id, a.occurrence, a.distance_m, a.elevation_gain_m, a.slope_percent, a.cotacol, a.cotacol_algo_version, a.started_at, a.ended_at, a.created_at"
+const attemptColumns = "a.ride_id, a.climb_id, a.occurrence, a.distance_m, a.elevation_gain_m, a.slope_percent, a.cotacol, a.average_power_w, a.cotacol_algo_version, a.started_at, a.ended_at, a.created_at"
 
 // AttemptsForClimb returns every recorded attempt of one official climb, oldest ride first.
 func AttemptsForClimb(db *sql.DB, climbID int64) ([]Attempt, error) {
@@ -78,17 +79,18 @@ func ReplaceRideAttempts(db *sql.DB, rideID int64, attempts []Attempt) error {
 		keys = append(keys, "(?, ?)")
 		args = append(args, attempt.ClimbID, attempt.Occurrence)
 		if _, err := tx.Exec(`
-			INSERT INTO ride_official_climb_attempts (ride_id, climb_id, occurrence, distance_m, elevation_gain_m, slope_percent, cotacol, cotacol_algo_version, started_at, ended_at)
-			VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
+			INSERT INTO ride_official_climb_attempts (ride_id, climb_id, occurrence, distance_m, elevation_gain_m, slope_percent, cotacol, average_power_w, cotacol_algo_version, started_at, ended_at)
+			VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
 			ON CONFLICT(ride_id, climb_id, occurrence) DO UPDATE SET
 				distance_m = excluded.distance_m,
 				elevation_gain_m = excluded.elevation_gain_m,
 				slope_percent = excluded.slope_percent,
 				cotacol = excluded.cotacol,
+				average_power_w = excluded.average_power_w,
 				cotacol_algo_version = excluded.cotacol_algo_version,
 				started_at = excluded.started_at,
 				ended_at = excluded.ended_at
-		`, attempt.RideID, attempt.ClimbID, attempt.Occurrence, attempt.DistanceM, attempt.ElevationGainM, attempt.SlopePercent, attempt.Cotacol, attempt.CotacolAlgoVersion,
+		`, attempt.RideID, attempt.ClimbID, attempt.Occurrence, attempt.DistanceM, attempt.ElevationGainM, attempt.SlopePercent, attempt.Cotacol, attempt.AveragePowerW, attempt.CotacolAlgoVersion,
 			attempt.StartedAt.UTC().Format(time.RFC3339), attempt.EndedAt.UTC().Format(time.RFC3339)); err != nil {
 			return fmt.Errorf("insert ride attempt: %w", err)
 		}
@@ -113,12 +115,16 @@ type attemptScanner interface {
 func scanAttempt(s attemptScanner) (Attempt, error) {
 	var (
 		attempt            Attempt
+		averagePower       sql.NullFloat64
 		startedAt, endedAt string
 		createdAt          sql.NullString
 	)
-	if err := s.Scan(&attempt.RideID, &attempt.ClimbID, &attempt.Occurrence, &attempt.DistanceM, &attempt.ElevationGainM, &attempt.SlopePercent, &attempt.Cotacol, &attempt.CotacolAlgoVersion, &startedAt, &endedAt, &createdAt); err != nil {
+	if err := s.Scan(&attempt.RideID, &attempt.ClimbID, &attempt.Occurrence, &attempt.DistanceM, &attempt.ElevationGainM, &attempt.SlopePercent, &attempt.Cotacol, &averagePower, &attempt.CotacolAlgoVersion, &startedAt, &endedAt, &createdAt); err != nil {
 		return Attempt{}, err
 	}
+	if averagePower.Valid {
+		attempt.AveragePowerW = &averagePower.Float64
+	}
 	var err error
 	if attempt.StartedAt, err = time.Parse(time.RFC3339, startedAt); err != nil {
 		return Attempt{}, fmt.Errorf("invalid ride_official_climb_attempts.started_at: %w", err)
@@ -145,8 +151,8 @@ type AttemptStats struct {
 	EndIndex        int
 }
 
-// AttemptFromStats snapshots one analyzed climb occurrence; timestamps come from the ride points bounding the climb interval.
-func AttemptFromStats(rideID int64, occurrence int, stats AttemptStats, timestamps []time.Time) Attempt {
+// AttemptFromStats snapshots one analyzed climb occurrence; timestamps come from the ride points bounding the climb interval, and AveragePowerW is the mean of the power samples inside it (nil when the ride recorded no power).
+func AttemptFromStats(rideID int64, occurrence int, stats AttemptStats, timestamps []time.Time, powers []float64) Attempt {
 	startedAt, endedAt := time.Time{}, time.Time{}
 	if stats.StartIndex < len(timestamps) {
 		startedAt = timestamps[stats.StartIndex]
@@ -162,8 +168,22 @@ func AttemptFromStats(rideID int64, occurrence int, stats AttemptStats, timestam
 		ElevationGainM:     stats.ElevationGainM,
 		SlopePercent:       stats.SlopePercent,
 		Cotacol:            stats.Cotacol,
+		AveragePowerW:      meanPower(powers, stats.StartIndex, stats.EndIndex),
 		CotacolAlgoVersion: climb.CotacolAlgorithmVersion,
 		StartedAt:          startedAt,
 		EndedAt:            endedAt,
 	}
 }
+
+// meanPower averages the power samples inside the climb interval (bounds included); it is nil when the interval does not fit the column.
+func meanPower(powers []float64, start, end int) *float64 {
+	if start < 0 || end >= len(powers) || start > end {
+		return nil
+	}
+	sum := 0.0
+	for _, power := range powers[start : end+1] {
+		sum += power
+	}
+	mean := sum / float64(end-start+1)
+	return &mean
+}
diff --git a/official_climb/attempt_test.go b/official_climb/attempt_test.go
index 8913807..e9d8b11 100644
--- a/official_climb/attempt_test.go
+++ b/official_climb/attempt_test.go
@@ -47,6 +47,7 @@ func withStartDate(startDate string) func(*rideInsert) {
 }
 
 func attemptFixture(rideID, climbID int64, occurrence int) official_climb.Attempt {
+	averagePowerW := 235.5
 	return official_climb.Attempt{
 		RideID:             rideID,
 		ClimbID:            climbID,
@@ -55,6 +56,7 @@ func attemptFixture(rideID, climbID int64, occurrence int) official_climb.Attemp
 		ElevationGainM:     210,
 		SlopePercent:       6.5,
 		Cotacol:            84.2,
+		AveragePowerW:      &averagePowerW,
 		CotacolAlgoVersion: "v1",
 		StartedAt:          time.Date(2026, 9, 1, 10, 0, 0, 0, time.UTC),
 		EndedAt:            time.Date(2026, 9, 1, 10, 18, 30, 0, time.UTC),
@@ -66,10 +68,12 @@ func TestReplaceRideAttemptsRoundTrip(t *testing.T) {
 	created, err := official_climb.Create(db, official_climb.OfficialClimb{Name: "Col de Test", StartCoord: coord(43.6, 5.4), EndCoord: coord(43.62, 5.42), Sample: sampleFixture()})
 	require.NoError(t, err)
 	rideID := insertRide(t, db)
+	withoutPower := attemptFixture(rideID, created.ID, 1)
+	withoutPower.AveragePowerW = nil
 
 	require.NoError(t, official_climb.ReplaceRideAttempts(db, rideID, []official_climb.Attempt{
 		attemptFixture(rideID, created.ID, 0),
-		attemptFixture(rideID, created.ID, 1),
+		withoutPower,
 	}))
 
 	attempts, err := official_climb.AttemptsForClimb(db, created.ID)
@@ -80,6 +84,9 @@ func TestReplaceRideAttemptsRoundTrip(t *testing.T) {
 	assert.Equal(t, 3200.0, attempts[0].DistanceM)
 	assert.Equal(t, "v1", attempts[0].CotacolAlgoVersion)
 	assert.Equal(t, time.Date(2026, 9, 1, 10, 18, 30, 0, time.UTC), attempts[0].EndedAt)
+	require.NotNil(t, attempts[0].AveragePowerW)
+	assert.InDelta(t, 235.5, *attempts[0].AveragePowerW, 0.01)
+	assert.Nil(t, attempts[1].AveragePowerW, "a missing power meter must round-trip as null")
 
 	rideAttempts, err := official_climb.AttemptsForRide(db, rideID)
 	require.NoError(t, err)