-- *********************************************
-- * Case study: Car Sharing (PickACar)
-- * Tables, operazioni e trigger
-- *
-- * Schema logico di input: PAC/logico-FINAL 
-- ********************************************* 

--
-- Creazione delle tables
-- 

create table UTENTI (
     IDUtente INT NOT NULL PRIMARY KEY
     	GENERATED ALWAYS AS IDENTITY (START WITH 10, INCREMENT BY 1),
     Cognome varchar(30) not null,
     Nome varchar(30) not null
     -- Indirizzo char(1) not null,
     -- Patente char(1) not null,
     -- CCCircuito char(1) not null,
     -- CCNumero char(1) not null,
     -- CCTitolare char(1) not null,
     -- ScadenzaMese char(1) not null,
     -- ScadenzaAnno char(1) not null,
     );

create table PARCHEGGI (
    CodiceParcheggio char(4) NOT NULL PRIMARY KEY,
    Indirizzo varchar(30) not null
	);
	
create table AUTO (
     Targa char(7) NOT NULL PRIMARY KEY,
     -- Modello char(1) not null,
     Km INT not null,
     IN_USO char(1) DEFAULT NULL CHECK (IN_USO = 'Y'),
     DISPONIBILE char(1) DEFAULT 'Y' CHECK (DISPONIBILE = 'Y'),
     PRENOTATA char(1) DEFAULT NULL CHECK (PRENOTATA = 'Y'),
     CodiceParcheggio char(4)
     );

alter table AUTO add constraint FKPIP
     foreign key (CodiceParcheggio)
     references PARCHEGGI;

alter table AUTO add constraint GRAUTO
     check((IN_USO is not null and DISPONIBILE is null and PRENOTATA is null)
           or (IN_USO is null and DISPONIBILE is not null and PRENOTATA is null)
           or (IN_USO is null and DISPONIBILE is null and PRENOTATA is not null)); 

alter table AUTO add constraint GRAUTO_1
     check((IN_USO is not null and CodiceParcheggio is null)
           or (IN_USO is null and CodiceParcheggio is not null)); 

create table PRENOTATE (
     Targa char(7) NOT NULL PRIMARY KEY,
     CodicePrenotazione char(10) NOT NULL UNIQUE
		     		GENERATED ALWAYS AS ('PR_' || Targa),
     DataRitiro DATE not null,
     OrarioRitiro TIME not null,
     RITIRO char(1) DEFAULT NULL CHECK (RITIRO IN ('Y','N')),
     IDUtente INT not null
     );

alter table PRENOTATE add constraint FKR_FK
     foreign key (Targa)
     references AUTO;

alter table PRENOTATE add constraint FKUP
     foreign key (IDUtente)
     references UTENTI;

create table NOLEGGI_IN_CORSO (
     IDNoleggioTemp INT NOT NULL PRIMARY KEY
     	GENERATED ALWAYS AS IDENTITY (START WITH 1000, INCREMENT BY 2),
     IDUtente INT not null,
     Targa char(7) not null,
     CodParcheggioRitiro char(4) not null,
     DataRitiro DATE not null,
     OrarioRitiro TIME not NULL
     );
     
alter table NOLEGGI_IN_CORSO add constraint GRNOLEGGI_IN_CORSO
     foreign key (CodParcheggioRitiro)
     references PARCHEGGI;

alter table NOLEGGI_IN_CORSO add constraint GRNOLEGGI_IN_CORSO_2
     foreign key (Targa)
     references AUTO;

alter table NOLEGGI_IN_CORSO add constraint GRNOLEGGI_IN_CORSO_1
     foreign key (IDUtente)
     references UTENTI;

 create table NOLEGGI_CONCLUSI (
     IDNoleggio INT NOT NULL PRIMARY KEY
     	GENERATED ALWAYS AS IDENTITY (START WITH 1001, INCREMENT BY 2),
     IDUtente INT not null,
     Targa char(7) not null,
     CodParcheggioRitiro char(4) not null,
     DataRitiro DATE not null,
     OrarioRitiro TIME not null,
     CodParcheggioRiconsegna char(4) not null,
     DataRiconsegna DATE not null,
     OrarioRiconsegna TIME not null,
     KmPercorsi INT not null,
     Costo DEC(7,2) not NULL
     );

alter table NOLEGGI_CONCLUSI add constraint FKNCA
     foreign key (Targa)
     references AUTO;

alter table NOLEGGI_CONCLUSI add constraint FKRICONSEGNE
     foreign key (CodParcheggioRiconsegna)
     references PARCHEGGI;

