Match official climbs against their recorded sample

An official climb matched on endpoint coordinates alone. On a loop ridden
the other way the route meets the climb's top before its bottom, so pairing
each start with the first end inside its lap window credited a 5.9 km
passage as the 850 m Chemin de la Tour Pey Blanc, and a 49 km one on
another ride.

Climbs now keep the sample of the segment they were created from: the
recorded length and elevation gain of the selected route points, captured
by the ride detail and preview handlers and stored NOT NULL. Matching takes
the whole route and drops any candidate outside ±50% of that sample on
either axis, and attempts are refreshed by upsert and prune so re-analyzing
a ride keeps the recorded-at stamp of the passages that survive.

The backfill leaves the web package: backfill.Run recomputes ride values
then official climb attempts as a single `go run . backfill` command
(climbs-backfill is gone), rideanalysis owns the attempt snapshot, and the
mountain-pass loading margin is shared. Applied to the stored rides it
stamps 78 samples, drops the four bogus attempts and leaves ride 800 with
its three Pont Rout passages.

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) {