From 7ceb22f1e12e3a040874a43b5e1177db83be15ed Mon Sep 17 00:00:00 2001
From: Thomas Vanbesien <tvanbesi@proton.me>
Date: Thu, 12 Mar 2026 18:00:45 +0100
Subject: 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
---
 db.py | 51 +++++++++++++++++++++++++++++++++++++--------------
 1 file changed, 37 insertions(+), 14 deletions(-)

(limited to 'db.py')

diff --git a/db.py b/db.py
index fa407d8..a5f3250 100644
--- a/db.py
+++ b/db.py
@@ -4,38 +4,61 @@ import os
 DB_PATH = os.path.join(os.path.dirname(os.path.abspath(__file__)), "egometrics.db")
 
 SCHEMA = """
-CREATE TABLE IF NOT EXISTS exercises (
+CREATE TABLE IF NOT EXISTS workout_exercises (
     id INTEGER PRIMARY KEY AUTOINCREMENT,
     name TEXT NOT NULL UNIQUE COLLATE NOCASE,
+    bw_relative BOOLEAN NOT NULL DEFAULT 0,
     note TEXT
 );
 
-CREATE TABLE IF NOT EXISTS sessions (
+CREATE TABLE IF NOT EXISTS workout_sessions (
     id INTEGER PRIMARY KEY AUTOINCREMENT,
     date_time TEXT NOT NULL,
     note TEXT
 );
 
-CREATE TABLE IF NOT EXISTS session_exercises (
+CREATE TABLE IF NOT EXISTS workout_session_exercises (
     id INTEGER PRIMARY KEY AUTOINCREMENT,
     session_id INTEGER NOT NULL,
     exercise_id INTEGER NOT NULL,
     position INTEGER NOT NULL,
-    sets INTEGER,
-    reps INTEGER,
-    rpe INTEGER,
-    rest_time INTEGER,
-    lsrpe INTEGER,
+    sets INTEGER NOT NULL,
+    reps TEXT NOT NULL,
+    weight TEXT NOT NULL,
+    rest_time TEXT,
+    lsrpe INTEGER NOT NULL,
     note TEXT,
-    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE,
-    FOREIGN KEY (exercise_id) REFERENCES exercises(id) ON DELETE RESTRICT
+    FOREIGN KEY (session_id) REFERENCES workout_sessions(id) ON DELETE CASCADE,
+    FOREIGN KEY (exercise_id) REFERENCES workout_exercises(id) ON DELETE RESTRICT
 );
 
-CREATE INDEX IF NOT EXISTS idx_session_exercises_session
-    ON session_exercises(session_id);
+CREATE TABLE IF NOT EXISTS workout_templates (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    name TEXT NOT NULL UNIQUE COLLATE NOCASE
+);
+
+CREATE TABLE IF NOT EXISTS workout_template_exercises (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    template_id INTEGER NOT NULL,
+    exercise_id INTEGER NOT NULL,
+    position INTEGER NOT NULL,
+    sets INTEGER NOT NULL,
+    reps INTEGER NOT NULL,
+    lsrpe INTEGER NOT NULL,
+    rest_time INTEGER NOT NULL,
+    note TEXT,
+    FOREIGN KEY (template_id) REFERENCES workout_templates(id) ON DELETE CASCADE,
+    FOREIGN KEY (exercise_id) REFERENCES workout_exercises(id) ON DELETE RESTRICT
+);
+
+CREATE INDEX IF NOT EXISTS idx_workout_session_exercises_session
+    ON workout_session_exercises(session_id);
+
+CREATE INDEX IF NOT EXISTS idx_workout_sessions_date
+    ON workout_sessions(date_time);
 
-CREATE INDEX IF NOT EXISTS idx_sessions_date
-    ON sessions(date_time);
+CREATE INDEX IF NOT EXISTS idx_workout_template_exercises_template
+    ON workout_template_exercises(template_id);
 """
 
 
-- 
cgit v1.2.3