--DROP VIEW numeroNoleggi;
--DROP TABLE Noleggio;
--DROP TABLE Stazione;
--DROP TABLE Bicicletta;
--DROP TABLE Utente;


CREATE TABLE Utente
(
	ut_id INTEGER PRIMARY KEY,
	ut_nome VARCHAR(30) NOT NULL,
	ut_cognome VARCHAR(30) NOT NULL,
	ut_email VARCHAR(30),
	ut_carta VARCHAR(20) NOT NULL
);

CREATE TABLE Bicicletta
(
	bc_id INTEGER PRIMARY KEY,
	bc_modello VARCHAR(20),
	bc_prezzo INTEGER NOT NULL --Prezzo orario del noleggio
);

CREATE TABLE Stazione
(
	st_id INTEGER PRIMARY KEY,
	st_indirizzo VARCHAR(50) NOT NULL,  --Andrebbe spezzato (via,numCivico)
	st_slot INTEGER NOT NULL CHECK(st_slot >= 0 AND st_slot <= 50) --Se st_slot = 50 ci sono 50 bici libere
	
);

CREATE TABLE Noleggio
(
	nl_id INTEGER PRIMARY KEY,
	nl_inizio TIMESTAMP,
	nl_fine TIMESTAMP,
	nl_utente INTEGER NOT NULL REFERENCES Utente(ut_id),
	nl_bici INTEGER NOT NULL REFERENCES Bicicletta(bc_id),
	nl_stPrelievo INTEGER NOT NULL REFERENCES Stazione(st_id),
	nl_stDeposito INTEGER REFERENCES Stazione(st_id)  --Se NULL la bici non ancora riconsegnata
);



INSERT INTO Utente VALUES
(1,'Luca','Rossi','luca.rossi@mail.com','1111222233334444'),
(2,'Marco','Bianchi','marco.bianchi@mail.com','2222333344445555'),
(3,'Anna','Verdi','anna.verdi@mail.com','3333444455556666'),
(4,'Giulia','Neri','giulia.neri@mail.com','4444555566667777'),
(5,'Paolo','Gialli','paolo.gialli@mail.com','5555666677778888'),
(6,'Sara','Blu','sara.blu@mail.com','6666777788889999'),
(7,'Davide','Marrone','davide.marrone@mail.com','7777888899990000'),
(8,'Elena','Rosa','elena.rosa@mail.com','8888999900001111'),
(9,'Simone','Ferrari','simone.ferrari@mail.com','9999000011112222'),
(10,'Chiara','Ricci','chiara.ricci@mail.com','1234123412341234');

INSERT INTO Bicicletta VALUES
(1,'CityBike',2),
(2,'MountainBike',3),
(3,'E-Bike',5),
(4,'CityBike',2),
(5,'MountainBike',3),
(6,'E-Bike',5),
(7,'CityBike',2),
(8,'MountainBike',3),
(9,'E-Bike',5),
(10,'CityBike',2);

INSERT INTO Stazione VALUES
(1,'Via Roma 1',20),
(2,'Via Milano 10',35),
(3,'Via Firenze 5',15),
(4,'Via Torino 8',40),
(5,'Via Napoli 3',25),
(6,'Via Venezia 12',30),
(7,'Via Bologna 7',10),
(8,'Via Genova 6',45),
(9,'Via Palermo 9',18),
(10,'Via Bari 11',50);

INSERT INTO Noleggio VALUES
(1,'2026-04-01 08:12','2026-04-01 09:05',3,1,2,5),
(2,'2026-04-01 08:45','2026-04-01 09:30',1,4,1,3),
(3,'2026-04-01 09:10','2026-04-01 10:50',7,2,6,4),
(4,'2026-04-01 09:40','2026-04-01 10:20',5,8,3,7),
(5,'2026-04-01 10:05','2026-04-01 11:00',9,3,5,2),
(6,'2026-04-01 10:30','2026-04-01 11:40',2,6,4,8),
(7,'2026-04-01 11:00','2026-04-01 12:10',6,9,7,1),
(8,'2026-04-01 11:25','2026-04-01 12:00',4,5,8,6),
(9,'2026-04-01 12:10','2026-04-01 13:30',8,7,9,3),
(10,'2026-04-01 12:40','2026-04-01 13:10',10,10,10,4),

