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



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

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

$data = [
	'section_name' => 'VARCHAR(100)',
	'value' => 'CLOB NULL'
];

$db->CreateTable('custom_code', $data);

DB_UPDATE_FILE
);


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

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

$db->query('UPDATE publish_menunode SET url = null WHERE url = "javascript:void(0);"');
DB_UPDATE_FILE
);


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

$INTRANET = $config->Get("INTRANET");
$DATA_DIR = $config->Get("DATA_DIR");

if (!is_dir("$DATA_DIR/ckeditor"))
	mkdir("$DATA_DIR/ckeditor", 0777, true);

foreach (glob("$INTRANET/common/ckeditor/*_custom.js") as $file)
{
	$regex = "#" . preg_quote("$INTRANET/common/ckeditor/", "#") . "(.*)_custom\.js#";

	preg_match($regex , $file,$matches);
	$base_name = $matches[1];
	$new_name = "$DATA_DIR/ckeditor/$base_name.js";

	rename($file, $new_name);
}

DB_UPDATE_FILE
);


$migrations->Run('01_move_localisation.php', <<<'DB_UPDATE_FILE'
<?php
/** @var \Claromentis\Setup\SetupFacade $migrations */
$migrations->GetDataDir();
$config = $migrations->GetConfigFull();

$INTRANET = $config->Get("INTRANET");
$CUSTOM = realpath($INTRANET . '/../custom');
$DATA_DIR = $config->Get("DATA_DIR");

$core_localisation = glob("$INTRANET/*/languages/*_custom.txt");
$intranet_escaped = preg_quote($INTRANET, '#');
//$dir_sep = preg_quote(DIRECTORY_SEPARATOR, '#');
$mkdir_if_not_exist = function($path) {
	if(!is_dir($path))
	{
		if(is_file($path))
			unlink($path);
		mkdir($path, 0777, true);
	}
};

foreach($core_localisation as $path)
{
	preg_match("#^$intranet_escaped/(.*)/languages/(.*)_custom\.txt$#", $path, $matches);
	$module = $matches[1];
	$name = $matches[2];

	$mkdir_if_not_exist("$DATA_DIR/i18n/$module");
	rename($path, "$DATA_DIR/i18n/$module/$name.txt");
}

$custom_localisation = glob("$CUSTOM/*/languages/*_custom.txt");
$custom_escaped = preg_quote($CUSTOM, '#');

foreach($custom_localisation as $path)
{
	preg_match("#^$custom_escaped/(.*)/languages/(.*)_custom\.txt$#", $path, $matches);
	$module = $matches[1];
	$name = $matches[2];

	$mkdir_if_not_exist("$DATA_DIR/i18n/$module");
	rename($path, "$DATA_DIR/i18n/$module/$name.txt");
}


//move custom holiday planner templates into holidays/languages/notifications
$holiday_templates = glob("$INTRANET/common/languages/notifications/*_custom/holidays.*");

foreach($holiday_templates as $template)
{
	preg_match("#^$intranet_escaped/common/languages/notifications/(.*)/(.*)\.(twig|txt)$#", $template, $matches);
	$lang = $matches[1];
	$name = $matches[2];
	$ext = $matches[3];
	$new_dir = "$INTRANET/holidays/languages/notifications/$lang";

	$mkdir_if_not_exist($new_dir);
	rename($template, "$new_dir/$name.$ext");
}

// move all core module templates into data_dir
$core_templates = glob("$INTRANET/*/languages/notifications/*_custom/*");

foreach($core_templates as $template)
{
	preg_match("#^$intranet_escaped/(.*)/languages/notifications/(.*)_custom/(.*)$#", $template, $matches);
	$module = $matches[1];
	$lang = $matches[2];
	$name = $matches[3];
	$new_dir = "$DATA_DIR/i18n/$module/notifications/$lang";

	$mkdir_if_not_exist($new_dir);
	rename($template, "$new_dir/$name");
}

$empty_dirs = glob("$INTRANET/*/languages/notifications/*_custom");
foreach ($empty_dirs as $dir)
	rmdir($dir);

// move all custom module templates into data_dir
$custom_templates = glob("$CUSTOM/*/languages/notifications/*_custom/*");

foreach($custom_templates as $template)
{
	preg_match("#^$custom_escaped/(.*)/languages/notifications/(.*)_custom/(.*)$#", $template, $matches);
	$module = $matches[1];
	$lang = $matches[2];
	$name = $matches[3];
	$new_dir = "$DATA_DIR/i18n/$module/notifications/$lang";

	$mkdir_if_not_exist($new_dir);
	rename($template, "$new_dir/$name");
}

$empty_dirs = glob("$CUSTOM/*/languages/notifications/*_custom");
foreach ($empty_dirs as $dir)
	rmdir($dir);

//move the custom digest template (if it exists)
$custom_digest = "$INTRANET/common/languages/notification/digest_custom.html";

if(is_file($custom_digest))
	rename($custom_digest, "$DATA_DIR/i18n/custom/notifications/digest.html");

DB_UPDATE_FILE
);


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

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

