set schema 'colleges2';
-----
-- PREREQUIS : Ce script ne fonctionne que si vous avez défini un répertoire de travail (Working directory)
--             par défaut dans lequel se trouve votre script et les données sous forme de fichiers CSV

-- _region - infos dans fr-en-etablissements-ep
/*WbImport -file=./fr-en-etablissements-ep.csv
         -header=true
         -delimiter=';'
         -quoteChar='"' 
         -table=_region
         -schema=colleges2
         -mode=insert,update
         -filecolumns=$wb_skip$,$wb_skip$,$wb_skip$,$wb_skip$,$wb_skip$,$wb_skip$,$wb_skip$,$wb_skip$,libelle_region,$wb_skip$,$wb_skip$,$wb_skip$,$wb_skip$,$wb_skip$,$wb_skip$,$wb_skip$,$wb_skip$,$wb_skip$,code_region
         -importColumns=libelle_region,code_region
         -keyColumns=code_region; */

/* Cette approche est loin d'être optimale et va requérir de charger plusieurs fois le même fichier CSV pour différentes tables.
   Importing file '/Users/delhay/vrac/git/enseignement/but_info/but_info_bdd/bdd_s2/sae_204/colleges-ips/enonce/soluce/./fr-en-etablissements-ep.csv' into table _region
   19 row(s) inserted
   8430 row(s) updated
   Execution time: 3m 11s   

   Il vaut donc mieux charger une bonne fois pour toute tous les fichiers CSV dans de grosses tables temporaires puis faire des requêtes. */

-- Données générales pour l'année 2022-2023
create table _temp_fr_en_etablissements_ep(
  uai                     char(8) primary key,
  ep_2022_2023            varchar(7),  -- ep pour l'année 2022-2023 : REP, REP+, HORS REP. Pas utilisé a priori
  nom_etablissement       varchar(108),
  type_etablissement      varchar(39), -- libelle_nature
  statut_public_prive     varchar(18), -- secteur
  libelle_academie        varchar(16), -- lib_academie
  libelle_departement     varchar(23), -- nom_departement
  nom_commune             varchar(45), -- nom_de_la_commune
  libelle_region          varchar(34), -- libelle_region
  uai_tete_de_reseau      char(8),     -- pas utilisé
  qp_a_proximite_o_n      char(16),    -- 'Dans QP' ou NULL
  qp_a_proximite          char(8),     -- code_quartier_prioritaire
  nom_du_qp               varchar(85), -- nom_quartier_prioritaire
  nombre_d_eleves         integer,     -- effectifs
  code_postal             char(5),     -- code_postal
  code_commune            char(5),     -- code_insee_de_la_commune
  code_departement        char(3),     -- code_du_departement
  code_academie           char(2),     -- code_academie
  code_region             char(2),     -- code_region
  libelle_nature          varchar(39), -- libelle_nature
  code_nature             char(3),     -- code_nature
  latitude                numeric,
  longitude               numeric
);

delete from _temp_fr_en_etablissements_ep;
WbImport -file=./fr-en-etablissements-ep.csv
         -header=true
         -delimiter=';'
         -quoteChar='"' 
         -table=_temp_fr_en_etablissements_ep
         -schema=colleges2
         -mode=insert, update -- car il y a des doublons (toute la ligne)
         -filecolumns=uai,ep_2022_2023,nom_etablissement,type_etablissement,statut_public_prive,libelle_academie,libelle_departement,nom_commune,libelle_region,uai_tete_de_reseau,qp_a_proximite_o_n,qp_a_proximite,nom_du_qp,nombre_d_eleves,code_postal,code_commune,code_departement,code_academie,code_region,libelle_nature,code_nature,$wb_skip$,latitude,longitude
         -importColumns=uai,ep_2022_2023,nom_etablissement,type_etablissement,statut_public_prive,libelle_academie,libelle_departement,nom_commune,libelle_region,uai_tete_de_reseau,qp_a_proximite_o_n,qp_a_proximite,nom_du_qp,nombre_d_eleves,code_postal,code_commune,code_departement,code_academie,code_region,libelle_nature,code_nature,latitude,longitude
         -keyColumns=uai;

