CREATE TABLE paziente (patientID INTEGER PRIMARY KEY AUTOINCREMENT,Nome VARCHAR(20),Cognome TEXT,Sesso CHAR,DataNascita DATE, CF TEXT UNIQUE);


INSERT INTO paziente (Nome, Cognome, Sesso, DataNascita, CF) VALUES
('Beppe', 'Lollobrigida', 'M', '1955-02-02', 'TRWXIM24E11D237Y'),
('Michelangelo', 'Ciani', 'M', '1950-10-18', 'GMBLYR98E14M799E'),
('Enzio', 'Spallanzani', 'M', '1992-02-01', 'AONNXS36K05E656C'),
('Pompeo', 'Stradivari', 'M', '1990-08-23', 'PRXUYA49O44A801U'),
('Ermanno', 'Greggio', 'M', '1961-07-06', 'LEUMSH56B73S918R'),
('Assunta', 'Montalbano', 'F', '1985-03-12', 'CTSRNX57H45V920U'),
('Filippo', 'Ambrosio', 'M', '1978-11-30', 'MNLJDU94S55O304X'),
('Sergio', 'Capone', 'M', '1967-12-15', 'BRQIUO12N90W154V'),
('Alessio', 'Conti', 'M', '1995-06-21', 'NKLYJP80U39E621A'),
('Lucia', 'Verdi', 'F', '1982-09-03', 'OJRYXT71M49R232Q');

CREATE TABLE dipendenti (employeeID INTEGER PRIMARY KEY AUTOINCREMENT,Nome TEXT, Cognome TEXT,matricola TEXT UNIQUE,Sesso CHAR, Qualifica TEXT);


INSERT INTO dipendenti (Nome, Cognome, matricola, Sesso, Qualifica) VALUES
('Giuseppe', 'Rossi', 'MAT12345', 'M', 'Medico Generico'),
('Alessandra', 'Bianchi', 'MAT12346', 'F', 'Cardiologo'),
('Luca', 'Verdi', 'MAT12347', 'M', 'Chirurgo'),
('Maria', 'Neri', 'MAT12348', 'F', 'Pediatra'),
('Francesco', 'Gialli', 'MAT12349', 'M', 'Neurologo'),
('Chiara', 'Blu', 'MAT12350', 'F', 'Dermatologo'),
('Marco', 'Sarti', 'MAT12351', 'M', 'Ortopedico'),
('Elena', 'Grassi', 'MAT12352', 'F', 'Psicologo'),
('Matteo', 'Moretti', 'MAT12353', 'M', 'Radiologo'),
('Lucia', 'Ricci', 'MAT12354', 'F', 'Ginecologo');

CREATE TABLE terapia ( therapyID INTEGER PRIMARY KEY AUTOINCREMENT, patientID INTEGER, Nome TEXT, CasaProduttrice TEXT, costo DECIMAL(5,2), PrincipioAttivo TEXT, DataInizio DATE, DataFine DATE, FOREIGN KEY(patientID) REFERENCES paziente(patientID) ON DELETE CASCADE );

INSERT INTO terapia (Nome, CasaProduttrice, costo, PrincipioAttivo, DataInizio, DataFine) VALUES
('Antibiotico A', 'FarmacoCorp', 24.50, 'Amoxicillina', '2023-01-15', '2023-02-15'),
('Antidepressivo B', 'PharmaItal', 45.00, 'Sertralina', '2022-10-01', '2023-03-01'),
('Analgesico C', 'MedicaPlus', 12.30, 'Ibuprofene', '2023-03-10', '2023-03-20'),
('Antinfiammatorio D', 'BioPharma', 32.99, 'Naproxene', '2023-04-22', '2023-05-06'),
('Antipiretico E', 'HealthPharma', 5.20, 'Paracetamolo', '2023-05-10', '2023-05-17'),
('Antibiotico X', 'FarmacoCorp', 28.75, 'Ciprofloxacina', '2023-02-20', '2023-03-15'),
('Sedativo Y', 'PharmaItal', 55.10, 'Diazepam', '2022-12-01', '2023-01-01'),
('Antistaminico Z', 'MedicaPlus', 10.50, 'Cetirizina', '2023-06-01', '2023-06-10'),
('Corticosteroide W', 'BioPharma', 25.30, 'Cortisone', '2023-07-01', '2023-07-20'),
('Broncodilatatore Q', 'HealthPharma', 18.99, 'Salbutamolo', '2023-08-05', '2023-08-15');