Add per-user meal plans and household sharing

Each user now has their own meal plan and shopping list. Users can
form a household (invite by username, owner can remove members) so
that shopping list generation combines all household members' plans.
DB migration preserves existing data assigned to user id=1.

Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>

4 files changed, 370 insertions, 42 deletions

diff --git a/app/app.py b/app/app.py
index 026907e..e3e45d9 100644
--- a/app/app.py
+++ b/app/app.py
@@ -185,6 +185,102 @@ def get_cuisines(db):
     return [r['cuisine'] for r in rows]
 
 
+def _migrate(db):
+    def cols(tbl):
+        return [r[1] for r in db.execute(f"PRAGMA table_info({tbl})").fetchall()]
+
+    db.executescript("""
+        CREATE TABLE IF NOT EXISTS households (
+            id       INTEGER PRIMARY KEY AUTOINCREMENT,
+            name     TEXT NOT NULL,
+            owner_id INTEGER NOT NULL REFERENCES users(id)
+        );
+        CREATE TABLE IF NOT EXISTS household_members (
+            household_id INTEGER NOT NULL REFERENCES households(id) ON DELETE CASCADE,
+            user_id      INTEGER NOT NULL REFERENCES users(id)      ON DELETE CASCADE,
+            PRIMARY KEY (household_id, user_id)
+        );
+        CREATE TABLE IF NOT EXISTS comments (
+            id         INTEGER PRIMARY KEY AUTOINCREMENT,
+            recipe_id  INTEGER NOT NULL REFERENCES recipes(id) ON DELETE CASCADE,
+            username   TEXT NOT NULL,
+            body       TEXT NOT NULL,
+            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+        );
+    """)
+
+    if 'rating' not in cols('recipes'):
+        db.execute('ALTER TABLE recipes ADD COLUMN rating INTEGER')
+        db.commit()
+
+    if 'user_id' not in cols('meal_plan'):
+        db.executescript("""
+            PRAGMA foreign_keys = OFF;
+            CREATE TABLE meal_plan_new (
+                id        INTEGER PRIMARY KEY AUTOINCREMENT,
+                user_id   INTEGER NOT NULL REFERENCES users(id)    ON DELETE CASCADE,
+                plan_date DATE NOT NULL,
+                meal_type TEXT NOT NULL CHECK(meal_type IN ('breakfast','lunch','dinner')),
+                recipe_id INTEGER NOT NULL REFERENCES recipes(id)  ON DELETE CASCADE,
+                servings  INTEGER DEFAULT 2,
+                UNIQUE(user_id, plan_date, meal_type)
+            );
+            INSERT INTO meal_plan_new (id, user_id, plan_date, meal_type, recipe_id, servings)
+                SELECT id, 1, plan_date, meal_type, recipe_id, servings FROM meal_plan;
+            DROP TABLE meal_plan;
+            ALTER TABLE meal_plan_new RENAME TO meal_plan;
+            PRAGMA foreign_keys = ON;
+        """)
+
+    if 'user_id' not in cols('shopping_list'):
+        db.executescript("""
+            PRAGMA foreign_keys = OFF;
+            CREATE TABLE shopping_list_new (
+                id              INTEGER PRIMARY KEY AUTOINCREMENT,
+                user_id         INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
+                week_start      DATE NOT NULL,
+                ingredient_name TEXT NOT NULL,
+                quantity        REAL NOT NULL,
+                unit            TEXT NOT NULL,
+                category        TEXT NOT NULL,
+                recipe_sources  TEXT,
+                checked         INTEGER DEFAULT 0
+            );
+            INSERT INTO shopping_list_new
+                (id, user_id, week_start, ingredient_name, quantity, unit, category, recipe_sources, checked)
+                SELECT id, 1, week_start, ingredient_name, quantity, unit, category, recipe_sources, checked
+                FROM shopping_list;
+            DROP TABLE shopping_list;
+            ALTER TABLE shopping_list_new RENAME TO shopping_list;
+            PRAGMA foreign_keys = ON;
+        """)
+
+
+def get_user_household(db, user_id):
+    return db.execute(
+        """SELECT h.* FROM households h
+           JOIN household_members hm ON h.id = hm.household_id
+           WHERE hm.user_id = ?""",
+        (user_id,)
+    ).fetchone()
+
+
+def get_household_member_ids(db, household_id):
+    rows = db.execute(
+        "SELECT user_id FROM household_members WHERE household_id = ?",
+        (household_id,)
+    ).fetchall()
+    return [r['user_id'] for r in rows]
+
+
+def get_plan_user_ids(db, user_id):
+    """Returns all user_ids whose meal plans feed into this user's shopping list."""
+    household = get_user_household(db, user_id)
+    if household:
+        return get_household_member_ids(db, household['id'])
+    return [user_id]
+
+
 # ── Helpers ───────────────────────────────────────────────────────────────────
 
 def week_start_from_str(s):
