#include <amxmodx>
#include <amxmisc>
#include <fakemeta>
#include <cstrike>
#include <hamsandwich>
#include <sqlx>


stock SQL_PrepareString(const szQuery[], szOutPut[], size){
	copy(szOutPut, size, szQuery);
	replace_all(szOutPut, size, "'", "\'");
	replace_all(szOutPut,size, "`", "\`");	
}

MySQL_Init(){
	new szHost[32], szUser[32], szPass[32], szDB[32];
	get_cvar_string("amx_drstats_host", szHost, 31);
	get_cvar_string("amx_drstats_user", szUser, 31);
	get_cvar_string("amx_drstats_pass", szPass, 31);
	get_cvar_string("amx_drstats_db", szDB, 31);
	
	gTuple = SQL_MakeDbTuple(szHost, szUser, szPass, szDB);
	
	if(gTuple == Empty_Handle){
		set_fail_state("Cant create connection tuple");
	}
	
	new iErr, szError[32];
	new Handle:link = SQL_Connect(gTuple, iErr, szError, 31);
	if(link == Empty_Handle){
		log_amx("Error (%d): %s", iErr, szError);
		set_fail_state("Cant connect to database");
	}
	
	new Handle:query;
	query = SQL_PrepareQuery(link, 
		"CREATE TABLE IF NOT EXISTS `runners`( \
			id 		INT(11) 	UNSIGNED 	AUTO_INCREMENT,\
			steamid		VARCHAR(32) 	NOT NULL, \
			nickname	VARCHAR(32) 	NOT NULL, \
			ip		VARCHAR(32) 	NOT NULL, \
			PRIMARY KEY(id) \
		)");
	SQL_Execute(query);
	SQL_FreeHandle(query);
	
	query = SQL_PrepareQuery(link, 
		"ALTER TABLE `runners` \
		ADD 	nationality	VARCHAR(3) 	NULL");
		
	SQL_Execute(query);
	SQL_FreeHandle(query);
	
	query = SQL_PrepareQuery(link, 
		"CREATE TABLE IF NOT EXISTS `maps`( \
			mid 		INT(11) 	UNSIGNED 	AUTO_INCREMENT,\
			mapname		VARCHAR(64) 	NOT NULL	UNIQUE, \
			games		INT(11) 	NOT NULL, 	\
			finishX		INT(11) 	NOT NULL	DEFAULT 0, \
			finishY		INT(11) 	NOT NULL 	DEFAULT 0, \
			finishZ		INT(11) 	NOT NULL 	DEFAULT 0, \
			PRIMARY KEY(mid) \
		)");
	SQL_Execute(query);
	SQL_FreeHandle(query);
	
	query = SQL_PrepareQuery(link, 
		"CREATE TABLE IF NOT EXISTS `results`( \
			id		INT(11)		UNSIGNED, \
			mid 		INT(11) 	UNSIGNED, \
			besttime	INT(11) 	NOT NULL, \
			games		INT(11) 	NOT NULL, \
			playedtime	INT(11)		NOT NULL, 	\
			deaths		INT(11)		NOT NULL, 	\
			FOREIGN KEY(id) REFERENCES `runners`(id) ON DELETE CASCADE, \
			FOREIGN KEY(mid) REFERENCES `maps`(mid) ON DELETE CASCADE, \
			PRIMARY KEY(id, mid) \
		)");
	SQL_Execute(query);
	SQL_FreeHandle(query);
	
	query = SQL_PrepareQuery(link, 
		"ALTER TABLE `results` \
		ADD 	recorddate	DATETIME	NULL");
		
	SQL_Execute(query);
	SQL_FreeHandle(query);
	
	
	SQL_FreeHandle(link);
	
	get_mapname(gszMapname, charsmax(gszMapname));
	formatex(gszQuery, charsmax(gszQuery), "INSERT INTO `maps`(mid, mapname, games) VALUES (0, '%s', 1) ON DUPLICATE KEY UPDATE games = games + 1", gszMapname);
	SQL_ThreadQuery(gTuple, "handleUpdateMap", gszQuery);
}
public handleUpdateMap(failstate, Handle:query, error[], errnum, data[], size){
	if(failstate != TQUERY_SUCCESS){
		log_amx("SQL Insert error: %s",error);
		return;
	}
	formatex(gszQuery, charsmax(gszQuery), "SELECT mid, games, finishX, finishY, finishZ FROM `maps` WHERE mapname='%s'", gszMapname);
	SQL_ThreadQuery(gTuple, "handleSelectMap", gszQuery);
}
public handleSelectMap(failstate, Handle:query, error[], errnum, data[], size){
	if(failstate != TQUERY_SUCCESS){
		log_amx("SQL Insert error: %s",error);
		return;
	}
	gMid = SQL_ReadResult(query, 0);
	giGames = SQL_ReadResult(query, 1);
	
	createFinishI( 0, SQL_ReadResult(query, 2),  SQL_ReadResult(query, 3),  SQL_ReadResult(query, 4));

	for(new i=1;i<33; i++)
		if(is_user_connected(i))
			client_putinserver(i);
			
	loadNshowTop15(0);
	
	
	if(gszTop15Redirect[0]){
		new szTemp[15];
		formatex(szTemp, 14, "mid=%d", gMid);
		add(gszTop15Redirect, charsmax(gszTop15Redirect), szTemp);
	}
}

