CREATE TABLE Sede(
    ID_Sede INT PRIMARY KEY,
    Nome CHAR(30) NOT NULL UNIQUE,
    Via CHAR(30) NOT NULL,
    Numero_Civico SMALLINT UNSIGNED NOT NULL,
    Citta CHAR(30) NOT NULL,
    CAP INT UNSIGNED NOT NULL, /*o mediumint*/
    Ora_Apertura TIME NOT NULL, /* '00:00:00' */
    Ora_Chiusura TIME NOT NULL  /* 'HH:MM:SS' */
);


CREATE TABLE Sala(
    ID_Sala INT PRIMARY KEY,
    Num_Sala INT UNSIGNED NOT NULL,
    Sede INT NOT NULL,
    FOREIGN KEY(Sede)
        REFERENCES Sede(ID_Sede)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);


CREATE TABLE Magazzino(
    ID_Magazzino INT PRIMARY KEY,
    Sede INT NOT NULL,
    FOREIGN KEY(Sede)
        REFERENCES Sede(ID_Sede)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);


CREATE TABLE Ingrediente(
    ID_Ingrediente INT PRIMARY KEY,
    Nome CHAR(30) NOT NULL UNIQUE,
    Provenienza CHAR(30) NOT NULL,
    Genere CHAR(15) NOT NULL,
    Tipo_Produzione CHAR(15), /* intensiva, biologica, altro */
    Allergene BOOLEAN NOT NULL /* no allergene = false; è allergene = true */
);


CREATE TABLE Confezione(
    ID_Confezione INT PRIMARY KEY,
    Codice_Lotto CHAR(20) NOT NULL,
    Quantita INT UNSIGNED NOT NULL, /*peso in grammi*/
    Quantita_Iniziale INT UNSIGNED NOT NULL,
    Prezzo_Acquisto FLOAT UNSIGNED NOT NULL,
    Data_Scadenza DATE,
    Aspetto ENUM('buono', 'esteticamente rovinato', 'completamente rovinato') NOT NULL,
    Stato ENUM('completa', 'in uso', 'parziale') NOT NULL DEFAULT 'completa',
    Data_Acquisto DATE NOT NULL,
    Data_Arrivo DATE NOT NULL,
    Ind_Collocamento_Mag CHAR(5) NOT NULL,
    Magazzino INT NOT NULL,
    Ingrediente INT NOT NULL,
    FOREIGN KEY(Magazzino)
        REFERENCES Magazzino(ID_Magazzino)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    FOREIGN KEY(Ingrediente)
        REFERENCES Ingrediente(ID_Ingrediente)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);


CREATE TABLE Menu(
    ID_Menu INT PRIMARY KEY,
    Data_Entrata_Vigore DATE NOT NULL, /* 'YYYY-MM-DD' */
    Data_Cessazione DATE,
    Sede INT NOT NULL,
    FOREIGN KEY(Sede)
        REFERENCES Sede(ID_Sede)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);


CREATE TABLE Ricetta(
    ID_Ricetta INT PRIMARY KEY,
    Nome CHAR(50) NOT NULL UNIQUE, /*nomi lunghi delle ricette*/
    Portata ENUM('Antipasto' , 'Primo', 'Secondo', 'Contorno', 'Dessert') NOT NULL
);


CREATE TABLE Piatto_Nel_Menu (
    Menu INT NOT NULL,
    Ricetta INT NOT NULL,
    Prezzo_Al_Pubblico FLOAT UNSIGNED NOT NULL,
    Prezzo_Di_Produzione FLOAT UNSIGNED,
    Novita DATE,
    FOREIGN KEY(Menu)
        REFERENCES Menu(ID_Menu)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    FOREIGN KEY(Ricetta)
        REFERENCES Ricetta(ID_Ricetta)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    PRIMARY KEY(Menu, Ricetta)
);/*Associazione tra Menu e Ricetta*/


