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.

41.3 KB
Raw Blame
package db

import (
	"bytes"
	"database/sql"
	"fmt"
	"log"
	"math/rand"
	"os"
	"strings"
	"testing"
	"time"

	_ "modernc.org/sqlite"
)

// setupMigrationTestDB points the package-level DB at a fresh in-memory sqlite and
// runs the base schema (but NOT alterMigrations — callers seed pre-migration rows in
// between the two, same as a real upgrade of an existing database). Foreign keys are
// on, matching the production DSN — the program_day_id ON DELETE SET NULL behavior
// under test only exists with them enforced.
func setupMigrationTestDB(t *testing.T) {
	t.Helper()
	name := fmt.Sprintf("file:migtestdb_%d?mode=memory&cache=shared&_pragma=foreign_keys(on)", rand.Int63())
	conn, err := sql.Open("sqlite", name)
	if err != nil {
		t.Fatalf("open test db: %v", err)
	}
	t.Cleanup(func() { conn.Close() })
	DB = conn
	if err := migrate(); err != nil {
		t.Fatalf("apply base schema: %v", err)
	}
}

// TestMultiDayProgramsMigration_WrapsExerciselessProgram is the regression check for
// the review finding: a pre-existing program with zero program_exercises rows (a
// valid state under the old flat model) must still come out of the migration with
// exactly one auto-created wrapper Day, not zero Days.
func TestMultiDayProgramsMigration_WrapsExerciselessProgram(t *testing.T) {
	setupMigrationTestDB(t)

	res, err := DB.Exec(`INSERT INTO users (email, password_hash) VALUES ('[email protected]', 'x')`)
	if err != nil {
		t.Fatalf("seed user: %v", err)
	}
	uid, _ := res.LastInsertId()
	res, err = DB.Exec(`INSERT INTO programs (user_id, name) VALUES (?, 'Empty Routine')`, uid)
	if err != nil {
		t.Fatalf("seed program: %v", err)
	}
	pid, _ := res.LastInsertId()

	multiDayProgramsMigration()

	var count int
	if err := DB.QueryRow(`SELECT COUNT(*) FROM program_days WHERE program_id = ?`, pid).Scan(&count); err != nil {
		t.Fatalf("count program_days: %v", err)
	}
	if count != 1 {
		t.Fatalf("expected the exerciseless program to get exactly 1 wrapper Day, got %d", count)
	}
	var orderIndex, isRest int
	if err := DB.QueryRow(`SELECT order_index, is_rest_day FROM program_days WHERE program_id = ?`, pid).
		Scan(&orderIndex, &isRest); err != nil {
		t.Fatalf("read wrapper day: %v", err)
	}
	if orderIndex != 0 || isRest != 0 {
		t.Fatalf("expected wrapper day order_index=0 is_rest_day=0, got order_index=%d is_rest_day=%d", orderIndex, isRest)
	}
}

// TestMultiDayProgramsMigration_IsIdempotent running it twice must not create a
// second wrapper Day for a program that already has one (matches the real boot path,
// where alterMigrations runs on every startup).
func TestMultiDayProgramsMigration_IsIdempotent(t *testing.T) {
	setupMigrationTestDB(t)

	res, err := DB.Exec(`INSERT INTO users (email, password_hash) VALUES ('[email protected]', 'x')`)
	if err != nil {
		t.Fatalf("seed user: %v", err)
	}
	uid, _ := res.LastInsertId()
	res, err = DB.Exec(`INSERT INTO programs (user_id, name) VALUES (?, 'Routine')`, uid)
	if err != nil {
		t.Fatalf("seed program: %v", err)
	}
	pid, _ := res.LastInsertId()

	multiDayProgramsMigration()
	multiDayProgramsMigration()

	var count int
	if err := DB.QueryRow(`SELECT COUNT(*) FROM program_days WHERE program_id = ?`, pid).Scan(&count); err != nil {
		t.Fatalf("count program_days: %v", err)
	}
	if count != 1 {
		t.Fatalf("expected exactly 1 wrapper Day after running the migration twice, got %d", count)
	}
}

func countRow(t *testing.T, query string, args ...any) int {
	t.Helper()
	var n int
	if err := DB.QueryRow(query, args...).Scan(&n); err != nil {
		t.Fatalf("count %q: %v", query, err)
	}
	return n
}

