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



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

// Constant values at the time of the migration
$PROJECT_AGGREGATION = 121;

$VIEW_PROJECT = 1;
$VIEW_TEAM = (1 << 1);
$VIEW_TASK = (1 << 2);
$VIEW_PRIVATE_TASK = (1 << 3);
$UPDATE_OWN_TASK_STATUS = (1 << 4);
$ADD_TASK = (1 << 5);
$EDIT_OWN_TASK = (1 << 6);
$EDIT_ANY_TASK = (1 << 7);
$VIEW_DISCUSSION = (1 << 8);
$PARTICIPATE_IN_DISCUSSION = (1 << 9);
$VIEW_PRIVATE_DISCUSSION = (1 << 10);
$PARTICIPATE_IN_PRIVATE_DISCUSSION = (1 << 11);
$EDIT_OWN_DISCUSSION = (1 << 12);
$EDIT_ANY_DISCUSSION = (1 << 13);
$VIEW_FILE = (1 << 14);
$ADD_FILE = (1 << 15);
$VIEW_PRIVATE_FILE = (1 << 16);
$DELETE_OWN_FILE = (1 << 17);
$DELETE_ANY_FILE = (1 << 18);
$EDIT_PROJECT_INFORMATION = (1 << 19);
$EDIT_TEAM = (1 << 20);
$DELETE_PROJECT = (1 << 21);
$PM_NOTIFICATIONS = (1 << 24);
$ADD_PROJECT_UPDATE = (1 << 25);

/** @var \Claromentis\Setup\SetupFacade $migrations */
$db = $migrations->GetDb();

$pm_rights = $VIEW_PROJECT
			| $VIEW_TEAM
			| $VIEW_TASK
			| $VIEW_PRIVATE_TASK
			| $UPDATE_OWN_TASK_STATUS
			| $ADD_TASK
			| $EDIT_OWN_TASK
			| $EDIT_ANY_TASK
			| $VIEW_DISCUSSION
			| $PARTICIPATE_IN_DISCUSSION
			| $VIEW_PRIVATE_DISCUSSION
			| $PARTICIPATE_IN_PRIVATE_DISCUSSION
			| $EDIT_OWN_DISCUSSION
			| $EDIT_ANY_DISCUSSION
			| $VIEW_FILE
			| $ADD_FILE
			| $VIEW_PRIVATE_FILE
			| $DELETE_OWN_FILE
			| $DELETE_ANY_FILE
			| $EDIT_PROJECT_INFORMATION
			| $EDIT_TEAM
			| $DELETE_PROJECT
			| $PM_NOTIFICATIONS
			| $ADD_PROJECT_UPDATE;
$senior_team_rights = $VIEW_PROJECT
			| $VIEW_TEAM
			| $VIEW_TASK
			| $VIEW_PRIVATE_TASK
			| $UPDATE_OWN_TASK_STATUS
			| $ADD_TASK
			| $EDIT_OWN_TASK
			| $VIEW_DISCUSSION
			| $PARTICIPATE_IN_DISCUSSION
			| $VIEW_PRIVATE_DISCUSSION
			| $PARTICIPATE_IN_PRIVATE_DISCUSSION
			| $EDIT_OWN_DISCUSSION
			| $VIEW_FILE
			| $ADD_FILE
			| $VIEW_PRIVATE_FILE
			| $DELETE_OWN_FILE
			| $DELETE_ANY_FILE
			| $EDIT_TEAM
			| $ADD_PROJECT_UPDATE;
$team_rights = $VIEW_PROJECT
			| $VIEW_TEAM
			| $VIEW_TASK
			| $VIEW_PRIVATE_TASK
			| $UPDATE_OWN_TASK_STATUS
			| $ADD_TASK
			| $EDIT_OWN_TASK
			| $VIEW_DISCUSSION
			| $PARTICIPATE_IN_DISCUSSION
			| $VIEW_PRIVATE_DISCUSSION
			| $PARTICIPATE_IN_PRIVATE_DISCUSSION
			| $EDIT_OWN_DISCUSSION
			| $VIEW_FILE
			| $ADD_FILE
			| $VIEW_PRIVATE_FILE
			| $DELETE_OWN_FILE;
