1 files changed, 46 insertions, 0 deletions
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
+      );