DROP DATABASE IF EXISTS lfg;
CREATE DATABASE lfg;
USE lfg;

DROP USER 'lfg';
CREATE USER 'lfg' IDENTIFIED BY 'XXX';
REVOKE ALL ON *.* FROM 'lfg';
GRANT ALL ON lfg.* TO 'lfg'@'%';
GRANT SELECT ON phrasebook.* TO 'lfg'@'%';
GRANT INSERT, UPDATE ON phrasebook.page_phrases TO 'lfg'@'%';

CREATE TABLE users (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	access TINYINT UNSIGNED NOT NULL DEFAULT 3,
	characters_visible BOOLEAN NOT NULL DEFAULT FALSE,
	name NATIONAL VARCHAR(32) NOT NULL UNIQUE,
	password NATIONAL VARCHAR(32) NOT NULL,
	email VARCHAR(64) NOT NULL UNIQUE,
	home INT UNSIGNED DEFAULT NULL,
	home_type VARCHAR(16) DEFAULT NULL,
	PRIMARY KEY(id)
);

INSERT INTO users (name, password, access, email) VALUES ('root', 'XXX', 0, 'XXX');

CREATE TABLE games (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	name NATIONAL VARCHAR(64) NOT NULL UNIQUE,
	PRIMARY KEY(id)
);

CREATE TABLE servers (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
	name NATIONAL VARCHAR(64) NOT NULL UNIQUE,
	game INT UNSIGNED NOT NULL,
	PRIMARY KEY(name, game),
	FOREIGN KEY (game) REFERENCES games(id) ON DELETE RESTRICT
);

CREATE TABLE guilds (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
	name NATIONAL VARCHAR(32) NOT NULL,
	server INT UNSIGNED NOT NULL,
	visibility TINYINT UNSIGNED NOT NULL DEFAULT 3, -- 3: Full, 2: Server, 0: Members
	PRIMARY KEY(name, server),
	FOREIGN KEY (server) REFERENCES servers(id) ON DELETE RESTRICT
);

CREATE TABLE classes (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
	name NATIONAL VARCHAR(32) NOT NULL,
	game INT UNSIGNED NOT NULL,
	color CHAR(6) NOT NULL DEFAULT '000000',
	PRIMARY KEY(name, game),
	FOREIGN KEY (game) REFERENCES games(id) ON DELETE RESTRICT
);

CREATE TABLE characters (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
	name NATIONAL VARCHAR(32) NOT NULL,
	class INT UNSIGNED NOT NULL,
	server INT UNSIGNED NOT NULL,
	guild INT UNSIGNED DEFAULT NULL,
	guild_access TINYINT UNSIGNED NOT NULL DEFAULT 2, -- 3: Leader, 2: Officer, 1: Member, 0: Recruit
	visibility TINYINT UNSIGNED NOT NULL DEFAULT 3, -- 3: Full, 2: Server, 1: Guild, 0: Owner
	user INT UNSIGNED NOT NULL,
	PRIMARY KEY(name, server),
	FOREIGN KEY (user) REFERENCES users(id) ON DELETE RESTRICT,
	FOREIGN KEY (guild) REFERENCES guilds(id) ON DELETE SET NULL,
	FOREIGN KEY (class) REFERENCES classes(id) ON DELETE RESTRICT,
	FOREIGN KEY (server) REFERENCES servers(id) ON DELETE RESTRICT
);

CREATE TABLE zones (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
	name NATIONAL VARCHAR(32) NOT NULL,
	capacity INT UNSIGNED NOT NULL,
	game INT UNSIGNED NOT NULL,
	PRIMARY KEY(name, game),
	FOREIGN KEY (game) REFERENCES games(id) ON DELETE CASCADE
);

CREATE TABLE instances (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
	visibility TINYINT UNSIGNED NOT NULL DEFAULT 3, -- 3: Full, 2: Server, 1: Guild, 0: Members
	exclusive BOOLEAN DEFAULT TRUE,
	auto_promote BOOLEAN DEFAULT TRUE,
	zone INT UNSIGNED NOT NULL,
	owner INT UNSIGNED NOT NULL,
	time DATETIME NOT NULL,
	FOREIGN KEY (owner) REFERENCES characters(id) ON DELETE RESTRICT,
	FOREIGN KEY (zone) REFERENCES zones(id) ON DELETE RESTRICT
);

CREATE TABLE instance_members (
	instance INT UNSIGNED NOT NULL,
	member INT UNSIGNED NOT NULL,
	access TINYINT UNSIGNED NOT NULL DEFAULT 1, -- 3: Leader, 2: Officer, 1: Member, 0: Recruit
	time_accepted DATETIME NOT NULL,
	standy_order INT UNSIGNED NOT NULL DEFAULT 0,
	PRIMARY KEY(member, instance),
	FOREIGN KEY (member) REFERENCES characters(id) ON DELETE RESTRICT,
	FOREIGN KEY (instance) REFERENCES instances(id) ON DELETE RESTRICT
);

CREATE TABLE requests (
	sender INT UNSIGNED NOT NULL,
	recipient INT UNSIGNED NOT NULL,
	guild INT UNSIGNED DEFAULT NULL,
	instance INT UNSIGNED DEFAULT NULL,
	opened BOOLEAN NOT NULL DEFAULT FALSE,
	FOREIGN KEY (sender) REFERENCES characters(id) ON DELETE CASCADE,
	FOREIGN KEY (recipient) REFERENCES characters(id) ON DELETE CASCADE,
	FOREIGN KEY (guild) REFERENCES guilds(id) ON DELETE CASCADE,
	FOREIGN KEY (instance) REFERENCES instances(id) ON DELETE CASCADE
);