From c5ab2f9a7f6a0b1e456e13934268d1509504c55a Mon Sep 17 00:00:00 2001 From: Peter Stone Date: Sun, 20 Sep 2026 07:47:48 +0000 Subject: Drop Trello as a shopping source, importing the two groups worth keeping MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit The Trello "Shopping" board had become a dumping ground: of 24 cards, 13 had no list at all (rendering as "Uncategorized"), and the rest were spread across Costco, Hardware Store, "Perennial " (trailing space, so a separate store from "Perennial") and online. Its items were read-only in doot — no rename, no move, no delete, only hide — and its list names were doubling as an accidental store registry, which is the only reason stores like Hardware Store existed at all. Migration 032 imports the two groups asked for, Hardware Store and the unlisted cards, into user_shopping_items. Costco, Perennial and online cards are left behind. Nothing is destroyed: the cards remain in Trello, this only stops doot displaying them. Imported rows become ordinary user items, so they also become editable, which Trello-sourced items never were. Trello remains the source for the other eight boards; this is only the Shopping board feeding the shopping list. Also updated the quick-action modal's Shopping tab, which posted source=trello with a Trello list_id. Left alone it would have kept creating cards that no longer appear anywhere. It now posts source=shopping with a store name, handled by a new case in HandleUnifiedAdd, and its picker reads from a new /partials/shopping-stores endpoint. The Trello-lists endpoint it used to call is deleted along with its route. Note for the edit feature still to come: stores have no existence independent of items, so a store with nothing in it cannot be chosen. That was previously masked by Trello's lists persisting. "Move between stores and sections" needs a real stores/sections table. Migration tested against a seeded snapshot of the real board, including the trailing-space store name, other boards, replay safety and the empty-cache no-op. Two existing tests asserting Trello check-state in shopping mode were updated to plantoeat, the only remaining external source. Co-Authored-By: Claude Opus 5 Claude-Session: https://claude.ai/code/session_018AwnALZL6bD4VBtATDVv3B --- cmd/dashboard/main.go | 2 +- internal/handlers/handlers.go | 14 ++ internal/handlers/handlers_test.go | 15 +- internal/handlers/shopping.go | 89 +++------ internal/handlers/shopping_sources_test.go | 159 ++++++++++++++++ internal/store/shopping_import_test.go | 229 ++++++++++++++++++++++++ migrations/032_import_trello_shopping_items.sql | 46 +++++ migrations/README.md | 3 +- web/templates/index.html | 15 +- web/templates/partials/shopping-mode-items.html | 3 +- web/templates/partials/shopping-tab.html | 4 +- 11 files changed, 493 insertions(+), 86 deletions(-) create mode 100644 internal/handlers/shopping_sources_test.go create mode 100644 internal/store/shopping_import_test.go create mode 100644 migrations/032_import_trello_shopping_items.sql diff --git a/cmd/dashboard/main.go b/cmd/dashboard/main.go index b6518c4..bfd890a 100644 --- a/cmd/dashboard/main.go +++ b/cmd/dashboard/main.go @@ -357,7 +357,6 @@ func main() { // Unified Quick Add (for Tasks tab) r.Post("/unified-add", h.HandleUnifiedAdd) - r.Get("/partials/shopping-lists", h.HandleGetShoppingLists) // Task detail/edit r.Get("/tasks/detail", h.HandleGetTaskDetail) @@ -369,6 +368,7 @@ func main() { r.Delete("/tasks/{id}", h.HandleDeleteTask) // Shopping quick-add + r.Get("/partials/shopping-stores", h.HandleGetShoppingStores) r.Post("/shopping/add", h.HandleShoppingQuickAdd) // Shopping mode (focused single-store view) diff --git a/internal/handlers/handlers.go b/internal/handlers/handlers.go index c5df00d..147db07 100644 --- a/internal/handlers/handlers.go +++ b/internal/handlers/handlers.go @@ -715,6 +715,20 @@ func (h *Handler) HandleUnifiedAdd(w http.ResponseWriter, r *http.Request) { } _ = h.store.InvalidateCache(store.CacheKeyTrelloBoards) + case "shopping": + // The quick-action modal's Shopping tab. This used to create a Trello + // card on the Shopping board; that board is no longer a shopping + // source, so an item added there would have vanished from the list. + storeName := strings.TrimSpace(r.FormValue("store")) + if storeName == "" { + JSONError(w, http.StatusBadRequest, "Store is required for a shopping item", nil) + return + } + if err := h.store.SaveUserShoppingItem(title, storeName); err != nil { + JSONError(w, http.StatusInternalServerError, "Failed to save shopping item", err) + return + } + default: JSONError(w, http.StatusBadRequest, "Invalid source", nil) return diff --git a/internal/handlers/handlers_test.go b/internal/handlers/handlers_test.go index 61fb75d..7630e5d 100644 --- a/internal/handlers/handlers_test.go +++ b/internal/handlers/handlers_test.go @@ -480,11 +480,13 @@ func TestHandleShoppingModeComplete(t *testing.T) { } }) + // PlanToEat is the only remaining external source; Trello was removed as a + // shopping source, so completing one of its items is no longer meaningful. t.Run("complete external item marks it checked", func(t *testing.T) { req := httptest.NewRequest("POST", "/shopping/mode/TestStore/complete", nil) req.Form = map[string][]string{ - "id": {"trello-123"}, - "source": {"trello"}, + "id": {"pte-123"}, + "source": {"plantoeat"}, } rctx := chi.NewRouteContext() @@ -499,9 +501,9 @@ func TestHandleShoppingModeComplete(t *testing.T) { } // Verify item is marked as checked - checks, _ := db.GetShoppingItemChecks("trello") - if !checks["trello-123"] { - t.Error("Expected trello item to be marked as checked") + checks, _ := db.GetShoppingItemChecks("plantoeat") + if !checks["pte-123"] { + t.Error("Expected plantoeat item to be marked as checked") } }) } @@ -787,7 +789,8 @@ func TestShoppingComplete_ExternalItemMarkedChecked(t *testing.T) { source string itemID string }{ - {"trello", "trello-card-123"}, + // Trello is no longer a shopping source; plantoeat is the only + // external one whose items can be checked off. {"plantoeat", "pte-item-456"}, } diff --git a/internal/handlers/shopping.go b/internal/handlers/shopping.go index 1c58126..e06cad6 100644 --- a/internal/handlers/shopping.go +++ b/internal/handlers/shopping.go @@ -16,7 +16,7 @@ import ( "task-dashboard/internal/models" ) -// HandleTabShopping renders the Shopping tab (Trello Shopping board + PlanToEat + User items) +// HandleTabShopping renders the Shopping tab (PlanToEat + user-added items) func (h *Handler) HandleTabShopping(w http.ResponseWriter, r *http.Request) { ctx := r.Context() stores := h.aggregateShoppingLists(ctx) @@ -125,7 +125,7 @@ func (h *Handler) HandleShoppingModeComplete(w http.ResponseWriter, r *http.Requ JSONError(w, http.StatusInternalServerError, "Failed to delete item", err) return } - case "trello", "plantoeat": + case "plantoeat": // Mark as checked (will be filtered out of view) if err := h.store.SetShoppingItemChecked(source, id, true); err != nil { JSONError(w, http.StatusInternalServerError, "Failed to complete item", err) @@ -160,81 +160,34 @@ func (h *Handler) HandleShoppingModeComplete(w http.ResponseWriter, r *http.Requ }{storeName, items}) } -// HandleGetShoppingLists returns the lists from the Shopping board for quick-add -func (h *Handler) HandleGetShoppingLists(w http.ResponseWriter, r *http.Request) { - boards, err := h.store.GetBoards() - if err != nil { - JSONError(w, http.StatusInternalServerError, "Failed to get boards", err) - return - } - - var shoppingBoardID string - for _, b := range boards { - if strings.EqualFold(b.Name, "Shopping") { - shoppingBoardID = b.ID - break - } - } - - if shoppingBoardID == "" { - JSONError(w, http.StatusNotFound, "Shopping board not found", nil) - return - } - - lists, err := h.trelloClient.GetLists(r.Context(), shoppingBoardID) - if err != nil { - JSONError(w, http.StatusInternalServerError, "Failed to get lists", err) - return - } +// HandleGetShoppingStores returns the current store names as `, - template.HTMLEscapeString(list.ID), - template.HTMLEscapeString(list.Name)) + template.HTMLEscapeString(s.Name), + template.HTMLEscapeString(s.Name)) } } -// aggregateShoppingLists combines Trello, PlanToEat, and user shopping items by store +// aggregateShoppingLists combines PlanToEat and user shopping items by store func (h *Handler) aggregateShoppingLists(ctx context.Context) []models.ShoppingStore { storeMap := make(map[string]map[string][]models.UnifiedShoppingItem) - // Fetch locally stored checked states for external sources - trelloChecks, _ := h.store.GetShoppingItemChecks("trello") + // Fetch locally stored checked states for the remaining external source. + // Trello is no longer a shopping source: its items were read-only here and + // its list names were doubling as an accidental store registry. See + // migration 032 for the one-time import that preceded removing it. plantoeatChecks, _ := h.store.GetShoppingItemChecks("plantoeat") - // 1. Fetch Trello "Shopping" board cards - boards, err := h.store.GetBoards() - if err != nil { - log.Printf("ERROR [Shopping/Trello]: %v", err) - } else { - for _, board := range boards { - if strings.EqualFold(board.Name, "Shopping") { - log.Printf("DEBUG [Shopping]: Found Shopping board with %d cards", len(board.Cards)) - for _, card := range board.Cards { - storeName := card.ListName - if storeName == "" { - storeName = "Uncategorized" - } - - if storeMap[storeName] == nil { - storeMap[storeName] = make(map[string][]models.UnifiedShoppingItem) - } - - item := models.UnifiedShoppingItem{ - ID: card.ID, - Name: card.Name, - Store: storeName, - Source: "trello", - Checked: trelloChecks[card.ID], - } - storeMap[storeName][""] = append(storeMap[storeName][""], item) - } - } - } - } - - // 2. Fetch PlanToEat shopping list + // 1. Fetch PlanToEat shopping list if h.planToEatClient != nil { items, err := h.planToEatClient.GetShoppingList(ctx) if err != nil { @@ -272,7 +225,7 @@ func (h *Handler) aggregateShoppingLists(ctx context.Context) []models.ShoppingS log.Printf("DEBUG [Shopping/PlanToEat]: Client not configured") } - // 3. Fetch user-added shopping items + // 2. Fetch user-added shopping items userItems, err := h.store.GetUserShoppingItems() if err != nil { log.Printf("ERROR [Shopping/User]: %v", err) @@ -298,7 +251,7 @@ func (h *Handler) aggregateShoppingLists(ctx context.Context) []models.ShoppingS } } - // 4. Convert map to sorted slice + // 3. Convert map to sorted slice var stores []models.ShoppingStore for storeName, categories := range storeMap { store := models.ShoppingStore{Name: storeName} diff --git a/internal/handlers/shopping_sources_test.go b/internal/handlers/shopping_sources_test.go new file mode 100644 index 0000000..517f4b4 --- /dev/null +++ b/internal/handlers/shopping_sources_test.go @@ -0,0 +1,159 @@ +package handlers + +import ( + "context" + "net/http" + "net/http/httptest" + "net/url" + "strings" + "testing" + + "task-dashboard/internal/models" +) + +// The Trello "Shopping" board is no longer a source for the shopping list. It +// had become a dumping ground, its items were read-only in doot (no rename, no +// move, no delete — only hide), and its list names were doubling as an +// accidental store registry. The two groups worth keeping were imported into +// user items by migration 032; the rest simply stop being displayed. The cards +// themselves are untouched in Trello. +func TestAggregateShoppingLists_IgnoresTrelloShoppingBoard(t *testing.T) { + h, cleanup := setupTestHandler(t) + defer cleanup() + + if err := h.store.SaveBoards([]models.Board{{ + ID: "shop-board", + Name: "Shopping", + Cards: []models.Card{ + {ID: "c1", Name: "Broccoli", ListName: ""}, + {ID: "c2", Name: "Wasp traps", ListName: "Hardware Store"}, + {ID: "c3", Name: "Pearl Waffles", ListName: "Costco"}, + }, + }}); err != nil { + t.Fatalf("seeding boards: %v", err) + } + + stores := h.aggregateShoppingLists(context.Background()) + + for _, st := range stores { + for _, cat := range st.Categories { + for _, it := range cat.Items { + if it.Source == "trello" { + t.Errorf("trello item %q leaked into the shopping list (store %q)", it.Name, st.Name) + } + } + } + } +} + +// User-added items are still aggregated — this is the source that remains, so +// a regression here would empty the list entirely. +func TestAggregateShoppingLists_IncludesUserItems(t *testing.T) { + h, cleanup := setupTestHandler(t) + defer cleanup() + + if err := h.store.SaveUserShoppingItem("Milk", "Costco"); err != nil { + t.Fatalf("saving user item: %v", err) + } + + stores := h.aggregateShoppingLists(context.Background()) + + var found bool + for _, st := range stores { + for _, cat := range st.Categories { + for _, it := range cat.Items { + if it.Name == "Milk" && it.Source == "user" && st.Name == "Costco" { + found = true + } + } + } + } + if !found { + t.Errorf("user item not found in aggregated list; got %+v", stores) + } +} + +// A Shopping board with cards must not conjure stores into existence. Store +// names used to come partly from Trello list names, which is why removing the +// source changes which stores exist at all. +func TestAggregateShoppingLists_TrelloListsDoNotCreateStores(t *testing.T) { + h, cleanup := setupTestHandler(t) + defer cleanup() + + if err := h.store.SaveBoards([]models.Board{{ + ID: "shop-board", + Name: "Shopping", + Cards: []models.Card{ + {ID: "c1", Name: "Gas valve", ListName: "Hardware Store"}, + }, + }}); err != nil { + t.Fatalf("seeding boards: %v", err) + } + + for _, st := range h.aggregateShoppingLists(context.Background()) { + if st.Name == "Hardware Store" { + t.Error(`"Hardware Store" store exists only because of a Trello list name`) + } + } +} + +// The quick-action modal's Shopping tab posts source=shopping. It used to post +// source=trello with a list_id, which after the Trello removal would have +// created a card nobody could see. +func TestUnifiedAdd_ShoppingSourceCreatesUserItem(t *testing.T) { + h, cleanup := setupTestHandler(t) + defer cleanup() + + form := url.Values{"title": {"Sponges"}, "source": {"shopping"}, "store": {"Costco"}} + req := httptest.NewRequest(http.MethodPost, "/unified-add", strings.NewReader(form.Encode())) + req.Header.Set("Content-Type", "application/x-www-form-urlencoded") + rec := httptest.NewRecorder() + + h.HandleUnifiedAdd(rec, req) + + if rec.Code != http.StatusOK { + t.Fatalf("got status %d, want 200; body=%s", rec.Code, rec.Body.String()) + } + items, err := h.store.GetUserShoppingItems() + if err != nil { + t.Fatalf("reading items: %v", err) + } + if len(items) != 1 || items[0].Name != "Sponges" || items[0].Store != "Costco" { + t.Errorf("got %+v, want one Sponges/Costco item", items) + } +} + +func TestUnifiedAdd_ShoppingRequiresAStore(t *testing.T) { + h, cleanup := setupTestHandler(t) + defer cleanup() + + form := url.Values{"title": {"Sponges"}, "source": {"shopping"}} + req := httptest.NewRequest(http.MethodPost, "/unified-add", strings.NewReader(form.Encode())) + req.Header.Set("Content-Type", "application/x-www-form-urlencoded") + rec := httptest.NewRecorder() + + h.HandleUnifiedAdd(rec, req) + + if rec.Code != http.StatusBadRequest { + t.Errorf("got status %d, want 400 when no store is given", rec.Code) + } +} + +// The store picker must offer the stores that actually exist. +func TestHandleGetShoppingStores_ReturnsOptions(t *testing.T) { + h, cleanup := setupTestHandler(t) + defer cleanup() + + _ = h.store.SaveUserShoppingItem("Milk", "Costco") + _ = h.store.SaveUserShoppingItem("Nails", "Hardware Store") + + rec := httptest.NewRecorder() + h.HandleGetShoppingStores(rec, httptest.NewRequest(http.MethodGet, "/partials/shopping-stores", nil)) + + body := rec.Body.String() + for _, want := range []string{``, ``} { + if !strings.Contains(body, want) { + t.Errorf("missing %q in %q", want, body) + } + } +} diff --git a/internal/store/shopping_import_test.go b/internal/store/shopping_import_test.go new file mode 100644 index 0000000..0e246d8 --- /dev/null +++ b/internal/store/shopping_import_test.go @@ -0,0 +1,229 @@ +package store + +import ( + "database/sql" + "os" + "path/filepath" + "sort" + "testing" + + _ "github.com/mattn/go-sqlite3" +) + +// Runs migrations/032 against a database seeded with a realistic snapshot of +// the Trello Shopping board, and asserts it imports exactly the two groups +// that were asked for and leaves the rest behind. +// +// The migration is executed from the real file rather than a copy, so the +// thing under test is what actually ships. +func TestMigration032_ImportsOnlyHardwareStoreAndUncategorized(t *testing.T) { + db := setupImportTestDB(t) + defer func() { _ = db.Close() }() + + seedShoppingBoard(t, db, []card{ + // Unlisted cards — these render as "Uncategorized" and are imported. + {"Broccoli", ""}, + {"Ibuprofen", ""}, + {"dog water bowl", ""}, + // Hardware Store — imported. + {"6x8 plywood", "Hardware Store"}, + {"Wasp traps", "Hardware Store"}, + // Deliberately left behind. + {"Pearl Waffles", "Costco"}, + {"Pita Bread", "Perennial "}, + {"Rip stop", "online"}, + }) + + runMigration032(t, db) + + got := userItems(t, db) + want := []item{ + {"6x8 plywood", "Hardware Store"}, + {"Broccoli", "Uncategorized"}, + {"Ibuprofen", "Uncategorized"}, + {"Wasp traps", "Hardware Store"}, + {"dog water bowl", "Uncategorized"}, + } + assertItems(t, got, want) +} + +// A card whose list has surrounding whitespace must not create a store that +// differs from the trimmed name only by invisible characters. "Perennial " on +// the real board was exactly this. +func TestMigration032_TrimsStoreNames(t *testing.T) { + db := setupImportTestDB(t) + defer func() { _ = db.Close() }() + + seedShoppingBoard(t, db, []card{ + {"Trimmed", " Hardware Store "}, + }) + runMigration032(t, db) + + got := userItems(t, db) + assertItems(t, got, []item{{"Trimmed", "Hardware Store"}}) +} + +// Cards on other boards must not be swept in — only the Shopping board. +func TestMigration032_IgnoresOtherBoards(t *testing.T) { + db := setupImportTestDB(t) + defer func() { _ = db.Close() }() + + seedBoard(t, db, "camping", "Camping") + mustExec(t, db, `INSERT INTO cards (id, name, board_id, list_name) VALUES ('c1','Tent pegs','camping','')`) + seedShoppingBoard(t, db, []card{{"Broccoli", ""}}) + + runMigration032(t, db) + + got := userItems(t, db) + assertItems(t, got, []item{{"Broccoli", "Uncategorized"}}) +} + +// Replaying the migration must not duplicate rows. +func TestMigration032_IsReplaySafe(t *testing.T) { + db := setupImportTestDB(t) + defer func() { _ = db.Close() }() + + seedShoppingBoard(t, db, []card{{"Broccoli", ""}, {"Wasp traps", "Hardware Store"}}) + + runMigration032(t, db) + runMigration032(t, db) + + got := userItems(t, db) + assertItems(t, got, []item{ + {"Broccoli", "Uncategorized"}, + {"Wasp traps", "Hardware Store"}, + }) +} + +// An item the user already added by hand must not gain a duplicate. +func TestMigration032_SkipsItemsAlreadyPresent(t *testing.T) { + db := setupImportTestDB(t) + defer func() { _ = db.Close() }() + + mustExec(t, db, `INSERT INTO user_shopping_items (name, store) VALUES ('Wasp traps','Hardware Store')`) + seedShoppingBoard(t, db, []card{{"Wasp traps", "Hardware Store"}}) + + runMigration032(t, db) + + assertItems(t, userItems(t, db), []item{{"Wasp traps", "Hardware Store"}}) +} + +// On a database with no cached Trello cards the migration is a no-op rather +// than an error — that is the state of any fresh dev database. +func TestMigration032_NoOpWithNoCards(t *testing.T) { + db := setupImportTestDB(t) + defer func() { _ = db.Close() }() + + runMigration032(t, db) + + if got := userItems(t, db); len(got) != 0 { + t.Errorf("expected no items, got %v", got) + } +} + +// ── helpers ────────────────────────────────────────────────────────────────── + +type card struct{ name, listName string } +type item struct{ Name, Store string } + +func setupImportTestDB(t *testing.T) *sql.DB { + t.Helper() + db, err := sql.Open("sqlite3", filepath.Join(t.TempDir(), "import.db")) + if err != nil { + t.Fatalf("open test db: %v", err) + } + db.SetMaxOpenConns(1) + mustExec(t, db, ` + CREATE TABLE boards (id TEXT PRIMARY KEY, name TEXT NOT NULL); + CREATE TABLE cards ( + id TEXT PRIMARY KEY, + name TEXT NOT NULL, + board_id TEXT NOT NULL, + list_name TEXT + ); + CREATE TABLE user_shopping_items ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + name TEXT NOT NULL, + store TEXT NOT NULL, + checked INTEGER DEFAULT 0, + created_at DATETIME DEFAULT CURRENT_TIMESTAMP + ); + `) + return db +} + +func seedBoard(t *testing.T, db *sql.DB, id, name string) { + t.Helper() + mustExec(t, db, `INSERT INTO boards (id, name) VALUES (?, ?)`, id, name) +} + +func seedShoppingBoard(t *testing.T, db *sql.DB, cards []card) { + t.Helper() + seedBoard(t, db, "shop", "Shopping") + for i, c := range cards { + mustExec(t, db, + `INSERT INTO cards (id, name, board_id, list_name) VALUES (?, ?, 'shop', ?)`, + "card-"+string(rune('a'+i)), c.name, c.listName) + } +} + +func runMigration032(t *testing.T, db *sql.DB) { + t.Helper() + path := filepath.Join("..", "..", "migrations", "032_import_trello_shopping_items.sql") + sqlBytes, err := os.ReadFile(path) + if err != nil { + t.Fatalf("reading migration: %v", err) + } + if _, err := db.Exec(string(sqlBytes)); err != nil { + t.Fatalf("executing migration: %v", err) + } +} + +func userItems(t *testing.T, db *sql.DB) []item { + t.Helper() + rows, err := db.Query(`SELECT name, store FROM user_shopping_items`) + if err != nil { + t.Fatalf("querying items: %v", err) + } + defer func() { _ = rows.Close() }() + + var out []item + for rows.Next() { + var it item + if err := rows.Scan(&it.Name, &it.Store); err != nil { + t.Fatalf("scan: %v", err) + } + out = append(out, it) + } + return out +} + +func assertItems(t *testing.T, got, want []item) { + t.Helper() + sortItems(got) + sortItems(want) + if len(got) != len(want) { + t.Fatalf("got %d items %v, want %d %v", len(got), got, len(want), want) + } + for i := range got { + if got[i] != want[i] { + t.Errorf("item %d: got %+v, want %+v", i, got[i], want[i]) + } + } +} + +func sortItems(items []item) { + sort.Slice(items, func(i, j int) bool { + if items[i].Store != items[j].Store { + return items[i].Store < items[j].Store + } + return items[i].Name < items[j].Name + }) +} + +func mustExec(t *testing.T, db *sql.DB, query string, args ...any) { + t.Helper() + if _, err := db.Exec(query, args...); err != nil { + t.Fatalf("exec %q: %v", query, err) + } +} diff --git a/migrations/032_import_trello_shopping_items.sql b/migrations/032_import_trello_shopping_items.sql new file mode 100644 index 0000000..a12a675 --- /dev/null +++ b/migrations/032_import_trello_shopping_items.sql @@ -0,0 +1,46 @@ +-- One-time import of Trello Shopping-board cards into user shopping items, +-- ahead of removing the Trello source from the shopping list entirely. +-- +-- The Trello "Shopping" board had become a dumping ground: of its 24 cards, 13 +-- had no list at all (rendering as "Uncategorized") and the rest were spread +-- over Costco, Hardware Store, "Perennial " (note the trailing space, which +-- made it a separate store from "Perennial") and online. +-- +-- Only the two groups worth keeping are imported: Hardware Store, and the +-- unlisted cards. Costco, Perennial and online cards are deliberately left +-- behind — they stop appearing once the Trello source is removed. Nothing is +-- destroyed by that: the cards remain in Trello, this only stops doot +-- displaying them. +-- +-- Imported rows become ordinary user items, which means they also become fully +-- editable (rename, move store, delete) — Trello-sourced items never were. +-- +-- Reads from the local `cards` cache rather than the Trello API. On any +-- database where that cache is empty (a fresh dev DB, a restore predating a +-- sync) this is simply a no-op, which is the correct outcome there. + +INSERT INTO user_shopping_items (name, store) +SELECT + c.name, + CASE + WHEN TRIM(COALESCE(c.list_name, '')) = '' THEN 'Uncategorized' + ELSE TRIM(c.list_name) + END AS store_name +FROM cards c +JOIN boards b ON c.board_id = b.id +WHERE LOWER(b.name) = 'shopping' + AND ( + TRIM(COALESCE(c.list_name, '')) = '' + OR LOWER(TRIM(c.list_name)) = 'hardware store' + ) + -- Guard against creating a duplicate of something already added by hand. + -- Migrations are tracked by filename so this runs once, but the guard makes + -- the statement safe to replay against a partially-imported database. + AND NOT EXISTS ( + SELECT 1 FROM user_shopping_items u + WHERE u.name = c.name + AND u.store = CASE + WHEN TRIM(COALESCE(c.list_name, '')) = '' THEN 'Uncategorized' + ELSE TRIM(c.list_name) + END + ); diff --git a/migrations/README.md b/migrations/README.md index e849b9c..a98efa2 100644 --- a/migrations/README.md +++ b/migrations/README.md @@ -27,10 +27,11 @@ Migration 007 was reserved during development but never implemented. The gap is ## Running Migrations Migrations are applied automatically on application startup via `store.RunMigrations()`. The `schema_migrations` table tracks which migrations have been applied. +| 032 | `032_import_trello_shopping_items.sql` | One-time import of Trello Shopping-board cards (Hardware Store + unlisted) into user items, ahead of removing Trello as a shopping source | ## Creating New Migrations -1. Create file: `migrations/NNN_description.sql` (next number is 014) +1. Create file: `migrations/NNN_description.sql` (next number is 033) 2. Write idempotent SQL (use `IF NOT EXISTS` where possible) 3. Test locally: `go run cmd/dashboard/main.go` 4. Verify: Check `schema_migrations` table for new entry diff --git a/web/templates/index.html b/web/templates/index.html index b11b557..f58c5e1 100644 --- a/web/templates/index.html +++ b/web/templates/index.html @@ -189,14 +189,14 @@
- + -