""" utils/db.py — All Supabase + SQLite database operations for FitPlan Pro. """ import os, json, time, uuid, sqlite3 import requests SUPABASE_URL = os.getenv("SUPABASE_URL", "").strip() SUPABASE_KEY = os.getenv("SUPABASE_KEY", "").strip() DB_PATH = "/tmp/fitplan_plans.db" USE_SUPABASE = bool(SUPABASE_URL and SUPABASE_KEY) # ══════════════════════════════════════════════════════════════════════════════ # SQLite setup # ══════════════════════════════════════════════════════════════════════════════ def _init_sqlite(): conn = sqlite3.connect(DB_PATH) c = conn.cursor() c.execute(""" CREATE TABLE IF NOT EXISTS plans ( plan_id TEXT PRIMARY KEY, username TEXT NOT NULL, created_at REAL, dietary_type TEXT, cuisine_type TEXT DEFAULT '', total_days INTEGER, is_active INTEGER DEFAULT 1 ) """) for _col in ["ALTER TABLE plans ADD COLUMN cuisine_type TEXT DEFAULT ''"]: try: c.execute(_col) except Exception: pass c.execute(""" CREATE TABLE IF NOT EXISTS plan_days ( id TEXT PRIMARY KEY, plan_id TEXT, day_number INTEGER, muscle_group TEXT DEFAULT 'Full Body', is_rest_day INTEGER DEFAULT 0, workout_json TEXT, dietary_json TEXT, pre_stretch_json TEXT, post_stretch_json TEXT ) """) for _col in [ "ALTER TABLE plan_days ADD COLUMN muscle_group TEXT DEFAULT 'Full Body'", "ALTER TABLE plan_days ADD COLUMN is_rest_day INTEGER DEFAULT 0", ]: try: c.execute(_col) except Exception: pass c.execute(""" CREATE TABLE IF NOT EXISTS daily_progress ( id TEXT PRIMARY KEY, username TEXT, plan_id TEXT, day_number INTEGER, date TEXT, workout_checks TEXT, dietary_checks TEXT, day_completed INTEGER DEFAULT 0, completed_at REAL ) """) c.execute(""" CREATE TABLE IF NOT EXISTS streaks ( username TEXT PRIMARY KEY, current_streak INTEGER DEFAULT 0, longest_streak INTEGER DEFAULT 0, last_completed_date TEXT, streak_history TEXT DEFAULT '[]', opted_in INTEGER DEFAULT 0, display_name TEXT DEFAULT '', goal TEXT DEFAULT '' ) """) for _col in [ "ALTER TABLE streaks ADD COLUMN opted_in INTEGER DEFAULT 0", "ALTER TABLE streaks ADD COLUMN display_name TEXT DEFAULT ''", "ALTER TABLE streaks ADD COLUMN goal TEXT DEFAULT ''", ]: try: c.execute(_col) except Exception: pass for _idx in [ "CREATE INDEX IF NOT EXISTS idx_plans_username ON plans(username)", "CREATE INDEX IF NOT EXISTS idx_plans_active ON plans(username,is_active)", "CREATE INDEX IF NOT EXISTS idx_plan_days_plan_id ON plan_days(plan_id)", "CREATE INDEX IF NOT EXISTS idx_daily_progress_user_plan ON daily_progress(username,plan_id)", "CREATE INDEX IF NOT EXISTS idx_streaks_username ON streaks(username)", ]: try: c.execute(_idx) except Exception: pass conn.commit() conn.close() _init_sqlite() # ══════════════════════════════════════════════════════════════════════════════ # Helpers # ══════════════════════════════════════════════════════════════════════════════ def _headers(): return { "apikey": SUPABASE_KEY, "Authorization": f"Bearer {SUPABASE_KEY}", "Content-Type": "application/json", "Prefer": "return=representation" } def _sb_get(table, filters=""): r = requests.get( f"{SUPABASE_URL}/rest/v1/{table}?{filters}", headers=_headers(), timeout=10 ) return r.json() if r.ok else [] def _sb_post(table, data, upsert=False): h = _headers() if upsert: h["Prefer"] = "resolution=merge-duplicates,return=representation" data = {k: (1 if v is True else (0 if v is False else v)) for k, v in data.items()} r = requests.post( f"{SUPABASE_URL}/rest/v1/{table}", headers=h, json=data, timeout=10 ) return r.ok def _sb_patch(table, filters, data): data = {k: (1 if v is True else (0 if v is False else v)) for k, v in data.items()} r = requests.patch( f"{SUPABASE_URL}/rest/v1/{table}?{filters}", headers=_headers(), json=data, timeout=10 ) return r.ok def _sb_delete(table, filters): r = requests.delete( f"{SUPABASE_URL}/rest/v1/{table}?{filters}", headers=_headers(), timeout=10 ) return r.ok # ══════════════════════════════════════════════════════════════════════════════ # PLAN OPERATIONS # ══════════════════════════════════════════════════════════════════════════════ def save_plan(username, dietary_type, total_days, days_data, cuisine_type=""): plan_id = str(uuid.uuid4()) if USE_SUPABASE: _sb_patch("plans", f"username=eq.{username}&is_active=eq.1", {"is_active": 0}) ok = _sb_post("plans", { "plan_id": plan_id, "username": username, "created_at": time.time(), "dietary_type": dietary_type, "cuisine_type": cuisine_type, "total_days": total_days, "is_active": 1 }) if not ok: raise RuntimeError("Failed to save plan header to Supabase") for day_num, day in enumerate(days_data, 1): _sb_post("plan_days", { "id": str(uuid.uuid4()), "plan_id": plan_id, "day_number": day_num, "muscle_group": day.get("muscle_group", "Full Body"), "is_rest_day": int(day.get("is_rest_day", False)), "workout_json": json.dumps(day.get("workout", [])), "dietary_json": json.dumps(day.get("dietary", {})), "pre_stretch_json": json.dumps(day.get("pre_stretch", [])), "post_stretch_json": json.dumps(day.get("post_stretch", [])) }) else: conn = sqlite3.connect(DB_PATH) c = conn.cursor() c.execute("UPDATE plans SET is_active=0 WHERE username=?", (username,)) c.execute("INSERT INTO plans VALUES (?,?,?,?,?,?,?)", (plan_id, username, time.time(), dietary_type, cuisine_type, total_days, 1)) for day_num, day in enumerate(days_data, 1): c.execute("INSERT INTO plan_days VALUES (?,?,?,?,?,?,?,?,?)", ( str(uuid.uuid4()), plan_id, day_num, day.get("muscle_group", "Full Body"), int(day.get("is_rest_day", False)), json.dumps(day.get("workout", [])), json.dumps(day.get("dietary", {})), json.dumps(day.get("pre_stretch", [])), json.dumps(day.get("post_stretch", [])) )) conn.commit() conn.close() return plan_id def get_active_plan(username): if USE_SUPABASE: plans = _sb_get("plans", f"username=eq.{username}&is_active=eq.1&order=created_at.desc&limit=1") if not plans: return None plan = plans[0] plan["days"] = _sb_get("plan_days", f"plan_id=eq.{plan['plan_id']}&order=day_number.asc") else: conn = sqlite3.connect(DB_PATH) conn.row_factory = sqlite3.Row c = conn.cursor() c.execute("SELECT * FROM plans WHERE username=? AND is_active=1 ORDER BY created_at DESC LIMIT 1", (username,)) row = c.fetchone() if not row: conn.close() return None plan = dict(row) c.execute("SELECT * FROM plan_days WHERE plan_id=? ORDER BY day_number ASC", (plan["plan_id"],)) plan["days"] = [dict(r) for r in c.fetchall()] conn.close() import datetime try: ts = plan.get("created_at", 0) plan["created_at_date"] = ( datetime.datetime.fromtimestamp(ts).date().isoformat() if ts else datetime.date.today().isoformat() ) except Exception: plan["created_at_date"] = datetime.date.today().isoformat() for day in plan.get("days", []): for field in ["workout_json", "dietary_json", "pre_stretch_json", "post_stretch_json"]: if isinstance(day.get(field), str): try: day[field] = json.loads(day[field]) except Exception: day[field] = [] if field != "dietary_json" else {} return plan def delete_active_plan(username): if USE_SUPABASE: _sb_patch("plans", f"username=eq.{username}&is_active=eq.1", {"is_active": 0}) else: conn = sqlite3.connect(DB_PATH) conn.execute("UPDATE plans SET is_active=0 WHERE username=?", (username,)) conn.commit() conn.close() # ══════════════════════════════════════════════════════════════════════════════ # PROGRESS OPERATIONS # ══════════════════════════════════════════════════════════════════════════════ def save_progress(username, plan_id, day_number, workout_checks, dietary_checks): from datetime import date as _date today = str(_date.today()) all_workout = all(workout_checks.values()) if workout_checks else False all_dietary = all(dietary_checks.values()) if dietary_checks else False day_completed = all_workout and all_dietary row_id = f"{username}_{plan_id}_{day_number}" payload = { "id": row_id, "username": username, "plan_id": plan_id, "day_number": day_number, "date": today, "workout_checks": json.dumps(workout_checks), "dietary_checks": json.dumps(dietary_checks), "day_completed": int(day_completed), "completed_at": time.time() if day_completed else None } if USE_SUPABASE: _sb_post("daily_progress", payload, upsert=True) else: conn = sqlite3.connect(DB_PATH) conn.execute("INSERT OR REPLACE INTO daily_progress VALUES (?,?,?,?,?,?,?,?,?)", ( row_id, username, plan_id, day_number, today, json.dumps(workout_checks), json.dumps(dietary_checks), int(day_completed), time.time() if day_completed else None )) conn.commit() conn.close() return day_completed def get_progress(username, plan_id, day_number): if USE_SUPABASE: rows = _sb_get("daily_progress", f"username=eq.{username}&plan_id=eq.{plan_id}&day_number=eq.{day_number}&limit=1") row = rows[0] if rows else None else: conn = sqlite3.connect(DB_PATH) conn.row_factory = sqlite3.Row c = conn.cursor() c.execute("SELECT * FROM daily_progress WHERE username=? AND plan_id=? AND day_number=?", (username, plan_id, day_number)) r = c.fetchone() row = dict(r) if r else None conn.close() if not row: return {"workout_checks": {}, "dietary_checks": {}, "day_completed": False} return { "workout_checks": json.loads(row.get("workout_checks") or "{}"), "dietary_checks": json.loads(row.get("dietary_checks") or "{}"), "day_completed": bool(row.get("day_completed", False)) } def get_all_progress(username, plan_id): if USE_SUPABASE: rows = _sb_get("daily_progress", f"username=eq.{username}&plan_id=eq.{plan_id}&order=day_number.asc") else: conn = sqlite3.connect(DB_PATH) conn.row_factory = sqlite3.Row c = conn.cursor() c.execute("SELECT * FROM daily_progress WHERE username=? AND plan_id=? ORDER BY day_number ASC", (username, plan_id)) rows = [dict(r) for r in c.fetchall()] conn.close() return [{ "day_number": r["day_number"], "workout_checks": json.loads(r.get("workout_checks") or "{}"), "dietary_checks": json.loads(r.get("dietary_checks") or "{}"), "day_completed": bool(r.get("day_completed", False)), "date": r.get("date", "") } for r in rows] # ══════════════════════════════════════════════════════════════════════════════ # STREAK OPERATIONS # ══════════════════════════════════════════════════════════════════════════════ def get_streak(username): if USE_SUPABASE: rows = _sb_get("streaks", f"username=eq.{username}&limit=1") row = rows[0] if rows else None else: conn = sqlite3.connect(DB_PATH) conn.row_factory = sqlite3.Row c = conn.cursor() c.execute("SELECT * FROM streaks WHERE username=?", (username,)) r = c.fetchone() row = dict(r) if r else None conn.close() if not row: return {"username": username, "current_streak": 0, "longest_streak": 0, "last_completed_date": None, "streak_history": []} return { "username": row["username"], "current_streak": row.get("current_streak", 0), "longest_streak": row.get("longest_streak", 0), "last_completed_date": row.get("last_completed_date"), "streak_history": json.loads(row.get("streak_history") or "[]") } def save_streak(streak_data): payload = { "username": streak_data["username"], "current_streak": streak_data.get("current_streak", 0), "longest_streak": streak_data.get("longest_streak", 0), "last_completed_date": streak_data.get("last_completed_date"), "streak_history": json.dumps(streak_data.get("streak_history", [])), "opted_in": int(streak_data.get("opted_in", False)), "display_name": streak_data.get("display_name", ""), "goal": streak_data.get("goal", ""), } if USE_SUPABASE: _sb_post("streaks", payload, upsert=True) else: conn = sqlite3.connect(DB_PATH) conn.execute(""" INSERT OR REPLACE INTO streaks (username,current_streak,longest_streak,last_completed_date, streak_history,opted_in,display_name,goal) VALUES (?,?,?,?,?,?,?,?) """, ( payload["username"], payload["current_streak"], payload["longest_streak"], payload["last_completed_date"], payload["streak_history"], payload["opted_in"], payload["display_name"], payload["goal"] )) conn.commit() conn.close() # ══════════════════════════════════════════════════════════════════════════════ # USER PROFILE OPERATIONS # ══════════════════════════════════════════════════════════════════════════════ def _init_profile_table(): conn = sqlite3.connect(DB_PATH) conn.execute(""" CREATE TABLE IF NOT EXISTS user_profiles ( username TEXT PRIMARY KEY, name TEXT, age INTEGER, gender TEXT, height REAL, weight REAL, fitness_level TEXT, goal TEXT, days_per_week INTEGER, months INTEGER, total_days INTEGER, equipment TEXT DEFAULT '[]', home_eq TEXT DEFAULT '[]', gym_eq TEXT DEFAULT '[]', no_eq INTEGER DEFAULT 0, injuries TEXT DEFAULT '[]', diet_type TEXT DEFAULT 'nonveg', cuisine_preference TEXT DEFAULT '', cuisine_label TEXT DEFAULT '', cuisine_icon TEXT DEFAULT '', cuisine_notes TEXT DEFAULT '', updated_at REAL ) """) for _col in [ "ALTER TABLE user_profiles ADD COLUMN injuries TEXT DEFAULT '[]'", "ALTER TABLE user_profiles ADD COLUMN diet_type TEXT DEFAULT 'nonveg'", "ALTER TABLE user_profiles ADD COLUMN cuisine_preference TEXT DEFAULT ''", "ALTER TABLE user_profiles ADD COLUMN cuisine_label TEXT DEFAULT ''", "ALTER TABLE user_profiles ADD COLUMN cuisine_icon TEXT DEFAULT ''", "ALTER TABLE user_profiles ADD COLUMN cuisine_notes TEXT DEFAULT ''", ]: try: conn.execute(_col) except Exception: pass conn.commit() conn.close() _init_profile_table() def save_user_profile(username, profile_data): payload = { "username": username, "name": profile_data.get("name", ""), "age": profile_data.get("age", 25), "gender": profile_data.get("gender", "Male"), "height": profile_data.get("height", 170), "weight": profile_data.get("weight", 70), "fitness_level": profile_data.get("level", "Beginner"), "goal": profile_data.get("goal", "General Fitness"), "days_per_week": profile_data.get("days_per_week", 5), "months": profile_data.get("months", 1), "total_days": profile_data.get("total_days", 20), "equipment": json.dumps(profile_data.get("equipment", [])), "home_eq": json.dumps(profile_data.get("home_eq", [])), "gym_eq": json.dumps(profile_data.get("gym_eq", [])), "no_eq": int(profile_data.get("no_eq", False)), "injuries": json.dumps(profile_data.get("injuries", [])), "diet_type": profile_data.get("diet_type", "nonveg"), "cuisine_preference": profile_data.get("cuisine_preference", ""), "cuisine_label": profile_data.get("cuisine_label", ""), "cuisine_icon": profile_data.get("cuisine_icon", ""), "cuisine_notes": profile_data.get("cuisine_notes", ""), "updated_at": time.time(), } if USE_SUPABASE: _sb_post("user_profiles", payload, upsert=True) else: conn = sqlite3.connect(DB_PATH) conn.execute(""" INSERT OR REPLACE INTO user_profiles (username,name,age,gender,height,weight,fitness_level,goal, days_per_week,months,total_days,equipment,home_eq,gym_eq,no_eq, injuries,diet_type,cuisine_preference,cuisine_label,cuisine_icon, cuisine_notes,updated_at) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) """, ( payload["username"], payload["name"], payload["age"], payload["gender"], payload["height"], payload["weight"], payload["fitness_level"], payload["goal"], payload["days_per_week"], payload["months"], payload["total_days"], payload["equipment"], payload["home_eq"], payload["gym_eq"], payload["no_eq"], payload["injuries"], payload["diet_type"], payload["cuisine_preference"], payload["cuisine_label"], payload["cuisine_icon"], payload["cuisine_notes"], payload["updated_at"] )) conn.commit() conn.close() def get_user_profile(username): if USE_SUPABASE: import urllib.request, urllib.parse headers = { "apikey": SUPABASE_KEY, "Authorization": f"Bearer {SUPABASE_KEY}", "Content-Type": "application/json", } url = (f"{SUPABASE_URL}/rest/v1/user_profiles" f"?username=eq.{urllib.parse.quote(username)}&select=*&limit=1") req = urllib.request.Request(url, headers=headers) try: with urllib.request.urlopen(req, timeout=8) as r: rows = json.loads(r.read().decode()) row = rows[0] if rows else None except Exception: row = None else: conn = sqlite3.connect(DB_PATH) conn.row_factory = sqlite3.Row c = conn.cursor() c.execute("SELECT * FROM user_profiles WHERE username=?", (username,)) r = c.fetchone() row = dict(r) if r else None conn.close() if not row: return None return { "name": row.get("name", ""), "age": row.get("age", 25), "gender": row.get("gender", "Male"), "height": row.get("height", 170), "weight": row.get("weight", 70), "level": row.get("fitness_level", "Beginner"), "goal": row.get("goal", "General Fitness"), "days_per_week": row.get("days_per_week", 5), "months": row.get("months", 1), "total_days": row.get("total_days", 20), "equipment": json.loads(row.get("equipment") or "[]"), "home_eq": json.loads(row.get("home_eq") or "[]"), "gym_eq": json.loads(row.get("gym_eq") or "[]"), "no_eq": bool(row.get("no_eq", False)), "injuries": json.loads(row.get("injuries") or "[]"), "diet_type": row.get("diet_type", "nonveg"), "cuisine_preference": row.get("cuisine_preference", ""), "cuisine_label": row.get("cuisine_label", ""), "cuisine_icon": row.get("cuisine_icon", ""), "cuisine_notes": row.get("cuisine_notes", ""), } # ══════════════════════════════════════════════════════════════════════════════ # WEIGHT LOG # ══════════════════════════════════════════════════════════════════════════════ def _init_weight_log(): conn = sqlite3.connect(DB_PATH) conn.execute(""" CREATE TABLE IF NOT EXISTS weight_log ( id TEXT PRIMARY KEY, username TEXT NOT NULL, date TEXT NOT NULL, weight_kg REAL NOT NULL ) """) conn.commit(); conn.close() _init_weight_log() def save_weight_log(username, date_str, weight_kg): row_id = f"{username}_{date_str}" if USE_SUPABASE: _sb_post("weight_log", {"id": row_id, "username": username, "date": date_str, "weight_kg": weight_kg}, upsert=True) else: conn = sqlite3.connect(DB_PATH) conn.execute("INSERT OR REPLACE INTO weight_log VALUES (?,?,?,?)", (row_id, username, date_str, weight_kg)) conn.commit(); conn.close() def get_weight_log(username, limit=60): if USE_SUPABASE: rows = _sb_get("weight_log", f"username=eq.{username}&order=date.asc&limit={limit}") else: conn = sqlite3.connect(DB_PATH) conn.row_factory = sqlite3.Row rows = [dict(r) for r in conn.execute( "SELECT * FROM weight_log WHERE username=? ORDER BY date ASC LIMIT ?", (username, limit)).fetchall()] conn.close() return [{"date": r["date"], "weight_kg": r["weight_kg"]} for r in rows] # ══════════════════════════════════════════════════════════════════════════════ # WORKOUT NOTES # ══════════════════════════════════════════════════════════════════════════════ def _init_workout_notes(): conn = sqlite3.connect(DB_PATH) conn.execute(""" CREATE TABLE IF NOT EXISTS workout_notes ( id TEXT PRIMARY KEY, username TEXT NOT NULL, plan_id TEXT NOT NULL, day_number INTEGER NOT NULL, exercise_idx INTEGER NOT NULL, note TEXT DEFAULT '' ) """) conn.commit(); conn.close() _init_workout_notes() def save_workout_note(username, plan_id, day_number, exercise_idx, note): row_id = f"{username}_{plan_id}_{day_number}_{exercise_idx}" if USE_SUPABASE: _sb_post("workout_notes", {"id": row_id, "username": username, "plan_id": plan_id, "day_number": day_number, "exercise_idx": exercise_idx, "note": note}, upsert=True) else: conn = sqlite3.connect(DB_PATH) conn.execute("INSERT OR REPLACE INTO workout_notes VALUES (?,?,?,?,?,?)", (row_id, username, plan_id, day_number, exercise_idx, note)) conn.commit(); conn.close() def get_workout_notes(username, plan_id, day_number): if USE_SUPABASE: rows = _sb_get("workout_notes", f"username=eq.{username}&plan_id=eq.{plan_id}&day_number=eq.{day_number}") else: conn = sqlite3.connect(DB_PATH) conn.row_factory = sqlite3.Row rows = [dict(r) for r in conn.execute( "SELECT * FROM workout_notes WHERE username=? AND plan_id=? AND day_number=?", (username, plan_id, day_number)).fetchall()] conn.close() return {r["exercise_idx"]: r["note"] for r in rows if r.get("note")} # ══════════════════════════════════════════════════════════════════════════════ # WATER TRACKER # ══════════════════════════════════════════════════════════════════════════════ def _init_water_tracker(): conn = sqlite3.connect(DB_PATH) conn.execute(""" CREATE TABLE IF NOT EXISTS water_tracker ( id TEXT PRIMARY KEY, username TEXT NOT NULL, date TEXT NOT NULL, glasses INTEGER DEFAULT 0 ) """) conn.commit(); conn.close() _init_water_tracker() def save_water(username, date_str, glasses): row_id = f"{username}_{date_str}" if USE_SUPABASE: _sb_post("water_tracker", {"id": row_id, "username": username, "date": date_str, "glasses": glasses}, upsert=True) else: conn = sqlite3.connect(DB_PATH) conn.execute("INSERT OR REPLACE INTO water_tracker VALUES (?,?,?,?)", (row_id, username, date_str, glasses)) conn.commit(); conn.close() def get_water(username, date_str): if USE_SUPABASE: rows = _sb_get("water_tracker", f"username=eq.{username}&date=eq.{date_str}&limit=1") return rows[0]["glasses"] if rows else 0 else: conn = sqlite3.connect(DB_PATH) row = conn.execute("SELECT glasses FROM water_tracker WHERE username=? AND date=?", (username, date_str)).fetchone() conn.close() return row[0] if row else 0 # ══════════════════════════════════════════════════════════════════════════════ # PERSONAL RECORDS # ══════════════════════════════════════════════════════════════════════════════ def _init_personal_records(): conn = sqlite3.connect(DB_PATH) conn.execute(""" CREATE TABLE IF NOT EXISTS personal_records ( id TEXT PRIMARY KEY, username TEXT NOT NULL, exercise TEXT NOT NULL, value REAL NOT NULL, unit TEXT DEFAULT 'reps', date TEXT NOT NULL, note TEXT DEFAULT '' ) """) conn.commit(); conn.close() _init_personal_records() def save_personal_record(username, exercise, value, unit, date_str, note=""): row_id = str(uuid.uuid4()) payload = {"id": row_id, "username": username, "exercise": exercise, "value": value, "unit": unit, "date": date_str, "note": note} if USE_SUPABASE: _sb_post("personal_records", payload) else: conn = sqlite3.connect(DB_PATH) conn.execute("INSERT INTO personal_records VALUES (?,?,?,?,?,?,?)", (row_id, username, exercise, value, unit, date_str, note)) conn.commit(); conn.close() def get_personal_records(username): if USE_SUPABASE: rows = _sb_get("personal_records", f"username=eq.{username}&order=date.asc") else: conn = sqlite3.connect(DB_PATH) conn.row_factory = sqlite3.Row rows = [dict(r) for r in conn.execute( "SELECT * FROM personal_records WHERE username=? ORDER BY date ASC", (username,)).fetchall()] conn.close() return rows def delete_personal_record(record_id): if USE_SUPABASE: _sb_delete("personal_records", f"id=eq.{record_id}") else: conn = sqlite3.connect(DB_PATH) conn.execute("DELETE FROM personal_records WHERE id=?", (record_id,)) conn.commit(); conn.close() # ══════════════════════════════════════════════════════════════════════════════ # BODY MEASUREMENTS # ══════════════════════════════════════════════════════════════════════════════ def _init_measurements(): conn = sqlite3.connect(DB_PATH) conn.execute(""" CREATE TABLE IF NOT EXISTS body_measurements ( id TEXT PRIMARY KEY, username TEXT NOT NULL, date TEXT NOT NULL, chest REAL, waist REAL, hips REAL, arms REAL, thighs REAL ) """) conn.commit(); conn.close() _init_measurements() def save_measurements(username, date_str, chest, waist, hips, arms, thighs): row_id = f"{username}_{date_str}" payload = {"id": row_id, "username": username, "date": date_str, "chest": chest, "waist": waist, "hips": hips, "arms": arms, "thighs": thighs} if USE_SUPABASE: _sb_post("body_measurements", payload, upsert=True) else: conn = sqlite3.connect(DB_PATH) conn.execute("INSERT OR REPLACE INTO body_measurements VALUES (?,?,?,?,?,?,?,?)", (row_id, username, date_str, chest, waist, hips, arms, thighs)) conn.commit(); conn.close() def get_measurements(username, limit=20): if USE_SUPABASE: rows = _sb_get("body_measurements", f"username=eq.{username}&order=date.asc&limit={limit}") else: conn = sqlite3.connect(DB_PATH) conn.row_factory = sqlite3.Row rows = [dict(r) for r in conn.execute( "SELECT * FROM body_measurements WHERE username=? ORDER BY date ASC LIMIT ?", (username, limit)).fetchall()] conn.close() return rows # ══════════════════════════════════════════════════════════════════════════════ # CHAT HISTORY # ══════════════════════════════════════════════════════════════════════════════ def _init_chat(): conn = sqlite3.connect(DB_PATH) conn.execute(""" CREATE TABLE IF NOT EXISTS chat_history ( id TEXT PRIMARY KEY, username TEXT NOT NULL, role TEXT NOT NULL, content TEXT NOT NULL, created_at REAL ) """) conn.commit(); conn.close() _init_chat() def save_chat_message(username, role, content): row_id = str(uuid.uuid4()) if USE_SUPABASE: _sb_post("chat_history", {"id": row_id, "username": username, "role": role, "content": content, "created_at": time.time()}) else: conn = sqlite3.connect(DB_PATH) conn.execute("INSERT INTO chat_history VALUES (?,?,?,?,?)", (row_id, username, role, content, time.time())) conn.commit(); conn.close() def get_chat_messages_raw(username, limit=50): if USE_SUPABASE: rows = _sb_get("chat_history", f"username=eq.{username}&order=created_at.asc&limit={limit}") else: conn = sqlite3.connect(DB_PATH) conn.row_factory = sqlite3.Row rows = [dict(r) for r in conn.execute( "SELECT * FROM chat_history WHERE username=? ORDER BY created_at ASC LIMIT ?", (username, limit)).fetchall()] conn.close() return [{"role": r["role"], "content": r["content"]} for r in rows] def clear_chat_history(username): if USE_SUPABASE: _sb_delete("chat_history", f"username=eq.{username}") else: conn = sqlite3.connect(DB_PATH) conn.execute("DELETE FROM chat_history WHERE username=?", (username,)) conn.commit(); conn.close() def save_chat_history(username, messages): clear_chat_history(username) for msg in messages: save_chat_message(username, msg.get("role", "user"), msg.get("content", "")) def get_chat_history(username, limit=20): return get_chat_messages_raw(username, limit=limit) # ══════════════════════════════════════════════════════════════════════════════ # USER SETTINGS # ══════════════════════════════════════════════════════════════════════════════ def _init_user_settings(): conn = sqlite3.connect(DB_PATH) conn.execute(""" CREATE TABLE IF NOT EXISTS user_settings ( username TEXT NOT NULL, setting_key TEXT NOT NULL, setting_value TEXT, updated_at REAL, PRIMARY KEY (username, setting_key) ) """) conn.commit(); conn.close() _init_user_settings() def save_user_setting(username, key, value): if USE_SUPABASE: _sb_post("user_settings", {"username": username, "setting_key": key, "setting_value": str(value), "updated_at": time.time()}, upsert=True) else: conn = sqlite3.connect(DB_PATH) conn.execute("INSERT OR REPLACE INTO user_settings VALUES (?,?,?,?)", (username, key, str(value), time.time())) conn.commit(); conn.close() def get_user_setting(username, key, default=None): if USE_SUPABASE: rows = _sb_get("user_settings", f"username=eq.{username}&setting_key=eq.{key}&limit=1") if rows: return rows[0].get("setting_value", default) else: conn = sqlite3.connect(DB_PATH) row = conn.execute( "SELECT setting_value FROM user_settings WHERE username=? AND setting_key=?", (username, key)).fetchone() conn.close() if row: return row[0] return default # ══════════════════════════════════════════════════════════════════════════════ # PROGRESS PHOTOS # ══════════════════════════════════════════════════════════════════════════════ def _init_progress_photos(): conn = sqlite3.connect(DB_PATH) conn.execute(""" CREATE TABLE IF NOT EXISTS progress_photos ( id TEXT PRIMARY KEY, username TEXT NOT NULL, photo_date TEXT, label TEXT, b64 TEXT, mime TEXT, created_at REAL ) """) conn.commit(); conn.close() _init_progress_photos() def save_progress_photo(username, photo_dict): row_id = str(uuid.uuid4()) if USE_SUPABASE: _sb_post("progress_photos", {"id": row_id, "username": username, "photo_date": photo_dict.get("date", ""), "label": photo_dict.get("label", ""), "b64": photo_dict.get("b64", ""), "mime": photo_dict.get("mime", "image/jpeg"), "created_at": time.time()}) else: conn = sqlite3.connect(DB_PATH) conn.execute("INSERT INTO progress_photos VALUES (?,?,?,?,?,?,?)", (row_id, username, photo_dict.get("date", ""), photo_dict.get("label", ""), photo_dict.get("b64", ""), photo_dict.get("mime", "image/jpeg"), time.time())) conn.commit(); conn.close() def get_progress_photos(username, limit=50): if USE_SUPABASE: rows = _sb_get("progress_photos", f"username=eq.{username}&order=created_at.asc&limit={limit}") else: conn = sqlite3.connect(DB_PATH) conn.row_factory = sqlite3.Row rows = [dict(r) for r in conn.execute( "SELECT * FROM progress_photos WHERE username=? ORDER BY created_at ASC LIMIT ?", (username, limit)).fetchall()] conn.close() return [{"date": r.get("photo_date", ""), "label": r.get("label", ""), "b64": r.get("b64", ""), "mime": r.get("mime", "image/jpeg")} for r in rows] def delete_progress_photo(username, photo_date, label): if USE_SUPABASE: _sb_delete("progress_photos", f"username=eq.{username}&photo_date=eq.{photo_date}&label=eq.{label}") else: conn = sqlite3.connect(DB_PATH) conn.execute("DELETE FROM progress_photos WHERE username=? AND photo_date=? AND label=?", (username, photo_date, label)) conn.commit(); conn.close() # ══════════════════════════════════════════════════════════════════════════════ # WORKOUT HISTORY (persists across plan deletions / regeneration) # ══════════════════════════════════════════════════════════════════════════════ def _init_workout_history(): conn = sqlite3.connect(DB_PATH) conn.execute(""" CREATE TABLE IF NOT EXISTS workout_history ( id TEXT PRIMARY KEY, username TEXT NOT NULL, date TEXT NOT NULL, day_number INTEGER DEFAULT 0, muscle_group TEXT DEFAULT 'Workout', status TEXT DEFAULT 'done', exercises INTEGER DEFAULT 0, is_rest INTEGER DEFAULT 0, UNIQUE (username, date) ) """) conn.commit() conn.close() _init_workout_history() def save_workout_history(username, history_entries): """Save completed/skipped sessions permanently. Survives plan deletion.""" if USE_SUPABASE: for entry in history_entries: _sb_post("workout_history", { "id": f"{username}_{entry.get('date_str', str(entry.get('date', '')))}", "username": username, "date": entry.get("date_str", str(entry.get("date", ""))), "day_number": entry.get("day", 0), "muscle_group": entry.get("muscle", "Workout"), "status": entry.get("status", "done"), "exercises": entry.get("exercises", 0), "is_rest": 1 if entry.get("is_rest") else 0, }, upsert=True) else: conn = sqlite3.connect(DB_PATH) for entry in history_entries: conn.execute(""" INSERT OR REPLACE INTO workout_history (id, username, date, day_number, muscle_group, status, exercises, is_rest) VALUES (?,?,?,?,?,?,?,?) """, ( f"{username}_{entry.get('date_str', str(entry.get('date', '')))}", username, entry.get("date_str", str(entry.get("date", ""))), entry.get("day", 0), entry.get("muscle", "Workout"), entry.get("status", "done"), entry.get("exercises", 0), 1 if entry.get("is_rest") else 0, )) conn.commit() conn.close() def get_workout_history(username): """Load all saved workout history, independent of current active plan.""" if USE_SUPABASE: rows = _sb_get("workout_history", f"username=eq.{username}&order=date.desc&limit=500") return [{ "date": r.get("date", ""), "day": r.get("day_number", 0), "muscle_group": r.get("muscle_group", "Workout"), "status": r.get("status", "done"), "exercises": r.get("exercises", 0), "is_rest": bool(r.get("is_rest", 0)), } for r in rows] if rows else [] else: conn = sqlite3.connect(DB_PATH) conn.row_factory = sqlite3.Row rows = [dict(r) for r in conn.execute( "SELECT * FROM workout_history WHERE username=? ORDER BY date DESC LIMIT 500", (username,)).fetchall()] conn.close() return [{ "date": r["date"], "day": r["day_number"], "muscle_group": r["muscle_group"], "status": r["status"], "exercises": r["exercises"], "is_rest": bool(r["is_rest"]), } for r in rows] # ══════════════════════════════════════════════════════════════════════════════ # COMPATIBILITY ALIASES # ══════════════════════════════════════════════════════════════════════════════ def get_water_intake(username, date_str): return get_water(username, date_str) def save_water_intake(username, date_str, glasses): return save_water(username, date_str, glasses) def save_personal_records(username, records_dict): from datetime import date as _d today = _d.today().isoformat() for ex_name, entries in records_dict.items(): if entries: e = entries[-1] save_personal_record(username, ex_name, e.get("value", 0), e.get("unit", "reps"), e.get("date", today), e.get("note", "")) def get_body_measurements(username, limit=20): return get_measurements(username, limit) def save_body_measurements(username, meas_list): from datetime import date as _d today = _d.today().isoformat() if meas_list: entry = meas_list[-1] m = entry.get("measurements", {}) save_measurements(username, entry.get("date", today), m.get("Chest (cm)", 0), m.get("Waist (cm)", 0), m.get("Hips (cm)", 0), m.get("Left Arm (cm)", 0), m.get("Left Thigh (cm)", 0))