# Linea21 DB patch - UTF-8 format
# patch v1.5.x To v1.6


# ************** Warning **************
# If you use linea version < 1.2 apply first, patch_1.2.mysql.xx.sql, then patch_1.3.mysql.sql, patch_1.4.mysql.sql and patch_1.5.mysql.sql !!!
#
# USE linea;

# we remove AUTO_INCREMENT
ALTER TABLE `l21_profile` CHANGE `profile_id` `profile_id` INT UNSIGNED NOT NULL ;
ALTER TABLE `l21_rights` CHANGE `rights_id` `rights_id` INT UNSIGNED NOT NULL  ;


# adding the ability to change threshold on indicator
ALTER TABLE `l21_sdi_value` ADD `sdiv_threshold` FLOAT NULL DEFAULT NULL AFTER `sdiv_value`  ;

# adding the ability to display or not an indicator to the dashboard
ALTER TABLE `l21_sdi_info` ADD `sdii_to_dashboard` VARCHAR( 1 ) NOT NULL DEFAULT 'Y' AFTER `sdii_frequency`  ;

# adding timestamp to l21_j_work_users table
ALTER TABLE `l21_j_work_users` ADD `jwu_last_modify` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `jwu_user_right`  ;

# adding user_last_con filed  to l21_user table
ALTER TABLE `l21_user` ADD `user_last_con` DATETIME NOT NULL DEFAULT '0001-01-01 00:00:00' AFTER `user_last_modify`  ;

# adding plugins table
CREATE TABLE IF NOT EXISTS `l21_plugins` (  `plugin_id` int NOT NULL AUTO_INCREMENT,  `plugin_name` varchar(255) NOT NULL,  `plugin_values` text NOT NULL,  `plugin_date_crea` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',  `plugin_last_modify` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  PRIMARY KEY (`plugin_id`)) DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;
# changing default values
ALTER TABLE `l21_sdi_info` CHANGE `sdii_max_value` `sdii_max_value` FLOAT NULL DEFAULT NULL ,CHANGE `sdii_min_value` `sdii_min_value` FLOAT NULL DEFAULT NULL ,CHANGE `sdii_threshold_value` `sdii_threshold_value` FLOAT NULL DEFAULT NULL  ;
# adding new fields to l21_sdi_info for dataViz
ALTER TABLE `l21_sdi_info` ADD `sdii_type` VARCHAR( 255 ) NOT NULL DEFAULT 'quantitative' AFTER `sdii_id` , ADD `sdii_dashboard_viz` VARCHAR( 255 ) NOT NULL DEFAULT 'gauge' AFTER `sdii_type` , ADD `sdii_detail_viz` VARCHAR( 255 ) NOT NULL DEFAULT 'column' AFTER `sdii_dashboard_viz`  ;

# adding page table
CREATE TABLE IF NOT EXISTS `l21_page` ( page_id int NOT NULL AUTO_INCREMENT, page_parent_id int NOT NULL DEFAULT '0', page_title varchar(200) NOT NULL, page_header text NOT NULL, page_body text NOT NULL, page_range int NOT NULL DEFAULT '0', page_posted_by int NOT NULL, page_published_date datetime NOT NULL DEFAULT '0001-01-01 00:00:00', page_date_crea datetime NOT NULL DEFAULT '0001-01-01 00:00:00',   page_last_modify timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, page_status varchar(1) NOT NULL DEFAULT 'D', PRIMARY KEY (page_id),  FULLTEXT KEY page_index (page_title,page_header,page_body)) DEFAULT CHARSET=utf8 COLLATE utf8_general_ci ;
