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



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

// oauth_access_token_scopes
$table_descr = array(
	'id'	=>	"IDENTITY",
	'access_token'	=>	"VARCHAR(255) NULL",
	'scope'	=>	"VARCHAR(255) NULL",
);

$db->CreateTable('oauth_access_token_scopes', $table_descr, true);
$db->CreateIndex('oauth_access_token_scopes', 'oauth_access_token_scopes_access_token_foreign', 'access_token');
$db->CreateIndex('oauth_access_token_scopes', 'oauth_access_token_scopes_scope_foreign', 'scope');



// oauth_access_tokens
$table_descr = array(
	'access_token'	=>	"VARCHAR(255) NOT_NULL DEFAULT ' '",
	'session_id'	=>	"INT NOT_NULL",
	'expire_time'	=>	"INT NOT_NULL",
);

$db->CreateTable('oauth_access_tokens', $table_descr, true);
$db->CreatePrimaryKey('oauth_access_tokens', 'access_token');
$db->CreateIndex('oauth_access_tokens', 'oauth_access_tokens_session_id_foreign', 'session_id');



// oauth_auth_code_scopes
$table_descr = array(
	'id'	=>	"IDENTITY",
	'auth_code'	=>	"VARCHAR(255) NULL",
	'scope'	=>	"VARCHAR(255) NULL",
);

$db->CreateTable('oauth_auth_code_scopes', $table_descr, true);
$db->CreateIndex('oauth_auth_code_scopes', 'oauth_auth_code_scopes_auth_code_foreign', 'auth_code');
$db->CreateIndex('oauth_auth_code_scopes', 'oauth_auth_code_scopes_scope_foreign', 'scope');



// oauth_auth_codes
$table_descr = array(
	'auth_code'	=>	"VARCHAR(255) NOT_NULL DEFAULT ' '",
	'session_id'	=>	"INT NOT_NULL",
	'expire_time'	=>	"INT NOT_NULL",
	'client_redirect_uri'	=>	"VARCHAR(255) NULL",
);

$db->CreateTable('oauth_auth_codes', $table_descr, true);
$db->CreatePrimaryKey('oauth_auth_codes', 'auth_code');
$db->CreateIndex('oauth_auth_codes', 'oauth_auth_codes_session_id_foreign', 'session_id');



// oauth_client_redirect_uris
$table_descr = array(
	'id'	=>	"IDENTITY",
	'client_id'	=>	"VARCHAR(255) NULL",
	'redirect_uri'	=>	"VARCHAR(255) NULL",
);

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



// oauth_clients
$table_descr = array(
	'id'	=>	"VARCHAR(255) NOT_NULL DEFAULT ' '",
	'secret'	=>	"VARCHAR(255) NULL",
	'name'	=>	"VARCHAR(255) NULL",
);

$db->CreateTable('oauth_clients', $table_descr, true);
$db->CreatePrimaryKey('oauth_clients', 'id');



// oauth_refresh_tokens
$table_descr = array(
	'refresh_token'	=>	"VARCHAR(255) NOT_NULL DEFAULT ' '",
	'expire_time'	=>	"INT NOT_NULL",
	'access_token'	=>	"VARCHAR(255) NULL",
);

$db->CreateTable('oauth_refresh_tokens', $table_descr, true);
$db->CreatePrimaryKey('oauth_refresh_tokens', 'refresh_token');
$db->CreateIndex('oauth_refresh_tokens', 'oauth_refresh_tokens_access_token_foreign', 'access_token');



// oauth_scopes
$table_descr = array(
	'id'	=>	"VARCHAR(255) NOT_NULL DEFAULT ' '",
	'description'	=>	"VARCHAR(255) NULL",
);

$db->CreateTable('oauth_scopes', $table_descr, true);
$db->CreatePrimaryKey('oauth_scopes', 'id');



// oauth_session_scopes
$table_descr = array(
	'id'	=>	"IDENTITY",
	'session_id'	=>	"INT NOT_NULL",
	'scope'	=>	"VARCHAR(255) NULL",
);

$db->CreateTable('oauth_session_scopes', $table_descr, true);
$db->CreateIndex('oauth_session_scopes', 'oauth_session_scopes_session_id_foreign', 'session_id');
$db->CreateIndex('oauth_session_scopes', 'oauth_session_scopes_scope_foreign', 'scope');



// oauth_sessions
$table_descr = array(
	'id'	=>	"IDENTITY",
	'owner_type'	=>	"VARCHAR(255) NULL",
	'owner_id'	=>	"VARCHAR(255) NULL",
	'client_id'	=>	"VARCHAR(255) NULL",
	'client_redirect_uri'	=>	"VARCHAR(255) NULL",
);

$db->CreateTable('oauth_sessions', $table_descr, true);
$db->CreateIndex('oauth_sessions', 'oauth_sessions_client_id_foreign', 'client_id');

?>
DB_UPDATE_FILE
);


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

