////////////////////////////////////////
// WCS - Warcraft:Source              //
// ---------------------------------- //
//  by kRypT0n!Te                     //
//      http://wcs.despo-clan.de      //
// BIG THANKS to Bl1nk and Vito for   //
// helping me with this !! :)         //
// ---------------------------------- //
// Content of this subscript          //
// - mySQL interface                  //
////////////////////////////////////////
// commands:
// wcs_sql_setplayer <userid>
// wcs_sql_getplayer <userid>
//
////////////////////////////////////////
block load
{
  // DB settings
  es_xsetinfo wcs_sql_user "USERNAME"
  es_xsetinfo wcs_sql_password "PASSWORD (NOT EMPTY !)"
  es_xsetinfo wcs_sql_host "IP ADDRESS"
  es_xsetinfo wcs_sql_port "3306" // 3306 is default
  es_xsetinfo wcs_sql_dbname "wcs_users" // can be modified !
  es_xsetinfo wcs_sql_tablename "player_levels" // can be modified !

  es es_xexists wcs_exists command wcs_sql_setplayer
  es es_xif (server_var(wcs_exists) = "0") then es_xregcmd wcs_sql_setplayer wcs/WCSsql/wcs_sql_setplayer "saves a player"
  es es_xexists wcs_exists command wcs_sql_getplayer
  es es_xif (server_var(wcs_exists) = "0") then es_xregcmd wcs_sql_getplayer wcs/WCSsql/wcs_sql_getplayer "get a player"
  es_xsetinfo wcs_sql_query 0
  es_xsetinfo wcs_sql_query2 0
  es_xsetinfo wcs_sql_query_result 0
  es_xsetinfo wcs_sql_tmp1 0
  es_xsetinfo wcs_sql_tmp2 0
  alias wcs_runquery "es_xsetinfo wcs_sql_query_result 0;es su_mysql server_var(wcs_sql_host) server_var(wcs_sql_port) server_var(wcs_sql_user) server_var(wcs_sql_password) server_var(wcs_sql_dbname) server_var(wcs_sql_query) wcs_sql_query_result;es est_strlen wcs_strlen server_var(wcs_sql_query);es es_xif (server_var(wcs_debug) = 1) then echo qry: server_var(wcs_sql_query) len: server_var(wcs_strlen) result: ' server_var(wcs_sql_query_result) '"
  // init the DB, table 1
  es es_xformat wcs_sql_query "CREATE TABLE %1 (steamid VARCHAR(30) NOT NULL, lastplayed INT DEFAULT 0, race TINYINT DEFAULT 0, PRIMARY KEY (steamid))" server_var(wcs_sql_tablename)
  wcs_runquery
  es_xsetinfo wcs_counter 1
	while "server_var(wcs_counter) <= server_var(wcs_numberofraces)" "es_xdoblock wcs/WCSsql/wcs_sql_createloop;es_xmath wcs_counter + 1"
	// get the name as last column
	es es_xformat wcs_sql_query "ALTER TABLE %1 ADD name_1 VARCHAR(50) DEFAULT 0" server_var(wcs_sql_tablename)
  wcs_runquery
  es es_xformat wcs_sql_query "UPDATE %1 set name_1 = name" server_var(wcs_sql_tablename)
  wcs_runquery
  es es_xformat wcs_sql_query "ALTER TABLE %1 DROP COLUMN name" server_var(wcs_sql_tablename)
  wcs_runquery
  es es_xformat wcs_sql_query "ALTER TABLE %1 CHANGE name_1 name VARCHAR(50) DEFAULT 0" server_var(wcs_sql_tablename)
  wcs_runquery
}

event server
{
  es_xsetinfo wcs_ver_latest 0
  es es_xexists wcs_exists command su_getversion
  es es_xif (server_var(wcs_exists) = "1") do
  {
    es su_GetVersion "wcs.despo-clan.de" "80" "wcs_ver_latest"
    es es_xif (server_var(wcs_ver_latest) != "0") do
    {
      wcs_getlanguage "wcs_lng" "wcs_lng_latestversion"
      es es_xformat wcs_lng server_var(wcs_lng) server_var(wcs_ver) server_var(wcs_ver_latest)
      es es_xdelayed 10 es_xmsg #multi server_var(wcs_lng)
    }
  }
}
block wcs_sql_createloop
{
	es es_xformat wcs_sql_query "ALTER TABLE %1 ADD race_%2 VARCHAR(50) DEFAULT 0" server_var(wcs_sql_tablename) server_var(wcs_counter)
	wcs_runquery
}

block wcs_sql_setplayer
{
// wcs_sql_setplayer <userid>
es es_xif (server_var(wcs_debug) = 1) then profile begin sql_setplayer
  es_xgetargv wcs_userid 1
  // get stuff
  es es_xkeygetvalue wcs_id "WCSuserdata" server_var(wcs_userid) "id"
  es es_xif ("BOT" in server_var(wcs_id)) then es_xsetinfo wcs_id "0"
  es es_xif (server_var(wcs_id) != "0") do
  {
	  es_xsetinfo wcs_race "1"
	  es_xsetinfo wcs_sqladd "0"
	  es_xgettime wcs_curtime
	  es_xsetinfo wcs_currace "0"
	  es_xsetinfo wcs_sql_levels "0"
	  es_xsetinfo wcs_txt_tmp1 "0"
	  es es_xkeygetvalue wcs_currace "WCSuserdata" server_var(wcs_userid) "race"
	  es es_xkeygetvalue wcs_name "WCSuserdata" server_var(wcs_userid) "name"
	  // delete the old entry
	  //es es_xformat wcs_sql_query "DELETE FROM %1 WHERE steamid='%2'" server_var(wcs_sql_tablename) server_var(wcs_id)
    //wcs_runquery
    // insert new stuff
    es_xsetinfo wcs_counter 1
    es es_xformat wcs_sql_query2 "ON DUPLICATE KEY UPDATE lastplayed='%1', race='%2', " server_var(wcs_curtime) server_var(wcs_currace)
    es es_xformat wcs_sql_query "INSERT INTO %1 VALUES ('%2','%3','%4'," server_var(wcs_sql_tablename) server_var(wcs_id) server_var(wcs_curtime) server_var(wcs_currace)
    while "server_var(wcs_counter) <= server_var(wcs_numberofraces)" "es_xdoblock wcs/WCSsql/wcs_sql_setloop;es_xmath wcs_counter + 1"
    es es_xif (server_var(wcs_sqladd) = "1") do
    {
    	es es_xformat wcs_sql_query "%1 %2" server_var(wcs_sql_query) server_var(wcs_sql_query2)
    	wcs_runquery
    }
es es_xif (server_var(wcs_debug) = 1) then profile end sql_setplayer
	}
}

