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



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

// pages_site
$table_descr = array(
    'id'     => "IDENTITY",
    'title'  => "VARCHAR(256)",
    'url_id' => "INT NULL",
);
$db->CreateTable('pages_site', $table_descr, true);

// pages_menu_item
$table_descr = array(
    'id'         => "IDENTITY",
    'site_id'    => "INT NOT_NULL",
    'parent_id'  => "INT NOT_NULL DEFAULT 0",
    'title'      => "VARCHAR(256)",
    'type'       => "INT NOT_NULL",
    'link_url'   => "VARCHAR(1024) NULL",
    'url_id'     => "INT NULL",
    'new_window' => "BOOL NOT_NULL DEFAULT 0",
    'is_visible' => "BOOL NOT_NULL DEFAULT 1",
    'display_order' => "INT NOT_NULL",
);
$db->CreateTable('pages_menu_item', $table_descr, true);

$db->AlterColumn('pages_url', 'name', 'full_url', 'VARCHAR(1024)');
$db->AddColumn('pages_url', 'site_id', 'INT NULL');


$db->query(new Claromentis\Core\DAL\QueryInsert('pages_url', array(
	'str:full_url' => 'main',
	'int:active_page_id' => 0, // to be set later
	'int:latest_page_id' => 0, // to be set later
	'int:site_id' => 0, // to be set later
)));
$url_id = $db->insertId();

$db->query("INSERT INTO pages_site (title, url_id) VALUES ('Main', int:url_id)", $url_id);
$site_id = $db->insertId();

$db->query(new \Claromentis\Core\DAL\QueryInsert('pages_page', array(
	'str:title' => "Main",
	'int:url_id' => $url_id,
)));
$page_id = $db->insertId();

$db->query("UPDATE pages_url SET site_id=int:s_id, active_page_id=int:a_id, latest_page_id=int:l_id
            WHERE id=int:url_id",
           $site_id, $page_id, $page_id, $url_id);

//list($site_id) = $db->query("SELECT id FROM pages_site")->fetchAllValues();
$db->query("UPDATE pages_url SET site_id=int:s_id", $site_id);
$db->query("UPDATE pages_url SET full_url=".$db->concat_ws('/', "'main'", 'full_url')." WHERE full_url<>'main'");

DB_UPDATE_FILE
);


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

$stored_design = \DBVar::Get('customization');
if ($stored_design !== false)
{
	$stored_design_data = json_decode($stored_design, true);
	if (is_array($stored_design_data))
	{
		if (isset($stored_design_data['home_layout']))
			unset($stored_design_data['home_layout']);
		if (isset($stored_design_data['homepage_layout']) && in_array($stored_design_data['homepage_layout'], ['Simple', 'Mixed', 'News', 'Social']))
			$stored_design_data['homepage_layout'] = 'main/'.$stored_design_data['homepage_layout'];

		DBVar::Set('customization', json_encode($stored_design_data, JSON_PRETTY_PRINT));
	}
}

DB_UPDATE_FILE
);


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

$res = $db->query("SELECT id FROM pages_site");
$all_sites = $res->fetchAllValues();

$res = $db->query("SELECT perm_owner_class_id, perm_owner_id FROM permissions WHERE class_id=126"); // 126 is pages admin
$all_perms = [];
while (list($oclass, $oid) = $res->fetchRow())
{
	$all_perms[] = [$oclass, $oid];
}

foreach ($all_sites as $site_id)
{
	$site_id = (int)$site_id;

	// give full permissions to site to those who have rights to add sites
	foreach ($all_perms as $p_info)
	{
		$db->query("INSERT INTO permissions (class_id, object_id, perm_owner_class_id, perm_owner_id, rights) VALUES
			(int:class, int:site, int:oclass, int:oid, int:rights)",
			132 /* CLASS_SITE */, $site_id, $p_info[0], $p_info[1], 23);
			// 23 is Perm::VIEW | Perm::EDIT_DRAFT | Perm::APPROVE | Perm::EDIT_PERMS
	}

	// make site visible to everyone
	$db->query("INSERT INTO permissions (class_id, object_id, perm_owner_class_id, perm_owner_id, rights) VALUES
			(int:class, int:site, int:oclass, int:oid, int:rights)",
	        132 /* CLASS_SITE */, $site_id, 4 /* OCLASS_ALL */, 1, 1 /* PERM_VIEW */);
}

DB_UPDATE_FILE
);


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

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

