CREATE TABLE REPARTI (
    CodiceReparto CHAR(5) PRIMARY KEY,
    Nome VARCHAR(50) NOT NULL
);
CREATE TABLE PAZIENTI (
    CodiceFiscale CHAR(16) PRIMARY KEY,
    Nome VARCHAR(50) NOT NULL,
    Cognome VARCHAR(50) NOT NULL
);
CREATE TABLE POSTI_LETTO (
    CodiceLetto INTEGER PRIMARY KEY,
    NumeroStanza INTEGER NOT NULL,
    Stato CHAR(1) CHECK (Stato IN ('P','L','O')),
    Reparto CHAR(5) NOT NULL REFERENCES REPARTI(CodiceReparto)
);
CREATE TABLE MALATTIE (
    ID_Malattia INTEGER PRIMARY KEY,
    descrizione VARCHAR(100) NOT NULL
);
CREATE TABLE DEGENZE (
    ID_Degenza INTEGER PRIMARY KEY,
    DataInizio DATE NOT NULL,
    DataFine DATE, -- Se NULL il paziente è ancora ricoverato
    CodiceMalattia INTEGER NOT NULL REFERENCES MALATTIE(ID_Malattia),
    CodiceFiscale CHAR(16) NOT NULL REFERENCES PAZIENTI(CodiceFiscale),
    CodiceLetto INTEGER NOT NULL REFERENCES POSTI_LETTO(CodiceLetto)
);


--INSERT

-- REPARTI
INSERT INTO REPARTI VALUES ('R001', 'Cardiologia');
INSERT INTO REPARTI VALUES ('R002', 'Ortopedia');
INSERT INTO REPARTI VALUES ('R003', 'Neurologia');
INSERT INTO REPARTI VALUES ('R004', 'Pediatria');

-- PAZIENTI
INSERT INTO PAZIENTI VALUES ('RSSMRA80A01H501U', 'Mario', 'Rossi');
INSERT INTO PAZIENTI VALUES ('VRDLGI75B12F205X', 'Luigi', 'Verdi');
INSERT INTO PAZIENTI VALUES ('BNCLRA90C23D612Y', 'Laura', 'Bianchi');
INSERT INTO PAZIENTI VALUES ('NRGNNA85D45G273Z', 'Anna', 'Neri');

-- POSTI_LETTO (Stato: P=prenotato, L=libero, O=occupato)
INSERT INTO POSTI_LETTO VALUES (1, 101, 'L', 'R001');
INSERT INTO POSTI_LETTO VALUES (2, 101, 'O', 'R001');
INSERT INTO POSTI_LETTO VALUES (3, 201, 'L', 'R002');
INSERT INTO POSTI_LETTO VALUES (4, 202, 'P', 'R002');
INSERT INTO POSTI_LETTO VALUES (5, 301, 'O', 'R003');
INSERT INTO POSTI_LETTO VALUES (6, 401, 'L', 'R004');

-- MALATTIE
INSERT INTO MALATTIE VALUES (1, 'Infarto miocardico');
INSERT INTO MALATTIE VALUES (2, 'Frattura femore');
INSERT INTO MALATTIE VALUES (3, 'Ictus');
INSERT INTO MALATTIE VALUES (4, 'Bronchite');

-- DEGENZE
INSERT INTO DEGENZE VALUES (1, DATE '2026-04-01', DATE '2026-04-10', 1, 'RSSMRA80A01H501U', 2);
INSERT INTO DEGENZE VALUES (2, DATE '2026-04-05', NULL, 2, 'VRDLGI75B12F205X', 4);
INSERT INTO DEGENZE VALUES (3, DATE '2026-03-20', DATE '2026-03-30', 3, 'BNCLRA90C23D612Y', 5);
INSERT INTO DEGENZE VALUES (4, DATE '2026-04-15', NULL, 4, 'NRGNNA85D45G273Z', 6);
INSERT INTO DEGENZE VALUES (5, DATE '2026-05-21', DATE '2026-06-10', 4, 'RSSMRA80A01H501U', 1);



--QUERY

 --l'elenco di tutti i posti letto dello stesso reparto per verificare quanti sono liberi;
 SELECT NumeroStanza, CodiceLetto 
 FROM POSTI_LETTO
 WHERE Stato = 'L' AND Reparto = 'R001'
 ORDER BY NumeroStanza;
 
--una tabella con tutti i periodi di degenza dallo stesso malato (anche in reparti diversi):
SELECT DataInizio, DataFine, ID_Degenza, REPARTI.Nome, CodiceReparto
FROM DEGENZE, POSTI_LETTO, REPARTI
WHERE POSTI_LETTO.CodiceLetto = DEGENZE.CodiceLetto 
	AND Reparto = CodiceReparto 
	AND CodiceFiscale = 'RSSMRA80A01H501U'
ORDER BY DataInizio;

--l'elenco di tutti i pazienti che sono stati ricoverati in un certo periodo di tempo
SELECT Nome, Cognome, DataInizio , DataFine, descrizione
FROM PAZIENTI JOIN DEGENZE ON PAZIENTI.CodiceFiscale = DEGENZE.CodiceFiscale
JOIN MALATTIE ON DEGENZE.CodiceMalattia = ID_Malattia
ORDER BY Nome, Cognome, DataInizio;


--VIEW query n^4

CREATE VIEW RICOVERI (Malattia, DurataMedia, Numero_Ricoveri) AS
SELECT descrizione, AVG(DataFine - DataInizio), COUNT(*)
FROM DEGENZE, MALATTIE
WHERE CodiceMalattia = ID_Malattia
GROUP BY descrizione;

--malattia che ha comportato il maggior numero di ricoveri, indicando anche la durata media
--della degenza per quel tipo di malattia
SELECT malattia, Numero_Ricoveri, DurataMedia
FROM RICOVERI
WHERE Numero_Ricoveri = (SELECT MAX(Numero_Ricoveri) FROM RICOVERI)