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



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

$converter = function($app_id)
{
    global $cfg_cla_plugins;
    $apps = array(
        0 => 0,
        1 => 'forum',
        2 => 'news',
        3 => 'publish',
        4 => 'user',
        5 => 'documents',
        6 => 'calendar',
        7 => 'people',
        8 => 'admin',
        9 => 'system',
        10 => 'webmail',
        11 => 'courses',
        12 => 'holidays',
        13 => 'search',
        14 => 'rooms',
        15 => 'surveys',
        16 => 'blog'
    );
    $all_apps = array_merge($apps, $cfg_cla_plugins);
    if (array_key_exists($app_id, $all_apps))
    {
        if (strpos($all_apps[$app_id], '\\'))
        {
            $name = array_slice(explode('\\', get_class($class)), 0, -1);
        }
        else
        {
            $pattern = '/(?<=[a-z])(?![a-z])/';
            $matches = preg_split($pattern, $all_apps[$app_id]);
            $name = $matches[0];
        }
        return strtolower($name);
    }
    else
        return $app_id;
};

// imessages
$db->DropIndex('imessages', 'imessages_fromid');
@$db->DropIndex('imessages', 'fromid');
$db->AlterColumn('imessages', 'fromid', 'from_id');
$db->AlterColumn('imessages', 'messagetext', 'message_text');
$db->CreateIndex('imessages', 'imessages_from_id', 'from_id');

// im_notification_methods
$db->DropIndex('im_notification_methods', 'im_notif_methods_app_type');
$db->DropPrimaryKey('im_notification_methods');
$db->AlterColumn('im_notification_methods', 'app_type', 'app_type', 'VARCHAR(100) DEFAULT NULL');
$db->CreateIndex('im_notification_methods', 'im_notif_methods_app_type', 'app_type');

$res = $db->query('SELECT app_type FROM im_notification_methods GROUP BY app_type');
while ($row = $res->fetchArray())
{
    $qu = new QueryUpdate('im_notification_methods', 'app_type eq:str:app_int', array('str:app_type' => $converter((int)$row['app_type'])));
    $qu->Bind('app_int', $row['app_type']);
    $db->query($qu);
}

//im_recipients
$db->DropPrimaryKey('im_recipients');
@$db->DropIndex('im_recipients', 'imsgid');
$db->DropIndex('im_recipients', 'im_recipients_u_d_r');
$db->AlterColumn('im_recipients', 'imsgid', 'imsg_id');
$db->AlterColumn('im_recipients', 'userid', 'user_id');
$db->AlterColumn('im_recipients', 'readyet', 'read_yet');
$db->CreatePrimaryKey('im_recipients', 'imsg_id', 'user_id');
$db->CreateIndex('im_recipients', 'im_recipients_u_d_r', 'user_id', 'deleted', 'read_yet');

DB_UPDATE_FILE
);


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

$db->AddColumn('hpl_days', 'is_system_day', "BOOL NOT_NULL DEFAULT 0");

$db->Query("UPDATE hpl_days SET is_system_day=1 WHERE id IN (1,2,3,4)");

DB_UPDATE_FILE
);


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

// Makes sure that the system days are reset to the values they were initially set in the init.php file.
$db->query("UPDATE hpl_days SET hpl_char=NULL, can_decline=0, can_half_day=0, limit_action=0, notify_to=0, stat=0, is_system_day=1 WHERE id=1");
$db->query("UPDATE hpl_days SET hpl_char=NULL, can_decline=0 , can_half_day=0, limit_action=1, notify_to=0, stat=0, is_system_day=1 WHERE id=2");
$db->query("UPDATE hpl_days SET hpl_char=NULL, can_decline=0 , can_half_day=1, limit_action=0, notify_to=0, stat=0, is_system_day=1 WHERE id=3");
$db->query("UPDATE hpl_days SET hpl_char=NULL, can_decline=0 , can_half_day=0, limit_action=1, notify_to=0, stat=0, is_system_day=1 WHERE id=4");




DB_UPDATE_FILE
);


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

// im_notification_methods, fix again
$db->DropIndex('im_notification_methods', 'im_notif_methods_app_type');
$db->DropPrimaryKey('im_notification_methods');
$db->AlterColumn('im_notification_methods', 'app_type', 'app_type', 'VARCHAR(100) NOT_NULL default \'system\'');
$db->CreateIndex('im_notification_methods', 'im_notif_methods_app_type', 'app_type');
$db->CreateIndex('im_notification_methods', 'im_notif_methods_u_app_type', 'user_id', 'app_type');
$db->CreatePrimaryKey('im_notification_methods', 'user_id', 'app_type');
DB_UPDATE_FILE
);


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

$res = $db->query("SELECT groupid,parent_id,man1_id,man2_id, state FROM hpl_groups");
$hpl_groups = array();
$reset_managers = array('man1_id' => array(), 'man2_id' => array());

while ($group = $res->fetchArray())
{   // Collects all the results into an array to be checked later.
    $hpl_groups[$group['groupid']] = $group;
}

