-- 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
      );