DROP TABLE IF EXISTS `interactive_map`;
CREATE TABLE `interactive_map` (
  `PK_interactive_map_id` int NOT NULL,
  `libelle_interactive_map` varchar(220) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `file_interactive_map` varchar(220) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `actif_interactive_map` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `interactive_map_config`;
CREATE TABLE `interactive_map_config` (
  `PK_interactive_map_config_id` int NOT NULL,
  `background_interactive_map_config` varchar(220) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `color_interactive_map_config` varchar(220) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `color1_interactive_map_config` varchar(220) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `interactive_map_item`;
CREATE TABLE `interactive_map_item` (
  `PK_interactive_map_item_id` int NOT NULL,
  `FK_interactive_map_id` int DEFAULT '0',
  `FK_interactive_map_item_group_id` int NOT NULL DEFAULT '0',
  `FK_interactive_map_item_category_id` int NOT NULL,
  `libelle_interactive_map_item` varchar(220) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `id_interactive_map_item` varchar(220) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `quota_interactive_map_item` int NOT NULL DEFAULT '1',
  `lock_interactive_map_item` int NOT NULL DEFAULT '0',
  `actif_interactive_map_item` int NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `interactive_map_item_allocation`;
CREATE TABLE `interactive_map_item_allocation` (
  `PK_interactive_map_item_allocation_id` int NOT NULL,
  `FK_interactive_map_item_id` int DEFAULT NULL,
  `FK_users_id` int NOT NULL,
  `checked_interactive_map_item_allocation` int NOT NULL,
  `date_checked_interactive_map_item_allocation` datetime NOT NULL,
  `date_creation_interactive_map_item_allocation` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `interactive_map_item_category`;
CREATE TABLE `interactive_map_item_category` (
  `PK_interactive_map_item_category_id` int NOT NULL,
  `libelle_interactive_map_item_category` varchar(220) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `color_interactive_map_item_category` varchar(220) COLLATE utf8mb4_unicode_ci NOT NULL,
  `actif_interactive_map_item_category` int NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `interactive_map_item_group`;
CREATE TABLE `interactive_map_item_group` (
  `PK_interactive_map_item_group_id` int NOT NULL,
  `libelle_interactive_map_item_group` varchar(220) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `actif_interactive_map_item_group` int NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


ALTER TABLE `interactive_map`
  ADD PRIMARY KEY (`PK_interactive_map_id`),
  ADD KEY `actif_interactive_map` (`actif_interactive_map`),
  ADD KEY `libelle_interactive_map` (`libelle_interactive_map`);

ALTER TABLE `interactive_map_config`
  ADD PRIMARY KEY (`PK_interactive_map_config_id`);

ALTER TABLE `interactive_map_item`
  ADD PRIMARY KEY (`PK_interactive_map_item_id`),
  ADD KEY `FK_interactive_map_id` (`FK_interactive_map_id`),
  ADD KEY `actif_interactive_map_item` (`actif_interactive_map_item`),
  ADD KEY `libelle_interactive_map_item` (`libelle_interactive_map_item`),
  ADD KEY `id_interactive_map_item` (`id_interactive_map_item`),
  ADD KEY `quota_interactive_map_item` (`quota_interactive_map_item`),
  ADD KEY `FK_interactive_map_item_group_id` (`FK_interactive_map_item_group_id`),
  ADD KEY `FK_interactive_map_item_category_id` (`FK_interactive_map_item_category_id`);

ALTER TABLE `interactive_map_item_allocation`
  ADD PRIMARY KEY (`PK_interactive_map_item_allocation_id`),
  ADD KEY `FK_interactive_map_item_id` (`FK_interactive_map_item_id`),
  ADD KEY `FK_users_id` (`FK_users_id`),
  ADD KEY `checked_interactive_map_item_allocation` (`checked_interactive_map_item_allocation`),
  ADD KEY `date_checked_interactive_map_item_allocation` (`date_checked_interactive_map_item_allocation`);

ALTER TABLE `interactive_map_item_category`
  ADD PRIMARY KEY (`PK_interactive_map_item_category_id`),
  ADD KEY `actif_interactive_map_item_category` (`actif_interactive_map_item_category`) USING BTREE,
  ADD KEY `libelle_interactive_map_item_category` (`libelle_interactive_map_item_category`) USING BTREE;

ALTER TABLE `interactive_map_item_group`
  ADD PRIMARY KEY (`PK_interactive_map_item_group_id`),
  ADD KEY `actif_interactive_map_item_group` (`actif_interactive_map_item_group`),
  ADD KEY `libelle_interactive_map_item_group` (`libelle_interactive_map_item_group`);


ALTER TABLE `interactive_map`
  MODIFY `PK_interactive_map_id` int NOT NULL AUTO_INCREMENT;

ALTER TABLE `interactive_map_config`
  MODIFY `PK_interactive_map_config_id` int NOT NULL AUTO_INCREMENT;

ALTER TABLE `interactive_map_item`
  MODIFY `PK_interactive_map_item_id` int NOT NULL AUTO_INCREMENT;

ALTER TABLE `interactive_map_item_allocation`
  MODIFY `PK_interactive_map_item_allocation_id` int NOT NULL AUTO_INCREMENT;

ALTER TABLE `interactive_map_item_category`
  MODIFY `PK_interactive_map_item_category_id` int NOT NULL AUTO_INCREMENT;

ALTER TABLE `interactive_map_item_group`
  MODIFY `PK_interactive_map_item_group_id` int NOT NULL AUTO_INCREMENT;


ALTER TABLE `interactive_map_item`
  ADD CONSTRAINT `interactive_map_item_ibfk_1` FOREIGN KEY (`FK_interactive_map_id`) REFERENCES `interactive_map` (`PK_interactive_map_id`) ON DELETE CASCADE;

ALTER TABLE `interactive_map_item_allocation`
  ADD CONSTRAINT `interactive_map_item_allocation_ibfk_1` FOREIGN KEY (`FK_interactive_map_item_id`) REFERENCES `interactive_map_item` (`PK_interactive_map_item_id`) ON DELETE CASCADE;
COMMIT;