@@ -231,12 +327,13 @@ def logout():
 def index():
     ws = week_start_from_str(None)
     dates = week_dates(ws)
+    uid = current_user.id if current_user.is_authenticated else 1
     db = database.get_db()
     rows = db.execute(
         """SELECT mp.*, r.name AS recipe_name, r.cuisine
            FROM meal_plan mp JOIN recipes r ON mp.recipe_id = r.id
-           WHERE mp.plan_date BETWEEN ? AND ?""",
-        (ws.isoformat(), dates[-1].isoformat()),
+           WHERE mp.plan_date BETWEEN ? AND ? AND mp.user_id = ?""",
+        (ws.isoformat(), dates[-1].isoformat(), uid),
     ).fetchall()
     plan = {f"{r['plan_date']}_{r['meal_type']}": dict(r) for r in rows}
     stats = db.execute("SELECT status, COUNT(*) AS cnt FROM recipes GROUP BY status").fetchall()
@@ -475,13 +572,14 @@ def rate_recipe(recipe_id):
 def meal_plan():
     ws = week_start_from_str(request.args.get('week'))
     dates = week_dates(ws)
+    uid = current_user.id if current_user.is_authenticated else 1
     db = database.get_db()
     rows = db.execute(
         """SELECT mp.*, r.name AS recipe_name, r.cuisine,
                   r.calories_per_serving, r.carbs_per_serving
            FROM meal_plan mp JOIN recipes r ON mp.recipe_id = r.id
-           WHERE mp.plan_date BETWEEN ? AND ?""",
-        (ws.isoformat(), dates[-1].isoformat()),
+           WHERE mp.plan_date BETWEEN ? AND ? AND mp.user_id = ?""",
+        (ws.isoformat(), dates[-1].isoformat(), uid),
     ).fetchall()
     all_recipes = db.execute(
         """SELECT id, name, cuisine, calories_per_serving
@@ -515,18 +613,18 @@ def add_meal():
     servings  = int(data.get('servings', 2))
 
     db = database.get_db()
-    db.execute("DELETE FROM meal_plan WHERE plan_date = ? AND meal_type = ?",
-               (plan_date, meal_type))
-    db.execute("INSERT INTO meal_plan (plan_date, meal_type, recipe_id, servings) VALUES (?,?,?,?)",
-               (plan_date, meal_type, recipe_id, servings))
+    db.execute("DELETE FROM meal_plan WHERE plan_date = ? AND meal_type = ? AND user_id = ?",
+               (plan_date, meal_type, current_user.id))
+    db.execute("INSERT INTO meal_plan (user_id, plan_date, meal_type, recipe_id, servings) VALUES (?,?,?,?,?)",
+               (current_user.id, plan_date, meal_type, recipe_id, servings))
     db.commit()
     recipe = db.execute(
         "SELECT name, cuisine, calories_per_serving, carbs_per_serving FROM recipes WHERE id = ?",
         (recipe_id,),
     ).fetchone()
     meal_id = db.execute(
-        "SELECT id FROM meal_plan WHERE plan_date = ? AND meal_type = ?",
-        (plan_date, meal_type),
+        "SELECT id FROM meal_plan WHERE plan_date = ? AND meal_type = ? AND user_id = ?",
+        (plan_date, meal_type, current_user.id),
     ).fetchone()['id']
     db.close()
     return jsonify({
@@ -543,8 +641,8 @@ def add_meal():
 def remove_meal():
     data = request.json
     db = database.get_db()
-    db.execute("DELETE FROM meal_plan WHERE plan_date = ? AND meal_type = ?",
-               (data['date'], data['meal_type']))
+    db.execute("DELETE FROM meal_plan WHERE plan_date = ? AND meal_type = ? AND user_id = ?",
+               (data['date'], data['meal_type'], current_user.id))
     db.commit()
     db.close()
     return jsonify({'success': True})
@@ -555,11 +653,14 @@ def remove_meal():
 @app.route('/shopping-list')
 def shopping_list():
     ws = week_start_from_str(request.args.get('week'))
+    uid = current_user.id if current_user.is_authenticated else 1
     db = database.get_db()
     items = db.execute(
-        "SELECT * FROM shopping_list WHERE week_start = ? ORDER BY category, ingredient_name",
-        (ws.isoformat(),),
+        "SELECT * FROM shopping_list WHERE week_start = ? AND user_id = ? ORDER BY category, ingredient_name",
+        (ws.isoformat(), uid),
     ).fetchall()
+    household = get_user_household(db, uid)
+    member_count = len(get_household_member_ids(db, household['id'])) if household else 1
     db.close()
 
     order = database.CATEGORY_ORDER
@@ -580,6 +681,7 @@ def shopping_list():
         categories=categories,
         total=len(items),
         checked=sum(1 for i in items if i['checked']),
+        member_count=member_count,
     )
 
 
@@ -590,11 +692,13 @@ def generate_shopping_list():
     dates = week_dates(ws)
 
     db = database.get_db()
+    member_ids = get_plan_user_ids(db, current_user.id)
+    placeholders = ','.join('?' * len(member_ids))
     meals = db.execute(
-        """SELECT mp.recipe_id, mp.servings AS meal_servings, r.servings AS recipe_servings
-           FROM meal_plan mp JOIN recipes r ON mp.recipe_id = r.id
-           WHERE mp.plan_date BETWEEN ? AND ?""",
-        (ws.isoformat(), dates[-1].isoformat()),
+        f"""SELECT mp.recipe_id, mp.servings AS meal_servings, r.servings AS recipe_servings
+            FROM meal_plan mp JOIN recipes r ON mp.recipe_id = r.id
+            WHERE mp.plan_date BETWEEN ? AND ? AND mp.user_id IN ({placeholders})""",
+        (ws.isoformat(), dates[-1].isoformat(), *member_ids),
     ).fetchall()
 
     if not meals:
@@ -621,18 +725,19 @@ def generate_shopping_list():
                     'recipes': {rec['name']},
                 }
 
-    db.execute("DELETE FROM shopping_list WHERE week_start = ?", (ws.isoformat(),))
+    db.execute("DELETE FROM shopping_list WHERE week_start = ? AND user_id = ?",
+               (ws.isoformat(), current_user.id))
     for item in totals.values():
         db.execute(
             """INSERT INTO shopping_list
-               (week_start, ingredient_name, quantity, unit, category, recipe_sources, checked)
-               VALUES (?,?,?,?,?,?,0)""",
-            (ws.isoformat(), item['name'], round(item['quantity'], 2),
+               (user_id, week_start, ingredient_name, quantity, unit, category, recipe_sources, checked)
+               VALUES (?,?,?,?,?,?,?,0)""",
+            (current_user.id, ws.isoformat(), item['name'], round(item['quantity'], 2),
              item['unit'], item['category'], ', '.join(sorted(item['recipes']))),
         )
     db.commit()
     db.close()
