feat: Persist Cotacol ride values

1 files changed, 68 insertions, 10 deletions

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
+}