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



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

$layout_desc = [
	'tile_id' => 'INT NOT_NULL DEFAULT 0',
	'user_id' => 'INT NOT_NULL DEFAULT 0',
	'field_type' => "VARCHAR(100) NULL",
	'value' => "VARCHAR(255) NOT_NULL DEFAULT ' '",
];
$db->CreateTable('pages_user_option', $layout_desc, true);
DB_UPDATE_FILE
);


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

$db->AddColumn('pages_site', 'created_by', 'INT NOT_NULL DEFAULT 0');
$db->AddColumn('pages_site', 'created_by_date', 'INT_DATE NOT_NULL DEFAULT 0');
$db->AddColumn('pages_site', 'modified_by', 'INT NULL DEFAULT 0');
$db->AddColumn('pages_site', 'modified_by_date', 'INT_DATE NULL DEFAULT 0');

$db->AddColumn('pages_page', 'created_by', 'INT NOT_NULL DEFAULT 0');
$db->AddColumn('pages_page', 'created_by_date', 'INT_DATE NOT_NULL DEFAULT 0');
$db->AddColumn('pages_page', 'modified_by', 'INT NULL DEFAULT 0');
$db->AddColumn('pages_page', 'modified_by_date', 'INT_DATE NULL DEFAULT 0');

//pre-populate from pages site audit
$query = "SELECT id FROM pages_site";
$res = $db->query($query);
$sites_ids = [];
if ($res->hasData())
{
	while ($row = $res->fetchArray())
	{
		$sites_ids[] = $row['id'];
	}

	//created by and created by date
	foreach ($sites_ids as $id)
	{
		$query = "SELECT t.userid, MAX(t.dt) as time, details, obj_id  FROM audit_log_txt t WHERE category='pages' AND sub_cat = 'add_site' AND obj_id = $id GROUP BY t.userid, details, obj_id ORDER BY time DESC";


		$res = $db->query($query);
		if ($res->hasData())
		{
			$row = $res->fetchArray();
			$user_id = $row['userid'];
			$time = $row['time'];
			$obj_id = $row['obj_id'];

			$db->DisableTokenCheck();
			$db->query("UPDATE pages_site SET created_by=int:user_id, created_by_date=int:date WHERE id eq:int:pages_id",
			           $user_id, $time, $obj_id);
		}
	}

	//modified by and modified by date
	foreach ($sites_ids as $id)
	{
		$query = "SELECT t.userid, MAX(t.dt) as time, details, obj_id  FROM audit_log_txt t WHERE category='pages' AND sub_cat = 'edit_site' AND obj_id = $id GROUP BY t.userid, details, obj_id ORDER BY time DESC";


		$res = $db->query($query);
		if ($res->hasData())
		{
			$row = $res->fetchArray();
			$user_id = $row['userid'];
			$time = $row['time'];
			$obj_id = $row['obj_id'];

			$db->DisableTokenCheck();
			$db->query("UPDATE pages_site SET modified_by=int:user_id, modified_by_date=int:date WHERE id eq:int:pages_id",
			           $user_id, $time, $obj_id);
		}
	}

}

//pre-populate from pages page audit
$query = "SELECT pu.id, pp.id as pages_id  FROM pages_url pu INNER JOIN pages_page pp ON pu.active_page_id = pp.id";
$res = $db->query($query);
$pages_ids = [];
if ($res->hasData())
{
	while ($row = $res->fetchArray())
	{
		$pages_ids[$row['pages_id']] = $row['id'];
	}


	//created by and created by date
	foreach ($pages_ids as $pages_id => $id)
	{
		$query = "SELECT t.userid, MAX(t.dt) as time, details, obj_id  FROM audit_log_txt t WHERE category='pages' AND sub_cat = 'add_page' AND obj_id = $id GROUP BY t.userid, details, obj_id ORDER BY time DESC";


		$res = $db->query($query);
		if ($res->hasData())
		{
			$row = $res->fetchArray();
			$user_id = $row['userid'];
			$time = $row['time'];

			$db->DisableTokenCheck();
			$db->query("UPDATE pages_page SET created_by=int:user_id, created_by_date=int:date WHERE id eq:int:pages_id",
			           $user_id, $time, $pages_id);
		}
	}

	//modified by and modified by date
	foreach ($pages_ids as $pages_id => $id)
	{
		$query = "SELECT t.userid, MAX(t.dt) as time, details, obj_id  FROM audit_log_txt t WHERE category='pages' AND (sub_cat = 'edit_page' OR sub_cat = 'checkin') AND obj_id = $id GROUP BY t.userid, details, obj_id ORDER BY time DESC";


		$res = $db->query($query);
		if ($res->hasData())
		{
			$row = $res->fetchArray();
			$user_id = $row['userid'];
			$time = $row['time'];

			$db->DisableTokenCheck();
			$db->query("UPDATE pages_page SET modified_by=int:user_id, modified_by_date=int:date WHERE id eq:int:pages_id",
			           $user_id, $time, $pages_id);
		}
	}

}
DB_UPDATE_FILE
);


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