query.sql

-- *********************************************
-- * Schema SQL Corretto per PL8 Database
-- * Piattaforma di Delivery di Cibo
-- *********************************************

-- Database Section
-- ________________ 

CREATE DATABASE PL8;
USE PL8;

-- Tables Section
-- _____________ 

CREATE TABLE ZONE_GEOGRAFICHE (
    Codice_Zona INTEGER AUTO_INCREMENT NOT NULL,
    Nome VARCHAR(50) NOT NULL,
    CONSTRAINT PK_ZONE_GEOGRAFICHE PRIMARY KEY (Codice_Zona)
);

CREATE TABLE RISTORANTI (
    P_IVA CHAR(11) NOT NULL,
    Nome VARCHAR(100) NOT NULL,
    Indirizzo VARCHAR(200) NOT NULL,
    Orario VARCHAR(100) NOT NULL,
    Codice_Zona INTEGER NOT NULL,
    CONSTRAINT PK_RISTORANTE PRIMARY KEY (P_IVA),
    CONSTRAINT CHK_PIVA CHECK (LENGTH(P_IVA) = 11 AND P_IVA REGEXP '^[0-9]{11}$'),
    CONSTRAINT FK_RISTORANTE_ZONA FOREIGN KEY (Codice_Zona) REFERENCES ZONE_GEOGRAFICHE(Codice_Zona)
);