-- Données sur les effectifs de 2019-2020 à 2022-2023
create table _temp_fr_en_college_effectif_niveau_sexe_lv(
  rentree_scolaire        char(9)       not null,
  region_academique       varchar(26)   not null,
  academie                varchar(16)   not null,
  departement             varchar(21)   not null,
  commune                 varchar(32)   not null,
  numero_college          char(8)       not null,
  denomination_principale varchar(30)   not null,
  patronyme               varchar(30),
  secteur                 varchar(6),
  rep                     numeric(1),
  rep_plus                numeric(1),
  nombre_eleves_total     numeric       not null default 0, -- si champ vide alors 0
  nombre_eleves_segpa     numeric       not null default 0,
  nombre_eleves_ulis      numeric       not null default 0,
  _6eme_total              numeric       not null default 0,
  _6eme_segpa              numeric       not null default 0,
  _6eme_ulis               numeric       not null default 0,
  _6eme_filles             numeric       not null default 0,
  _6emes_garcons           numeric       not null default 0,
  _6eme_lv1_allemand       numeric       not null default 0,
  _6eme_lv1_anglais        numeric       not null default 0,
  _6eme_lv1_espagnol       numeric       not null default 0,
  _6eme_lv1_autres         numeric       not null default 0,
  _6eme_lv2_allemand       numeric       not null default 0,
  _6eme_lv2_anglais        numeric       not null default 0,
  _6eme_lv2_espagnol       numeric       not null default 0,
  _6eme_lv2_italien        numeric       not null default 0,
  _6eme_lv2_autres         numeric       not null default 0,
  _5eme_total              numeric       not null default 0,
  _5eme_segpa              numeric       not null default 0,
  _5eme_ulis               numeric       not null default 0,
  _5eme_filles             numeric       not null default 0,
  _5emes_garcons           numeric       not null default 0,
  _5eme_lv1_allemand       numeric       not null default 0,
  _5eme_lv1_anglais        numeric       not null default 0,
  _5eme_lv1_espagnol       numeric       not null default 0,
  _5eme_lv1_autres         numeric       not null default 0,
  _5eme_lv2_allemand       numeric       not null default 0,
  _5eme_lv2_anglais        numeric       not null default 0,
  _5eme_lv2_espagnol       numeric       not null default 0,
  _5eme_lv2_italien        numeric       not null default 0,
  _5eme_lv2_autres         numeric       not null default 0,
  _4eme_total              numeric       not null default 0,
  _4eme_segpa              numeric       not null default 0,
  _4eme_ulis               numeric       not null default 0,
  _4eme_filles             numeric       not null default 0,
  _4emes_garcons           numeric       not null default 0,
  _4eme_lv1_allemand       numeric       not null default 0,
  _4eme_lv1_anglais        numeric       not null default 0,
  _4eme_lv1_espagnol       numeric       not null default 0,
  _4eme_lv1_autres         numeric       not null default 0,
  _4eme_lv2_allemand       numeric       not null default 0,
  _4eme_lv2_anglais        numeric       not null default 0,
  _4eme_lv2_espagnol       numeric       not null default 0,
  _4eme_lv2_italien        numeric       not null default 0,
  _4eme_lv2_autres         numeric       not null default 0,
  _3eme_total              numeric       not null default 0,
  _3eme_segpa              numeric       not null default 0,
  _3eme_ulis               numeric       not null default 0,
  _3eme_filles             numeric       not null default 0,
  _3emes_garcons           numeric       not null default 0,
  _3eme_lv1_allemand       numeric       not null default 0,
  _3eme_lv1_anglais        numeric       not null default 0,
  _3eme_lv1_espagnol       numeric       not null default 0,
  _3eme_lv1_autres         numeric       not null default 0,
  _3eme_lv2_allemand       numeric       not null default 0,
  _3eme_lv2_anglais        numeric       not null default 0,
  _3eme_lv2_espagnol       numeric       not null default 0,
  _3eme_lv2_italien        numeric       not null default 0,
  _3eme_lv2_autres         numeric       not null default 0,
  code_postal             char(5)       not null, 
  constraint _temp_effectif_pk primary key (rentree_scolaire, numero_college)
);

