-- Création de la table pour les données initiales
create table donnees
(
  num integer primary key,
  objet character varying(255),
  date character varying(15),
  duree integer,
  matricule_avion character varying(10),
  type_avion character varying(50),
  constructeur character varying(50),
  nb_places integer,
  prenom_proprietaire character varying(50),
  nom_proprietaire character varying(50),
  num_proprietaire character varying(5),
  rue_proprietaire character varying(50),
  telephone_proprietaire character varying(15),
  prenom_mecanicien character varying(50),
  nom_mecanicien character varying(50),
  num_mecanicien character varying(5),
  rue_mecanicien character varying(50),
  telephone_mecanicien character varying(15),
  habilitation_1 character varying(50),
  habilitation_2 character varying(50),
  habilitation_3 character varying(50)
);

-- Création de la table AVION
create table type_avion as
select row_number() over (order by constructeur, type_avion) id, 
type_avion nom, constructeur, nb_places
from donnees
group by type_avion, constructeur, nb_places
;
alter table type_avion add constraint type_avion_pkey primary key (id);


-- Identification des problèmes pour la table PROPRIETAIRE
select distinct trim(initcap(prenom_proprietaire)) prenom, 
trim(upper(nom_proprietaire)) nom, 
num_proprietaire num, trim(initcap(rue_proprietaire)) rue, 
telephone_proprietaire telephone
from donnees
order by 1,2;

-- Correction des données de PROPRIETAIRE
update donnees 
set nom_proprietaire='VAN BERGEN' 
where trim(upper(nom_proprietaire))='VANBERGEN';

-- Numérotation des données de PROPRIETAIRE
select row_number() over (order by 3, 2) id,
trim(initcap(prenom_proprietaire)) prenom, 
trim(upper(nom_proprietaire)) nom, 
num_proprietaire num, trim(initcap(rue_proprietaire)) rue, 
telephone_proprietaire telephone
from donnees
group by 2, 3, 4, 5, 6;

-- Création de la table PROPRIETAIRE
create table proprietaire as
select row_number() over (order by nom, prenom) id, prop.*
from (
	select trim(initcap(prenom_proprietaire)) prenom, 
	trim(upper(nom_proprietaire)) nom, 
	num_proprietaire num, trim(initcap(rue_proprietaire)) rue, 
	telephone_proprietaire telephone
	from donnees
	group by 1, 2, 3, 4, 5) prop
;
alter table proprietaire add constraint proprietaire_pkey primary key (id);

-- Création de la table MECANICIEN
create table mecanicien as
select row_number() over (order by nom, prenom) id, meca.*
from (
	select trim(initcap(prenom_mecanicien)) prenom, 
	trim(upper(nom_mecanicien)) nom, 
	num_mecanicien num, trim(initcap(rue_mecanicien)) rue, 
	telephone_mecanicien telephone
	from donnees
	group by 1, 2, 3, 4, 5) meca
;
alter table mecanicien add constraint mecanicien_pkey primary key (id);

-- Création de la table AVION
create table avion as 
select distinct donnees.matricule_avion matricule, type_avion.id id_type_avion, 
proprietaire.id id_proprietaire
from donnees
join type_avion on donnees.type_avion=type_avion.nom
left join proprietaire on trim(upper(donnees.nom_proprietaire))=proprietaire.nom
order by 1;
alter table avion add constraint avion_pkey primary key (matricule);
alter table avion add constraint avion_id_type_avion_fkey foreign key (id_type_avion) 
  references type_avion(id);
alter table avion add constraint avion_id_proprietaire_fkey foreign key (id_proprietaire)
  references proprietaire(id);

-- Création de la table INTERVENTION
create table intervention as
select d.num, d.objet, d.date, d.duree, d.matricule_avion, m.id id_mecanicien
from donnees d
left join mecanicien m on m.prenom=initcap(trim(d.prenom_mecanicien)) and m.nom=upper(trim(d.nom_mecanicien))
order by d.num
;
alter table intervention add constraint intervention_pkey primary key (num);
alter table intervention add constraint intervention_matricule_avion_fkey foreign key (matricule_avion) references avion(matricule_avion);
alter table intervention add constraint intervention_id_mecanicien_fkey foreign key (id_mecanicien) references mecanicien(id);

-- Création de al table HABILITATION
create table habilitation as 
select m.id id_mecanicien, t.id id_type_avion 
from (
	select initcap(trim(prenom_mecanicien)) prenom, upper(trim(nom_mecanicien)) nom, habilitation_1 hab
	from donnees
	union
	select initcap(trim(prenom_mecanicien)), upper(trim(nom_mecanicien)), habilitation_2 hab
	from donnees where habilitation_2 is not null
	union
	select initcap(trim(prenom_mecanicien)), upper(trim(nom_mecanicien)), habilitation_3 hab
	from donnees where habilitation_3 is not null
) h
join mecanicien m on m.nom=h.nom and m.prenom=h.prenom
join type_avion t on t.nom=h.hab
;
alter table habilitation add constraint habilitation_pkey primary key (id_mecanicien,id_type_avion);
alter table habilitation add constraint habilitation_id_mecanicien_fkey foreign key (id_mecanicien) references mecanicien(id);
alter table habilitation add constraint habilitation_id_type_avion_fkey foreign key (id_type_avion) references type_avion(id);