path: root/official_climb/attempt.go

package official_climb

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

	"github.com/martinlehoux/biking_home/climb"
)

// Attempt is one recorded passage of an official climb in a ride, keyed by (rideID, climbID, occurrence); stats are snapshotted so listings never re-parse ride files. AveragePowerW is nil for rides without a power meter.
type Attempt struct {
	RideID             int64
	ClimbID            int64
	Occurrence         int
	DistanceM          float64
	ElevationGainM     float64
	SlopePercent       float64
	Cotacol            float64
	AveragePowerW      *float64
	CotacolAlgoVersion string
	StartedAt          time.Time
	EndedAt            time.Time
	CreatedAt          time.Time
}

const attemptColumns = "a.ride_id, a.climb_id, a.occurrence, a.distance_m, a.elevation_gain_m, a.slope_percent, a.cotacol, a.average_power_w, a.cotacol_algo_version, a.started_at, a.ended_at, a.created_at"

// AttemptsForClimb returns every recorded attempt of one official climb, oldest ride first.
func AttemptsForClimb(db *sql.DB, climbID int64) ([]Attempt, error) {
	return listAttempts(db, "climb_id = ?", climbID, "r.start_date, a.ride_id, a.occurrence")
}

// AttemptsForRide returns every recorded attempt within one ride, in route order.
func AttemptsForRide(db *sql.DB, rideID int64) ([]Attempt, error) {
	return listAttempts(db, "ride_id = ?", rideID, "a.occurrence")
}

func listAttempts(db *sql.DB, where string, arg any, order string) ([]Attempt, error) {
	rows, err := db.Query(`
		SELECT `+attemptColumns+`
		FROM ride_official_climb_attempts a
		JOIN rides r ON r.id = a.ride_id
		WHERE `+where+`
		ORDER BY `+order, arg)
	if err != nil {
		return nil, fmt.Errorf("list official climb attempts: %w", err)
	}
	defer rows.Close()
	attempts := make([]Attempt, 0)
	for rows.Next() {
		attempt, err := scanAttempt(rows)
		if err != nil {
			return nil, err
		}
		attempts = append(attempts, attempt)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate official climb attempts: %w", err)
	}
	return attempts, nil
}

// ReplaceRideAttempts refreshes a ride's attempt rows: fresh matches are written, stale ones dropped, the recorded-at stamp of survivors kept.
func ReplaceRideAttempts(db *sql.DB, rideID int64, attempts []Attempt) error {
	tx, err := db.Begin()
	if err != nil {
		return fmt.Errorf("replace ride attempts: %w", err)
	}
	defer func() { _ = tx.Rollback() }()
	keys := make([]string, 0, len(attempts))
	args := make([]any, 0, 2*len(attempts)+1)
	args = append(args, rideID)
	for _, attempt := range attempts {
		if attempt.RideID != rideID {
			return fmt.Errorf("replace ride attempts: attempt references ride %d, expected %d", attempt.RideID, rideID)
		}
		keys = append(keys, "(?, ?)")
		args = append(args, attempt.ClimbID, attempt.Occurrence)
		if _, err := tx.Exec(`
			INSERT INTO ride_official_climb_attempts (ride_id, climb_id, occurrence, distance_m, elevation_gain_m, slope_percent, cotacol, average_power_w, cotacol_algo_version, started_at, ended_at)
			VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
			ON CONFLICT(ride_id, climb_id, occurrence) DO UPDATE SET
				distance_m = excluded.distance_m,
				elevation_gain_m = excluded.elevation_gain_m,
				slope_percent = excluded.slope_percent,
				cotacol = excluded.cotacol,
				average_power_w = excluded.average_power_w,
				cotacol_algo_version = excluded.cotacol_algo_version,
				started_at = excluded.started_at,
				ended_at = excluded.ended_at
		`, attempt.RideID, attempt.ClimbID, attempt.Occurrence, attempt.DistanceM, attempt.ElevationGainM, attempt.SlopePercent, attempt.Cotacol, attempt.AveragePowerW, attempt.CotacolAlgoVersion,
			attempt.StartedAt.UTC().Format(time.RFC3339), attempt.EndedAt.UTC().Format(time.RFC3339)); err != nil {
			return fmt.Errorf("insert ride attempt: %w", err)
		}
	}
	prune := "DELETE FROM ride_official_climb_attempts WHERE ride_id = ?"
	if len(keys) > 0 {
		prune += " AND (climb_id, occurrence) NOT IN (VALUES " + strings.Join(keys, ", ") + ")"
	}
	if _, err := tx.Exec(prune, args...); err != nil {
		return fmt.Errorf("drop stale ride attempts: %w", err)
	}
	if err := tx.Commit(); err != nil {
		return fmt.Errorf("commit ride attempts: %w", err)
	}
	return nil
}

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

