<?php
session_start();
include("html_functions.php");

if ($_SESSION['user_is_commish']!=1)
{
	header('Location: '.$_SERVER['HTTP_REFERER']);
}

$adminpath=$_SESSION['adminpath'];

$debug=$_GET['debug'];
if ($debug=="") {$debug=$_POST['debug'];}
$timeout=$_GET['timeout'];
if (($timeout<30) || ($timeout=="")) {$timeout=120;}

$maxSize=$_POST['filesize'];

include($adminpath."/dbopen.php");

##### Get Old League Date #####
$query="SHOW TABLES LIKE 'leagues';";
$result=mysqli_query($db,$query);
if (mysqli_num_rows($result))
{
	$query="SELECT leagues.current_date FROM leagues WHERE league_id=".$_SESSION['lgid']." GROUP BY leagues.current_date;";
	$result=mysqli_query($db,$query);
	if (mysqli_num_rows($result))
	{
		$row=mysqli_fetch_array($result);
		$oldLgDate=$row[0];

		if ($debug==1) {echo "Old league date: $oldLgDate<br/>\n";}
	}
}

##### Load SQL Files #####
$loadCnt=0;
if ($dir = opendir($adminpath))
{
	$now=time();    
     	if ($debug==1) {echo "Reading $adminpath<br/>\n";}
	while (false !== ($file = readdir($dir)))
    	{
		$ex = explode(".",$file);
		$last = count($ex)-1;
	  	$fileTime=filemtime($adminpath."/".$file);
		$fileSize=filesize($adminpath."/".$file);

		if ($debug==1) {echo "$file :: $maxSize :: $fileSize<br/>\n";}
	  	if (($maxSize!="")&&($maxSize>0)&&($fileSize>$maxSize)) {continue;}

		if (($ex[$last]=="sql") && ($file!=".") && ($file!=".."))
		{
			$fileList[$loadCnt]=$file;
			$loadCnt++;
		}
    	}
	if ($debug==1) {echo "loadCnt: $loadCnt<br/>\n";}

	if ($loadCnt>0)
	{
		asort($fileList);
		foreach ($fileList as $key => $loadFile)
		{
			$ex = explode(".",$loadFile);
			set_time_limit($timeout);
			$fnow=time();

			$tableName=$ex[0];
			$query="INSERT INTO ootp_sql_admin (module, setting, value) VALUES ('admin', 'Load Status', 'Loading $tableName') ON DUPLICATE KEY UPDATE value='Loading $tableName';";
			mysqli_query($db,$query);

			$query="CREATE TABLE IF NOT EXISTS `$tableName';";
			mysqli_query($db,$query);

			## Import data
			$file=$adminpath."/".$loadFile;
			$fr = fopen($file,"r");
			$errCnt=0;
			if (isset($errors))
			{ 
				unset($errors);
				unset($queries);
			}
			while (!feof($fr))
			{
				$query=fgets($fr);
				$query=trim($query);
				if (($query=="")||($query[0]=="#")) {continue;}
				$query=str_replace(", , );",",1,1);",$query);
				//$query=preg_replace("/([\xC2\xC3])([\x80-\xBF])/e","chr(ord('\\1')<<6&0xC0|ord('\\2')&0x3F)",$query);
				$query=str_replace(", ,",",'',",$query);
				$query=str_replace("#IND",0,$query);
				$query=str_replace("#INF",0,$query);
				if (($tableName=='players_career_batting_stats')||($tableName=='players_career_pitching_stats'))
				{
					$query=str_replace("insert into","insert ignore into",$query);
				}
				$result=mysqli_query($db,$query);
				$err=mysqli_error($db);
				if (($err!="") && ($query!=""))
				{
					$errors[$errCnt]=$err;
					$queries[$errCnt]=$query;
					$errCnt++;
				}
				if ((substr_count($query,"CREATE ")>0)&&(($tableName=='players_career_batting_stats')||($tableName=='players_career_pitching_stats')))
				{
					$query="ALTER TABLE $tableName ADD PRIMARY KEY (player_id,year,team_id,league_id,split_id);";
					$result=mysqli_query($db,$query);
				}
			}
			fclose($fr);
		       
			## Report Errors
			$fend=time();
			if ($errCnt==0)
			{
				$ltime=$fend-$fnow;
		    		$query="INSERT INTO ootp_sql_admin (module, setting, identifier, value) VALUES (";
				$query.="'File Load','Load Time','$loadFile'";
		    		$query.=",'$fend|$ltime'";
				$query.=") ON DUPLICATE KEY UPDATE value='$fend|$ltime';";
				mysqli_query($db,$query);
			}
			else
			{
				for ($i=0;$i<$errCnt;$i++)
				{
					echo "ERROR in $tableName:<br/>  ".$queries[$i]." -> ".$errors[$i]."<br/>\n";
				}
			}
		}
		$end=time();
	}
}
else
{
     	$_SESSION['loaderr']="ERROR: Unable to read directory ".$adminpath;
}

if ($loadCnt==0)
{
	$tmp="LOAD STATUS: No files loaded!<br/>";
    	$tmp.="You may have one of the following problems:<br/>";
	$tmp.=" 1. The path to the SQL files is incorrect: $adminpath<br/>";
    	$tmp.=" 2. The SQL files are not uploaded to the server.<br/>";
	$_SESSION['loaderr']=$tmp;
}
else {$_SESSION['loaderr']="LOAD STATUS: $loadCnt files loaded in ".($end-$now)." seconds";}