alter table NOLEGGI_CONCLUSI add constraint FKRITIRI
     foreign key (CodParcheggioRitiro)
     references PARCHEGGI;

alter table NOLEGGI_CONCLUSI add constraint FKUN
     foreign key (IDUtente)
     references UTENTI;

--
-- Inserimento dati d'esempio
-- 

INSERT INTO UTENTI(Cognome,Nome)
VALUES (:cognome,:nome);

INSERT INTO PARCHEGGI(CodiceParcheggio,Indirizzo)
VALUES (:codPark,:indirizzo);

INSERT INTO AUTO(Targa,Km,CodiceParcheggio)
VALUES (:targa,:km,:codPark);

--
-- Operazioni e trigger
--

--
-- PRENOTAZIONE
--
CREATE TRIGGER DISP2PRENOTA
AFTER INSERT ON PRENOTATE
REFERENCING NEW AS NP
FOR EACH ROW
UPDATE 	AUTO
SET 	DISPONIBILE = NULL,
		PRENOTATA = 'Y'
WHERE 	Targa = NP.Targa;

INSERT INTO PRENOTATE(Targa,IDUtente,DataRitiro,OrarioRitiro)
VALUES (:targa,:idutente,:data,:ora);

--
-- CANCELLAZIONE
--
CREATE TRIGGER DELETE_PRENOTAZIONE -- scatta anche per il ritiro
AFTER UPDATE ON PRENOTATE
REFERENCING NEW AS NP
FOR EACH ROW
DELETE FROM PRENOTATE
WHERE  CodicePrenotazione =	NP.CodicePrenotazione;

CREATE TRIGGER PRENOTA2DISP
AFTER DELETE ON PRENOTATE
REFERENCING OLD AS OP
FOR EACH ROW
WHEN (OP.RITIRO = 'N')
UPDATE 	AUTO
SET 	DISPONIBILE = 'Y',
    	PRENOTATA = NULL
WHERE 	Targa = OP.Targa;

UPDATE 	PRENOTATE
SET 	RITIRO = 'N'		
WHERE 	CodicePrenotazione = :cod_prenotazione;

--
-- RITIRO
--
CREATE TRIGGER PRENOTA2RIT
AFTER DELETE ON PRENOTATE
REFERENCING OLD AS OP
FOR EACH ROW
WHEN (OP.RITIRO = 'Y')
INSERT INTO NOLEGGI_IN_CORSO(Targa,IDUtente,CodParcheggioRitiro,DataRitiro,OrarioRitiro)
VALUES(OP.Targa,OP.IDUtente,
		(SELECT CodiceParcheggio FROM AUTO WHERE Targa = OP.Targa),CURRENT DATE,CURRENT TIME);

CREATE TRIGGER PRENOTA2IN_USO
AFTER INSERT ON NOLEGGI_IN_CORSO
REFERENCING NEW AS NN
FOR EACH ROW
UPDATE 	AUTO
SET 	IN_USO = 'Y',
		PRENOTATA = NULL,
		CodiceParcheggio = NULL
WHERE 	Targa = NN.Targa;

UPDATE 	PRENOTATE
SET 	RITIRO = 'Y'		
WHERE 	CodicePrenotazione = :cod_prenotazione;

--
-- RICONSEGNA
--
CREATE TRIGGER NOLEGGIO_CONCLUSO
AFTER INSERT ON NOLEGGI_CONCLUSI
REFERENCING NEW AS NNC
FOR EACH ROW
BEGIN ATOMIC
DELETE  FROM NOLEGGI_IN_CORSO
WHERE   Targa = NNC.Targa;
UPDATE  AUTO
SET 	IN_USO = NULL,
    	DISPONIBILE = 'Y',
    	CodiceParcheggio = NNC.CodParcheggioRiconsegna,
    	Km = Km + NNC.KmPercorsi
WHERE 	Targa = NNC.Targa;
END

INSERT INTO NOLEGGI_CONCLUSI
       (Targa,IDUtente,CodParcheggioRitiro,DataRitiro,OrarioRitiro,
	    CodParcheggioRiconsegna,DataRiconsegna,OrarioRiconsegna,KmPercorsi,Costo)
SELECT	Targa,IDUtente,CodParcheggioRitiro,DataRitiro,OrarioRitiro,
        :codPark,CURRENT DATE,CURRENT TIME,:kmpercorsi,:costo
FROM 	NOLEGGI_IN_CORSO
WHERE 	Targa = :targa;

