1 files changed, 193 insertions, 0 deletions
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
+}