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



$migrations->Run('01_news_channel_owner.php', <<<'DB_UPDATE_FILE'
<?

$db->AddColumn('news_channel', 'owner', "INT NOT_NULL DEFAULT 0");

?>
DB_UPDATE_FILE
);


$migrations->Run('02_docs_wf_notify_owner.php', <<<'DB_UPDATE_FILE'
<?php

$db->AddColumn('wf_states', 'owner_notify', 'INT');

?>
DB_UPDATE_FILE
);


$migrations->Run('03_field_type_date_picker.php', <<<'DB_UPDATE_FILE'
<?php

// change "short string" with "date" string format to dedicated "date picker" type
$db->query("UPDATE fb_field SET type=int:type_date_picker WHERE type=int:type_short_string AND string_format=int:string_format_date", 20, 1, 3);

?>
DB_UPDATE_FILE
);


$migrations->Run('04_form_data_int.php', <<<'DB_UPDATE_FILE'
<?php

$db->AddColumn('fb_field_data', 'value_int', 'INT DEFAULT 0');
$db->query("UPDATE fb_field_data SET value_int=0");

?>
DB_UPDATE_FILE
);


$migrations->Run('05_ic_drop_unused.php', <<<'DB_UPDATE_FILE'
<?php

$db->DropColumn('fb_field', 'width_input_px');
$db->DropColumn('fb_field', 'width_label_px');

$db->DropColumn('fb_section', 'style_descr');

?>
DB_UPDATE_FILE
);


$migrations->Run('06_ic_data_convert.php', <<<'DB_UPDATE_FILE'
<?php

// convert "date (3 dropdowns)" default value
$res = $db->query("SELECT id, default_value, items FROM fb_field WHERE type=int:type_date", 11 /* TYPE_DATE (3 dropdowns) */);
while (list($fid, $def, $items) = $res->fetchRow())
{
	$vals = explode("\n", $def, 5);
	$items_vals = explode("\n", $items, 2);
	if (count($vals) == 5)
	{
		$items = $vals[0]."\n".$vals[1];
		if (is_numeric($vals[2])) $vals[2] = sprintf("%4d", $vals[2]);
		if (is_numeric($vals[3])) $vals[3] = sprintf("%4d", $vals[3]);
		if (is_numeric($vals[4])) $vals[4] = sprintf("%4d", $vals[4]);
		$default = $vals[2].$vals[3].$vals[4];
	} elseif (count($vals) == 1 && count($items_vals) == 2)
	{
		// this date is already in the new format
		continue;
	} else
	{
		$items = '';
		$default = '';
	}
	$db->query("UPDATE fb_field SET default_value=str:def, items=str:items WHERE id=int:fid", $default, $items, $fid);
}


// move integer values to value_int
// start from moving everything that looks like a number
if ($db->type() == 'mysql')
	$db->query("UPDATE IGNORE fb_field_data SET value_int=value WHERE value REGEXP '^-{0,1}[0-9]+$'");
else
	$db->query("UPDATE fb_field_data SET value_int=CAST(value AS INT) WHERE ISNUMERIC(value + '.0e0')=1");


/* TYPE_CHECKBOX */
$res = $db->query("SELECT DISTINCT sym_name FROM fb_field WHERE type=7");
if ($res->numRows() > 0)
{
	$names = $res->fetchAllValues();

	if ($db->type() == 'mysql')
		$db->query("UPDATE fb_field_data SET value_int=IF(value IS NULL OR value='0', 0, 1) WHERE field_sym_name IN in:str:symname", $names);
	else
		$db->query("UPDATE fb_field_data SET value_int=(CASE WHEN value IS NULL OR value='0' THEN 0 ELSE 1 END) WHERE field_sym_name IN in:str:symname", $names);
}


/* TYPE_DATE and TYPE_DATE_PICKER*/
$res = $db->query("SELECT DISTINCT sym_name FROM fb_field WHERE type IN (11, 20)");
if ($res->numRows() > 0)
{
	$names = $res->fetchAllValues();

	$date_res = $db->query("SELECT form_data_id, value_int FROM fb_field_data WHERE field_sym_name IN in:str:symname AND value_int>30000000", $names);
	//if ($date_res->numRows() > 0)
	//	echo $date_res->numRows()." dates with symname '$sym_name' are stored in datetime format. Will be converted now<br>\n";

	while (list($fd_id, $date_val)= $date_res->fetchRow())
	{
		if (strlen($date_val) == 14)
		{
			$date_obj = new Date($date_val);
			$date_val = $date_obj->getDate(DATE_FORMAT_DATE_DAY);

			$db->query("UPDATE fb_field_data SET value_int=int:val WHERE form_data_id=int:fdid AND field_sym_name=str:symname", $date_val, $fd_id, $sym_name);
		}
	}
}

/*TYPE_SHORT_STRING + STRING_FORMAT_NUMERIC */
$res = $db->query("SELECT DISTINCT sym_name, type FROM fb_field WHERE type=1 AND string_format=1");
if ($res->numRows() > 0)
{
	$names = $res->fetchAllValues();
	$date_res = $db->query("UPDATE fb_field_data SET value_int=NULL WHERE field_sym_name IN in:str:symname AND value_int=0 AND value IS NULL", $names);
}

?>
DB_UPDATE_FILE
);


