#include <amxmodx>
#include <sqlx>

#define VERSION "1.04"

/***************************************
********* QUERIES
***************************************/

#define QUERY_CREATE_TABLE "CREATE TABLE IF NOT EXISTS `%s` ( \
				`authid` VARCHAR( 24 ) NOT NULL, \
				`wins` INT( 6 ) NOT NULL, \
				`lastname` VARCHAR( 32 ) NOT NULL, \
				`timestamp` INT( 12 ) NOT NULL, \ 
				`points` INT( 8 ) NOT NULL, \
				`serverip` VARCHAR( 21 ) NOT NULL, \
				PRIMARY KEY ( `authid` ) );"

#define QUERY_CLEAR_TABLE "DELETE FROM `%s` WHERE `serverip` = '%s';"
	
#define QUERY_FIX_TABLE "REPAIR TABLE `%s`; OPTIMIZE TABLE `%s`;"

#define QUERY_INSERT_DATA "INSERT INTO `%s` ( `authid`, `wins`, `lastname`, `timestamp`, `points`, `serverip` ) \
						VALUES ( '%s', '%i', '%s', '%i', '%i', '%s' );"
						
#define QUERY_INSERT_UPDATEFLAG "INSERT INTO `%s` ( `authid`, `wins`, `lastname`, `timestamp`, `points`, `serverip` ) \
						VALUES ( 'updating', '0', '', '%i', '0', '%s' );"

#define QUERY_DELETE_UPDATEFLAG "DELETE FROM `%s` WHERE `authid` = 'updating' AND serverip = '%s';"

/***************************************
********* VARIABLES
***************************************/

// globals
new cvHost, cvUser, cvPass, cvDB, cvTable, cvRate, init, table[32], insert_error, serverip[22], queryNum, queryIter;

// mysql
new Handle:tuple, Handle:db, Handle:query, mkQuery[512], error[1024], errcode;

/***************************************
********* PLUGIN LOAD AND UNLOAD
***************************************/

public plugin_init()
{
	register_plugin("GunGameSQL",VERSION,"Avalanche");
	register_cvar("gg_sql_version",VERSION,FCVAR_SERVER);
	set_cvar_string("gg_sql_version",VERSION);
	
	get_user_ip(0,serverip,21,0);

	cvHost	= register_cvar("gg_sql_host","127.0.0.1",FCVAR_PROTECTED);
	cvUser	= register_cvar("gg_sql_user","root",FCVAR_PROTECTED);
	cvPass	= register_cvar("gg_sql_pass","",FCVAR_PROTECTED);
	cvDB		= register_cvar("gg_sql_db","amx",FCVAR_PROTECTED);
	cvTable	= register_cvar("gg_sql_table","gg_stats",FCVAR_PROTECTED);

	cvRate	= register_cvar("gg_sql_updaterate","3");

	set_task(1.0,"sql_init");
	set_task(2.0,"count_map_load");
}

public plugin_end()
{
	sql_uninit();
}

/***************************************
********* MAIN FUNCTIONS
***************************************/

// keep track of map loads and only update database every so often
public count_map_load()
{
	// could not establish connection
	if(!init) return;

	// get how many plugin loads more until we prune
	new buffer[8], sql_update;
	get_localinfo("gg_sql_update",buffer,7);
	sql_update = str_to_num(buffer);

	// localinfo not set yet
	if(sql_update == 0)
	{
		// set to rate minus one
		get_pcvar_string(cvRate,buffer,7);
		num_to_str(str_to_num(buffer)-1,buffer,7);
		set_localinfo("gg_sql_update",buffer);

		return;
	}

	// time to update
	else if(sql_update <= 1)
	{
		// reset our count
		get_pcvar_string(cvRate,buffer,7);
		set_localinfo("gg_sql_update",buffer);

		update_database();

		return;
	}

	// decrement our count
	num_to_str(sql_update-1,buffer,7);
	set_localinfo("gg_sql_update",buffer);
	
	// tune-up
	get_pcvar_string(cvTable,table,31);
	format(mkQuery,511,QUERY_FIX_TABLE,table,table);
	SQL_ThreadQuery(tuple,"sql_query_handler",mkQuery);
}