// TestUpgradeFromPreDayStructureDeployment simulates upgrading a REAL older install
// straight to HEAD: the base schema plus the progressive-overload (#40) columns is
// exactly what a deployment running #40 but predating the multi-day rework had on
// disk. Seed old-shape user data — a flat program with exercises, an UNRESOLVED
// staged suggestion, logged workout history — then run the full alterMigrations()
// sequence (the multi-day rework AND the program_day_id fix in one boot) and assert
// zero data loss.
func TestUpgradeFromPreDayStructureDeployment(t *testing.T) {
	setupMigrationTestDB(t)
	// The #40-era columns that deployment already had (same ensureColumn calls its
	// boots ran).
	ensureColumn("program_sets", "suggested_weight", `ALTER TABLE program_sets ADD COLUMN suggested_weight REAL`)
	ensureColumn("program_sets", "suggested_reps", `ALTER TABLE program_sets ADD COLUMN suggested_reps INTEGER`)
	ensureColumn("program_sets", "suggested_is_pr", `ALTER TABLE program_sets ADD COLUMN suggested_is_pr INTEGER NOT NULL DEFAULT 0`)

	if _, err := DB.Exec(`INSERT INTO users (email, password_hash) VALUES ('old@user', 'x')`); err != nil {
		t.Fatalf("seed user: %v", err)
	}
	if _, err := DB.Exec(`INSERT INTO exercises (name) VALUES ('Bench Press')`); err != nil {
		t.Fatalf("seed exercise: %v", err)
	}
	if _, err := DB.Exec(`INSERT INTO programs (user_id, name) VALUES (1, 'Old Flat Program')`); err != nil {
		t.Fatalf("seed program: %v", err)
	}
	if _, err := DB.Exec(`INSERT INTO program_exercises (program_id, exercise_id, order_index) VALUES (1, 1, 0)`); err != nil {
		t.Fatalf("seed program exercise: %v", err)
	}
	if _, err := DB.Exec(
		`INSERT INTO program_sets (program_exercise_id, set_number, target_reps, target_weight, suggested_weight, suggested_reps, suggested_is_pr)
		 VALUES (1, 1, 5, 135, 140, 5, 1)`); err != nil {
		t.Fatalf("seed program set: %v", err)
	}
	// Logged history — pre-day workouts had no program linkage at all.
	if _, err := DB.Exec(`INSERT INTO workouts (user_id, name) VALUES (1, 'Old Workout')`); err != nil {
		t.Fatalf("seed workout: %v", err)
	}
	if _, err := DB.Exec(`INSERT INTO workout_exercises (workout_id, exercise_id) VALUES (1, 1)`); err != nil {
		t.Fatalf("seed workout exercise: %v", err)
	}
	if _, err := DB.Exec(`INSERT INTO sets (workout_exercise_id, reps, weight) VALUES (1, 5, 135)`); err != nil {
		t.Fatalf("seed set: %v", err)
	}

	alterMigrations()

	// The flat program got wrapped in exactly one workout Day that owns its exercises.
	if n := countRow(t, `SELECT COUNT(*) FROM program_days WHERE program_id = 1 AND is_rest_day = 0 AND order_index = 0`); n != 1 {
		t.Fatalf("expected exactly 1 wrapper day, got %d", n)
	}
	if n := countRow(t, `SELECT COUNT(*) FROM program_exercises WHERE program_id = 1 AND program_day_id IS NULL`); n != 0 {
		t.Fatalf("expected no orphaned program_exercises, got %d", n)
	}
	// The staged (#40) suggestion survived both migrations untouched.
	var sw float64
	var sr, spr int
	if err := DB.QueryRow(`SELECT suggested_weight, suggested_reps, suggested_is_pr FROM program_sets WHERE id = 1`).Scan(&sw, &sr, &spr); err != nil {
		t.Fatalf("read staged suggestion: %v", err)
	}
	if sw != 140 || sr != 5 || spr != 1 {
		t.Fatalf("staged suggestion mutated by migrations: got weight=%v reps=%d is_pr=%d", sw, sr, spr)
	}
	// Workout history intact; the pre-day workout has no program linkage, so the
	// program_day_id backfill must NOT invent one (it only touches program_id rows).
	if n := countRow(t, `SELECT COUNT(*) FROM workouts`); n != 1 {
		t.Fatalf("workout history lost: %d rows", n)
	}
	if n := countRow(t, `SELECT COUNT(*) FROM workouts WHERE program_day_id IS NOT NULL`); n != 0 {
		t.Fatalf("backfill wrongly linked a non-program workout")
	}
	if n := countRow(t, `SELECT COUNT(*) FROM sets WHERE reps = 5 AND weight = 135`); n != 1 {
		t.Fatalf("logged sets lost")
	}
	// Invariant (#40 / day-delete safety): logged sets never persist a program_set_id
	// FK back to the routine — the column must not exist after any migration.
	if has, err := hasColumn("sets", "program_set_id"); err != nil {
		t.Fatalf("check sets.program_set_id: %v", err)
	} else if has {
		t.Fatalf("sets grew a program_set_id column — logged history must never hold a live FK into program_sets")
	}
	// Re-running the whole sequence is idempotent (every boot runs it).
	alterMigrations()
	if n := countRow(t, `SELECT COUNT(*) FROM program_days WHERE program_id = 1`); n != 1 {
		t.Fatalf("second boot duplicated wrapper days: %d", n)
	}
}

// TestNormalizeWorkoutStartedAt_RewritesPreFixOffsetRows seeds started_at text in
// every shape a pre-UTC-normalization deployment could have written — a no-name
// fixed-offset zone ('+0800 +0800', the driver's String() of a JSON '+08:00'
// timestamp, unreadable as time.Time), a named-zone offset ('-0500 EST'), a UTC row,
// and a CURRENT_TIMESTAMP-shaped row — and asserts the backfill rewrites only the
// non-UTC rows to UTC so stored-text ordering matches instant ordering.
func TestNormalizeWorkoutStartedAt_RewritesPreFixOffsetRows(t *testing.T) {
	setupMigrationTestDB(t)
	if _, err := DB.Exec(`INSERT INTO users (email, password_hash) VALUES ('tz@user', 'x')`); err != nil {
		t.Fatalf("seed user: %v", err)
	}
	insert := func(name, startedAt string) int64 {
		t.Helper()
		res, err := DB.Exec(`INSERT INTO workouts (user_id, name, started_at) VALUES (1, ?, ?)`, name, startedAt)
		if err != nil {
			t.Fatalf("seed workout %s: %v", name, err)
		}
		id, _ := res.LastInsertId()
		return id
	}
	storedText := func(id int64) string {
		t.Helper()
		var s string
		if err := DB.QueryRow(`SELECT CAST(started_at AS TEXT) FROM workouts WHERE id = ?`, id).Scan(&s); err != nil {
			t.Fatalf("read started_at of %d: %v", id, err)
		}
		return s
	}

	// Wall-clock 02:00 the "next day" but instant 2026-07-18 18:00Z — raw text
	// ordering would wrongly rank it newest.
	fixedOffset := insert("fixed-offset", "2026-07-19 02:00:00 +0800 +0800")
	utc := insert("utc", "2026-07-18 20:00:00 +0000 UTC")
	// Instant 2026-07-19 02:00Z — the true most recent.
	namedZone := insert("named-zone", "2026-07-18 21:00:00 -0500 EST")
	plain := insert("plain", "2026-07-10 08:00:00")

	normalizeWorkoutStartedAt()

	if got := storedText(fixedOffset); got != "2026-07-18 18:00:00 +0000 UTC" {
		t.Fatalf("fixed-offset row not normalized: %q", got)
	}
	if got := storedText(namedZone); got != "2026-07-19 02:00:00 +0000 UTC" {
		t.Fatalf("named-zone row not normalized: %q", got)
	}
	if got := storedText(utc); got != "2026-07-18 20:00:00 +0000 UTC" {
		t.Fatalf("UTC row must be untouched: %q", got)
	}
	if got := storedText(plain); got != "2026-07-10 08:00:00" {
		t.Fatalf("zoneless row must be untouched: %q", got)
	}
	// Text ordering is now chronological: the named-zone row is the true most recent.
	var newest int64
	if err := DB.QueryRow(`SELECT id FROM workouts ORDER BY started_at DESC, id DESC LIMIT 1`).Scan(&newest); err != nil {
		t.Fatalf("order query: %v", err)
	}
	if newest != namedZone {
		t.Fatalf("expected the -0500 row (id %d) to text-order newest after normalization, got id %d", namedZone, newest)
	}
	// Idempotent: a second boot finds nothing to rewrite.
	normalizeWorkoutStartedAt()
	if got := storedText(fixedOffset); got != "2026-07-18 18:00:00 +0000 UTC" {
		t.Fatalf("second run changed an already-normalized row: %q", got)
	}
}