-- Reformatage des années en années scolaires.
drop function if exists ftg_rentree4_rentree9();
create or replace function ftg_rentree4_rentree9() returns trigger as $$
declare
  annee_int   integer;
begin
  annee_int := new.rentree_scolaire::integer;
  new.rentree_scolaire := to_char(annee_int,'FM9999') || '-' || to_char(annee_int+1,'FM9999');
  return new;
end;
$$ language 'plpgsql';

-- Trigger se déclenchant sur ajout de données dans _temp_fr_en_college_effectif_niveau_sexe_lv
drop trigger if exists tg_rentree4_rentree9 on _temp_fr_en_college_effectif_niveau_sexe_lv;
create trigger tg_rentree4_rentree9
before insert or update of rentree_scolaire
on _temp_fr_en_college_effectif_niveau_sexe_lv
for each row
execute procedure ftg_rentree4_rentree9(); 

-- rentree_scolaire;region_academique;academie;departement;commune;numero_college;denomination_principale;patronyme;secteur;rep;rep_plus;nombre_eleves_total;nombre_eleves_total_hors_segpa_hors_ulis;nombre_eleves_segpa;nombre_eleves_ulis;6eme_total;6eme_hors_segpa_hors_ulis;6eme_segpa;6eme_ulis;6eme_filles;6emes_garcons;6eme_lv1_allemand;6eme_lv1_anglais;6eme_lv1_espagnol;6eme_lv1_autres;6eme_lv2_allemand;6eme_lv2_anglais;6eme_lv2_espagnol;6eme_lv2_italien;6eme_lv2_autres;5eme_total;5eme_hors_segpa_hors_ulis;5eme_segpa;5eme_ulis;5eme_filles;5emes_garcons;5eme_lv1_allemand;5eme_lv1_anglais;5eme_lv1_espagnol;5eme_lv1_autres;5eme_lv2_allemand;5eme_lv2_anglais;5eme_lv2_espagnol;5eme_lv2_italien;5eme_lv2_autres;4eme_total;4eme_hors_segpa_hors_ulis;4eme_segpa;4eme_ulis;4eme_filles;4emes_garcons;4eme_lv1_allemand;4eme_lv1_anglais;4eme_lv1_espagnol;4eme_lv1_autres;4eme_lv2_allemand;4eme_lv2_anglais;4eme_lv2_espagnol;4eme_lv2_italien;4eme_lv2_autres;3eme_total;3eme_hors_segpa_hors_ulis;3eme_segpa;3eme_ulis;3eme_filles;3emes_garcons;3eme_lv1_allemand;3eme_lv1_anglais;3eme_lv1_espagnol;3eme_lv1_autres;3eme_lv2_allemand;3eme_lv2_anglais;3eme_lv2_espagnol;3eme_lv2_italien;3eme_lv2_autres;code_postal
delete from _temp_fr_en_college_effectif_niveau_sexe_lv;
WbImport -file=./fr-en-college-effectifs-niveau-sexe-lv.csv
         -header=true
         -delimiter=';'
         -quoteChar='"' 
         -table=_temp_fr_en_college_effectif_niveau_sexe_lv
         -schema=colleges2
         -mode=insert, update -- car il y a des doublons (toute la ligne)
         -filecolumns=rentree_scolaire,region_academique,academie,departement,commune,numero_college,denomination_principale,patronyme,secteur,rep,rep_plus,nombre_eleves_total,$wb_skip$,nombre_eleves_segpa,nombre_eleves_ulis,_6eme_total,$wb_skip$,_6eme_segpa,_6eme_ulis,_6eme_filles,_6emes_garcons,_6eme_lv1_allemand,_6eme_lv1_anglais,_6eme_lv1_espagnol,_6eme_lv1_autres,_6eme_lv2_allemand,_6eme_lv2_anglais,_6eme_lv2_espagnol,_6eme_lv2_italien,_6eme_lv2_autres,_5eme_total,$wb_skip$,_5eme_segpa,_5eme_ulis,_5eme_filles,_5emes_garcons,_5eme_lv1_allemand,_5eme_lv1_anglais,_5eme_lv1_espagnol,_5eme_lv1_autres,_5eme_lv2_allemand,_5eme_lv2_anglais,_5eme_lv2_espagnol,_5eme_lv2_italien,_5eme_lv2_autres,_4eme_total,$wb_skip$,_4eme_segpa,_4eme_ulis,_4eme_filles,_4emes_garcons,_4eme_lv1_allemand,_4eme_lv1_anglais,_4eme_lv1_espagnol,_4eme_lv1_autres,_4eme_lv2_allemand,_4eme_lv2_anglais,_4eme_lv2_espagnol,_4eme_lv2_italien,_4eme_lv2_autres,_3eme_total,$wb_skip$,_3eme_segpa,_3eme_ulis,_3eme_filles,_3emes_garcons,_3eme_lv1_allemand,_3eme_lv1_anglais,_3eme_lv1_espagnol,_3eme_lv1_autres,_3eme_lv2_allemand,_3eme_lv2_anglais,_3eme_lv2_espagnol,_3eme_lv2_italien,_3eme_lv2_autres,code_postal
         -importColumns=rentree_scolaire,region_academique,academie,departement,commune,numero_college,denomination_principale,patronyme,secteur,rep,rep_plus,nombre_eleves_total,nombre_eleves_total_hors_segpa_hors_ulis,nombre_eleves_segpa,nombre_eleves_ulis,_6eme_total,_6eme_hors_segpa_hors_ulis,_6eme_segpa,_6eme_ulis,_6eme_filles,_6emes_garcons,_6eme_lv1_allemand,_6eme_lv1_anglais,_6eme_lv1_espagnol,_6eme_lv1_autres,_6eme_lv2_allemand,_6eme_lv2_anglais,_6eme_lv2_espagnol,_6eme_lv2_italien,_6eme_lv2_autres,_5eme_total,_5eme_hors_segpa_hors_ulis,_5eme_segpa,_5eme_ulis,_5eme_filles,_5emes_garcons,_5eme_lv1_allemand,_5eme_lv1_anglais,_5eme_lv1_espagnol,_5eme_lv1_autres,_5eme_lv2_allemand,_5eme_lv2_anglais,_5eme_lv2_espagnol,_5eme_lv2_italien,_5eme_lv2_autres,_4eme_total,_4eme_hors_segpa_hors_ulis,_4eme_segpa,_4eme_ulis,_4eme_filles,_4emes_garcons,_4eme_lv1_allemand,_4eme_lv1_anglais,_4eme_lv1_espagnol,_4eme_lv1_autres,_4eme_lv2_allemand,_4eme_lv2_anglais,_4eme_lv2_espagnol,_4eme_lv2_italien,_4eme_lv2_autres,_3eme_total,_3eme_hors_segpa_hors_ulis,_3eme_segpa,_3eme_ulis,_3eme_filles,_3emes_garcons,_3eme_lv1_allemand,_3eme_lv1_anglais,_3eme_lv1_espagnol,_3eme_lv1_autres,_3eme_lv2_allemand,_3eme_lv2_anglais,_3eme_lv2_espagnol,_3eme_lv2_italien,_3eme_lv2_autres,code_postal
         -keyColumns=rentree_scolaire,numero_college;


