peworkshop/fill-database.sql

-- Schema for clue_hunt.db
DROP TABLE IF EXISTS clues;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS teams;
DROP TABLE IF EXISTS game_settings;

CREATE TABLE users (
                       id INTEGER PRIMARY KEY AUTOINCREMENT,
                       username TEXT UNIQUE NOT NULL,
                       password TEXT NOT NULL,
                       role TEXT NOT NULL DEFAULT 'user',
                       team_id INTEGER, -- Ensure this column exists
                       created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                       FOREIGN KEY (team_id) REFERENCES teams(id)
);

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

CREATE TABLE clues (
                       id INTEGER PRIMARY KEY AUTOINCREMENT,
                       description TEXT NOT NULL,
                       team_id INTEGER NOT NULL,
                       found BOOLEAN DEFAULT FALSE,
                       found_at TIMESTAMP,
                       created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                       FOREIGN KEY (team_id) REFERENCES teams(id)
);

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 INTO users (username, password, role)
VALUES ('admin', '$2a$10$N9qo8uLOickgx2ZMRZoMyeIjZAgcfl7p92ldGxad68LJZdL17lhWy', 'admin');

INSERT INTO teams (name, code) VALUES ('Team Alpha', 'ALPHA123');
INSERT INTO teams (name, code) VALUES ('Team Beta', 'BETA456');
INSERT INTO teams (name, code) VALUES ('Team Gamma', 'GAMMA789');

INSERT INTO clues (description, team_id) VALUES ('Find the red door', 1);
INSERT INTO clues (description, team_id) VALUES ('Look under the bridge', 1);
INSERT INTO clues (description, team_id) VALUES ('Check the library', 1);