 INSERT INTO `recommend_academy` (`ID`, `name`, `domain`) VALUES
   (1, 'Académie d\'Aix-Marseille', 'ac-aix-marseille.fr'),
   (2, 'Académie d\'Amiens', 'ac-amiens.fr'),
   (3, 'Académie de Besançon', 'ac-besancon.fr'),
   (4, 'Académie de Bordeaux', 'ac-bordeaux.fr'),
   (5, 'Académie de Caen', 'ac-caen.fr'),
   (6, 'Académie de Clermont-Ferrand', 'ac-clermont.fr'),
   (7, 'Académie de Corse', 'ac-corse.fr'),
   (8, 'Académie de Créteil', 'ac-creteil.fr'),
   (9, 'Académie de Dijon', 'ac-dijon.fr'),
   (10, 'Académie de Grenoble', 'ac-grenoble.fr'),
   (11, 'Académie de la Guadeloupe', 'ac-guadeloupe.fr'),
   (12, 'Académie de Guyane', 'ac-guyane.fr'),
   (13, 'Académie de Lille', 'ac-lille.fr'),
   (14, 'Académie de Limoges', 'ac-limoges.fr'),
   (15, 'Académie de Lyon', 'ac-lyon.fr'),
   (16, 'Académie de la Martinique', 'ac-martinique.fr'),
   (17, 'Académie de Montpellier', 'ac-montpellier.fr'),
   (18, 'Académie de Nancy-Metz', 'ac-nancy-metz.fr'),
   (19, 'Académie de Nantes', 'ac-nantes.fr'),
   (20, 'Académie de Nice', 'ac-nice.fr'),
   (21, 'Académie d\'Orléans-Tours', 'ac-orleans-tours.fr'),
   (22, 'Académie de Paris', 'ac-paris.fr'),
   (23, 'Académie de Poitiers', 'ac-poitiers.fr'),
   (24, 'Académie de Reims', 'ac-reims.fr'),
   (25, 'Académie de Rennes', 'ac-rennes.fr'),
   (26, 'Académie de La Réunion', 'ac-reunion.fr'),
   (27, 'Académie de Rouen', 'ac-rouen.fr'),
   (28, 'Académie de Strasbourg', 'ac-strasbourg.fr'),
   (29, 'Académie de Toulouse', 'ac-toulouse.fr'),
   (30, 'Académie de Versailles', 'ac-versailles.fr');
      
INSERT INTO academies_dpt (academyID, departement) VALUES(1,4),(1,5),(1,13),(1,84),(2,2),(2,60),(2,80),(3,25),(3,39),(3,70),(3,90),(4,24),(4,33),(4,40),(4,47),(4,64),(5,14),(5,50),(5,61),(6,3),(6,15),(6,43),(6,63),(7,20),(8,77),(8,93),(8,94),(9,21),(9,58),(9,71),(9,89),(10,7),(10,26),(10,38),(10,73),(10,74),(11,971),(11,977),(11,978),(12,973),(13,59),(13,62),(14,19),(14,23),(14,87),(15,1),(15,42),(15,69),(16,972),(17,11),(17,30),(17,34),(17,48),(17,66),(18,54),(18,55),(18,57),(18,88),(19,44),(19,49),(19,53),(19,72),(19,85),(20,6),(20,83),(21,18),(21,28),(21,36),(21,37),(21,41),(21,45),(22,75),(23,16),(23,17),(23,79),(23,86),(24,08),(24,10),(24,51),(24,52),(25,22),(25,29),(25,35),(25,56),(26,974),(27,27),(27,76),(28,67),(28,68),(29,9),(29,12),(29,31),(29,32),(29,46),(29,65),(29,81),(29,82),(30,78),(30,91),(30,92),(30,95);

UPDATE `recommend_listedschool` ta SET ta.academyID = 
   (SELECT tb.academyID
      FROM `recommend_academy_department` tb
      WHERE tb.departement = (CAST(ta.code_postal_uai AS SIGNED) DIV 1000) )
