/* =====================================================================
   SE VOLETE FARE IL DB DA ZERO (CONSUGLIATO)
   Eseguite PRIMA tutto questo blocco per creare lo schema iniziale.
   ===================================================================== */

PRAGMA foreign_keys = ON;  -- attiva i vincoli di chiave esterna in SQLite

/* Prima cancelliamo eventuali tabelle preesistenti con gli stessi nomi,
   così tutti partono da uno schema identico ed evitiamo errori. */

DROP TABLE IF EXISTS Terapia;
DROP TABLE IF EXISTS Paziente;
DROP TABLE IF EXISTS Farmaci;
DROP TABLE IF EXISTS CartellaClinica;
DROP TABLE IF EXISTS Referto;

/* =====================================================================
   CREAZIONE SCHEMA CONSIGLIATO PER GLI ESERCIZI
   ===================================================================== */

/* Tabella dei referti */
CREATE TABLE Referto (
    ID_referto   INTEGER PRIMARY KEY,
    Data         NUMERIC,        -- formato consigliato: 'YYYY-MM-DD'
    Conclusione  TEXT
);

/* Cartella clinica: collega esami e referti */
CREATE TABLE CartellaClinica (
    CodiceEsame         INTEGER PRIMARY KEY,
    Data                NUMERIC,        -- 'YYYY-MM-DD'
    PercorsoDatoMedico  TEXT,
    ID_referto2         INTEGER,
    FOREIGN KEY (ID_referto2) REFERENCES Referto(ID_referto)
);

/* Tabella dei pazienti:
   Aggiungiamo SUBITO Nome e Sesso per evitare di modificarla dopo. */
CREATE TABLE Paziente (
    CF            TEXT PRIMARY KEY,   -- Codice Fiscale
    Cognome       TEXT NOT NULL,
    Nome          TEXT NOT NULL,
    Sesso         TEXT,               -- 'M', 'F', 'X', ecc.
    Data_Nascita  NUMERIC NOT NULL,   -- 'YYYY-MM-DD' o 'YYYY-MM-DD HH:MM:SS'
    Via           TEXT,
    Num_civ       TEXT,
    Citta         TEXT,
    Stato         TEXT,
    CodiceEsame   INTEGER,
    FOREIGN KEY (CodiceEsame) REFERENCES CartellaClinica(CodiceEsame)
);

/* Tabella dei farmaci */
CREATE TABLE Farmaci (
    CodiceFarmaco    INTEGER PRIMARY KEY AUTOINCREMENT,
    PrincipioAttivo  TEXT
);

/* Tabella delle terapie:
   collega Paziente + Farmaco, con date di inizio/fine. */
CREATE TABLE Terapia (
    ID_terapia    INTEGER PRIMARY KEY AUTOINCREMENT,
    CF_paziente   TEXT NOT NULL,
    CodiceFarmaco INTEGER NOT NULL,
    Data_inizio   NUMERIC NOT NULL,   -- 'YYYY-MM-DD'
    Data_fine     NUMERIC,            -- 'YYYY-MM-DD'
    Dose          TEXT,               -- es. "5 mg/die"
    FOREIGN KEY (CF_paziente)   REFERENCES Paziente(CF),
    FOREIGN KEY (CodiceFarmaco) REFERENCES Farmaci(CodiceFarmaco)
);

/* =====================================================================
   1. INSERIMENTO DI DATI (INSERT) – DATE E DATETIME IN SQLITE
   ===================================================================== */

/*
   SQLite NON ha un tipo DATE o DATETIME "vero".
   Consigliato: salvare le date come stringhe di testo in formato ISO:

   - Solo data:                 'YYYY-MM-DD'
   - Data + ora:                'YYYY-MM-DD HH:MM:SS'
   - Data + ora + fuso:         'YYYY-MM-DD HH:MM:SS+NN:NN'

   Anche se il tipo della colonna è NUMERIC, SQLite accetta stringhe
   e le funzioni date(), datetime(), julianday() funzionano correttamente.
*/

