# BUILD Linea21 DB script - UTF-8 format
# ENGINE var is not specified in queries - from MySQL 5.5 it will automatically create innoDB tables
# CREATE DATABASE IF NOT EXISTS `linea` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# USE linea;

# One query per line !!!!! DO NOT CHANGE

SET SESSION SQL_MODE='';

DROP TABLE IF EXISTS `l21_bibliography_resources`;
CREATE TABLE l21_bibliography_resources (  bibliores_id int unsigned NOT NULL AUTO_INCREMENT,  bibliores_author varchar(200) NOT NULL,  bibliores_title varchar(255) NOT NULL,  bibliores_sub_title varchar(255) NOT NULL,  bibliores_editor varchar(255) NOT NULL,  bibliores_date varchar(100) NOT NULL,  bibliores_range int unsigned NOT NULL  DEFAULT '0', bibliores_date_crea datetime NOT NULL DEFAULT '0001-01-01 00:00:00', bibliores_last_modify timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  bibliores_validity enum('Y','N') NOT NULL DEFAULT 'Y', PRIMARY KEY (bibliores_id) ) DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

DROP TABLE IF EXISTS `l21_category_user`;
CREATE TABLE l21_category_user (  catus_id int unsigned NOT NULL AUTO_INCREMENT,  catus_name varchar(100) NOT NULL,  catus_description varchar(255) NOT NULL,  catus_date_crea datetime NOT NULL DEFAULT '0001-01-01 00:00:00',  catus_last_modify timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  PRIMARY KEY (catus_id)) DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

DROP TABLE IF EXISTS `l21_comment`;
CREATE TABLE l21_comment (  comment_id int NOT NULL AUTO_INCREMENT,  comment_module varchar(100) NOT NULL,  comment_module_id varchar(255) NOT NULL,  comment_user_id int NOT NULL,  comment_name varchar(255) NOT NULL,  comment_email varchar(255) NOT NULL,  comment_url varchar(255) NOT NULL, comment_title VARCHAR(500) NOT NULL, comment_body text NOT NULL,  comment_notification varchar(1) NOT NULL,   comment_date_crea datetime NOT NULL DEFAULT '0001-01-01 00:00:00',  comment_last_modify timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  comment_status enum('D','P','E') NOT NULL DEFAULT 'D',  PRIMARY KEY (comment_id),  FULLTEXT KEY comment_index (comment_name,comment_email,comment_body,comment_title)) DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

DROP TABLE IF EXISTS `l21_email_collector`;
CREATE TABLE l21_email_collector (  emailcol_id int unsigned NOT NULL AUTO_INCREMENT,  emailcol_email varchar(200) NOT NULL,  emailcol_date_crea datetime NOT NULL DEFAULT '0001-01-01 00:00:00',  emailcol_last_modify timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  emailcol_validity enum('Y','N') NOT NULL DEFAULT 'Y',  PRIMARY KEY (emailcol_id)) DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

DROP TABLE IF EXISTS `l21_j_biblio`;
CREATE TABLE l21_j_biblio (  j_root_id int NOT NULL DEFAULT '0',  j_biblio_id INT NOT NULL DEFAULT '0',  j_type enum('P','W') NOT NULL DEFAULT 'P') DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

DROP TABLE IF EXISTS `l21_j_link`;
CREATE TABLE l21_j_link (  j_root_id INT NOT NULL DEFAULT '0',  j_link_id INT NOT NULL DEFAULT '0',  j_type enum('P','W') NOT NULL DEFAULT 'P') DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

DROP TABLE IF EXISTS `l21_j_multi`;
CREATE TABLE l21_j_multi (  j_root_id INT NOT NULL DEFAULT '0',  j_multi_id INT NOT NULL DEFAULT '0',  j_type enum('P','W') NOT NULL DEFAULT 'P') DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

DROP TABLE IF EXISTS `l21_j_parts`;
CREATE TABLE l21_j_parts (  j_root_id INT NOT NULL DEFAULT '0',  j_parts_id INT NOT NULL DEFAULT '0',  j_type enum('P','W') NOT NULL DEFAULT 'P') DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