CREATE TABLE CLIENTI (
    Codice_Cliente INTEGER AUTO_INCREMENT NOT NULL,
    Nome VARCHAR(50) NOT NULL,
    Cognome VARCHAR(50) NOT NULL,
    E_mail VARCHAR(100) NOT NULL,
    Telefono VARCHAR(15) NOT NULL,
    Data_di_Nascita DATE NOT NULL,
    Username VARCHAR(30) NOT NULL UNIQUE,
    CONSTRAINT PK_CLIENTE PRIMARY KEY (Codice_Cliente),
    CONSTRAINT CHK_EMAIL_CLIENTE CHECK (E_mail REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
    CONSTRAINT CHK_TELEFONO_CLIENTE CHECK (Telefono REGEXP '^[0-9+\-\s()]{7,15}$')
);

CREATE TABLE RIDER (
    Codice_Rider INTEGER AUTO_INCREMENT NOT NULL,
    Nome VARCHAR(50) NOT NULL,
    Cognome VARCHAR(50) NOT NULL,
    E_mail VARCHAR(100) NOT NULL,
    Telefono VARCHAR(15) NOT NULL,
    Data_di_Nascita DATE NOT NULL,
    IBAN VARCHAR(34) NOT NULL,
    Codice_Fiscale CHAR(16) NOT NULL UNIQUE,
    Patente BOOLEAN DEFAULT FALSE,
    Disponibile BOOLEAN DEFAULT TRUE,
    Codice_Zona INTEGER NOT NULL,
    CONSTRAINT PK_RIDER PRIMARY KEY (Codice_Rider),
    CONSTRAINT FK_RIDER_ZONA FOREIGN KEY (Codice_Zona) REFERENCES ZONE_GEOGRAFICHE(Codice_Zona),
    CONSTRAINT CHK_EMAIL_RIDER CHECK (E_mail REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
    CONSTRAINT CHK_TELEFONO_RIDER CHECK (Telefono REGEXP '^[0-9+\-\s()]{7,15}$'),
    CONSTRAINT CHK_CF CHECK (LENGTH(Codice_Fiscale) = 16),
    CONSTRAINT CHK_IBAN CHECK (LENGTH(IBAN) >= 15 AND LENGTH(IBAN) <= 34)
);

CREATE TABLE INDIRIZZI (
    Codice_Indirizzo INTEGER AUTO_INCREMENT NOT NULL,
    Via VARCHAR(100) NOT NULL,
    Numero_Civico VARCHAR(10) NOT NULL,
    CAP CHAR(5) NOT NULL,
    Interno INTEGER,
    Scala INTEGER,
    Codice_Zona INTEGER NOT NULL,
    CONSTRAINT PK_INDIRIZZO PRIMARY KEY (Codice_Indirizzo),
    CONSTRAINT FK_INDIRIZZO_ZONA FOREIGN KEY (Codice_Zona) REFERENCES ZONE_GEOGRAFICHE(Codice_Zona),
    CONSTRAINT CHK_CAP CHECK (CAP REGEXP '^[0-9]{5}$')
);

CREATE TABLE PIATTI (
    Codice_Piatto INTEGER AUTO_INCREMENT NOT NULL,
    Nome VARCHAR(100) NOT NULL,
    Prezzo DECIMAL(8,2) NOT NULL,
    Descrizione TEXT,
    CONSTRAINT PK_PIATTO PRIMARY KEY (Codice_Piatto),
    CONSTRAINT CHK_PREZZO_PIATTO CHECK (Prezzo > 0)
);

CREATE TABLE PROMOZIONI (
    P_IVA CHAR(11) NOT NULL,
    Data_Inizio DATE NOT NULL,
    Data_Fine DATE NOT NULL,
    Nome VARCHAR(100) NOT NULL,
    Descrizione TEXT NOT NULL,
    Percentuale_Sconto TINYINT NOT NULL,
    CONSTRAINT PK_PROMOZIONE PRIMARY KEY (P_IVA, Data_Inizio, Data_Fine),
    CONSTRAINT FK_PROMOZIONE_RISTORANTE FOREIGN KEY (P_IVA) REFERENCES RISTORANTI(P_IVA),
    CONSTRAINT CHK_DATE_PROMOZIONE CHECK (Data_Fine > Data_Inizio),
    CONSTRAINT CHK_SCONTO_PROMOZIONE CHECK (Percentuale_Sconto BETWEEN 1 AND 100)
);

CREATE TABLE METODI_PAGAMENTO (
    Codice_Cliente INTEGER NOT NULL,
    Nome VARCHAR(50) NOT NULL,
    CONSTRAINT PK_METODO_PAGAMENTO PRIMARY KEY (Codice_Cliente, Nome),
    CONSTRAINT FK_METODO_PAGAMENTO_CLIENTE FOREIGN KEY (Codice_Cliente) REFERENCES CLIENTI(Codice_Cliente) ON DELETE CASCADE
);

CREATE TABLE CARTE (
    Codice_Cliente INTEGER NOT NULL,
    Nome VARCHAR(50) NOT NULL,
    Numero CHAR(16) NOT NULL,
    Titolare VARCHAR(100) NOT NULL,
    Data_Scadenza DATE NOT NULL,
    CVV CHAR(3) NOT NULL,
    CONSTRAINT PK_CARTA PRIMARY KEY (Codice_Cliente, Nome, Numero),
    CONSTRAINT FK_CARTA_METODO FOREIGN KEY (Codice_Cliente, Nome) REFERENCES METODI_PAGAMENTO(Codice_Cliente, Nome) ON DELETE CASCADE,
    CONSTRAINT CHK_NUMERO_CARTA CHECK (Numero REGEXP '^[0-9]{16}$'),
    CONSTRAINT CHK_CVV CHECK (CVV REGEXP '^[0-9]{3}$')
);

CREATE TABLE RACCOLTE_PUNTI (
    Codice_Cliente INTEGER NOT NULL UNIQUE,
    Punti_Totali INT NOT NULL DEFAULT 0,
    Soglia_Punti INT NOT NULL DEFAULT 100,
    Percentuale_Sconto TINYINT NOT NULL DEFAULT 5,
    CONSTRAINT PK_RACCOLTA_PUNTI PRIMARY KEY (Codice_Cliente),
    CONSTRAINT FK_RACCOLTA_PUNTI_CLIENTE FOREIGN KEY (Codice_Cliente) REFERENCES CLIENTI(Codice_Cliente) ON DELETE CASCADE,
    CONSTRAINT CHK_PUNTI_TOTALI CHECK (Punti_Totali >= 0),
    CONSTRAINT CHK_SOGLIA_PUNTI CHECK (Soglia_Punti > 0),
    CONSTRAINT CHK_SCONTO_RACCOLTA CHECK (Percentuale_Sconto BETWEEN 1 AND 50)
);

CREATE TABLE PAGAMENTI (
    Codice_Pagamento INTEGER AUTO_INCREMENT NOT NULL,
    Data DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    Importo DECIMAL(10,2) NOT NULL,
    Codice_Cliente INTEGER NOT NULL,
    Nome VARCHAR(50) NOT NULL,
    CONSTRAINT PK_PAGAMENTO PRIMARY KEY (Codice_Pagamento),
    CONSTRAINT FK_PAGAMENTO_METODO FOREIGN KEY (Codice_Cliente, Nome) REFERENCES METODI_PAGAMENTO(Codice_Cliente, Nome),
    CONSTRAINT CHK_IMPORTO_PAGAMENTO CHECK (Importo > 0)
);

CREATE TABLE ORDINI (
    Codice_Ordine INTEGER AUTO_INCREMENT NOT NULL,
    Codice_Pagamento INTEGER NOT NULL UNIQUE,
    Prezzo_Totale DECIMAL(10,2) NOT NULL,
    P_IVA CHAR(11) NOT NULL,
    CONSTRAINT PK_ORDINE PRIMARY KEY (Codice_Ordine),
    CONSTRAINT FK_ORDINE_PAGAMENTO FOREIGN KEY (Codice_Pagamento) REFERENCES PAGAMENTI(Codice_Pagamento),
    CONSTRAINT FK_ORDINE_RISTORANTE FOREIGN KEY (P_IVA) REFERENCES RISTORANTI(P_IVA),
    CONSTRAINT CHK_PREZZO_TOTALE CHECK (Prezzo_Totale > 0)
);

CREATE TABLE STATI_ORDINI (
    Codice_Ordine INTEGER NOT NULL,
    Data DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    In_Preparazione BOOLEAN NOT NULL DEFAULT FALSE,
    Ora_In_Preparazione DATETIME,
    In_Consegna BOOLEAN NOT NULL DEFAULT FALSE,
    Ora_In_Consegna DATETIME,
    Consegnato BOOLEAN NOT NULL DEFAULT FALSE,
    Ora_Consegnato DATETIME,
    Codice_Rider INTEGER NULL,
    CONSTRAINT PK_STATO_ORDINE PRIMARY KEY (Codice_Ordine),
    CONSTRAINT FK_STATO_ORDINE_ORDINE FOREIGN KEY (Codice_Ordine) REFERENCES ORDINI(Codice_Ordine),
    CONSTRAINT FK_STATO_ORDINE_RIDER FOREIGN KEY (Codice_Rider) REFERENCES RIDER(Codice_Rider),
    CONSTRAINT CHK_SEQUENZA_STATI CHECK (
        (Ora_In_Preparazione IS NULL OR Ora_In_Preparazione >= Data) AND
        (Ora_In_Consegna IS NULL OR (Ora_In_Preparazione IS NOT NULL AND Ora_In_Consegna >= Ora_In_Preparazione)) AND
        (Ora_Consegnato IS NULL OR (Ora_In_Consegna IS NOT NULL AND Ora_Consegnato >= Ora_In_Consegna))
    )
);

CREATE TABLE DETTAGLI_ORDINI (
    Codice_Piatto INTEGER NOT NULL,
    Codice_Ordine INTEGER NOT NULL,
    Numero_Linea TINYINT NOT NULL,
    Quantita SMALLINT NOT NULL,
    Prezzo_Unitario DECIMAL(8,2) NOT NULL,
    CONSTRAINT PK_DETTAGLIO_ORDINE PRIMARY KEY (Codice_Piatto, Codice_Ordine),
    CONSTRAINT UK_DETTAGLIO_ORDINE UNIQUE (Codice_Ordine, Numero_Linea),
    CONSTRAINT FK_DETTAGLIO_PIATTO FOREIGN KEY (Codice_Piatto) REFERENCES PIATTI(Codice_Piatto),
    CONSTRAINT FK_DETTAGLIO_ORDINE FOREIGN KEY (Codice_Ordine) REFERENCES ORDINI(Codice_Ordine) ON DELETE CASCADE,
    CONSTRAINT CHK_QUANTITA CHECK (Quantita > 0),
    CONSTRAINT CHK_PREZZO_UNITARIO CHECK (Prezzo_Unitario > 0),
    CONSTRAINT CHK_NUMERO_LINEA CHECK (Numero_Linea > 0)
);

CREATE TABLE CONTRATTI (
    Codice_Rider INTEGER NOT NULL,
    Paga_Oraria DECIMAL(6,2) NOT NULL,
    Testo TEXT NOT NULL,
    CONSTRAINT PK_CONTRATTO PRIMARY KEY (Codice_Rider),
    CONSTRAINT FK_CONTRATTO_RIDER FOREIGN KEY (Codice_Rider) REFERENCES RIDER(Codice_Rider),
    CONSTRAINT CHK_PAGA_ORARIA CHECK (Paga_Oraria > 0)
);

CREATE TABLE MEZZI (
	Codice_Mezzo INTEGER NOT NULL,
    Codice_Rider INTEGER NOT NULL,
    Tipo ENUM('Auto', 'Moto', 'Bicicletta', 'Scooter') NOT NULL,
    Targa VARCHAR(10),
    Modello VARCHAR(50),
    CONSTRAINT PK_MEZZO PRIMARY KEY (Codice_Rider, Codice_Mezzo),
    CONSTRAINT FK_MEZZO_RIDER FOREIGN KEY (Codice_Rider) REFERENCES RIDER(Codice_Rider) ON DELETE CASCADE,
    CONSTRAINT CHK_TARGA_REQUIRED CHECK (
        (Tipo IN ('Auto', 'Moto', 'Scooter') AND Targa IS NOT NULL) OR 
        (Tipo = 'Bicicletta')
    )
);

CREATE TABLE Offre (
    P_IVA CHAR(11) NOT NULL,
    Codice_Piatto INTEGER NOT NULL,
    CONSTRAINT PK_OFFRE PRIMARY KEY (P_IVA, Codice_Piatto),
    CONSTRAINT FK_OFFRE_RISTORANTE FOREIGN KEY (P_IVA) REFERENCES RISTORANTI(P_IVA),
    CONSTRAINT FK_OFFRE_PIATTO FOREIGN KEY (Codice_Piatto) REFERENCES PIATTI(Codice_Piatto)
);

CREATE TABLE Applicazione (
    Codice_Ordine INTEGER NOT NULL,
    Sconto_Applicato DECIMAL(8,2) NOT NULL,
    P_IVA CHAR(11) NOT NULL,
    Data_Inizio DATE NOT NULL,
    Data_Fine DATE NOT NULL,
    CONSTRAINT PK_APPLICAZIONE PRIMARY KEY (Codice_Ordine),
    CONSTRAINT FK_APPLICAZIONE_ORDINE FOREIGN KEY (Codice_Ordine) REFERENCES ORDINI(Codice_Ordine) ON DELETE CASCADE,
    CONSTRAINT FK_APPLICAZIONE_PROMOZIONE FOREIGN KEY (P_IVA, Data_Inizio, Data_Fine) REFERENCES PROMOZIONI(P_IVA, Data_Inizio, Data_Fine),
    CONSTRAINT CHK_SCONTO_APPLICATO CHECK (Sconto_Applicato > 0)
);

CREATE TABLE Genera_Punti (
    Codice_Ordine INTEGER NOT NULL,
    Codice_Cliente INTEGER NOT NULL,
    Punti_Generati INT NOT NULL,
    CONSTRAINT PK_GENERA_PUNTI PRIMARY KEY (Codice_Ordine),
    CONSTRAINT FK_GENERA_PUNTI_ORDINE FOREIGN KEY (Codice_Ordine) REFERENCES ORDINI(Codice_Ordine) ON DELETE CASCADE,
    CONSTRAINT FK_GENERA_PUNTI_RACCOLTA FOREIGN KEY (Codice_Cliente) REFERENCES RACCOLTE_PUNTI(Codice_Cliente),
    CONSTRAINT CHK_PUNTI_GENERATI CHECK (Punti_Generati > 0)
);

CREATE TABLE Utilizza_Punti (
    Codice_Ordine INTEGER NOT NULL,
    Codice_Cliente INTEGER NOT NULL,
    Punti_Usati INT NOT NULL,
    Sconto_Applicato DECIMAL(8,2) NOT NULL,
    CONSTRAINT PK_UTILIZZA_PUNTI PRIMARY KEY (Codice_Ordine),
    CONSTRAINT FK_UTILIZZA_PUNTI_ORDINE FOREIGN KEY (Codice_Ordine) REFERENCES ORDINI(Codice_Ordine) ON DELETE CASCADE,
    CONSTRAINT FK_UTILIZZA_PUNTI_RACCOLTA FOREIGN KEY (Codice_Cliente) REFERENCES RACCOLTE_PUNTI(Codice_Cliente),
    CONSTRAINT CHK_PUNTI_USATI CHECK (Punti_Usati > 0),
    CONSTRAINT CHK_SCONTO_PUNTI CHECK (Sconto_Applicato > 0)
);

CREATE TABLE Recensioni (
    Codice_Cliente INTEGER NOT NULL,
    P_IVA CHAR(11) NOT NULL,
    Numero_Stelle TINYINT NOT NULL,
    Descrizione TEXT,
    Titolo VARCHAR(100) NOT NULL,
    Data DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT PK_RECENSIONE PRIMARY KEY (Codice_Cliente, P_IVA),
    CONSTRAINT FK_RECENSIONE_CLIENTE FOREIGN KEY (Codice_Cliente) REFERENCES CLIENTI(Codice_Cliente),
    CONSTRAINT FK_RECENSIONE_RISTORANTE FOREIGN KEY (P_IVA) REFERENCES RISTORANTI(P_IVA),
    CONSTRAINT CHK_NUMERO_STELLE CHECK (Numero_Stelle BETWEEN 1 AND 5)
);

CREATE TABLE Residenza (
    Codice_Cliente INTEGER NOT NULL,
    Codice_Indirizzo INTEGER NOT NULL,
    CONSTRAINT PK_RESIDENZA PRIMARY KEY (Codice_Cliente, Codice_Indirizzo),
    CONSTRAINT FK_RESIDENZA_CLIENTE FOREIGN KEY (Codice_Cliente) REFERENCES CLIENTI(Codice_Cliente) ON DELETE CASCADE,
    CONSTRAINT FK_RESIDENZA_INDIRIZZO FOREIGN KEY (Codice_Indirizzo) REFERENCES INDIRIZZI(Codice_Indirizzo)
);

CREATE TABLE Visualizzazione (
    Codice_Cliente INTEGER NOT NULL,
    P_IVA CHAR(11) NOT NULL,
    CONSTRAINT PK_VISUALIZZAZIONE PRIMARY KEY (Codice_Cliente, P_IVA),
    CONSTRAINT FK_VISUALIZZAZIONE_CLIENTE FOREIGN KEY (Codice_Cliente) REFERENCES CLIENTI(Codice_Cliente) ON DELETE CASCADE,
    CONSTRAINT FK_VISUALIZZAZIONE_RISTORANTE FOREIGN KEY (P_IVA) REFERENCES RISTORANTI(P_IVA)
);

-- Indexes Section
-- _______________

-- Indici per chiavi esterne più utilizzate
CREATE INDEX IDX_ORDINE_RISTORANTE ON ORDINI(P_IVA);
CREATE INDEX IDX_DETTAGLIO_ORDINE_ORDINE ON DETTAGLI_ORDINI(Codice_Ordine);
CREATE INDEX IDX_RIDER_ZONA ON RIDER(Codice_Zona);
CREATE INDEX IDX_RISTORANTE_ZONA ON RISTORANTI(Codice_Zona);
CREATE INDEX IDX_RECENSIONE_RISTORANTE ON Recensioni(P_IVA);
CREATE INDEX IDX_PAGAMENTO_CLIENTE ON PAGAMENTI(Codice_Cliente);

-- Indici per ricerche comuni
CREATE INDEX IDX_CLIENTE_EMAIL ON CLIENTI(E_mail);
CREATE INDEX IDX_CLIENTE_USERNAME ON CLIENTI(Username);
CREATE INDEX IDX_RIDER_EMAIL ON RIDER(E_mail);
CREATE INDEX IDX_PIATTO_NOME ON PIATTI(Nome);
CREATE INDEX IDX_ORDINE_DATA ON STATI_ORDINI(Data);
CREATE INDEX IDX_PROMOZIONE_DATE ON PROMOZIONI(Data_Inizio, Data_Fine);

-- Trigger per aggiornamento automatico punti
DELIMITER //

CREATE TRIGGER TR_AGGIORNA_PUNTI_DOPO_ORDINE
    AFTER INSERT ON Genera_Punti
    FOR EACH ROW
BEGIN
    UPDATE RACCOLTE_PUNTI 
    SET Punti_Totali = Punti_Totali + NEW.Punti_Generati
    WHERE Codice_Cliente = NEW.Codice_Cliente;
END//

CREATE TRIGGER TR_SOTTRAI_PUNTI_UTILIZZATI
    AFTER INSERT ON Utilizza_Punti
    FOR EACH ROW
BEGIN
    UPDATE RACCOLTA_PUNTI 
    SET Punti_Totali = Punti_Totali - NEW.Punti_Usati
    WHERE Codice_Cliente = NEW.Codice_Cliente;
END//

DELIMITER ;