Add templates, per-set tracking, and float weight support

- Rename tables with workout_ prefix, add workout_templates and
  workout_template_exercises tables
- Add bw_relative flag to exercises for body-weight-relative display
- Store reps, weight, and rest_time as per-set comma-separated TEXT
  in session exercises (rest_time is optional/nullable)
- Support float weight with one decimal place
- Add template CRUD and template-to-session logging flow

1 files changed, 162 insertions, 40 deletions

diff --git a/models.py b/models.py
index 654b346..a844271 100644
--- a/models.py
+++ b/models.py
@@ -1,62 +1,89 @@
 import sqlite3
 
+# --- Workout Exercises ---
 
-def add_exercise(conn: sqlite3.Connection, name: str, note: str | None = None) -> int:
-    cur = conn.execute("INSERT INTO exercises (name, note) VALUES (?, ?)", (name, note))
+
+def add_workout_exercise(
+    conn: sqlite3.Connection,
+    name: str,
+    bw_relative: bool = False,
+    note: str | None = None,
+) -> int:
+    cur = conn.execute(
+        "INSERT INTO workout_exercises (name, bw_relative, note) VALUES (?, ?, ?)",
+        (name, int(bw_relative), note),
+    )
     conn.commit()
     assert cur.lastrowid is not None
     return cur.lastrowid
 
 
-def list_exercises(conn: sqlite3.Connection) -> list[sqlite3.Row]:
-    return conn.execute("SELECT id, name, note FROM exercises ORDER BY name").fetchall()
+def list_workout_exercises(conn: sqlite3.Connection) -> list[sqlite3.Row]:
+    return conn.execute(
+        "SELECT id, name, bw_relative, note FROM workout_exercises ORDER BY name"
+    ).fetchall()
 
 
-def get_exercise(conn: sqlite3.Connection, exercise_id: int) -> sqlite3.Row | None:
+def get_workout_exercise(
+    conn: sqlite3.Connection, exercise_id: int
+) -> sqlite3.Row | None:
     return conn.execute(
-        "SELECT id, name, note FROM exercises WHERE id = ?", (exercise_id,)
+        "SELECT id, name, bw_relative, note FROM workout_exercises WHERE id = ?",
+        (exercise_id,),
     ).fetchone()
 
 