DROP TABLE IF EXISTS `l21_j_project_sdi`;
CREATE TABLE l21_j_project_sdi (  jps_project_id INT NOT NULL DEFAULT '0',  jps_sdi_id INT NOT NULL DEFAULT '0') DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

DROP TABLE IF EXISTS `l21_j_work_users`;
CREATE TABLE l21_j_work_users (  jwu_id INT unsigned NOT NULL AUTO_INCREMENT,  jwu_workshop_id INT unsigned NOT NULL DEFAULT '0',  jwu_user_id INT unsigned NOT NULL DEFAULT '0',  jwu_user_right enum('A','O','U') NOT NULL DEFAULT 'U',  `jwu_last_modify` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (jwu_id)) DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

DROP TABLE IF EXISTS `l21_link_resources`;
CREATE TABLE l21_link_resources (  linkres_id INT unsigned NOT NULL AUTO_INCREMENT,  linkres_text text NOT NULL,  linkres_mask varchar(200) NOT NULL,  linkres_range int unsigned NOT NULL DEFAULT '0',  linkres_date_crea datetime NOT NULL DEFAULT '0001-01-01 00:00:00',  linkres_last_modify timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  linkres_validity enum('Y','N') NOT NULL DEFAULT 'Y',  PRIMARY KEY (linkres_id)) DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

DROP TABLE IF EXISTS `l21_multimedia_resources`;
CREATE TABLE l21_multimedia_resources (  multires_id INT unsigned NOT NULL AUTO_INCREMENT,  multires_name varchar(200) NOT NULL,  multires_description text NOT NULL,  multires_media_uri varchar(255) NOT NULL,  multires_range int unsigned NOT NULL DEFAULT '0',  multires_date_crea datetime NOT NULL DEFAULT '0001-01-01 00:00:00',  multires_last_modify timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  multires_validity enum('Y','N') NOT NULL DEFAULT 'Y',  PRIMARY KEY (multires_id)) DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

DROP TABLE IF EXISTS `l21_news`;
CREATE TABLE l21_news (  news_id INT unsigned NOT NULL AUTO_INCREMENT,  news_title varchar(200) NOT NULL,  news_header text NOT NULL,  news_body text NOT NULL,  news_level int NOT NULL DEFAULT '0',  news_scale int NOT NULL DEFAULT '0', news_range int unsigned NOT NULL DEFAULT '0', news_sticky VARCHAR(1) NOT NULL DEFAULT 'N', news_posted_by INT NOT NULL DEFAULT '0',  news_published_date datetime NOT NULL DEFAULT '0001-01-01 00:00:00',  news_perempt_date datetime NOT NULL DEFAULT '0001-01-01 00:00:00',  news_is_national enum('Y','N') NOT NULL DEFAULT 'N',  news_date_crea datetime NOT NULL DEFAULT '0001-01-01 00:00:00',  news_last_modify timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  news_statut enum('D','P','AA','PA','E') NOT NULL DEFAULT 'D',  PRIMARY KEY (news_id),  FULLTEXT KEY news_index (news_title,news_header,news_body)) DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

DROP TABLE IF EXISTS `l21_newsletter`;
CREATE TABLE l21_newsletter (  newsletter_id mediumint NOT NULL AUTO_INCREMENT,  newsletter_title varchar(200) DEFAULT NULL,  newsletter_body text NOT NULL,  newsletter_posted_by INT NOT NULL DEFAULT '0',  newsletter_published_date datetime NOT NULL DEFAULT '0001-01-01 00:00:00',  newsletter_date_crea datetime NOT NULL DEFAULT '0001-01-01 00:00:00',  newsletter_last_modify timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  newsletter_statut enum('D','W','P','E') NOT NULL DEFAULT 'D',  PRIMARY KEY (newsletter_id),  FULLTEXT KEY newsletter_index (newsletter_title,newsletter_body)) DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

