feat: Persist Cotacol ride values

9 files changed, 174 insertions, 41 deletions

diff --git a/cli/cli.go b/cli/cli.go
index 6e427bb..1d1b0f3 100644
--- a/cli/cli.go
+++ b/cli/cli.go
@@ -14,6 +14,7 @@ import (
 	"github.com/martinlehoux/biking_home/mountain_pass"
 	"github.com/martinlehoux/biking_home/osmpass"
 	"github.com/martinlehoux/biking_home/ride"
+	"github.com/martinlehoux/biking_home/rides"
 	"github.com/martinlehoux/biking_home/web"
 	"github.com/martinlehoux/kagamigo/kcore"
 )
@@ -25,6 +26,7 @@ var (
 	fetchOSM    = flag.Bool("fetch-osm", false, "download the France OSM PBF (resumable) into france-latest.osm.pbf")
 	extractOSM  = flag.String("extract-osm", "", "extract mountain passes from an OSM PBF file into the database")
 	enrich      = flag.Bool("enrich", false, "backfill mountain pass coordinates from OSM data")
+	backfill    = flag.Bool("backfill", false, "recompute all stored ride values")
 	chartFile   = flag.String("chart", "", "render a climb/pass chart for a GPX file")
 	parser      = ride.GPXRideParser{}
 )
