path: root/ride/store.go

package ride

import (
	"database/sql"
	"fmt"
	"log/slog"
	"strings"
	"time"

	"github.com/martinlehoux/kagamigo/kcore"
)

type StoredRide struct {
	ID                    int64
	ExternalID            string
	GPXPath               string
	Name                  string
	Type                  string
	StartDate             time.Time
	DistanceM             float64
	ExternalMovingTimeS   int64
	movingTimeS           *int64
	movingTimeAlgoVersion string
	ElapsedTimeS          int64
	TotalElevationGainM   float64
	AverageSpeedMps       float64
	cotacolScore          *float64
	cotacolAlgoVersion    string
	BikeType              BikeType
	RideType              RideType
	CreatedAt             time.Time
	UpdatedAt             time.Time
}

type SortColumn string

const (
	SortName       SortColumn = "name"
	SortStartDate  SortColumn = "started"
	SortDistance   SortColumn = "distance"
	SortMovingTime SortColumn = "moving_time"
	SortSpeed      SortColumn = "speed"
	SortElevation  SortColumn = "elevation"
	SortCotacol    SortColumn = "cotacol"
	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, bike_type, ride_type, created_at, updated_at"

func Save(db *sql.DB, r StoredRide) error {
	var cotacolScore any
	var cotacolAlgorithmVersion any
	var movingTimeS any
	var movingTimeAlgorithmVersion any
	if r.GPXPath != "" {
		parsed, err := ParseFile(GPXRideParser{}, r.GPXPath)
		if err != nil {
			return fmt.Errorf("compute ride values for %q: %w", r.ExternalID, err)
		}
		cotacolScore = Cotacol(parsed)
		cotacolAlgorithmVersion = CotacolAlgorithmVersion
		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, bike_type, ride_type)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
		ON CONFLICT(external_id) DO UPDATE SET
			gpx_path = excluded.gpx_path,
			name = excluded.name,
			type = excluded.type,
			start_date = excluded.start_date,
			distance_m = excluded.distance_m,
			external_moving_time_s = excluded.external_moving_time_s,
			moving_time_s = excluded.moving_time_s,
			moving_time_algo_version = excluded.moving_time_algo_version,
			elapsed_time_s = excluded.elapsed_time_s,
			total_elevation_gain_m = excluded.total_elevation_gain_m,
			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, string(r.BikeType), string(r.RideType))
	return err
}

// Backfill recomputes and persists stored ride values for every ride. An
// optional reporter is invoked with the number of rides processed and the
// total, so callers (e.g. the CLI) can render progress.
func Backfill(db *sql.DB, reporters ...func(current, total int)) (int, error) {
	rows, err := db.Query("SELECT " + columns + " FROM rides ORDER BY id")
	if err != nil {
		return 0, err
	}
	var pending []StoredRide
	for rows.Next() {
		item, err := scanRide(rows)
		if err != nil {
			rows.Close()
			return 0, err
		}
		pending = append(pending, item)
	}
	if err := rows.Err(); err != nil {
		rows.Close()
		return 0, err
	}
	if err := rows.Close(); err != nil {
		return 0, err
	}
	backfilled := 0
	report := func(int, int) {}
	if len(reporters) > 0 {
		report = reporters[0]
	}
	for i, item := range pending {
		report(i+1, len(pending))
		if err := Save(db, item); err != nil {
			slog.Warn("Failed to backfill ride values", "ride", item.ExternalID, "file", item.GPXPath, "error", err)
			continue
		}
		backfilled++
	}
	return backfilled, nil
}

func List(db *sql.DB) ([]StoredRide, error) {
	return ListSorted(db, SortStartDate, true)
}

func ListSorted(db *sql.DB, column SortColumn, descending bool) ([]StoredRide, error) {
	expression, ok := sortExpressions[column]
	if !ok {
		return nil, fmt.Errorf("invalid ride sort column %q", column)
	}
	direction := "ASC"
	if descending {
		direction = "DESC"
	}
	query := "SELECT " + columns + " FROM rides ORDER BY "
	args := []any{}
	if column == SortMovingTime {
		args = append(args, MovingTimeAlgorithmVersion)
	} else if isComputedSort(column) {
		query += "(cotacol_score IS NULL OR cotacol_algo_version IS NULL OR cotacol_algo_version <> ? OR distance_m <= 0) ASC, "
		args = append(args, CotacolAlgorithmVersion)
	}
	query += expression + " " + direction + ", id DESC"
	rows, err := db.Query(query, args...)
	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()
}