func TestNormalizeFoodLoggedAt_RewritesPreFixOffsetRows(t *testing.T) {
	setupMigrationTestDB(t)
	if _, err := DB.Exec(`INSERT INTO users (email, password_hash) VALUES ('tz@food', 'x')`); err != nil {
		t.Fatalf("seed user: %v", err)
	}
	insert := func(name, loggedAt string) int64 {
		t.Helper()
		res, err := DB.Exec(`INSERT INTO food_logs (user_id, name, logged_at) VALUES (1, ?, ?)`, name, loggedAt)
		if err != nil {
			t.Fatalf("seed food log %s: %v", name, err)
		}
		id, _ := res.LastInsertId()
		return id
	}
	storedText := func(id int64) string {
		t.Helper()
		var s string
		if err := DB.QueryRow(`SELECT CAST(logged_at AS TEXT) FROM food_logs WHERE id = ?`, id).Scan(&s); err != nil {
			t.Fatalf("read logged_at of %d: %v", id, err)
		}
		return s
	}

	fixedOffset := insert("fixed-offset", "2026-07-19 02:00:00 +0800 +0800")
	utc := insert("utc", "2026-07-18 20:00:00 +0000 UTC")
	namedZone := insert("named-zone", "2026-07-18 21:00:00 -0500 EST")
	plain := insert("plain", "2026-07-10 08:00:00")

	normalizeFoodLoggedAt()

	if got := storedText(fixedOffset); got != "2026-07-18 18:00:00 +0000 UTC" {
		t.Fatalf("fixed-offset row not normalized: %q", got)
	}
	if got := storedText(namedZone); got != "2026-07-19 02:00:00 +0000 UTC" {
		t.Fatalf("named-zone row not normalized: %q", got)
	}
	if got := storedText(utc); got != "2026-07-18 20:00:00 +0000 UTC" {
		t.Fatalf("UTC row must be untouched: %q", got)
	}
	if got := storedText(plain); got != "2026-07-10 08:00:00" {
		t.Fatalf("zoneless row must be untouched: %q", got)
	}
	var inRange int
	if err := DB.QueryRow(
		`SELECT COUNT(*) FROM food_logs WHERE logged_at >= ? AND logged_at < ?`,
		"2026-07-18 00:00:00 +0000 UTC", "2026-07-19 00:00:00 +0000 UTC",
	).Scan(&inRange); err != nil {
		t.Fatalf("range query: %v", err)
	}
	if inRange != 2 {
		t.Fatalf("expected 2 rows in the July 18 UTC window after normalization, got %d", inRange)
	}

	normalizeFoodLoggedAt()
	if got := storedText(fixedOffset); got != "2026-07-18 18:00:00 +0000 UTC" {
		t.Fatalf("second run changed an already-normalized row: %q", got)
	}

	done, err := hasMigrationFlag("normalize_food_logged_at")
	if err != nil {
		t.Fatalf("check flag: %v", err)
	}
	if !done {
		t.Fatalf("expected normalize_food_logged_at flag set once a run finds nothing to rewrite")
	}
	if wDone, err := hasMigrationFlag("normalize_workout_started_at"); err != nil || wDone {
		t.Fatalf("food normalization must not touch the workouts flag (done=%v, err=%v)", wDone, err)
	}
}

// TestProgramDayBackfillTransitionalWindow simulates the deployment window that ran
// the multi-day release (program_days + workouts.program_id exist) but predates the
// program_day_id fix: its program workouts carry program_id and no day linkage. The
// backfill must attribute those rows to the program's FIRST workout day (lowest
// order_index, non-rest — not merely order_index 0, which can be a rest slot) — and
// must never re-run once the column exists, because a NULL can then also mean "its
// day was deleted" (ON DELETE SET NULL), which a re-run would wrongly overwrite.
func TestProgramDayBackfillTransitionalWindow(t *testing.T) {
	setupMigrationTestDB(t)

	if _, err := DB.Exec(`INSERT INTO users (email, password_hash) VALUES ('old@user', 'x')`); err != nil {
		t.Fatalf("seed user: %v", err)
	}
	if _, err := DB.Exec(`INSERT INTO programs (user_id, name) VALUES (1, 'Cycle')`); err != nil {
		t.Fatalf("seed program: %v", err)
	}

	// Bring the DB to the multi-day release's shape, then rebuild the cycle so its
	// FIRST slot is a rest day.
	multiDayProgramsMigration()
	ensureColumn("workouts", "program_id", `ALTER TABLE workouts ADD COLUMN program_id INTEGER`)
	if _, err := DB.Exec(`DELETE FROM program_days WHERE program_id = 1`); err != nil {
		t.Fatalf("clear wrapper day: %v", err)
	}
	var dayIDs [3]int64
	for i, rest := range []int{1, 0, 0} { // 0=Rest 1=A 2=B
		res, err := DB.Exec(`INSERT INTO program_days (program_id, order_index, is_rest_day, name) VALUES (1, ?, ?, '')`, i, rest)
		if err != nil {
			t.Fatalf("seed day %d: %v", i, err)
		}
		dayIDs[i], _ = res.LastInsertId()
	}
	// Two transitional-window workouts: attributed to the program, day unknown.
	for i := 0; i < 2; i++ {
		if _, err := DB.Exec(`INSERT INTO workouts (user_id, name, program_id) VALUES (1, 'w', 1)`); err != nil {
			t.Fatalf("seed transitional workout: %v", err)
		}
	}

	workoutProgramDayMigration()

	if n := countRow(t, `SELECT COUNT(*) FROM workouts WHERE program_id = 1 AND program_day_id = ?`, dayIDs[1]); n != 2 {
		t.Fatalf("expected both transitional workouts backfilled to the first WORKOUT day (A), got %d", n)
	}

	// Delete day A → SET NULL on its workouts; a re-run must NOT re-backfill them.
	if _, err := DB.Exec(`DELETE FROM program_days WHERE id = ?`, dayIDs[1]); err != nil {
		t.Fatalf("delete day A: %v", err)
	}
	if n := countRow(t, `SELECT COUNT(*) FROM workouts WHERE program_day_id IS NULL`); n != 2 {
		t.Fatalf("ON DELETE SET NULL didn't drop the linkage: %d null rows", n)
	}
	workoutProgramDayMigration()
	if n := countRow(t, `SELECT COUNT(*) FROM workouts WHERE program_day_id IS NULL`); n != 2 {
		t.Fatalf("backfill re-ran on an already-migrated DB and overwrote SET NULL rows")
	}
}

