5 files changed, 562 insertions, 4 deletions

diff --git a/internal/store/shopping.go b/internal/store/shopping.go
index 40571ac..448a2be 100644
--- a/internal/store/shopping.go
+++ b/internal/store/shopping.go
@@ -1,24 +1,85 @@
 package store
 
-import "time"
+import (
+	"database/sql"
+	"errors"
+	"fmt"
+	"strings"
+	"time"
+)
 
 type UserShoppingItem struct {
 	ID        int64
 	Name      string
 	Store     string
+	Section   string
 	Checked   bool
 	CreatedAt time.Time
 }
 
 // SaveUserShoppingItem saves a new user shopping item
 func (s *Store) SaveUserShoppingItem(name, store string) error {
-	_, err := s.db.Exec(`INSERT INTO user_shopping_items (name, store) VALUES (?, ?)`, name, store)
+	return s.SaveUserShoppingItemInSection(name, store, "")
+}
+
+// SaveUserShoppingItemInSection saves a new item filed under a section.
+func (s *Store) SaveUserShoppingItemInSection(name, store, section string) error {
+	_, err := s.db.Exec(
+		`INSERT INTO user_shopping_items (name, store, section) VALUES (?, ?, ?)`,
+		strings.TrimSpace(name), strings.TrimSpace(store), strings.TrimSpace(section))
 	return err
 }
 