$db->query("DELETE FROM variables WHERE var_name = 'system_cache_cleared'");

$timestamp = new Datetime();
$timestamp->setTime(0, 0, 0);

$db->query("INSERT INTO variables (var_name, var_value) VALUES ('system_cache_cleared', str:timestamp)", $timestamp->getTimestamp());
DB_UPDATE_FILE
);


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

// Menu data is cached in users sessions. To show the new progressive menu stuff we need to invalidate all sessions
// and get people to login again

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

$db->query("TRUNCATE TABLE autologon_cookie");
DB_UPDATE_FILE
);


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

/**
 * Give messages an optional group ID
 */
$database->AddColumn('imessages', 'conversation_id', 'INT NOT_NULL DEFAULT 0');

/**
 * Create a table for conversations
 */
$database->CreateTable(
	'im_conversations',
	[
		'id' => 'IDENTITY'
	]
);

/**
 * Create a pivot table for conversations and their participating users
 */
$database->CreateTable(
	'im_conversations_users',
	[
		'conversation_id' => 'INT NOT_NULL',
		'user_id'         => 'INT NOT_NULL'
	]
);

$database->CreatePrimaryKey('im_conversations_users', 'conversation_id', 'user_id');
$database->CreateIndex('im_conversations_users', 'idx_conversation_id', 'conversation_id');
$database->CreateIndex('im_conversations_users', 'idx_user_id', 'user_id');

/**
 * Load all personal messages and their participants (senders and recipients)
 */
$result = $database->query(
	'SELECT id, from_id, im_recipients.user_id recipient_id FROM imessages
	JOIN im_recipients ON im_recipients.imsg_id = imessages.id
	WHERE message_type = 0'
);

/**
 * Collect message sender and recipient IDs as participants for each message
 */
$message_participants = [];

while ($row = $result->fetchArray())
{
	$message_id   = $row['id'];
	$sender_id    = $row['from_id'];
	$recipient_id = $row['recipient_id'];

	// Create a participant list for the message
	if (!isset($message_participants[$message_id]))
		$message_participants[$message_id] = [];

	// Add the sender
	if (!in_array($sender_id, $message_participants[$message_id]))
		$message_participants[$message_id][] = $sender_id;

	// Add the recipient
	if (!in_array($recipient_id, $message_participants[$message_id]))
		$message_participants[$message_id][] = $recipient_id;
}

// Create conversations for each message, grouped by participants
foreach ($message_participants as $message_id => $participants)
{
	if (empty($participants))
		continue;

	$participants_count = count($participants);

	// Determine whether a conversation already exists by finding conversations
	// with the exact same participants
	$result = $database->query(
		'SELECT ic.id FROM im_conversations ic
		INNER JOIN im_conversations_users icu ON icu.conversation_id = ic.id
		WHERE icu.user_id IN in:int:participants
		AND (SELECT COUNT(user_id) FROM im_conversations_users WHERE conversation_id = ic.id) eq:int:participants_count
		GROUP BY ic.id
		HAVING COUNT(DISTINCT icu.user_id) eq:int:participants_count',
		$participants,
		$participants_count,
		$participants_count
	);

	$conversation_id = $result->hasData() ? $result->fetchRow()[0] : 0;

	// Create this conversation and its participants if they don't exist yet
	if (!$conversation_id)
	{
		// Create the conversation
		if ($database->type() === 'mssql')
			$database->query('INSERT INTO im_conversations DEFAULT VALUES');
		else
			$database->query('INSERT INTO im_conversations () VALUES ()');

		$conversation_id = $database->insertId();

		if (!$conversation_id)
		{
			$migrations->Log('Failed to create a conversation during communication migration');
			continue;
		}

		// Relate the participants to the conversation
		foreach ($participants as $participant_id)
		{
			$database->query(
				'INSERT INTO im_conversations_users (conversation_id, user_id) VALUES (int:conversation_id, int:user_id)',
				$conversation_id,
				$participant_id
			);
		}
	}

	// Relate the message to this conversation
	$database->query('UPDATE imessages SET conversation_id = int:conversation_id WHERE id eq:int:id', $conversation_id, $message_id);
}

DB_UPDATE_FILE
);


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

$database->CreateIndex('imessages', 'idx_conversation_id', 'conversation_id');

DB_UPDATE_FILE
);


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