$db->RenameTable('oauth_client_redirect_uris', 'oauth_client_redirect_ur_b');

// oauth_client_redirect_uris
$table_descr = array(
	'client_id'	=>	"VARCHAR(255) NULL",
	'redirect_uri'	=>	"VARCHAR(255) NULL",
);

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

$db->query("INSERT INTO oauth_client_redirect_uris SELECT client_id, redirect_uri FROM oauth_client_redirect_ur_b");

$db->DropTable('oauth_client_redirect_ur_b');

DB_UPDATE_FILE
);


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

$local_data = $migrations->GetLocalDataDir();
$config_path = $local_data.'/config';
if (!is_dir($config_path))
{
	mkdir($config_path, 0777);
	chmod($config_path, 0777);
}

$plugins = $migrations->GetPluginsRepository();
$plugins->Remove('non-existing-code', 'non-existing-class'); // force config save

$migrations->Log(" ");
$migrations->Log("==============================================================================================");
$migrations->Log("   The plugins list configuration has been moved to local_data/config/plugins.json");
$migrations->Log("   Now please remove all \$cfg_cla_plugins from the config file");
$migrations->Log("==============================================================================================");
$migrations->Log(" ");


DB_UPDATE_FILE
);


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

$icons_conversion = array(
	'icon-cogwheels' => 'glyphicons-cogwheels',
	'icon-stats' => 'glyphicons-stats',
	'icon-security_camera' => 'glyphicons-security-camera',
	'icon-hdd' => 'glyphicons-hdd',
	'icon-edit' => 'glyphicons-edit',
	'icon-calendar' => 'glyphicons-calendar',
	'icon-link' => 'glyphicons-link',
	'icon-circle_ok' => 'glyphicons-circle-ok',
	'icon-book_open' => 'glyphicons-book-open',
	'icon-file' => 'glyphicons-file',
	'icon-sort' => 'glyphicons-sort',
	'icon-comments' => 'glyphicons-comments',
	'icon-airplane' => 'glyphicons-airplane',
	'icon-picture' => 'glyphicons-picture',
	'icon-random' => 'glyphicons-random',
	'icon-more' => 'glyphicons-more',
	'icon-bullhorn' => 'glyphicons-bullhorn',
	'icon-unlock' => 'glyphicons-unlock',
	'icon-address_book' => 'glyphicons-address-book',
	'icon-global' => 'glyphicons-global',
	'icon-clock' => 'glyphicons-clock',
	'icon-search' => 'glyphicons-search',
	'icon-inbox_in' => 'glyphicons-inbox-in',
	'icon-tags' => 'glyphicons-tags',
	'icon-envelope' => 'glyphicons-envelope',
	'icon-retweet' => 'glyphicons-retweet',
);

$res = $db->query("SELECT id, css_class FROM panels");
while (list($id, $css_class) = $res->fetchRow())
{
	if (isset($icons_conversion[$css_class]))
	{
		$db->query("UPDATE panels SET css_class=str:new WHERE id=int:id", $icons_conversion[$css_class], $id);
	}
}

DB_UPDATE_FILE
);


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


// create table DISCUSSION
$table_descr = array(
    'id'	        => "IDENTITY",
    'user_id'	    => "INT NOT_NULL",
    'object_id'     => "INT NOT_NULL",
    'ancillary'     => "INT NULL DEFAULT 0",
    'aggregation'   => "INT NOT_NULL",
    'parent_id'     => "INT NOT_NULL DEFAULT 0",
    'attachment_id' => "INT NULL DEFAULT 0",
    'txt'           => "VARCHAR_MAX NULL",
    'timestamp'     => "INT_DATE NULL",
    'like_count'    => "INT NULL DEFAULT 0",
    'reply_count'   => "INT NULL DEFAULT 0"
);
$db->CreateTable('discussions', $table_descr, true);
$db->CreateIndex('discussions', 'discussion_aggr_object_id', 'aggregation', 'object_id');


// create table LIKES
$table_descr = array(
    'id'	        => "IDENTITY",
    'user_id'	    => "INT NOT_NULL",
    'object_id'     => "INT NOT_NULL",
    'aggregation'   => "INT NOT_NULL",
    'timestamp'     => "INT_DATE NULL",
);
$db->CreateTable('likes', $table_descr, true);
$db->CreateIndex('likes', 'likes_aggr_object_id', 'aggregation', 'object_id');
DB_UPDATE_FILE
);


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

// create table for ATTACHMENTS
$table_descr = array(
    'id'	        => "IDENTITY",
    'discussion_id' => "INT NOT_NULL DEFAULT 0",
    'doc_id'        => "INT NOT_NULL DEFAULT 0",
    'name'	        => "VARCHAR(255) NULL",
    'extension'     => "VARCHAR(4) NULL",
    'path'          => "VARCHAR(255) NULL",
    'type'          => "INT NOT_NULL DEFAULT 0"
);
$db->CreateTable('discussion_attachments', $table_descr, true);
$db->CreateIndex('discussion_attachments', 'aggr_discussion_id', 'discussion_id');

