CREATE DATABASE db IF NOT EXIST;

USE db;

CREATE TABLE t_aeroport(
   aeroport_id INT AUTO_INCREMENT,
   code_iata CHAR(3),
   code_icao CHAR(4),
   nom VARCHAR(50),
   PRIMARY KEY(aeroport_id),
   UNIQUE(code_icao)
);

CREATE TABLE t_compagnie(
   compagnie_id INT AUTO_INCREMENT,
   code_iata CHAR(2),
   nom VARCHAR(30),
   aeroport_fk INT NOT NULL,
   PRIMARY KEY(compagnie_id),
   FOREIGN KEY(aeroport_fk) REFERENCES t_aeroport(aeroport_id)
);

CREATE TABLE t_type_avion(
   type_avion_id INT AUTO_INCREMENT,
   nom VARCHAR(50),
   description TEXT,
   PRIMARY KEY(type_avion_id)
);

CREATE TABLE t_avion(
   avion_id INT AUTO_INCREMENT,
   capacite INT,
   compagnie_fk INT NOT NULL,
   type_avion_fk INT NOT NULL,
   PRIMARY KEY(avion_id),
   FOREIGN KEY(compagnie_fk) REFERENCES t_compagnie(compagnie_id),
   FOREIGN KEY(type_avion_fk) REFERENCES t_type_avion(type_avion_id)
);

CREATE TABLE t_programme_vol(
   numero_vol CHAR(8),
   heure_depart TIME,
   heure_arrivee TIME,
   lundi TINYINT,
   mardi TINYINT,
   mercredi TINYINT,
   jeudi TINYINT,
   vendredi TINYINT,
   samedi TINYINT,
   dimanche TINYINT,
   compagnie_fk INT NOT NULL,
   aeroport_arrivee_fk INT NOT NULL,
   aeroport_depart_fk INT NOT NULL,
   PRIMARY KEY(numero_vol),
   FOREIGN KEY(compagnie_fk) REFERENCES t_compagnie(compagnie_id),
   FOREIGN KEY(aeroport_arrivee_fk) REFERENCES t_aeroport(aeroport_id),
   FOREIGN KEY(aeroport_depart_fk) REFERENCES t_aeroport(aeroport_id)
);

CREATE TABLE t_passager(
   passager_id INT AUTO_INCREMENT,
   numero_passeport CHAR(9),
   nom VARCHAR(100),
   prenom VARCHAR(100),
   PRIMARY KEY(passager_id),
   UNIQUE(numero_passeport)
);

CREATE TABLE t_vol(
   vol_id INT AUTO_INCREMENT,
   heure_depart TIME,
   heure_arrivee TIME,
   avion_fk INT NOT NULL,
   compagnie_fk INT NOT NULL,
   aeroport_arrivee_fk INT NOT NULL,
   aeroport_depart_fk INT NOT NULL,
   numero_vol_fk CHAR(8) NOT NULL,
   PRIMARY KEY(vol_id),
   FOREIGN KEY(avion_fk) REFERENCES t_avion(avion_id),
   FOREIGN KEY(compagnie_fk) REFERENCES t_compagnie(compagnie_id),
   FOREIGN KEY(aeroport_arrivee_fk) REFERENCES t_aeroport(aeroport_id),
   FOREIGN KEY(aeroport_depart_fk) REFERENCES t_aeroport(aeroport_id),
   FOREIGN KEY(numero_vol_fk) REFERENCES t_programme_vol(numero_vol)
);

CREATE TABLE t_reservation(
   reservation_id INT AUTO_INCREMENT,
   siege CHAR(4),
   prix DECIMAL(10,2),
   passager_fk INT NOT NULL,
   vol_fk INT NOT NULL,
   PRIMARY KEY(reservation_id),
   FOREIGN KEY(passager_fk) REFERENCES t_passager(passager_id),
   FOREIGN KEY(vol_fk) REFERENCES t_vol(vol_id)
);