-- Migration 003 – German item list for the hidden Zombie apocalypse activity.
-- Run once, after 002, against an existing PackIt database:
--     mysql -u <user> -p <your-database> < migrations/003_zombie_items_german.sql
-- New installs get all of this from schema.sql and can skip it.
--
-- 002 looked item groups and items up by the English sample-data names, so on
-- a database with its own (e.g. German) groups the activity ended up empty.
-- This rebuilds the list without depending on any particular group existing:
-- an item whose group is missing lands in the new "Zombie-Abwehr" group.
-- Safe to run more than once.

-- 1. Activity and its group in German.
SET @zombie = (SELECT activityID FROM activities
               WHERE hidden = 1 AND activityName IN ('Zombie apocalypse', 'Zombie-Apokalypse')
               ORDER BY activityID LIMIT 1);

UPDATE activities SET activityName = 'Zombie-Apokalypse' WHERE activityID = @zombie;
UPDATE activity_groups ag
JOIN activities a ON a.groupID = ag.groupID AND a.activityID = @zombie
SET ag.groupName = 'Überleben'
WHERE ag.groupName = 'Survival';

-- 2. Start the list over. The English items 002 created are only removed when
--    no other activity uses them.
DELETE FROM activity_item_map WHERE activityID = @zombie;
DELETE FROM items
WHERE itemName IN ('Running shoes (cardio is rule #1)', 'Bite-proof arm guards',
                   'Map with safe zones marked', 'Canned beans', 'Manual can opener',
                   'Cricket bat', 'Crowbar', 'Duct tape', 'Walkie-talkies')
  AND itemID NOT IN (SELECT itemID FROM activity_item_map);

-- 3. Item group for the gear no normal trip needs. The guard sits in HAVING:
--    an aggregate still yields a row when WHERE filters every row out.
INSERT INTO item_groups (groupName, sortOrder)
SELECT 'Zombie-Abwehr', COALESCE(MAX(sortOrder), 0) + 1 FROM item_groups
HAVING SUM(groupName = 'Zombie-Abwehr') = 0;
SET @zgroup = (SELECT groupID FROM item_groups WHERE groupName = 'Zombie-Abwehr'
               ORDER BY groupID LIMIT 1);

-- 4. The list. altName is the English sample-data name of an item that may
--    already exist; an item found under either name is reused, not duplicated.
CREATE TEMPORARY TABLE zombie_list (
    itemName  VARCHAR(150) NOT NULL,
    altName   VARCHAR(150) NULL,
    groupName VARCHAR(100) NOT NULL,
    quantity  INT          NOT NULL
);
INSERT INTO zombie_list (itemName, altName, groupName, quantity) VALUES
('Laufschuhe (Regel Nr. 1: Cardio)',        NULL,                         'Kleidung',      1),
('Bissfeste Armschützer (Paar)',            NULL,                         'Kleidung',      1),
('Schlafsack',                              'Sleeping bag',               'Kleidung',      1),
('Dosenbohnen',                             NULL,                         'Essen',         12),
('Twinkies (letzte Packung der Welt)',      NULL,                         'Essen',         1),
('Trinken (1 l)',                           'Water bottle',               'Essen',         2),
('Dosenöffner (manuell)',                   NULL,                         'Küche',         1),
('Feuerzeug',                               NULL,                         'Küche',         1),
('Stirnlampe',                              'Headlamp + spare batteries', 'Elektronik',    1),
('Funkgerät',                               NULL,                         'Elektronik',    2),
('Kurbelradio',                             NULL,                         'Elektronik',    1),
('EH-Set',                                  'First aid kit',              'Sicherheit',    1),
('Multitool',                               NULL,                         'Sicherheit',    1),
('Karte mit sicheren Zonen',                NULL,                         'Kleinzeug',     1),
('Zombie-Wörterbuch (Grrr = Hallo)',        NULL,                         'Kleinzeug',     1),
('Glaubwürdige Ausrede für die Bisswunde',  NULL,                         'Kleinzeug',     1),
('Cricketschläger',                         NULL,                         'Zombie-Abwehr', 1),
('Brecheisen',                              NULL,                         'Zombie-Abwehr', 1),
('Panzertape',                              NULL,                         'Zombie-Abwehr', 1),
('Bolzenschneider',                         NULL,                         'Zombie-Abwehr', 1),
('Knoblauch (hilft nicht, beruhigt aber)',  NULL,                         'Zombie-Abwehr', 1),
('Gartenzwerg zur Ablenkung',               NULL,                         'Zombie-Abwehr', 1);

INSERT INTO items (groupID, itemName, sortOrder)
SELECT COALESCE((SELECT ig.groupID FROM item_groups ig
                 WHERE ig.groupName = z.groupName ORDER BY ig.groupID LIMIT 1), @zgroup),
       z.itemName, 0
FROM zombie_list z
WHERE NOT EXISTS (SELECT 1 FROM items i WHERE i.itemName IN (z.itemName, z.altName));

INSERT INTO activity_item_map (activityID, itemID, quantity)
SELECT @zombie, MIN(i.itemID), z.quantity
FROM zombie_list z
JOIN items i ON i.itemName IN (z.itemName, z.altName)
GROUP BY z.itemName, z.quantity;

DROP TEMPORARY TABLE zombie_list;