DROP TABLE IF EXISTS `l21_priority`;
CREATE TABLE l21_priority (  priority_id INT NOT NULL AUTO_INCREMENT,  priority_name varchar(255) NOT NULL,  priority_date_crea datetime DEFAULT NULL,  priority_last_modify timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  PRIMARY KEY (priority_id)) DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

DROP TABLE IF EXISTS `l21_profile`;
CREATE TABLE l21_profile (  profile_id INT unsigned NOT NULL,  profile_firstname varchar(200) NOT NULL DEFAULT '',  profile_lastname varchar(200) NOT NULL DEFAULT '',  profile_email varchar(200) NOT NULL,  profile_email_display enum('Y','N') NOT NULL DEFAULT 'Y',  profile_city varchar(100) NOT NULL,  profile_birthdate date NOT NULL DEFAULT '0001-01-01',  profile_leisures varchar(150) NOT NULL,  profile_job varchar(150) NOT NULL,  profile_avatar varchar(255) NOT NULL,  profile_quotation varchar(150) NOT NULL, profile_signature varchar(200) NOT NULL,  profile_date_crea datetime NOT NULL DEFAULT '0001-01-01 00:00:00',  profile_last_modify timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  PRIMARY KEY (profile_id)) DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

DROP TABLE IF EXISTS `l21_project`;
CREATE TABLE IF NOT EXISTS `l21_project` ( `project_id` INT NOT NULL AUTO_INCREMENT, `project_parent_id` INT NOT NULL, `project_priority_id` INT NOT NULL, `project_scale_id` INT NOT NULL, `project_level_id` INT unsigned NOT NULL DEFAULT '0', `project_name` varchar(500) NOT NULL, `project_goal` text, `project_opgoal` text, `project_description` text, `project_body` text NOT NULL, `project_budget` INT DEFAULT NULL, `project_budget_comment` text, `project_elected` varchar(255) DEFAULT NULL, `project_team` text, `project_achievement` VARCHAR(100) NOT NULL, `project_begin_date` date NOT NULL DEFAULT '0001-01-01', `project_estimated_date` date NOT NULL DEFAULT '0001-01-01', `project_end_date` date NOT NULL DEFAULT '0001-01-01', `project_completed` INT NOT NULL DEFAULT '0', `project_completed_sync` VARCHAR(1) NOT NULL DEFAULT 'N' , `project_cal_comment` text, `project_impact` text,`project_indic_order` text,`project_range` INT NOT NULL DEFAULT '0', `project_workshop_id` INT unsigned NOT NULL DEFAULT '0', `project_comment` text, `project_posted_by` INT NOT NULL, `project_published_date` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', `project_date_crea` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', `project_last_modify` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `project_statut` enum('D','P','AA','PA','E') NOT NULL, PRIMARY KEY (`project_id`),  FULLTEXT KEY project_index (`project_name`, `project_goal`, `project_opgoal`, `project_description`, `project_body`, `project_comment`, `project_budget_comment`, `project_cal_comment`, `project_elected`, `project_team`)) DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

DROP TABLE IF EXISTS `l21_publication`;
CREATE TABLE l21_publication (  publi_id INT unsigned NOT NULL AUTO_INCREMENT,  publi_title varchar(200) NOT NULL,  publi_resume text NOT NULL,  publi_level int NOT NULL DEFAULT '0',  publi_scale int NOT NULL DEFAULT '0', publi_range INT NOT NULL DEFAULT '0',  publi_posted_by int NOT NULL DEFAULT '0',  publi_published_date datetime NOT NULL DEFAULT '0001-01-01 00:00:00',  publi_comment text NOT NULL,  publi_date_crea datetime NOT NULL DEFAULT '0001-01-01 00:00:00',  publi_last_modify timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  publi_statut enum('D','P','AA','PA','E') NOT NULL DEFAULT 'D',  PRIMARY KEY (publi_id),  FULLTEXT KEY publi_index (publi_title,publi_resume)) DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