$data_dir = $migrations->GetDataDir();
mkdir($data_dir . '/discussion', 0777);
mkdir($data_dir . '/discussion/tmp', 0777);
DB_UPDATE_FILE
);


$migrations->Run('820_delete_courses.php', <<<'DB_UPDATE_FILE'
<?php
use Claromentis\Core\DAL;
/** @var \Claromentis\Setup\SetupFacade $migrations */
$db = $migrations->GetDb();

$migrations->Log("Backing up courses data");
$data_dir = $migrations->GetDataDir();
$export_success = true;
try {
	/*
	define('COURSES_STATUS_NEW', 0);
	define('COURSES_STATUS_PROCEEDING', 1);
	define('COURSES_STATUS_FINISHED', 2);
	*/
	$course_status_text = array(
		0 => 'no started',
		1 => 'in progress',
		2 => 'finished',
	);


	/*
	define('COURSES_USER_STATUS_CANDIDATE', 0);
	define('COURSES_USER_STATUS_MEMBER', 1);
	define('COURSES_USER_STATUS_REFUSED', 2);  //Organiser refused user's membership request
	define('COURSES_USER_STATUS_STARTING_COURSE', 3);
	define('COURSES_USER_STATUS_FINISHED_COURSE', 4);
	define('COURSES_USER_STATUS_NOT_FINISHED_COURSE', 5);
	define('COURSES_USER_STATUS_IN_QUEUE', 6);
	*/

	$user_status_text = array(
		1 => 'registered',
		3 => 'started',
		4 => 'completed',
		5 => 'not completed',
	);


	$outfile = $data_dir.'/courses_data.csv';
	$fh = fopen($outfile, 'wb');
	if (!$fh)
		throw new Exception("Couldn't create courses data file $outfile");
	fputcsv($fh, [
		"Record ID",
		"Username",
		"Firstname",
		"Lastname",
		"Email",
		"Record Type",
		"Title",
		"Training Provider",
		"Completion Date",
		"Other Information",
		"Qualification Code",
		"completion status",
		"course status",
		"course start date"]);

	$query = new DAL\Query("SELECT u.username, u.firstname, u.surname, u.emailad,
									c.title c_title, c.end_date, c.description, c.refcode, cm.status, c.status c_status, c.start_date
			FROM course_members cm, courses c, users u WHERE cm.status IN (1,3,4,5) AND c.is_deleted=0");
	$query->addJoinCondition('cm', 'c', 'c.id=cm.course_id');
	$query->addJoinCondition('cm', 'u', 'u.id=cm.user_id');
	$result = $db->query($query);
	if ($result instanceof DAL\EmptyResult)
		throw new Exception("SQL query error");
	while ($arr = $result->fetchArray())
	{
		$end_date = new DateDay($arr['end_date']);
		$start_date = new DateDay($arr['start_date']);

		$csv_array = [
			'',
			$arr['username'],
			$arr['firstname'],
			$arr['surname'],
			$arr['emailad'],
			'Training Record',
			$arr['c_title'],
			'',
			$end_date->getDate(DATE_FORMAT_CLA_LONG_DATE),
			$arr['description'],
			$arr['refcode'],
			$user_status_text[$arr['status']],
			$course_status_text[$arr['c_status']],
			$start_date->getDate(DATE_FORMAT_CLA_LONG_DATE),
		];

		if (fputcsv($fh, $csv_array) === false)
			throw new Exception("Error while writing file");
	}
	fclose($fh);
	$migrations->Log("Courses data backed up successfully to $outfile");
	$export_success = true;
} catch (Exception $e)
{
	$migrations->Log("Courses data backup failed, so db tables ('course_members' and 'courses') are not deleted");
}


//define('PERM_CLASS_COURSE_ADD', 21);
$result = $db->query("DELETE FROM permissions WHERE class_id=21");

//define('PERM_CLASS_COURSE', 20);
$result = $db->query("DELETE FROM permissions WHERE class_id=21");

$db->query("DELETE FROM publish_menunode WHERE url LIKE '/intranet/courses/%'");

rename($data_dir.'/course_maps', $data_dir.'/course_maps.deleteme');

// dropping the tables only if export was successfull
if ($export_success)
{
	$db->DropTable("courses");
	$db->DropTable("course_members");
}

DB_UPDATE_FILE
);


$migrations->Run('821_change_menu_links.php', <<<'DB_UPDATE_FILE'
<?php
use Claromentis\Core\DAL;
/** @var \Claromentis\Setup\SetupFacade $migrations */
$db = $migrations->GetDb();

$db->query("UPDATE publish_menunode SET url=str:new WHERE url eq:str:old",
           "/news/", "/intranet/news/");
$db->query("UPDATE publish_menunode SET url=str:new WHERE url eq:str:old",
           "/menu/", "/intranet/publish/menu_builder.php");

DB_UPDATE_FILE
);


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