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



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

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

$table_descr = array(
	'id'            => "IDENTITY",
	'head_id'        => "INT NOT_NULL",
    'title'          => "VARCHAR(255) NOT_NULL DEFAULT ' '",
    'slug'          => "VARCHAR(255) NOT_NULL DEFAULT ' '",
	'depth'        => "INT NOT_NULL",
	'show_above'    => "INT NOT_NULL DEFAULT 0"
);

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

$table_descr = array(
	'department_id' => "INT NOT_NULL",
	'user_id' => "INT NOT_NULL"
);

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



DB_UPDATE_FILE
);


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

$table_descr = array(
	'id' => "IDENTITY",
	'title' => "VARCHAR(255) NULL",
	'description' => "VARCHAR_MAX NULL",
	'priority' => "INT NOT_NULL",
	'homepage_url' => 'VARCHAR(1024) NULL',
	'base_vi' => 'VARCHAR(128) NULL',
);

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

// Theme 'view' permissions are in permissions table is own perm class and object_id=theme.id
// Theme 'forced' permissions are in permissions table is _different_ perm class and object_id=theme.id

// Selected theme in users table is stored as string equal to theme.code (added to theme table in later migration)

DB_UPDATE_FILE
);


$migrations->Run('02_orgchart_app_registration_and_admin.php', <<<'DB_UPDATE_FILE'
<?php
use Claromentis\Core\DAL;

/**
 * @var $migrations \Claromentis\Setup\SetupFacade
 */

// Enable the module's admin panel for the admin user
$migrations->GetAdminPanelCreator()->Enable('orgchart');


// Now add org chart to the applications menu
$db = $migrations->GetDb();
// get the current max value of display order
$res = $db->query('SELECT MAX(display_order) AS display_order FROM infobar_menu_item');
$display_order = $res->fetchAllValues();

$data = [
	'str:title' => 'Org Chart',
	'str:url' => 'orgchart',
	'int:display_order' => $display_order[0] + 1,
	'int:new_window' => 0,
	'int:menu_type' => 1, // 1 is InfobarMenuType::APPLICATIONS
	'int:item_type' => 1, // 1 is ItemType::APPLICATION
	'int:is_visible' => 1,
	'str:css_class' => null,
];
$query = new DAL\QueryInsert('infobar_menu_item', $data);
$db->query($query);

// create permissions entry for the new application menu item
$new_id = $db->insertId();
if ($new_id)
{
	$db->query('INSERT INTO permissions (class_id, object_id, perm_owner_class_id, perm_owner_id, rights) VALUES (27, int:new_id, 4, 0, 1)
						', $new_id);
}
DB_UPDATE_FILE
);


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

// This column will store JSON data for theme styles
$db->AddColumn('theme', 'style', 'CLOB');

// Theme screenshot should be in CDN theme/{theme_id_or_code}/screenshot.jpg
// Other images would be theme/{theme_id_or_code}/background.jpg and theme/{theme_id_or_code}/logo.jpg

DB_UPDATE_FILE
);


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

$db->AddColumn('theme', 'code', 'VARCHAR(128)');
$db->query("UPDATE theme SET code=".$db->concat_ws(':', 'str:theme', 'id'), 'theme');

DB_UPDATE_FILE
);


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

$res = $db->query("SELECT * FROM skins ORDER BY skin_id DESC");
$priority = 1;
$perm_class = 136; // \Claromentis\Design\Perm::CLASS_THEME
$obj_type_to_oclass = [
	"individual" => 1, //PERM_OCLASS_INDIVIDUAL
	"role" => 2, //PERM_OCLASS_ROLE
	"group" => 3, //PERM_OCLASS_GROUP
	"extranet" => 5, //PERM_OCLASS_EXAREA
];
while ($arr = $res->fetchArray())
{
	$skin_id = $arr['skin_id'];
	$code = $arr['skin_name'];

	if ($code === 'guest_default')
		continue;

	$q = new \Claromentis\Core\DAL\QueryInsert('theme', [
		'str:title' => $code,
		'str:description' => "VI $code",
		'int:priority' => $priority++,
		'str:homepage_url' => '',
		'str:base_vi' => $code,
		'str:code' => $code,
		'clob:style' => ''
	]);
	$db->query($q);
	$theme_id = $db->insertId();

	// now migrate permissions
	foreach ($obj_type_to_oclass as $otype=>$oclass)
	{
		$db->query("INSERT INTO permissions (class_id, object_id, perm_owner_class_id, perm_owner_id, rights)
				SELECT $perm_class, $theme_id, $oclass, obj_id, 1 FROM skin_permissions
					WHERE skin_id=int:id AND obj_type=str:otype AND available=1",
				$skin_id, $otype);
	}
}

DB_UPDATE_FILE
);


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

$res = $db->query("SELECT id, code FROM theme WHERE code LIKE '%:%'");
while ($arr = $res->fetchArray())
{
	$theme_code = $arr['code'];
	$theme_code_new = preg_replace("/[^\w\d]/", '_', $theme_code);
	if ($theme_code_new !== $theme_code)
	{
		$db->query("UPDATE theme SET code=str:new WHERE code=str:old", $theme_code_new, $theme_code);
		$db->query("UPDATE users SET skin=str:new WHERE skin=str:old", $theme_code_new, $theme_code);
	}
}

DB_UPDATE_FILE
);


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

$db->AddColumn("theme", "pages_url_id", "INT NULL");

// pages_url table may have column "full_url" or "name" 
list($pages_cnt) = @$db->query_row("SELECT COUNT(full_url) FROM pages_url"); // suppress error if pages app is not installed
if ($pages_cnt > 0)
{
	$url_column = 'full_url';
} else
{
	list($pages_cnt) = @$db->query_row("SELECT COUNT(name) FROM pages_url"); // suppress error if pages app is not installed
	$url_column = 'name';
}

if ($pages_cnt > 0)
{
	$res = $db->query("SELECT id, homepage_url FROM theme WHERE homepage_url IS NOT NULL");
	while (list($theme_id, $url) = $res->fetchRow())
	{
		list($url_id) = $db->query_row("SELECT id FROM pages_url WHERE $url_column eq:str:url", $url);
		if ($url_id > 0)
			$db->query("UPDATE theme SET pages_url_id=int:url_id WHERE id=int:theme_id", $url_id, $theme_id);
	}

	list($default_styles) = $db->query_row("SELECT var_value FROM variables WHERE var_name eq:str:varname", 'customization');
	if (!empty($default_styles))
	{
		$default_styles_arr = @json_decode($default_styles, true);
		if (is_array($default_styles_arr) && !empty($default_styles_arr['homepage_layout']))
		{
			$url = $default_styles_arr['homepage_layout'];
			list($url_id) = $db->query_row("SELECT id FROM pages_url WHERE $url_column eq:str:url", $url);
			if ($url_id > 0)
			{
				$default_styles_arr['homepage_layout'] = (int)$url_id;
			} else
			{
				unset($default_styles_arr['homepage_layout']);
			}

			$db->query("UPDATE variables SET var_value=str:new_val WHERE var_name eq:str:varname",
			           json_encode($default_styles_arr), 'customization');
		}
	}
}

DB_UPDATE_FILE
);


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

$db->DropColumn("theme", "homepage_url");

DB_UPDATE_FILE
);


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