CREATE TABLE Macchinario(
    ID_Macchinario INT PRIMARY KEY,
    Nome CHAR(30) NOT NULL UNIQUE
);


CREATE TABLE Macchinario_Nella_Sede(
    ID_Mac_Sede INT PRIMARY KEY,
    In_Funzione BOOLEAN NOT NULL DEFAULT 0, /* false = non in uso, true = in uso */
    Macchinario INT NOT NULL,
    Sede INT NOT NULL,
    FOREIGN KEY(Macchinario)
        REFERENCES Macchinario(ID_Macchinario)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    FOREIGN KEY(Sede)
        REFERENCES Sede(ID_Sede)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);


CREATE TABLE Funzione_Del_Macchinario (
    ID_Funzione INT PRIMARY KEY,
    Funzione CHAR(50) NOT NULL
);


CREATE TABLE Specifica_Funzione(
    Funzione INT NOT NULL,
    Macchinario INT NOT NULL,
    FOREIGN KEY(Funzione)
        REFERENCES Funzione_Del_Macchinario(ID_Funzione)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    FOREIGN KEY(Macchinario)
        REFERENCES Macchinario(ID_Macchinario)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    PRIMARY KEY(Funzione, Macchinario)
);/*Associazione tra Macchinario e Funzione_Del_Macchinario*/


CREATE TABLE Fase (
    Numero_Passaggio INT NOT NULL,
    Dose SMALLINT UNSIGNED,
    Tempo INT UNSIGNED,
    Utilizzo_Estetico BOOLEAN, /*se true ha utilizzo estetico*/
    Manovre CHAR(100) NOT NULL,
    Ricetta INT NOT NULL,
    Ingrediente_Usato INT,
    Macchinario INT,
    FOREIGN KEY(Ricetta)
        REFERENCES Ricetta(ID_Ricetta)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    FOREIGN KEY(Ingrediente_Usato)
        REFERENCES Ingrediente(ID_Ingrediente)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    FOREIGN KEY(Macchinario)
        REFERENCES Macchinario(ID_Macchinario)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    PRIMARY KEY(Numero_Passaggio, Ricetta)
);


CREATE TABLE Variazione(        /* se elimini in nuova_manovra scrivi "uccidi questo" o "non agg quello"*/
    ID_Variazione INT PRIMARY KEY, /*se aggiungi o altro attributo tipo Aggiungi_Dopo oppure utilizza la FK di fase*/
    Nuova_Dose SMALLINT UNSIGNED, /*per la modifica semplicemente FK con fase da sostituire e altri attributi da cambiare, riscrivi anche ing mac e dose se sono uguali*/
    Nuova_Manovra CHAR(100) NOT NULL,
    Nuovo_Tempo SMALLINT UNSIGNED,
    Tipo_Variazione ENUM('modifica', 'elimina', 'aggiungi dopo') NOT NULL, /*tipo variazione*/
    Utilizzo_Estetico BOOLEAN, /*se true ha utilizzo estetico*/
    Ricetta INT NOT NULL,
    Fase INT NOT NULL,
    Macchinario INT,
    Ingrediente INT,
    FOREIGN KEY(Ricetta)
        REFERENCES Ricetta(ID_Ricetta)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    FOREIGN KEY(Fase)
        REFERENCES Fase(Numero_Passaggio)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    FOREIGN KEY(Macchinario)
        REFERENCES Macchinario(ID_Macchinario)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    FOREIGN KEY(Ingrediente)
        REFERENCES Ingrediente(ID_Ingrediente)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);


CREATE TABLE Tavolo(
    ID_Tavolo INT PRIMARY KEY,
    Num_Tavolo INT UNSIGNED NOT NULL,
    Stato BOOLEAN NOT NULL DEFAULT 0,
    Posti SMALLINT UNSIGNED NOT NULL,
    Sala INT NOT NULL,
    FOREIGN KEY (Sala)
        REFERENCES Sala(ID_Sala)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);


