<?php
define ('SITEAPP_ACCESS_LEVEL_COMMERCIAL',  1);
define ('SITEAPP_ACCESS_LEVEL_KEY',         2);
define ('SITEAPP_ACCESS_LEVEL',             SITEAPP_ACCESS_LEVEL_COMMERCIAL | SITEAPP_ACCESS_LEVEL_KEY);

// exceptions
class DBException extends Exception {};

// functions
function printr($str)
{
	echo $str . "\n";
}

function db_query($query, $link)
{
	$result = mysql_query($query, $link);
	if (!$result) {
		throw new DBException(mysql_error($link));
	}
	return $result;
}

function create_subquery($items)
{
	$f = array();
	foreach ($items as $k=>$v) {
		if (is_null($v)) {
			$value = 'NULL';
		} else {
			$value = sprintf("'%s'", mysql_escape_string($v));
		}
		$f[] = sprintf("`%s`=%s", $k, $value);
	}
	return implode(',', $f);
}

// $id is a string which looks like number (taken from query)
function remove_siteapp_entry($id, $link)
{
	$result = db_query("DELETE FROM SiteAppFiles WHERE instance_id = $id", $link);
	$result = db_query("DELETE FROM SiteAppResources WHERE app_id = $id", $link);
	$result = db_query("DELETE FROM SiteApps WHERE id = $id", $link);
}

// schemas
$schemas = array();
$schemas['APSLicenses'] = "CREATE TABLE `%s` (
    `id`                    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
,   `key_number`            VARCHAR(255) CHARACTER SET ascii COLLATE ascii_bin NOT NULL
,   `source`                TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL
,   `ka_url`                VARCHAR(255) CHARACTER SET ascii COLLATE ascii_bin NOT NULL
,   `expiration_date`       date default NULL
,   `update_date`           date default NULL
,   `license_type_id`       INT UNSIGNED NOT NULL
,   `personal`              ENUM ('false', 'true') NOT NULL DEFAULT 'false'
,                           UNIQUE (`key_number`)
) TYPE=InnoDB;";
$schemas['APSApplicationItems'] = "CREATE TABLE `%s` (
    `id`                    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
,   `license_type_id`       INT UNSIGNED NOT NULL
                                -- License type id
,   `pkg_id`                INT UNSIGNED NOT NULL DEFAULT 0
,   `shared`                ENUM ('false', 'true') NOT NULL DEFAULT 'false'
,   `disabled`              ENUM ('false', 'true') NOT NULL DEFAULT 'false'
,                           INDEX (`pkg_id`)
,                           UNIQUE (`license_type_id`, `pkg_id`)
) TYPE=InnoDB;";
$schemas['APSClientApplicationItems'] = "CREATE TABLE `%s` (
    `id`                    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
,   `client_id`             INT UNSIGNED NOT NULL DEFAULT 0
,   `app_item_id`           INT UNSIGNED NOT NULL DEFAULT 0
,   `instances_limit`       INT NOT NULL DEFAULT -1
,                           INDEX (`client_id`)
,                           INDEX (`app_item_id`)
,                           UNIQUE (`client_id`, `app_item_id`)
)  TYPE=InnoDB;";
$schemas['APSLicenseTypes'] = "CREATE TABLE `%s` (
    `id`                    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
,   `application_name`      VARCHAR(122) CHARACTER SET ascii COLLATE ascii_bin NOT NULL
,   `application_versions`  VARCHAR(122) CHARACTER SET ascii COLLATE ascii_bin NOT NULL
,   `application_features`  VARCHAR(255) CHARACTER SET ascii COLLATE ascii_bin NOT NULL
,   `license_type_hash`     VARCHAR(255) CHARACTER SET ascii COLLATE ascii_bin NOT NULL
,   `description`           VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci
,                           UNIQUE (`license_type_hash`)
,							UNIQUE(`application_name`, `application_versions`, `application_features`)
) TYPE=InnoDB";
$schemas['SiteApps'] = "CREATE TABLE `%s` (
    id                      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
,    `app_release`          VARCHAR(30) CHARACTER SET ascii COLLATE ascii_bin NOT NULL
,   dom_id                  INT UNSIGNED NOT NULL
,   dom_type                ENUM('domain', 'subdomain') NOT NULL DEFAULT 'domain'
,   install_prefix          VARCHAR(255) CHARACTER SET ascii COLLATE ascii_bin NOT NULL
,   htdocs_directory        ENUM('httpdocs', 'httpsdocs') NOT NULL DEFAULT 'httpdocs'
,   capp_item_id            INT UNSIGNED NOT NULL
,   params_id               INT UNSIGNED
,   `license_id`            INT UNSIGNED
,                           INDEX (capp_item_id)
,                           INDEX (params_id)
,                           INDEX (`license_id`)
) TYPE=InnoDB;";
$schemas['SiteAppPackages'] = "CREATE TABLE `%s` (
    id                      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