-- Données sur la valeur ajoutée
create table _temp_fr_en_indicateurs_valeur_ajoutee_colleges(
  session                               char(9),
  uai                                   char(8),
  nom_de_l_etablissement                varchar(87)   not null,
  commune                               varchar(32)   not null,
  departement                           varchar(21)   not null,
  academie                              varchar(16)   not null,
  secteur                               char(2)       not null,
  nb_candidats_g                        numeric       default 0,
  taux_de_reussite_g                    numeric(5,1),
  va_du_taux_de_reussite_g              numeric(5,1),
  nb_candidats_p                        numeric       default 0,
  taux_de_reussite_p                    numeric(5,1),
  note_a_l_ecrit_g                      numeric(4,1),
  va_de_la_note_g                       numeric(4,1),
  note_a_l_ecrit_p                      numeric(4,1),
  taux_d_acces_6eme_3eme                numeric(5,1),
  part_presents_3eme_ordinaire_total    numeric(5,1),
  part_presents_3eme_ordinaire_g        numeric(5,1),
  part_presents_3eme_ordinaire_p        numeric(5,1),
  part_presents_3eme_segpa_total        numeric(5,1),
  nb_mentions_ab_g                      numeric       default 0,
  nb_mentions_b_g                       numeric       default 0,
  nb_mentions_tb_g                      numeric       default 0,
  constraint indicateurs_valeur_ajoutee_pk primary key (uai, session)
);

