CREATE TABLE `accounts` (

`id` INT NOT NULL AUTO_INCREMENT,
`password` VARCHAR(255) NOT NULL/* VARCHAR(32) for MD5*/,

`type` INT NOT NULL DEFAULT 1,
`premdays` INT NOT NULL DEFAULT 0,
`lastday` INT UNSIGNED NOT NULL DEFAULT 0,
`key` VARCHAR(20) NOT NULL DEFAULT '0',
`email` VARCHAR(255) NOT NULL DEFAULT '',
`blocked` TINYINT(1) NOT NULL DEFAULT FALSE,
`warnings` INT NOT NULL DEFAULT 0,
PRIMARY KEY (`id`)

)
ENGINE = InnoDB;


INSERT INTO `accounts` VALUES (1, '1', 1, 65535, 0, '0', '', 0, 0);


CREATE TABLE `players` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`group_id` INT NOT NULL DEFAULT 1,
`account_id` INT NOT NULL DEFAULT 0,

`level` INT NOT NULL DEFAULT 1,
`vocation` INT NOT NULL DEFAULT 0,
`health` INT NOT NULL DEFAULT 150,
`healthmax` INT NOT NULL DEFAULT 150,
`experience` INT NOT NULL DEFAULT 0,

`lookbody` INT NOT NULL DEFAULT 136,

`lookfeet` INT NOT NULL DEFAULT 0,

`lookhead` INT NOT NULL DEFAULT 0,

`looklegs` INT NOT NULL DEFAULT 0,

`looktype` INT NOT NULL DEFAULT 0,

`lookaddons` INT NOT NULL DEFAULT 0,

`maglevel` INT NOT NULL DEFAULT 0,

`mana` INT NOT NULL DEFAULT 0,

`manamax` INT NOT NULL DEFAULT 0,
`manaspent` INT NOT NULL DEFAULT 0,
`soul` INT UNSIGNED NOT NULL DEFAULT 0,
`town_id` INT NOT NULL DEFAULT 0,

`posx` INT NOT NULL DEFAULT 0,
`posy` INT NOT NULL DEFAULT 0,

`posz` INT NOT NULL DEFAULT 0,

`conditions` blob NOT NULL,

`cap` INT NOT NULL DEFAULT 0,
`sex` INT NOT NULL DEFAULT 0,

`lastlogin` INT UNSIGNED NOT NULL DEFAULT 0,
`lastip` INT UNSIGNED NOT NULL DEFAULT 0,
`save` TINYINT(1) UNSIGNED NOT NULL DEFAULT '1',
`redskull` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
`redskulltime` INT NOT NULL DEFAULT 0,

`rank_id` INT NOT NULL DEFAULT 0,

`guildnick` VARCHAR(255) NOT NULL DEFAULT '',
`lastlogout` INT UNSIGNED NOT NULL DEFAULT 0,

`blessings` TINYINT(4) UNSIGNED NOT NULL DEFAULT 0,
`direction` INT UNSIGNED NOT NULL DEFAULT 0,

PRIMARY KEY (`id`),
KEY (`name`),

FOREIGN KEY (`account_id`) REFERENCES `accounts`(`id`) ON DELETE CASCADE

)
ENGINE = InnoDB;


INSERT INTO `players` VALUES (1, 'Account Manager', 1, 1, 1, 0, 150, 150, 0, 0, 0, 0, 0, 110, 0, 0, 0, 0, 0, 0, 0, 50, 50, 7, '', 400, 0, 0, 0, 0, 0, 0, 0, '', 0, 0, 0);


CREATE TABLE `bans` (
`type` INT NOT NULL COMMENT 'this field defines if its ip, account, player, or any else ban',
`ip` INT UNSIGNED NOT NULL DEFAULT 0,
`mask` INT UNSIGNED NOT NULL DEFAULT 4294967295,
`player` INT UNSIGNED NOT NULL DEFAULT 0,
`account` INT UNSIGNED NOT NULL DEFAULT 0,
`time` INT UNSIGNED NOT NULL DEFAULT 0,
`reason_id` INT NOT NULL DEFAULT 0,
`action_id` INT NOT NULL DEFAULT 0,
`comment` VARCHAR(60) NOT NULL DEFAULT '',

`banned_by` INT UNSIGNED NOT NULL DEFAULT 0

)
ENGINE = InnoDB;