// An existing server upgrading to stored days must not move anything a user can see.
//
// This is the one property no fresh-database test can check: the app worked before by
// deriving each day from the account's zone at read time, and works after by reading a
// day off the row. The upgrade is only safe if the backfill reproduces the old answer
// exactly — so the assertion is against the *old* rule, computed independently here.
func TestBackfillLocalDays_reproducesThePreMigrationDay(t *testing.T) {
	setupMigrationTestDB(t)

	if _, err := DB.Exec(`INSERT INTO users (email, password_hash) VALUES ('old@user', 'x')`); err != nil {
		t.Fatalf("seed user: %v", err)
	}
	// A user already living west of UTC, the case the whole feature exists for.
	if _, err := DB.Exec(`INSERT INTO user_settings (user_id, timezone) VALUES (1, 'America/New_York')`); err != nil {
		t.Fatalf("seed settings: %v", err)
	}

	// Instants chosen to straddle both midnights: 03:00Z is the previous day in New
	// York, 16:00Z is the same day, and 23:30Z is still the same day there.
	instants := []time.Time{
		time.Date(2026, 8, 5, 3, 0, 0, 0, time.UTC),
		time.Date(2026, 8, 5, 16, 0, 0, 0, time.UTC),
		time.Date(2026, 8, 5, 23, 30, 0, 0, time.UTC),
	}
	for i, at := range instants {
		if _, err := DB.Exec(
			`INSERT INTO food_logs (user_id, name, meal, calories, protein, carbs, fat, servings, logged_at)
			 VALUES (1, ?, 'dinner', 100, 0, 0, 0, 1, ?)`, fmt.Sprintf("meal-%d", i), at); err != nil {
			t.Fatalf("seed food %d: %v", i, err)
		}
		if _, err := DB.Exec(`INSERT INTO weight_logs (user_id, weight, logged_at) VALUES (1, 180, ?)`, at); err != nil {
			t.Fatalf("seed weight %d: %v", i, err)
		}
		if _, err := DB.Exec(`INSERT INTO workouts (user_id, name, started_at) VALUES (1, ?, ?)`,
			fmt.Sprintf("lift-%d", i), at); err != nil {
			t.Fatalf("seed workout %d: %v", i, err)
		}
	}

	alterMigrations()

	// The pre-migration rule, spelled out rather than reused, so a bug in the
	// migration's helper cannot also define what "correct" means here.
	ny, err := time.LoadLocation("America/New_York")
	if err != nil {
		t.Fatalf("load zone: %v", err)
	}
	for i, at := range instants {
		want := at.In(ny).Format("2006-01-02")

		var foodDay, weightDay string
		if err := DB.QueryRow(`SELECT logged_on FROM food_logs WHERE name = ?`, fmt.Sprintf("meal-%d", i)).Scan(&foodDay); err != nil {
			t.Fatalf("read food day %d: %v", i, err)
		}
		if foodDay != want {
			t.Errorf("food %d (%s): backfilled %q, pre-migration read gave %q", i, at.Format(time.RFC3339), foodDay, want)
		}
		if err := DB.QueryRow(`SELECT logged_on FROM weight_logs WHERE logged_at = ?`, at).Scan(&weightDay); err != nil {
			t.Fatalf("read weight day %d: %v", i, err)
		}
		if weightDay != want {
			t.Errorf("weight %d: backfilled %q, want %q", i, weightDay, want)
		}

		// The workout stores an offset instead of a day; applying it must name the
		// same day. -240 in August (EDT), -300 in winter.
		var offset int
		if err := DB.QueryRow(`SELECT tz_offset_minutes FROM workouts WHERE name = ?`, fmt.Sprintf("lift-%d", i)).Scan(&offset); err != nil {
			t.Fatalf("read workout offset %d: %v", i, err)
		}
		if got := at.Add(time.Duration(offset) * time.Minute).Format("2006-01-02"); got != want {
			t.Errorf("workout %d: offset %d puts it on %q, want %q", i, offset, got, want)
		}
	}
}

// The backfill must not run twice, and must leave already-filled rows alone — a
// second boot re-deriving days would undo any day a client had since corrected.
func TestBackfillLocalDays_isIdempotent(t *testing.T) {
	setupMigrationTestDB(t)
	if _, err := DB.Exec(`INSERT INTO users (email, password_hash) VALUES ('old@user', 'x')`); err != nil {
		t.Fatalf("seed user: %v", err)
	}
	if _, err := DB.Exec(
		`INSERT INTO food_logs (user_id, name, meal, calories, protein, carbs, fat, servings, logged_at)
		 VALUES (1, 'meal', 'dinner', 100, 0, 0, 0, 1, ?)`,
		time.Date(2026, 8, 5, 16, 0, 0, 0, time.UTC)); err != nil {
		t.Fatalf("seed food: %v", err)
	}
	alterMigrations()

	// Stand in for a client that has since filed this entry under a different day.
	if _, err := DB.Exec(`UPDATE food_logs SET logged_on = '1999-01-01' WHERE name = 'meal'`); err != nil {
		t.Fatalf("overwrite day: %v", err)
	}
	alterMigrations()

	var day string
	if err := DB.QueryRow(`SELECT logged_on FROM food_logs WHERE name = 'meal'`).Scan(&day); err != nil {
		t.Fatalf("read day: %v", err)
	}
	if day != "1999-01-01" {
		t.Errorf("second migration pass rewrote a filled day to %q", day)
	}
}