-- reformatage des années de session en années scolaires
drop function if exists ftg_insert_session();
create or replace function ftg_insert_session() returns trigger as $$
declare
  annee_session   integer;
begin
  annee_session := new.session::integer;
  new.session := to_char(annee_session-1,'FM9999') || '-' || to_char(annee_session,'FM9999');
  return new;
end;
$$ language 'plpgsql';

drop trigger if exists tg_insert_session on _temp_fr_en_indicateurs_valeur_ajoutee_colleges;
create trigger tg_insert_session
before insert or update
on _temp_fr_en_indicateurs_valeur_ajoutee_colleges
for each row
execute procedure ftg_insert_session();

-- session;uai;nom_de_l_etablissement;commune;departement;academie;secteur;nb_candidats_g;taux_de_reussite_g;va_du_taux_de_reussite_g;nb_candidats_p;taux_de_reussite_p;note_a_l_ecrit_g;va_de_la_note_g;note_a_l_ecrit_p;taux_d_acces_6eme_3eme;part_presents_3eme_ordinaire_total;part_presents_3eme_ordinaire_g;part_presents_3eme_ordinaire_p;part_presents_3eme_segpa_total;nb_mentions_ab_g;nb_mentions_b_g;nb_mentions_tb_g;nb_mentions_global_g
delete from _temp_fr_en_indicateurs_valeur_ajoutee_colleges;
WbImport -file=./fr-en-indicateurs-valeur-ajoutee-colleges.csv
         -header=true
         -delimiter=';'
         -quoteChar='"' 
         -table=_temp_fr_en_indicateurs_valeur_ajoutee_colleges
         -schema=colleges2
         -mode=insert, update -- car il y a des doublons (toute la ligne)
         -filecolumns=session,uai,nom_de_l_etablissement,commune,departement,academie,secteur,nb_candidats_g,taux_de_reussite_g,va_du_taux_de_reussite_g,nb_candidats_p,taux_de_reussite_p,note_a_l_ecrit_g,va_de_la_note_g,note_a_l_ecrit_p,taux_d_acces_6eme_3eme,part_presents_3eme_ordinaire_total,part_presents_3eme_ordinaire_g,part_presents_3eme_ordinaire_p,part_presents_3eme_segpa_total,nb_mentions_ab_g,nb_mentions_b_g,nb_mentions_tb_g
         -importColumns=session,uai,nom_de_l_etablissement,commune,departement,academie,secteur,nb_candidats_g,taux_de_reussite_g,va_du_taux_de_reussite_g,nb_candidats_p,taux_de_reussite_p,note_a_l_ecrit_g,va_de_la_note_g,note_a_l_ecrit_p,taux_d_acces_6eme_3eme,part_presents_3eme_ordinaire_total,part_presents_3eme_ordinaire_g,part_presents_3eme_ordinaire_p,part_presents_3eme_segpa_total,nb_mentions_ab_g,nb_mentions_b_g,nb_mentions_tb_g
         -keyColumns=session,uai;

