PackIt/schema.sql

-- PackIt Database Schema
-- Run this once to set up all required tables.

CREATE DATABASE IF NOT EXISTS packit CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE packit;

-- Activity groups (categories)
CREATE TABLE IF NOT EXISTS activity_groups (
    groupID   INT AUTO_INCREMENT PRIMARY KEY,
    groupName VARCHAR(100) NOT NULL,
    sortOrder INT DEFAULT 0
);

-- Activities
CREATE TABLE IF NOT EXISTS activities (
    activityID   INT AUTO_INCREMENT PRIMARY KEY,
    groupID      INT NOT NULL,
    activityName VARCHAR(150) NOT NULL,
    sortOrder    INT DEFAULT 0,
    FOREIGN KEY (groupID) REFERENCES activity_groups(groupID) ON DELETE CASCADE
);

-- Item groups
CREATE TABLE IF NOT EXISTS item_groups (
    groupID   INT AUTO_INCREMENT PRIMARY KEY,
    groupName VARCHAR(100) NOT NULL,
    sortOrder INT DEFAULT 0
);

-- Items
CREATE TABLE IF NOT EXISTS items (
    itemID    INT AUTO_INCREMENT PRIMARY KEY,
    groupID   INT NOT NULL,
    itemName  VARCHAR(150) NOT NULL,
    sortOrder INT DEFAULT 0,
    FOREIGN KEY (groupID) REFERENCES item_groups(groupID) ON DELETE CASCADE
);

-- Relation between activities and items
CREATE TABLE IF NOT EXISTS activity_item_map (
    activityID INT NOT NULL,
    itemID     INT NOT NULL,
    PRIMARY KEY (activityID, itemID),
    FOREIGN KEY (activityID) REFERENCES activities(activityID) ON DELETE CASCADE,
    FOREIGN KEY (itemID)     REFERENCES items(itemID)          ON DELETE CASCADE
);