$senior_client_rights = $VIEW_PROJECT
			| $VIEW_TEAM
			| $UPDATE_OWN_TASK_STATUS
			| $ADD_TASK
			| $EDIT_OWN_TASK
			| $VIEW_DISCUSSION
			| $PARTICIPATE_IN_DISCUSSION
			| $EDIT_OWN_DISCUSSION
			| $VIEW_FILE
			| $ADD_FILE
			| $DELETE_OWN_FILE;
$client_rights = $VIEW_PROJECT
			| $VIEW_TEAM
			| $UPDATE_OWN_TASK_STATUS
			| $EDIT_OWN_TASK
			| $VIEW_DISCUSSION
			| $PARTICIPATE_IN_DISCUSSION
			| $EDIT_OWN_DISCUSSION
			| $VIEW_FILE
			| $ADD_FILE
			| $DELETE_OWN_FILE;
$viewer_rights = $VIEW_PROJECT
			| $VIEW_TEAM
			| $VIEW_DISCUSSION
			| $VIEW_FILE;

// Make a list
$old_roles = [
	1 => [
		'name' => 'PM',
		'rights' => $pm_rights
	],
	2 => [
		'name' => 'Senior Team',
		'rights' => $senior_team_rights
	],
	3 => [
		'name' => 'Team',
		'rights' => $team_rights
	],
	4 => [
		'name' => 'Senior Client',
		'rights' => $senior_client_rights
	],
	5 => [
		'name' => 'Client',
		'rights' => $client_rights
	],
	6 => [
		'name' => 'Viewer',
		'rights' => $viewer_rights
	]
];

// Need to add the roles to both
$project_ids = [0];
$res = $db->query('SELECT id FROM projects_project');
// Also add these roles to all existing projects
while ($arr = $res->fetchArray())
{
	$project_ids[] = (int)$arr['id'];
}

foreach ($project_ids as $project_id)
{
	// Find existing members
	$members = [];
	foreach ($old_roles as $old_role_id => $role)
	{
		$members[$old_role_id] = [];
	}

	$res = $db->query('SELECT user_id, role FROM projects_member WHERE project_id eq:int:project_id', $project_id);
	while ($arr = $res->fetchArray())
	{
		$members[(int)$arr['role']][] = (int)$arr['user_id'];
	}

	// Add new role for this project
	$order = 0;
	foreach ($old_roles as $old_role_id => $role)
	{
		$db->query("INSERT INTO acl_role (aggregation, object_id, name, position, rights, read_only) VALUES (int:aggr, int:project_id, str:role_name, int:order, int:rights, 0)",
			$PROJECT_AGGREGATION,
			$project_id,
			$role['name'],
			$order++,
			$role['rights']);

		$role_id = $db->insertId();
		foreach ($members[$old_role_id] as $member)
		{
			$db->query('INSERT INTO permissions (class_id, object_id, perm_owner_class_id, perm_owner_id, rights) VALUES (int:class_id, int:object_id, int:perm_owner_class_id, int:perm_owner_id, int:rights)',
				140, $role_id, 1, $member, 1);
		}
	}
}

// Removed dropping role column from member - Do the actual drop in a later release

DB_UPDATE_FILE
);


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

/** @var \Claromentis\Setup\SetupFacade $migrations */
$db = $migrations->GetDb();

$res= $db->query('SELECT id, description FROM projects_project WHERE description IS NOT NULL');

while ($arr = $res->fetchArray())
{
	$db->query('UPDATE projects_project SET description = str:description WHERE id eq:int:id', ClaText::ProcessBBCode($arr['description']), (int)$arr['id']);
}

DB_UPDATE_FILE
);


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

// Add CKFinder file storage for anything uploaded to the project description
global $APPDATA;
if (!is_dir("$APPDATA/projects"))
	mkdir("$APPDATA/projects", 0775, true);
if (!is_dir("$APPDATA/projects/description"))
	mkdir("$APPDATA/projects/description", 0775, true);

DB_UPDATE_FILE
);


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

/** @var \Claromentis\Setup\SetupFacade $migrations */
$db = $migrations->GetDb();

$rights_adjustment = (1 << 22) //\Claromentis\Projects\Permissions::TEAM_NOTIFICATIONS
| (1 <<23); //\Claromentis\Projects\Permissions::TEAM_MEMBER
$exempt_role = "Viewer";

