/*
Navicat MySQL Data Transfer

Source Server         : play
Source Server Version : 50142
Source Host           : play.net-7.org:3307
Source Database       : net7_user

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

Date: 2010-03-04 23:56:17
*/

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `account_avatar_forumname`
-- ----------------------------
DROP TABLE IF EXISTS `account_avatar_forumname`;
CREATE TABLE `account_avatar_forumname` (
  `accountID` int(11) DEFAULT NULL,
  `accountName` varchar(40) DEFAULT NULL,
  `avatarName` varchar(40) DEFAULT NULL,
  `forumName` varchar(40) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for `account_infractions`
-- ----------------------------
DROP TABLE IF EXISTS `account_infractions`;
CREATE TABLE `account_infractions` (
  `account_ID` int(10) unsigned NOT NULL,
  `infraction_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `admin_ID` int(10) unsigned NOT NULL,
  `infraction` text NOT NULL,
  `warn_level_increment` int(10) unsigned NOT NULL,
  KEY `Index_AccountID` (`account_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Player infractions';

-- ----------------------------
-- Table structure for `account_status_levels`
-- ----------------------------
DROP TABLE IF EXISTS `account_status_levels`;
CREATE TABLE `account_status_levels` (
  `id` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `account_status` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Reference Table for account status levels';

-- ----------------------------
-- Table structure for `accounts`
-- ----------------------------
DROP TABLE IF EXISTS `accounts`;
CREATE TABLE `accounts` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(40) NOT NULL,
  `password` varchar(40) NOT NULL,
  `status` int(11) NOT NULL DEFAULT '0',
  `formname` varchar(40) NOT NULL DEFAULT 'no_form_name',
  `email` varchar(40) NOT NULL DEFAULT 'noemail@net-7.org',
  `last_login` timestamp NOT NULL DEFAULT '1989-12-30 00:00:00',
  `last_logout` timestamp NOT NULL DEFAULT '1989-12-30 00:00:00',
  `warn_level` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `Index_id` (`id`),
  KEY `Index_name` (`username`),
  KEY `Index_pass` (`password`),
  KEY `Index_Status` (`status`),
  KEY `Index_formName` (`formname`),
  KEY `Index_email` (`email`),
  KEY `Index_warn` (`warn_level`)
) ENGINE=InnoDB AUTO_INCREMENT=3387 DEFAULT CHARSET=latin1 COMMENT='NEVER delete an account';

-- ----------------------------
-- Table structure for `accounts_log`
-- ----------------------------
DROP TABLE IF EXISTS `accounts_log`;
CREATE TABLE `accounts_log` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `gm_user` text NOT NULL,
  `time` datetime NOT NULL,
  `username` varchar(40) NOT NULL,
  `password` varchar(40) NOT NULL,
  `status` int(11) NOT NULL DEFAULT '0',
  `formname` varchar(40) NOT NULL DEFAULT 'no_form_name',
  `email` varchar(40) NOT NULL DEFAULT 'noemail@net-7.org',
  `last_login` timestamp NOT NULL DEFAULT '1989-12-30 00:00:00',
  `last_logout` timestamp NOT NULL DEFAULT '1989-12-30 00:00:00',
  `warn_level` int(10) NOT NULL DEFAULT '0',
  `username_old` varchar(40) NOT NULL,
  `password_old` varchar(40) NOT NULL,
  `status_old` int(11) NOT NULL DEFAULT '0',
  `formname_old` varchar(40) NOT NULL DEFAULT 'no_form_name',
  `email_old` varchar(40) NOT NULL DEFAULT 'noemail@net-7.org',
  `warn_level_old` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `Index_id` (`id`),
  KEY `Index_name` (`username`),
  KEY `Index_pass` (`password`),
  KEY `Index_Status` (`status`),
  KEY `Index_formName` (`formname`),
  KEY `Index_email` (`email`),
  KEY `Index_warn` (`warn_level`)
) ENGINE=InnoDB AUTO_INCREMENT=937 DEFAULT CHARSET=latin1 COMMENT='NEVER delete an account';

-- ----------------------------
-- Table structure for `avatar_ammo`
-- ----------------------------
DROP TABLE IF EXISTS `avatar_ammo`;
CREATE TABLE `avatar_ammo` (
  `avatar_id` int(11) NOT NULL,
  `equipment_slot` int(11) NOT NULL DEFAULT '0',
  `item_id` int(11) DEFAULT NULL,
  `quality` float(11,4) DEFAULT NULL,
  `ammo_stack` int(11) DEFAULT NULL,
  `builder_name` text,
  `structure` float(11,4) DEFAULT NULL,
  PRIMARY KEY (`avatar_id`,`equipment_slot`),
  KEY `avatar_ammo_ik1` (`avatar_id`,`equipment_slot`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for `avatar_data`
-- ----------------------------
DROP TABLE IF EXISTS `avatar_data`;
CREATE TABLE `avatar_data` (
  `avatar_id` int(10) unsigned NOT NULL COMMENT 'Base 1',
  `first_name` varchar(20) NOT NULL,
  `last_name` varchar(20) NOT NULL,
  `type` int(11) NOT NULL,
  `version` tinyint(4) NOT NULL,
  `race` int(11) NOT NULL,
  `prof` int(11) NOT NULL,
  `gender` int(11) NOT NULL,
  `mood` int(11) NOT NULL,
  `personality` tinyint(4) NOT NULL,
  `nlp` tinyint(4) NOT NULL COMMENT 'No idea what this is',
  `body` tinyint(4) NOT NULL,
  `pants` tinyint(4) NOT NULL,
  `head` tinyint(4) NOT NULL,
  `hair` tinyint(4) NOT NULL,
  `ear` tinyint(4) NOT NULL,
  `goggle` tinyint(4) NOT NULL,
  `beard` tinyint(4) NOT NULL,
  `weapon_hip` tinyint(4) NOT NULL,
  `weapon_unique` tinyint(4) NOT NULL,
  `weapon_back` tinyint(4) NOT NULL,
  `head_texture` tinyint(4) NOT NULL,
  `tattoo_texture` tinyint(4) NOT NULL,
  `tattoo_X` float NOT NULL,
  `tattoo_Y` float NOT NULL,
  `tattoo_Z` float NOT NULL,
  `hair_H` float NOT NULL,
  `hair_S` float NOT NULL,
  `hair_V` float NOT NULL,
  `beard_H` float NOT NULL,
  `beard_S` float NOT NULL,
  `beard_V` float NOT NULL,
  `eye_H` float NOT NULL,
  `eye_S` float NOT NULL,
  `eye_V` float NOT NULL,
  `skin_H` float NOT NULL,
  `skin_S` float NOT NULL,
  `skin_V` float NOT NULL,
  `shirt_p_H` float NOT NULL,
  `shirt_p_S` float NOT NULL,
  `shirt_p_V` float NOT NULL,
  `shirt_s_H` float NOT NULL,
  `shirt_s_S` float NOT NULL,
  `shirt_s_V` float NOT NULL,
  `pants_p_H` float NOT NULL,
  `pants_p_S` float NOT NULL,
  `pants_p_V` float NOT NULL,
  `pants_s_H` float NOT NULL,
  `pants_s_S` float NOT NULL,
  `pants_s_V` float NOT NULL,
  `shirt_p_metal` int(11) NOT NULL,
  `shirt_s_metal` int(11) NOT NULL,
  `pants_p_metal` int(11) NOT NULL,
  `pants_s_metal` int(11) NOT NULL,
  `height_weight_0` float NOT NULL,
  `height_weight_1` float NOT NULL,
  `height_weight_2` float NOT NULL,
  `height_weight_3` float NOT NULL,
  `height_weight_4` float NOT NULL,
  PRIMARY KEY (`avatar_id`),
  CONSTRAINT `FK_avatar_data_1` FOREIGN KEY (`avatar_id`) REFERENCES `avatar_info` (`avatar_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for `avatar_equipment`
-- ----------------------------
DROP TABLE IF EXISTS `avatar_equipment`;
CREATE TABLE `avatar_equipment` (
  `avatar_id` int(11) NOT NULL,
  `equipment_slot` int(11) NOT NULL DEFAULT '0',
  `item_id` int(11) DEFAULT NULL,
  `quality` float(11,4) DEFAULT NULL,
  `builder_name` text,
  `structure` float(11,4) DEFAULT NULL,
  PRIMARY KEY (`avatar_id`,`equipment_slot`),
  KEY `avatar_equipment_ik1` (`avatar_id`,`equipment_slot`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for `avatar_exploration`
-- ----------------------------
DROP TABLE IF EXISTS `avatar_exploration`;
CREATE TABLE `avatar_exploration` (
  `avatar_id` int(11) NOT NULL,
  `object_id` int(11) NOT NULL,
  `explore_flags` tinyint(4) NOT NULL,
  KEY `avatar_exploration_ik1` (`avatar_id`,`object_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for `avatar_faction_level`
-- ----------------------------
DROP TABLE IF EXISTS `avatar_faction_level`;
CREATE TABLE `avatar_faction_level` (
  `avatar_id` int(11) NOT NULL DEFAULT '0',
  `faction_level_list` text,
  PRIMARY KEY (`avatar_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for `avatar_gm_items`
-- ----------------------------
DROP TABLE IF EXISTS `avatar_gm_items`;
CREATE TABLE `avatar_gm_items` (
  `avatar_id` int(11) NOT NULL DEFAULT '0',
  `item_id` int(11) NOT NULL,
  `stack_level` int(11) NOT NULL,
  `trade_stack` int(11) NOT NULL,
  `quality` float(11,4) NOT NULL,
  `cost` int(11) NOT NULL,
  `builder_name` text NOT NULL,
  `structure` float(11,4) NOT NULL,
  PRIMARY KEY (`avatar_id`,`item_id`),
  KEY `avatar_inventory_items_ik1` (`avatar_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for `avatar_gm_items_log`
-- ----------------------------
DROP TABLE IF EXISTS `avatar_gm_items_log`;
CREATE TABLE `avatar_gm_items_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `gm_user` text NOT NULL,
  `time` datetime NOT NULL,
  `avatar_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  `stack_level` int(11) NOT NULL,
  `quality` float(11,4) NOT NULL,
  `cost` int(11) NOT NULL,
  `builder_name` text NOT NULL,
  `structure` float(11,4) NOT NULL,
  `trade_stack` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=543 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for `avatar_info`
-- ----------------------------
DROP TABLE IF EXISTS `avatar_info`;
CREATE TABLE `avatar_info` (
  `avatar_id` int(10) unsigned NOT NULL,
  `account_id` int(10) unsigned NOT NULL,
  `slot` int(10) unsigned NOT NULL,
  `sector` int(10) unsigned NOT NULL,
  `galaxy` int(10) unsigned NOT NULL,
  `count` int(10) unsigned NOT NULL COMMENT 'No idea what this is',
  `admin` int(10) NOT NULL,
  `combat` int(10) unsigned NOT NULL,
  `explore` int(10) unsigned NOT NULL,
  `trade` int(10) unsigned NOT NULL,
  `engine_trail_type` int(10) DEFAULT NULL,
  `last_login` timestamp NOT NULL DEFAULT '1989-12-30 00:00:00',
  `last_logout` timestamp NOT NULL DEFAULT '1989-12-30 00:00:00',
  `last_logout_t` bigint(20) DEFAULT NULL,
  `time_played` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`avatar_id`),
  KEY `FK_avatar_info_1` (`account_id`),
  KEY `Index_sector` (`sector`),
  KEY `Index_galaxy` (`galaxy`),
  KEY `Index_admin` (`admin`),
  KEY `Index_login` (`last_login`),
  KEY `Index_logout` (`last_logout`),
  CONSTRAINT `FK_avatar_info_1` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for `avatar_info_log`
-- ----------------------------
DROP TABLE IF EXISTS `avatar_info_log`;
CREATE TABLE `avatar_info_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `gm_user` text NOT NULL,
  `time` datetime NOT NULL,
  `avatar_id` int(10) unsigned NOT NULL,
  `account_id` int(10) unsigned NOT NULL,
  `slot` int(10) unsigned NOT NULL,
  `sector` int(10) unsigned NOT NULL,
  `galaxy` int(10) unsigned NOT NULL,
  `count` int(10) unsigned NOT NULL COMMENT 'No idea what this is',
  `admin` int(10) NOT NULL,
  `combat` int(10) unsigned NOT NULL,
  `explore` int(10) unsigned NOT NULL,
  `trade` int(10) unsigned NOT NULL,
  `engine_trail_type` int(10) DEFAULT NULL,
  `last_login` timestamp NOT NULL DEFAULT '1989-12-30 00:00:00',
  `last_logout` timestamp NOT NULL DEFAULT '1989-12-30 00:00:00',
  `last_logout_t` bigint(20) DEFAULT NULL,
  `time_played` bigint(20) NOT NULL DEFAULT '0',
  `slot_old` int(10) unsigned NOT NULL,
  `sector_old` int(10) unsigned NOT NULL,
  `galaxy_old` int(10) unsigned NOT NULL,
  `count_old` int(10) unsigned NOT NULL COMMENT 'No idea what this is',
  `admin_old` int(10) NOT NULL,
  `combat_old` int(10) unsigned NOT NULL,
  `explore_old` int(10) unsigned NOT NULL,
  `trade_old` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_avatar_info_1` (`account_id`),
  KEY `Index_sector` (`sector`),
  KEY `Index_galaxy` (`galaxy`),
  KEY `Index_admin` (`admin`),
  KEY `Index_login` (`last_login`),
  KEY `Index_logout` (`last_logout`),
  CONSTRAINT `avatar_info_log_ibfk_1` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=181 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for `avatar_inventory_items`
-- ----------------------------
DROP TABLE IF EXISTS `avatar_inventory_items`;
CREATE TABLE `avatar_inventory_items` (
  `avatar_id` int(11) NOT NULL DEFAULT '0',
  `item_id` int(11) DEFAULT NULL,
  `stack_level` int(11) DEFAULT NULL,
  `inventory_slot` int(11) NOT NULL DEFAULT '0',
  `trade_stack` int(11) DEFAULT NULL,
  `quality` float(11,4) DEFAULT NULL,
  `cost` int(11) DEFAULT NULL,
  `builder_name` text,
  `structure` float(11,4) DEFAULT NULL,
  PRIMARY KEY (`avatar_id`,`inventory_slot`),
  KEY `avatar_inventory_items_ik1` (`avatar_id`,`inventory_slot`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for `avatar_level_info`
-- ----------------------------
DROP TABLE IF EXISTS `avatar_level_info`;
CREATE TABLE `avatar_level_info` (
  `avatar_id` int(11) NOT NULL,
  `player_rank_name` int(11) NOT NULL,
  `hull_upgrade_level` int(11) NOT NULL,
  `hull_points` float NOT NULL,
  `max_hull_points` float NOT NULL,
  `credits` bigint(20) unsigned NOT NULL,
  `cargo_space` int(11) NOT NULL,
  `combat_bar_level` float NOT NULL,
  `explore_bar_level` float NOT NULL,
  `trade_bar_level` float NOT NULL,
  `weapon_slots` int(11) DEFAULT NULL,
  `device_slots` int(11) DEFAULT NULL,
  `skill_points` int(11) DEFAULT NULL,
  `engine_thrust_type` int(11) NOT NULL,
  `warp_power_level` int(11) NOT NULL,
  `registered_starbase` int(11) DEFAULT NULL,
  `reactor_level` float DEFAULT NULL,
  `shield_level` float DEFAULT NULL,
  `xp_debt` int(10) unsigned NOT NULL DEFAULT '0',
  `last_debt` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`avatar_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for `avatar_level_info_log`
-- ----------------------------
DROP TABLE IF EXISTS `avatar_level_info_log`;
CREATE TABLE `avatar_level_info_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `gm_user` text NOT NULL,
  `time` datetime NOT NULL,
  `avatar_id` int(11) NOT NULL,
  `player_rank_name` int(11) NOT NULL,
  `hull_upgrade_level` int(11) NOT NULL,
  `hull_points` float NOT NULL,
  `max_hull_points` float NOT NULL,
  `credits` bigint(20) unsigned NOT NULL,
  `cargo_space` int(11) NOT NULL,
  `combat_bar_level` float NOT NULL,
  `explore_bar_level` float NOT NULL,
  `trade_bar_level` float NOT NULL,
  `weapon_slots` int(11) DEFAULT NULL,
  `device_slots` int(11) DEFAULT NULL,
  `skill_points` int(11) DEFAULT NULL,
  `engine_thrust_type` int(11) NOT NULL,
  `warp_power_level` int(11) NOT NULL,
  `registered_starbase` int(11) DEFAULT NULL,
  `reactor_level` float DEFAULT NULL,
  `shield_level` float DEFAULT NULL,
  `xp_debt` int(10) unsigned NOT NULL DEFAULT '0',
  `last_debt` int(10) unsigned NOT NULL DEFAULT '0',
  `player_rank_name_old` int(11) NOT NULL,
  `hull_upgrade_level_old` int(11) NOT NULL,
  `hull_points_old` float NOT NULL,
  `max_hull_points_old` float NOT NULL,
  `credits_old` bigint(20) unsigned NOT NULL,
  `cargo_space_old` int(11) NOT NULL,
  `combat_bar_level_old` float NOT NULL,
  `explore_bar_level_old` float NOT NULL,
  `trade_bar_level_old` float NOT NULL,
  `weapon_slots_old` int(11) DEFAULT NULL,
  `device_slots_old` int(11) DEFAULT NULL,
  `skill_points_old` int(11) DEFAULT NULL,
  `engine_thrust_type_old` int(11) NOT NULL,
  `warp_power_level_old` int(11) NOT NULL,
  `registered_starbase_old` int(11) DEFAULT NULL,
  `reactor_level_old` float DEFAULT NULL,
  `shield_level_old` float DEFAULT NULL,
  `xp_debt_old` int(10) unsigned NOT NULL DEFAULT '0',
  `last_debt_old` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=107 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for `avatar_mission_progress`
-- ----------------------------
DROP TABLE IF EXISTS `avatar_mission_progress`;
CREATE TABLE `avatar_mission_progress` (
  `avatar_id` int(11) NOT NULL,
  `mission_slot` int(11) NOT NULL DEFAULT '0',
  `mission_id` int(11) DEFAULT NULL,
  `stage_num` int(11) DEFAULT NULL,
  `mission_flags` int(11) DEFAULT NULL,
  PRIMARY KEY (`avatar_id`,`mission_slot`),
  KEY `avatar_mission_progress_ik1` (`avatar_id`,`mission_slot`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for `avatar_position`
-- ----------------------------
DROP TABLE IF EXISTS `avatar_position`;
CREATE TABLE `avatar_position` (
  `avatar_id` int(11) NOT NULL DEFAULT '0',
  `posx` double(12,4) NOT NULL,
  `posy` double(12,4) NOT NULL,
  `posz` double(12,4) NOT NULL,
  `ori_w` double NOT NULL,
  `ori_x` double NOT NULL,
  `ori_y` double NOT NULL,
  `ori_z` double NOT NULL,
  `sector_id` int(11) NOT NULL,
  PRIMARY KEY (`avatar_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for `avatar_position_log`
-- ----------------------------
DROP TABLE IF EXISTS `avatar_position_log`;
CREATE TABLE `avatar_position_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `gm_user` text NOT NULL,
  `time` datetime NOT NULL,
  `avatar_id` int(11) NOT NULL DEFAULT '0',
  `posx` double(12,4) NOT NULL,
  `posy` double(12,4) NOT NULL,
  `posz` double(12,4) NOT NULL,
  `ori_w` double NOT NULL,
  `ori_x` double NOT NULL,
  `ori_y` double NOT NULL,
  `ori_z` double NOT NULL,
  `sector_id` int(11) NOT NULL,
  `posx_old` double(12,4) NOT NULL,
  `posy_old` double(12,4) NOT NULL,
  `posz_old` double(12,4) NOT NULL,
  `ori_w_old` double NOT NULL,
  `ori_x_old` double NOT NULL,
  `ori_y_old` double NOT NULL,
  `ori_z_old` double NOT NULL,
  `sector_id_old` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for `avatar_recipes`
-- ----------------------------
DROP TABLE IF EXISTS `avatar_recipes`;
CREATE TABLE `avatar_recipes` (
  `avatar_id` int(11) NOT NULL DEFAULT '0',
  `item_id` int(11) NOT NULL DEFAULT '0',
  `attempts` int(10) unsigned DEFAULT '0',
  `avg_quality` float DEFAULT '0',
  PRIMARY KEY (`avatar_id`,`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for `avatar_skill_levels`
-- ----------------------------
DROP TABLE IF EXISTS `avatar_skill_levels`;
CREATE TABLE `avatar_skill_levels` (
  `avatar_id` int(11) NOT NULL,
  `skill_id` int(11) NOT NULL DEFAULT '0',
  `skill_level` int(11) DEFAULT NULL,
  PRIMARY KEY (`avatar_id`,`skill_id`),
  KEY `avatar_skill_levels_ik1` (`avatar_id`,`skill_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for `avatar_skill_levels_log`
-- ----------------------------
DROP TABLE IF EXISTS `avatar_skill_levels_log`;
CREATE TABLE `avatar_skill_levels_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `gm_user` text NOT NULL,
  `time` datetime NOT NULL,
  `avatar_id` int(11) NOT NULL,
  `skill_id` int(11) NOT NULL DEFAULT '0',
  `skill_level` int(11) DEFAULT NULL,
  `skill_id_old` int(11) NOT NULL DEFAULT '0',
  `skill_level_old` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `avatar_skill_levels_ik1` (`avatar_id`,`skill_id`)
) ENGINE=InnoDB AUTO_INCREMENT=207 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for `avatar_trade_items`
-- ----------------------------
DROP TABLE IF EXISTS `avatar_trade_items`;
CREATE TABLE `avatar_trade_items` (
  `avatar_id` int(11) NOT NULL DEFAULT '0',
  `item_id` int(11) DEFAULT NULL,
  `stack_level` int(11) DEFAULT NULL,
  `inventory_slot` int(11) NOT NULL DEFAULT '0',
  `trade_stack` int(11) DEFAULT NULL,
  `quality` float(11,4) DEFAULT NULL,
  `cost` int(11) DEFAULT NULL,
  `builder_name` text,
  `structure` float(11,4) DEFAULT NULL,
  PRIMARY KEY (`avatar_id`,`inventory_slot`),
  KEY `avatar_trade_items_ik1` (`avatar_id`,`inventory_slot`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for `avatar_vault_items`
-- ----------------------------
DROP TABLE IF EXISTS `avatar_vault_items`;
CREATE TABLE `avatar_vault_items` (
  `avatar_id` int(11) NOT NULL DEFAULT '0',
  `item_id` int(11) DEFAULT NULL,
  `stack_level` int(11) DEFAULT NULL,
  `inventory_slot` int(11) NOT NULL DEFAULT '0',
  `trade_stack` int(11) DEFAULT NULL,
  `quality` float(11,4) DEFAULT NULL,
  `cost` int(11) DEFAULT NULL,
  `builder_name` text,
  `structure` float(11,4) DEFAULT NULL,
  PRIMARY KEY (`avatar_id`,`inventory_slot`),
  KEY `avatar_vault_items_ik1` (`avatar_id`,`inventory_slot`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for `faction_data`
-- ----------------------------
DROP TABLE IF EXISTS `faction_data`;
CREATE TABLE `faction_data` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `avatar_id` bigint(20) NOT NULL,
  `faction_id` bigint(20) NOT NULL,
  `faction_value` float NOT NULL,
  `faction_order` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`,`avatar_id`,`faction_id`)
) ENGINE=InnoDB AUTO_INCREMENT=240443 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for `faction_data_log`
-- ----------------------------
DROP TABLE IF EXISTS `faction_data_log`;
CREATE TABLE `faction_data_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `gm_user` text NOT NULL,
  `time` datetime NOT NULL,
  `avatar_id` bigint(20) NOT NULL,
  `faction_id` bigint(20) NOT NULL,
  `faction_value` float NOT NULL,
  `faction_order` tinyint(4) NOT NULL,
  `faction_value_old` float NOT NULL,
  `faction_order_old` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=246 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for `factions`
-- ----------------------------
DROP TABLE IF EXISTS `factions`;
CREATE TABLE `factions` (
  `faction_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `description` text,
  `player_PDA` tinyint(4) DEFAULT NULL,
  `PDA_text` text,
  PRIMARY KEY (`faction_id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for `forbidden_names`
-- ----------------------------
DROP TABLE IF EXISTS `forbidden_names`;
CREATE TABLE `forbidden_names` (
  `nickname` varchar(255) NOT NULL,
  PRIMARY KEY (`nickname`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for `friends_lists`
-- ----------------------------
DROP TABLE IF EXISTS `friends_lists`;
CREATE TABLE `friends_lists` (
  `avatar_id` int(10) unsigned NOT NULL,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`avatar_id`,`name`),
  KEY `Index_1` (`avatar_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='friend+ignore list';

-- ----------------------------
-- Table structure for `galaxy`
-- ----------------------------
DROP TABLE IF EXISTS `galaxy`;
CREATE TABLE `galaxy` (
  `id` int(11) NOT NULL DEFAULT '0',
  `galaxy` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for `guild_members`
-- ----------------------------
DROP TABLE IF EXISTS `guild_members`;
CREATE TABLE `guild_members` (
  `avatar_id` int(11) NOT NULL,
  `guild_id` int(11) NOT NULL,
  `rank` int(11) NOT NULL,
  `contribution` int(11) NOT NULL,
  `active` tinyint(4) NOT NULL,
  `tag` varchar(32) NOT NULL,
  PRIMARY KEY (`avatar_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for `guild_ranks`
-- ----------------------------
DROP TABLE IF EXISTS `guild_ranks`;
CREATE TABLE `guild_ranks` (
  `id` int(10) unsigned NOT NULL COMMENT 'guild*10+0-9',
  `name` varchar(64) NOT NULL,
  `permissions` int(11) NOT NULL,
  `maxpromote` int(11) NOT NULL,
  `maxremove` int(11) NOT NULL,
  `mindemote` int(11) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for `guilds`
-- ----------------------------
DROP TABLE IF EXISTS `guilds`;
CREATE TABLE `guilds` (
  `guild_id` int(11) NOT NULL,
  `name` varchar(40) NOT NULL,
  `motd` varchar(128) NOT NULL,
  `points` int(11) NOT NULL,
  `level` int(11) NOT NULL,
  `public` tinyint(4) NOT NULL,
  PRIMARY KEY (`guild_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for `hulls`
-- ----------------------------
DROP TABLE IF EXISTS `hulls`;
CREATE TABLE `hulls` (
  `Race` tinyint(3) unsigned NOT NULL,
  `Profession` tinyint(3) unsigned NOT NULL,
  `upgrade_level` tinyint(3) unsigned NOT NULL,
  `hull_points` int(10) unsigned NOT NULL,
  `weapon_slots` tinyint(3) unsigned NOT NULL,
  `device_slots` tinyint(3) unsigned NOT NULL,
  `cargo_slots` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for `ignore_lists`
-- ----------------------------
DROP TABLE IF EXISTS `ignore_lists`;
CREATE TABLE `ignore_lists` (
  `avatar_id` int(10) unsigned NOT NULL,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`avatar_id`,`name`),
  KEY `Index_1` (`avatar_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for `missions_completed`
-- ----------------------------
DROP TABLE IF EXISTS `missions_completed`;
CREATE TABLE `missions_completed` (
  `avatar_id` int(11) NOT NULL,
  `mission_id` int(11) NOT NULL DEFAULT '0',
  `mission_completion_flags` int(11) DEFAULT NULL,
  PRIMARY KEY (`avatar_id`,`mission_id`),
  KEY `missions_completed_ik1` (`avatar_id`,`mission_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for `professions`
-- ----------------------------
DROP TABLE IF EXISTS `professions`;
CREATE TABLE `professions` (
  `id` int(11) NOT NULL DEFAULT '0',
  `profession` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for `races`
-- ----------------------------
DROP TABLE IF EXISTS `races`;
CREATE TABLE `races` (
  `id` int(11) NOT NULL DEFAULT '0',
  `race` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for `ship_data`
-- ----------------------------
DROP TABLE IF EXISTS `ship_data`;
CREATE TABLE `ship_data` (
  `avatar_id` int(10) unsigned NOT NULL,
  `race` int(11) NOT NULL,
  `prof` int(11) NOT NULL,
  `hull` int(11) NOT NULL,
  `wing` int(11) NOT NULL,
  `decal` int(11) NOT NULL,
  `name` varchar(26) NOT NULL COMMENT 'Shipname, not chatacter',
  `name_H` float NOT NULL,
  `name_S` float NOT NULL,
  `name_V` float NOT NULL,
  `hull_p_H` float NOT NULL,
  `hull_p_S` float NOT NULL,
  `hull_p_V` float NOT NULL,
  `hull_p_flat` tinyint(4) NOT NULL,
  `hull_p_metal` int(11) NOT NULL,
  `hull_s_H` float NOT NULL,
  `hull_s_S` float NOT NULL,
  `hull_s_V` float NOT NULL,
  `hull_s_flat` tinyint(4) NOT NULL,
  `hull_s_metal` int(11) NOT NULL,
  `prof_p_H` float NOT NULL,
  `prof_p_S` float NOT NULL,
  `prof_p_V` float NOT NULL,
  `prof_p_flat` tinyint(3) NOT NULL,
  `prof_p_metal` int(11) NOT NULL,
  `prof_s_H` float NOT NULL,
  `prof_s_S` float NOT NULL,
  `prof_s_V` float NOT NULL,
  `prof_s_flat` tinyint(3) NOT NULL,
  `prof_s_metal` int(11) NOT NULL,
  `wing_p_H` float NOT NULL,
  `wing_p_S` float NOT NULL,
  `wing_p_V` float NOT NULL,
  `wing_p_flat` tinyint(3) NOT NULL,
  `wing_p_metal` int(11) NOT NULL,
  `wing_s_H` float NOT NULL,
  `wing_s_S` float NOT NULL,
  `wing_s_V` float NOT NULL,
  `wing_s_flat` tinyint(3) NOT NULL,
  `wing_s_metal` int(11) NOT NULL,
  `engine_p_H` float NOT NULL,
  `engine_p_S` float NOT NULL,
  `engine_p_V` float NOT NULL,
  `engine_p_flat` tinyint(3) NOT NULL,
  `engine_p_metal` int(11) NOT NULL,
  `engine_s_H` float NOT NULL,
  `engine_s_S` float NOT NULL,
  `engine_s_V` float NOT NULL,
  `engine_s_flat` tinyint(3) NOT NULL,
  `engine_s_metal` int(11) NOT NULL,
  PRIMARY KEY (`avatar_id`),
  CONSTRAINT `FK_ship_data_1` FOREIGN KEY (`avatar_id`) REFERENCES `avatar_info` (`avatar_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for `ship_info`
-- ----------------------------
DROP TABLE IF EXISTS `ship_info`;
CREATE TABLE `ship_info` (
  `avatar_id` int(10) unsigned NOT NULL,
  `hull` int(11) NOT NULL,
  `prof` int(11) NOT NULL,
  `engine` int(11) NOT NULL,
  `wing` int(11) NOT NULL,
  `pos_0` float NOT NULL,
  `pos_1` float NOT NULL,
  `pos_2` float NOT NULL,
  `ori_0` float NOT NULL,
  `ori_1` float NOT NULL,
  `ori_2` float NOT NULL,
  `ori_3` float NOT NULL,
  PRIMARY KEY (`avatar_id`),
  CONSTRAINT `FK_ship_info_1` FOREIGN KEY (`avatar_id`) REFERENCES `avatar_info` (`avatar_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for `skill_list`
-- ----------------------------
DROP TABLE IF EXISTS `skill_list`;
CREATE TABLE `skill_list` (
  `skill_id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'Skill ID',
  `skill_name` varchar(128) NOT NULL COMMENT 'Skill Name',
  PRIMARY KEY (`skill_id`)
) ENGINE=InnoDB AUTO_INCREMENT=63 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for `ssl_deny_list`
-- ----------------------------
DROP TABLE IF EXISTS `ssl_deny_list`;
CREATE TABLE `ssl_deny_list` (
  `deny_addr` char(255) NOT NULL,
  PRIMARY KEY (`deny_addr`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for `status_levels`
-- ----------------------------
DROP TABLE IF EXISTS `status_levels`;
CREATE TABLE `status_levels` (
  `id` tinyint(3) NOT NULL DEFAULT '0',
  `status` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Reference table for admin status levels';

-- ----------------------------
-- Table structure for `warning_levels`
-- ----------------------------
DROP TABLE IF EXISTS `warning_levels`;
CREATE TABLE `warning_levels` (
  `sound_warning_level` int(11) DEFAULT NULL,
  `avatar_id` int(11) NOT NULL,
  PRIMARY KEY (`avatar_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- View structure for `accounts_change`
-- ----------------------------
DROP VIEW IF EXISTS `accounts_change`;
CREATE ALGORITHM=UNDEFINED DEFINER=`david`@`%` SQL SECURITY DEFINER VIEW `accounts_change` AS select `accounts_log`.`id` AS `id`,`accounts_log`.`gm_user` AS `gm_user`,`accounts_log`.`time` AS `time`,if((`accounts_log`.`username` <> `accounts_log`.`username_old`),`accounts_log`.`username`,'') AS `if (accounts_log.username != accounts_log.username_old, accounts_log.username, '')`,if((`accounts_log`.`password` <> `accounts_log`.`password_old`),`accounts_log`.`password`,'') AS `if (accounts_log.``password`` != accounts_log.password_old, accounts_log.password, '')`,(`accounts_log`.`status` - `accounts_log`.`status_old`) AS `accounts_log.``status`` - accounts_log.status_old`,if((`accounts_log`.`formname` <> `accounts_log`.`formname_old`),`accounts_log`.`formname`,'') AS `if (accounts_log.formname != accounts_log.formname_old, accounts_log.formname, '')`,if((`accounts_log`.`email` <> `accounts_log`.`email_old`),`accounts_log`.`email`,'') AS `if (accounts_log.email != accounts_log.email_old, accounts_log.email,'')`,(`accounts_log`.`warn_level` - `accounts_log`.`warn_level_old`) AS `accounts_log.warn_level - accounts_log.warn_level_old` from `accounts_log`;

-- ----------------------------
-- View structure for `avatar_gm_items_change`
-- ----------------------------
DROP VIEW IF EXISTS `avatar_gm_items_change`;
CREATE ALGORITHM=UNDEFINED DEFINER=`david`@`%` SQL SECURITY DEFINER VIEW `avatar_gm_items_change` AS select `avatar_gm_items_log`.`id` AS `id`,`avatar_gm_items_log`.`gm_user` AS `gm_user`,`avatar_gm_items_log`.`time` AS `time`,`avatar_gm_items_log`.`avatar_id` AS `avatar_id`,`avatar_gm_items_log`.`item_id` AS `item_id`,`avatar_gm_items_log`.`stack_level` AS `stack_level`,`avatar_gm_items_log`.`quality` AS `quality`,`avatar_gm_items_log`.`cost` AS `cost`,`avatar_gm_items_log`.`builder_name` AS `builder_name`,`avatar_gm_items_log`.`structure` AS `structure`,`avatar_gm_items_log`.`trade_stack` AS `trade_stack` from `avatar_gm_items_log`;

-- ----------------------------
-- View structure for `avatar_info_change`
-- ----------------------------
DROP VIEW IF EXISTS `avatar_info_change`;
CREATE ALGORITHM=UNDEFINED DEFINER=`david`@`%` SQL SECURITY DEFINER VIEW `avatar_info_change` AS select `avatar_info_log`.`id` AS `id`,`avatar_info_log`.`gm_user` AS `gm_user`,`avatar_info_log`.`time` AS `time`,`avatar_info_log`.`avatar_id` AS `avatar_id`,`avatar_info_log`.`account_id` AS `account_id`,if((`avatar_info_log`.`sector` <> `avatar_info_log`.`sector_old`),`avatar_info_log`.`sector`,0) AS `if (avatar_info_log.sector != avatar_info_log.sector_old, avatar_info_log.sector, 0)`,(`avatar_info_log`.`admin` - `avatar_info_log`.`admin_old`) AS `avatar_info_log.admin - avatar_info_log.admin_old`,(`avatar_info_log`.`combat` - `avatar_info_log`.`combat_old`) AS `avatar_info_log.combat - avatar_info_log.combat_old`,(`avatar_info_log`.`explore` - `avatar_info_log`.`explore_old`) AS `avatar_info_log.explore - avatar_info_log.explore_old`,(`avatar_info_log`.`trade` - `avatar_info_log`.`trade_old`) AS `avatar_info_log.trade - avatar_info_log.trade_old` from `avatar_info_log`;

-- ----------------------------
-- View structure for `avatar_level_info_change`
-- ----------------------------
DROP VIEW IF EXISTS `avatar_level_info_change`;
CREATE ALGORITHM=UNDEFINED DEFINER=`david`@`%` SQL SECURITY DEFINER VIEW `avatar_level_info_change` AS select `avatar_level_info_log`.`id` AS `id`,`avatar_level_info_log`.`gm_user` AS `gm_user`,`avatar_level_info_log`.`time` AS `time`,`avatar_level_info_log`.`avatar_id` AS `avatar_id`,(`avatar_level_info_log`.`player_rank_name` - `avatar_level_info_log`.`player_rank_name_old`) AS `avatar_level_info_log.player_rank_name - avatar_level_info_log.player_rank_name_old`,(`avatar_level_info_log`.`hull_upgrade_level` - `avatar_level_info_log`.`hull_upgrade_level_old`) AS `avatar_level_info_log.hull_upgrade_level - avatar_level_info_log.hull_upgrade_level_old`,(`avatar_level_info_log`.`hull_points` - `avatar_level_info_log`.`hull_points_old`) AS `avatar_level_info_log.hull_points - avatar_level_info_log.hull_points_old`,(`avatar_level_info_log`.`max_hull_points` - `avatar_level_info_log`.`max_hull_points_old`) AS `avatar_level_info_log.max_hull_points - avatar_level_info_log.max_hull_points_old`,(`avatar_level_info_log`.`credits` - `avatar_level_info_log`.`credits_old`) AS `avatar_level_info_log.credits - avatar_level_info_log.credits_old`,(`avatar_level_info_log`.`cargo_space` - `avatar_level_info_log`.`cargo_space_old`) AS `avatar_level_info_log.cargo_space - avatar_level_info_log.cargo_space_old`,(`avatar_level_info_log`.`weapon_slots` - `avatar_level_info_log`.`weapon_slots_old`) AS `avatar_level_info_log.weapon_slots - avatar_level_info_log.weapon_slots_old`,(`avatar_level_info_log`.`device_slots` - `avatar_level_info_log`.`device_slots_old`) AS `avatar_level_info_log.device_slots - avatar_level_info_log.device_slots_old`,(`avatar_level_info_log`.`skill_points` - `avatar_level_info_log`.`skill_points_old`) AS `avatar_level_info_log.skill_points - avatar_level_info_log.skill_points_old` from `avatar_level_info_log`;

-- ----------------------------
-- View structure for `avatar_position_change`
-- ----------------------------
DROP VIEW IF EXISTS `avatar_position_change`;
CREATE ALGORITHM=UNDEFINED DEFINER=`david`@`%` SQL SECURITY DEFINER VIEW `avatar_position_change` AS select `avatar_position_log`.`id` AS `id`,`avatar_position_log`.`gm_user` AS `gm_user`,`avatar_position_log`.`time` AS `time`,`avatar_position_log`.`avatar_id` AS `avatar_id`,if((`avatar_position_log`.`sector_id` <> `avatar_position_log`.`sector_id_old`),`avatar_position_log`.`sector_id`,0) AS `if (avatar_position_log.sector_id != avatar_position_log.sector_id_old, avatar_position_log.sector_id, 0)` from `avatar_position_log`;

-- ----------------------------
-- View structure for `avatar_skill_level_change`
-- ----------------------------
DROP VIEW IF EXISTS `avatar_skill_level_change`;
CREATE ALGORITHM=UNDEFINED DEFINER=`david`@`%` SQL SECURITY DEFINER VIEW `avatar_skill_level_change` AS select `avatar_skill_levels_log`.`id` AS `id`,`avatar_skill_levels_log`.`gm_user` AS `gm_user`,`avatar_skill_levels_log`.`time` AS `time`,`avatar_skill_levels_log`.`avatar_id` AS `avatar_id`,(`avatar_skill_levels_log`.`skill_id` - `avatar_skill_levels_log`.`skill_id_old`) AS `avatar_skill_levels_log.skill_id - avatar_skill_levels_log.skill_id_old`,(`avatar_skill_levels_log`.`skill_level` - `avatar_skill_levels_log`.`skill_level_old`) AS `avatar_skill_levels_log.skill_level - avatar_skill_levels_log.skill_level_old` from `avatar_skill_levels_log`;

-- ----------------------------
-- View structure for `faction_data_log_change`
-- ----------------------------
DROP VIEW IF EXISTS `faction_data_log_change`;
CREATE ALGORITHM=UNDEFINED DEFINER=`david`@`%` SQL SECURITY DEFINER VIEW `faction_data_log_change` AS select `faction_data_log`.`id` AS `id`,`faction_data_log`.`gm_user` AS `gm_user`,`faction_data_log`.`time` AS `time`,`faction_data_log`.`avatar_id` AS `avatar_id`,`faction_data_log`.`faction_id` AS `faction_id`,(`faction_data_log`.`faction_value` - `faction_data_log`.`faction_value_old`) AS `Faction Value Change`,(`faction_data_log`.`faction_order` - `faction_data_log`.`faction_order_old`) AS `Faction Order Change` from `faction_data_log`;

-- ----------------------------
-- View structure for `guild_member_list`
-- ----------------------------
DROP VIEW IF EXISTS `guild_member_list`;
CREATE ALGORITHM=UNDEFINED DEFINER=`david`@`%` SQL SECURITY DEFINER VIEW `guild_member_list` AS select `guild_members`.`rank` AS `rank`,`avatar_data`.`first_name` AS `first_name`,`avatar_info`.`combat` AS `combat`,`avatar_info`.`explore` AS `explore`,`avatar_info`.`trade` AS `trade`,((`avatar_info`.`combat` + `avatar_info`.`explore`) + `avatar_info`.`trade`) AS `OverAll Level`,`avatar_info`.`last_login` AS `last_login`,`avatar_info`.`last_logout` AS `last_logout`,`guilds`.`name` AS `name` from (((`guilds` join `guild_members` on((`guilds`.`guild_id` = `guild_members`.`guild_id`))) join `avatar_data` on((`guild_members`.`avatar_id` = `avatar_data`.`avatar_id`))) join `avatar_info` on((`avatar_data`.`avatar_id` = `avatar_info`.`avatar_id`)));

-- ----------------------------
-- View structure for `online_avatar_list`
-- ----------------------------
DROP VIEW IF EXISTS `online_avatar_list`;
CREATE ALGORITHM=UNDEFINED DEFINER=`david`@`%` SQL SECURITY DEFINER VIEW `online_avatar_list` AS select `avatar_data`.`first_name` AS `first_name`,`races`.`race` AS `race`,`professions`.`profession` AS `profession`,`avatar_info`.`combat` AS `combat`,`avatar_info`.`explore` AS `explore`,`avatar_info`.`trade` AS `trade` from (((`avatar_data` join `avatar_info` on((`avatar_info`.`avatar_id` = `avatar_data`.`avatar_id`))) join `races` on((`avatar_data`.`race` = `races`.`id`))) join `professions` on((`avatar_data`.`prof` = `professions`.`id`))) where (`avatar_info`.`last_login` > `avatar_info`.`last_logout`) order by `avatar_data`.`first_name`;

-- ----------------------------
-- Procedure structure for `accLogin`
-- ----------------------------
DROP PROCEDURE IF EXISTS `accLogin`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `accLogin`(IN accID INTEGER, IN theTime VARCHAR(40))
BEGIN
    UPDATE net7_user.accounts
      SET last_login = theTime
    WHERE id = accID;
  END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for `accLogout`
-- ----------------------------
DROP PROCEDURE IF EXISTS `accLogout`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `accLogout`(IN accID INTEGER, IN theTime VARCHAR(40))
BEGIN
    UPDATE net7_user.accounts
      SET last_logout = theTime
    WHERE id = accID;
  END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for `avaLogin`
-- ----------------------------
DROP PROCEDURE IF EXISTS `avaLogin`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `avaLogin`(IN avaID INTEGER, IN theTime VARCHAR(40))
BEGIN
    UPDATE net7_user.avatar_info
      SET last_login = theTime
    WHERE avatar_id = avaID;
  END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for `avaLogout`
-- ----------------------------
DROP PROCEDURE IF EXISTS `avaLogout`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `avaLogout`(IN avaID INTEGER, IN theTime VARCHAR(40))
BEGIN
    UPDATE net7_user.avatar_info
      SET last_logout = theTime, time_played = time_played + (now() - last_login)
    WHERE avatar_id = avaID;
  END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for `incWarn`
-- ----------------------------
DROP PROCEDURE IF EXISTS `incWarn`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `incWarn`(IN accID INTEGER, IN adminID INTEGER, IN infrac TEXT, IN incAmount INTEGER)
BEGIN
    UPDATE Net7_user.accounts
      SET warn_level = warn_level + incAmount
    WHERE id = accID;

    INSERT INTO Net7_user.account_infractions
      VALUES(accID, NOW(), adminID, infrac, incAmount);
  END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for `logoutOnShutdown`
-- ----------------------------
DROP PROCEDURE IF EXISTS `logoutOnShutdown`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `logoutOnShutdown`(IN theTime VARCHAR(40))
BEGIN
    UPDATE Net7_user.accounts
      SET last_logout = theTime
    WHERE last_login > last_logout;

    UPDATE Net7_user.avatar_info
      SET last_logout = theTime
    WHERE last_login > last_logout;
  END
;;
DELIMITER ;

-- ----------------------------
-- Function structure for `getDPS`
-- ----------------------------
DROP FUNCTION IF EXISTS `getDPS`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `getDPS`(avaID INT(11), cat INT(11), slot INT(11), itemID INT(11)) RETURNS float
    READS SQL DATA
BEGIN
    DECLARE DPS FLOAT;
    DECLARE qMOD FLOAT;

    SELECT quality_mod INTO qMOD FROM net7.item_base WHERE id = itemID;

    SET DPS = 0.0;

    CASE
      
      WHEN cat = 100 THEN
        BEGIN

          SELECT (beam.damage_100 * (1 + (((a.Quality * 100) - 100) * (qMOD - 1) / 100)) / beam.reload_100) INTO DPS
          FROM net7_user.avatar_equipment a
          INNER JOIN net7.item_base ib ON
            a.item_id = ib.id
          INNER JOIN net7.item_beam beam ON
            a.item_id = beam.item_id
          WHERE a.avatar_id = avaID AND
            a.equipment_slot = slot;

          RETURN DPS;
        END;
      
      WHEN cat = 101 THEN
        BEGIN

          SELECT (((ia.damage_100 * (1 + (((aa.Quality * 100) - 100) * (aib.quality_mod - 1) / 100))) * pl.ammo_per_shot) / (pl.reload_100 * (1 -(((ae.Quality * 100) - 100) * (1 - qMOD) / 100)))) INTO DPS
          FROM net7_user.avatar_ammo aa
          INNER JOIN net7_user.avatar_equipment ae ON
            ae.avatar_id = aa.avatar_id AND
            ae.equipment_slot = aa.equipment_slot
          INNER JOIN net7.item_base aib ON
            aa.item_id = aib.id
          INNER JOIN net7.item_ammo ia ON
            aa.item_id = ia.item_id
          INNER JOIN net7.item_projectile pl ON
            ae.item_id = pl.item_id
          WHERE ae.avatar_id = avaID AND
            ae.equipment_slot = slot;

          RETURN DPS;
        END;
      
      WHEN cat = 102 THEN
        BEGIN

          SELECT (((ia.damage_100 * (1 + (((aa.Quality * 100) - 100) * (aib.quality_mod - 1) / 100))) * ml.ammo_per_shot) / (ml.reload_100 * (1 -(((ae.Quality * 100) - 100) * (1 - qMOD) / 100)))) INTO DPS
          FROM net7_user.avatar_ammo aa
          INNER JOIN net7_user.avatar_equipment ae ON
            ae.avatar_id = aa.avatar_id AND
            ae.equipment_slot = aa.equipment_slot
          INNER JOIN net7.item_base aib ON
            aa.item_id = aib.id
          INNER JOIN net7.item_ammo ia ON
            aa.item_id = ia.item_id
          INNER JOIN net7.item_missile ml ON
            ae.item_id = ml.item_id
          WHERE ae.avatar_id = avaID AND
            ae.equipment_slot = slot;

          RETURN DPS;
        END;
    END CASE;

  END
;;
DELIMITER ;

-- ----------------------------
-- Function structure for `isAccLoggedIn`
-- ----------------------------
DROP FUNCTION IF EXISTS `isAccLoggedIn`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `isAccLoggedIn`(theID Int(11)) RETURNS tinyint(1)
    DETERMINISTIC
BEGIN
       DECLARE li TIMESTAMP;
       DECLARE lo TIMESTAMP;
       SELECT last_login INTO li FROM net7_user.accounts WHERE id = theID;
       SELECT last_logout INTO lo FROM net7_user.accounts WHERE id = theID;
       IF li > lo THEN
         RETURN 1;
       ELSE
         RETURN 0;
       END IF;
    END
;;
DELIMITER ;

-- ----------------------------
-- Function structure for `isAvaLoggedIn`
-- ----------------------------
DROP FUNCTION IF EXISTS `isAvaLoggedIn`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `isAvaLoggedIn`(theID Int(11)) RETURNS tinyint(1)
    DETERMINISTIC
BEGIN
       DECLARE li TIMESTAMP;
       DECLARE lo TIMESTAMP;
       SELECT last_login INTO li FROM net7_user.avatar_info WHERE avatar_id = theID;
       SELECT last_logout INTO lo FROM net7_user.avatar_info WHERE avatar_id = theID;
       IF li > lo THEN
         RETURN 1;
       ELSE
         RETURN 0;
       END IF;
    END
;;
DELIMITER ;
DELIMITER ;;
CREATE TRIGGER `gm_accounts_log` BEFORE UPDATE ON `accounts` FOR EACH ROW BEGIN
    IF SUBSTRING_INDEX(USER(),'@',1) <> 'root' THEN
       INSERT INTO accounts_log (gm_user,`time`,username,`password`,`status`,formname,email,last_login,last_logout,warn_level,username_old,password_old,status_old,formname_old,email_old,warn_level_old) VALUES (user(),now(),NEW.username,NEW.`password`,NEW.`status`,NEW.formname,NEW.email,NEW.last_login,NEW.last_logout,NEW.warn_level,OLD.username,OLD.`password`,OLD.`status`,OLD.formname,OLD.email,OLD.warn_level);
    END IF;
END
;;
DELIMITER ;
DELIMITER ;;
CREATE TRIGGER `log` BEFORE INSERT ON `avatar_gm_items` FOR EACH ROW BEGIN
   INSERT INTO avatar_gm_items_log (avatar_id, item_id, stack_level, quality, cost, builder_name, structure, trade_stack,gm_user,time) VALUES (NEW.avatar_id,NEW.item_id,NEW.stack_level,NEW.quality,NEW.cost,NEW.builder_name,NEW.structure,NEW.trade_stack,user(),now());
END
;;
DELIMITER ;
DELIMITER ;;
CREATE TRIGGER `gm_avatar_info_log` BEFORE UPDATE ON `avatar_info` FOR EACH ROW BEGIN
    IF SUBSTRING_INDEX(USER(),'@',1) <> 'root' THEN
       INSERT INTO avatar_info_log (gm_user,`time`,avatar_id,account_id,slot,sector,galaxy,`count`,admin,combat,explore,trade,engine_trail_type,last_login,last_logout,last_logout_t,time_played,slot_old,sector_old,galaxy_old,count_old,admin_old,combat_old,explore_old,trade_old) VALUES (user(),now(),NEW.avatar_id,NEW.account_id,NEW.slot,NEW.sector,NEW.galaxy,NEW.`count`,NEW.admin,NEW.combat,NEW.explore,NEW.trade,NEW.engine_trail_type,NEW.last_login,NEW.last_logout,NEW.last_logout_t,NEW.time_played,OLD.slot,OLD.sector,OLD.galaxy,OLD.`count`,OLD.admin,OLD.combat,OLD.explore,OLD.trade);
    END IF;
END
;;
DELIMITER ;
DELIMITER ;;
CREATE TRIGGER `gm_avatar_level_log` BEFORE UPDATE ON `avatar_level_info` FOR EACH ROW BEGIN
    IF SUBSTRING_INDEX(USER(),'@',1) <> 'root' THEN
       INSERT INTO avatar_level_info_log (avatar_id,player_rank_name,hull_upgrade_level,hull_points,max_hull_points,credits,cargo_space,combat_bar_level,explore_bar_level,trade_bar_level,weapon_slots,device_slots,skill_points,engine_thrust_type,warp_power_level,registered_starbase,reactor_level,shield_level,xp_debt,last_debt, gm_user,time, player_rank_name_old,hull_upgrade_level_old,hull_points_old,max_hull_points_old,credits_old,cargo_space_old,combat_bar_level_old,explore_bar_level_old,trade_bar_level_old,weapon_slots_old,device_slots_old,skill_points_old,engine_thrust_type_old,warp_power_level_old,registered_starbase_old,reactor_level_old,shield_level_old,xp_debt_old,last_debt_old) VALUES (NEW.avatar_id,NEW.player_rank_name,NEW.hull_upgrade_level,NEW.hull_points,NEW.max_hull_points,NEW.credits,NEW.cargo_space,NEW.combat_bar_level,NEW.explore_bar_level,NEW.trade_bar_level,NEW.weapon_slots,NEW.device_slots,NEW.skill_points,NEW.engine_thrust_type,NEW.warp_power_level,NEW.registered_starbase,NEW.reactor_level,NEW.shield_level,NEW.xp_debt,NEW.last_debt,user(),now(), OLD.player_rank_name,OLD.hull_upgrade_level,OLD.hull_points,OLD.max_hull_points,OLD.credits,OLD.cargo_space,OLD.combat_bar_level,OLD.explore_bar_level,OLD.trade_bar_level,OLD.weapon_slots,OLD.device_slots,OLD.skill_points,OLD.engine_thrust_type,OLD.warp_power_level,OLD.registered_starbase,OLD.reactor_level,OLD.shield_level,OLD.xp_debt,OLD.last_debt);
    END IF;
END
;;
DELIMITER ;
DELIMITER ;;
CREATE TRIGGER `gm_avatar_position_log` BEFORE UPDATE ON `avatar_position` FOR EACH ROW BEGIN
    IF SUBSTRING_INDEX(USER(),'@',1) <> 'root' THEN
       INSERT INTO avatar_position_log (gm_user,`time`,avatar_id,posx,posy,posz,ori_w,ori_x,ori_y,ori_z,sector_id,posx_old,posy_old,posz_old,ori_w_old,ori_x_old,ori_y_old,ori_z_old,sector_id_old) VALUES (user(),now(),NEW.avatar_id,NEW.posx,NEW.posy,NEW.posz,NEW.ori_w,NEW.ori_x,NEW.ori_y,NEW.ori_z,NEW.sector_id,OLD.posx,OLD.posy,OLD.posz,OLD.ori_w,OLD.ori_x,OLD.ori_y,OLD.ori_z,OLD.sector_id);
    END IF;
END
;;
DELIMITER ;
DELIMITER ;;
CREATE TRIGGER `gm_avatar_skill_levels_log` BEFORE INSERT ON `avatar_skill_levels` FOR EACH ROW BEGIN
    IF SUBSTRING_INDEX(USER(),'@',1) <> 'root' THEN
       INSERT INTO avatar_skill_levels_log (gm_user,`time`,avatar_id,skill_id,skill_level,skill_id_old,skill_level_old) VALUES (user(),now(),NEW.avatar_id,NEW.skill_id,NEW.skill_level,0,0);
    END IF;
END
;;
DELIMITER ;
DELIMITER ;;
CREATE TRIGGER `gm_avatar_skill_levels` BEFORE UPDATE ON `avatar_skill_levels` FOR EACH ROW BEGIN
    IF SUBSTRING_INDEX(USER(),'@',1) <> 'root' THEN
       INSERT INTO avatar_skill_levels_log (gm_user,`time`,avatar_id,skill_id,skill_level,skill_id_old,skill_level_old) VALUES (user(),now(),NEW.avatar_id,NEW.skill_id,NEW.skill_level,OLD.skill_id,OLD.skill_level);
    END IF;
END
;;
DELIMITER ;
DELIMITER ;;
CREATE TRIGGER `gm_log_factions` BEFORE UPDATE ON `faction_data` FOR EACH ROW BEGIN
    IF SUBSTRING_INDEX(USER(),'@',1) <> 'root' THEN
       INSERT INTO faction_data_log (avatar_id,faction_id, faction_value, faction_order, gm_user,time,faction_value_old, faction_order_old) VALUES (NEW.avatar_id,NEW.faction_id, NEW.faction_value, NEW.faction_order,user(),now(),OLD.faction_value, OLD.faction_order);
    END IF;
END
;;
DELIMITER ;