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



$migrations->Run('2310_password_recovery_refactor.php', <<<'DB_UPDATE_FILE'
<?php

try {
	$USER_AGGREGATION = 27;

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

	$columns = [
		'id'            => 'IDENTITY',
		'email_address' => 'VARCHAR(200)',
		'expiry'        => 'INT_DATE NOT_NULL DEFAULT 0',
		'recovery_key'  => 'VARCHAR(32)'
	];
	$db->CreateTable('password_recovery', $columns);

	// Migrate existing keys
	$res = $db->query("SELECT id, object_id, txtval FROM metadata_simple WHERE aggregation eq:int:user_aggregation AND name='password_recovery_key'", $USER_AGGREGATION);
	$key_ids = [];
	while ($arr = $res->fetchArray())
	{
		$key_ids[]         = (int)$arr['id'];
		$user_id           = (int)$arr['object_id'];
		[$key, $timestamp] = explode('_', $arr['txtval']);

		// Look up the users email address
		$user_res = $db->query('SELECT emailad FROM users WHERE id eq:int:user_id', $user_id);
		if ($user_res->numRows() < 1)
			{continue;}
		$user = $user_res->fetchArray();
		$email_address = $user['emailad'];

		// Convert the time to the Claromentis format
		$expiry = new \Date($timestamp);

		$row = [
			'str:email_address' => $email_address,
			'int:expiry'        => $expiry->getDate(DATE_FORMAT_TIMESTAMP),
			'str:recovery_key'  => $key
		];
		$db->unsafe_query = true;
		$db->query(new \Claromentis\Core\DAL\QueryInsert('password_recovery', $row));
	}

	// Remove the metadata as it is no longer needed
	if (count($key_ids) > 0)
	{
		$db->unsafe_query = true;
		$db->query('DELETE FROM metadata_simple WHERE id IN in:int:key_ids', $key_ids);
	}
} catch (\Throwable $throwable) {
	$logger = $migrations->getLogger();

	$logger->warning($throwable->getMessage());
	$logger->debug($throwable);
}

DB_UPDATE_FILE
);


$migrations->Run('202310_statistics_optimisations_90.php', <<<'DB_UPDATE_FILE'
<?php

try {
	/**
	 * FRAM-935 Statistics optimisations
	 *
	 * @var \Claromentis\Setup\SetupFacade $migrations
	 */
	$database = $migrations->GetDb();

	// Originally implemented in 8.13 in web/intranet/setup/_init/20230830_statistics_optimisations.php

	// stat_views
	$database->CreateIndex('stat_views', 'stat_views_id_aggregation_date_time', 'object_id', 'aggregation', 'date_time');
	$database->CreateIndex('stat_views', 'stat_views_id_aggregation_date_time_user_id', 'object_id', 'aggregation', 'date_time', 'user_id');

	// stat_visits
	$database->CreateIndex('stat_visits', 'idx_session_key_date_time_start', 'session_key', 'date_time_start');
	$database->CreateIndex('stat_visits', 'idx_date_time_start', 'date_time_start');

	// Originally implemented in 8.13 in web/intranet/setup/_init/20230912_statistics_optimisations.php

	// stat_visits_sum
	$database->CreateIndex('stat_visits_sum', 'idx_date_time_user_id', 'date_time', 'user_id');
	$database->CreateIndex('stat_visits_sum', 'idx_date_time_pages_visited', 'date_time', 'pages_visited');
} catch (\Throwable $throwable) {
	$logger = $migrations->getLogger();

	$logger->warning($throwable->getMessage());
	$logger->debug($throwable);
}

DB_UPDATE_FILE
);


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