(11,'2026-04-02 08:05','2026-04-02 08:50',1,2,2,6),
(12,'2026-04-02 08:30','2026-04-02 09:40',2,3,3,7),
(13,'2026-04-02 09:00','2026-04-02 10:10',3,4,4,8),
(14,'2026-04-02 09:20','2026-04-02 10:00',4,5,5,9),
(15,'2026-04-02 10:10','2026-04-02 11:30',5,6,6,10),
(16,'2026-04-02 10:45','2026-04-02 11:20',6,7,7,2),
(17,'2026-04-02 11:15','2026-04-02 12:30',7,8,8,3),
(18,'2026-04-02 11:50','2026-04-02 12:40',8,9,9,4),
(19,'2026-04-02 12:30','2026-04-02 13:20',9,10,10,5),
(20,'2026-04-02 13:00','2026-04-02 14:10',10,1,1,6),

(21,'2026-04-03 08:15','2026-04-03 09:05',2,5,2,7),
(22,'2026-04-03 08:50','2026-04-03 09:45',3,6,3,8),
(23,'2026-04-03 09:20','2026-04-03 10:30',4,7,4,9),
(24,'2026-04-03 09:40','2026-04-03 10:20',5,8,5,10),
(25,'2026-04-03 10:10','2026-04-03 11:30',6,9,6,1),
(26,'2026-04-03 10:35','2026-04-03 11:20',7,10,7,2),
(27,'2026-04-03 11:00','2026-04-03 12:00',8,1,8,3),
(28,'2026-04-03 11:40','2026-04-03 12:50',9,2,9,4),
(29,'2026-04-03 12:15','2026-04-03 13:05',10,3,10,5),
(30,'2026-04-03 12:50','2026-04-03 13:40',1,4,1,6),

(31,'2026-04-04 08:20','2026-04-04 09:15',2,6,2,8),
(32,'2026-04-04 08:55','2026-04-04 09:50',3,7,3,9),
(33,'2026-04-04 09:10','2026-04-04 10:30',4,8,4,10),
(34,'2026-04-04 09:35','2026-04-04 10:20',5,9,5,1),
(35,'2026-04-04 10:00','2026-04-04 11:15',6,10,6,2),
(36,'2026-04-04 10:40','2026-04-04 11:30',7,1,7,3),
(37,'2026-04-04 11:10','2026-04-04 12:20',8,2,8,4),
(38,'2026-04-04 11:45','2026-04-04 12:40',9,3,9,5),
(39,'2026-04-04 12:20','2026-04-04 13:10',10,4,10,6),
(40,'2026-04-04 12:55','2026-04-04 13:45',1,5,1,7),

(41,'2026-04-05 08:10','2026-04-05 09:00',2,7,2,9),
(42,'2026-04-05 08:40','2026-04-05 09:50',3,8,3,10),
(43,'2026-04-05 09:15','2026-04-05 10:05',4,9,4,1),
(44,'2026-04-05 09:50','2026-04-05 10:40',5,10,5,2),
(45,'2026-04-05 10:20','2026-04-05 11:30',6,1,6,3),
(46,'2026-04-05 10:55','2026-04-05 11:45',7,2,7,4),
(47,'2026-04-05 11:30','2026-04-05 12:40',8,3,8,5),
(48,'2026-04-05 12:05','2026-04-05 13:00',9,4,9,6),
(49,'2026-04-05 12:30','2026-04-05 13:20',10,5,10,7),
(50,'2026-04-05 13:00','2026-04-05 14:00',1,6,1,8),

(51,'2026-04-06 08:05','2026-04-06 09:15',2,8,2,10),
(52,'2026-04-06 08:45','2026-04-06 09:40',3,9,3,1),
(53,'2026-04-06 09:20','2026-04-06 10:10',4,10,4,2),
(54,'2026-04-06 09:50','2026-04-06 10:45',5,1,5,3),
(55,'2026-04-06 10:15','2026-04-06 11:30',6,2,6,4),
(56,'2026-04-06 10:40','2026-04-06 11:35',7,3,7,5),
(57,'2026-04-06 11:10','2026-04-06 12:00',8,4,8,6),
(58,'2026-04-06 11:50','2026-04-06 12:45',9,5,9,7),
(59,'2026-04-06 12:30','2026-04-06 13:25',10,6,10,8),
(60,'2026-04-06 13:05','2026-04-06 14:10',1,7,1,9),

(61,'2026-04-07 08:10','2026-04-07 09:00',2,9,2,1),
(62,'2026-04-07 08:50','2026-04-07 09:45',3,10,3,2),
(63,'2026-04-07 09:20','2026-04-07 10:15',4,1,4,3),
(64,'2026-04-07 09:40','2026-04-07 10:30',5,2,5,4),
(65,'2026-04-07 10:10','2026-04-07 11:20',6,3,6,5),
(66,'2026-04-07 10:45','2026-04-07 11:35',7,4,7,6),
(67,'2026-04-07 11:15','2026-04-07 12:05',8,5,8,7),
(68,'2026-04-07 11:50','2026-04-07 12:45',9,6,9,8),
(69,'2026-04-07 12:20','2026-04-07 13:15',10,7,10,9),
(70,'2026-04-07 12:55','2026-04-07 13:50',1,8,1,10),