DROP TABLE IF EXISTS `l21_publication_contents`;
CREATE TABLE l21_publication_contents (  publicon_id INT unsigned NOT NULL AUTO_INCREMENT,  publicon_title varchar(200) NOT NULL,  publicon_body text NOT NULL,  publicon_range INT NOT NULL DEFAULT '0',  publicon_date_crea datetime NOT NULL DEFAULT '0001-01-01 00:00:00',  publicon_last_modify timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  publicon_validity enum('Y','N') NOT NULL DEFAULT 'Y',  PRIMARY KEY (publicon_id),  FULLTEXT KEY publication_contents_index (publicon_title,publicon_body)) DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

DROP TABLE IF EXISTS `l21_rights`;
CREATE TABLE l21_rights (  rights_id INT unsigned NOT NULL, rights_sdi enum('A','O','U') NOT NULL DEFAULT 'U', rights_dashboard enum('A','O','U') NOT NULL DEFAULT 'U',  rights_workshop enum('A','O','U') NOT NULL DEFAULT 'U',  rights_project enum('A','O','U') NOT NULL DEFAULT 'U',  rights_publication enum('A','O','U') NOT NULL DEFAULT 'U',  rights_news enum('A','O','U') NOT NULL DEFAULT 'U',  rights_yellowpages enum('A','O','U') NOT NULL DEFAULT 'U',  rights_level enum('A','U') NOT NULL DEFAULT 'U',  rights_scale enum('A','U') NOT NULL DEFAULT 'U', rights_category_user enum('A','U') NOT NULL DEFAULT 'U',  rights_date_crea datetime NOT NULL DEFAULT '0001-01-01 00:00:00',  rights_last_modify timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  PRIMARY KEY (rights_id)) DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

DROP TABLE IF EXISTS `l21_scale`;
CREATE TABLE l21_scale (  scale_id int unsigned NOT NULL AUTO_INCREMENT, scale_denomination varchar(200) NOT NULL,  scale_surface double unsigned NOT NULL DEFAULT '0',  scale_inhabitantsnumber bigint unsigned NOT NULL DEFAULT '0',  scale_cin varchar(100) NOT NULL DEFAULT '', scale_extradata text NOT NULL, scale_root int unsigned NOT NULL DEFAULT '0',  scale_comment text NOT NULL,  scale_dependencies int unsigned NOT NULL DEFAULT '0',  scale_range INT NOT NULL DEFAULT '0',  scale_date_crea datetime NOT NULL DEFAULT '0001-01-01 00:00:00',  scale_last_modify timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  scale_statut enum('P','D','E','I') NOT NULL DEFAULT 'P',  PRIMARY KEY (scale_id)) DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

DROP TABLE IF EXISTS `l21_sdi_evaluation`;
CREATE TABLE l21_sdi_evaluation (  sdie_id INT unsigned NOT NULL AUTO_INCREMENT,  sdie_scale_compare int unsigned NOT NULL DEFAULT '0',  sdie_fiability int unsigned NOT NULL DEFAULT '0',  sdie_accessibility int unsigned NOT NULL DEFAULT '0',  sdie_lisibility int unsigned NOT NULL DEFAULT '0',  sdie_relevance int unsigned NOT NULL DEFAULT '0',  sdie_global_performance int unsigned NOT NULL DEFAULT '0',  sdie_date_crea datetime NOT NULL DEFAULT '0001-01-01 00:00:00',  sdie_last_modify timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  PRIMARY KEY (sdie_id)) DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