-- Stored selection hashes (for shareable URLs)
CREATE TABLE IF NOT EXISTS selection_hashes (
    hashID       INT AUTO_INCREMENT PRIMARY KEY,
    hash         CHAR(64) NOT NULL UNIQUE,
    activity_ids TEXT NOT NULL,
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Admin users
CREATE TABLE IF NOT EXISTS admin_users (
    adminID       INT AUTO_INCREMENT PRIMARY KEY,
    username      VARCHAR(50)  NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL
);

-- -----------------------------------------------
-- Sample data
-- -----------------------------------------------

INSERT INTO activity_groups (groupName, sortOrder) VALUES
('Overnight',        1),   -- groupID 1
('Rescue & Safety',  2),   -- groupID 2
('Water Activities', 3),   -- groupID 3
('Winter Sports',    4);   -- groupID 4

-- activityID 1=Camping 2=Hiking 3=Bivouac 4=First Aid 5=Mountain Rescue
-- activityID 6=Kayaking 7=Swimming 8=Skiing 9=Snowshoeing
INSERT INTO activities (groupID, activityName, sortOrder) VALUES
(1, 'Camping',         1),
(1, 'Hiking',          2),
(1, 'Bivouac',         3),
(2, 'First Aid',       1),
(2, 'Mountain Rescue', 2),
(3, 'Kayaking',        1),
(3, 'Swimming',        2),
(4, 'Skiing',          1),
(4, 'Snowshoeing',     2);

INSERT INTO item_groups (groupName, sortOrder) VALUES
('Clothing',     1),   -- groupID 1
('Shelter',      2),   -- groupID 2
('Navigation',   3),   -- groupID 3
('Medical',      4),   -- groupID 4
('Water & Food', 5),   -- groupID 5
('Safety Gear',  6);   -- groupID 6

-- Items with their resulting auto_increment IDs:
--  1  Base layer top        (Clothing)
--  2  Base layer bottom     (Clothing)
--  3  Fleece jacket         (Clothing)
--  4  Rain jacket           (Clothing)
--  5  Gloves                (Clothing)
--  6  Warm hat              (Clothing)
--  7  Hiking boots          (Clothing)
--  8  Wetsuit               (Clothing)
--  9  Ski jacket            (Clothing)
-- 10  Ski pants             (Clothing)
-- 11  Ski boots             (Clothing)
-- 12  Tent                  (Shelter)
-- 13  Sleeping bag          (Shelter)
-- 14  Sleeping mat          (Shelter)
-- 15  Bivouac bag           (Shelter)
-- 16  Topographic map       (Navigation)
-- 17  Compass               (Navigation)
-- 18  GPS device            (Navigation)
-- 19  First aid kit         (Medical)
-- 20  Emergency blanket     (Medical)
-- 21  Tourniquet            (Medical)
-- 22  SAM splint            (Medical)
-- 23  Water bottle          (Water & Food)
-- 24  Water filter          (Water & Food)
-- 25  Trail snacks          (Water & Food)
-- 26  Stove & fuel          (Water & Food)
-- 27  Cooking pot           (Water & Food)
-- 28  Headlamp + batteries  (Safety Gear)
-- 29  Whistle               (Safety Gear)
-- 30  Rope (30m)            (Safety Gear)
-- 31  Avalanche probe       (Safety Gear)
-- 32  Avalanche transceiver (Safety Gear)
-- 33  Shovel                (Safety Gear)
-- 34  Life jacket           (Safety Gear)
-- 35  Paddle float          (Safety Gear)

INSERT INTO items (groupID, itemName, sortOrder) VALUES
(1, 'Base layer top',             1),
(1, 'Base layer bottom',          2),
(1, 'Fleece jacket',              3),
(1, 'Rain jacket',                4),
(1, 'Gloves',                     5),
(1, 'Warm hat',                   6),
(1, 'Hiking boots',               7),
(1, 'Wetsuit',                    8),
(1, 'Ski jacket',                 9),
(1, 'Ski pants',                 10),
(1, 'Ski boots',                 11),
(2, 'Tent',                       1),
(2, 'Sleeping bag',               2),
(2, 'Sleeping mat',               3),
(2, 'Bivouac bag',                4),
(3, 'Topographic map',            1),
(3, 'Compass',                    2),
(3, 'GPS device',                 3),
(4, 'First aid kit',              1),
(4, 'Emergency blanket',          2),
(4, 'Tourniquet',                 3),
(4, 'SAM splint',                 4),
(5, 'Water bottle',               1),
(5, 'Water filter',               2),
(5, 'Trail snacks',               3),
(5, 'Stove & fuel',               4),
(5, 'Cooking pot',                5),
(6, 'Headlamp + spare batteries', 1),
(6, 'Whistle',                    2),
(6, 'Rope (30m)',                 3),
(6, 'Avalanche probe',            4),
(6, 'Avalanche transceiver',      5),
(6, 'Shovel',                     6),
(6, 'Life jacket',                7),
(6, 'Paddle float',               8);

-- -----------------------------------------------
-- Activity -> Item mappings (all IDs verified)
-- -----------------------------------------------

-- activityID 1: Camping
INSERT INTO activity_item_map (activityID, itemID) VALUES
(1, 1),(1, 2),(1, 3),(1, 4),(1, 7),
(1,12),(1,13),(1,14),
(1,16),(1,17),
(1,19),(1,20),
(1,23),(1,24),(1,25),(1,26),(1,27),
(1,28),(1,29);

-- activityID 2: Hiking
INSERT INTO activity_item_map (activityID, itemID) VALUES
(2, 1),(2, 2),(2, 3),(2, 4),(2, 7),
(2,16),(2,17),(2,18),
(2,19),(2,20),
(2,23),(2,24),(2,25),
(2,28),(2,29);

-- activityID 3: Bivouac
INSERT INTO activity_item_map (activityID, itemID) VALUES
(3, 1),(3, 2),(3, 3),(3, 4),(3, 5),(3, 6),(3, 7),
(3,14),(3,15),
(3,16),(3,17),
(3,19),(3,20),
(3,23),(3,25),
(3,28),(3,29);

-- activityID 4: First Aid
INSERT INTO activity_item_map (activityID, itemID) VALUES
(4,19),(4,20),(4,21),(4,22),
(4,28),(4,29);

-- activityID 5: Mountain Rescue
INSERT INTO activity_item_map (activityID, itemID) VALUES
(5, 1),(5, 3),(5, 4),(5, 5),(5, 6),
(5,16),(5,17),(5,18),
(5,19),(5,20),(5,21),(5,22),
(5,28),(5,29),(5,30);

-- activityID 6: Kayaking
INSERT INTO activity_item_map (activityID, itemID) VALUES
(6, 1),(6, 4),(6, 8),
(6,19),(6,20),
(6,23),(6,24),
(6,28),(6,29),
(6,34),(6,35);

-- activityID 7: Swimming
INSERT INTO activity_item_map (activityID, itemID) VALUES
(7, 8),
(7,19),(7,20),
(7,34);

-- activityID 8: Skiing
INSERT INTO activity_item_map (activityID, itemID) VALUES
(8, 1),(8, 2),(8, 5),(8, 6),(8, 9),(8,10),(8,11),
(8,16),(8,17),(8,18),
(8,19),(8,20),
(8,23),(8,25),
(8,28),(8,29),
(8,31),(8,32),(8,33);

-- activityID 9: Snowshoeing
INSERT INTO activity_item_map (activityID, itemID) VALUES
(9, 1),(9, 2),(9, 3),(9, 4),(9, 5),(9, 6),(9, 7),
(9,16),(9,17),
(9,19),(9,20),
(9,23),(9,24),(9,25),
(9,28),(9,29),
(9,31);

-- -----------------------------------------------
-- Default admin user
-- Username: admin   Password: packit-admin
-- Change the password after first login!
-- -----------------------------------------------
INSERT INTO admin_users (username, password_hash) VALUES
('admin', '$2y$12$eTfGi/R5IxHH5NQxrVe9.OijNfyFfpvT/X1i9aYgqZPRF6.Gu72hC');