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



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

# IC-79 add column is_external_submission
$db->AddColumn('hd_project', 'is_external_submission', 'BOOL DEFAULT 0');


DB_UPDATE_FILE
);


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

// FRAM-185 - Local document content text extraction and caching in a MRU table
$columns = [
	'type' => 'VARCHAR(50) NULL',
	'file_hash' => 'VARCHAR(8) NULL',
	'file_size' => 'INT NOT_NULL DEFAULT 0',
	'content' => 'CLOB NULL',
	'content_size' => 'INT NOT_NULL DEFAULT 0',
	'date_accessed' => 'INT_DATE NOT_NULL DEFAULT 0'        // The last time some content with this hash was indexed
];
$db->CreateTable('indexing_file_content_cache', $columns);
$db->CreateIndex('indexing_file_content_cache', 'indexing_file_content_cache_file_hash_file_size_index', 'file_hash', 'file_size');
$db->CreateIndex('indexing_file_content_cache', 'indexing_file_content_cache_type_date_accessed', 'type', 'date_accessed');
$db->CreateIndex('indexing_file_content_cache', 'indexing_file_content_cache_date_accessed', 'date_accessed');

DB_UPDATE_FILE
);


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

// Done as part of FRAM-185 - Local document content text extraction to clean up old tables that have no reference in code anymore
try {
	$db->DropIndex('indexing_instruction', 'indexing_instruction_object');
	$db->DropTable('indexing_instruction');
} catch (\Throwable $throwable) {
	$logger->error("Error dropping table 'indexing_instruction': " . $throwable->getMessage());
	$logger->debug($throwable);
}

try {
	$db->DropTable('search_cache');
} catch (\Throwable $throwable) {
	$logger->error("Error dropping table 'search_cache': " . $throwable->getMessage());
	$logger->debug($throwable);
}

try {
	$db->DropIndex('search_cache2', 'cache2_index');
	$db->DropTable('search_cache2');
} catch (\Throwable $throwable) {
	$logger->error("Error dropping table 'search_cache2': " . $throwable->getMessage());
	$logger->debug($throwable);
}

try {
	$db->DropIndex('search_index_cache', 'search_index_cache_res_id');
	$db->DropIndex('search_index_cache', 'search_index_cache_query_pos');
	$db->DropIndex('search_index_cache', 'search_index_cache_word_id');
	$db->DropTable('search_index_cache');
} catch (\Throwable $throwable) {
	$logger->error("Error dropping table 'search_index_cache': " . $throwable->getMessage());
	$logger->debug($throwable);
}

DB_UPDATE_FILE
);


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

# IC-79 update column name to external_submission
$db->AlterColumn('hd_project', 'is_external_submission', 'external_submission');


DB_UPDATE_FILE
);


$migrations->Run('2023-03-08_add_comments_enabled_column_in_the_image_albums_table.php', <<<'DB_UPDATE_FILE'
<?php
/** @var SetupFacade $migrations */

use Claromentis\Setup\SetupFacade;

$db = $migrations->GetDb();

$db->AddColumn('image_albums', 'comments_enabled', 'BOOL NOT_NULL DEFAULT 1');

DB_UPDATE_FILE
);


$migrations->Run('2023-05-24_update_comments_enabled_column_in_the_image_albums_table.php', <<<'DB_UPDATE_FILE'
<?php
/** @var SetupFacade $migrations */

use Claromentis\Setup\SetupFacade;

$db = $migrations->GetDb();

$db->AlterColumn('image_albums', 'comments_enabled', 'comments_enabled', 'BOOL NOT_NULL DEFAULT 1');

DB_UPDATE_FILE
);


$migrations->Run('09012023_add_primary_key_id_and_user_agent_columns_to_onesignal_users_table.php', <<<'DB_UPDATE_FILE'
<?php
/**
 * @var $migrations \Claromentis\Setup\SetupFacade
 */

$db = $migrations->GetDb();
$db->AddColumn('onesignal_users', 'id', 'IDENTITY');
$db->AddColumn('onesignal_users', 'user_agent', 'VARCHAR(255)');

DB_UPDATE_FILE
);


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

$db->CreateIndex('hpl_request', 'hpl_request_date_start', 'date_start');
$db->CreateIndex('hpl_request', 'hpl_request_date_end', 'date_end');
$db->CreateIndex('hpl_request', 'hpl_request_day_id', 'day_id');
$db->CreateIndex('hpl_request', 'hpl_request_day_part', 'day_part');
$db->CreateIndex('hpl_request', 'hpl_request_user_id', 'user_id');
$db->CreateIndex('hpl_request', 'hpl_request_status', 'status');
$db->CreateIndex('hpl_request', 'hpl_request_date_requested', 'date_requested');
$db->CreateIndex('hpl_request', 'hpl_request_requested_by', 'requested_by');
$db->CreateIndex('hpl_request', 'hpl_request_date_approved', 'date_approved');
$db->CreateIndex('hpl_request', 'hpl_request_approved_by', 'approved_by');