DROP TABLE IF EXISTS `l21_sdi_info`;
CREATE TABLE l21_sdi_info (  sdii_id INT unsigned NOT NULL AUTO_INCREMENT,  sdii_nature VARCHAR( 255 ) NOT NULL DEFAULT 'quantitative', sdii_type VARCHAR( 255 ) NOT NULL DEFAULT 'not-set', sdii_dashboard_viz VARCHAR( 255 ) NOT NULL DEFAULT 'gauge', sdii_detail_viz VARCHAR( 255 ) NOT NULL DEFAULT 'column', sdii_mcolor VARCHAR(500) NOT NULL DEFAULT '#4AC0F2', sdii_tcolor VARCHAR(100) NOT NULL DEFAULT '#FE9440', sdii_value_type VARCHAR( 50 ) NOT NULL DEFAULT 'unique', sdii_unique_mode varchar(100) NOT NULL DEFAULT 'normal', sdii_multiple_type VARCHAR(50) NOT NULL DEFAULT 'sum', sdii_multiple_mode varchar(50) NOT NULL DEFAULT 'normal',sdii_name varchar(500) NOT NULL, sdii_description text NOT NULL, sdii_input_instructions TEXT NOT NULL, sdii_comment text NOT NULL,  sdii_level int unsigned NOT NULL DEFAULT '0',  sdii_range int unsigned NOT NULL DEFAULT '0',  sdii_goal text NOT NULL,  sdii_consulting text NOT NULL,  sdii_unit varchar(100) NOT NULL,  sdii_max_value float NULL DEFAULT NULL,  sdii_min_value float NULL DEFAULT NULL,  sdii_threshold_value float NULL DEFAULT NULL,  sdii_threshold_relative enum('Y','N') NOT NULL DEFAULT 'Y', sdii_force_chart_min varchar(255) NOT NULL DEFAULT 'novalue', sdii_frequency INT NOT NULL DEFAULT '0',  `sdii_to_dashboard` VARCHAR( 1 ) NOT NULL DEFAULT 'Y', `sdii_api_enabled` VARCHAR(1) NOT NULL DEFAULT 'N', `sdii_api_url` VARCHAR(2000) NOT NULL DEFAULT '', `sdii_api_datefield` VARCHAR(100) NOT NULL DEFAULT '',  `sdii_api_valuefield` VARCHAR(100) NOT NULL DEFAULT '' , `sdii_api_multimapping` text NOT NULL, `sdii_api_decimal` INT NOT NULL DEFAULT '0', `sdii_api_getvalues` VARCHAR(1) NOT NULL DEFAULT 'N',`sdii_api_getvalues_since` VARCHAR(100) NOT NULL DEFAULT '', `sdii_api_overwritevalues` varchar(1) NOT NULL DEFAULT 'N', sdii_provider INT unsigned NOT NULL DEFAULT '0',  sdii_evaluation INT unsigned NOT NULL DEFAULT '0',  sdii_reglementation INT unsigned NOT NULL DEFAULT '0',  sdii_date_crea datetime NOT NULL DEFAULT '0001-01-01 00:00:00',  sdii_last_modify timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  sdii_statut enum('P','D','E') NOT NULL DEFAULT 'P',  PRIMARY KEY (sdii_id),  FULLTEXT KEY sdi_info_index (sdii_name,sdii_description,sdii_goal,sdii_consulting)) DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

DROP TABLE IF EXISTS `l21_sdi_provider`;
CREATE TABLE l21_sdi_provider (  sdip_id INT unsigned NOT NULL AUTO_INCREMENT,  sdip_name varchar(255) NOT NULL,  sdip_service varchar(255) NOT NULL,  sdip_description text NOT NULL,  sdip_incharge varchar(150) NOT NULL,  sdip_address text NOT NULL,  sdip_phone varchar(10) NOT NULL,  sdip_fax varchar(12) NOT NULL,  sdip_email varchar(200) NOT NULL,  sdip_date_crea datetime NOT NULL DEFAULT '0001-01-01 00:00:00',  sdip_last_modify timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  PRIMARY KEY (sdip_id)) DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

DROP TABLE IF EXISTS `l21_sdi_reglementation`;
CREATE TABLE l21_sdi_reglementation (  sdir_id INT unsigned NOT NULL AUTO_INCREMENT,  sdir_title varchar(255) NOT NULL,  sdir_body text NOT NULL,  sdir_referer_uri text NOT NULL,  sdir_mask_uri varchar(255) NOT NULL,  sdir_date_crea datetime NOT NULL DEFAULT '0001-01-01 00:00:00',  sdir_last_modify timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  PRIMARY KEY (sdir_id)) DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

