<?php
$_db_migration_to = '29.02'; // 8.3.0-rc
if (!isset($migrations) || !is_object($migrations))
	die("This file cannot be executed directly");
$migrations->CheckValid($_db_migration_to);
//===========================================================================================



$migrations->Run('01_long_tables_indexes.php', <<<'DB_UPDATE_FILE'
<?php
/** @var \Claromentis\Setup\SetupFacade $migrations */
$db = $migrations->GetDb();

// reduce url_hash column length from 255 to 32 chars and re-create index so it's not too long
$db->DropIndex('ERMS_component_url', 'date_and_hash_idx');
$table_descr = array(
	'component_id'	=>	"INT NULL",
	'user_id'	=>	"INT NULL",
	'date_created_utime'	=>	"INT NULL",
	'url_hash'	=>	"VARCHAR(32) NULL",
);

$db->CreateTable('ERMS_component_url', $table_descr, true);
$db->CreateIndex('ERMS_component_url', 'date_and_hash_idx', 'date_created_utime', 'url_hash');


// Shorten record type name and recreate index
$db->DropIndex('ERMS_record_types', 'ERMS_record_types_name');
$db->AlterColumn('ERMS_record_types', 'name', 'name', "VARCHAR(100) NULL");
$db->CreateIndex('ERMS_record_types', 'ERMS_record_types_name', 'name');



// Rebuild cln_sended_reminders
$table_descr = array(
	'send_date'	=>	"INT_DATE NULL",
	'hash'	=>	"VARCHAR(100) NULL",
);
$db->CreateTable('cln_sent_reminders', $table_descr);

$db->query("INSERT INTO cln_sent_reminders SELECT * FROM cln_sended_reminders");
$db->CreateIndex('cln_sent_reminders', 'cln_sent_reminders_hash', 'hash');

$db->DropIndex('cln_sended_reminders', 'cln_sended_reminders_hash');
$db->DropTable('cln_sended_reminders');


// Reduce cln_sys_event_options.event_key from 255 to 100 characters
$db->DropIndex('cln_sys_event_options', 'cln_sys_event_opt_event_user');
$db->AlterColumn('cln_sys_event_options', 'event_key', 'event_key', 'VARCHAR(100) NULL');
$db->CreateIndex('cln_sys_event_options', 'cln_sys_event_opt_event_user', 'event_key', 'user_id');


// This index exists in schema file, but the column was already dropped
// The at-symbol is there because this index might not exist, and we have no way of checking
@$db->DropIndex('oauth_auth_codes', 'oauth_auth_codes_session_id_foreign');

// Recreate index with part of name column
$db->DropIndex('pub_category', 'pub_category_del_parent_name');
$db->CreateIndex('pub_category', 'pub_category_del_parent_name', 'deleted', 'parent', 'name(100)');

// Recreate index with part of title column
$db->DropIndex('pub_page', 'pub_page_del_parent_title');
$db->CreateIndex('pub_page', 'pub_page_del_parent_title', 'deleted', 'cat_id', 'title(100)');


// Shorten simple_acl.object_code from 255 to 100 characters
$db->DropIndex('simple_acl', 'simple_acl_object_code');
$db->DropIndex('simple_acl', 'simple_acl_user_object');

$db->AlterColumn('simple_acl', 'object_code', 'object_code', "VARCHAR(100) NULL");

$db->CreateIndex('simple_acl', 'simple_acl_object_code', 'object_code');
$db->CreateIndex('simple_acl', 'simple_acl_user_object', 'user_id', 'object_code');

DB_UPDATE_FILE
);


