CREATE TABLE Aeroporto (
    id_aeroporto INT PRIMARY KEY,
    nome VARCHAR(50) NOT NULL,
    citta VARCHAR(50) NOT NULL,
    nazione VARCHAR(50) NOT NULL
);

CREATE TABLE Cliente (
    id_cliente INT PRIMARY KEY,
    nome VARCHAR(50) NOT NULL,
    cognome VARCHAR(50) NOT NULL,
    documento VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE Tratta (
id_tratta INT PRIMARY KEY,
partenza INT REFERENCES Aeroporto (id_aeroporto) NOT NULL,
destinazione INT REFERENCES Aeroporto (id_aeroporto) NOT NULL,
capienza INT NOT NULL,
costo DECIMAL (8,2) NOT NULL

);
CREATE TABLE Volo (
    id_volo INT PRIMARY KEY,
    tratta INT REFERENCES Tratta(id_tratta) NOT NULL,
    data_ora_partenza TIMESTAMP NOT NULL
);

CREATE TABLE Prenotazione (
    id_prenotazione INT PRIMARY KEY,
    cliente INT REFERENCES Cliente(id_cliente) NOT NULL,
    volo INT REFERENCES Volo(id_volo) NOT NULL
);


-- INSERT Aeroporto
INSERT INTO Aeroporto (id_aeroporto, nome, citta, nazione) VALUES
(1, 'Leonardo da Vinci', 'Roma', 'Italia'),
(2, 'Malpensa', 'Milano', 'Italia'),
(3, 'Charles de Gaulle', 'Parigi', 'Francia'),
(4, 'Heathrow', 'Londra', 'Regno Unito'),
(5, 'Barajas', 'Madrid', 'Spagna');

-- INSERT Cliente
INSERT INTO Cliente (id_cliente, nome, cognome, documento) VALUES
(1, 'Mario', 'Rossi', 'AA123456'),
(2, 'Luca', 'Bianchi', 'BB234567'),
(3, 'Giulia', 'Verdi', 'CC345678'),
(4, 'Anna', 'Neri', 'DD456789'),
(5, 'Marco', 'Gialli', 'EE567890');

-- INSERT Tratta
INSERT INTO Tratta (id_tratta, partenza, destinazione, capienza, costo) VALUES
(1, 1, 2, 180, 120.50),
(2, 2, 3, 200, 180.00),
(3, 3, 4, 220, 150.75),
(4, 4, 5, 170, 210.00),
(5, 5, 1, 190, 250.25);

-- INSERT Volo
INSERT INTO Volo (id_volo, tratta, data_ora_partenza) VALUES
(1, 1, '2026-06-01 08:30:00'),
(2, 2, '2026-06-02 10:15:00'),
(3, 3, '2026-06-03 14:45:00'),
(4, 4, '2026-06-04 18:00:00'),
(5, 5, '2026-06-05 21:20:00');

-- INSERT Prenotazione
INSERT INTO Prenotazione (id_prenotazione, cliente, volo) VALUES
(1, 1, 1),
(2, 2, 2),
(3, 3, 3),
(4, 4, 4),
(5, 5, 5),
(6, 1, 3),
(7, 2, 5),
(8, 2, 1);



-- 1)Per ogni singolo volo occorre conoscere l’elenco dei passeggeri che lo hanno prenotato.

SELECT nome, cognome, documento 
FROM cliente, prenotazione, volo
WHERE id_cliente = cliente AND volo = id_volo
	AND id_volo = 1
ORDER BY cognome, nome



-- 2)Per ogni singola tratta occorre conoscere il totale dei passeggeri in un certo periodo.

SELECT COUNT(cliente) AS num_passeggeri, nome AS aeroporto_partenza
FROM prenotazione, volo, tratta, aeroporto
WHERE volo = id_volo AND tratta = id_tratta AND partenza = id_aeroporto
	AND id_tratta = 1
GROUP BY aeroporto_partenza, destinazione;

SELECT COUNT(cliente) AS num_passeggeri, nome AS aeroporto_destinazione
FROM prenotazione, volo, tratta, aeroporto
WHERE volo = id_volo AND tratta = id_tratta AND destinazione = id_aeroporto
	AND id_tratta = 1
GROUP BY aeroporto_destinazione, destinazione

-- oppure in una sola query (duplicando con a e b l'aeroporto)

SELECT COUNT(cliente) AS num_passeggeri, a.nome AS partenza, b.nome AS arrivo
FROM prenotazione, volo, tratta, aeroporto a, aeroporto b
WHERE volo = id_volo AND tratta = id_tratta 
	AND partenza = a.id_aeroporto AND destinazione = b.id_aeroporto
	AND id_tratta = 1
GROUP BY a.nome, b.nome, destinazione;





--4. Numero di viaggiatori per aeroporto a fine anno
--(considerando sia partenze che arrivi)

SELECT
    id_aeroporto,
    nome,
    COUNT(cliente) AS NumeroViaggiatori
FROM Aeroporto 
JOIN Tratta 
    ON id_aeroporto = partenza
    OR id_aeroporto = destinazione -- NOTA OR che permette una doppia JOIN !!!
JOIN Volo 
    ON id_tratta = tratta
JOIN Prenotazione 
    ON volo = id_volo
WHERE EXTRACT(YEAR FROM data_ora_partenza) = 2026 -- periodo
GROUP BY id_aeroporto, nome
ORDER BY id_aeroporto;


-- 3)I clienti devono poter conoscere la disponibilità di ogni volo.

SELECT id_volo, (capienza - COUNT(cliente)) as posti_disponibili
FROM prenotazione, volo, tratta
WHERE volo = id_volo AND tratta = id_tratta
GROUP BY id_volo, capienza -- NB capienza va incluso in GROUP BY 
ORDER BY id_volo