path: root/tools/sql/schema.txt

CREATE TABLE IF NOT EXISTS blacklist_ip ( ip TEXT NOT NULL PRIMARY KEY ) WITHOUT ROWID;
CREATE TABLE IF NOT EXISTS blacklist_mail ( mail TEXT NOT NULL PRIMARY KEY ) WITHOUT ROWID;

CREATE TABLE IF NOT EXISTS titles (
	title_id TEXT NOT NULL PRIMARY KEY,
	title_name TEXT NOT NULL,
	bgg INTEGER,
	hidden BOOLEAN
) WITHOUT ROWID;

CREATE TABLE IF NOT EXISTS roles (
	title_id TEXT NOT NULL REFERENCES titles(title_id) ON DELETE CASCADE,
	role TEXT NOT NULL,
	UNIQUE ( title_id, role )
);

-- Users --

CREATE TABLE IF NOT EXISTS users (
	user_id	INTEGER NOT NULL PRIMARY KEY,
	name TEXT NOT NULL UNIQUE COLLATE NOCASE,
	mail TEXT NOT NULL UNIQUE COLLATE NOCASE,
	notify BOOLEAN NOT NULL DEFAULT 0,
	banned BOOLEAN NOT NULL DEFAULT 0,
	ctime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	password TEXT NOT NULL,
	salt TEXT NOT NULL,
	about TEXT
);