/* Inseriamo alcuni referti di esempio */
INSERT INTO Referto (ID_referto, Data, Conclusione) VALUES
    (1, '2018-05-10', 'Controllo di routine nella norma'),
    (2, '2019-11-20', 'Riacutizzazione ipertensione'),
    (3, '2014-03-15', 'Controllo Coagulazione');

/* Cartelle cliniche */
INSERT INTO CartellaClinica (CodiceEsame, Data, PercorsoDatoMedico, ID_referto2) VALUES
    (100, '2018-05-10', '/dati/rossi_2018_05_10.pdf', 1),
    (101, '2019-11-20', '/dati/nunziatella_2019_11_20.pdf', 2),
    (102, '2014-03-15', '/dati/verdi_2014_03_15.pdf', 3);

/* Inserimento pazienti
   Esempio di uso di date in formato 'YYYY-MM-DD' */
INSERT INTO Paziente (CF, Cognome, Nome, Sesso, Data_Nascita, Via, Num_civ, Citta, Stato, CodiceEsame)
VALUES
    ('RSSMRA80A01F205X', 'Rossi',       'Mario',      'M', '1980-01-10', 'Via Roma',     '10', 'Napoli',  'Italia', 100),
    ('VRDLGI90B15H501Y', 'Verdi',       'Luigi',      'M', '1990-02-15', 'Via Milano',   '5',  'Milano',  'Italia', 102),
    ('BNCLRA85C30H501Z', 'Bianchi',     'Laura',      'F', '1985-03-30', 'Via Firenze',  '22', 'Firenze', 'Italia', NULL),
    ('NNZNTL70D20H501W', 'Nunziatella', 'Paolo',      'M', '1970-04-20', 'Via Torino',   '7',  'Torino',  'Italia', 101),
    ('NNZNTL95E05H501K', 'Nunziatella', 'Chiara',     'F', '1995-05-05', 'Via Torino',   '7',  'Torino',  'Italia', NULL),
    ('MRTFNC60F10H501Q', 'Martini',     'Francesca',  'F', '1960-06-10', 'Via Venezia',  '12', 'Venezia', 'Italia', NULL);

/* Inserimento farmaci, compreso il warfarin */
INSERT INTO Farmaci (PrincipioAttivo) VALUES
    ('warfarin'),
    ('aspirina'),
    ('paracetamolo'),
    ('metformina'),
    ('atorvastatina');

/* Controlliamo i codici assegnati automaticamente (per curiosità) */
-- SELECT * FROM Farmaci;

/* Inseriamo alcune terapie
   Notare il formato della data: 'YYYY-MM-DD'
*/
INSERT INTO Terapia (CF_paziente, CodiceFarmaco, Data_inizio, Data_fine, Dose) VALUES
    -- Terapie con warfarin per vari pazienti
    ('NNZNTL70D20H501W', 1, '2014-01-10', '2014-12-20', '5 mg/die'),
    ('NNZNTL70D20H501W', 1, '2018-01-15', '2019-03-10', '3 mg/die'),
    ('RSSMRA80A01F205X', 1, '2019-02-01', '2019-12-15', '4 mg/die'),
    -- Terapie con altri farmaci
    ('VRDLGI90B15H501Y', 2, '2019-01-05', '2019-06-30', '100 mg/die'),
    ('BNCLRA85C30H501Z', 3, '2017-09-01', '2020-01-01', '1 g/die'),
    ('MRTFNC60F10H501Q', 4, '2013-05-01', '2014-02-01', '500 mg/die');

/* Esempi di inserimento usando l’ORA CORRENTE
   (solo a scopo didattico, da NON eseguire se volete dati fissi):

   INSERT INTO Paziente (..., Data_Nascita, ...)
   VALUES (..., date('now', '-30 years'), ...);

   date('now')           -> data odierna
   datetime('now')       -> data + ora correnti
   time('now')           -> solo orario corrente
*/

/* =====================================================================
   2. QUERY DI BASE: SELECT, WHERE, AS, ORDER BY, LIMIT
   ===================================================================== */

-- 2.1 Selezionare tutti i pazienti (tutte le colonne, tutte le righe)
SELECT * 
FROM Paziente;

-- 2.2 Selezionare solo alcune colonne
SELECT CF, Cognome, Nome, Data_Nascita
FROM Paziente;

