CREATE TABLE ZONA (
    Nome            VARCHAR(50)     NOT NULL,
    AreaCitta       VARCHAR(100)    NOT NULL,
    TariffaOraria   DECIMAL(5,2)    NOT NULL,
    
    PRIMARY KEY (Nome)
);


CREATE TABLE PARCHEGGIO (
    IndirizzoGeografico VARCHAR(150)    NOT NULL,
    CapacitaMoto        INT             NOT NULL,
    CapacitaAutomobile  INT             NOT NULL,
    CapacitaCamion      INT             NOT NULL,
    Categoria           VARCHAR(10)     NOT NULL,
    H_apertura          TIME,
    H_chiusura          TIME,
    Responsabile        CHAR(16),
    Zona                VARCHAR(50),
    
    PRIMARY KEY (IndirizzoGeografico),

    FOREIGN KEY (Zona) REFERENCES ZONA(Nome),
    
    CONSTRAINT CHK_Categoria_Parcheggio
        CHECK (Categoria IN ('garage', 'aperto')),

    CONSTRAINT CHK_Verifica_Orario
        CHECK (
            (Categoria == "garage" AND (H_apertura IS NOT NULL AND H_chiusura IS NOT NULL))
            OR
            (Categoria == "aperto" AND (H_apertura IS NULL AND H_chiusura IS NULL))
        )
);


CREATE TABLE UTENTE (
    CodiceFiscale   CHAR(16)        NOT NULL,
    Nome            VARCHAR(50)     NOT NULL,
    Cognome         VARCHAR(50)     NOT NULL,
    NumTelefono     VARCHAR(15),
    Mail            VARCHAR(100)    NOT NULL,
    Password        VARCHAR(255)    NOT NULL,
    
    PRIMARY KEY (CodiceFiscale)
);


CREATE TABLE DIPENDENTE (
    CodiceFiscale       CHAR(16)        NOT NULL,
    Nome                VARCHAR(50)     NOT NULL,
    Cognome             VARCHAR(50)     NOT NULL,
    Ruolo               VARCHAR(30)     NOT NULL,
    ParcheggioAssegnato VARCHAR(150),
    
    PRIMARY KEY (CodiceFiscale),

    FOREIGN KEY (ParcheggioAssegnato) REFERENCES PARCHEGGIO(IndirizzoGeografico),
    
    CONSTRAINT CHK_Ruolo_Dipendente
        CHECK (Ruolo IN ('operatore', 'controllore', 'addetto_amministrativo', 'responsabile')),
    CONSTRAINT CHK_Addetto_amministrativo
        CHECK (Ruolo == 'addetto_amministrativo' AND (ParcheggioAssegnato IS NULL))
);


CREATE TABLE VEICOLO (
    Targa       VARCHAR(10)     NOT NULL,
    Categoria   VARCHAR(15)     NOT NULL,
    Utente      CHAR(16)        NOT NULL,
    
    PRIMARY KEY (Targa),

    FOREIGN KEY (Utente) REFERENCES UTENTE(CodiceFiscale)
    
    CONSTRAINT CHK_Categoria_Veicolo
        CHECK (Categoria IN ('moto', 'automobile', 'camion'))
);


CREATE TABLE ABBONAMENTO (
    Utente          CHAR(16)        NOT NULL,
    DataValidazione DATE            NOT NULL,
    DataScadenza    DATE            NOT NULL,
    Tariffa         DECIMAL(7,2)    NOT NULL,
    
    PRIMARY KEY (Utente, DataValidazione),
    
    FOREIGN KEY (Utente) REFERENCES UTENTE(CodiceFiscale)
);


CREATE TABLE BIGLIETTO (
    Utente          CHAR(16)        NOT NULL,
    OraValidazione  DATETIME        NOT NULL,
    OraScadenza     DATETIME        NOT NULL,
    Tariffa         DECIMAL(5,2)    NOT NULL,
    Zona            VARCHAR(50)     NOT NULL,
    
    PRIMARY KEY (Utente, OraValidazione),
    
    FOREIGN KEY (Utente) REFERENCES UTENTE(CodiceFiscale),
    FOREIGN KEY (Zona) REFERENCES ZONA(Nome)
);