$migrations->Run('01_migrate_gallery_comments.php', <<<'DB_UPDATE_FILE'
<?php
/** @var $migrations Claromentis\Setup\SetupFacade */
$db = $migrations->GetDb();

// 15 is AGGREGATION_IMAGE_GALLERY

$db->query("INSERT INTO discussions (user_id, object_id, ancillary, aggregation, parent_id, attachment_id, txt, timestamp, like_count, reply_count)
	SELECT user_id, object_id, status, aggregation, 0, 0, comment_text, date_created, 0, 0 FROM comments WHERE aggregation = 15");

$db->query("DELETE FROM comments WHERE aggregation = 15");

DB_UPDATE_FILE
);


$migrations->Run('01_user_ext_info.php', <<<'DB_UPDATE_FILE'
<?php
/** @var \Claromentis\Setup\SetupFacade $migrations */
$db = $migrations->GetDb();

$db->AddColumn('users', 'ext_type', 'VARCHAR(20)');
$db->AddColumn('users', 'ext_id', 'VARCHAR(255)');

DB_UPDATE_FILE
);


$migrations->Run('02_ext_type_migrate.php', <<<'DB_UPDATE_FILE'
<?php
/** @var \Claromentis\Setup\SetupFacade $migrations */
$db = $migrations->GetDb();

list($loc_count) = $db->query_row("SELECT COUNT(1) FROM users WHERE intranetuser='loc'");
//list($yes_count) = $db->query_row("SELECT COUNT(1) FROM users WHERE intranetuser='yes'");

if ($loc_count > 0)
{
	$db->query("UPDATE users SET ext_type='ldap' WHERE intranetuser='yes' AND ldap_guid IS NOT NULL");
	$db->query("UPDATE users SET intranetuser='yes' WHERE intranetuser='loc'");
}

DB_UPDATE_FILE
);


$migrations->Run('02_mail_handle_flag.php', <<<'DB_UPDATE_FILE'
<?php
/** @var \Claromentis\Setup\SetupFacade $migrations */
$db = $migrations->GetDb();

$db->AddColumn('hd_project', 'handle_incoming_mail', 'INT DEFAULT 0');
DB_UPDATE_FILE
);


$migrations->Run('03_remove_bg_exec_count.php', <<<'DB_UPDATE_FILE'
<?php
/** @var \Claromentis\Setup\SetupFacade $migrations */
$db = $migrations->GetDb();

$db->query("DELETE FROM variables WHERE var_name='background_counter'");

DB_UPDATE_FILE
);


$migrations->Run('04_migrate_ldap_config.php', <<<'DB_UPDATE_FILE'
<?php
/** @var \Claromentis\Setup\SetupFacade $migrations */
$db = $migrations->GetDb();

$config = $migrations->GetConfigFull();

$directories = $config->Get('cfg_users_directories');
if (!empty($directories))
{
	$new_directories = [];
	foreach ($directories as $key=>$value)
	{
		if (is_array($value))
		{
			$new_directories[$key] = $value;
			continue;
		}

		if ($value === 'user.directory.ldap_directory')
			continue;
	}

	if ($config['cfg_LDAP_enabled']) // is LDAP is not enabled, just not creating a record for it
	{
		$ldap_settings = [];

		if ($config['cfg_LDAP_server'] && $config['cfg_LDAP_port'])
		{
			$server_url = $config['cfg_LDAP_server'] . ':' . $config['cfg_LDAP_port'];
			if (!preg_match('|^ldaps?://|i', $server_url))
				$server_url = 'ldap://' . $server_url;
		} else
		{
			$server_url = '';
		}

		$ldap_settings['enabled'] = true;
		$ldap_settings['server_url'] = $server_url;
		$ldap_settings['user'] = $config['cfg_LDAP_user'];
		$ldap_settings['password'] = $config['cfg_LDAP_password'];
		$ldap_settings['domain'] = $config['cfg_LDAP_domain'];
		$ldap_settings['base_dn'] = $config['cfg_LDAP_DN'];

		$ldap_settings['user_type_filter'] = $config['cfg_LDAP_user_type_filter'];
		$ldap_settings['user_enabled_filter'] = $config['cfg_LDAP_user_enabled_filter'];
		$ldap_settings['username_field'] = $config['cfg_LDAP_username_field'];
		$ldap_settings['membership_field'] = $config['cfg_LDAP_membership_field'];
		$ldap_settings['guid_field'] = $config['cfg_LDAP_use_guid'];

		$ldap_settings['groups'] = $config['cfg_LDAP_limit_to_group'];
		if (!empty($ldap_settings['groups']) && !is_array($ldap_settings['groups']))
			$ldap_settings['groups'] = [$ldap_settings['groups']];
		$ldap_settings['ous'] = $config['cfg_LDAP_limit_to_OU'];
		if (!empty($ldap_settings['ous']) && !is_array($ldap_settings['ous']))
			$ldap_settings['ous'] = [$ldap_settings['ous']];

		$ldap_settings['bind_by_dn'] = $config['cfg_LDAP_bind_by_dn'];

		global $WIN32;
		$ldap_settings['dn_lookup_use_com'] = ($WIN32 != false && empty($config['cfg_LDAP_domains_info']) == 0 && !$config['cfg_LDAP_bind_by_dn'] && class_exists('COM', false));
		$ldap_settings['sys_charset'] = $config['cfg_LDAP_sys_charset'];

		$ldap_settings['fields_map'] = $config['cfg_LDAP_only_fields'];
		$ldap_settings["map_profile_photo"] = null;
		$ldap_settings["map_cla_orgchart"] = null;
		$ldap_settings["map_groups"] = 0;
		$ldap_settings["map_groups_expression"] = null;
		$ldap_settings["sync_interval"] = "0";
		$ldap_settings["initial_sync_time"] = "";
		
		// Added October 2018 by tezmc. this change won't affect migrations already run.
		// check that sync interval matches one we support and fall back if needed.
		$tmp_sync_interval = $config['cfg_LDAP_auto_update_interval'];		
		if ($tmp_sync_interval)
		{
			$supported_intervals = [0, 60, 720, 1440, 10080];
			if (!in_array(intval($tmp_sync_interval), $supported_intervals))
				$ldap_settings["sync_interval"] = "1440";
			else
				$ldap_settings["sync_interval"] = "$tmp_sync_interval";
			$ldap_settings["initial_sync_time"] = "4:00";
		}
		
		$ldap_settings["type"] = "1";
		$new_directories['cfgmigrated'] = array(
			"type" => "ldap",
			"options" => $ldap_settings
		);
	}


	$config->Set('cfg_users_directories', $new_directories);
	$config->Save();
}

DB_UPDATE_FILE
);


$migrations->Run('05_ext_type_length.php', <<<'DB_UPDATE_FILE'
<?php
/** @var \Claromentis\Setup\SetupFacade $migrations */
$db = $migrations->GetDb();

$config = $migrations->GetConfigFull();

$directories = $config->Get('cfg_users_directories');
if (!empty($directories))
{
	$random_string_func = function ()
	{
		$length = 20;
		$characters = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
		$characters = str_shuffle($characters);
		$chars_length = strlen($characters)-1;
		$random_string = str_repeat(' ', $length); // preallocate string
		for ($i=0; $i < $length; $i++)
		{
			$random_string[$i] = $characters[mt_rand(0, $chars_length)];
		}
		return $random_string;
	};

	$new_directories = [];
	foreach ($directories as $key=>$value)
	{
		$new_key = $random_string_func();
		$new_directories[$new_key] = $value;
	}

	$config->Set('cfg_users_directories', $new_directories);
	$config->Save();

	reset($new_directories);
	$ldap_key = key($new_directories);

	if ($ldap_key != '')
	{
		$db->query("UPDATE users SET ext_type=str:new_key WHERE ext_type='ldap'", $ldap_key);
	}
}

DB_UPDATE_FILE
);


//===========================================================================================
$migrations->SetVersion('29.02');