-----------------------
-- Données sur IPS
create table _temp_fr_en_ips_colleges_ap2022(
  rentree_scolaire          char(9),     
  academie                  varchar(16)   not null,
  code_du_departement       char(3)       not null,
  departement               varchar(21)   not null,
  uai                       char(8),
  nom_de_l_etablissment     varchar(79)   not null,
  code_insee_de_la_commune  varchar(5)    not null,
  nom_de_la_commune         varchar(35)   not null,
  secteur                   varchar(18)   not null,
  effectifs                 integer       not null,
  ips                       numeric(5,1)  not null,
  ecart_type_de_l_ips       numeric(4,1)  not null,
  constraint ips_colleges_ap2022_pk primary key(rentree_scolaire,uai)
);

-- rentree_scolaire;academie;code_du_departement;departement;uai;nom_de_l_etablissment;code_insee_de_la_commune;nom_de_la_commune;secteur;effectifs;ips;ecart_type_de_l_ips
delete from _temp_fr_en_ips_colleges_ap2022;
WbImport -file=./fr-en-ips-colleges-ap2022.csv
         -header=true
         -delimiter=';'
         -quoteChar='"' 
         -table=_temp_fr_en_ips_colleges_ap2022
         -schema=colleges2
         -mode=insert, update -- car il y a peut-être des doublons (toute la ligne)
         -filecolumns=rentree_scolaire,academie,code_du_departement,departement,uai,nom_de_l_etablissment,code_insee_de_la_commune,nom_de_la_commune,secteur,effectifs,ips,ecart_type_de_l_ips
         -importColumns=rentree_scolaire,academie,code_du_departement,departement,uai,nom_de_l_etablissment,code_insee_de_la_commune,nom_de_la_commune,secteur,effectifs,ips,ecart_type_de_l_ips
         -keyColumns=rentree_scolaire,uai;


--------------------
--------------------
-- STATS SUR LES DONNEES : a décommenter pour obtenir le résultat
/*
select count(distinct uai) as nb_row_temp_fr_en_etablissements_ep
from _temp_fr_en_etablissements_ep;
-- 8500 établissements en tout

select count(distinct uai) as nb_row_colleges_temp_fr_en_etablissements_ep
from _temp_fr_en_etablissements_ep
where type_etablissement = 'Collège';
-- 1414

select count(*) as nb_row_temp_fr_en_college_effectif_niveau_sexe_lv
from _temp_fr_en_college_effectif_niveau_sexe_lv;
-- 32982 (colleges,annee)

select count(distinct numero_college) as nb_row_colleges_temp_fr_en_college_effectif_niveau_sexe_lv
from _temp_fr_en_college_effectif_niveau_sexe_lv;
-- 8337

select count(distinct uai) from 
(select uai
from _temp_fr_en_etablissements_ep
where type_etablissement = 'Collège'
intersect
select numero_college as uai
from _temp_fr_en_college_effectif_niveau_sexe_lv) as intersection;
-- 1414 (1414 si pas de restriction sur type_etablissement)
-- A priori tous les colleges de fr_en_etablissements_ep
*/

------------------------------------------------------
---- DISTRIBUTION DES DONNEES DANS LES TABLES
------------------------------------------------------
-- _region : 19 n-uplets
insert into _region (code_region, libelle_region)
  select distinct code_region, libelle_region
  from _temp_fr_en_etablissements_ep
  where code_region is not null;


-- _departement : 98 n-uplets --> il en manque
insert into _departement(code_du_departement, nom_departement, code_region)
  select distinct code_departement, libelle_departement, code_region
  from _temp_fr_en_etablissements_ep
  where code_departement is not null;

-- Mise en majuscules et suppression des accents 
update _departement
set nom_departement = upper(nom_departement);
update _departement
set nom_departement = replace(nom_departement,'É','E');
update _departement
set nom_departement = replace(nom_departement,'È','E');
update _departement
set nom_departement = replace(nom_departement,'Ô','O');

-- _commune :

-- Uniformisation des noms de commune
update _temp_fr_en_college_effectif_niveau_sexe_lv
set commune = replace(commune, '-', ' ');

