Add workout logging CLI with SQLite storage

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

6 files changed, 568 insertions, 0 deletions

diff --git a/.gitignore b/.gitignore
new file mode 100644
index 0000000..54c9e2b
--- /dev/null
+++ b/.gitignore
@@ -0,0 +1,3 @@
+egometrics.db
+__pycache__/
+*.pyc
diff --git a/README.md b/README.md
new file mode 100644
index 0000000..4cec9e1
--- /dev/null
+++ b/README.md
@@ -0,0 +1,26 @@
+# EgoMetrics
+
+Personal metrics tracking tool. Currently tracks workout sessions.
+
+## Requirements
+
+- Python 3.10+
+- SQLite (bundled with Python)
+
+No external dependencies.
+
+## Usage
+
+```
+python3 egometrics.py
+```
+
+### Main menu
+
+1. **Log Workout** — pick exercises, enter sets/reps/RPE/rest time/Last Set RPE for each
+2. **View Sessions** — browse past sessions, view details, delete
+3. **Manage Exercises** — add, edit, list, delete exercises
+
+### Data
+
+All data is stored in `egometrics.db` (created on first run). Copy this file to use your data on another machine.
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()
diff --git a/egometrics.py b/egometrics.py
new file mode 100644
index 0000000..eda2faa
--- /dev/null
+++ b/egometrics.py
@@ -0,0 +1,278 @@
+import sqlite3
+
+from db import get_connection, init_db
+import models
+import ui
+
+
+def main_menu(conn: sqlite3.Connection) -> None:
+    while True:
+        print("\n=== EgoMetrics ===\n")
+        print("1. Log Workout")
+        print("2. View Sessions")
+        print("3. Manage Exercises")
+        print("4. Quit")
+        choice = input("\n> ").strip()
+        if choice == "1":
+            log_workout(conn)
+        elif choice == "2":
+            view_sessions(conn)
+        elif choice == "3":
+            manage_exercises(conn)
+        elif choice == "4":
+            break
+
+
+def manage_exercises(conn: sqlite3.Connection) -> None:
+    while True:
+        ui.print_header("Manage Exercises")
+        print("1. List Exercises")
+        print("2. Add Exercise")
+        print("3. Edit Exercise")
+        print("4. Delete Exercise")
+        print("5. Back")
+        choice = input("\n> ").strip()
+        if choice == "1":
+            list_exercises(conn)
+        elif choice == "2":
+            add_exercise(conn)
+        elif choice == "3":
+            edit_exercise(conn)
+        elif choice == "4":
+            delete_exercise(conn)
+        elif choice == "5":
+            break
+
+
+def list_exercises(conn: sqlite3.Connection) -> None:
+    exercises = models.list_exercises(conn)
+    ui.print_header("All Exercises")
+    ui.print_table(
+        ["ID", "Name", "Note"],
+        [[str(e["id"]), e["name"], e["note"] or ""] for e in exercises],
+    )
+
+
+def add_exercise(conn: sqlite3.Connection) -> None:
+    name = ui.prompt_str("Name: ")
+    assert name is not None
+    note = ui.prompt_str("Note (optional): ", required=False)
+    try:
+        models.add_exercise(conn, name, note)
+        print(f'Exercise "{name}" added.')
+    except sqlite3.IntegrityError:
+        print(f'Exercise "{name}" already exists.')
+
+
+def edit_exercise(conn: sqlite3.Connection) -> None:
+    list_exercises(conn)
+    eid = ui.prompt_int("\nExercise ID to edit: ")
+    assert eid is not None
+    ex = models.get_exercise(conn, eid)
+    if not ex:
+        print("Exercise not found.")
+        return
+    print(f'Editing "{ex["name"]}" (leave blank to keep current value)')
+    name = ui.prompt_str(f"Name [{ex['name']}]: ", required=False)
+    note = ui.prompt_str(f"Note [{ex['note'] or ''}]: ", required=False)
+    try:
+        models.update_exercise(conn, eid, name, note)
+        print("Exercise updated.")
+    except sqlite3.IntegrityError:
+        print(f'An exercise named "{name}" already exists.')
+
+
+def delete_exercise(conn: sqlite3.Connection) -> None:
+    list_exercises(conn)
+    eid = ui.prompt_int("\nExercise ID to delete: ")
+    assert eid is not None
+    ex = models.get_exercise(conn, eid)
+    if not ex:
+        print("Exercise not found.")
+        return
+    if not ui.confirm(f'Delete "{ex["name"]}"?'):
+        return
+    try:
+        models.delete_exercise(conn, eid)
+        print("Exercise deleted.")
+    except sqlite3.IntegrityError:
+        print(f'Cannot delete "{ex["name"]}" — it is used in existing sessions.')
+
+
+def log_workout(conn: sqlite3.Connection) -> None:
+    exercises = models.list_exercises(conn)
+    if not exercises:
+        print("No exercises defined. Add some first via Manage Exercises.")
+        return
+
+    # Session metadata
+    ui.print_header("Log Workout")
+    date_time = ui.prompt_datetime("Date/Time")
+    session_note = ui.prompt_str("Session note (optional): ", required=False)
+
+    # Build exercise list — collect everything in memory before saving
+    entries = []
+    while True:
+        print("\nAvailable exercises:")
+        for e in exercises:
+            print(f"  {e['id']}. {e['name']}")
+        print()
+        choice = input("Select exercise ID ('n' = new exercise, 'd' = done): ").strip()
+
+        if choice.lower() == "d":
+            if not entries:
+                print("No exercises added. Aborting.")
+                return
+            break
+
+        # Inline exercise creation
+        elif choice.lower() == "n":
+            name = ui.prompt_str("Exercise name: ")
+            assert name is not None
+            note = ui.prompt_str("Note (optional): ", required=False)
+            try:
+                models.add_exercise(conn, name, note)
+                exercises = models.list_exercises(conn)
+                print(f'Exercise "{name}" added.')
+            except sqlite3.IntegrityError:
+                print(f'Exercise "{name}" already exists.')
+            continue
+
+        # Select existing exercise and prompt for set details
+        try:
+            eid = int(choice)
+        except ValueError:
+            print("Invalid input.")
+            continue
+        ex = models.get_exercise(conn, eid)
+        if not ex:
+            print("Exercise not found.")
+            continue
+
+        print(f"\n  -- {ex['name']} --")
+        sets = ui.prompt_int("  Sets: ", min_val=1)
+        reps = ui.prompt_int("  Reps: ", min_val=1)
+        rpe = ui.prompt_int("  RPE (optional): ", required=False, min_val=1, max_val=10)
+        rest_time = ui.prompt_int(
+            "  Rest time in seconds (optional): ", required=False, min_val=0
+        )
+        lsrpe = ui.prompt_int(
+            "  Last Set RPE (optional): ", required=False, min_val=1, max_val=10
+        )
+        note = ui.prompt_str("  Note (optional): ", required=False)
+
+        entries.append(
+            {
+                "exercise_id": eid,
+                "exercise_name": ex["name"],
+                "sets": sets,
+                "reps": reps,
+                "rpe": rpe,
+                "rest_time": rest_time,
+                "lsrpe": lsrpe,
+                "note": note,
+            }
+        )
+        print(f'  "{ex["name"]}" added to session.')
+
+    # Show summary and confirm before writing to DB
+    ui.print_header("Session Summary")
+    print(f"Date: {date_time}")
+    if session_note:
+        print(f"Note: {session_note}")
+    ui.print_table(
+        ["#", "Exercise", "Sets", "Reps", "RPE", "Rest", "LSRPE", "Note"],
+        [
+            [
+                str(i),
+                e["exercise_name"],
+                str(e["sets"]),
+                str(e["reps"]),
+                str(e["rpe"] or ""),
+                f"{e['rest_time']}s" if e["rest_time"] else "",
+                str(e["lsrpe"] or ""),
+                e["note"] or "",
+            ]
+            for i, e in enumerate(entries, 1)
+        ],
+    )
+
+    if ui.confirm("\nSave this session?"):
+        models.save_session(conn, date_time, session_note, entries)
+        print("Session saved!")
+    else:
+        print("Session discarded.")
+
+
+def view_sessions(conn: sqlite3.Connection) -> None:
+    while True:
+        # List all sessions with exercise name preview
+        sessions = models.list_sessions(conn)
+        if not sessions:
+            print("\nNo sessions recorded yet.")
+            return
+        ui.print_header("Past Sessions")
+        ui.print_table(
+            ["#", "Date", "Exercises", "Note"],
+            [
+                [str(i), s["date_time"], s["exercises"] or "", s["note"] or ""]
+                for i, s in enumerate(sessions, 1)
+            ],
+        )
+
+        # Select a session to view details
+        choice = input("\nSelect # for details ('b' = back): ").strip()
+        if choice.lower() == "b":
+            break
+        try:
+            idx = int(choice) - 1
+            if idx < 0 or idx >= len(sessions):
+                print("Invalid selection.")
+                continue
+        except ValueError:
+            print("Invalid input.")
+            continue
+
+        # Show full session detail with all exercise entries
+        session_id = sessions[idx]["id"]
+        session, entries = models.get_session_detail(conn, session_id)
+        assert session is not None
+        ui.print_header(f"Session: {session['date_time']}")
+        if session["note"]:
+            print(f"Note: {session['note']}\n")
+        ui.print_table(
+            ["#", "Exercise", "Sets", "Reps", "RPE", "Rest", "LSRPE", "Note"],
+            [
+                [
+                    str(e["position"]),
+                    e["exercise_name"],
+                    str(e["sets"]),
+                    str(e["reps"]),
+                    str(e["rpe"] or ""),
+                    f"{e['rest_time']}s" if e["rest_time"] else "",
+                    str(e["lsrpe"] or ""),
+                    e["note"] or "",
+                ]
+                for e in entries
+            ],
+        )
+        action = input("\nActions: (b)ack, (d)elete session\n> ").strip().lower()
+        if action == "d":
+            if ui.confirm("Delete this session?"):
+                models.delete_session(conn, session_id)
+                print("Session deleted.")
+
+
+def main() -> None:
+    init_db()
+    conn = get_connection()
+    try:
+        main_menu(conn)
+    except KeyboardInterrupt:
+        print("\nGoodbye!")
+    finally:
+        conn.close()
+
+
+if __name__ == "__main__":
+    main()
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
diff --git a/ui.py b/ui.py
new file mode 100644
index 0000000..2d63c06
--- /dev/null
+++ b/ui.py
@@ -0,0 +1,77 @@
+from datetime import datetime
+
+
+def print_header(title: str) -> None:
+    print(f"\n--- {title} ---\n")
+
+
+def print_table(headers: list[str], rows: list[list[str]]) -> None:
+    if not rows:
+        print("  (empty)")
+        return
+    all_rows = [headers] + [[str(c) for c in row] for row in rows]
+    widths = [max(len(r[i]) for r in all_rows) for i in range(len(headers))]
+    fmt = " | ".join(f"{{:<{w}}}" for w in widths)
+    print(fmt.format(*headers))
+    print("-+-".join("-" * w for w in widths))
+    for row in all_rows[1:]:
+        print(fmt.format(*row))
+
+
+def prompt_int(
+    prompt: str,
+    required: bool = True,
+    min_val: int | None = None,
+    max_val: int | None = None,
+) -> int | None:
+    while True:
+        val = input(prompt).strip()
+        if not val:
+            if not required:
+                return None
+            print("This field is required.")
+            continue
+        try:
+            n = int(val)
+        except ValueError:
+            print("Please enter a valid integer.")
+            continue
+        if min_val is not None and n < min_val:
+            print(f"Must be at least {min_val}.")
+            continue
+        if max_val is not None and n > max_val:
+            print(f"Must be at most {max_val}.")
+            continue
+        return n
+
+
+def prompt_str(prompt: str, required: bool = True) -> str | None:
+    while True:
+        val = input(prompt).strip()
+        if not val:
+            if not required:
+                return None
+            print("This field is required.")
+            continue
+        return val
+
+
+def prompt_datetime(prompt: str, default_now: bool = True) -> str:
+    while True:
+        default = datetime.now().strftime("%Y-%m-%d %H")
+        val = input(f"{prompt} [{default}]: ").strip()
+        if not val:
+            if default_now:
+                return default
+        else:
+            try:
+                datetime.strptime(val, "%Y-%m-%d %H")
+                return val
+            except ValueError:
+                print("Invalid format. Use YYYY-MM-DD HH.")
+                continue
+
+
+def confirm(prompt: str) -> bool:
+    val = input(f"{prompt} [y/N]: ").strip().lower()
+    return val == "y"