/*
MySQL Data Transfer
Source Host: localhost
Source Database: vchat
Target Host: localhost
Target Database: vchat
Date: 25.7.2007 22:15:43
*/

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for vchat_members
-- ----------------------------
CREATE TABLE `vchat_members` (
  `id` char(32) NOT NULL,
  `user_id` int(10) unsigned default NULL,
  `nick` varchar(255) NOT NULL,
  `enter_time` double unsigned NOT NULL,
  `last_activity_time` double unsigned default NULL,
  `color` char(7) NOT NULL,
  `is_typing` tinyint(1) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for vchat_messages
-- ----------------------------
CREATE TABLE `vchat_messages` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `message_type` enum('sys','user') default NULL,
  `entrance_type` enum('out','expire','in') default NULL,
  `sender_id` char(32) NOT NULL,
  `sender_nick` varchar(100) NOT NULL,
  `sender_color` char(7) default NULL,
  `recipient_id` char(32) default NULL,
  `recipient_nick` varchar(100) default NULL,
  `recipient_color` char(7) default NULL,
  `private` tinyint(1) unsigned NOT NULL default '0',
  `message` text,
  `time` double unsigned NOT NULL,
  `is_user` tinyint(1) unsigned default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for vchat_users
-- ----------------------------
CREATE TABLE `vchat_users` (
  `id` int(4) unsigned NOT NULL auto_increment,
  `login` varchar(50) NOT NULL,
  `password` char(32) NOT NULL,
  `chat_color` char(8) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records 
-- ----------------------------
INSERT INTO `vchat_users` VALUES ('1', 'user1', '24c9e15e52afc47c225b757e7bee1f9d', '#ff0000');

/*
error_reporting(E_ALL);  // nastavi uroven reportovani
function obsluha_chyb($errno, $errmsg, $filename, $linenum, $vars) {
	if (error_reporting()) {
      // pokud je nastavena nenulova uroven reportovani - pred funkci neni znak @
      echo $filename.' :: '.$linenum.' :: '.$errmsg."<br />\n";
    }
}
set_error_handler("obsluha_chyb");
*/


$mysqli = new mysqli('host', 'username', 'password', 'dbname');

if (mysqli_connect_errno()) {
	simpleReply(2, 'Nelze nevázat spojení s databází');
	exit;
}
$mysqli->query('set names utf8');

$tbUsers = 'vchat_users';
$tbUsersUserNameColumn = 'login';
$tbChatMembers = 'vchat_members';
$tbMessages = 'vchat_messages';
$maxMemberMinutes = 30;
$maxMessageMinutes = 200;
$maxRows = 200;

switch ($_REQUEST['action']) {
	case "insertMessage" :
		insertMessage();
		break;
	case "autologin" :
		break;
	case "loginUser" :
		login('user');
		break;
	case "loginGuest" :
		login('guest');
		break;
	case "refresh" :
		refresh();
		break;
	case "refreshInfo":
		refresh('info');
		break;
	case "logout" :
		logout();
		break;
	case "clean" :
		clean();
		break;
	default :
		simpleReply(2, 'Špatný typ požadavku');
		break;
}

// Promazani vChatu. Provadi se s pravdepodobnosti 1/6
//if (rand(0, 5) == 0) {
clean();
//}

// prihlasovani uzivatele
function login($loginType) {
	global $mysqli, $tbUsers, $tbChatMembers, $tbMessages, $tbUsersUserNameColumn;

	$chatId = null;
	$error = '';

	if ($loginType == 'user') {

		$login = $_REQUEST['login'];
		$cryptedPassword = md5($_REQUEST['password']);

		$stmt1 = $mysqli->prepare("select id, {$tbUsersUserNameColumn}, chat_color from {$tbUsers} u where u.{$tbUsersUserNameColumn} = ? and u.password = ?");
		$stmt1->bind_param('ss', $login, $cryptedPassword);
		$stmt1->execute();
		$stmt1->bind_result($userId, $nick, $prefferedColor);
		$stmt1->store_result();

		if ($stmt1->num_rows == 1) {
			$stmt1->fetch();
			$chatId = md5($userId);

			$stmt2 = $mysqli->prepare("select count(*) as sum from {$tbChatMembers} where id = ?");
			$stmt2->bind_param('s', $chatId);
			$stmt2->execute();
			$stmt2->bind_result($sum);
			$stmt2->fetch();

			if ($sum == 0) {
				$stmt2->free_result();

				$time = getmicrotime();
				$color = getColor($prefferedColor);

				$status = insertUser($chatId, $userId, $nick, $color, $time);

				if ($status == 2) {
					$error = 'Chyba při zápisu do databáze';
				}
			} else {
				$status = 1;
			}
		} else {
			$status = 2;
			$error = 'Špatné přihlašovací údaje';
		}
	} else
		if ($loginType == 'guest') {

			$time = getmicrotime();
			$color = getColor();
			$userId = null;
			$nick = $_REQUEST['nick'];
			$chatId = md5($time);

			$stmt2 = $mysqli->prepare("select count(*) as sum from {$tbChatMembers} where nick = ?");
			$stmt2->bind_param('s', $nick);
			$stmt2->execute();
			$stmt2->bind_result($sum);
			$stmt2->fetch();

			if ($sum == 0) {
				$stmt2->free_result();
				
				$status = insertUser($chatId, $userId, $nick, $color, $time);

				if ($status == 2) {
					$error = 'Chyba při zápisu do databáze';
				}
			} else {
				$status = 2;
				$error = 'Tato přezdívka je již obsazena';
			}
		}

	header("Content-type: text/xml");
	header("Cache-Control: no-cache");

	$xml = "<?xml version=\"1.0\"?>\n";
	$xml .= "<response>\n";
	$xml .= "\t<status>{$status}</status>\n";
	if ($chatId != null)
		$xml .= "\t<chatId>{$chatId}</chatId>\n";
	if ($error != null)
		$xml .= "\t<error><![CDATA[{$error}]]></error>\n";
	$xml .= "</response>";

	echo $xml;
}

function insertUser($chatId, $userId, $nick, $color, $time) {
	global $mysqli, $tbChatMembers, $tbMessages;
	
	$status = null;

	$mysqli->autocommit(false);

	$stmt3 = $mysqli->prepare("insert into {$tbChatMembers} set id = ?, user_id = ?, enter_time = ?, last_activity_time = ?, nick = ?, color = ?");
	$stmt3->bind_param('siddss', $chatId, $userId, $time, $time, $nick, $color);
	$stmt3->execute();

	$stmt4 = $mysqli->prepare("insert into {$tbMessages} set message_type = 'sys', entrance_type = 'in', sender_id = ?, sender_nick = ?, sender_color = ?, time = ?, is_user = ?");
	$isUser = $userId > 0 ? 1:0;
	$stmt4->bind_param('sssdi', $chatId, $nick, $color, $time, $isUser);
	$stmt4->execute();

	if (!$mysqli->commit()) {
		$mysqli->rollback();
		$status = 2;
	} else {
		$status = 1;
	}

	return $status;
}

// funkce pro odpoved pomoci 'jednoducheho' xml
function simpleReply($status, $error) {
	header("Content-type: text/xml");
	header("Cache-Control: no-cache");

	$xml = "<?xml version=\"1.0\"?>\n";
	$xml .= "<response>\n";
	$xml .= "\t<status>{$status}</status>\n";
	$xml .= "\t<error><![CDATA[{$error}]]></error>\n";
	$xml .= "</response>";

	echo $xml;
}

// vycisteni starych prispevku a neaktivnich uzivatelu
function clean() {
	global $mysqli, $tbUsers, $tbChatMembers, $tbMessages, $maxMemberMinutes, $maxMessageMinutes;

	$mt = getmicrotime();
	$t1 = $mt - (60 * $maxMemberMinutes);

	// odhlaseni uzivatelu, kteri nejsou aktivni
	$stmt = $mysqli->prepare("select id, nick, color, user_id from {$tbChatMembers} where last_activity_time < ?");
	$stmt->bind_param('d', $t1);
	$stmt->execute();
	$stmt->bind_result($_chatId, $_nick, $_color, $_userId);
	$reason = 'expire';
	$expiredUsers = array ();
	while ($stmt->fetch()) {
		$expiredUsers[] = array (
			$_chatId,
			$_nick,
			$_color,
			$_userId
		);
	}

	foreach ($expiredUsers as $data) {
		removeUser($data[0], $data[1], $data[2], $data[3], $t1, $reason);
	}

	// isTyping priznak
	$stmt = $mysqli->prepare("update {$tbChatMembers} set is_typing = 0 where last_activity_time < ?");
	$t2 = $mt -5;
	$stmt->bind_param('d', $t2);
	$stmt->execute();

	// smazani stary prispevku
	$t3 = $mt - (60 * $maxMessageMinutes);
	$stmt = $mysqli->prepare("delete from {$tbMessages} where time < ?");
	$stmt->bind_param('d', $t3);
	$stmt->execute();
}

// odstraneni uzivatele
function removeUser($chatId, $nick, $color, $userId, $time, $reason) {
	global $mysqli, $tbUsers, $tbChatMembers, $tbMessages;

	$status = 1;
	$error = null;

	$mysqli->autocommit(false);

	$stmt1 = $mysqli->prepare("insert into {$tbMessages} set message_type = 'sys', sender_id = ?, entrance_type = ?, sender_nick = ?, sender_color = ?, time = ?, is_user = ?");

	$isUser = $userId > 0;
	$stmt1->bind_param('ssssdi', $chatId, $reason, $nick, $color, $time, $isUser);
	$stmt1->execute();

	$stmt2 = $mysqli->prepare("delete from {$tbChatMembers} where id = ?");
	$stmt2->bind_param('s', $chatId);
	$stmt2->execute();

	if (!$mysqli->commit()) {
		$mysqli->rollback();
		$status = 2;
		$error = 'Chyba při zápisu do databáze';
	}

	return array (
		'status' => $status,
		'error' => $error
	);
}

function insertMessage() {
	global $mysqli, $tbUsers, $tbChatMembers, $tbMessages;

	$lastTime = $_REQUEST['lastTime'];
	$chatId = $_REQUEST['chatId'];
	$message = trim(htmlspecialchars($_REQUEST['message']));
	$recipientId = $_REQUEST['recipientId'];
	$private = $_REQUEST['private'] == 'true' ? 1 : 0;
	$members = array ();
	$time = getmicrotime();
	$error = null;

	if ($message == '') {
		$status = 2;
		$error = 'Není zadána zpráva';
	} else
		if ($chatId == null) {
			$status = 2;
			$error = 'Odesílatel zprávy není definován';
		} else {
			if ($recipientId != null) {
				$stmt1 = $mysqli->prepare("select id, nick, color, user_id from {$tbChatMembers} where id in(?, ?)");
				$stmt1->bind_param('ss', $chatId, $recipientId);
				$stmt1->execute();
				$stmt1->bind_result($_chatId, $_nick, $_color, $_userId);
				$stmt1->store_result();

				if ($stmt1->num_rows != 2) {
					$status = 2;
					$error = 'Odesílatel nebo adresát nenalezen';
				} else {
					while ($stmt1->fetch()) {
						$members[$_chatId] = array (
							'nick' => $_nick,
							'color' => $_color,
							'userId' => $_userId
						);
					}
				}
				$stmt1->free_result();
			} else {

				$stmt1 = $mysqli->prepare("select id, nick, color, user_id from {$tbChatMembers} where id = ?");
				$stmt1->bind_param('s', $chatId);
				$stmt1->execute();
				$stmt1->bind_result($_chatId, $_nick, $_color, $_userId);
				$stmt1->store_result();

				if ($stmt1->num_rows != 1) {
					$status = 2;
					$error = 'Odesílatel nenalezen';
				} else {
					$stmt1->fetch();
					$members[$_chatId] = array (
						'nick' => $_nick,
						'color' => $_color,
							'userId' => $_userId
					);

				}
				$stmt1->free_result();
			}
		}

	if ($error == null) {

		$mysqli->autocommit(false);

		$stmt2 = $mysqli->prepare("update {$tbChatMembers} set last_activity_time = ? where id = ?");
		$stmt2->bind_param('ds', $time, $chatId);
		$stmt2->execute();
		$isUser = $members[$chatId]['userId'] > 0 ? 1:0;
		if ($recipientId != null) {
			$sql = "insert into {$tbMessages} set message_type = 'user', sender_id = ?, sender_nick = ?, sender_color = ?, recipient_id = ?, recipient_nick = ?, recipient_color = ?, time = ?, message = ?, private = ?, is_user = ?";
			$stmt3 = $mysqli->prepare($sql);
			$stmt3->bind_param('ssssssdsii', $chatId, $members[$chatId]['nick'], $members[$chatId]['color'], $recipientId, $members[$recipientId]['nick'], $members[$recipientId]['color'], $time, $message, $private, $isUser);
			$stmt3->execute();
		} else {
			$stmt3 = $mysqli->prepare("insert into {$tbMessages} set message_type = 'user', sender_id = ?, sender_nick = ?, sender_color = ?, time = ?, message = ?, is_user = ?");
			$stmt3->bind_param('sssdsi', $chatId, $members[$chatId]['nick'], $members[$chatId]['color'], $time, $message, $isUser);
			$stmt3->execute();
		}

		if (!$mysqli->commit()) {
			$mysqli->rollback();
			$status = 2;
			$error = 'Chyba při zápisu do databáze';
		} else {
			$status = 1;
		}
	}

	header("Content-type: text/xml");
	header("Cache-Control: no-cache");

	$xml = "<?xml version=\"1.0\"?>\n";
	$xml .= "<response>\n";
	$xml .= "\t<status>{$status}</status>\n";
	$xml .= "\t<error><![CDATA[{$error}]]></error>\n";
	$xml .= "</response>";

	echo $xml;
}

function refresh($type = 'user') {
	global $mysqli, $tbUsers, $tbChatMembers, $tbMessages, $maxRows;

	$lastTime = $_REQUEST['lastTime'];
	$chatId = $_REQUEST['chatId'];
	$isTyping = $_REQUEST['isTyping'] == 'true' ? 1 : 0;
	$validUser = false;
	$error = null;
	$newTime = getmicrotime();
	$status = 1;

	$xmlMembers = '';
	$xmlMessages = '';

	header("Content-type: text/xml");
	header("Cache-Control: no-cache");

	$xml = "<?xml version=\"1.0\" encoding=\"utf-8\"?>\n";
	$xml .= "<response>\n";

	// is_typing flag
	if ($chatId != null) {
		$stmt1 = $mysqli->prepare("update {$tbChatMembers} set is_typing = ?, last_activity_time = ? where id = ?");
		$stmt1->bind_param('ids', $isTyping, $newTime, $chatId);
		$stmt1->execute();
	}

	// members in chat
	$members = array ();
	$stmt2 = $mysqli->prepare("select id, nick, color, is_typing, user_id from {$tbChatMembers} order by nick asc");
	$stmt2->bind_result($_chatId, $_nick, $_color, $_isTyping, $userId);
	$stmt2->execute();
	while ($stmt2->fetch()) {
		if (!$validUser && $chatId == $_chatId)
			$validUser = true;
		$members[$_chatId] = array (
			'inXml' => ($lastTime == "0"
		), 'nick' => $_nick, 'color' => $_color, 'inChat' => true, 'isTyping' => ($_isTyping == 1), 'userId' => $userId);
	}

	if ($type == 'info' || $validUser) {
		// messages
		if ($lastTime != 0) {
			$stmt3 = $mysqli->prepare("select * from {$tbMessages} where (private = 0 || (private = 1 and (recipient_id = ? or sender_id = ?))) and time > ? order by time desc limit {$maxRows}");
			$stmt3->bind_param('ssd', $chatId, $chatId, $lastTime);
			$stmt3->execute();
		} else {

			$stmt3 = $mysqli->prepare("select * from {$tbMessages} where (private = 0 || (private = 1 and (recipient_id = ? or sender_id = ?))) order by time desc limit {$maxRows}");
			$stmt3->bind_param('ss', $chatId, $chatId);
			$stmt3->execute();
		}
		$stmt3->bind_result($_id, $_messageType, $_entranceType, $_senderId, $_senderNick, $_senderColor, $_recipientId, $_recipientNick, $_recipientColor, $_private, $_message, $_time, $_isUser);

		while ($stmt3->fetch()) {

			if (!array_key_exists($_senderId, $members)) {
				$members[$_senderId] = array (
					'inXml' => true,
					'nick' => $_senderNick,
					'color' => $_senderColor,
					'inChat' => false,
					'isTyping' => false, 
					'isUser' => $_isUser
				);
			} else {
				$members[$_senderId]['inXml'] = true;
			}

			$_msg = '';
			switch ($_messageType) {
				case 'sys' :
					$_msg = "\t\t<sysMessage isUser=\"{$_isUser}\" type=\"{$_entranceType}\" chatId=\"{$_senderId}\" time=\"{$_time}\" />\n";
					break;
				case 'user' :
					if ($_recipientId != null) {
						if (!array_key_exists($_recipientId, $members)) {
							$members[$_recipientId] = array (
								'inXml' => true,
								'nick' => $_recipientNick,
								'color' => $_recipientNick,
								'inChat' => false,
								'isTyping' => false
							);
						} else {
							$members[$_recipientId]['inXml'] = true;
						}
					}
					$_msg = "\t\t<memMessage " . ($_private == 1 ? "private=\"true\" " : '') . "senderChatId=\"{$_senderId}\" " . ($_recipientId != null ? "recipiendChatId=\"{$_recipientId}\"" : '') . " time=\"{$_time}\">\n";
					$_msg .= "<![CDATA[" . stripslashes($_message) . "]]>\n";
					$_msg .= "\t\t</memMessage>\n";
					break;
			}
			$xmlMessages = $_msg . $xmlMessages;
		}

		foreach ($members as $chatId => $member) {
			if ($member['inXml'] || $member['isTyping'] == 1) {
				$xmlMembers .= "\t\t<member chatId=\"{$chatId}\" userId=\"{$member['userId']}\" color=\"{$member['color']}\"" . ($member['isTyping'] ? ' isTyping="true"' : '') . "" . ($member['inChat'] ? ' inChat="true"' : '') . "><![CDATA[{$member['nick']}]]></member>\n";
			}
		}
	} else {
		$status = 3;
		$error = "Autentizace selhala";
	}

	$xml .= "\t<members>\n{$xmlMembers}\t</members>\n";
	$xml .= "\t<messages>\n{$xmlMessages}\t</messages>\n";
	$xml .= "\t<sysInfo>\n";
	$xml .= "\t\t<status>{$status}</status>\n";
	$xml .= "\t\t<lastTime>{$newTime}</lastTime>\n";
	if ($error != null)
		$xml .= "\t<error><![CDATA[{$error} {$checkToken}]]></error>\n";
	$xml .= "\t</sysInfo>\n";
	$xml .= "</response>";
	
	if($type == 'info') {
		//$xml = iconv('UTF-8', 'WINDOWS-1250', $xml);
	}

	echo $xml;
}

// odhlaseni uzivatele
function logout() {
	global $mysqli, $tbUsers, $tbChatMembers, $tbMessages;

	$chatId = $_REQUEST['chatId'];
	$error = '';
	$status = 1;

	if ($chatId != null) {
		// odhlaseni uzivatelu, kteri nejsou aktivni
		$stmt = $mysqli->prepare("select nick, color, user_id from {$tbChatMembers} where id = ?");
		$stmt->bind_param('s', $chatId);
		$stmt->execute();
		$stmt->bind_result($nick, $color, $userId);
		$stmt->store_result();

		if ($stmt->num_rows == 1) {
			$stmt->fetch();
			$res = removeUser($chatId, $nick, $color, $userId, getmicrotime(), 'out');
			$status = $res['status'];
			$error = $res['error'];
		} else {
			$status = 2;
			$error = 'Chyba odhlášení - chybné ID uživatele';
		}
	} else {
		$status = 2;
		$error = 'Chyba odhlášení - není zadáno ID uživatele';
	}

	simpleReply($status, $error);
}

// service function
function getColor($preferredColor = null) {
	global $mysqli, $tbUsers, $tbChatMembers, $tbMessages;

	$usedColors = array ();
	if ($result = $mysqli->query("select color from {$tbChatMembers}")) {
		while ($obj = $result->fetch_object()) {
			$usedColors[] = $obj->color;
		}
	}

	$color = null;
	if ($preferredColor != null && !in_array($preferredColor, $usedColors)) {
		$color = $preferredColor;
	} else {
		$colors = array (
			'#009900',
			'#0066FF',
			'#CC3366',
			'#FF33FF',
			'#006633',
			'#0000CC',
			'#CC6633',
			'#000000',
			'#FF0000',
			'#ADAA27',
			'#FF6600',
			'#7D01E3',
			'#336699'
		);
		foreach ($colors as $_color) {
			if (!in_array($_color, $usedColors)) {
				$color = $_color;
				break;
			}
		}
		if ($color == null) {
			if ($preferredColor != null)
				$color = $preferredColor;
			else
				$colors[rand(0, sizeof($colors) - 1)];
		}
	}
	return $color;
}

function getmicrotime() {
	list ($usec, $sec) = explode(" ", microtime());
	return ((float) $usec + (float) $sec);
}

$mysqli->close();
?>