;
   
 INSERT INTO `recommend_listedschool_normalized` (listedschoolID, codePostal, lastWord, normalizedName) 
 SELECT 
   ID, 
   `recommend_listedschool`.code_postal_uai,
   LOWER(SUBSTRING_INDEX(`recommend_listedschool`.appellation_officielle_uai, ' ', -1)),
   REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LOWER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(`recommend_listedschool`.appellation_officielle_uai, 
      'd\'enseignement', ''),
      'général', ''),
      'et technologique', ''),
      'des métiers', ''),
      'privé', ''), 
      'Etablissement régional d\'enseignement adapté', ''), 
      'Section d\'enseignement général et professionnel adapté du', '')), 
      'polyvalent', ''),
      '-', ''),
      'é', 'e'),
      'è', 'e'),
      'de ', ''),
      'du ', ''),
      ' ', '')
 FROM `recommend_listedschool` 
 WHERE `recommend_listedschool`.nature_uai >= 300
 AND (`recommend_listedschool`.appellation_officielle_uai NOT LIKE 'Section%')
 ;
    



 INSERT INTO `recommend_school_listedschool` (schoolID, listedschoolID) 
  (SELECT 
  `school`.ID, 
  `recommend_listedschool_normalized`.listedschoolID
  FROM `school` JOIN `recommend_listedschool_normalized` ON  
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LOWER(`school`.name),
         'é', 'e'),
         'è', 'e'),
         '-', ''),
         'de ', ''),
         'du ', ''),
         ' ', '') 
      = `recommend_listedschool_normalized`.normalizedName
    AND
        (CAST(`recommend_listedschool_normalized`.codePostal AS SIGNED) DIV 1000) 
      = (CAST(`school`.zipcode AS SIGNED) DIV 1000)
    );

 INSERT INTO `recommend_school_listedschool` (schoolID, listedschoolID) 
  (SELECT 
  `school`.ID, 
  `recommend_listedschool_normalized`.listedschoolID
  FROM `school` LEFT JOIN `recommend_listedschool_normalized` ON  
         (CAST(`recommend_listedschool_normalized`.codePostal AS SIGNED) DIV 1000) 
      = (CAST(`school`.zipcode AS SIGNED) DIV 1000)
      AND 
        `recommend_listedschool_normalized`.lastWord 
      = LOWER(SUBSTRING_INDEX(`school`.name, ' ', -1))
  LEFT JOIN `recommend_listedschool` ON
      `recommend_listedschool`.ID = `recommend_listedschool_normalized`.listedschoolID
  WHERE
     0 = (SELECT COUNT(*) as r FROM `recommend_school_listedschool`
                  WHERE `recommend_school_listedschool`.schoolID = `school`.ID)
   AND 
      1 = (SELECT COUNT(*) 
     FROM `recommend_listedschool_normalized` 
     WHERE
         (CAST(`recommend_listedschool_normalized`.codePostal AS SIGNED) DIV 1000) 
      = (CAST(`school`.zipcode AS SIGNED) DIV 1000)
      AND 
        `recommend_listedschool_normalized`.lastWord 
      = LOWER(SUBSTRING_INDEX(`school`.name, ' ', -1))
     ) 
   );
      