var sortExpressions = map[SortColumn]string{
	SortName:       "name COLLATE NOCASE",
	SortStartDate:  "start_date",
	SortDistance:   "distance_m",
	SortMovingTime: "CASE WHEN moving_time_algo_version = ? THEN COALESCE(moving_time_s, external_moving_time_s) ELSE external_moving_time_s END",
	SortSpeed:      "average_speed_mps",
	SortElevation:  "total_elevation_gain_m",
	SortCotacol:    "cotacol_score",
	SortCotacolKm:  "cotacol_score * 100000.0 / NULLIF(distance_m, 0)",
}

func isComputedSort(column SortColumn) bool {
	return column == SortCotacol || column == SortCotacolKm
}

func GetByExternalID(db *sql.DB, externalID string) (StoredRide, bool, error) {
	row := db.QueryRow("SELECT "+columns+" FROM rides WHERE external_id = ?", externalID)
	ride, err := scanRide(row)
	if err == sql.ErrNoRows {
		return StoredRide{}, false, nil
	}
	if err != nil {
		return StoredRide{}, false, err
	}
	return ride, true, nil
}

func GetByID(db *sql.DB, id int64) (StoredRide, bool, error) {
	row := db.QueryRow("SELECT "+columns+" FROM rides WHERE id = ?", id)
	ride, err := scanRide(row)
	if err == sql.ErrNoRows {
		return StoredRide{}, false, nil
	}
	if err != nil {
		return StoredRide{}, false, err
	}
	return ride, true, nil
}

type scanner interface {
	Scan(dest ...any) error
}

func scanRide(s scanner) (StoredRide, error) {
	var (
		ride               StoredRide
		startDate          string
		movingTimeS        sql.NullInt64
		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, &bikeType, &rideType, &createdAt, &updatedAt)
	if err != nil {
		return StoredRide{}, err
	}
	if movingTimeS.Valid {
		ride.movingTimeS = &movingTimeS.Int64
	}
	if movingTimeAlgoVer.Valid {
		ride.movingTimeAlgoVersion = movingTimeAlgoVer.String
	}
	if cotacolScore.Valid {
		ride.cotacolScore = &cotacolScore.Float64
	}
	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")
	}
	ride.CreatedAt, err = time.Parse(time.RFC3339, createdAt)
	if err != nil {
		return StoredRide{}, kcore.Wrap(err, "invalid created_at in rides row")
	}
	ride.UpdatedAt, err = time.Parse(time.RFC3339, updatedAt)
	if err != nil {
		return StoredRide{}, kcore.Wrap(err, "invalid updated_at in rides row")
	}
	return ride, nil
}

func (r StoredRide) CotacolAlgorithmVersion() string {
	return r.cotacolAlgoVersion
}

func (r StoredRide) MovingTimeS() (int64, bool) {
	if r.movingTimeS == nil {
		return 0, false
	}
	return *r.movingTimeS, true
}

func (r StoredRide) MovingTimeAlgorithmVersion() string {
	return r.movingTimeAlgoVersion
}

// EffectiveMovingTimeS is the canonical moving time when the stored value
// matches the current algorithm version, falling back to Strava's value.
func (r StoredRide) EffectiveMovingTimeS() int64 {
	if moving, ok := r.MovingTimeS(); ok && r.movingTimeAlgoVersion == MovingTimeAlgorithmVersion {
		return moving
	}
	return r.ExternalMovingTimeS
}

func (r StoredRide) CotacolScore() (float64, bool) {
	if r.cotacolScore == nil {
		return 0, false
	}
	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)
	}
	var query strings.Builder
	query.WriteString("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.WriteString(",")
		}
		query.WriteString("?")
		args = append(args, id)
	}
	query.WriteString(")")
	result, err := db.Exec(query.String(), 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()
}