-- Migration 002 – hidden (easter egg) activities + the Zombie apocalypse list.
-- Run once against an existing PackIt database:
--     mysql -u <user> -p <your-database> < migrations/002_hidden_activities.sql
-- New installs get all of this from schema.sql and can skip it.

-- 1. Hidden activities are left off the selection page until revealed.
ALTER TABLE activities ADD COLUMN hidden TINYINT(1) NOT NULL DEFAULT 0;

-- 2. The Zombie apocalypse activity in its own group.
INSERT INTO activity_groups (groupName, sortOrder)
SELECT 'Survival', COALESCE(MAX(sortOrder), 0) + 1 FROM activity_groups;
SET @survivalGroup = LAST_INSERT_ID();

INSERT INTO activities (groupID, activityName, sortOrder, hidden)
VALUES (@survivalGroup, 'Zombie apocalypse', 1, 1);
SET @zombie = LAST_INSERT_ID();

-- 3. Items only this list needs. Item groups are looked up by their sample-data
--    names; an item whose group was renamed or removed is skipped rather than
--    failing the migration, and can be added through the admin panel instead.
INSERT INTO items (groupID, itemName, sortOrder)
SELECT ig.groupID, n.itemName, n.sortOrder
FROM (
    SELECT 'Clothing'     AS groupName, 'Running shoes (cardio is rule #1)' AS itemName, 12 AS sortOrder
    UNION ALL SELECT 'Clothing',     'Bite-proof arm guards',      13
    UNION ALL SELECT 'Navigation',   'Map with safe zones marked',  4
    UNION ALL SELECT 'Water & Food', 'Canned beans',                6
    UNION ALL SELECT 'Water & Food', 'Manual can opener',           7
    UNION ALL SELECT 'Safety Gear',  'Cricket bat',                 9
    UNION ALL SELECT 'Safety Gear',  'Crowbar',                    10
    UNION ALL SELECT 'Safety Gear',  'Duct tape',                  11
    UNION ALL SELECT 'Safety Gear',  'Walkie-talkies',             12
) n
JOIN item_groups ig ON ig.groupName = n.groupName
WHERE NOT EXISTS (SELECT 1 FROM items i WHERE i.itemName = n.itemName);

-- 4. Map new and existing items to the activity by name. MIN(itemID) guards
--    against an install that happens to hold two items with the same name.
INSERT INTO activity_item_map (activityID, itemID, quantity)
SELECT @zombie, MIN(i.itemID), n.quantity
FROM (
    SELECT 'Running shoes (cardio is rule #1)' AS itemName, 1 AS quantity
    UNION ALL SELECT 'Bite-proof arm guards',       2
    UNION ALL SELECT 'Sleeping bag',                1
    UNION ALL SELECT 'Map with safe zones marked',  1
    UNION ALL SELECT 'Compass',                     1
    UNION ALL SELECT 'First aid kit',               1
    UNION ALL SELECT 'Water bottle',                2
    UNION ALL SELECT 'Water filter',                1
    UNION ALL SELECT 'Canned beans',               12
    UNION ALL SELECT 'Manual can opener',           1
    UNION ALL SELECT 'Headlamp + spare batteries',  1
    UNION ALL SELECT 'Rope (30m)',                  1
    UNION ALL SELECT 'Cricket bat',                 1
    UNION ALL SELECT 'Crowbar',                     1
    UNION ALL SELECT 'Duct tape',                   1
    UNION ALL SELECT 'Walkie-talkies',              2
) n
JOIN items i ON i.itemName = n.itemName
GROUP BY n.itemName, n.quantity;