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