// One unreadable legacy timestamp must not take the rest of the upgrade with it.
//
// backfillDayColumn used to scan straight into time.Time, so a value the driver cannot
// parse failed the scan, returned an error, and made backfillLocalDays bail — before
// the workout-offset backfill and before the completion flag. Every later boot retried
// and failed identically, leaving logged_on unset and tz_offset_minutes null forever.
// A row that cannot be read is now skipped and reported; everything else still lands.
func TestBackfillLocalDays_oneUnreadableRowDoesNotBlockTheRest(t *testing.T) {
	setupMigrationTestDB(t)

	if _, err := DB.Exec(`INSERT INTO users (email, password_hash) VALUES ('old@user', 'x')`); err != nil {
		t.Fatalf("seed user: %v", err)
	}
	if _, err := DB.Exec(`INSERT INTO user_settings (user_id, timezone) VALUES (1, 'America/New_York')`); err != nil {
		t.Fatalf("seed settings: %v", err)
	}

	good := time.Date(2026, 8, 5, 16, 0, 0, 0, time.UTC)
	// Written as raw text the driver cannot turn back into a time.Time — the shape
	// normalizeUTCTimestamps exists to repair, which is why it now covers weight_logs.
	if _, err := DB.Exec(`INSERT INTO weight_logs (user_id, weight, logged_at) VALUES (1, 180, 'not-a-timestamp')`); err != nil {
		t.Fatalf("seed unreadable weight: %v", err)
	}
	if _, err := DB.Exec(`INSERT INTO weight_logs (user_id, weight, logged_at) VALUES (1, 181, ?)`, good); err != nil {
		t.Fatalf("seed good weight: %v", err)
	}
	if _, err := DB.Exec(`INSERT INTO workouts (user_id, name, started_at) VALUES (1, 'lift', ?)`, good); err != nil {
		t.Fatalf("seed workout: %v", err)
	}
	// The offset backfill reads started_at the same way, and had the same defect. Seeding
	// an unreadable weight row alone let that one survive a review: the run died in the
	// weight pass, so the workout pass never got to fail on its own account.
	if _, err := DB.Exec(`INSERT INTO workouts (user_id, name, started_at) VALUES (1, 'bad lift', 'not-a-timestamp')`); err != nil {
		t.Fatalf("seed unreadable workout: %v", err)
	}

	alterMigrations()

	var day string
	if err := DB.QueryRow(`SELECT logged_on FROM weight_logs WHERE weight = 181`).Scan(&day); err != nil {
		t.Fatalf("read good weight day: %v", err)
	}
	if want := "2026-08-05"; day != want {
		t.Errorf("readable row was not backfilled: got %q, want %q", day, want)
	}

	// The offset backfill runs after the day backfill, so it is the first casualty of
	// an early return and the clearest signal the run completed.
	var offset sql.NullInt64
	if err := DB.QueryRow(`SELECT tz_offset_minutes FROM workouts WHERE name = 'lift'`).Scan(&offset); err != nil {
		t.Fatalf("read workout offset: %v", err)
	}
	if !offset.Valid {
		t.Error("workout offset never backfilled — the run stopped at the unreadable row")
	}

	done, err := hasMigrationFlag("backfill_local_days")
	if err != nil {
		t.Fatalf("check flag: %v", err)
	}
	if !done {
		t.Error("completion flag never set — every boot will retry this forever")
	}
}

// An existing install carries the taxonomy exactly as free-exercise-db spelled it
// ("body only", "lower back"). open-exercise-db serves the same vocabulary as
// slugs. Left unconverted the table holds both spellings of one concept, and
// every equipment or muscle filter silently returns half its rows.
func TestNormalizeExerciseTaxonomy_RewritesLegacySpellings(t *testing.T) {
	setupMigrationTestDB(t)

	rows := []struct{ name, muscle, category, equipment, secondary string }{
		{"Push Up", "chest", "strength", "body only", `["triceps","lower back"]`},
		{"Deadlift", "lower back", "strength", "barbell", `["middle back","glutes"]`},
		{"Snatch", "shoulders", "olympic weightlifting", "", `[]`},
		{"Curl", "biceps", "strength", "e-z curl bar", `[]`},
		{"Ball Crunch", "abdominals", "strength", "exercise ball", `[]`},
		{"Roll Out", "abdominals", "strength", "foam roll", `[]`},
		{"Med Ball Throw", "chest", "plyometrics", "medicine ball", `[]`},
	}
	for _, r := range rows {
		if _, err := DB.Exec(`INSERT INTO exercises (name, muscle_group, category, equipment, secondary_muscles)
			VALUES (?, ?, ?, ?, ?)`, r.name, r.muscle, r.category, r.equipment, r.secondary); err != nil {
			t.Fatalf("seed %s: %v", r.name, err)
		}
	}

	alterMigrations()

	want := map[string][3]string{ // name -> {muscle_group, category, equipment}
		"Push Up":        {"chest", "strength", "body-only"},
		"Deadlift":       {"lower-back", "strength", "barbell"},
		"Snatch":         {"shoulders", "olympic-weightlifting", "none"},
		"Curl":           {"biceps", "strength", "e-z-curl-bar"},
		"Ball Crunch":    {"abdominals", "strength", "exercise-ball"},
		"Roll Out":       {"abdominals", "strength", "foam-roll"},
		"Med Ball Throw": {"chest", "plyometrics", "medicine-ball"},
	}
	for name, w := range want {
		var muscle, category, equipment string
		if err := DB.QueryRow(`SELECT muscle_group, category, equipment FROM exercises WHERE name = ?`, name).
			Scan(&muscle, &category, &equipment); err != nil {
			t.Fatalf("read %s: %v", name, err)
		}
		if muscle != w[0] || category != w[1] || equipment != w[2] {
			t.Errorf("%s = %q/%q/%q, want %q/%q/%q", name, muscle, category, equipment, w[0], w[1], w[2])
		}
	}

	var secondary string
	DB.QueryRow(`SELECT secondary_muscles FROM exercises WHERE name = 'Deadlift'`).Scan(&secondary)
	if secondary != `["middle-back","glutes"]` {
		t.Errorf("secondary_muscles = %s, want the slug form", secondary)
	}
}

