
-- Schema: Esercizio Gallerie d'Arte (MySQL 8.x, patch 2)
-- Autore: ChatGPT (OpenAI) - 21/04/2025
-- Modifica: la colonna 'data' in recensione ora è DATETIME DEFAULT CURRENT_TIMESTAMP
--           (DEFAULT CURRENT_DATE non è supportato su MySQL < 8.0.13)

SET NAMES utf8mb4;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;

DROP TABLE IF EXISTS recensione;
DROP TABLE IF EXISTS prenotazione;
DROP TABLE IF EXISTS prestito;
DROP TABLE IF EXISTS sala;
DROP TABLE IF EXISTS opera;
DROP TABLE IF EXISTS visitatore;
DROP TABLE IF EXISTS galleria;

SET foreign_key_checks = 1;

CREATE TABLE galleria (
    id_galleria INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nome        VARCHAR(100) NOT NULL,
    citta       VARCHAR(100) NOT NULL,
    indirizzo   VARCHAR(255) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE sala (
    id_sala     INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    id_galleria INT UNSIGNED NOT NULL,
    nome        VARCHAR(100) NOT NULL,
    piano       INT,
    CONSTRAINT fk_sala_galleria
        FOREIGN KEY (id_galleria) REFERENCES galleria(id_galleria)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    UNIQUE KEY uq_sala_galleria_nome (id_galleria, nome)
) ENGINE=InnoDB;

CREATE TABLE visitatore (
    codice_fiscale CHAR(16) PRIMARY KEY,
    nome           VARCHAR(100) NOT NULL,
    cognome        VARCHAR(100) NOT NULL,
    data_nascita   DATE,
    citta          VARCHAR(100)
) ENGINE=InnoDB;

CREATE TABLE opera (
    codice_opera INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    titolo       VARCHAR(200) NOT NULL,
    artista      VARCHAR(150),
    tipo         VARCHAR(60),
    anno         INT,
    dimensioni   VARCHAR(60)
) ENGINE=InnoDB;

CREATE TABLE prestito (
    id_prestito        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    codice_opera       INT UNSIGNED NOT NULL,
    codice_visitatore  CHAR(16) NOT NULL,
    id_galleria        INT UNSIGNED NOT NULL,
    data_inizio        DATE NOT NULL,
    data_fine_prevista DATE NOT NULL,
    data_restituzione  DATE,
    CONSTRAINT fk_prestito_opera
        FOREIGN KEY (codice_opera) REFERENCES opera(codice_opera)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT fk_prestito_visitatore
        FOREIGN KEY (codice_visitatore) REFERENCES visitatore(codice_fiscale)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT fk_prestito_galleria
        FOREIGN KEY (id_galleria) REFERENCES galleria(id_galleria)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE prenotazione (
    id_prenotazione   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    codice_opera      INT UNSIGNED NOT NULL,
    codice_visitatore CHAR(16) NOT NULL,
    id_galleria       INT UNSIGNED NOT NULL,
    data_inizio       DATE NOT NULL,
    data_fine         DATE,
    stato             ENUM('attiva','completata','annullata') NOT NULL DEFAULT 'attiva',
    CONSTRAINT fk_prenotazione_opera
        FOREIGN KEY (codice_opera) REFERENCES opera(codice_opera)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT fk_prenotazione_visitatore
        FOREIGN KEY (codice_visitatore) REFERENCES visitatore(codice_fiscale)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT fk_prenotazione_galleria
        FOREIGN KEY (id_galleria) REFERENCES galleria(id_galleria)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE recensione (
    id_recensione     INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    id_galleria       INT UNSIGNED NOT NULL,
    codice_visitatore CHAR(16) NOT NULL,
    testo             TEXT,
    voto              TINYINT UNSIGNED,
    data              DATETIME DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_recensione_voto CHECK (voto BETWEEN 1 AND 5),
    CONSTRAINT fk_recensione_galleria
        FOREIGN KEY (id_galleria) REFERENCES galleria(id_galleria)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT fk_recensione_visitatore
        FOREIGN KEY (codice_visitatore) REFERENCES visitatore(codice_fiscale)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB;

-- indici e dati di esempio identici
INSERT INTO galleria (nome, citta, indirizzo) VALUES
('Galleria degli Uffizi',         'Firenze', 'Piazzale degli Uffizi, 6'),
('Galleria Borghese',             'Roma',    'Piazzale Scipione Borghese, 5'),
('Collezione Peggy Guggenheim',   'Venezia', 'Dorsoduro, 701');

INSERT INTO sala (id_galleria, nome, piano) VALUES
(1, 'Sala Botticelli', 1),
(1, 'Sala Leonardo',   2),
(2, 'Ala Caravaggio',  1),
(3, 'Giardino',        0);

INSERT INTO visitatore (codice_fiscale, nome, cognome, data_nascita, citta) VALUES
('RSSMRA85A01H501X', 'Mario',  'Rossi',   '1985-01-01', 'Firenze'),
('VRDLGI90C11F205Z', 'Giulia', 'Verdi',   '1990-03-11', 'Roma'),
('BNCCRL75L22H501P', 'Carlo',  'Bianchi', '1975-07-22', 'Milano');

INSERT INTO opera (titolo, artista, tipo, anno, dimensioni) VALUES
('La nascita di Venere', 'Botticelli',  'Impressionismo', 1486, '172cm x 278cm'),
('David',                'Michelangelo','Scultura',       1504, '517cm x 199cm'),
('Il bacio',             'Hayez',       'Romanticismo',   1859, '112cm x 88cm');

INSERT INTO prestito (codice_opera, codice_visitatore, id_galleria, data_inizio, data_fine_prevista, data_restituzione) VALUES
(2, 'RSSMRA85A01H501X', 2, '2025-03-01', '2025-04-01', NULL),
(1, 'VRDLGI90C11F205Z', 1, '2025-02-15', '2025-03-15', '2025-03-14');

INSERT INTO prenotazione (codice_opera, codice_visitatore, id_galleria, data_inizio, data_fine, stato) VALUES
(3, 'BNCCRL75L22H501P', 3, '2025-04-10', '2025-04-12', 'attiva'),
(1, 'RSSMRA85A01H501X', 1, '2025-04-05', '2025-04-07', 'completata');

INSERT INTO recensione (id_galleria, codice_visitatore, testo, voto, data) VALUES
(1, 'VRDLGI90C11F205Z', 'Esperienza meravigliosa!', 5, '2025-03-20 12:00:00'),
(2, 'BNCCRL75L22H501P', 'Troppa gente, poco spazio.', 1, '2025-03-25 10:30:00');
