


CREATE TABLE IF NOT EXISTS `achievements` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `description` text,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `player_achievements` (
  `player_name` text NOT NULL,
  `achievement_id` int(11) NOT NULL,
  `server_id` int(11) NOT NULL,
  `date_awarded` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  UNIQUE KEY `accomplishment_id` (`achievement_id`,`player_name`(50))
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `server` (
  `server_id` int(11) NOT NULL auto_increment,
  `server_name` text collate utf8_unicode_ci,
  `ip_address` text collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`server_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1985 ;

CREATE TABLE IF NOT EXISTS `stats_game` (
  `stats_game_id` int(11) NOT NULL auto_increment,
  `server_id` int(11) NOT NULL,
  `game_type` text collate utf8_unicode_ci NOT NULL,
  `is_official` tinyint(1) NOT NULL,
  `player_count` smallint(5) unsigned NOT NULL,
  `duration_seconds` smallint(5) unsigned NOT NULL,
  `level_name` text collate utf8_unicode_ci NOT NULL,
  `is_team_game` tinyint(1) NOT NULL,
  `team_count` tinyint(3) unsigned default NULL,
  `insertion_date` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`stats_game_id`),
  KEY `server_id` (`server_id`),
  KEY `is_official` (`is_official`),
  KEY `player_count` (`player_count`),
  KEY `is_team_game` (`is_team_game`),
  KEY `team_count` (`team_count`),
  KEY `insertion_date` (`insertion_date`),
  KEY `game_type` (`game_type`(20))
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=33112 ;

CREATE TABLE IF NOT EXISTS `stats_level` (
  `stats_level_id` int(11) NOT NULL auto_increment,
  `level_name` varchar(255) default NULL,
  `creator` varchar(255) NOT NULL,
  `hash` varchar(32) NOT NULL,
  `game_type` varchar(32) NOT NULL,
  `has_levelgen` tinyint(1) NOT NULL,
  `team_count` int(11) NOT NULL,
  `winning_score` int(11) NOT NULL,
  `game_duration` int(11) NOT NULL,
  PRIMARY KEY  (`stats_level_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=91 ;

CREATE TABLE IF NOT EXISTS `stats_player` (
  `stats_player_id` int(11) NOT NULL auto_increment,
  `stats_game_id` int(11) NOT NULL,
  `player_name` text collate utf8_unicode_ci NOT NULL,
  `is_authenticated` tinyint(1) NOT NULL,
  `is_robot` tinyint(1) NOT NULL,
  `result` char(1) collate utf8_unicode_ci NOT NULL,
  `points` int(11) NOT NULL,
  `kill_count` smallint(5) unsigned NOT NULL,
  `death_count` smallint(5) unsigned NOT NULL,
  `suicide_count` smallint(5) unsigned NOT NULL,
  `asteroid_crashes` smallint(11) NOT NULL,
  `flag_drops` smallint(11) NOT NULL,
  `flag_pickups` smallint(11) NOT NULL,
  `flag_returns` smallint(11) NOT NULL,
  `flag_scores` smallint(11) NOT NULL,
  `teleport_uses` smallint(11) NOT NULL,
  `switched_team_count` smallint(5) default NULL,
  `stats_team_id` int(11) default NULL,
  `insertion_date` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`stats_player_id`),
  KEY `is_authenticated` (`is_authenticated`),
  KEY `result` (`result`),
  KEY `stats_team_id` (`stats_team_id`),
  KEY `insertion_date` (`insertion_date`),
  KEY `player_name` (`player_name`(50)),
  KEY `is_robot` (`is_robot`),
  KEY `stats_game_id` (`stats_game_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=117543 ;

CREATE TABLE IF NOT EXISTS `stats_player_shots` (
  `stats_player_shots_id` int(11) NOT NULL auto_increment,
  `stats_player_id` int(11) NOT NULL,
  `weapon` text collate utf8_unicode_ci NOT NULL,
  `shots` int(11) NOT NULL,
  `shots_struck` int(11) NOT NULL,
  PRIMARY KEY  (`stats_player_shots_id`),
  UNIQUE KEY `stats_player_id` (`stats_player_id`,`weapon`(10)),
  KEY `weapon` (`weapon`(10))
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=172009 ;

CREATE TABLE IF NOT EXISTS `stats_team` (
  `stats_team_id` int(11) NOT NULL auto_increment,
  `stats_game_id` int(11) NOT NULL,
  `team_name` text collate utf8_unicode_ci,
  `color_hex` text collate utf8_unicode_ci NOT NULL,
  `team_score` int(11) default NULL,
  `result` char(1) collate utf8_unicode_ci default NULL,
  `insertion_date` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`stats_team_id`),
  KEY `stats_game_id` (`stats_game_id`),
  KEY `result` (`result`),
  KEY `insertion_date` (`insertion_date`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=61736 ;
CREATE TABLE IF NOT EXISTS `v_authenticated_game` (
`stats_game_id` int(11)
,`authenticated_count` bigint(21)
);CREATE TABLE IF NOT EXISTS `v_current_week_top_player_games` (
`player_name` text
,`game_count` bigint(21)
,`is_authenticated` tinyint(1)
);CREATE TABLE IF NOT EXISTS `v_current_week_top_player_official_wins` (
`player_name` text
,`win_count` bigint(21)
,`is_authenticated` tinyint(1)
);CREATE TABLE IF NOT EXISTS `v_last_week_top_player_games` (
`player_name` text
,`game_count` bigint(21)
,`is_authenticated` tinyint(1)
);CREATE TABLE IF NOT EXISTS `v_last_week_top_player_official_wins` (
`player_name` text
,`win_count` bigint(21)
,`is_authenticated` tinyint(1)
);CREATE TABLE IF NOT EXISTS `v_official_game` (
`stats_game_id` int(11)
);DROP TABLE IF EXISTS `v_authenticated_game`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_authenticated_game` AS select `stats_player`.`stats_game_id` AS `stats_game_id`,count(`stats_player`.`stats_player_id`) AS `authenticated_count` from `stats_player` where (`stats_player`.`is_authenticated` = 1) group by `stats_player`.`stats_game_id`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_current_week_top_player_games` AS select `sp`.`player_name` AS `player_name`,count(`sp`.`stats_game_id`) AS `game_count`,`sp`.`is_authenticated` AS `is_authenticated` from `stats_player` `sp` where ((`sp`.`is_robot` = 0) and (unix_timestamp(`sp`.`insertion_date`) > unix_timestamp((cast((utc_timestamp() + interval 8 hour) as date) - interval ((dayofweek((utc_timestamp() + interval 8 hour)) + 5) % 7) day)))) group by `sp`.`player_name`,`sp`.`is_authenticated` order by count(`sp`.`stats_game_id`) desc;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_current_week_top_player_official_wins` AS select `sp`.`player_name` AS `player_name`,count(`og`.`stats_game_id`) AS `win_count`,`sp`.`is_authenticated` AS `is_authenticated` from (`stats_player` `sp` join `v_official_game` `og`) where ((`sp`.`stats_game_id` = `og`.`stats_game_id`) and (`sp`.`result` = _utf8'W') and (`sp`.`is_robot` = 0) and (unix_timestamp(`sp`.`insertion_date`) > unix_timestamp((cast((utc_timestamp() + interval 8 hour) as date) - interval ((dayofweek((utc_timestamp() + interval 8 hour)) + 5) % 7) day)))) group by `sp`.`player_name`,`sp`.`is_authenticated` order by count(`og`.`stats_game_id`) desc;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_last_week_top_player_games` AS select `sp`.`player_name` AS `player_name`,count(`sp`.`stats_game_id`) AS `game_count`,`sp`.`is_authenticated` AS `is_authenticated` from `stats_player` `sp` where ((`sp`.`is_robot` = 0) and (unix_timestamp(`sp`.`insertion_date`) >= unix_timestamp((cast((utc_timestamp() + interval 8 hour) as date) - interval (((dayofweek((utc_timestamp() + interval 8 hour)) + 5) % 7) + 7) day))) and (unix_timestamp(`sp`.`insertion_date`) < unix_timestamp((cast((utc_timestamp() + interval 8 hour) as date) - interval ((dayofweek((utc_timestamp() + interval 8 hour)) + 5) % 7) day)))) group by `sp`.`player_name`,`sp`.`is_authenticated` order by count(`sp`.`stats_game_id`) desc;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_last_week_top_player_official_wins` AS select `sp`.`player_name` AS `player_name`,count(`og`.`stats_game_id`) AS `win_count`,`sp`.`is_authenticated` AS `is_authenticated` from (`stats_player` `sp` join `v_official_game` `og`) where ((`sp`.`stats_game_id` = `og`.`stats_game_id`) and (`sp`.`result` = _utf8'W') and (`sp`.`is_robot` = 0) and (unix_timestamp(`sp`.`insertion_date`) >= unix_timestamp((cast((utc_timestamp() + interval 8 hour) as date) - interval (((dayofweek((utc_timestamp() + interval 8 hour)) + 5) % 7) + 7) day))) and (unix_timestamp(`sp`.`insertion_date`) < unix_timestamp((cast((utc_timestamp() + interval 8 hour) as date) - interval ((dayofweek((utc_timestamp() + interval 8 hour)) + 5) % 7) day)))) group by `sp`.`player_name`,`sp`.`is_authenticated` order by count(`og`.`stats_game_id`) desc;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_official_game` AS select `sg`.`stats_game_id` AS `stats_game_id` from (`stats_game` `sg` join `v_authenticated_game` `tmp`) where ((`sg`.`stats_game_id` = `tmp`.`stats_game_id`) and (`tmp`.`authenticated_count` > 1) and (`sg`.`player_count` > 3));

