Skip to content

armgit / lyftr

Public Imported from GitHub
0

Self-hosted fitness tracker. Log workouts, track nutrition, and monitor progress. Docker deployment, mobile-friendly.

14.1 KB
Raw Blame
package stores

import (
	"database/sql"
	"strings"

	"github.com/Cawlumm/lyftr-backend/models"
)

// WorkoutStore owns all SQL for workouts, workout_exercises and sets, plus the
// workout-derived exercise analytics (PRs/history/daily count).
type WorkoutStore struct{ db *sql.DB }

func NewWorkoutStore(db *sql.DB) *WorkoutStore { return &WorkoutStore{db: db} }

// WorkoutFilter holds list paging + optional name search.
type WorkoutFilter struct {
	Limit, Offset int
	Query         string
}

const workoutCols = `id, user_id, name, notes, duration, started_at, tz_offset_minutes, created_at`

func scanWorkout(row interface{ Scan(...any) error }, w *models.Workout) error {
	return row.Scan(&w.ID, &w.UserID, &w.Name, &w.Notes, &w.Duration, &w.StartedAt, &w.TZOffsetMinutes, &w.CreatedAt)
}

func (s *WorkoutStore) List(uid int64, f WorkoutFilter, locale string) ([]models.Workout, error) {
	var rows *sql.Rows
	var err error
	if f.Query != "" {
		rows, err = s.db.Query(
			`SELECT `+workoutCols+` FROM workouts WHERE user_id = ? AND LOWER(name) LIKE ? ORDER BY started_at DESC LIMIT ? OFFSET ?`,
			uid, "%"+strings.ToLower(f.Query)+"%", f.Limit, f.Offset,
		)
	} else {
		rows, err = s.db.Query(
			`SELECT `+workoutCols+` FROM workouts WHERE user_id = ? ORDER BY started_at DESC LIMIT ? OFFSET ?`,
			uid, f.Limit, f.Offset,
		)
	}
	if err != nil {
		return nil, err
	}
	workouts := []models.Workout{}
	for rows.Next() {
		var w models.Workout
		if err := scanWorkout(rows, &w); err != nil {
			rows.Close()
			return nil, err
		}
		workouts = append(workouts, w)
	}
	if err := rows.Err(); err != nil {
		rows.Close()
		return nil, err
	}
	rows.Close() // close the parent cursor BEFORE loading children (#36: avoid holding 2 pool connections per request)

	for i := range workouts {
		ex, err := s.loadExercises(workouts[i].ID, locale)
		if err != nil {
			return nil, err
		}
		workouts[i].Exercises = ex
	}
	return workouts, nil
}

// Get returns a user-owned workout with its exercises/sets, or sql.ErrNoRows.
func (s *WorkoutStore) Get(uid, id int64, locale string) (models.Workout, error) {
	var w models.Workout
	if err := scanWorkout(
		s.db.QueryRow(`SELECT `+workoutCols+` FROM workouts WHERE id = ? AND user_id = ?`, id, uid), &w,
	); err != nil {
		return models.Workout{}, err
	}
	ex, err := s.loadExercises(id, locale)
	if err != nil {
		return models.Workout{}, err
	}
	w.Exercises = ex
	return w, nil
}

// get re-reads by id (no user scope) — used after a user-scoped write.
func (s *WorkoutStore) get(id int64, locale string) (models.Workout, error) {
	var w models.Workout
	if err := scanWorkout(s.db.QueryRow(`SELECT `+workoutCols+` FROM workouts WHERE id = ?`, id), &w); err != nil {
		return models.Workout{}, err
	}
	ex, err := s.loadExercises(id, locale)
	if err != nil {
		return models.Workout{}, err
	}
	w.Exercises = ex
	return w, nil
}

