<?php

$dbFile = dirname(__file__) . "/db.php";
include($dbFile);
function login($username, $password)
{
	global $mysqli;
	$query = $mysqli->prepare("SELECT * FROM users WHERE username = ?");
	$query->bind_param("s", $username);
	$query->execute();
	$result = $query->get_result();
	if ($result->num_rows > 0) {
		$array = $result->fetch_array();
		if (password_verify($password, $array['password_hash'])) {
			return true;
		} else {
			return false;
		}
	} else {
		return false;
	}
}
function addUser($username, $password)
{
	global $mysqli;;

	$passwordHash = password_hash($password, PASSWORD_DEFAULT);
	$query = $mysqli->prepare("INSERT INTO users (username, password_hash) VALUES (?,?);");
	$query->bind_param("ss", $username, $passwordHash);
	$query->execute();
	return true;
}
function callApi($api_link, $jsonBody)
{
	global $authVar;
	$curl = curl_init();
	curl_setopt_array($curl, array(
	  CURLOPT_URL => $api_link,
	  CURLOPT_RETURNTRANSFER => true,
	  CURLOPT_MAXREDIRS => 10,
	  CURLOPT_TIMEOUT => 5,
	  CURLOPT_FOLLOWLOCATION => true,
	  CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
	  CURLOPT_CUSTOMREQUEST => 'POST',
	  CURLOPT_POSTFIELDS => $jsonBody,
	  CURLOPT_SSL_VERIFYHOST => 0,
	  CURLOPT_SSL_VERIFYPEER => 0,
	  CURLOPT_HEADER => 1,
	  CURLOPT_HTTPHEADER => array(
		'Content-Type: application/json',
		'token-auth: ' . $authVar
	  ),
	));
	$response = curl_exec($curl);
	
	$header_size = curl_getinfo($curl, CURLINFO_HEADER_SIZE);
	$header = substr($response, 0, $header_size);
	$html = substr($response, $header_size);
	$headers = get_headers_from_curl_response($header);
	curl_close($curl);
	return ["result" => "success", "data" => $html, "headers" => $headers];
	
}

function get_headers_from_curl_response($headerRaw)
{
	$headers = array();

	foreach (explode("\r\n", $headerRaw) as $i => $line)
		if ($i === 0)
			$headers['http_code'] = $line;
		else {
			list($key, $value) = explode(': ', $line);

			$headers[$key] = $value;
		}

	return $headers;
}

function savePanelRoot($rootPath)
{
	global $mysqli;
	$query = $mysqli->prepare("UPDATE config SET panel_root=?");
	$query->bind_param("s", $rootPath);
	$query->execute();
}
function loadPanelRoot()
{
	global $mysqli;
	$query = $mysqli->prepare("SELECT * FROM config");
	$query->execute();
	$result = $query->get_result();
	$resultArray = $result->fetch_array();
	return $resultArray['panel_root'];
}
function addDNS($name, $url)
{
	global $mysqli;
	$query = $mysqli->prepare("INSERT INTO dns (name, url,  active) VALUES (?,?,1);");
	$query->bind_param("ss", $name, $url);
	$query->execute();
}
function deleteDNS($id)
{
	global $mysqli;
	$query = $mysqli->prepare("DELETE FROM dns WHERE id=?");
	$query->bind_param("i", $id);
	$query->execute();
}
function saveDNS($id, $name, $url, $active)
{
	global $mysqli;
	$activeInt = 1;
	if ($active == false) {
		$activeInt = 0;
	}
	$query = $mysqli->prepare("UPDATE dns SET name=?, url=?, active=? WHERE id=?");
	$query->bind_param("ssii", $name, $url, $activeInt, $id);
	$query->execute();
}