delete from _commune;
insert into _commune (code_insee_de_la_commune, nom_de_la_commune, code_du_departement)
  select distinct code_postal as code_commune, commune, code_du_departement
  from _temp_fr_en_college_effectif_niveau_sexe_lv inner join _departement
  on departement = nom_departement
  order by code_commune;
-- 4747 n-uplets

-- Difficulté sur la presentation des communes : prendre un référence
-- https://public.opendatasoft.com/explore/dataset/correspondance-code-cedex-code-insee/table/?flg=fr-fr

create table _temp_communes(
  Code              char(5)     not null,
  Libelle           varchar(38),
  Code_INSEE        char(5),
  type_code         varchar(11) not null,
  Nom_commune       varchar(45),
  Nom_departement   varchar(23),
  NOM_EPCI          varchar(75),
  Nom_region        varchar(26)
);
delete from _temp_communes;
WbImport -file=./correspondance-code-cedex-code-insee.csv
         -header=true
         -delimiter=';'
         --quoteChar='"' 
         -table=_temp_communes
         -schema=colleges2
         -mode=insert-- , update -- car il y a des doublons (toute la ligne)
         -filecolumns=Code,Libelle,Code_INSEE,type_code,Nom_commune,Nom_departement,NOM_EPCI,Nom_region
         -importColumns=Code,Libelle,Code_INSEE,type_code,Nom_commune,Nom_departement,NOM_EPCI,Nom_region
         -keyColumns=code_insee;

insert into _commune (code_insee_de_la_commune, nom_de_la_commune, code_du_departement)
  select distinct coalesce(code_insee, code) as code_insee_de_la_commune, commune, code_du_departement
  from _temp_fr_en_indicateurs_valeur_ajoutee_colleges va left outer join _temp_communes tc
  on va.commune = tc.libelle
  inner join _departement d
  on tc.nom_departement = d.nom_departement
  where (code_insee, commune) not in (select code_insee_de_la_commune, nom_de_la_commune from _commune)
    and (code, commune) not in (select code_insee_de_la_commune, nom_de_la_commune from _commune);

insert into _commune (code_insee_de_la_commune, nom_de_la_commune, code_du_departement)
  select distinct code_commune, upper(nom_commune), code_departement
  from _temp_fr_en_etablissements_ep
  where (code_commune, upper(nom_commune)) not in (select code_insee_de_la_commune, nom_de_la_commune from _commune) ;

-- Il manque des départements dans _departement

insert into _departement (code_du_departement, code_region, nom_departement)
  values ('015','84','CANTAL'),
         ('032','76','GERS'),
         ('043','84','HAUTE-LOIRE'),
         ('048','76','LOZERE');

insert into _commune (code_insee_de_la_commune, nom_de_la_commune, code_du_departement)
  select distinct code_commune, nom_commune, code_departement
  from _temp_fr_en_etablissements_ep
  where (code_commune, nom_commune) not in (select code_insee_de_la_commune, nom_de_la_commune from _commune) ;

----------------
--- _academie
delete from _academie;
--- A compléter
insert into _academie (code_academie, lib_academie)
  select distinct code_academie, libelle_academie
  from _temp_fr_en_etablissements_ep
  where (code_academie, libelle_academie) not in (select code_academie, lib_academie from _academie) and (code_academie, libelle_academie) is not null;
-- Nettoyage des noms d'académies : majuscules et sans accents
update _academie
set lib_academie = upper(lib_academie);
update _academie
set lib_academie = replace(lib_academie,'É','E');
update _academie
set lib_academie = replace(lib_academie,'È','E');
update _academie
set lib_academie = replace(lib_academie,'Ô','O');

------------------------
-- Quartier prioritaire
delete from _quartier_prioritaire;
--- A compléter
  
alter table  _est_a_proximite_de
  drop constraint est_a_proximite_de_fk_qp;
  
alter table _quartier_prioritaire
  drop constraint quartier_prioritaire_pk;