// createWorkoutTx inserts the workout + its exercises/sets within an existing
// transaction, returning the new workout id. Factored out so the
// PR-snapshot read, the insert, and the suggestion-staging write can share one
// transaction (see CreateWithProgression) — SQLite then serializes concurrent
// submissions instead of letting them read the same stale prior-best (#40).
func createWorkoutTx(tx *sql.Tx, uid int64, req models.CreateWorkoutRequest) (int64, error) {
	// ProgramID (nil for freestyle workouts) is persisted so ProgramStore can compute
	// a routine's cycle position — see ProgramStore.currentDayIndex. No FK: deleting
	// a program must never take workout history down with it. ProgramDayID pins WHICH
	// day of the cycle this workout was for (day-accurate due-day tracking); its FK is
	// ON DELETE SET NULL so deleting/editing a day only drops the linkage, never the
	// workout.
	//
	// A live session can outlive its routine's day rows (an edit mid-workout can
	// remove days, a delete takes the whole program), so a stale/foreign day id must
	// degrade to "day unknown" (NULL) here — never fail the user's save on the FK. The same check
	// re-scopes the id to THIS request's program AND the calling user (via the
	// programs join), so a client can't link a workout to another program's day or
	// probe/persist references into some other user's routine. A REST day's id
	// degrades to NULL the same way (is_rest_day = 0 below): rest days are excluded
	// from the due-day tracker's anchor lookup, so persisting such a link would
	// strand the workout — neither anchoring nor advancing the tracker — where a
	// day-less row still advances it count-based.
	dayID := req.ProgramDayID
	if dayID != nil {
		if req.ProgramID == nil {
			dayID = nil // a day without its program is meaningless
		} else {
			var ok int64
			err := tx.QueryRow(
				`SELECT pd.id FROM program_days pd
				 JOIN programs p ON p.id = pd.program_id AND p.user_id = ?
				 WHERE pd.id = ? AND pd.program_id = ? AND pd.is_rest_day = 0`, uid, *dayID, *req.ProgramID,
			).Scan(&ok)
			if err == sql.ErrNoRows {
				dayID = nil
			} else if err != nil {
				return 0, err
			}
		}
	}
	res, err := tx.Exec(
		`INSERT INTO workouts (user_id, name, notes, duration, started_at, tz_offset_minutes, program_id, program_day_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
		uid, req.Name, req.Notes, req.Duration, req.StartedAt, req.TZOffsetMinutes, req.ProgramID, dayID,
	)
	if err != nil {
		return 0, err
	}
	wid, err := res.LastInsertId()
	if err != nil {
		return 0, err
	}
	if err := insertWorkoutExercises(tx, wid, req.Exercises); err != nil {
		return 0, err
	}
	return wid, nil
}

// Update replaces a user-owned workout and its children in one tx. sql.ErrNoRows
// if the workout isn't theirs (nothing is mutated).
func (s *WorkoutStore) Update(uid, id int64, req models.CreateWorkoutRequest, locale string) (models.Workout, error) {
	// Ownership check + replace-children, all in one transaction (no TOCTOU).
	if err := inTxDo(s.db, func(tx *sql.Tx) error {
		var ownedID int64
		if err := tx.QueryRow(`SELECT id FROM workouts WHERE id = ? AND user_id = ?`, id, uid).Scan(&ownedID); err != nil {
			return err // ErrNoRows = not theirs
		}
		// started_at is only rewritten when the request carries one: a PUT omitting
		// it (a name/notes-only patch) must not zero the stored timestamp — it's the
		// due-day tracker's ordering key (ProgramStore.currentDayIndex), and a
		// 0001-01-01 rewrite would silently drop this workout to oldest.
		if req.StartedAt.IsZero() {
			if _, err := tx.Exec(
				`UPDATE workouts SET name = ?, notes = ?, duration = ? WHERE id = ?`,
				req.Name, req.Notes, req.Duration, id,
			); err != nil {
				return err
			}
		} else if _, err := tx.Exec(
			// The offset travels with the instant. Rewriting started_at without it leaves
			// the row describing a moment in one zone and a day in another, so the day the
			// edit screen showed and the day every reader derives disagree — the entry
			// silently lands a day off. Only rewritten alongside started_at: the branch
			// above is a name/notes patch, where the original offset is still the true one.
			`UPDATE workouts SET name = ?, notes = ?, duration = ?, started_at = ?, tz_offset_minutes = ? WHERE id = ?`,
			req.Name, req.Notes, req.Duration, req.StartedAt, req.TZOffsetMinutes, id,
		); err != nil {
			return err
		}
		// Children replaced wholesale; sets cascade-delete with their workout_exercise.
		if _, err := tx.Exec(`DELETE FROM workout_exercises WHERE workout_id = ?`, id); err != nil {
			return err
		}
		return insertWorkoutExercises(tx, id, req.Exercises)
	}); err != nil {
		return models.Workout{}, err
	}
	return s.get(id, locale)
}

func (s *WorkoutStore) Delete(uid, id int64) (int64, error) {
	res, err := s.db.Exec(`DELETE FROM workouts WHERE id = ? AND user_id = ?`, id, uid)
	if err != nil {
		return 0, err
	}
	n, _ := res.RowsAffected()
	return n, nil
}

// insertWorkoutExercises writes the exercises + their sets for a workout within tx.
func insertWorkoutExercises(tx *sql.Tx, wid int64, exercises []models.CreateWorkoutExerciseReq) error {
	for i, ex := range exercises {
		exRes, err := tx.Exec(
			`INSERT INTO workout_exercises (workout_id, exercise_id, order_index, notes, rest_seconds) VALUES (?, ?, ?, ?, ?)`,
			wid, ex.ExerciseID, i, ex.Notes, ex.RestSeconds,
		)
		if err != nil {
			return err
		}
		weid, err := exRes.LastInsertId()
		if err != nil {
			return err
		}
		for j, st := range ex.Sets {
			if _, err := tx.Exec(
				`INSERT INTO sets (workout_exercise_id, set_number, reps, weight, duration, distance, rpe, is_warmup)
				 VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
				weid, j+1, st.Reps, st.Weight, st.Duration, st.Distance, st.RPE, st.IsWarmup,
			); err != nil {
				return err
			}
		}
	}
	return nil
}