func scanAttempt(s attemptScanner) (Attempt, error) {
	var (
		attempt            Attempt
		averagePower       sql.NullFloat64
		startedAt, endedAt string
		createdAt          sql.NullString
	)
	if err := s.Scan(&attempt.RideID, &attempt.ClimbID, &attempt.Occurrence, &attempt.DistanceM, &attempt.ElevationGainM, &attempt.SlopePercent, &attempt.Cotacol, &averagePower, &attempt.CotacolAlgoVersion, &startedAt, &endedAt, &createdAt); err != nil {
		return Attempt{}, err
	}
	if averagePower.Valid {
		attempt.AveragePowerW = &averagePower.Float64
	}
	var err error
	if attempt.StartedAt, err = time.Parse(time.RFC3339, startedAt); err != nil {
		return Attempt{}, fmt.Errorf("invalid ride_official_climb_attempts.started_at: %w", err)
	}
	if attempt.EndedAt, err = time.Parse(time.RFC3339, endedAt); err != nil {
		return Attempt{}, fmt.Errorf("invalid ride_official_climb_attempts.ended_at: %w", err)
	}
	if createdAt.Valid {
		if attempt.CreatedAt, err = time.Parse(time.RFC3339, createdAt.String); err != nil {
			return Attempt{}, fmt.Errorf("invalid ride_official_climb_attempts.created_at: %w", err)
		}
	}
	return attempt, nil
}

// AttemptStats carries the analyzed values snapshotted into an attempt row.
type AttemptStats struct {
	OfficialClimbID int64
	DistanceM       float64
	ElevationGainM  float64
	SlopePercent    float64
	Cotacol         float64
	StartIndex      int
	EndIndex        int
}

// AttemptFromStats snapshots one analyzed climb occurrence; timestamps come from the ride points bounding the climb interval, and AveragePowerW is the mean of the power samples inside it (nil when the ride recorded no power).
func AttemptFromStats(rideID int64, occurrence int, stats AttemptStats, timestamps []time.Time, powers []float64) Attempt {
	startedAt, endedAt := time.Time{}, time.Time{}
	if stats.StartIndex < len(timestamps) {
		startedAt = timestamps[stats.StartIndex]
	}
	if stats.EndIndex < len(timestamps) {
		endedAt = timestamps[stats.EndIndex]
	}
	return Attempt{
		RideID:             rideID,
		ClimbID:            stats.OfficialClimbID,
		Occurrence:         occurrence,
		DistanceM:          stats.DistanceM,
		ElevationGainM:     stats.ElevationGainM,
		SlopePercent:       stats.SlopePercent,
		Cotacol:            stats.Cotacol,
		AveragePowerW:      meanPower(powers, stats.StartIndex, stats.EndIndex),
		CotacolAlgoVersion: climb.CotacolAlgorithmVersion,
		StartedAt:          startedAt,
		EndedAt:            endedAt,
	}
}

// meanPower averages the power samples inside the climb interval (bounds included); it is nil when the interval does not fit the column.
func meanPower(powers []float64, start, end int) *float64 {
	if start < 0 || end >= len(powers) || start > end {
		return nil
	}
	sum := 0.0
	for _, power := range powers[start : end+1] {
		sum += power
	}
	mean := sum / float64(end-start+1)
	return &mean
}