-- 2.3 Uso di WHERE: pazienti di una certa città
SELECT CF, Cognome, Nome, Citta
FROM Paziente
WHERE Citta = 'Torino';

-- 2.4 Uso di WHERE con più condizioni (AND, OR)
SELECT CF, Cognome, Nome, Sesso, Citta
FROM Paziente
WHERE Citta = 'Torino'
  AND Sesso = 'F';

-- 2.5 Alias di colonna con AS
SELECT 
    CF AS CodiceFiscale,
    Cognome AS Cognome_Paziente,
    Nome AS Nome_Paziente
FROM Paziente;

-- 2.6 Ordinamento con ORDER BY (ASC, DESC)
SELECT Cognome, Nome, Data_Nascita
FROM Paziente
ORDER BY Cognome ASC, Nome ASC;   -- ASC è il default

-- Ordinamento decrescente per data di nascita (dal più giovane al più vecchio)
SELECT Cognome, Nome, Data_Nascita
FROM Paziente
ORDER BY Data_Nascita DESC;

-- 2.7 LIMIT per mostrare solo le prime N righe (es. prime 3)
SELECT Cognome, Nome, Data_Nascita
FROM Paziente
ORDER BY Cognome
LIMIT 3;

/* =====================================================================
   3. OPERATORI, DATE(), DATETIME(), JULIANDAY(), CALCOLO ETÀ
   ===================================================================== */

/* -------- 3.1 Esempi di operatori di confronto --------
   =   uguale
   <>  diverso
   >   maggiore
   >=  maggiore o uguale
   <   minore
   <=  minore o uguale
*/

-- Pazienti nati dopo il 1 gennaio 1985
SELECT Cognome, Nome, Data_Nascita
FROM Paziente
WHERE Data_Nascita > '1985-01-01';

-- Pazienti NON nati nel 1990 (semplificando per anno con strftime)
SELECT Cognome, Nome, Data_Nascita
FROM Paziente
WHERE strftime('%Y', Data_Nascita) <> '1990';


/* -------- 3.2 Funzioni su date in SQLite --------

   date(...)      -> restituisce solo la parte di data in formato 'YYYY-MM-DD'
   datetime(...)  -> restituisce data + ora 'YYYY-MM-DD HH:MM:SS'
   time(...)      -> solo l'ora 'HH:MM:SS'
   julianday(...) -> numero reale di "giorni giuliani" (utile per differenze)

   Esempio: date('now'), datetime('now'), time('now')
*/

-- Data odierna (secondo il sistema)
SELECT date('now') AS Data_oggi;

-- Data e ora correnti
SELECT datetime('now') AS DataOra_oggi;

-- Solo ora corrente
SELECT time('now') AS Ora_oggi;

-- Usare date() sulla Data_Nascita del paziente
SELECT CF, Cognome, Nome,
       date(Data_Nascita)   AS Solo_data,
       time(Data_Nascita)   AS Solo_ora,      -- se la Data_Nascita ha anche l'ora
       datetime(Data_Nascita) AS Data_e_ora
FROM Paziente;


/* -------- 3.3 JULIANDAY: cos'è e a cosa serve --------

   julianday('data') restituisce un numero reale corrispondente ai giorni
   (con parte decimale) trascorsi da una certa data di riferimento (sistema giuliano).

   Per noi è utile perché:
       differenza_in_giorni = julianday(data_fine) - julianday(data_inizio)

   Esempio: calcolare l'età di un paziente:
       (julianday('now') - julianday(Data_Nascita)) / 365.25
*/

-- Calcolo dell'età approssimata di ogni paziente (in anni interi)
SELECT
    CF,
    Cognome,
    Nome,
    CAST((julianday('now') - julianday(Data_Nascita)) / 365.25 AS INTEGER) AS Eta
FROM Paziente;

-- Calcolo della durata (in giorni) di ciascuna terapia
SELECT
    ID_terapia,
    CF_paziente,
    Data_inizio,
    Data_fine,
    (julianday(Data_fine) - julianday(Data_inizio)) AS Durata_giorni