// loadExercises loads a workout's exercises (JOIN exercises for display) then
// their sets. Scans + closes the parent cursor fully BEFORE the per-exercise set
// queries (#36) so a request never holds two pool connections at once.
func (s *WorkoutStore) loadExercises(workoutID int64, locale string) ([]models.WorkoutExercise, error) {
	join, joinArgs := translationJoin(locale)
	rows, err := s.db.Query(
		`SELECT we.id, we.workout_id, we.exercise_id, we.order_index, we.notes, we.rest_seconds,
		        `+localizedName+`, e.muscle_group, e.category, e.equipment, e.image_url
		 FROM workout_exercises we
		 JOIN exercises e ON e.id = we.exercise_id`+join+`
		 WHERE we.workout_id = ? ORDER BY we.order_index`,
		append(joinArgs, workoutID)...,
	)
	if err != nil {
		return nil, err
	}
	var exercises []models.WorkoutExercise
	for rows.Next() {
		var we models.WorkoutExercise
		if err := rows.Scan(
			&we.ID, &we.WorkoutID, &we.ExerciseID, &we.OrderIndex, &we.Notes, &we.RestSeconds,
			&we.Exercise.Name, &we.Exercise.MuscleGroup, &we.Exercise.Category,
			&we.Exercise.Equipment, &we.Exercise.ImageURL,
		); err != nil {
			rows.Close()
			return nil, err
		}
		we.Exercise.ID = we.ExerciseID
		exercises = append(exercises, we)
	}
	if err := rows.Err(); err != nil {
		rows.Close()
		return nil, err
	}
	rows.Close()

	for i := range exercises {
		sets, err := s.loadSets(exercises[i].ID)
		if err != nil {
			return nil, err
		}
		exercises[i].Sets = sets
	}
	return exercises, nil
}