CREATE TABLE Pony(
    ID_Pony INT PRIMARY KEY,
    Tipologia BOOLEAN NOT NULL, /* false = 2 ruote; true = 4 ruote */
    Stato BOOLEAN NOT NULL DEFAULT 0,
    Sede INT NOT NULL,
    FOREIGN KEY(Sede)
        REFERENCES Sede(ID_Sede)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);


CREATE TABLE Comanda (
    ID_Comanda INT PRIMARY KEY,
    Stato ENUM('nuova', 'parziale', 'in preparazione', 'evasa', 'consegna') NOT NULL DEFAULT 'nuova',
    Time_Stamp DATETIME,
    Partenza_Takeaway DATETIME,
    Consegna_Takeaway DATETIME,
    Rientro_Takeaway DATETIME,
    Tavolo INT,
    Pony INT,
    Sede INT NOT NULL,
    FOREIGN KEY(Tavolo)
        REFERENCES Tavolo(ID_Tavolo)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    FOREIGN KEY(Pony)
        REFERENCES Pony(ID_Pony)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    FOREIGN KEY(Sede)
        REFERENCES Sede(ID_Sede)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);


CREATE TABLE Piatto_Ordinato(
    ID_Piatto INT PRIMARY KEY,
    Stato ENUM('attesa', 'in preparazione', 'servizio') NOT NULL DEFAULT 'attesa',
    Ricetta INT NOT NULL,
    Comanda INT NOT NULL,
    FOREIGN KEY(Ricetta)
        REFERENCES Ricetta(ID_Ricetta)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    FOREIGN KEY(Comanda)
        REFERENCES Comanda(ID_Comanda)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);


CREATE TABLE Account (
    ID_Account INT PRIMARY KEY,
    Username CHAR(20) NOT NULL UNIQUE,
    Password CHAR(20) NOT NULL,
    Nome CHAR(20) NOT NULL,
    Cognome CHAR(20) NOT NULL,
    Data_Nascita DATE NOT NULL,
    Sesso BOOLEAN, /* false = femmina, true = maschio */
    Via CHAR(20),
    Numero_Civico SMALLINT UNSIGNED,
    Citta CHAR(30),
    CAP INT UNSIGNED,
    Bannato BOOLEAN NOT NULL DEFAULT 0
);


CREATE TABLE Questionario(
    ID_Questionario INT PRIMARY KEY,
    Attivo BOOLEAN NOT NULL, /*true se la sede sta utilizzando questo questionario*/
    Sede INT NOT NULL,
    FOREIGN KEY(Sede)
        REFERENCES Sede(ID_Sede)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);


CREATE TABLE Recensione(
    ID_Recensione INT PRIMARY KEY,
    Testo CHAR(255) NOT NULL,
    Voto TINYINT UNSIGNED NOT NULL,
    Account INT NOT NULL,
    Questionario INT NOT NULL,
    FOREIGN KEY(Account)
        REFERENCES Account(ID_Account)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    FOREIGN KEY(Questionario)
        REFERENCES Questionario(ID_Questionario)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);


CREATE TABLE Account_Giudica_Recensione(
    Voto_Veridicita SMALLINT UNSIGNED NOT NULL,
    Voto_Accuratezza SMALLINT UNSIGNED NOT NULL,
    Testo CHAR(100),
    Account INT NOT NULL,
    Recensione INT NOT NULL,
    FOREIGN KEY(Account)
        REFERENCES Account(ID_Account)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    FOREIGN KEY(Recensione)
        REFERENCES Recensione(ID_Recensione)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    PRIMARY KEY(Account, Recensione)
); /*Associazione tra Account e Recensione*/


CREATE TABLE Domanda_Questionario(
    ID_Domanda INT PRIMARY KEY,
    Testo CHAR(100) NOT NULL,
    Questionario INT NOT NULL,
    FOREIGN KEY(Questionario)
        REFERENCES Questionario(ID_Questionario)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);