+// UpdateUserShoppingItem renames an item and/or moves it between stores and
+// sections. Everything an edit can change goes through here, so a partial
+// update cannot leave an item half-moved.
+func (s *Store) UpdateUserShoppingItem(id int64, name, store, section string) error {
+	name = strings.TrimSpace(name)
+	store = strings.TrimSpace(store)
+	section = strings.TrimSpace(section)
+	if name == "" {
+		return fmt.Errorf("item name is required")
+	}
+	if store == "" {
+		return fmt.Errorf("store is required")
+	}
+
+	res, err := s.db.Exec(
+		`UPDATE user_shopping_items SET name = ?, store = ?, section = ? WHERE id = ?`,
+		name, store, section, id)
+	if err != nil {
+		return err
+	}
+	// An UPDATE that matches nothing succeeds at the SQL level, so a stale or
+	// wrong id would otherwise look like a successful edit.
+	if n, err := res.RowsAffected(); err == nil && n == 0 {
+		return ErrItemNotFound
+	}
+	return nil
+}
+
+// GetUserShoppingItem returns a single item, or ErrItemNotFound.
+func (s *Store) GetUserShoppingItem(id int64) (*UserShoppingItem, error) {
+	var item UserShoppingItem
+	err := s.db.QueryRow(
+		`SELECT id, name, store, COALESCE(section, ''), checked, created_at
+		 FROM user_shopping_items WHERE id = ?`, id).
+		Scan(&item.ID, &item.Name, &item.Store, &item.Section, &item.Checked, &item.CreatedAt)
+	if errors.Is(err, sql.ErrNoRows) {
+		return nil, ErrItemNotFound
+	}
+	if err != nil {
+		return nil, err
+	}
+	return &item, nil
+}
+
+// ErrItemNotFound is returned when an item id matches nothing.
+var ErrItemNotFound = errors.New("shopping item not found")
+
 // GetUserShoppingItems retrieves all user shopping items
 func (s *Store) GetUserShoppingItems() ([]UserShoppingItem, error) {
-	rows, err := s.db.Query(`SELECT id, name, store, checked, created_at FROM user_shopping_items ORDER BY store, created_at DESC`)
+	rows, err := s.db.Query(`SELECT id, name, store, COALESCE(section, ''), checked, created_at FROM user_shopping_items ORDER BY store, created_at DESC`)
 	if err != nil {
 		return nil, err
 	}
@@ -27,7 +88,7 @@ func (s *Store) GetUserShoppingItems() ([]UserShoppingItem, error) {
 	var items []UserShoppingItem
 	for rows.Next() {
 		var item UserShoppingItem
-		if err := rows.Scan(&item.ID, &item.Name, &item.Store, &item.Checked, &item.CreatedAt); err != nil {
+		if err := rows.Scan(&item.ID, &item.Name, &item.Store, &item.Section, &item.Checked, &item.CreatedAt); err != nil {
 			return nil, err
 		}
 		items = append(items, item)
diff --git a/internal/store/shopping_import_test.go b/internal/store/shopping_import_test.go
index 0e246d8..87dee92 100644
--- a/internal/store/shopping_import_test.go
+++ b/internal/store/shopping_import_test.go
@@ -145,6 +145,7 @@ func setupImportTestDB(t *testing.T) *sql.DB {
 			id INTEGER PRIMARY KEY AUTOINCREMENT,
 			name TEXT NOT NULL,
 			store TEXT NOT NULL,
+			section TEXT NOT NULL DEFAULT '',
 			checked INTEGER DEFAULT 0,
 			created_at DATETIME DEFAULT CURRENT_TIMESTAMP
 		);
diff --git a/internal/store/shopping_registry.go b/internal/store/shopping_registry.go
new file mode 100644
index 0000000..f3cc515
--- /dev/null
+++ b/internal/store/shopping_registry.go
@@ -0,0 +1,193 @@
+package store
+
+import (
+	"database/sql"
+	"errors"
+	"fmt"
+	"strings"
+)
+
+// ShoppingStore is a registered store, with its sections.
+type ShoppingStore struct {
+	ID       int64
+	Name     string
+	Sections []ShoppingSection
+}
+
+// ShoppingSection is an aisle or grouping within a store.
+type ShoppingSection struct {
+	ID      int64
+	StoreID int64
+	Name    string
+}
+
+// ErrStoreNotFound is returned when an operation names a store that is not
+// registered. Callers surface this rather than silently creating one, so a
+// typo does not quietly produce a second store.
+var ErrStoreNotFound = errors.New("store not found")
+
+// GetShoppingStores returns every registered store with its sections.
+func (s *Store) GetShoppingStores() ([]ShoppingStore, error) {
+	rows, err := s.db.Query(`SELECT id, name FROM shopping_stores ORDER BY sort_order, name`)
+	if err != nil {
+		return nil, err
+	}
+	defer func() { _ = rows.Close() }()
+
+	var stores []ShoppingStore
+	byID := map[int64]int{}
+	for rows.Next() {
+		var st ShoppingStore
+		if err := rows.Scan(&st.ID, &st.Name); err != nil {
+			return nil, err
+		}
+		byID[st.ID] = len(stores)
+		stores = append(stores, st)
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+
+	secRows, err := s.db.Query(`SELECT id, store_id, name FROM shopping_sections ORDER BY sort_order, name`)
+	if err != nil {
+		return nil, err
+	}
+	defer func() { _ = secRows.Close() }()
+
+	for secRows.Next() {
+		var sec ShoppingSection
+		if err := secRows.Scan(&sec.ID, &sec.StoreID, &sec.Name); err != nil {
+			return nil, err
+		}
+		if idx, ok := byID[sec.StoreID]; ok {
+			stores[idx].Sections = append(stores[idx].Sections, sec)
+		}
+	}
+	return stores, secRows.Err()
+}
+
+// AddShoppingStore registers a store. Adding one that already exists is a
+// no-op rather than an error — the intent ("there should be a store called
+// this") is already satisfied.
+func (s *Store) AddShoppingStore(name string) error {
+	name = strings.TrimSpace(name)
+	if name == "" {
+		return fmt.Errorf("store name is required")
+	}
+	_, err := s.db.Exec(`INSERT OR IGNORE INTO shopping_stores (name) VALUES (?)`, name)
+	return err
+}
+
+// RenameShoppingStore renames a store and moves its items with it.
+//
+// Items reference a store by name, so the two have to change together or every
+// item in the store is orphaned. Done in one transaction for that reason.
+func (s *Store) RenameShoppingStore(id int64, newName string) error {
+	newName = strings.TrimSpace(newName)
+	if newName == "" {
+		return fmt.Errorf("store name is required")
+	}
+
+	tx, err := s.db.Begin()
+	if err != nil {
+		return err
+	}
+	defer func() { _ = tx.Rollback() }()
+
+	var oldName string
+	if err := tx.QueryRow(`SELECT name FROM shopping_stores WHERE id = ?`, id).Scan(&oldName); err != nil {
+		if errors.Is(err, sql.ErrNoRows) {
+			return ErrStoreNotFound
+		}
+		return err
+	}
+	if _, err := tx.Exec(`UPDATE shopping_stores SET name = ? WHERE id = ?`, newName, id); err != nil {
+		return err
+	}
+	if _, err := tx.Exec(`UPDATE user_shopping_items SET store = ? WHERE store = ?`, newName, oldName); err != nil {
+		return err
+	}
+	return tx.Commit()
+}
+
+// DeleteShoppingStore removes a store. Its items are not deleted; they are
+// moved to unassigned so nothing silently disappears with the store.
+func (s *Store) DeleteShoppingStore(id int64) error {
+	tx, err := s.db.Begin()
+	if err != nil {
+		return err
+	}
+	defer func() { _ = tx.Rollback() }()
+
+	var name string
+	if err := tx.QueryRow(`SELECT name FROM shopping_stores WHERE id = ?`, id).Scan(&name); err != nil {
+		if errors.Is(err, sql.ErrNoRows) {
+			return ErrStoreNotFound
+		}
+		return err
+	}
+	if _, err := tx.Exec(
+		`UPDATE user_shopping_items SET store = 'Unassigned', section = '' WHERE store = ?`, name); err != nil {
+		return err
+	}
+	// Sections go with the store via ON DELETE CASCADE.
+	if _, err := tx.Exec(`DELETE FROM shopping_stores WHERE id = ?`, id); err != nil {
+		return err
+	}
+	return tx.Commit()
+}
+
+// AddShoppingSection adds a section to a store.
+func (s *Store) AddShoppingSection(storeID int64, name string) error {
+	name = strings.TrimSpace(name)
+	if name == "" {
+		return fmt.Errorf("section name is required")
+	}
+	_, err := s.db.Exec(
+		`INSERT OR IGNORE INTO shopping_sections (store_id, name) VALUES (?, ?)`, storeID, name)
+	return err
+}
+
+// DeleteShoppingSection removes a section, clearing it from its items rather
+// than deleting them.
+func (s *Store) DeleteShoppingSection(id int64) error {
+	tx, err := s.db.Begin()
+	if err != nil {
+		return err
+	}
+	defer func() { _ = tx.Rollback() }()
+
+	var storeName, sectionName string
+	err = tx.QueryRow(`
+		SELECT st.name, se.name
+		FROM shopping_sections se JOIN shopping_stores st ON st.id = se.store_id
+		WHERE se.id = ?`, id).Scan(&storeName, &sectionName)
+	if err != nil {
+		if errors.Is(err, sql.ErrNoRows) {
+			return nil // already gone
+		}
+		return err
+	}
+	if _, err := tx.Exec(
+		`UPDATE user_shopping_items SET section = '' WHERE store = ? AND section = ?`,
+		storeName, sectionName); err != nil {
+		return err
+	}
+	if _, err := tx.Exec(`DELETE FROM shopping_sections WHERE id = ?`, id); err != nil {
+		return err
+	}
+	return tx.Commit()
+}
+
+// ShoppingStoreNames returns just the registered store names, for pickers.
+func (s *Store) ShoppingStoreNames() ([]string, error) {
+	stores, err := s.GetShoppingStores()
+	if err != nil {
+		return nil, err
+	}
+	names := make([]string, 0, len(stores))
+	for _, st := range stores {
+		names = append(names, st.Name)
+	}
+	return names, nil
+}
diff --git a/internal/store/shopping_registry_test.go b/internal/store/shopping_registry_test.go
new file mode 100644
index 0000000..b8ed79e
--- /dev/null
+++ b/internal/store/shopping_registry_test.go
@@ -0,0 +1,302 @@
+package store
+
+import (
+	"database/sql"
+	"errors"
+	"path/filepath"
+	"testing"
+
+	_ "github.com/mattn/go-sqlite3"
+)
+
+func setupRegistryStore(t *testing.T) *Store {
+	t.Helper()
+	db, err := sql.Open("sqlite3", filepath.Join(t.TempDir(), "reg.db"))
+	if err != nil {
+		t.Fatalf("open: %v", err)
+	}
+	db.SetMaxOpenConns(1)
+	if _, err := db.Exec(`
+		PRAGMA foreign_keys = ON;
+		CREATE TABLE user_shopping_items (
+			id INTEGER PRIMARY KEY AUTOINCREMENT,
+			name TEXT NOT NULL,
+			store TEXT NOT NULL,
+			section TEXT NOT NULL DEFAULT '',
+			checked INTEGER DEFAULT 0,
+			created_at DATETIME DEFAULT CURRENT_TIMESTAMP
+		);
+		CREATE TABLE shopping_stores (
+			id INTEGER PRIMARY KEY AUTOINCREMENT,
+			name TEXT NOT NULL UNIQUE,
+			sort_order INTEGER NOT NULL DEFAULT 0,
+			created_at DATETIME DEFAULT CURRENT_TIMESTAMP
+		);
+		CREATE TABLE shopping_sections (
+			id INTEGER PRIMARY KEY AUTOINCREMENT,
+			store_id INTEGER NOT NULL REFERENCES shopping_stores(id) ON DELETE CASCADE,
+			name TEXT NOT NULL,
+			sort_order INTEGER NOT NULL DEFAULT 0,
+			created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
+			UNIQUE (store_id, name)
+		);`); err != nil {
+		t.Fatalf("schema: %v", err)
+	}
+	return &Store{db: db}
+}
+
+// The registry's whole purpose: a store exists whether or not anything is in
+// it, so you can move an item *to* an empty store.
+func TestShoppingStores_ExistWithoutItems(t *testing.T) {
+	s := setupRegistryStore(t)
+	defer func() { _ = s.Close() }()
+
+	if err := s.AddShoppingStore("Hardware Store"); err != nil {
+		t.Fatalf("add: %v", err)
+	}
+	names, err := s.ShoppingStoreNames()
+	if err != nil {
+		t.Fatalf("names: %v", err)
+	}
+	if len(names) != 1 || names[0] != "Hardware Store" {
+		t.Errorf("got %v, want [Hardware Store]", names)
+	}
+}
+
+func TestShoppingStores_AddIsIdempotent(t *testing.T) {
+	s := setupRegistryStore(t)
+	defer func() { _ = s.Close() }()
+
+	_ = s.AddShoppingStore("Costco")
+	if err := s.AddShoppingStore("Costco"); err != nil {
+		t.Fatalf("re-add should be a no-op, got: %v", err)
+	}
+	names, _ := s.ShoppingStoreNames()
+	if len(names) != 1 {
+		t.Errorf("got %d stores, want 1", len(names))
+	}
+}
+
+func TestShoppingStores_AddRejectsBlank(t *testing.T) {
+	s := setupRegistryStore(t)
+	defer func() { _ = s.Close() }()
+	if err := s.AddShoppingStore("   "); err == nil {
+		t.Error("expected an error for a blank store name")
+	}
+}
+
+// Items reference a store by name, so a rename must carry them along or every
+// item in that store is orphaned.
+func TestShoppingStores_RenameMovesItsItems(t *testing.T) {
+	s := setupRegistryStore(t)
+	defer func() { _ = s.Close() }()
+
+	_ = s.AddShoppingStore("Hardware")
+	_ = s.SaveUserShoppingItem("Nails", "Hardware")
+	_ = s.SaveUserShoppingItem("Milk", "Costco")
+
+	stores, _ := s.GetShoppingStores()
+	if err := s.RenameShoppingStore(stores[0].ID, "Hardware Store"); err != nil {
+		t.Fatalf("rename: %v", err)
+	}
+
+	items, _ := s.GetUserShoppingItems()
+	for _, it := range items {
+		if it.Name == "Nails" && it.Store != "Hardware Store" {
+			t.Errorf("item stayed on the old store name: %+v", it)
+		}
+		if it.Name == "Milk" && it.Store != "Costco" {
+			t.Errorf("an unrelated item was moved: %+v", it)
+		}
+	}
+}
+
+func TestShoppingStores_RenameUnknownStore(t *testing.T) {
+	s := setupRegistryStore(t)
+	defer func() { _ = s.Close() }()
+	if err := s.RenameShoppingStore(999, "Nope"); !errors.Is(err, ErrStoreNotFound) {
+		t.Errorf("got %v, want ErrStoreNotFound", err)
+	}
+}
+
+// Deleting a store must not take its items with it — losing the shopping list
+// is a much worse outcome than an item needing to be re-filed.
+func TestShoppingStores_DeleteKeepsItems(t *testing.T) {
+	s := setupRegistryStore(t)
+	defer func() { _ = s.Close() }()
+
+	_ = s.AddShoppingStore("Hardware")
+	_ = s.SaveUserShoppingItemInSection("Nails", "Hardware", "Fasteners")
+
+	stores, _ := s.GetShoppingStores()
+	if err := s.DeleteShoppingStore(stores[0].ID); err != nil {
+		t.Fatalf("delete: %v", err)
+	}
+
+	items, _ := s.GetUserShoppingItems()
+	if len(items) != 1 {
+		t.Fatalf("got %d items, want the item kept", len(items))
+	}
+	if items[0].Store != "Unassigned" || items[0].Section != "" {
+		t.Errorf("got %+v, want it moved to Unassigned with no section", items[0])
+	}
+}
+
+func TestShoppingSections_AddedAndReturnedWithTheirStore(t *testing.T) {
+	s := setupRegistryStore(t)
+	defer func() { _ = s.Close() }()
+
+	_ = s.AddShoppingStore("Costco")
+	stores, _ := s.GetShoppingStores()
+	if err := s.AddShoppingSection(stores[0].ID, "Produce"); err != nil {
+		t.Fatalf("add section: %v", err)
+	}
+	_ = s.AddShoppingSection(stores[0].ID, "Frozen")
+
+	stores, _ = s.GetShoppingStores()
+	if len(stores[0].Sections) != 2 {
+		t.Fatalf("got %d sections, want 2", len(stores[0].Sections))
+	}
+}
+
+// The same section name in two different stores is normal ("Produce" exists in
+// both) and must not collide.
+func TestShoppingSections_SameNameInDifferentStores(t *testing.T) {
+	s := setupRegistryStore(t)
+	defer func() { _ = s.Close() }()
+
+	_ = s.AddShoppingStore("Costco")
+	_ = s.AddShoppingStore("Safeway")
+	stores, _ := s.GetShoppingStores()
+	for _, st := range stores {
+		if err := s.AddShoppingSection(st.ID, "Produce"); err != nil {
+			t.Fatalf("add section to %s: %v", st.Name, err)
+		}
+	}
+	stores, _ = s.GetShoppingStores()
+	for _, st := range stores {
+		if len(st.Sections) != 1 {
+			t.Errorf("%s has %d sections, want 1", st.Name, len(st.Sections))
+		}
+	}
+}
+
+func TestShoppingSections_DeleteClearsItFromItems(t *testing.T) {
+	s := setupRegistryStore(t)
+	defer func() { _ = s.Close() }()
+
+	_ = s.AddShoppingStore("Costco")
+	stores, _ := s.GetShoppingStores()
+	_ = s.AddShoppingSection(stores[0].ID, "Produce")
+	_ = s.SaveUserShoppingItemInSection("Kale", "Costco", "Produce")
+
+	stores, _ = s.GetShoppingStores()
+	if err := s.DeleteShoppingSection(stores[0].Sections[0].ID); err != nil {
+		t.Fatalf("delete section: %v", err)
+	}
+
+	items, _ := s.GetUserShoppingItems()
+	if len(items) != 1 {
+		t.Fatalf("item was deleted along with its section")
+	}
+	if items[0].Section != "" {
+		t.Errorf("Section = %q, want cleared", items[0].Section)
+	}
+	if items[0].Store != "Costco" {
+		t.Errorf("Store = %q, want unchanged", items[0].Store)
+	}
+}
+
+// Deleting a store's section must cascade, not leave orphaned rows pointing at
+// a store that no longer exists.
+func TestShoppingSections_CascadeWhenStoreDeleted(t *testing.T) {
+	s := setupRegistryStore(t)
+	defer func() { _ = s.Close() }()
+
+	_ = s.AddShoppingStore("Costco")
+	stores, _ := s.GetShoppingStores()
+	_ = s.AddShoppingSection(stores[0].ID, "Produce")
+
+	if err := s.DeleteShoppingStore(stores[0].ID); err != nil {
+		t.Fatalf("delete store: %v", err)
+	}
+	var n int
+	if err := s.db.QueryRow(`SELECT COUNT(*) FROM shopping_sections`).Scan(&n); err != nil {
+		t.Fatalf("count: %v", err)
+	}
+	if n != 0 {
+		t.Errorf("%d orphaned sections remain", n)
+	}
+}
+
+// ── item editing ─────────────────────────────────────────────────────────────
+
+func TestUpdateUserShoppingItem_RenamesAndMoves(t *testing.T) {
+	s := setupRegistryStore(t)
+	defer func() { _ = s.Close() }()
+
+	_ = s.SaveUserShoppingItemInSection("Mlik", "Costco", "Dairy")
+	items, _ := s.GetUserShoppingItems()
+
+	if err := s.UpdateUserShoppingItem(items[0].ID, "Milk", "Safeway", "Refrigerated"); err != nil {
+		t.Fatalf("update: %v", err)
+	}
+
+	got, err := s.GetUserShoppingItem(items[0].ID)
+	if err != nil {
+		t.Fatalf("get: %v", err)
+	}
+	if got.Name != "Milk" || got.Store != "Safeway" || got.Section != "Refrigerated" {
+		t.Errorf("got %+v, want renamed and moved", got)
+	}
+}
+
+func TestUpdateUserShoppingItem_ClearingTheSection(t *testing.T) {
+	s := setupRegistryStore(t)
+	defer func() { _ = s.Close() }()
+
+	_ = s.SaveUserShoppingItemInSection("Kale", "Costco", "Produce")
+	items, _ := s.GetUserShoppingItems()
+
+	if err := s.UpdateUserShoppingItem(items[0].ID, "Kale", "Costco", ""); err != nil {
+		t.Fatalf("update: %v", err)
+	}
+	got, _ := s.GetUserShoppingItem(items[0].ID)
+	if got.Section != "" {
+		t.Errorf("Section = %q, want cleared", got.Section)
+	}
+}
+
+// A SQL UPDATE that matches nothing still succeeds, so a stale id would look
+// like a successful edit and the user would think the change stuck.
+func TestUpdateUserShoppingItem_UnknownIDIsAnError(t *testing.T) {
+	s := setupRegistryStore(t)
+	defer func() { _ = s.Close() }()
+
+	if err := s.UpdateUserShoppingItem(999, "Ghost", "Costco", ""); !errors.Is(err, ErrItemNotFound) {
+		t.Errorf("got %v, want ErrItemNotFound", err)
+	}
+}
+
+func TestUpdateUserShoppingItem_RejectsBlankNameOrStore(t *testing.T) {
+	s := setupRegistryStore(t)
+	defer func() { _ = s.Close() }()
+
+	_ = s.SaveUserShoppingItem("Milk", "Costco")
+	items, _ := s.GetUserShoppingItems()
+
+	if err := s.UpdateUserShoppingItem(items[0].ID, "  ", "Costco", ""); err == nil {
+		t.Error("expected an error for a blank name")
+	}
+	if err := s.UpdateUserShoppingItem(items[0].ID, "Milk", "  ", ""); err == nil {
+		t.Error("expected an error for a blank store")
+	}
+}
+
+func TestGetUserShoppingItem_NotFound(t *testing.T) {
+	s := setupRegistryStore(t)
+	defer func() { _ = s.Close() }()
+	if _, err := s.GetUserShoppingItem(999); !errors.Is(err, ErrItemNotFound) {
+		t.Errorf("got %v, want ErrItemNotFound", err)
+	}
+}
diff --git a/internal/store/shopping_test.go b/internal/store/shopping_test.go
index 01f106c..3dfd2c2 100644
--- a/internal/store/shopping_test.go
+++ b/internal/store/shopping_test.go
@@ -27,6 +27,7 @@ func setupTestStoreWithShopping(t *testing.T) *Store {
 			id INTEGER PRIMARY KEY AUTOINCREMENT,
 			name TEXT NOT NULL,
 			store TEXT NOT NULL,
+			section TEXT NOT NULL DEFAULT '',
 			checked INTEGER DEFAULT 0,
 			created_at DATETIME DEFAULT CURRENT_TIMESTAMP
 		);