SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS Combatant;
DROP TABLE IF EXISTS Biography;
DROP TABLE IF EXISTS Stats;
DROP TABLE IF EXISTS Combat_Stats;
DROP TABLE IF EXISTS Combat_Records;
DROP TABLE IF EXISTS Appearance;
DROP TABLE IF EXISTS Hair_Color;
DROP TABLE IF EXISTS Eye_Color;
DROP TABLE IF EXISTS Skin_Tone;
DROP TABLE IF EXISTS Gender; 

CREATE TABLE Gender (
 id	INT(21) UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,
 gender	VARCHAR(111) NOT NULL,
 PRIMARY KEY (id)
) TYPE=InnoDB;

CREATE TABLE Eye_Color (
 id	INT(21) UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,
 color	VARCHAR(111) NOT NULL,
 PRIMARY KEY (id)
) TYPE=InnoDB;

CREATE TABLE Hair_Color (
 id	INT(21) UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,
 color	VARCHAR(111) NOT NULL, 
 PRIMARY KEY (id)
) TYPE=InnoDB;

CREATE TABLE Skin_Tone (
 id	INT(21) UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,
 tone	VARCHAR(111) NOT NULL, 
 PRIMARY KEY (id)
) TYPE=InnoDB;

CREATE TABLE Stats (
 id		INT(21) UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,
 power	 	INT(10) NOT NULL, 
 quickness 	INT(10) NOT NULL, 
 toughness 	INT(10) NOT NULL, 
 intellect 	INT(10) NOT NULL, 
 will	 	INT(10) NOT NULL, 
 presense 	INT(10) NOT NULL, 
 PRIMARY KEY (id)
) TYPE=InnoDB;

CREATE TABLE Combat_Stats (
 id		INT(21) UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,
 attack	 	INT(10) NOT NULL, 
 defense 	INT(10) NOT NULL, 
 accuracy 	INT(10) NOT NULL, 
 evasion 	INT(10) NOT NULL, 
 hit_points 	INT(10) NOT NULL,
 min_damage	INT(10) NOT NULL, 
 max_damage 	INT(10) NOT NULL, 
 critical	INT(10) NOT NULL,
 exp_bonus	INT(10) NOT NULL,
 PRIMARY KEY (id)
) TYPE=InnoDB;

CREATE TABLE Combat_Records (
 id		INT(21) UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,
 experience 	INT(10) NOT NULL, 
 level	 	INT(10) NOT NULL, 
 fights 	INT(10) NOT NULL, 
 wins	 	INT(10) NOT NULL, 
 losses 	INT(10) NOT NULL, 
 draws	 	INT(10) NOT NULL, 
 PRIMARY KEY (id)
) TYPE=InnoDB;

CREATE TABLE Biography (
 id	INT(21) UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,
 height	INT(10) NOT NULL,
 weight	INT(10) NOT NULL,
 age	INT(10) NOT NULL,
 PRIMARY KEY (id)
) TYPE=InnoDB;

CREATE TABLE Appearance (
 id	INT(21) UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,
 hair	INT(21) UNSIGNED NOT NULL,
 eyes	INT(21) UNSIGNED NOT NULL,
 skin	INT(21) UNSIGNED NOT NULL,
 gender	INT(21) UNSIGNED NOT NULL,
 KEY (hair), KEY (eyes), KEY (skin), KEY (gender),
 FOREIGN KEY (hair)	REFERENCES Hair_Color(id),
 FOREIGN KEY (eyes)	REFERENCES Eye_Color(id),
 FOREIGN KEY (skin)	REFERENCES Skin_Tone(id),
 FOREIGN KEY (gender)	REFERENCES Gender(id),
 PRIMARY KEY (id)
) TYPE=InnoDB;

