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, 135 insertions, 0 deletions

diff --git a/backfill/backfill.go b/backfill/backfill.go
new file mode 100644
index 0000000..4cf934c
--- /dev/null
+++ b/backfill/backfill.go
@@ -0,0 +1,135 @@
+// Package backfill recomputes the data derived from stored rides: ride values and official climb attempts.
+package backfill
+
+import (
+	"database/sql"
+	"fmt"
+	"log/slog"
+	"slices"
+
+	"github.com/martinlehoux/biking_home/config"
+	"github.com/martinlehoux/biking_home/mountain_pass"
+	"github.com/martinlehoux/biking_home/official_climb"
+	"github.com/martinlehoux/biking_home/ride"
+	"github.com/martinlehoux/biking_home/rideanalysis"
+	"github.com/martinlehoux/kagamigo/kcore"
+	"github.com/schollz/progressbar/v3"
+)
+
+// Run recomputes ride values, then official climb attempts.
+func Run(db *sql.DB, configPath string) {
+	ride.BackfillCmd(db)
+	var bar *progressbar.ProgressBar
+	count, err := ClimbAttempts(db, configPath, func(current, total int) {
+		if bar == nil && mountain_pass.ProgressBarEnabled() {
+			bar = progressbar.Default(int64(total), "Backfilling climb attempts")
+		}
+		if bar != nil {
+			bar.Set(current)
+		}
+	})
+	kcore.Expect(err, "failed to backfill climb attempts")
+	slog.Info("Backfilled climb attempts", "rides", count)
+}
+
+// ClimbAttempts re-records the official climb attempts of every route-bearing ride and flags the climbs the sample band emptied.
+func ClimbAttempts(db *sql.DB, configPath string, reporters ...func(current, total int)) (int, error) {
+	appConfig, err := config.Load(configPath)
+	if err != nil {
+		return 0, err
+	}
+	matchPolicy := official_climb.DefaultMatchPolicy()
+	matchPolicy.EndpointRadiusM = appConfig.OfficialClimb.MatchRadiusM
+	before, err := climbAttemptCounts(db)
+	if err != nil {
+		return 0, err
+	}
+	items, err := ride.List(db)
+	if err != nil {
+		return 0, err
+	}
+	officialClimbs, err := official_climb.List(db)
+	if err != nil {
+		return 0, err
+	}
+	report := func(int, int) {}
+	if len(reporters) > 0 {
+		report = reporters[0]
+	}
+	backfilled := 0
+	for i, item := range items {
+		report(i+1, len(items))
+		if item.FitPath == "" && item.GPXPath == "" {
+			continue
+		}
+		parsed, err := ride.ParseStoredRide(item)
+		if err != nil {
+			slog.Warn("Failed to parse ride for climb backfill", "ride_id", item.ID, "error", err)
+			continue
+		}
+		passes, err := mountain_pass.LoadMountainPassesAroundRide(db, parsed.Coords, rideanalysis.PassMarginM)
+		if err != nil {
+			slog.Warn("Failed to load passes for climb backfill", "ride_id", item.ID, "error", err)
+		}
+		analysis := rideanalysis.Analyze(parsed, passes, officialClimbs, matchPolicy)
+		attempts := rideanalysis.Attempts(item.ID, analysis, parsed)
+		if err := official_climb.ReplaceRideAttempts(db, item.ID, attempts); err != nil {
+			slog.Warn("Failed to record climb attempts", "ride_id", item.ID, "error", err)
+			continue
+		}
+		backfilled++
+	}
+	if err := reportAttemptDrops(db, before); err != nil {
+		return backfilled, err
+	}
+	return backfilled, nil
+}
+
+// climbAttemptCounts returns the number of recorded attempts per climb.
+func climbAttemptCounts(db *sql.DB) (map[int64]int, error) {
+	rows, err := db.Query("SELECT climb_id, COUNT(*) FROM ride_official_climb_attempts GROUP BY climb_id")
+	if err != nil {
+		return nil, fmt.Errorf("count climb attempts: %w", err)
+	}
+	defer rows.Close()
+	counts := make(map[int64]int)
+	for rows.Next() {
+		var (
+			climbID int64
+			count   int
+		)
+		if err := rows.Scan(&climbID, &count); err != nil {
+			return nil, fmt.Errorf("scan climb attempt count: %w", err)
+		}
+		counts[climbID] = count
+	}
+	if err := rows.Err(); err != nil {
+		return nil, fmt.Errorf("iterate climb attempt counts: %w", err)
+	}
+	return counts, nil
+}
+
+// reportAttemptDrops flags the climbs that lost attempts to the sample band; one keeping less than half needs review.
+func reportAttemptDrops(db *sql.DB, before map[int64]int) error {
+	after, err := climbAttemptCounts(db)
+	if err != nil {
+		return err
+	}
+	climbIDs := make([]int64, 0, len(before))
+	for climbID := range before {
+		climbIDs = append(climbIDs, climbID)
+	}
+	slices.Sort(climbIDs)
+	for _, climbID := range climbIDs {
+		previous, kept := before[climbID], after[climbID]
+		if kept >= previous {
+			continue
+		}
+		if kept*2 < previous {
+			slog.Warn("Climb kept less than half of its attempts; review its sample", "climb_id", climbID, "before", previous, "after", kept)
+			continue
+		}
+		slog.Info("Climb attempts dropped outside the sample band", "climb_id", climbID, "before", previous, "after", kept)
+	}
+	return nil
+}