j+' SrSSKrSSKrSSKJrJr \R R S5=(di \RR\RR\RR\RR\ 55SSS55r SrS rSS jrS rS rSS jrSrSrSrSSjrSSjrSSjrSrg)zPSQLite storage: users, per-activity progress, attempt history, badges, settings.N)datetimedateDB_PATHz.._Datazfrench_grades.dbc[R"[SS9n[RUlUR S5 U$)N )timeoutzPRAGMA journal_mode = WAL)sqlite3connectrRow row_factoryexecute)conns db.pyget_dbr s0 ??7B /D{{DLL,- Kc~[R"[RR[5SS9 [ 5nUR S5 SURS5R5Vs/sHoSPM sn;aURS5 UR5 UR5 gs snf)NT)exist_oka, CREATE TABLE IF NOT EXISTS users ( id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT UNIQUE NOT NULL, role TEXT NOT NULL CHECK(role IN ('kid','parent')), password_hash TEXT NOT NULL, display_name TEXT NOT NULL, created_at TEXT DEFAULT (datetime('now','localtime')) ); CREATE TABLE IF NOT EXISTS progress ( user_id INTEGER NOT NULL, activity_id TEXT NOT NULL, lesson_id TEXT NOT NULL, best_score INTEGER DEFAULT 0, best_total INTEGER DEFAULT 0, best_pct INTEGER DEFAULT 0, stars INTEGER DEFAULT 0, points INTEGER DEFAULT 0, attempts INTEGER DEFAULT 0, completed INTEGER DEFAULT 0, first_done TEXT, last_done TEXT, PRIMARY KEY (user_id, activity_id) ); CREATE TABLE IF NOT EXISTS attempts ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER NOT NULL, activity_id TEXT NOT NULL, lesson_id TEXT NOT NULL, score INTEGER, total INTEGER, pct INTEGER, stars INTEGER, points_earned INTEGER DEFAULT 0, seconds INTEGER DEFAULT 0, created_at TEXT DEFAULT (datetime('now','localtime')) ); CREATE INDEX IF NOT EXISTS idx_attempts_user ON attempts(user_id, created_at); CREATE TABLE IF NOT EXISTS badges ( user_id INTEGER NOT NULL, badge_id TEXT NOT NULL, earned_at TEXT DEFAULT (datetime('now','localtime')), PRIMARY KEY (user_id, badge_id) ); CREATE TABLE IF NOT EXISTS days ( user_id INTEGER NOT NULL, day TEXT NOT NULL, points INTEGER DEFAULT 0, activities INTEGER DEFAULT 0, PRIMARY KEY (user_id, day) ); CREATE TABLE IF NOT EXISTS settings ( key TEXT PRIMARY KEY, value TEXT ); partialzPRAGMA table_info(attempts)namez9ALTER TABLE attempts ADD COLUMN partial INTEGER DEFAULT 0) osmakedirspathdirnamerr executescriptrfetchallcommitclose)crs rinit_dbr!sKK(48AOO44 jAII6S,T,],],_`,_q6,_`` MNHHJGGIas4B:c[5nURSU45R5nUR5 U(aUS$U$)Nz&SELECT value FROM settings WHERE key=?value)rrfetchoner)keydefaultrrows r get_settingr(Ws?A ))OVWI  S9A3u<01uW_eeijvews,-s5w-/H#jJfhqsvxN O [5uZcehjmn pII_Yu5RUVYZaZfefVgimRnu|pqCDEF JJL " " $EIIsu|DFLuMNHHJGGI fsY]^gYh iirc8[5nURSU45R5n[URSU45R55nUR 5 0nUH,n[U5nUR US5US'XdUS'M. U$)z\Best result per activity, plus the fastest perfect run (personal best for timed challenges).z&SELECT * FROM progress WHERE user_id=?zSELECT activity_id, MIN(seconds) FROM attempts WHERE user_id=? AND pct=100 AND seconds>0 GROUP BY activity_idrN best_secs)rrrrJrget)rLrrowsbestoutr ds r progress_mapr_sA 99=z J S S UD  57>jBBJ(* NDGGI C  G!M"23+ !Am  Jrc :[5nURSU45R5SnURSU45R5nURSU45R5Vs/sHoDSPM nnURSU45R5Vs/sHoDSPM nnURSU45R5S nUR 5 S n[ R "5n [U5n U R5U ;a"U RU R5S - 5n U R5U ;a=US - nU RU R5S - 5n U R5U ;aM=[[U5US =(d S US =(d S US=(d S US=(d S [U5X[U5[ R "5R5U ;S9 $s snfs snf)NzISELECT COALESCE(SUM(points_earned),0) AS xp FROM attempts WHERE user_id=?xpaSELECT COUNT(*) AS done, COALESCE(SUM(stars),0) AS stars, COALESCE(SUM(points),0) AS points, SUM(CASE WHEN best_pct=100 THEN 1 ELSE 0 END) AS perfect, COALESCE(SUM(attempts),0) AS attempts FROM progress WHERE user_id=? AND completed=1zGSELECT day FROM days WHERE user_id=? AND activities>0 ORDER BY day DESCdayz>SELECT badge_id FROM badges WHERE user_id=? ORDER BY earned_atbadge_idzBSELECT COALESCE(SUM(seconds),0) AS s FROM attempts WHERE user_id=?srr2doner?perfectattempts) rarer?rfrgdaysstreakbadgesrRrH)rrr$rrrrHsetrI fromordinal toordinalrJrClen) rLrrar'r rhrjsecsrir^daysets rstatsrqsA ^ah`j k t t vw{ |B ))HJQ UU]U]U_ ii(qt{s}~HHJ KJeHJD K%&YY/oryq{%|&F&F&HI&H m&HFI 99Y\c[e f o o qru vDGGI F A YF{{}F" MM!++-!+ , ++-6 !!  MM!++-!+ , ++-6 ! 3r7V!1W9JTWXaTbTgfgruwAsBsGFGYvc$iW[WaWaWcWmWmWosyWy {{ KIs 3H&Hc[5nURSX45nUR5 URS:nUR 5 U$)Nz=INSERT OR IGNORE INTO badges (user_id, badge_id) VALUES (?,?)r)rrrrowcountr)rLrcrcurnews r award_badgervsCA ))SV]Uh iCHHJ ,, CGGI Jrc[5nURSX45R5nUR5 UVs/sHn[ U5PM sn$s snf)Nz?SELECT * FROM attempts WHERE user_id=? ORDER BY id DESC LIMIT ?)rrrrrJ)rLlimitrr[r s rhistoryrysLA 99VY`Xh i r r tDGGI! "TDGT "" "sAc[5nURSX45R5nUR5 [ U5Vs/sHn[ U5PM sn$s snf)NzRSELECT day, points, activities FROM days WHERE user_id=? ORDER BY day DESC LIMIT ?)rrrrreversedrJ)rLnrr[r s r day_seriesr}sYA 99ilskw x B B DDGGI%d^ ,^DG^ ,, ,s A!cP[5nU(a'URSX45 URSX45 ONU(a'URSX45 URSX45 O SHnURSUS3U45 M UR5 UR5 g)Nz6DELETE FROM progress WHERE user_id=? AND activity_id=?z6DELETE FROM attempts WHERE user_id=? AND activity_id=?z4DELETE FROM progress WHERE user_id=? AND lesson_id=?z4DELETE FROM attempts WHERE user_id=? AND lesson_id=?)progressrgrjrhz DELETE FROM z WHERE user_id=?)rrrr)rLrMrNrts rresetrsA JWLbc JWLbc  H7J^_ H7J^_;A II QC'787* E<HHJGGIrc SSKnUR(dUR*S-OUR*S-nU(a[ S5(a[ S[ U55 g[5nUSS3nSS/4S S /4S S S /4SS/44H.upEUH#nURSUSUSUSUS3 U45 M% M0 UR5 UR5 [ S[ U55 g)zIRows written before TZ was set are in UTC: shift them once to local time.rNi tz_shiftedz+dz hoursrg created_atrj earned_atr first_done last_doneuserszUPDATE z SET z =datetime(z , ?) WHERE z IS NOT NULL) timedaylighttimezonealtzoner(r+r*rrrr)roffrmodtablecolscols rmigrate_timezoners(, 4==.D DLL=D;PC +l++L#c(+A HF C#l^4x+6OR\_kmx^yQz~EHTGU}VW C IIweC5 3%{3%|\_b^d eWHHJGGI c#h'r)N)rF)r/))NN)__doc__rr rrenvironrZrnormpathjoinrabspath__file__rrr!r(r+r5rWr_rqrvryr}rrr3rrrsV # **.. # Urww'7'7 RWW__UWU\U\UdUdemUnEoquw~AS9T(U;H,IjB {,#- (r