Self-hosted fitness tracker. Log workouts, track nutrition, and monitor progress. Docker deployment, mobile-friendly.
package stores
import (
"database/sql"
"strings"
"github.com/Cawlumm/lyftr-backend/models"
)
// ProgramStore owns all SQL for programs, program_days, program_exercises and
// program_sets.
type ProgramStore struct{ db *sql.DB }
func NewProgramStore(db *sql.DB) *ProgramStore { return &ProgramStore{db: db} }
// ProgramFilter holds list paging + optional name search.
type ProgramFilter struct {
Limit, Offset int
Query string
}
const programCols = `id, user_id, name, notes, created_at`
func scanProgram(row interface{ Scan(...any) error }, p *models.Program) error {
return row.Scan(&p.ID, &p.UserID, &p.Name, &p.Notes, &p.CreatedAt)
}
func (s *ProgramStore) List(uid int64, f ProgramFilter, locale string) ([]models.Program, error) {
var rows *sql.Rows
var err error
if f.Query != "" {
rows, err = s.db.Query(
`SELECT `+programCols+` FROM programs WHERE user_id = ? AND LOWER(name) LIKE ? ORDER BY created_at DESC LIMIT ? OFFSET ?`,
uid, "%"+strings.ToLower(f.Query)+"%", f.Limit, f.Offset,
)
} else {
rows, err = s.db.Query(
`SELECT `+programCols+` FROM programs WHERE user_id = ? ORDER BY created_at DESC LIMIT ? OFFSET ?`,
uid, f.Limit, f.Offset,
)
}
if err != nil {
return nil, err
}
programs := []models.Program{}
for rows.Next() {
var p models.Program
if err := scanProgram(rows, &p); err != nil {
rows.Close()
return nil, err
}
programs = append(programs, p)
}
if err := rows.Err(); err != nil {
rows.Close()
return nil, err
}
rows.Close() // close the parent cursor BEFORE loading children (#36)
// loadDays (exercises/sets) stays a per-program call — pre-existing N+1, out of
// scope here. currentDayIndex is batched below instead of called per-program:
// unlike loadDays it added 2 brand-new sequential round trips per program on top
// of that, and SetMaxOpenConns(1) serializes every one of them on the process's
// single connection.
for i := range programs {
days, err := s.loadDays(programs[i].ID, locale)
if err != nil {
return nil, err
}
programs[i].Days = days
}
idxByID, err := s.currentDayIndex(uid, programs)
if err != nil {
return nil, err
}
for i := range programs {
programs[i].CurrentDayIndex = idxByID[programs[i].ID]
}
return programs, nil
}
// Get returns a user-owned program with its days/exercises/sets, or sql.ErrNoRows.
func (s *ProgramStore) Get(uid, id int64, locale string) (models.Program, error) {
var p models.Program
if err := scanProgram(
s.db.QueryRow(`SELECT `+programCols+` FROM programs WHERE id = ? AND user_id = ?`, id, uid), &p,
); err != nil {
return models.Program{}, err
}
if err := s.hydrate(&p, locale); err != nil {
return models.Program{}, err
}
return p, nil
}
func (s *ProgramStore) get(id int64, locale string) (models.Program, error) {
var p models.Program
if err := scanProgram(s.db.QueryRow(`SELECT `+programCols+` FROM programs WHERE id = ?`, id), &p); err != nil {
return models.Program{}, err
}
if err := s.hydrate(&p, locale); err != nil {
return models.Program{}, err
}
return p, nil
}
// hydrate loads a program's Days (with their Exercises/Sets) and computes
// CurrentDayIndex in place.
func (s *ProgramStore) hydrate(p *models.Program, locale string) error {
days, err := s.loadDays(p.ID, locale)
if err != nil {
return err
}
p.Days = days
idxByID, err := s.currentDayIndex(p.UserID, []models.Program{{ID: p.ID, Days: days}})
if err != nil {
return err
}
p.CurrentDayIndex = idxByID[p.ID]
return nil
}
// currentDayIndex computes, for every given program, which Days[] entry is due
// today, in a small constant number of queries regardless of how many programs
// are passed (a single query via a CTE, whether called with one program from
// hydrate or a whole page of them from List — see ProgramStore.List). The due day
// is derived from WHICH specific day the most recently started program workout
// was logged against (workouts.program_day_id), not from a blind workout count —
// a count is blind to repeats and out-of-order logging, silently desyncing
// "today's workout" from what was actually done. The due day is the next workout
// day after the last-logged one in cycle order, wrapping to the first workout day
// past the end; rest days are never "due" and are skipped over. Repeating the
// same day therefore doesn't skip anything (due stays the next day in sequence),
// and logging an out-of-cycle day deliberately moves the tracker to whatever
// follows THAT day — no "remaining incomplete days" bookkeeping, by design.
//
// Only workouts whose program_day_id maps to one of THIS program's current non-rest
// days can anchor the tracker to a specific day; rows carrying a day from some other
// program are ignored outright. Rows with a NULL day and no dropped mark — clients
// that predate day tracking — can't anchor, but each one newer than the anchor still
// ADVANCES the tracker by one workout day, count-based: without that fallback a user
// on an older app build would log forever while the due day stayed parked, a
// regression from the pre-linkage COUNT-based algorithm which advanced on every
// workout. The exception is rows marked program_day_dropped (their day was
// deliberately deleted — or toggled into a rest day — by a routine edit): their
// cycle position is unknowable, and counting them as advances would let one edit
// swing the due day by the parity of an arbitrary lifetime count — deleting or
// rest-toggling a day with 50 logged workouts must not make "today's workout"
// depend on 50 mod cycle length. Dropped rows are ignored entirely. No qualifying
// workout at all → the first workout day is due. A program with no workout days
// (every day is rest, or no days yet) has nothing to be due → 0.
func (s *ProgramStore) currentDayIndex(uid int64, programs []models.Program) (map[int64]int, error) {
result := make(map[int64]int, len(programs))
workoutIdx := make(map[int64][]int, len(programs))
progByID := make(map[int64]models.Program, len(programs))
var ids []int64
for _, p := range programs {
progByID[p.ID] = p
var idx []int
for i, d := range p.Days {
if !d.IsRestDay {
idx = append(idx, i)
}
}
if len(idx) == 0 {
result[p.ID] = 0 // no workout days at all — nothing to be due
continue
}
workoutIdx[p.ID] = idx
ids = append(ids, p.ID)
}
if len(ids) == 0 {
return result, nil
}
idSelects := make([]string, len(ids))
inPlaceholders := make([]string, len(ids))
for i := range ids {
idSelects[i] = "SELECT ? AS program_id"
inPlaceholders[i] = "?"
}
idsCSV := strings.Join(inPlaceholders, ",")
query := `
WITH ids(program_id) AS (` + strings.Join(idSelects, " UNION ALL ") + `),
anchors AS (
SELECT w.program_id AS program_id, w.program_day_id AS anchor_day_id,
w.id AS anchor_wid, w.started_at AS anchor_started_at
FROM workouts w
JOIN program_days pd ON pd.id = w.program_day_id
WHERE w.user_id = ? AND pd.is_rest_day = 0 AND w.program_id IN (` + idsCSV + `)
AND NOT EXISTS (
SELECT 1 FROM workouts w2
JOIN program_days pd2 ON pd2.id = w2.program_day_id
WHERE w2.user_id = w.user_id AND w2.program_id = w.program_id AND pd2.is_rest_day = 0
AND (w2.started_at > w.started_at OR (w2.started_at = w.started_at AND w2.id > w.id))
)
)
SELECT ids.program_id, a.anchor_day_id, a.anchor_wid, a.anchor_started_at,
(SELECT COUNT(*) FROM workouts w
WHERE w.user_id = ? AND w.program_id = ids.program_id
AND w.program_day_id IS NULL AND w.program_day_dropped = 0
AND (a.anchor_started_at IS NULL
OR w.started_at > a.anchor_started_at
OR (w.started_at = a.anchor_started_at AND w.id > a.anchor_wid))
) AS unlinked
FROM ids
LEFT JOIN anchors a ON a.program_id = ids.program_id`
args := make([]any, 0, 2*len(ids)+2)
for _, id := range ids {
args = append(args, id)
}
args = append(args, uid)
for _, id := range ids {
args = append(args, id)
}
args = append(args, uid)
rows, err := s.db.Query(query, args...)
if err != nil {
return nil, err
}
defer rows.Close()
for rows.Next() {
var pid int64
var anchorDayID sql.NullInt64
var anchorWID sql.NullInt64
var anchorStarted sql.NullString
var unlinked int
if err := rows.Scan(&pid, &anchorDayID, &anchorWID, &anchorStarted, &unlinked); err != nil {
return nil, err
}
idx := workoutIdx[pid]
if !anchorDayID.Valid {
result[pid] = idx[unlinked%len(idx)]
continue
}
p := progByID[pid]
found := false
for pos, wi := range idx {
if p.Days[wi].ID == anchorDayID.Int64 {
result[pid] = idx[(pos+1+unlinked)%len(idx)]
found = true
break
}
}
if !found {
// Anchor day vanished between the query and here — fall through as if its
// log were day-less too (mirrors currentDayIndex's own fallback).
result[pid] = idx[(unlinked+1)%len(idx)]
}
}
return result, rows.Err()
}
func (s *ProgramStore) Create(uid int64, req models.CreateProgramRequest, locale string) (models.Program, error) {
pid, err := inTx(s.db, func(tx *sql.Tx) (int64, error) {
res, err := tx.Exec(`INSERT INTO programs (user_id, name, notes) VALUES (?, ?, ?)`, uid, req.Name, req.Notes)
if err != nil {
return 0, err
}
pid, err := res.LastInsertId()
if err != nil {
return 0, err
}
if err := insertProgramDays(tx, pid, req.Days); err != nil {
return 0, err
}
return pid, nil
})
if err != nil {
return models.Program{}, err
}
return s.get(pid, locale)
}
// Update replaces a user-owned program and its days/exercises/sets in one tx.
// sql.ErrNoRows if the program isn't theirs (nothing is mutated).
//
// Day rows are updated IN PLACE, matched to the request by day id (CreateProgramDayReq.ID)
// — never deleted + recreated wholesale. workouts.program_day_id references
// program_days ON DELETE SET NULL, so recreating every day on any edit (even a name
// typo) would scrub the day linkage off the program's entire workout history and
// reset the due-day tracker to Day 1 — the exact silent desync the linkage exists to
// prevent. Id matching (not position) keeps that linkage correct across reorders and
// mid-list removals too: position alone can't tell "renamed day 0" from "deleted
// day 0". Request content alone can't distinguish a legacy id-less payload from a
// modern client that deleted every existing day and added only new ones (zero ids
// either way), so modern clients also send DayIDsKnown to force id matching for
// that edit — the deleted days' history is dropped as residue instead of being
// positionally re-attributed to the brand-new days. Only requests with no ids AND
// no flag (clients predating the id round-trip) fall back to positional matching —
// their same-position edits keep working; only their structural edits carry the old
// positional mis-attribution.
//
// Only days actually absent from the request are deleted; their workouts' linkage is
// dropped AND marked program_day_dropped so the due-day tracker ignores those rows
// as residue instead of miscounting them as day-less logs (see currentDayIndex).
// A day edited into a rest day sheds its workouts' linkage the same way (NULL +
// dropped): a rest day can never be due, so keeping the link would make those
// workouts invisible to the tracker, and counting them as day-less advances would
// swing the due day by the parity of that day's logged history. Each day's
// exercises/sets are still replaced wholesale: nothing persists a reference to them
// (a logged set's program_set_id is request-only, never stored), so recreating them
// is safe — the delete cascades to program_sets via the program_exercise_id FK,
// same layered cascade the flat model relied on.
func (s *ProgramStore) Update(uid, id int64, req models.CreateProgramRequest, locale string) (models.Program, error) {
if err := inTxDo(s.db, func(tx *sql.Tx) error {
var ownedID int64
if err := tx.QueryRow(`SELECT id FROM programs WHERE id = ? AND user_id = ?`, id, uid).Scan(&ownedID); err != nil {
return err
}
if _, err := tx.Exec(`UPDATE programs SET name = ?, notes = ? WHERE id = ?`, req.Name, req.Notes, id); err != nil {
return err
}
existing, err := dayIDsInOrder(tx, id)
if err != nil {
return err
}
hasIDs := req.DayIDsKnown
for _, day := range req.Days {
if day.ID != 0 {
hasIDs = true
break
}
}
isExisting := make(map[int64]bool, len(existing))
for _, dayID := range existing {
isExisting[dayID] = true
}
kept := make(map[int64]bool, len(existing))
for i, day := range req.Days {
// Which existing row this request day is: by id when the client sends ids
// (an id from some other program — or a duplicate — is treated as a new
// day, so a crafted request can't touch rows outside this owned program),
// by position for legacy id-less requests. 0 = a genuinely new day.
var dayID int64
if hasIDs {
if day.ID != 0 && isExisting[day.ID] && !kept[day.ID] {
dayID = day.ID
}
} else if i < len(existing) {
dayID = existing[i]
}
if dayID == 0 {
if err := insertProgramDay(tx, id, i, day); err != nil {
return err
}
continue
}
kept[dayID] = true
if _, err := tx.Exec(
`UPDATE program_days SET order_index = ?, is_rest_day = ?, name = ? WHERE id = ?`,
i, day.IsRestDay, day.Name, dayID,
); err != nil {
return err
}
if _, err := tx.Exec(`DELETE FROM program_exercises WHERE program_day_id = ?`, dayID); err != nil {
return err
}
if day.IsRestDay {
// Un-link workouts logged against this now-rest day (also lazily scrubs
// any pre-existing rest-day linkage): rest days are excluded from the
// tracker's anchor lookup, so a kept link would strand those workouts —
// neither anchoring nor advancing. Marked dropped exactly like a deleted
// day's rows: the day's cycle position is gone either way, and counting
// the shed rows as day-less advances would swing the due day by the
// parity of the day's whole logged history (see currentDayIndex).
if err := dropDayLinkage(tx, dayID); err != nil {
return err
}
continue // rest days never carry exercises, even if the client sent some
}
if err := insertProgramExercises(tx, id, dayID, day.Exercises); err != nil {
return err
}
}
for _, dayID := range existing {
if kept[dayID] {
continue
}
// Deliberately removed day: drop its workouts' linkage (per the ON DELETE
// SET NULL contract) but mark them dropped — their cycle position is
// unknowable, and counting them as day-less advances would swing the due
// day by the parity of an arbitrary historical count (see currentDayIndex).
if err := dropDayLinkage(tx, dayID); err != nil {
return err
}
if _, err := tx.Exec(`DELETE FROM program_days WHERE id = ?`, dayID); err != nil {
return err
}
}
return nil
}); err != nil {
return models.Program{}, err
}
return s.get(id, locale)
}
// dropDayLinkage severs workouts.program_day_id for a day that's being deleted or
// rest-toggled, marking them program_day_dropped so currentDayIndex's "unlinked rows
// advance the tracker" fallback skips them — their cycle position became unknowable
// the moment the day went away, so counting them would swing the due day by the
// parity of an arbitrary historical count (see currentDayIndex's own comment).
func dropDayLinkage(tx *sql.Tx, dayID int64) error {
_, err := tx.Exec(`UPDATE workouts SET program_day_id = NULL, program_day_dropped = 1 WHERE program_day_id = ?`, dayID)
return err
}
// dayIDsInOrder returns a program's existing day row ids in cycle order, within tx.
func dayIDsInOrder(tx *sql.Tx, programID int64) ([]int64, error) {
rows, err := tx.Query(`SELECT id FROM program_days WHERE program_id = ? ORDER BY order_index`, programID)
if err != nil {
return nil, err
}
defer rows.Close()
ids := []int64{}
for rows.Next() {
var id int64
if err := rows.Scan(&id); err != nil {
return nil, err
}
ids = append(ids, id)
}
return ids, rows.Err()
}
// ProgressInput is one logged working set to consider for auto-progression:
// the routine set it came from, what the user logged, and whether that logged set
// was also an all-time best (computed by the controller from a pre-save PR snapshot).
type ProgressInput struct {
ProgramSetID int64
Weight float64
Reps int
IsPR bool
}
// SuggestTargets stages (does NOT apply) a per-set target suggestion for each set the
// user beat this workout (issue #40). The user later approves them on the routine via
// ResolveSuggestions. Upward only, per progressedTarget. Returns the routine name, how
// many suggestions were staged, and whether any was an all-time PR (drives the 🏆
// toast). If the program isn't the caller's, nothing is touched (name "", count 0, no
// error): the ownership check + the pe.program_id join stop a client from staging onto
// another user's routine by guessing set ids.
func (s *ProgramStore) SuggestTargets(uid, programID int64, sets []ProgressInput) (string, int, bool, error) {
var name string
var count int
var anyPR bool
err := inTxDo(s.db, func(tx *sql.Tx) error {
var err error
name, count, anyPR, err = suggestTargetsTx(tx, uid, programID, sets)
return err
})
if err != nil {
return "", 0, false, err
}
return name, count, anyPR, nil
}
// suggestTargetsTx is SuggestTargets' body, scoped to an existing transaction so a
// caller can serialize it with an earlier PR-snapshot read and workout insert (see
// WorkoutStore.CreateWithProgression) — closes a TOCTOU race where two concurrent
// workout submissions could both compute suggested_is_pr against the same stale prior
// best (issue #40 progression review). program_exercises keeps a denormalized
// program_id column (alongside program_day_id) specifically so this join — and the
// ownership guard below — never had to change when routines grew a day layer.
func suggestTargetsTx(tx *sql.Tx, uid, programID int64, sets []ProgressInput) (string, int, bool, error) {
var name string
count, anyPR := 0, false
if err := tx.QueryRow(`SELECT name FROM programs WHERE id = ? AND user_id = ?`, programID, uid).Scan(&name); err != nil {
if err == sql.ErrNoRows {
return "", 0, false, nil // not the caller's program — no-op, not an error
}
return "", 0, false, err
}
for _, in := range sets {
var curReps int
var curWeight float64
var sugReps sql.NullInt64
var sugWeight sql.NullFloat64
// Re-assert the set belongs to THIS (already-owned) program before touching it.
err := tx.QueryRow(
`SELECT ps.target_reps, ps.target_weight, ps.suggested_reps, ps.suggested_weight
FROM program_sets ps
JOIN program_exercises pe ON pe.id = ps.program_exercise_id
WHERE ps.id = ? AND pe.program_id = ?`,
in.ProgramSetID, programID,
).Scan(&curReps, &curWeight, &sugReps, &sugWeight)
if err == sql.ErrNoRows {
continue // set isn't in this routine anymore (edited/deleted) — skip
}
if err != nil {
return "", 0, false, err
}
// Compare against the best of (current target, any pending suggestion) so a
// smaller-but-still-over-target set can't downgrade an un-approved PR (#40 edge).
baseReps, baseWeight := curReps, curWeight
if sugReps.Valid {
baseReps = int(sugReps.Int64)
}
if sugWeight.Valid {
baseWeight = sugWeight.Float64
}
newWeight, newReps, improved := progressedTarget(baseWeight, baseReps, in.Weight, in.Reps)
if !improved {
continue
}
if _, err := tx.Exec(
`UPDATE program_sets SET suggested_weight = ?, suggested_reps = ?, suggested_is_pr = ? WHERE id = ?`,
newWeight, newReps, in.IsPR, in.ProgramSetID,
); err != nil {
return "", 0, false, err
}
count++
if in.IsPR {
anyPR = true
}
}
return name, count, anyPR, nil
}
// ResolveSuggestions applies (accept) or clears (dismiss) staged routine suggestions by
// program_set id, then returns the refreshed program. Accepting copies suggested_* into
// target_*; both paths clear the suggestion. Ownership-gated, and every id is re-scoped
// to this program via the pe.program_id sub-select — the same IDOR guard as SuggestTargets,
// so a client can't touch another user's routine by guessing ids.
func (s *ProgramStore) ResolveSuggestions(uid, programID int64, accept, dismiss []int64, locale string) (models.Program, error) {
err := inTxDo(s.db, func(tx *sql.Tx) error {
var ownedID int64
if err := tx.QueryRow(`SELECT id FROM programs WHERE id = ? AND user_id = ?`, programID, uid).Scan(&ownedID); err != nil {
return err
}
owned := `AND program_exercise_id IN (SELECT id FROM program_exercises WHERE program_id = ?)`
for _, id := range accept {
if _, err := tx.Exec(
`UPDATE program_sets
SET target_weight = COALESCE(suggested_weight, target_weight),
target_reps = COALESCE(suggested_reps, target_reps),
suggested_weight = NULL, suggested_reps = NULL, suggested_is_pr = 0
WHERE id = ? `+owned,
id, programID,
); err != nil {
return err
}
}
for _, id := range dismiss {
if _, err := tx.Exec(
`UPDATE program_sets
SET suggested_weight = NULL, suggested_reps = NULL, suggested_is_pr = 0
WHERE id = ? `+owned,
id, programID,
); err != nil {
return err
}
}
return nil
})
if err != nil {
return models.Program{}, err
}
return s.get(programID, locale)
}
// progressedTarget applies the upward-only progression rule for a single set and
// reports whether the target changed: a heavier logged weight raises the weight
// and adopts the reps done at it; the same weight with more reps raises the reps;
// equal-or-lighter leaves the target untouched (a deload never lowers a routine).
// eps absorbs float drift from unit conversion so an unchanged weight still counts
// as "same" for the reps branch.
func progressedTarget(curWeight float64, curReps int, logWeight float64, logReps int) (float64, int, bool) {
const eps = 1e-6
switch {
case logWeight > curWeight+eps:
return logWeight, logReps, true
case logWeight >= curWeight-eps && logReps > curReps:
return curWeight, logReps, true
default:
return curWeight, curReps, false
}
}
func (s *ProgramStore) Delete(uid, id int64) (int64, error) {
res, err := s.db.Exec(`DELETE FROM programs WHERE id = ? AND user_id = ?`, id, uid)
if err != nil {
return 0, err
}
n, _ := res.RowsAffected()
return n, nil
}
// insertProgramDays writes a program's Days (in request order — OrderIndex on the
// request is trusted as-is, matching the pre-existing convention for exercise/set
// ordering) plus each Day's exercises/sets within tx.
func insertProgramDays(tx *sql.Tx, programID int64, days []models.CreateProgramDayReq) error {
for i, day := range days {
if err := insertProgramDay(tx, programID, i, day); err != nil {
return err
}
}
return nil
}
// insertProgramDay writes one Day at the given cycle position. orderIndex is the
// request array position (same convention as insertProgramExercises' `i`, not a
// client-echoed value) — a client resending a stale/duplicate order_index can't
// scramble the cycle order.
func insertProgramDay(tx *sql.Tx, programID int64, orderIndex int, day models.CreateProgramDayReq) error {
dayRes, err := tx.Exec(
`INSERT INTO program_days (program_id, order_index, is_rest_day, name) VALUES (?, ?, ?, ?)`,
programID, orderIndex, day.IsRestDay, day.Name,
)
if err != nil {
return err
}
dayID, err := dayRes.LastInsertId()
if err != nil {
return err
}
if day.IsRestDay {
return nil // rest days never carry exercises, even if the client sent some
}
return insertProgramExercises(tx, programID, dayID, day.Exercises)
}
func insertProgramExercises(tx *sql.Tx, programID, dayID int64, exercises []models.CreateProgramExerciseReq) error {
for i, ex := range exercises {
exRes, err := tx.Exec(
`INSERT INTO program_exercises (program_id, program_day_id, exercise_id, order_index, notes, rest_seconds) VALUES (?, ?, ?, ?, ?, ?)`,
programID, dayID, ex.ExerciseID, i, ex.Notes, ex.RestSeconds,
)
if err != nil {
return err
}
peid, err := exRes.LastInsertId()
if err != nil {
return err
}
for j, st := range ex.Sets {
sn := st.SetNumber
if sn == 0 {
sn = j + 1
}
if _, err := tx.Exec(
`INSERT INTO program_sets (program_exercise_id, set_number, target_reps, target_weight) VALUES (?, ?, ?, ?)`,
peid, sn, st.TargetReps, st.TargetWeight,
); err != nil {
return err
}
}
}
return nil
}
// loadDays scans + closes the parent cursor BEFORE loading each day's exercises
// (#36 — same close-then-load discipline as loadExercises/loadSets below).
func (s *ProgramStore) loadDays(programID int64, locale string) ([]models.ProgramDay, error) {
rows, err := s.db.Query(
`SELECT id, program_id, order_index, is_rest_day, name FROM program_days WHERE program_id = ? ORDER BY order_index`,
programID,
)
if err != nil {
return nil, err
}
days := []models.ProgramDay{}
for rows.Next() {
var d models.ProgramDay
var isRest int
if err := rows.Scan(&d.ID, &d.ProgramID, &d.OrderIndex, &isRest, &d.Name); err != nil {
rows.Close()
return nil, err
}
d.IsRestDay = isRest != 0
days = append(days, d)
}
if err := rows.Err(); err != nil {
rows.Close()
return nil, err
}
rows.Close()
for i := range days {
if days[i].IsRestDay {
days[i].Exercises = []models.ProgramExercise{}
continue
}
ex, err := s.loadExercises(days[i].ID, locale)
if err != nil {
return nil, err
}
days[i].Exercises = ex
}
return days, nil
}
// loadExercises scans + closes the parent cursor BEFORE loading each exercise's
// sets (#36), and surfaces scan errors.
func (s *ProgramStore) loadExercises(dayID int64, locale string) ([]models.ProgramExercise, error) {
join, joinArgs := translationJoin(locale)
rows, err := s.db.Query(
`SELECT pe.id, pe.program_day_id, pe.exercise_id, pe.order_index, pe.notes, pe.rest_seconds,
`+localizedName+`, e.muscle_group, e.category, e.equipment, e.image_url
FROM program_exercises pe
JOIN exercises e ON e.id = pe.exercise_id`+join+`
WHERE pe.program_day_id = ? ORDER BY pe.order_index`,
append(joinArgs, dayID)...,
)
if err != nil {
return nil, err
}
exercises := []models.ProgramExercise{}
for rows.Next() {
var pe models.ProgramExercise
if err := rows.Scan(
&pe.ID, &pe.ProgramDayID, &pe.ExerciseID, &pe.OrderIndex, &pe.Notes, &pe.RestSeconds,
&pe.Exercise.Name, &pe.Exercise.MuscleGroup, &pe.Exercise.Category,
&pe.Exercise.Equipment, &pe.Exercise.ImageURL,
); err != nil {
rows.Close()
return nil, err
}
pe.Exercise.ID = pe.ExerciseID
exercises = append(exercises, pe)
}
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 *ProgramStore) loadSets(programExerciseID int64) ([]models.ProgramSet, error) {
rows, err := s.db.Query(
`SELECT id, program_exercise_id, set_number, target_reps, target_weight,
suggested_weight, suggested_reps, suggested_is_pr
FROM program_sets WHERE program_exercise_id = ? ORDER BY set_number`,
programExerciseID,
)
if err != nil {
return nil, err
}
defer rows.Close()
sets := []models.ProgramSet{}
for rows.Next() {
var st models.ProgramSet
var sw sql.NullFloat64
var sr sql.NullInt64
if err := rows.Scan(&st.ID, &st.ProgramExerciseID, &st.SetNumber, &st.TargetReps, &st.TargetWeight,
&sw, &sr, &st.SuggestedIsPR); err != nil {
return nil, err
}
if sw.Valid {
st.SuggestedWeight = &sw.Float64
}
if sr.Valid {
r := int(sr.Int64)
st.SuggestedReps = &r
}
sets = append(sets, st)
}
return sets, rows.Err()
}