FROM Terapia
WHERE Data_fine IS NOT NULL;


/* -------- 3.4 Operatori aggregati: DISTINCT, COUNT, SUM, AVG, MIN, MAX -------- */

-- DISTINCT: mostra solo valori diversi (non duplicati)
SELECT DISTINCT Citta
FROM Paziente;

-- COUNT: numero di righe che soddisfano la condizione
SELECT COUNT(*) AS Numero_pazienti
FROM Paziente;

-- COUNT di un attributo (ignora i NULL)
SELECT COUNT(CodiceEsame) AS Pazienti_con_cartella
FROM Paziente;

-- SUM, AVG, MIN, MAX – esempio su una colonna numerica
-- (qui facciamo un esempio artificiale con la durata delle terapie)
SELECT
    SUM(julianday(Data_fine) - julianday(Data_inizio)) AS Somma_giorni,
    AVG(julianday(Data_fine) - julianday(Data_inizio)) AS Media_giorni,
    MIN(julianday(Data_fine) - julianday(Data_inizio)) AS Min_giorni,
    MAX(julianday(Data_fine) - julianday(Data_inizio)) AS Max_giorni
FROM Terapia
WHERE Data_fine IS NOT NULL;


/* -------- 3.5 GROUP BY e HAVING --------

   GROUP BY raggruppa le righe in base a uno o più attributi.

   HAVING permette di fare condizioni su operatori aggregati,
   un po' come WHERE ma dopo il raggruppamento.
*/

-- Quante terapie ha avuto ogni paziente?
SELECT
    CF_paziente,
    COUNT(*) AS Numero_terapie
FROM Terapia
GROUP BY CF_paziente;

-- Mostra SOLO i pazienti che hanno avuto più di 1 terapia
SELECT
    CF_paziente,
    COUNT(*) AS Numero_terapie
FROM Terapia
GROUP BY CF_paziente
HAVING COUNT(*) > 1;


/* -------- 3.6 Operatore LIKE e pattern --------

   %  = qualunque numero (anche zero) di caratteri
   _  = esattamente UN carattere

   WHERE colonna LIKE 'pattern'

   Esempio dato: 
      SELECT *
      FROM terapia
      WHERE PrincipioAttivo LIKE '%o';

   (Nel nostro schema PrincipioAttivo è nella tabella Farmaci,
    quindi facciamo un esempio corretto con JOIN)
*/

-- Tutti i farmaci il cui principio attivo finisce in 'ina'
SELECT *
FROM Farmaci
WHERE PrincipioAttivo LIKE '%ina';

-- Tutti i pazienti il cui cognome inizia con 'N'
SELECT *
FROM Paziente
WHERE Cognome LIKE 'N%';

-- Cognomi che hanno esattamente 7 lettere e iniziano per 'N'
SELECT *
FROM Paziente
WHERE Cognome LIKE 'N______';  -- N + 6 underscore = 7 caratteri totali


/* =====================================================================
   4. ESEMPI DI QUERY PER LE DOMANDE 1.1–1.5
   ===================================================================== */

/* 1.1 Conoscere cognome e nome dei pazienti maschi considerati
       nella base di dati;
*/
SELECT Cognome, Nome
FROM Paziente
WHERE Sesso = 'M';

/* 1.2 Conoscere tutti i dati relativi ai pazienti il cui cognome è
       “Nunziatella”;
*/
SELECT *
FROM Paziente
WHERE Cognome = 'Nunziatella';

/* 1.3 Conoscere cognome, nome, anno di nascita dei pazienti considerati,
       in ordine di cognome.
   Usiamo strftime('%Y', Data_Nascita) per estrarre l’anno come testo.
*/
SELECT
    Cognome,
    Nome,
    strftime('%Y', Data_Nascita) AS Anno_Nascita
FROM Paziente
ORDER BY Cognome ASC, Nome ASC;

/* 1.4 Conoscere cognome, nome e età dei pazienti
   (usiamo julianday per calcolare un’età approssimata)
*/
SELECT
    Cognome,
    Nome,
    CAST((julianday('now') - julianday(Data_Nascita)) / 365.25 AS INTEGER) AS Eta