// The rewrite runs once and is not re-applied. It is guarded by a migration flag
// rather than being idempotent by construction, because a second pass over a
// table that has since gained user-contributed values could rewrite something it
// should not.
func TestNormalizeExerciseTaxonomy_RunsOnce(t *testing.T) {
	setupMigrationTestDB(t)
	if _, err := DB.Exec(`INSERT INTO exercises (name, equipment) VALUES ('Push Up', 'body only')`); err != nil {
		t.Fatalf("seed: %v", err)
	}

	alterMigrations()
	if _, err := DB.Exec(`UPDATE exercises SET equipment = 'body only' WHERE name = 'Push Up'`); err != nil {
		t.Fatalf("reintroduce: %v", err)
	}
	alterMigrations()

	var equipment string
	DB.QueryRow(`SELECT equipment FROM exercises WHERE name = 'Push Up'`).Scan(&equipment)
	if equipment != "body only" {
		t.Errorf("equipment = %q, want the migration to have skipped its second run", equipment)
	}
}

// The unique index added for #115 is created with ensureIndex, which is log.Fatal on
// failure — so on any database that already holds duplicate bookmarks, getting the
// dedupe order wrong does not produce a bad row, it stops the server booting. That is
// precisely the set of installs that have the bug, which makes this the migration worth
// pinning rather than trusting.
func TestDedupeSavedFoods_collapsesDuplicatesAndIndexes(t *testing.T) {
	setupMigrationTestDB(t)

	var uid, other int64
	for _, u := range []struct {
		email string
		into  *int64
	}{{"[email protected]", &uid}, {"[email protected]", &other}} {
		res, err := DB.Exec(`INSERT INTO users (email, password_hash) VALUES (?, 'x')`, u.email)
		if err != nil {
			t.Fatalf("insert user: %v", err)
		}
		*u.into, _ = res.LastInsertId()
	}

	// The state from the issue: the same bookmark pressed twice, plus rows that only
	// look similar — a different brand, and another user's identical bookmark.
	rows := []struct {
		uid   int64
		name  string
		brand string
		kcal  float64
	}{
		{uid, "Chicken Breast", "Tesco", 165},
		{uid, "Chicken Breast", "Tesco", 170}, // duplicate, differing macros
		{uid, "Chicken Breast", "Tesco", 165}, // duplicate again
		{uid, "Chicken Breast", "Sainsbury's", 168},
		{uid, "Rolled Oats", "Quaker", 389},
		{other, "Chicken Breast", "Tesco", 165},
	}
	for _, r := range rows {
		if _, err := DB.Exec(
			`INSERT INTO saved_foods (user_id, name, brand, calories) VALUES (?, ?, ?, ?)`,
			r.uid, r.name, r.brand, r.kcal,
		); err != nil {
			t.Fatalf("seed saved_food: %v", err)
		}
	}

	alterMigrations()

	var n int
	if err := DB.QueryRow(
		`SELECT COUNT(*) FROM saved_foods WHERE user_id = ? AND name = 'Chicken Breast' AND brand = 'Tesco'`, uid,
	).Scan(&n); err != nil {
		t.Fatalf("count duplicates: %v", err)
	}
	if n != 1 {
		t.Fatalf("expected the duplicates collapsed to 1 row, got %d", n)
	}

	// Earliest row wins, so the surviving macros are the first save's.
	var kcal float64
	DB.QueryRow(
		`SELECT calories FROM saved_foods WHERE user_id = ? AND name = 'Chicken Breast' AND brand = 'Tesco'`, uid,
	).Scan(&kcal)
	if kcal != 165 {
		t.Errorf("kept calories %v, want the first save's 165", kcal)
	}

	// The look-alikes are untouched: a different brand is a different product, and
	// uniqueness is per user.
	if err := DB.QueryRow(`SELECT COUNT(*) FROM saved_foods WHERE user_id = ?`, uid).Scan(&n); err != nil {
		t.Fatalf("count user rows: %v", err)
	}
	if n != 3 {
		t.Errorf("expected 3 rows left for the user (Tesco, Sainsbury's, Oats), got %d", n)
	}
	if err := DB.QueryRow(`SELECT COUNT(*) FROM saved_foods WHERE user_id = ?`, other).Scan(&n); err != nil {
		t.Fatalf("count other user rows: %v", err)
	}
	if n != 1 {
		t.Errorf("other user's bookmark was collapsed too: %d rows", n)
	}

	// And the index now actually forbids what the migration just cleaned up.
	if _, err := DB.Exec(
		`INSERT INTO saved_foods (user_id, name, brand, calories) VALUES (?, 'Rolled Oats', 'Quaker', 389)`, uid,
	); err == nil {
		t.Fatal("a duplicate bookmark was still insertable after the migration")
	}
}

// alterMigrations runs on every boot; the dedupe must not fight the index on the second
// pass, and must not touch rows that are legitimately there.
func TestDedupeSavedFoods_isIdempotentAcrossBoots(t *testing.T) {
	setupMigrationTestDB(t)

	res, err := DB.Exec(`INSERT INTO users (email, password_hash) VALUES ('[email protected]', 'x')`)
	if err != nil {
		t.Fatalf("insert user: %v", err)
	}
	uid, _ := res.LastInsertId()
	if _, err := DB.Exec(
		`INSERT INTO saved_foods (user_id, name, brand, calories) VALUES (?, 'Oats', 'Quaker', 389)`, uid,
	); err != nil {
		t.Fatalf("seed: %v", err)
	}

	alterMigrations()
	alterMigrations()

	var n int
	DB.QueryRow(`SELECT COUNT(*) FROM saved_foods WHERE user_id = ?`, uid).Scan(&n)
	if n != 1 {
		t.Fatalf("expected the single bookmark to survive two boots, got %d rows", n)
	}
}

// The display_name column (#170) is in the base schema, so a fresh install never
// exercises the ALTER path that every EXISTING install takes. Dropping it back off
// reproduces a pre-#170 database and checks the claim the migration comment makes:
// existing rows come out with an empty name, which is what the clients read as unset.
func TestAlterMigrations_addsDisplayNameToAnExistingSettingsRow(t *testing.T) {
	setupMigrationTestDB(t)

	if _, err := DB.Exec(`ALTER TABLE user_settings DROP COLUMN display_name`); err != nil {
		t.Fatalf("make the schema look pre-#170: %v", err)
	}

	res, err := DB.Exec(`INSERT INTO users (email, password_hash) VALUES ('[email protected]', 'x')`)
	if err != nil {
		t.Fatalf("seed user: %v", err)
	}
	uid, _ := res.LastInsertId()
	if _, err := DB.Exec(`INSERT INTO user_settings (user_id, weight_unit) VALUES (?, 'kg')`, uid); err != nil {
		t.Fatalf("seed settings: %v", err)
	}

	alterMigrations()

	var name, unit string
	if err := DB.QueryRow(`SELECT display_name, weight_unit FROM user_settings WHERE user_id = ?`, uid).Scan(&name, &unit); err != nil {
		t.Fatalf("read back: %v", err)
	}
	if name != "" {
		t.Fatalf("display_name = %q, want empty for a row that predates the column", name)
	}
	// The surrounding row is untouched — the column was added, not rebuilt.
	if unit != "kg" {
		t.Fatalf("weight_unit = %q, want kg", unit)
	}

	// Second boot must be a no-op rather than an error.
	alterMigrations()
	if err := DB.QueryRow(`SELECT display_name FROM user_settings WHERE user_id = ?`, uid).Scan(&name); err != nil {
		t.Fatalf("read back after a second boot: %v", err)
	}
	if name != "" {
		t.Fatalf("display_name = %q after re-running migrations", name)
	}
}

