Progetto-PHP/SQL/01_schema.sql

-- 01_schema.sql

-- CREAZIONE RELAZIONE UTENTE
CREATE TABLE utente (
    idUtente INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
    nome VARCHAR(30) NOT NULL,
    cognome VARCHAR(30) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    passwordHash VARCHAR(255) NOT NULL,
    attivo TINYINT(1) NOT NULL
);

-- CREAZIONE RELAZIONE PROPRIETARIO
CREATE TABLE proprietario (
    idUtente INT PRIMARY KEY NOT NULL,

    FOREIGN KEY (idUtente) REFERENCES utente (idUtente)
);

-- CREAZIONE RELAZIONE PERSONALE
CREATE TABLE personale (
    idUtente INT PRIMARY KEY NOT NULL,
    dataRegistrazione TIMESTAMP NOT NULL,

    FOREIGN KEY (idUtente) REFERENCES utente (idUtente)
);

-- CREAZIONE RELAZIONE CLIENTE
CREATE TABLE cliente (
    idUtente INT PRIMARY KEY NOT NULL,
    telefono VARCHAR(20) NOT NULL,

    FOREIGN KEY (idUtente) REFERENCES utente (idUtente)
);

-- CREAZIONE RELAZIONE PROVINCIA
CREATE TABLE provincia (
    idProvincia INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    nome VARCHAR(45) NOT NULL,
    sigla VARCHAR(45) NOT NULL
);

-- CREAZIONE RELAZIONE COMUNE
CREATE TABLE comune (
    idComune INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    nome VARCHAR(45) NOT NULL,
    idProvincia INT NOT NULL,

    FOREIGN KEY (idProvincia) REFERENCES provincia(idProvincia)
);

-- CREAZIONE RELAZIONE INDIRIZZO
CREATE TABLE indirizzo (
    idIndirizzo INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    via VARCHAR(45) NOT NULL,
    numeroCivico VARCHAR (10) NOT NULL,
    predefinito TINYINT(1) NOT NULL DEFAULT 0,
    idComune INT NOT NULL,
    idCliente INT NOT NULL,

    FOREIGN KEY (idComune) REFERENCES comune (idComune),
    FOREIGN KEY (idCliente) REFERENCES cliente (idUtente)
);

-- CREAZIONE RELAZIONE ORDINE
CREATE TABLE ordine (
    idOrdine INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    dataOraCreazione TIMESTAMP NOT NULL,
    dataOraConferma TIMESTAMP,
    dataOraAnnullamento TIMESTAMP,
    statoConferma ENUM('bozza','confermato','annullato') NOT NULL,
    orarioConsegna TIMESTAMP,
    totale DECIMAL(10,2) NOT NULL,
    tempoStimatoConsegna INT,
    idCliente INT NOT NULL,
    idIndirizzo INT NOT NULL,

    FOREIGN KEY (idCliente) REFERENCES cliente (idUtente),
    FOREIGN KEY (idIndirizzo) REFERENCES indirizzo (idIndirizzo),

    CHECK (dataOraCreazione <= dataOraConferma),
    CHECK (dataOraConferma <= orarioConsegna),
    CHECK (totale >= 0),
    CHECK (tempoStimatoConsegna >= 0),
    CHECK (statoConferma <> 'confermato' OR dataOraConferma IS NOT NULL)        -- se è confermato → dataOraConferma non può essere NULL
);

-- CREAZIONE RELAZIONE STATO ORDINE 
CREATE TABLE statoOrdine (
    idStatoOrdine INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    nomeStato ENUM(
        'inserito',
        'in preparazione',
        'pronto',
        'in consegna',
        'consegnato'
    ),
    posizione TINYINT NOT NULL
);

-- CREAZIONE RELAZIONE STORICO STATO
CREATE TABLE storicoStato (
    idStoricoStato INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    timestamp TIMESTAMP NOT NULL,
    idOrdine INT NOT NULL,
    idStatoOrdine INT NOT NULL,
    idPersonale INT,

    FOREIGN KEY (idOrdine) REFERENCES ordine (idOrdine),
    FOREIGN KEY (idStatoOrdine) REFERENCES statoOrdine (idStatoOrdine),
    FOREIGN KEY (idPersonale) REFERENCES personale (idUtente)
);

-- CREAZIONE RELAZIONE INGREDIENTE
CREATE TABLE ingrediente (
    idIngrediente INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    nome VARCHAR(100) NOT NULL,
    unitaMisura VARCHAR(20) NOT NULL
);

-- CREAZIONE RELAZIONE PRODOTTO
CREATE TABLE prodotto (
    idProdotto INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    nome VARCHAR(100) NOT NULL,
    descrizione TEXT NOT NULL,
    prezzoBase DECIMAL(10,2) NOT NULL,
    tempoPreparazione INT NOT NULL,
    proceduraPreparazione TEXT NOT NULL,
    disponibile TINYINT(1) NOT NULL DEFAULT 0,

    CHECK (prezzoBase >= 0),
    CHECK (tempoPreparazione >= 0)
);

