1 files changed, 133 insertions, 0 deletions
diff --git a/official_climb/list.go b/official_climb/list.go
new file mode 100644
index 0000000..33b12e8
--- /dev/null
+++ b/official_climb/list.go
@@ -0,0 +1,133 @@
+package official_climb
+
+import (
+	"database/sql"
+	"fmt"
+	"time"
+
+	"github.com/martinlehoux/biking_home/climb"
+)
+
+// SortColumn is a climbs-list ordering key.
+type SortColumn string
+
+const (
+	SortName     SortColumn = "name"
+	SortAttempts SortColumn = "attempts"
+	SortDistance SortColumn = "distance"
+	SortSlope    SortColumn = "slope"
+	SortCotacol  SortColumn = "cotacol"
+	SortBestTime SortColumn = "best_time"
+)
+
+// ListedClimb is one climbs-list row: official climb identity plus aggregates
+// over its recorded attempts. Medians are computed in Go from the attempt
+// rows; best time is the minimum attempt duration.
+type ListedClimb struct {
+	ID             int64
+	Name           string
+	Attempts       int
+	DistanceM      float64
+	ElevationGainM float64
+	SlopePercent   float64
+	Cotacol        float64
+	BestDurationS  float64
+	HasBest        bool
+}
+
+func (c ListedClimb) Category() string {
+	return climb.CategoryOf(c.Cotacol).String()
+}
+
+// ListWithAggregates returns every official climb with aggregates over its
+// attempts. Attempts default to zero for climbs never ridden; climbs without
+// attempts show no distance/slope stats.
+func ListWithAggregates(db *sql.DB) ([]ListedClimb, error) {
+	climbRows, err := db.Query("SELECT id, name FROM official_climbs ORDER BY name ASC")
+	if err != nil {
+		return nil, fmt.Errorf("list climbs: %w", err)
+	}
+	defer climbRows.Close()
+	climbs := make([]ListedClimb, 0)
+	index := make(map[int64]int)
+	for climbRows.Next() {
+		var row ListedClimb
+		if err := climbRows.Scan(&row.ID, &row.Name); err != nil {
+			return nil, err
+		}
+		index[row.ID] = len(climbs)
+		climbs = append(climbs, row)
+	}
+	if err := climbRows.Err(); err != nil {
+		return nil, fmt.Errorf("iterate climbs: %w", err)
+	}
+
+	attemptRows, err := db.Query(`
+		SELECT climb_id, distance_m, elevation_gain_m, slope_percent, cotacol, started_at, ended_at
+		FROM ride_official_climb_attempts`)
+	if err != nil {
+		return nil, fmt.Errorf("list climb attempts: %w", err)
+	}
+	defer attemptRows.Close()
+	samples := make(map[int64]*attemptSamples)
+	for attemptRows.Next() {
+		var (
+			climbID   int64
+			distanceM float64
+			gainM     float64
+			slope     float64
+			cotacol   float64
+			startedAt string
+			endedAt   string
+		)
+		if err := attemptRows.Scan(&climbID, &distanceM, &gainM, &slope, &cotacol, &startedAt, &endedAt); err != nil {
+			return nil, err
+		}
+		sample, known := samples[climbID]
+		if !known {
+			sample = &attemptSamples{}
+			samples[climbID] = sample
+		}
+		sample.distances = append(sample.distances, distanceM)
+		sample.gains = append(sample.gains, gainM)
+		sample.slopes = append(sample.slopes, slope)
+		sample.cotacols = append(sample.cotacols, cotacol)
+		start, err := time.Parse(time.RFC3339, startedAt)
+		if err != nil {
+			return nil, fmt.Errorf("invalid ride_official_climb_attempts.started_at: %w", err)
+		}
+		end, err := time.Parse(time.RFC3339, endedAt)
+		if err != nil {
+			return nil, fmt.Errorf("invalid ride_official_climb_attempts.ended_at: %w", err)
+		}
+		durationS := end.Sub(start).Seconds()
+		if durationS > 0 && (sample.bestDurationS == 0 || durationS < sample.bestDurationS) {
+			sample.bestDurationS = durationS
+		}
+	}
+	if err := attemptRows.Err(); err != nil {
+		return nil, fmt.Errorf("iterate climb attempts: %w", err)
+	}
+
+	for id, sample := range samples {
+		row := &climbs[index[id]]
+		row.Attempts = len(sample.distances)
+		row.DistanceM = Median(sample.distances)
+		row.ElevationGainM = Median(sample.gains)
+		row.SlopePercent = Median(sample.slopes)
+		row.Cotacol = Median(sample.cotacols)
+		if sample.bestDurationS > 0 {
+			row.BestDurationS = sample.bestDurationS
+			row.HasBest = true
+		}
+	}
+	return climbs, nil
+}
+
+type attemptSamples struct {
+	distances     []float64
+	gains         []float64
+	slopes        []float64
+	cotacols      []float64
+	bestDurationS float64
+}