feat(ride): persist canonical moving time, preserve Strava reference

1 files changed, 61 insertions, 23 deletions

diff --git a/rides/rides.go b/rides/rides.go
index f68952c..b0ce5fb 100644
--- a/rides/rides.go
+++ b/rides/rides.go
@@ -11,21 +11,23 @@ import (
 )
 
 type Ride struct {
-	ID                  int64
-	ExternalID          string
-	GPXPath             string
-	Name                string
-	Type                string
-	StartDate           time.Time
-	DistanceM           float64
-	MovingTimeS         int64
-	ElapsedTimeS        int64
-	TotalElevationGainM float64
-	AverageSpeedMps     float64
-	cotacolScore        *float64
-	cotacolAlgoVersion  string
-	CreatedAt           time.Time
-	UpdatedAt           time.Time
+	ID                    int64
+	ExternalID            string
+	GPXPath               string
+	Name                  string
+	Type                  string
+	StartDate             time.Time
+	DistanceM             float64
+	ExternalMovingTimeS   int64
+	movingTimeS           *int64
+	movingTimeAlgoVersion string
+	ElapsedTimeS          int64
+	TotalElevationGainM   float64
+	AverageSpeedMps       float64
+	cotacolScore          *float64
+	cotacolAlgoVersion    string
+	CreatedAt             time.Time
+	UpdatedAt             time.Time
 }
 
 type SortColumn string
@@ -40,36 +42,42 @@ const (
 	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"
+const columns = "id, external_id, gpx_path, name, type, start_date, distance_m, external_moving_time_s, moving_time_s, moving_time_algo_version, 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 {
 	var cotacolScore any
 	var cotacolAlgorithmVersion any
+	var movingTimeS any
+	var movingTimeAlgorithmVersion any
 	if r.GPXPath != "" {
 		parsed, err := ride.ParseFile(ride.GPXRideParser{}, r.GPXPath)
 		if err != nil {
-			return fmt.Errorf("compute Cotacol for ride %q: %w", r.ExternalID, err)
+			return fmt.Errorf("compute ride values for %q: %w", r.ExternalID, err)
 		}
 		cotacolScore = ride.Cotacol(parsed)
 		cotacolAlgorithmVersion = ride.CotacolAlgorithmVersion
+		movingTimeS = int64(ride.MovingTime(parsed) / time.Second)
+		movingTimeAlgorithmVersion = ride.MovingTimeAlgorithmVersion
 	}
 	_, 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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
+		INSERT INTO rides (external_id, gpx_path, name, type, start_date, distance_m, external_moving_time_s, moving_time_s, moving_time_algo_version, 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,
 			type = excluded.type,
 			start_date = excluded.start_date,
 			distance_m = excluded.distance_m,
+			external_moving_time_s = excluded.external_moving_time_s,
 			moving_time_s = excluded.moving_time_s,
+			moving_time_algo_version = excluded.moving_time_algo_version,
 			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, cotacolScore, cotacolAlgorithmVersion)
+	`, r.ExternalID, r.GPXPath, r.Name, r.Type, r.StartDate.UTC().Format(time.RFC3339), r.DistanceM, r.ExternalMovingTimeS, movingTimeS, movingTimeAlgorithmVersion, r.ElapsedTimeS, r.TotalElevationGainM, r.AverageSpeedMps, cotacolScore, cotacolAlgorithmVersion)
 	return err
 }
 
@@ -120,7 +128,9 @@ func ListSorted(db *sql.DB, column SortColumn, descending bool) ([]Ride, error)
 	}
 	query := "SELECT " + columns + " FROM rides ORDER BY "
 	args := []any{}
-	if isComputedSort(column) {
+	if column == SortMovingTime {
+		args = append(args, ride.MovingTimeAlgorithmVersion)
+	} else 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)
 	}
@@ -145,7 +155,7 @@ var sortExpressions = map[SortColumn]string{
 	SortName:       "name COLLATE NOCASE",
 	SortStartDate:  "start_date",
 	SortDistance:   "distance_m",
-	SortMovingTime: "moving_time_s",
+	SortMovingTime: "CASE WHEN moving_time_algo_version = ? THEN COALESCE(moving_time_s, external_moving_time_s) ELSE external_moving_time_s END",
 	SortElevation:  "total_elevation_gain_m",
 	SortCotacol:    "cotacol_score",
 	SortCotacolKm:  "cotacol_score * 100000.0 / NULLIF(distance_m, 0)",
@@ -187,15 +197,23 @@ func scanRide(s scanner) (Ride, error) {
 	var (
 		ride               Ride
 		startDate          string
+		movingTimeS        sql.NullInt64
+		movingTimeAlgoVer  sql.NullString
 		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, &cotacolScore, &cotacolAlgoVersion, &createdAt, &updatedAt)
+	err := s.Scan(&ride.ID, &ride.ExternalID, &ride.GPXPath, &ride.Name, &ride.Type, &startDate, &ride.DistanceM, &ride.ExternalMovingTimeS, &movingTimeS, &movingTimeAlgoVer, &ride.ElapsedTimeS, &ride.TotalElevationGainM, &ride.AverageSpeedMps, &cotacolScore, &cotacolAlgoVersion, &createdAt, &updatedAt)
 	if err != nil {
 		return Ride{}, err
 	}
+	if movingTimeS.Valid {
+		ride.movingTimeS = &movingTimeS.Int64
+	}
+	if movingTimeAlgoVer.Valid {
+		ride.movingTimeAlgoVersion = movingTimeAlgoVer.String
+	}
 	if cotacolScore.Valid {
 		ride.cotacolScore = &cotacolScore.Float64
 	}
@@ -221,6 +239,26 @@ func (r Ride) CotacolAlgorithmVersion() string {
 	return r.cotacolAlgoVersion
 }
 
+func (r Ride) MovingTimeS() (int64, bool) {
+	if r.movingTimeS == nil {
+		return 0, false
+	}
+	return *r.movingTimeS, true
+}
+
+func (r Ride) MovingTimeAlgorithmVersion() string {
+	return r.movingTimeAlgoVersion
+}
+
+// EffectiveMovingTimeS is the canonical moving time when the stored value
+// matches the current algorithm version, falling back to Strava's value.
+func (r Ride) EffectiveMovingTimeS() int64 {
+	if moving, ok := r.MovingTimeS(); ok && r.movingTimeAlgoVersion == ride.MovingTimeAlgorithmVersion {
+		return moving
+	}
+	return r.ExternalMovingTimeS
+}
+
 func (r Ride) CotacolScore() (float64, bool) {
 	if r.cotacolScore == nil {
 		return 0, false