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,
    quantity   INT NOT NULL DEFAULT 1,
    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)
-- hash is a 5-character SHA-256 prefix, grown one character at a time by
-- list.php when a shorter prefix is already taken by a different selection.
CREATE TABLE IF NOT EXISTS selection_hashes (
    hashID       INT AUTO_INCREMENT PRIMARY KEY,
    hash         VARCHAR(16) NOT NULL UNIQUE,
    activity_ids VARCHAR(500) NOT NULL,
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_activity_ids (activity_ids)
);

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

-- Failed admin logins, used by admin/login.php to throttle brute-force
-- attempts. Rows older than a day are pruned opportunistically on each write.
CREATE TABLE IF NOT EXISTS login_attempts (
    attemptID    INT AUTO_INCREMENT PRIMARY KEY,
    ip           VARCHAR(45) NOT NULL,
    username     VARCHAR(50) NOT NULL,
    attempted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_ip_time   (ip, attempted_at),
    INDEX idx_user_time (username, attempted_at)
);

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