CREATE TABLE IF NOT EXISTS user_last_seen (
	user_id INTEGER NOT NULL PRIMARY KEY REFERENCES users(user_id) ON DELETE CASCADE,
	atime TIMESTAMP NOT NULL,
	aip TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS tokens (
	user_id INTEGER NOT NULL PRIMARY KEY REFERENCES users(user_id) ON DELETE CASCADE,
	token TEXT NOT NULL,
	time TIMESTAMP NOT NULL
);

CREATE TABLE IF NOT EXISTS last_notified (
	game_id INTEGER NOT NULL REFERENCES games(game_id) ON DELETE CASCADE,
	user_id INTEGER NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
	time TIMESTAMP NOT NULL,
	PRIMARY KEY ( game_id, user_id )
) WITHOUT ROWID;

DROP VIEW IF EXISTS user_view;
CREATE VIEW user_view AS
	SELECT
		user_id, name, mail, notify
	FROM users
	;

DROP VIEW IF EXISTS user_login_view;
CREATE VIEW user_login_view AS
	SELECT
		user_id, name, mail, notify, password, salt
	FROM users
	;

DROP VIEW IF EXISTS user_profile_view;
CREATE VIEW user_profile_view AS
	SELECT
		user_id, name, mail, notify, ctime, atime, about
	FROM users
	NATURAL LEFT JOIN user_last_seen
	;

-- Messages --

CREATE TABLE IF NOT EXISTS messages (
	message_id INTEGER NOT NULL PRIMARY KEY,
	deleted_from_inbox BOOLEAN NOT NULL DEFAULT 0,
	deleted_from_outbox BOOLEAN NOT NULL DEFAULT 0,
	from_id INTEGER NOT NULL DEFAULT 0 REFERENCES users(user_id) ON DELETE SET DEFAULT,
	to_id INTEGER NOT NULL DEFAULT 0 REFERENCES users(user_id) ON DELETE SET DEFAULT,
	time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	read BOOLEAN NOT NULL DEFAULT 0,
	subject TEXT NOT NULL,
	body TEXT NOT NULL
);

DROP VIEW IF EXISTS message_view;
CREATE VIEW message_view AS
	SELECT *
		, users_from.name AS from_name
		, users_to.name AS to_name
	FROM messages
	JOIN users AS users_from ON messages.from_id = users_from.user_id
	JOIN users AS users_to ON messages.to_id = users_to.user_id
	;

DROP INDEX IF EXISTS messages_inbox_idx;
CREATE INDEX messages_inbox_idx ON messages(to_id) WHERE deleted_from_inbox=0;

DROP INDEX IF EXISTS messages_inbox_unread_idx;
CREATE INDEX messages_inbox_unread_idx ON messages(to_id) WHERE read=0 AND deleted_from_inbox=0;

-- Forum --

CREATE TABLE IF NOT EXISTS threads (
	thread_id INTEGER NOT NULL PRIMARY KEY,
	author_id INTEGER NOT NULL DEFAULT 0 REFERENCES users(user_id) ON DELETE SET DEFAULT,
	subject TEXT NOT NULL,
	locked BOOLEAN NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS posts (
	post_id INTEGER NOT NULL PRIMARY KEY,
	thread_id INTEGER NOT NULL REFERENCES threads(thread_id) ON DELETE CASCADE,
	author_id INTEGER NOT NULL DEFAULT 0 REFERENCES users(user_id) ON DELETE SET DEFAULT,
	ctime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	mtime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	body TEXT NOT NULL
);

DROP VIEW IF EXISTS thread_view;
CREATE VIEW thread_view AS
	SELECT *
		, author.name AS author_name
		, ( SELECT count(*) FROM posts WHERE posts.thread_id = threads.thread_id ) - 1 AS replies
		, ( SELECT max(posts.mtime) FROM posts WHERE posts.thread_id = threads.thread_id ) AS mtime
	FROM threads
	JOIN users AS author ON threads.author_id = author.user_id
	;

DROP VIEW IF EXISTS post_view;
CREATE VIEW post_view AS
	SELECT *
		, author.name AS author_name
	FROM posts
	JOIN users AS author ON posts.author_id = author.user_id
	;

DROP INDEX IF EXISTS posts_thread_idx;
CREATE INDEX posts_thread_idx ON posts(thread_id);

-- Games --

CREATE TABLE IF NOT EXISTS games (
	game_id INTEGER NOT NULL PRIMARY KEY,
	title_id TEXT NOT NULL REFERENCES titles(title_id) ON DELETE CASCADE,
	scenario TEXT,
	options TEXT,
	owner_id INTEGER DEFAULT 0 REFERENCES users(user_id) ON DELETE SET NULL,
	ctime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	private BOOLEAN NOT NULL DEFAULT 0,
	random BOOLEAN NOT NULL DEFAULT 0,
	description TEXT NOT NULL DEFAULT '',
	status INTEGER NOT NULL DEFAULT 0,
	result TEXT
);

DROP INDEX IF EXISTS games_title_idx;
CREATE INDEX games_title_idx ON games(title_id);

DROP INDEX IF EXISTS games_status_idx;
CREATE INDEX games_status_idx ON games(status);

CREATE TABLE IF NOT EXISTS game_state (
	game_id INTEGER NOT NULL PRIMARY KEY REFERENCES games(game_id) ON DELETE CASCADE,
	mtime TIMESTAMP NOT NULL,
	active TEXT,
	state TEXT
);

CREATE TABLE IF NOT EXISTS game_chat (
	chat_id INTEGER NOT NULL PRIMARY KEY,
	game_id INTEGER NOT NULL REFERENCES games(game_id) ON DELETE CASCADE,
	time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	user_id INTEGER NOT NULL DEFAULT 0 REFERENCES users(user_id) ON DELETE SET DEFAULT,
	message TEXT
);

DROP VIEW IF EXISTS game_chat_view;
CREATE VIEW game_chat_view AS
	SELECT chat_id, game_id, time, name, message
	FROM game_chat
	NATURAL JOIN users
	;

DROP INDEX IF EXISTS game_chat_idx;
CREATE INDEX game_chat_idx ON game_chat(game_id);

CREATE TABLE IF NOT EXISTS game_replay (
	game_id INTEGER NOT NULL REFERENCES games(game_id) ON DELETE CASCADE,
	time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	role TEXT,
	action TEXT,
	arguments TEXT
);

CREATE TABLE IF NOT EXISTS players (
	game_id INTEGER NOT NULL REFERENCES games(game_id) ON DELETE CASCADE,
	role TEXT NOT NULL,
	user_id INTEGER NOT NULL DEFAULT 0 REFERENCES users(user_id) ON DELETE SET DEFAULT,
	PRIMARY KEY ( game_id, role )
) WITHOUT ROWID;

DROP INDEX IF EXISTS player_user_idx;
CREATE INDEX player_user_idx ON players(user_id);

DROP INDEX IF EXISTS player_game_user_idx;
CREATE INDEX player_game_user_idx ON players(game_id,user_id);

DROP VIEW IF EXISTS game_view;
CREATE VIEW game_view AS
	SELECT games.*
		, titles.title_name
		, owner.name AS owner_name
		, game_state.mtime
		, game_state.active
	FROM games
	NATURAL LEFT JOIN game_state
	NATURAL JOIN titles
	JOIN users AS owner ON owner.user_id=games.owner_id
	;

DROP VIEW IF EXISTS game_full_view;
CREATE VIEW game_full_view AS
	SELECT *,
		( SELECT group_concat(name, ', ')
			FROM players
			NATURAL JOIN users
			WHERE players.game_id=game_view.game_id
		) AS player_names,
		( SELECT count(DISTINCT user_id) = 1
			FROM players
			WHERE players.game_id=game_view.game_id
		) AS is_solo
	FROM game_view
	;

DROP VIEW IF EXISTS opposed_games;
CREATE VIEW opposed_games AS
	SELECT *
	FROM GAMES
	WHERE
		STATUS > 0 AND
		(
			SELECT count(DISTINCT user_id) > 1
			FROM players
			where players.game_id=games.game_id
		)
	;

DROP VIEW IF EXISTS your_turn_reminder;
CREATE VIEW your_turn_reminder AS
	SELECT
		game_id, role, user_id, name, mail, notify
	FROM game_full_view
	JOIN players USING(game_id)
	JOIN users USING(user_id)
	WHERE status = 1 AND is_solo = 0 AND notify = 1
		AND active IN ( 'All', 'Both', role )
		AND datetime('now') > datetime(mtime, '+1 hour')
	;

DROP VIEW IF EXISTS your_turn;
CREATE VIEW your_turn AS
	SELECT game_id, user_id, role
	FROM players
	JOIN games USING(game_id)
	JOIN game_state USING(game_id)
	WHERE status=1 AND active IN ( 'All', 'Both', role )
	;

DROP TRIGGER IF EXISTS no_part_on_active_game;
CREATE TRIGGER no_part_on_active_game BEFORE DELETE ON players
BEGIN
	SELECT RAISE(ABORT, "Cannot remove players from started games.")
	WHERE ( SELECT status FROM games WHERE games.game_id = old.game_id ) > 0 ;
END;

DROP TRIGGER IF EXISTS no_join_on_active_game;
CREATE TRIGGER no_join_on_active_game BEFORE INSERT ON players
BEGIN
	SELECT RAISE(ABORT, "Cannot add players to started games.")
	WHERE ( SELECT status FROM games WHERE games.game_id = new.game_id ) > 0 ;
END;

DROP TRIGGER IF EXISTS must_be_valid_role;
CREATE TRIGGER must_be_valid_role BEFORE INSERT ON players
BEGIN
	SELECT
		RAISE(ABORT, "Invalid role.")
	WHERE
		NOT EXISTS (
			SELECT 1
			FROM roles
			WHERE roles.title_id = ( SELECT title_id FROM games WHERE games.game_id = new.game_id )
			AND roles.role = new.role
		)
		AND new.role <> 'Random 1'
		AND new.role <> 'Random 2'
		AND new.role <> 'Random 3'
		AND new.role <> 'Random 4'
		AND new.role <> 'Random 5'
		AND new.role <> 'Random 6'
		AND new.role <> 'Random 7'
	;
END;

-- Manual key management if pragma foreign_keys = OFF
DROP TRIGGER IF EXISTS trigger_delete_on_games;
CREATE TRIGGER trigger_delete_on_games AFTER DELETE ON games
BEGIN
	DELETE FROM game_state WHERE game_id = old.game_id;
	DELETE FROM game_chat WHERE game_id = old.game_id;
	DELETE FROM game_replay WHERE game_id = old.game_id;
	DELETE FROM last_notified WHERE game_id = old.game_id;
	DELETE FROM players WHERE game_id = old.game_id;
END;

-- Game Stats

DROP VIEW IF EXISTS role_index_view;
CREATE VIEW role_index_view(t,i,r) AS
	SELECT title_id, row_number() OVER ( PARTITION BY title_id ), role
	FROM roles;

DROP VIEW IF EXISTS game_stat_view;
CREATE VIEW game_stat_view AS
	SELECT title_name, NULL AS scenario
		, NULL as total
		, (SELECT r FROM role_index_view WHERE t=titles.title_id AND i=1) AS r1
		, (SELECT r FROM role_index_view WHERE t=titles.title_id AND i=2) AS r2
		, (SELECT r FROM role_index_view WHERE t=titles.title_id AND i=3) AS r3
		, (SELECT r FROM role_index_view WHERE t=titles.title_id AND i=4) AS r4
		, (SELECT r FROM role_index_view WHERE t=titles.title_id AND i=5) AS r5
		, (SELECT r FROM role_index_view WHERE t=titles.title_id AND i=6) AS r6
		, (SELECT r FROM role_index_view WHERE t=titles.title_id AND i=7) AS r7
	FROM titles
	WHERE hidden=0
	UNION
	SELECT title_name, scenario
		, count(*) as total
		, sum((SELECT i FROM role_index_view WHERE t=title_id AND r=result)=1) AS r1
		, sum((SELECT i FROM role_index_view WHERE t=title_id AND r=result)=2) AS r2
		, sum((SELECT i FROM role_index_view WHERE t=title_id AND r=result)=3) AS r3
		, sum((SELECT i FROM role_index_view WHERE t=title_id AND r=result)=4) AS r4
		, sum((SELECT i FROM role_index_view WHERE t=title_id AND r=result)=5) AS r5
		, sum((SELECT i FROM role_index_view WHERE t=title_id AND r=result)=6) AS r6
		, sum((SELECT i FROM role_index_view WHERE t=title_id AND r=result)=7) AS r7
	FROM games
	NATURAL JOIN titles
	WHERE hidden=0 AND status=2 AND game_id IN (SELECT game_id FROM opposed_games)
	GROUP BY title_id, scenario
	ORDER BY title_name, total DESC NULLS FIRST
	;