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


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

ALTER TABLE `l21_user` CHANGE `user_forget_passkey` `user_forget_passkey` VARCHAR(50) NOT NULL DEFAULT '' ;
ALTER TABLE `l21_user` CHANGE `user_cookiehash` `user_cookiehash` varchar(50) NOT NULL DEFAULT '';
ALTER TABLE `l21_user` CHANGE `user_old_logins` `user_old_logins` varchar(255) NOT NULL DEFAULT '';

# Adding Finalities table (used for french only)
CREATE TABLE `l21_finalities` (  `finality_id` int NOT NULL AUTO_INCREMENT,  `finality_name` varchar(255) NOT NULL,  `finality_date_crea` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',  `finality_last_modify` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  `finality_status` enum('D','P','E') NOT NULL DEFAULT 'P',  PRIMARY KEY (`finality_id`)) DEFAULT CHARSET=utf8mb4;

# Populate l21_finalities table
INSERT INTO `l21_finalities` VALUES (1,'La lutte contre le changement climatique','2016-07-20 12:00:01','2016-07-20 10:00:01','P'),(2,'La préservation de la biodiversité, des milieux et des ressources','2016-07-20 12:00:01','2016-07-20 10:00:01','P'),(3,'La cohésion sociale et la solidarité entre les territoires et les générations','2016-07-20 12:01:01','2016-07-20 10:01:01','P'),(4,'L’épanouissement des êtres humains','2016-07-20 12:01:01','2016-07-20 10:01:01','P'),(5,'Une dynamique de développement suivant des modes de production et de consommation responsables','2016-07-20 12:01:15','2016-07-20 10:01:15','P');

# Create join tables
CREATE TABLE `l21_j_project_finality` (  `jpf_id` int NOT NULL AUTO_INCREMENT,  `jpf_project_id` int NOT NULL,  `jpf_finality_id` int NOT NULL,  `jpf_value` varchar(1) NOT NULL,  PRIMARY KEY (`jpf_id`)) DEFAULT CHARSET=utf8mb4;