$migrations->Run('07_ic_label_field.php', <<<'DB_UPDATE_FILE'
<?php

// All "Label" field types should get "separate cells" flag and no "on top"
$res = $db->query("UPDATE fb_field SET two_cells=1, on_top=0 WHERE type=int:type_label", 12 /* TYPE_LABEL */);

?>
DB_UPDATE_FILE
);


$migrations->Run('08_autologon_table.php', <<<'DB_UPDATE_FILE'
<?php

$table_descr = array(
	'user_id'       => "INT NOT_NULL",
	'cookie'        => "VARCHAR(255) NULL",
	'date_created'  => "INT NOT_NULL", // unix timestamp
	'date_used'     => "INT NOT_NULL", // unix timestamp
	'ip_created'    => "VARCHAR(255) NULL",
	'ip_used'       => "VARCHAR(255) NULL",
);

$db->CreateTable('autologon_cookie', $table_descr, true);

?>
DB_UPDATE_FILE
);


$migrations->Run('09_secure_password_hash.php', <<<'DB_UPDATE_FILE'
<?php

if ($db->type() == 'mssql')
{
	$db->query("UPDATE users SET last_time_login=0 WHERE ISNUMERIC(last_time_login + '.0e0')<>1 OR last_time_login IS NULL");
	$db->query("UPDATE users SET last_time_used=0 WHERE ISNUMERIC(last_time_used + '.0e0')<>1 OR last_time_used IS NULL");

	$last_times = 'CAST(last_time_login AS INT), CAST(last_time_used AS INT)';
} else
{
	$db->query("UPDATE users SET last_time_login=0 WHERE NOT last_time_login REGEXP '^-{0,1}[0-9]+$' OR last_time_login IS NULL");
	$db->query("UPDATE users SET last_time_used=0 WHERE NOT last_time_used REGEXP '^-{0,1}[0-9]+$' OR last_time_used IS NULL");

	$last_times = 'last_time_login, last_time_used';
}


$old_users_count = $db->query("SELECT COUNT(1) FROM users");

$db->RenameTable('users', 'users_old');

// users
$table_descr = array(
	'id'	=>	"IDENTITY",
	'intranetuser'	=>	"VARCHAR(3) NULL DEFAULT 'no'",
	'username'	=>	"VARCHAR(100) NULL",
	'password'	=>	"VARCHAR(200) NULL",
	'a1_hash'	=>	"VARCHAR(255) NULL",
	'firstname'	=>	"VARCHAR(200) NULL",
	'surname'	=>	"VARCHAR(200) NULL",
	'company'	=>	"VARCHAR(200) NULL",
	'emailad'	=>	"VARCHAR(200) NULL",
	'ex_area_id'	=>	"INT NULL DEFAULT 0",
	'job_title'	=>	"VARCHAR(200) NULL",
	'last_time_login'	=>	"INT NULL",
	'last_time_used'	=>	"INT NULL",
	'user_code'	=>	"VARCHAR(200) NULL",
	'notify_on_checkin'	=>	"BOOL NULL DEFAULT 1",
	'notification_method'	=>	"BOOL NULL DEFAULT 1",
	'language'	=>	"VARCHAR(3) NULL DEFAULT 'en'",
	'easy_find_num'	=>	"INT NULL DEFAULT 10",
	'whats_new_mask'	=>	"INT NOT_NULL DEFAULT 12386",
	'skin'	=>	"VARCHAR(30) NULL DEFAULT 'default'",
	'default_skin'	=>	"INT NOT_NULL DEFAULT 0",
	'password_policy'	=>	"VARCHAR(20) NULL DEFAULT '0_0_30_5'",
	'pp_change_next_time'	=>	"BOOL NOT_NULL DEFAULT 0",
	'pp_default'	=>	"BOOL NOT_NULL DEFAULT 1",
	'password_last_change'	=>	"INT NOT_NULL DEFAULT 0",
	'ldap_guid'	=>	"VARCHAR(255) NULL",
	'login_frozen_time'	=>	"INT_DATE NOT_NULL DEFAULT 0",
	'login_attempts'	=>	"INT NOT_NULL DEFAULT 0",
);

$db->CreateTable('users', $table_descr, true);

if ($db->type() == 'mssql')
	$db->query("SET IDENTITY_INSERT users ON");

$db->query("INSERT INTO users (id, intranetuser, username, password, a1_hash, firstname, surname, company,
		emailad, ex_area_id, job_title, last_time_login, last_time_used,
		notify_on_checkin, notification_method, skin, language, easy_find_num, whats_new_mask, default_skin, user_code,
		password_policy, pp_change_next_time, pp_default, password_last_change, ldap_guid, login_frozen_time, login_attempts)
	SELECT id, intranetuser, username, password, a1_hash, firstname, surname, company,
		emailad, ex_area_id, job_title, $last_times,
		notify_on_checkin, notification_method, skin, language, easy_find_num, whats_new_mask, default_skin, user_code,
		password_policy, pp_change_next_time, pp_default, password_last_change, ldap_guid, login_frozen_time, login_attempts
	FROM users_old");

if ($db->type() == 'mssql')
	$db->query("SET IDENTITY_INSERT users OFF");

$db->query("UPDATE users SET last_time_login=NULL WHERE last_time_login=0");
$db->query("UPDATE users SET last_time_used=NULL WHERE last_time_used=0");

$new_users_count = $db->query("SELECT COUNT(1) FROM users");

if ($new_users_count == $old_users_count)
	$db->DropTable('users_old');
else
	echo "=============================================\nERROR! Number of users has changed after migration. The backup table 'users_old' is left for analysis.\n\n";
?>
DB_UPDATE_FILE
);


