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



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

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

// Mentions from old Innovate comments were not converted from the old @<username> format to @[<user_id>] format so do it now
$res = $db->query('SELECT id, txt FROM discussions WHERE txt LIKE str:in', '%@%');
while ($arr = $res->fetchArray())
{
	$txt = $arr['txt'];
	preg_match_all('/(@([a-z]{1}[^\s]*))/mi', $txt, $matches, PREG_SET_ORDER, 0);
	
	$comment_changed = false;
	foreach ($matches as $match)
	{
		$res2 = $db->query('SELECT id FROM users WHERE username eq:str:username', $match[2]);
		if ($res2->numRows() > 0)
		{
			$user = $res2->fetchArray();
			$mention = '@[' . $user['id'] . ']';
			$txt = str_replace($match[1], $mention, $txt);
			$comment_changed = true;
		}
	}

	if ($comment_changed)
	{
		$db->unsafe_query = 1;
		$db->query('UPDATE discussions SET txt = str:txt WHERE id eq:int:id', $txt, $arr['id']);
		$db->unsafe_query = 0;
	}
}
DB_UPDATE_FILE
);


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