@@ -46,6 +48,10 @@ func Run(db *sql.DB, configPath string, appConfig config.Config) {
 	case *enrich:
 		_, err := osmpass.EnrichMountainPasses(db)
 		kcore.Expect(err, "failed to enrich mountain passes")
+	case *backfill:
+		count, err := rides.Backfill(db)
+		kcore.Expect(err, "failed to backfill ride values")
+		slog.Info("Backfilled ride values", "rides", count)
 	case *chartFile != "":
 		runChart(db, *chartFile)
 	default:
diff --git a/db/migrations/20260807000000_ride_cotacol.sql b/db/migrations/20260807000000_ride_cotacol.sql
new file mode 100644
index 0000000..44e3a81
--- /dev/null
+++ b/db/migrations/20260807000000_ride_cotacol.sql
@@ -0,0 +1,7 @@
+-- migrate:up
+alter table rides add column cotacol_score real;
+alter table rides add column cotacol_algo_version text;
+
+-- migrate:down
+alter table rides drop column cotacol_algo_version;
+alter table rides drop column cotacol_score;
diff --git a/db/schema.sql b/db/schema.sql
index 6a22bc5..1a3d62a 100644
--- a/db/schema.sql
+++ b/db/schema.sql
@@ -27,6 +27,8 @@ CREATE TABLE rides (
     elapsed_time_s integer not null,
     total_elevation_gain_m real not null,
     average_speed_mps real not null,
+    cotacol_score real,
+    cotacol_algo_version text,
     created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
     updated_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
 );
@@ -34,4 +36,5 @@ CREATE TABLE rides (
 INSERT INTO "schema_migrations" (version) VALUES
   ('20250802140659'),
   ('20260802090000'),
-  ('20260804000000');
+  ('20260804000000'),
+  ('20260807000000');
diff --git a/ride/difficulty_test.go b/ride/difficulty_test.go
index 4b72037..142ac0a 100644
--- a/ride/difficulty_test.go
+++ b/ride/difficulty_test.go
@@ -7,48 +7,48 @@ import (
 	"github.com/stretchr/testify/assert"
 )
 
-func TestDifficultyScoreConstantClimb(t *testing.T) {
+func TestCotacolConstantClimb(t *testing.T) {
 	r := RideBuilder{precision: 100}.WithSection("2km at 7%").Build()
-	assert.InDelta(t, 98.0, r.DifficultyScore(), 1e-9)
+	assert.InDelta(t, 98.0, ride.Cotacol(r), 1e-9)
 }
 
-func TestDifficultyScoreIgnoresDescent(t *testing.T) {
+func TestCotacolIgnoresDescent(t *testing.T) {
 	r := RideBuilder{precision: 100}.WithSection("2km at 7%").WithSection("2km at -7%").Build()
-	assert.InDelta(t, 98.0, r.DifficultyScore(), 1e-9)
+	assert.InDelta(t, 98.0, ride.Cotacol(r), 1e-9)
 }
 
-func TestDifficultyScoreFlatIsZero(t *testing.T) {
+func TestCotacolFlatIsZero(t *testing.T) {
 	r := RideBuilder{precision: 100}.WithSection("5km at 0%").Build()
-	assert.Zero(t, r.DifficultyScore())
+	assert.Zero(t, ride.Cotacol(r))
 }
 
-func TestDifficultyScoreSteepShortCountsMore(t *testing.T) {
+func TestCotacolSteepShortCountsMore(t *testing.T) {
 	steep := RideBuilder{precision: 100}.WithSection("0.5km at 14%").Build()
 	long := RideBuilder{precision: 100}.WithSection("1km at 7%").Build()
-	assert.InDelta(t, 98.0, steep.DifficultyScore(), 1e-9)
-	assert.InDelta(t, 49.0, long.DifficultyScore(), 1e-9)
+	assert.InDelta(t, 98.0, ride.Cotacol(steep), 1e-9)
+	assert.InDelta(t, 49.0, ride.Cotacol(long), 1e-9)
 }
 
-func TestDifficultyScorePrecisionInvariant(t *testing.T) {
+func TestCotacolPrecisionInvariant(t *testing.T) {
 	coarse := RideBuilder{precision: 100}.WithSection("3km at 5%").Build()
 	fine := RideBuilder{precision: 50}.WithSection("3km at 5%").Build()
-	assert.InDelta(t, coarse.DifficultyScore(), fine.DifficultyScore(), 1e-9)
+	assert.InDelta(t, ride.Cotacol(coarse), ride.Cotacol(fine), 1e-9)
 }
 
-func TestDifficultyScoreExampleRide(t *testing.T) {
+func TestCotacolExampleRide(t *testing.T) {
 	r, err := ride.ParseFile(parser, "../examples/2022-07-21.Pogacar.gpx")
 	assert.NoError(t, err)
-	assert.InDelta(t, 3049.0, r.DifficultyScore(), 1)
+	assert.InDelta(t, 3049.0, ride.Cotacol(r), 1)
 }
 
-func TestDifficultyScoreClimbNotAtStart(t *testing.T) {
+func TestCotacolClimbNotAtStart(t *testing.T) {
 	r := RideBuilder{precision: 100}.WithSection("1km at 0%").WithSection("2km at 7%").Build()
 	climbs := r.AllClimbs()
 	assert.Len(t, climbs, 1)
 	assert.InDelta(t, 98.0, climbs[0].DifficultyScore(), 1e-9)
 }
 
-func TestDifficultyScoreHautacamClimbfinderReference(t *testing.T) {
+func TestCotacolHautacamClimbfinderReference(t *testing.T) {
 	r, err := ride.ParseFile(parser, "../examples/2022-07-21.Pogacar.gpx")
 	assert.NoError(t, err)
 	climbs := r.AllClimbs()
diff --git a/ride/ride.go b/ride/ride.go
index ef91272..52f95b8 100644
--- a/ride/ride.go
+++ b/ride/ride.go
@@ -245,8 +245,10 @@ func (r *Ride) ScoreFromKm(start, end float64) float64 {
 	return Score(*r, i, j)
 }
 
-func (r *Ride) DifficultyScore() float64 {
-	return difficultyScore(*r, 0, r.Len()-1)
+const CotacolAlgorithmVersion = "v1"
+
+func Cotacol(ride Ride) float64 {
+	return difficultyScore(ride, 0, ride.Len()-1)
 }
 
 func difficultyScore(r Ride, startIndex, endIndex int) float64 {
diff --git a/rides/rides.go b/rides/rides.go
index f9d33da..619fefa 100644
--- a/rides/rides.go
+++ b/rides/rides.go
@@ -5,6 +5,7 @@ import (
 	"fmt"
 	"time"
 
+	"github.com/martinlehoux/biking_home/ride"
 	"github.com/martinlehoux/kagamigo/kcore"
 )
 
@@ -20,6 +21,8 @@ type Ride struct {
 	ElapsedTimeS        int64
 	TotalElevationGainM float64
 	AverageSpeedMps     float64
+	cotacolScore        *float64
+	cotacolAlgoVersion  string
 	CreatedAt           time.Time
 	UpdatedAt           time.Time
 }
@@ -34,12 +37,17 @@ const (
 	SortElevation  SortColumn = "elevation"
 )
 
-const columns = "id, external_id, gpx_path, name, type, start_date, distance_m, moving_time_s, elapsed_time_s, total_elevation_gain_m, average_speed_mps, created_at, updated_at"
+const columns = "id, external_id, gpx_path, name, type, start_date, distance_m, moving_time_s, elapsed_time_s, total_elevation_gain_m, average_speed_mps, cotacol_score, cotacol_algo_version, created_at, updated_at"
 
 func Save(db *sql.DB, r Ride) error {
-	_, err := db.Exec(`
-		INSERT INTO rides (external_id, gpx_path, name, type, start_date, distance_m, moving_time_s, elapsed_time_s, total_elevation_gain_m, average_speed_mps)
-		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
+	parsed, err := ride.ParseFile(ride.GPXRideParser{}, r.GPXPath)
+	if err != nil {
+		return fmt.Errorf("compute Cotacol for ride %q: %w", r.ExternalID, err)
+	}
+	cotacolScore := ride.Cotacol(parsed)
+	_, err = db.Exec(`
+		INSERT INTO rides (external_id, gpx_path, name, type, start_date, distance_m, moving_time_s, elapsed_time_s, total_elevation_gain_m, average_speed_mps, cotacol_score, cotacol_algo_version)
+		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
 		ON CONFLICT(external_id) DO UPDATE SET
 			gpx_path = excluded.gpx_path,
 			name = excluded.name,
@@ -50,11 +58,42 @@ func Save(db *sql.DB, r Ride) error {
 			elapsed_time_s = excluded.elapsed_time_s,
 			total_elevation_gain_m = excluded.total_elevation_gain_m,
 			average_speed_mps = excluded.average_speed_mps,
+			cotacol_score = excluded.cotacol_score,
+			cotacol_algo_version = excluded.cotacol_algo_version,
 			updated_at = strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
-	`, r.ExternalID, r.GPXPath, r.Name, r.Type, r.StartDate.UTC().Format(time.RFC3339), r.DistanceM, r.MovingTimeS, r.ElapsedTimeS, r.TotalElevationGainM, r.AverageSpeedMps)
+	`, r.ExternalID, r.GPXPath, r.Name, r.Type, r.StartDate.UTC().Format(time.RFC3339), r.DistanceM, r.MovingTimeS, r.ElapsedTimeS, r.TotalElevationGainM, r.AverageSpeedMps, cotacolScore, ride.CotacolAlgorithmVersion)
 	return err
 }
 
+func Backfill(db *sql.DB) (int, error) {
+	rows, err := db.Query("SELECT " + columns + " FROM rides ORDER BY id")
+	if err != nil {
+		return 0, err
+	}
+	var pending []Ride
+	for rows.Next() {
+		item, err := scanRide(rows)
+		if err != nil {
+			rows.Close()
+			return 0, err
+		}
+		pending = append(pending, item)
+	}
+	if err := rows.Err(); err != nil {
+		rows.Close()
+		return 0, err
+	}
+	if err := rows.Close(); err != nil {
+		return 0, err
+	}
+	for i, item := range pending {
+		if err := Save(db, item); err != nil {
+			return i, fmt.Errorf("backfill ride %q: %w", item.ExternalID, err)
+		}
+	}
+	return len(pending), nil
+}
+
 func List(db *sql.DB) ([]Ride, error) {
 	return ListSorted(db, SortStartDate, true)
 }
@@ -110,15 +149,23 @@ type scanner interface {
 
 func scanRide(s scanner) (Ride, error) {
 	var (
-		ride      Ride
-		startDate string
-		createdAt string
-		updatedAt string
+		ride               Ride
+		startDate          string
+		cotacolScore       sql.NullFloat64
+		cotacolAlgoVersion sql.NullString
+		createdAt          string
+		updatedAt          string
 	)
-	err := s.Scan(&ride.ID, &ride.ExternalID, &ride.GPXPath, &ride.Name, &ride.Type, &startDate, &ride.DistanceM, &ride.MovingTimeS, &ride.ElapsedTimeS, &ride.TotalElevationGainM, &ride.AverageSpeedMps, &createdAt, &updatedAt)
+	err := s.Scan(&ride.ID, &ride.ExternalID, &ride.GPXPath, &ride.Name, &ride.Type, &startDate, &ride.DistanceM, &ride.MovingTimeS, &ride.ElapsedTimeS, &ride.TotalElevationGainM, &ride.AverageSpeedMps, &cotacolScore, &cotacolAlgoVersion, &createdAt, &updatedAt)
 	if err != nil {
 		return Ride{}, err
 	}
+	if cotacolScore.Valid {
+		ride.cotacolScore = &cotacolScore.Float64
+	}
+	if cotacolAlgoVersion.Valid {
+		ride.cotacolAlgoVersion = cotacolAlgoVersion.String
+	}
 	ride.StartDate, err = time.Parse(time.RFC3339, startDate)
 	if err != nil {
 		return Ride{}, kcore.Wrap(err, "invalid start_date in rides row")
@@ -133,3 +180,14 @@ func scanRide(s scanner) (Ride, error) {
 	}
 	return ride, nil
 }
+
+func (r Ride) CotacolAlgorithmVersion() string {
+	return r.cotacolAlgoVersion
+}
+
+func (r Ride) CotacolScore() (float64, bool) {
+	if r.cotacolScore == nil {
+		return 0, false
+	}
+	return *r.cotacolScore, true
+}
diff --git a/rides/rides_test.go b/rides/rides_test.go
index 1ba23f9..cbe5670 100644
--- a/rides/rides_test.go
+++ b/rides/rides_test.go
@@ -2,9 +2,12 @@ package rides
 
 import (
 	"database/sql"
+	"os"
+	"path/filepath"
 	"testing"
 	"time"
 
+	"github.com/martinlehoux/biking_home/ride"
 	"github.com/martinlehoux/kagamigo/kcore"
 	_ "github.com/mattn/go-sqlite3"
 	"github.com/stretchr/testify/assert"
@@ -29,6 +32,8 @@ func newTestDB(t *testing.T) *sql.DB {
 			elapsed_time_s integer not null,
 			total_elevation_gain_m real not null,
 			average_speed_mps real not null,
+			cotacol_score real,
+			cotacol_algo_version text,
 			created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
 			updated_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
 		)
@@ -37,10 +42,13 @@ func newTestDB(t *testing.T) *sql.DB {
 	return db
 }
 
-func sampleRide() Ride {
+func sampleRide(t *testing.T) Ride {
+	t.Helper()
+	gpxPath := filepath.Join(t.TempDir(), "ride.gpx")
+	require.NoError(t, os.WriteFile(gpxPath, []byte(testGPX), 0o600))
 	return Ride{
 		ExternalID:          "strava:1234",
-		GPXPath:             "rides/activity_1234.gpx",
+		GPXPath:             gpxPath,
 		Name:                "Morning Ride",
 		Type:                "Ride",
 		StartDate:           time.Date(2026, 8, 1, 7, 30, 0, 0, time.UTC),
@@ -52,27 +60,66 @@ func sampleRide() Ride {
 	}
 }
 
+const testGPX = `<?xml version="1.0"?><gpx xmlns="http://www.topografix.com/GPX/1/1" version="1.1"><trk><trkseg><trkpt lat="43.0" lon="5.0"><ele>100</ele></trkpt><trkpt lat="43.001" lon="5.001"><ele>200</ele></trkpt></trkseg></trk></gpx>`
+
 func TestUpsertAndGet(t *testing.T) {
 	db := newTestDB(t)
-	err := Save(db, sampleRide())
+	sample := sampleRide(t)
+	err := Save(db, sample)
 	require.NoError(t, err)
 
 	got, ok, err := GetByExternalID(db, "strava:1234")
 	require.NoError(t, err)
 	require.True(t, ok)
 	assert.Equal(t, "Morning Ride", got.Name)
-	assert.Equal(t, sampleRide().StartDate, got.StartDate)
+	assert.Equal(t, sample.StartDate, got.StartDate)
 	assert.Equal(t, 42_195.0, got.DistanceM)
-	assert.Equal(t, "rides/activity_1234.gpx", got.GPXPath)
+	assert.Equal(t, sample.GPXPath, got.GPXPath)
+	score, found := got.CotacolScore()
+	require.True(t, found)
+	assert.Greater(t, score, 0.0)
+	assert.Equal(t, ride.CotacolAlgorithmVersion, got.CotacolAlgorithmVersion())
 
 	_, ok, err = GetByExternalID(db, "strava:9999")
 	require.NoError(t, err)
 	assert.False(t, ok)
 }
 
+func TestSaveComputesCotacol(t *testing.T) {
+	db := newTestDB(t)
+	require.NoError(t, Save(db, sampleRide(t)))
+
+	got, ok, err := GetByExternalID(db, "strava:1234")
+	require.NoError(t, err)
+	require.True(t, ok)
+	score, found := got.CotacolScore()
+	require.True(t, found)
+	assert.Greater(t, score, 0.0)
+	assert.Equal(t, ride.CotacolAlgorithmVersion, got.CotacolAlgorithmVersion())
+}
+
+func TestBackfill(t *testing.T) {
+	db := newTestDB(t)
+	require.NoError(t, Save(db, sampleRide(t)))
+
+	count, err := Backfill(db)
+	require.NoError(t, err)
+	assert.Equal(t, 1, count)
+	got, ok, err := GetByExternalID(db, "strava:1234")
+	require.NoError(t, err)
+	require.True(t, ok)
+	_, found := got.CotacolScore()
+	assert.True(t, found)
+	assert.Equal(t, ride.CotacolAlgorithmVersion, got.CotacolAlgorithmVersion())
+
+	count, err = Backfill(db)
+	require.NoError(t, err)
+	assert.Equal(t, 1, count)
+}
+
 func TestUpsertUpdatesExisting(t *testing.T) {
 	db := newTestDB(t)
-	ride := sampleRide()
+	ride := sampleRide(t)
 	require.NoError(t, Save(db, ride))
 	ride.Name = "Renamed Ride"
 	ride.DistanceM = 50_000
@@ -87,9 +134,9 @@ func TestUpsertUpdatesExisting(t *testing.T) {
 
 func TestList(t *testing.T) {
 	db := newTestDB(t)
-	first := sampleRide()
+	first := sampleRide(t)
 	first.StartDate = time.Date(2026, 7, 1, 7, 0, 0, 0, time.UTC)
-	second := sampleRide()
+	second := sampleRide(t)
 	second.ExternalID = "strava:5678"
 	second.Name = "Evening Ride"
 	require.NoError(t, Save(db, first))
diff --git a/web/server.go b/web/server.go
index c7b585e..f8388b9 100644
--- a/web/server.go
+++ b/web/server.go
@@ -89,7 +89,7 @@ func buildRideViews(items []rides.Ride) []RideView {
 		if err != nil {
 			slog.Warn("Failed to compute Cotacol", "ride", item.ExternalID, "file", item.GPXPath, "error", err)
 		} else {
-			score := parsed.DifficultyScore()
+			score := ride.Cotacol(parsed)
 			view.Cotacol = formatCotacol(score)
 			view.CotacolPer100Km = formatCotacolPer100Km(score, item.DistanceM)
 			view.cotacolScore = score
diff --git a/web/server_test.go b/web/server_test.go
index b6799cd..98ea681 100644
--- a/web/server_test.go
+++ b/web/server_test.go
@@ -5,6 +5,7 @@ import (
 	"net/http"
 	"net/http/httptest"
 	"net/url"
+	"os"
 	"path/filepath"
 	"strings"
 	"testing"
@@ -35,6 +36,8 @@ func newWebTestServer(t *testing.T) (*Server, *sql.DB) {
 			elapsed_time_s integer not null,
 			total_elevation_gain_m real not null,
 			average_speed_mps real not null,
+			cotacol_score real,
+			cotacol_algo_version text,
 			created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
 			updated_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
 		)
@@ -48,11 +51,18 @@ func newWebTestServer(t *testing.T) (*Server, *sql.DB) {
 	return NewServer(db, configPath), db
 }
 
+func testGPXPath(t *testing.T, name string) string {
+	t.Helper()
+	path := filepath.Join(t.TempDir(), name)
+	require.NoError(t, os.WriteFile(path, []byte(`<?xml version="1.0"?><gpx xmlns="http://www.topografix.com/GPX/1/1" version="1.1"><trk><trkseg><trkpt lat="43.0" lon="5.0"><ele>100</ele></trkpt><trkpt lat="43.001" lon="5.001"><ele>200</ele></trkpt></trkseg></trk></gpx>`), 0o600))
+	return path
+}
+
 func TestHandlerRendersRidesPage(t *testing.T) {
 	server, db := newWebTestServer(t)
 	require.NoError(t, rides.Save(db, rides.Ride{
 		ExternalID: "strava:1234",
-		GPXPath:    "missing.gpx",
+		GPXPath:    testGPXPath(t, "long.gpx"),
 		Name:       "Long Ride",
 		Type:       "Ride",
 		StartDate:  time.Date(2026, 8, 1, 7, 0, 0, 0, time.UTC),
@@ -60,7 +70,7 @@ func TestHandlerRendersRidesPage(t *testing.T) {
 	}))
 	require.NoError(t, rides.Save(db, rides.Ride{
 		ExternalID: "strava:5678",
-		GPXPath:    "short.gpx",
+		GPXPath:    testGPXPath(t, "short.gpx"),
 		Name:       "Short Ride",
 		Type:       "Ride",
 		StartDate:  time.Date(2026, 8, 2, 7, 0, 0, 0, time.UTC),
@@ -192,7 +202,7 @@ func TestHandlerSortsRidesByDistance(t *testing.T) {
 	server, db := newWebTestServer(t)
 	require.NoError(t, rides.Save(db, rides.Ride{
 		ExternalID: "strava:550e8400-e29b-41d4-a716-446655440000",
-		GPXPath:    "near.gpx",
+		GPXPath:    testGPXPath(t, "near.gpx"),
 		Name:       "Near Ride",
 		Type:       "Ride",
 		StartDate:  time.Date(2026, 8, 1, 7, 0, 0, 0, time.UTC),
@@ -200,7 +210,7 @@ func TestHandlerSortsRidesByDistance(t *testing.T) {
 	}))
 	require.NoError(t, rides.Save(db, rides.Ride{
 		ExternalID: "strava:6ba7b810-9dad-41d1-80b4-00c04fd430c8",
-		GPXPath:    "far.gpx",
+		GPXPath:    testGPXPath(t, "far.gpx"),
 		Name:       "Far Ride",
 		Type:       "Ride",
 		StartDate:  time.Date(2026, 8, 2, 7, 0, 0, 0, time.UTC),