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



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

/** @var $migrations \Claromentis\Setup\SetupFacade */
$db = $migrations->GetDb();
$db->AddColumn('users_fields', 'show_padlock', 'INT NOT_NULL DEFAULT 0' );

$query= "SELECT field_name, count(*) as c FROM users_fields_perms GROUP BY field_name";
$res = $db->query($query);
while ($arr = $res->fetchArray())
{
	$field_name = $arr['field_name'];
	$query2 = "UPDATE users_fields SET show_padlock=int:value WHERE field_name=str:field_name";
	$db->query($query2, 1, $field_name);
}
DB_UPDATE_FILE
);


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

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

$table_description = array(
	'api_name' =>  'VARCHAR(32)',
	'api_description' => "VARCHAR(128) NOT_NULL DEFAULT 'Enter description' ",
	'api_help' => 'VARCHAR(512)',
	'json_value' => "VARCHAR(1500) NOT_NULL DEFAULT '{}'"
);

$db->CreateTable('integration', $table_description, true);

// no need for an id, primary index is on the api_name
$db->CreatePrimaryKey('integration', 'api_name');

// templates for initial keys which will be available in Claromentis
$initial_keys = array(
	'google_maps' => ['Google Maps API Key','Created in the Google Developer console','{"api_key":"xxxxxxxx"}'],
	'google_picker' => ['Google Picker key and OAuth details','Needs a client_id and app_id as well as the developer_key. All can be created in the Google Developer console.','{"developer_key":"xxxxxxxxxx","client_id":"xxxxxxxxx","app_id":"xxxxxxxxx"}'],
);

foreach	($initial_keys as $key => $value)
{
	$db->query('INSERT INTO integration VALUES (str:api_name, str:description, str:help_text, str:json_data)', $key, $value[0], $value[1], $value[2]);
}

DB_UPDATE_FILE
);


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

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

$desc = [
	'id' => 'IDENTITY',
	'aggregation' => 'INT NOT_NULL DEFAULT 0',
	'object_id' => 'INT NOT_NULL DEFAULT 0',
	'read_only' => 'BOOL NOT_NULL DEFAULT 0',
	'name' => 'VARCHAR(255)',
	'position' => 'INT NOT_NULL DEFAULT 0',
	'bitmask' => 'INT NOT_NULL DEFAULT 0'
];

$db->CreateTable('acl_role_name', $desc);

$desc = [
	'id' => 'IDENTITY',
	'aggregation' => 'INT NOT_NULL DEFAULT 0',
	'object_id' => 'INT NOT_NULL DEFAULT 0',
	'name' => 'VARCHAR(255)',
	'roles' => 'INT NOT_NULL DEFAULT 0'
];

$db->CreateTable('acl_role_right', $desc);
DB_UPDATE_FILE
);


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

$db->AddColumn('hpl_days', "user_comment_mandatory", "BOOL NOT_NULL DEFAULT 1");

$db->query("UPDATE hpl_days SET user_comment_mandatory=0 WHERE name=str:dtn", 'Sickness day');

DB_UPDATE_FILE
);


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

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

$config = $migrations->GetConfigFull('core');
$doc_keys = $config->Get('cfg_docs_google_auth');

if (!empty($doc_keys['client_id']) && !empty($doc_keys['client_secret']))
{
	$new_key_json = json_encode(array('client_id' => $doc_keys['client_id'], 'client_secret' => $doc_keys['client_secret']));

	// check any key data that may already be in the integration table
	$result = $db->query("SELECT * FROM integration WHERE api_name = 'docs_google_auth'");
	$entry = null;
	$entry_data = null;
	if ($result->hasData())
	{
		$entry = $result->fetchArray();
		$entry_data = json_decode($entry['json_value'], true);
	}

	if ($entry === null && $entry_data === null)
	{
		// no entry exists, create it.
		$db->query("INSERT INTO integration VALUES ('docs_google_auth', 'Google OAuth for Documents', 'Allows gathering additional information about a document when adding from Google Docs', str:key_json)", $new_key_json);
	}
	else
	{
		// check key value and update if it's the default value (all x's)
		if ((!empty($entry_data['client_id']) && !empty($entry_data['client_secret'])) && preg_match('/^x+$/', $entry_data['client_id']) && preg_match('/^x+$/', $entry_data['client_secret']))
		{
			$db->query("UPDATE integration SET json_value = str:new_key_json WHERE api_name='docs_google_auth'", $new_key_json);
		}
		// don't change existing values that aren't the default.
	}
}
else
{
	$new_key_json = json_encode(array('client_id' => 'xxxxxxxxxxxxxxx', 'client_secret' => 'xxxxxxxxxxxxxxx'));
	// no entry exists, create it with default values.
	$db->query("INSERT INTO integration VALUES ('docs_google_auth', 'Google OAuth for Documents', 'Allows gathering additional information about a document when adding from Google Docs', str:key_json)", $new_key_json);
}




DB_UPDATE_FILE
);


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

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

$db->RenameTable('acl_role_name', 'acl_role');
$db->AlterColumn('acl_role', 'bitmask', 'rights');
$db->DropTable('acl_role_right');
DB_UPDATE_FILE
);


$migrations->Run('03_initialise_mailserver_domain_table.php', <<<'DB_UPDATE_FILE'
<?php

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

$table_descr = array(
	'id'			=>	"IDENTITY",
	'domain'		=>	"VARCHAR_MAX NULL",
);

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


DB_UPDATE_FILE
);


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

$db->query("DELETE FROM ERMS_metadata_link WHERE name=str:cus_type OR name=str:cus_id", 'custodian_type', 'custodian_id');
$db->query("DELETE FROM ERMS_metadata_inherit WHERE name=str:cus_type OR name=str:cus_id", 'custodian_type', 'custodian_id');
$db->query("DELETE FROM ERMS_metadata_element WHERE name=str:cus_type OR name=str:cus_id", 'custodian_type', 'custodian_id');

DB_UPDATE_FILE
);


$migrations->Run('04_ic_switch_to_mailgun.php', <<<'DB_UPDATE_FILE'
<?php

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

//$db->DropColumn('hd_project', 'mail_account_id');
$db->AddColumn('hd_project', 'use_mail_service', 'INT DEFAULT 0');
DB_UPDATE_FILE
);


$migrations->Run('05_ic_mailgun_recipient.php', <<<'DB_UPDATE_FILE'
<?php

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

$db->AddColumn('hd_project', 'mailgun_recipient_address', 'VARCHAR(255) DEFAULT NULL');
DB_UPDATE_FILE
);


$migrations->Run('06_mailgun_support_html_fields.php', <<<'DB_UPDATE_FILE'
<?php

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

$db->AddColumn('hd_project', 'mailgun_use_html_field', 'BOOL NOT_NULL DEFAULT 0');
DB_UPDATE_FILE
);


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

$db->DropIndex('ERMS_file_plan', 'ERMS_file_plan_refcode');

DB_UPDATE_FILE
);


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