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



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

$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);
}

DB_UPDATE_FILE
);


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