/*
Navicat MySQL Data Transfer

Source Server         : Versus Linux
Source Server Version : 50547
Source Host           : localhost:3306
Source Database       : auth

Target Server Type    : MYSQL
Target Server Version : 50547
File Encoding         : 65001

Date: 2016-05-22 04:26:09
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `account`
-- ----------------------------
DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
`id`  int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Identifier' ,
`username`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,
`sha_pass_hash`  varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,
`sessionkey`  varchar(80) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,
`v`  varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,
`s`  varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,
`token_key`  varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,
`email`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,
`reg_mail`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,
`joindate`  timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ,
`last_ip`  varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '127.0.0.1' ,
`last_attempt_ip`  varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '127.0.0.1' ,
`failed_logins`  int(10) UNSIGNED NOT NULL DEFAULT 0 ,
`locked`  tinyint(3) UNSIGNED NOT NULL DEFAULT 0 ,
`lock_country`  varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '00' ,
`last_login`  timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ,
`online`  tinyint(3) UNSIGNED NOT NULL DEFAULT 0 ,
`expansion`  tinyint(3) UNSIGNED NOT NULL DEFAULT 2 ,
`mutetime`  bigint(20) NOT NULL DEFAULT 0 ,
`mutereason`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,
`muteby`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,
`locale`  tinyint(3) UNSIGNED NOT NULL DEFAULT 0 ,
`os`  varchar(3) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,
`recruiter`  int(10) UNSIGNED NOT NULL DEFAULT 0 ,
PRIMARY KEY (`id`),
UNIQUE INDEX `idx_username` (`username`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
COMMENT='Account System'
AUTO_INCREMENT=4491

;

-- ----------------------------
-- Table structure for `account_access`
-- ----------------------------
DROP TABLE IF EXISTS `account_access`;
CREATE TABLE `account_access` (
`id`  int(10) UNSIGNED NOT NULL ,
`gmlevel`  tinyint(3) UNSIGNED NOT NULL ,
`RealmID`  int(11) NOT NULL DEFAULT '-1' ,
PRIMARY KEY (`id`, `RealmID`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci

;

-- ----------------------------
-- Table structure for `account_banned`
-- ----------------------------
DROP TABLE IF EXISTS `account_banned`;
CREATE TABLE `account_banned` (
`id`  int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Account id' ,
`bandate`  int(10) UNSIGNED NOT NULL DEFAULT 0 ,
`unbandate`  int(10) UNSIGNED NOT NULL DEFAULT 0 ,
`bannedby`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`banreason`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`active`  tinyint(3) UNSIGNED NOT NULL DEFAULT 1 ,
PRIMARY KEY (`id`, `bandate`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
COMMENT='Ban List'

;

-- ----------------------------
-- Table structure for `account_muted`
-- ----------------------------
DROP TABLE IF EXISTS `account_muted`;
CREATE TABLE `account_muted` (
`guid`  int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Global Unique Identifier' ,
`mutedate`  int(10) UNSIGNED NOT NULL DEFAULT 0 ,
`mutetime`  int(10) UNSIGNED NOT NULL DEFAULT 0 ,
`mutedby`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`mutereason`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
PRIMARY KEY (`guid`, `mutedate`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
COMMENT='mute List'

;

-- ----------------------------
-- Table structure for `autobroadcast`
-- ----------------------------
DROP TABLE IF EXISTS `autobroadcast`;
CREATE TABLE `autobroadcast` (
`realmid`  int(11) NOT NULL DEFAULT '-1' ,
`id`  tinyint(3) UNSIGNED NOT NULL AUTO_INCREMENT ,
`weight`  tinyint(3) UNSIGNED NULL DEFAULT 1 ,
`text`  longtext CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
PRIMARY KEY (`id`, `realmid`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=7

;

-- ----------------------------
-- Table structure for `ip_banned`
-- ----------------------------
DROP TABLE IF EXISTS `ip_banned`;
CREATE TABLE `ip_banned` (
`ip`  varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '127.0.0.1' ,
`bandate`  int(10) UNSIGNED NOT NULL ,
`unbandate`  int(10) UNSIGNED NOT NULL ,
`bannedby`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '[Console]' ,
`banreason`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'no reason' ,
PRIMARY KEY (`ip`, `bandate`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
COMMENT='Banned IPs'

;

-- ----------------------------
-- Table structure for `ip2nation`
-- ----------------------------
DROP TABLE IF EXISTS `ip2nation`;
CREATE TABLE `ip2nation` (
`ip`  int(11) UNSIGNED NOT NULL DEFAULT 0 ,
`country`  char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,
INDEX `ip` (`ip`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci

;

-- ----------------------------
-- Table structure for `ip2nationCountries`
-- ----------------------------
DROP TABLE IF EXISTS `ip2nationCountries`;
CREATE TABLE `ip2nationCountries` (
`code`  varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,
`iso_code_2`  varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,
`iso_code_3`  varchar(3) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' ,
`iso_country`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,
`country`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,
`lat`  float NOT NULL DEFAULT 0 ,
`lon`  float NOT NULL DEFAULT 0 ,
PRIMARY KEY (`code`),
INDEX `code` (`code`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci

;

-- ----------------------------
-- Table structure for `logs`
-- ----------------------------
DROP TABLE IF EXISTS `logs`;
CREATE TABLE `logs` (
`time`  int(10) UNSIGNED NOT NULL ,
`realm`  int(10) UNSIGNED NOT NULL ,
`type`  varchar(250) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`level`  tinyint(3) UNSIGNED NOT NULL DEFAULT 0 ,
`string`  text CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci

;

-- ----------------------------
-- Table structure for `logs_ip_actions`
-- ----------------------------
DROP TABLE IF EXISTS `logs_ip_actions`;
CREATE TABLE `logs_ip_actions` (
`id`  int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Unique Identifier' ,
`account_id`  int(10) UNSIGNED NOT NULL COMMENT 'Account ID' ,
`character_guid`  int(10) UNSIGNED NOT NULL COMMENT 'Character Guid' ,
`type`  tinyint(3) UNSIGNED NOT NULL ,
`ip`  varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '127.0.0.1' ,
`systemnote`  text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT 'Notes inserted by system' ,
`unixtime`  int(10) UNSIGNED NOT NULL COMMENT 'Unixtime' ,
`time`  timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Timestamp' ,
`comment`  text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT 'Allows users to add a comment' ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
COMMENT='Used to log ips of individual actions'
AUTO_INCREMENT=1

;

-- ----------------------------
-- Table structure for `rbac_account_permissions`
-- ----------------------------
DROP TABLE IF EXISTS `rbac_account_permissions`;
CREATE TABLE `rbac_account_permissions` (
`accountId`  int(10) UNSIGNED NOT NULL COMMENT 'Account id' ,
`permissionId`  int(10) UNSIGNED NOT NULL COMMENT 'Permission id' ,
`granted`  tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Granted = 1, Denied = 0' ,
`realmId`  int(11) NOT NULL DEFAULT '-1' COMMENT 'Realm Id, -1 means all' ,
PRIMARY KEY (`accountId`, `permissionId`, `realmId`),
FOREIGN KEY (`accountId`) REFERENCES `account` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
FOREIGN KEY (`permissionId`) REFERENCES `rbac_permissions` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
INDEX `fk__rbac_account_roles__rbac_permissions` (`permissionId`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
COMMENT='Account-Permission relation'

;

-- ----------------------------
-- Table structure for `rbac_default_permissions`
-- ----------------------------
DROP TABLE IF EXISTS `rbac_default_permissions`;
CREATE TABLE `rbac_default_permissions` (
`secId`  int(10) UNSIGNED NOT NULL COMMENT 'Security Level id' ,
`permissionId`  int(10) UNSIGNED NOT NULL COMMENT 'permission id' ,
`realmId`  int(11) NOT NULL DEFAULT '-1' COMMENT 'Realm Id, -1 means all' ,
PRIMARY KEY (`secId`, `permissionId`, `realmId`),
FOREIGN KEY (`permissionId`) REFERENCES `rbac_permissions` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
INDEX `fk__rbac_default_permissions__rbac_permissions` (`permissionId`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
COMMENT='Default permission to assign to different account security levels'

;

-- ----------------------------
-- Table structure for `rbac_linked_permissions`
-- ----------------------------
DROP TABLE IF EXISTS `rbac_linked_permissions`;
CREATE TABLE `rbac_linked_permissions` (
`id`  int(10) UNSIGNED NOT NULL COMMENT 'Permission id' ,
`linkedId`  int(10) UNSIGNED NOT NULL COMMENT 'Linked Permission id' ,
PRIMARY KEY (`id`, `linkedId`),
FOREIGN KEY (`id`) REFERENCES `rbac_permissions` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
FOREIGN KEY (`linkedId`) REFERENCES `rbac_permissions` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
INDEX `fk__rbac_linked_permissions__rbac_permissions1` (`id`) USING BTREE ,
INDEX `fk__rbac_linked_permissions__rbac_permissions2` (`linkedId`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
COMMENT='Permission - Linked Permission relation'

;

-- ----------------------------
-- Table structure for `rbac_permissions`
-- ----------------------------
DROP TABLE IF EXISTS `rbac_permissions`;
CREATE TABLE `rbac_permissions` (
`id`  int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Permission id' ,
`name`  varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'Permission name' ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
COMMENT='Permission List'

;

-- ----------------------------
-- Table structure for `realmcharacters`
-- ----------------------------
DROP TABLE IF EXISTS `realmcharacters`;
CREATE TABLE `realmcharacters` (
`realmid`  int(10) UNSIGNED NOT NULL DEFAULT 0 ,
`acctid`  int(10) UNSIGNED NOT NULL ,
`numchars`  tinyint(3) UNSIGNED NOT NULL DEFAULT 0 ,
PRIMARY KEY (`realmid`, `acctid`),
INDEX `acctid` (`acctid`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
COMMENT='Realm Character Tracker'

;

-- ----------------------------
-- Table structure for `realmlist`
-- ----------------------------
DROP TABLE IF EXISTS `realmlist`;
CREATE TABLE `realmlist` (
`id`  int(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
`name`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,
`address`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '127.0.0.1' ,
`localAddress`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '127.0.0.1' ,
`localSubnetMask`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '255.255.255.0' ,
`port`  smallint(5) UNSIGNED NOT NULL DEFAULT 8085 ,
`icon`  tinyint(3) UNSIGNED NOT NULL DEFAULT 0 ,
`flag`  tinyint(3) UNSIGNED NOT NULL DEFAULT 2 ,
`timezone`  tinyint(3) UNSIGNED NOT NULL DEFAULT 0 ,
`allowedSecurityLevel`  tinyint(3) UNSIGNED NOT NULL DEFAULT 0 ,
`population`  float UNSIGNED NOT NULL DEFAULT 0 ,
`gamebuild`  int(10) UNSIGNED NOT NULL DEFAULT 12340 ,
PRIMARY KEY (`id`),
UNIQUE INDEX `idx_name` (`name`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
COMMENT='Realm System'
AUTO_INCREMENT=5

;

-- ----------------------------
-- Table structure for `updates`
-- ----------------------------
DROP TABLE IF EXISTS `updates`;
CREATE TABLE `updates` (
`name`  varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'filename with extension of the update.' ,
`hash`  char(40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT 'sha1 hash of the sql file.' ,
`state`  enum('RELEASED','ARCHIVED') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'RELEASED' COMMENT 'defines if an update is released or archived.' ,
`timestamp`  timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'timestamp when the query was applied.' ,
`speed`  int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'time the query takes to apply in ms.' ,
PRIMARY KEY (`name`)
)
ENGINE=MyISAM
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
COMMENT='List of all applied updates in this database.'

;

-- ----------------------------
-- Table structure for `updates_include`
-- ----------------------------
DROP TABLE IF EXISTS `updates_include`;
CREATE TABLE `updates_include` (
`path`  varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'directory to include. $ means relative to the source directory.' ,
`state`  enum('RELEASED','ARCHIVED') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'RELEASED' COMMENT 'defines if the directory contains released or archived updates.' ,
PRIMARY KEY (`path`)
)
ENGINE=MyISAM
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
COMMENT='List of directories where we want to include sql updates.'

;

-- ----------------------------
-- Table structure for `uptime`
-- ----------------------------
DROP TABLE IF EXISTS `uptime`;
CREATE TABLE `uptime` (
`realmid`  int(10) UNSIGNED NOT NULL ,
`starttime`  int(10) UNSIGNED NOT NULL DEFAULT 0 ,
`uptime`  int(10) UNSIGNED NOT NULL DEFAULT 0 ,
`maxplayers`  smallint(5) UNSIGNED NOT NULL DEFAULT 0 ,
`revision`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'Trinitycore' ,
PRIMARY KEY (`realmid`, `starttime`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
COMMENT='Uptime system'

;

-- ----------------------------
-- Auto increment value for `account`
-- ----------------------------
ALTER TABLE `account` AUTO_INCREMENT=4491;

-- ----------------------------
-- Auto increment value for `autobroadcast`
-- ----------------------------
ALTER TABLE `autobroadcast` AUTO_INCREMENT=7;

-- ----------------------------
-- Auto increment value for `logs_ip_actions`
-- ----------------------------
ALTER TABLE `logs_ip_actions` AUTO_INCREMENT=1;

-- ----------------------------
-- Auto increment value for `realmlist`
-- ----------------------------
ALTER TABLE `realmlist` AUTO_INCREMENT=5;