func (s *WorkoutStore) loadSets(workoutExerciseID int64) ([]models.Set, error) {
	rows, err := s.db.Query(
		`SELECT id, workout_exercise_id, set_number, reps, weight, duration, distance, rpe, is_warmup
		 FROM sets WHERE workout_exercise_id = ? ORDER BY set_number`,
		workoutExerciseID,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var sets []models.Set
	for rows.Next() {
		var st models.Set
		if err := rows.Scan(&st.ID, &st.WorkoutExerciseID, &st.SetNumber, &st.Reps, &st.Weight, &st.Duration, &st.Distance, &st.RPE, &st.IsWarmup); err != nil {
			return nil, err
		}
		sets = append(sets, st)
	}
	return sets, rows.Err()
}

// CountOnLocalDay counts workouts whose own recorded offset puts them on `day`.
//
// Shifting the instant by the row's stored offset is exact and needs no zone: a
// workout at 2026-08-04T16:00Z with offset -240 was 12:00 on the 4th where it
// happened, and always will be. Rows predating the column have a NULL offset and are
// counted as UTC, which is the day they were counted under before it existed.
func (s *WorkoutStore) CountOnLocalDay(uid int64, day string) (int, error) {
	var n int
	// substr(...,1,19) takes the "YYYY-MM-DD HH:MM:SS" prefix. The driver writes a
	// time.Time in Go's String() form — "2026-04-25 03:30:00 +0000 UTC" — which
	// SQLite's date() cannot parse at all, returning NULL and matching nothing. The
	// prefix is a format it does accept, and is UTC because every write path
	// normalizes to UTC before storing (normalizeLoggedAt, plus the boot-time
	// normalizeUTCTimestamps for older rows).
	err := s.db.QueryRow(
		`SELECT COUNT(*) FROM workouts
		 WHERE user_id = ?
		   AND date(substr(started_at, 1, 19), CAST(COALESCE(tz_offset_minutes, 0) AS TEXT) || ' minutes') = ?`,
		uid, day,
	).Scan(&n)
	return n, err
}

// ExercisePR is the best single set for an exercise (heaviest, then most reps).
type ExercisePR struct {
	Weight    float64
	Reps      int
	Date      string
	WorkoutID int64
}

// ExerciseHistoryPoint is a per-workout rollup for an exercise's progress chart.
type ExerciseHistoryPoint struct {
	Date        string  `json:"date"`
	MaxWeight   float64 `json:"max_weight"`
	TotalVolume float64 `json:"total_volume"`
	SetsCount   int     `json:"sets_count"`
}

// PRForExercise returns the user's PR set for an exercise, or sql.ErrNoRows if
// they have no qualifying (non-warmup, weighted) set. Reads workout tables — this
// is workout-derived analytics keyed by exercise, so it lives on WorkoutStore.
func (s *WorkoutStore) PRForExercise(uid, exerciseID int64) (ExercisePR, error) {
	return prForExerciseTx(s.db, uid, exerciseID)
}

// prForExerciseTx runs the PRForExercise query against anything that can QueryRow —
// s.db for the standalone read, or an existing *sql.Tx when the read must be
// serialized with a later write in the same transaction (see CreateWithProgression).
func prForExerciseTx(q interface {
	QueryRow(query string, args ...any) *sql.Row
}, uid, exerciseID int64) (ExercisePR, error) {
	var pr ExercisePR
	err := q.QueryRow(`
		SELECT s.weight, s.reps, w.started_at, w.id
		FROM sets s
		JOIN workout_exercises we ON we.id = s.workout_exercise_id
		JOIN workouts w ON w.id = we.workout_id
		WHERE w.user_id = ? AND we.exercise_id = ? AND s.is_warmup = 0 AND s.weight > 0
		ORDER BY s.weight DESC, s.reps DESC
		LIMIT 1
	`, uid, exerciseID).Scan(&pr.Weight, &pr.Reps, &pr.Date, &pr.WorkoutID)
	return pr, err
}

func (s *WorkoutStore) HistoryForExercise(uid, exerciseID int64, limit int) ([]ExerciseHistoryPoint, error) {
	rows, err := s.db.Query(`
		SELECT w.started_at, MAX(s.weight), SUM(s.reps * s.weight), COUNT(s.id)
		FROM sets s
		JOIN workout_exercises we ON we.id = s.workout_exercise_id
		JOIN workouts w ON w.id = we.workout_id
		WHERE w.user_id = ? AND we.exercise_id = ? AND s.is_warmup = 0
		GROUP BY w.id
		ORDER BY w.started_at DESC
		LIMIT ?
	`, uid, exerciseID, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	history := []ExerciseHistoryPoint{}
	for rows.Next() {
		var p ExerciseHistoryPoint
		if err := rows.Scan(&p.Date, &p.MaxWeight, &p.TotalVolume, &p.SetsCount); err != nil {
			return nil, err
		}
		history = append(history, p)
	}
	return history, rows.Err()
}