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



$migrations->Run('01_ic_issues_replace_invalid_entries.php', <<<'DB_UPDATE_FILE'
<?php
$db = $migrations->GetDb();

// Some values for fields with multiple options were saved as 'Array' due to a bug. This causes validation problems when trying to
// save a correct value instead.

// Find all potentially bad fields
$result = $db->query('SELECT form_data_id, field_sym_name FROM fb_field_data WHERE value = str:array', 'Array');
while ($arr = $result->fetchArray())
{
	// Find which field type this is
	list($field_type) = $db->query_row('SELECT type FROM fb_field WHERE form_id = (SELECT form_id FROM fb_form_data WHERE id = int:form_data_id) AND sym_name = str:sym_name', (int)$arr['form_data_id'], $arr['field_sym_name']);

	// Is it one of those affected?
	$filed_type = (int)$field_type;
	if ((($field_type == FBField::TYPE_MULTIPLE_SELECT)) ||
			(($field_type == FBField::TYPE_MULTIPLE_USERLIST)) ||
			(($field_type == FBField::TYPE_MULTIPLE_CHECKBOXES)))
	{
		$db->unsafe_query = true;
		$db->query('UPDATE fb_field_data SET value = NULL WHERE form_data_id = int:form_data_id AND field_sym_name = str:sym_name', (int)$arr['form_data_id'], $arr['field_sym_name']);
	}
}
DB_UPDATE_FILE
);


$migrations->Run('02_ic_issues_replace_invalid_entries_2.php', <<<'DB_UPDATE_FILE'
<?php
$db = $migrations->GetDb();

// Some values for fields with multiple options default values saved as 'Array' due to a bug.

$multiple_types = array(10, 15, 22);  // multiple selects, multiple checkboxes and multiple users
$db->unsafe_query = true;
$db->query('UPDATE fb_field SET default_value = NULL WHERE default_value LIKE like:array AND type IN in:int:types', 'Array', $multiple_types);
DB_UPDATE_FILE
);


$migrations->Run('03_move_logs.php', <<<'DB_UPDATE_FILE'
<?php
/** @var $migrations \Claromentis\Setup\SetupFacade */

$data_dir = $migrations->GetDataDir();
$old_logs_dir = $data_dir.'/logs';

$audit_archive_dir = $data_dir.'/audit_archive';
if (!is_dir($audit_archive_dir))
	mkdir($audit_archive_dir, 0777);

$local_data_dir = $migrations->GetLocalDataDir();
$new_logs_dir = $local_data_dir.'/logs';
if (!is_dir($new_logs_dir))
	mkdir($new_logs_dir, 0777);

$audit_files = glob($old_logs_dir.'/log-*.csv');
foreach ($audit_files as $file)
{
	$new_location = $audit_archive_dir . '/' . basename($file);
	if (!rename($file, $new_location))
	{
		if (copy($file, $new_location))
			unlink($file);
	}
}

if (realpath($old_logs_dir) !== realpath($new_logs_dir))
{
	$system_logs = glob($old_logs_dir . '/*.log');
	foreach ($system_logs as $file)
	{
		$new_location = $new_logs_dir . '/' . basename($file);
		if (!rename($file, $new_location))
		{
			if (copy($file, $new_location))
				unlink($file);
		}
	}

	// if this directory is not empty, deleting it will fail, which is exactly what we need
	@rmdir($old_logs_dir);
}

DB_UPDATE_FILE
);


$migrations->Run('04_ic_forms_duplicate_orders.php', <<<'DB_UPDATE_FILE'
<?php
$db = $migrations->GetDb();

/*
 * The IC form CSV import checked that it was preserving a unique order to imported fields/sections but did not check fields from the database
 * that were not mentioned in the CSV file. So some duplicate orders may have been created if this feature was used. This shouldn't effect using
 * the forms but the admin form page can't edit them or even see some of them in 'show fields as a table' mode.
 *
 * This script evaluates the field order of all forms modified after the bug was introduced to resolve a unique order for their fields if needed.
 * The existing field sequence is preserved where it is unique already. Duplicate orders end up being in ID order but still within the
 * original sequence on the form.
  */

$start_date = new DateDay(20150130);  // First code committed with bug

$results = $db->query('SELECT id FROM fb_form WHERE last_modified >= int:start_date', $start_date->getDate(DATE_FORMAT_UNIXTIME));
while ($arr = $results->fetchArray())
{
	$form_id = (int)$arr['id'];

	// Fields. Must be in order
	$result = $db->query('SELECT id from fb_field where form_id = int:form_id ORDER BY position, id ASC', $form_id);
	while ($arr = $result->fetchArray())
	{
		// The position may increase during the loop so has to be looked up each time
		list($position) = $db->query_row('SELECT position from fb_field where id = int:id', $arr['id']);
		AdjustExistingFieldPositions($db, $arr['id'], $position, $form_id);
	}

	// Sections. Must be in order
	$result = $db->query('SELECT id from fb_section where form_id = int:form_id ORDER BY position, id ASC', $form_id);
	while ($arr = $result->fetchArray())
	{
		// The position may increase during the loop so has to be looked up each time
		list($position) = $db->query_row('SELECT position from fb_section where id = int:id', $arr['id']);
		AdjustExistingSectionPositions($db, $arr['id'], $position, $form_id);
	}
}

function AdjustExistingFieldPositions($db, $field_id, $order, $form_id)
{
	$count = 0;
	list($field_count) = $db->query_row('SELECT count(id) FROM fb_field WHERE position = int:position AND form_id = int:form_id', $order, $form_id);
	list($section_count) = $db->query_row('SELECT count(id) FROM fb_section WHERE position = int:position AND form_id = int:form_id', $order, $form_id);
	$count += $field_count;
	$count += $section_count;

	// A count of 1 means nothing conflicted
	if ($count <= 1)
		return;

	// Increment the order of all fields and sections of this order or higher, except the one being looked at
	$db->DisableTokenCheck();
	$db->query('UPDATE fb_field SET position = position + 1 WHERE position >= int:position AND id <> int:id AND form_id = int:form_id', $order, $field_id, $form_id);
	$db->query('UPDATE fb_section SET position = position + 1 WHERE position >= int:position AND form_id = int:form_id', $order, $form_id);
	$db->EnableTokenCheck();
}

function AdjustExistingSectionPositions($db, $section_id, $order, $form_id)
{
	$count = 0;
	list($field_count) = $db->query_row('SELECT count(id) FROM fb_field WHERE position = int:position AND form_id = int:form_id', $order, $form_id);
	list($section_count) = $db->query_row('SELECT count(id) FROM fb_section WHERE position = int:position AND form_id = int:form_id', $order, $form_id);
	$count += $field_count;
	$count += $section_count;

	// A count of 1 means nothing conflicted
	if ($count <= 1)
		return;

	// Increment the order of all fields and sections of this order or higher, except the one being looked at
	$db->DisableTokenCheck();
	$db->query('UPDATE fb_field SET position = position + 1 WHERE position >= int:position AND form_id = int:form_id', $order, $form_id);
	$db->query('UPDATE fb_section SET position = position + 1 WHERE position >= int:position AND id <> int:id AND form_id = int:form_id', $order, $section_id, $form_id);
	$db->EnableTokenCheck();
}
DB_UPDATE_FILE
);


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