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, 52 insertions, 0 deletions

diff --git a/db.py b/db.py
new file mode 100644
index 0000000..fa407d8
--- /dev/null
+++ b/db.py
@@ -0,0 +1,52 @@
+import sqlite3
+import os
+
+DB_PATH = os.path.join(os.path.dirname(os.path.abspath(__file__)), "egometrics.db")
+
+SCHEMA = """
+CREATE TABLE IF NOT EXISTS exercises (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    name TEXT NOT NULL UNIQUE COLLATE NOCASE,
+    note TEXT
+);
+
+CREATE TABLE IF NOT EXISTS sessions (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    date_time TEXT NOT NULL,
+    note TEXT
+);
+
+CREATE TABLE IF NOT EXISTS 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,
+    note TEXT,
+    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE,
+    FOREIGN KEY (exercise_id) REFERENCES exercises(id) ON DELETE RESTRICT
+);
+
+CREATE INDEX IF NOT EXISTS idx_session_exercises_session
+    ON session_exercises(session_id);
+
+CREATE INDEX IF NOT EXISTS idx_sessions_date
+    ON sessions(date_time);
+"""
+
+
+def get_connection() -> sqlite3.Connection:
+    conn = sqlite3.connect(DB_PATH)
+    conn.row_factory = sqlite3.Row
+    conn.execute("PRAGMA foreign_keys = ON")
+    return conn
+
+
+def init_db() -> None:
+    conn = get_connection()
+    conn.executescript(SCHEMA)
+    conn.close()