,   name                    VARCHAR(255) CHARACTER SET ascii COLLATE ascii_bin NOT NULL
,   version                 VARCHAR(30) CHARACTER SET ascii COLLATE ascii_bin NOT NULL
,   `release`                   VARCHAR(30) CHARACTER SET ascii COLLATE ascii_bin NOT NULL
,   description             VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci
,	categories				VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci
,   access_level            INT UNSIGNED NOT NULL DEFAULT 0
,   integrated              ENUM('false', 'true') NOT NULL DEFAULT 'false'
,   `package_type`          INT UNSIGNED NOT NULL DEFAULT 0
,   params_id               INT UNSIGNED
,                           UNIQUE (name, version)
) TYPE=InnoDB";

// get connection settings
$password = file_get_contents('/etc/psa/.psa.shadow');
$password = chop($password);
$link = mysql_connect('localhost', 'admin', $password);
if (!$link) {
	printr('Unable connect to the Plesk database:' . mysql_error());
	exit(1);
}
if (!mysql_select_db('psa', $link)) {
	printr('Unable to select database psa: ' . mysql_error($link));
	exit(1);
}


$new_tables = array('NEWSiteAppPackages', 'NEWSiteApps', 'APSApplicationItems', 'APSClientApplicationItems', 'APSLicenseTypes', 'APSLicenses');

// check that database already altered
$result = mysql_list_tables('psa', $link);
$found = array();

while ($row=mysql_fetch_row($result)) {
	$table_name = $row[0];
	$found[$table_name] = 1;
}
mysql_free_result($result);

if (isset($found['APSLicenses'])) {
	// i.e. APSLicenses was created successfully and all other APS tables too
	// so no need to perform upgrade
	print "APS: tables already upgraded.\n";
	exit(0);
}

if (isset($found['NEWSiteAppPackages']) && isset($found['NEWSiteApps']) 
	&& !isset($found['SiteAppPackages']) && !isset($found['SiteApps']))
{
	// unrecoverable fatal error
	print "Unable to perfrom APS tables upgrade: fatal unrecoverable error. Please restore database from dump and try again.\n";
	exit(1);
}


if (isset($found['NEWSiteAppPackages']) && isset($found['NEWSiteApps'])
	&& isset($found['SiteAppPackages']) && isset($found['SiteApps'])) 
{
	// it means that during database upgrade old tables were bot touched so drop all new tables
	// and perform upgrade process from scratch
	foreach ($new_tables as $t) {
		$query = sprintf('DROP TABLE IF EXISTS %s', $t);
		$res = mysql_query($query, $link);
		if (!$res) {
			printr('Mysql error: ' . mysql_error($link));
		}
	}
}