// transfer stats file to website
public update_database()
{
	new sfFile[64], sfLineData[81], sfAuthid[48], sfWins[6], sfName[64], sfTimestamp[12], sfPoints[8];

	get_cvar_string("gg_stats_file",sfFile,63);
	trim(sfFile);

	// stats disabled/file doesn't exist
	if(!sfFile[0] || !file_exists(sfFile))
	{
		log_amx("Could not update database: stats file ^"%s^" does not exist",sfFile);
		return;
	}

	// clear old entries
	get_pcvar_string(cvTable,table,31);
	format(mkQuery,511,QUERY_CLEAR_TABLE,table,serverip);

	query = SQL_PrepareQuery(db,mkQuery);
	SQL_Execute(query);

	SQL_QueryError(query,error,1023);
	if(error[0])
	{
		log_amx("Could not execute query ^"%s^" -- Error: %s",mkQuery,error);
		SQL_FreeHandle(query);
		return;
	}
	SQL_FreeHandle(query);
 	 
	// insert update flag
	formatex(mkQuery,511,QUERY_INSERT_UPDATEFLAG,table,get_systime(),serverip);

	query = SQL_PrepareQuery(db,mkQuery);
	SQL_Execute(query);

	SQL_QueryError(query,error,1023);
	if(error[0])
	{
		log_amx("Could not execute query ^"%s^" -- Error: %s",mkQuery,error);
		SQL_FreeHandle(query);
		return;
	}
	SQL_FreeHandle(query);

	new lines, file = fopen(sfFile,"rt");
	if(!file) return;
	
	queryIter = 0;
	queryNum = 0;

	while(!feof(file))
	{
		// one of our threaded queries produced an error
		if(insert_error)
		{
			fclose(file);
			return;
		}

		lines++;
		fgets(file,sfLineData,80);

		// isolate authid
		strtok(sfLineData,sfAuthid,23,sfLineData,80,'^t');

		// isolate wins
		strtok(sfLineData,sfWins,5,sfLineData,80,'^t');

		// isolate name
		strtok(sfLineData,sfName,31,sfLineData,80,'^t');

		// isolate timestamp (and points)
		strtok(sfLineData,sfTimestamp,11,sfPoints,7,'^t');

		// clean to prevent SQL injection
		replace_all(sfAuthid,47,"'","\'");
		replace_all(sfName,63,"'","\'");

		// put it in the database
		queryNum++;
		formatex(mkQuery,511,QUERY_INSERT_DATA,table,sfAuthid,str_to_num(sfWins),sfName,str_to_num(sfTimestamp),str_to_num(sfPoints),serverip);
		SQL_ThreadQuery(tuple,"sql_query_handler",mkQuery);
	}

	fclose(file);

	if(!insert_error) log_amx("Updated the database with %i entries",lines);
}

/***************************************
********* SQL FUNCTIONS
***************************************/

// opens the database
public sql_init()
{
	init = 0;

	new host[32], user[32], pass[32], dbname[32];
	get_pcvar_string(cvHost,host,31);
	get_pcvar_string(cvUser,user,31);
	get_pcvar_string(cvPass,pass,31);
	get_pcvar_string(cvDB,dbname,31);

	tuple = SQL_MakeDbTuple(host,user,pass,dbname);
	if(tuple) db = SQL_Connect(tuple,errcode,error,1023);

	if(db == Empty_Handle)
	{
		log_amx("Could not connect to database. Error #%i: %s",errcode,error);
		return 0;
	}

	get_pcvar_string(cvTable,table,31);
	format(mkQuery,511,QUERY_CREATE_TABLE,table);

	query = SQL_PrepareQuery(db,mkQuery);
	SQL_Execute(query);

	SQL_QueryError(query,error,1023);
	if(error[0])
	{
		log_amx("Could not execute query ^"%s^" -- Error: %s",mkQuery,error);
		SQL_FreeHandle(query);
		return 0;
	}
	SQL_FreeHandle(query);

	init = 1;
	return init;
}

// closes the database
public sql_uninit()
{
	if(init)
	{
		if(db) SQL_FreeHandle(db);
		if(tuple) SQL_FreeHandle(tuple);
	}
}

// checks the query for failure
public sql_query_handler(failstate,Handle:query,error[],errnum,data[],size,Float:queuetime)
{
	// report first error that we come across
	if(failstate != TQUERY_SUCCESS && !insert_error)
	{
		insert_error = 1;

		SQL_GetQueryString(query,mkQuery,511);
		log_amx("Could not execute query ^"%s^" -- Error #%i: %s",mkQuery,errnum,error);
	}
	
	// finished queries
	if(queryNum && ++queryIter >= queryNum)
	{
		queryIter = 0;
		queryNum = 0;

		set_task(1.0,"delete_update_flag");
	}
}

// get rid of the update flag in our database
public delete_update_flag()
{
	get_pcvar_string(cvTable,table,31);
	formatex(mkQuery,511,QUERY_DELETE_UPDATEFLAG,table,serverip);

	query = SQL_PrepareQuery(db,mkQuery);
	SQL_Execute(query);

	SQL_QueryError(query,error,1023);
	if(error[0]) log_amx("Could not execute query ^"%s^" -- Error: %s",mkQuery,error);

	SQL_FreeHandle(query);
}