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


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

# add INSEE COG to scales and extra field to store extra data
ALTER TABLE `l21_scale` ADD `scale_cin` VARCHAR( 100 ) NOT NULL AFTER `scale_inhabitantsnumber` , ADD `scale_extradata` TEXT NOT NULL AFTER `scale_cin` ;

# Adding l21_level table
CREATE TABLE IF NOT EXISTS `l21_level` (  `level_id` tinyint unsigned NOT NULL AUTO_INCREMENT,  `level_name` varchar(200) NOT NULL,  `level_code` varchar(100) NOT NULL DEFAULT '',  `level_label` varchar(255) NOT NULL,  `level_parent` tinyint unsigned NOT NULL DEFAULT '0',  `level_desc_project` text NOT NULL, `level_desc_dashboard` text NOT NULL,  `level_desc_publication` text NOT NULL,  `level_desc_news` text NOT NULL,  `level_desc_workshop` text NOT NULL,  `level_range` tinyint unsigned NOT NULL DEFAULT '0',  `level_date_crea` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',  `level_last_modify` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  `level_status` enum('P','D','E') NOT NULL DEFAULT 'P',  PRIMARY KEY (`level_id`)) DEFAULT CHARSET=utf8mb4 ;

# replace theme references to level 
ALTER TABLE l21_theme CHANGE theme_id theme_id INT UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `l21_project` CHANGE `project_theme_id` `project_level_id` INT UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE `l21_news` CHANGE `news_theme` `news_level` INT NOT NULL DEFAULT '0';
ALTER TABLE `l21_workshop` CHANGE `workshop_theme` `workshop_level` INT NOT NULL DEFAULT '0';
ALTER TABLE `l21_publication` CHANGE `publi_theme` `publi_level` INT NOT NULL DEFAULT '0';
ALTER TABLE `l21_sdi_info` CHANGE `sdii_theme` `sdii_level` INT UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE `l21_yellowpages` CHANGE `yellowp_theme` `yellowp_level` INT NOT NULL DEFAULT '0';
ALTER TABLE `l21_rights` CHANGE `rights_theme` `rights_level` ENUM( 'A', 'U' ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'U';
ALTER TABLE `l21_sdi_value` CHANGE `sdiv_user_id` `sdiv_user_id` INT NOT NULL DEFAULT '1';

# DROP TABLE IF EXISTS `l21_sd_theme`;
# DROP TABLE IF EXISTS `l21_theme`;