func emailIndexCount(t *testing.T) int {
	t.Helper()
	var n int
	if err := DB.QueryRow(`SELECT COUNT(*) FROM sqlite_master WHERE type='index' AND name='idx_users_email_nocase'`).Scan(&n); err != nil {
		t.Fatalf("count index: %v", err)
	}
	return n
}

func captureLog(t *testing.T) *bytes.Buffer {
	t.Helper()
	var buf bytes.Buffer
	log.SetOutput(&buf)
	t.Cleanup(func() { log.SetOutput(os.Stderr) })
	return &buf
}

func seedEmailUser(t *testing.T, email string) int64 {
	t.Helper()
	res, err := DB.Exec(`INSERT INTO users (email, password_hash) VALUES (?, 'x')`, email)
	if err != nil {
		t.Fatalf("seed %s: %v", email, err)
	}
	id, _ := res.LastInsertId()
	if _, err := DB.Exec(`INSERT INTO user_settings (user_id) VALUES (?)`, id); err != nil {
		t.Fatalf("seed settings: %v", err)
	}
	return id
}

func TestEmailNocaseIndex_createdOnACleanInstall(t *testing.T) {
	setupMigrationTestDB(t)
	seedEmailUser(t, "[email protected]")

	alterMigrations()

	if emailIndexCount(t) != 1 {
		t.Fatal("index missing")
	}
	var email string
	if err := DB.QueryRow(`SELECT email FROM users`).Scan(&email); err != nil || email != "[email protected]" {
		t.Errorf("email = %q, %v; want it untouched", email, err)
	}
	if _, err := DB.Exec(`INSERT INTO users (email, password_hash) VALUES ('[email protected]', 'x')`); err == nil {
		t.Error("a case variant was accepted")
	}
}

func TestEmailNocaseIndex_skippedWhenAddressesCollide(t *testing.T) {
	setupMigrationTestDB(t)
	a := seedEmailUser(t, "[email protected]")
	b := seedEmailUser(t, "[email protected]")
	buf := captureLog(t)

	alterMigrations()

	if emailIndexCount(t) != 0 {
		t.Error("index created despite a collision")
	}
	var n int
	if err := DB.QueryRow(`SELECT COUNT(*) FROM users WHERE (id = ? AND email = '[email protected]') OR (id = ? AND email = '[email protected]')`, a, b).Scan(&n); err != nil || n != 2 {
		t.Errorf("users = %d, %v; want both untouched", n, err)
	}
	if err := DB.QueryRow(`SELECT COUNT(*) FROM user_settings WHERE user_id IN (?, ?)`, a, b).Scan(&n); err != nil || n != 2 {
		t.Errorf("user_settings = %d, %v; want both kept", n, err)
	}
	out := buf.String()
	if !strings.Contains(out, "[email protected]") || !strings.Contains(out, "[email protected]") {
		t.Errorf("log does not name both spellings: %s", out)
	}
	if !strings.Contains(out, "delete-account") || strings.Contains(out, "Settings") {
		t.Errorf("log should name delete-account and not Settings: %s", out)
	}
}

func TestEmailNocaseIndex_isIdempotentAcrossBoots(t *testing.T) {
	setupMigrationTestDB(t)
	seedEmailUser(t, "[email protected]")
	alterMigrations()
	alterMigrations()
	if emailIndexCount(t) != 1 {
		t.Fatal("clean install: index count != 1")
	}
	var n int
	if err := DB.QueryRow(`SELECT COUNT(*) FROM users`).Scan(&n); err != nil || n != 1 {
		t.Fatalf("users = %d, %v", n, err)
	}

	setupMigrationTestDB(t)
	seedEmailUser(t, "[email protected]")
	b := seedEmailUser(t, "[email protected]")
	alterMigrations()
	alterMigrations()
	if emailIndexCount(t) != 0 {
		t.Fatal("colliding install: index exists")
	}
	if err := DB.QueryRow(`SELECT COUNT(*) FROM users`).Scan(&n); err != nil || n != 2 {
		t.Fatalf("users = %d, %v", n, err)
	}

	if _, err := DB.Exec(`DELETE FROM users WHERE id = ?`, b); err != nil {
		t.Fatalf("delete: %v", err)
	}
	alterMigrations()
	if emailIndexCount(t) != 1 {
		t.Error("index not created after the collision was resolved")
	}
}