CREATE TABLE `groups` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(255) NOT NULL COMMENT 'group name',
	`flags` BIGINT UNSIGNED NOT NULL DEFAULT 0,
	`access` INT NOT NULL,
	`maxdepotitems` INT NOT NULL,
	`maxviplist` INT NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE = InnoDB;

INSERT INTO `groups` VALUES (2, 'Gamemaster', 137438953471, 1, 0, 0);
INSERT INTO `groups` VALUES (1, 'Player', 0, 0, 0, 0);

CREATE TABLE `guilds` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(255) NOT NULL COMMENT 'guild name - nothing else needed here',
	`ownerid` INT NOT NULL,
	`creationdata` INT NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE = InnoDB;

CREATE TABLE `guild_invites` (
	`player_id` INT UNSIGNED NOT NULL DEFAULT 0,
	`guild_id` INT UNSIGNED NOT NULL DEFAULT 0
) ENGINE = InnoDB;

CREATE TABLE `guild_ranks` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`guild_id` INT NOT NULL COMMENT 'guild',
	`name` VARCHAR(255) NOT NULL COMMENT 'rank name',
	`level` INT NOT NULL COMMENT 'rank level - leader, vice, member, maybe something else',
	PRIMARY KEY (`id`),
	FOREIGN KEY (`guild_id`) REFERENCES `guilds`(`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE `house_lists` (
	`house_id` INT NOT NULL,
	`listid` INT NOT NULL,
	`list` TEXT NOT NULL
) ENGINE = InnoDB;

CREATE TABLE `houses` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`owner` INT NOT NULL,
	`paid` INT UNSIGNED NOT NULL DEFAULT 0,
	`warnings` TEXT NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE = InnoDB;

CREATE TABLE `player_depotitems` (
	`player_id` INT NOT NULL,
	`depotid` INT NOT NULL DEFAULT 0,
	`sid` INT NOT NULL COMMENT 'any given range eg 0-100 will be reserved for depot lockers and all > 100 will be then normal items inside depots',
	`pid` INT NOT NULL DEFAULT 0,
	`itemtype` INT NOT NULL,
	`count` INT NOT NULL DEFAULT 0,
	`attributes` BLOB,
	`writer` TEXT NOT NULL DEFAULT '',
	`date` INT UNSIGNED NOT NULL DEFAULT '0',
	FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE,
	KEY (`player_id`, `depotid`)
) ENGINE = InnoDB;

CREATE TABLE `player_items` (
	`player_id` INT NOT NULL DEFAULT 0,
	`pid` INT NOT NULL DEFAULT 0,
	`sid` INT NOT NULL DEFAULT 0,
	`itemtype` INT NOT NULL DEFAULT 0,
	`count` INT NOT NULL DEFAULT 0,
	`attributes` text NOT NULL,
	`writer` text NOT NULL,
	`date` INT UNSIGNED NOT NULL DEFAULT 0,
	FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE `player_skills` (
	`player_id` INT NOT NULL DEFAULT 0,
	`skillid` tinyint(4) NOT NULL DEFAULT 0,
	`value` INT UNSIGNED NOT NULL DEFAULT 0,
	`count` INT UNSIGNED NOT NULL DEFAULT 0,
	FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE `player_spells` (
	`player_id` INT NOT NULL,
	`name` VARCHAR(255) NOT NULL,
	 FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE `player_storage` (
	`player_id` INT NOT NULL DEFAULT 0,
	`key` INT UNSIGNED NOT NULL DEFAULT 0,
	`value` INT NOT NULL DEFAULT 0,
	FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE `player_viplist` (
	`player_id` INT NOT NULL COMMENT 'id of player whose viplist entry it is',
	`vip_id` INT NOT NULL COMMENT 'id of target player of viplist entry',
	FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE,
	FOREIGN KEY (`vip_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE `tile_items` (
	`tile_id` INT NOT NULL,
	`sid` INT NOT NULL,
	`pid` INT NOT NULL DEFAULT 0,
	`itemtype` INT NOT NULL,
	`count` INT NOT NULL DEFAULT 0,
	`attributes` BLOB
) ENGINE = InnoDB;

CREATE TABLE `tiles` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`x` INT NOT NULL,
	`y` INT NOT NULL,
	`z` INT NOT NULL,
	PRIMARY KEY(`id`)
) ENGINE = InnoDB;