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



# Adding project_stage field in l21_project table
ALTER TABLE `l21_project` ADD `project_achievement` VARCHAR(100) NOT NULL AFTER `project_team`;

# Adding sdii_multiple_mode field in l21_sdi_info table to handle labels_as_abscissa for multiple indicators
ALTER TABLE `l21_sdi_info` ADD `sdii_multiple_mode` VARCHAR(50) NOT NULL DEFAULT 'normal' AFTER `sdii_multiple_type`;

# Adding sdii_input_instructions field in l21_sdi_info table to handle input instructions for users
ALTER TABLE `l21_sdi_info` ADD `sdii_input_instructions` TEXT NOT NULL AFTER `sdii_description`;

# Adding sdii_force_chart_min field in l21_sdi_info table to handle min value for charts
ALTER TABLE `l21_sdi_info` ADD `sdii_force_chart_min` VARCHAR(255) NOT NULL DEFAULT 'novalue' AFTER `sdii_threshold_relative`;

# Adding sdii_api_multimapping field in l21_sdi_info table to handle Multiple indicators with API / mapping
ALTER TABLE `l21_sdi_info` ADD `sdii_api_multimapping` TEXT NOT NULL AFTER `sdii_api_valuefield`;

# Adding sdii_unique_mode field in l21_sdi_info table to handle accumulated values
ALTER TABLE `l21_sdi_info` ADD `sdii_unique_mode` VARCHAR(100) NOT NULL DEFAULT 'normal' AFTER `sdii_value_type`;

# Adding sdii_api_overwritevalues field
ALTER TABLE `l21_sdi_info` ADD `sdii_api_overwritevalues` VARCHAR(1) NOT NULL DEFAULT 'N' AFTER `sdii_api_getvalues_since`;

# Adding sdiv_history field in l21_sdi_value table to log actions on values
ALTER TABLE `l21_sdi_value` ADD `sdiv_history` TEXT NOT NULL AFTER `sdiv_computed`;

# Convert tinyint to int
ALTER TABLE `l21_workshop_calendar` CHANGE `workcal_posted_by` `workcal_posted_by` INT NOT NULL DEFAULT '0';

# Add null value to sdiv_value to handle no data
ALTER TABLE `l21_sdi_value` CHANGE `sdiv_value` `sdiv_value` FLOAT NULL DEFAULT '0';
ALTER TABLE `l21_sdi_value` CHANGE `sdiv_multivalue` `sdiv_multivalue` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL;


# first conversion to varchar - necessary to prevent strange round values
ALTER TABLE `l21_sdi_value` CHANGE `sdiv_value` `sdiv_value` VARCHAR(255) NULL DEFAULT '0';
ALTER TABLE `l21_sdi_value` CHANGE `sdiv_value` `sdiv_value` DOUBLE NULL DEFAULT '0';

# first conversion to varchar - necessary to prevent strange round values
ALTER TABLE `l21_sdi_value` CHANGE `sdiv_threshold` `sdiv_threshold` VARCHAR(255) NULL DEFAULT NULL;
ALTER TABLE `l21_sdi_value` CHANGE `sdiv_threshold` `sdiv_threshold` DOUBLE NULL DEFAULT NULL;