$db->query(
	"UPDATE acl_role SET acl_role.rights = acl_role.rights | int:rights_adjustment WHERE acl_role.name !eq:str:exempt_role",
	$rights_adjustment,
	$exempt_role
);

DB_UPDATE_FILE
);


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

use Claromentis\Core\DAL\Exceptions\TransactionException;
use Claromentis\Setup\SetupFacade;

/**
 * @throws TransactionException
 */

/** @var SetupFacade $migrations */

$db = $migrations->GetDb();

try
{
	$db->DoTransaction(function () use ($migrations) {
		$db = $migrations->GetDb();

		/**
		 * @see \Claromentis\Projects\ProjectsRepository::PROJECTS_ROOT_FOLDER
		 */
		$projects_root_folder_variable = 'project_module_root_folder_id';

		/**
		 * @see PERM_CLASS_UNIFIED
		 */
		$perm_class_unified = 19;

		/**
		 * @see \Claromentis\Core\Acl\PermOClass::INDIVIDUAL
		 */
		$perm_oclass_individual = 1;

		/**
		 * @see \Claromentis\Core\Acl\PermOClass::ALL
		 */
		$perm_oclass_all = 4;

		/**
		 * @see PERM_OCLASS_PARENT
		 */
		$perm_oclass_parent = 7;

		/**
		 * @see PERM_OCLASS_SECURITY_LEVEL
		 */
		$perm_oclass_security_level = 8;

		$projects_localizations = [
			'en' => 'Projects',
			'us' => 'Projects',
			'ru' => 'Проекты',
			'fr' => null,
			'de' => 'Projekte',
			'br' => null,
			'es' => null,
			'nl' => null,
			'cz' => null,
			'pl' => null,
			'gr' => null,
			'ar' => null,
			'ku' => null,
			'hu' => 'Projektek',
			'uz' => 'Loyihalar'
		];

		/**
		 * @see AGGREGATION_FOLDER
		 */
		$folder_aggregation = 2;

		$result = $db->query("SELECT variables.var_value, ERMS_file_plan.id FROM variables LEFT JOIN ERMS_file_plan ON variables.var_value = ERMS_file_plan.id WHERE var_name eq:str:projects_root_folder_variable", $projects_root_folder_variable);
		$row    = $result->fetchRow();

// If the Projects' Root Folder exists.
		if (isset($row[1]))
		{
			return;
		}

		$projects_root_folder_defined = false;
// If the Project's Root Folder has been defined, but does not exist.
		if (isset($row[0]))
		{
			$projects_root_folder_defined = true;
		}

// The Projects' Root Folder does not exist.
		$config           = $migrations->GetConfigFull();
		$default_language = $config->Get('cfg_default_language');
		if (!is_string($default_language) || !isset($projects_localizations[$default_language]))
		{
			$default_language = 'en';
		}

		$projects_root_folder_name = $projects_localizations[$default_language];

		$result = $db->query("SELECT id FROM ERMS_file_plan WHERE title eq:str:projects_root_folder_name", $projects_root_folder_name);
		$row    = $result->fetchRow();

// If the Folder with the chosen name doesn't exist, create it
		if (!isset($row[0]))
		{
			// Create the ID for the Unified Permissions object.
			$db->query("INSERT INTO perm_objects (dummy) VALUES (0)");
			$perm_id = (int) $db->insertId();

			$perm_oclass_id_rights = [
				$perm_oclass_individual     => ['perm_owner_id' => 1, 'rights' => 8],
				$perm_oclass_all            => ['perm_owner_id' => 0, 'rights' => 1],
				$perm_oclass_parent         => ['perm_owner_id' => 0, 'rights' => 1],
				$perm_oclass_security_level => ['perm_owner_id' => 0, 'rights' => 1]
			];

			// Set the Unified Permissions Object's Permissions.
			foreach ($perm_oclass_id_rights as $perm_oclass_id => $object_rights)
			{
				$db->query("INSERT INTO permissions (
			class_id,
			object_id,
			perm_owner_class_id,
			perm_owner_id,
			rights
		) VALUES (
			int:class_id,
			int:id,
			int:oclass,
			int:oid,
			int:perms
		)",
					$perm_class_unified,
					$perm_id,
					$perm_oclass_id,
					$object_rights['perm_owner_id'],
					$object_rights['rights']
				);
			}

			// Create Folder.
			$db->query("INSERT INTO ERMS_file_plan (
			title,
			date_created,
			date_last_modified,
			aggregation,
			creator,
			perm_id
		) VALUES (
			str:title,
			int:date_created,
			int:date_last_modified,
			int:aggregation,
			int:creator,
			int:perm_id
		)",
				$projects_root_folder_name,
				date('YmdHis'),
				date('YmdHis'),
				$folder_aggregation,
				1,
				$perm_id
			);
			$folder_id = (int) $db->insertId();
		} else
		{
			$folder_id = (int) $row[0];
		}

		if ($projects_root_folder_defined)
		{
			$query = "UPDATE variables SET var_value=str:value WHERE var_name eq:str:name";
		} else
		{
			$query = "INSERT INTO variables (var_value, var_name) VALUES (str:value, str:name)";
		}

		$db->query($query, $folder_id, $projects_root_folder_variable);
	});
} catch (TransactionException $exception)
{
	$migrations->log("Migration 03_projects_default_documents_failed, no Repository changes made.");

	throw $exception;
}