-    return jsonify({'success': True})
+    return jsonify({'success': True, 'member_count': len(member_ids)})
 
 
 @app.route('/shopping-list/<int:item_id>/check', methods=['POST'])
@@ -640,8 +745,8 @@ def generate_shopping_list():
 def check_item(item_id):
     checked = request.json.get('checked', False)
     db = database.get_db()
-    db.execute("UPDATE shopping_list SET checked = ? WHERE id = ?",
-               (1 if checked else 0, item_id))
+    db.execute("UPDATE shopping_list SET checked = ? WHERE id = ? AND user_id = ?",
+               (1 if checked else 0, item_id, current_user.id))
     db.commit()
     db.close()
     return jsonify({'success': True})
@@ -651,14 +756,130 @@ def check_item(item_id):
 @login_required
 def clear_shopping_list():
     db = database.get_db()
-    db.execute("DELETE FROM shopping_list WHERE week_start = ?",
-               (request.json.get('week'),))
+    db.execute("DELETE FROM shopping_list WHERE week_start = ? AND user_id = ?",
+               (request.json.get('week'), current_user.id))
     db.commit()
     db.close()
     return jsonify({'success': True})
 
 
 
+# ── Household ─────────────────────────────────────────────────────────────────
+
+@app.route('/household')
+@login_required
+def household():
+    db = database.get_db()
+    hh = get_user_household(db, current_user.id)
+    members = []
+    if hh:
+        rows = db.execute(
+            """SELECT u.id, u.username, (u.id = ?) AS is_me
+               FROM household_members hm JOIN users u ON hm.user_id = u.id
+               WHERE hm.household_id = ? ORDER BY u.username""",
+            (current_user.id, hh['id']),
+        ).fetchall()
+        members = [dict(r) for r in rows]
+    db.close()
+    return render_template('household.html', household=hh, members=members,
+                           is_owner=bool(hh and hh['owner_id'] == current_user.id))
+
+
+@app.route('/household/create', methods=['POST'])
+@login_required
+def create_household():
+    db = database.get_db()
+    if get_user_household(db, current_user.id):
+        db.close()
+        flash('You are already in a household. Leave it first.', 'warning')
+        return redirect(url_for('household'))
+    name = request.form.get('name', '').strip() or f"{current_user.username}'s household"
+    cur = db.execute("INSERT INTO households (name, owner_id) VALUES (?, ?)",
+                     (name, current_user.id))
+    db.execute("INSERT INTO household_members (household_id, user_id) VALUES (?, ?)",
+               (cur.lastrowid, current_user.id))
+    db.commit()
+    db.close()
+    flash('Household created!', 'success')
+    return redirect(url_for('household'))
+
+
+@app.route('/household/invite', methods=['POST'])
+@login_required
+def invite_member():
+    username = request.form.get('username', '').strip()
+    db = database.get_db()
+    hh = get_user_household(db, current_user.id)
+    if not hh or hh['owner_id'] != current_user.id:
+        db.close()
+        flash('Only the household owner can invite members.', 'danger')
+        return redirect(url_for('household'))
+    target = db.execute("SELECT id FROM users WHERE username = ?", (username,)).fetchone()
+    if not target:
+        db.close()
+        flash(f'User "{username}" not found.', 'danger')
+        return redirect(url_for('household'))
+    if get_user_household(db, target['id']):
+        db.close()
+        flash(f'{username} is already in a household.', 'warning')
+        return redirect(url_for('household'))
+    db.execute("INSERT INTO household_members (household_id, user_id) VALUES (?, ?)",
+               (hh['id'], target['id']))
+    db.commit()
+    db.close()
+    flash(f'{username} added to your household!', 'success')
+    return redirect(url_for('household'))
+
+
+@app.route('/household/remove', methods=['POST'])
+@login_required
+def remove_member():
+    username = request.form.get('username', '').strip()
+    db = database.get_db()
+    hh = get_user_household(db, current_user.id)
+    if not hh or hh['owner_id'] != current_user.id:
+        db.close()
+        flash('Only the household owner can remove members.', 'danger')
+        return redirect(url_for('household'))
+    target = db.execute("SELECT id FROM users WHERE username = ?", (username,)).fetchone()
+    if not target or target['id'] == current_user.id:
+        db.close()
+        flash('Cannot remove that user.', 'danger')
+        return redirect(url_for('household'))
+    db.execute("DELETE FROM household_members WHERE household_id = ? AND user_id = ?",
+               (hh['id'], target['id']))
+    db.commit()
+    db.close()
+    flash(f'{username} removed from the household.', 'success')
+    return redirect(url_for('household'))
+
+
+@app.route('/household/leave', methods=['POST'])
+@login_required
+def leave_household():
+    db = database.get_db()
+    hh = get_user_household(db, current_user.id)
+    if not hh:
+        db.close()
+        flash('You are not in a household.', 'warning')
+        return redirect(url_for('household'))
+    if hh['owner_id'] == current_user.id:
+        count = db.execute("SELECT COUNT(*) FROM household_members WHERE household_id = ?",
+                           (hh['id'],)).fetchone()[0]
+        if count > 1:
+            db.close()
+            flash('Remove all other members before dissolving the household.', 'danger')
+            return redirect(url_for('household'))
+        db.execute("DELETE FROM households WHERE id = ?", (hh['id'],))
+    else:
+        db.execute("DELETE FROM household_members WHERE household_id = ? AND user_id = ?",
+                   (hh['id'], current_user.id))
+    db.commit()
+    db.close()
+    flash('You have left the household.' if hh['owner_id'] != current_user.id else 'Household dissolved.', 'info')
+    return redirect(url_for('household'))
+
+
 # ── AI Recipe Assistant ───────────────────────────────────────────────────────
 
 def _execute_ai_tool(name, tool_input):