$migrations->Run('10_im_recipients_index.php', <<<'DB_UPDATE_FILE'
<?php

$db->DropIndex('im_recipients', 'im_recipients_del_read');
$db->CreateIndex('im_recipients', 'im_recipients_u_d_r', 'userid', 'deleted', 'readyet');

?>
DB_UPDATE_FILE
);


$migrations->Run('11_ic_users_migration.php', <<<'DB_UPDATE_FILE'
<?php

// get all "users list" fields
$res = $db->query("SELECT id, items FROM fb_field WHERE type IN in:int:type", array(13, 15)); // FBField::TYPE_USERLIST, FBField::TYPE_MULTIPLE_USERLIST

while (list($field_id, $options_str) = $res->fetchRow())
{
	$options = @unserialize($options_str);

	// if options are not permissions yet, load and re-save them
	if (!is_array($options_str))
	{
		$permisssions = new Permissions(PERM_CLASS_FORM_FIELD_USERLIST, $field_id);
		$permisssions->Load();
		$options_str = $permisssions->Serialize();
		$db->query("UPDATE fb_field SET items=clob:items WHERE id=int:f_id", $options_str, $field_id);
	}
}

// delete all permissions from the database
$db->query("DELETE FROM permissions WHERE class_id=int:class", PERM_CLASS_FORM_FIELD_USERLIST);


?>
DB_UPDATE_FILE
);


$migrations->Run('12_ic_notification_settings.php', <<<'DB_UPDATE_FILE'
<?

$db->AddColumn('hd_project_notif_settings', 'form_field', "VARCHAR(80) NULL");
$db->AddColumn('hd_project_notif_settings', 'email_addr', "VARCHAR(512) NULL");

?>
DB_UPDATE_FILE
);


