AllWhomstWander/db/sqlite/20240506-initialize-db.sql

-- Items definition

CREATE TABLE Items (
	id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	name TEXT NOT NULL,
	description TEXT NOT NULL,
	image_path TEXT,
	model_path TEXT,
	map_icon_path TEXT,
	is_active INTEGER DEFAULT (1) NOT NULL,
	is_unique_per_player INTEGER DEFAULT (0) NOT NULL,
	is_unique_per_world INTEGER DEFAULT (0) NOT NULL
);


-- NPCs definition

CREATE TABLE NPCs (
	id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	name TEXT NOT NULL,
	description TEXT NOT NULL,
	is_active INTEGER DEFAULT (1) NOT NULL,
	is_unique INTEGER DEFAULT (0) NOT NULL,
	model_path TEXT,
	image_path TEXT,
	avatar_path TEXT
, minimum_group INTEGER DEFAULT (1) NOT NULL, maximum_group INTEGER DEFAULT (1) NOT NULL);


-- Players definition

CREATE TABLE Players (
	id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	nickname TEXT NOT NULL,
	is_active INTEGER DEFAULT (1) NOT NULL
);


-- POIs definition

CREATE TABLE POIs (
	id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	name TEXT NOT NULL,
	latitude NUMERIC NOT NULL,
	longitude NUMERIC NOT NULL,
	radius NUMERIC DEFAULT (25) NOT NULL,
	is_active INTEGER DEFAULT (0) NOT NULL
, description TEXT, map_marker_icon TEXT);

CREATE INDEX POIs_latitude_IDX ON POIs (latitude);
CREATE INDEX POIs_longitude_IDX ON POIs (longitude);
CREATE INDEX POIs_is_active_IDX ON POIs (is_active);
CREATE INDEX POIs_name_IDX ON POIs (name);


-- PlayerAuth definition

CREATE TABLE PlayerAuth (
	id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	player_id INTEGER NOT NULL,
	email TEXT,
	hashed_pw TEXT(255) NOT NULL,
	is_active INTEGER DEFAULT (1) NOT NULL,
	is_email_verified INTEGER DEFAULT (0) NOT NULL,
	CONSTRAINT PlayerAuth_Players_FK FOREIGN KEY (player_id) REFERENCES Players(id)
);


-- PlayerEncounters definition

CREATE TABLE PlayerEncounters (
	player_id INTEGER NOT NULL,
	npc_id INTEGER NOT NULL,
	encounter_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	CONSTRAINT PlayerEncounters_Players_FK FOREIGN KEY (player_id) REFERENCES Players(id),
	CONSTRAINT PlayerEncounters_NPCs_FK FOREIGN KEY (npc_id) REFERENCES NPCs(id)
);


-- PlayerFriends definition

CREATE TABLE PlayerFriends (
	id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	player_id INTEGER NOT NULL,
	friend_id INTEGER NOT NULL,
	CONSTRAINT PlayerFriends_Players_FK FOREIGN KEY (player_id) REFERENCES Players(id),
	CONSTRAINT PlayerFriends_Players_FK_1 FOREIGN KEY (friend_id) REFERENCES Players(id)
);


-- PlayerItems definition

CREATE TABLE PlayerItems (
	player_id INTEGER NOT NULL,
	item_id INTEGER NOT NULL,
	quantity INTEGER DEFAULT (1) NOT NULL,
	is_active INTEGER DEFAULT (1) NOT NULL,
	CONSTRAINT PlayerItems_Players_FK FOREIGN KEY (player_id) REFERENCES Players(id),
	CONSTRAINT PlayerItems_Items_FK FOREIGN KEY (item_id) REFERENCES Items(id)
);


-- PlayerNPCs definition

CREATE TABLE PlayerNPCs (
	id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	player_id INTEGER NOT NULL,
	npc_id INTEGER NOT NULL,
	group_size INTEGER DEFAULT (1) NOT NULL,
	is_active INTEGER DEFAULT (1) NOT NULL,
	CONSTRAINT PlayerNPCs_Players_FK FOREIGN KEY (player_id) REFERENCES Players(id),
	CONSTRAINT PlayerNPCs_NPCs_FK FOREIGN KEY (npc_id) REFERENCES NPCs(id)
);


-- PlayerPWChanges definition

CREATE TABLE PlayerPWChanges (
	id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	player_id INTEGER NOT NULL,
	token TEXT NOT NULL,
	is_used INTEGER DEFAULT (0) NOT NULL,
	expires INTEGER NOT NULL,
	CONSTRAINT PlayerPWChanges_Players_FK FOREIGN KEY (player_id) REFERENCES Players(id)
);


-- PlayerSessions definition

CREATE TABLE PlayerSessions (
	id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	player_id INTEGER NOT NULL,
	session_id TEXT NOT NULL,
	CONSTRAINT PlayerSessions_Players_FK FOREIGN KEY (player_id) REFERENCES Players(id)
);

CREATE INDEX PlayerSessions_session_id_IDX ON PlayerSessions (session_id);


-- "POI-Items" definition

CREATE TABLE "POI-Items" (
	id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	poi_id INTEGER NOT NULL,
	item_id INTEGER NOT NULL,
	is_active INTEGER DEFAULT (1) NOT NULL,
	refresh_rate_in_seconds INTEGER DEFAULT (0) NOT NULL,
	CONSTRAINT POI_Items_POIs_FK FOREIGN KEY (poi_id) REFERENCES POIs(id),
	CONSTRAINT POI_Items_Items_FK FOREIGN KEY (item_id) REFERENCES Items(id)
);


-- "POI-NPCs" definition

CREATE TABLE "POI-NPCs" (
	id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	poi_id INTEGER NOT NULL,
	npc_id INTEGER NOT NULL,
	group_size INTEGER DEFAULT (1) NOT NULL,
	name INTEGER,
	description INTEGER,
	CONSTRAINT POI_NPCs_POIs_FK FOREIGN KEY (poi_id) REFERENCES POIs(id),
	CONSTRAINT POI_NPCs_NPCs_FK FOREIGN KEY (npc_id) REFERENCES NPCs(id)
);


-- "POI-NPC-Items" definition

CREATE TABLE "POI-NPC-Items" (
	poi_npc_id INTEGER NOT NULL,
	item_id INTEGER NOT NULL,
	id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	quantity INTEGER DEFAULT (1) NOT NULL,
	refresh_rate_in_seconds INTEGER DEFAULT (0) NOT NULL,
	CONSTRAINT POI_NPC_Items_POI_NPCs_FK FOREIGN KEY (poi_npc_id) REFERENCES "POI-NPCs"(id),
	CONSTRAINT POI_NPC_Items_Items_FK FOREIGN KEY (item_id) REFERENCES Items(id)
);