perf: Load ride values from SQLite

5 files changed, 153 insertions, 60 deletions

diff --git a/rides/rides.go b/rides/rides.go
index 619fefa..fb6185f 100644
--- a/rides/rides.go
+++ b/rides/rides.go
@@ -3,6 +3,7 @@ package rides
 import (
 	"database/sql"
 	"fmt"
+	"log/slog"
 	"time"
 
 	"github.com/martinlehoux/biking_home/ride"
@@ -35,6 +36,8 @@ const (
 	SortDistance   SortColumn = "distance"
 	SortMovingTime SortColumn = "moving_time"
 	SortElevation  SortColumn = "elevation"
+	SortCotacol    SortColumn = "cotacol"
+	SortCotacolKm  SortColumn = "cotacol_100km"
 )
 
 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"
@@ -86,12 +89,15 @@ func Backfill(db *sql.DB) (int, error) {
 	if err := rows.Close(); err != nil {
 		return 0, err
 	}
-	for i, item := range pending {
+	backfilled := 0
+	for _, item := range pending {
 		if err := Save(db, item); err != nil {
-			return i, fmt.Errorf("backfill ride %q: %w", item.ExternalID, err)
+			slog.Warn("Failed to backfill ride values", "ride", item.ExternalID, "file", item.GPXPath, "error", err)
+			continue
 		}
+		backfilled++
 	}
-	return len(pending), nil
+	return backfilled, nil
 }
 
 func List(db *sql.DB) ([]Ride, error) {
@@ -107,7 +113,14 @@ func ListSorted(db *sql.DB, column SortColumn, descending bool) ([]Ride, error)
 	if descending {
 		direction = "DESC"
 	}
-	rows, err := db.Query("SELECT " + columns + " FROM rides ORDER BY " + expression + " " + direction + ", id DESC")
+	query := "SELECT " + columns + " FROM rides ORDER BY "
+	args := []any{}
+	if isComputedSort(column) {
+		query += "(cotacol_score IS NULL OR cotacol_algo_version IS NULL OR cotacol_algo_version <> ? OR distance_m <= 0) ASC, "
+		args = append(args, ride.CotacolAlgorithmVersion)
+	}
+	query += expression + " " + direction + ", id DESC"
+	rows, err := db.Query(query, args...)
 	if err != nil {
 		return nil, err
 	}
@@ -129,6 +142,12 @@ var sortExpressions = map[SortColumn]string{
 	SortDistance:   "distance_m",
 	SortMovingTime: "moving_time_s",
 	SortElevation:  "total_elevation_gain_m",
+	SortCotacol:    "cotacol_score",
+	SortCotacolKm:  "cotacol_score * 100000.0 / NULLIF(distance_m, 0)",
+}
+
+func isComputedSort(column SortColumn) bool {
+	return column == SortCotacol || column == SortCotacolKm
 }
 
 func GetByExternalID(db *sql.DB, externalID string) (Ride, bool, error) {
diff --git a/rides/rides_test.go b/rides/rides_test.go
index cbe5670..7f10ab5 100644
--- a/rides/rides_test.go
+++ b/rides/rides_test.go
@@ -117,6 +117,25 @@ func TestBackfill(t *testing.T) {
 	assert.Equal(t, 1, count)
 }
 
+func TestBackfillSkipsInvalidRide(t *testing.T) {
+	db := newTestDB(t)
+	_, 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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
+	`, "strava:550e8400-e29b-41d4-a716-446655440000", "missing.gpx", "Broken Ride", "Ride", "2026-08-01T07:00:00Z", 20_000, 0, 0, 0, 0)
+	require.NoError(t, err)
+
+	count, err := Backfill(db)
+	require.NoError(t, err)
+	assert.Zero(t, count)
+
+	got, ok, err := GetByExternalID(db, "strava:550e8400-e29b-41d4-a716-446655440000")
+	require.NoError(t, err)
+	require.True(t, ok)
+	_, found := got.CotacolScore()
+	assert.False(t, found)
+}
+
 func TestUpsertUpdatesExisting(t *testing.T) {
 	db := newTestDB(t)
 	ride := sampleRide(t)
@@ -149,3 +168,56 @@ func TestList(t *testing.T) {
 	assert.Equal(t, "strava:1234", rides[1].ExternalID)
 	kcore.Assert(len(rides) == 2, "two rides")
 }
+
+func TestListSortedByCotacolKeepsStaleLast(t *testing.T) {
+	db := newTestDB(t)
+	first := sampleRide(t)
+	first.ExternalID = "strava:550e8400-e29b-41d4-a716-446655440000"
+	first.DistanceM = 20_000
+	second := sampleRide(t)
+	second.ExternalID = "strava:6ba7b810-9dad-41d1-80b4-00c04fd430c8"
+	second.DistanceM = 40_000
+	require.NoError(t, Save(db, first))
+	require.NoError(t, Save(db, second))
+	_, err := db.Exec(`
+		UPDATE rides
+		SET cotacol_score = CASE external_id WHEN ? THEN 4 ELSE 6 END,
+			cotacol_algo_version = ?
+	`, first.ExternalID, ride.CotacolAlgorithmVersion)
+	require.NoError(t, err)
+
+	items, err := ListSorted(db, SortCotacol, false)
+	require.NoError(t, err)
+	assert.Equal(t, first.ExternalID, items[0].ExternalID)
+	assert.Equal(t, second.ExternalID, items[1].ExternalID)
+
+	_, err = db.Exec("UPDATE rides SET cotacol_algo_version = ? WHERE external_id = ?", "old", first.ExternalID)
+	require.NoError(t, err)
+	items, err = ListSorted(db, SortCotacol, false)
+	require.NoError(t, err)
+	assert.Equal(t, second.ExternalID, items[0].ExternalID)
+	assert.Equal(t, first.ExternalID, items[1].ExternalID)
+}
+
+func TestListSortedByCotacolPer100Km(t *testing.T) {
+	db := newTestDB(t)
+	first := sampleRide(t)
+	first.ExternalID = "strava:550e8400-e29b-41d4-a716-446655440000"
+	first.DistanceM = 20_000
+	second := sampleRide(t)
+	second.ExternalID = "strava:6ba7b810-9dad-41d1-80b4-00c04fd430c8"
+	second.DistanceM = 40_000
+	require.NoError(t, Save(db, first))
+	require.NoError(t, Save(db, second))
+	_, err := db.Exec(`
+		UPDATE rides
+		SET cotacol_score = CASE external_id WHEN ? THEN 4 ELSE 6 END,
+			cotacol_algo_version = ?
+	`, first.ExternalID, ride.CotacolAlgorithmVersion)
+	require.NoError(t, err)
+
+	items, err := ListSorted(db, SortCotacolKm, false)
+	require.NoError(t, err)
+	assert.Equal(t, second.ExternalID, items[0].ExternalID)
+	assert.Equal(t, first.ExternalID, items[1].ExternalID)
+}
diff --git a/web/server.go b/web/server.go
index f8388b9..845c31c 100644
--- a/web/server.go
+++ b/web/server.go
@@ -11,7 +11,6 @@ import (
 	"net/url"
 	"os"
 	"path/filepath"
-	"sort"
 	"strconv"
 	"strings"
 	"sync"
@@ -57,13 +56,11 @@ func (s *Server) handleRides(w http.ResponseWriter, r *http.Request) {
 	rideSort := parseRideSort(r.URL.Query())
 	items, err := s.listRides(rideSort)
 	if err != nil {
+		slog.Error("Failed to load rides", "sort", rideSort.Column, "descending", rideSort.Descending, "error", err)
 		http.Error(w, "failed to load rides", http.StatusInternalServerError)
 		return
 	}
 	views := buildRideViews(items)
-	if _, databaseColumn := rideSort.databaseColumn(); !databaseColumn {
-		sortRideViews(views, rideSort)
-	}
 	kcore.RenderPage(r.Context(), RidesPage(views, rideSortHeaders(rideSort)), w)
 }
 
@@ -78,54 +75,22 @@ func (s *Server) listRides(rideSort RideSort) ([]rides.Ride, error) {
 const minimumDisplayedDistanceM = 10_000
 
 func buildRideViews(items []rides.Ride) []RideView {
-	parser := ride.GPXRideParser{}
 	views := make([]RideView, 0, len(items))
 	for _, item := range items {
 		if item.DistanceM < minimumDisplayedDistanceM {
 			continue
 		}
-		view := RideView{Ride: item, Cotacol: "-"}
-		parsed, err := ride.ParseFile(parser, item.GPXPath)
-		if err != nil {
-			slog.Warn("Failed to compute Cotacol", "ride", item.ExternalID, "file", item.GPXPath, "error", err)
-		} else {
-			score := ride.Cotacol(parsed)
+		view := RideView{Ride: item, Cotacol: "-", CotacolPer100Km: "-"}
+		score, ready := item.CotacolScore()
+		if ready && item.CotacolAlgorithmVersion() == ride.CotacolAlgorithmVersion && item.DistanceM > 0 {
 			view.Cotacol = formatCotacol(score)
 			view.CotacolPer100Km = formatCotacolPer100Km(score, item.DistanceM)
-			view.cotacolScore = score
-			view.cotacolPer100Km = score * 100 / (item.DistanceM / 1000)
-			view.cotacolReady = true
 		}
 		views = append(views, view)
 	}
 	return views
 }
 
-func sortRideViews(items []RideView, rideSort RideSort) {
-	sort.SliceStable(items, func(i, j int) bool {
-		left, right := items[i], items[j]
-		if !left.cotacolReady || !right.cotacolReady {
-			if left.cotacolReady != right.cotacolReady {
-				return left.cotacolReady
-			}
-			return false
-		}
-		var leftValue, rightValue float64
-		switch rideSort.Column {
-		case rideSortCotacol:
-			leftValue, rightValue = left.cotacolScore, right.cotacolScore
-		case rideSortCotacolKm:
-			leftValue, rightValue = left.cotacolPer100Km, right.cotacolPer100Km
-		default:
-			return false
-		}
-		if rideSort.Descending {
-			return leftValue > rightValue
-		}
-		return leftValue < rightValue
-	})
-}
-
 func (s *Server) handleSyncForm(w http.ResponseWriter, r *http.Request) {
 	data := SyncPageData{
 		From:    queryOrDefault(r, "from", time.Now().AddDate(0, 0, -30).Format(dateFormat)),
diff --git a/web/server_test.go b/web/server_test.go
index 98ea681..d2e14f0 100644
--- a/web/server_test.go
+++ b/web/server_test.go
@@ -180,22 +180,25 @@ func TestRideSortHeadersToggleDirection(t *testing.T) {
 	assert.Equal(t, "ascending", headers[2].AriaSort)
 }
 
-func TestSortRideViewsByCotacolKeepsMissingLast(t *testing.T) {
-	items := []RideView{
-		{cotacolScore: 4, cotacolReady: true},
-		{cotacolScore: 1, cotacolReady: true},
-		{cotacolReady: false},
+func TestBuildRideViewsUsesStoredCotacol(t *testing.T) {
+	_, db := newWebTestServer(t)
+	ride := rides.Ride{
+		ExternalID: "strava:550e8400-e29b-41d4-a716-446655440000",
+		GPXPath:    testGPXPath(t, "stored.gpx"),
+		Name:       "Stored Ride",
+		Type:       "Ride",
+		StartDate:  time.Date(2026, 8, 1, 7, 0, 0, 0, time.UTC),
+		DistanceM:  20_000,
 	}
+	require.NoError(t, rides.Save(db, ride))
+	_, err := db.Exec("UPDATE rides SET gpx_path = ? WHERE external_id = ?", "missing.gpx", ride.ExternalID)
+	require.NoError(t, err)
+	items, err := rides.List(db)
+	require.NoError(t, err)
 
-	sortRideViews(items, RideSort{Column: rideSortCotacol})
-	assert.Equal(t, 1.0, items[0].cotacolScore)
-	assert.Equal(t, 4.0, items[1].cotacolScore)
-	assert.False(t, items[2].cotacolReady)
-
-	sortRideViews(items, RideSort{Column: rideSortCotacol, Descending: true})
-	assert.Equal(t, 4.0, items[0].cotacolScore)
-	assert.Equal(t, 1.0, items[1].cotacolScore)
-	assert.False(t, items[2].cotacolReady)
+	views := buildRideViews(items)
+	require.Len(t, views, 1)
+	assert.NotEqual(t, "-", views[0].Cotacol)
 }
 
 func TestHandlerSortsRidesByDistance(t *testing.T) {
@@ -226,3 +229,38 @@ func TestHandlerSortsRidesByDistance(t *testing.T) {
 	assert.Less(t, strings.Index(body, "Near Ride"), strings.Index(body, "Far Ride"))
 	assert.Contains(t, body, "dir=desc&amp;sort=distance")
 }
+
+func TestHandlerSortsRidesByCotacol(t *testing.T) {
+	server, db := newWebTestServer(t)
+	first := rides.Ride{
+		ExternalID: "strava:550e8400-e29b-41d4-a716-446655440000",
+		GPXPath:    testGPXPath(t, "first.gpx"),
+		Name:       "Low Cotacol",
+		Type:       "Ride",
+		StartDate:  time.Date(2026, 8, 1, 7, 0, 0, 0, time.UTC),
+		DistanceM:  20_000,
+	}
+	second := rides.Ride{
+		ExternalID: "strava:6ba7b810-9dad-41d1-80b4-00c04fd430c8",
+		GPXPath:    testGPXPath(t, "second.gpx"),
+		Name:       "High Cotacol",
+		Type:       "Ride",
+		StartDate:  time.Date(2026, 8, 2, 7, 0, 0, 0, time.UTC),
+		DistanceM:  40_000,
+	}
+	require.NoError(t, rides.Save(db, first))
+	require.NoError(t, rides.Save(db, second))
+	_, err := db.Exec(`
+		UPDATE rides
+		SET cotacol_score = CASE external_id WHEN ? THEN 1 ELSE 4 END
+	`, first.ExternalID)
+	require.NoError(t, err)
+
+	req := httptest.NewRequest(http.MethodGet, "/?sort=cotacol&dir=asc", nil)
+	response := httptest.NewRecorder()
+	server.Handler().ServeHTTP(response, req)
+
+	body := response.Body.String()
+	assert.Equal(t, http.StatusOK, response.Code)
+	assert.Less(t, strings.Index(body, "Low Cotacol"), strings.Index(body, "High Cotacol"))
+}
diff --git a/web/views.go b/web/views.go
index a19ced9..a9ffffa 100644
--- a/web/views.go
+++ b/web/views.go
@@ -12,9 +12,6 @@ type RideView struct {
 	rides.Ride
 	Cotacol         string
 	CotacolPer100Km string
-	cotacolScore    float64
-	cotacolPer100Km float64
-	cotacolReady    bool
 }
 
 type RideSort struct {
@@ -66,6 +63,8 @@ func (s RideSort) databaseColumn() (rides.SortColumn, bool) {
 		rideSortDistance:   rides.SortDistance,
 		rideSortMovingTime: rides.SortMovingTime,
 		rideSortElevation:  rides.SortElevation,
+		rideSortCotacol:    rides.SortCotacol,
+		rideSortCotacolKm:  rides.SortCotacolKm,
 	}
 	column, found := columns[s.Column]
 	return column, found