DROP TABLE IF EXISTS `l21_sdi_value`;
CREATE TABLE l21_sdi_value (  sdiv_id INT unsigned NOT NULL AUTO_INCREMENT,  sdiv_value DOUBLE DEFAULT '0', sdiv_multivalue TEXT, sdiv_threshold DOUBLE NULL DEFAULT NULL, sdiv_analysis TEXT NOT NULL, sdiv_comment text NOT NULL, sdiv_history TEXT NOT NULL, sdiv_comment_display varchar(1) NOT NULL DEFAULT 'N',  sdiv_sdi_info INT unsigned NOT NULL DEFAULT '0', sdiv_user_id INT NOT NULL DEFAULT '1', sdiv_scale int unsigned NOT NULL DEFAULT '0',  sdiv_computed VARCHAR(1) NOT NULL DEFAULT 'N', sdiv_date_published date NOT NULL DEFAULT '0001-01-01',  sdiv_date_crea datetime NOT NULL DEFAULT '0001-01-01 00:00:00',  sdiv_last_modify timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  sdiv_statut enum('P','D','E') NOT NULL DEFAULT 'P',  PRIMARY KEY (sdiv_id)) DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

DROP TABLE IF EXISTS `l21_user`;
CREATE TABLE l21_user (  user_id INT unsigned NOT NULL AUTO_INCREMENT,  user_login varchar(50) NOT NULL,  user_password varchar(200) NOT NULL,  user_community INT NOT NULL DEFAULT '0',  user_category int NOT NULL DEFAULT '0',  user_rights INT NOT NULL DEFAULT '0',  user_profile INT NOT NULL DEFAULT '0', user_forget_passkey varchar(50) NOT NULL DEFAULT '', user_cookiehash varchar(50) NOT NULL DEFAULT '', user_old_logins varchar(255) NOT NULL DEFAULT '', `user_forums` text NOT NULL, user_date_crea datetime NOT NULL DEFAULT '0001-01-01 00:00:00',  user_last_modify timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  user_last_con datetime NOT NULL DEFAULT '0001-01-01 00:00:00', user_validity enum('D','Y','N') NOT NULL DEFAULT 'Y',  PRIMARY KEY (user_id),  KEY user_login (user_login)) DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

DROP TABLE IF EXISTS `l21_workshop`;
CREATE TABLE l21_workshop (  workshop_id INT unsigned NOT NULL AUTO_INCREMENT,  workshop_denomination varchar(255) NOT NULL,  workshop_resume text NOT NULL,  workshop_level int NOT NULL DEFAULT '0',  workshop_comment text NOT NULL,  workshop_restricted INT NOT NULL DEFAULT '0', workshop_visible INT NOT NULL DEFAULT '1', workshop_range INT NOT NULL DEFAULT '0',  workshop_date_crea datetime NOT NULL DEFAULT '0001-01-01 00:00:00',  workshop_last_modify timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  workshop_statut enum('D','P','AA','PA','E') NOT NULL DEFAULT 'D',  PRIMARY KEY (workshop_id),  FULLTEXT KEY workshop_index (workshop_denomination,workshop_resume)) DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

DROP TABLE IF EXISTS `l21_workshop_calendar`;
CREATE TABLE l21_workshop_calendar (  workcal_id INT unsigned NOT NULL AUTO_INCREMENT,  workcal_task_date datetime NOT NULL DEFAULT '0001-01-01 00:00:00',  workcal_task varchar(200) NOT NULL,  workcal_task_details text NOT NULL,  workcal_workshop_id INT NOT NULL DEFAULT '0',  workcal_posted_by int NOT NULL DEFAULT '0',  workcal_date_crea datetime NOT NULL DEFAULT '0001-01-01 00:00:00',  workcal_last_modify timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  workcal_validity enum('Y','N') NOT NULL DEFAULT 'Y',  PRIMARY KEY (workcal_id),  FULLTEXT KEY workshop_cal_index (workcal_task,workcal_task_details)) DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

