Warren H
291 lines
10 KiB
SQL
291 lines
10 KiB
SQL
PRAGMA foreign_keys = ON;
|
|
|
|
CREATE TABLE IF NOT EXISTS locations (
|
|
id INTEGER PRIMARY KEY,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL,
|
|
description TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS dungeons (
|
|
id INTEGER PRIMARY KEY,
|
|
location_id INTEGER NOT NULL REFERENCES locations(id),
|
|
slug TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL,
|
|
recommended_level INTEGER NOT NULL DEFAULT 1,
|
|
content_type TEXT NOT NULL DEFAULT 'dungeon' CHECK (content_type IN ('dungeon', 'raid')),
|
|
party_size INTEGER NOT NULL DEFAULT 6,
|
|
completion_item_level INTEGER,
|
|
experience_reward INTEGER NOT NULL DEFAULT 100,
|
|
description TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS difficulties (
|
|
id INTEGER PRIMARY KEY,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL,
|
|
dropped_item_level INTEGER NOT NULL,
|
|
unlock_level INTEGER NOT NULL DEFAULT 1,
|
|
health_multiplier REAL NOT NULL DEFAULT 1,
|
|
damage_multiplier REAL NOT NULL DEFAULT 1,
|
|
experience_multiplier REAL NOT NULL DEFAULT 1,
|
|
description TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS dungeon_difficulties (
|
|
dungeon_id INTEGER NOT NULL REFERENCES dungeons(id) ON DELETE CASCADE,
|
|
difficulty_id INTEGER NOT NULL REFERENCES difficulties(id),
|
|
PRIMARY KEY (dungeon_id, difficulty_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS encounters (
|
|
id INTEGER PRIMARY KEY,
|
|
dungeon_id INTEGER NOT NULL REFERENCES dungeons(id),
|
|
sequence INTEGER NOT NULL,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL,
|
|
encounter_type TEXT NOT NULL CHECK (encounter_type IN ('trash', 'boss')),
|
|
max_health INTEGER NOT NULL,
|
|
base_damage INTEGER NOT NULL,
|
|
tank_damage INTEGER NOT NULL,
|
|
party_damage INTEGER NOT NULL,
|
|
description TEXT NOT NULL,
|
|
image_url TEXT NOT NULL DEFAULT '/boss-placeholder.svg',
|
|
UNIQUE (dungeon_id, sequence)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS mechanics (
|
|
id INTEGER PRIMARY KEY,
|
|
encounter_id INTEGER NOT NULL REFERENCES encounters(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
mechanic_type TEXT NOT NULL,
|
|
interval_seconds REAL,
|
|
power INTEGER NOT NULL DEFAULT 0,
|
|
description TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS classes (
|
|
id INTEGER PRIMARY KEY,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL,
|
|
resource_name TEXT NOT NULL,
|
|
max_resource INTEGER NOT NULL DEFAULT 100,
|
|
theme_color TEXT NOT NULL DEFAULT '#e5b95f',
|
|
description TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS spells (
|
|
id INTEGER PRIMARY KEY,
|
|
class_id INTEGER NOT NULL REFERENCES classes(id) ON DELETE CASCADE,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL,
|
|
spell_type TEXT NOT NULL,
|
|
resource_cost INTEGER NOT NULL,
|
|
cooldown_seconds REAL NOT NULL DEFAULT 0,
|
|
power INTEGER NOT NULL,
|
|
unlock_level INTEGER NOT NULL DEFAULT 1,
|
|
glyph TEXT NOT NULL DEFAULT '+',
|
|
description TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS items (
|
|
id INTEGER PRIMARY KEY,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL,
|
|
slot TEXT NOT NULL CHECK (slot IN ('weapon', 'helmet', 'chest', 'gloves', 'boots', 'pants', 'ring', 'necklace', 'trinket', 'component')),
|
|
rarity TEXT NOT NULL,
|
|
item_level INTEGER NOT NULL,
|
|
healing_power INTEGER NOT NULL DEFAULT 0,
|
|
max_resource_bonus INTEGER NOT NULL DEFAULT 0,
|
|
glyph TEXT NOT NULL DEFAULT '?',
|
|
image_url TEXT NOT NULL DEFAULT '/equipment-placeholder.svg',
|
|
description TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS encounter_loot (
|
|
encounter_id INTEGER NOT NULL REFERENCES encounters(id) ON DELETE CASCADE,
|
|
item_id INTEGER NOT NULL REFERENCES items(id) ON DELETE CASCADE,
|
|
difficulty_id INTEGER REFERENCES difficulties(id),
|
|
drop_weight INTEGER NOT NULL DEFAULT 100,
|
|
drop_chance REAL NOT NULL DEFAULT 0.65 CHECK (drop_chance BETWEEN 0 AND 1),
|
|
PRIMARY KEY (encounter_id, difficulty_id, item_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS dungeon_completion_loot (
|
|
dungeon_id INTEGER NOT NULL REFERENCES dungeons(id) ON DELETE CASCADE,
|
|
item_id INTEGER NOT NULL REFERENCES items(id) ON DELETE CASCADE,
|
|
PRIMARY KEY (dungeon_id, item_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS crafting_recipes (
|
|
id INTEGER PRIMARY KEY,
|
|
item_id INTEGER NOT NULL UNIQUE REFERENCES items(id) ON DELETE CASCADE,
|
|
difficulty_id INTEGER REFERENCES difficulties(id),
|
|
source_dungeon_id INTEGER REFERENCES dungeons(id) ON DELETE CASCADE,
|
|
source_encounter_id INTEGER REFERENCES encounters(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS crafting_recipe_components (
|
|
recipe_id INTEGER NOT NULL REFERENCES crafting_recipes(id) ON DELETE CASCADE,
|
|
item_id INTEGER NOT NULL REFERENCES items(id) ON DELETE CASCADE,
|
|
quantity INTEGER NOT NULL CHECK (quantity > 0),
|
|
PRIMARY KEY (recipe_id, item_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS item_sets (
|
|
id INTEGER PRIMARY KEY,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL,
|
|
description TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS item_set_items (
|
|
set_id INTEGER NOT NULL REFERENCES item_sets(id) ON DELETE CASCADE,
|
|
item_id INTEGER NOT NULL UNIQUE REFERENCES items(id) ON DELETE CASCADE,
|
|
PRIMARY KEY (set_id, item_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS item_set_bonuses (
|
|
id INTEGER PRIMARY KEY,
|
|
set_id INTEGER NOT NULL REFERENCES item_sets(id) ON DELETE CASCADE,
|
|
required_pieces INTEGER NOT NULL CHECK (required_pieces > 0),
|
|
effect_type TEXT NOT NULL,
|
|
description TEXT NOT NULL,
|
|
UNIQUE (set_id, required_pieces)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS accounts (
|
|
id INTEGER PRIMARY KEY,
|
|
username TEXT NOT NULL UNIQUE COLLATE NOCASE,
|
|
password_hash TEXT NOT NULL,
|
|
password_salt TEXT NOT NULL,
|
|
completed_dungeon_parts INTEGER NOT NULL DEFAULT 0,
|
|
completed_raid_phases INTEGER NOT NULL DEFAULT 0,
|
|
created_ip TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS account_ip_allowances (
|
|
ip_address TEXT PRIMARY KEY,
|
|
max_accounts INTEGER NOT NULL CHECK (max_accounts >= 1),
|
|
note TEXT NOT NULL DEFAULT '',
|
|
updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS characters (
|
|
id INTEGER PRIMARY KEY,
|
|
account_id INTEGER REFERENCES accounts(id) ON DELETE CASCADE,
|
|
class_id INTEGER NOT NULL REFERENCES classes(id),
|
|
name TEXT NOT NULL,
|
|
level INTEGER NOT NULL DEFAULT 1,
|
|
experience INTEGER NOT NULL DEFAULT 0,
|
|
talent_points INTEGER NOT NULL DEFAULT 1,
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE (account_id, class_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS sessions (
|
|
id INTEGER PRIMARY KEY,
|
|
account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
|
|
token_hash TEXT NOT NULL UNIQUE,
|
|
active_character_id INTEGER REFERENCES characters(id),
|
|
expires_at TEXT NOT NULL,
|
|
created_ip TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS sessions_token_hash_idx ON sessions(token_hash);
|
|
CREATE INDEX IF NOT EXISTS sessions_expires_at_idx ON sessions(expires_at);
|
|
|
|
CREATE TABLE IF NOT EXISTS character_ability_slots (
|
|
character_id INTEGER NOT NULL REFERENCES characters(id) ON DELETE CASCADE,
|
|
slot_number INTEGER NOT NULL CHECK (slot_number BETWEEN 1 AND 6),
|
|
spell_id INTEGER REFERENCES spells(id),
|
|
PRIMARY KEY (character_id, slot_number),
|
|
UNIQUE (character_id, spell_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS talents (
|
|
id INTEGER PRIMARY KEY,
|
|
class_id INTEGER NOT NULL REFERENCES classes(id) ON DELETE CASCADE,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL,
|
|
max_rank INTEGER NOT NULL DEFAULT 1,
|
|
tier INTEGER NOT NULL DEFAULT 1,
|
|
branch INTEGER NOT NULL DEFAULT 1,
|
|
prerequisite_talent_id INTEGER REFERENCES talents(id),
|
|
prerequisite_rank INTEGER NOT NULL DEFAULT 0,
|
|
effect_type TEXT NOT NULL DEFAULT 'placeholder',
|
|
effect_value_per_rank REAL NOT NULL DEFAULT 0,
|
|
glyph TEXT NOT NULL DEFAULT '+',
|
|
description TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS character_talents (
|
|
character_id INTEGER NOT NULL REFERENCES characters(id) ON DELETE CASCADE,
|
|
talent_id INTEGER NOT NULL REFERENCES talents(id) ON DELETE CASCADE,
|
|
rank INTEGER NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (character_id, talent_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS level_progression (
|
|
level INTEGER PRIMARY KEY CHECK (level BETWEEN 1 AND 25),
|
|
experience_required INTEGER NOT NULL,
|
|
talent_points_total INTEGER NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS game_settings (
|
|
key TEXT PRIMARY KEY,
|
|
value TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS character_inventory (
|
|
character_id INTEGER NOT NULL REFERENCES characters(id) ON DELETE CASCADE,
|
|
item_id INTEGER NOT NULL REFERENCES items(id),
|
|
quantity INTEGER NOT NULL DEFAULT 1,
|
|
equipped INTEGER NOT NULL DEFAULT 0 CHECK (equipped IN (0, 1)),
|
|
PRIMARY KEY (character_id, item_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS encounter_loot_rolls (
|
|
id INTEGER PRIMARY KEY,
|
|
character_id INTEGER NOT NULL REFERENCES characters(id) ON DELETE CASCADE,
|
|
run_token TEXT NOT NULL,
|
|
encounter_id INTEGER NOT NULL REFERENCES encounters(id),
|
|
difficulty_id INTEGER NOT NULL REFERENCES difficulties(id),
|
|
item_id INTEGER REFERENCES items(id),
|
|
dropped INTEGER NOT NULL CHECK (dropped IN (0, 1)),
|
|
was_duplicate INTEGER NOT NULL DEFAULT 0 CHECK (was_duplicate IN (0, 1)),
|
|
quantity_after INTEGER NOT NULL DEFAULT 0,
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE (character_id, run_token, encounter_id, difficulty_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS encounter_loot_roll_items (
|
|
roll_id INTEGER NOT NULL REFERENCES encounter_loot_rolls(id) ON DELETE CASCADE,
|
|
item_id INTEGER NOT NULL REFERENCES items(id),
|
|
quantity INTEGER NOT NULL DEFAULT 1 CHECK (quantity > 0),
|
|
was_duplicate INTEGER NOT NULL DEFAULT 0 CHECK (was_duplicate IN (0, 1)),
|
|
quantity_after INTEGER NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (roll_id, item_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS dungeon_runs (
|
|
id INTEGER PRIMARY KEY,
|
|
character_id INTEGER NOT NULL REFERENCES characters(id),
|
|
dungeon_id INTEGER NOT NULL REFERENCES dungeons(id),
|
|
difficulty_id INTEGER REFERENCES difficulties(id),
|
|
result TEXT NOT NULL CHECK (result IN ('active', 'victory', 'defeat', 'abandoned')),
|
|
character_name TEXT NOT NULL DEFAULT '',
|
|
class_name TEXT NOT NULL DEFAULT '',
|
|
character_level INTEGER NOT NULL DEFAULT 1,
|
|
average_item_level REAL NOT NULL DEFAULT 1,
|
|
resource_spent INTEGER NOT NULL DEFAULT 0,
|
|
duration_seconds INTEGER NOT NULL DEFAULT 0,
|
|
leaderboard_eligible INTEGER NOT NULL DEFAULT 0 CHECK (leaderboard_eligible IN (0, 1)),
|
|
start_part INTEGER NOT NULL DEFAULT 1,
|
|
completed_parts INTEGER NOT NULL DEFAULT 1,
|
|
started_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
completed_at TEXT
|
|
);
|