CodiceSQLprogetto.sql

-- MySQL Script generated by MySQL Workbench
-- gio 06 giu 2019 16:39:13 CEST
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema sistemaaste
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema sistemaaste
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `sistemaaste` DEFAULT CHARACTER SET latin1 ;
USE `sistemaaste` ;

-- -----------------------------------------------------
-- Table `sistemaaste`.`categoria1`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sistemaaste`.`categoria1` (
  `Nome` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`Nome`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `sistemaaste`.`categoria2`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sistemaaste`.`categoria2` (
  `Nome` VARCHAR(20) NOT NULL,
  `NomeCategoriaPadre` VARCHAR(20) NULL DEFAULT NULL,
  PRIMARY KEY (`Nome`),
  CONSTRAINT `fk_categoria2_categoria1`
    FOREIGN KEY (`NomeCategoriaPadre`)
    REFERENCES `sistemaaste`.`categoria1` (`Nome`)
    ON DELETE SET NULL
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

CREATE INDEX `fk_categoria2_categoria1_idx` ON `sistemaaste`.`categoria2` (`NomeCategoriaPadre` ASC);


-- -----------------------------------------------------
-- Table `sistemaaste`.`categoria3`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sistemaaste`.`categoria3` (
  `Nome` VARCHAR(20) NOT NULL,
  `NomeCategoriaPadre` VARCHAR(20) NULL,
  PRIMARY KEY (`Nome`),
  CONSTRAINT `fk_categoria3_categoria21`
    FOREIGN KEY (`NomeCategoriaPadre`)
    REFERENCES `sistemaaste`.`categoria2` (`Nome`)
    ON DELETE SET NULL
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

CREATE INDEX `fk_categoria3_categoria21_idx` ON `sistemaaste`.`categoria3` (`NomeCategoriaPadre` ASC);


-- -----------------------------------------------------
-- Table `sistemaaste`.`utentebase`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sistemaaste`.`utentebase` (
  `CF` CHAR(16) NOT NULL,
  `Nome` VARCHAR(20) NOT NULL,
  `Cognome` VARCHAR(20) NOT NULL,
  `DataNascita` DATE NOT NULL,
  `CittaNascita` VARCHAR(20) NOT NULL,
  `NCarta` CHAR(16) NOT NULL,
  `ScadenzaCarta` CHAR(4) NOT NULL,
  `CVV` CHAR(3) NOT NULL,
  `IndirizzoConsegna` VARCHAR(30) NOT NULL,
  PRIMARY KEY (`CF`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

CREATE UNIQUE INDEX `NCarta` ON `sistemaaste`.`utentebase` (`NCarta` ASC);


-- -----------------------------------------------------
-- Table `sistemaaste`.`storicocategoria1`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sistemaaste`.`storicocategoria1` (
  `Nome` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`Nome`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `sistemaaste`.`storicocategoria2`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sistemaaste`.`storicocategoria2` (
  `Nome` VARCHAR(20) NOT NULL,
  `NomeCategoriaPadre` VARCHAR(20) NULL DEFAULT NULL,
  PRIMARY KEY (`Nome`),
  CONSTRAINT `fk_storicocategoria2_storicocategoria11`
    FOREIGN KEY (`NomeCategoriaPadre`)
    REFERENCES `sistemaaste`.`storicocategoria1` (`Nome`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

CREATE INDEX `fk_storicocategoria2_storicocategoria11_idx` ON `sistemaaste`.`storicocategoria2` (`NomeCategoriaPadre` ASC);


-- -----------------------------------------------------
-- Table `sistemaaste`.`storicocategoria3`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sistemaaste`.`storicocategoria3` (
  `Nome` VARCHAR(20) NOT NULL,
  `NomeCategoriaPadre` VARCHAR(20) NULL DEFAULT NULL,
  PRIMARY KEY (`Nome`),
  CONSTRAINT `fk_storicocategoria3_storicocategoria21`
    FOREIGN KEY (`NomeCategoriaPadre`)
    REFERENCES `sistemaaste`.`storicocategoria2` (`Nome`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

CREATE INDEX `fk_storicocategoria3_storicocategoria21_idx` ON `sistemaaste`.`storicocategoria3` (`NomeCategoriaPadre` ASC);


-- -----------------------------------------------------
-- Table `sistemaaste`.`utenteamministratore`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sistemaaste`.`utenteamministratore` (
  `CF` CHAR(16) NOT NULL,
  `Nome` VARCHAR(20) NOT NULL,
  `Cognome` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`CF`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `sistemaaste`.`oggettoallasta`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sistemaaste`.`oggettoallasta` (
  `ID` INT(11) NOT NULL AUTO_INCREMENT,
  `Nome` VARCHAR(20) NOT NULL,
  `CategoriaNew` VARCHAR(20) NULL DEFAULT NULL,
  `CategoriaOld` VARCHAR(20) NOT NULL,
  `Descrizione` LONGTEXT NULL DEFAULT NULL,
  `Stato` VARCHAR(20) NOT NULL,
  `Colore` VARCHAR(20) NULL DEFAULT NULL,
  `Espositore` CHAR(16) NOT NULL,
  `Dimensioni` VARCHAR(10) NULL DEFAULT NULL,
  `PrezzoBase` DECIMAL(7,2) NOT NULL,
  `NOfferte` INT(11) NOT NULL DEFAULT 0,
  `MAXOfferta` DECIMAL(7,2) NULL DEFAULT NULL,
  `MigliorOfferente` CHAR(16) NULL DEFAULT NULL,
  `ImportoControffertaMax` DECIMAL(7,2) NULL DEFAULT NULL,
  `Scadenza` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP(),
  PRIMARY KEY (`ID`),
  CONSTRAINT `fk_oggettoallasta_categoria31`
    FOREIGN KEY (`CategoriaNew`)
    REFERENCES `sistemaaste`.`categoria3` (`Nome`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_oggettoallasta_storicocategoria31`
    FOREIGN KEY (`CategoriaOld`)
    REFERENCES `sistemaaste`.`storicocategoria3` (`Nome`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_oggettoallasta_utenteamministratore1`
    FOREIGN KEY (`Espositore`)
    REFERENCES `sistemaaste`.`utenteamministratore` (`CF`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

CREATE INDEX `fk_oggettoallasta_categoria31_idx` ON `sistemaaste`.`oggettoallasta` (`CategoriaNew` ASC);

CREATE INDEX `fk_oggettoallasta_storicocategoria31_idx` ON `sistemaaste`.`oggettoallasta` (`CategoriaOld` ASC);

CREATE INDEX `fk_oggettoallasta_utenteamministratore1_idx` ON `sistemaaste`.`oggettoallasta` (`Espositore` ASC);


-- -----------------------------------------------------
-- Table `sistemaaste`.`offerta`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sistemaaste`.`offerta` (
  `Utente` CHAR(16) NOT NULL,
  `Oggetto` INT(11) NOT NULL,
  `Istante` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP(),
  `Importo` DECIMAL(7,2) NOT NULL,
  `Automatica` TINYINT(1) NOT NULL DEFAULT 0,
  `ImportoControfferta` DECIMAL(7,2) NULL DEFAULT NULL,
  PRIMARY KEY (`Utente`, `Oggetto`, `Istante`),
  CONSTRAINT `fk_offerta_utentebase1`
    FOREIGN KEY (`Utente`)
    REFERENCES `sistemaaste`.`utentebase` (`CF`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_offerta_oggettoallasta1`
    FOREIGN KEY (`Oggetto`)
    REFERENCES `sistemaaste`.`oggettoallasta` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

CREATE INDEX `fk_offerta_utentebase1_idx` ON `sistemaaste`.`offerta` (`Utente` ASC);

CREATE INDEX `fk_offerta_oggettoallasta1_idx` ON `sistemaaste`.`offerta` (`Oggetto` ASC);

USE `sistemaaste` ;

-- -----------------------------------------------------
-- Placeholder table for view `sistemaaste`.`aste_attive`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sistemaaste`.`aste_attive` (`ID` INT, `Nome` INT, `Espositore` INT, `CategoriaOld` INT, `Descrizione` INT, `Dimensioni` INT, `Stato` INT, `Colore` INT, `PrezzoBase` INT, `N°Offerte` INT, `MAXofferta` INT, `Scadenza` INT);

-- -----------------------------------------------------
-- Placeholder table for view `sistemaaste`.`visualizzazione_aste`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sistemaaste`.`visualizzazione_aste` (`ID` INT, `Nome` INT, `Espositore` INT, `CategoriaOld` INT, `Descrizione` INT, `Dimensioni` INT, `Stato` INT, `Colore` INT, `PrezzoBase` INT, `N°Offerte` INT, `MAXofferta` INT, `Scadenza` INT);

-- -----------------------------------------------------
-- Placeholder table for view `sistemaaste`.`titolario_gerarchico_lower`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sistemaaste`.`titolario_gerarchico_lower` (`Nome` INT, `Padre` INT);

-- -----------------------------------------------------
-- Placeholder table for view `sistemaaste`.`titolario_gerarchico_upper`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sistemaaste`.`titolario_gerarchico_upper` (`Nome` INT, `Padre` INT);

-- -----------------------------------------------------
-- procedure inserimento_categoria3
-- -----------------------------------------------------

DELIMITER $$
USE `sistemaaste`$$
CREATE PROCEDURE `inserimento_categoria3` (in nome_categoria varchar(20), in padre_categoria varchar(20))
BEGIN

	declare var_compara_nome varchar(20);
    declare var_compara_nome_padre varchar(20);
    
    
	declare exit handler for sqlexception
    begin
		rollback;
        resignal;
	end;
    
    
    start transaction;
    
    #controlla se la categoria è già esistente
    select `Nome` from `categoria3` as C where C.`Nome` = `nome_categoria` into var_compara_nome;
        if var_compara_nome is not null then
			signal sqlstate '45001'
			set message_text = "Categoria gia esistente.";
        end if;
    
    if `padre_categoria` is null then
        insert into `categoria3`(`Nome`) values (`nome_categoria`);
	end if;
	
    
    
    if `padre_categoria` is not null then
		select `Nome` from `categoria2` where `Nome` = `padre_categoria` into var_compara_nome_padre;
        # se non esiste questa categoria-padre viene inserita al secondo livello
        if var_compara_nome_padre is null then
			signal sqlstate '45007'
            set message_text = "Attenzione, non esiste la categoria padre indicata al livello 2 del titolario!";
		end if;
	insert into `categoria3`(`Nome`,`NomeCategoriaPadre`) values (`nome_categoria`,`padre_categoria`);
    end if;
    
    commit;
    
        
        
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure inserimento_categoria2
-- -----------------------------------------------------

DELIMITER $$
USE `sistemaaste`$$
CREATE PROCEDURE `inserimento_categoria2` (in nome_categoria varchar(20), in padre_categoria varchar(20))
BEGIN

	declare var_compara_nome varchar(20);
    declare var_compara_nome_padre varchar(20);
    
    
	declare exit handler for sqlexception
    begin
		rollback;
        resignal;
	end;
    
    
    start transaction;
    
    # controlla se esiste gia la categoria
    select `Nome` from `categoria2` as C where C.`Nome` = `nome_categoria` into var_compara_nome;
        if var_compara_nome is not null then
			signal sqlstate '45001'
			set message_text = "Categoria gia esistente.";
        end if;
    
    
    if `padre_categoria` is null then
        insert into `categoria2`(`Nome`) values (`nome_categoria`);
	end if;
	
    
    
    if `padre_categoria` is not null then
		select `Nome` from `categoria1` where `Nome` = `padre_categoria` into var_compara_nome_padre;
        # se non esiste questa categoria-padre viene inserita al secondo livello
        if var_compara_nome_padre is null then
			signal sqlstate '45007'
            set message_text = "Attenzione, non esiste la categoria padre indicata al livello 1 del titolario!";
		end if;
	insert into `categoria2`(`Nome`,`NomeCategoriaPadre`) values (`nome_categoria`,`padre_categoria`);
    end if;
    
    commit;
    
        
        
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure inserimento_categoria1
-- -----------------------------------------------------

DELIMITER $$
USE `sistemaaste`$$
CREATE PROCEDURE `inserimento_categoria1` (in nome_categoria varchar(20))
BEGIN

	declare var_compara_nome varchar(20);
    
    
	declare exit handler for sqlexception
    begin
		rollback;
        resignal;
	end;
    
    
    start transaction;
    
    # controlla se esiste gia la categoria
    select `Nome` from `categoria1` as C where C.`Nome` = `nome_categoria` into var_compara_nome;
        if var_compara_nome is not null then
			signal sqlstate '45001'
			set message_text = "Categoria gia esistente.";
        end if;
    
    insert into `categoria1`(`Nome`) values (`nome_categoria`);
    
    commit;
    
        
        
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure cancellazione_categoria
-- -----------------------------------------------------

DELIMITER $$
USE `sistemaaste`$$
CREATE PROCEDURE `cancellazione_categoria` (in nome_categoria varchar(20))
BEGIN

	declare var_compara_nome varchar(20);
    
	declare exit handler for sqlexception
    begin
		rollback;
        resignal;
	end;
    
    
    start transaction;
    
		select `Nome` from `categoria1` as C where C.`Nome` = `nome_categoria` into var_compara_nome;
        if var_compara_nome is null then
			
            select `Nome` from `categoria2` as C where C.`Nome` = `nome_categoria` into var_compara_nome;
            if var_compara_nome is null then
            
                select `Nome` from `categoria3` as C where C.`Nome` = `nome_categoria` into var_compara_nome;
				if var_compara_nome is null then
					signal sqlstate '45002'
					set message_text = "Questa categoria non esiste.";
				end if;
                
                if var_compara_nome is not null then
					delete from `categoria3` where `Nome` = `nome_categoria`;
				end if;
                
			end if;
            
            if var_compara_nome is not null then
				delete from `categoria2` where `Nome` = `nome_categoria`;
            end if;
            
            
		end if;
        
        if var_compara_nome is not null then
			delete from `categoria1` where `Nome` = `nome_categoria`;
        end if;
        
    commit;
    
	
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure popolamento_categorie
-- -----------------------------------------------------

DELIMITER $$
USE `sistemaaste`$$
CREATE PROCEDURE `popolamento_categorie` ()
BEGIN
	
    declare exit handler for sqlexception
    begin
    rollback;
    resignal;
    end;
    
    start transaction;
    
    call inserimento_categoria1('alimentari');
    call inserimento_categoria1('elettronica');
    call inserimento_categoria1('casa');
    
    
    call inserimento_categoria2('elettrodomestici', 'elettronica');
    call inserimento_categoria2('console','elettronica');
    call inserimento_categoria2('cibo fresco','alimentari');
    call inserimento_categoria2('cibo in scatola','alimentari');
    call inserimento_categoria2('giardinaggio','casa');
    call inserimento_categoria2('mobili','casa');
    
    call inserimento_categoria3('cucina', 'elettrodomestici');
    call inserimento_categoria3('pulizia', 'elettrodomestici');
    call inserimento_categoria3('portatili', 'console');
    call inserimento_categoria3('fisse', 'console');
    call inserimento_categoria3('palmari', 'console');
    call inserimento_categoria3('potatura', 'giardinaggio');
    call inserimento_categoria3('muratura', 'giardinaggio');
    call inserimento_categoria3('innaffiamento', 'giardinaggio');
    call inserimento_categoria3('pensili', 'mobili');
    call inserimento_categoria3('ripiani', 'mobili');
    call inserimento_categoria3('poltrone e divani', 'mobili');
    
    commit;
    
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure aggiornamento_categoria
-- -----------------------------------------------------

DELIMITER $$
USE `sistemaaste`$$
CREATE PROCEDURE `aggiornamento_categoria` (in old_nome_categoria varchar(20), in new_nome_categoria varchar(20), in old_nome_padre varchar(20), in new_nome_padre varchar (20) )
BEGIN
	
    declare var_compara_nome varchar(20);
    declare var_compara_nomepadre varchar(20);
    
    declare exit handler for sqlexception
    begin
		rollback;
        resignal;
	end;
    
    
    start transaction;
    
    
    # check sulle variabili di ingresso indispensabili all'esecuzione della procedura
    if `old_nome_categoria` is null then
		signal sqlstate '45005'
		set message_text = "E' necessario fornire il nome della categoria per cercarla nel titolario.";
	end if;
	
    
                    
    #verifica l'esistenza della categoria da modificare, cercandola nei tre livelli del titolario.
    
    #LIVELLO 1, RICERCA.
    select `Nome` from `categoria1` as C where C.`Nome` = `old_nome_categoria` into var_compara_nome;
        if var_compara_nome is null then # chiave non trovata al primo livello!
        
			#LIVELLO2, RICERCA.
            select `Nome` from `categoria2` as C where C.`Nome` = `old_nome_categoria` into var_compara_nome;
            if var_compara_nome is null then #chiave non trovata al secondo livello!
            
					#LIVELLO3, RICERCA.
					select `Nome` from `categoria3` as C where C.`Nome` = `old_nome_categoria` into var_compara_nome;
					if var_compara_nome is null then #chiave non trovata al primo livello, quindi non presente nel titolario!
						signal sqlstate '45002'
						set message_text = "Questa categoria non esiste.";
					end if;
					
					if var_compara_nome is not null then #chiave trovata al primo livello.
					
						if `new_nome_padre` is null then
					
							if `new_nome_categoria` is null then
								signal sqlstate '45004'
								set message_text = "Non si può aggiornare a valore nullo il nome di una categoria.";
							end if;
							#si vuole aggiornare solo il nome della categoria.
							update `categoria3` set `Nome` = `new_nome_categoria` where `Nome` = `old_nome_categoria`;
						end if;
					
						if `new_nome_padre` is not null then
							
                            #se non esiste,ERRORE
                            select `Nome` from `categoria2` where `Nome`=`new_nome_padre` into var_compara_nomepadre;
                            if var_compara_nomepadre is null then
								signal sqlstate '45007'
								set message_text = "Attenzione, non esiste la categoria padre indicata al livello 2 del titolario!";
							end if;
					
							if `new_nome_categoria` is null then #si vuole aggiornare solo il padre
							
								update `categoria3` set `NomeCategoriaPadre` = `new_nome_padre` where `Nome` = `old_nome_categoria`;
							end if;
						
							if `new_nome_categoria` is not null then #si vuole aggiornare sia il padre che il nome della categoria
								update `categoria3` set `Nome` = `new_nome_categoria`, `NomeCategoriaPadre` = `new_nome_padre`  
									where `Nome` = `old_nome_categoria`;
								
							end if;
						
						end if;
					 set var_compara_nome = null;
					 end if;
                     
                    
			end if;
            
            if var_compara_nome is not null then #chiave trovata al secondo livello!
            
				if `new_nome_padre` is null then
					if `new_nome_categoria` is null then
						signal sqlstate '45004'
						set message_text = "Non si può aggiornare a valore nullo il nome di una categoria.";
					end if;
                    #si vuole aggiornare solo il nome della categoria.
					update `categoria2` set `Nome` = `new_nome_categoria` where `Nome` = `old_nome_categoria`;
				end if;
                
                if `new_nome_padre` is not null then
                
					#se non esiste, ERRORE
					select `Nome` from `categoria1` where `Nome`=`new_nome_padre` into var_compara_nomepadre;
					if var_compara_nomepadre is null then
						signal sqlstate '45007'
						set message_text = "Attenzione, non esiste la categoria padre indicata al livello 1 del titolario!";
					end if;
                            
					if `new_nome_categoria` is null then #si vuole aggiornare solo il padre
						
						update `categoria2` set `NomeCategoriaPadre` = `new_nome_padre` where `Nome` = `old_nome_categoria`;
					end if;
                    
					if `new_nome_categoria` is not null then #si vuole aggiornare sia il padre che il nome della categoria
						update `categoria2` set `Nome` = `new_nome_categoria`, `NomeCategoriaPadre` = `new_nome_padre`  
							where `Nome` = `old_nome_categoria`;
					end if;
                    
				end if;
			set var_compara_nome = null;
            end if;
            
            
		end if;
        
        if var_compara_nome is not null then  #chiave trovata al primo livello!
			if `new_nome_categoria` is null then
				signal sqlstate '45004'
				set message_text = "Non si può aggiornare a valore nullo il nome di una categoria.";
			end if;
        
            update `categoria1` set `Nome` = `new_nome_categoria` where `Nome` = `old_nome_categoria`;
            
        end if;
    
    
    
    commit;
    
    
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure registrazione_utente
-- -----------------------------------------------------

DELIMITER $$
USE `sistemaaste`$$
CREATE PROCEDURE `registrazione_utente` (in user_type int, 
										 in cf char(16), 
										 in nome_utente varchar(20), 
                                         in cognome_utente varchar(20), 
                                         in birth date, 
                                         in birth_place varchar(20), 
                                         in num_carta char(16), 
                                         in scad_carta char(4), 
                                         in cvv char(3), 
                                         in indirizzo_consegna varchar(30),
                                         in psw varchar(30))
BEGIN
    
    
	declare exit handler for sqlexception
    begin
		rollback;
        resignal;
	end;
    
    
    start transaction;
    
    if `user_type` = 0 then
		
        insert into `utenteamministratore`(`CF`,`Nome`,`Cognome`) values (`cf`,`nome_utente`,`cognome_utente`);
        insert into `Login`(`CodiceFiscale`,`Password`,`Admin`) values (`cf`,`psw`,`user_type`);
        
	end if;
    
    if `user_type` = 1 then
		
        insert into `utentebase`(`CF`,`Nome`,`Cognome`,`DataNascita`,`CittaNascita`, `NCarta`, `ScadenzaCarta`, `CVV`, `IndirizzoConsegna`) 
			values (`cf`,`nome_utente`,`cognome_utente`,`birth`,`birth_place`,`num_carta`,`scad_carta`,`cvv`,`indirizzo_consegna`);
		insert into `Login`(`CodiceFiscale`,`Password`,`Admin`) values (`cf`,`psw`,`user_type`);
            
	end if;
    
    if `user_type` <> 1 and `user_type` <> 0 then
		signal sqlstate '45010'
        set message_text = "tipologia utente non specificata.";
    end if;
    
    
    commit;
    
        
        
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure inizializzazione_asta
-- -----------------------------------------------------

DELIMITER $$
USE `sistemaaste`$$
CREATE PROCEDURE `inizializzazione_asta` (in nome_ogg varchar(20), in cat varchar(20), in descrizione_ogg longtext, 
										  in stato_ogg varchar(30), in color_ogg varchar(20), 
                                          in espositore_ogg char(16), in dim varchar(20), in prezzo_base decimal(7,2), 
                                          in scad_asta int)

BEGIN

	declare exit handler for sqlexception
    begin
		rollback;
        resignal;
	end;
    
    start transaction;
		
        if (`scad_asta` < 1 or `scad_asta`>7 ) then
			signal sqlstate '45008'
            set message_text = "Attenzione: L'asta puo avere durata da un minimo di 1 a un massimo di 7 giorni.";
		end if;
    
		if `cat` is not null then
			
						insert into `oggettoallasta`(`Nome`,`CategoriaNew`,`CategoriaOld`,`Descrizione`,`Stato`,`Colore`,`Espositore`,
													 `Dimensioni`,`PrezzoBase`,`Scadenza`,`MAXOfferta`) values
													(`nome_ogg`,`cat`,`cat`,`descrizione_ogg`,`stato_ogg`,`color_ogg`,`espositore_ogg`,
													 `dim`,`prezzo_base`, date_add(now(), interval `scad_asta` day), `prezzo_base`);
                                                     
		end if;
        
        
                                        
    
    
    commit;



END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure registra_offerta
-- -----------------------------------------------------

DELIMITER $$
USE `sistemaaste`$$
CREATE PROCEDURE `registra_offerta` (in cf_offerente char(16), 
									 in oggetto_asta int(11),
									 in importo_rilancio decimal(7,2), 
									 in importo_controfferta_max decimal (7,2) )
                                     
BEGIN

	declare var_compara_offerta decimal(7,2);
    declare var_importo_controff decimal(7,2);
    
    declare ultimo_offerente char(16);
    declare scadenza_asta timestamp;
    
    
	declare exit handler for sqlexception
    begin
		rollback;
        resignal;
	end;
    
    start transaction;
    
		select `Scadenza` from `oggettoallasta` where `ID` = `oggetto_asta` into scadenza_asta;
        if scadenza_asta < current_timestamp() then
			signal sqlstate '45017'
            set message_text = "Asta conclusa. Oggetto aggiudicato. Non è possibile rilanciare offerte!";
        end if;
    
    
    
		#controllo sull'ultimo offerente dell'asta in questione
		select `MigliorOfferente` from `oggettoallasta` where `ID` = `oggetto_asta` into ultimo_offerente;
        if ultimo_offerente = `cf_offerente` then
			signal sqlstate '45016'
            set message_text = "Attenzione: non è possibile rilanciare la propria offerta!";
        end if;
        
        
		
		if `importo_controfferta_max` is null then
			set var_importo_controff = 0.00;
        end if;
        if `importo_controfferta_max` is not null then
			set var_importo_controff = `importo_controfferta_max`;
        end if;
        
        
        
        if `importo_rilancio` is null then
			signal sqlstate '45013'
			set message_text = "Attenzione, non è stato indicato un importo di rilancio sull'asta!";
        end if;
        
		
        
		#controllo che l'importo di rilancio  sia maggiore dell'attuale offerta massima
		select `MAXOfferta` from `oggettoallasta` where `ID` = `oggetto_asta` into var_compara_offerta;
		
		if var_compara_offerta is not null then #se esiste già un'offerta massima sull'oggetto all'asta
		
			if `importo_rilancio` <= var_compara_offerta then 
				signal sqlstate '45012'
				set message_text = "Attenzione, l'importo dell'offerta non è sufficiente a rilanciare l'offerta massima precedente";
			end if;
			
			insert into `offerta`(`Utente`,`Oggetto`,`Importo`,`ImportoControfferta`) values (`cf_offerente`,`oggetto_asta`,`importo_rilancio`, var_importo_controff);
			update `oggettoallasta` set `NOfferte` = `NOfferte` + 1,
										`MAXofferta` = `importo_rilancio`,
										`MigliorOfferente` = `cf_offerente`,
                                        `ImportoControffertaMax` = var_importo_controff
									where `ID` = `oggetto_asta`;
		 
		end if;
		
		if var_compara_offerta is null then #se questa è la prima offerta sull'oggetto all'asta
			#controllo che l'importo di rilancio non sia nullo e che sia maggiore dell'attuale offerta massima
			select `PrezzoBase` from `oggettoallasta` where `ID` = `oggetto_asta` into var_compara_offerta;
			
			if `importo_rilancio` <= var_compara_offerta then
				signal sqlstate '45012'
				set message_text = "Attenzione, l'importo dell'offerta non è sufficiente a rilanciare l'offerta massima precedente";
			end if;
			
			insert into `offerta`(`Utente`,`Oggetto`,`Importo`,`ImportoControfferta`) values (`cf_offerente`,`oggetto_asta`,`importo_rilancio`,var_importo_controff);
			update `oggettoallasta` set `NOfferte` = `NOfferte` + 1,
										`MAXofferta` = `importo_rilancio`,
										`MigliorOfferente` = `cf_offerente`,
                                        `ImportoControffertaMax` = var_importo_controff
									where `ID` = `oggetto_asta`;
		end if;
				
		
        
        
        
        
        
    commit;
    
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure visualizza_aste_per_nome_oggetto
-- -----------------------------------------------------

DELIMITER $$
USE `sistemaaste`$$
CREATE PROCEDURE `visualizza_aste_per_nome_oggetto` (in nomeoggetto varchar(20))


BEGIN
	
    declare var_nomeoggetto varchar(20);
    
    set var_nomeoggetto = concat( "%", nomeoggetto, "%");
        
	select `ID`, `Nome`, `CategoriaOld`, `MAXOfferta`
	from `aste_attive`
	where `Nome` like var_nomeoggetto;   
        
    
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure visualizza_aste_attive
-- -----------------------------------------------------

DELIMITER $$
USE `sistemaaste`$$
CREATE PROCEDURE `visualizza_aste_attive` ()

BEGIN
    
	select `ID`, `Nome`, `CategoriaOld`, `MAXOfferta`
	from `aste_attive`;

END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure visualizza_aste_per_categoria
-- -----------------------------------------------------

DELIMITER $$
USE `sistemaaste`$$
CREATE PROCEDURE `visualizza_aste_per_categoria` (in categoria varchar(20))
BEGIN
	
    
    declare var_categoria varchar(20);
    
    set var_categoria = concat( "%", categoria, "%");
        
        
	select `ID`, `aste`.`Nome`, `CategoriaOld`, `MAXOfferta`
	from `aste_attive` as `aste`
    join `storicocategoria3` as `scat3` on `CategoriaOld` = `scat3`.`Nome`
    join `storicocategoria2` as `scat2` on `scat3`.`NomeCategoriaPadre` = `scat2`.`Nome`
    join `storicocategoria1` as `scat1` on `scat2`.`NomeCategoriaPadre` = `scat1`.`Nome`
	where `scat3`.`Nome` like var_categoria 
	   or `scat2`.`Nome` like var_categoria
       or `scat1`.`Nome` like var_categoria;


END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure visualizza_aste_per_espositore
-- -----------------------------------------------------

DELIMITER $$
USE `sistemaaste`$$
CREATE PROCEDURE `visualizza_aste_per_espositore` (in espositore char(16))

BEGIN
	
    select `Espositore`, `ID`, `Nome`, `CategoriaOld`, `MAXOfferta`, 'attiva'
        from `visualizzazione_aste`
        where `Espositore` = espositore and Scadenza > current_timestamp()
						union
		select `Espositore`, `ID`, `Nome`, `CategoriaOld`, `MAXOfferta`, 'terminata'
        from `visualizzazione_aste`
        where `Espositore` = espositore and Scadenza <= current_timestamp()
		;
   
	

END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure visualizza_stato_asta
-- -----------------------------------------------------

DELIMITER $$
USE `sistemaaste`$$
CREATE PROCEDURE `visualizza_stato_asta` (in asta_id int)

BEGIN
	
    select `Espositore`, `Nome`, `Descrizione`, `CategoriaOld`, `Stato`, `Colore`,
			`Dimensioni`, `NOfferte`, `MAXOfferta`, `Scadenza`
	from `visualizzazione_aste`
    where `ID` = asta_id;
	

END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure visualizza_oggetti_aggiudicati
-- -----------------------------------------------------

DELIMITER $$
USE `sistemaaste`$$
CREATE PROCEDURE `visualizza_oggetti_aggiudicati` (in utente char(16))

BEGIN

	select `Nome`, `Descrizione`, `MAXOfferta`, `Scadenza`
    from `oggettoallasta` 
    where `MigliorOfferente` = utente 
      and `Scadenza` < current_timestamp();

END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure visualizza_partecipazione_aste
-- -----------------------------------------------------

DELIMITER $$
USE `sistemaaste`$$
CREATE PROCEDURE `visualizza_partecipazione_aste` (in utente char(16))

BEGIN

	select distinct `ID`, `Nome`, `Descrizione`, `Scadenza`
    from `offerta` 
    join `oggettoallasta` on `Oggetto` = `ID`
    where `Utente` = utente;
    
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure report_asta
-- -----------------------------------------------------

DELIMITER $$
USE `sistemaaste`$$
CREATE PROCEDURE `report_asta` (in id_asta int)

BEGIN

	select `Utente`, `Importo`, `Istante`, `Automatica`
    from `offerta`
    join `oggettoallasta` on `Oggetto` = `ID`
    where `Oggetto` = id_asta
    order by `Importo` desc;

END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure validazione_accesso
-- -----------------------------------------------------

DELIMITER $$
USE `sistemaaste`$$
CREATE PROCEDURE `validazione_accesso` (in var_cf char(16), in var_psw varchar(30), in var_admin bool)

BEGIN

	declare var_compara_cf char(16);
    
	declare exit handler for sqlexception
    begin
		rollback;
        resignal;
	end;
    
    start transaction;
    
		select `CodiceFiscale` 
		from `Login`
		where `CodiceFiscale` = `var_cf` and `Password` = `var_psw` and `Admin` = `var_admin`
        into var_compara_cf;
        
        if var_compara_cf is null then 
			signal sqlstate '45022'
            set message_text = "Accesso negato.";
		end if;
		
        
    
    commit;



END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure visualizzazione_titolario_gerarchico
-- -----------------------------------------------------

DELIMITER $$
USE `sistemaaste`$$
CREATE PROCEDURE `visualizzazione_titolario_gerarchico` ()
BEGIN

	select t_low.`Nome` as cat3, t_low.`Padre` as cat2, t_up.`Padre` as cat1
    from `titolario_gerarchico_lower` as t_low left join `titolario_gerarchico_upper` as t_up
    on t_low.`Padre` = t_up.`Nome`
    group by t_up.`Padre`, t_low.`Padre`, t_low.`Nome`
		
        union
        
	select t_low.`Nome` as cat3, t_low.`Padre` as cat2, t_up.`Padre` as cat1
    from `titolario_gerarchico_lower` as t_low right join `titolario_gerarchico_upper` as t_up
    on t_low.`Padre` = t_up.`Nome`
    group by t_up.`Padre`, t_low.`Padre`, t_low.`Nome`;
    
        
        
END$$

DELIMITER ;

-- -----------------------------------------------------
-- View `sistemaaste`.`aste_attive`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `sistemaaste`.`aste_attive`;
USE `sistemaaste`;
CREATE  OR REPLACE VIEW `aste_attive` AS
SELECT `ID`,`Nome`, `Espositore`, `CategoriaOld`, `Descrizione`, 
		`Dimensioni`, `Stato`, `Colore`, `PrezzoBase`, `N°Offerte`, 
        `MAXofferta`, `Scadenza`
FROM `oggettoallasta`
WHERE `Scadenza` > current_timestamp();

-- -----------------------------------------------------
-- View `sistemaaste`.`visualizzazione_aste`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `sistemaaste`.`visualizzazione_aste`;
USE `sistemaaste`;
CREATE  OR REPLACE VIEW `visualizzazione_aste` AS
SELECT `ID`,`Nome`, `Espositore`, `CategoriaOld`, `Descrizione`, 
		`Dimensioni`, `Stato`, `Colore`, `PrezzoBase`, `N°Offerte`, 
        `MAXofferta`, `Scadenza`
FROM `oggettoallasta`;

-- -----------------------------------------------------
-- View `sistemaaste`.`titolario_gerarchico_lower`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `sistemaaste`.`titolario_gerarchico_lower`;
USE `sistemaaste`;
CREATE  OR REPLACE VIEW `titolario_gerarchico_lower`(Nome, Padre) AS

	SELECT c3.`Nome` AS Nome, c2.`Nome` AS Padre
	FROM `categoria3` as c3 LEFT JOIN `categoria2` as c2
	ON c3.`NomeCategoriaPadre` = c2.`Nome`
    GROUP BY c2.`Nome`, c3.`Nome`

		UNION
		
	SELECT c3.`Nome` AS Nome, c2.`Nome` AS Padre
	FROM `categoria3` as c3 RIGHT JOIN `categoria2` as c2
	ON c3.`NomeCategoriaPadre` = c2.`Nome`
    GROUP BY c2.`Nome`, c3.`Nome`;

-- -----------------------------------------------------
-- View `sistemaaste`.`titolario_gerarchico_upper`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `sistemaaste`.`titolario_gerarchico_upper`;
USE `sistemaaste`;
CREATE  OR REPLACE VIEW `titolario_gerarchico_upper`(Nome, Padre) AS

	SELECT c2.`Nome` AS Nome, c1.`Nome` AS Padre
	FROM `categoria2` as c2 LEFT JOIN `categoria1` as c1
	ON c2.`NomeCategoriaPadre` = c1.`Nome`
    GROUP BY c1.`Nome`, c2.`Nome`

		UNION
		
	SELECT c2.`Nome` AS Nome, c1.`Nome` AS Padre
	FROM `categoria2` as c2 RIGHT JOIN `categoria1` as c1
	ON c2.`NomeCategoriaPadre` = c1.`Nome`
    GROUP BY c1.`Nome`, c2.`Nome`;
CREATE USER 'utentebase' IDENTIFIED BY 'totigimmi';

CREATE USER 'utenteamm' IDENTIFIED BY 'utenteamministratore';


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;