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



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

$db->query("UPDATE pages_tile SET class_key=LOWER(class_key)");

$db->query("UPDATE pages_tile SET class_key=str:new WHERE class_key=str:old", 'calendar.minicalendar', 'minicalendar');
$db->query("UPDATE pages_tile SET class_key=str:new WHERE class_key=str:old", 'calendar.upcoming_events', 'calendarupcomingevents');
$db->query("UPDATE pages_tile SET class_key=str:new WHERE class_key=str:old", 'news.list', 'homepagenews');
$db->query("UPDATE pages_tile SET class_key=str:new WHERE class_key=str:old", 'communication.surveys', 'homepagesurveyslist');
$db->query("UPDATE pages_tile SET class_key=str:new WHERE class_key=str:old", 'communication.polls', 'homepagepollslist');
$db->query("UPDATE pages_tile SET class_key=str:new WHERE class_key=str:old", 'news.announcements', 'announcements');
$db->query("UPDATE pages_tile SET class_key=str:new WHERE class_key=str:old", 'people.myprofilelink', 'myprofilelink');
$db->query("UPDATE pages_tile SET class_key=str:new WHERE class_key=str:old", 'whatsnew.list', 'homepagewhatsnew');

$db->query("UPDATE pages_tile SET class_key=str:new WHERE class_key=str:old", 'knowledgebase.recent_articles', 'knowledgebaserecentarticles');
$db->query("UPDATE pages_tile SET class_key=str:new WHERE class_key=str:old", 'knowledgebase.recent_questions', 'knowledgebaserecentquestions');

$db->query("UPDATE pages_tile SET class_key=str:new WHERE class_key=str:old", 'policy.awaiting_action', 'policyawaitingaction');

$db->query("UPDATE pages_tile SET class_key=str:new WHERE class_key=str:old", 'projects.my_projects', 'projectsmyprojects');
$db->query("UPDATE pages_tile SET class_key=str:new WHERE class_key=str:old", 'projects.my_tasks', 'projectsmytasks');

$db->query("UPDATE pages_tile SET class_key=str:new WHERE class_key=str:old", 'social.feed', 'socialcomponent');

$db->query("UPDATE pages_tile SET class_key=str:new WHERE class_key=str:old", 'innovate.channel_live', 'innovatecomponentlive');

$db->query("UPDATE pages_tile SET class_key=str:new WHERE class_key=str:old", 'anniversary', 'anniversary');

$db->query("UPDATE pages_tile SET class_key=str:new WHERE class_key=str:old", 'thankyou', 'thankyoucomponent');

$db->query("UPDATE pages_tile SET class_key=str:new WHERE class_key=str:old", 'peoplecomponent', 'peoplecomponent');


DB_UPDATE_FILE
);


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

$db->AddColumn('pages_page', 'title', 'VARCHAR(1024)');
$db->query("UPDATE pages_page SET title=name");

$db->AddColumn('pages_page', 'version', 'INT NOT_NULL DEFAULT 1');

$db->AddColumn('pages_page', 'is_draft', 'BOOL NOT_NULL DEFAULT 0');

$db->AddColumn('pages_page', 'primary_page_id', 'INT NULL');
$db->query("UPDATE pages_page SET primary_page_id=id");

$db->AddColumn('pages_page', 'latest_version_id', 'INT NULL');
$db->query("UPDATE pages_page SET latest_version_id=id");

DB_UPDATE_FILE
);


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

$db->AddColumn('pages_page', 'pgroup_id', 'INT');
$db->query("UPDATE pages_page SET pgroup_id=id");

DB_UPDATE_FILE
);


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

$db->AlterColumn('pages_page', 'primary_page_id', 'active_version_id');

DB_UPDATE_FILE
);


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

// pages_url
$table_descr = array(
	'id'	=>	"IDENTITY",
	'name'	=>	"VARCHAR(64)",
	'active_page_id'	=>	"INT",
	'latest_page_id'	=>	"INT",
);

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

$db->AddColumn('pages_page', 'url_id', 'INT');


$r = $db->query("SELECT id, name, active_version_id, latest_version_id, pgroup_id FROM pages_page");
$gid2uid = [];
while ($arr = $r->fetchArray())
{
	if (!isset($gid2uid[$arr['pgroup_id']]))
	{
		$db->query("INSERT INTO pages_url (name, active_page_id, latest_page_id) VALUES (str:name, int:aid, int:lid)",
		           $arr['name'], $arr['active_version_id'], $arr['latest_version_id']);
		$url_id = $db->insertId();;
		if (!$url_id)
		{
			$migrations->Log("ERROR: failed to add a record to pages_url");
		}
		$gid2uid[$arr['pgroup_id']] = $url_id;
	}

	$db->query("UPDATE pages_page SET url_id=int:uid WHERE id=int:pid", $gid2uid[$arr['pgroup_id']], $arr['id']);
}

//$db->query("INSERT INTO pages_url (name, active_page_id, latest_page_id)
//            SELECT DISTINCT name, active_version_id, latest_version_id FROM pages_page");

$db->DropColumn('pages_page', 'active_version_id');
$db->DropColumn('pages_page', 'latest_version_id');
$db->DropColumn('pages_page', 'name');
$db->DropColumn('pages_page', 'pgroup_id');


DB_UPDATE_FILE
);


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