1 files changed, 41 insertions, 0 deletions
diff --git a/migrations/034_shopping_stores_and_sections.sql b/migrations/034_shopping_stores_and_sections.sql
new file mode 100644
index 0000000..2b47846
--- /dev/null
+++ b/migrations/034_shopping_stores_and_sections.sql
@@ -0,0 +1,41 @@
+-- A real registry for shopping stores and sections.
+--
+-- Until now a store existed only because some item referenced it by name.
+-- Trello's list names were quietly acting as the registry; removing Trello as
+-- a shopping source (migration 032) made the gap visible. Without this you
+-- cannot move an item to a store that is currently empty, and moving the last
+-- item out of a store makes that store vanish mid-edit.
+--
+-- Sections are the same idea one level down. They previously existed only as
+-- PlanToEat's category strings, so a user item could not be filed under one at
+-- all.
+
+CREATE TABLE IF NOT EXISTS 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 IF NOT EXISTS 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)
+);
+
+CREATE INDEX IF NOT EXISTS idx_shopping_sections_store ON shopping_sections(store_id);
+
+-- Items gain a section. Store stays a name rather than a foreign key: items
+-- also arrive from PlanToEat carrying a store name that may not be registered
+-- yet, and a NOT NULL reference would mean either dropping those items or
+-- auto-creating stores from scraped data.
+ALTER TABLE user_shopping_items ADD COLUMN section TEXT NOT NULL DEFAULT '';
+
+-- Seed the registry from the stores that currently exist, so nothing
+-- disappears the moment the registry becomes authoritative.
+INSERT OR IGNORE INTO shopping_stores (name)
+SELECT DISTINCT TRIM(store) FROM user_shopping_items
+WHERE TRIM(COALESCE(store, '')) <> '';