@@ -885,19 +1106,6 @@ def ai_chat():
 if __name__ == '__main__':
     database.init_db()
     _db = database.get_db()
-    _cols = [r[1] for r in _db.execute("PRAGMA table_info(recipes)").fetchall()]
-    if 'rating' not in _cols:
-        _db.execute('ALTER TABLE recipes ADD COLUMN rating INTEGER')
-        _db.commit()
-    _db.executescript("""
-        CREATE TABLE IF NOT EXISTS comments (
-            id INTEGER PRIMARY KEY AUTOINCREMENT,
-            recipe_id INTEGER NOT NULL REFERENCES recipes(id) ON DELETE CASCADE,
-            username TEXT NOT NULL,
-            body TEXT NOT NULL,
-            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
-        );
-    """)
-    _db.commit()
+    _migrate(_db)
     _db.close()
     app.run(host='0.0.0.0', port=5000, debug=False)
diff --git a/app/templates/base.html b/app/templates/base.html
index b58ef64..6702474 100644
--- a/app/templates/base.html
+++ b/app/templates/base.html
@@ -40,6 +40,11 @@
             <i class="bi bi-cart me-1"></i>Shopping List
           </a>
         </li>
+        <li class="nav-item">
+          <a class="nav-link {% if request.endpoint == 'household' %}active{% endif %}" href="/household">
+            <i class="bi bi-people me-1"></i>Household
+          </a>
+        </li>
       </ul>
 
       <ul class="navbar-nav ms-auto gap-1 align-items-center">