function loadAllDNS($activeOnly = false)
{
	global $mysqli;
	$returnable = array();
	if ($activeOnly) {
		$query = $mysqli->prepare("SELECT * FROM dns;");
	} else {
		$query = $mysqli->prepare("SELECT * FROM dns WHERE active = 1;");
	}
	$query->execute();
	$result = $query->get_result();
	while ($resultArray = $result->fetch_array()) {
		$thisDNS = array();
		$thisDNS['id'] = $resultArray['id'];
		$thisDNS['name'] = $resultArray['name'];
		$thisDNS['portal'] = $resultArray['url'];
		$thisDNS['active'] = $resultArray['active'];
		$returnable[] = $thisDNS;
	}
	return $returnable;
}
function loadDNS($id)
{
	global $mysqli;
	$query = $mysqli->prepare("SELECT * FROM dns WHERE id = ?;");
	$query->bind_param("i", $id);
	$query->execute();
	$result = $query->get_result();
	$resultArray = $result->fetch_array();
	$returnable = array();
	$returnable['id'] = $resultArray['id'];
	$returnable['name'] = $resultArray['name'];
	$returnable['portal'] = $resultArray['url'];
	$returnable['active'] = $resultArray['active'];
	return $returnable;
}

function saveProfile($id, $username, $password)
{
	$passwordHash = password_hash($password, PASSWORD_DEFAULT);
	global $mysqli;
	$query = $mysqli->prepare("UPDATE users SET	username=?, password_hash=? WHERE id=?");
	$query->bind_param("ssi", $username, $passwordHash, $id);
	$query->execute();
}
function loadProfile($id)
{
	global $mysqli;
	$query = $mysqli->prepare("SELECT * FROM users WHERE id = ?");
	$query->bind_param("i", $id);
	$query->execute();
	$result = $query->get_result();
	$resultArray = $result->fetch_array();
	$returnable = array();
	$returnable['username'] = $resultArray['username'];
	$returnable['password'] = '******';
	return $returnable;
}

//SECURITY NOTE - never let $tableName be set by user - always harcoded
function updateDbEntry($tableName, $data)
{
	global $mysqli;
	$where = "";
	if (isset($data['id'])) {
		$where = " WHERE id = " . $data['id'];
	}
	unset($data['id']);
	$fields = "";
	foreach ($data as $fieldName => $fieldValue) {
		if ($fields != "") {
			$fields .= ", ";
		}
		$fields .= $fieldName;

		$fields .= "='" . $mysqli->real_escape_string($fieldValue) . "'";
	}
	$query = "UPDATE $tableName SET " . $fields . $where;
	$mysqli->query($query);
}
function addDbEntry($tableName, $data)
{

	global $mysqli;
	$fields = "";
	$values = "";
	foreach ($data as $fieldName => $fieldValue) {
		if ($fields != "") {
			$fields .= ", ";
		}
		$fields .= $fieldName;
		if ($values != "") {
			$values .= ", ";
		}
		$values .= "'" . $mysqli->real_escape_string($fieldValue) . "'";
	}
	$query = "INSERT INTO $tableName ($fields) VALUES ($values)";
	$mysqli->query($query);
}
//SECURITY NOTE - never let $tableName be set by user - always harcoded
function getDbEntry($tableName, $id = 0)
{
	global $mysqli;
	$where = "";
	if ($id != 0) 
	{
		$where = " WHERE id = " . $mysqli->real_escape_string($id);
	}
	$query = "SELECT * FROM $tableName " . $where;
	$result = $mysqli->query($query);
	return $result->fetch_assoc();
}

function getUsersForDns($dnsId)
{
	$returnable = array();
	global $mysqli;
	$query = $mysqli->prepare("SELECT * FROM ibo_users WHERE dns_id = ?");
	$query->bind_param("i", $dnsId);
	$query->execute();
	$result = $query->get_result();

	while($resultArray = $result->fetch_array())
	{
		$toAdd = array();
		$toAdd['id'] = $resultArray['id'];
		$toAdd['username'] = $resultArray['username'];
		$toAdd['password'] = $resultArray['password'];
		$toAdd['mac_address'] = $resultArray['mac_address'];
		$returnable[] = $toAdd;
	}
	return $returnable;
}

function loadIBOUser($dns, $userId)
{
	$returnable = array();
	global $mysqli;
	$query = $mysqli->prepare("SELECT * FROM ibo_users WHERE dns_id = ? AND mac_address = ?");
	$query->bind_param("is",$dns, base64_decode($userId));
	$query->execute();
	$result = $query->get_result();
	return $result->fetch_array();
}
function deleteIBOUser($dns, $userId)
{
	$returnable = array();
	global $mysqli;
	$query = $mysqli->prepare("DELETE FROM ibo_users WHERE dns_id = ? AND mac_address = ?");
	$query->bind_param("is",$dns, base64_decode($userId));
	$query->execute();
	
}

