mirror of
https://github.com/mudabbir-ahmad/peworkshop.git
synced 2026-10-07 19:50:20 +00:00
63 lines
5.0 KiB
XML
63 lines
5.0 KiB
XML
<?xml version="1.0" encoding="UTF-8"?><sqlb_project><db path="identifier.sqlite" readonly="0" foreign_keys="1" case_sensitive_like="0" temp_store="0" wal_autocheckpoint="1000" synchronous="2"/><attached/><window><main_tabs open="structure browser pragmas query" current="1"/></window><tab_structure><column_width id="0" width="300"/><column_width id="1" width="0"/><column_width id="2" width="100"/><column_width id="3" width="1871"/><column_width id="4" width="0"/><expanded_item id="0" parent="1"/><expanded_item id="1" parent="1"/><expanded_item id="2" parent="1"/><expanded_item id="3" parent="1"/></tab_structure><tab_browse><table title="users" custom_title="0" dock_id="2" table="4,5:mainusers"/><dock_state state="000000ff00000000fd0000000100000002000005a800000326fc0100000001fc00000000000005a80000011e00fffffffa000000010100000002fb000000160064006f0063006b00420072006f00770073006500310000000000ffffffff0000000000000000fb000000160064006f0063006b00420072006f00770073006500320100000000ffffffff0000011e00ffffff000002690000000000000004000000040000000800000008fc00000000"/><default_encoding codec=""/><browse_table_settings><table schema="main" name="clues" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_" freeze_columns="0"><sort/><column_widths><column index="1" value="35"/><column index="2" value="175"/><column index="3" value="57"/><column index="4" value="40"/><column index="5" value="59"/><column index="6" value="159"/></column_widths><filter_values/><conditional_formats/><row_id_formats/><display_formats/><hidden_columns/><plot_y_axes/><global_filter/></table><table schema="main" name="teams" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_" freeze_columns="0"><sort/><column_widths><column index="1" value="35"/><column index="2" value="87"/><column index="3" value="71"/><column index="4" value="159"/></column_widths><filter_values/><conditional_formats/><row_id_formats/><display_formats/><hidden_columns/><plot_y_axes/><global_filter/></table><table schema="main" name="users" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_" freeze_columns="0"><sort/><column_widths><column index="1" value="35"/><column index="2" value="65"/><column index="3" value="300"/><column index="4" value="47"/><column index="5" value="57"/><column index="6" value="159"/></column_widths><filter_values/><conditional_formats/><row_id_formats/><display_formats/><hidden_columns/><plot_y_axes/><global_filter/></table></browse_table_settings></tab_browse><tab_sql><sql name="SQL 1*">-- Schema for clue_hunt.db
|
|
|
|
|
|
|
|
-- Drop tables if they exist
|
|
|
|
DROP TABLE IF EXISTS clues;
|
|
|
|
DROP TABLE IF EXISTS users;
|
|
|
|
DROP TABLE IF EXISTS teams;
|
|
|
|
DROP TABLE IF EXISTS game_settings;
|
|
|
|
|
|
|
|
-- Users table
|
|
|
|
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)
|
|
|
|
);
|
|
|
|
|
|
|
|
-- Teams table
|
|
|
|
CREATE TABLE teams (
|
|
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
|
|
name TEXT NOT NULL,
|
|
|
|
code TEXT UNIQUE NOT NULL,
|
|
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
|
|
);
|
|
|
|
|
|
|
|
-- Clues table
|
|
|
|
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)
|
|
|
|
);
|
|
|
|
|
|
|
|
-- 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 initial admin user
|
|
|
|
INSERT INTO users (username, password, role)
|
|
|
|
VALUES ('admin', '$2a$10$N9qo8uLOickgx2ZMRZoMyeIjZAgcfl7p92ldGxad68LJZdL17lhWy', 'admin');
|
|
|
|
-- Password is 'password123' - change this in production!
|
|
|
|
|
|
|
|
-- Insert sample teams
|
|
|
|
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 sample clues for Team Alpha
|
|
|
|
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);
|
|
|
|
|
|
|
|
</sql><current_tab id="0"/></tab_sql></sqlb_project>
|