diff --git a/app/templates/household.html b/app/templates/household.html
new file mode 100644
index 0000000..cbb5121
--- /dev/null
+++ b/app/templates/household.html
@@ -0,0 +1,106 @@
+{% extends "base.html" %}
+{% block title %}Household — Menu Planner{% endblock %}
+
+{% block content %}
+<div class="d-flex align-items-center justify-content-between mb-4">
+  <div>
+    <h1 class="h3 fw-bold mb-0"><i class="bi bi-people me-2"></i>Household</h1>
+    {% if household %}<p class="text-muted mb-0">{{ household.name }}</p>{% endif %}
+  </div>
+</div>
+
+{% if not household %}
+<div class="card shadow-sm">
+  <div class="card-body text-center py-5">
+    <i class="bi bi-house-add fs-1 text-muted mb-3 d-block"></i>
+    <h5 class="fw-semibold">You're not in a household yet</h5>
+    <p class="text-muted">Create one and invite family members.<br>
+      Shopping lists will automatically combine everyone's meal plans.</p>
+    <form method="POST" action="/household/create" class="d-inline-flex gap-2 mt-2">
+      <input type="text" name="name" class="form-control form-control-sm"
+             placeholder="e.g. The Smith Family" style="width:230px">
+      <button type="submit" class="btn btn-primary btn-sm">
+        <i class="bi bi-plus-circle me-1"></i>Create Household
+      </button>
+    </form>
+  </div>
+</div>
+
+{% else %}
+
+<!-- Members -->
+<div class="card shadow-sm mb-4">
+  <div class="card-header fw-semibold d-flex align-items-center gap-2">
+    <i class="bi bi-people-fill"></i> Members
+    <span class="badge bg-secondary ms-auto">{{ members|length }}</span>
+  </div>
+  <ul class="list-group list-group-flush">
+    {% for m in members %}
+    <li class="list-group-item d-flex align-items-center justify-content-between">
+      <div class="d-flex align-items-center gap-2">
+        <i class="bi bi-person-circle text-muted fs-5"></i>
+        <span class="fw-semibold">{{ m.username }}</span>
+        {% if m.id == household.owner_id %}
+          <span class="badge bg-warning text-dark">Owner</span>
+        {% endif %}
+        {% if m.is_me %}
+          <span class="text-muted small">(you)</span>
+        {% endif %}
+      </div>
+      {% if is_owner and not m.is_me %}
+      <form method="POST" action="/household/remove"
+            onsubmit="return confirm('Remove {{ m.username }} from the household?')">
+        <input type="hidden" name="username" value="{{ m.username }}">
+        <button type="submit" class="btn btn-sm btn-outline-danger">
+          <i class="bi bi-person-dash me-1"></i>Remove
+        </button>
+      </form>
+      {% endif %}
+    </li>
+    {% endfor %}
+  </ul>
+</div>
+
+{% if is_owner %}
+<!-- Invite -->
+<div class="card shadow-sm mb-4">
+  <div class="card-header fw-semibold">
+    <i class="bi bi-person-plus me-1"></i>Invite Member
+  </div>
+  <div class="card-body">
+    <form method="POST" action="/household/invite" class="d-flex gap-2">
+      <input type="text" name="username" class="form-control" placeholder="Username" required autocomplete="off">
+      <button type="submit" class="btn btn-primary">Add</button>
+    </form>
+    <p class="text-muted small mt-2 mb-0">
+      The user must already have an account. They'll join immediately and their meal plan
+      will be included in your combined shopping list.
+    </p>
+  </div>
+</div>
+{% endif %}
+
+<!-- Leave / dissolve -->
+<div class="card shadow-sm border-danger border-opacity-25">
+  <div class="card-body">
+    {% if is_owner and members|length > 1 %}
+    <p class="text-muted small mb-2">
+      Remove all other members before you can dissolve the household.
+    </p>
+    <button class="btn btn-outline-danger btn-sm" disabled>
+      <i class="bi bi-box-arrow-left me-1"></i>Dissolve Household
+    </button>
+    {% else %}
+    <form method="POST" action="/household/leave"
+          onsubmit="return confirm('{% if is_owner %}Dissolve this household?{% else %}Leave this household?{% endif %}')">
+      <button type="submit" class="btn btn-outline-danger btn-sm">
+        <i class="bi bi-box-arrow-left me-1"></i>
+        {% if is_owner %}Dissolve Household{% else %}Leave Household{% endif %}
+      </button>
+    </form>
+    {% endif %}
+  </div>
+</div>
+
+{% endif %}
+{% endblock %}
diff --git a/app/templates/shopping_list.html b/app/templates/shopping_list.html
index e6cfaaf..8a3a60e 100644
--- a/app/templates/shopping_list.html
+++ b/app/templates/shopping_list.html
@@ -9,6 +9,9 @@
       Week of {{ week_start.strftime('%B %d, %Y') }}
       {% if total %}&nbsp;·&nbsp; {{ checked }}/{{ total }} checked{% endif %}
     </p>
+    {% if member_count and member_count > 1 %}
+    <p class="text-muted small mb-0"><i class="bi bi-people me-1"></i>Combined from {{ member_count }} household members' plans</p>
+    {% endif %}
   </div>
   <div class="d-flex gap-2 align-items-center">
     <a href="/shopping-list?week={{ prev_week }}" class="btn btn-outline-secondary btn-sm"><i class="bi bi-chevron-left"></i></a>
@@ -145,7 +148,13 @@ async function regenerate() {
   if (!res) return;
   const data = await res.json();
   if (data.success) {
-    location.reload();
+    if (data.member_count > 1) {
+      document.getElementById('toastBody').textContent = `Combined from ${data.member_count} members' plans!`;
+      toast.show();
+      setTimeout(() => location.reload(), 1500);
+    } else {
+      location.reload();
+    }
   } else {
     document.getElementById('toastBody').textContent = data.error || 'No meals planned for this week.';
     document.getElementById('toast').className = 'toast align-items-center text-bg-danger border-0';