learning-website-framework1-9  Exercise 1: A Progress Model Keyed by Path
==========================================================================

If the sites ever record which chapters a visitor has finished, the record
needs a stable identifier for a page. The best one already exists: the page's
root-relative path (Chapter 3). Build a small progress store with SQLite and an
alias table, so that renaming a chapter does not lose anyone's progress.

Save as progress.py:

import os, sqlite3, sys

SCHEMA = """
CREATE TABLE IF NOT EXISTS completed (
    user_id    TEXT NOT NULL,
    page_path  TEXT NOT NULL,        -- the stable id: root-relative path of the page
    done_at    TEXT NOT NULL DEFAULT (datetime('now')),
    PRIMARY KEY (user_id, page_path)
);
CREATE TABLE IF NOT EXISTS alias (   -- old path -> current path, after a rename or move
    old_path TEXT PRIMARY KEY,
    new_path TEXT NOT NULL
);
"""

def connect(path=":memory:"):
    db = sqlite3.connect(path)
    db.executescript(SCHEMA)
    return db

def resolve(db, page_path):
    row = db.execute("SELECT new_path FROM alias WHERE old_path = ?", (page_path,)).fetchone()
    return row[0] if row else page_path

def mark_done(db, user_id, page_path):
    db.execute("INSERT OR IGNORE INTO completed (user_id, page_path) VALUES (?, ?)",
               (user_id, resolve(db, page_path)))
    db.commit()

def course_progress(db, user_id, course_pages):
    """course_pages: list of the course's chapter paths (current names)."""
    done = {r[0] for r in db.execute("SELECT page_path FROM completed WHERE user_id = ?", (user_id,))}
    finished = [p for p in course_pages if p in done]
    return len(finished), len(course_pages)

def chapters_of(content, course_rel):
    folder = os.path.join(content, course_rel)
    names = [n[:-5] for n in os.listdir(folder)
             if n.endswith(".html") and not n.endswith("_print.html") and not n.startswith("_")]
    key = lambda n: int(n.rsplit("_", 1)[1])
    return sorted(("/" + course_rel + "/" + n + "/" for n in names), key=lambda p: key(p.strip("/")))

if __name__ == "__main__":
    content = sys.argv[1]
    course = "hungary/hungarian-basic-3"
    pages = chapters_of(content, course)
    db = connect()
    print(f"course has {len(pages)} chapters; first: {pages[0].split('/')[-2]}")
    for p in pages[:3]:
        mark_done(db, "user-1", p)
    mark_done(db, "user-1", pages[0])                    # marking twice changes nothing
    done, total = course_progress(db, "user-1", pages)
    print(f"user-1: {done}/{total} = {100*done//total}%")
    print(f"user-2: {course_progress(db, 'user-2', pages)[0]}/{total}")

    # a chapter file is renamed: the alias keeps old links and old progress working
    old = pages[3]
    new = old.replace("hungarian_basic_conversation_3_4", "hungarian_basic_conversation_3_four")
    db.execute("INSERT INTO alias VALUES (?, ?)", (old, new))
    mark_done(db, "user-1", old)                         # recorded under the new name
    stored = [r[0].split('/')[-2] for r in db.execute("SELECT page_path FROM completed WHERE user_id='user-1' ORDER BY page_path")]
    print("stored ids:", stored[-1])

Run it:

  python progress.py "<debserver>/claude-projects/website-content/content"

Output (checked by running it):

course has 12 chapters; first: hungarian_basic_conversation_3_1
user-1: 3/12 = 25%
user-2: 0/12
stored ids: hungarian_basic_conversation_3_four

Design points
-------------
- The primary key is (user, page path), so marking a chapter done twice does
  nothing (INSERT OR IGNORE).
- Progress is computed against the course's CURRENT chapter list, so adding a
  chapter lowers the percentage honestly instead of leaving a stale number.
- The alias table (old path -> new path) is the same idea as a redirect: when
  a chapter is renamed or a folder moves to another site, add one row and old
  progress, old links and bookmarks keep working. It can be generated from the
  redirect map in Learning Website: Framework & Architecture 11.
- Store the minimum: a user id, a path and a time. No names, no browsing
  history beyond what the feature needs.

WHY THIS WORKS AS AN ANSWER
---------------------------
The hard part of progress tracking is not the storage but the identity of a
page over time. Using the path, plus an alias table for renames, solves it with
two small tables and the same mapping the redirects already need.