Add hardcore ride categorisation with bike and ride types

9 files changed, 331 insertions, 7 deletions

diff --git a/db/migrations/20260823000000_ride_categories.sql b/db/migrations/20260823000000_ride_categories.sql
new file mode 100644
index 0000000..0ae4f1b
--- /dev/null
+++ b/db/migrations/20260823000000_ride_categories.sql
@@ -0,0 +1,29 @@
+-- migrate:up
+ALTER TABLE rides ADD COLUMN bike_type TEXT NOT NULL DEFAULT '' CHECK (bike_type IN ('', 'road', 'mtb'));
+ALTER TABLE rides ADD COLUMN ride_type TEXT NOT NULL DEFAULT '' CHECK (ride_type IN ('', 'training', 'indoor', 'race', 'transport'));
+
+-- migrate:down
+-- SQLite does not support DROP COLUMN with CHECK in older versions, recreate table without columns
+CREATE TABLE rides_new (
+    id integer primary key,
+    external_id text unique not null,
+    gpx_path text not null,
+    name text not null,
+    type text not null,
+    start_date text not null,
+    distance_m real not null,
+    external_moving_time_s integer not null,
+    elapsed_time_s integer not null,
+    total_elevation_gain_m real not null,
+    average_speed_mps real not null,
+    created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
+    updated_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
+    cotacol_score real,
+    cotacol_algo_version text,
+    moving_time_s integer,
+    moving_time_algo_version text
+);
+INSERT INTO rides_new (id, external_id, gpx_path, name, type, start_date, distance_m, external_moving_time_s, elapsed_time_s, total_elevation_gain_m, average_speed_mps, created_at, updated_at, cotacol_score, cotacol_algo_version, moving_time_s, moving_time_algo_version)
+SELECT id, external_id, gpx_path, name, type, start_date, distance_m, external_moving_time_s, elapsed_time_s, total_elevation_gain_m, average_speed_mps, created_at, updated_at, cotacol_score, cotacol_algo_version, moving_time_s, moving_time_algo_version FROM rides;
+DROP TABLE rides;
+ALTER TABLE rides_new RENAME TO rides;
diff --git a/db/schema.sql b/db/schema.sql
index 60505bb..9713add 100644
--- a/db/schema.sql
+++ b/db/schema.sql
@@ -21,7 +21,7 @@ CREATE TABLE rides (
     average_speed_mps real not null,
     created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
     updated_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
-, cotacol_score real, cotacol_algo_version text, moving_time_s integer, moving_time_algo_version text);
+, cotacol_score real, cotacol_algo_version text, moving_time_s integer, moving_time_algo_version text, bike_type TEXT NOT NULL DEFAULT '' CHECK (bike_type IN ('', 'road', 'mtb')), ride_type TEXT NOT NULL DEFAULT '' CHECK (ride_type IN ('', 'training', 'indoor', 'race', 'transport')));
 CREATE TABLE official_climbs (
     id integer primary key,
     name text not null,
@@ -40,4 +40,5 @@ INSERT INTO "schema_migrations" (version) VALUES
   ('20260807000000'),
   ('20260813000000'),
   ('20260820184307'),
-  ('20260821000000');
+  ('20260821000000'),
+  ('20260823000000');
diff --git a/ride/category.go b/ride/category.go
new file mode 100644
index 0000000..0eafcc0
--- /dev/null
+++ b/ride/category.go
@@ -0,0 +1,56 @@
+package ride
+
+type BikeType string
+
+const (
+	BikeTypeRoad BikeType = "road"
+	BikeTypeMTB  BikeType = "mtb"
+)
+
+type RideType string
+
+const (
+	RideTypeTraining  RideType = "training"
+	RideTypeIndoor    RideType = "indoor"
+	RideTypeRace      RideType = "race"
+	RideTypeTransport RideType = "transport"
+)
+
+var validBikeTypes = map[BikeType]struct{}{
+	BikeTypeRoad: {},
+	BikeTypeMTB:  {},
+}
+
+var validRideTypes = map[RideType]struct{}{
+	RideTypeTraining:  {},
+	RideTypeIndoor:    {},
+	RideTypeRace:      {},
+	RideTypeTransport: {},
+}
+
+var AllBikeTypes = []BikeType{BikeTypeRoad, BikeTypeMTB}
+var AllRideTypes = []RideType{RideTypeTraining, RideTypeIndoor, RideTypeRace, RideTypeTransport}
+
+func IsValidBikeType(value string) bool {
+	_, ok := validBikeTypes[BikeType(value)]
+	return ok
+}
+
+func IsValidRideType(value string) bool {
+	_, ok := validRideTypes[RideType(value)]
+	return ok
+}
+
+func IsValidOrEmptyBikeType(value string) bool {
+	if value == "" {
+		return true
+	}
+	return IsValidBikeType(value)
+}
+
+func IsValidOrEmptyRideType(value string) bool {
+	if value == "" {
+		return true
+	}
+	return IsValidRideType(value)
+}
diff --git a/ride/store.go b/ride/store.go
index af598e8..162b912 100644
--- a/ride/store.go
+++ b/ride/store.go
@@ -25,6 +25,8 @@ type StoredRide struct {
 	AverageSpeedMps       float64
 	cotacolScore          *float64
 	cotacolAlgoVersion    string
+	BikeType              BikeType
+	RideType              RideType
 	CreatedAt             time.Time
 	UpdatedAt             time.Time
 }
@@ -42,7 +44,7 @@ const (
 	SortCotacolKm  SortColumn = "cotacol_100km"
 )
 
-const columns = "id, external_id, gpx_path, name, type, start_date, distance_m, external_moving_time_s, moving_time_s, moving_time_algo_version, elapsed_time_s, total_elevation_gain_m, average_speed_mps, cotacol_score, cotacol_algo_version, created_at, updated_at"
+const columns = "id, external_id, gpx_path, name, type, start_date, distance_m, external_moving_time_s, moving_time_s, moving_time_algo_version, elapsed_time_s, total_elevation_gain_m, average_speed_mps, cotacol_score, cotacol_algo_version, bike_type, ride_type, created_at, updated_at"
 
 func Save(db *sql.DB, r StoredRide) error {
 	var cotacolScore any
@@ -59,9 +61,15 @@ func Save(db *sql.DB, r StoredRide) error {
 		movingTimeS = int64(MovingTime(parsed) / time.Second)
 		movingTimeAlgorithmVersion = MovingTimeAlgorithmVersion
 	}
+	if !IsValidOrEmptyBikeType(string(r.BikeType)) {
+		return fmt.Errorf("invalid bike_type %q", r.BikeType)
+	}
+	if !IsValidOrEmptyRideType(string(r.RideType)) {
+		return fmt.Errorf("invalid ride_type %q", r.RideType)
+	}
 	_, err := db.Exec(`
-		INSERT INTO rides (external_id, gpx_path, name, type, start_date, distance_m, external_moving_time_s, moving_time_s, moving_time_algo_version, elapsed_time_s, total_elevation_gain_m, average_speed_mps, cotacol_score, cotacol_algo_version)
-		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
+		INSERT INTO rides (external_id, gpx_path, name, type, start_date, distance_m, external_moving_time_s, moving_time_s, moving_time_algo_version, elapsed_time_s, total_elevation_gain_m, average_speed_mps, cotacol_score, cotacol_algo_version, bike_type, ride_type)
+		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
 		ON CONFLICT(external_id) DO UPDATE SET
 			gpx_path = excluded.gpx_path,
 			name = excluded.name,
@@ -76,8 +84,10 @@ func Save(db *sql.DB, r StoredRide) error {
 			average_speed_mps = excluded.average_speed_mps,
 			cotacol_score = excluded.cotacol_score,
 			cotacol_algo_version = excluded.cotacol_algo_version,
+			bike_type = CASE WHEN excluded.bike_type = '' THEN rides.bike_type ELSE excluded.bike_type END,
+			ride_type = CASE WHEN excluded.ride_type = '' THEN rides.ride_type ELSE excluded.ride_type END,
 			updated_at = strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
-	`, r.ExternalID, r.GPXPath, r.Name, r.Type, r.StartDate.UTC().Format(time.RFC3339), r.DistanceM, r.ExternalMovingTimeS, movingTimeS, movingTimeAlgorithmVersion, r.ElapsedTimeS, r.TotalElevationGainM, r.AverageSpeedMps, cotacolScore, cotacolAlgorithmVersion)
+	`, r.ExternalID, r.GPXPath, r.Name, r.Type, r.StartDate.UTC().Format(time.RFC3339), r.DistanceM, r.ExternalMovingTimeS, movingTimeS, movingTimeAlgorithmVersion, r.ElapsedTimeS, r.TotalElevationGainM, r.AverageSpeedMps, cotacolScore, cotacolAlgorithmVersion, string(r.BikeType), string(r.RideType))
 	return err
 }
 
@@ -202,10 +212,12 @@ func scanRide(s scanner) (StoredRide, error) {
 		movingTimeAlgoVer  sql.NullString
 		cotacolScore       sql.NullFloat64
 		cotacolAlgoVersion sql.NullString
+		bikeType           string
+		rideType           string
 		createdAt          string
 		updatedAt          string
 	)
-	err := s.Scan(&ride.ID, &ride.ExternalID, &ride.GPXPath, &ride.Name, &ride.Type, &startDate, &ride.DistanceM, &ride.ExternalMovingTimeS, &movingTimeS, &movingTimeAlgoVer, &ride.ElapsedTimeS, &ride.TotalElevationGainM, &ride.AverageSpeedMps, &cotacolScore, &cotacolAlgoVersion, &createdAt, &updatedAt)
+	err := s.Scan(&ride.ID, &ride.ExternalID, &ride.GPXPath, &ride.Name, &ride.Type, &startDate, &ride.DistanceM, &ride.ExternalMovingTimeS, &movingTimeS, &movingTimeAlgoVer, &ride.ElapsedTimeS, &ride.TotalElevationGainM, &ride.AverageSpeedMps, &cotacolScore, &cotacolAlgoVersion, &bikeType, &rideType, &createdAt, &updatedAt)
 	if err != nil {
 		return StoredRide{}, err
 	}
@@ -221,6 +233,8 @@ func scanRide(s scanner) (StoredRide, error) {
 	if cotacolAlgoVersion.Valid {
 		ride.cotacolAlgoVersion = cotacolAlgoVersion.String
 	}
+	ride.BikeType = BikeType(bikeType)
+	ride.RideType = RideType(rideType)
 	ride.StartDate, err = time.Parse(time.RFC3339, startDate)
 	if err != nil {
 		return StoredRide{}, kcore.Wrap(err, "invalid start_date in rides row")
@@ -266,3 +280,81 @@ func (r StoredRide) CotacolScore() (float64, bool) {
 	}
 	return *r.cotacolScore, true
 }
+
+func (r StoredRide) IsCategorised() bool {
+	return r.BikeType != "" && r.RideType != ""
+}
+
+func UpdateCategories(db *sql.DB, id int64, bikeType BikeType, rideType RideType) error {
+	if !IsValidBikeType(string(bikeType)) {
+		return fmt.Errorf("invalid bike_type %q", bikeType)
+	}
+	if !IsValidRideType(string(rideType)) {
+		return fmt.Errorf("invalid ride_type %q", rideType)
+	}
+	result, err := db.Exec(`UPDATE rides SET bike_type = ?, ride_type = ?, updated_at = strftime('%Y-%m-%dT%H:%M:%SZ', 'now') WHERE id = ?`, string(bikeType), string(rideType), id)
+	if err != nil {
+		return err
+	}
+	affected, err := result.RowsAffected()
+	if err != nil {
+		return err
+	}
+	if affected == 0 {
+		return sql.ErrNoRows
+	}
+	return nil
+}
+
+func BulkUpdateCategories(db *sql.DB, ids []int64, bikeType BikeType, rideType RideType) (int64, error) {
+	if len(ids) == 0 {
+		return 0, nil
+	}
+	if !IsValidBikeType(string(bikeType)) {
+		return 0, fmt.Errorf("invalid bike_type %q", bikeType)
+	}
+	if !IsValidRideType(string(rideType)) {
+		return 0, fmt.Errorf("invalid ride_type %q", rideType)
+	}
+	query := "UPDATE rides SET bike_type = ?, ride_type = ?, updated_at = strftime('%Y-%m-%dT%H:%M:%SZ', 'now') WHERE id IN ("
+	args := []any{string(bikeType), string(rideType)}
+	for i, id := range ids {
+		if i > 0 {
+			query += ","
+		}
+		query += "?"
+		args = append(args, id)
+	}
+	query += ")"
+	result, err := db.Exec(query, args...)
+	if err != nil {
+		return 0, err
+	}
+	return result.RowsAffected()
+}
+
+func CountUncategorised(db *sql.DB) (int, error) {
+	row := db.QueryRow(`SELECT COUNT(*) FROM rides WHERE bike_type = '' OR ride_type = ''`)
+	var count int
+	if err := row.Scan(&count); err != nil {
+		return 0, err
+	}
+	return count, nil
+}
+
+func ListUncategorised(db *sql.DB) ([]StoredRide, error) {
+	rows, err := db.Query("SELECT " + columns + " FROM rides WHERE bike_type = '' OR ride_type = '' ORDER BY start_date DESC, id DESC")
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var rides []StoredRide
+	for rows.Next() {
+		ride, err := scanRide(rows)
+		if err != nil {
+			return nil, err
+		}
+		rides = append(rides, ride)
+	}
+	return rides, rows.Err()
+}
diff --git a/web/categories.go b/web/categories.go
new file mode 100644
index 0000000..4077092
--- /dev/null
+++ b/web/categories.go
@@ -0,0 +1,89 @@
+package web
+
+import (
+	"log/slog"
+	"net/http"
+	"strconv"
+
+	"github.com/martinlehoux/biking_home/ride"
+)
+
+func (s *Server) handleUpdateRideCategories(w http.ResponseWriter, r *http.Request) {
+	id, err := strconv.ParseInt(r.PathValue("id"), 10, 64)
+	if err != nil || id <= 0 {
+		http.NotFound(w, r)
+		return
+	}
+	item, found, err := ride.GetByID(s.db, id)
+	if err != nil {
+		slog.Error("Failed to load ride for category update", "ride_id", id, "error", err)
+		http.Error(w, "failed to load ride", http.StatusInternalServerError)
+		return
+	}
+	if !found {
+		http.NotFound(w, r)
+		return
+	}
+	_ = item
+	if err := r.ParseForm(); err != nil {
+		http.Error(w, "invalid form", http.StatusBadRequest)
+		return
+	}
+	bikeType := ride.BikeType(r.FormValue("bike_type"))
+	rideType := ride.RideType(r.FormValue("ride_type"))
+	if !ride.IsValidBikeType(string(bikeType)) {
+		http.Error(w, "invalid bike_type", http.StatusBadRequest)
+		return
+	}
+	if !ride.IsValidRideType(string(rideType)) {
+		http.Error(w, "invalid ride_type", http.StatusBadRequest)
+		return
+	}
+	if err := ride.UpdateCategories(s.db, id, bikeType, rideType); err != nil {
+		slog.Error("Failed to update ride categories", "ride_id", id, "error", err)
+		http.Error(w, "failed to update categories", http.StatusInternalServerError)
+		return
+	}
+	slog.Info("Updated ride categories", "ride_id", id, "bike_type", bikeType, "ride_type", rideType)
+	http.Redirect(w, r, rideDetailURL(id)+"?categories=updated", http.StatusSeeOther)
+}
+
+func rideCategoriesUpdateURL(id int64) string {
+	return rideDetailURL(id) + "/categories"
+}
+
+var bikeTypeOptions = []ride.BikeType{ride.BikeTypeRoad, ride.BikeTypeMTB}
+var rideTypeOptions = []ride.RideType{ride.RideTypeTraining, ride.RideTypeIndoor, ride.RideTypeRace, ride.RideTypeTransport}
+
+var bikeTypeLabels = map[ride.BikeType]string{
+	ride.BikeTypeRoad: "Road",
+	ride.BikeTypeMTB:  "MTB",
+}
+
+var rideTypeLabels = map[ride.RideType]string{
+	ride.RideTypeTraining:  "Training",
+	ride.RideTypeIndoor:    "Indoor",
+	ride.RideTypeRace:      "Race",
+	ride.RideTypeTransport: "Transport",
+}
+
+func formatBikeType(value ride.BikeType) string {
+	if label, ok := bikeTypeLabels[value]; ok {
+		return label
+	}
+	return string(value)
+}
+
+func formatRideType(value ride.RideType) string {
+	if label, ok := rideTypeLabels[value]; ok {
+		return label
+	}
+	return string(value)
+}
+
+func formatCategoryOrEmpty(value string) string {
+	if value == "" {
+		return "—"
+	}
+	return value
+}
diff --git a/web/request.go b/web/request.go
index d51a582..43eee48 100644
--- a/web/request.go
+++ b/web/request.go
@@ -25,6 +25,9 @@ func rideDetailNotice(r *http.Request) string {
 	if r.URL.Query().Get("official_climb") == "created" {
 		return "Official climb saved and matched to this ride by coordinates."
 	}
+	if r.URL.Query().Get("categories") == "updated" {
+		return "Ride categories updated."
+	}
 	return ""
 }
 
diff --git a/web/server.go b/web/server.go
index 26dbb06..687fc8d 100644
--- a/web/server.go
+++ b/web/server.go
@@ -48,6 +48,7 @@ func (s *Server) Handler() http.Handler {
 	handleRoute(mux, "GET /rides/{id}", s.handleRide)
 	handleRoute(mux, "GET /analytics", s.handleAnalytics)
 	handleRoute(mux, "POST /rides/{id}/official-climbs", s.handleCreateOfficialClimb)
+	handleRoute(mux, "POST /rides/{id}/categories", s.handleUpdateRideCategories)
 	handleRoute(mux, "GET /sync", s.handleSyncForm)
 	handleRoute(mux, "POST /sync", s.handleSync)
 	handleRoute(mux, "GET /strava/login", s.handleStravaLogin)
diff --git a/web/static/style.css b/web/static/style.css
index 830e50a..bda7188 100644
--- a/web/static/style.css
+++ b/web/static/style.css
@@ -136,6 +136,16 @@ select { border: 1px solid var(--color-input-border); border-radius: .55rem; pad
 .section-heading .eyebrow { margin-bottom: .2rem; }
 .section-heading h2 { margin: 0; font-size: 1.2rem; }
 .section-count { display: grid; place-items: center; min-width: 2rem; height: 2rem; border-radius: 999px; background: var(--color-secondary-surface); color: var(--color-forest); font-weight: 800; }
+.categories-panel { padding: 1rem 1.25rem; }
+.categories-layout { display: flex; align-items: center; justify-content: space-between; gap: 1.25rem; flex-wrap: wrap; }
+.categories-heading { display: flex; align-items: baseline; gap: .75rem; flex-wrap: wrap; }
+.categories-heading .eyebrow { margin: 0; }
+.categories-heading h2 { margin: 0; font-size: 1.1rem; }
+.category-badge { display: inline-flex; align-items: center; border-radius: 999px; padding: .35rem .65rem; background: var(--color-notice-surface); color: var(--color-notice-text); font-size: .75rem; font-weight: 700; letter-spacing: .06em; text-transform: uppercase; }
+.category-form { display: flex; align-items: end; gap: .65rem; flex-wrap: wrap; margin-left: auto; }
+.category-form label { min-width: 8.5rem; }
+.category-form select { min-width: 8.5rem; }
+.category-form .button { align-self: end; padding: .55rem .9rem; }
 .official-climbs-panel { padding: 0; border: 0; background: transparent; box-shadow: none; }
 .official-climbs-panel .section-heading { margin-bottom: .75rem; }
 .official-climb-list { display: grid; gap: 0; }
diff --git a/web/templates.templ b/web/templates.templ
index 9be2daf..d1719b8 100644
--- a/web/templates.templ
+++ b/web/templates.templ
@@ -180,6 +180,49 @@ templ RideDetailContent(data RideDetailView) {
 				<div><dt>Elevation</dt><dd>{ formatElevation(data.Ride.TotalElevationGainM) }</dd></div>
 			</dl>
 		</section>
+		<section class="panel categories-panel">
+			<div class="categories-layout">
+				<div class="categories-heading">
+					<p class="eyebrow">Curation</p>
+					<h2>Categories</h2>
+				</div>
+				<form method="post" action={ templ.URL(rideCategoriesUpdateURL(data.Ride.ID)) } class="category-form">
+					<label for="ride-bike-type">Bike type
+						<select id="ride-bike-type" name="bike_type" required>
+							if data.Ride.BikeType == "" {
+								<option value="" selected>—</option>
+							} else {
+								<option value="">—</option>
+							}
+							for _, opt := range bikeTypeOptions {
+								if data.Ride.BikeType == opt {
+									<option value={ string(opt) } selected>{ bikeTypeLabels[opt] }</option>
+								} else {
+									<option value={ string(opt) }>{ bikeTypeLabels[opt] }</option>
+								}
+							}
+						</select>
+					</label>
+					<label for="ride-ride-type">Ride type
+						<select id="ride-ride-type" name="ride_type" required>
+							if data.Ride.RideType == "" {
+								<option value="" selected>—</option>
+							} else {
+								<option value="">—</option>
+							}
+							for _, opt := range rideTypeOptions {
+								if data.Ride.RideType == opt {
+									<option value={ string(opt) } selected>{ rideTypeLabels[opt] }</option>
+								} else {
+									<option value={ string(opt) }>{ rideTypeLabels[opt] }</option>
+								}
+							}
+						</select>
+					</label>
+					<button class="button" type="submit">Save</button>
+				</form>
+			</div>
+		</section>
 		if data.HasRoute {
 			if len(data.Profile.OfficialClimbs()) > 0 {
 				<section class="panel official-climbs-panel">