##### Update Human Managers #####
$query="INSERT INTO ootp_sql_admin (module, setting, value) VALUES ('admin', 'Load Status', 'Processing Users') ON DUPLICATE KEY UPDATE value='Processing Users';";
mysqli_query($db,$query);
## Check if user table exists
$query="SHOW TABLES LIKE 'ootp_sql_users';";
$result=mysqli_query($db,$query);
if (!mysqli_num_rows($result))
{
	##### Initialize Table #####
	$query="CREATE TABLE IF NOT EXISTS ootp_sql_users (human_manager_id INT, ip_address VARCHAR(100), last_date DATE, token VARCHAR(256) DEFAULT NULL, PRIMARY KEY (human_manager_id, ip_address));";
	mysqli_query($db,$query);
}
 
## Get OOTP Managers
$query="SELECT human_manager_id,first_name,last_name FROM human_managers WHERE human_manager_id NOT IN (SELECT DISTINCT human_manager_id FROM ootp_sql_users);";
$result=mysqli_query($db,$query);
$updText="";
if (mysqli_num_rows($result))
{
	while ($row=mysqli_fetch_array($result))
    	{
		$hmid=$row['human_manager_id'];

		$escapedPW=addslashes("baseball");
	  	$salt=bin2hex(random_bytes(32));
		$saltedPW=$escapedPW . $salt;

		$hashedPW = hash('sha256', $saltedPW);

	  	$updText.="INSERT INTO ootp_sql_admin (scope,module,setting,identifier,value) VALUES ('ALL','admin','salt','".$hmid."','".$salt."') ON DUPLICATE KEY UPDATE value='$salt';\n";
	  	$updText.="INSERT INTO ootp_sql_admin (scope,module,setting,identifier,value) VALUES ('ALL','admin','password','".$hmid."','".$hashedPW."') ON DUPLICATE KEY UPDATE value='$hashedPW';\n";
    	}
}

## Load Managers
if ($updText!="")
{
     	$updText=trim($updText,",");
	$updText=trim($updText);
     	$e=explode("\n",$updText);
	foreach ($e as $key => $query)
    	{
		mysqli_query($db,$query);
	  	$err=mysqli_error($db);
		if ($err!="") {echo $err."<br />$query<br/><br/>\n";}
    	}
}

##### Get New League Date #####
$query="INSERT INTO ootp_sql_admin (module, setting, value) VALUES ('admin', 'Load Status', 'Checking Date') ON DUPLICATE KEY UPDATE value='Checking Date';";
if ($debug==1) {echo "$query<br/>\n";}
mysqli_query($db,$query);
$query="SELECT leagues.current_date,'$oldLgDate' as last_date,DATEDIFF(leagues.current_date,'$oldLgDate') as days FROM leagues WHERE league_id=".$_SESSION['lgid'].";";
if ($debug==1) {echo "$query<br/>\n";}
$result=mysqli_query($db,$query);
$row=mysqli_fetch_array($result);
$newLgDate=$row[0];
$simLength=$row['days'];

if ($simLength>0)    ## Update of existing tables ##
{
     	$_SESSION['lgdate']=$newLgDate;
     
	## Save off Settings
	$query="INSERT INTO ootp_sql_admin (module, setting, value) VALUES ('admin','LastDate','$oldLgDate') ON DUPLICATE KEY UPDATE value='$oldLgDate';";
	if ($debug==1) {echo "$query<br/>\n";}
     	mysqli_query($db,$query);
	$query="INSERT INTO ootp_sql_admin (module, setting, value) VALUES ('admin','calcLength','$simLength') ON DUPLICATE KEY UPDATE value='$simLength';";
	if ($debug==1) {echo "$query<br/>\n";}
     	mysqli_query($db,$query);

	$query="INSERT INTO ootp_sql_admin (module, setting, value) VALUES ('admin', 'Load Status', 'Done') ON DUPLICATE KEY UPDATE value='Done';";
	if ($debug==1) {echo "$query<br/>\n";}
	mysqli_query($db,$query);
}
else
{
	$query="INSERT INTO ootp_sql_admin (module, setting, value) VALUES ('admin', 'Load Status', 'Done') ON DUPLICATE KEY UPDATE value='Done';";
	if ($debug==1) {echo "$query<br/>\n";}
	mysqli_query($db,$query);
}

##### Clear old user tokens #####
$query="DELETE FROM ootp_sql_users WHERE DATEDIFF(last_date,$newLgDate)<-30";
mysqli_query($db,$query);

##### Clear Viewed Results #####
$query="DELETE FROM ootp_sql_admin WHERE setting='showScores'";
mysqli_query($db,$query);

#Save off Load date
$scope='ALL';
$module='admin';
$setting='loadrun';

$query="INSERT INTO ootp_sql_admin (scope, module, setting, value) VALUES (";
$query.="'$scope', '$module', '$setting', '$newLgDate'";
$query.=") ON DUPLICATE KEY UPDATE value='$newLgDate';";
if ($debug==1) {echo "$query<br/>\n";}
mysqli_query($db,$query);

$_SESSION[$setting]=1;


mysqli_close($db);

if ($debug!=1) {header('Location: ./admin.php');}
?>