// Does not include Folder Metadata.
// Does not index the folder.

/*
 * 1. Check if the directory is already set. If yes exit.
 * 2. Get the systems default Localization.
 * 3. Check if the directory already exists. If no, create the directory. Make sure it defaults to All Registered view permissions
 * 4. Update Projects to use this as the root directory.
 */



DB_UPDATE_FILE
);


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

use Claromentis\Core\DAL\Exceptions\TransactionException;
use Claromentis\Setup\SetupFacade;

/**
 * @throws TransactionException
 */

/** @var SetupFacade $migrations */

$db = $migrations->GetDb();

$perm_oclass_individual = 1;
$perm_oclass_all = 4;

$result = $db->query("SELECT variables.var_value, ERMS_file_plan.id, ERMS_file_plan.deleted FROM variables LEFT JOIN ERMS_file_plan ON variables.var_value = ERMS_file_plan.id WHERE var_name eq:str:projects_root_folder_variable", 'project_module_root_folder_id');
$row    = $result->fetchRow();


$projects_localizations = [
	'en' => 'Projects',
	'us' => 'Projects',
	'ru' => 'Проекты',
	'fr' => null,
	'de' => 'Projekte',
	'br' => null,
	'es' => null,
	'nl' => null,
	'cz' => null,
	'pl' => null,
	'gr' => null,
	'ar' => null,
	'ku' => null,
	'hu' => 'Projektek',
	'uz' => 'Loyihalar'
];

$projects_root_folder_deleted = ($row[2] == 1);

//if Projects exists and hasn't been deleted
if (isset($row[1]) && !$projects_root_folder_deleted)
{
	return;
}

$projects_root_folder_defined = false;
// If the Project's Root Folder has been defined, but does not exist.
if (isset($row[0]))
{
	$projects_root_folder_defined = true;
}


// The Projects' Root Folder does not exist.
$config  = $migrations->GetConfigFull();
$default_language = $config->Get('cfg_default_language');
if (!is_string($default_language) || !isset($projects_localizations[$default_language]))
{
	$default_language = 'en';
}

$projects_root_folder_name = $projects_localizations[$default_language];

$result = $db->query("SELECT id FROM ERMS_file_plan WHERE title eq:str:projects_root_folder_name", $projects_root_folder_name);
$row    = $result->fetchRow();


