Add hardcore ride categorisation with bike and ride types

1 files changed, 97 insertions, 5 deletions

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