1 files changed, 26 insertions, 15 deletions

diff --git a/official_climb/attempt.go b/official_climb/attempt.go
index 43778f0..c340fea 100644
--- a/official_climb/attempt.go
+++ b/official_climb/attempt.go
@@ -3,14 +3,13 @@ package official_climb
 import (
 	"database/sql"
 	"fmt"
+	"strings"
 	"time"
 
 	"github.com/martinlehoux/biking_home/climb"
 )
 
-// Attempt is one recorded passage of an official climb during one ride,
-// identified by (rideID, climbID, occurrence). Scalar stats are snapshotted at
-// recording time so climb listings never re-parse ride files.
+// Attempt is one recorded passage of an official climb in a ride, keyed by (rideID, climbID, occurrence); stats are snapshotted so listings never re-parse ride files.
 type Attempt struct {
 	RideID             int64
 	ClimbID            int64
@@ -27,14 +26,12 @@ type Attempt struct {
 
 const attemptColumns = "a.ride_id, a.climb_id, a.occurrence, a.distance_m, a.elevation_gain_m, a.slope_percent, a.cotacol, a.cotacol_algo_version, a.started_at, a.ended_at, a.created_at"
 
-// AttemptsForClimb returns every recorded attempt of one official climb,
-// oldest ride first.
+// AttemptsForClimb returns every recorded attempt of one official climb, oldest ride first.
 func AttemptsForClimb(db *sql.DB, climbID int64) ([]Attempt, error) {
 	return listAttempts(db, "climb_id = ?", climbID, "r.start_date, a.ride_id, a.occurrence")
 }
 
-// AttemptsForRide returns every recorded attempt within one ride, in route
-// order.
+// AttemptsForRide returns every recorded attempt within one ride, in route order.
 func AttemptsForRide(db *sql.DB, rideID int64) ([]Attempt, error) {
 	return listAttempts(db, "ride_id = ?", rideID, "a.occurrence")
 }
@@ -64,29 +61,45 @@ func listAttempts(db *sql.DB, where string, arg any, order string) ([]Attempt, e
 	return attempts, nil
 }
 
-// ReplaceRideAttempts atomically rewrites every attempt row of one ride,
-// dropping stale matches and inserting the freshly analyzed ones.
+// ReplaceRideAttempts refreshes a ride's attempt rows: fresh matches are written, stale ones dropped, the recorded-at stamp of survivors kept.
 func ReplaceRideAttempts(db *sql.DB, rideID int64, attempts []Attempt) error {
 	tx, err := db.Begin()
 	if err != nil {
 		return fmt.Errorf("replace ride attempts: %w", err)
 	}
 	defer func() { _ = tx.Rollback() }()
-	if _, err := tx.Exec("DELETE FROM ride_official_climb_attempts WHERE ride_id = ?", rideID); err != nil {
-		return fmt.Errorf("delete ride attempts: %w", err)
-	}
+	keys := make([]string, 0, len(attempts))
+	args := make([]any, 0, 2*len(attempts)+1)
+	args = append(args, rideID)
 	for _, attempt := range attempts {
 		if attempt.RideID != rideID {
 			return fmt.Errorf("replace ride attempts: attempt references ride %d, expected %d", attempt.RideID, rideID)
 		}
+		keys = append(keys, "(?, ?)")
+		args = append(args, attempt.ClimbID, attempt.Occurrence)
 		if _, err := tx.Exec(`
 			INSERT INTO ride_official_climb_attempts (ride_id, climb_id, occurrence, distance_m, elevation_gain_m, slope_percent, cotacol, cotacol_algo_version, started_at, ended_at)
 			VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
+			ON CONFLICT(ride_id, climb_id, occurrence) DO UPDATE SET
+				distance_m = excluded.distance_m,
+				elevation_gain_m = excluded.elevation_gain_m,
+				slope_percent = excluded.slope_percent,
+				cotacol = excluded.cotacol,
+				cotacol_algo_version = excluded.cotacol_algo_version,
+				started_at = excluded.started_at,
+				ended_at = excluded.ended_at
 		`, attempt.RideID, attempt.ClimbID, attempt.Occurrence, attempt.DistanceM, attempt.ElevationGainM, attempt.SlopePercent, attempt.Cotacol, attempt.CotacolAlgoVersion,
 			attempt.StartedAt.UTC().Format(time.RFC3339), attempt.EndedAt.UTC().Format(time.RFC3339)); err != nil {
 			return fmt.Errorf("insert ride attempt: %w", err)
 		}
 	}
+	prune := "DELETE FROM ride_official_climb_attempts WHERE ride_id = ?"
+	if len(keys) > 0 {
+		prune += " AND (climb_id, occurrence) NOT IN (VALUES " + strings.Join(keys, ", ") + ")"
+	}
+	if _, err := tx.Exec(prune, args...); err != nil {
+		return fmt.Errorf("drop stale ride attempts: %w", err)
+	}
 	if err := tx.Commit(); err != nil {
 		return fmt.Errorf("commit ride attempts: %w", err)
 	}
@@ -132,9 +145,7 @@ type AttemptStats struct {
 	EndIndex        int
 }
 
-// AttemptFromStats builds the persisted snapshot of one analyzed climb
-// occurrence. Timestamps come from the ride's recorded points bounding the
-// climb interval.
+// AttemptFromStats snapshots one analyzed climb occurrence; timestamps come from the ride points bounding the climb interval.
 func AttemptFromStats(rideID int64, occurrence int, stats AttemptStats, timestamps []time.Time) Attempt {
 	startedAt, endedAt := time.Time{}, time.Time{}
 	if stats.StartIndex < len(timestamps) {