REPLACE INTO `recommend_school_listedschool` (schoolId, listedschoolID) VALUES
(57, 33812),
(57, 35795),
(77, 9419),
(77, 9654),
(92, 34865),
(98, 56665),
(117, 27621),
(117, 28396),
(120, 67078),
(173, 54350),
(215, 60862),
(246, 32056),
(264, 36931),
(264, 37230),
(264, 37278),
(301, 55110),
(301, 55113),
(302, 35796),
(307, 32174),
(307, 32188),
(307, 32760),
(307, 33034),
(307, 33053),
(318, 34795),
(319, 15213),
(319, 15870),
(319, 15705),
(392, 27894),
(397, 65523),
(411, 48809),
(411, 48930),
(455, 35502),
(468, 48118),
(468, 48696),
(474, 61300),
(523, 33737),
(523, 33822),
(544, 58695),
(553, 37713),
(581, 25077),
(628, 17906),
(628, 18353),
(654, 48090),
(654, 48683),
(718, 34852),
(727, 66747),
(727, 66840),
(779, 53976),
(779, 54499),
(793, 28519),
(793, 28941),
(802, 66141),
(802, 66736),
(845, 30732),
(852, 33041),
(876, 7605),
(878, 52387),
(878, 53074),
(894, 33727),
(894, 35800),
(938, 14409),
(938, 15066),
(942, 6981),
(942, 7396),
(946, 2218),
(946, 2734),
(953, 43721),
(953, 44522),
(953, 44697),
(954, 18623),
(954, 18651),
(967, 30323),
(967, 30897),
(976, 37693),
(977, 20432),
(977, 20695),
(992, 649),
(992, 660),
(992, 666),
(992, 1165),
(993, 9),
(993, 10),
(1024, 14452),
(1024, 15058),
(1033, 2189),
(1033, 2190),
(1035, 14451),
(1035, 15060),
(1035, 15149),
(1053, 31420),
(1053, 31452),
(1053, 32016),
(1053, 32113),
(1072, 66023),
(1075, 14961),
(1075, 14367),
(1105, 27411),
(1116, 10479),
(1116, 11093),
(1141, 19530),
(1144, 7874),
(1144, 7894),
(1158, 32194),
(1158, 33042),
(1158, 33052),
(1161, 62192),
(1182, 40054),
(1182, 39765),
(1192, 12477),
(1192, 12531),
(1211, 27872),
(1211, 28347),
(1228, 25046),
(1228, 25929),
(1236, 648),
(1237, 44062),
(1237, 44605),
(1248, 58197),
(1248, 58605),
(1260, 10447),
(1260, 11041),
(1276, 40156),
(1276, 40780),
(1277, 25041),
(1277, 25924),
(1277, 26057),
(1290, 49838),
(1290, 49995),
(1291, 48132),
(1291, 48706),
(1292, 8370),
(1292, 8371),
(1339, 41690),
(1389, 35902),
(1463, 35911),
(1471, 52962),
(1471, 53375),
(1478, 13138),
(1484, 15186),
(1502, 23853),
(1502, 24401),
(1510, 49999),
(1536, 19774),
(1536, 19779),
(1536, 20188),
(1544, 31457),
(1547, 13133),
(1576, 36271),
(1576, 36289),
(1601, 14411),
(1601, 14421),
(1610, 36927),
(1610, 37172),
(1610, 37297),
(1612, 60507),
(1612, 61029),
(1612, 61123),
(1625, 48808),
(1625, 48966),
(1633, 49269),
(1633, 49282),
(1633, 49284),
(1643, 46958),
(1643, 47249),
(1648, 38502),
(1650, 21369),
(1650, 22232),
(1668, 9724),
(1668, 9736),
(1668, 10175),
(1669, 30318),
(1669, 30738),
(1675, 60510),
(1675, 60738),
(1675, 61127),
(1720, 32147),
(1720, 32636),
(1728, 48052),
(1728, 48105),
(1728, 48690),
(1739, 31159),
(1739, 31173),
(1741, 33768),
(1741, 33824),
(1743, 6962),
(1760, 37694),
(1760, 38598),
(1781, 32192),
(1795, 45316),
(1806, 21369),
(1830, 35383),
(1838, 47710),
(1873, 19734),
(1873, 19748),
(1875, 24200),
(1875, 24614),
(1878, 60845),
(1878, 61183),
(1878, 61189),
(1882, 12547),
(1882, 12975),
(1899, 63053),
(1899, 63416),
(1966, 10486),
(1966, 11098),
(1972, 52961),
(1972, 53363),
(1990, 38529),
(1999, 47254),
(2035, 34828),
(2035, 34868),
(2042, 59206),
(2042, 59341),
(2052, 61883),
(2066, 10447),
(2066, 11041)
;
      