$migrations->Run('13_hd_field_group_name.php', <<<'DB_UPDATE_FILE'
<?php

$db->AddColumn('hd_fields_group', 'name', "VARCHAR(100)");

$res = $db->query("SELECT id FROM hd_fields_group");
while (list($id) = $res->fetchRow())
{
	$db->query("UPDATE hd_fields_group SET name=str:name WHERE id=int:id", 'group-'.$id, $id);
}

?>
DB_UPDATE_FILE
);


$migrations->Run('14_ic_triggers.php', <<<'DB_UPDATE_FILE'
<?

$table_descr = array(
	'id'	=>	"IDENTITY",
	'pos'	=>	"INT NOT_NULL DEFAULT 0",
	'project_id'	=>	"INT NOT_NULL DEFAULT 0",
	'name'	=>	"VARCHAR(255) NULL",
);

$db->CreateTable('hd_trigger', $table_descr, true);


$table_descr = array(
	'trigger_id'    => "INT NOT_NULL DEFAULT 0",
	'rule_type'     => "INT NOT_NULL",
	'negative'      => "BOOL NOT_NULL DEFAULT 0",
	'field_symname'	=> "VARCHAR(80) NULL",
	'value'         => "VARCHAR_MAX NULL",
	'predicat'      => "INT NOT_NULL DEFAULT 0",
);

$db->CreateTable('hd_trigger_rule', $table_descr, true);

?>
DB_UPDATE_FILE
);


$migrations->Run('15_ic_convert_to_triggers.php', <<<'DB_UPDATE_FILE'
<?

$res = $db->query("SELECT id, project_id, rule_id, rule_was_false FROM hd_project_notif_settings");

$created_triggers = array();
while ($arr = $res->fetchArray())
{
	$cache_key = $arr['project_id'].'_'.$arr['rule_id'].'_'.$arr['rule_was_false'];
	if (!$created_triggers[$cache_key])
	{
		list($rule_name) = $db->query_row("SELECT name FROM hd_fields_rule WHERE id=int:id", $arr['rule_id']);
		if (!strlen($rule_name))
			$rule_name = 'Rule #'.$arr['rule_id'];

		if ($rule_name === 'Default (Being reported)')
		{
			$name = "New issue is reported";

			$query = new QueryInsert('hd_trigger', array('str(255):name' => $name, 'int:project_id' => $arr['project_id']));
			$db->query($query);

			$trigger_id = $db->insertId();
			$created_triggers[$cache_key] = $trigger_id;

			$query = new QueryInsert('hd_trigger_rule', array(
			                    'int:trigger_id' => $trigger_id,
			                    'int:rule_type' => 1,  //IC_TRIGGER_REPORTED,
			                    ));
			$db->query($query);
		} else
		{
			if ($arr['rule_was_false'])
				$name = "Changed to \"".$rule_name.'"';
			else
				$name = "Match \"".$rule_name.'"';

			$query = new QueryInsert('hd_trigger', array('str(255):name' => $name, 'int:project_id' => $arr['project_id']));
			$db->query($query);

			$trigger_id = $db->insertId();
			$created_triggers[$cache_key] = $trigger_id;

			$query = new QueryInsert('hd_trigger_rule', array(
			                    'int:trigger_id' => $trigger_id,
			                    'int:rule_type' => 8,  //IC_TRIGGER_RULE_NEW,
			                    'str:value' => $arr['rule_id']));
			$db->query($query);

			if ($arr['rule_was_false'])
			{
				$query = new QueryInsert('hd_trigger_rule', array(
				                                                 'int:trigger_id' => $trigger_id,
				                                                 'int:rule_type' => 6, //IC_TRIGGER_RULE_OLD,
				                                                 'str:value' => $arr['rule_id'],
				                                                 'int:negative' => 1));
				$db->query($query);
			}
		}
	}

	$trigger_id = $created_triggers[$cache_key];
	$db->query("UPDATE hd_project_notif_settings SET rule_id=int:new_id WHERE id=int:id", $trigger_id, $arr['id']);
}



$res = $db->query("SELECT pos, project_id, rule_id, field_symname FROM hd_project_assigning");
while ($arr = $res->fetchArray())
{
	$cache_key = $arr['project_id'].'_'.$arr['rule_id'].'_0';
	if (!$created_triggers[$cache_key])
	{
		list($rule_name) = $db->query_row("SELECT name FROM hd_fields_rule WHERE id=int:id", $arr['rule_id']);
		if (!strlen($rule_name))
			$rule_name = 'Rule #'.$arr['rule_id'];

		if ($rule_name === 'Default (Being reported)')
		{
			$name = "New issue is reported";

			$query = new QueryInsert('hd_trigger', array('str(255):name' => $name, 'int:project_id' => $arr['project_id']));
			$db->query($query);

			$trigger_id = $db->insertId();
			$created_triggers[$cache_key] = $trigger_id;

			$query = new QueryInsert('hd_trigger_rule', array(
			                    'int:trigger_id' => $trigger_id,
			                    'int:rule_type' => 1,  //IC_TRIGGER_REPORTED,
			                    ));
			$db->query($query);
		} else
		{
			list($rule_name) = $db->query_row("SELECT name FROM hd_fields_rule WHERE id=int:id", $arr['rule_id']);
			if (!strlen($rule_name))
				$rule_name = 'Rule #'.$arr['rule_id'];

			$name = "Match \"".$rule_name.'"';

			$query = new QueryInsert('hd_trigger', array('str(255):name' => $name, 'int:project_id' => $arr['project_id']));
			$db->query($query);

			$trigger_id = $db->insertId();
			$created_triggers[$cache_key] = $trigger_id;

			$query = new QueryInsert('hd_trigger_rule', array(
			                                                 'int:trigger_id' => $trigger_id,
			                                                 'int:rule_type' => 8,  //IC_TRIGGER_RULE_NEW,
			                                                 'str:value' => $arr['rule_id']));
			$db->query($query);
		}
	}

	$trigger_id = $created_triggers[$cache_key];
	$db->query("UPDATE hd_project_assigning SET rule_id=int:new_id WHERE project_id=int:pid AND pos=int:pos AND field_symname eq:str:field_symname", $trigger_id, $arr['project_id'], $arr['pos'], $arr['field_symname']);
}

?>
DB_UPDATE_FILE
);


