--
-- Host: localhost    Database: polaris
-- ------------------------------------------------------


--
-- Table structure for table `action`
--

CREATE TABLE `action` (
  `id` int NOT NULL AUTO_INCREMENT,
  `label` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `actor` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `action`
--


--
-- Table structure for table `aircraft`
--

CREATE TABLE `aircraft` (
  `id` int NOT NULL,
  `category_id` int NOT NULL,
  `class_id` int NOT NULL,
  `manufacturer_id` int NOT NULL,
  `type_id` int NOT NULL,
  `model_id` int NOT NULL,
  `engine_type_id` int NOT NULL,
  `registration` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `power` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `cost_hour` double DEFAULT NULL,
  `state` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_13D9672912469DE2` (`category_id`),
  KEY `IDX_13D96729EA000B10` (`class_id`),
  KEY `IDX_13D96729A23B42D` (`manufacturer_id`),
  KEY `IDX_13D96729C54C8C93` (`type_id`),
  KEY `IDX_13D967297975B7E7` (`model_id`),
  KEY `IDX_13D96729577F21F8` (`engine_type_id`),
  CONSTRAINT `FK_13D9672912469DE2` FOREIGN KEY (`category_id`) REFERENCES `category_aircraft` (`id`),
  CONSTRAINT `FK_13D96729577F21F8` FOREIGN KEY (`engine_type_id`) REFERENCES `engine_type_aircraft` (`id`),
  CONSTRAINT `FK_13D967297975B7E7` FOREIGN KEY (`model_id`) REFERENCES `model_aircraft` (`id`),
  CONSTRAINT `FK_13D96729A23B42D` FOREIGN KEY (`manufacturer_id`) REFERENCES `manufacturer_aircraft` (`id`),
  CONSTRAINT `FK_13D96729BF396750` FOREIGN KEY (`id`) REFERENCES `resources` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_13D96729C54C8C93` FOREIGN KEY (`type_id`) REFERENCES `type_aircraft` (`id`),
  CONSTRAINT `FK_13D96729EA000B10` FOREIGN KEY (`class_id`) REFERENCES `class_aircraft` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `aircraft`
--


--
-- Table structure for table `aircraft_counter_type_value`
--

CREATE TABLE `aircraft_counter_type_value` (
  `aircraft_id` int NOT NULL,
  `counter_type_value_id` int NOT NULL,
  PRIMARY KEY (`aircraft_id`,`counter_type_value_id`),
  KEY `IDX_E6BC0EF1846E2F5C` (`aircraft_id`),
  KEY `IDX_E6BC0EF12402E8F` (`counter_type_value_id`),
  CONSTRAINT `FK_E6BC0EF12402E8F` FOREIGN KEY (`counter_type_value_id`) REFERENCES `counter_type_value` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_E6BC0EF1846E2F5C` FOREIGN KEY (`aircraft_id`) REFERENCES `aircraft` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `aircraft_counter_type_value`
--


--
-- Table structure for table `airport`
--

CREATE TABLE `airport` (
  `id` int NOT NULL AUTO_INCREMENT,
  `code` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `airport`
--


--
-- Table structure for table `app_role`
--

CREATE TABLE `app_role` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `rights` longtext COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '(DC2Type:array)',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `app_role`
--

INSERT INTO `app_role` VALUES (1,'admin','a:6:{s:5:\"users\";a:5:{i:1;s:4:\"view\";i:2;s:6:\"create\";i:3;s:4:\"edit\";i:4;s:6:\"delete\";i:0;s:4:\"list\";}s:5:\"roles\";a:5:{i:0;s:4:\"list\";i:1;s:4:\"view\";i:2;s:6:\"create\";i:3;s:4:\"edit\";i:4;s:6:\"delete\";}s:3:\"ato\";a:5:{i:0;s:4:\"list\";i:1;s:4:\"view\";i:2;s:6:\"create\";i:3;s:4:\"edit\";i:4;s:6:\"delete\";}s:8:\"aircraft\";a:5:{i:0;s:4:\"list\";i:1;s:4:\"view\";i:2;s:6:\"create\";i:3;s:4:\"edit\";i:4;s:6:\"delete\";}s:7:\"counter\";a:5:{i:0;s:4:\"list\";i:1;s:4:\"view\";i:2;s:6:\"create\";i:3;s:4:\"edit\";i:4;s:6:\"delete\";}s:9:\"formation\";a:5:{i:0;s:4:\"list\";i:1;s:4:\"view\";i:2;s:6:\"create\";i:3;s:4:\"edit\";i:4;s:6:\"delete\";}}'),(2,'user et rôles','a:6:{s:5:\"users\";a:5:{i:0;s:4:\"list\";i:1;s:4:\"view\";i:2;s:6:\"create\";i:3;s:4:\"edit\";i:4;s:6:\"delete\";}s:5:\"roles\";a:5:{i:0;s:4:\"list\";i:1;s:4:\"view\";i:2;s:6:\"create\";i:3;s:4:\"edit\";i:4;s:6:\"delete\";}s:3:\"ato\";a:0:{}s:8:\"aircraft\";a:0:{}s:7:\"counter\";a:0:{}s:9:\"formation\";a:0:{}}'),(3,'formation','a:6:{s:5:\"users\";a:0:{}s:5:\"roles\";a:0:{}s:3:\"ato\";a:0:{}s:8:\"aircraft\";a:0:{}s:7:\"counter\";a:0:{}s:9:\"formation\";a:5:{i:0;s:4:\"list\";i:1;s:4:\"view\";i:2;s:6:\"create\";i:3;s:4:\"edit\";i:4;s:6:\"delete\";}}');

--
-- Table structure for table `ato`
--

CREATE TABLE `ato` (
  `id` int NOT NULL AUTO_INCREMENT,
  `postal_address_id` int NOT NULL,
  `code` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `contact_email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `contact_phone` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `parent_id` int DEFAULT NULL,
  `user_activated_automatically` tinyint(1) NOT NULL,
  `approval_number` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `organism_type` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `logo_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `last_update` datetime DEFAULT NULL,
  `configuration` json DEFAULT NULL,
  `dictionary` json DEFAULT NULL,
  `leader_responsible_id` int DEFAULT NULL,
  `security_manager_id` int DEFAULT NULL,
  `compliance_manager_id` int DEFAULT NULL,
  `head_teacher_id` int DEFAULT NULL,
  `assistant_head_teacher_id` int DEFAULT NULL,
  `chief_ground_instructor_id` int DEFAULT NULL,
  `chief_flight_instructor_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_200AB83EFD54954B` (`postal_address_id`),
  UNIQUE KEY `UNIQ_200AB83EF5A825DA` (`leader_responsible_id`),
  UNIQUE KEY `UNIQ_200AB83EB8ECE616` (`security_manager_id`),
  UNIQUE KEY `UNIQ_200AB83E521911ED` (`compliance_manager_id`),
  UNIQUE KEY `UNIQ_200AB83EE36700E2` (`head_teacher_id`),
  UNIQUE KEY `UNIQ_200AB83ED1F50D7E` (`assistant_head_teacher_id`),
  UNIQUE KEY `UNIQ_200AB83E851FE69C` (`chief_ground_instructor_id`),
  UNIQUE KEY `UNIQ_200AB83EBDA5B3D2` (`chief_flight_instructor_id`),
  KEY `IDX_200AB83E727ACA70` (`parent_id`),
  CONSTRAINT `FK_200AB83E521911ED` FOREIGN KEY (`compliance_manager_id`) REFERENCES `ato_user` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_200AB83E727ACA70` FOREIGN KEY (`parent_id`) REFERENCES `ato` (`id`),
  CONSTRAINT `FK_200AB83E851FE69C` FOREIGN KEY (`chief_ground_instructor_id`) REFERENCES `ato_user` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_200AB83EB8ECE616` FOREIGN KEY (`security_manager_id`) REFERENCES `ato_user` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_200AB83EBDA5B3D2` FOREIGN KEY (`chief_flight_instructor_id`) REFERENCES `ato_user` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_200AB83ED1F50D7E` FOREIGN KEY (`assistant_head_teacher_id`) REFERENCES `ato_user` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_200AB83EE36700E2` FOREIGN KEY (`head_teacher_id`) REFERENCES `ato_user` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_200AB83EF5A825DA` FOREIGN KEY (`leader_responsible_id`) REFERENCES `ato_user` (`id`),
  CONSTRAINT `FK_200AB83EFD54954B` FOREIGN KEY (`postal_address_id`) REFERENCES `postal_address` (`id`),
  CONSTRAINT `ato_chk_1` CHECK (json_valid(`configuration`)),
  CONSTRAINT `ato_chk_2` CHECK (json_valid(`dictionary`))
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `ato`
--


--
-- Table structure for table `ato_counter_type_value`
--

CREATE TABLE `ato_counter_type_value` (
  `ato_id` int NOT NULL,
  `counter_type_value_id` int NOT NULL,
  PRIMARY KEY (`ato_id`,`counter_type_value_id`),
  KEY `IDX_82B4DD5847DC024` (`ato_id`),
  KEY `IDX_82B4DD52402E8F` (`counter_type_value_id`),
  CONSTRAINT `FK_82B4DD52402E8F` FOREIGN KEY (`counter_type_value_id`) REFERENCES `counter_type_value` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_82B4DD5847DC024` FOREIGN KEY (`ato_id`) REFERENCES `ato` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `ato_counter_type_value`
--


--
-- Table structure for table `ato_instructor_skill`
--

CREATE TABLE `ato_instructor_skill` (
  `ato_id` int NOT NULL,
  `instructor_skill_id` int NOT NULL,
  PRIMARY KEY (`ato_id`,`instructor_skill_id`),
  KEY `IDX_29651708847DC024` (`ato_id`),
  KEY `IDX_2965170855E763E7` (`instructor_skill_id`),
  CONSTRAINT `FK_2965170855E763E7` FOREIGN KEY (`instructor_skill_id`) REFERENCES `instructor_skill` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_29651708847DC024` FOREIGN KEY (`ato_id`) REFERENCES `ato` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `ato_instructor_skill`
--


--
-- Table structure for table `ato_role`
--

CREATE TABLE `ato_role` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `rights` longtext COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '(DC2Type:array)',
  `ato_id` int NOT NULL,
  `start_flight` tinyint(1) NOT NULL,
  `validate_session` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_54883B97847DC024` (`ato_id`),
  CONSTRAINT `FK_54883B97847DC024` FOREIGN KEY (`ato_id`) REFERENCES `ato` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `ato_role`
--


--
-- Table structure for table `ato_user`
--

CREATE TABLE `ato_user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `ato_id` int NOT NULL,
  `user_id` char(36) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '(DC2Type:guid)',
  `validate_at` datetime DEFAULT NULL,
  `is_suspended` tinyint(1) DEFAULT NULL,
  `suspension_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_8E7267B4847DC024` (`ato_id`),
  KEY `IDX_8E7267B4A76ED395` (`user_id`),
  CONSTRAINT `FK_8E7267B4847DC024` FOREIGN KEY (`ato_id`) REFERENCES `ato` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_8E7267B4A76ED395` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `ato_user`
--


--
-- Table structure for table `ato_user_action`
--

CREATE TABLE `ato_user_action` (
  `id` int NOT NULL AUTO_INCREMENT,
  `action_id` int NOT NULL,
  `ato_user_id` int NOT NULL,
  `state` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `validate_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_F1A04DE79D32F035` (`action_id`),
  KEY `IDX_F1A04DE730A17E02` (`ato_user_id`),
  CONSTRAINT `FK_F1A04DE730A17E02` FOREIGN KEY (`ato_user_id`) REFERENCES `ato_user` (`id`),
  CONSTRAINT `FK_F1A04DE79D32F035` FOREIGN KEY (`action_id`) REFERENCES `action` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `ato_user_action`
--


--
-- Table structure for table `ato_user_ato_role`
--

CREATE TABLE `ato_user_ato_role` (
  `ato_user_id` int NOT NULL,
  `ato_role_id` int NOT NULL,
  PRIMARY KEY (`ato_user_id`,`ato_role_id`),
  KEY `IDX_EAA3F26E30A17E02` (`ato_user_id`),
  KEY `IDX_EAA3F26E41CC8F3B` (`ato_role_id`),
  CONSTRAINT `FK_EAA3F26E30A17E02` FOREIGN KEY (`ato_user_id`) REFERENCES `ato_user` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_EAA3F26E41CC8F3B` FOREIGN KEY (`ato_role_id`) REFERENCES `ato_role` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `ato_user_ato_role`
--


--
-- Table structure for table `category_aircraft`
--

CREATE TABLE `category_aircraft` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `category_aircraft`
--


--
-- Table structure for table `class_aircraft`
--

CREATE TABLE `class_aircraft` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `class_aircraft`
--


--
-- Table structure for table `class_aircraft_manufacturer_aircraft`
--

CREATE TABLE `class_aircraft_manufacturer_aircraft` (
  `class_aircraft_id` int NOT NULL,
  `manufacturer_aircraft_id` int NOT NULL,
  PRIMARY KEY (`class_aircraft_id`,`manufacturer_aircraft_id`),
  KEY `IDX_E01BD698BC00E06A` (`class_aircraft_id`),
  KEY `IDX_E01BD69884B93B67` (`manufacturer_aircraft_id`),
  CONSTRAINT `FK_E01BD69884B93B67` FOREIGN KEY (`manufacturer_aircraft_id`) REFERENCES `manufacturer_aircraft` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_E01BD698BC00E06A` FOREIGN KEY (`class_aircraft_id`) REFERENCES `class_aircraft` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `class_aircraft_manufacturer_aircraft`
--


--
-- Table structure for table `class_aircraft_model_aircraft`
--

CREATE TABLE `class_aircraft_model_aircraft` (
  `class_aircraft_id` int NOT NULL,
  `model_aircraft_id` int NOT NULL,
  PRIMARY KEY (`class_aircraft_id`,`model_aircraft_id`),
  KEY `IDX_D4AC5D84BC00E06A` (`class_aircraft_id`),
  KEY `IDX_D4AC5D84EA6C16B0` (`model_aircraft_id`),
  CONSTRAINT `FK_D4AC5D84BC00E06A` FOREIGN KEY (`class_aircraft_id`) REFERENCES `class_aircraft` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_D4AC5D84EA6C16B0` FOREIGN KEY (`model_aircraft_id`) REFERENCES `model_aircraft` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `class_aircraft_model_aircraft`
--


--
-- Table structure for table `class_aircraft_type_aircraft`
--

CREATE TABLE `class_aircraft_type_aircraft` (
  `class_aircraft_id` int NOT NULL,
  `type_aircraft_id` int NOT NULL,
  PRIMARY KEY (`class_aircraft_id`,`type_aircraft_id`),
  KEY `IDX_1C27305FBC00E06A` (`class_aircraft_id`),
  KEY `IDX_1C27305FCF77F8CD` (`type_aircraft_id`),
  CONSTRAINT `FK_1C27305FBC00E06A` FOREIGN KEY (`class_aircraft_id`) REFERENCES `class_aircraft` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_1C27305FCF77F8CD` FOREIGN KEY (`type_aircraft_id`) REFERENCES `type_aircraft` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `class_aircraft_type_aircraft`
--


--
-- Table structure for table `classroom`
--

CREATE TABLE `classroom` (
  `id` int NOT NULL,
  `nb_places` int DEFAULT NULL,
  `additional_information` longtext COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`),
  CONSTRAINT `FK_497D309DBF396750` FOREIGN KEY (`id`) REFERENCES `resources` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `classroom`
--


--
-- Table structure for table `constraint`
--

CREATE TABLE `constraint` (
  `id` int NOT NULL AUTO_INCREMENT,
  `instructor_skill_id` int DEFAULT NULL,
  `action_id` int DEFAULT NULL,
  `formation_item_id` int DEFAULT NULL,
  `parent_id` int NOT NULL,
  `type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `formation_item_constraint_type` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_394A8EF255E763E7` (`instructor_skill_id`),
  KEY `IDX_394A8EF29D32F035` (`action_id`),
  KEY `IDX_394A8EF257901725` (`formation_item_id`),
  KEY `IDX_394A8EF2727ACA70` (`parent_id`),
  CONSTRAINT `FK_394A8EF255E763E7` FOREIGN KEY (`instructor_skill_id`) REFERENCES `instructor_skill` (`id`),
  CONSTRAINT `FK_394A8EF257901725` FOREIGN KEY (`formation_item_id`) REFERENCES `formation_item` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_394A8EF2727ACA70` FOREIGN KEY (`parent_id`) REFERENCES `formation_item` (`id`),
  CONSTRAINT `FK_394A8EF29D32F035` FOREIGN KEY (`action_id`) REFERENCES `action` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `constraint`
--


--
-- Table structure for table `counter_counter_type_value`
--

CREATE TABLE `counter_counter_type_value` (
  `counter_id` int NOT NULL,
  `counter_type_value_id` int NOT NULL,
  PRIMARY KEY (`counter_id`,`counter_type_value_id`),
  KEY `IDX_9A27E752FCEEF2E3` (`counter_id`),
  KEY `IDX_9A27E7522402E8F` (`counter_type_value_id`),
  CONSTRAINT `FK_9A27E7522402E8F` FOREIGN KEY (`counter_type_value_id`) REFERENCES `counter_type_value` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_9A27E752FCEEF2E3` FOREIGN KEY (`counter_id`) REFERENCES `counter` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `counter_counter_type_value`
--


--
-- Table structure for table `counter_group`
--

CREATE TABLE `counter_group` (
  `id` int NOT NULL,
  `type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `FK_BE593DE7BF396750` FOREIGN KEY (`id`) REFERENCES `counter_type_value` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `counter_group`
--


--
-- Table structure for table `counter_predefined_value`
--

CREATE TABLE `counter_predefined_value` (
  `id` int NOT NULL AUTO_INCREMENT,
  `counter_type_id` int NOT NULL,
  `value` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_11873EEE4B27D0B2` (`counter_type_id`),
  CONSTRAINT `FK_11873EEE4B27D0B2` FOREIGN KEY (`counter_type_id`) REFERENCES `counter_type` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `counter_predefined_value`
--


--
-- Table structure for table `counter_type`
--

CREATE TABLE `counter_type` (
  `id` int NOT NULL,
  `value_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_basic_counter` tinyint(1) DEFAULT NULL,
  `theoretical_counter` tinyint(1) NOT NULL,
  `add_on_session_end` tinyint(1) NOT NULL,
  `is_flight_characteristic` tinyint(1) NOT NULL,
  `display_on_booklet` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `FK_C3538E4EBF396750` FOREIGN KEY (`id`) REFERENCES `counter_type_value` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `counter_type`
--


--
-- Table structure for table `counter_type_counter_group`
--

CREATE TABLE `counter_type_counter_group` (
  `counter_type_id` int NOT NULL,
  `counter_group_id` int NOT NULL,
  PRIMARY KEY (`counter_type_id`,`counter_group_id`),
  KEY `IDX_B20934924B27D0B2` (`counter_type_id`),
  KEY `IDX_B2093492B2B3A245` (`counter_group_id`),
  CONSTRAINT `FK_B20934924B27D0B2` FOREIGN KEY (`counter_type_id`) REFERENCES `counter_type` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_B2093492B2B3A245` FOREIGN KEY (`counter_group_id`) REFERENCES `counter_group` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `counter_type_counter_group`
--


--
-- Table structure for table `counter_type_value`
--

CREATE TABLE `counter_type_value` (
  `id` int NOT NULL AUTO_INCREMENT,
  `counter_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `import_on_ato_create` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `counter_type_value`
--


--
-- Table structure for table `engine_type_aircraft`
--

CREATE TABLE `engine_type_aircraft` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `engine_type_aircraft`
--


--
-- Table structure for table `extra_lesson`
--

CREATE TABLE `extra_lesson` (
  `id` int NOT NULL AUTO_INCREMENT,
  `lesson_id` int DEFAULT NULL,
  `formation_id` int NOT NULL,
  `is_theoretical` tinyint(1) NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `goals` longtext COLLATE utf8mb4_unicode_ci,
  `duration` int DEFAULT NULL,
  `is_test` tinyint(1) NOT NULL,
  `description` longtext COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`),
  KEY `IDX_2E584C5BCDF80196` (`lesson_id`),
  KEY `IDX_2E584C5B5200282E` (`formation_id`),
  CONSTRAINT `FK_2E584C5B5200282E` FOREIGN KEY (`formation_id`) REFERENCES `formation` (`id`),
  CONSTRAINT `FK_2E584C5BCDF80196` FOREIGN KEY (`lesson_id`) REFERENCES `formation_item` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `extra_lesson`
--


--
-- Table structure for table `extra_lesson_skills`
--

CREATE TABLE `extra_lesson_skills` (
  `extra_lesson_id` int NOT NULL,
  `skills_id` int NOT NULL,
  PRIMARY KEY (`extra_lesson_id`,`skills_id`),
  KEY `IDX_3DC4DAAE3FD4DD80` (`extra_lesson_id`),
  KEY `IDX_3DC4DAAE7FF61858` (`skills_id`),
  CONSTRAINT `FK_3DC4DAAE3FD4DD80` FOREIGN KEY (`extra_lesson_id`) REFERENCES `extra_lesson` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_3DC4DAAE7FF61858` FOREIGN KEY (`skills_id`) REFERENCES `skills` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `extra_lesson_skills`
--


--
-- Table structure for table `formation`
--

CREATE TABLE `formation` (
  `id` int NOT NULL AUTO_INCREMENT,
  `lesson_notation_id` int DEFAULT NULL,
  `exercises_notation_id` int DEFAULT NULL,
  `exercise_decomposition_notation_id` int DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `subname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `code` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `version` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` longtext COLLATE utf8mb4_unicode_ci,
  `goals` longtext COLLATE utf8mb4_unicode_ci,
  `duration` int DEFAULT NULL,
  `is_theoretical` tinyint(1) NOT NULL,
  `use_theoretical_counters` tinyint(1) NOT NULL,
  `theoretical_lesson_in_practical_formation` tinyint(1) NOT NULL,
  `time_bloc_management` tinyint(1) NOT NULL,
  `flight_time_managment` tinyint(1) NOT NULL,
  `use_base_counters` tinyint(1) NOT NULL,
  `flight_characteristics` tinyint(1) NOT NULL,
  `use_not_technical_skills` tinyint(1) NOT NULL,
  `comment_on_not_technical_skills` tinyint(1) NOT NULL,
  `use_color_code_for_not_technical_skills` tinyint(1) NOT NULL,
  `use_modules_group` tinyint(1) NOT NULL,
  `use_modules` tinyint(1) NOT NULL,
  `use_phases` tinyint(1) NOT NULL,
  `use_exercises_group` tinyint(1) NOT NULL,
  `use_exercises` tinyint(1) NOT NULL,
  `use_technical_skills` tinyint(1) NOT NULL,
  `use_exercises_decomposition` tinyint(1) NOT NULL,
  `is_active` tinyint(1) NOT NULL,
  `use_practical_counter` tinyint(1) NOT NULL,
  `use_formation_duration` tinyint(1) NOT NULL,
  `use_exercise_decomposition_notation` tinyint(1) NOT NULL,
  `use_exercises_comments` tinyint(1) NOT NULL,
  `created_at` datetime NOT NULL,
  `last_modification` datetime DEFAULT NULL,
  `enable_student_self_evaluation` tinyint(1) NOT NULL,
  `is_draft` tinyint(1) NOT NULL,
  `items` json DEFAULT NULL,
  `formation_create_step_on_save` int DEFAULT NULL,
  `ato_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_404021BF67595CFC` (`lesson_notation_id`),
  KEY `IDX_404021BFB61422E` (`exercises_notation_id`),
  KEY `IDX_404021BFE250A17B` (`exercise_decomposition_notation_id`),
  KEY `IDX_404021BF847DC024` (`ato_id`),
  CONSTRAINT `FK_404021BF67595CFC` FOREIGN KEY (`lesson_notation_id`) REFERENCES `rating` (`id`),
  CONSTRAINT `FK_404021BF847DC024` FOREIGN KEY (`ato_id`) REFERENCES `ato` (`id`),
  CONSTRAINT `FK_404021BFB61422E` FOREIGN KEY (`exercises_notation_id`) REFERENCES `rating` (`id`),
  CONSTRAINT `FK_404021BFE250A17B` FOREIGN KEY (`exercise_decomposition_notation_id`) REFERENCES `rating` (`id`),
  CONSTRAINT `formation_chk_1` CHECK (json_valid(`items`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `formation`
--


--
-- Table structure for table `formation_item`
--

CREATE TABLE `formation_item` (
  `id` int NOT NULL AUTO_INCREMENT,
  `parent_id` int DEFAULT NULL,
  `formation_id` int DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `standard_duration` int DEFAULT NULL,
  `max_duration` int DEFAULT NULL,
  `type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` longtext COLLATE utf8mb4_unicode_ci,
  `goals` longtext COLLATE utf8mb4_unicode_ci,
  `position` int NOT NULL,
  `extra_lesson_id` int DEFAULT NULL,
  `config_isexpended` tinyint(1) DEFAULT NULL,
  `formation_item_from_list_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notation_id` int DEFAULT NULL,
  `min_rating_id` int DEFAULT NULL,
  `classroom_id` int DEFAULT NULL,
  `subname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_theoretical` tinyint(1) DEFAULT NULL,
  `min_visibility` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `min_weather` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ceiling` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `technical_ref` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `groupe` tinyint(1) DEFAULT NULL,
  `is_important` tinyint(1) DEFAULT NULL,
  `expected_value_id` int DEFAULT NULL,
  `expected_duration` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_48C4C05E727ACA70` (`parent_id`),
  KEY `IDX_48C4C05E5200282E` (`formation_id`),
  KEY `IDX_48C4C05E3FD4DD80` (`extra_lesson_id`),
  KEY `IDX_48C4C05E9680B7F7` (`notation_id`),
  KEY `IDX_48C4C05E55D631A8` (`min_rating_id`),
  KEY `IDX_48C4C05E6278D5A8` (`classroom_id`),
  KEY `IDX_48C4C05EB0E02586` (`expected_value_id`),
  CONSTRAINT `FK_48C4C05E3FD4DD80` FOREIGN KEY (`extra_lesson_id`) REFERENCES `extra_lesson` (`id`) ON DELETE SET NULL,
  CONSTRAINT `FK_48C4C05E5200282E` FOREIGN KEY (`formation_id`) REFERENCES `formation` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_48C4C05E55D631A8` FOREIGN KEY (`min_rating_id`) REFERENCES `rating_value` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_48C4C05E6278D5A8` FOREIGN KEY (`classroom_id`) REFERENCES `classroom` (`id`),
  CONSTRAINT `FK_48C4C05E727ACA70` FOREIGN KEY (`parent_id`) REFERENCES `formation_item` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_48C4C05E9680B7F7` FOREIGN KEY (`notation_id`) REFERENCES `rating` (`id`),
  CONSTRAINT `FK_48C4C05EB0E02586` FOREIGN KEY (`expected_value_id`) REFERENCES `rating_value` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `formation_item`
--


--
-- Table structure for table `formation_item_action`
--

CREATE TABLE `formation_item_action` (
  `formation_item_id` int NOT NULL,
  `action_id` int NOT NULL,
  PRIMARY KEY (`formation_item_id`,`action_id`),
  KEY `IDX_9EC8D98657901725` (`formation_item_id`),
  KEY `IDX_9EC8D9869D32F035` (`action_id`),
  CONSTRAINT `FK_9EC8D98657901725` FOREIGN KEY (`formation_item_id`) REFERENCES `formation_item` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_9EC8D9869D32F035` FOREIGN KEY (`action_id`) REFERENCES `action` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `formation_item_action`
--


--
-- Table structure for table `formation_item_formation_item`
--

CREATE TABLE `formation_item_formation_item` (
  `formation_item_source` int NOT NULL,
  `formation_item_target` int NOT NULL,
  PRIMARY KEY (`formation_item_source`,`formation_item_target`),
  KEY `IDX_DEAC66A1868E2A67` (`formation_item_source`),
  KEY `IDX_DEAC66A19F6B7AE8` (`formation_item_target`),
  CONSTRAINT `FK_DEAC66A1868E2A67` FOREIGN KEY (`formation_item_source`) REFERENCES `formation_item` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_DEAC66A19F6B7AE8` FOREIGN KEY (`formation_item_target`) REFERENCES `formation_item` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `formation_item_formation_item`
--


--
-- Table structure for table `formation_item_skills`
--

CREATE TABLE `formation_item_skills` (
  `formation_item_id` int NOT NULL,
  `skills_id` int NOT NULL,
  PRIMARY KEY (`formation_item_id`,`skills_id`),
  KEY `IDX_C35436457901725` (`formation_item_id`),
  KEY `IDX_C3543647FF61858` (`skills_id`),
  CONSTRAINT `FK_C35436457901725` FOREIGN KEY (`formation_item_id`) REFERENCES `formation_item` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_C3543647FF61858` FOREIGN KEY (`skills_id`) REFERENCES `skills` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `formation_item_skills`
--


--
-- Table structure for table `formation_items_technical_skills`
--

CREATE TABLE `formation_items_technical_skills` (
  `formation_item_id` int NOT NULL,
  `skills_id` int NOT NULL,
  PRIMARY KEY (`formation_item_id`,`skills_id`),
  KEY `IDX_72D338D457901725` (`formation_item_id`),
  KEY `IDX_72D338D47FF61858` (`skills_id`),
  CONSTRAINT `FK_72D338D457901725` FOREIGN KEY (`formation_item_id`) REFERENCES `formation_item` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_72D338D47FF61858` FOREIGN KEY (`skills_id`) REFERENCES `skills` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `formation_items_technical_skills`
--


--
-- Table structure for table `formation_practical_counter_type`
--

CREATE TABLE `formation_practical_counter_type` (
  `formation_id` int NOT NULL,
  `counter_type_value_id` int NOT NULL,
  PRIMARY KEY (`formation_id`,`counter_type_value_id`),
  KEY `IDX_8EA8CCD95200282E` (`formation_id`),
  KEY `IDX_8EA8CCD92402E8F` (`counter_type_value_id`),
  CONSTRAINT `FK_8EA8CCD92402E8F` FOREIGN KEY (`counter_type_value_id`) REFERENCES `counter_type_value` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_8EA8CCD95200282E` FOREIGN KEY (`formation_id`) REFERENCES `formation` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `formation_practical_counter_type`
--


--
-- Table structure for table `formation_theoretical_counter_type`
--

CREATE TABLE `formation_theoretical_counter_type` (
  `formation_id` int NOT NULL,
  `counter_type_value_id` int NOT NULL,
  PRIMARY KEY (`formation_id`,`counter_type_value_id`),
  KEY `IDX_4B85E0E35200282E` (`formation_id`),
  KEY `IDX_4B85E0E32402E8F` (`counter_type_value_id`),
  CONSTRAINT `FK_4B85E0E32402E8F` FOREIGN KEY (`counter_type_value_id`) REFERENCES `counter_type_value` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_4B85E0E35200282E` FOREIGN KEY (`formation_id`) REFERENCES `formation` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `formation_theoretical_counter_type`
--


--
-- Table structure for table `instructor_skill`
--

CREATE TABLE `instructor_skill` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `instructor_skill`
--


--
-- Table structure for table `instructor_skill_ato_user`
--

CREATE TABLE `instructor_skill_ato_user` (
  `instructor_skill_id` int NOT NULL,
  `ato_user_id` int NOT NULL,
  PRIMARY KEY (`instructor_skill_id`,`ato_user_id`),
  KEY `IDX_1EC3796555E763E7` (`instructor_skill_id`),
  KEY `IDX_1EC3796530A17E02` (`ato_user_id`),
  CONSTRAINT `FK_1EC3796530A17E02` FOREIGN KEY (`ato_user_id`) REFERENCES `ato_user` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_1EC3796555E763E7` FOREIGN KEY (`instructor_skill_id`) REFERENCES `instructor_skill` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `instructor_skill_ato_user`
--


--
-- Table structure for table `lesson_annotation`
--

CREATE TABLE `lesson_annotation` (
  `id` int NOT NULL AUTO_INCREMENT,
  `ato_id` int NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_234D7723847DC024` (`ato_id`),
  CONSTRAINT `FK_234D7723847DC024` FOREIGN KEY (`ato_id`) REFERENCES `ato` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `lesson_annotation`
--


--
-- Table structure for table `lesson_annotation_value`
--

CREATE TABLE `lesson_annotation_value` (
  `id` int NOT NULL AUTO_INCREMENT,
  `value` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `lesson_annotation_value`
--


--
-- Table structure for table `manufacturer_aircraft`
--

CREATE TABLE `manufacturer_aircraft` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `manufacturer_aircraft`
--


--
-- Table structure for table `media_object`
--

CREATE TABLE `media_object` (
  `id` int NOT NULL AUTO_INCREMENT,
  `file_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `content_url` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `formation_id` int DEFAULT NULL,
  `lesson_id` int DEFAULT NULL,
  `original_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `action_id` int DEFAULT NULL,
  `extra_lesson_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_14D431325200282E` (`formation_id`),
  KEY `IDX_14D43132CDF80196` (`lesson_id`),
  KEY `IDX_14D431329D32F035` (`action_id`),
  KEY `IDX_14D431323FD4DD80` (`extra_lesson_id`),
  CONSTRAINT `FK_14D431323FD4DD80` FOREIGN KEY (`extra_lesson_id`) REFERENCES `extra_lesson` (`id`),
  CONSTRAINT `FK_14D431325200282E` FOREIGN KEY (`formation_id`) REFERENCES `formation` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_14D431329D32F035` FOREIGN KEY (`action_id`) REFERENCES `action` (`id`),
  CONSTRAINT `FK_14D43132CDF80196` FOREIGN KEY (`lesson_id`) REFERENCES `formation_item` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `media_object`
--


--
-- Table structure for table `migration_versions`
--

CREATE TABLE `migration_versions` (
  `version` varchar(14) COLLATE utf8mb4_unicode_ci NOT NULL,
  `executed_at` datetime NOT NULL COMMENT '(DC2Type:datetime_immutable)',
  PRIMARY KEY (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `migration_versions`
--


--
-- Table structure for table `model_aircraft`
--

CREATE TABLE `model_aircraft` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `engine_type_id` int NOT NULL,
  `category_id` int NOT NULL,
  `manufacturer_id` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_D90336CA577F21F8` (`engine_type_id`),
  KEY `IDX_D90336CA12469DE2` (`category_id`),
  KEY `IDX_D90336CAA23B42D` (`manufacturer_id`),
  CONSTRAINT `FK_D90336CA12469DE2` FOREIGN KEY (`category_id`) REFERENCES `category_aircraft` (`id`),
  CONSTRAINT `FK_D90336CA577F21F8` FOREIGN KEY (`engine_type_id`) REFERENCES `engine_type_aircraft` (`id`),
  CONSTRAINT `FK_D90336CAA23B42D` FOREIGN KEY (`manufacturer_id`) REFERENCES `manufacturer_aircraft` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `model_aircraft`
--


--
-- Table structure for table `notification`
--

CREATE TABLE `notification` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` char(36) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '(DC2Type:guid)',
  `content` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_read` tinyint(1) NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_BF5476CAA76ED395` (`user_id`),
  CONSTRAINT `FK_BF5476CAA76ED395` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `notification`
--


--
-- Table structure for table `postal_address`
--

CREATE TABLE `postal_address` (
  `id` int NOT NULL AUTO_INCREMENT,
  `number` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `street` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `city` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `zipcode` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `country` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `postal_address`
--

INSERT INTO `postal_address` VALUES (1,'11','zfzf','zefze','78505','FRANCE'),(2,'70','Rue de l\'Agriculture','Saint-Amand','7000','France');

--
-- Table structure for table `promotion`
--

CREATE TABLE `promotion` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `ato_id` int NOT NULL,
  `created_at` datetime NOT NULL,
  `status` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_C11D7DD1847DC024` (`ato_id`),
  CONSTRAINT `FK_C11D7DD1847DC024` FOREIGN KEY (`ato_id`) REFERENCES `ato` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `promotion`
--


--
-- Table structure for table `promotion_ato_user`
--

CREATE TABLE `promotion_ato_user` (
  `promotion_id` int NOT NULL,
  `ato_user_id` int NOT NULL,
  PRIMARY KEY (`promotion_id`,`ato_user_id`),
  KEY `IDX_4826C3F0139DF194` (`promotion_id`),
  KEY `IDX_4826C3F030A17E02` (`ato_user_id`),
  CONSTRAINT `FK_4826C3F0139DF194` FOREIGN KEY (`promotion_id`) REFERENCES `promotion` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_4826C3F030A17E02` FOREIGN KEY (`ato_user_id`) REFERENCES `ato_user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `promotion_ato_user`
--


--
-- Table structure for table `promotion_formation`
--

CREATE TABLE `promotion_formation` (
  `id` int NOT NULL AUTO_INCREMENT,
  `formation_id` int NOT NULL,
  `promotion_id` int NOT NULL,
  `status` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_3137F092139DF194` (`promotion_id`),
  KEY `IDX_3137F0925200282E` (`formation_id`),
  CONSTRAINT `FK_3137F092139DF194` FOREIGN KEY (`promotion_id`) REFERENCES `promotion` (`id`),
  CONSTRAINT `FK_3137F0925200282E` FOREIGN KEY (`formation_id`) REFERENCES `formation` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `promotion_formation`
--


--
-- Table structure for table `rating`
--

CREATE TABLE `rating` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `rating`
--


--
-- Table structure for table `rating_ato`
--

CREATE TABLE `rating_ato` (
  `rating_id` int NOT NULL,
  `ato_id` int NOT NULL,
  PRIMARY KEY (`rating_id`,`ato_id`),
  KEY `IDX_39D385C7A32EFC6` (`rating_id`),
  KEY `IDX_39D385C7847DC024` (`ato_id`),
  CONSTRAINT `FK_39D385C7847DC024` FOREIGN KEY (`ato_id`) REFERENCES `ato` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_39D385C7A32EFC6` FOREIGN KEY (`rating_id`) REFERENCES `rating` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `rating_ato`
--


--
-- Table structure for table `rating_value`
--

CREATE TABLE `rating_value` (
  `id` int NOT NULL AUTO_INCREMENT,
  `rating_type_id` int NOT NULL,
  `value` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `validate_exercise` tinyint(1) DEFAULT NULL,
  `position` int NOT NULL,
  `media_value_id` int DEFAULT NULL,
  `color` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `exercise_notperformed` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_80615708F6419510` (`media_value_id`),
  KEY `IDX_80615708260075EB` (`rating_type_id`),
  CONSTRAINT `FK_80615708260075EB` FOREIGN KEY (`rating_type_id`) REFERENCES `rating` (`id`),
  CONSTRAINT `FK_80615708F6419510` FOREIGN KEY (`media_value_id`) REFERENCES `media_object` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `rating_value`
--


--
-- Table structure for table `resources`
--

CREATE TABLE `resources` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `ato_id` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_EF66EBAE847DC024` (`ato_id`),
  CONSTRAINT `FK_EF66EBAE847DC024` FOREIGN KEY (`ato_id`) REFERENCES `ato` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `resources`
--


--
-- Table structure for table `session`
--

CREATE TABLE `session` (
  `id` int NOT NULL AUTO_INCREMENT,
  `instructor_id` int NOT NULL,
  `start_at` datetime NOT NULL,
  `end_at` datetime DEFAULT NULL,
  `comments` longtext COLLATE utf8mb4_unicode_ci,
  `is_theoretical` tinyint(1) NOT NULL,
  `formation_id` int NOT NULL,
  `lesson_id` int DEFAULT NULL,
  `lesson_notation_id` int DEFAULT NULL,
  `bloc_time` int DEFAULT NULL,
  `flight_time` int DEFAULT NULL,
  `lesson_extra_id` int DEFAULT NULL,
  `instructor_signature_id` int DEFAULT NULL,
  `success` tinyint(1) NOT NULL,
  `status` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `pdf_file_id` int DEFAULT NULL,
  `validate_at` datetime DEFAULT NULL,
  `correction_id` int DEFAULT NULL,
  `departure_airport_id` int DEFAULT NULL,
  `arrival_airport_id` int DEFAULT NULL,
  `aircraft_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_D044D5D463D2D5DF` (`lesson_extra_id`),
  UNIQUE KEY `UNIQ_D044D5D41AE37126` (`instructor_signature_id`),
  UNIQUE KEY `UNIQ_D044D5D4E071F843` (`pdf_file_id`),
  UNIQUE KEY `UNIQ_D044D5D494AE086B` (`correction_id`),
  KEY `IDX_D044D5D48C4FC193` (`instructor_id`),
  KEY `IDX_D044D5D45200282E` (`formation_id`),
  KEY `IDX_D044D5D4CDF80196` (`lesson_id`),
  KEY `IDX_D044D5D467595CFC` (`lesson_notation_id`),
  KEY `IDX_D044D5D4F631AB5C` (`departure_airport_id`),
  KEY `IDX_D044D5D47F43E343` (`arrival_airport_id`),
  KEY `IDX_D044D5D4846E2F5C` (`aircraft_id`),
  CONSTRAINT `FK_D044D5D41AE37126` FOREIGN KEY (`instructor_signature_id`) REFERENCES `signature` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_D044D5D45200282E` FOREIGN KEY (`formation_id`) REFERENCES `formation` (`id`),
  CONSTRAINT `FK_D044D5D463D2D5DF` FOREIGN KEY (`lesson_extra_id`) REFERENCES `extra_lesson` (`id`),
  CONSTRAINT `FK_D044D5D467595CFC` FOREIGN KEY (`lesson_notation_id`) REFERENCES `rating_value` (`id`),
  CONSTRAINT `FK_D044D5D47F43E343` FOREIGN KEY (`arrival_airport_id`) REFERENCES `airport` (`id`),
  CONSTRAINT `FK_D044D5D4846E2F5C` FOREIGN KEY (`aircraft_id`) REFERENCES `aircraft` (`id`),
  CONSTRAINT `FK_D044D5D48C4FC193` FOREIGN KEY (`instructor_id`) REFERENCES `ato_user` (`id`),
  CONSTRAINT `FK_D044D5D494AE086B` FOREIGN KEY (`correction_id`) REFERENCES `session` (`id`) ON DELETE SET NULL,
  CONSTRAINT `FK_D044D5D4CDF80196` FOREIGN KEY (`lesson_id`) REFERENCES `formation_item` (`id`),
  CONSTRAINT `FK_D044D5D4E071F843` FOREIGN KEY (`pdf_file_id`) REFERENCES `media_object` (`id`),
  CONSTRAINT `FK_D044D5D4F631AB5C` FOREIGN KEY (`departure_airport_id`) REFERENCES `airport` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `session`
--


--
-- Table structure for table `session_exercise`
--

CREATE TABLE `session_exercise` (
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `session_exercise`
--


--
-- Table structure for table `signature`
--

CREATE TABLE `signature` (
  `id` int NOT NULL AUTO_INCREMENT,
  `ato_user_id` int NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_AE88014130A17E02` (`ato_user_id`),
  CONSTRAINT `FK_AE88014130A17E02` FOREIGN KEY (`ato_user_id`) REFERENCES `ato_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `signature`
--


--
-- Table structure for table `skills`
--

CREATE TABLE `skills` (
  `id` int NOT NULL AUTO_INCREMENT,
  `ato_id` int NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `rating_system_id` int DEFAULT NULL,
  `is_technical` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_D5311670847DC024` (`ato_id`),
  KEY `IDX_D5311670F31275AF` (`rating_system_id`),
  CONSTRAINT `FK_D5311670847DC024` FOREIGN KEY (`ato_id`) REFERENCES `ato` (`id`),
  CONSTRAINT `FK_D5311670F31275AF` FOREIGN KEY (`rating_system_id`) REFERENCES `rating` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `skills`
--


--
-- Table structure for table `skills_detail`
--

CREATE TABLE `skills_detail` (
  `id` int NOT NULL AUTO_INCREMENT,
  `skills_id` int NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_631971207FF61858` (`skills_id`),
  CONSTRAINT `FK_631971207FF61858` FOREIGN KEY (`skills_id`) REFERENCES `skills` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `skills_detail`
--


--
-- Table structure for table `student_formation`
--

CREATE TABLE `student_formation` (
  `id` int NOT NULL AUTO_INCREMENT,
  `formation_id` int NOT NULL,
  `student_id` int NOT NULL,
  `status` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `registered_on` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_272EE03F5200282E` (`formation_id`),
  KEY `IDX_272EE03FCB944F1A` (`student_id`),
  CONSTRAINT `FK_272EE03F5200282E` FOREIGN KEY (`formation_id`) REFERENCES `formation` (`id`),
  CONSTRAINT `FK_272EE03FCB944F1A` FOREIGN KEY (`student_id`) REFERENCES `ato_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `student_formation`
--


--
-- Table structure for table `type_aircraft`
--

CREATE TABLE `type_aircraft` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `manufacturer_id` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_DE2640D5A23B42D` (`manufacturer_id`),
  CONSTRAINT `FK_DE2640D5A23B42D` FOREIGN KEY (`manufacturer_id`) REFERENCES `manufacturer_aircraft` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `type_aircraft`
--


--
-- Table structure for table `type_aircraft_model_aircraft`
--

CREATE TABLE `type_aircraft_model_aircraft` (
  `type_aircraft_id` int NOT NULL,
  `model_aircraft_id` int NOT NULL,
  PRIMARY KEY (`type_aircraft_id`,`model_aircraft_id`),
  KEY `IDX_114C446CCF77F8CD` (`type_aircraft_id`),
  KEY `IDX_114C446CEA6C16B0` (`model_aircraft_id`),
  CONSTRAINT `FK_114C446CCF77F8CD` FOREIGN KEY (`type_aircraft_id`) REFERENCES `type_aircraft` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_114C446CEA6C16B0` FOREIGN KEY (`model_aircraft_id`) REFERENCES `model_aircraft` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `type_aircraft_model_aircraft`
--


--
-- Table structure for table `user_app_role`
--

CREATE TABLE `user_app_role` (
  `user_id` char(36) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '(DC2Type:guid)',
  `app_role_id` int NOT NULL,
  PRIMARY KEY (`user_id`,`app_role_id`),
  KEY `IDX_B2CA4BCAA76ED395` (`user_id`),
  KEY `IDX_B2CA4BCA3B5EA2E1` (`app_role_id`),
  CONSTRAINT `FK_B2CA4BCA3B5EA2E1` FOREIGN KEY (`app_role_id`) REFERENCES `app_role` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_B2CA4BCAA76ED395` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `user_app_role`
--

INSERT INTO `user_app_role` VALUES ('2be64b2c-1cdc-45cf-a43e-b095857364nb',1),('2be64b2c-1cdc-45cf-a43e-b095857384de',1);


