



CREATE TABLE if not exists `projects` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text,
  `color` varchar(20) DEFAULT '#000000',
  `public` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8mb4;



CREATE TABLE if not exists `entities` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `entity_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=2876 DEFAULT CHARSET=utf8mb4;



CREATE TABLE if not exists `criteria` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text,
  `link` varchar(255) DEFAULT NULL,
  `type` varchar(255) NOT NULL,
  `unit` varchar(45) DEFAULT NULL,
  `content` text,
  `enable` tinyint(1) NOT NULL DEFAULT '0',
  `weight` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=193 DEFAULT CHARSET=utf8mb4;



CREATE TABLE if not exists `links` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text,
  `url` varchar(255) NOT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4;


CREATE TABLE if not exists `projects_plugins` (
  `project_id` int(11) NOT NULL,
  `plugin` varchar(45) NOT NULL,
  UNIQUE KEY `project_plugin` (`project_id`,`plugin`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



CREATE TABLE if not exists `collections` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text,
  `user_id` int(11) NOT NULL,
  `project_id` int(11) NOT NULL,
  `public` tinyint(4) DEFAULT '0',
  `dynamic` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;



CREATE TABLE if not exists `entities_collections` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `collection_id` int(11) NOT NULL,
  `entity_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_entities_collections_1_idx` (`collection_id`),
  KEY `fk_entities_collections_2_idx` (`entity_id`),
  CONSTRAINT `fk_entities_collections_1` FOREIGN KEY (`collection_id`) REFERENCES `collections` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_entities_collections_2` FOREIGN KEY (`entity_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;



CREATE TABLE if not exists `entities_criteria` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `entity_id` int(11) NOT NULL,
  `criterion_id` int(11) NOT NULL,
  `content` text,
  `verified` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_link_entity_criterion` (`entity_id`,`criterion_id`),
  KEY `fk_entities_criteria_2_idx` (`entity_id`),
  KEY `fk_entities_criteria_1_idx` (`criterion_id`),
  CONSTRAINT `fk_entities_criteria_1` FOREIGN KEY (`criterion_id`) REFERENCES `criteria` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_entities_criteria_2` FOREIGN KEY (`entity_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=55596 DEFAULT CHARSET=utf8mb4;



CREATE TABLE if not exists `files` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `filename` varchar(255) NOT NULL,
  `description` text,
  `user_id` int(11) NOT NULL,
  `entity_id` int(11) NOT NULL,
  `criterion_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  UNIQUE KEY `filename_UNIQUE` (`filename`)
) ENGINE=InnoDB AUTO_INCREMENT=590 DEFAULT CHARSET=utf8mb4;



CREATE TABLE if not exists `projects_criteria` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `project_id` int(11) NOT NULL,
  `criterion_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_new_table_1_idx` (`project_id`),
  KEY `fk_projects_criteria_1_idx` (`criterion_id`),
  CONSTRAINT `fk_projects_criteria_1` FOREIGN KEY (`criterion_id`) REFERENCES `criteria` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_projects_criteria_2` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=204 DEFAULT CHARSET=utf8mb4;



CREATE TABLE if not exists `projects_entities` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `project_id` int(11) NOT NULL,
  `entity_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index4` (`project_id`,`entity_id`),
  KEY `fk_projects_entities_1_idx` (`project_id`),
  KEY `fk_projects_entities_2_idx` (`entity_id`),
  CONSTRAINT `fk_projects_entities_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_projects_entities_2` FOREIGN KEY (`entity_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2866 DEFAULT CHARSET=utf8mb4;



CREATE TABLE if not exists `projects_links` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `project_id` int(11) NOT NULL,
  `link_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_projects_links_1_idx` (`project_id`),
  KEY `fk_projects_links_2_idx` (`link_id`),
  CONSTRAINT `fk_projects_links_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_projects_links_2` FOREIGN KEY (`link_id`) REFERENCES `links` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8mb4;




CREATE TABLE if not exists `projects_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `project_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8mb4;


ALTER TABLE `collections`
	ADD KEY `fk_collections_1_idx` (`project_id`),
	ADD KEY `fk_collections_2_idx` (`user_id`),
	ADD CONSTRAINT `fk_collections_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
	ADD CONSTRAINT `fk_collections_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
	
ALTER TABLE `files`
	ADD KEY `fk_files_1_idx` (`user_id`),
	ADD KEY `fk_files_2_idx` (`entity_id`),
	ADD KEY `fk_files_3_idx` (`criterion_id`),
	ADD CONSTRAINT `fk_files_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
	ADD CONSTRAINT `fk_files_2` FOREIGN KEY (`entity_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
	ADD CONSTRAINT `fk_files_3` FOREIGN KEY (`criterion_id`) REFERENCES `criteria` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `projects_users`
	ADD KEY `fk_projects_users_1_idx` (`project_id`),
	ADD KEY `fk_projects_users_2_idx` (`user_id`),
	ADD CONSTRAINT `fk_projects_users_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
	ADD CONSTRAINT `fk_projects_users_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

/* 2023-05-16 */
ALTER TABLE `donnees_groupees`.`files` ADD COLUMN `metadata` TEXT;

/* 2023-09-25 */
ALTER TABLE `donnees_groupees`.`projects` ADD COLUMN `map` BOOLEAN DEFAULT FALSE; /* Ajout d'un champ pour indiquer que les entités du projet peuvent s'afficher sous forme de carte */
ALTER TABLE `donnees_groupees`.`projects` ADD COLUMN `geometry_coordinates` TEXT; /* Ajout d'un champ sur lequel se basera la carte pour l'affichage */
ALTER TABLE `donnees_groupees`.`projects` ADD COLUMN `geometry_zoom` TINYINT DEFAULT 7; /* Ajout d'un champ sur lequel se basera la carte pour l'affichage */
ALTER TABLE `donnees_groupees`.`criteria` ADD COLUMN `show_map` BOOLEAN DEFAULT TRUE; /* Ajout d'un champ pour indiquer que le champ sélectionné peut s'afficher sur la carte d'un projet */
ALTER TABLE `donnees_groupees`.`criteria` ADD COLUMN `show_table` BOOLEAN DEFAULT TRUE; /* Ajout d'un champ pour indiquer que le champ sélectionné peut s'afficher dans le tableau d'un projet */
ALTER TABLE `donnees_groupees`.`criteria` ADD COLUMN `editable` BOOLEAN DEFAULT FALSE; /* Ajout d'un champ pour indiquer que le champ peut être modifié */