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



$migrations->Run('20240902_stats_optimize_query_BT-4810.php', <<<'DB_UPDATE_FILE'
<?php

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

// add a new column (Mssql won't ALTER anew column without default value)
$db->AddColumn('stat_visits', 'day_group', 'INT NOT_NULL DEFAULT 0');

// 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');

// 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('20241003_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();

// 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');

DB_UPDATE_FILE
);


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