--
-- init.sql -- initialize database
--
-- Copyright (c) 2013-2018 David Demelier <markand@malikania.fr>
--
-- Permission to use, copy, modify, and/or distribute this software for any
-- purpose with or without fee is hereby granted, provided that the above
-- copyright notice and this permission notice appear in all copies.
--
-- THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES
-- WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF
-- MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR
-- ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES
-- WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN
-- ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF
-- OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.
--

CREATE TABLE IF NOT EXISTS account(
	id SERIAL,
	login TEXT NOT NULL,
	password TEXT NOT NULL,
	firstname TEXT,
	lastname TEXT,
	email TEXT,
	PRIMARY KEY(id)
);

CREATE TABLE IF NOT EXISTS character(
	id SERIAL,
	account_id INTEGER NOT NULL,
	nickname TEXT NOT NULL,
	type TEXT NOT NULL,
	hp_level SMALLINT NOT NULL DEFAULT 1,
	hp_factor SMALLINT NOT NULL,
	hp_exp INT NOT NULL,
	force_level SMALLINT NOT NULL DEFAULT 1,
	force_factor SMALLINT NOT NULL,
	force_exp INT NOT NULL,
	defense_level SMALLINT NOT NULL DEFAULT 1,
	defense_factor SMALLINT NOT NULL,
	defense_exp INT NOT NULL,
	agility_level SMALLINT NOT NULL DEFAULT 1,
	agility_factor SMALLINT NOT NULL,
	agility_exp INT NOT NULL,
	luck_level SMALLINT NOT NULL DEFAULT 1,
	luck_factor SMALLINT NOT NULL,
	luck_exp INT NOT NULL,
	PRIMARY KEY(id),
	FOREIGN KEY (account_id) REFERENCES account(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS spell(
	id SERIAL,
	character_id INTEGER NOT NULL,
	type TEXT NOT NULL,
	level SMALLINT NOT NULL,
	PRIMARY KEY(id),
	FOREIGN KEY (character_id) REFERENCES character(id) ON DELETE CASCADE
);