-def update_exercise(
+def update_workout_exercise(
     conn: sqlite3.Connection,
     exercise_id: int,
     name: str | None = None,
+    bw_relative: bool | None = None,
     note: str | None = None,
 ) -> bool:
-    ex = get_exercise(conn, exercise_id)
+    ex = get_workout_exercise(conn, exercise_id)
     if not ex:
         return False
     conn.execute(
-        "UPDATE exercises SET name = ?, note = ? WHERE id = ?",
-        (name or ex["name"], note, exercise_id),
+        "UPDATE workout_exercises SET name = ?, bw_relative = ?, note = ? WHERE id = ?",
+        (
+            name or ex["name"],
+            int(bw_relative) if bw_relative is not None else ex["bw_relative"],
+            note,
+            exercise_id,
+        ),
     )
     conn.commit()
     return True
 
 
-def delete_exercise(conn: sqlite3.Connection, exercise_id: int) -> None:
-    conn.execute("DELETE FROM exercises WHERE id = ?", (exercise_id,))
+def delete_workout_exercise(conn: sqlite3.Connection, exercise_id: int) -> None:
+    conn.execute("DELETE FROM workout_exercises WHERE id = ?", (exercise_id,))
     conn.commit()
 
 
-def create_session(
+# --- Workout Sessions ---
+
+
+def create_workout_session(
     conn: sqlite3.Connection, date_time: str, note: str | None = None
 ) -> int:
     cur = conn.execute(
-        "INSERT INTO sessions (date_time, note) VALUES (?, ?)", (date_time, note)
+        "INSERT INTO workout_sessions (date_time, note) VALUES (?, ?)",
+        (date_time, note),
     )
     assert cur.lastrowid is not None
     return cur.lastrowid
 
 
-def list_sessions(conn: sqlite3.Connection, limit: int = 20) -> list[sqlite3.Row]:
+def list_workout_sessions(
+    conn: sqlite3.Connection, limit: int = 20
+) -> list[sqlite3.Row]:
     return conn.execute(
         """SELECT s.id, s.date_time, s.note,
                   GROUP_CONCAT(e.name, ', ') as exercises
-           FROM sessions s
-           LEFT JOIN session_exercises se ON s.id = se.session_id
-           LEFT JOIN exercises e ON se.exercise_id = e.id
+           FROM workout_sessions s
+           LEFT JOIN workout_session_exercises se ON s.id = se.session_id
+           LEFT JOIN workout_exercises e ON se.exercise_id = e.id
            GROUP BY s.id
            ORDER BY s.date_time DESC
            LIMIT ?""",
@@ -64,19 +91,20 @@ def list_sessions(conn: sqlite3.Connection, limit: int = 20) -> list[sqlite3.Row
     ).fetchall()
 
 
-def get_session_detail(
+def get_workout_session_detail(
     conn: sqlite3.Connection, session_id: int
 ) -> tuple[sqlite3.Row | None, list[sqlite3.Row]]:
     session = conn.execute(
-        "SELECT id, date_time, note FROM sessions WHERE id = ?", (session_id,)
+        "SELECT id, date_time, note FROM workout_sessions WHERE id = ?", (session_id,)
     ).fetchone()
     if not session:
         return None, []
     entries = conn.execute(
         """SELECT se.id, se.position, e.name as exercise_name,
-                  se.sets, se.reps, se.rpe, se.rest_time, se.lsrpe, se.note
-           FROM session_exercises se
-           JOIN exercises e ON se.exercise_id = e.id
+                  e.bw_relative,
+                  se.sets, se.reps, se.weight, se.rest_time, se.lsrpe, se.note
+           FROM workout_session_exercises se
+           JOIN workout_exercises e ON se.exercise_id = e.id
            WHERE se.session_id = ?
            ORDER BY se.position""",
         (session_id,),
@@ -84,49 +112,143 @@ def get_session_detail(
     return session, entries
 
 
-def delete_session(conn: sqlite3.Connection, session_id: int) -> None:
-    conn.execute("DELETE FROM sessions WHERE id = ?", (session_id,))
+def delete_workout_session(conn: sqlite3.Connection, session_id: int) -> None:
+    conn.execute("DELETE FROM workout_sessions WHERE id = ?", (session_id,))
     conn.commit()
 
 
-def add_session_exercise(
+def add_workout_session_exercise(
     conn: sqlite3.Connection,
     session_id: int,
     exercise_id: int,
     position: int,
     sets: int,
-    reps: int,
-    rpe: int | None = None,
-    rest_time: int | None = None,
-    lsrpe: int | None = None,
+    reps: str,
+    weight: str,
+    rest_time: int,
+    lsrpe: int,
     note: str | None = None,
 ) -> int:
     cur = conn.execute(
-        """INSERT INTO session_exercises
-           (session_id, exercise_id, position, sets, reps, rpe, rest_time, lsrpe, note)
+        """INSERT INTO workout_session_exercises
+           (session_id, exercise_id, position, sets, reps, weight, rest_time, lsrpe, note)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)""",
-        (session_id, exercise_id, position, sets, reps, rpe, rest_time, lsrpe, note),
+        (session_id, exercise_id, position, sets, reps, weight, rest_time, lsrpe, note),
     )
     assert cur.lastrowid is not None
     return cur.lastrowid
 
 
-def save_session(
-    conn: sqlite3.Connection, date_time: str, note: str | None, exercises: list[dict]
+def save_workout_session(
+    conn: sqlite3.Connection,
+    date_time: str,
+    note: str | None,
+    exercises: list[dict],
 ) -> int:
-    session_id = create_session(conn, date_time, note)
+    session_id = create_workout_session(conn, date_time, note)
     for i, ex in enumerate(exercises, 1):
-        add_session_exercise(
+        add_workout_session_exercise(
             conn,
             session_id,
             ex["exercise_id"],
             i,
             ex["sets"],
             ex["reps"],
-            ex.get("rpe"),
-            ex.get("rest_time"),
-            ex.get("lsrpe"),
+            ex["weight"],
+            ex["rest_time"],
+            ex["lsrpe"],
             ex.get("note"),
         )
     conn.commit()
     return session_id
+
+
+# --- Workout Templates ---
+
+
+def create_workout_template(conn: sqlite3.Connection, name: str) -> int:
+    cur = conn.execute("INSERT INTO workout_templates (name) VALUES (?)", (name,))
+    conn.commit()
+    assert cur.lastrowid is not None
+    return cur.lastrowid
+
+
+def list_workout_templates(conn: sqlite3.Connection) -> list[sqlite3.Row]:
+    return conn.execute("""SELECT t.id, t.name,
+                  GROUP_CONCAT(e.name, ', ') as exercises
+           FROM workout_templates t
+           LEFT JOIN workout_template_exercises te ON t.id = te.template_id
+           LEFT JOIN workout_exercises e ON te.exercise_id = e.id
+           GROUP BY t.id
+           ORDER BY t.name""").fetchall()
+
+
+def get_workout_template(
+    conn: sqlite3.Connection, template_id: int
+) -> sqlite3.Row | None:
+    return conn.execute(
+        "SELECT id, name FROM workout_templates WHERE id = ?", (template_id,)
+    ).fetchone()
+
+
+def get_workout_template_detail(
+    conn: sqlite3.Connection, template_id: int
+) -> tuple[sqlite3.Row | None, list[sqlite3.Row]]:
+    template = get_workout_template(conn, template_id)
+    if not template:
+        return None, []
+    entries = conn.execute(
+        """SELECT te.id, te.position, e.id as exercise_id, e.name as exercise_name,
+                  e.bw_relative,
+                  te.sets, te.reps, te.lsrpe, te.rest_time, te.note
+           FROM workout_template_exercises te
+           JOIN workout_exercises e ON te.exercise_id = e.id
+           WHERE te.template_id = ?
+           ORDER BY te.position""",
+        (template_id,),
+    ).fetchall()
+    return template, entries
+
+
+def update_workout_template_name(
+    conn: sqlite3.Connection, template_id: int, name: str
+) -> bool:
+    t = get_workout_template(conn, template_id)
+    if not t:
+        return False
+    conn.execute(
+        "UPDATE workout_templates SET name = ? WHERE id = ?", (name, template_id)
+    )
+    conn.commit()
+    return True
+
+
+def delete_workout_template(conn: sqlite3.Connection, template_id: int) -> None:
+    conn.execute("DELETE FROM workout_templates WHERE id = ?", (template_id,))
+    conn.commit()
+
+
+def save_workout_template_exercises(
+    conn: sqlite3.Connection, template_id: int, exercises: list[dict]
+) -> None:
+    """Replace all exercises in a template with the given list."""
+    conn.execute(
+        "DELETE FROM workout_template_exercises WHERE template_id = ?", (template_id,)
+    )
+    for i, ex in enumerate(exercises, 1):
+        conn.execute(
+            """INSERT INTO workout_template_exercises
+               (template_id, exercise_id, position, sets, reps, lsrpe, rest_time, note)
+               VALUES (?, ?, ?, ?, ?, ?, ?, ?)""",
+            (
+                template_id,
+                ex["exercise_id"],
+                i,
+                ex["sets"],
+                ex["reps"],
+                ex["lsrpe"],
+                ex["rest_time"],
+                ex.get("note"),
+            ),
+        )
+    conn.commit()