loadPlayerId(id){
	if(!gMid) return;
	
	new szAuth[64];
	new szField[32];
	
	switch(get_pcvar_num(gcvarSave)){
		case 1:{
			get_user_authid(id, szAuth, 31);
			szField = "steamid";
		}
		case 2:{
			get_user_name(id, szAuth, 31);
			SQL_PrepareString(szAuth, szAuth, 63);
			szField = "nickname";
		}
		case 3:{
			get_user_ip(id, szAuth, 31, 1);
			szField = "ip";
		}
	}
	new data[1];
	data[0] = id;
	formatex(gszQuery, charsmax(gszQuery), "SELECT id, ip, nationality FROM `runners` WHERE %s='%s'",szField, szAuth);
	SQL_ThreadQuery(gTuple, "handleSelectRunner", gszQuery, data, sizeof(data));
}

public handleStandard(failstate, Handle:query, error[], errnum, data[], size){
	if(failstate != TQUERY_SUCCESS){
		log_amx("SQL Insert error: %s",error);
		return;
	}
}
getNationality(id, const szIP[], szCode[5]){
	new szTemp[3];
	if(geoip_code2_ex( szIP, szTemp)){
		copy(szCode, 4, szTemp);
	}
	else{
		get_user_info(id, "lang", szCode, 2);
		SQL_PrepareString(szCode, szCode, 4);
	}
}
public handleSelectRunner(failstate, Handle:query, error[], errnum, data[], size){
	if(failstate != TQUERY_SUCCESS){
		log_amx("SQL Insert error: %s",error);
		return;
	}
	new id = data[0];
	if(!is_user_connected(id)) return;
	
	new szCode[5];
	
	if(SQL_MoreResults(query)){
		client_authorized_db(id, SQL_ReadResult(query, 0));
		
		SQL_ReadResult(query, 2, szCode, 1)
		if(szCode[0] == 0){
			new szIP[32];
			SQL_ReadResult(query, 1, szIP, 31);
			
			
			getNationality(id, szIP, szCode);
			formatex(gszQuery, charsmax(gszQuery), "UPDATE `runners` SET nationality='%s' WHERE id=%d",szCode, giPid[id]);
			SQL_ThreadQuery(gTuple, "handleStandard", gszQuery);
		}
	}else{
		new szAuth[32];
		get_user_authid(id, szAuth, 31);
		new szIP[32];
		get_user_ip(id, szIP, 31, 1)
		new szName[64];
		get_user_name(id, szName, 31);
		SQL_PrepareString(szName, szName, 63);
		
		getNationality(id, szIP, szCode);
		
		formatex(gszQuery, charsmax(gszQuery), "INSERT INTO `runners` VALUES (0, '%s', '%s', '%s', '%s')", szAuth, szName, szIP, szCode);
		SQL_ThreadQuery(gTuple, "handleInsertRunner", gszQuery, data, size);
	}
}
public handleInsertRunner(failstate, Handle:query, error[], errnum, data[], size){
	if(failstate != TQUERY_SUCCESS){
		log_amx("SQL Insert error: %s",error);
		return;
	}
	
	new id = data[0];
	if(!is_user_connected(id)) return;
	
	client_authorized_db(id , SQL_GetInsertId(query));
}

public handleLoadData(failstate, Handle:query, error[], errnum, data[], size){
	if(failstate != TQUERY_SUCCESS){
		log_amx("SQL Insert error: %s",error);
		return;
	}
	
	new id = data[0];
	if(!is_user_connected(id)) return;
	
	if(SQL_MoreResults(query)){
		giBestTime[id] 		= SQL_ReadResult(query, 2);
		giPlayedGames[id] 	= SQL_ReadResult(query, 3) + 1;
		giPlayedTime[id] 	= SQL_ReadResult(query, 4);
		giDeaths[id] 		= SQL_ReadResult(query, 5);
		SQL_ReadResult(query, 6, gszRecordTime[id], charsmax(gszRecordTime[]));
	}else{
		giBestTime[id] = 0;
		giPlayedGames[id] = 1;
		giPlayedTime[id] = 0;
		giDeaths[id] = 0;
		gszRecordTime[id] = "";
		
		formatex(gszQuery, charsmax(gszQuery), "INSERT INTO `results` VALUES (%d, %d, 0, 1, 0, 0, NULL)", giPid[id], gMid);
		SQL_ThreadQuery(gTuple, "handleStandard", gszQuery);
	}
}