insert into _quartier_prioritaire (code_quartier_prioritaire, nom_quartier_prioritaire)
  select distinct qp_a_proximite, nom_du_qp 
  from _temp_fr_en_etablissements_ep
  where (qp_a_proximite, nom_du_qp) not in (select code_quartier_prioritaire, nom_quartier_prioritaire from _quartier_prioritaire)
  and (qp_a_proximite, nom_du_qp) is not null;

------------------------
-- Type
delete from _type;
--- A compléter
insert into _type (code_nature, libelle_nature)
select distinct code_nature, libelle_nature
from _temp_fr_en_etablissements_ep
where (code_nature, libelle_nature) not in (select code_nature, libelle_nature from _type)
and (code_nature, libelle_nature) is not null;

------------------------
-- Etablissement
delete from _etablissement;
--- A compléter
insert into _etablissement (uai, nom_etablissement, secteur, code_postal, latitude, longitude, code_insee_de_la_commune, nom_de_la_commune, code_academie, code_nature)
  select distinct uai, nom_etablissement, statut_public_prive, code_postal, latitude, longitude, code_commune, nom_commune, code_academie, code_nature
  from _temp_fr_en_etablissements_ep
  where (uai, nom_etablissement, statut_public_prive, code_postal, latitude, longitude, code_commune, nom_commune, code_academie, code_nature) not in (select uai, nom_etablissement, secteur, code_postal, latitude, longitude, code_insee_de_la_commune, nom_de_la_commune, code_academie, code_nature from _etablissement)
  and (uai, nom_etablissement, statut_public_prive, code_postal, latitude, longitude, code_commune, nom_commune, code_academie, code_nature) is not null;

------------------------
-- Année
delete from _annee;
--- A compléter
insert into _annee(annee_scolaire)
  select distinct rentree_scolaire
  from _temp_fr_en_college_effectif_niveau_sexe_lv
  where (rentree_scolaire) not in (select annee_scolaire from _annee) ;
  
------------------------
-- Caracteristique colleges
delete from _caracteristiques_tout_etablissement;
--- A compléter
insert into _caracteristiques_tout_etablissement(uai, annee_scolaire, effectifs)
  select distinct e.uai, c.rentree_scolaire, e.nombre_d_eleves
  from _temp_fr_en_etablissements_ep e
  join _temp_fr_en_college_effectif_niveau_sexe_lv c on e.uai = c.numero_college
  where (e.uai, c.rentree_scolaire, e.nombre_d_eleves) not in (select uai, annee_scolaire, effectifs from _caracteristiques_tout_etablissement) 
  and (e.uai, c.rentree_scolaire, e.nombre_d_eleves) is not null;

------------------------
alter table _quartier_prioritaire
  add constraint quartier_prioritaire_pk2 primary key(code_quartier_prioritaire, nom_quartier_prioritaire);

alter table _est_a_proximite_de
  add nom_quartier_prioritaire VARCHAR(85);
  
alter table _est_a_proximite_de
  add constraint est_a_proximite_de_fk_qp2 foreign key(code_quartier_prioritaire, nom_quartier_prioritaire) references _quartier_prioritaire(code_quartier_prioritaire, nom_quartier_prioritaire) ;

------------------------
insert into _est_a_proximite_de(uai, code_quartier_prioritaire, nom_quartier_prioritaire)
  select distinct uai, qp_a_proximite, nom_du_qp
  from _temp_fr_en_etablissements_ep
  where (uai, qp_a_proximite, nom_du_qp) 
    not in (select uai, code_quartier_prioritaire, nom_quartier_prioritaire from _est_a_proximite_de)
    and uai in (select uai from _etablissement)
    and (qp_a_proximite, nom_du_qp) in (select qp_a_proximite, nom_du_qp from _quartier_prioritaire)
    and (uai, qp_a_proximite, nom_du_qp) is not null;
    
------------------------
-- Caracteristique colleges
delete from _classe;
--- A compléter
insert into _classe(id_classe) values ('6eme');
insert into _classe(id_classe) values ('5eme');
insert into _classe(id_classe) values ('4eme');
insert into _classe(id_classe) values ('3eme');

  
  
commit;