# Linea21 DB patch - UTF-8 format
# patch v2.3.x To v2.4


# Adding project_task table
CREATE TABLE `l21_project_task` (  `task_id` int NOT NULL NOT NULL AUTO_INCREMENT,  `task_label` varchar(4000) NOT NULL,  `task_status` varchar(10) NOT NULL,  `task_user_id` int NOT NULL,  `task_date_done` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',  `task_project_id` int NOT NULL,  `task_reminder_freq` varchar(100) NOT NULL,  `task_reminder_lastdate` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',  `task_reminder_exceptions` varchar(500) NOT NULL DEFAULT '',  `task_range` int NOT NULL,  `task_date_crea` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',  `task_last_modify` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `task_deleted` varchar(1) NOT NULL DEFAULT 'N', PRIMARY KEY (task_id),  FULLTEXT KEY task_index (task_label)) DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;


# Adding `project_cal_comment` field in table
ALTER TABLE `l21_project` ADD `project_cal_comment` TEXT NULL DEFAULT NULL AFTER `project_completed`; 
# we rebuild index
ALTER TABLE l21_project DROP INDEX project_index, ADD FULLTEXT KEY project_index (`project_name`, `project_goal`, `project_description`, `project_body`, `project_comment`, `project_budget_comment`, `project_cal_comment`, `project_elected`, `project_team`);

# Adding `project_completed_sync` field in table
ALTER TABLE `l21_project` ADD `project_completed_sync` VARCHAR(1) NOT NULL DEFAULT 'N' AFTER `project_completed`; 

# Adding `project_cal_comment` field in table
ALTER TABLE `l21_sdi_info` CHANGE `sdii_type` `sdii_nature` VARCHAR(255) NOT NULL DEFAULT 'quantitative'; 
ALTER TABLE `l21_sdi_info` ADD `sdii_type` VARCHAR(255) NOT NULL DEFAULT 'not-set' AFTER `sdii_nature`;  

# Adding `project_cal_comment` field in table
ALTER TABLE `l21_rights` ADD `rights_sdi` ENUM('A','O','U') NOT NULL DEFAULT 'U' AFTER `rights_id`; 
UPDATE `l21_rights` SET `rights_sdi` =  `rights_dashboard`; # by default give access to module when user has already access to dashboard

# Adding active field in l21_menu table
ALTER TABLE `l21_menu` ADD `menu_active` VARCHAR(1) NOT NULL DEFAULT 'Y' AFTER `menu_order`; 