saveFinishOrigin(){
	if(pev_valid(gEntFinish)){
		new Float:fOrigin[3], iOrigin[3];
		pev(gEntFinish, pev_origin, fOrigin);
		FVecIVec(fOrigin, iOrigin);
		
		formatex(gszQuery, charsmax(gszQuery), "UPDATE `maps` SET finishX = %d, finishY=%d, finishZ=%d WHERE mid=%d", 
			iOrigin[0], iOrigin[1], iOrigin[2], gMid);
			
		SQL_ThreadQuery(gTuple, "handleStandard", gszQuery);
	}
}

loadRank(id, const szFunc[]){
	new fun = get_func_id(szFunc);
	if(!fun) return;
	
	formatex(gszQuery, charsmax(gszQuery), "SELECT COUNT(*) FROM `results` WHERE mid=%d && besttime > 0 && besttime < %d", 
			gMid, giBestTime[id]);
		
	new data[2];
	data[0] = id;
	data[1] = fun;
	SQL_ThreadQuery(gTuple, "handleLoadRank", gszQuery, data, sizeof(data));
}

public handleLoadRank(failstate, Handle:query, error[], errnum, data[], size){
	if(failstate != TQUERY_SUCCESS){
		log_amx("SQL Insert error: %s",error);
		return;
	}
	
	new id = data[0];
	new fun = data[1];
	
	new rank = SQL_ReadResult(query, 0);
	
	if(callfunc_begin_i(fun)){
		callfunc_push_int(id);
		callfunc_push_int(rank+1);
		callfunc_end();
	}
}


loadNshowTop15(id){
	formatex(gszQuery, charsmax(gszQuery), "SELECT nickname, besttime FROM `results` JOIN `runners` ON `runners`.id=`results`.id WHERE mid=%d && besttime ORDER BY besttime ASC LIMIT 15", 
			gMid, giBestTime[id]);
		
	new data[1];
	data[0] = id;
	SQL_ThreadQuery(gTuple, "handleLoadTop15", gszQuery, data, sizeof(data));
}

public handleLoadTop15(failstate, Handle:query, error[], errnum, data[], size){
	if(failstate != TQUERY_SUCCESS){
		log_amx("SQL Insert error: %s",error);
	}

	new id = data[0];
	if(!is_user_connected(id) && id!=0) return;
	
	new iLen = 0, iMax = charsmax(gszMotd);
	iLen += formatex(gszMotd[iLen], iMax-iLen, "<pre>");
	iLen += formatex(gszMotd[iLen], iMax-iLen, "%L     %L     %L^n", id, "WORD_POS", id, "WORD_PLAYER", id, "WORD_TIME");
	
	new i=1;
	new iTime, szName[32], szTime[32];
	while(SQL_MoreResults(query)){
		SQL_ReadResult(query, 0, szName, 31);
		iTime = SQL_ReadResult(query, 1);
		getFormatedTime(iTime, szTime, 31);
		
		iLen += formatex(gszMotd[iLen], iMax-iLen, "%-3d %-32s   ", i, szName);
		if(i == 1){
			giBestTimeofMap = iTime;
			iLen += formatex(gszMotd[iLen], iMax-iLen, " %-15s ",  szTime);
			if(id == 0) return;
		}else{
			iLen += formatex(gszMotd[iLen], iMax-iLen, " %-15s  ", szTime);
			
			getFormatedTime(iTime-giBestTimeofMap , szTime, 31);
			iLen += formatex(gszMotd[iLen], iMax-iLen, "+%-15s", szTime);
		}
		iLen += formatex(gszMotd[iLen], iMax-iLen, "^n");
		
		i++;
		SQL_NextRow(query);
	}
	iLen += formatex(gszMotd[iLen], iMax-iLen, "</pre>");
	show_motd(id, gszMotd, "Top15");
}
	
/* AMXX-Studio Notes - DO NOT MODIFY BELOW HERE
*{\\ rtf1\\ ansi\\ deff0{\\ fonttbl{\\ f0\\ fnil Tahoma;}}\n\\ viewkind4\\ uc1\\ pard\\ lang1045\\ f0\\ fs16 \n\\ par }
*/