// Finds all the manager ids that need to be reset.
foreach ($hpl_groups as $group)
{
    if (needs_reseting_man_id('man1_id', $group, $hpl_groups))
    {
        $reset_managers['man1_id'][] = $group['groupid'];
    }

    if (needs_reseting_man_id('man2_id', $group, $hpl_groups))
    {
        $reset_managers['man2_id'][] = $group['groupid'];
    }
}

// Checks if the given manager's ids need to be reset (if it has the -1
// id set to managers when it does not inherit from the manager with id 1).
function needs_reseting_man_id($manager, $group, $hpl_groups)
{
    if ($group[$manager] != -1)
    {   // only interested on the old default -1 flag. Anything other then
        // -1 shouldn't be changed.
        return false;
    }

    if (is_zone($group))
    {   // Zones should have it always set to 0. I am checking for the
        // -1 id here, just in case, but this shouldn't really be necessary.
        return ($group[$manager] == -1);
    }

    // Finds the parent of the current group.
    $parent_id = $group['parent_id'];
    $parent = $hpl_groups[$parent_id];

    if (is_zone($parent))
    {   // If the parent is a zone then this group doesn't inherit from the
        // parent and, therefore. if the manager is -1 it should be chamged.
        return ($group[$manager] == -1);
    }

    // Follows the parent line upwards until finding either a zone or the
    // main admin (1) set as a manager.
    while ($group['parent_id'] != 0)
    {
        // Moves to the parent.
        $parent_id = $group['parent_id'];
        $group = $hpl_groups[$parent_id];

        if ($group[$manager] == 1)
        {   // Found the main admin set as a manager.
            return false;
        }
    }
    return true;
}

// Checks if the given group is a zone.
function is_zone($group)
{
    if ($group['parent_id'] == 0 && $group['state'] == 1)
    {
        return true;
    }
    return false;
}

// Resets the ids of the managers that need reseting, if any.
if (!empty($reset_managers['man1_id']))
{
    $db->query("UPDATE hpl_groups SET man1_id=0 WHERE groupid IN in:int:change_managers AND man1_id=-1", $reset_managers['man1_id']);
}

// Resets the ids of the substitutes that need reseting, if any.
if (!empty($reset_managers['man2_id']))
{
    $db->query("UPDATE hpl_groups SET man2_id=0 WHERE groupid IN in:int:change_managers AND man2_id=-1", $reset_managers['man2_id']);
}
DB_UPDATE_FILE
);


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

$db->AddColumn('hd_project', 'issues_primary_field', "VARCHAR(80) DEFAULT NULL");

DB_UPDATE_FILE
);


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

$db->AddColumn('hd_project_statuses', 'archive_idle_time', "INT NOT_NULL DEFAULT 0");

$db->Query("UPDATE hd_project_statuses SET archive_idle_time=(SELECT archive_idle_time FROM hd_project WHERE id=hd_project_statuses.project_id) WHERE is_archived=1");

$db->DropColumn('hd_project', 'archive_idle_time');
$db->DropColumn('hd_project', 'archive_issues');
DB_UPDATE_FILE
);


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

$project_icon = 'icon-notes_2';
$project_icon_color = '#0088CC';

// Create the columns for the icon and icon color.
$db->AddColumn('hd_project', 'project_icon', "VARCHAR(40) DEFAULT NULL");
$db->AddColumn('hd_project', 'project_icon_color', "VARCHAR(7) DEFAULT NULL");

// Sets default values for the newly created columns.
$db->query("UPDATE hd_project SET project_icon=str:project_icon WHERE project_icon IS NULL", $project_icon);
$db->query("UPDATE hd_project SET project_icon_color=str:project_icon_color WHERE project_icon_color IS NULL", $project_icon_color);

DB_UPDATE_FILE
);


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

$db->AddColumn('pub_element', 'body', 'CLOB');

/**
 * TAG_TYPE_IMAGE = 1
 * TAG_TYPE_FILE = 100
 */
$elements = $db->query('SELECT id FROM pub_element WHERE type NOT IN (1,100)');

$data_location = isset($GLOBALS['publishDataDir']) ? $GLOBALS['publishDataDir'] : $migrations->GetDataDir().'/publish_files';
while (list($id) = $elements->fetchRow())
{
	$file = $data_location."/$id.dat";
	$body = file_get_contents($file);
	$qu = new \Claromentis\Core\DAL\QueryUpdate('pub_element', 'id=int:id', array('clob:body' => $body));
	$qu->Bind('id', $id);
	$db->query($qu);
	@unlink($file);
}

DB_UPDATE_FILE
);


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

$db->DropColumn('pub_category', 'files_location');
$db->DropColumn('pub_category', 'server');
$db->DropColumn('pub_category', 'static');
$db->DropColumn('pub_category', 'folder_type');

DB_UPDATE_FILE
);


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

$db->AddColumn('hd_project', 'thankyou_page_display_condition', 'INT NOT_NULL DEFAULT 0');
$db->AddColumn('hd_project', 'thankyou_page_type', 'INT NOT_NULL DEFAULT 1');
$db->AddColumn('hd_project', 'thankyou_page_content', 'CLOB');
DB_UPDATE_FILE
);


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

