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


# ************** Warning **************
# Apply previous patch to Linea21, before applying this one
#
# USE linea;

ALTER TABLE `l21_plugins` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

# increase user_password field value to 200 char - (100 chars allow from UI)
ALTER TABLE `l21_user` CHANGE `user_password` `user_password` VARCHAR( 200 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ;

# Add new fields to project table
ALTER TABLE `l21_project` ADD `project_goal` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL AFTER `project_name` ;
ALTER TABLE `l21_project` ADD `project_budget_comment` TEXT NULL DEFAULT NULL AFTER `project_budget` ;
ALTER TABLE `l21_project` ADD `project_comment` TEXT NULL DEFAULT NULL AFTER `project_workshop_id` ;
ALTER TABLE `l21_project` ADD `project_elected` VARCHAR( 255 ) NULL DEFAULT NULL AFTER `project_budget_comment` ;
ALTER TABLE `l21_project` ADD `project_team` TEXT NULL DEFAULT NULL AFTER `project_elected` ;
ALTER TABLE `l21_project` DROP INDEX project_name;
ALTER TABLE `l21_project` ADD FULLTEXT KEY project_index (`project_name`,`project_goal`,`project_description`,`project_body`,`project_comment`,`project_budget_comment`, `project_elected`, `project_team`);

# implement indicators multi-values
ALTER TABLE `l21_sdi_info` ADD `sdii_value_type` VARCHAR( 50 ) NOT NULL DEFAULT 'unique' AFTER `sdii_detail_viz` ;
ALTER TABLE `l21_sdi_value` ADD `sdiv_multivalue` TEXT NOT NULL AFTER `sdiv_value` ;

# ADD 'O' option on rights_project colonn
ALTER TABLE `l21_rights` CHANGE `rights_project` `rights_project` ENUM( 'A','O','U' ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'U';

# ADD user login to values
ALTER TABLE  `l21_sdi_value` ADD  `sdiv_user_id` INT( 11 ) NOT NULL DEFAULT  '1' AFTER  `sdiv_sdi_info` 

# Adding tags table
CREATE TABLE IF NOT EXISTS `l21_tag` (  `tag_id` int(11) NOT NULL AUTO_INCREMENT,  `tag_name` varchar(255) NOT NULL,  `tag_comment` text NOT NULL,  `tag_date_crea` datetime NOT NULL,  `tag_last_modify` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  PRIMARY KEY (`tag_id`)) DEFAULT CHARSET=utf8mb4 ;

CREATE TABLE IF NOT EXISTS `l21_j_tag` (  `jta_id` int(11) NOT NULL AUTO_INCREMENT,  `jta_module` varchar(100) NOT NULL,  `jta_module_id` int(11) NOT NULL,  `jta_tag_id` int(11) NOT NULL,  PRIMARY KEY (`jta_id`)) DEFAULT CHARSET=utf8mb4 ;


# adding manager field for Project module
CREATE TABLE IF NOT EXISTS `l21_j_project_manager` (  `jpm_id` int(11) NOT NULL AUTO_INCREMENT,  `jpm_project_id` int(11) NOT NULL,  `jpm_manager_id` int(11) NOT NULL,  PRIMARY KEY (`jpm_id`)) DEFAULT CHARSET=utf8mb4 ;

# removing Levels
# DROP TABLE l21_level ; // removed because it caused problem when re-introducing l21_level with 1.9
ALTER TABLE `l21_news` DROP `news_level` ;
ALTER TABLE `l21_workshop` DROP `workshop_level` ;
ALTER TABLE `l21_publication` DROP `publi_level` ;
ALTER TABLE `l21_rights` DROP `rights_level` ;

# adding user_id on l21_sdi_value table 
ALTER TABLE `l21_sdi_value` ADD `sdiv_user_id` INT NOT NULL DEFAULT '1' AFTER `sdiv_sdi_info` ;


# Adding l21_email_queue table
CREATE TABLE IF NOT EXISTS `l21_email_queue` (  `eq_id` int(11) NOT NULL AUTO_INCREMENT,  `eq_recipient` varchar(255) NOT NULL,  `eq_subject` text NOT NULL,  `eq_htmlbody` text NOT NULL,  `eq_textbody` text NOT NULL,  `eq_module` varchar(255) NOT NULL,  `eq_priority` INT NOT NULL DEFAULT '2' COMMENT '1 = high, 2 = medium, 3 =low',  `eq_date_crea` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',  `eq_last_modify` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  `eq_status` varchar(255) NOT NULL,  PRIMARY KEY (`eq_id`)) DEFAULT CHARSET=utf8mb4 ;