#
# HLstats Database Upgrade file
# -----------------------------
#
# To upgrade an existing HLstats 1.00 database to version 1.01, type:
#
#   mysql hlstats < upgrade_from_1.00.sql
#
#   THEN
#
#   mysql hlstats < gamesupport_xxxx.sql
#


# Fix bad award codes
UPDATE hlstats_Awards SET code='Defused_The_Bomb' WHERE code='bomb_defuse';
UPDATE hlstats_Awards SET code='Planted_The_Bomb' WHERE code='bomb_plant';
UPDATE hlstats_Awards SET code='Rescued_A_Hostage' WHERE code='hostage_rescue';
UPDATE hlstats_Awards SET code='Assassinated_The_VIP' WHERE code='assassinate_vip';

# Change table structures
ALTER TABLE hlstats_Clans MODIFY COLUMN tag varchar(32) DEFAULT '' NOT NULL;
ALTER TABLE hlstats_Clans MODIFY COLUMN name varchar(128) DEFAULT '' NOT NULL;
ALTER TABLE hlstats_Clans MODIFY COLUMN homepage varchar(64) DEFAULT '' NOT NULL;

ALTER TABLE hlstats_Servers MODIFY COLUMN name varchar(64) DEFAULT '' NOT NULL;
ALTER TABLE hlstats_Servers ADD COLUMN publicaddress varchar(64) DEFAULT '' NOT NULL AFTER game;
ALTER TABLE hlstats_Servers ADD COLUMN rcon_password varchar(48) DEFAULT '' NOT NULL AFTER statusurl;

ALTER TABLE hlstats_Awards ADD UNIQUE KEY code (game, awardType, code);

ALTER TABLE hlstats_Players MODIFY COLUMN lastName varchar(64) DEFAULT '' NOT NULL;
ALTER TABLE hlstats_PlayerNames MODIFY COLUMN name varchar(64) DEFAULT '' NOT NULL;
ALTER TABLE hlstats_PlayerUniqueIds MODIFY COLUMN uniqueId varchar(64) DEFAULT '' NOT NULL;

ALTER TABLE hlstats_Events_ChangeName MODIFY COLUMN oldName varchar(64) DEFAULT '' NOT NULL;
ALTER TABLE hlstats_Events_ChangeName MODIFY COLUMN newName varchar(64) DEFAULT '' NOT NULL;



#######################
#
# 1.10 Update
#
#######################


#######################
#
# Removed CS actions used to increase skill points
#
#######################
DELETE FROM hlstats_Actions WHERE game='cstrike' AND code='Begin_Bomb_Defuse_Without_Kit';
DELETE FROM hlstats_Actions WHERE game='cstrike' AND code='Begin_Bomb_Defuse_With_Kit';


#######################
#
# Add missing body_background option
#
#######################
INSERT IGNORE INTO hlstats_Options VALUES ('body_background','');


#######################
#
# Add new default tags
#
#######################
INSERT IGNORE INTO hlstats_ClanTags VALUES (9,'-=|AXXXXXX|=-','EITHER');
INSERT IGNORE INTO hlstats_ClanTags VALUES (10,'-=[AXXXXXX]=-','EITHER');
INSERT IGNORE INTO hlstats_ClanTags VALUES (11,'-=]AXXXXXX[=-','EITHER');
INSERT IGNORE INTO hlstats_ClanTags VALUES (12,'~{|AXXXXXX|}~','EITHER');
INSERT IGNORE INTO hlstats_ClanTags VALUES (13,'-|-AXXXXXX-|-','EITHER');
INSERT IGNORE INTO hlstats_ClanTags VALUES (14,'-(AXXXXXX)-','EITHER');
INSERT IGNORE INTO hlstats_ClanTags VALUES (15,'::AXXXXXX::','EITHER');
INSERT IGNORE INTO hlstats_ClanTags VALUES (16,'<<AXXXXXX>>','EITHER');
INSERT IGNORE INTO hlstats_ClanTags VALUES (17,'{{AXXXXXX}}','EITHER');
INSERT IGNORE INTO hlstats_ClanTags VALUES (18,'((AXXXXXX))','EITHER');
INSERT IGNORE INTO hlstats_ClanTags VALUES (19,'.|AXXXXXX|.','EITHER');
INSERT IGNORE INTO hlstats_ClanTags VALUES (20,'--AXXXXXX--','EITHER');
INSERT IGNORE INTO hlstats_ClanTags VALUES (21,'-)AXXXXXX(-','EITHER');
INSERT IGNORE INTO hlstats_ClanTags VALUES (22,'/AXXXXXX\\','EITHER');
INSERT IGNORE INTO hlstats_ClanTags VALUES (23,'//AXXXXXX\\\\','EITHER');
INSERT IGNORE INTO hlstats_ClanTags VALUES (24,'_AXXXXXX_','EITHER');
INSERT IGNORE INTO hlstats_ClanTags VALUES (25,'_=|AXXXXXX|=_','EITHER');
INSERT IGNORE INTO hlstats_ClanTags VALUES (26,'*AXXXXXX*','EITHER');
INSERT IGNORE INTO hlstats_ClanTags VALUES (27,'.:AXXXXXX:','START');
INSERT IGNORE INTO hlstats_ClanTags VALUES (28,'[(AXXXXXX)]','EITHER');