CREATE TABLE SOSTA (
    Veicolo             VARCHAR(10)     NOT NULL,
    Data                DATE            NOT NULL,
    Hinizio             TIME            NOT NULL,
    Hfine               TIME,
    Parcheggio          VARCHAR(150)    NOT NULL,
    AbbonamentoUtente   CHAR(16),
    AbbonamentoData     DATE,
    BigliettoUtente     CHAR(16),
    BigliettoOra        DATETIME,
    
    PRIMARY KEY (Veicolo, Data, Hinizio),

    FOREIGN KEY (Veicolo) REFERENCES VEICOLO(Targa),
    FOREIGN KEY (Parcheggio) REFERENCES PARCHEGGIO(IndirizzoGeografico),
    FOREIGN KEY (AbbonamentoUtente, AbbonamentoData) REFERENCES ABBONAMENTO(Utente, DataValidazione),
    FOREIGN KEY (BigliettoUtente, BigliettoOra) REFERENCES BIGLIETTO(Utente, OraValidazione)
    
    CONSTRAINT CHK_Sosta_Abbonamento
        CHECK (
            (AbbonamentoUtente IS NULL AND AbbonamentoData IS NULL)
            OR
            (AbbonamentoUtente IS NOT NULL AND AbbonamentoData IS NOT NULL)
        ),
    CONSTRAINT CHK_Sosta_Biglietto
        CHECK (
            (BigliettoUtente IS NULL AND BigliettoOra IS NULL)
            OR
            (BigliettoUtente IS NOT NULL AND BigliettoOra IS NOT NULL)
        ),
    CONSTRAINT CHK_Sosta_TitoloEsclusivo
        CHECK (
            (AbbonamentoUtente IS NOT NULL AND BigliettoUtente IS NULL)
            OR
            (AbbonamentoUtente IS NULL AND BigliettoUtente IS NOT NULL)
        )
);


CREATE TABLE SANZIONE (
    Veicolo         VARCHAR(10)     NOT NULL,
    Data            DATE            NOT NULL,
    Hinizio         TIME            NOT NULL,
    Motivo          VARCHAR(255)    NOT NULL,
    ImportoEuro     DECIMAL(7,2)    NOT NULL,
    Dipendente      CHAR(16)        NOT NULL,
    
    PRIMARY KEY (Veicolo, Data, Hinizio),
    
    FOREIGN KEY (Veicolo, Data, Hinizio) REFERENCES SOSTA(Veicolo, Data, Hinizio),
    FOREIGN KEY (Dipendente) REFERENCES DIPENDENTE(CodiceFiscale)
);


CREATE TABLE VALUTAZIONE (
    Utente          CHAR(16)        NOT NULL,
    DataValutazione DATE            NOT NULL,
    VotoPulizia     INT             NOT NULL,
    VotoSicurezza   INT             NOT NULL,
    Commento        TEXT,
    Parcheggio      VARCHAR(150)    NOT NULL,

    PRIMARY KEY (Utente, DataValutazione),

    FOREIGN KEY (Utente) REFERENCES UTENTE(CodiceFiscale),
    FOREIGN KEY (Parcheggio) REFERENCES PARCHEGGIO(IndirizzoGeografico)
    
    CONSTRAINT CHK_VotoPulizia
        CHECK (VotoPulizia BETWEEN 1 AND 5),
    CONSTRAINT CHK_VotoSicurezza
        CHECK (VotoSicurezza BETWEEN 1 AND 5)
);

CREATE TABLE TURNO (
    Dipendente  CHAR(16)        NOT NULL,
    Data        DATE            NOT NULL,
    Hinizio     TIME            NOT NULL,
    Hfine       TIME            NOT NULL,
    Parcheggio  VARCHAR(150)    NOT NULL,
    
    PRIMARY KEY (Dipendente, Data, Hinizio),
    
    FOREIGN KEY (Dipendente) REFERENCES DIPENDENTE(CodiceFiscale),
    FOREIGN KEY (Parcheggio) REFERENCES PARCHEGGIO(IndirizzoGeografico)
);


CREATE TABLE SCONTO (
    Tipo        VARCHAR(20)     NOT NULL,
    DataInizio  DATE            NOT NULL,
    DataFine    DATE            NOT NULL,
    Percentuale DECIMAL(5,2)    NOT NULL,
    TitoloSosta VARCHAR(15)     NOT NULL,
    Utente      CHAR(16),
    Zona        VARCHAR(50),
    
    PRIMARY KEY (Tipo, DataInizio, DataFine, Percentuale, TitoloSosta),

    FOREIGN KEY (Utente) REFERENCES UTENTE(CodiceFiscale),
    FOREIGN KEY (Zona) REFERENCES ZONA(Nome)
    
    CONSTRAINT CHK_Tipo_Sconto
        CHECK (Tipo IN ('generico', 'legato a una zona')),
    CONSTRAINT CHK_TitoloSosta
        CHECK (TitoloSosta IN ('biglietto', 'abbonamento')),
    CONSTRAINT CHK_Sconto_Zona
        CHECK (
            (Tipo = 'legato a una zona' AND Zona IS NOT NULL)
            OR
            (Tipo = 'generico' AND Zona IS NULL)
        )
);