$db->DropColumn('pub_page', 'is_published');

DB_UPDATE_FILE
);


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

$db->DropTable('publish_files');

DB_UPDATE_FILE
);


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

$db->DropColumn('hd_project_localization', 'local_value');
DB_UPDATE_FILE
);


$migrations->Run('12_move_publish_ckfinder_dirs.php', <<<'DB_UPDATE_FILE'
<?php
/**
 * Move subfolders of /intranet/publish/files to /appdata/publish/files.
 * All of these subfolders pertain to CKFinder uploads for publish pages
 * with the same name as the folder.
 */
$base = realpath($GLOBALS['INTRANET'].'/../').'/';
$old_dir = $base.'intranet/publish/files/';
$new_dir = $base.'appdata/publish/files/';

// Create $new_dir path with 0777 permissions
if (!is_dir($new_dir))
{
	mkdir($new_dir, 0777, true);
	chmod($base.'appdata', 0777);
	chmod($base.'appdata/publish', 0777);
	chmod($new_dir, 0777);
}

/**
 * Include a couple of filesystem functions for copying directories
 * recursively. Code for outputting errors has been removed.
 */
if (!function_exists('mpfd_create_path'))
{
	/**
	 * Create all required directories in specified path.
	 * Permissions default to 0777.
	 *
	 * @param string $path
	 * @param int|null $perms
	 *
	 * @return bool
	 * @see /intranet/common/filesystem.php
	 */
	function mpfd_create_path($path, $perms = null)
	{
		$DS = DIRECTORY_SEPARATOR;
		if (is_null($perms))
			$perms = 0777;

		if (is_dir($path))
		{
			@chmod($path, $perms);
			return true;
		}

		$path_elements = preg_split('@/|\\\\@', $path);
		if ($path_elements[count($path_elements) - 1] == '')
			array_pop($path_elements);
		$path_elements_tmp = $path_elements;

		$success = false;
		while ((sizeof($path_elements_tmp) >= 1 && $path_elements_tmp[0] != '') || (sizeof($path_elements_tmp) > 1 && $path_elements_tmp[0] == ''))
		{
			$dirname = join($DS, $path_elements_tmp);
			if (is_dir($dirname) || @mkdir($dirname))
			{
				$success = true;
				if (!is_null($perms))
					@chmod($dirname, $perms);
				break;
			}
			array_pop($path_elements_tmp);
		}

		if (!$success)
			return false;

		$cnt = sizeof($path_elements_tmp)+1;
		for ($i = $cnt; $i <= sizeof($path_elements); ++$i)
		{
			$dirname = join($DS, array_slice($path_elements, 0, $i));
			if (!is_dir($dirname) && !@mkdir($dirname))
				$success = false;
			if (!is_null($perms))
				@chmod($dirname, $perms);
		}

		return $success;
	}
}

if (!function_exists('mpfd_copydir_r'))
{
	/**
	 * Recursively copy all content of one directory to another.
	 * On error, returns false
	 *
	 * @param string $source_path
	 * @param string $target_path
	 *
	 * @return bool
	 * @see /intranet/common/filesystem.php
	 */
	function mpfd_copydir_r($source_path, $target_path)
	{
		$dir = opendir($source_path);

		mpfd_create_path($target_path);

		while (($file = readdir($dir)) !== false)
		{
			if ($file == '.' || $file == '..')
				continue;

			if (is_dir($source_path.'/'.$file))
			{
				mkdir($target_path);

				if (!is_dir($target_path.'/'.$file) && !mkdir($target_path.'/'.$file))
					return false;

				if (!mpfd_copydir_r($source_path.'/'.$file, $target_path.'/'.$file))
					return false;
			} else
			{
				if (!copy($source_path.'/'.$file, $target_path.'/'.$file))
					return false;
			}
		}

		closedir($dir);

		return true;
	}
}

// Read files and directories in $old_dir
$files = glob($old_dir . '*');

// Move all files and directories from $old_dir into $new_dir
// Copy if unable to move, and delete if the file is .xml
foreach ($files as $file)
{
	if (file_exists($file))
	{
		$ext = pathinfo($file, PATHINFO_EXTENSION);

		if ($ext == 'xml')
		{
			unlink($file);
		} else
		{
			$new_file = $new_dir . basename($file);

			// Try renaming, fall back to copying
			if(!@rename($file, $new_file)){
				if (is_dir($file))
					mpfd_copydir_r($file, $new_file);
				else
					copy($file, $new_file);
			}
		}
	}
}
DB_UPDATE_FILE
);


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

// im_digest_queue
$table_descr = array(
	'id'	=>	"IDENTITY",
	'im_msg_id'	=>	"INT NULL",
	'user_id'	=>	"INT NULL",
	'app_type'	=>	"VARCHAR(100) NULL",
);

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

DB_UPDATE_FILE
);


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

$db->query('DELETE FROM hd_project_localization WHERE local_key eq:str:related OR local_key eq:str:updated', 'related_issues', 'issues_updated');
DB_UPDATE_FILE
);


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