block wcs_sql_setloop
{
  es es_xformat wcs_racef "race_%1" server_var(wcs_counter)
  es es_xkeygetvalue wcs_txt_tmp1 "WCSusers" server_var(wcs_id) server_var(wcs_racef)
  es es_xif (server_var(wcs_txt_tmp1) != "0") do
  {
    es es_xtoken wcs_curlevel server_var(wcs_txt_tmp1) "1" "|"
    es es_xif (server_var(wcs_curlevel) = "0") then es_xsetinfo wcs_txt_tmp1 "0"
    es es_xif (server_var(wcs_curlevel) > "0") then es_xsetinfo wcs_sqladd "1"
  }
  es es_xif (server_var(wcs_counter) != server_var(wcs_numberofraces)) do
  {
  	es es_xformat wcs_sql_query "%1 '%2'," server_var(wcs_sql_query) server_var(wcs_txt_tmp1)
  	es es_xformat wcs_sql_query2 "%1 %2='%3'," server_var(wcs_sql_query2) server_var(wcs_racef) server_var(wcs_txt_tmp1)
  }
  es es_xif (server_var(wcs_counter) = server_var(wcs_numberofraces)) do
  {
  	es es_xformat wcs_sql_query "%1 '%2', '%3')" server_var(wcs_sql_query) server_var(wcs_txt_tmp1) server_var(wcs_name)
  	es es_xformat wcs_sql_query2 "%1 %2='%3', name='%4';" server_var(wcs_sql_query2) server_var(wcs_racef) server_var(wcs_txt_tmp1) server_var(wcs_name)
  }
}

block wcs_sql_getplayer
{
// wcs_sql_getplayer server_var(wcs_userid)
es es_xif (server_var(wcs_debug) = 1) then profile begin sql_getplayer
  es_xgetargv wcs_userid 1
  es es_xkeygetvalue wcs_id WCSuserdata server_var(wcs_userid) "id"
  es es_xif ("BOT" in server_var(wcs_id)) then es_xsetinfo wcs_id "0"
  es es_xif (server_var(wcs_id) != "0") do
  {
    es es_xformat wcs_sql_query "SELECT * FROM %1 WHERE steamid='%2'" server_var(wcs_sql_tablename) server_var(wcs_id)
    wcs_runquery
		es es_xif (server_var(wcs_sql_query_result) != "0") do
		{
			es_xsetinfo wcs_string_end "3"
			es es_xmath wcs_string_end + server_var(wcs_numberofraces)
			es es_xkeycreate "WCSusers" server_var(wcs_id)
			// token list: <steamid> <lastplayed> <race> <skills.....>
			es es_xtoken wcs_race server_var(wcs_sql_query_result) 3 " "
			es es_xif (server_var(wcs_race) = "0") then es_xsetinfo wcs_race "1"
			es es_xkeysetvalue "WCSusers" server_var(wcs_id) "race" server_var(wcs_race)
			es es_xkeygetvalue wcs_lastplayed "WCSusers" server_var(wcs_id) "lastplayed"
			// start with token 4 -> race_1
			es_xsetinfo wcs_counter 4
			es_xsetinfo wcs_race 1
			while "server_var(wcs_counter) <= server_var(wcs_string_end)" "es_xdoblock wcs/WCSsql/wcs_sql_getloop;es_xmath wcs_counter + 1;es_xmath wcs_race + 1"
			es es_xkeysetvalue WCSuserdata server_var(wcs_userid) "sql_loaded" "1"
    }
  }
es es_xif (server_var(wcs_debug) = 1) then profile end sql_getplayer
}

// get level from .txt file and SQL DB, and compare if level of SQL > .txt 
block wcs_sql_getloop
{
	es_xsetinfo wcs_sql_level "0"
	es_xsetinfo wcs_txt_level "0"
	es es_xformat wcs_racef "race_%1" server_var(wcs_race)
	es es_xkeygetvalue wcs_txt_level "WCSusers" server_var(wcs_id) server_var(wcs_racef)
  es es_xif (server_var(wcs_txt_level) != "0") then es_xtoken wcs_txt_level server_var(wcs_txt_level) "1" "|"
  es es_xtoken wcs_sql_tmp1 server_var(wcs_sql_query_result) server_var(wcs_counter) " "
	es es_xif (server_var(wcs_sql_tmp1) != "0") then es_xtoken wcs_sql_level server_var(wcs_sql_tmp1) "1" "|"
  es es_xif (server_var(wcs_sql_level) > server_var(wcs_txt_level)) then es_xkeysetvalue "WCSusers" server_var(wcs_id) server_var(wcs_racef) server_var(wcs_sql_tmp1)
}