DROP TABLE IF EXISTS `l21_workshop_com`;
CREATE TABLE l21_workshop_com (  workcom_id INT unsigned NOT NULL AUTO_INCREMENT,  workcom_subject varchar(200) NOT NULL,  workcom_body text NOT NULL,  workcom_parent INT unsigned NOT NULL DEFAULT '0',  workcom_workshop_id int unsigned NOT NULL DEFAULT '0',  workcom_user_id INT unsigned NOT NULL DEFAULT '0',  workcom_last_user_id INT unsigned NOT NULL DEFAULT '0',  workcom_viewed INT unsigned NOT NULL DEFAULT '0', workcom_date_crea datetime NOT NULL DEFAULT '0001-01-01 00:00:00',  workcom_last_modify timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  workcom_statut enum('P','C','E') NOT NULL DEFAULT 'P',  PRIMARY KEY (workcom_id),  FULLTEXT KEY workshop_com_index (workcom_subject,workcom_body)) DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

DROP TABLE IF EXISTS `l21_workshop_notify_exception`;
CREATE TABLE l21_workshop_notify_exception (  workshop_id INT NOT NULL,  user_id INT NOT NULL,  `type` varchar(100) NOT NULL) DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

DROP TABLE IF EXISTS `l21_workshop_report`;
CREATE TABLE l21_workshop_report (  workrep_id INT unsigned NOT NULL AUTO_INCREMENT,  workrep_title varchar(200) NOT NULL,  workrep_resume text NOT NULL,  workrep_workshop_id INT unsigned NOT NULL DEFAULT '0',  workrep_published_date datetime NOT NULL DEFAULT '0001-01-01 00:00:00',  workrep_comment text NOT NULL, workrep_visible INT NOT NULL DEFAULT '1', workrep_date_crea datetime NOT NULL DEFAULT '0001-01-01 00:00:00',  workrep_last_modify timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  workrep_statut enum('D','P','AA','PA','E') NOT NULL DEFAULT 'D',  PRIMARY KEY (workrep_id),  FULLTEXT KEY workshop_report_index (workrep_title,workrep_resume)) DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

DROP TABLE IF EXISTS `l21_workshop_report_contents`;
CREATE TABLE l21_workshop_report_contents (  workrepcon_id INT unsigned NOT NULL AUTO_INCREMENT,  workrepcon_title varchar(100) NOT NULL,  workrepcon_body text NOT NULL,  workrepcon_range INT NOT NULL DEFAULT '0',  workrepcon_date_crea datetime NOT NULL DEFAULT '0001-01-01 00:00:00',  workrepcon_last_modify timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  workrepcon_validity enum('Y','N') NOT NULL DEFAULT 'Y',  PRIMARY KEY (workrepcon_id),  FULLTEXT KEY workshop_report_contents_index (workrepcon_title,workrepcon_body)) DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

DROP TABLE IF EXISTS `l21_yellowpages`;
CREATE TABLE l21_yellowpages (  yellowp_id INT unsigned NOT NULL AUTO_INCREMENT,  yellowp_name varchar(150) NOT NULL,  yellowp_activity varchar(255) NOT NULL,  yellowp_level int NOT NULL DEFAULT '0',  yellowp_street varchar(200) NOT NULL,  yellowp_postal_code varchar(6) NOT NULL,  yellowp_city varchar(100) NOT NULL,  yellowp_country varchar(100) NOT NULL,  yellowp_phone varchar(12) NOT NULL,  yellowp_fax varchar(12) NOT NULL,  yellowp_email varchar(150) NOT NULL,  yellowp_website varchar(150) NOT NULL,  yellowp_latitude float NOT NULL DEFAULT '0',  yellowp_longitude float NOT NULL DEFAULT '0',  yellowp_date_crea datetime NOT NULL DEFAULT '0001-01-01 00:00:00',  yellowp_last_modify timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  yellowp_statut enum('D','P','AA','E') NOT NULL DEFAULT 'D',  PRIMARY KEY (yellowp_id)) DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

