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



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

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

$db->AddColumn('projects_display_order', 'status_id', 'INT DEFAULT NULL');

DB_UPDATE_FILE
);


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

/** Claromentis\Core\Acl\RoleBased::PERM_CLASS_ACL_ROLE */
$PERM_CLASS_ACL_ROLE = 140;

/** Claromentis\Projects\Permissions::PROJECT_AGGREGATION */
$PROJECT_AGGREGATION = 121;

/** Default object_id for project roles */
$OBJECT_ID = 0;

/** remove users from column Member in Default project roles & rights */
$db->query("DELETE FROM permissions
	WHERE class_id = $PERM_CLASS_ACL_ROLE
  	AND object_id IN (
		SELECT id FROM acl_role WHERE aggregation = $PROJECT_AGGREGATION and object_id = $OBJECT_ID
	)");

DB_UPDATE_FILE
);


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

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

/** the migration 08_01.php (INSERT INTO projects_task_assignee SELECT id, assignee FROM projects_task)
 * can create task assigned to the user_id equal 0
 */
$db->query("DELETE FROM projects_task_assignee WHERE user_id=0");

DB_UPDATE_FILE
);


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

use Claromentis\Setup\SetupFacade;

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

$db->CreateTable("projects_topic", [
	'id' => 'IDENTITY',
	'project_id' => 'INT',
	'title' => 'VARCHAR(100)'
]);
$db->CreateIndex("projects_topic", "topic_project_id", 'project_id');

$projects_result = $db->query("SELECT id FROM projects_project");

while ($result = $projects_result->fetchArray())
{
	$project_id = $result['id'];

	/**
	 * Create a default Topic for each Project
	 *
	 * 121: Claromentis/Projects/Permissions::PROJECT_AGGREGATION
	 * 155: Claromentis/Projects/Entity/ProjectTopic::AGGREGATION
	 */
	$db->query(
		"INSERT INTO projects_topic(project_id, title) VALUES (int:pid, str:title)",
		$project_id,
		lmsg("projects.topic.default_topic_name")
	);
	$topic_id = $db->insertId();

	// Move all comments/discussions from the Project to the new Topic
	$db->query(
		"UPDATE discussions SET aggregation=155, object_id=int:tid WHERE aggregation=121 AND object_id=int:pid",
		$topic_id,
		$project_id
	);
}

DB_UPDATE_FILE
);


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

use Claromentis\Setup\SetupFacade;

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

$db->AlterColumn('projects_topic', 'title', 'title', 'VARCHAR(255)');

DB_UPDATE_FILE
);


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

use Claromentis\Setup\SetupFacade;

/** @var SetupFacade $migrations */
/** @var \Claromentis\Core\DAL\Interfaces\DbInterface|\Claromentis\Core\DAL\Interfaces\SchemaInterface $db */
$db = $migrations->GetDb();

$db->AddColumn('projects_topic', 'description', 'CLOB');
$db->AddColumn('projects_topic', 'owner_id', 'INT');
$db->AddColumn('projects_topic', 'modified', 'INT_DATE');

$db->CreateIndex('projects_topic', 'topic_owner', 'owner_id');

DB_UPDATE_FILE
);


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

use Claromentis\Setup\SetupFacade;

/** @var SetupFacade $migrations */
/** @var \Claromentis\Core\DAL\Interfaces\DbInterface|\Claromentis\Core\DAL\Interfaces\SchemaInterface $db */
$db = $migrations->GetDb();

$db->AddColumn('projects_topic', 'pinned', 'BOOL NOT_NULL DEFAULT 0');

DB_UPDATE_FILE
);


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

use Claromentis\Setup\SetupFacade;

/** @var SetupFacade $migrations */
/** @var \Claromentis\Core\DAL\Interfaces\DbInterface|\Claromentis\Core\DAL\Interfaces\SchemaInterface $db */
$db = $migrations->GetDb();


// Add "Manage own Topics" right to anybody who has "Contribute to topics" right as a best guess to match a new install
$db->query("UPDATE acl_role" .
	" SET rights = rights | " . (1<<26) .
	" WHERE " . $db->bitwiseand("rights", (1<<9)) . " = " . (1<<9));

// Add "Manage any Topics" right to anybody who has "Edit/delete any tasks" right as a best guess to match a new install
$db->query("UPDATE acl_role" .
	" SET rights = rights | " . (1<<27) .
	" WHERE " . $db->bitwiseand("rights", (1<<7)) . " = " . (1<<7));

DB_UPDATE_FILE
);


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

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

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

// Add the created date column
$db->AddColumn('projects_topic', 'created', 'INT_DATE NULL');

DB_UPDATE_FILE
);


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

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

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

if ($db->type() === 'mssql')
{
	$sql = 'UPDATE topic
	SET topic.created = project.date_created
	FROM projects_topic topic
	INNER JOIN projects_project project
	ON project.id = topic.project_id
	WHERE topic.created IS NULL';
} else
{
	$sql = 'UPDATE projects_topic topic, projects_project project
	SET topic.created = project.date_created
	WHERE topic.project_id = project.id AND topic.created IS NULL';
}

$query = new DAL\Query($sql);
$db->query($query);

DB_UPDATE_FILE
);


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