FROM Paziente
ORDER BY Cognome, Nome;

/* 1.5 Conoscere principio attivo, ID paziente (CF) e data inizio delle
       terapie finite durante il 2019

   "finite durante il 2019" -> Data_fine compresa tra 2019-01-01 e 2019-12-31.
*/
SELECT
    f.PrincipioAttivo,
    t.CF_paziente AS ID_paziente,
    t.Data_inizio
FROM Terapia t
JOIN Farmaci f ON t.CodiceFarmaco = f.CodiceFarmaco
WHERE t.Data_fine BETWEEN '2019-01-01' AND '2019-12-31'
ORDER BY t.Data_inizio;


/* =====================================================================
   5. JOIN (INNER, LEFT, "RIGHT") – DEFINIZIONI E ESEMPI
   ===================================================================== */

/*
   INNER JOIN:
       restituisce solo le righe che hanno corrispondenza in entrambe le tabelle.

   LEFT JOIN:
       restituisce tutte le righe della tabella a sinistra, e solo le corrispondenti
       della tabella a destra (se non c’è corrispondenza, i campi della destra sono NULL).

   RIGHT JOIN:
       SQLite NON supporta RIGHT JOIN direttamente.
       Si può simulare con LEFT JOIN invertendo l'ordine delle tabelle.
*/

/* ---- 2.4 Conoscere tutte le terapie associate ad ogni paziente ----
   Usiamo un LEFT JOIN per vedere anche pazienti che NON hanno terapie (se presenti).
*/

SELECT
    p.CF,
    p.Cognome,
    p.Nome,
    t.ID_terapia,
    t.Data_inizio,
    t.Data_fine
FROM Paziente p
LEFT JOIN Terapia t
    ON p.CF = t.CF_paziente
ORDER BY p.Cognome, p.Nome, t.Data_inizio;


/* ---- Esempio di "RIGHT JOIN" simulato ----
   "Tutte le terapie con (eventuale) paziente" (di fatto è un INNER JOIN
   perché il vincolo di chiave esterna impone che il paziente esista).
*/

SELECT
    t.ID_terapia,
    t.Data_inizio,
    t.Data_fine,
    p.CF,
    p.Cognome,
    p.Nome
FROM Terapia t
LEFT JOIN Paziente p
    ON t.CF_paziente = p.CF;


/* ---- 2.5 Conoscere cognome e nome dei pazienti trattati con warfarin ----
   JOIN tra Paziente, Terapia, Farmaci
*/

SELECT DISTINCT
    p.Cognome,
    p.Nome
FROM Paziente p
JOIN Terapia t
    ON p.CF = t.CF_paziente
JOIN Farmaci f
    ON t.CodiceFarmaco = f.CodiceFarmaco
WHERE f.PrincipioAttivo = 'warfarin'
ORDER BY p.Cognome, p.Nome;

/* DISTINCT evita di ripetere lo stesso paziente se ha avuto più
   di una terapia con warfarin.
*/


/* ---- 2.6 Conoscere cognome, nome, data di nascita, terapia per quei
        pazienti la cui terapia è finita prima dell’anno 2015. ----

   "prima dell’anno 2015" -> Data_fine < '2015-01-01'
*/

SELECT
    p.Cognome,
    p.Nome,
    p.Data_Nascita,
    f.PrincipioAttivo AS Terapia_PrincipioAttivo,
    t.Data_inizio,
    t.Data_fine
FROM Paziente p
JOIN Terapia t
    ON p.CF = t.CF_paziente
JOIN Farmaci f
    ON t.CodiceFarmaco = f.CodiceFarmaco
WHERE t.Data_fine < '2015-01-01'
ORDER BY p.Cognome, p.Nome, t.Data_fine;


/* ---- Esempio aggiuntivo: pazienti SENZA alcuna terapia ---- */

SELECT
    p.CF,
    p.Cognome,
    p.Nome
FROM Paziente p
LEFT JOIN Terapia t
    ON p.CF = t.CF_paziente
WHERE t.ID_terapia IS NULL;


/* =====================================================================
   FINE DEL FILE DI ESERCIZI
   ===================================================================== */