CREATE TABLE Risposta_Questionario(
    ID_Risposta INT PRIMARY KEY,
    Testo CHAR(100) NOT NULL,
    Domanda INT NOT NULL,
    FOREIGN KEY(Domanda)
        REFERENCES Domanda_Questionario(ID_Domanda)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);


CREATE TABLE Serata_A_Tema (
    ID_Tema INT PRIMARY KEY,
    Tema CHAR(20) NOT NULL UNIQUE,
    Disposizione_Tavoli CHAR(30) NOT NULL,
    Decorazioni CHAR(150) NOT NULL
);


CREATE TABLE Prenotazione (
    ID_Prenotazione INT PRIMARY KEY,
    Num_Persone SMALLINT UNSIGNED NOT NULL,
    Data_Prenotazione DATETIME NOT NULL,
    Nominativo CHAR(20) NOT NULL,
    Num_Telefono BIGINT UNSIGNED,
    Account INT,
    Serata INT DEFAULT NULL,
    FOREIGN KEY(Serata)
        REFERENCES Serata_A_Tema(ID_Tema)
        ON UPDATE CASCADE
        ON DELETE RESTRICT, /*o no action (uguale a restrict)*/
    FOREIGN KEY(Account)
        REFERENCES Account(ID_Account)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
);


CREATE TABLE Prenota_Tavolo(
    Prenotazione INT NOT NULL,
    Tavolo INT NOT NULL,
    FOREIGN KEY(Prenotazione)
        REFERENCES Prenotazione(ID_Prenotazione)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    FOREIGN KEY(Tavolo)
        REFERENCES Tavolo(ID_Tavolo)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    PRIMARY KEY(Prenotazione, Tavolo)
); /*Associazione tra Prenotazione e Tavolo*/


CREATE TABLE Risposta_Data(
    Recensione INT NOT NULL,
    Risposta INT NOT NULL,
    FOREIGN KEY(Recensione)
        REFERENCES Recensione(ID_Recensione)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    FOREIGN KEY(Risposta)
        REFERENCES Risposta_Questionario(ID_Risposta)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    PRIMARY KEY(Recensione, Risposta)
); /*Associazione tra Risposta_Questionario e Recensione*/


CREATE TABLE Variazione_Al_Piatto(
    Piatto INT NOT NULL,
    Variazione INT NOT NULL,
    FOREIGN KEY(Piatto)
        REFERENCES Piatto_Ordinato(ID_Piatto)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    FOREIGN KEY(Variazione)
        REFERENCES Variazione(ID_Variazione)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    PRIMARY KEY(Piatto, Variazione)
); /*Associazione tra Variazione e Piatto Ordinato*/


CREATE TABLE Proposta(
    ID_Proposta INT PRIMARY KEY,
    Nome_Piatto CHAR(30),
    Procedimento TEXT NOT NULL,
    Descrizione CHAR(100) NOT NULL,
    Ingredienti CHAR(255),
    Account INT NOT NULL,
    Ricetta INT,
    FOREIGN KEY(Account)
        REFERENCES Account(ID_Account)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    FOREIGN KEY(Ricetta)
        REFERENCES Ricetta(ID_Ricetta)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);


CREATE TABLE Account_Giudica_Proposta(
    Voto SMALLINT UNSIGNED NOT NULL,
    Account INT NOT NULL,
    Proposta INT NOT NULL,
    FOREIGN KEY(Account)
        REFERENCES Account(ID_Account)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    FOREIGN KEY(Proposta)
        REFERENCES Proposta(ID_Proposta)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    PRIMARY KEY(Account, Proposta)
); /*Associazione tra Account e Proposta*/



/*///////////////////////////////////////////////////////////////////////////

-FK: http://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html

-Specifying RESTRICT (or NO ACTION) is the same as omitting the ON DELETE or ON UPDATE clause.

*/