
--
-- Table structure for table `pear_contenttype`
--

CREATE TABLE `pear_contenttype` (
  `id` int unsigned NOT NULL,
  `code` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Dumping data for table `pear_contenttype`
--

INSERT INTO `pear_contenttype` VALUES (5,'COMICS'),(3,'COURT-METRAGE'),(7,'EVENEMENT-JOUABLE'),(1,'FILM'),(4,'JEU-VIDEO'),(2,'SERIE'),(6,'THEATRE');

--
-- Table structure for table `pear_country`
--

CREATE TABLE `pear_country` (
  `id` int unsigned NOT NULL,
  `code` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `name` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `alpha2` varchar(2) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `alpha3` varchar(3) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Dumping data for table `pear_country`
--

INSERT INTO `pear_country` VALUES (1,'FRENCH-REPUBLIC','France','FR','FRA'),(2,'UNITED-STATES-OF-AMERICA','United States of America','US','USA');

--
-- Table structure for table `pear_country_lang`
--

CREATE TABLE `pear_country_lang` (
  `id` int unsigned NOT NULL,
  `idCountry` int unsigned NOT NULL,
  `idLang` int unsigned NOT NULL,
  `code` varchar(128) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`),
  KEY `foreign_pear_country_lang_idCountry` (`idCountry`),
  KEY `foreign_pear_country_lang_idLang` (`idLang`),
  CONSTRAINT `foreign_pear_country_lang_idCountry` FOREIGN KEY (`idCountry`) REFERENCES `pear_country` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `foreign_pear_country_lang_idLang` FOREIGN KEY (`idLang`) REFERENCES `pear_lang` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Dumping data for table `pear_country_lang`
--

INSERT INTO `pear_country_lang` VALUES (1,1,1,'fr-FR'),(2,2,2,'en-US');

--
-- Table structure for table `pear_lang`
--

CREATE TABLE `pear_lang` (
  `id` int unsigned NOT NULL,
  `code` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `name` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `alpha2` varchar(2) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `alpha3` varchar(3) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Dumping data for table `pear_lang`
--

INSERT INTO `pear_lang` VALUES (1,'FRENCH','français','fr','fra'),(2,'ENGLISH','english','en','eng');

--
-- Table structure for table `pear_univers`
--

CREATE TABLE `pear_univers` (
  `id` int unsigned NOT NULL,
  `code` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `displayOrder` int unsigned DEFAULT NULL,
  `alternateContent` int unsigned DEFAULT NULL,
  `musicURL` varchar(1024) DEFAULT NULL,
  `iconURL` varchar(1024) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `logoURL` varchar(1024) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `buttonImageURL` varchar(1024) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `buttonVideoURL` varchar(1024) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `backgroundImageURL` varchar(1024) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `backgroundVideoURL` varchar(1024) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Dumping data for table `pear_univers`
--

INSERT INTO `pear_univers` VALUES (1,'MARVEL-STUDIOS',1,0,'assets/audio/marvel-studios/MarvelStudios_Music.mp3','assets/img/marvel-studios/ARCAHUS_icone_mcu.png','assets/img/marvel-studios/Marvel_Studios_Logo_blanc.png','assets/img/marvel-studios/MarvelStudios_btn.png','assets/video/marvel-studios/MCU_btn.webm','assets/img/marvel-studios/MCU_Background_Fade.png','assets/video/marvel-studios/MCU_Background.webm'),(2,'STARWARS',2,0,'assets/audio/starwars/StarWars_Music.mp3','','','assets/img/starwars/Starwars_btn.png','assets/video/starwars/Starwars_btn.mp4','assets/img/starwars/StarWars_Background_Fade.png','assets/video/starwars/StarWars_Background.mp4'),(3,'JURASSIC-WORLD',5,0,'assets/audio/jurassic-world/JurassicWorld_Music.mp3','','','assets/img/jurassic-world/JurassicWorld_btn.png','assets/video/jurassic-world/Jurassic-World_btn.webm','','assets/video/jurassic-world/Jurassic-World_Background.webm'),(4,'PREDATOR-ALIEN',8,1,'assets/audio/pedator-alien/Predalien_Music.mp3','','','assets/img/predator-alien/Predalien_btn.png','assets/video/predator-alien/Predalien_btn.mp4','assets/img/predator-alien/Predalien_Background_Fade.png','assets/video/predator-alien/Predalien_Background.mp4'),(5,'WIZARDING-WORLD',7,1,'assets/audio/wizarding-world/WizardingWorld_Music.mp3','','','assets/img/wizarding-world/WizardingWorld_btn.png','assets/video/wizarding-world/WizardingWorld_btn.mp4','assets/img/wizarding-world/WizardingWorld_Background_Fade.png','assets/video/wizarding-world/WizardingWorld_Background.mp4'),(6,'GAME-OF-THRONES',6,1,'assets/audio/game-of-thrones/GameOfThrones_Music.mp3','','','assets/img/game-of-thrones/GameOfThrones_btn.png','assets/video/game-of-thrones/GameOfThrones_btn.mp4','assets/img/game-of-thrones/GameOfThrones_Background_Fade.png','assets/video/game-of-thrones/GameOfThrones_Background.mp4'),(7,'THE-LORD-OF-THE-RINGS',35,1,'assets/audio/the-lord-of-the-rings/TheLordOfTheRings_Music.mp3','','','assets/img/the-lord-of-the-rings/TheLordOfTheRings_btn.png','assets/video/the-lord-of-the-rings/TheLordOfTheRings_btn.mp4','assets/img/the-lord-of-the-rings/TheLordOfTheRings_Background_Fade.png','assets/video/the-lord-of-the-rings/TheLordOfTheRings_Background.mp4'),(8,'FORTNITE',16,1,'assets/audio/fortnite/Fortnite_Music.mp3','','','assets/img/fortnite/Fortnite_btn.png','assets/video/fortnite/Fortnite_btn.mp4','assets/img/fortnite/Fortnite_Background_Fade.png','assets/video/fortnite/Fortnite_Background.mp4'),(9,'AVATAR',10,1,'assets/audio/avatar/Avatar_Music.mp3','','','assets/img/avatar/Avatar_btn.png','assets/video/avatar/Avatar_btn.webm','','assets/video/avatar/Avatar_Background.webm'),(10,'TERMINATOR',31,1,'assets/audio/terminator/Terminator_Music.mp3','','','assets/img/terminator/Terminator_btn.png','assets/video/terminator/Terminator_btn.mp4','assets/img/terminator/Terminator_Background-Fade.png','assets/video/terminator/Terminator_Background.mp4'),(11,'MONSTERVERSE',14,1,'assets/audio/monsterverse/MonsterVerse_Music.mp3','','','assets/img/monsterverse/MonsterVerse_btn.png','assets/video/monsterverse/MonsterVerse_btn.mp4','assets/img/monsterverse/MonsterVerse_Background_Fade.png','assets/video/monsterverse/MonsterVerse_Background.mp4'),(12,'DC-EXTENDED-UNIVERSE',30,1,'assets/audio/dc-extended-universe/DCExtendedUniverse_Music.mp3','','','assets/img/dc-extended-universe/DCExtendedUniverse_btn.png','assets/video/dc-extended-universe/DCExtendedUniverse_btn.mp4','assets/img/dc-extended-universe/DCExtendedUniverse_Background_Fade.png','assets/video/dc-extended-universe/DCExtendedUniverse_Background.mp4'),(13,'TRANSFORMERS',11,1,'assets/audio/transformers/Transformers_Music.mp3','','','assets/img/transformers/Transformers_btn.png','assets/video/transformers/Transformers_btn.mp4','assets/img/transformers/Transformers_Background-Fade.png','assets/video/transformers/Transformers_Background.mp4'),(14,'THE-HUNGER-GAMES',15,1,'assets/audio/the-hunger-games/TheHungerGames_Music.mp3','','','assets/img/the-hunger-games/TheHungerGames_btn.png','assets/video/the-hunger-games/TheHungerGames_btn.mp4','assets/img/the-hunger-games/TheHungerGames_Background-Fade.png','assets/video/the-hunger-games/TheHungerGames_Background.mp4'),(15,'MATRIX',17,1,'assets/audio/matrix/Matrix_Music.mp3','','','assets/img/matrix/Matrix_btn.png','assets/video/matrix/Matrix_btn.mp4','','assets/video/matrix/Matrix_Background.mp4'),(16,'ROCKY',18,1,'assets/audio/rocky/Rocky_Music.mp3','','','assets/img/rocky/Rocky_btn.png','assets/video/rocky/Rocky_btn.mp4','','assets/video/rocky/Rocky_Background.mp4'),(17,'JOHN-WICK',19,1,'assets/audio/john-wick/John-Wick_Music.mp3','','','assets/img/john-wick/John-Wick_btn.png','assets/video/john-wick/John-Wick_btn.mp4','','assets/video/john-wick/John-Wick_Background.mp4'),(18,'XMEN',20,1,'assets/audio/xmen/Xmen_Music.mp3','','','assets/img/xmen/Xmen_btn.png','assets/video/xmen/Xmen_btn.mp4','','assets/video/xmen/Xmen_Background.mp4'),(19,'FAST-AND-FURIOUS',21,1,'assets/audio/fast-and-furious/FastAndFurious_Music.mp3','','','assets/img/fast-and-furious/FastAndFurious_btn.png','assets/video/fast-and-furious/FastAndFurious_btn.mp4','','assets/video/fast-and-furious/FastAndFurious_Background.mp4'),(20,'PIRATES-OF-THE-CARIBBEAN',22,1,'assets/audio/pirates-of-the-caribbean/PiratesOfTheCaribbean_Music.mp3','','','assets/img/pirates-of-the-caribbean/PiratesOfTheCaribbean_btn.png','assets/video/pirates-of-the-caribbean/PiratesOfTheCaribbean_btn.mp4','','assets/video/pirates-of-the-caribbean/PiratesOfTheCaribbean_Background.mp4'),(21,'MISSION-IMPOSSIBLE',23,1,'assets/audio/mission-impossible/MissionImpossible Theme_Music.mp3','','','assets/img/mission-impossible/MissionImpossible_btn.png','assets/video/mission-impossible/MissionImpossible_btn.mp4','','assets/video/mission-impossible/MissionImpossible_Background.mp4'),(22,'ICE-AGE',24,1,'assets/audio/ice-age/IceAge_Music.mp3','','','assets/img/ice-age/IceAge_btn.png','assets/video/ice-age/IceAge_btn.mp4','','assets/video/ice-age/IceAge_Background.mp4'),(23,'PLANET-OF-THE-APES',25,1,'assets/audio/planet-of-apes/PlanetOfTheApes_Music.mp3','','','assets/img/planet-of-apes/PlanetOfTheApes_btn.png','assets/video/planet-of-apes/PlanetOfTheApes_btn.mp4','','assets/video/planet-of-apes/PlanetOfTheApes_Background.mp4'),(24,'SPIDER-VERSE',4,0,'assets/audio/spider-verse/SpiderVerse_Music.mp3','','','assets/img/spider-verse/SpiderVerse_btn.png','assets/video/spider-verse/SpiderVerse_btn.mp4','','assets/video/spider-verse/SpiderVerse_Background.mp4'),(25,'SHREK',26,1,'assets/audio/shrek/Shrek_Music.mp3','','','assets/img/shrek/Shrek_btn.png','assets/video/shrek/Shred_btn.mp4','','assets/video/shrek/Shrek_Background.mp4'),(26,'MAD-MAX',27,1,'assets/audio/madmax/MadMax_Music.mp3','','','assets/img/madmax/MadMax_btn.png','assets/video/madmax/MadMax_btn.mp4','','assets/video/madmax/MadMax_Background.mp4'),(27,'INDIANA-JONES',28,1,'assets/audio/indiana-jones/IndianaJones_Music.mp3','','','assets/img/indiana-jones/IndianaJones_btn.png','assets/video/indiana-jones/Indiana-Jones_btn.webm','','assets/video/indiana-jones/Indiana-Jones_Background.webm'),(28,'DUNE',12,0,'assets/audio/dune/Dune_Music.mp3','','','assets/img/dune/Dune_btn.webp','assets/video/dune/Dune_btn.webm','','assets/video/dune/Dune_Background.webm'),(29,'MIRACULOUS',29,0,'assets/audio/miraculous/Miraculous_Music.mp3','','','assets/img/miraculous/Miraculous_btn.webp','assets/video/miraculous/Miraculous_btn.webm','','assets/video/miraculous/Miraculous_Background.webm'),(30,'DC-STUDIOS',3,0,'assets/audio/dc-studios/DC-Studios_Music.mp3','','','assets/img/dc-studios/DC-Studios_btn.webp','assets/video/dc-studios/DC-Studios_btn.webm','','assets/video/dc-studios/DC-Studios_Background.webm'),(31,'HOW-TO-TRAIN-YOUR-DRAGON',13,0,'assets/audio/how-to-train-your-dragon/How-To-Train-Your-Dragon_Music.mp3.mp3','','','assets/img/how-to-train-your-dragon/How-To-Train-Your-Dragon_btn.webp','assets/video/how-to-train-your-dragon/How-To-Train-Your-Dragon_btn.webm','','assets/video/how-to-train-your-dragon/How-To-Train-Your-Dragon_Background.webm'),(32,'THE-BOYS',32,1,'assets/audio/the-boys/The-Boys_Music.mp3','','','assets/img/the-boys/The-Boys_btn.webp','assets/video/the-boys/The-Boys_btn.webm','','assets/video/the-boys/The-Boys_Background.webm'),(33,'THE-BATMAN',33,0,'assets/audio/the-batman/The-Batman_Music.mp3','','','assets/img/the-batman/The-Batman_btn.webp','assets/video/the-batman/The-Batman_btn.webm','','assets/video/the-batman/The-Batman_Background.webm'),(34,'AVATAR-THE-LAST-AIRBENDER',34,1,'assets/audio/avatar-the-last-airbender/Avatar-The-Last-Airbender_Music.mp3','','','assets/img/avatar-the-last-airbender/Avatar-The-Last-Airbender_btn.webp','assets/video/avatar-the-last-airbender/Avatar-The-Last-Airbender_btn.webm','','assets/video/avatar-the-last-airbender/Avatar-The-Last-Airbender_Background.webm'),(35,'STRANGER-THINGS',9,1,'assets/audio/stranger-things/Stranger-Things_Music.mp3','','','assets/img/stranger-things/Stranger-Things_btn.webp','assets/video/stranger-things/Stranger-Things_btn.webm','','assets/video/stranger-things/Stranger-Things_Background.webm');

--
-- Table structure for table `pear_univers_lang`
--

CREATE TABLE `pear_univers_lang` (
  `id` int unsigned NOT NULL,
  `idUnivers` int unsigned NOT NULL,
  `idLang` int unsigned NOT NULL,
  `titleSEO` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `descriptionSEO` varchar(256) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `keywordsSEO` varchar(256) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `buttonURL` varchar(1024) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `foreign_pear_univers_lang_idUnivers` (`idUnivers`),
  KEY `foreign_pear_univers_lang_idLang` (`idLang`),
  CONSTRAINT `foreign_pear_univers_lang_idLang` FOREIGN KEY (`idLang`) REFERENCES `pear_lang` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `foreign_pear_univers_lang_idUnivers` FOREIGN KEY (`idUnivers`) REFERENCES `pear_univers` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Dumping data for table `pear_univers_lang`
--

INSERT INTO `pear_univers_lang` VALUES (1,1,1,'Marvel Studios','Chronologie du Marvel Cinematic Universe (MCU) (films, séries & courts-métrages) selon l’ordre de sortie et de visionnage — régulièrement mise à jour par PEAR.','Chronologie, marvel, mcu, marvel studios, avengers, comics, films, cinema, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/MarvelStudios_universes_btn.webp'),(2,1,2,'Marvel Studios','Chronology of the Marvel Cinematic Universe (MCU) (movies, series & shorts) by release and viewing order — regularly updated by PEAR.','Timeline, marvel, mcu, marvel studios, avengers, comics, movies, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/MarvelStudios_universes_btn.webp'),(3,2,1,'Star Wars','Chronologie de Star Wars (films, séries & courts-métrages) selon l’ordre de sortie et de visionnage canon — régulièrement mise à jour par PEAR.','Chronologie,  starwars, lucas film, films, cinéma, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/StarWars_universes_btn.webp'),(4,2,2,'Star Wars','Star Wars timeline (films, series & short films) by release order and canon viewing order — regularly updated by PEAR.','Timeline, starwars, lucas film, films, cinema, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/StarWars_universes_btn.webp'),(5,3,1,'Jurassic World','Chronologie de l\'univers Jurassic (Jurassic World et Jurassic Park) (films, séries & courts-métrages) selon l’ordre de sortie et de visionnage — régulièrement mise à jour par PEAR.','Chronologie, jurassic world, jurassic park, universal, dinosaure, films, cinéma, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/JurassicWorld_universes_btn.webp'),(6,3,2,'Jurassic World','Jurassic Universe timeline (Jurassic World and Jurassic Park) (films, series & short films) by release order and viewing order — regularly updated by PEAR.','Timeline, jurassic world, jurassic park, universal, dinosaure, films, cinéma, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/JurassicWorld_universes_btn.webp'),(7,4,1,'Alien & Predator','Chronologie des Aliens & Predators (films & séries) selon l’ordre de sortie et de visionnage — régulièrement mise à jour par PEAR.','Chronologie, predator, alien, triller, horreur, films, cinéma, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/Predalien_universes_btn.webp'),(8,4,2,'Alien & Predator','Aliens & Predators timeline (films & series) by release order and viewing order — regularly updated by PEAR.','Timeline, predator, alien, triller, horror, movies, cinema, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/Predalien_universes_btn.webp'),(9,5,1,'Wizarding World','La chronologie de sortie et de visionage des films Harry Potter et les Animaux Fantastiques présentée par PEAR Pop Culture','Chronologie, harry potter, animaux fantastiques, sorciers, wizarding world, cinema, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/WizardingWorld_universes_btn.webp'),(10,5,2,'Wizarding World','Harry Potter and the Fantastic Beasts\'s timeline for the release and viewing of movies presented by PEAR Pop Culture','Timeline, harry potter, fantastic beasts, wizard, wizarding world, movies, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/WizardingWorld_universes_btn.webp'),(11,6,1,'Game of Thrones','La chronologie de sortie et de visionage des séries de l\'univers Game of Thrones présentée par PEAR Pop Culture','Chronologie, game of thrones, house of the dragon, séries, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/GameOfThrones_universes_btn.webp'),(12,6,2,'Game of Thrones','The Game of Thrones\'s timeline for the release and viewing timeline presented by PEAR Pop Culture','Timeline, game of thrones, house of the dragon, series, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/GameOfThrones_universes_btn.webp'),(13,7,1,'Le Seigneur des anneaux','La chronologie de sortie et de visionage des films et séries de l\'univers Le Seigneur des anneux présentée par PEAR Pop Culture','Chronologie, seigneur des anneaux, anneaux de pouvoir, lord of the rings, films, séries, cinéma, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/LeSeigneurDesAnneaux_universes_btn.webp'),(14,7,2,'The Lord of the Rings','The Lord of the Rings\'s timeline for the release and viewing of movies presented by PEAR Pop Culture','Timeline, lord of the rings, the rings of power, films, series, cinema, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/TheLordOfTheRings_universes_btn.webp'),(15,8,1,'Fortnite','La chronologie de sortie et de visionage des saisons et comics de l\'univers Fortnite présentée par PEAR Pop Culture','Chronologie, fortnite, epic games, jeux vidéos, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/Fortnite_universes_btn.webp'),(16,8,2,'Fortnite','Fortnite\'s timeline for the release and viewing of seasons and comics presented by PEAR Pop Culture','Timeline, fortnite, epic games, videos games, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/Fortnite_universes_btn.webp'),(17,9,1,'Avatar','La chronologie de sortie et de visionage des films et jeux vidéos de l\'univers Avatar présentée par PEAR Pop Culture','Chronologie, avatar, james cameron, film, cinéma, pandora, jeux vidéos, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/Avatar_universes_btn.webp'),(18,9,2,'Avatar','Avatar\'s timeline for the release and viewing of films and videos games presented by PEAR Pop Culture','Timeline, avatar, james cameron, film, cinema, pandora, videos games, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/Avatar_universes_btn.webp'),(19,10,1,'Terminator','La chronologie de sortie et de visionage des films et série de l\'univers Terminator présentée par PEAR Pop Culture','Chronologie, terminator, film, cinéma, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/Terminator_universes_btn.webp'),(20,10,2,'Terminator','Terminator\'s timeline for the release and viewing of films and series presented by PEAR Pop Culture','Timeline, terminator, film, cinema, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/Terminator_universes_btn.webp'),(21,11,1,'MonsterVerse','La chronologie de sortie et de visionage des films et série de l\'univers MonsterVerse présentée par PEAR Pop Culture','Chronologie, monsterverse, godzilla, king kong, film, cinéma, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/MonsterVerse_universes_btn.webp'),(22,11,2,'MonsterVerse','MonsterVerse\'s timeline for the release and viewing of films and series presented by PEAR Pop Culture','Timeline, monsterverse, godzilla, king kong, film, cinema, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/MonsterVerse_universes_btn.webp'),(23,12,1,'DC Univers Étendu','La chronologie de sortie et de visionage des films l\'univers étendu DC présentée par PEAR Pop Culture','Chronologie, dc, justice league, batman, superman, wonderwoman, aquaman, flash, shazam, suicid squad, film, cinéma, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/DCExtendedUniverse_universes_btn.webp'),(24,12,2,'DC Extended Universe','Extended Universe\'s timeline for the release and viewing of films presented by PEAR Pop Culture','Timeline, dc, justice league, batman, superman, wonderwoman, aquaman, flash, shazam, suicid squad, film, cinéma, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/DCExtendedUniverse_universes_btn.webp'),(25,13,1,'Transformers','La chronologie de sortie et de visionage des films l\'univers Transformers présentée par PEAR Pop Culture','Chronologie, transformers, autobots, decepticons, film, cinéma, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/Transformers_universes_btn.webp'),(26,13,2,'Transformers','Transformers\'s timeline for the release and viewing of films presented by PEAR Pop Culture','Timeline, transformers, autobots, decepticons, film, cinéma, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/Transformers_universes_btn.webp'),(27,14,1,'The Hunger Games','La chronologie de sortie et de visionage des films et séries l\'univers The Hunger Games présentée par PEAR Pop Culture','Chronologie, hunger games, film, cinéma, séries, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/TheHungerGames_universes_btn.webp'),(28,14,2,'The Hunger Games','The Hunger Games\'s timeline for the release and viewing of films and series presented by PEAR Pop Culture','Timeline, hunger games, film, cinéma, series, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/TheHungerGames_universes_btn.webp'),(29,15,1,'Matrix','La chronologie de sortie et de visionage des films de l\'univers Matrix présentée par PEAR Pop Culture','Chronologie, matrix, film, cinéma, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/Matrix_universes_btn.webp'),(30,15,2,'Matrix','The Matrix\'s timeline for the release and viewing of films presented by PEAR Pop Culture','Timeline, matrix, film, cinema, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/Matrix_universes_btn.webp'),(31,16,1,'Rocky','La chronologie de sortie et de visionage des films de l\'univers Rocky et Creed présentée par PEAR Pop Culture','Chronologie, Rocky, Creed, film, cinéma, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/Rocky_universes_btn.webp'),(32,16,2,'Rocky','The Rocky\'s and Creed timeline for the release and viewing of films presented by PEAR Pop Culture','Timeline, Rocky, Creed, film, cinema, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/Rocky_universes_btn.webp'),(33,17,1,'John Wick','La chronologie de sortie et de visionage des films et séries de l\'univers John Wick présentée par PEAR Pop Culture','Chronologie, John Wick, Keanu Reeves, film, série, cinéma, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/John-Wick_universes_btn.webp'),(34,17,2,'John Wick','The John Wick\'s timeline for the release and viewing of films and series presented by PEAR Pop Culture','Timeline, John Wick, Keanu Reeves, film, serie, cinema, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/John-Wick_universes_btn.webp'),(35,18,1,'X-Men','La chronologie de sortie et de visionage des films et séries de l\'univers X-Men présentée par PEAR Pop Culture','Chronologie, Marvel, X-Men, film, série, cinéma, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/Xmen_universes_btn.webp'),(36,18,2,'X-Men','The X-Men\'s timeline for the release and viewing of films and series presented by PEAR Pop Culture','Timeline, Marvel, X-Men, film, serie, cinema, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/Xmen_universes_btn.webp'),(37,19,1,'Fast & Furious','La chronologie de sortie et de visionage des films de la saga Fast & Furious présentée par PEAR Pop Culture','Chronologie, Fast & Furious, Fast and Furious, film, série, cinéma, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/FastAndFurious_universes_btn.webp'),(38,19,2,'Fast & Furious','The Fast & Furious saga\'s timeline for the release and viewing of films presented by PEAR Pop Culture','Timeline, Fast & Furious, Fast and Furious, film, serie, cinema, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/FastAndFurious_universes_btn.webp'),(39,20,1,'Pirates des Caraïbes','La chronologie de sortie et de visionage des films de la saga Pirates des Caraïbes présentée par PEAR Pop Culture','Chronologie, Pirates des Caraïbes, film, série, cinéma, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/PiratesDesCaraibes_universes_btn.webp'),(40,20,2,'Pirates of the Caribbean','Pirates of the Caribbean saga\'s timeline for the release and viewing of films presented by PEAR Pop Culture','Timeline, Pirates of the Caribbean, film, serie, cinema, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/PiratesOfTheCaribbean_universes_btn.webp'),(41,21,1,'Mission : Impossible','La chronologie de sortie et de visionage des films de la saga Mission Impossible présentée par PEAR Pop Culture','Chronologie, Impossible, Tom Cruise, film, série, cinéma, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/MissionImpossible_universes_btn.webp'),(42,21,2,'Mission: Impossible','Mission Impossible saga\'s timeline for the release and viewing of films presented by PEAR Pop Culture','Timeline, Mission Impossible, Tom Cruise, film, serie, cinema, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/MissionImpossible_universes_btn.webp'),(43,22,1,'L\'Âge de Glace','La chronologie de sortie et de visionage des films et courts-métrages de la saga L\'Âge de Glace présentée par PEAR Pop Culture','Chronologie, Age de Glace, film, série, cinéma, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/AgeDeGlace_universes_btn.webp'),(44,22,2,'Ice Age','Ice Age saga\'s timeline for the release and viewing of films and shorts presented by PEAR Pop Culture','Timeline, Ice Age, film, serie, cinema, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/IceAge_universes_btn.webp'),(45,23,1,'La Planète des Singes','La chronologie de sortie et de visionage des films de la saga La Planète des Singes présentée par PEAR Pop Culture','Chronologie, La Planète des Singes, film, série, cinéma, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/LaPlaneteDesSinges_universes_btn.webp'),(46,23,2,'Planet of the Apes','Planet of the Apes saga\'s timeline for the release and viewing of films presented by PEAR Pop Culture','Timeline, Planet of the Apes, film, serie, cinema, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/PlanetOfTheApes_universes_btn.webp'),(47,24,1,'Spider-Verse (Spider-Man)','La chronologie de sortie et de visionage des films, séries, court métrages, jeux... des Spider-Man présentée par PEAR Pop Culture','Chronologie, Spider Man, Spider Verse, film, série, cinéma, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/SpiderVerse_universes_btn.webp'),(48,24,2,'Spider-Verse (Spider-Man)','Spider-Man\'s timeline for the release and viewing of films, series, shorts, video games... presented by PEAR Pop Culture','Timeline, Spider Man, Spider Verse, film, serie, cinema, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/SpiderVerse_universes_btn.webp'),(49,25,1,'Shrek','La chronologie de sortie et de visionage des films et court métrages des Shrek présentée par PEAR Pop Culture','Chronologie, Shrek, film, série, cinéma, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/Shrek_universes_btn.webp'),(50,25,2,'Shrek','Shrek saga\'s timeline for the release and viewing of films and shorts presented by PEAR Pop Culture','Timeline, Shrek, film, serie, cinema, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/Shrek_universes_btn.webp'),(51,26,1,'Mad Max','La chronologie de sortie et de visionage des films Mad Max présentée par PEAR Pop Culture','Chronologie, Mad Max, film, cinéma, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/MadMax_universes_btn.webp'),(52,26,2,'Mad Max','Mad Max saga\'s timeline for the release and viewing of films presented by PEAR Pop Culture','Timeline, Mad Max, film, cinema, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/MadMax_universes_btn.webp'),(53,27,1,'Indiana Jones','La chronologie de sortie et de visionage des films et séries Indiana Jones présentée par PEAR Pop Culture','Chronologie, Indiana Jones, serie, film, cinéma, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/IndianaJones_universes_btn.webp'),(54,27,2,'Indiana Jones','Indiana Jones saga\'s timeline for the release and viewing of films and series presented by PEAR Pop Culture','Timeline,  Indiana Jones, serie, film, cinema, PEAR, Pop Culture, ARCAHUS','assets/img/home/buttons/IndianaJones_universes_btn.webp'),(55,28,1,'Dune','La chronologie de sortie et de visionage des films et séries Dune présentée par PEAR Pop Culture','','assets/img/home/buttons/Dune_universes_btn.webp'),(56,28,2,'Dune','Dune saga\'s timeline for the release and viewing of films and series presented by PEAR Pop Culture','','assets/img/home/buttons/Dune_universes_btn.webp'),(57,29,1,'Miraculous','La chronologie de sortie et de visionage des films et séries Miraculous présentée par PEAR Pop Culture','','assets/img/home/buttons/Miraculous_universes_btn.webp'),(58,29,2,'Miraculous','Miraculous saga\'s timeline for the release and viewing of films and series presented by PEAR Pop Culture','','assets/img/home/buttons/Miraculous_universes_btn.webp'),(59,30,1,'DC Studios','La chronologie de sortie et de visionage des films l\'univers DC présentée par PEAR Pop Culture','','assets/img/home/buttons/DC-Studios_universes_btn.webp'),(60,30,2,'DC Studios','DC Universe\'s timeline for the release and viewing of films presented by PEAR Pop Culture','','assets/img/home/buttons/DC-Studios_universes_btn.webp'),(61,31,1,'Dragons','La chronologie de sortie et de visionage des films, séries et court-métrages l\'univers Dragons présentée par PEAR Pop Culture','','assets/img/home/buttons/Dragons_universes_btn.webp'),(62,31,2,'How to Train Your Dragon','How to Train Your Dragon Universe\'s timeline for the release and viewing of films presented by PEAR Pop Culture','','assets/img/home/buttons/How-To-Train-Your-Dragon_universes_btn.webp'),(64,32,1,'The Boys','La chronologie de sortie et de visionage des séries de l\'univers The Boys présentée par PEAR Pop Culture','','assets/img/home/buttons/The-Boys_universes_btn.webp'),(65,32,2,'The Boys','The Boys\' timeline for the release and viewing of series presented by PEAR Pop Culture','','assets/img/home/buttons/The-Boys_universes_btn.webp'),(66,33,1,'The Batman','La chronologie de sortie et de visionage des films et séries de l\'univers The Batman présentée par PEAR Pop Culture','','assets/img/home/buttons/The-Batman_universes_btn.webp'),(67,33,2,'The Batman','The Batman\'s timeline for the release and viewing of films and series presented by PEAR Pop Culture','','assets/img/home/buttons/The-Batman_universes_btn.webp'),(68,34,1,'Avatar, le dernier maître de l\'air','Chronologie de l\'univers de l\'Avatar (films, séries & courts-métrages) selon l’ordre de sortie et de visionnage — régulièrement mise à jour par PEAR.','','assets/img/home/buttons/Avatar-The-Last-Airbender_universes_btn.webp'),(69,34,2,'Avatar: The Last Airbender','Chronology of the Avatar Universe (films, series & short films) in order of release and viewing — regularly updated by PEAR.','','assets/img/home/buttons/Avatar-The-Last-Airbender_universes_btn.webp'),(70,35,1,'Stranger Things','Chronologie de l\'univers de Stranger Things (Séries et pièces de théâtre) selon l’ordre de sortie et de visionnage — régulièrement mise à jour par PEAR.','','assets/img/home/buttons/Stranger-Things_universes_btn.webp'),(71,35,2,'Stranger Things','Chronology of the Stranger Things Universe (Series and plays) in order of release and viewing — regularly updated by PEAR.','','assets/img/home/buttons/Stranger-Things_universes_btn.webp');

--
-- Dumping routines for database 'pearpobarcahus'
--
/*!50003 DROP FUNCTION IF EXISTS `remove_short_words` */;
DELIMITER ;;
CREATE FUNCTION `remove_short_words`(`inStr` LONGTEXT, `minLength` INT) RETURNS longtext CHARSET latin1
BEGIN
    DECLARE retStr LONGTEXT DEFAULT "";
    DECLARE tempStr LONGTEXT DEFAULT "";
    DECLARE aWord LONGTEXT DEFAULT "";
    DECLARE wordLength INT DEFAULT 0;
    SET tempStr = TRIM(inStr);
    WHILE (LENGTH(tempStr) > 0) DO
        SET aWord = SUBSTRING_INDEX(tempStr, " ", 1);
        SET wordLength = LENGTH(aWord);
        IF (wordLength >= minLength) THEN
            SET retStr = CONCAT(retStr, " ", aWord);
        END IF;
        SET tempStr = TRIM(SUBSTRING(tempStr, wordLength + 1));
    END WHILE;

    RETURN TRIM(retStr);
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `remove_special_chars` */;
DELIMITER ;;
CREATE FUNCTION `remove_special_chars`(`inStr` LONGTEXT) RETURNS longtext CHARSET latin1
BEGIN
    -- RETURN REGEXP_REPLACE(inStr, '[\]\\[?!£@#$%&§`~^_{}.:,;></\\|()=*\+-]+', '');
    -- n'est pas disponoble avant la version 8 de mysql
    SET inStr = REPLACE(inStr, '[', '');
    SET inStr = REPLACE(inStr, ']', '');
    SET inStr = REPLACE(inStr, '(', '');
    SET inStr = REPLACE(inStr, ')', '');
    SET inStr = REPLACE(inStr, '{', '');
    SET inStr = REPLACE(inStr, '}', '');
    SET inStr = REPLACE(inStr, '&', '');
    SET inStr = REPLACE(inStr, '~', '');
    SET inStr = REPLACE(inStr, '"', '');
    SET inStr = REPLACE(inStr, '#', '');
    SET inStr = REPLACE(inStr, '\'', '');
    SET inStr = REPLACE(inStr, '-', '');
    SET inStr = REPLACE(inStr, '|', '');
    SET inStr = REPLACE(inStr, '`', '');
    SET inStr = REPLACE(inStr, '_', '');
    SET inStr = REPLACE(inStr, '\\', '');
    SET inStr = REPLACE(inStr, '^', '');
    SET inStr = REPLACE(inStr, '@', '');
    SET inStr = REPLACE(inStr, '°', '');
    SET inStr = REPLACE(inStr, '+', '');
    SET inStr = REPLACE(inStr, '=', '');
    SET inStr = REPLACE(inStr, '¨', '');
    SET inStr = REPLACE(inStr, '£', '');
    SET inStr = REPLACE(inStr, '$', '');
    SET inStr = REPLACE(inStr, '¤', '');
    SET inStr = REPLACE(inStr, '*', '');
    SET inStr = REPLACE(inStr, '?', '');
    SET inStr = REPLACE(inStr, ',', '');
    SET inStr = REPLACE(inStr, '.', '');
    SET inStr = REPLACE(inStr, ';', '');
    SET inStr = REPLACE(inStr, '/', '');
    SET inStr = REPLACE(inStr, ':', '');
    SET inStr = REPLACE(inStr, '§', '');
    SET inStr = REPLACE(inStr, '!', '');
    RETURN inStr;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `get_content` */;
DELIMITER ;;
CREATE PROCEDURE `get_content`(contentCode VARCHAR(128), langAlpha2 VARCHAR(2))
BEGIN
    SELECT
        univers.id AS universID,
        LOWER(univers.code) AS universCode,
        univers.displayOrder AS universDisplayOrder,
        univers.iconURL AS universIconURL,
        univers.logoURL AS universLogoURL,
        univers.buttonImageURL AS universButtonImageURL,
        univers.buttonVideoURL AS universButtonVideoURL,
        univers.backgroundImageURL AS universBackgroundImageURL,
        univers.backgroundVideoURL AS universBackgroundVideoURL,
        univers_lang.id AS universLangId,
        univers_lang.titleSEO AS universLangTitleSEO,
        univers_lang.descriptionSEO AS universLangDescriptionSEO,
        univers_lang.keywordsSEO AS universLangKeywordsSEO,
        content.id AS contentId,
        content.viewingOrder AS contentViewingOrder,
        LOWER(content.code) AS contentCode,
        content.phase AS contentPhase,
        content.director AS contentDirector,
        content.studio AS contentStudio,
        content.duration AS contentDuration,
        content.postCreditScene AS contentPostCreditScene,
        content_lang.id AS contentLangId,
        content_lang.category AS contentLangCategory,
        content_lang.title AS contentLangTitle,
        content_lang.description AS contentLangDescription,
        content_lang.releaseDate AS contentLangReleaseDate,
        content_lang.releaseLabel AS contentLangReleaseLabel,
        content_lang.trailerURL AS contentLangTrailerURL,
        content_lang.logoURL AS contentLangLogoURL,
        content_lang.posterURL AS contentLangPosterURL,
        LOWER(type.code) as typeCode,
        lang.alpha2 AS langAlpha2
    FROM
        pear_content_lang AS content_lang
        INNER JOIN pear_lang AS lang ON lang.id = content_lang.idLang
        INNER JOIN pear_content AS content ON content.id = content_lang.idContent
        INNER JOIN pear_univers AS univers ON univers.id = content.idUnivers
        INNER JOIN pear_univers_lang AS univers_lang ON univers_lang.idUnivers = univers.id
        INNER JOIN pear_contenttype AS type ON type.id = content.idContentType
    WHERE
        lang.alpha2 = LOWER(langAlpha2)
        AND content.code = UPPER(contentCode)
    ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `get_contents` */;
DELIMITER ;;
CREATE PROCEDURE `get_contents`(universCode VARCHAR(128), langAlpha2 VARCHAR(2), orderBy VARCHAR(32))
BEGIN
    SELECT
        content.id AS contentId,
        content.viewingOrder AS contentViewingOrder,
        LOWER(content.code) AS contentCode,
        content.phase AS contentPhase,
        content.director AS contentDirector,
        content.studio AS contentStudio,
        content.duration AS contentDuration,
        content.postCreditScene AS contentPostCreditScene,
        content_lang.id AS contentLangId,
        content_lang.category AS contentLangCategory,
        content_lang.title AS contentLangTitle,
        content_lang.description AS contentLangDescription,
        content_lang.releaseDate AS contentLangReleaseDate,
        content_lang.releaseLabel AS contentLangReleaseLabel,
        content_lang.isUSAreleaseDate AS contentLangIsUSAreleaseDate,
        content_lang.trailerURL AS contentLangTrailerURL,
        content_lang.trailerToNewPage AS contentLangTrailerToNewPage,
        content_lang.logoURL AS contentLangLogoURL,
        content_lang.posterURL AS contentLangPosterURL,
        LOWER(type.code) AS typeCode,
        lang.alpha2 AS langAlpha2
    FROM
        pear_content_lang AS content_lang
        INNER JOIN pear_lang AS lang ON lang.id = content_lang.idLang
        INNER JOIN pear_content AS content ON content.id = content_lang.idContent
        INNER JOIN pear_univers AS univers ON univers.id = content.idUnivers
        INNER JOIN pear_contenttype AS type ON type.id = content.idContentType
    WHERE
        lang.alpha2 = LOWER(langAlpha2)
        AND univers.code = UPPER(universCode)
        AND CASE WHEN orderBy = 'viewingOrder' THEN content.viewingOrder IS NOT NULL ELSE TRUE END
    ORDER BY
        CASE WHEN orderBy = 'releaseDate' THEN content_lang.releaseDate END ASC,
        CASE WHEN orderBy = 'viewingOrder' THEN content.viewingOrder END ASC,
        CASE WHEN orderBy <> 'releaseDate' AND orderBy <> 'viewingOrder' THEN content.id END ASC
    ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `get_univers` */;
DELIMITER ;;
CREATE PROCEDURE `get_univers`(IN `universCode` VARCHAR(128), IN `langAlpha2` VARCHAR(2))
BEGIN
    SELECT
        univers.id AS universID,
        LOWER(univers.code) AS universCode,
        univers.displayOrder AS universDisplayOrder,
        univers.alternateContent AS universAlternateContent,
        univers.musicURL AS universMusicURL,
        univers.iconURL AS universIconURL,
        univers.logoURL AS universLogoURL,
        univers.buttonImageURL AS universButtonImageURL,
        univers.buttonVideoURL AS universButtonVideoURL,
        univers.backgroundImageURL AS universBackgroundImageURL,
        univers.backgroundVideoURL AS universBackgroundVideoURL,
        univers_lang.id AS universLangId,
        univers_lang.titleSEO AS universLangTitleSEO,
        univers_lang.descriptionSEO AS universLangDescriptionSEO,
        univers_lang.keywordsSEO AS universLangKeywordsSEO,
        univers_lang.buttonURL AS universLangButtonURL,
        lang.alpha2 AS langAlpha2
    FROM
        pear_univers_lang AS univers_lang
        INNER JOIN pear_lang AS lang ON lang.id = univers_lang.idLang
        INNER JOIN pear_univers AS univers ON univers.id = univers_lang.idUnivers
    WHERE
        lang.alpha2 = LOWER(langAlpha2)
        AND
        CASE
            WHEN universCode IS NOT NULL THEN univers.code = UPPER(universCode)
            ELSE 1
        END
    ORDER BY
        univers.displayOrder
    ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `get_universes` */;
DELIMITER ;;
CREATE PROCEDURE `get_universes`(IN `langAlpha2` VARCHAR(2))
BEGIN
    SELECT
        univers.id AS universID,
        LOWER(univers.code) AS universCode,
        univers.displayOrder AS universDisplayOrder,
        univers.alternateContent AS universAlternateContent,
        univers.musicURL AS universMusicURL,
        univers.iconURL AS universIconURL,
        univers.logoURL AS universLogoURL,
        univers.buttonImageURL AS universButtonImageURL,
        univers.buttonVideoURL AS universButtonVideoURL,
        univers.backgroundImageURL AS universBackgroundImageURL,
        univers.backgroundVideoURL AS universBackgroundVideoURL,
        univers_lang.titleSEO AS universLangTitleSEO,
        univers_lang.buttonURL AS universLangButtonURL
    FROM pear_univers AS univers
    INNER JOIN pear_univers_lang AS univers_lang ON univers.id = univers_lang.idUnivers
    INNER JOIN pear_lang AS lang ON lang.id = univers_lang.idLang
    WHERE
    lang.alpha2 = LOWER(langAlpha2)
    ORDER BY univers.displayOrder
    ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `search` */;
DELIMITER ;;
CREATE PROCEDURE `search`(IN `search` LONGTEXT, IN `lang` CHAR(2), IN `minLength` INT)
BEGIN

DECLARE exactSearch LONGTEXT DEFAULT '';
DECLARE partialSearch LONGTEXT DEFAULT '';

SET exactSearch =
REPLACE(
    LOWER(
        TRIM(
            search
        )
    )
,' ', '%');

SET partialSearch =
REPLACE(
    LOWER(
        remove_short_words(
            remove_special_chars(
                search
            )
        , minLength)
    )
,' ', '%');

CREATE TEMPORARY TABLE temporary_table (
    `rank` INT,
    `type` VARCHAR(128),
    `id` INT,
    `code` VARCHAR(128),
    `universCode` VARCHAR(128),
    `title` VARCHAR(128),
    `description` VARCHAR(4096),
    `image` VARCHAR(1024)
);


SET @sql1 =
CONCAT(
'\r\nINSERT INTO temporary_table (',
'\r\n  SELECT',
'\r\n  1 AS `rank`,',
'\r\n  "univers" AS `type`,',
'\r\n  univers.id AS `id`,',
'\r\n  univers.code AS `code`,',
'\r\n  univers.code AS `univerCode`,',
'\r\n  univerLang.titleSEO AS `title`,',
'\r\n  univerLang.descriptionSEO AS `description`,',
'\r\n  univerLang.buttonURL AS `image`',
'\r\n  FROM pear_univers AS univers',
'\r\n  INNER JOIN pear_univers_lang AS univerLang ON univers.id = univerLang.idUnivers',
'\r\n  INNER JOIN pear_lang AS lang ON lang.Id = univerLang.idLang',
'\r\n  WHERE',
'\r\n  lang.alpha2 = "', lang, '"',
'\r\n  AND',
'\r\n  LOWER(univerLang.titleSEO) = "', exactSearch, '"',
'\r\n);'
);

SET @sql2 =
CONCAT(
'\r\nINSERT INTO temporary_table (',
'\r\n  SELECT',
'\r\n  2 AS `rank`,',
'\r\n  "content" AS `type`,',
'\r\n  content.id AS `id`,',
'\r\n  content.code AS `code`,',
'\r\n  univers.code AS `univerCode`,',
'\r\n  contentLang.title AS `title`,',
'\r\n  contentLang.description AS `description`,',
'\r\n  contentLang.logoURL AS `image`',
'\r\n  FROM pear_content AS content',
'\r\n  INNER JOIN pear_content_lang AS contentLang ON contentLang.idContent = content.id',
'\r\n  INNER JOIN pear_univers AS univers ON univers.id = content.idUnivers',
'\r\n  INNER JOIN pear_lang AS lang ON lang.Id = contentLang.idLang',
'\r\n  WHERE',
'\r\n  lang.alpha2 = "', lang, '"',
'\r\n  AND',
'\r\n  LOWER(contentLang.title) = "', exactSearch, '"',
'\r\n);'
);

SET @sql3 =
CONCAT(
'\r\nINSERT INTO temporary_table (',
'\r\n  SELECT',
'\r\n  3 AS `rank`,',
'\r\n  "univers" AS `type`,',
'\r\n  univers.id AS `id`,',
'\r\n  univers.code AS `code`,',
'\r\n  univers.code AS `univerCode`,',
'\r\n  univerLang.titleSEO AS `title`,',
'\r\n  univerLang.descriptionSEO AS `description`,',
'\r\n  univerLang.buttonURL AS `image`',
'\r\n  FROM pear_univers AS univers',
'\r\n  INNER JOIN pear_univers_lang AS univerLang ON univers.id = univerLang.idUnivers',
'\r\n  INNER JOIN pear_lang AS lang ON lang.Id = univerLang.idLang',
'\r\n  WHERE',
'\r\n  lang.alpha2 = "', lang, '"',
'\r\n  AND',
'\r\n  LOWER(univerLang.titleSEO) LIKE "%', exactSearch, '%"',
'\r\n);'
);

SET @sql4 =
CONCAT(
'\r\nINSERT INTO temporary_table (',
'\r\n  SELECT',
'\r\n  4 AS `rank`,',
'\r\n  "univers" AS `type`,',
'\r\n  univers.id AS `id`,',
'\r\n  univers.code AS `code`,',
'\r\n  univers.code AS `univerCode`,',
'\r\n  univerLang.titleSEO AS `title`,',
'\r\n  univerLang.descriptionSEO AS `description`,',
'\r\n  univerLang.buttonURL AS `image`',
'\r\n  FROM pear_univers AS univers',
'\r\n  INNER JOIN pear_univers_lang AS univerLang ON univers.id = univerLang.idUnivers',
'\r\n  INNER JOIN pear_lang AS lang ON lang.Id = univerLang.idLang',
'\r\n  WHERE',
'\r\n  lang.alpha2 = "', lang, '"',
'\r\n  AND',
'\r\n  LOWER(univerLang.descriptionSEO) LIKE "%', exactSearch, '%"',
'\r\n);'
);

SET @sql5 =
CONCAT(
'\r\nINSERT INTO temporary_table (',
'\r\n  SELECT',
'\r\n  5 AS `rank`,',
'\r\n  "content" AS `type`,',
'\r\n  content.id AS `id`,',
'\r\n  content.code AS `code`,',
'\r\n  univers.code AS `univerCode`,',
'\r\n  contentLang.title AS `title`,',
'\r\n  contentLang.description AS `description`,',
'\r\n  contentLang.logoURL AS `image`',
'\r\n  FROM pear_content AS content',
'\r\n  INNER JOIN pear_content_lang AS contentLang ON contentLang.idContent = content.id',
'\r\n  INNER JOIN pear_univers AS univers ON univers.id = content.idUnivers',
'\r\n  INNER JOIN pear_lang AS lang ON lang.Id = contentLang.idLang',
'\r\n  WHERE',
'\r\n  lang.alpha2 = "', lang, '"',
'\r\n  AND',
'\r\n  LOWER(contentLang.title) LIKE "%', exactSearch, '%"',
'\r\n);'
);

SET @sql6 =
CONCAT(
'\r\nINSERT INTO temporary_table (',
'\r\n  SELECT',
'\r\n  6 AS `rank`,',
'\r\n  "content" AS `type`,',
'\r\n  content.id AS `id`,',
'\r\n  content.code AS `code`,',
'\r\n  univers.code AS `univerCode`,',
'\r\n  contentLang.title AS `title`,',
'\r\n  contentLang.description AS `description`,',
'\r\n  contentLang.logoURL AS `image`',
'\r\n  FROM pear_content AS content',
'\r\n  INNER JOIN pear_content_lang AS contentLang ON contentLang.idContent = content.id',
'\r\n  INNER JOIN pear_univers AS univers ON univers.id = content.idUnivers',
'\r\n  INNER JOIN pear_lang AS lang ON lang.Id = contentLang.idLang',
'\r\n  WHERE',
'\r\n  lang.alpha2 = "', lang, '"',
'\r\n  AND',
'\r\n  LOWER(contentLang.description) LIKE "%', exactSearch,'%"',
'\r\n);'
);

SET @sql7 =
CONCAT(
'\r\nINSERT INTO temporary_table (',
'\r\n  SELECT',
'\r\n  7 AS `rank`,',
'\r\n  "univers" AS `type`,',
'\r\n  univers.id AS `id`,',
'\r\n  univers.code AS `code`,',
'\r\n  univers.code AS `univerCode`,',
'\r\n  univerLang.titleSEO AS `title`,',
'\r\n  univerLang.descriptionSEO AS `description`,',
'\r\n  univerLang.buttonURL AS `image`',
'\r\n  FROM pear_univers AS univers',
'\r\n  INNER JOIN pear_univers_lang AS univerLang ON univers.id = univerLang.idUnivers',
'\r\n  INNER JOIN pear_lang AS lang ON lang.Id = univerLang.idLang',
'\r\n  WHERE',
'\r\n  lang.alpha2 = "', lang, '"',
'\r\n  AND',
'\r\n  univers.id IN (',
'\r\n    SELECT',
'\r\n    DISTINCT content.idUnivers',
'\r\n    FROM pear_content AS content',
'\r\n    INNER JOIN pear_content_lang AS contentLang ON contentLang.idContent = content.id',
'\r\n    INNER JOIN pear_lang AS lang ON lang.Id = contentLang.idLang',
'\r\n    WHERE',
'\r\n    lang.alpha2 = "', lang, '"',
'\r\n    AND',
'\r\n    (',
'\r\n      LOWER(contentLang.title) LIKE "%', exactSearch, '%"',
'\r\n      OR LOWER(contentLang.description) LIKE "%', exactSearch, '%"',
'\r\n    )',
'\r\n  )',
'\r\n);'
);

SET @sql8 =
CONCAT(
'\r\nINSERT INTO temporary_table (',
'\r\n  SELECT',
'\r\n  8 AS `rank`,',
'\r\n  "univers" AS `type`,',
'\r\n  univers.id AS `id`,',
'\r\n  univers.code AS `code`,',
'\r\n  univers.code AS `univerCode`,',
'\r\n  univerLang.titleSEO AS `title`,',
'\r\n  univerLang.descriptionSEO AS `description`,',
'\r\n  univerLang.buttonURL AS `image`',
'\r\n  FROM pear_univers AS univers',
'\r\n  INNER JOIN pear_univers_lang AS univerLang ON univers.id = univerLang.idUnivers',
'\r\n  INNER JOIN pear_lang AS lang ON lang.Id = univerLang.idLang',
'\r\n  WHERE',
'\r\n  lang.alpha2 = "', lang, '"',
'\r\n  AND',
'\r\n  LOWER(univerLang.titleSEO) LIKE "%', partialSearch, '%"',
'\r\n);'
);

SET @sql9 =
CONCAT(
'\r\nINSERT INTO temporary_table (',
'\r\n  SELECT',
'\r\n  9 AS `rank`,',
'\r\n  "univers" AS `type`,',
'\r\n  univers.id AS `id`,',
'\r\n  univers.code AS `code`,',
'\r\n  univers.code AS `univerCode`,',
'\r\n  univerLang.titleSEO AS `title`,',
'\r\n  univerLang.descriptionSEO AS `description`,',
'\r\n  univerLang.buttonURL AS `image`',
'\r\n  FROM pear_univers AS univers',
'\r\n  INNER JOIN pear_univers_lang AS univerLang ON univers.id = univerLang.idUnivers',
'\r\n  INNER JOIN pear_lang AS lang ON lang.Id = univerLang.idLang',
'\r\n  WHERE',
'\r\n  lang.alpha2 = "', lang, '"',
'\r\n  AND',
'\r\n  LOWER(univerLang.descriptionSEO) LIKE "%', partialSearch, '%"',
'\r\n);'
);

SET @sql10 =
CONCAT(
'\r\nINSERT INTO temporary_table (',
'\r\n  SELECT',
'\r\n  10 AS `rank`,',
'\r\n  "content" AS `type`,',
'\r\n  content.id AS `id`,',
'\r\n  content.code AS `code`,',
'\r\n  univers.code AS `univerCode`,',
'\r\n  contentLang.title AS `title`,',
'\r\n  contentLang.description AS `description`,',
'\r\n  contentLang.logoURL AS `image`',
'\r\n  FROM pear_content AS content',
'\r\n  INNER JOIN pear_content_lang AS contentLang ON contentLang.idContent = content.id',
'\r\n  INNER JOIN pear_univers AS univers ON univers.id = content.idUnivers',
'\r\n  INNER JOIN pear_lang AS lang ON lang.Id = contentLang.idLang',
'\r\n  WHERE',
'\r\n  lang.alpha2 = "', lang, '"',
'\r\n  AND',
'\r\n  (',
'\r\n    LOWER(contentLang.title) LIKE "%', partialSearch, '%"',
'\r\n    OR LOWER(contentLang.description) LIKE "%', partialSearch, '%"',
'\r\n  )',
'\r\n);'
);

SET @sql11 =
CONCAT(
'\r\nINSERT INTO temporary_table (',
'\r\n  SELECT',
'\r\n  11 AS `rank`,',
'\r\n  "content" AS `type`,',
'\r\n  content.id AS `id`,',
'\r\n  content.code AS `code`,',
'\r\n  univers.code AS `univerCode`,',
'\r\n  contentLang.title AS `title`,',
'\r\n  contentLang.description AS `description`,',
'\r\n  contentLang.logoURL AS `image`',
'\r\n  FROM pear_content AS content',
'\r\n  INNER JOIN pear_content_lang AS contentLang ON contentLang.idContent = content.id',
'\r\n  INNER JOIN pear_univers AS univers ON univers.id = content.idUnivers',
'\r\n  INNER JOIN pear_lang AS lang ON lang.Id = contentLang.idLang',
'\r\n  WHERE',
'\r\n  lang.alpha2 = "', lang, '"',
'\r\n  AND',
'\r\n  LOWER(contentLang.description) LIKE "%', partialSearch, '%"',
'\r\n);'
);

SET @sql12 =
CONCAT(
'\r\nINSERT INTO temporary_table (',
'\r\n  SELECT',
'\r\n  12 AS `rank`,',
'\r\n  "univers" AS `type`,',
'\r\n  univers.id AS `id`,',
'\r\n  univers.code AS `code`,',
'\r\n  univers.code AS `univerCode`,',
'\r\n  univerLang.titleSEO AS `title`,',
'\r\n  univerLang.descriptionSEO AS `description`,',
'\r\n  univerLang.buttonURL AS `image`',
'\r\n  FROM pear_univers AS univers',
'\r\n  INNER JOIN pear_univers_lang AS univerLang ON univers.id = univerLang.idUnivers',
'\r\n  INNER JOIN pear_lang AS lang ON lang.Id = univerLang.idLang',
'\r\n  WHERE',
'\r\n  lang.alpha2 = "', lang, '"',
'\r\n  AND',
'\r\n  univers.id IN (',
'\r\n    SELECT',
'\r\n    DISTINCT content.idUnivers',
'\r\n    FROM pear_content AS content',
'\r\n    INNER JOIN pear_content_lang AS contentLang ON contentLang.idContent = content.id',
'\r\n    INNER JOIN pear_lang AS lang ON lang.Id = contentLang.idLang',
'\r\n    WHERE',
'\r\n    lang.alpha2 = "', lang, '"',
'\r\n    AND',
'\r\n    (',
'\r\n      LOWER(contentLang.title) LIKE "%', partialSearch, '%"',
'\r\n      OR LOWER(contentLang.description) LIKE "%', partialSearch, '%"',
'\r\n    )',
'\r\n  )',
'\r\n);'
);

SET @sql13 =
CONCAT(
'\r\nINSERT INTO temporary_table (',
'\r\n  SELECT',
'\r\n  13 AS `rank`,',
'\r\n  "univers" AS `type`,',
'\r\n  univers.id AS `id`,',
'\r\n  univers.code AS `code`,',
'\r\n  univers.code AS `univerCode`,',
'\r\n  univerLang.titleSEO AS `title`,',
'\r\n  univerLang.descriptionSEO AS `description`,',
'\r\n  univerLang.buttonURL AS `image`',
'\r\n  FROM pear_univers AS univers',
'\r\n  INNER JOIN pear_univers_lang AS univerLang ON univers.id = univerLang.idUnivers',
'\r\n  INNER JOIN pear_lang AS lang ON lang.Id = univerLang.idLang',
'\r\n  WHERE',
'\r\n  lang.alpha2 = "', lang, '"',
'\r\n  AND',
'\r\n  (LOWER(univerLang.titleSEO) LIKE "%', REPLACE(partialSearch,'%', '%" OR LOWER(univerLang.titleSEO) LIKE \"%'), '%")',
'\r\n);'
);

SET @sql14 =
CONCAT(
'\r\nINSERT INTO temporary_table (',
'\r\n  SELECT',
'\r\n  14 AS `rank`,',
'\r\n  "univers" AS `type`,',
'\r\n  univers.id AS `id`,',
'\r\n  univers.code AS `code`,',
'\r\n  univers.code AS `univerCode`,',
'\r\n  univerLang.titleSEO AS `title`,',
'\r\n  univerLang.descriptionSEO AS `description`,',
'\r\n  univerLang.buttonURL AS `image`',
'\r\n  FROM pear_univers AS univers',
'\r\n  INNER JOIN pear_univers_lang AS univerLang ON univers.id = univerLang.idUnivers',
'\r\n  INNER JOIN pear_lang AS lang ON lang.Id = univerLang.idLang',
'\r\n  WHERE',
'\r\n  lang.alpha2 = "', lang, '"',
'\r\n  AND',
'\r\n  (LOWER(univerLang.descriptionSEO) LIKE "%', REPLACE(partialSearch,'%', '%" OR LOWER(univerLang.descriptionSEO) LIKE \"%'), '%")',
'\r\n);'
);

SET @sql15 =
CONCAT(
'\r\nINSERT INTO temporary_table (',
'\r\n  SELECT',
'\r\n  15 AS `rank`,',
'\r\n  "content" AS `type`,',
'\r\n  content.id AS `id`,',
'\r\n  content.code AS `code`,',
'\r\n  univers.code AS `univerCode`,',
'\r\n  contentLang.title AS `title`,',
'\r\n  contentLang.description AS `description`,',
'\r\n  contentLang.logoURL AS `image`',
'\r\n  FROM pear_content AS content',
'\r\n  INNER JOIN pear_content_lang AS contentLang ON contentLang.idContent = content.id',
'\r\n  INNER JOIN pear_univers AS univers ON univers.id = content.idUnivers',
'\r\n  INNER JOIN pear_lang AS lang ON lang.Id = contentLang.idLang',
'\r\n  WHERE',
'\r\n  lang.alpha2 = "', lang, '"',
'\r\n  AND',
'\r\n  (LOWER(contentLang.title) LIKE "%', REPLACE(partialSearch,'%', '%" OR LOWER(contentLang.title) LIKE \"%'), '%")',
'\r\n);'
);

SET @sql16 =
CONCAT(
'\r\nINSERT INTO temporary_table (',
'\r\n  SELECT',
'\r\n  16 AS `rank`,',
'\r\n  "content" AS `type`,',
'\r\n  content.id AS `id`,',
'\r\n  content.code AS `code`,',
'\r\n  univers.code AS `univerCode`,',
'\r\n  contentLang.title AS `title`,',
'\r\n  contentLang.description AS `description`,',
'\r\n  contentLang.logoURL AS `image`',
'\r\n  FROM pear_content AS content',
'\r\n  INNER JOIN pear_content_lang AS contentLang ON contentLang.idContent = content.id',
'\r\n  INNER JOIN pear_univers AS univers ON univers.id = content.idUnivers',
'\r\n  INNER JOIN pear_lang AS lang ON lang.Id = contentLang.idLang',
'\r\n  WHERE',
'\r\n  lang.alpha2 = "', lang, '"',
'\r\n  AND',
'\r\n  (LOWER(contentLang.description) LIKE "%', REPLACE(partialSearch,'%', '%" OR LOWER(contentLang.description) LIKE \"%'), '%")',
'\r\n);'
);

SET @sql17 =
CONCAT(
'\r\nINSERT INTO temporary_table (',
'\r\n  SELECT',
'\r\n  17 AS `rank`,',
'\r\n  "univers" AS `type`,',
'\r\n  univers.id AS `id`,',
'\r\n  univers.code AS `code`,',
'\r\n  univers.code AS `univerCode`,',
'\r\n  univerLang.titleSEO AS `title`,',
'\r\n  univerLang.descriptionSEO AS `description`,',
'\r\n  univerLang.buttonURL AS `image`',
'\r\n  FROM pear_univers AS univers',
'\r\n  INNER JOIN pear_univers_lang AS univerLang ON univers.id = univerLang.idUnivers',
'\r\n  INNER JOIN pear_lang AS lang ON lang.Id = univerLang.idLang',
'\r\n  WHERE',
'\r\n  lang.alpha2 = "', lang, '"',
'\r\n  AND',
'\r\n  univers.id IN (',
'\r\n    SELECT',
'\r\n    DISTINCT content.idUnivers',
'\r\n    FROM pear_content AS content',
'\r\n    INNER JOIN pear_content_lang AS contentLang ON contentLang.idContent = content.id',
'\r\n    INNER JOIN pear_lang AS lang ON lang.Id = contentLang.idLang',
'\r\n    WHERE',
'\r\n    lang.alpha2 = "', lang, '"',
'\r\n    AND',
'\r\n    (',
'\r\n      LOWER(contentLang.title) LIKE "%', REPLACE(partialSearch,'%', '%" OR LOWER(contentLang.title) LIKE \"%'), '%"',
'\r\n      OR LOWER(contentLang.description) LIKE "%', REPLACE(partialSearch,'%', '%" OR LOWER(contentLang.description) LIKE \"%'), '%"',
'\r\n    )',
'\r\n  )',
'\r\n);'
);

PREPARE query FROM @sql1; EXECUTE query;
PREPARE query FROM @sql2; EXECUTE query;
PREPARE query FROM @sql3; EXECUTE query;
PREPARE query FROM @sql4; EXECUTE query;
PREPARE query FROM @sql5; EXECUTE query;
PREPARE query FROM @sql6; EXECUTE query;
PREPARE query FROM @sql7; EXECUTE query;
PREPARE query FROM @sql8; EXECUTE query;
PREPARE query FROM @sql9; EXECUTE query;
PREPARE query FROM @sql10; EXECUTE query;
PREPARE query FROM @sql11; EXECUTE query;
PREPARE query FROM @sql12; EXECUTE query;
PREPARE query FROM @sql13; EXECUTE query;
PREPARE query FROM @sql14; EXECUTE query;
PREPARE query FROM @sql15; EXECUTE query;
PREPARE query FROM @sql16; EXECUTE query;
PREPARE query FROM @sql17; EXECUTE query;

DEALLOCATE PREPARE query;

SELECT `rank`, `type`, `id`, `code`, `universCode`, `title`, `description`, `image`
FROM temporary_table
GROUP BY `type`, `code`
ORDER BY `rank` ASC;

DROP TABLE temporary_table;

END ;;
DELIMITER ;