DB_UPDATE_FILE
);


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

// Remove unnecessary indexes
try {
	$db->DropIndex('hpl_request', 'hpl_request_day_id');
	$db->DropIndex('hpl_request', 'hpl_request_day_part');
	$db->DropIndex('hpl_request', 'hpl_request_requested_by');
	$db->DropIndex('hpl_request', 'hpl_request_date_approved');
	$db->DropIndex('hpl_request', 'hpl_request_approved_by');
} catch (\Throwable $throwable) {
	$logger->error("Error dropping indexes from table 'hpl_request': " . $throwable->getMessage());
	$logger->debug($throwable);
}

// Add indexes for specific use cases

/** @see \HpViewRequestList::GetRepositoryFilter() */
$db->CreateIndex('hpl_request', 'hpl_request_user_id_status', 'user_id', 'status');

/** @see \HpProcessZonesData::RecalculateUserHoliday */
$db->CreateIndex('hpl_request', 'hpl_request_date_status', 'date_start', 'date_end', 'status');

/**
 * @see \HpToolKit::PreloadCurUsersDays()
 * @see \HpTeamCalendarProvider::GetRequestInfo()
 * @see \HpProcessZonesData::RecalculateUserHolidays()
 * @see \Claromentis\Holidays\Repository\RequestRepository::ApplyFilters()
 */
$db->CreateIndex('hpl_request', 'hpl_request_cur_user_days', 'date_start', 'date_end', 'user_id', 'status');

DB_UPDATE_FILE
);


$migrations->Run('20230320_add_hide_author_column_to_the_news_table.php', <<<'DB_UPDATE_FILE'
<?php
/**
 * @var $migrations \Claromentis\Setup\SetupFacade
 */

$db = $migrations->GetDb();
$db->AddColumn('news', 'hide_author', 'BOOL DEFAULT 0');

DB_UPDATE_FILE
);


$migrations->Run('20230621_remap_search_history_index.php', <<<'DB_UPDATE_FILE'
<?php
use Claromentis\Core\Services;
use Claromentis\Search;

/**
 * claromentis/product/core!1332 SRCH-15 Top search terms
 *
 * @var SetupFacade $migrations
 */
$logger = $migrations->getLogger();
$logger->notice('Remapping History Storage');

$core = Services::I()->container;

/**
 * @var Search\Indexing\Client\ClientInterface $indexer
 * @var Search\History\Storage $storage
 */
$indexer = $core['search.indexer'];
$storage = $core[Search\History\Storage::class];

$storage->InitMapping($indexer);

DB_UPDATE_FILE
);


$migrations->Run('20230822_add_enable_likes_feature_column.php', <<<'DB_UPDATE_FILE'
<?php
/**
 * @var $migrations \Claromentis\Setup\SetupFacade
 */

$db = $migrations->GetDb();

$db->AddColumn('hd_project', 'is_likes_enabled', 'INT NOT_NULL DEFAULT 0');

DB_UPDATE_FILE
);


$migrations->Run('20230823_enable_like_feature_for_projects.php', <<<'DB_UPDATE_FILE'
<?php
/**
 * @var $migrations \Claromentis\Setup\SetupFacade
 */

use Claromentis\Core\DAL\Exceptions\TransactionException;
use Claromentis\Core\DAL\Interfaces\DbInterface;

$db = $migrations->GetDb();
$logger = $migrations->getLogger();

$custom_ic_like_table = 'sd_ic_like';
$application_name = 'iclike';

$ic_like_config = $migrations->GetConfigFull($application_name);

/** Get the IC projects that are in use by IC Like custom module. */
$project_ids = $ic_like_config->get('cfg_ic_project_select') ?? [];

try {

	$db->DoTransaction(function (DbInterface $db) use ($application_name, $project_ids, $logger) {

		/** Check if the IC Like custom module is installed */
		$result = $db->query('SELECT 1 FROM db_installed WHERE application=str:app', $application_name);

		if (!$result->hasData()) {
			$logger->notice("IC Like app not installed: Skipping Likes migration from custom module to Core InfoCapture");
			return;
		}

		if (!is_array($project_ids) || empty($project_ids)) {
			$logger->warning("IC Likes feature is not enabled for any IC project in custom module, Skip enabling Like feature for InfoCapture projects");
			return;
		}

		// Update the equivalent projects in Core InfoCapture, and enable the Likes feature for them
		$db->query("UPDATE hd_project SET is_likes_enabled = int:is_likes_enabled WHERE id IN in:int:project_ids", 1, $project_ids);
	});

} catch (TransactionException $exception) {
	$logger->warning("IC Likes migration failed: Could not enabled Like feature for Core InfoCapture projects");
	$logger->debug($exception);
}




DB_UPDATE_FILE
);


