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



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

/* Copy value from string to int for TYPE_DATE_PICKER */
$res = $db->query("SELECT DISTINCT sym_name FROM fb_field WHERE type=20");
if ($res->numRows() > 0)
{
	$names = $res->fetchAllValues();

	if ($db->type() == 'mysql')
		$db->query("UPDATE IGNORE fb_field_data SET value_int=value WHERE field_sym_name IN in:str:symname AND value_int IS NULL AND value REGEXP '^-{0,1}[0-9]+$'", $names);
	else
		$db->query("UPDATE fb_field_data SET value_int=CAST(value AS INT) WHERE field_sym_name IN in:str:symname AND value_int IS NULL AND ISNUMERIC(value + '.0e0')=1", $names);
}

DB_UPDATE_FILE
);


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

// note, all errors suppressed as these indexes might already exist in the db
// added by migration to 7.4.15

// DELETE FROM search_query_params WHERE last_used<1470234692;
@$db->CreateIndex('search_query_params', 'search_query_params_last_used', 'last_used');

// SELECT FROM metadata_set WHERE set_type=48;
@$db->CreateIndex('metadata_set', 'metadata_set_type', 'set_type');

// SELECT FROM news_translations WHERE news_id =1 AND language ='en';
@$db->CreateIndex('news_translations', 'news_translations_news_id_lang', 'news_id', 'language');

// SELECT FROM infobar_menu_personal WHERE user_id=1 ORDER BY display_order;
@$db->CreateIndex('infobar_menu_personal', 'infobar_menu_personal_user_id', 'user_id');

// SELECT FROM users WHERE last_time_used > 1470237692 AND (intranetuser<>'no') ORDER BY last_time_login DESC LIMIT 11;
//@$db->CreateIndex('users', 'users_state', 'intranetuser', 'last_time_used');
// not sure if it's good to have this index, as the column is regularly updated

// SELECT FROM ERMS_metadata_field WHERE name ='news_source';
@$db->CreateIndex('ERMS_metadata_field', 'ERMS_metadata_field_name', 'name');

// SELECT FROM roles r, user_roles ur WHERE r.roleid = ur.roleid AND ur.userid = 1 ORDER BY r.rolename;
@$db->CreateIndex('user_roles', 'user_roles_userid', 'userid');

// SELECT FROM skin_permissions WHERE skin_id=1 AND obj_type ='individual' AND obj_id IN (1) GROUP BY available;
@$db->CreateIndex('skin_permissions', 'skin_permissions_sid_otype_oid', 'skin_id', 'obj_type', 'obj_id');
@$db->DropIndex('skin_permissions', 'skin_permissions_id');

// SELECT FROM publish_menunode WHERE menu_id=1 AND parent_id=0 AND visible>0 ORDER BY order_index;
@$db->CreateIndex('publish_menunode', 'publish_menunode_mid_pid_vis', 'menu_id', 'parent_id');

// SELECT FROM pub_page WHERE last_modified_by=1 AND version=-1 AND deleted=0 ORDER BY date_last_modified;
@$db->CreateIndex('pub_page', 'pub_page_deleted_version', 'deleted', 'version');

// SELECT FROM pub_page WHERE title ='about_us.php' AND cat_id=2 AND id=primary_page_id AND deleted=0;
@$db->CreateIndex('pub_page', 'pub_page_del_parent_title', 'deleted', 'cat_id', 'title');

// SELECT FROM pub_category WHERE name ='main' AND parent=0 AND deleted=0;
@$db->CreateIndex('pub_category', 'pub_category_del_parent_name', 'deleted', 'parent', 'name');

// SELECT FROM pub_template_property, pub_element LEFT JOIN pub_element_property ON pub_element.id = pub_element_property.content_id WHERE pub_element.page_id = 1
// AND (pub_element.language  IS NULL OR pub_element.language  IS NULL OR pub_element.language IS NULL) AND pub_template_property.template_id = 1
@$db->CreateIndex('pub_element', 'pub_element_page_id', 'page_id');
@$db->CreateIndex('pub_element_property', 'pub_element_property_content_id', 'content_id');

// SELECT FROM pub_template_property WHERE template_id=1;
@$db->CreateIndex('pub_template_property', 'pub_template_property_tmpl_id', 'template_id');

// SELECT FROM hpl_users WHERE userid IN (0) AND year<=2016 AND hld_total >= 0 ORDER BY year DESC;
@$db->CreateIndex('hpl_users', 'hpl_users_year_userid', 'year', 'userid');

// SELECT FROM cln_event ce, cln_seq_reminder csr WHERE
// 			ce.date_start >= 20160803153200 AND (
// 				(csr.rem_time > 2000 AND csr.seq_id = ce.seq_id AND ce.date_start <= 20160810153201 AND
// 					UNIX_TIMESTAMP(STR_TO_DATE(ce.date_start, '%Y%m%d%H%i%s')) -csr.rem_time*60 <= 1470238320) OR
// 				(ce.date_start <= 20160804153201 AND csr.seq_id = ce.seq_id AND
// 					UNIX_TIMESTAMP(STR_TO_DATE(ce.date_start, '%Y%m%d%H%i%s')) -csr.rem_time*60 <= 1470238320) );
@$db->CreateIndex('cln_event', 'cln_event_date_start', 'date_start');

// SELECT FROM cln_sended_reminders WHERE hash IN ('default');
@$db->CreateIndex('cln_sended_reminders', 'cln_sended_reminders_hash', 'hash');

// SELECT FROM cln_sys_event_options WHERE sended_flag = 0 AND reminder_date < 20160803153201;
@$db->CreateIndex('cln_sys_event_options', 'cln_sys_event_opt_date', 'reminder_date', 'sended_flag');
@$db->CreateIndex('cln_sys_event_options', 'cln_sys_event_opt_event_user', 'event_key', 'user_id');


// SELECT FROM users_fields WHERE users_list_order<>-1 ORDER BY users_list_order ASC;
// SELECT FROM users_fields WHERE advanced_search_order<>-1 ORDER BY advanced_search_order ASC;
@$db->CreateIndex('users_fields', 'users_fields_list_order', 'users_list_order');
@$db->CreateIndex('users_fields', 'users_fields_search_order', 'advanced_search_order');

// SELECT FROM users LEFT JOIN extranet_areas ea ON users.ex_area_id = ea.id WHERE intranetuser IN ('loc', 'yes')   ORDER BY users.firstname ASC, users.surname ASC  LIMIT 0,24;
@$db->CreateIndex('users', 'users_state', 'intranetuser');
@$db->CreateIndex('users', 'users_username', 'username');

DB_UPDATE_FILE
);


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