CREATE TABLE Combatant (
 name		VARCHAR(111) UNIQUE NOT NULL,
 bio		INT(21) UNSIGNED NOT NULL,
 stats		INT(21) UNSIGNED NOT NULL,
 combatstats	INT(21) UNSIGNED NOT NULL,
 record		INT(21) UNSIGNED NOT NULL,
 appearance	INT(21) UNSIGNED NOT NULL,
 KEY (bio), KEY (stats), KEY (combatstats), KEY (record), KEY (appearance),
 FOREIGN KEY (bio)		REFERENCES Biography(id),
 FOREIGN KEY (stats)		REFERENCES Stats(id),
 FOREIGN KEY (combatstats)	REFERENCES Combat_Stats(id),
 FOREIGN KEY (record)		REFERENCES Combat_Records(id),
 FOREIGN KEY (appearance)	REFERENCES Appearance(id),
 PRIMARY KEY (name)
) TYPE=InnoDB;

-- BEGIN FILLING TABLES --

INSERT INTO Combatant VALUES
--	(name(PK), bio(fk), stats(fk), combatstats(fk), record(fk), appearance(fk))
	('Greg Hart',		1, 1, 1, 1, 1),
	('Marcus Lorai',	2, 2, 2, 2, 2),
	('Alise Orde',		3, 3, 3, 3, 3),
	('Mary Wilk',		4, 4, 4, 4, 4);

INSERT INTO Biography VALUES
--	(id, height, weight, age)
	(NULL, 71, 216, 17),
	(NULL, 68, 194, 24),
	(NULL, 63, 142, 21),
	(NULL, 61, 131, 18);

INSERT INTO Stats VALUES
--	(id, power, quickness, toughness, intellect, will, presence)
	(NULL, 4, 2, 4, 6, 4, 1),
	(NULL, 5, 4, 1, 5, 5, 1),
	(NULL, 6, 5, 2, 1, 5, 2),
	(NULL, 2, 3, 5, 4, 1, 6);

INSERT INTO Combat_Stats VALUES
--	(id, attack, defense, accuracy, evasion, hit_points, min_damage, max_damage, crit, exp_bonus)
	(NULL, 8, 8, 12, 4, 44, 1, 2, 1714, 8),
	(NULL, 10, 2, 10, 8, 55, 1, 2, 1428, 8),
	(NULL, 12, 4, 2, 10, 55, 1, 3, 285, 12),
	(NULL, 4, 10, 8, 6, 11, 1, 1, 1142, 28);

INSERT INTO Combat_Records VALUES
--	(id, experience, level, fights, wins, losses, draws)
	(NULL, 0, 1, 0, 0, 0, 0),
	(NULL, 0, 1, 0, 0, 0, 0),
	(NULL, 0, 1, 0, 0, 0, 0),
	(NULL, 0, 1, 0, 0, 0, 0);

INSERT INTO Appearance VALUES
--	(id, hair(fk), eyes(fk), skin(fk), gender(fk))
	(NULL, 6, 4, 2, 1),
	(NULL, 2, 2, 6, 1),
	(NULL, 1, 3, 5, 2),
	(NULL, 4, 1, 3, 2);

INSERT INTO Hair_Color VALUES
--	(id, name)
	(NULL, 'Brown'),
	(NULL, 'Blonde'),
	(NULL, 'Red'),
	(NULL, 'Black'),
	(NULL, 'Silver'),
	(NULL, 'White');

INSERT INTO Eye_Color VALUES
--	(id, name)
	(NULL, 'Red'),
	(NULL, 'Blue'),
	(NULL, 'Green'),
	(NULL, 'Brown');

INSERT INTO Skin_Tone VALUES
--	(id, name)
	(NULL, 'Pale'),
	(NULL, 'Light Tan'),
	(NULL, 'Medium Tan'),
	(NULL, 'Dark Tan'),
	(NULL, 'Fair'),
	(NULL, 'Black');

INSERT INTO Gender VALUES
--	(id, name)
	(NULL, 'Male'),
	(NULL, 'Female');

SET FOREIGN_KEY_CHECKS=1;

