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



$migrations->Run('20250801_1_stats_optimize_query_BT-4810.php', <<<'DB_UPDATE_FILE'
<?php

/**
 * BT-4810 Optimize query in `StatsReporter::GetDataVisits()`
 *
 * Reapplied for v10.
 *
 * @var \Claromentis\Setup\SetupFacade $migrations
 */
$db = $migrations->GetDb();
$logger = $migrations->getLogger();

try {
	// add a new column (Mssql won't ALTER anew column without default value)
	$db->AddColumn('stat_visits', 'day_group', 'INT NOT_NULL DEFAULT 0');
} catch (\Throwable $throwable) {
	$logger->warning($throwable->getMessage());
	$logger->debug($throwable);
}

try {
	// improve StatReporter::GetDataVisits()
	$db->CreateIndex('stat_visits', 'idx_date_time_start_session', 'date_time_start', 'session_key');
	// improve StatReporter::GetAverageTimeOnSite()
	$db->CreateIndex('stat_visits', 'idx_day_group', 'day_group');
} catch (\Throwable $throwable) {
	$logger->warning($throwable->getMessage());
	$logger->debug($throwable);
}

// Precompute day_group so we don't have to compute this for each row
$db->query('UPDATE stat_visits SET day_group = FLOOR(date_time_start / 1000000)');

DB_UPDATE_FILE
);


$migrations->Run('20250801_2_index_stat_views_BT-4810.php', <<<'DB_UPDATE_FILE'
<?php

/**
 * BT-4810 Optimize query in `StatsReporter::GetActionsCount()`
 *
 * @var \Claromentis\Setup\SetupFacade $migrations
 */
$db = $migrations->GetDb();
$logger = $migrations->getLogger();

try {
	// The method StatReporter::GetActionsCount() is called multiple times in the StatPluginContentProvider::GetModuleSummaryTemplateData()
	// adding the index will improve the loading page time significantly
	$db->CreateIndex('stat_views', 'idx_aggregation_date_time', 'aggregation', 'date_time');
	$db->CreateIndex('stat_updates', 'idx_aggregation_date_time', 'aggregation', 'date_time');
} catch (\Throwable $throwable) {
	$logger->warning($throwable->getMessage());
	$logger->debug($throwable);
}

DB_UPDATE_FILE
);


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