// Build simple site tree
$res = $db->query('SELECT pages_url.id pages_url_id, pages_url.*, pages_page.* FROM pages_url JOIN pages_page ON pages_url.active_page_id = pages_page.id');

$page_urls = [];
while ($arr = $res->fetchArray())
{
	$page_urls[] = $arr;
}

$sites = [];

// First pass - get root pages
foreach ($page_urls as $page_url)
{
	if (strpos($page_url['full_url'], '/') == false)
	{
		$page_url['children'] = [];
		$sites[$page_url['full_url']] = $page_url;
	}
}

// Second pass - get first-level pages
foreach ($page_urls as $page_url)
{
	if (strpos($page_url['full_url'], '/') === false)
		continue;

	$levels = explode('/', $page_url['full_url']);

	if (count($levels) === 2)
	{
		// Ignore any without root level pages
		if (!array_key_exists($levels[0], $sites))
			continue;

		$page_url['children'] = [];
		$sites[$levels[0]]['children'][$levels[1]] = $page_url;
	}
}
// Third pass - get second-level pages
foreach ($page_urls as $page_url)
{
	if (strpos($page_url['full_url'], '/') === false)
		continue;

	$levels = explode('/', $page_url['full_url']);

	if (count($levels) === 3)
	{
		// Ignore any without parent pages
		if (!array_key_exists($levels[0], $sites))
			continue;
		if (!array_key_exists($levels[1], $sites[$levels[0]]['children']))
			continue;

		$sites[$levels[0]]['children'][$levels[1]]['children'][$levels[2]] = $page_url;
	}
}

// Save as menu items

foreach ($sites as $site)
{
	$values = [
		'int:site_id' => (int)$site['site_id'],
		'int:parent_id' => 0,
		'str:title' => $site['title'],
		'int:type' => 1, // page_link
		'int:url_id' => (int)$site['pages_url_id'],
		'int:is_visible' => 1,
		'int:display_order' => 0
	];
	$db->unsafe_query = true;
	$qi = new DAL\QueryInsert('pages_menu_item', $values);
	$db->query($qi);
	$top_id = $db->insertId();

	$level_1_order = 1;
	foreach ($site['children'] as $level_1)
	{
		$values = [
			'int:site_id' => (int)$level_1['site_id'],
			'int:parent_id' => $top_id,
			'str:title' => $level_1['title'],
			'int:type' => 1, // page_link
			'int:url_id' => (int)$level_1['pages_url_id'],
			'int:is_visible' => 1,
			'int:display_order' => $level_1_order
		];
		$db->unsafe_query = true;
		$qi = new DAL\QueryInsert('pages_menu_item', $values);
		$db->query($qi);
		$level_1_id = $db->insertId();

		$level_1_order++;

		$level_2_order = 1;
		foreach ($level_1['children'] as $level_2)
		{
			$values = [
				'int:site_id' => (int)$level_2['site_id'],
				'int:parent_id' => $level_1_id,
				'str:title' => $level_2['title'],
				'int:type' => 1, // page_link
				'int:url_id' => (int)$level_2['pages_url_id'],
				'int:is_visible' => 1,
				'int:display_order' => $level_2_order
			];
			$db->unsafe_query = true;
			$qi = new DAL\QueryInsert('pages_menu_item', $values);
			$db->query($qi);

			$level_2_order++;
		}
	}
}
DB_UPDATE_FILE
);


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

$db->AddColumn('pages_page', 'show_title', 'BOOL DEFAULT 1');
$db->AddColumn('pages_page', 'show_menu', 'BOOL DEFAULT 1');

// Pages 1.0 didn't have title, so we should not show it for any existing page
$db->query("UPDATE pages_page SET show_title=0, show_menu=0");

DB_UPDATE_FILE
);


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

$table_descr = array(
	'id'      => "IDENTITY",
	'secret'  => "VARCHAR(128)",
	'content' => "CLOB",
);
$db->CreateTable('pages_unsafe_string', $table_descr, true);

DB_UPDATE_FILE
);


$migrations->Run('07_text_component_storage.php', <<<'DB_UPDATE_FILE'
<?php

global $APPDATA;
if (!is_dir("$APPDATA/pages_text_component"))
{
	mkdir("$APPDATA/pages_text_component", 0777, true);
	mkdir("$APPDATA/pages_text_component/images", 0777, true);
}
DB_UPDATE_FILE
);


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