Merge ride and rides packages into ride/store following mountain_pass structure

Consolidate persistence (Save, List, Backfill) into ride/store.go alongside
ride.go/climb.go/moving_time.go, rename rides.Ride to ride.StoredRide to
avoid collision with parsed ride.Ride, update all web/cli consumers and
RideView to use named Ride field, adjust templates and regenerate templ files,
and remove rides directory.

1 files changed, 268 insertions, 0 deletions

diff --git a/ride/store.go b/ride/store.go
new file mode 100644
index 0000000..af598e8
--- /dev/null
+++ b/ride/store.go
@@ -0,0 +1,268 @@
+package ride
+
+import (
+	"database/sql"
+	"fmt"
+	"log/slog"
+	"time"
+
+	"github.com/martinlehoux/kagamigo/kcore"
+)
+
+type StoredRide struct {
+	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
+
+const (
+	SortName       SortColumn = "name"
+	SortStartDate  SortColumn = "started"
+	SortDistance   SortColumn = "distance"
+	SortMovingTime SortColumn = "moving_time"
+	SortSpeed      SortColumn = "speed"
+	SortElevation  SortColumn = "elevation"
+	SortCotacol    SortColumn = "cotacol"
+	SortCotacolKm  SortColumn = "cotacol_100km"
+)
+
+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 StoredRide) error {
+	var cotacolScore any
+	var cotacolAlgorithmVersion any
+	var movingTimeS any
+	var movingTimeAlgorithmVersion any
+	if r.GPXPath != "" {
+		parsed, err := ParseFile(GPXRideParser{}, r.GPXPath)
+		if err != nil {
+			return fmt.Errorf("compute ride values for %q: %w", r.ExternalID, err)
+		}
+		cotacolScore = Cotacol(parsed)
+		cotacolAlgorithmVersion = CotacolAlgorithmVersion
+		movingTimeS = int64(MovingTime(parsed) / time.Second)
+		movingTimeAlgorithmVersion = MovingTimeAlgorithmVersion
+	}
+	_, err := db.Exec(`
+		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.ExternalMovingTimeS, movingTimeS, movingTimeAlgorithmVersion, r.ElapsedTimeS, r.TotalElevationGainM, r.AverageSpeedMps, cotacolScore, 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 []StoredRide
+	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
+	}
+	backfilled := 0
+	for _, item := range pending {
+		if err := Save(db, item); err != nil {
+			slog.Warn("Failed to backfill ride values", "ride", item.ExternalID, "file", item.GPXPath, "error", err)
+			continue
+		}
+		backfilled++
+	}
+	return backfilled, nil
+}
+
+func List(db *sql.DB) ([]StoredRide, error) {
+	return ListSorted(db, SortStartDate, true)
+}
+
+func ListSorted(db *sql.DB, column SortColumn, descending bool) ([]StoredRide, error) {
+	expression, ok := sortExpressions[column]
+	if !ok {
+		return nil, fmt.Errorf("invalid ride sort column %q", column)
+	}
+	direction := "ASC"
+	if descending {
+		direction = "DESC"
+	}
+	query := "SELECT " + columns + " FROM rides ORDER BY "
+	args := []any{}
+	if column == SortMovingTime {
+		args = append(args, 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, CotacolAlgorithmVersion)
+	}
+	query += expression + " " + direction + ", id DESC"
+	rows, err := db.Query(query, args...)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var rides []StoredRide
+	for rows.Next() {
+		ride, err := scanRide(rows)
+		if err != nil {
+			return nil, err
+		}
+		rides = append(rides, ride)
+	}
+	return rides, rows.Err()
+}
+
+var sortExpressions = map[SortColumn]string{
+	SortName:       "name COLLATE NOCASE",
+	SortStartDate:  "start_date",
+	SortDistance:   "distance_m",
+	SortMovingTime: "CASE WHEN moving_time_algo_version = ? THEN COALESCE(moving_time_s, external_moving_time_s) ELSE external_moving_time_s END",
+	SortSpeed:      "average_speed_mps",
+	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) (StoredRide, bool, error) {
+	row := db.QueryRow("SELECT "+columns+" FROM rides WHERE external_id = ?", externalID)
+	ride, err := scanRide(row)
+	if err == sql.ErrNoRows {
+		return StoredRide{}, false, nil
+	}
+	if err != nil {
+		return StoredRide{}, false, err
+	}
+	return ride, true, nil
+}
+
+func GetByID(db *sql.DB, id int64) (StoredRide, bool, error) {
+	row := db.QueryRow("SELECT "+columns+" FROM rides WHERE id = ?", id)
+	ride, err := scanRide(row)
+	if err == sql.ErrNoRows {
+		return StoredRide{}, false, nil
+	}
+	if err != nil {
+		return StoredRide{}, false, err
+	}
+	return ride, true, nil
+}
+
+type scanner interface {
+	Scan(dest ...any) error
+}
+
+func scanRide(s scanner) (StoredRide, error) {
+	var (
+		ride               StoredRide
+		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.ExternalMovingTimeS, &movingTimeS, &movingTimeAlgoVer, &ride.ElapsedTimeS, &ride.TotalElevationGainM, &ride.AverageSpeedMps, &cotacolScore, &cotacolAlgoVersion, &createdAt, &updatedAt)
+	if err != nil {
+		return StoredRide{}, err
+	}
+	if movingTimeS.Valid {
+		ride.movingTimeS = &movingTimeS.Int64
+	}
+	if movingTimeAlgoVer.Valid {
+		ride.movingTimeAlgoVersion = movingTimeAlgoVer.String
+	}
+	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 StoredRide{}, kcore.Wrap(err, "invalid start_date in rides row")
+	}
+	ride.CreatedAt, err = time.Parse(time.RFC3339, createdAt)
+	if err != nil {
+		return StoredRide{}, kcore.Wrap(err, "invalid created_at in rides row")
+	}
+	ride.UpdatedAt, err = time.Parse(time.RFC3339, updatedAt)
+	if err != nil {
+		return StoredRide{}, kcore.Wrap(err, "invalid updated_at in rides row")
+	}
+	return ride, nil
+}
+
+func (r StoredRide) CotacolAlgorithmVersion() string {
+	return r.cotacolAlgoVersion
+}
+
+func (r StoredRide) MovingTimeS() (int64, bool) {
+	if r.movingTimeS == nil {
+		return 0, false
+	}
+	return *r.movingTimeS, true
+}
+
+func (r StoredRide) 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 StoredRide) EffectiveMovingTimeS() int64 {
+	if moving, ok := r.MovingTimeS(); ok && r.movingTimeAlgoVersion == MovingTimeAlgorithmVersion {
+		return moving
+	}
+	return r.ExternalMovingTimeS
+}
+
+func (r StoredRide) CotacolScore() (float64, bool) {
+	if r.cotacolScore == nil {
+		return 0, false
+	}
+	return *r.cotacolScore, true
+}