peworkshop/init-schema.sql

-- Schema for clue_hunt.db

-- Drop tables if they exist (in reverse order of dependencies)
DROP TABLE IF EXISTS team_clues;
DROP TABLE IF EXISTS progress;
DROP TABLE IF EXISTS clues;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS teams;
DROP TABLE IF EXISTS hunts;
DROP TABLE IF EXISTS game_settings;

-- Create teams table
CREATE TABLE teams (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    code TEXT NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create users table
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    role TEXT DEFAULT 'user' NOT NULL,
    team_id INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE SET NULL
);

-- Create clues table
CREATE TABLE clues (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    description TEXT NOT NULL,
    location TEXT,
    qr_code TEXT DEFAULT '',
    found BOOLEAN DEFAULT FALSE,
    skipped BOOLEAN DEFAULT FALSE,
    found_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create team_clues table (for tracking which teams have found which clues)
CREATE TABLE team_clues (
    team_id INTEGER NOT NULL,
    clue_id INTEGER NOT NULL,
    found_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (team_id, clue_id),
    FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE,
    FOREIGN KEY (clue_id) REFERENCES clues(id) ON DELETE CASCADE
);

-- Create progress table
CREATE TABLE progress (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    team_id INTEGER,
    clue_id INTEGER,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (team_id) REFERENCES teams(id),
    FOREIGN KEY (clue_id) REFERENCES clues(id)
);

-- Create hunts table
CREATE TABLE hunts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    active BOOLEAN DEFAULT 0,
    start_time DATETIME DEFAULT NULL,
    end_time DATETIME DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create game_settings table
CREATE TABLE game_settings (
    id INTEGER PRIMARY KEY,
    hunt_active BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert a default hunt row (only structural setup, no custom values)
INSERT INTO hunts (active) SELECT 0 WHERE NOT EXISTS (SELECT 1 FROM hunts LIMIT 1);