$migrations->Run('20230823_migrate_likes_from_custom_table_into_like_component_table.php', <<<'DB_UPDATE_FILE'
<?php
/**
 * @var $migrations \Claromentis\Setup\SetupFacade
 */

use Claromentis\Core\DAL\Exceptions\TransactionException;
use Claromentis\Core\DAL\Interfaces\DbInterface;

$db     = $migrations->GetDb();
$logger = $migrations->getLogger();

$application_name = 'iclike';
$custom_ic_like_table = 'sd_ic_like';

/** @see AGGREGATION_IC_ISSUE */
$AGGREGATION_IC_ISSUE = 118;


try {
	$db->DoTransaction(function (DbInterface $db) use ($application_name, $custom_ic_like_table, $AGGREGATION_IC_ISSUE, $logger) {
		/** Check if the IC Like custom module is installed */
		$result = $db->query('SELECT 1 FROM db_installed WHERE application=str:app', $application_name);

		if (!$result->hasData()) {
			$logger->notice("IC Like app not installed: Skipping Likes migration from custom module to Core InfoCapture");
			return;
		}

		$query = "INSERT INTO likes (user_id, object_id, aggregation, timestamp)
			SELECT user_id, issue_id, {$AGGREGATION_IC_ISSUE}, date_created FROM {$custom_ic_like_table}";

		$db->query($query);
	});

} catch (TransactionException $exception) {
	$logger->warning("IC Likes migration failed: Likes have not been migrated from '{$custom_ic_like_table}' custom module table to 'Likes' table");
	$logger->error($exception);
}




DB_UPDATE_FILE
);


$migrations->Run('20230831_add_like_count_column_to_hd_issue_table.php', <<<'DB_UPDATE_FILE'
<?php
/**
 * @var $migrations \Claromentis\Setup\SetupFacade
 */

$db = $migrations->GetDb();

/** Add the `like_count` column to the `hd_issue` table, the column hold the total of likes */
$db->AddColumn('hd_issue', 'like_count', 'INT NOT_NULL DEFAULT 0');

DB_UPDATE_FILE
);


$migrations->Run('20230831_disable_ic_like_custom_module_upon_upgrade.php', <<<'DB_UPDATE_FILE'
<?php
/**
 * @var $migrations \Claromentis\Setup\SetupFacade
 */

use Claromentis\Core\Services;
use Claromentis\Core\Plugin\Api;

/** @var Api $plugin_api */
$plugin_api = Services::I()->{Api::class};

$plugin_code = 'iclike';

$plugin_api->disable($plugin_code);

DB_UPDATE_FILE
);


$migrations->Run('20230904_update_like_count_column_from_likes_table.php', <<<'DB_UPDATE_FILE'
<?php
/**
 * @var $migrations \Claromentis\Setup\SetupFacade
 */

use Claromentis\Core\DAL\Exceptions\TransactionException;
use Claromentis\Core\DAL\Interfaces\DbInterface;

$db     = $migrations->GetDb();
$logger = $migrations->getLogger();

/** @see AGGREGATION_IC_ISSUE */
$AGGREGATION_IC_ISSUE = 118;

/**
 * Migration to the update the ticket like_count column.
 * We count the total of likes for an issue from the likes table.
 */

try {
	$db->DoTransaction(function (DbInterface $db) use ($AGGREGATION_IC_ISSUE, $logger) {

		$sub_query = "(SELECT COUNT(*) FROM likes WHERE likes.aggregation = {$AGGREGATION_IC_ISSUE} AND likes.object_id = hd_issue.id)";

		$query = "UPDATE hd_issue SET like_count = {$sub_query}";

		$db->query($query);
	});

} catch (TransactionException $exception) {
	$logger->warning("IC Likes migration failed: Could not update the total of likes for an issue ('like_count' column)");
	$logger->error($exception);
}

DB_UPDATE_FILE
);


$migrations->Run('202312112232_helpdesk_rss_is_public.php', <<<'DB_UPDATE_FILE'
<?php

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

$database->AddColumn('hd_project', 'is_public_rss_feed', 'BOOL NOT_NULL DEFAULT 0');

DB_UPDATE_FILE
);


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