// TestUpgrade_ExerciseTranslationsOnExistingData upgrades an install that already
// holds exercises, workouts and programs. The new table must arrive empty and leave
// every exercise id, name and foreign key as it was: exercises.name stays the English
// base, so there is nothing to backfill and nothing that may be rewritten.
func TestUpgrade_ExerciseTranslationsOnExistingData(t *testing.T) {
	setupMigrationTestDB(t)
	// An install that has the oedb link already (the column predates this table).
	ensureColumn("exercises", "oedb_id", `ALTER TABLE exercises ADD COLUMN oedb_id TEXT`)

	if _, err := DB.Exec(`INSERT INTO users (email, password_hash) VALUES ('old@user', 'x')`); err != nil {
		t.Fatalf("seed user: %v", err)
	}
	if _, err := DB.Exec(`INSERT INTO exercises (name, description, oedb_id) VALUES ('Bench Press', '1. Press.', 'uuid-1')`); err != nil {
		t.Fatalf("seed exercise: %v", err)
	}
	if _, err := DB.Exec(`INSERT INTO exercises (name, description) VALUES ('My Own Lift', 'mine')`); err != nil {
		t.Fatalf("seed legacy exercise: %v", err)
	}
	if _, err := DB.Exec(`INSERT INTO workouts (user_id, name) VALUES (1, 'W')`); err != nil {
		t.Fatalf("seed workout: %v", err)
	}
	if _, err := DB.Exec(`INSERT INTO workout_exercises (workout_id, exercise_id) VALUES (1, 1), (1, 2)`); err != nil {
		t.Fatalf("seed workout exercises: %v", err)
	}
	if _, err := DB.Exec(`INSERT INTO sets (workout_exercise_id, reps, weight) VALUES (1, 5, 135)`); err != nil {
		t.Fatalf("seed set: %v", err)
	}
	if _, err := DB.Exec(`INSERT INTO programs (user_id, name) VALUES (1, 'P')`); err != nil {
		t.Fatalf("seed program: %v", err)
	}
	if _, err := DB.Exec(`INSERT INTO program_exercises (program_id, exercise_id, order_index) VALUES (1, 1, 0), (1, 2, 1)`); err != nil {
		t.Fatalf("seed program exercises: %v", err)
	}

	verify := func(boot string) {
		t.Helper()
		if n := countRow(t, `SELECT COUNT(*) FROM exercise_translations`); n != 0 {
			t.Fatalf("%s: exercise_translations holds %d rows, want it empty", boot, n)
		}
		for id, want := range map[int]string{1: "Bench Press", 2: "My Own Lift"} {
			var name string
			if err := DB.QueryRow(`SELECT name FROM exercises WHERE id = ?`, id).Scan(&name); err != nil || name != want {
				t.Fatalf("%s: exercise %d = %q (%v), want %q", boot, id, name, err, want)
			}
		}
		if n := countRow(t, `SELECT COUNT(*) FROM exercises WHERE description IN ('1. Press.', 'mine')`); n != 2 {
			t.Fatalf("%s: exercise descriptions changed", boot)
		}
		if n := countRow(t, `SELECT COUNT(*) FROM workout_exercises we JOIN exercises e ON e.id = we.exercise_id`); n != 2 {
			t.Fatalf("%s: a workout_exercises row lost its exercise (%d resolve)", boot, n)
		}
		if n := countRow(t, `SELECT COUNT(*) FROM program_exercises pe JOIN exercises e ON e.id = pe.exercise_id`); n != 2 {
			t.Fatalf("%s: a program_exercises row lost its exercise (%d resolve)", boot, n)
		}
	}

	alterMigrations()
	verify("first boot")
	alterMigrations()
	verify("second boot")

	// The cascade is load-bearing: deleting an exercise must take its translations
	// with it rather than fail on the foreign key.
	if _, err := DB.Exec(`INSERT INTO exercises (name) VALUES ('Spare')`); err != nil {
		t.Fatalf("seed spare: %v", err)
	}
	if _, err := DB.Exec(`INSERT INTO exercise_translations (exercise_id, locale, name) VALUES (3, 'es', 'Repuesto')`); err != nil {
		t.Fatalf("seed translation: %v", err)
	}
	if _, err := DB.Exec(`DELETE FROM exercises WHERE id = 3`); err != nil {
		t.Fatalf("deleting an exercise with a translation: %v", err)
	}
	if n := countRow(t, `SELECT COUNT(*) FROM exercise_translations`); n != 0 {
		t.Fatalf("translation survived its exercise: %d rows", n)
	}
	if _, err := DB.Exec(`INSERT INTO exercise_translations (exercise_id, locale, name, origin) VALUES (1, 'es', 'x', 'robot')`); err == nil {
		t.Fatal("an unknown origin was accepted")
	}
}

// upstream_missing_at is a nullable column on a table that workouts and routines FK
// into, so adding it must change no row and must be safe to run on every boot.
func TestUpgrade_ExerciseUpstreamMissingAt(t *testing.T) {
	setupMigrationTestDB(t)
	alterMigrations()
	// The previous release's schema: everything but the new column.
	if _, err := DB.Exec(`ALTER TABLE exercises DROP COLUMN upstream_missing_at`); err != nil {
		t.Fatalf("recreate the old schema: %v", err)
	}
	if has, _ := hasColumn("exercises", "upstream_missing_at"); has {
		t.Fatal("the old schema must not have the column")
	}
	if _, err := DB.Exec(`INSERT INTO exercises (id, name, description, oedb_id) VALUES (1, 'Upstream Row', 'desc', 'uuid-1'), (2, 'Legacy Row', 'old', NULL)`); err != nil {
		t.Fatal(err)
	}
	if _, err := DB.Exec(`INSERT INTO users (id, email, password_hash) VALUES (1, '[email protected]', 'x')`); err != nil {
		t.Fatal(err)
	}
	if _, err := DB.Exec(`INSERT INTO workouts (id, user_id, name, started_at) VALUES (1, 1, 'W', '2026-01-01 00:00:00')`); err != nil {
		t.Fatal(err)
	}
	if _, err := DB.Exec(`INSERT INTO workout_exercises (workout_id, exercise_id, order_index) VALUES (1, 1, 0)`); err != nil {
		t.Fatal(err)
	}

	alterMigrations()
	alterMigrations()

	if has, err := hasColumn("exercises", "upstream_missing_at"); err != nil || !has {
		t.Fatalf("hasColumn = %v, %v, want the column added", has, err)
	}
	if n := countRow(t, `SELECT COUNT(*) FROM exercises WHERE upstream_missing_at IS NULL`); n != 2 {
		t.Errorf("rows with a NULL mark = %d, want 2", n)
	}
	if n := countRow(t, `SELECT COUNT(*) FROM exercises WHERE (id = 1 AND name = 'Upstream Row' AND description = 'desc' AND oedb_id = 'uuid-1') OR (id = 2 AND name = 'Legacy Row' AND description = 'old' AND oedb_id IS NULL)`); n != 2 {
		t.Errorf("migrated rows = %d, want both unchanged", n)
	}
	if n := countRow(t, `SELECT COUNT(*) FROM workout_exercises we JOIN exercises e ON e.id = we.exercise_id`); n != 1 {
		t.Errorf("joined references = %d, want 1", n)
	}

	setupMigrationTestDB(t)
	alterMigrations()
	if has, _ := hasColumn("exercises", "upstream_missing_at"); !has {
		t.Error("a fresh database must have the column")
	}
}