-- CREAZIONE RELAZIONE PRODOTTOUSAINGREDIENTE
CREATE TABLE prodottoUsaIngrediente (
    quantita DECIMAL(10,3) NOT NULL,
    idProdotto INT NOT NULL,
    idIngrediente INT NOT NULL,

    PRIMARY KEY (idProdotto, idIngrediente),

    FOREIGN KEY (idProdotto) REFERENCES prodotto (idProdotto),
    FOREIGN KEY (idIngrediente) REFERENCES ingrediente (idIngrediente),

    CHECK (quantita > 0)
);

-- CREAZIONE RELAZIONE GRUPPO ECLUSIONE
CREATE TABLE gruppoEsclusione (
    idGruppoEsclusione INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    nome VARCHAR(45) NOT NULL,
    idProdotto INT NOT NULL,

    FOREIGN KEY (idProdotto) REFERENCES prodotto (idProdotto)
);

-- CREAZIONE RELAZIONE CARATTERISTICA 
CREATE TABLE caratteristica (
    idCaratteristica INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    nome VARCHAR(100) NOT NULL,
    descrizione TEXT NOT NULL,
    differenzaPrezzo DECIMAL(10,2) NOT NULL,
    predefinita TINYINT(1) DEFAULT 0 NOT NULL,
    disponibile TINYINT(1) DEFAULT 0 NOT NULL,
    idGruppoEsclusione INT,     -- Una caratteristica può appartenere o no ad un gruppo di eclusione
    idProdotto INT NOT NULL,

    FOREIGN KEY (idGruppoEsclusione) REFERENCES gruppoEsclusione (idGruppoEsclusione),
    FOREIGN KEY (idProdotto) REFERENCES prodotto (idProdotto),

    CHECK (differenzaPrezzo >= 0)
);

-- CREAZIONE RELAZIONE RIGAORDINE
CREATE TABLE rigaOrdine (
    idRigaOrdine INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    quantita TINYINT NOT NULL,
    prezzoBaseApplicato DECIMAL(10,2) NOT NULL,
    idOrdine INT NOT NULL,
    idProdotto INT NOT NULL,

    FOREIGN KEY (idOrdine) REFERENCES ordine (idOrdine),
    FOREIGN KEY (idProdotto) REFERENCES prodotto (idProdotto),

    CHECK (quantita > 0),
    CHECK (prezzoBaseApplicato >= 0)
);

-- CREAZIONE RELAZIONE RIGAORDINESELEZIONACARATTERISTICA
CREATE TABLE rigaOrdineSelezionaCaratteristica (
    idCaratteristica INT NOT NULL,
    idRigaOrdine INT NOT NULL,
    differenzaPrezzoApplicata DECIMAL(10,2) NOT NULL,

    PRIMARY KEY (idCaratteristica, idRigaOrdine),

    FOREIGN KEY (idCaratteristica) REFERENCES caratteristica (idCaratteristica),
    FOREIGN KEY (idRigaOrdine) REFERENCES rigaOrdine (idRigaOrdine)
);

-- CREAZIONE VISTE (RECUPERO ULTIMO STATO ORDINE)
CREATE VIEW ultimoStatoOrdini AS (
    SELECT 
        ss1.idOrdine,
        ss1.idStatoOrdine,
        so1.nomeStato,
        so1.posizione
    
    FROM 
        storicoStato AS ss1

        JOIN statoOrdine AS so1
        ON ss1.idStatoOrdine = so1.idStatoOrdine
    
    WHERE 
        so1.posizione = (
            SELECT 
                MAX(so2.posizione)
            
            FROM
                storicoStato AS ss2

                JOIN statoOrdine AS so2
                ON ss2.idStatoOrdine = so2.idStatoOrdine
            
            WHERE 
                ss2.idOrdine = ss1.idOrdine
        )
); 

-- CREAZIONE VISTE (RECUPERO STATO COMPLETO ORDINE)
CREATE VIEW statoOrdini AS (
    SELECT 
        ss1.idOrdine,
        ss1.idStatoOrdine,
        ss1.idPersonale,
        u1.nome AS nomePersonale,
        u1.cognome AS cognomePersonale,
        ss1.timestamp,
        so1.nomeStato

    FROM storicoStato AS ss1

    JOIN statoOrdine AS so1
        ON ss1.idStatoOrdine = so1.idStatoOrdine

    LEFT JOIN personale AS p1
        ON ss1.idPersonale = p1.idUtente

    LEFT JOIN utente AS u1
        ON p1.idUtente = u1.idUtente

    WHERE
        1

    ORDER BY
        ss1.timestamp
);