#!/usr/local/bin/php -q 
<?php

$log_file = '/var/log/roundcube/import-addrbook.log';
$usergroups_name_garbage = 'AddrGarbage';

ini_set("memory_limit", "256M");

function logging($file_handler, $message_type, $message_text, $stderr = false) {
	fputs($file_handler, ($message_type ? date("Y-m-d H:i:s ").$message_type." " : "").$message_text);
	if ($stderr) fputs($stderr, $message_text);
}

$stderr = fopen("php://stderr", "w");
if ($stderr) {
	$log = fopen($log_file, "a");
	if (!$log) {
		fputs($stderr, "couldn't open file ".$log_file." for appending\n");
	} else {
		$data = array();

		define('IN_VMA','1');
		include('/usr/local/www/vmailadmin/includes/config.php');
		logging($log, "", "======================================================================\n");
		logging($log, "info:   ", "try to detect MySQL connection parameters for vMail.Admin DB...", $stderr);
		if (!isset($database) or !is_array($database) or !isset($database['host']) or !$database['host'] or !isset($database['name']) or !$database['name'] or !isset($database['username']) or !$database['username'] or !isset($database['password']) or !$database['password']) {
			logging($log, "", " failed\n", $stderr);
			logging($log, "error:  ", "couldn't detect MySQL connection parameters for vMail.Admin DB\n", $stderr);
		} else {
			$mysql_user = $database['username'];
			$mysql_pass = $database['password'];
			$mysql_host = $database['host'];
			$mysql_db = $database['name'];
			logging($log, "", " success\n", $stderr);
			logging($log, "info:   ", "connect to MySQL server '$mysql_host' as user '$mysql_user'...", $stderr);
			$db_connect_id = mysqli_connect($mysql_host, $mysql_user, $mysql_pass, $mysql_db);
			if (!$db_connect_id) {
				logging($log, "", " failed\n", $stderr);
				logging($log, "error:  ", "couldn't connect to MySQL server '$mysql_host' as user '$mysql_user'\n", $stderr);
			} else {
				logging($log, "", " success\n", $stderr);

					$ok = true;

					logging($log, "info:   ", "get vMail.Admin users info\n", $stderr);
					$users = array();
					$query = sprintf("SELECT transport.domain, mailbox.pobox AS mail, mailbox.name FROM mailbox, transport WHERE mailbox.domain_id = transport.domain_id AND transport.domain <> 'localhost' AND transport.domain NOT LIKE 'mail%%';");
					logging($log, "info:   ", "execute MySQL query '$query'...", $stderr);
					$res = mysqli_query($db_connect_id, $query);
					if (!$res) {
						logging($log, "", " failed\n", $stderr);
						logging($log, "error:  ", "couldn't execute MySQL query '$query'\n", $stderr);
						$ok = false;
					} else {
						logging($log, "", " success\n", $stderr);
						while ($row = mysqli_fetch_array($res)) {
							list($row['lastname'], $row['firstname']) = explode(' ', trim($row['name']));
							$data[$row['domain']][] = $row;
						}
						mysqli_free_result($res);
					}

				logging($log, "info:   ", "close MySQL connection...", $stderr);
				if (mysqli_close($db_connect_id)) {
					logging($log, "", " success\n", $stderr);
				} else {
					logging($log, "", " failed\n", $stderr);
					logging($log, "error:  ", "couldn't close connection to MySQL server '$mysql_host'\n", $stderr);
				}
			}
		}
//print_r($data);
//die();

		$mysql_config = '';
		if (file_exists('/usr/local/www/roundcube/config/db.inc.php')) {
			include('/usr/local/www/roundcube/config/db.inc.php');
			 $mysql_config= $rcmail_config['db_dsnw'];
		} elseif (file_exists('/usr/local/www/roundcube/config/config.inc.php')) {
			include('/usr/local/www/roundcube/config/config.inc.php');
			$mysql_config = $config['db_dsnw'];
		}
		if ($mysql_config) {
			logging($log, "", "======================================================================\n");
			logging($log, "info:   ", "try to detect MySQL connection parameters for roundcube DB...", $stderr);
			if (!preg_match('/mysql:\/\/([^:\@]+)(?::([^@]*))\@([^\/]+)\/(.+)/', $mysql_config, $matches)) {
				logging($log, "", " failed\n", $stderr);
				logging($log, "error:  ", "couldn't detect MySQL connection parameters for roundcube DB\n", $stderr);
			} else {
				$mysql_user = $matches[1];
				$mysql_pass = $matches[2];
				$mysql_host = $matches[3];
				$mysql_db = $matches[4];
				logging($log, "", " success\n", $stderr);
				logging($log, "info:   ", "connect to MySQL server '$mysql_host' as user '$mysql_user'...", $stderr);
				$db_connect_id = mysqli_connect($mysql_host, $mysql_user, $mysql_pass, $mysql_db);
				if (!$db_connect_id) {
					logging($log, "", " failed\n", $stderr);
					logging($log, "error:  ", "couldn't connect to MySQL server '$mysql_host' as user '$mysql_user'\n", $stderr);
				} else {
					logging($log, "", " success\n", $stderr);

						$ok = true;

						logging($log, "info:   ", "get roundcube users info\n", $stderr);
						$users = array();
						$query = sprintf("SELECT * FROM users;");
						logging($log, "info:   ", "execute MySQL query '$query'...", $stderr);
						$res = mysqli_query($db_connect_id, $query);
						if (!$res) {
							logging($log, "", " failed\n", $stderr);
							logging($log, "error:  ", "couldn't execute MySQL query '$query'\n", $stderr);
							$ok = false;
						} else {
							logging($log, "", " success\n", $stderr);
							while ($row = mysqli_fetch_array($res)) {
								$users[$row['user_id']] = $row;
							}
							mysqli_free_result($res);
						}

						logging($log, "info:   ", "get roundcube contactgroups info\n", $stderr);
						$contactgroups = array();
						$query = sprintf("SELECT * FROM contactgroups;");
						logging($log, "info:   ", "execute MySQL query '$query'...", $stderr);
						$res = mysqli_query($db_connect_id, $query);
						if (!$res) {
							logging($log, "", " failed\n", $stderr);
							logging($log, "error:  ", "couldn't execute MySQL query '$query'\n", $stderr);
							$ok = false;
						} else {
							logging($log, "", " success\n", $stderr);
							while ($row = mysqli_fetch_array($res)) {
								$contactgroups[$row['user_id']][$row['name']] = $row;
							}
							mysqli_free_result($res);
						}
//print_r($contactgroups);
//die();

						if ($ok and $data) {
							foreach ($users as $user_id => $user_rec) {
								$domain = explode('@', $user_rec['username'])[1];
								$usergroups = array();
								if (isset($data[$domain]) and is_array($data[$domain]) and (count($data[$domain]) > 0)) $usergroups[] = $domain;
								if ($domain == 'luxeform.ua') $usergroups[] = 'rost.ua';
								if ($domain == 'rost.ua') $usergroups[] = 'luxeform.ua';

								logging($log, "info:   ", "get contacts of user '".$user_rec['username']."'\n", $stderr);
								$contacts = array();
								$contacts_list_contact_id = array();
								$contacts_contactgroups = array();
								$query = sprintf(
									"SELECT contactgroups.contactgroup_id, contactgroups.name AS contactgroup_name, contacts.* FROM contacts ".
									"LEFT JOIN contactgroupmembers ON contactgroupmembers.contact_id = contacts.contact_id ".
									"LEFT JOIN contactgroups ON contactgroups.contactgroup_id = contactgroupmembers.contactgroup_id AND ".
									"contactgroups.user_id = %d AND contactgroups.del = 0 ".
									"WHERE contacts.user_id = %d AND contacts.del = 0 ".
									"ORDER BY contacts.user_id, contacts.email, contacts.contact_id;",
									$user_id, $user_id
								);
//print($query);
//die();

								logging($log, "info:   ", "execute MySQL query '$query'...", $stderr);
								$res = mysqli_query($db_connect_id, $query);
								if (!$res) {
									logging($log, "", " failed\n", $stderr);
									logging($log, "error:  ", "couldn't execute MySQL query '$query'\n", $stderr);
								} else {
									logging($log, "", " success\n", $stderr);
									while ($row = mysqli_fetch_array($res)) {
										$row['old'] = 1;
										$row['email'] = strtolower($row['email']);
										$contacts[$row['email']][$row['contact_id']] = $row;
										if (!$contacts_list_contact_id[$row['email']]) $contacts_list_contact_id[$row['email']] = $row['contact_id'];
										$contacts_contactgroups[$row['email']][$row['contactgroup_name']] = $row;
									}
									mysqli_free_result($res);
//print($user_id." ".$user_rec['username']."\n");
//print_r($contacts);
//print_r($contacts_list_contact_id);
//print_r($contacts_contactgroups);
//die();

									foreach ($usergroups as $usergroups_name) {
										if (!$contactgroups[$user_id][$usergroups_name]['contactgroup_id']) {
											logging($log, "info:   ", "add contactgroup '$usergroups_name' for user '".$users[$user_id]['username']."'\n", $stderr);

											$query = sprintf(
												"INSERT INTO contactgroups (user_id, changed, del, name) VALUES ('%s', NOW(), '%s', '%s');",
												$user_id, 0, $usergroups_name
											);
//print($query);
//die();
											logging($log, "info:   ", "execute MySQL query '$query'...", $stderr);
											$res = mysqli_query($db_connect_id, $query);
											if (!$res) {
												logging($log, "", " failed\n", $stderr);
												logging($log, "error:  ", "couldn't execute MySQL query '$query'\n", $stderr);
											} else {
												logging($log, "", " success\n", $stderr);
												$contactgroups[$user_id][$usergroups_name] = array('contactgroup_id' => mysqli_insert_id($db_connect_id), 'name' => $usergroups_name);
											}
										}

										if ($contactgroups[$user_id][$usergroups_name]['contactgroup_id']) {

											// for each of all contacts
											foreach ($data[$usergroups_name] as $addrbook_item) {
//print_r($addrbook_item);
//die();
												$contacts_contactgroups[$addrbook_item['mail']][$usergroups_name]['old'] = 0;
												logging($log, "info:   ", sprintf("user: '%s'; contact: '%s'", $user_rec['username'], $addrbook_item['mail']), $stderr);

												$vcard = sprintf(
"BEGIN:VCARD
VERSION:3.0
N:%s;%s;;;
FN:%s
EMAIL;type=INTERNET;type=WORK:%s
END:VCARD",
str_replace("'", "\\'", $addrbook_item['firstname']), str_replace("'", "\\'", $addrbook_item['lastname']), str_replace("'", "\\'", $addrbook_item['name']), $addrbook_item['mail']
												);

												// if contact with this e-mail exists
												if ($contacts[$addrbook_item['mail']]) {
//print_r($contacts[$addrbook_item['mail']]);
//die();
													logging($log, "", " exists", $stderr);
													// if contacts isn't included in the $usergroups_name addrbook
													if (!$contacts_contactgroups[$addrbook_item['mail']][$usergroups_name]['contactgroup_id']) {
														logging($log, "", ", add to $usergroups_name addrbook\n", $stderr);

														$query = sprintf(
															"INSERT INTO contactgroupmembers (contactgroup_id, contact_id, created) VALUES ('%s', '%s', NOW());",
															$contactgroups[$user_id][$usergroups_name]['contactgroup_id'], $contacts_list_contact_id[$addrbook_item['mail']]
														);
//print($query);
//die();
														logging($log, "info:   ", "execute MySQL query '$query'...", $stderr);
														$res = mysqli_query($db_connect_id, $query);
														if (!$res) {
															logging($log, "", " failed\n", $stderr);
															logging($log, "error:  ", "couldn't execute MySQL query '$query'\n", $stderr);
														} else {
															logging($log, "", " success\n", $stderr);
															$contacts_contactgroups[$addrbook_item['mail']][$usergroups_name] = array(
																'contactgroup_id' => $contactgroups[$user_id][$usergroups_name]['contactgroup_id'],
																'contactgroup_name' => $usergroups_name,
																'contact_id' => $contacts_list_contact_id[$addrbook_item['mail']],
																'name' => $addrbook_item['name'],
																'firstname' => $addrbook_item['firstname'],
																'surname' => $addrbook_item['lastname'],
																'email' => $addrbook_item['mail'],
																'user_id' => $user_id
															);
														}

													// if contacts is included in the $usergroups_name addrbook
													} else {
														logging($log, "", "\n", $stderr);
													}

													$query = sprintf("UPDATE contacts SET del=0, name = '%s', firstname = '%s', surname = '%s', vcard = '%s' WHERE contact_id = '%s';", 
														str_replace("'", "\\'", $addrbook_item['name']),
														str_replace("'", "\\'", $addrbook_item['firstname']),
														str_replace("'", "\\'", $addrbook_item['lastname']),
														$vcard,
														$contacts_contactgroups[$addrbook_item['mail']][$usergroups_name]['contact_id']
													);
//print($query);
//die();
//													logging($log, "info:   ", "execute MySQL query '$query'...", $stderr);
													$res = mysqli_query($db_connect_id, $query);
													if (!$res) {
//														logging($log, "", " failed\n", $stderr);
														logging($log, "error:  ", "couldn't execute MySQL query '$query'\n", $stderr);
													} else {
//														logging($log, "", " success\n", $stderr);
													}

												// if contact with this e-mail doesn't exist
												} else {
													logging($log, "", "doesn't exist\n", $stderr);

													// construct VCARD for new contact
													// add new contact
													$query = sprintf(
														"INSERT INTO contacts (changed, del, name, email, firstname, surname, vcard, words, user_id) VALUES (NOW(), '%s', '%s', '%s', '%s', '%s', '%s', ' %s %s', '%s');",
														0, str_replace("'", "\\'", $addrbook_item['name']), $addrbook_item['mail'],
														str_replace("'", "\\'", $addrbook_item['firstname']), str_replace("'", "\\'", $addrbook_item['lastname']),
														$vcard, str_replace("'", "\\'", $addrbook_item['name']), $addrbook_item['mail'], $user_id
													);
//print($query);
//die();
//													logging($log, "info:   ", "execute MySQL query '$query'...", $stderr);
													$res = mysqli_query($db_connect_id, $query);
													if (!$res) {
//														logging($log, "", " failed\n", $stderr);
														logging($log, "error:  ", "couldn't execute MySQL query '$query'\n", $stderr);
													} else {
//														logging($log, "", " success\n", $stderr);
														$contact_id = mysqli_insert_id($db_connect_id);
														$contacts[$addrbook_item['mail']][$contact_id] = array(
															'contact_id' => $contact_id,
															'del' => 0,
															'email' => $addrbook_item['mail'],
															'name' => $addrbook_item['name'],
															'firstname' => $addrbook_item['firstname'],
															'surname' => $addrbook_item['lastname'],
															'vcard' => $vcard,
															'words' => ' '.$addrbook_item['name'].' '.$addrbook_item['mail'],
															'user_id' => $user_id
														);
														$contacts_contactgroups[$addrbook_item['mail']][$usergroups_name] = $contacts[$addrbook_item['mail']][$contact_id];
														$contacts_list_contact_id[$addrbook_item['mail']] = $contact_id;

														// include new contact to TonisBook
														$query = sprintf(
															"INSERT INTO contactgroupmembers (contactgroup_id, contact_id, created) VALUES ('%s', '%s', NOW());",
															$contactgroups[$user_id][$usergroups_name]['contactgroup_id'], $contacts_list_contact_id[$addrbook_item['mail']]
														);
//print($query);
//die();
//														logging($log, "info:   ", "execute MySQL query '$query'...", $stderr);
														$res = mysqli_query($db_connect_id, $query);
														if (!$res) {
//															logging($log, "", " failed\n", $stderr);
															logging($log, "error:  ", "couldn't execute MySQL query '$query'\n", $stderr);
														} else {
//															logging($log, "", " success\n", $stderr);
														}
													}
												}
											}

											// for each "old" contacts from RoundCube
											foreach ($contacts_contactgroups as $email => $contacts_contactgroup) {
												// if this contact doesn't exists in source list
												if ($contacts_contactgroup[$usergroups_name]['old']) {
//print($user_id." ".$user_rec['username']."\n");
//print($email);
//print_r($contacts_contactgroup[$usergroups_name]);
//die();
													logging($log, "info:   ", "delete contact $email from addresbook of user ".$users[$user_id]['username']."\n", $stderr);
//print_r($contacts_contactgroup[$usergroups_name]);
//print_r($contacts_contactgroup[$usergroups_name_garbage]);
//die();
													$query = sprintf(
														"UPDATE contacts SET del=1 WHERE contact_id = '%s';",
														$contacts_contactgroup[$usergroups_name]['contact_id']
													);
//print($query);
//die();
													logging($log, "info:   ", "execute MySQL query '$query'...", $stderr);
													$res = mysqli_query($db_connect_id, $query);
													if (!$res) {
														logging($log, "", " failed\n", $stderr);
														logging($log, "error:  ", "couldn't execute MySQL query '$query'\n", $stderr);
													} else {
														logging($log, "", " success\n", $stderr);
													}
												}
											}
										}
									}
								}
							}
						}
					logging($log, "info:   ", "close MySQL connection...", $stderr);
					if (mysqli_close($db_connect_id)) {
						logging($log, "", " success\n", $stderr);
					} else {
						logging($log, "", " failed\n", $stderr);
						logging($log, "error:  ", "couldn't close connection to MySQL server '$mysql_host'\n", $stderr);
					}
				}
			}
		}
		fclose($log);
	}
	fclose($stderr);
}

?>