(71,'2026-04-08 08:00','2026-04-08 09:10',2,10,2,3),
(72,'2026-04-08 08:40','2026-04-08 09:35',3,1,3,4),
(73,'2026-04-08 09:10','2026-04-08 10:20',4,2,4,5),
(74,'2026-04-08 09:50','2026-04-08 10:40',5,3,5,6),
(75,'2026-04-08 10:20','2026-04-08 11:30',6,4,6,7),
(76,'2026-04-08 10:55','2026-04-08 11:50',7,5,7,8),
(77,'2026-04-08 11:25','2026-04-08 12:20',8,6,8,9),
(78,'2026-04-08 12:00','2026-04-08 13:00',9,7,9,10),
(79,'2026-04-08 12:30','2026-04-08 13:25',10,8,10,1),
(80,'2026-04-08 13:05','2026-04-08 14:10',1,9,1,2),

(81,'2026-04-09 08:10','2026-04-09 09:00',2,1,2,4),
(82,'2026-04-09 08:50','2026-04-09 09:45',3,2,3,5),
(83,'2026-04-09 09:20','2026-04-09 10:10',4,3,4,6),
(84,'2026-04-09 09:40','2026-04-09 10:30',5,4,5,7),
(85,'2026-04-09 10:10','2026-04-09 11:20',6,5,6,8),
(86,'2026-04-09 10:45','2026-04-09 11:35',7,6,7,9),
(87,'2026-04-09 11:15','2026-04-09 12:05',8,7,8,10),
(88,'2026-04-09 11:50','2026-04-09 12:45',9,8,9,1),
(89,'2026-04-09 12:20','2026-04-09 13:15',10,9,10,2),
(90,'2026-04-09 12:55','2026-04-09 13:50',1,10,1,3),

(91,'2026-04-10 08:05','2026-04-10 09:00',2,2,2,5),
(92,'2026-04-10 08:40','2026-04-10 09:35',3,3,3,6),
(93,'2026-04-10 09:15','2026-04-10 10:05',4,4,4,7),
(94,'2026-04-10 09:50','2026-04-10 10:40',5,5,5,8),
(95,'2026-04-10 10:20','2026-04-10 11:30',6,6,6,9),
(96,'2026-04-10 10:55','2026-04-10 11:50',7,7,7,10),
(97,'2026-04-10 11:25','2026-04-10 12:20',8,8,8,1),
(98,'2026-04-10 12:00','2026-04-10 13:00',9,9,9,2),
(99,'2026-04-10 12:30','2026-04-10 13:25',10,10,10,3),
(100,'2026-04-10 13:10',NULL,1,1,1,NULL),
(101,'2025-04-01 08:00','2025-04-01 09:00',1,1,1,2); -- inserita per avere stazione con numero massimo noleggi (noleggi = 4)



-- query 1: dato il codice di una bicicletta elencare gli utenti che l’hanno utilizzata nel mese corrente

SELECT ut_nome, ut_cognome, nl_fine
FROM Utente, Noleggio
WHERE ut_id = nl_utente AND EXTRACT(MONTH FROM nl_fine) = 4
ORDER BY ut_nome, ut_cognome, nl_fine;

-- query 2: mostrare la stazione presso la quale è stato effettuato il maggior numero di noleggi in un dato periodo.

CREATE VIEW numeroNoleggi (stazione, noleggi) AS
SELECT nl_stPrelievo, COUNT(*) AS numeroNoleggi
FROM noleggio
GROUP BY nl_stPrelievo;

SELECT stazione, noleggi
FROM numeroNoleggi
WHERE noleggi = (SELECT MAX(noleggi) FROM numeroNoleggi)


--un report contenente le bici noleggiate da un utente, le stazioni in cui sono state prelevate e restituite, 
--la durata del noleggio ed i relativi costi

SELECT 
	st_indirizzo, 
	bc_modello , 
	(SELECT st_indirizzo FROM stazione WHERE st_id = nl_stDeposito ) AS indirizzo_deposito, 
	nl_fine - nl_inizio AS durata_noleggio, 
	ROUND((CAST(EXTRACT(EPOCH FROM (nl_fine-nl_inizio)) AS NUMERIC ) / 3600) * bc_prezzo, 2) AS costo_noleggio
FROM noleggio, stazione, bicicletta
WHERE nl_utente = 1 AND st_id = nl_stPrelievo AND nl_bici = bc_id
ORDER BY st_indirizzo, bc_modello ;