$migrations->Run('16_ic_rules_drop_unused.php', <<<'DB_UPDATE_FILE'
<?

$db->DropColumn('hd_fields_rule', 'for_assigning');
$db->DropColumn('hd_fields_rule', 'for_notifications');

?>
DB_UPDATE_FILE
);


$migrations->Run('17_ic_search_fields.php', <<<'DB_UPDATE_FILE'
<?

$db->AddColumn("hd_project", "search_form_fields", "VARCHAR(2000) NULL");
$db->AddColumn("hd_project", "search_columns", "INT DEFAULT 2");

$db->query("UPDATE hd_project SET search_columns=2");

$res = $db->query("SELECT id FROM hd_project");

$searchable_types = array(7 /*FBField::TYPE_CHECKBOX*/, 8 /*FBField::TYPE_RADIO*/, 9 /*FBField::TYPE_SELECT*/, 13 /*FBField::TYPE_USERLIST*/);
while (list($project_id) = $res->fetchRow())
{
	list($form_id) = $db->query_row("SELECT id FROM fb_form WHERE category_id=int:project_id AND id=primary_version_id", $project_id);

	$r = $db->query("SELECT sym_name FROM fb_field WHERE form_id=int:form_id AND type IN in:int:types AND is_searchable=1 ORDER BY position", $form_id, $searchable_types);
	$fields = $r->fetchAllValues();

	$fields = array_merge(array(
                           '_created_',
                           '_last_modified_',
                           '_reporter_',
                           '_status_',
                           '_assigned_',
                           '_traffic_light_',
	                      ), $fields);
	$fields = join("\n", $fields);
	$db->query("UPDATE hd_project SET search_form_fields=str(2000):fields_str WHERE id=int:project_id", $fields, $project_id);
}

$db->DropColumn("fb_field", "is_searchable");
?>

DB_UPDATE_FILE
);


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