// If the Folder with the chosen name doesn't exist, create it
if (!isset($row[0]))
{
	// Create the ID for the Unified Permissions object.
	$db->query("INSERT INTO perm_objects (dummy) VALUES (0)");
	$perm_id = (int) $db->insertId();

	$perm_oclass_id_rights = [
		$perm_oclass_individual     => ['perm_owner_id' => 1, 'rights' => 8],
		$perm_oclass_all            => ['perm_owner_id' => 0, 'rights' => 1],
		PERM_OCLASS_PARENT         => ['perm_owner_id' => 0, 'rights' => 1],
		PERM_OCLASS_SECURITY_LEVEL => ['perm_owner_id' => 0, 'rights' => 1]
	];

	// Set the Unified Permissions Object's Permissions.
	foreach ($perm_oclass_id_rights as $perm_oclass_id => $object_rights)
	{
		$db->query("INSERT INTO permissions (
					class_id,
					object_id,
					perm_owner_class_id,
					perm_owner_id,
					rights
				) VALUES (
					int:class_id,
					int:id,
					int:oclass,
					int:oid,
					int:perms
				)",
			PERM_CLASS_UNIFIED,
			$perm_id,
			$perm_oclass_id,
			$object_rights['perm_owner_id'],
			$object_rights['rights']
		);
	}

	// Create Folder.
	$db->query("INSERT INTO ERMS_file_plan (
				title,
				date_created,
				date_last_modified,
				aggregation,
				creator,
				perm_id
			) VALUES (
				str:title,
				int:date_created,
				int:date_last_modified,
				int:aggregation,
				int:creator,
				int:perm_id
			)",
		$projects_root_folder_name,
		date('YmdHis'),
		date('YmdHis'),
		AGGREGATION_FOLDER,
		1,
		$perm_id
	);
	$folder_id = (int) $db->insertId();
} else
{
	$folder_id = (int) $row[0];
}

if ($projects_root_folder_defined)
{
	$query = "UPDATE variables SET var_value=str:value WHERE var_name eq:str:name";
} else
{
	$query = "INSERT INTO variables (var_value, var_name) VALUES (str:value, str:name)";
}

$db->query($query, $folder_id,'project_module_root_folder_id');

if ($projects_root_folder_deleted)
{
	$db->query("UPDATE ERMS_file_plan SET deleted = int:del WHERE id = int:id", 0, $folder_id);
}




DB_UPDATE_FILE
);


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

use Claromentis\Setup\SetupFacade;

/** @var SetupFacade $migrations */
$db = $migrations->GetDb();

$db->AddColumn('projects_project', 'todo_status_label', 'VARCHAR(256)');
$db->AddColumn('projects_project', 'completed_status_label', 'VARCHAR(256)');

$db->query("UPDATE projects_project SET todo_status_label = 'To do', completed_status_label='Done'");

$db->AddColumn('projects_task', 'status_id', 'INT');
$db->query('UPDATE projects_task SET status_id=-1 WHERE is_complete=0');
$db->query('UPDATE projects_task SET status_id=-2 WHERE is_complete<>0');
$db->DropColumn('projects_task', 'is_complete');

$db->CreateTable('projects_status', [
	'id' => 'IDENTITY',
	'project_id' => 'INT',
	'name' => 'VARCHAR(256)',
	'sort_order' => 'INT'
]);
DB_UPDATE_FILE
);


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

use Claromentis\Setup\SetupFacade;

/** @var SetupFacade $migrations */

$db = $migrations->GetDb();

$db->AlterColumn('projects_project', 'description', 'description', 'CLOB NULL DEFAULT NULL');
$db->AlterColumn('projects_history', 'changes', 'changes', 'CLOB NULL DEFAULT NULL');

DB_UPDATE_FILE
);


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

use Claromentis\Setup\SetupFacade;

/** @var SetupFacade $migrations */
$db = $migrations->GetDb();

$cols = [
	'task_id' => 'INT NOT_NULL',
	'user_id' => 'INT NOT_NULL',
];

$db->CreateTable("projects_task_assignee", $cols);
$db->CreatePrimaryKey("projects_task_assignee", "task_id", "user_id");
$db->CreateIndex("projects_task_assignee", "projects_task_assignee_users", "user_id");

$db->query("INSERT INTO projects_task_assignee SELECT id, assignee FROM projects_task");

DB_UPDATE_FILE
);


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

use Claromentis\Setup\SetupFacade;

/** @var SetupFacade $migrations */
$db = $migrations->GetDb();

$db->query("UPDATE permissions
	SET rights = " . $db->bitwiseand("rights", ~(1<<20)) .
	" WHERE class_id = 140
	AND object_id IN (
		SELECT id FROM acl_role WHERE aggregation=121
	)");

DB_UPDATE_FILE
);


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