#######################
#
# Add statsme tables
#
#######################


#
# Table structure for table 'hlstats_Events_Statsme'
#
CREATE TABLE hlstats_Events_Statsme (
  id int(10) unsigned NOT NULL auto_increment,
  eventTime datetime NOT NULL default '0000-00-00 00:00:00',
  serverId int(10) unsigned NOT NULL default '0',
  map varchar(32) NOT NULL default '',
  playerId int(10) unsigned NOT NULL default '0',
  weapon varchar(32) NOT NULL default '',
  shots int(6) unsigned NOT NULL default '0',
  hits int(6) unsigned NOT NULL default '0',
  headshots int(6) unsigned NOT NULL default '0',
  damage int(6) unsigned NOT NULL default '0',
  kills int(6) unsigned NOT NULL default '0',
  deaths int(6) unsigned NOT NULL default '0',
  KEY playerId (playerId),
  PRIMARY KEY  (id)
) TYPE=MyISAM;


#
# Table structure for table 'hlstats_Events_Statsme2'
#
CREATE TABLE hlstats_Events_Statsme2 (
  id int(10) unsigned NOT NULL auto_increment,
  eventTime datetime NOT NULL default '0000-00-00 00:00:00',
  serverId int(10) unsigned NOT NULL default '0',
  map varchar(32) NOT NULL default '',
  playerId int(10) unsigned NOT NULL default '0',
  weapon varchar(32) NOT NULL default '',
  head int(6) unsigned NOT NULL default '0',
  chest int(6) unsigned NOT NULL default '0',
  stomach int(6) unsigned NOT NULL default '0',
  leftarm int(6) unsigned NOT NULL default '0',
  rightarm int(6) unsigned NOT NULL default '0',
  leftleg int(6) unsigned NOT NULL default '0',
  rightleg int(6) unsigned NOT NULL default '0',
  KEY playerId (playerId),
  PRIMARY KEY  (id)
) TYPE=MyISAM;


#
# Table structure for table 'hlstats_Events_StatsmeTime'
#
CREATE TABLE hlstats_Events_StatsmeTime (
  id int(10) unsigned NOT NULL auto_increment,
  eventTime datetime NOT NULL default '0000-00-00 00:00:00',
  serverId int(10) unsigned NOT NULL default '0',
  map varchar(32) NOT NULL default '',
  playerId int(10) unsigned NOT NULL default '0',
  time time NOT NULL default '00:00:00',
  KEY playerId (playerId),
  PRIMARY KEY  (id)
) TYPE=MyISAM;


#
# Table structure for table 'hlstats_Events_StatsmeLatency'
#
CREATE TABLE hlstats_Events_StatsmeLatency (
  id int(10) unsigned NOT NULL auto_increment,
  eventTime datetime NOT NULL default '0000-00-00 00:00:00',
  serverId int(10) unsigned NOT NULL default '0',
  map varchar(32) NOT NULL default '',
  playerId int(10) unsigned NOT NULL default '0',
  ping int(6) unsigned NOT NULL default '0',
  KEY playerId (playerId),
  PRIMARY KEY  (id)
) TYPE=MyISAM;

#######################
#
# 1.20 Update
#
#######################

#
# Remove old Teams table
#
DROP TABLE hlstats_Teams;


#
# Add new Teams table
#
CREATE TABLE hlstats_Teams (
  teamId int(10) unsigned NOT NULL auto_increment,
  game varchar(16) DEFAULT 'valve' NOT NULL,
  code varchar(32) DEFAULT '' NOT NULL,
  name varchar(64) DEFAULT '' NOT NULL,
  hidden enum('0', '1') DEFAULT '0' NOT NULL,
  PRIMARY KEY (teamId),
  UNIQUE gamecode (game,code)
);


#
# Add Roles Table
#
CREATE TABLE hlstats_Roles (
  roleId int(10) unsigned NOT NULL auto_increment,
  game varchar(16) DEFAULT 'valve' NOT NULL,
  code varchar(32) DEFAULT '' NOT NULL,
  name varchar(64) DEFAULT '' NOT NULL,
  hidden enum('0', '1') DEFAULT '0' NOT NULL,
  PRIMARY KEY (roleId),
  UNIQUE gamecode (game,code)
);


#
# Remove Teams in old format 
#
TRUNCATE hlstats_Teams;


#
# Add Weapon Body Color
#
INSERT INTO hlstats_Options VALUES ('table_wpnbgcolor','#000000');


#
# Empty Action, Awards, Games, and Weapons Tables
#
TRUNCATE hlstats_Actions;
TRUNCATE hlstats_Awards;
TRUNCATE hlstats_Games;
TRUNCATE hlstats_Weapons;