Add workout logging CLI with SQLite storage

Exercises CRUD, session logging with sets/reps/RPE/rest/LSRPE,
session viewing and deletion. Interactive terminal menu.

1 files changed, 132 insertions, 0 deletions

diff --git a/models.py b/models.py
new file mode 100644
index 0000000..654b346
--- /dev/null
+++ b/models.py
@@ -0,0 +1,132 @@
+import sqlite3
+
+
+def add_exercise(conn: sqlite3.Connection, name: str, note: str | None = None) -> int:
+    cur = conn.execute("INSERT INTO exercises (name, note) VALUES (?, ?)", (name, 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 get_exercise(conn: sqlite3.Connection, exercise_id: int) -> sqlite3.Row | None:
+    return conn.execute(
+        "SELECT id, name, note FROM exercises WHERE id = ?", (exercise_id,)
+    ).fetchone()
+
+
+def update_exercise(
+    conn: sqlite3.Connection,
+    exercise_id: int,
+    name: str | None = None,
+    note: str | None = None,
+) -> bool:
+    ex = get_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),
+    )
+    conn.commit()
+    return True
+
+
+def delete_exercise(conn: sqlite3.Connection, exercise_id: int) -> None:
+    conn.execute("DELETE FROM exercises WHERE id = ?", (exercise_id,))
+    conn.commit()
+
+
+def create_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)
+    )
+    assert cur.lastrowid is not None
+    return cur.lastrowid
+
+
+def list_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
+           GROUP BY s.id
+           ORDER BY s.date_time DESC
+           LIMIT ?""",
+        (limit,),
+    ).fetchall()
+
+
+def get_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,)
+    ).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
+           WHERE se.session_id = ?
+           ORDER BY se.position""",
+        (session_id,),
+    ).fetchall()
+    return session, entries
+
+
+def delete_session(conn: sqlite3.Connection, session_id: int) -> None:
+    conn.execute("DELETE FROM sessions WHERE id = ?", (session_id,))
+    conn.commit()
+
+
+def add_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,
+    note: str | None = None,
+) -> int:
+    cur = conn.execute(
+        """INSERT INTO session_exercises
+           (session_id, exercise_id, position, sets, reps, rpe, rest_time, lsrpe, note)
+           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)""",
+        (session_id, exercise_id, position, sets, reps, rpe, 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]
+) -> int:
+    session_id = create_session(conn, date_time, note)
+    for i, ex in enumerate(exercises, 1):
+        add_session_exercise(
+            conn,
+            session_id,
+            ex["exercise_id"],
+            i,
+            ex["sets"],
+            ex["reps"],
+            ex.get("rpe"),
+            ex.get("rest_time"),
+            ex.get("lsrpe"),
+            ex.get("note"),
+        )
+    conn.commit()
+    return session_id