DROP TABLE IF EXISTS `l21_plugins`;
CREATE TABLE IF NOT EXISTS `l21_plugins` ( `plugin_id` INT NOT NULL AUTO_INCREMENT, `plugin_name` varchar(255) NOT NULL,  `plugin_values` text NOT NULL, `plugin_date_crea` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', `plugin_last_modify` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,   PRIMARY KEY (`plugin_id`)) DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

DROP TABLE IF EXISTS `l21_page`;
CREATE TABLE IF NOT EXISTS `l21_page` ( page_id INT NOT NULL AUTO_INCREMENT, page_parent_id INT NOT NULL DEFAULT '0', page_homepage VARCHAR( 1 ) NOT NULL DEFAULT 'N' , page_title varchar(200) NOT NULL, page_header text NOT NULL, page_body text NOT NULL, page_heading varchar(50) NOT NULL DEFAULT '', page_range INT NOT NULL DEFAULT '0', page_posted_by INT NOT NULL, page_published_date datetime NOT NULL DEFAULT '0001-01-01 00:00:00', page_date_crea datetime NOT NULL DEFAULT '0001-01-01 00:00:00',   page_last_modify timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, page_status varchar(1) NOT NULL DEFAULT 'D', PRIMARY KEY (page_id),  FULLTEXT KEY page_index (page_title,page_header,page_body)) DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

DROP TABLE IF EXISTS `l21_actor`;
CREATE TABLE `l21_actor` (`actor_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,`actor_name` VARCHAR( 255 ) NOT NULL) DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

DROP TABLE IF EXISTS `l21_j_project_actor`;
CREATE TABLE `l21_j_project_actor` (  `jpa_id` INT NOT NULL AUTO_INCREMENT,  `jpa_project_id` INT NOT NULL,  `jpa_actor_id` INT NOT NULL,  `jpa_type` varchar(50) NOT NULL,  PRIMARY KEY (`jpa_id`)) DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;

# Adding tags table
DROP TABLE IF EXISTS `l21_tag`;
CREATE TABLE IF NOT EXISTS `l21_tag` (  `tag_id` INT 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 ENGINE=InnoDB;

DROP TABLE IF EXISTS `l21_j_tag`;
CREATE TABLE `l21_j_tag` (  `jta_id` INT NOT NULL AUTO_INCREMENT,  `jta_module` varchar(100) NOT NULL,  `jta_module_id` INT NOT NULL,  `jta_tag_id` INT NOT NULL,  PRIMARY KEY (`jta_id`)) DEFAULT CHARSET=utf8 ENGINE=InnoDB;

# Adding l21_j_project_manager table
CREATE TABLE IF NOT EXISTS `l21_j_project_manager` (  `jpm_id` INT NOT NULL AUTO_INCREMENT,  `jpm_project_id` INT NOT NULL,  `jpm_manager_id` INT NOT NULL,  PRIMARY KEY (`jpm_id`)) DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;

# Adding l21_email_queue table
CREATE TABLE IF NOT EXISTS `l21_email_queue` (  `eq_id` INT NOT NULL AUTO_INCREMENT,  `eq_recipient` varchar(255) NOT NULL,  `eq_subject` text NOT NULL,  `eq_htmlbody` MEDIUMTEXT 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 ENGINE=InnoDB;

# Adding l21_level table
CREATE TABLE IF NOT EXISTS `l21_level` (  `level_id` int 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` int 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` int 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 ENGINE=InnoDB;

# 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 ENGINE=InnoDB;

# 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=utf8 ENGINE=InnoDB;

# Adding vote tables
CREATE TABLE `l21_vote` ( `vote_id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `vote_module` varchar(100) NOT NULL, `vote_module_id` varchar(255) NOT NULL, `vote_user_id` INT NOT NULL, `vote_user_ip` varchar(100) NOT NULL, `vote_date_crea` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', PRIMARY KEY (`vote_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# 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 l21_banned_ip table
CREATE TABLE `l21_banned_ip` (  `ban_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,  `ban_ip` varchar(255) NOT NULL, `ban_user_id` INT UNSIGNED NOT NULL DEFAULT '1', `ban_date_crea` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',  `ban_last_modify` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