try {
	// check database server version
	$sql = "SHOW VARIABLES LIKE 'version'";
	$result = db_query($sql, $link);
	$db_ver = mysql_result($result, 0, 1);
	
	if (preg_match('/^(3|4\.0)\./', $db_ver)) {
		// not mysql 4.1 compatible, so transform queries
		foreach ($schemas as $k=>$v) {
			$new_scheme = preg_replace('/CHARACTER SET [0-9a-z_]+/', '', $v);
			$new_scheme = preg_replace('/COLLATE [0-9a-z_]+/', '', $new_scheme);
			$schemas[$k] = $new_scheme;
		}
	}

	// read table SiteAppPackages
	$siteapp_packages = array();
	$sql = sprintf('SELECT * FROM `SiteAppPackages`');
	$result = db_query($sql, $link);
	while ($row = mysql_fetch_assoc($result)) {
		$siteapp_packages[$row['id']] = $row;
	}
	mysql_free_result($result);
	
	// build new application items and new site app packages tables contents
	// We also should set which app item will be set up as 'not shared'

	$site_app_items = array();
	$map_sapkg_ai = array(); // mapping site app package id -> application item
	
	foreach ($siteapp_packages as $pkg_id => $sa_package) {
		// now column `commercial` has different meaning, so empty it
		$siteapp_packages[$pkg_id]['access_level'] = $siteapp_packages[$pkg_id]['access_level'] & SITEAPP_ACCESS_LEVEL_KEY;
		$siteapp_packages[$pkg_id]['categories'] = '';

		// check is AppItem already exists for given name and version
		$found = false;
		foreach ($site_app_items as $ind => $sa_item) {
			if ($sa_item['name'] == $sa_package['name'] 
				&& $sa_item['version'] == $sa_package['version']) 
			{
				$found = $ind;
				break;
			}
		}
		$set_shared = true;
		if (false !== $found) {
			// check is current package's release ">" than release of saved
		 	if ($site_app_items[$found]['release'] < $sa_package['release']) {
				$site_app_items[$found]['package_id'] = $sa_package['id'];
				$site_app_items[$found]['release'] = $sa_package['release'];
			}
			// if at least one package from group with the same name and version
			// was defined as COMMERCIAL or INTAGRATED then mark corresponding 
			// application items as NOT SHARED.
			if ('0' != $sa_package['access_level'] || 'true' == $sa_package['integrated']) {
				$site_app_items[$found]['shared'] = false;
			}
		} else {
			$found = 1 + count($site_app_items);
			$sa_item = array(
				'name' => $sa_package['name'],
				'version' => $sa_package['version'],
				'release' => $sa_package['release'],
				'package_id' => $sa_package['id'],
				'shared' => ('0' == $sa_package['access_level'] && 'false' == $sa_package['integrated'])
			);
			$site_app_items[$found] = $sa_item;
		}
		$map_sapkg_ai[$sa_package['id']] = $found;
	}

	// now $site_app_items contains all required info
	// now $map_sapkg_ai contains all required info

	// read table SiteApps
	$siteapps = array();
	$sql = sprintf('SELECT sa.id id, sa.dom_id dom_id, sa.dom_type dom_type, 
		sa.install_prefix install_prefix, sa.htdocs_directory htdocs_directory, sa.params_id params_id, sa.app_id app_id, d.cl_id cl_id
		FROM `SiteApps` AS sa INNER JOIN `domains` AS d ON (d.id=sa.dom_id) WHERE `dom_type`="domain"');
	$result = db_query($sql, $link);
	while ($row = mysql_fetch_assoc($result)) {
		$siteapps[] = $row;
	}
	mysql_free_result($result);

	$sql = sprintf('SELECT sa.id id, sa.dom_id dom_id, sa.dom_type dom_type, 
		sa.install_prefix install_prefix, sa.htdocs_directory htdocs_directory, sa.params_id params_id, sa.app_id app_id, d.cl_id cl_id
		FROM `SiteApps` AS sa INNER JOIN `subdomains` AS sd ON (sd.id=sa.dom_id) INNER JOIN  `domains` AS d ON (sd.dom_id=d.id) WHERE `dom_type`="subdomain"');
	$result = db_query($sql, $link);
	while ($row = mysql_fetch_assoc($result)) {
		$siteapps[] = $row;
	}
	mysql_free_result($result);
	
	// mapping capp_item_id -> (cl_id x app_item_id)

	// remove orphaned entries
	foreach ($siteapps as $ind => $sa) {
		if (!array_key_exists($sa['app_id'], $siteapp_packages)) {
			remove_siteapp_entry($sa['id'], $link);
			unset($siteapps[$ind]);
		}
	}

	$map_capp_cl_x_ai = array();
	// find what application items uses each siteapp
	foreach ($siteapps as $ind => $sa) {
		$pkg_id = $sa['app_id'];
		$siteapps[$ind]['app_release'] = $siteapp_packages[$pkg_id]['release'];
		unset($siteapps[$ind]['app_id']);
		$app_item_id = $map_sapkg_ai[$pkg_id];
		
		// find is corresponding client application item is created
		$found = false;
		foreach ($map_capp_cl_x_ai as $capp_item_id => $tuple) {
			if ($app_item_id == $tuple['app_item_id'] 
				&& $sa['cl_id'] == $tuple['cl_id']) 
			{
				$found = $capp_item_id;
				break;
			}
		}
		if (false === $found) {
			// create new client application item
			$tuple = array(
				'app_item_id' => $app_item_id,
				'cl_id' => $sa['cl_id']
			);
			$found = count($map_capp_cl_x_ai) + 1;
			$map_capp_cl_x_ai[$found] = $tuple;
		}
		$siteapps[$ind]['capp_item_id'] = $found;
		//$siteapps[$ind]['app_item_id'] = $map_sapkg_ai[$pkg_id];
	}

	// convert application pool
	$sql = 'SELECT c.id AS client_id, c.sapp_pool_id, r.component_id, sap.id AS pkg_id FROM clients AS c INNER JOIN Repository AS r ON c.sapp_pool_id=r.rep_id LEFT JOIN SiteAppPackages AS sap ON sap.id=r.component_id;';
	$result = db_query($sql, $link);
	while ($row = mysql_fetch_assoc($result)) {
		// add each record to the client's pool i.e. create application item
		// get corresponding application item
		$app_item_id = $map_sapkg_ai[$row['pkg_id']];
		// find corresponding capp_item
		$capp_item_id = false;
		foreach ($map_capp_cl_x_ai as $id => $tuple) {
			if ($row['client_id'] == $tuple['cl_id'] && $app_item_id == $tuple['app_item_id']) {
				$capp_item_id = $id;
				break;
			}
		}
		if (false == $capp_item_id) {
			$found = count($map_capp_cl_x_ai) + 1;
			$map_capp_cl_x_ai[$found] = array('cl_id' => $row['client_id'], 'app_item_id' => $app_item_id);
		}
	}
	mysql_free_result($result);

	// init temporary table for new application packages 
	$sql = sprintf($schemas['SiteAppPackages'], 'NEWSiteAppPackages');
	$result = db_query($sql, $link);
	foreach ($site_app_items as $app_item) {
		$pkg_id = $app_item['package_id'];
		$package = $siteapp_packages[$pkg_id];
		$sql = sprintf( 'INSERT INTO `%s` SET %s;', 'NEWSiteAppPackages', create_subquery($siteapp_packages[$pkg_id]) );
		db_query($sql, $link);
	}

	// init temporary table for site apps
	$sql = sprintf($schemas['SiteApps'], 'NEWSiteApps');
	$result = db_query($sql, $link);
	foreach ($siteapps as $app) {
		$_app = $app;
		unset($_app['cl_id']);
		$sql = sprintf( 'INSERT INTO `%s` SET %s;', 'NEWSiteApps', create_subquery($_app) );
		db_query($sql, $link);
	}

	// init temporary table for Application Items
	$sql = sprintf($schemas['APSApplicationItems'], 'APSApplicationItems');
	$result = db_query($sql, $link);
	foreach ($site_app_items as $k => $app_item) {
		$db_app_item = array(
			'id' => $k,
			'license_type_id' => '0',
			'pkg_id' => $app_item['package_id'],
			'shared' => $app_item['shared'] ? 'true' : 'false',
			'disabled' => 'false'
		);
		$sql = sprintf( 'INSERT INTO `%s` SET %s;', 'APSApplicationItems', create_subquery($db_app_item) );
		db_query($sql, $link);
	}
	
	// init temporary table for Client Application Items
	$sql = sprintf($schemas['APSClientApplicationItems'], 'APSClientApplicationItems');
	$result = db_query($sql, $link);
	foreach ($map_capp_cl_x_ai as $k => $tuple) {
		$db_capp_item = array(
			'id' => $k,
			'app_item_id' => $tuple['app_item_id'],
			'client_id' => $tuple['cl_id'],
			'instances_limit' => '-1'
		);
		$sql = sprintf( 'INSERT INTO `%s` SET %s;', 'APSClientApplicationItems', create_subquery($db_capp_item) );
		db_query($sql, $link);
	}
	
	// create other tables
	$tables = array('APSLicenseTypes', 'APSLicenses');
	foreach ($tables as $t) {
		$sql = sprintf($schemas[$t], $t);
		$result = db_query($sql, $link);
	}

	// drop old tables
	$tables = array('SiteApps', 'SiteAppPackages');
	foreach ($tables as $t) {
		$sql = sprintf('DROP TABLE IF EXISTS `%s`', $t);
		$result = db_query($sql, $link);
	}

	// rename new tables
	foreach ($tables as $t) {
		$sql = sprintf('ALTER TABLE `NEW%s` RENAME TO `%s`', $t, $t);
		$result = db_query($sql, $link);
	}

	// all done

} catch (DBException $e) {
	printr('Database error: ' . $e->getMessage());
	// cleanup 
	foreach ($new_tables as $t) {
		$query = sprintf('DROP TABLE IF EXISTS %s', $t);
		$res = mysql_query($query, $link);
		if (!$res) {
			printr('Mysql error: ' . mysql_error($link));
		}
	}
	exit(1);
}
?>
