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