function getUsersForMac($macAddress)
{
	$returnable = array();
	global $mysqli;
	$query = $mysqli->prepare("SELECT * FROM ibo_users WHERE mac_address = ?");
	$query->bind_param("s", $macAddress);
	$query->execute();
	$result = $query->get_result();
	while($resultArray = $result->fetch_array())
	{
		$toAdd = array();
		$toAdd['username'] = $resultArray['username'];
		$toAdd['password'] = $resultArray['password'];
		$returnable[$resultArray['dns_id']] = $toAdd;
	}
	return $returnable;
}

function addOrUpdateAccount($dnsId, $macAddress, $username, $password)
{
	$returnable = array();
	global $mysqli;
	$query = $mysqli->prepare("SELECT * FROM ibo_users WHERE dns_id = ? AND mac_address = ?");
	$query->bind_param("is", $dnsId,$macAddress);
	$query->execute();
	$result = $query->get_result();
	if($result->num_rows)
	{
		//update
		$query = $mysqli->prepare("UPDATE ibo_users SET username = ?, password = ? WHERE dns_id = ? AND mac_address = ?");
		$query->bind_param("ssis",$username,$password,$dnsId,$macAddress);
		$query->execute();
	}
	else
	{
		//insert
		$query = $mysqli->prepare("INSERT INTO ibo_users (dns_id, mac_address, username, password) VALUES(?, ?, ?, ?);");
		$query->bind_param("isss",$dnsId,$macAddress,$username,$password);
		$query->execute();
	}
}
	function loadHAXSportsCategories()
{
	global $mysqli;
	$query = $mysqli->prepare("SELECT * FROM hax_sports_categories");
	$query->execute();
	$result = $query->get_result();
	$returnable = array();
	while ($resultArray = $result->fetch_array()) {
		$returnable[] = $resultArray;
	}
	return $returnable;
}
function loadHAXSportsCategory($id)
{
	global $mysqli;
	$query = $mysqli->prepare("SELECT * FROM hax_sports_categories WHERE id = ?");
	$query->bind_param("i", $id);

	$query->execute();
	$result = $query->get_result();
	return $result->fetch_array();
}
function loadHAXSportsEventsDisplay()
{
	global $mysqli;

	$query = $mysqli->prepare("SELECT hax_sports_events.*, cat.title as category_id, ta.name as team_a_id, tb.name as team_b_id
	 FROM hax_sports_events 
	LEFT JOIN hax_sports_teams ta on hax_sports_events.team_a_id = ta.id
	LEFT JOIN hax_sports_teams tb on hax_sports_events.team_b_id = tb.id
	LEFT JOIN hax_sports_categories cat on hax_sports_events.category_id = cat.id ORDER BY start_timestamp");
	$query->execute();
	$result = $query->get_result();
	$returnable = array();
	while ($resultArray = $result->fetch_array()) {
		$returnable[] = $resultArray;
	}
	return $returnable;
}
function loadHAXSportsEvents($category = 0)
{
	global $mysqli;
	if ($category == 0) {
		$query = $mysqli->prepare("SELECT * FROM hax_sports_events ORDER BY start_timestamp");
	} else {
		$query = $mysqli->prepare("SELECT * FROM hax_sports_events WHERE category_id = ? ORDER BY start_timestamp");
		$query->bind_param("i", $category);
	}
	$query->execute();
	$result = $query->get_result();
	$returnable = array();
	while ($resultArray = $result->fetch_array()) {
		$returnable[] = $resultArray;
	}
	return $returnable;
}
function loadHAXSportsEvent($id)
{
	global $mysqli;

	$query = $mysqli->prepare("SELECT * FROM hax_sports_events WHERE id = ?");
	$query->bind_param("i", $id);

	$query->execute();
	$result = $query->get_result();
	$returnable = array();
	return $result->fetch_array();
}
function loadHAXSportsTeams()
{
	global $mysqli;
	$query = $mysqli->prepare("SELECT * FROM hax_sports_teams");
	$query->bind_param("i", $teamId);
	$query->execute();
	$result = $query->get_result();
	$returnable = array();
	while ($resultArray = $result->fetch_array()) {
		$returnable[] = $resultArray;
	}
	return $returnable;
}
function deleteHAXCategory($id)
{
	global $mysqli;
	$query = $mysqli->prepare("DELETE FROM hax_sports_categories WHERE id=?");
	$query->bind_param("i", $id);
	$query->execute();
}
function file_curl_contents($url, $getHeaders = false)
{
	global $authVar;
	global $versionVar;
	 $ch = curl_init();

    curl_setopt($ch, CURLOPT_HEADER, 0);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
    curl_setopt($ch, CURLOPT_URL, $url);
	$headers = [
		
		'User-Agent: OnePanel LTQD',
		'token-auth: ' . $authVar,
		'version-token: ' . $versionVar
	];
	curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);
	curl_setopt($ch, CURLOPT_HEADER, 1);
	curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 0);
	$response = curl_exec($ch);

	$header_size = curl_getinfo($ch, CURLINFO_HEADER_SIZE);
	$header = substr($response, 0, $header_size);
	$html = substr($response, $header_size);

	$headers = get_headers_from_curl_response($header);
	if($getHeaders)
	{
		return ["data" => $html, "headers" => $headers];
	}
	else
	{
		return $html;
	}
   
}
function deleteHAXEvent($id)
{
	global $mysqli;
	$query = $mysqli->prepare("DELETE FROM hax_sports_events WHERE id=?");
	$query->bind_param("i", $id);
	$query->execute();
}
function updateSports()
{
	global $mysqli;
	global $sportsApiKey;
	$leagues = loadHAXSportsCategories();

	$refreshHash = md5(time());
	foreach($leagues as $league)
	{
		if($league['league_id'])
		{
			$teamIds = array();
			$teams = file_curl_contents("https://andyhax.uk/sports/" . $sportsApiKey . "/teams/" . $league['league_id']);
			$teamsInfo = json_decode($teams,true);
			foreach($teamsInfo as $thisTeam)
			{
				$checkQuery = $query = $mysqli->prepare("SELECT * FROM hax_sports_teams WHERE league_id = ? AND team_id = ?");
				$checkQuery->bind_param("ii", $league['league_id'],$thisTeam['id']);
				$checkQuery->execute();
				$result = $checkQuery->get_result();
				if (!$result->num_rows) 
				{
					$addQuery = $mysqli->prepare("INSERT INTO `hax_sports_teams` (`team_id`, `name`, `flag`, `league_id`, `update_hash`, `created_at`, `updated_at`) VALUES (?, ?, ?, ?, ?, NOW(), NOW())");
					$addQuery->bind_param("issis", $thisTeam['id'],$thisTeam['name'],$thisTeam['flag'],$league['league_id'],$refreshHash);
					$addQuery->execute();
					$dbId = $addQuery->insert_id;
				}
				else
				{
					$resultArray = $result->fetch_array();
					$dbId = $resultArray['id'];
					$updateQuery = $mysqli->prepare("UPDATE hax_sports_teams SET update_hash = ? WHERE team_id = ? AND league_id = ?");
					$updateQuery->bind_param("sii", $refreshHash,$thisTeam['id'],$league['league_id']);
					$updateQuery->execute();
				}
				$teamIds[$thisTeam['id']] = $dbId;
			}
			$updateQuery = $mysqli->prepare("DELETE FROM hax_sports_teams WHERE league_id = ? AND update_hash != ?");
			$updateQuery->bind_param("is", $league['league_id'],$refreshHash);
			$updateQuery->execute();
	
		$events = file_curl_contents("https://andyhax.uk/sports/" . $sportsApiKey . "/events/" . $league['league_id']);
		$eventsInfo = json_decode($events,true);
			foreach($eventsInfo as $thisevent)
			{
				$checkQuery = $query = $mysqli->prepare("SELECT * FROM hax_sports_events WHERE league_id = ? AND event_id = ?");
				$checkQuery->bind_param("ii", $league['league_id'],$thisevent['id']);
				$checkQuery->execute();
				$result = $checkQuery->get_result();
				
				$teamAId = $teamIds[$thisevent['team_a_id']];
				$teamBId = $teamIds[$thisevent['team_b_id']];
				if(!$teamAId && $thisevent['team_a_id'])
				{
					//need to check the database and then grab from the API if not.
					$teamQuery = $mysqli->prepare("SELECT * FROM hax_sports_teams WHERE league_id = ? AND team_id = ?");
					$teamQuery->bind_param("ii", $league['league_id'],$thisevent['team_a_id']);
					$teamQuery->execute();
					$teamResult = $teamQuery->get_result();
					if($teamResult->num_rows)
					{
						//got it already, get the id and add it
						$resultArray = $teamResult->fetch_array();
						$teamDbId = $resultArray['id'];
					}
					else{
						//don't have it, fetch it individually from the API
						$team = file_curl_contents("https://andyhax.uk/sports/" . $sportsApiKey . "/team/" . $thisevent['team_a_id']);
						$teamsInfo = json_decode($team,true);
						foreach($teamsInfo as $thisTeam)
						{
							$checkQuery = $query = $mysqli->prepare("SELECT * FROM hax_sports_teams WHERE league_id = ? AND team_id = ?");
							$checkQuery->bind_param("ii", $league['league_id'],$thisTeam['id']);
							$checkQuery->execute();
							$teamResult = $checkQuery->get_result();
							if (!$teamResult->num_rows) 
							{
								$addQuery = $mysqli->prepare("INSERT INTO `hax_sports_teams` (`team_id`, `name`, `flag`, `league_id`, `update_hash`, `created_at`, `updated_at`) VALUES (?, ?, ?, ?, ?, NOW(), NOW())");
								$addQuery->bind_param("issis", $thisTeam['id'],$thisTeam['name'],$thisTeam['flag'],$league['league_id'],$refreshHash);
								$addQuery->execute();
								$teamDbId = $addQuery->insert_id;
							}
							else
							{
								$resultArray = $teamResult->fetch_array();
								$teamDbId = $resultArray['id'];
								$updateQuery = $mysqli->prepare("UPDATE hax_sports_teams SET update_hash = ? WHERE team_id = ? AND league_id = ?");
								$updateQuery->bind_param("sii", $refreshHash,$thisTeam['id'],$league['league_id']);
								$updateQuery->execute();
							}
						}
					}
					$teamAId = $teamDbId;
				}
				if(!$teamBId && $thisevent['team_b_id'])
				{
					//need to check the database and then grab from the API if not.
					$teamQuery = $mysqli->prepare("SELECT * FROM hax_sports_teams WHERE league_id = ? AND team_id = ?");
					$teamQuery->bind_param("ii", $league['league_id'],$thisevent['team_b_id']);
					$teamQuery->execute();
					$teamResult = $teamQuery->get_result();
					if($teamResult->num_rows)
					{
						//got it already, get the id and add it
						$resultArray = $teamResult->fetch_array();
						$teamDbId = $resultArray['id'];
					}
					else{
						//don't have it, fetch it individually from the API
						$team = file_curl_contents("https://andyhax.uk/sports/" . $sportsApiKey . "/team/" . $thisevent['team_b_id']);
						$teamsInfo = json_decode($team,true);
						foreach($teamsInfo as $thisTeam)
						{
							$checkQuery = $query = $mysqli->prepare("SELECT * FROM hax_sports_teams WHERE league_id = ? AND team_id = ?");
							$checkQuery->bind_param("ii", $league['league_id'],$thisTeam['id']);
							$checkQuery->execute();
							$teamResult = $checkQuery->get_result();
							if (!$teamResult->num_rows) 
							{
								$addQuery = $mysqli->prepare("INSERT INTO `hax_sports_teams` (`team_id`, `name`, `flag`, `league_id`, `update_hash`, `created_at`, `updated_at`) VALUES (?, ?, ?, ?, ?, NOW(), NOW())");
								$addQuery->bind_param("issis", $thisTeam['id'],$thisTeam['name'],$thisTeam['flag'],$league['league_id'],$refreshHash);
								$addQuery->execute();
								$teamDbId = $addQuery->insert_id;
							}
							else
							{
								$resultArray = $teamResult->fetch_array();
								$teamDbId = $resultArray['id'];
								$updateQuery = $mysqli->prepare("UPDATE hax_sports_teams SET update_hash = ? WHERE team_id = ? AND league_id = ?");
								$updateQuery->bind_param("sii", $refreshHash,$thisTeam['id'],$league['league_id']);
								$updateQuery->execute();
							}
						}
					}
					$teamBId = $teamDbId;
				}
				if (!$result->num_rows) 
				{
					$addQuery = $mysqli->prepare("INSERT INTO `hax_sports_events` (`event_id`, `team_a_id`, `team_b_id`, `backdrop`, `start_timestamp`, `end_timestamp`, `description`, `created_at`, `updated_at`, `category_id`,  `league_id`, `update_hash`) VALUES (?, ?, ?, ?, ?, ?, ?, NOW(), NOW(), ?, ?, ?)");
					$addQuery->bind_param("iiissssiis", $thisevent['id'],$teamAId,$teamBId,$thisevent['backdrop'],$thisevent['start_timestamp'],$thisevent['end_timestamp'],$thisevent['description'],$league['id'],$league['league_id'],$refreshHash);
					$addQuery->execute();
					$dbId = $addQuery->insert_id;
					//now add some channels
				}
				else
				{
					$resultArray = $result->fetch_array();
					$dbId = $resultArray['id'];
					$updateQuery = $mysqli->prepare("UPDATE hax_sports_events SET team_a_id = ?, team_b_id = ?, `start_timestamp` = ?, `end_timestamp` = ?, update_hash = ? WHERE event_id = ? AND league_id = ?");
					$updateQuery->bind_param("iisssii",$teamAId,$teamBId, $thisevent['start_timestamp'],$thisevent['end_timestamp'],$refreshHash,$thisevent['id'],$league['league_id']);
					$updateQuery->execute();
				}
				foreach($thisevent['channels'] as $channel)
				{
					$channelCheck = $mysqli->prepare("SELECT * FROM hax_sports_event_channels WHERE event_id = ? AND channel_name = ?");
					$channelCheck->bind_param("is", $dbId,$channel['name']);
					$channelCheck->execute();
					$channelResult = $channelCheck->get_result();
					if($channelResult->num_rows == 0)
					{
						//add
						$addQuery = $mysqli->prepare("INSERT INTO hax_sports_event_channels (event_id, channel_name, channel_epg, channel_logo) VALUES(?, ?, ?, ?);");
						$addQuery->bind_param("isss", $dbId,$channel['name'],$channel['epg'],$channel['logo']);
						$addQuery->execute();
					}
					else
					{
						$addQuery = $mysqli->prepare("UPDATE hax_sports_event_channels SET channel_epg = ?, channel_logo = ? WHERE event_id = ? AND channel_name = ?;");
						$addQuery->bind_param("ssis",$channel['epg'],$channel['logo'], $dbId,$channel['name']);
						$addQuery->execute();
					}
					
				}
			}
			$updateQuery = $mysqli->prepare("DELETE FROM hax_sports_events WHERE league_id = ? AND update_hash != ?");
			$updateQuery->bind_param("is", $league['league_id'],$refreshHash);
			$updateQuery->execute();
		}
	}
}

function loadHAXSportsEventsForDate($dateString)
{
		global $mysqli;

		$query = $mysqli->prepare("select * from hax_sports_events where start_timestamp > ? AND start_timestamp < DATE_ADD(?,INTERVAL 1 DAY) ORDER BY start_timestamp");
		$query->bind_param("ss", $dateString, $dateString);

	$query->execute();
	$result = $query->get_result();
	$returnable = array();
	while ($resultArray = $result->fetch_array()) {
		$returnable[] = $resultArray;
	}
	return $returnable;
}
function loadHAXChannelsForEvent($eventId)
{
	global $mysqli;

	$query = $mysqli->prepare("select * from hax_sports_event_channels WHERE event_id = ?");
	$query->bind_param("s", $eventId);

	$query->execute();
	$result = $query->get_result();
	$returnable = array();
	while ($resultArray = $result->fetch_assoc()) {
		$returnable[] = $resultArray;
	}
	return $returnable;
}